小E為大家準備了50 Excel快捷鍵

領取直接關注公棕号【秋葉Excel】，回複【頭條】！

小夥伴們，大家好，我是農夫，專治疑難雜「數」的農夫~

我想很多從事行政工作的小夥伴，可能經常遇到這樣的問題：

總有很多人一天内打卡很多次，每到月底核算考勤數據，财務面對員工多次打卡的數據時，怎麼看都有點頭疼。

如何将這些數據規整好呢？也就是将下圖中，左邊的長數據，處理成右邊的規範數據？

其實，在 Excel 中，用數據透視表，3 步就能搞定！

❶ 數據檢查

❷ 建立輔助列

❸ 插入數據透視表

數據檢查

我們使用函數方法或者數透方法一定要注意兩個前提條件：

❶ 數據去重；

❷ 時間升序排序。

因此，我們導出來的考勤數據，先要看下是否存在重複值、時間數據列是否升序排列。

如果存在重複值，則需要使用【數據】選項卡下的【删除重複值】進行去重處理；

如不是按升序排列，則需要使用【數據】選項卡下的【排序】進行組内升序處理~

建立輔助列

數據準備完畢後，在 H 列建立輔助列「打卡列」，根據每日打卡次數升序編号。

你以為這是手動數出來的嗎？哪能呀，可别忘了 Excel 是個技術活哦！

通過 COUNTIFS 多條件計數公式，可以輕松實現自動編号！

打卡列輸入公式：

=COUNTIFS($E$2:$E2,E2,$F$2:$F2,F2)

COUNTIFS 多條件計數公式，就是對區域中同時滿足多個條件的單元格，進行計數。

因此，當我們固定住第一行單元格時，随着區域不斷的擴大，COUNTIFS 函數就會計算區域中符合條件（如員工劉備和 7 月 1 日兩個條件同時滿足）的數據出現的次數。

即員工當日在該時間點第幾次打卡數（如劉備在 7 月 1 日 9:00 是一天中的第二次打卡）~

COUNTIFS 函數的寫法是：

=COUNTIFS（條件區域 1，條件 1，條件區域 2，條件 2）

▲ 左右滑動查看

條件區域 1：第一個條件的區域範圍：如姓名列；

條件 1：符合條件 1 的參數：如姓名列中的劉備；

條件區域 2：第二個條件的區域範圍：如日期列；

條件 2：符合條件 2 的參數：如日期列中的 7 月 1 日。

多個條件之間是且（AND）的關系，即隻有兩個條件都滿足的時候才為真（TRUE)~

其中，$E$2:$E2 和$F$2:$F2 的混合引用，實現了随着光标往下拉動，不斷擴大區域的多條件計數的範圍。

而當我們計算出數值的次數後，如何展示第 X 次這種标準的格式？

難道要手動更改第 X 次？

No No No！兩種方法輕松解決！

▋第一種：公式法

通過在公式前後添加「第」和「次」，并用&連接即可。

="第"&COUNTIFS($E$2:$E2,E2,$F$2:$F2,F2)&"次"

▲ 左右滑動查看

但是，要注意這樣得到的結果是字符型哦~

▋第二種：自定義格式

隻需選中整列，點擊數字格式的「自定義格式」，選擇第二個 0，在前面輸入「第」，後面輸入「次」，确定即可。

（0 其實在自定義格式中，為 Excel 中的占位符。）

這樣，我們獲得的結果則是數值型。

下面，我們用動圖展示下：

插入數據透視表

選中所有數據，建立數據透視表。

❶ 打卡列放入「列」中，姓名和日期放入「行」中，時間放入「值」中。

這時小夥伴的數據透視表可能是這個樣子的~

這是因為數據透視表的默認的計算方式是對時間進行【計數】，所以，我們需要将計算方式更改為【求和】。

而更改後我們發現，表格中的怎麼都是小數形式呢？

答案就是數據的呈現方式出了問題，隻需将其格式改為【時間】格式即可。

于是就形成了如下圖的數據：

❷ 選中數據透視後的表格，選擇【設計】選項卡——【報表布局】——選擇【以表格形式顯示】——再選擇【重複所有項目标簽】；

這樣，數據基本就處理好了，隻是多了「行列總計」項和姓名「彙總」。

❸ 去掉「行列總計」這個就更簡單了，選擇【設計】選項卡——【總計】——【對行和列禁用】即可！

❹ 對于姓名「彙總」情況，隻需選擇【設計】選項卡下的【分類彙總】——【不顯示分類彙總】就行。

步驟太多記不住？動圖呈上~

總結一下

整理多次打卡的考勤數據，用數據透視表，隻需 2 步就解決了，是不是很簡單！

好了，今天的教程就到這裡了，還不會的小夥伴趕緊打開電腦操練起來吧~

我是農夫，專治疑難雜「數」的農夫~

小E為大家準備了50 Excel快捷鍵

領取直接關注公棕号【秋葉Excel】，回複【頭條】！

,

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

查看全部