網友在 Excel 中使用 SUMIF 函數時，想要顯示其符合條件者清單，該如何處理？
【公式設計與解析】
1. 定義儲存格範圍名稱
選取儲存格A1:C25，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、人員、時數。
2. 計算指定人員的時數加總
儲存格E4：=SUMIF(人員,E2,時數)
利用 SUMIF 函數求得在「人員」陣列中符合儲存格E2內容（丁）者，對應的「時數」加總。
3. 列出日期清單
儲存格F2：{=IFERROR(INDEX(日期,SMALL(IF(人員=$E$2,ROW(日期),2^10),
ROW(1:1))-1,1),"")}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加入「｛｝」。
複製儲存格F2，貼至儲存格F2:F14。
(1) IF(人員=$E$2,ROW(日期),2^10)
在陣列公式中，判斷人員陣列中和儲存格E2相同者，藉由 ROW 函數傳回日期的列號，否則傳回一個佷大的數值(2^10：2的10次方(=1024))
本例傳回：2,1024,1024,1024,1024,1024,8,9,10,11,1024,….。
(2) SMALL(第(1)式,ROW(1:1))
根據第(1)式傳回的列號或數值，代入 SMALL 函數由小至大依序取得列號。
本例：2,8,9,10,11,….。
(3) INDEX(日期,第(2)式-1,1)
利用 INDEX 函數在日期陣列中，依第(2)式傳回的數值查表取得對應的儲存內容。
(4) IFERROR(第(3)式,"")
利用 IFERROR 函數，在公式因第(3)式傳回錯誤訊息，以空字串顯示。
4. 列出時數清單
原理同「2. 列出日期清單」：
儲存格G2：{=IFERROR(INDEX(時數,SMALL(IF(人員=$E$2,ROW(時數),2^10),
ROW(1:1))-1,1),"")}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加入「｛｝」。
複製儲存格G2，貼至儲存格GF2:G14。
【延伸練習】
若是不想要另外列出清單，也可以利用格式化條件設定，來讓符合條件者以不同色彩顯示。
1. 選取儲存格A2:C25。
2. 設定格式化的條件：
規則類型：使用公式來決定要格式化哪些儲存格
編輯規則：=$B2=$E$2
設定格式：橙色文字前景色彩
【延伸閱讀】
參考：本部落格中其他關於 Excel INDEX 函數的應用
參考：本部落格中其他關於 Excel SUMIF 函數的應用
參考：本部落格中其他關於 Excel SUMIFS 函數的應用
留言列表