俞木發(fā)

實(shí)例：在下面的這張截圖中，上半部分是某公司的加班原始記錄數(shù)據(jù)（含每個(gè)員工的信息），現(xiàn)在需要在給某員工（如李四）的截圖中實(shí)現(xiàn)如圖片下半部分所示的效果（圖1）。要求如下：

·同名的員工要進(jìn)行區(qū)分，比如生產(chǎn)1部和生產(chǎn)2部均有名為“李四”的員工。

·選擇相應(yīng)的員工后只高亮顯示該員工的加班記錄，其他員工的數(shù)據(jù)自動(dòng)隱藏。

·選擇員工后自動(dòng)統(tǒng)計(jì)出其加班時(shí)長(zhǎng)和加班費(fèi)。

·選擇員工后通過(guò)Excel生成截圖，以便發(fā)送給員工核對(duì)。根據(jù)上述的要求，我們可以在Excel中依次執(zhí)行下述操作：

1. 對(duì)員工進(jìn)行唯一性的處理

從圖1中可以看到，同名員工所在的部門是不同的，因此對(duì)員工唯一性的標(biāo)注可以通過(guò)“部門+姓名”的方式來(lái)實(shí)現(xiàn)。在原始數(shù)據(jù)表的D列后插入一個(gè)新列，接著定位到E2單元格并輸入公式“=C2&D2”，下拉后就可以將同名員工進(jìn)行區(qū)分了。同上，在I、M列執(zhí)行相同的操作，完成員工的標(biāo)注（圖2）。

這里為了方便對(duì)員工進(jìn)行選擇，可以使用數(shù)據(jù)驗(yàn)證添加下拉列表的方法實(shí)現(xiàn)快捷選擇。因?yàn)閱T工的唯一標(biāo)識(shí)數(shù)據(jù)位于E、I、M列，先定位到O2單元格并輸入公式“=E2”，下拉公式直到顯示為“0”，然后在顯示為“0”的單元格中繼續(xù)輸入公式“=I2”，同上在下一個(gè)為“0”的單元格中輸入公式“=M2”，下拉后在O列中就可以列出所有加班員工的數(shù)據(jù)了（圖3）。

接下來(lái)就是對(duì)O列的員工數(shù)據(jù)去重。選中O2：O22數(shù)據(jù)區(qū)域并復(fù)制，定位到Q2單元格，依次點(diǎn)擊“開(kāi)始→粘貼→粘貼數(shù)值→值”，然后再選中Q2：Q22區(qū)域中的數(shù)據(jù)，依次點(diǎn)擊“數(shù)據(jù)→刪除重復(fù)值→當(dāng)前選定區(qū)域排序→刪除重復(fù)項(xiàng)”，刪除重復(fù)項(xiàng)后再將空值單元格刪除，這樣在Q2：Q12區(qū)域得到的就是不重復(fù)的加班員工的數(shù)據(jù)。最后定位到P2單元格，依次點(diǎn)擊“數(shù)據(jù)→數(shù)據(jù)驗(yàn)證→設(shè)置→序列”，將“來(lái)源”選擇為“=$Q$2：$Q$12”，在P2單元格的下拉列表中就可以選擇加班員工了（圖4）。

2. 高亮顯示員工的數(shù)據(jù)

要實(shí)現(xiàn)在選擇了指定的員工后，其數(shù)據(jù)的高亮顯示，可通過(guò)條件格式來(lái)完成。選中C1： N 8數(shù)據(jù)區(qū)域，依次點(diǎn)擊“開(kāi)始→ 條件格式→新建規(guī)則→ 使用公式確定要設(shè)置格式的單元格”，輸入公式“=$E2=$P$2”（注意E2使用相對(duì)引用，P 2使用絕對(duì)引用），符合條件的單元格設(shè)置為綠色填充，應(yīng)用的區(qū)域是C2：F8。操作同上，再輸入公式“=$I2=$P$2”和“= $ M 2 = $ P $ 2”，應(yīng)用的區(qū)域是“= $ G $ 2 ： $ J $ 8”和“=$K$2：$N$8”（圖5）。

公式解釋：

這里使用三個(gè)公式依次對(duì)加班數(shù)據(jù)中的“部門：加班時(shí)間”區(qū)域進(jìn)行條件應(yīng)用，當(dāng)我們?cè)赑2單元格中選擇了員工姓名后，從E2單元格開(kāi)始，在上述的數(shù)據(jù)區(qū)域中所有包含該員工的數(shù)據(jù)就應(yīng)用上述設(shè)置的條件，即將員工所在的單元格填充為高亮綠色顯示。

為了便于查看數(shù)據(jù)，現(xiàn)在可以將E、M、I和Q列設(shè)置為隱藏，接著選中C2：N8數(shù)據(jù)區(qū)域，將其字體顏色設(shè)置為白色。這樣當(dāng)我們?cè)赑2單元格的下拉列表中選擇相應(yīng)的員工后，C2：N8區(qū)域中符合條件格式的單元格就會(huì)自動(dòng)以高亮綠色顯示，而其他員工的數(shù)據(jù)則會(huì)自動(dòng)“消失”（實(shí)際字體為白色）（圖6）。

3. 自動(dòng)統(tǒng)計(jì)加班數(shù)據(jù)

定位到A10單元格并輸入公式“= P2&"加班費(fèi)小計(jì)："”，即在A10單元格中顯示P2單元格新選擇員工的數(shù)據(jù)，并將其和“加班費(fèi)小計(jì)：”字樣連接起來(lái)。再定位到D10單元格并輸入公式“=SUMIF（$E$2：$N$8，P2，$F$2：$N$8）”，表示使用SUMIF函數(shù)，以P2單元格顯示的姓名作為條件，對(duì)指定區(qū)域F2：N8的加班時(shí)間進(jìn)行統(tǒng)計(jì)。定位到D11單元格并輸入公式“=D10*14”，完成加班費(fèi)的統(tǒng)計(jì)。這樣，我們只要在P2單元格中選擇了員工姓名后，就可以自動(dòng)完成加班時(shí)間和加班費(fèi)的統(tǒng)計(jì)了（圖7）。

4. 完成截圖 發(fā)送以供核對(duì)

選中A1：N11數(shù)據(jù)區(qū)域并復(fù)制，定位到A17單元格，接著依次點(diǎn)擊“開(kāi)始→粘貼→其他粘貼選項(xiàng)→鏈接的圖片”，這樣在下方就會(huì)出現(xiàn)加班費(fèi)的截圖（選擇不同的員工姓名后圖片內(nèi)容會(huì)同步發(fā)生變化）。最后，我們只需將這張圖片使用QQ、微信等發(fā)送給員工進(jìn)行核對(duì)即可（圖8）。