學不完．教不停．用不盡

【觀察】

在 Excel 中的運算結果，其有效位數可達 15 位，大部分在儲存格上會以四捨五入方式來顯示，但是取用這個儲存格來運算時，並不是以「顯示值」來運算，而是以「實際值」來運算。

以下圖為例，在C欄中是A欄/B欄的運算結果，再取小數點3位。如果以C欄的結果來運算，在D欄中顯示C欄X2的結果。

【改變】

在 Excel 中常會有跟日期有關的記錄，有人問到：如果想要將已過去的日子中的記錄，以淡化的文字顯示，而且只要打開檔案，即會自動根據今天的日期來判斷。該如何處理呢？

【想法】

因為資料表中已有日期的數列，所以只要取得今天的日期來做比較，小於今日者，設定為淺灰色，大於或等於今日者，顯示原來的顏色。這些功能可以使用設定格化條件來處理？

【做法】

在 Excel 的工作表中，如果想要隨機產生一個可能是英文字(大、小寫字母)或是數字(0,1,…,9)，該如何處理？

【基本概念】

參考下圖右，可知數字的 ASCII 碼，位於 48~57 (10 進制數)，大寫字母位於 65~90，小寫字母位於 97~122。

【設定公式】

在 Excel 中如果在儲存格中記錄了日期和時間，但是有人有某種需求，在取用來計算時，只想要使用日期部分，該如何處理？

以下圖為例，平圴值公式為：( B – A ) / ( B時間 - A時間 )，其中E欄是以完整日期時間來計算，F欄則是只取日期部分來計算。

【解法】

因為 Excel 會將一天視為 1，而1/24為1小時，1/24/60為1分，1/24/60/60為1秒。因此，如果只想要取得日期部分，則只要透過 INT 函數(取不大於的最大整數)即可達到。

儲存格E2：=(D2-B2)/(C2-A2)

在 Excel 中取得一個學生的分組名單(如下圖)，現在要依組別摘要班級和姓名的資料(如下下圖)，該如何處理？

摘要後的組別資料：

【準備工作】

先將資料範圍定義名稱：

在 Excel 中有機會使用陣列公式，但是對些人而言，陣列公式似乎不容易理解。會寫程式的人應該對「陣列」不會陌生，陣列是一塊記憶體，藉由存取陣列，即可使用記憶體中的內容。參考以下的範例(參考下圖)：

【常數陣列】

(1) 儲存格C2：=SUM({1,2,3,4,5,6,7})

{1,2,3,4,5,6,7}：以「{ }」含括的為陣列，以「,」隔開元素。{1,2,3,4,5,6,7}並沒有放在儲存格中，而是直接由記憶體取出來運算。

在 Excel 中，如果想要設計一個四則運算練習(以加法為例)的工具，該如何處理？(參考下圖)

 【設計規格】

1. 尚未輸入計算結果時，D欄顯示為空白。

2. 使用者於C欄輸入計算結果，如果答對，則在D欄顯示「正確」，如果答錯，則顯示正確的運算式。

3. 以一題5分，來計算得分。

4. 使用只能於C欄輸入資料，其餘欄位無法刪除，而選取D欄中儲存格時，不會顯示公式。

在 Excel 中的一個常見資料表(如下圖)，如果在12列和13列之間插入一列輸入資料，平均值會變成如何？

【原始內容】

儲存格B13：AVERAGE(B2:B12)

儲存格C13：AVERAGE(C2:C12)

儲存格D13：AVERAGE(D2:D12)

儲存格E13：AVERAGE(E2:E12)

有網友問到，在 Excel 中取得一個成績表(參考下圖右)，想要分析這些班級高(等)於平均和低於平均的人數分別為多少，該如何處理？希望能使用定義名稱的方式來操作。

【準備工作】

先定義要用的「名稱」。選取儲存格A1:F26，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，分別定義了：「座號、三年1班、三年2班、三年3班、三年4班、三年5班」等名稱。

【輸入公式】

儲存格I2：{=SUM(IF(INDIRECT(H2)>=AVERAGE(INDIRECT(H2)),1,0))}

以下的例子來練習陣列的觀念。在 Excel 的工作表中要將多個儲存格中的數字分離出來並加總，該如何處理？(參考下圖，每個數字前有7碼的文字)

【方法一】

儲存格A8：=SUMPRODUCT(RIGHT(A2:A6,LEN(A2:A6)-7)*1)

因為 SUMPRODUCT 函數即以陣列觀念在處理，檢視下圖可以發現：