在學校中要辦理研習時,因為行政同仁給的資料是姓名第2個字缺字的清單(如下圖D欄),而針對這個缺字的姓名清單要利用原始資料表(如下圖的A欄和B欄)來查詢每個人的Email(如下圖E欄),該如何處理?

在此,先假設在原始清單中並沒有姓名第1個字和第3個字重覆者。

Excel-如何利用缺字的姓名順利進行查詢(SUMPRODUCT,OFFSET)

 

【公式設計與解析】

選取儲存格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 函數取得對應的儲存格內容。

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel SUMPRODUCT OFFSET
    全站熱搜

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