學不完．教不停．用不盡

在 Excel 中，使用設定格式化條件來凸顯某些儲存格是很方便的事。(例如下圖)

藉由輸入一個數值，或是使用控制項-微調按鈕來輸入一個數字，在表格中立即以改變色彩的方式來凸顯對應的儲存格。

(1)第一個表格

將儲存格A2的格式化條件設定為當$A2=$G$2時，改變底色色彩為深色和文字色彩為白色，並套用到儲存格$A$2:$B$21。

在 Excel 中使用[設定格式化的條件]功能，可以產生很多有趣和有用的效果，以下來練習產生一個0~9的亂數，並以數位數字形式呈現(如下圖)：

先將儲存格A1:E7，調整欄寬和列高為要顯示數位數字的外形，色彩分為淺色和深色。

以下已經將依照順序將儲存格B2,C2,D2,D3,D4,D5,D6,C6,B6,B5,B4,B3,C4要顯示的色彩編寫成0,1的字串。(1：深色，0：淺色)

主要是要設定儲存格的條件和格式，首先將儲存格B2,C2,D2,D3,D4,D5,D6,C6,B6,B5,B4,B3,C4都設定為深色。接著逐一設定儲存格的格式化條件如下，並設定為顯示為淺色。

前一篇文章提到多條件AND運算來計算總和：

http://isvincent.blogspot.com/2010/06/excel-and.html

這次來探討：多條件OR運算來計算總和(參考下圖)，你可以使用 SUMPRODUCT 函數和 DSUM 函數。

(1) SUMPRODUCT

儲存格H3：=SUM(D2:D11)-SUMPRODUCT(NOT(B2:B11>5)*NOT(C2:C11>3)*D2:D11)

在 Excel 的工作表中，如果要根據二個以上條件來取出某一欄的內容加總，其條件之間是以 AND 運算來執行，可以有多種方式來達到目的。

例如使用 SUMIFS 函數、SUM+IF+陣列、SUMPRODUCT 函數等方式。

(1) SUMIFS

儲存格H3：=SUMIFS(D2:D11,B2:B11,">5",C2:C11,">3")

根據 B 欄的條件(>5) AND C 欄的條件(>3)，結果為True者，相對取出 D 欄的內容來相加。

在 Excel 中，如果要根據檢定考試分數表，統計參加人數、及格人數、平均分數等，可以使用SUMIFS、COUNTIFS、SUMPRODUCT等函數來完成。

(1)計算報名人數

儲存格G3：=SUMPRODUCT(($B$2:$B$24=G$2)*1,(($C$2:$C$24)=$F3)*1)

複製儲存格G3到儲存格G3:J4。

此方式利用 SUMPRODUCT 函數，將[檢定]和[級別]合於條件者X1(將True、False轉成1、0)後相乘而得到結果。

這次要來練習：在 Excel中 如果要根據一個報名費表格，查詢隨機輸入的人員資料中每個人的報名費，進而建立報名費的小計總表。如下圖：

輸入公式：

儲存格D2：=INDEX($F$2:$H$6,MATCH(B2,$F$2:$F$6,0),MATCH(C2,$F$2:$H$2,0))

將儲存格D2複製到儲存格D2：D24。

藉由第一個MATCH函數：MATCH(B2,$F$2:$F$6,0)，查出[檢定]項目在報名費單價表格中的第幾列。

在Excel中使用萬用字元搭配在SUMIF公式中，可以產生隨意的組合結果，如下圖：

輸人公式：

儲存格F2：=SUMIF($A$2:$A$23,E2,$B$2:$B$23)

將存格F2複製到儲存格F2:F9。

此公式的意義為根據E欄的篩選條件，比對A欄中的內容，如果相符者取出B欄的內容相加。

在Excel中，如果想要建立一個每年每個月星期幾數量的統計表(如下圖)，該如何處理呢？

首先建立一個微調按鈕控制項，其設定如下：

(表單控制項的操作可參考：http://isvincent.blogspot.com/2010/05/excel_7085.html)

接著輸入公式：

一個老師為了鼓勵學生，訂定依考試成績發給獎金的標準：

450 ~ 459：50元

460 ~ 469：100元

470 ~ 479：150元

480 ~ 489：200元

490 ~ 499：250元

在Excel中設定格式化條件是很好的控制工具，這次要拿來製造類似按鈕的凹凸效果。例如：如果儲存格為奇數，則該儲存格呈現凹的效果，如果儲存格為偶數，則該儲存格呈現凸的效果。

例如在儲存格B2設定格式化的條件如下：

(1)=MOD(B2,2)=0，儲存格內容為偶數。