俞木發(fā)

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

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

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

·選擇員工后自動統(tǒng)計出其加班時長和加班費。

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

1. 對員工進行唯一性的處理

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

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

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

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

要實現(xiàn)在選擇了指定的員工后，其數(shù)據(jù)的高亮顯示，可通過條件格式來完成。選中C1： N 8數(shù)據(jù)區(qū)域，依次點擊“開始→ 條件格式→新建規(guī)則→ 使用公式確定要設(shè)置格式的單元格”，輸入公式“=$E2=$P$2”（注意E2使用相對引用，P 2使用絕對引用），符合條件的單元格設(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）。

公式解釋：

這里使用三個公式依次對加班數(shù)據(jù)中的“部門：加班時間”區(qū)域進行條件應(yīng)用，當(dāng)我們在P2單元格中選擇了員工姓名后，從E2單元格開始，在上述的數(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)我們在P2單元格的下拉列表中選擇相應(yīng)的員工后，C2：N8區(qū)域中符合條件格式的單元格就會自動以高亮綠色顯示，而其他員工的數(shù)據(jù)則會自動“消失”（實際字體為白色）（圖6）。

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

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

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

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