☰

使用 Excel 和 PowerQuery 設計大眾考勤系統

tag-icon • tag-icon microsoft-excel microsoft-excel-2016 power-query microsoft-powerbi
使用 Excel 和 PowerQuery 設計大眾考勤系統

我正在建立一個 Excel 電子表格，用於追蹤大量人員的日常出勤記錄。這裡顯示的所有資料範例都是虛構的，包括資料結構。出於說明目的，僅顯示了資料結構的要點，因為我正在嚴格鎖定的電腦上處理敏感的個人資料（即沒有互聯網，禁止任何形式的腳本（如VBA），只有Microsoft Office 套件來操作數據）。

包含人事記錄的主表如下所示：

Master List

ID     Name     Home Address         Phone Number   Remarks
1      Alice    12 Somewhere Rd.     123456         remark 1 goes here
2      Bob      23 Someplace Dr.     234567         remark 2 goes here
3      Charlie  34 Somewhen Blvd.    345678         remark 3 goes here

正如我們所看到的，在考勤期間並不需要所有資訊。因此，我執行 Power Query 將其精簡為必需的內容

Attendance

ID     Name      Remarks
1      Alice     remark 1 goes here
2      Bob       remark 2 goes here
3      Charlie   remark 3 goes here

之後，我將出勤條目附加到表格右側。

Attendance

ID     Name      Remarks               2020/6/25   2020/6/26  ...............
1      Alice     remark 1 goes here    P           P          ...............
2      Bob       remark 2 goes here    P           VL         ...............
3      Charlie   remark 3 goes here    P           P          ...............

最後我設定自引用表這樣，每次刷新出勤表時，出勤條目都會保留在其原始行中，這意味著如果我在中間某處插入人員，就不會出現任何問題。

然而，當我意識到我需要用有關休假本身的詳細資訊標記每個特殊出勤條目（例如病假）時，我很快就遇到了一個問題。我最初嘗試直接將註釋添加到出勤單元格中，但意識到註釋並不遵循Power Query 生成的表格（即它保留在原始的絕對單元格位置中，這意味著插入新人員將拋出下半部分）表格正好對齊一行）。

後來我嘗試使用超連結將每個出勤條目連結到一個單獨的表，其中包含有關休假的備註/詳細信息，但在查詢刷新後該連結變得無法單擊。

我應該如何為每日每人考勤系統設計一個備註系統，這是我一開始的方法，我查詢主列表，減少列，然後附加更多列，即使是開始的正確方法？

謝謝你！

答案1

我的方法是在開始時查詢主列表，減少列，然後附加更多列，即使是開始的正確方法？

這是這樣做的一種方法。我認為您只需為每個日期添加一個備註列，然後按照您已經採取的方法來獲取自引用表，就可以實現備註空間。看起來像這樣的東西：

在此輸入影像描述

就我個人而言，我不會這樣做，因為當出現諸如“你能告訴我愛麗絲和鮑勃在過去 30 個工作日內缺席了多少次嗎？”之類的問題時（一如既往）或“你能告訴我平均缺勤天數嗎？”等等，它們將更難解決（不是那很難公平，但仍然）。

因此，我更願意使用標準化數據進行輸入，然後可以將其用於報告。

考慮到這一點，我從兩張表開始：

  1. 人們
  2. 日期

在此輸入影像描述

Include 欄位使用此公式（稍後會清楚原因）：

=[@Dates]<=TODAY()

我對每個表創建了一個查詢。在日期查詢中，我過濾了 Include=TRUE，然後刪除了 Include 列。然後，我向人員查詢新增了一個自訂列來引用日期查詢。像這樣：

在此輸入影像描述

您可以看到它已將第三列新增至人員查詢。擴展後，可以為所有人提供一個很好的所有日期清單（這本質上是笛卡爾連接）：

在此輸入影像描述

我將其加載到工作簿中並添加兩列 - 出勤和備註。這樣做的好處是，您現在可以添加任意數量的列來註釋或標記每個人日。

在此輸入影像描述

此時，我讀到了關聯到您在貼文中引用的頁面。起初我聽錯了。然後我又錯了。然後我意識到它是如何實際上工作並最終獲得了一個自我參考表，我現在可以根據該表編輯我的出勤和評論，並為新員工刷新，讓一切都保持在正確的位置。

這樣做的另一個好處是，每天當我打開工作簿並刷新查詢時，當天每個員工的一組新行都會自動添加到查詢中（這最終是在日期表）。

編輯：

您可以使用 PowerQuery 將資料轉換為各種報表格式，包括貼文中第三個表格的格式。

對我上面答案的最終結果建立一個查詢，然後執行以下操作：

選擇出席和備註，然後使用“變換”>“任意列”>“逆透視列”>“僅逆透視所選列”：

在此輸入影像描述

您會注意到此操作會刪除所有空白儲存格。如果您想保留這些內容，則需要在使用 Unpivot 之前填充它們。這可以透過將值從 null 替換為您喜歡的任何值來完成。

取消透視後，我有屬性和值列。現在我想選擇日期。我將分隔符號設為“-”（當然，您可以使用您喜歡的任何內容），最後我得到一列，表示日期及其所引用的資料類型（出席或備註）。

然後，我選擇這個新的合併列並使用“變換”>“任意列”>“透視列”並按如下方式配置：

在此輸入影像描述

最終結果就是您想要的格式。同樣，如果您希望此報告包含所有人員，無論他們的出勤情況是否已輸入，您需要在使用 Unpivot 之前在出勤列中設定一個虛擬值。

在此輸入影像描述

如果你能原諒這些口語的話，這一切可能看起來有點像在房子裡走來走去。一般來說，我鼓勵任何人在確定解決方案之前考慮如何使用這些數據。根據我的經驗，首先對數據進行標準化（正如我所提議的那樣），然後根據該表建立報告是一種更可持續的方法，並且提供了更大的靈活性。

相關內容