學不完．教不停．用不盡

網友遇到這樣的問題：在 Excel 中有一個去程和回程日期的資料表，如何根據這兩個日期對應查到大人和孩童所對應的票價？

在下圖中，利用兩個條件：出發日期、回程日期，在下圖右側的對照表中查得大人和孩童的票價，並且在對照表中以不同色彩標示查詢結果。

【公式設計與解析】

選取儲存格E1:H10，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：出發日期、回程日期、大人、孩童。

1. 計算大人票價

儲存格B5：=SUMPRODUCT((出發日期=A3)*(回程日期=B3)*大人)

(出發日期=A3)*(回程日期=B3)：利用『雙條件』來判斷，傳回 TRUE／FALSE 陣列，第一個『*』運算相當於執行邏輯 AND 運算。而第二個『*』運算是乘以一個數值，所以會將 TRUE／FALSE 陣列轉換為 1／0 陣列。

在 SUMPRODUCT 函數中代入上式，再求出其『乘積和』，即為所求。

2. 計算孩童票價

儲存格B6：=SUMPRODUCT((出發日期=A3)*(回程日期=B3)*孩童)

說明如(1)。

3. 修正查無資料者

如果你想讓兩個日期對應不到票價者顯示『查無資料』，則公式改為：

儲存格B5：=IF((出發日期=A3)*(回程日期=B3),SUMPRODUCT((出發日期=A3)*(回程日期=B3)*大人),"查無資料")

儲存格B６：=IF((出發日期=A3)*(回程日期=B3),SUMPRODUCT((出發日期=A3)*(回程日期=B3)*孩童),"查無資料")

4. 設定顯示格式

如果你想讓儲存格A3和儲存格B3所對應的票價那一列顯示不同色彩，則可以透過設定格式化的條件來處理。

(1) 先選取儲存格D2:H12。

(2) 選取「設定格式化的條件」選項。

(3) 選取「使用公式來決定要格式化哪些儲存格」

(4) 編輯規則：=($E2=$A$3)*($F2=$B$3)

(5) 設定不同的底色。

你有發現：不論在儲存格中的公式和設定格式化的條件所使用的雙條件是一樣的，你有真正體會其中的運算概念？