2017年9月2日 星期六

Excel-依數值區間找尋對應姓名(SUMPRODUCT,ROW,OFFSET)

網友問到:在 Excel 中有一個餐券號碼的清單,如何找出任一個餐券號碼所持有的學生姓名?
Excel-依數值區間找尋對應姓名(SUMPRODUCT,ROW,OFFSET)

【公式設計與解析】
選取儲存格A1:C24,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:學生姓名、餐券起號、餐券迄號。
儲存格F2:=OFFSET($A$1,SUMPRODUCT((E2>=餐券起號)*(E2<=餐券迄號)*
(ROW(學生姓名)))-1,0)
(1) (E2>=餐券起號)*(E2<=餐券迄號)
在 SUMPRODUCT 函數中,利用雙條件來判斷餐券號碼是否在範圍內。其中『*』運算子相當於執行邏輯 AND 運算。
(2) ROW(學生姓名)
利用 ROW 函數取得每個學生姓名所在的列號。
(3) SUMPRODUCT((E2>=餐券起號)*(E2<=餐券迄號)*(ROW(學生姓名))
利用 SUMPRODUCT 函數傳回學生姓名對應的列號。
(4) OFFSET($A$1,第(3)式-1,0)
利用第(3)式傳回的學生姓名對應列號代入 OFFSET 函數得到對應的學生姓名。

沒有留言:

張貼留言

檢視其他文章

好康東東