學不完．教不停．用不盡

在 Excel 的工作表中建立了一個學生的成績表，常會有老師在進行成績分析時，會需要用條件來篩選想要的結果。例如下圖中，要求「國文>80且英文>75者的數學最高分」和「五科均及格者的英文最高分」。

如果你用篩選工具來操作，面對條件較多時的操作將會煩瑣且修改不易，還是以公式來設計較為妥當。

【準備工作】

選取儲存格B2:F26，按一下 Ctrl+Shift+F3 鍵，定義名稱：國文、英文、數學、社會、自然。

在 Excel 中有一個成績的基本資料表，一般我們會使用樞紐析表來呈現統計結果。如果你在樞紐分析表中想要篩選部分資料來比對，要操作的步驟比較多，而你如果使用「交叉分析篩選器」來做各種交叉比對的工作，使用上會比樞紐分析工具來的方便。

以下圖的資料表為例，其中有500筆學生資料，含有性別、住家、夜間讀書方式、平均成績等欄位：

當你產生了像以下的樞紐分析表：

如果想要單獨列出女生和中壢地區的比較，你必須先點選「性別」，再取消勾選「男」，然後選取「住家」，只留下勾選「中壢」：

在 Excel 中常有一些貼心的轉換功能，觀察在下圖中展現的日期較換功能：

1. 在A欄中輸入月名。(在儲存格A2中輸入jan，接著以自動填滿方式產生儲存格A2:A13。)

2. 在B欄中輸入日數。

3. 在儲存格C2輸入公式「=A2&B2」。(串接儲存格A2和儲存格B2，此時的資料型態是文字。)

4. 在儲存格D2輸入公式「=VALUE(A2&B2)」。(將儲存格A2和儲存格B2串接的文字轉成數字。)

5. 將上述的數字在儲存格格式設定中設定為日期。(其中自動以當年的年度(例如2013)為年份。)

一位網友問到：在其工作表中的一個公式，在操作時發生了錯誤結果，覺得很不合理。

先來觀察其操作結果。

儲存格A2：=TEXT(B2,"yymmdd")&TEXT(COUNTIF($B$2:B2,B2),"0000")

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

操作後卻得了錯誤的結果：(儲存格A3應為1303070002)

在 Excel 中，你可以使用「資料驗證」功能達到下拉式清單的效果，如果你使用表單中的「下拉式方塊」控制項也可以做到下拉式清單效果。

現在，我們要來在一個日期和金額的資料表中，以下拉式清單來篩選某個月份的資料，篩選出來月份的日期，以不同儲存格底色呈現。(參考下圖)

參考以下做法：

1. 選取[開發人員/控制項]中的「插入」，新增一個「下拉式方塊」控制項。(置於儲存格D1位置)

在 Excel 的工作表中日積月累建立了一個成績表(如下圖)，但是在最後時若是只想要篩選某些項次的成績來處理，每次都要修改公式，實在很煩人！該如何處理呢？

在下圖中的成績表如果想要篩選其中要計算的項目，可以利用表單中的「核取方塊」來進行篩選的動作，而計算總和和加權平均的公式，則會依篩選的項次來決定計算的內容。

參考以下的步驟來操作：

1. 選取［開發人員/控制項］中的「插入」，再選取「核取方塊」項目。

2. 在儲存格B2位置中新增一個核取方塊，並輸入文字「採計」。

在 Excel 中有一個工作表含有日期數列，如果想要以一個表單的核取方塊工具來標示星期六日，該如何處理呢？

因為表單的核取方塊會產生二種結果：勾選(True)、不勾選(False)，將其套用於設定格式化的條件中，即可以勾選核取方塊的方式，來切換標示/取消標示星期六日。

參考以下的做法：

1. 選取［開發人員/控制項］中的「插入/核取方塊」項目。

2. 在工作表中拖曳產生一個核取方塊，輸入文字：標示星期六日。

在 Excel 的工作表中，有人想要將一個經年累記錄而成的直式報表轉換成矩型的報表(參考下圖)，以方便呈現(資料表不會因長而不易顯示)或是製作統計圖表等。

或許你會認為以剪貼方式也很快可以重組資料，但是如果資料更多時將會不利於處理，使用公式來處理可以一勞永逸。而本例將不以相關查表函數來處理，而是要藉用 SUMPRODUCT 函數來執行查表的結果。

【準備工作】

選取資料範圍，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：年份、月份、收益。

下圖的 Excel 例子多年前曾經用過，可以用來學習在公式中使用：名稱和資料驗證清單，使用 VLOOKUP 和 INDIRECT 函數來執行查詢的動作，可以讓公式簡捷可用。

我們要來設計使用下拉式清單來選取年級別和成績別，並能自動傳回對應的比重，因為查詢內容分佈在三個資料範圍中，該如何來撰寫公式呢？

(1) 建立名稱

選取［公式/名稱管理員］按鈕，在[名稱管理員]對話框中新增：

名稱：一年級，參照到：儲存格B5:C9。

常會在報表中遇到一串數列中含有一些 0 的數值，而這些 0 源自於某些資料轉換時所產生，如果要求取這堆資料中的最小值，但又不是這些 0 的數值時，該如何處理？這時，你需要使用到陣列公式！

以下圖的數列為例，其中有一些儲存格含有 0。

儲存格C2：{=MIN(IF(A2:A22>0,A2:A22,FALSE))}

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

IF(A2:A22>0,A2:A22,FALSE)：找出數列中大於 0 的儲存格陣列，再藉由 MIN 函數求取其中的最小值。其中參數不可改成 0 或是留空白，否則結果永遠會得到 0。