學不完．教不停．用不盡

常有人會問到在 Excel 中以日期為主的每日或分組的數值計算，例如：計算最近幾筆和最近幾天的每日平均，或是計算幾筆和幾天的日期區間的分組平均。本篇就來看看這樣的差異，如何處理？

1. 計算最近幾筆和最近幾天的每日平均

參考下圖，要計算以 8 天為單位來計算最近幾筆和最近幾天的每日平均。

(1) 計算最近 8 筆的每日平均

儲存格D9：=AVERAGE(C2:C9)

最近整理了幾篇和二進制轉換有關的先前文章，請自行點選檢視。

(1) Excel-二進制和十六進制互轉

(2) Excel-以2的補數模擬二進制的減法

在使用 Excel 時，「設定格式化的條件」是個很有用的工具，經由一些巧妙的安排，讓公式產生的結果能更添美化和趣味，以下例舉 6 個以前寫過的應用，拿出來複習一下。

(1) Excel-利用格式化條件設定顯示16X16點陣字

(2) Excel-BMI值計算(格式化的條件規則)

有網友問到：如下圖的 Excel 資料表(右圖)，如何將「報名」欄為「Yes」者取出摘要成左圖的結果，其中每個學員的 Email 和電話分成二列呈現。

為了說明方便，選取F欄中所有「報名」欄的內容，定義名稱：報名。

【公式設計與解析】

1. 列出學員姓名

以前的一位老同事最近問到一個常見的問題：如何在 Word 中插入圖片時，能自動調整圖片大小以符合表格的欄位寬度？

以 Word 2010 為例，下圖是一個 2X2 的表格，想要在這個表格中插入四張圖，可是四張圖片的大小並不相同，希望能插入圖片時，不要影響表格的欄位寬度，該如何處理呢？

在預設狀態下插入圖片時，會改變儲存格的大小。

要解決這樣的問題，可以先調整好你要的表格、欄位的大小，然後選取表格，再選取[表格工具／版面配置]功能表中的[儲存格大小／自動調整]選項中的「固定欄寬」。

有位老師問到：

如果在 Excel 中每一個月有不同內容的多個工作表，其內容是某些課程的時間、地點和攜帶物品的明細表。如何能在輸入某個月份時，即能列出該月份的各個課程之時間、地點和攜帶物品，以方便提醒每位小朋友呢？

【公式設計與解析】

關於這個問題，因為老師想要將各個月的課程內容放在不同的工作表，所以依老師的想法設計公式。

網友問到在 Excel 中有一個如下圖(上)的一個基本資料表，如何藉由輸入發票號碼，來篩選出同一發票的所有客戶資料？(參考下圖(下))

【公式設計與解析】

1. 查詢客戶編號

儲存格C19：=OFFSET(B1,MATCH(A20,A2:A17,0),,,)

網友在 Excel 中有一個資料表如下圖中的A欄和B欄，想要根據項次和數值的內容，將合計不超過某一數值者(本例為：300)分成一組，並且依項次由小至大分組。其次，再將各組的項次列出。以上兩個問題，該如何處理？

觀察下圖，項次 A01~A06 的合計為 285，若再加上 A07，會超過 300，所以將A01~A06 分在第1組。而 A07 為第 2 組的第 1 個，A07~A11 合計為 222，若再加上A12 的 96，會超過 300，所以將 A07~A11 分為第 2 組，依此類推。

【公式設計與解析】

本例以累不超過 300 者為一組。

在網路上看到一篇 Excel 的文章，覺得可以分享給有需要的人。

觀察下圖，這是一個由每個月最後一天所集合而成的報表所繪製出來的折線圖。然而，9/30、10/31、11/30、12/31 因為是推測值(時間是未來時間)，所以想要用不同色彩來呈現，以凸顯這是推測的區域，該如何處理呢？

參考步驟：

下圖是一個依資料表做成的折線圖，其中數列構成的折線圖是單一色彩，無法顯示二種不同色彩。

你知道在 Excel 中要輸入函數有那些方法嗎？對於初學者，可能要由瀏覽函數開始慢慢找到想要的函數，漸漸的可以把函數名稱背下來直接輸入。有時候會面對少用而陌生的函數，該如何取得提示和說明呢？

(1) 瀏覽函數來輸入

使用[插入函數]按鈕，當你選取一個儲存格後，按一下 fx，可以開啟[插入函數]對話框，透過瀏覽函數的方式來找到並且輸入函數。你也可以按下 Shift+F3 鍵，來開啟[插入函數]對話框。

有學校同仁問到：如何在下圖中 Excel 的儲存格輸入資料，能由左而右、由上而下的順序輸入資料？

在 Excel 中的儲存格輸入資料，按下 Enter 鍵，預設跳到「下一列同一欄」的儲存格，而按下 Tab 鍵，預設跳到「同一列下一欄」的儲存格。由於在工作中，常常是一張紙填寫一筆資料(Record)，所以輸入時會同一列的內容輸完才會換到下一列，因此依向左而右、由上而下的順序在儲存格中輸入資料是有其必要性。

如果你選取[檔案／選項]功能表，在「進階」標籤下的[編輯選項]區中，可以設定按 Enter 鍵後，移動選取範圍的方向，預設值為：下。

如果你在一個儲存格中輸入資料後習慣按 Enter 鍵，則你可以在此更改為方向：右，並且在輸入前先選取要輸入資料的儲存格範圍。如此，便可以依你的習慣來輸入資料。

這是回應學校老師的一個練習範例。參考下圖，這是一個常見的成績表，如果想要計算每個區間中的各科平均該如何處理？如果計算各科平均時，想要依某個加權來計算總平均，又該如何處理？

先觀察上圖，每個區間的次數並不相同，在此要自動產生各個科目在這個區間的「平均」，並且計算各科的「加權平均」。(如下圖，還能以群組概念檢視資料)

參考以下的做法：

1. 選取儲存格A1:G20。

在 Excel 中，如果想要依據一個包含 4 個區域、5 種費率的對照表(如下圖左)，依不同重量來自動計算金額(如下圖右)，該如何處理？

本例想要依區域和重量，自動對應出費率，才能試算金額。

【準備工作】

定義以下四個名稱：

北：=工作表1!$B$2:$C$6

在你閱讀本篇之前先告訴你 Excel 2021 版有新的做法：

Excel-根據單條件和雙條件篩選資料(FILTER)

先前的二篇 Excel 文章，都是使用「陣列公式」來處理：

Excel-運用雙條件從資料表摘要資料(OFFSET,INDIRECT,SUMPRODCUT)

Excel-藉由定義名稱轉換表格來摘要資料(陣列公式,OFFSET,INDIRECT)

這次不要使用陣列公式，而是使用 SUMPRODUCT 函數來運算。在下圖右的上下二個摘要表中，上半部是依據一個條件來篩選資料，下半部是依據二個條件來篩選資料。請自行對照以上二篇文章比較其差異。

唸國中的兒子上數學課時，數學老師提到費氏數列(Fibonacci number)，回家和我討論到這件事。我想用 Excel 建構這個數列給他看。

參考：https://en.wikipedia.org/wiki/Fibonacci_number(以下部分圖片取自該網頁)

數列：0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, ...

根據公式，這個數列其實就是由前二個的數值的和所構成，因此公式很簡單：

在 Excel 中，樞紐分析表／樞紐分析是非常重要的功能，特別為學生整理出網站中數篇和樞紐分析表／樞紐分析圖有關的文章。

以下有四篇先前寫過和萬年曆有關的文章，不同的日期產生方式和呈現格式，要運用公式，也要使用設定格式化的條件功能，大家再自行比較一下。

(1) Excel-建立連續日期的月曆表

(2) Excel-萬年月曆(WEEKDAY,DAY,DATE)

最近在整理資料時，發現幾篇很久以前的文章，拿出來玩味一下，發現以前還蠻熱血的！會去想這些可能自己用不到，但是可以訓練提升自己的程度。以下列舉幾篇，有興趣的人自行由標題點閱了！

(1) Excel-畫出特殊統計圖形

有網友問到：給予一個預定的日期，其前10天為警示日期，如何在警示日期到達的那天，自動顯示紅色予以警示？

當在儲存格C2中輸入日期時，儲存格B2要顯示其前10天的日期，所以：

儲存格B2：=C2-10

複製儲存格B2，往下各列貼上。

接下來要處理警示日期到來時要顯示紅色：

有網友問到：在 Excel 中如何輸入年和月的數值後後，自動產生該月的所有日的數值和對應的星期幾？

【公式設計與解析】

(1) 儲存格B2：=IF(ROW(1:1)<=DAY(DATE($A$2,$A$4+1,0)),ROW(1:1),"")

DAY(DATE($A$2,$A$4+1,0))：根據儲存格A2(年)和儲存格A4(月)，求出該月的日數。