學不完．教不停．用不盡

回答網友提問：在下圖中左有一個 Excel 的資料表，如何取出指定日期的所有資料？

在下圖中的「預製日」欄位是一個日期清單，現在我們要在儲存格G1中輸入一個日期，在下圖右列出預製日是該日期的所有資料清單，該如何處理？

這類題目，已是我接觸的問題中，最常被問及的。可見需求很高，所以再次不厭其煩的解說，希望能對網友有幫助。

【公式設計與解析】

首先，為了讓公式易讀好懂，先選取儲存格A1:A31，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：預製日。

網友問到 Excel 的問題：如下圖，當資料超過 10000 筆時，想要將一列轉換為三列時，該如何處理？

參考下圖，資料由一列轉三列時，依其色彩放在不同的位置上。

參考以下的公式：

【公式設計與解析】

網友問到在 Excel 中，如果想要根據輸入的一個數值，顯示對應的結果，該如何處理？

參考下圖，輸入和輸出對照如下：

●1：Test Passed

●2：Test Failed

●3：Test Not Applicable

這是一個多選一的輸出結果，並且為輸入錯誤給予錯誤訊息。

同時有二個讀者問到類似的問題。在下圖左是一個 Excel 清單，如何計算日期區間中各個項目的數量小計(如下圖右)？

【公式設計與解析】

選取儲存格A1:C29，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、項目、數量。

儲存格F5：=SUMPRODUCT((日期>=$F$1)*(日期<=$F$2)*(項目=E5)*數量)

讀者問到：在下圖左的資料清單中有四個字元，分別被標示多個數字，該數字是其位置的順序。如何將其轉換為下圖右依其位置填入字元？

下圖左之中的所有數字因代表位置，所以均不會重複。

【公式設計與解析】

儲存格G1：

=OFFSET($A$1,SUMPRODUCT(($B$1:$D$4=F1)*ROW($B$1:$D$4))-1,0)

在下圖的 Word 文件中，若是已經有對章、節、小節等標題結構設定了樣式，並且有文字內容，要如何取出文件中的大綱標題？

如果你開啟了『功能窗格』，便可以清楚看到該文件的標題結構：

先查詢一下內容文字的樣式(此例為「內文」樣式)。

查詢得知「內文」樣式的大網階層為『本文』。其他(章、節、小節)使用大網階層1, 2, 3。

網友問到一個 Excel 公式運算的問題：如下圖，如何求得在『配編』欄位中各個年月的配編數(幾種不一樣的類別)？

在下圖中，年月是10501者，有 1, 2, 3, 4 共四種配編類別，該如何求得？

【公式設計與解析】

儲存格G2：

有些人開始使用 Google 雲端硬碟的 Google 試算表來取代 Excel，或是你想要發佈一個試算表文件時，透過 Google 試算表也很方便。本文要來看看如何在 Google 試算表中製作一個圖表。

在 Google 試算表中，當你要建立一個圖表時，不妨可以由 Google 提供的「探索」來開始。以下圖在 Excel 中的一個資料表為例：

將這個檔案上傳至 Google 雲端硬碟並開啟(如下圖)，按一下視窗右下角的「探索」。

在[探索]窗格中，由「格式設定」區中點選一種格式設定，資料表隨即會套用這個格式：

有網友問到：在 Excel 中有一個資料清單(如下圖左)，如何轉換為表格形式(如下圖右)？

在下圖左的資料清單是由類別和項目組成，在下圖右的表格中將相同的類別的項目集合在一起，該如何設計公式？

【公式設計與解析】

為了幫助公式理解，請先選取儲存格A1:B25，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：類別、項目。

在 Excel 中，如果你的工作表裡含有一些合併的儲存格，當你在這些儲存格中進行搜尋動作時，有時會遇到當機現象。你碰到過嗎？

如下圖，要在某些已合併的儲存格中搜尋含『Data』的儲存格，目前有四個儲存格含有『Data』。『Data』資料雖然分佈各欄中，但是列號分別有重疊。

當你開始搜尋動作時，只看到在名稱方塊中有不斷跳動的儲存格名稱，現在已經進入當機的狀態了。雖然你可以按 Esc 鍵，來終止尋找動作，但無法跳出當機狀態。

是否有方法來避免在含有合併儲存格中搜尋資料引起的當機現象？我的做法是在搜尋對話框中的進階選項，將『循列』改成『循欄』，即可解決這個問題。

網友問到：如下圖，有一個 Excel 工作表的資料清單，如何找出儲存格中含有『助理』二個字？

1. 使用 SUBSTITUTE 函數

儲存格B2：=IF(SUBSTITUTE(A2,"助理","")<>A2,"助理","")

複製儲存格B2，貼至儲存格B2:B15。

網友問到：在 Excel 中有一個資料表(參考下圖)，如何由數值內容反推欄／列的標題？

例如：在儲存格J2中指定一個數值，要找出其人員為：『戊』，月份為：『三月』。.

【公式設計與解析】

1. 使用 SUMPRODUCT 函數

找出列標題：

有網友問到：在 Excel 的工作表中有一個數值清單，如何取出固定間隔列的數值予以加總？

參考下圖，如何取出間隔 1, 2, 3, 4, 5, 6, 7, 8, 9 列的數值來加總？

【公式設計與解析】

儲存格E2：=$B$2+SUMPRODUCT((MOD(ROW($A$3:$A$25)-2,ROW(2:2))=0)*$B$3:$B$25)

複製儲存格E2，貼至儲存格E2:E10。

網友問到在 Excel 中如何重新排列資料，例如下圖由六欄轉換為一欄，其中會使用的函數有：OFFSET、INT、MOD、ROW、COLUMN等。

以下例舉五種資料重組的樣式來練習：

(1) 六欄轉換為一欄

儲存格A6：=OFFSET($A$1,MOD(ROW(1:1)-1,2),INT((ROW(1:1)-1)/2))

參考下圖，在 Excel 的工作表中有多個儲存格，每個儲存格中有一個字，如何找出每『X』字元的位置，進而找出每個『X』字元的間隔距離？

【公式設計與解析】

儲存格D4：=FIND("X",PHONETIC($A$1:$Z$1),D3+1)

(1) 先利用 PHONETIC 函數將多個儲存格串接成一個字串。

(2) 再透過 FIND 函數來搜尋字元在字串中的位置。

本篇是針對校內教師 Excel 研習課程使用的一個範例做說明。

在下圖中，有個學生多次小考的成績表，每次小考設有「加權」，本次要練習表單控制項在學生成績方面的處理，並且練習計算學生的「加權平均」成績。

1. 使用微調按鈕選取不同次別所有學生考試成績和統計圖

如下圖，你可使用微調按鈕來選取不同次別的考試成績，並且做成統計圖表。

網友問到：在 Excel 的資料清單中，如何用公式篩選符合條件者？

參考下圖左，是一個『日期、編號、評語』的清單，現在要根據一個『編號』值，篩選出符合該編號的資料內容(日期和評語)，該如何處理？

【公式設計與解析】

選取儲存格B1:B26，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：編號。

儲存格E3：

讀者提問：下圖是 Excel 的資料表，如果在綠色區域中的S1~S8欄位中，根據藍色區域中的 S 對照 T 來列出橙色區域中的value。

例如：第2列中的S8位在T3欄位，查表得到T3=0.8，將其填入儲存格E2。

儲存格E2：=IFERROR(OFFSET($B$12,MATCH(E$1,$A2:$D2,0)-1,0),"")

複製儲存格E2，貼至儲存格E2:L9。

(1) MATCH(E$1,$A2:$D2,0)

網友問到一個很實用的問題：在 Excel 的資料表中，有部分欄位缺漏資料，如何挑出這些缺漏的記錄，以方便後續處理？

在下圖左的資料清單中含有四個欄位，其中姓名沒有缺漏，而性別、生日、餐食等有部分缺漏，在此要以「進階篩選工具」來挑出含有空白內容的記錄。

做法很簡單，參考下圖左儲存格F1:I4的內容，請先輸入：

儲存格G2：『<=""』；儲存格H3：『<=""』；儲存格I4：『<=""』。

再進入「進階篩選」(選取[資料／排序與篩選]功能表區中的「篩選」)，設定：

在 Excel 中，在下圖中有類別和項目的清單，要如何才能產生不重覆的排列組合結果(參考下圖右)？

在下圖左中，有類別：甲、乙、丙、丁，項目：忠、孝、仁、愛，要產生其不重覆的排列組合結果，該如何處理？本篇將利用二種方法來處理。

1. 使用公式

(1) 類別欄位