學不完．教不停．用不盡

網友想要在 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

網友問到：如下的 Excel 資料表，如何從表格中找出最大值，並且傳回其欄、列名稱？

如下圖，欄標題：甲、乙、…、癸，列標題：子、丑、…、亥。假設資料中的數值，其中沒有重複的內容。

【公式設計與解析】

選取儲存格B2:K13，定義名稱：DATA。

有網友根據下圖左 Excel 的基本資料表，其中包含日期、項目和數值的清單，而項目是由多個不固定的內容所組成。若要轉換成下圖右的資料表，分別依內容和月份計算數值總和，該如何處理？

【公式設計與解析】

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

日期、項目、數值。

網友問到一個 Excel 的問題，是關於計算累進的乘積和。參考下圖，其中有一個數值區間和比重，如果在儲存格H2輸入一個數值，例如：750，而此數可以分解為：

750=100+100+200+200+100+50

再將每個區間的數量乘以比重：

100*6%+100*6%+200*3%+200*2%+100*5%+50*1%=27.5

輸入：750→輸出：27.5。

延續前一篇文章：Excel-郵遞區號查詢(VLOOKUP,OFFSET,SUMPRODUCT)

如下圖，若在儲存格E3中選取某一個縣市(例如：新北市)，如何能自動列出該縣市的各區名稱？本篇要改良前一篇的公式。

選取儲存格A2:C63，按 Ctrl+Shfit+F3 鍵，勾選「頂端列」，定義名稱：

郵遞區號、縣市、區。

再來，要設定一個名稱：完整區名。並設定其參照到：

網友問到如何在 Excel 中查詢郵遞區號的問題。首先下載郵局提供的郵遞區號對照表：

http://www.post.gov.tw/post/internet/Download/default.jsp?ID=22

稍加整理後，即可用於查詢。(如下圖的A,B,C欄)

【公式設計與解析】

如下圖的 Excel 資料表，其中的表格內容是散亂的資料分佈。要如何取出表格有資料的部分重新排列？

下圖左為原始資料，下圖右為重排後的結果。

【公式設計與解析】

1.

先建立一個輔助欄位，用以計算資料表中每一列有內容儲存格的數量。

網友根據下圖的 Excel 資料表，如何才能求得表一、表二、表三的結果。

【公式設計與解析】

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

條件一、水果、條件二。

有網友問到如下圖的 Excel 資料表中，如何計算各科的加權平均？

下圖中，每個科目都有一個加權和一個分數，要找出各科的加權平權。

【公式設計與解析】

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

在 Excel 中如果你想要將資料清單中重覆者挑出來並移除，最方便的方式如下：

先選取資料範圍。(本例為儲存格A1:B20)，再使用[資料／資料工具]功能表中的「移除範圍」指令。

Excel 會詢問要列入檢查重覆的欄位：

移除重覆後，會顯示找到幾個重覆，而保留了幾個唯一值。

有同仁問到：在 Excel 中如果開啟 Google 表單下載之填答記錄(如下圖)，當在執行『排序』時，其中有一個條件要根據時間戳記來排序，卻遇到了錯誤結果。

如下圖，當使用「篩選」功能中的『從A到Z排序』：

卻發生了如下圖的錯誤結果。(其中11:31:38卻小於8:05:23)

有網友問到：最近排班時會遇到七休一的問題，如何在 Excel 中如果連續排到 7 天時即給予警示？

以下圖中的排班格式為例(排班的形式可能很多種)，『V』記號表示要排班，在連續 7 天以上被排班時，給予紅色粗體字來識別。

通常這類問題，都只要透過「設定格式化的條件」來處理。假設，整個日期報表是由第 2 列開始，所以從第 8 列開始設定格式化的條件。

1. 選取B8:B27。

2. 選取[常用／樣式]功能表中的「設定格式化的條件／新增規則」選項。

在日常生活中，有時會用到要在一個數值區間中計算含有某個數字的個數，該如何處理？

以下圖的 Excel 例子，要計算 1~500 的數值區間中，共有幾個數含有『4』？例如：4, 140, 403, ...，這些數都含有 4。

【公式設計與解析】

儲存格C2：{=SUM(1*(SUBSTITUTE(ROW(1:500),"4","")<>ROW(1:500)&""))}

網友問到一個 Excel 的問題：

根據前一篇文章：Excel-依據日期區間列出各月人員清單(OFFSET,ROW,COLUMN,陣列公式)，如果日期有跨年時，該如何處理？

本例以2015年7月～12月、2016年1月～6為週期，來列出各月學員的清單。

【公式設計與解析】