學不完．教不停．用不盡

前一篇文章提到：使用多層下拉式清單結構輸入資料(基礎)

參考網址：http://isvincent.blogspot.com/2010/06/excel_25.html

這次要來練習：不需要每個年級都定義一個名稱，而是要以整個基本資料表為單位來查詢。

各年級資料置於list工作表

1. 將A欄的儲存格設定資料驗證的準則為：

(1) 儲存格內允許：清單

在Excel中輸入資料，如果完全以Key In方式輸入，除了耗費較多時間之外，還可能打錯字。所以如果能以選取方式來輸入資料，應該可以改善這些問題。解決這個問題，利用資料驗證、INDIRECT函數和定義名稱，可以很簡單的解決。

假設要有三個年級，每個年級的班級名稱都不相同，規則上也有所差異。(參考下圖)

假設各年級資料置於list工作表中，作法如下：

1. 定義名稱

(1) 一年級：=list!$A$2:$A$15

在 Excel 中也可以做到選取儲存格內容後，即對應顯示一個圖片。(以Excel 2010為例)

以下就以製作「各地區的天氣預報的報表」來練習，Show工作表為顯示結果，List工作表為基本資料。先將網路上找到天氣相關的圖片，複製到List工作表中，並調整為相同大小，置放於Show工作表的B欄中。

Show工作表

List工作表

首先，為了達到各地區天氣狀況以選取方式產生(免於輸入)(如下圖)，所以要藉助資料驗證的功能。

這次來玩玩在Excel中產生10組偶數和10組奇數，比較其大小後，以設定格式化條件表示結果(如下圖)。如果偶數>奇數，則以紅色圓球表示；如果奇數>偶數，則以黑色圓球表示。

(1) 產生0~1000的偶數

儲存格B2：=INT(RAND()*500)*2

複製儲存格B2到儲存格B2:K2。

(2) 產生0~1000的偶數

在 Excel 中，SUMPRODUCT函數對於製作數字統計相關的摘要表非常容易，再來練習以下的報表製作：

(1) 在B欄要計算每個人的請假次別(不管何種假別)：

儲存格B1：輸入「第1次」

儲存格B2：="第"&COUNTIF(A$2:A3,A3)&"次"

複製儲存格B2到儲存格B2:B21。

在 Excel 的一個大工作表中，如果要選其中某些類型的儲存格，不管使用那一種人工點選的方式，多將是費時費力。你可以讓Excel為你代勞！

選取資料範圍，按一下 F5 鍵，顯示[到]對話框，按一下[特殊]按鈕，顯示[特殊目標]對話框。

例如：選取[空格]選項，則可以快速選取所有的空格，這比起一格一格的選取要方便多了！

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