學不完．教不停．用不盡

有網友根據下圖左 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為週期，來列出各月學員的清單。

【公式設計與解析】

網友問到 Excel 的問題：如何將 2015/05/25 轉換為 2015年05月25日？

參考下圖，使用者在輸入日期時，可能會輸入數值格式的 2015/05/25，其數值置於D欄(本例為：42149)，也可能輸入的是文字格式的 2015/05/25 字串。相同公式不一定都適用於數值和文字格式的日期格式。

【公式設計與解析】

儲存格B2：

=YEAR(A2)&"年"&TEXT(MONTH(A2),"00")&"月"&TEXT(DAY(A2),"00")&"日"

網友問到：在 Excel 中有一個學員受訓的記錄表，如何根據下圖左的日期區間，轉換至下圖中各月的清單報表？

【公式設計與解析】

先選取儲存格A1:C23，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：學員、開始、結束。

儲存格E2：

你有遇過這樣的問題？在 Excel 產生的統計圖表要貼至 Word 中，但是圖表是一個可以使用下拉式清單選取不同資料的表格所建立，即動態表格共用同一個圖表。

參考下圖，如果你想分別將2013年、2014年、2015年三個資料表建立的圖表複製到 Word 文件中，將會發生以下的問題。

如果你選取了2013年所產生的統計圖：

貼至 Word 文件中：(目前 Excel 文件在開啟狀態)

繼上篇文章：Excel-將日期中月、日第1碼的0去除(LEFT,MID,RIGHT)

如果在 Excel 中，想要將民國年轉換為西元年，並將月、日以2碼表示，該如何處理。

【公式設計與解析】

儲存格C2：=TEXT((LEFT(A2,3)+1911)&MID(A2,4,9),"yyyy/mm/dd")

網友問到：在 Excel 的工作表中如果日期欄位裡放的是民國年，且月和日都以2碼表示，該如何去除月、日第1碼的0？

參考下圖，因為A欄裡放的是『民國年』的資料，因此儲存格內容視為文字，而非 Excel 預設的數值日期格式。

【公式設計與解析】

儲存格C2：=LEFT(A2,4)&(--MID(A2,5,2))&"/"&(--RIGHT(A2,2))

前二篇文章提到了在 Excel 中的排名問題：

Excel-使用RANK.EQ和RANK.AVG處理排名問題

Excel-重覆名次不跳過

如果現在要根據分組的人員分數，來求各組人員的名次，該如何處理？

有同仁在 Excel 中使用「進階篩選」時，想要將[基本資料]工作表中的資料篩選至[篩選結果]工作表中，於操作過程中遇到一些的問題。(參考下圖)

其步驟為：

1. 目前被選取的是[基本資料]工作表的儲存格A1，選取「進階篩選」。

2. 在[進階篩選]對話框中填入「資料範圍」和「篩選範圍」。這兩個範圍都在[基本資料]工作表中。

3. 因為勾選了「將篩選結果複製到其他地方」，所以在「複製到」文字方塊中選取了另一個工作表的儲存格，例如：篩選結果!$A$1:$C$1。

在 Excel 中通常在設計排序的公式時會使用 RANK 函數，當遇到兩個比較的值相同時會給予同名次，並且跳過下一個名次。在 Excel 2010 以上版本，RANK 函數已進化為 RANK.EQ 和 RANK.AVG。這二個有何差異？

RANK.EQ：當有多個比較值相同時，會以最前面的名次當為排名，並跳過重覆的排名。

RANK.AVG：當有多個比較值相同時，會以平均名次當為排名。

觀察下圖：

儲存格A2:A25，已定義名稱：資料。。