小夥伴們好啊，今天咱們來分享一個數據整理的實例。

昨天，函數正式課群裡有位小夥伴發了這樣一份考勤數據：

每個人全天的考勤數據都擠在一個單元格裡，現在要計算每個人每天的實際出勤時間。

最終希望得到的數據是這樣的：

回過頭來咱們觀察一下數據結構，看看有沒有可以利用的規律。

首先來看看K列姓名的規律：每個姓名之間都間隔一行：

利用這個規律，咱們就可以在“考勤整理”工作表裡，使用公式提取員工姓名了。

在“考勤整理”工作表的B2單元格輸入以下公式，下拉至出現空白為止：

=OFFSET('10月考勤'!K$5,(ROW(A1)-1)*2,)&""

簡單說說公式的意思：

(ROW(A1)-1)*2部分，計算結果為0，公式下拉時，會得到從0開始、按2遞增的序号0、2、4、6、8……。

OFFSET函數以“10月考勤”工作表K5單元格為基準，以ROW函數的計算結果作為向下偏移的行數，也就是公式每下拉一行，就從“10月考勤”工作表K5單元格向下偏移兩行。

當OFFSET函數引用空單元格的時候，會顯示成無意義的0，所以在公式最後加了一個小尾巴&""，目的就是将這個無意義的0變成空文本，從而顯示成空白了。

接下來就要提取每天的工作時長了， 咱們再來觀察一下打卡時間的規律。

以“白秀蓮”1号的打卡數據為例，打卡數據位于姓名的下一行，雖然有多次打卡記錄，但是咱們看前5位，其實就是最早的上班時間，而後5位就是最晚的下班時間了。

接下來咱們先看看如何定位到這個打卡時間所在的單元格：

=INDEX('10月考勤'!A:A,MATCH($B2,'10月考勤'!$K:$K,) 1)

MATCH函數的作用是根據指定的查詢值，返回該查詢值在查詢區域中首次出現的位置。

INDEX函數的作用是根據指定的位置信息，從一個區域中返回對應位置的内容。

公式中的MATCH($B2,'10月考勤'!$K:$K,)部分，利用B列已經提取出的姓名，借助MATCH函數計算出該姓名首次出現的位置。最後加上1，就是這個人的打卡記錄所在的行了。

再使用INDEX函數，從“10月考勤”工作表的A列提取出該員工1号的打卡記錄。

要從打卡記錄中分别提取前 5位和後5位，這裡需要使用LEFT函數和RIGHT函數。

LEFT函數的作用是從數據的左側開始，提取指定位數的字符。

RIGHT函數的作用，是從數據右側開始提取指定位數的字符。

先計算出下班打卡時間：

RIGHT(INDEX('10月考勤'!A:A,MATCH($B2,'10月考勤'!$K:$K,) 1),5)

再計算出上班打卡時間：

LEFT(INDEX('10月考勤'!A:A,MATCH($B2,'10月考勤'!$K:$K,) 1),5)

二者相減，就是每天的工作時長。

如果某一天的打卡記錄是空白，RIGHT和LEFT函數會返回空文本，空文本再相減的話，就返回錯誤值了，所以要加上一個IFERROR函數，來屏蔽錯誤值。

最後咱們把以上兩段公式組合一下，在C2單元格輸入以下公式，向右向下複制即可：

=IFERROR(RIGHT(INDEX('10月考勤'!A:A,MATCH($B2,'10月考勤'!$K:$K,) 1),5)-LEFT(INDEX('10月考勤'!A:A,MATCH($B2,'10月考勤'!$K:$K,) 1),5),"")

圖文制作：周慶麟

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!