學不完．教不停．用不盡

學校同仁問到在 Excel 中使用樞紐分析表時常見的問題：如何將樞紐分析結果依某個欄位項目分頁列印？也就是要依某個欄位中的項目，每一個項目印一張報表。該如何處理？

例如：將以下的資料表轉換出一個樞紐分析表。

我們要的樞紐分析表如下圖，其中要以「月份」來篩選。

檢查是否你要列印的月份都已勾選。(按下(全部)右側的下拉式清單按鈕，勾選「選取多重項目」，即可挑選要列印的月份。)

在 Excel 中有一個常見的如下圖左的資料表，如果要建立如下圖右的四個統計表，要如何處理？

【準備工作】

為了方便說明，先來定義儲存格範圍的名稱。選取儲存格A1:D25，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：年級、類別、級別、人數。

【公式設計與解析】

大家使用 Excel 時，不知是否也踫到像有位網友遇到這樣的問題？

當你要定義名稱時，先選取儲存格A1:D10，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，你想要定義名稱：RT100、UX200、KM300、NY400。

看似正常的操作，到[名稱管理員]對話框中才發現，名稱被直接改為(參考下圖)：RT100_、UX200_、KM300_、NY400_。在不知情的狀況(未進入名稱管理員檢視)，將名稱使用在公式中，卻一直出現錯誤訊息。這到底是什麼原因呢？

這是因為 Excel 有規定：名稱不可以是「內建名稱」，而名稱：RT100、UX200、KM300、NY400 正是以欄名列號組成的名稱，所以不可使用這樣的名稱，但是 Excel 又很好心的自動幫你改為預定名稱之後加上「_」，並沒有顯示錯誤訊息，所以才會產生這種狀況。(觀察下圖來了解，定義的名稱無效的可能原因。)

常常有人在 Excel 中要使用日期時卻發現無法拿來運算？這是為什麼呢？因為通常是格式不對！例如：2015/7/10 輸入為 7/10/2015 或 7.10/2015 等格式，這兩種日期格式並非 Excel 中的標準日期格式，因此都被視為「文字」，所以無法拿來運算。

如果要將這些文字拿來運算時，就必須要先轉換為數值，在下圖中，為了解說方便，所以將年、月、日分三欄處理。

【公式設計與解析】

1. 取出「年」

有網友想要在 Excel 中如下圖左的資料清單，將不同類別的數量予以小計，該如何處理？在下圖左中，如果三組「類別、數量」放在同一欄中，不管使用 SUMPRODUCT 函數或是 SUMIF 函數等，都可以一個公式就可以解決，但是分成多組就不易處理了。

【公式設計與解析】

方式１：使用 SUMPRODUCT 函數

(1) SUMPRODUCT(($A$2:$A$16=H2)*$B$2:$B$16)

網友問到要在 Excel 中設計一個二層的下拉式清單，方便來輸入資料。如下圖的範例中，已經有「季別、月份、星期、天干、地支」等五類的資料，每一類中各個一些資料項目。如何能根據選取的不同類別，在「項目」中顯示不同的項目清單？

大多數網友應該都是不想寫程式，想要直接使用公式等方式來達到這個效果。建議藉助「資料驗證」功能和 INDIRECT 函數來搭配，也可以做到這個效果。

【設計與解析】

1. 先為每個資料類別定義一個名稱，例如：

網友想要在 Excel 的工作表中輸入一個日期之後，能自動產生星期幾，並且將是星期六、日的儲存格格式標示不一樣的格式，該如何處理？

如下圖中的一個日期對照三種不同的星期幾表示法，而且星期六和星期日自動以不同的格式來標示。

【公式設計與解析】

如果你使用儲存格格式設定，則只要在[數值]標籤中選取[日期]類別，再選取「星期三」即可。

有網友詢問在 Excel 試算表中有如下圖的資料清單，如何判斷某個代號在最近 90 天(三個月)曾經出現過並且標示，該如何處理？

如下圖中，由 2015/4/1 起每天會有一筆或是多筆資料(以代號表示)，希望能在輸入一個代號後，即能顯示這代號於最近三個月曾經出現過了。

【公式設計與解析】

儲存格C2：

在 Excel 的工作表中有一個含有姓名和數值的二樓資料，如何能依序由大到小列出前幾名？在下圖中，是由 22 個數值中取出前 10 名，並且由大至小依序列出。

【公式設計與解析】

(1) 由大至小依序列出數值

儲存格F3：=LARGE(數值,ROW(1:1))

這個例子是有網友想要在 20 個不連續的數字中，隨機取出 6 個不重覆的數，該如何處理？參考下圖，已經有 20 個不連續的數值清單，想要從這些數字取出不重覆的 6 個數。

為了簡化公式，所以使用了一個輔助欄位，在C欄中輸入公式：=RAND()，在C欄中的亂數接近不會重覆。

【公式設計與解析】

儲存格E2：

孩子無意中提到天干、地支為何？排列結果為何？

天干：甲、乙、丙、丁、戊、己、庚、辛、壬、癸。

地支：子、丑、寅、卯、辰、巳、午、未、申、酉、戌、亥。

天干和地支的組合形成了循環60個循環，如下圖的D欄。

該如何利用 Excel 的公式找出天干和地支的全部組合呢？

根據前一篇文章：Excel-計算以每n個為一組來計算m個可以分成多少組(INT)，讀者想要在每天計算組數時，若不滿一組者併入隔天繼續累計，該如何處理？

在下圖中，是以每４個為一組來計算累計的組數。其中最後一筆的累計組數，並不包含最後不滿一組者。

【公式設計】

有網友問到：希望利用 Excel 來計算分組數量的問題。例如：以２／３／４／５／６個為一組時，分別可以分成幾組(不足一組之零星數量也算一組)，該如何處理。

【公式設計與解析】

以每 n 個為一組，來計算 m 個有多少組的公式為：=INT((m-1)/n)+1。

開學了，老師同仁問到如何利用 Excel 在一次全年級的學生考試成績中找出：頂標、前標、均標、後標、底標的成績？

先來看看目前五標的定義為：

頂標：該科成績位於第88百分位數之考生級分

前標：該科成績位於第75百分位數之考生級分

均標：該科成績位於第50百分位數之考生級分

後標：該科成績位於第25百分位數之考生級分

網友們高頻率會詢問到在 Excel 中如何來轉換日期格式，例如：1040702 轉換為 104/07/02。這類的問題該如何處理？

在 Excel 中，日期是一個數值，1040702 並不是一個標準的日期數值，如果拿來轉換後，若是以顯示的角度來看(格式)，轉換後可以是數字，也可以是文字。而數字可以用來執行日期的運算。

【公式設計與解析】

(1)轉換為數值格式

在 Excel 中用來計算平均的方式很多，也有函數可以直接使用。但是，如果要依據多個條件來篩選出數值再計算平均，或許就會比較傷腦筋。

如下圖中的資料表，如果只想取出乙和丁的數值，並計算二者所有數值的平均，該如何處理？通常公式較長時，配合陣列來設計可以縮短公式，又該如何處理？

【公式設計與解析】

在儲存格D2中例舉以下幾種公式寫法：

在網路上看到有人在 Excel 中，利用選項按鈕來決定列印時的資料範圍，這個是很有用的工具。因為把多個資料做在同一個工作表中有其方便性，但是在列印時，不見得同時要印出每一個資料表，或是有時只想列印某一個資料表。

用以下一個工作表中所建立的三個資料表，來示範如何操作：

1. 利用[開發人員]功能表插入三個選項按鈕，分別修改標籤為：Ａ表、Ｂ表、Ｃ表。

2.接著設定儲存格連結至一個儲存格(本例為儲存格K20)。

網友問到：在 Excel 中希望能在輸入年、月、日後，自動顯示星期幾(如下圖)，有那些公式可用？

要轉換年月日為一個日期數值，可以使用 DATE 函數；若要將一個日期顯示為星期幾，則可以使用儲存格格式設定或是使用 TEXT 函數來處理？

(1)

轉換日期為一個數值，儲存格D2：=DATE(A2,B2,C2)

再設定儲存格格式：日期／星期三

在 Excel 的工作表中取得一個如下圖左的資料表，其主要是在「甲、乙、丙」三個欄位中填入「A、B、C」。該如何轉換為如下圖右的資料表？其主要是在「A、B、C」三個欄位中填入「甲、乙、丙」。

【公式設計】

儲存格G1：=IFERROR(INDEX($C$1:$E$1,,MATCH(G$1,$C2:$E2,0)),"")

複製儲存格G1，貼至儲存格G1:I21。

MATCH(G$1,$C2:$E2,0)：利用 MATCH 函數找出儲存格G1的內容在儲存格C2:E2中的位置(傳回一個順序的位置)，如果儲存格內容不存在，則傳回錯誤訊息(#N/A)。

全家要出遊，有時要上山下海的，孩子們除了會問那裡有沒有 WiFi 之外，也會問到山有多高？當然就會透過 Google 查閱相關資料盡力查到山有多高。但是，其實手機上的 Google 地圖會也有相關的資料可看。

當你在 Google 地圖上查詢到相關的位置後，在功能表上切換到「地形」。

你試著縮放不同大小的層級：

在某些大小之下，即可發現顯示了「等高線」，並且每間隔 100 公尺會有標示。我就用這個功能約略的回答孩子們的疑問。