學不完．教不停．用不盡

在 Excel 中，如果要根據「開始月份」和「時程」的資料，自動在1~12月份中，在所經過的月份中標示色彩，該如何做呢？(如下圖)

應用設定格式化條件的方式，應是最簡單的，例如：

設定儲存格D2的格式化條件：=AND(D$1>=$B2,D$1<$B2+$C2)

其觀念是如果[月份>=開始月份]且[月份<開始月份+時程]，則標示色彩。

在 Excel 2010 中設定格式化條件，有一種「圖示集」的功能，這次要取這個功能在工作表中顯示16X16點陣字型，應該會很有趣吧！。

如何取得16X16的點陣字型呢？我從網路上取得一位網友自製的程式，可以抓取系統中16X16字型的16進制資料：

文章：http://delphi.ktop.com.tw/board.php?cid=17&fid=56&tid=18012

下載：http://delphi.ktop.com.tw/download.php?download=upload%5C00018012_MakeBit.zip

(非常感謝網友的分享！)

儲存格A1：=VALUE(MID(RIGHT("00000000"&HEX2BIN($R1),8),COLUMN(),1))

在 Excel 中的一個表格，如果要計算相鄰兩欄的兩兩相乘積，你會如何做呢 ？

儲存格L3：=SUM(B3*C3,D3*E3,F3*G3,H3*I3,J3*K3)

這是最簡單的做法。

如果使用 SUMPRODUCT 函數來試試呢？參考以下做法：

儲存格L3：=SUMPRODUCT(MOD(COLUMN(B3:J3)+1,2)*B3:J3*C3:K3)

在 Excel 中常見到以下的報表，如何由一些零散的資料組合，轉換成二維報表呢？(如下圖)

(1)統計次數

儲存格F3：{=SUM(IF($A$3:$A$24=F$2,IF(LEFT($B$3:$B$24,2)=$E3,1,0)))}

將儲存格F3複製到儲存格F3:I6。

使用LEFT函數取出編號第1,2個字元，並使用陣列+IF(IF())，成立得1，不成立得0，統計總和即為次數。

在 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，儲存格內容為偶數。

年的制訂是根據太陽的運動而來，一回歸年是指太陽在天上運行，連續兩次通過春分點的間隔時間，稱為一個回歸年(tropical year)，實際長度為365.24219天，這是真正的一年長度。

曆法上的一年長度為365天，稱為一「曆年(calendar year)」，回歸年會比曆年多出0.24219天(相當於5.8小時)，如此一來，累積4年後為0.96876天，接近一天，為修正之，故曆法中有「閏年」制度，每四年會在2月多29日一天。

然而，累積四年後多的0.96876天，與真正的一日尚差0.03124天，故如果不間斷地按四年一閏的方式修正，百年後將累積成365*100+25＝36525日，又比真正的一世紀日數365.24219*100＝36524.219多了一點點。因此曆法學家便重新規定閏年的規則為：西元年份

(1) 逢4的倍數為閏年。

(2) 逢100的倍數不是閏年。

(3) 逢400的倍數是閏年。

利用Excel來做個練習，如何將一個數字放在一個二維的陣列中？例如在一個10X10的陣列中，如果隨機產生一個數(例如：43)，在這個二維陣列中標示出位置(例如第5列第3欄)。

在儲存格A1中要產生一個1~100的隨機亂數，填入公式：

儲存格A1：=INT(RAND()*100+1)

在儲存格B2:K10中要填入判斷位置的公式：

儲存格B2：=IF((INT($A$1/10)+1=$A2)*(MOD($A$1,10)=B$1),"＊","")

在Excel中分別於不同的工作表中放置各季於四個區域中的四個人的業績，該如何在一個圖表中動態顯示呢？

首先，將第1季至第4季的工作表如上圖建置好，其中四個工作表的結構是一樣的。

在Microsoft Word中(以Office 2010版為例)，如果要在列印時調整紙張和文件的大小，該如何處理呢？以原稿為A4的文件來做示範：

1.選取[檔案]功能表中選取[列印]選項。

2.在視窗最下方選取每張紙要放幾頁文件(例如：每張2頁)。

3.在[配合總張調整大小]中選取要輸出的紙張(例如：B4)。

本例為在B4紙張中，每張紙列印2頁(原稿為A4大小)。

在 Excel 中如果取得一個成績表，試著想要動態顯示成績圖表，該如何操作呢？將會用到查詢表格、微調按鈕、動態顯示圖表中的文字等。

首先，先增一個「微調按鈕」表單工具，其設定如下：

在儲存格A10中輸入公式：=A12&"成績表現"。

接著，在輸入資料查表的公式：

在Excel中提供了多種的數值進位方式，如下圖：

(註：上圖之ROUNDUP為無條件進位至整數，ROUNDOWN為無條件捨去至整數)

上圖例子是將各個小數進位到整數，做法如下表：

其中，MROUND函數要四捨五入至最近的 5 的倍數，使用公式：=MROUND(C1,5*C1/ABS(C1))，