2018年2月19日 星期一

Excel-查詢分組組別(SUMPRODUCT,COLUMN,OFFSET)

(網友提問)參考下圖,如何在 Excel 中根據分組表將成員名單顯示每位成員所分的隊伍?
下圖中,右邊是分組表,左邊是成員名單,有些成員沒有被分到組別,要將有分到組別者,顯示其組名。
Excel-查詢分組組別(SUMPRODUCT,COLUMN,OFFSET)

【公式設計與解析】
儲存格C3:=IFERROR(OFFSET($E$2,0,SUMPRODUCT(($E$3:$H$13=A3)*
COLUMN($E$3:$H$13))-5),"")
(1) COLUMN($E$3:$H$13)
找出儲存格E3:H13中,每個儲存格的欄號(傳回一個數字)。COLUMN(E3)=5、COLUMN(F3)=6、COLUMN(G3)=7、COLUMN(H3)=8。
(2) SUMPRODUCT(($E$3:$H$13=A3)*COLUMN($E$3:$H$13))
由於每位隊員僅會被分在一組中,所以利用 SUMPRODUCT 函數找出儲存格A3內容對應組別的欄號。
(3) OFFSET($E$2,0,第(2)式-5)
將第(2)式傳回的欄號置入 OFFSET 函數,取得對應的儲存格內容(組名)。其中『-5』是因為分組表由E欄開始。
(4) IFERROR(第(4)式,"")
若成員沒有被分到組別,可能傳回錯誤訊息,利用 IFFERROR 函數將錯誤訊息置換為空字串。

沒有留言:

張貼留言

檢視其他文章

好康東東