贊助廠商

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

搜尋本部落格文章資料

廣告贊助

在 Excel 中有一個資料表(如下圖左),想要根據『座號』來取得姓名、成績和名次的資料,該如何處理?(如下圖右)

通常你會使用 VLOOKUP 函數或 HLOOKUP 函數來執行查詢工作,在本例中先觀察這個資料表,是由姓名、座號、成績、名次等四個欄位所組成,若要使用 VLOOKUP 函數來查詢,當取用資料在A欄~D欄時,因為A欄是姓名而非座號,所以無法使用。若取資料B欄~D欄,可以順利用座號查詢,但是無法查詢姓名,因為姓名欄位不在資料範圍。而且查詢結果的顯示順序和原資料的排列順序並不相同。

如何以一個公式便能完成多個儲存格的查詢?還有其他方式可以達到查詢結果,來練習看看。

取代VLOOKUP函數查詢(INDIRECT,INDEX,MATCH)

 

【公式設計與解析】

(1) 定義名稱

若要讓公式精簡易讀,可以藉助定義儲存格範圍的名稱。選取A欄~D欄中有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、座號、成績、名次。

 

(2) 使用 INDEX 函數+MATCH 函數

儲存格F4:=INDEX(INDIRECT(F3),MATCH($G$1,座號,0))

INDIRECT(F3):利用 INDIRECT 函數將儲存格F3的內容(例如:姓名),轉換為儲存格範圍(已定義名稱:姓名)。

MATCH($G$1,座號,0):利用 MATCH 函數取得儲存格G1的內容位於座號陣列中的那一個位置(傳回列號)。

最後,透過 INDEX 函數以查表方式取得座號對應的其他欄位值。

複製儲存格F4,貼至儲存格F4:H4。

文章標籤
創作者介紹

學不完.教不停.用不盡

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


留言列表 (3)

發表留言
  • eoornbbo
  • 尺寸   數量
    58 2
    60 10
    234 15
    275 25
    80 30
    請問excel的函數有可以設定配出600跟640的組合嗎?
  • eoornbbo
  • 尺寸   數量
    58     2
    60     10
    234    15
    275    25
    80     30
    請問excel的函數有可以設定配出600跟640的組合嗎?
找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼