在學校中要辦理研習時,因為行政同仁給的資料是姓名第2個字缺字的清單(如下圖D欄),而針對這個缺字的姓名清單要利用原始資料表(如下圖的A欄和B欄)來查詢每個人的Email(如下圖E欄),該如何處理?
在此,先假設在原始清單中並沒有姓名第1個字和第3個字重覆者。
【公式設計與解析】
選取儲存格A1:A200,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名。
儲存格E2:=OFFSET($A$1,SUMPRODUCT((LEFT(姓名,1)=LEFT(D2,1))*(RIGHT
(姓名,1)=RIGHT(D2,1))*ROW(姓名))-1,1)
(1) (LEFT(姓名,1)=LEFT(D2,1))*(RIGHT(姓名,1)=RIGHT(D2,1))
在 SUMPRODUCT 函數中使用二個條件:
LEFT(姓名,1)=LEFT(D2,1):
判斷在姓名陣列中是否有和儲存格D2左邊第1個字相同者,傳回 TRUE/FALSE 陣列。
RIGHT(姓名,1)=RIGHT(D2,1):
判斷在姓名陣列中是否有和儲存格D2右邊第1個字相同者,傳回 TRUE/FALSE 陣列。
公式中的「*」相檔於執行邏輯 AND 運算,會將 TRUE/FALSE 陣列轉換為 1/0 陣列。
(2) SUMPRODUCT(第(1)式*ROW(姓名))
ROW(姓名)會傳回姓名陣列的每一個儲存格列號。
第(1)式*ROW(姓名)會傳回符合條件者的儲存格列號。
(3) OFFSET($A$1,第(2)式-1,1)
利用第(2)式的傳回值代入 OFFSET 函數取得對應的儲存格內容。
留言列表