有網友問到:如何在 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。

arrow
arrow
    全站熱搜

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