2018年1月31日 星期三

Excel-列出指定項目的清單(MATCH,COUNTIF,OFFSET)

(網友提問)在 Excel 中常會用到要執行查詢的工作,或許你會用 VLOOKUP 函數或是 INDEX 函數,以欄、列交會之處查詢。但是如果要列出相同者的清單,就無法直接使用這兩個函數。這次來試試 OFFSET 函數。
Excel-列出指定項目的清單(MATCH,COUNTIF,OFFSET)

【公式設計與解析】
本題已假設同機種的物料是連續排列。
儲存格E2:=IF(ROW(1:1)<=COUNTIF($A$2:$A$24,$D$2),OFFSET($B$1,
MATCH($D$2,$A$2:$A$24,0)+ROW(1:1)-1,0),"")
複製儲存格E2,貼至儲存格E2:E9。
(1) COUNTIF($A$2:$A$24,$D$2)
計算儲存格D2(機種)在儲存格A2:A24中共有幾個(傳回數值,本例傳回 7)。
(2) MATCH($D$2,$A$2:$A$24,0)
找出儲存格D2(機種)在儲存格A2:A24中第一個的位置(傳回數值,本例傳回 11)。
(3) OFFSET($B$1,第(2)式+ROW(1:1)-1,0)
當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
第(2)式+ROW(1:1)-1:本例依序傳回 11、12、13、...。
將上式代入 OFFSET 函數求得對應的儲存格內容。
(4) IF(ROW(1:1)<=第(1)式,第(3)式,"")
當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
本例中若 ROW 函數的傳回值小於或等於 7,則執行第(3)式,否則顯示空字串。

沒有留言:

張貼留言

檢視其他文章

好康東東