年底將至,公司大部分員工的年假還沒休完。
為了鼓勵大家積極休假,領導要求每位員工先按月提交自己的休假計劃表,原則上公司儘量批准每個人的休假申請,但是如果同一天休假的人數超過 50%,部門負責人會請大家協商後錯開休假時間。
那麼問題來了,如果部門有很多人,每人一張休假表,誰能幫領導彙總成一個總表,以便 一眼便能知曉同一天有哪些人休假?
案例:
下圖 1 至 5 是部門各位員工 11 月的休假計劃表,每人有一個以自己名字命名的獨立工作表,為了方便部門領導檢視及統一規劃,請將所有人的休假表彙總成一個總表,要求如下:
僅列出有人休假的所有日期;
每個日期對應的單元格內列出當天休假的所有員工姓名,以“、”分開。
效果如下圖 6 所示。
解決方案:
先將原始資料表改造一下。
1。 開啟“王鋼蛋”的休假表 ——> 選中整個資料表的第三行 ——> 按 F5 或 Ctrl+G?——> 在彈出的對話方塊中點選“定位條件”
2。 在彈出的對話方塊中選擇“常量”——> 勾選“文字”——> 點選“確定”
此時就選中了所有填寫了“休”的單元格。
3。 輸入“王鋼蛋”——> 按 Ctrl+Enter 回車
用同樣的方式將每個工作表中的休假標識都替換成員工的姓名。
4。 選擇選單欄的“資料”——>“新建查詢”——>“從檔案”——>“從工作簿”
5。 在彈出的對話方塊中找到本休假表文件 ——> 選擇後點擊“匯入”
資料表就上傳至了 Power Query。
6。 在彈出的“導航器”對話方塊中勾選“選擇多項”——> 勾選工作簿中的所有工作表?——> 點選“轉換資料”
7。 展開左邊的“查詢”區域?——> 選擇第一個查詢,即“龍淑芬”——> 選擇選單欄的“主頁”——>“追加查詢”
8。 在彈出的對話方塊中選擇“三個或更多表”——> 選中“可用表”區域中除了當前表以外的所有表?——> 點選“新增”按鈕,將選中的表格都新增到“要追加的表”區域?——> 點選“確定”按鈕
所有查詢都追加到了“龍淑芬”查詢中。
9。 選擇“主頁”——>“將第一行用作標題”
10。 點選第一列標題旁邊的篩選箭頭?——> 取消選擇所有數字?——> 點選“確定”
現在就隱藏了重複的日期行,僅保留每個人的休假記錄。
11。 按住 Shift 鍵,選中所有列?——> 選擇選單欄的“轉換”——>“逆透視列”
12。 點選第一個列名中的屬性標識?——> 在彈出的選單中選擇“整數”,從而將日期列的屬性由文字更改為數值
13。 選中第一列?——> 選擇選單欄的“主頁”——>“升序排序”
14。 選中第一列?——> 選擇選單欄的“轉換”——>“透視列”
15。 在彈出的對話方塊中按以下方式設定?——> 點選“確定”:
值列:選擇“值”
展開“高階選項”——> 在“聚合值函式”區域選擇“不要聚合”
透視結果中,有多人休假的單元格都顯示為錯誤值,所以還要再設定一下。
16。 在程式碼的最右邊括號前新增以下程式碼,表示將文字合併起來,用“、”分隔開:
each Text。Combine(_,“、”)
* 請注意:除了頓號分隔符以外,其他所有符號都必須是英文半形符號。
現在錯誤值就被正確替換成了多個員工姓名,每個姓名之間以“、”隔開。
17。 選擇選單欄的“主頁”——>“關閉並上載”——>“關閉並上載至。。。”
18。 在彈出的對話方塊中選擇“僅建立連線”——> 點選“載入”
現在回到了 Excel。
19。 在右側的“工作簿查詢”區域中選中“龍淑芬”——> 右鍵單擊?——> 在彈出的選單中選擇“載入到。。。”
20。 在彈出的對話方塊中選擇“表”——>“新建工作表”——> 點選“載入”
工作簿中就會新建一個工作表,Power Query 中的合併結果表就載入到了其中。
將表格中的所有單元格設定為自動換行,適當調整單元格的高度與寬度,就是最終需要的結果。
Excel學習世界
轉發、在看也是愛!