學不完．教不停．用不盡

網友根據下圖左的 Excel 資料表，想要列出如下圖右的摘要結果，該如何處理？

在下圖左的資料表中每個人員可以參加 3 個組別(沒有重覆)，要如何列出每個組的人員清單(如下圖右)？

【公式設計與解析】

儲存格E2：{=OFFSET($A$1,SMALL(IF(($B$2:$D$23=F$1),ROW($B$2:$D$23),

有網友問到：在 Excel 中的 SUMIF、SUMIFS、SUMPRODUCT 函數有其共通之處，在使用時如何互相取代？如果使用 SUM+IF+陣列公式，又是如何互相取代？

參考下圖的資料表，運用 SUMIF、SUMIFS、SUMPRODUCT 函數及使用 SUM+IF+陣列公式來計算特定條件下的數量小計和金額小計。

以下用四個例子來對照 SUMIF、SUMIFS 及 SUMPRODUCT 函數及使用 SUM+IF+陣列公式。

為方便說明，先選取B1:D23，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：單價、數量、金額。

陣列公式輸入完成要 Ctrl+Shift+Enter 鍵，Excel 會自動產生「｛｝」。

網友問到：在 Excel 中如下圖的資料表中，如何在挑選日期後自動計算每日的數量總和？

參考下圖，當指定一個項目時，希望能計算各日的總和；當指定一個日期時，希望能計算所有項目的總和，該如何處理？

【公式設計與解析】

1. 為儲存格範圍定義名稱

有網友問到在 Excel 中跨工作表使用SUMPRODUCT函數的問題。參考下圖：

假設在工作表3的 SUMPRODUCT 函數要使用工作表1和工作表2的內容來運算。

在 SUMPRODUCT 函數中參照其他工作表的位址是可以的，只要儲存格範圍的對照是正確的即可。如下公式：

工作表3的儲存格A2：=SUMPRODUCT((工作表1!$B$2:$B$11>5)*工作表2!B2:B11)

如果你覺得公式太長不好處理，可以參考以下的做法：

網友問到：在下圖左的 Excel 工作表中有一些廠商和產品的需求量，其中有一些是重覆的項目，如何將其轉換為下圖右的摘要統計表？

在下圖中，轉換過程中，空白列也一併消除了。該如何建立這個報表？

網友詢問該用何公式來處理？我也還沒想出來。但是使用樞紐分析表，幾分鐘即可以輕易做出結果。

參考以下的步驟：

1. 選取儲存格A1:C20，建立樞紐分析表。

網友問到的 Excel 問題：如下圖左有一個人員的排班基本表，其中每個日期分為二列，分別是早班和晚班的人員輪值名單，現在要轉換為下圖右的人員和日期的彙整表，該如何處理？對照

【公式設計與解析】

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

儲存格I2：=OFFSET($B$1,SUMPRODUCT((OFFSET($C$1,MATCH($H2,

網友問到實用且簡單的問題：在下圖的 Excel 工作表中，如何將報到日期欄位尚未輸入資料者，整列以不同底色標示？

參考以下的步驟：

1. 選取所有的資料，本例為儲存格A2:D26。

2. 在「設定格式化的條件」中新增一個規則。

規則類型：使用公式來決定要格式化哪些儲存格

有同事問到一個簡單卻常見的問題。在 Excel、Word 中，我們常會利用各種捷徑、超連結、釘選等功能來達到快速開啟文件，但卻常忽略了這份文件的實體位置，而在檔案總管中要切換至文件所屬資料夾，總覺得要想很久文件放在那個資料夾，或切換很多層資料夾才能到達文件所在位置。

如何才能在檔案總管中，快速開啟某一文件的所在資料夾呢？

以 Excel 2013 版本為例，其「資訊」頁面提供了解答！

當你開啟了一份文件後，切換至[檔案]功能表的「資訊」面頁下。你會在檔案名稱之下看到檔案的儲存格位置，點選這個位置就會顯示快顯功能，點選「開啟檔案位置」，即會在檔案總管中開啟這個資料夾。

或是在視窗右下角的[相關文件]區中也可以點選「開啟檔案位置」，快速在檔案總管裡進入這份文件的資料夾。

網友問到：在 Excel 工作表中有一個社團選取結果的資料表(如下圖左)，如何才能分社團／分星期的列出各個報表？

例如：在下圖中有多個班級的選社結果，其中星期一至星期五，每個學生每天都有一個要參加的社團，如何能分別依各星期和各社團列出社團人員的清單？

【公式設計與解析】

1. 定義儲存格範圍名稱

選取D欄至H欄中有資料的範圍(例如：儲存格D1:H200)，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：星期一、星期二、星期三、星期四、星期五。

這篇文章雖然簡單，但是有感而發。因為常看到同事在已寫好的公式又新增資料，造成一些調整公式上的困擾。

在 Excel 中寫好的計算公式，如下圖：

一旦新增了某些資料後，還得要自行更新某些公式，是否有些較方便的方式？

(1)

網友提問 Excel 的問題：想要統計公司裡的三間工廠於一年中各個月的離職人數，該如何處理？

如下圖，有一個日期清單，在工廠欄位中有A廠、B廠、C廠三種可能，如何能建立分月／分廠的分析報表？

【公式設計與解析】

1. 定義儲存格範圍名稱

網友在下圖的 Excel 工作表中，想要根據圖上的A欄條件，要在B欄顯示一個結果，該如何處理？

如下圖，共有四個條件，四個結果，假設A欄小於或等於 500 時，結果預設為 0。

【公式設計與解析】

(1) 使用 IF 函數

網友問到 Excel 的問題，如下圖一組分成 2, 3, 4, 6, 7, ... 個，如何計算分組後的小計(加總。

以下圖為例，每 2 個一組時，第01組為儲存格A2:A3、第02組為儲存格A4:A5、...，如何自動計算各個分組的小計？

【公式設計與解析】

儲存格D2：=SUM(OFFSET($A$2,(ROW(1:1)-1)*D$1,0,D$1,1))

網友想要在 Excel 工作表中來計算一個月的工作天，其中要計算不含星期日和不含星期六、日的工作天，分別為多少？

如下圖，本例提供二種不同的計算方式。其一：列出該月的每一天；其二：只列出某年某月，即可計算工作天數。

【公式設計與解析】

(1) 計算不含星期六日的工作天數(使用日期清單)

儲存格E2：=SUMPRODUCT(1*(WEEKDAY(A2:A32,2)<6))

網友問到：如下圖，根據儲存格A2和儲存格B2的內容來決定整欄的色彩。

當A2>B2時，A欄為淺綠色、B欄為粉紅色。

當A2<B2時，A欄為粉紅色、B欄為淺綠色。

1. 如下圖，預設A欄色彩設定為淡綠色，預設B欄色彩設定為粉紅色。

學校同仁問到 Word 合併列印時遇到的問題，如下圖的主文件設定(錯誤)，通常我們會使用 Next Record 指令，用以在一頁中合併多筆資料(本例中每 1 頁有 4 筆記錄)：

得到的結果，每 4 筆就跳過 1 筆，問題出在那裡？(座號 5 和座位 10 未被列出)

因為 Word 在列印時會在下一頁時即會自動列印下一筆記錄，因此在設定主文件時，最後一筆記錄之後，不需再加上 Next Record 指令。

在 Excel 中的個資料表，如下圖，在A欄中的項目有許多是重覆的，如何計算各個不同項目的四個欄位資料的總和？

例如：在『甲』項目在第5,6列，要計算數值1～數值4的總和。

【公式設計與解析】

儲存格H2：=SUMPRODUCT(($A$2:$A$22=G2)*$B$2:$E$22)

$A$2:$A$22=G2：在儲存格A2:A22中判斷是否和儲存格G2相同，傳回 TRUE／FALSE 陣列。

有網友問到：在 Excel 的工作表中，每三列中只有第二列有資料，如何複製第二列的資料至第一列和第三列中？

如下圖，儲存格A2有資料，要將儲存格A1和儲存格A3複製儲存格A2的資料，同欄依此類推的操作，該如何處理？

【公式設計與解析】

1. 原始資料在A欄，在C欄中顯示處理後的結果。

有網友想要在 Excel 的兩個工作表中，使用一個 VLOOKUP 函數查詢，該如何處理？

就我的理解，VLOOKUP 函數無法使用在跨工作表中，所以必須稍加調整公式才能查詢結果。

例如下圖中，工作表1和工作表2含有相同資料範圍但是不同內容的資料，如果想要在一個儲存格中完成查詢的工作表該如何設計公式？

【公式設計與解析】

網友問到：在 Excel 中如何為儲存格內容加上編號？

如下圖A欄為原始內容，在B欄和C欄分別加上不同格式的編號。

【公式設計與解析】

(1) B欄編號格式(例舉三種)

儲存格B2：=ROW(1:1)&"."&A2