2017年1月12日 星期四

Excel-在數個區間中根據資料求得編號(SUMPRODUCT,OFFSET)

網友問到 Excel 的問題:如何在數個區間中根據資料求得編號?
如下圖,資料被置放在不同的四個區間中,現在要根據一個資料內容,來求得其編號,該如何處理?
Excel-在數個區間中根據資料求得編號(SUMPRODUCT,OFFSET)

【公式設計與解析】
儲存格B9:=OFFSET(A1,SUMPRODUCT((B2:K5=B8)*ROW(B2:K5))-1,
SUMPRODUCT((B2:K5=B8)*COLUMN(B2:K5))-2)
(1) 求得資料的列號:SUMPRODUCT((B2:K5=B8)*ROW(B2:K5))
ROW(B2:B5):會傳回每個儲存格的列號。
(2) 求得資料的欄號:SUMPRODUCT((B2:K5=B8)*COLUMN(B2:K5))
COLUMN(B2:K5):會傳回每個儲存格的欄號
(3) 根據欄號和列號求得編號:OFFSET(A1,第(1)式-1,第(2)式-2)
由儲存格A1為起始點,將列號減 1,將欄號減 2,即可獲得對應的儲存格位置。

沒有留言:

張貼留言

檢視其他文章

好康東東