學不完．教不停．用不盡

有網友問到：在 Excel 中有一個資料表(如下圖)，其中有 Ref 和 Name 兩個欄位，如何將相同 Name 的資料集合在同一欄，並且列出 Ref，該如何處理？

【準備工作】

選取儲存格A1:B6，按一下 Ctrl+Shift+F3 鍵，定義名稱：Ref、Name。

【輸入公式】

在 Excel 的工作表的儲存格中，可能會有全型字和半型字交雜的字串，如何找出儲存格中全型字和半型字的數量呢？(參考下圖)

儲存格B2：{=SUM(--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>255))}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。

以字串「123四五67八九」為例：

ROW(INDIRECT("1:"&LEN(A2)))：

老師們常在課堂上讓學生分組上台報告，並且互打分數，例如每個學生取得以下的打成績表單，自己組別無需打分數。老師取回班上所有同學的成績表單，該如何在 Excel 中算出分組成績呢？

成績輸入系統要求：(參考下圖)

1. 自己組別不需打成績，自己組別儲存格以深色表示，若強行輸入，則顯示錯誤訊息。

2. 同組每個人的成績相同，即為全班給分的平均。若有人某些組別不給分，則計算平均時，不以 0 分列入平均。

3. 每班人數不一，而且每組人數也不一定相同。

這次要來用 Excel 練習呈現兩次考試的進/退步狀況，如下圖：

1. 算出第2次和第1次成績的差距，不需顯示 + / - 符號。

2. 如果差距大於或等於 0，則以紅色數字和向下紅色箭頭呈現。

3. 如果差距小於 0，則以綠色數字和向上綠色箭頭呈現。

在 Excel 中，有人有這樣的需求：想要找出一個儲存格中以「,」分隔的項目有幾個，該如何處理？(參考下圖)

【做法】

因為每個項目以「,」分隔，所以將儲存格中的「,」以空白取代，再將原來儲存格的字串長度減掉消掉「,」後的字串長度，即為「,」的個數，再加上1，即為項目個數。

在 Excel 中可以很方便的將一個矩陣資料加以轉置，參考下圖：

如果你要使用 Excel 的工具，則只要複製儲存格，在執行貼上時，按右鍵選取「轉置」即可。

如果你要使用公式來處理，該如何處理呢？

在 Excel 中有一個數列，常會踫到要依不同分組來計算總和。參考下圖的二種分組方式來計算總和，該如何處理？

1. 項次每間隔 5 個為一組

2.項次連續 5 個為一組

選取儲存格B1:B26，按一下 Ctrl+Shift+F3 鍵，定義名稱：數值。

1. 項次每間隔 5 個數值為一組

有人問到：在 Excel 中，如果想要將幾個儲存格的內容串接在一起(參考下圖)，你可能會這樣做，例如：

儲存格A11：=B2&B3&B4&B5&B6&B7&B8&B9

這樣的做法對於要串接的內容來自多個儲存格時，在操作上十分的不方便。

在 Excel 中有一個很特殊的函數：PHONETIC，原本的作用是：從文字字串中抽選注音標示 (Furigana) 字元。

語法：PHONETIC(reference)

三不五時就會有人問到：在 Word 文件中常見的一個問題，如下圖的一個圖片無法完全的顯示，實在令人困擾，該如何解決呢？

我所踫過的狀況都是因為這個圖片所在這一行的行距被改變了(例如：固定行高22點)，所以只要改回單行間距、 1.5 倍行高或是 2 倍行高即可。

你可以使用快速鍵：

Ctrl + 1：設定單行間距

在 Excel 中如何找出每個月的天數呢？尤其要注意二月時會有閏月的問題。在下圖中，要查出某年某月的天數，該如何處理？

以下利用三種做法來練習：

(1) 儲存格C2：=DATE(A2,B2+1,1)-DATE(A2,B2,1)

DATE(A2,B2,1)：找出這個月的 1 日，做為 DATEDIF 函數的起始日期。

DATE(A2,B2+1,1)：找出下個月的 1 日，做為 DATEDIF 函數的終止日期。

在 Excel 中的樞紐分析表是一個非常好用的工具，很輕鬆就可以做到摘要的工作。以下圖為例，要依水果類別和等級類別來做加總的摘要表。

同事問到，如何以公式方式來做到相同結果？

這其實是個很好的練習題。

1. 先選取儲存格B2:D25，按一下 Ctrl+Shift+F3 鍵，定義名稱：等級、水果、數量。

在 Excel 中，如果要計算符合某個條件的個數，透過 COUNTIF 函數可以很容易達成。試著以不同公式來處理下圖的數值數列中，大於 10 的個數。

以下提供四種方式供學生練習找出大於 10 的個數：

(1) 儲存格D2：=COUNTIF(A2:A20,">10")

(2) 儲存格D2：=SUMPRODUCT((A2:A20>10)*1)

公式中的「*」，可以將判斷式所得的 True/False 陣列，轉換為 1/0 的陣列。

在 Excel 中大家都很熟悉如果要做到動態色彩效果可以使用「設定格式化條件」功能。如下圖，有一個含有日期的資料表，雖然月份都是由小到大，但是可能有些月份有，有些月份沒有，如何設定才能讓不同月份有所區隔。

(一) 月份必須連續，且每一月份皆有，利用儲存格底色分隔月份。(可以用在跨年份)

1. 選取儲存格A3：B27。設定儲存格底色：橙色。

2. 設定格式化條件為：使用公式來決定要格式化哪些儲存格。

同仁問到一個有趣的問題：在 Excel 中有一個折線圖，如果想要改變其中的線條層次(上層/下層)，該如何處理？

例如：在下列的統計圖中想要將最底層的紅色折線放到最上層。

參考以下的步驟：

1. 選取這個圖表。

2. 選取［圖表工具/設計］功能表中的「選取資料」。

同事問到：在 Excel 中以一個資料表製成一個統計圖，如果只想列印資料表，不想列印統計圖，該如何處理？(參考下圖)

提供二種方式來操作：

一、將統計圖隱藏

1. 選取統計圖後，選取 [常用/編輯]功能表中的「選取窗格」。

2. 在[選取窗格]中點選圖表，該圖表會被隱藏起來。

在 Excel 中常見到在一堆資料中要找出數個符合的項目，參考下圖。將檢核結果符合者，給予「V」記號，該如何處理？

為了讓學生多多練習，提供四種方式來操作，複製儲存格B2，往下各列貼上：

(1) 儲存格B2：=IF(ISNA(VLOOKUP(A2,$D$2:$D$9,1,FALSE)),"","V")

VLOOKUP(A2,$D$2:$D$9,1,FALSE)：查詢儲存格A2的內容是否為符合的項目，若不是會得到一個錯誤訊息「#N/A」。

在 Excel 中輸入資料，預設值為按下 Enter 鍵後移至同欄的下一列儲存格。同事問到，如何能向下輸入、向上輸入、向右輸入、向左輸入呢？

有一些簡單的技巧，可能常被忽略了。你可以選取一段儲存格範圍，參考以下有四種做法：

(1) 輸入資料後按 Enter 鍵，作用儲存格的移動方向為：由上往下，由左至右。

(2) 輸入資料後按 Shift + Enter 鍵，作用儲存格的移動方向為：由下往上，由右至左。

在 Excel 中常會使用到「日期/時間」的表示，所以日期/時間的格式設定相當重要。在儲存格的格式設定之「數值/自訂」中，可以輸入自訂的格式：

​  ​ ​   ​

整理了一些常用的格式如下：

【日期格式】

最近個人資料法的問題吵的沸沸揚揚，很多要公告的文件都必須要把名字做適當的隱藏。參考下圖，如何將第 2 字隱藏或是將倒數第 2 個字隱藏呢？

因為人名可能有 2, 3, 4, 5, …個字，所以可以隱藏第 2 個字或是倒數第 2 個字，或其他隱藏方式。

利用 REPLACE 函數，可以置換文字。

(1) 隱藏第 2 個字

儲存格C2：=REPLACE(B2,2,1,"O")

(2) 隱藏倒數第 2 個字

網友問到：如何計算某個日期為當年第幾週？如果不使用 WEEKNUM 函數，如何做到相同結果？(參考下圖)

(1) 使用 WEEKNUM 函數

在 Excel 2010 中，有一個 WEEKNUM 函數，可以根據某個日期傳回該日期為本年度的第幾週。

儲存格B2：=WEEKNUM(A2,1)

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