學不完．教不停．用不盡

網友問到 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，已定義名稱：資料。。

網友有一個 Excel 工作表，其中是提貨日期的清單，如何能依條件來顯示不同格式做為提醒？其條件如下：

1. 到期日前30天警示 (紅底白字)

2. 到期日前60天警示 (黃底黑字)

3. 以上同時包含時，但若已有提交日期填入，則取消警示。

通常只需要使用「設定格式化的條件」即可完成。依指定的三個條件，在「設定格式化的條件」中設定二條規則。

有網友問到：在 Excel 中有一個報名資料總表，如何讓報名結果依梯次顯示在不同工作表中？

如下圖的報名總表，其梯次有三種選項：一月、二月、三月。

如何將資料自動顯示在『一月、二月、三月』不同的工作表中？

網友根據先前的這篇文章：Excel-多條件的查詢(INDEX+SMALL+陣列)，發現他使用的 Excel 版本無法使用 IFERROR 函數，該如何修改公式？

在下圖中，其實是要根據三個條件(編號、外形、尺寸)求得對應的售價，而且當查無資料時以空白顯示，該如何處理？

【公式設計與說明】

選取儲存格A1:C19，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：編號、外形、尺寸。

在 Excel 中如果你有一些統計圖是經常性要產生且格式固定，你大概不會想要每次重覆多個步驟來設定，希望能有快速生成的方式。將圖表儲存成『範本』，是個不錯的選擇！

參考下圖，在本例中有三個表格要產生相同格式的統計圖。

當你取一個表格產生了一個統計圖，也都調整好所有的格式。

在統計圖的圖表區中，按一下右鍵，選取「另存為範本」。

網友問到：在 Excel 中如果要將一列多欄轉為多列多欄，該如何處理？

參考下圖，想要將同一列中的儲存格A1:R1，轉換為儲存格A5:D9(四欄)。

【公式設計與解析】

儲存格A5：=OFFSET($A$1,0,+(ROW(1:1)-1)*4+MOD(COLUMN(A:A)-1,4),1,1)

網友問到：參考下圖，在 Excel 中，如何將表格中的欄列互換？

在下圖中，如果你使用複製功能，再使用『轉置』貼上，即可得到欄列互換的結果。但是，如果要使用公式來設計，該如何處理？

【公式設計與解析】

透過 OFFSET 函數處理欄列互換很簡單！根據微軟的定義：

在 Word 中或許你使用過『樣式』功能，來加速文書排版工作，並且讓文件不同地方的版面效果趨近於一致，更可以加速修改格式時的速度。

而在 Excel 中也可以使用『樣式』功能！例如，在下圖中有三個表格，其中一個已經格式設定完成，如果要讓另外兩個表格，也是套用相同的表格格式。相信你的直覺做法是用『複製格式』，刷二下即可完成，當然，這是沒問題的。但是，若要修改其中的格式，就得重覆操作。

本例特別使用『樣式』功能來練習。

1. 選取本例中的儲存格A1:D1。(這些儲存格已經設定跨欄置中效果)

有老師問到：如下圖，在 Excel 中如何製作這個二週小考平均的比較表？

在下圖中，已有兩週的小考平均，如何在第2週比第1週的平均進步時顯示綠色正三角型圖示，在第2週比第1週的平均退步時顯示紅色倒三角型圖示？

【公式設計與解析】

1. 先在 K 欄中計算第2週和第1週的平均值的差。

網友根據這篇文章：Excel-表格的轉換(OFFSET,SUMPRODUCT,ROW)，問到想要如下圖中由B表格查詢A表格，該如何處理？

【公式設計與解析析】

儲存格E3：{=IFERROR(OFFSET($A$1,SMALL(IF($B$3:$B$18=$D3,ROW
($B$3:$B$18),""),COLUMN(A:A))-1,0),"")}

網友想要在 Excel 中做資料處理，參考下圖中的數值欄位，要依規則取後2碼來調整「進位」，該如何處理？

規則：

(1) 若後2碼<=50，則調整為：50

(2) 若後2碼>50，則調整為：100

網友問到：如何在 Excel 中將一個矩陣資料內容轉換為一欄？參考下圖，儲存格A1:D5中的資料要轉換至儲存格A7:A26。

【公式設計與解析】

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

複製儲存格A7，往下各列貼上。

參考下圖，在 Excel 中，如果要將儲存格內容「877/13/3214/6481/643/4486」的文字，依其分隔符號『/』取出『877、13、3214、6481、643、4486』，該如何處理？注意其分隔符號之間的內容的文字長度並不一致。

通常，我們會使用 Excel 中的「資料部析」工具來處理，手動操作也很方便，但是如果想要以公式來處理，或像下圖中要把取出的資料放在同一欄中。(資料剖析工具只能將資料部析結果放在同一列中)

【公式設計與解析】

參考上圖，假設：本例的資料中有五個分隔符號『/』，將資料分成六組。