網友想要根據 Excel 中的一個物品規格與售價的資料清單中，在指定規格後查詢到對應的售價，該如何處理？
參考下圖，這個物品規格的資料清單中，包含了欄位：Model、CPU、Memory、Storage、GPU、Price等。
【公式設計與解析】
首先，處理規格查詢的部分，想要建立可以使用下拉式清單來選取規格，以免 Keyin 規格造成的問題。
1. 在 Excel 中建立一個各欄位內容不重覆的清單，參考下圖。
注意：欄位稱意故意定為『Ｌ』再串接原來欄位名稱。
2. 選取儲存格K1:P13。
3. 按 Ctrl+G 鍵，開啟[到]對話框。
4. 按[特殊]按鈕，開啟[特殊目標]按鈕。
5. 選取「常數」，按下[確定]按鈕。此時有文字內容的儲存格會被選取。
6. 按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名：LModel、LCPU、LMemory、LStorage、LGPU、LPrice。
7. 選取儲存格I2，設定「資料驗證」，並且設定如下的內容：
資料驗證準則：清單
來源：=INDIRECT("L"&H2)
(利用 INDIRECT 函數將字元L串接儲存格H2的內容轉換為真實儲存格範圍，其中文字組合恰為先前定義好的名稱。)
8. 複製儲存格I2，貼至儲存格I2:I7。
如此，便能使用下拉式清單來選取規格了。
接著，來處理查詢價格的公式。
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內的公式中擷取，並將儲存格位址改為絶對參照。)
格式設定：紅色組體字。
如果挑選規格後是『查無此規格』，則不會有任何資料被標示為紅色粗體。
留言列表