2016年2月1日 星期一

Excel-在多個類別中查詢(VLOOKUP,INDIRECT)

有網友問到:如何在 Excel 中,利用一個多類別的折扣對照表來查詢各種數量的折扣。例如下圖中,分別有陸運、海運、空運三類的不同數量/折扣的對照表,要如何設計公式,只要挑選運別,輸入數量後,自動產生折扣值?
先前的一篇文章:Excel-在兩個資料表中查詢(VLOOKUP),已有一些說明。本文用不一樣的方式來操作。在下圖中,以陸運為例:數量超過200,折扣為95%;數量超過300,折扣為92%。
Excel-在多個類別中查詢(VLOOKUP,INDIRECT)

【公式設計與解析】
1. 為各種類別命名儲存格範圍。
(1) 選取儲存格E2:F9。
(2) 選取[公式/已定義名稱]功能表中的「定義名稱」選項。
Excel-在多個類別中查詢(VLOOKUP,INDIRECT)
(3) 在[名稱]方塊中,已自動產生「陸運」,按下[確定]按鈕即可。
(如果沒有自動產生,請自行輸入「陸運」)。
Excel-在多個類別中查詢(VLOOKUP,INDIRECT)
(4) 依步驟(3),再設定「海運」和「空運」。

2. 設計下拉式選單
(1) 選取儲存格A2:A18。
(2) 選取[資料/資料工具]功能表中的「資料驗證/資料驗證」選項。
(3) 設定資料驗證:
儲存格內允許:『清單』;來源:『陸運,海運,空運』。
Excel-在多個類別中查詢(VLOOKUP,INDIRECT)

3. 設計公式
(1) 儲存格C2:=VLOOKUP(B2,INDIRECT(A2),2,TRUE)
INDIRECT(A2):將儲存格A2中的文字轉換為一個儲存格名稱,該名稱在步驟1中已先行定義好名稱了。
再透過 VLOOKUP 函數利用查表方式找出數量對應的折扣,注意:要選取參數:TRUE
(2) 複製儲存格C2,貼至儲存格C2:C18。

沒有留言:

張貼留言

好康東東