贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

網友問到:在 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 函數得到對應的學生姓名。

文章標籤

vincent 發表在 痞客邦 留言(2) 人氣()

網友問題:參考下圖,如何將圖上半的資料清單轉換為圖下半的樣式?

Excel-表格資料重組(OFFSET,SMALL,ROW,陣列公式)


儲存格B12:{=IFERROR(OFFSET($A$1,SMALL(IF(B$2:B$9<>"",ROW
($B$2:$B$9),""),ROW(1:1))-1,0),"")&IFERROR(OFFSET(B$1,SMALL(
IF(B$2:B$9<>"",ROW($B$2:$B$9),""),ROW(1:1))-1,0),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格B12,貼至儲存格B12:E19。

姓名:IFERROR(OFFSET($A$1,SMALL(IF(B$2:B$9<>"",ROW($B$2:$B$9),""),
ROW(1:1))-1,0),"")

數值:IFERROR(OFFSET(B$1,SMALL(IF(B$2:B$9<>"",ROW($B$2:$B$9),""),
ROW(1:1))-1,0),"")

姓名和數值的公式以『&』串接。

詳細公式說明和以下這篇類似:

Excel-依日期區間列出符合的清單(SMALL,OFFSET,ROW,陣列公式)

文章標籤

vincent 發表在 痞客邦 留言(2) 人氣()

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼