網(wǎng)友求助SOS：如何將表①布局的考勤記錄轉(zhuǎn)換成表②布局的考勤記錄。

對于財務(wù)部門或人力資源部門進行考勤統(tǒng)計工作，大多數(shù)情況下都是考勤機導(dǎo)出表②表格形式的記錄，然后想方設(shè)法變成表①那樣，他這個卻是反過來的。為統(tǒng)計工作造成了一定的難度。

微軟Office LTSC 2021專業(yè)增強版 簡體中文批量許可版 2024年09月更新

• 類型：辦公軟件
• 大?。?/span>2.2GB
• 語言：簡體中文
• 時間：2024-09-12

查看詳情

WPS Office 2024 V12.1.0.21541 官方最新正式版

• 類型：辦公軟件
• 大?。?/span>260MB
• 語言：簡體中文
• 時間：2025-06-09

查看詳情

由于原表格數(shù)據(jù)量太大，我們簡化數(shù)據(jù)源，用幾組簡單的數(shù)據(jù)還原真實的職場辦公場景即可：

左側(cè)為簡單的一維格式的考勤數(shù)據(jù)，一列姓名，一列時間點，每行為一組一一對應(yīng)的數(shù)據(jù)。我們要轉(zhuǎn)換為右下側(cè)二維格式的考勤數(shù)據(jù)，列標(biāo)題為時間點，行標(biāo)題為姓名，中間值區(qū)域為對應(yīng)的個數(shù)不等的時間點記錄。

• A列（A2:A8）：員工姓名（張三、李四）
• B列（B2:B8）：Excel日期時間序列號，格式如45778.32847。將B列設(shè)置單元格格式為數(shù)值后可查看。

日期時間序列號由整數(shù)部分（日期）和小數(shù)部分（時間）組成，例如：

• 45778→日期：2025-5-1
• 0.32847→時間：7:53

我們今天講的這個方法，不添加任何輔助列，只在一個單元格輸入一組嵌套函數(shù)公式。這個公式通過Excel的日期時間序列號的特性和數(shù)組運算實現(xiàn)了考勤數(shù)據(jù)的二維透視。

PIVOTBY函數(shù)類似于數(shù)據(jù)透視表功能。

函數(shù)總體結(jié)構(gòu)：

=PIVOTBY(

A2:A8, // 行分類字段（員工姓名）

INT(B2:B8), // 列分類字段（日期部分）

MOD(B2:B8,1),// 待聚合值（時間部分）

LAMBDA(x, TEXTJOIN(CHAR(10),,TEXT(x,"h:mm"))), // 聚合函數(shù)

,0,,0// 參數(shù)占位（不顯示總計行/列）

)

我們一步一步輸入公式，理解作用過程。

輸入公式：

=PIVOTBY(A2:A8,INT(B2:B8),,)

行分組

根據(jù)A2:A8的姓名進行縱向分組，形成張三、李四兩行。

列分組

通過INT(B2:B8)提取日期(整數(shù)部分)，將不同日期的考勤橫向分組。

例如：45778對應(yīng)2025-5-1，45779對應(yīng)2025-5-2

實際效果中的日期，需要設(shè)置單元格格式為“m"月"d"日";@”才能正常顯示。

完善公式參數(shù)：

=PIVOTBY(A2:A8,INT(B2:B8),MOD(B2:B8,1),)

待聚合值處理

MOD(B2:B8,1)提取時間(小數(shù)部分)。

由于目前還沒有設(shè)置第4參數(shù)，第3參數(shù)的待聚合值要靠4參數(shù)的聚合函數(shù)實現(xiàn)，所以暫時顯示錯誤值。

完善公式參數(shù)：

=PIVOTBY(A2:A8,INT(B2:B8),MOD(B2:B8,1),LAMBDA(x,TEXTJOIN(CHAR(10),,TEXT(x,"h:mm"))))

用LAMBDA函數(shù)設(shè)置第4參數(shù)，聚合函數(shù)。

LAMBDA函數(shù)定義第3參數(shù)的待聚合值為x

使用TEXT(x,"h:mm")：

將小數(shù)轉(zhuǎn)換為標(biāo)準(zhǔn)時間格式（如0.328472→7:54）

使用TEXTJOIN(CHAR(10),...)：

用換行符合并同一單元格內(nèi)的多個時間。

動態(tài)數(shù)組工作原理：

公式自動檢測行/列維度組合，例如：

張三在2025-5-1日有兩次打卡（7:53和17:15）

公式會自動創(chuàng)建二維矩陣，交叉位置合并對應(yīng)時間。

實際效果中的換行符（CHAR(10））需要單元格啟用自動換行功能后才能正常顯示。

實際效果中的時間，需要設(shè)置單元格格式為“h:mm;@”才能正常顯示。

繼續(xù)完善公式：

=PIVOTBY(A2:A8,INT(B2:B8),MOD(B2:B8,1),LAMBDA(x,TEXTJOIN(CHAR(10),,TEXT(x,"h:mm"))),,0)

設(shè)置第6參數(shù)為“0”，代表不顯示“總計行”。

繼續(xù)完善公式：

=PIVOTBY(A2:A8,INT(B2:B8),MOD(B2:B8,1),LAMBDA(x,TEXTJOIN(CHAR(10),,TEXT(x,"h:mm"))),,0,,0)

設(shè)置第8參數(shù)為“0”，代表不顯示“總計列”。

推薦閱讀：

• 不要再用透視表! wps的超級匯總函數(shù)PivotBy更好用
• wps函數(shù)PIVOTBY參數(shù)居然有10個! 高效數(shù)據(jù)透視分析指南

最新評論