學不完．教不停．用不盡

大家都知道身體質量指數(BMI)的計算公式是「體重(kg)/(身高(M))^2」，這次要設計一個簡單的工具來找合理的體重範圍。

動作是希望輸入身高值和體重值後，能自動顯示一組身高範圍和體重範圍組成的 BMI 值。並且設計以微調按鈕來控制不同體重值，而能自動標示這個身高和體重所在的 BMI 值，並且將合於標準範圍(18.5~24)的部分以淺綠色表示(落在淺紅色區域即為不標準)。(參考下圖)

【表格數值製作】

1. 產生身高數列

儲存格B2：=＄C＄1-5；儲存格C2：=B2+1

在 Excel 中自動篩選是個不錯的工具，不用寫程式即可以篩選出想要的內容。原始資料如下：

例如：在 AAA 欄位中篩選大於 50 的資料：

如果要將篩選後的內容再加以運算，例如要做加總(SUM)：

儲存格B17：=SUM(B4:B15)

【網友問題】

有網友問到：=COUNTIF('0'!F5,B2)+COUNTIF('1'!F5,B2)+COUNTIF('2'!F5,B2)+.......

請問像這樣各工作表的加總有沒有比較簡便的公式，因為目前有一百頁左右的工作表需要加總，麻煩您了，我嘗試過用"*"但是沒有用，算不出答案。

【題目假設】

我把題目假設為如下的工作表(參考下圖)共有多個工作表(名稱1~6)，每個工作表的B欄都有10個數字。如果要根據儲存格E1的條件，要把這 6 個工作表中符合條件的數字找出來並加總。

一年又將結束，又有好多單位送來新的年曆，有時候想要製作自己想要的形式，或許利用 Excel 就可以如願。例如：在一張A4大小的紙張上，置放有兩個月的月曆(如下圖)。

【題目需求】

1. 要能產生萬年曆。

2. 一次產生兩個月。

3. 星期六、日要以不同色彩標示。

下圖左是在 Excel 中常見的一種資料表，想要執行各種運算(如下圖右)，而且全部以 SUMPRODUCT 函數來練習。

【準備工作】

選取儲存格A1:D26，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」選項，建立「類別、售價、打折、數量」四個名稱。

【建立公式】

(1) 所有售出的原價總和

有同仁問到：如何在輸入資料時，當重覆輸入時可以獲得提示？以下三種方式都可以有提示的作用。

【方法一：在核對欄位中顯示V】

檢查A欄中的資料，從儲存格A2到某個儲存格之間有幾個和某個儲存格相同的資料。

儲存格B2：=IF(COUNTIF($A$2:A3,A3)>1,"V","")

在 Excel 中要建立一個有規則的超連結是相當方便的，透過 HYPERLINK 函數將文字轉換為超連結。這次來試試產生超連結在 Google 搜尋引擎和博客來網站上應用。

【依關鍵字產生搜尋結果網址】

先找出Google的搜尋網址及參數：「http://www.google.com.tw/#hl=zh-TW&site=&q=」，置於儲存格C1。

儲存格C3：=HYPERLINK($C$1&A3&IF(B3<>"","+"&B3,""))

IF(B3<>"","+"&B3,"")：判斷是否有輸入第二個關鍵字。

複製儲存格C3，貼至儲存格C3:C7。

有時為了怕顯示在網頁上的郵件位址被惡意搜集，所以會將郵件位址稍微變更一下(參考下圖)，例如：將「@」換成「 at 」，將「.」換成「 dot 」等。如何在 Excel 中以公式來完成這個工作呢？

【置換「@」】

將「@」置換為「 at 」，將儲存格B2的內容轉換為儲存格C2：

儲存格C2：=SUBSTITUTE(B2,"@"," at ")

將儲存格C2的內容還原在儲存格D2：

儲存格D2：=SUBSTITUTE(C2," at ","@")

(一) 資料範圍：一欄中的數列

在 Excel 中有一數列(如下圖左)，如果要計算：奇數和、偶數和、奇數列的和、偶數列的和，該如何處理？

【準備工作】

選取儲存格B1:B21，按一下 Ctrl+Shift+Enter 鍵，勾選「頂端列」選項，建立名稱：數列。

【運算公式】

在 Excel 中如果你在工作表中按日輸入一些資料，做為簡單的行事曆(如下圖左)。現在，想要從這個行事曆資料中摘要出某一週的行事曆，及每天開啟資料夾時自動顯示當週的行事曆，該如何處理？

【準備工作】

將行事曆資料範圍設定名稱為：資料。

【指定週別】

(1) 首先，要求某一週的第一天(星期一)：

在 Excel 的工作表中，如果指定二個起迄日期，要求出這兩個日期之間，每個星期幾的天數有幾天，該如何處理(參考下圖)？以下要分別以陣列公式和 SUMPRODUCT 函數來計算。

【方法一：使用陣列公式】

儲存格D2：{=SUM(IF(WEEKDAY(ROW(INDIRECT($A$2 & ":" & $A$5)),2)=ROW(1:1),1,0))}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。

在 Excel 中有一個每天會增加三筆資料的報表(如下圖左)，如何根據不同產品，產生依星期和月份統計的報表(累計下載和平均檢閱)(參考下圖右)？

因為每天都會增加三筆資料，所以資料的範圍並不固定。

【準備工作】

這次要來練習 COUNTIF 和 SUMIF 公式。

【準備工作】

將各欄位資料設定名稱以方便在公中使用。

選取資料範圍(本例為儲存格A1:A25)，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」選項。定義四個名稱：「序號、姓名、日期、數量」。

選取儲存格F6，選取［資料/資料工具］中的「資料驗證」按鈕。在［儲存格允許］中選取「清單」，在來源中輸入：「=INDIRECT(“日期")」。

使用 Excel 來做數學運算是很方便的，例如以下的六個例子以SUMPRODUCT函數配合ROW函數來運算。

SUMPRODUCT函數可以將多組陣列中的數字相乘後加總，但是其中的參數如果只使用一組陣列，則只會將其加總。在此例中以ROW(1:20)來表示 1,2,3, … , 20 組成的陣列。

儲存格B2：=SUMPRODUCT(ROW(1:20))

儲存格B3：=SUMPRODUCT(1/ROW(1:20))

儲存格B4：=SUMPRODUCT((ROW(1:20))^2)

在 Excel 中根據一個日期和項目所構成的銷售額資料表(參考下圖左)，現在要依月份和星期幾來摘要統計表(參考下圖右)，該如何處理？這個題目要使用 SUMPRODUCT、MONTH、WEEKDAY、ROW 等函數。

【準備工作】

首先，建立名稱，讓後續的公式更簡捷。

選取資料範圍(本例為儲存格A1:C26)，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，建立三個名稱：日期、項目、銷售額。

在 Excel 的工作表中，如果想產生一組不重覆的隨機亂數，例如：1~36，分別置於 6X6 儲存格中，該如何處理？(參考下圖)。

【準備工作】

1. 在儲存格H1中輸入公式「=RAND()」，按下 Enter 鍵後，即會產生一個亂數值。

2. 複製儲存格H1，貼至儲存格H1:H36，共計 36 個亂數。(每按一下 F9 鍵，即可重新產生一組亂數。)

每一本書的背面都有大家熟悉的 ISBN(國際標準書碼)，利用這個 ISBN，我們要在 Excel 中練習檢核輸入的 ISBN 是否正確。首先，介紹檢核碼的計算方法。

【檢核碼的計算方法】

以下資料取自：http://zh.wikipedia.org/wiki/國際標準書號

(1) 10碼

假設某國際標準書號號碼前 9 位是：7-309-04547

計算加權和S：S = 7×10 + 3×9 + 0×8 + 9×7 + 0×6 + 4×5 + 5×4 + 4×3 + 7×2 = 226

在 Excel 中在輸入資料時，如果想要根據起迄的日期，在下拉式清單中選取這段範圍的日期，該如何處理(參考下圖左 )？

【動作一：產生日期數列】

為了在儲存格可以選取起迄日期範圍內的日期，我們必須先產生這段範圍的日期(參考上圖右)。參考以下的做法：

儲存格E2：=D2

在 Excel 中有一個資料表含有項目A和項目B的數列(參考下圖左)，若要將A欄和B欄相乘的結果加總，該如何處理(參考下圖右)？

你可以仿照C欄，將A欄和B欄相乘，然後將C欄的資料加總。但是我們要練習的是以一個儲存格即要完成這個工作。參考以下的作法：

【準備工作】

選取儲存格A1:B22，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：項目A、項目B。

在 Excel 中取得一個資料表(如下圖左)，如果要將項目區分成偶數和奇數分列(如下圖中和下圖右)，該如何處理？本題可以使用陣列公式來完成。

【準備工作】

選取儲存格A1:B26，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，建立名稱：序號、項目。