2015年11月3日 星期二

Excel-用多項規格(多條件)查詢售價(SUMPRODUCT,OFFSET,資料驗證)

網友想要根據 Excel 中的一個物品規格與售價的資料清單中,在指定規格後查詢到對應的售價,該如何處理?
參考下圖,這個物品規格的資料清單中,包含了欄位:Model、CPU、Memory、Storage、GPU、Price等。
Excel-用多項規格(多條件)查詢售價(SUMPRODUCT,OFFSET,資料驗證)

【公式設計與解析】
首先,處理規格查詢的部分,想要建立可以使用下拉式清單來選取規格,以免 Keyin 規格造成的問題。
1. 在 Excel 中建立一個各欄位內容不重覆的清單,參考下圖。
注意:欄位稱意故意定為『L』再串接原來欄位名稱。
Excel-用多項規格(多條件)查詢售價(SUMPRODUCT,OFFSET,資料驗證)
2. 選取儲存格K1:P13。
3. 按 Ctrl+G 鍵,開啟[到]對話框。
4. 按[特殊]按鈕,開啟[特殊目標]按鈕。
5. 選取「常數」,按下[確定]按鈕。此時有文字內容的儲存格會被選取。
Excel-用多項規格(多條件)查詢售價(SUMPRODUCT,OFFSET,資料驗證)
6. 按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名:LModel、LCPU、LMemory、LStorage、LGPU、LPrice。
7. 選取儲存格I2,設定「資料驗證」,並且設定如下的內容:
資料驗證準則:清單
來源:=INDIRECT("L"&H2)
(利用 INDIRECT 函數將字元L串接儲存格H2的內容轉換為真實儲存格範圍,其中文字組合恰為先前定義好的名稱。)
Excel-用多項規格(多條件)查詢售價(SUMPRODUCT,OFFSET,資料驗證)
8. 複製儲存格I2,貼至儲存格I2:I7。
如此,便能使用下拉式清單來選取規格了。
Excel-用多項規格(多條件)查詢售價(SUMPRODUCT,OFFSET,資料驗證)

接著,來處理查詢價格的公式。
1. 定義名稱:
選取儲存格A1:F17,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名:Model、CPU、Memory、Storage、GPU、Price。
2. 輸入公式:
儲存格I8:=IFERROR(OFFSET(F1,SUMPRODUCT((Model=I2)*(CPU=I3)*
(Memory=I4)*(Storage=I5)*(GPU=I6)*ROW(Model))-1,,,),"查無此規格")
其中:
(Model=I2)*(CPU=I3)*(Memory=I4)*(Storage=I5)*(GPU=I6)*ROW(Model):在SUMPRODUCT 函數中根據四個條件,傳回完全符合的『列號』(ROW(Model))。
OFFSET(F1,傳回的『列號』-1,,,):使用 OFFSET 函數根據傳回的列號,查詢對應的價格。
IFERROR(OFFSET(F1,傳回的『列號』-1,,,),"查無此規格"):如果傳回的是錯誤訊息,表示查不到該規格,則使用 IFFERROR 函數將錯誤訊息顯示『查無此規格』

最後,設定當查詢完成時,同步讓資料清單標示該筆查詢到的內容。
1. 選取儲存格A2:F17。
2. 新增一個設定格式化的條件,如下設定:
規則類型:使用公式來決定要格式化哪些儲存格。
編輯規則:=ROW(A2)=SUMPRODUCT((Model=$I$2)*(CPU=$I$3)*
(Memory=$I$4)*(Storage=$I$5)*(GPU=$I$6)*ROW(Model))
(該公式大部分直接由上述儲存格I8內的公式中擷取,並將儲存格位址改為絶對參照。)
格式設定:紅色組體字。
Excel-用多項規格(多條件)查詢售價(SUMPRODUCT,OFFSET,資料驗證)
如果挑選規格後是『查無此規格』,則不會有任何資料被標示為紅色粗體。

沒有留言:

張貼留言

好康東東