學不完．教不停．用不盡

網友問到在 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(月)，求出該月的日數。

在 Excel 中有一個各班的成績表，有老師想要從成績總表摘要出各班總分最高分且數學大於或等於90分者，該如何處理？(本例假設總分無同分者)

【準備工作】

選取儲存格A1:G25，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，建立名稱：班級、座號、姓名、國文、英文、數學、總分。

【公式設計與解析】

在 Excel 中有一個運動會報名表的某一種格式(參考下圖)，其中 F 代表男生、M 代表女生。每一種項目男生和女生分別最多二人報名(0、1、2人皆可)，如何根據這個資料表來產生摘要表？(參考下下圖)

下圖是要產生的摘要表，為了說明方便，特別建立了一個輔助欄位(H欄)。表中項目和性別是固定不變的內容。

【準備工作】

在 Excel 中如果要將多個資料表合併成一個(藉由各種運算)，你可以試試「合併彙算」和「樞紐分析表」。

例如，以下圖中三個資料表的格式是一致的。

(1) 使用合併彙算

請你先選取要儲存合併彙算結果的儲存格(本例為：儲存格G7)，當我們選取[資料／合併彙算]功能時，在[合併彙算]對話框中，先選取要計算所使用的函數，本例選取「加總」。然後選取你要彙整的儲存格範圍(參照位址)。

有網友問到：在 Excel 的工作表中有一個資料清單，如下圖的A欄和B欄。如何摘要結果如C欄和D欄？

小計欄位要計算資料清單前幾碼相同者的數值總和，其中，類別欄位(C欄)為標示A欄的前三碼在第一次出現時顯示，其餘以空白顯示，而小計欄位(D欄)則根據C欄的結果，將相同前三碼者之B欄數值予以加總。該如何處理？

【公式設計與解析】

儲存格C2：=IF(LEFT(A2,3)=LEFT(A3,3),"",LEFT(A2,3))

如果本列的前三碼和下一列的前三碼相同者，顯示空白，否則顯示前三碼。

當在 Excel 的工作表中要建立一個新的樞紐分析表，可以有那些方式？如果以 Excel 2013 為例，在[插入]功能表中就有：樞紐分析表和建議的樞紐分析表可以選擇。

如果你選取：樞紐分析表，則會進入樞紐分析表的版面配置，在此你可以「手動」組織你的樞紐分析表。

如果選取：建議的樞紐分析表，則可以由建議清單中選取其中之一，Excel 會「自動」依此版面建立這個樞紐分析表。

有網友問到在 Excel 中有一個如下圖左的資料表，如果在儲存格E2中輸入某些字根，要自動列出含有這些字根的清單(如下圖左)。該如何處理？

【準備工作】

選取基本資料表的所有儲存格，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：序號、英文、中文。

【公式設計與解析】

儲存格G2：｛=IFERROR(SMALL(IF(ISNUMBER(FIND($E$2,英文)),