文/王樂生

以前使用excel都是處理一般的數(shù)據(jù)，今天老板交了一個任務(wù)給我—讓我使用指紋打卡機(jī)里面的數(shù)據(jù)做出考勤表出來。當(dāng)時一聽到這個任務(wù)，我是懵逼的，不信你看。

PS：在網(wǎng)上找不到類似的解決辦法，我在思考為什么，是不是另有玄機(jī)或者更好的解決辦法而我不知道？？？

這是指紋打卡機(jī)原始數(shù)據(jù)：

指紋打卡機(jī)原始數(shù)據(jù)

老板要求做出來的效果：

老板要求做出來的效果

其實(shí)做出這樣的效果最簡單的就是——手工輸入。但是我嫌太麻煩而且又太慢了（眼睛疼），所以就想辦法用excel做出這樣子。

首先我想到的是上網(wǎng)查，結(jié)果大家都是簡單粗暴只有上下班，沒有像老板要求的這么復(fù)雜的例子。無奈。。。

好啦，經(jīng)過兩個多小時辛苦的探索，我做出來的效果圖如下：

最終做出來的效果圖

做的時候真的是眼睛疼，而且又緊張，不過還好老天眷顧+腦瓜子靈光，終于也算是做出來了，可能沒有那么好看，可是我這個版本數(shù)據(jù)很齊全對不對！

好啦，現(xiàn)在開始說步驟。

第一步，把打卡機(jī)數(shù)據(jù)ctrl+A全選，ctrl+c復(fù)制，打開一個excel文檔，ctrl+v粘貼。

把行列對齊

初步的效果圖如下：

把數(shù)據(jù)粘貼到excel中的效果

好了，現(xiàn)在我們要做的就是先給name（姓名）一列排序，先隨便選中name（姓名）一列的某個單元格（注意：有姓名的單元格）數(shù)據(jù)—>排序—>A-Z

排序

接下來選中name（姓名）一列，ctrl+c復(fù)制，粘貼在DateTime（打卡時間）后面?，F(xiàn)在效果如圖：

復(fù)制名字列

刪除空行

接下來我們要從DateTime（打卡時間）里面取出日期和時間

取好列名

在日期的第一格使用=INT(G2)公式，取出日期

使用int函數(shù)取出日期

回車后的效果圖

截取日期的效果圖

在這里為什么結(jié)果會是數(shù)字，因?yàn)檫@是取出來的時間戳，時間戳都是用數(shù)字表示，我們將這一列單元格設(shè)置成日期格式

設(shè)置日期列單元格格式

設(shè)置好之后的效果圖

單元格格式設(shè)置好的效果圖

接著，把鼠標(biāo)放在這個單元格的右下角，當(dāng)鼠標(biāo)變成黑色的實(shí)心的十字架時，將整列填充完畢

日期列效果圖

接下來取時間

使用=G2-I2公式，取出時間

使用=G2-I2取時間公式示例

記得要給時間列設(shè)置單元格格式

給時間列設(shè)置單元格格式

使用鼠標(biāo)十字架填充時間列

取出日期和時間效果圖

接下來很重要的一步，通過時間分辨出每個時間段上下班的標(biāo)志，在這里我們要分辨兩次。

因?yàn)榈谝淮问欠直鏈?zhǔn)確的上下班時間段，第二次則是分辨出上午下午晚上就OK了

先取好列名

取好列名

使用? =IF(J2<--"8:10:00","上午上班",IF(J2<--"12:20:00","中午下班",IF(J2<--"13:40:00","下午上班",IF(J2<--"17:40:00","晚上下班",IF(J2<--"18:10:00","加班上班","半夜下班")))))? 函數(shù)分辨出上下午上下班時間

使用公式效果圖

使用鼠標(biāo)十字架填充好上下午上下班時間這一列

接下來我們來使用? =IF(J2<--"8:10:00","上午",IF(J2<--"12:10:00","上午",IF(J2<--"13:40:00","下午",IF(J2<--"17:40:00","下午",IF(J2<--"18:10:00","晚上","晚上")))))? 函數(shù)分辨出上午下午晚上

使用公式分辨上午下午晚上

使用鼠標(biāo)十字架填充好上下午這一列

分辨時間段的效果圖

現(xiàn)在選中name（姓名）、日期、時間、上下午上下班時間、上下午這五列

選中需要的五列

點(diǎn)擊數(shù)據(jù)——>數(shù)據(jù)透視表

選擇數(shù)據(jù)透視表

彈出來的對話框

點(diǎn)擊彈出來的對話框——>確定按鈕

然后會出現(xiàn)以下頁面，重點(diǎn)在右邊→_→

重點(diǎn)看右邊

點(diǎn)擊透視表區(qū)域上面的復(fù)選框，按以下效果圖排列

必須要是一模一樣的，否則后面的效果做不出來。

行列字段可以拖動

行列排列的效果圖

現(xiàn)在點(diǎn)擊值——>計數(shù)項(xiàng)：時間旁邊的黑色小三角

點(diǎn)擊小三角出來的效果

選擇“值字段設(shè)置”

在彈出來的對話框選擇“最小值”—>點(diǎn)擊確定

選擇最小值

然后在數(shù)據(jù)區(qū)域設(shè)置時間格式

效果圖如下

效果圖

現(xiàn)在匯總太多看起來有點(diǎn)麻煩

選中“上午匯總”單元格右擊，將分類匯總“上下午”的√去掉

去掉分類匯總上下午

因?yàn)楝F(xiàn)在在數(shù)據(jù)透視表里面，所以里面的數(shù)據(jù)我們現(xiàn)在還不能隨意的修改和刪除，現(xiàn)在我們復(fù)制所有的數(shù)據(jù)，粘貼到另一個excel文件里面

粘貼到新的excel文件中

粘貼好后，現(xiàn)在我們就可以隨意的對數(shù)據(jù)進(jìn)行修改、添加和刪除了

注意：出現(xiàn)######號是因?yàn)槿掌诟袷讲粚?，設(shè)置一下日期格式就好了

接下來，我們刪除沒用的日期時間，調(diào)整上下午的位置，然后在匯總的前面插入一行來計算每天加班的時間

效果圖

使用? =INT((HOUR(E7-E6)*60+MINUTE(E7-E6))/30)/2? 公式來計算加班的時間

注意，加班這一行的單元格格式要是數(shù)值型的，加班匯總那一行的單元格格式也要是數(shù)值型的

加班匯總所使用的函數(shù)：sum（）；

用完公式后的效果圖如下：

效果圖

在這里要注意的是，如果單元格有數(shù)據(jù)空白則計算不出來數(shù)據(jù)

如果有多個員工，那么計算加班的方法差不多，公式也只要修改行號就好了

最終效果圖

附：我們應(yīng)該怎么知道單元格的地址

怎么知道當(dāng)前單元格的地址

在上圖的左上角紅色框框內(nèi)的E8就是當(dāng)前（綠色被選中的單元格）單元格的地址。

以上。