學不完．教不停．用不盡

在 Excel 中有一個日期數列(參考A欄)，如果想要將同一日期編成同一編號+流水號(參考C欄)，該如何處理？

解決這個題目要藉助一個輔助欄位(參考B欄)：

儲存格B2：=IF(1/COUNTIF($A$2:A2,A2)=1,1,0)

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

此公式的用意在如果該日期第一次出現，則結果為1，否則為0。

在 Excel 中有一個資料表(如下圖左)，如果要依日期先要來摘要(如下圖右)，該如何處理？

【準備工作】

選取儲存格A1:B23，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：姓名、日期。

有網友問到，在 Excel 中如下圖左的一個資料表，要重新摘要成下圖右的資料表，該如何處理？

【準備工作】

為了便於公式說明，請選取儲存格A1:B9，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：專案、姓名。

YAHOO!奇摩的字典功能，相信很多人已經用過，其網址為：

http://tw.dictionary.yahoo.com/dictionary

只要在「字典搜尋」中輸入一個單字，即可進行字典查詢，以「conflict」為例：

找到的字典內容如下圖示，觀察網址列上的網址為：

http://tw.dictionary.yahoo.com/dictionary?p=conflict

在 Word 中編輯文件，字型是排版時是否美觀的重要因素，而在自己的電腦呈現漂亮的文件，移至其他電腦時，會因他台電腦沒有文件中使用的字型，而導致全部變回預設字型(例如：新細明體)。如何能將文件中的字型一起打包在文件中呢？

以 Word 2010 為例，在儲存 Word 文件時先做以下的設定：

1. 選取[檔案/選項]，再選取[儲存格]標籤。

2. 勾選「在檔案中內嵌字型」選項。

3. 勾選「不要內嵌一般系統字型」選項，因為這些字型幾乎每部電腦的 Windows 系統都會有。

4. 如果勾選「只嵌入文件中使用的字元」選項，則不會將整個字型檔都放入文件中。

在 Excel 的工作表中，如果要將資料依某個字元的位置，剖析成二部分，該如何處理？其實你只要使用 Excel 提供的資料剖析工具即可輕鬆完成，但我們要以公式來練習這個動作。

儲存格B2：=LEFT(A2,SEARCH(":",A2)-1)

SEARCH(":",A2)：找尋「:」字元的位置，再利用 LEFT 函數，由字串左邊取到「:」字元位置的前一字元。

儲存格B2：=RIGHT(A2,LEN(A2)-SEARCH(":",A2))

LEN(A2)：計算字串的長度。

LEN(A2)-SEARCH(":",A2)：計算「:」字元至字串最右邊有幾個字元，再利用 RIGHT 函數取出這些字串。

在 Word 中編輯文件時，有時為了便於溝通，需要用到每一行的行號，你只要選取[版面配置]標籤之下的「行號」，再選取連續編號或每頁重新編號或是每節重新編號等動作，Word 會自動幫你將每一行打上編號，而你也可以指定某一段不要編號。

而顯示的行號在列印時也會一併列印出來，要注意哦！

在 Excel 中有個數列，如果只想取出固定間隔的數值來計算該如何處理？例如，計算項目1,4,7, ... , 22, 25的和、平均。

為了簡化公式，選取儲存格A1:A27，按一下 Ctrl+Shift+F3 鍵，建立名稱：項目、數值。

以下分別使用四種不同方式來設計，試著比較一下：

【方法一：使用項目欄位+陣列公式】

儲存格E2：{=SUM(IF(MOD(項目,3)=1,數值))}

在 Excel 中如果貼上一些其他地方複製而來的資料，而你想要去除其中的括號和多餘空白，該如何處理？(參考下圖)

儲存格B2：=TRIM(SUBSTITUTE(SUBSTITUTE(A2,")",""),"(",""))

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

SUBSTITUTE(A2,")","")：將儲存格A2中的「(」以空串字取代。

SUBSTITUTE(SUBSTITUTE(A2,")",""),"(","")：將去除「(」後的字串，再去除「)」

TRIM 函數：移除儲存格B2中文字之間多餘的空格(僅保留文字間一個空白字元)。

在 Excel 中的資料表中(參考下圖)，其內容欄位的數字有些是文字形式。如果你選取了這些數字，Excel 在狀態列中顯示的加總結果並不會把文字形式的數字併入計算。其結果相當於使用公式：=SUM(B2:B20)。

如果你在計算時，想把這些文字形的數字一起和其他數字加總該如何處理。你可以透過陣列公式來處理，輸入公式後要按 Ctrl+Shift+F3 鍵。

(1) 儲存格D3：{=SUM(--B2:B20)}

使用運算「--」，相當於將儲存格內容乘以兩次「-1」，即可將文字形式數字轉換為一般數字，而此公式相當於：{=SUM(B2:B20*1)}

(2) 儲存格D4：{=SUM(VALUE(B2:B20))}

透過 VALUE 函數將文字轉成數字再運算。

在 Excel 的資料表中有一個數列，想要找出數列中最大的負數、最小的正數、最大奇數、最小偶數，該如何處理？這個題目必須以陣列公式來解決。

選取儲存格A1:A25，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：數值。

以下均為陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。

(1) 求最大的負數

儲存格D2：{=MAX(IF(數值<0,數值,FALSE))}

在 Excel 的一個資料表中，如果想要查詢最大值、對應的編號及儲存格位址，該如何處理？(參考下圖)

因為資料會不斷的輸入，所以將B欄定義一個名稱，方便程式說明：

選取B欄，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：數值。

(1) 求最大值

儲存格D2：=MAX(數值)

在 Excel 的工作表中有個日期和數量的報表，如果想要摘要依月份計算數量的平均，該如何處理？(參考下圖)

選取儲存格A1:B26，按一下 Ctrl+Shift+F3 鍵，定義名稱：日期、數量。

【使用陣列公式】

儲存格E2：{=AVERAGE(IF(MONTH(日期)=ROW(1:1),數量,FALSE))}

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

在 Excel 的工作表中，在A欄中會不斷的輸入考試成績，如果只想計算最近幾次的平均，該如何處理？(參考下圖)

為了便於理解公式，先選取A欄，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，將A欄定義名稱為「成績」。

要小心特別的狀況，如果輸入的成績數量少於要求的次數會出現錯誤訊息。

所以公式定為：

儲存格D2：=AVERAGE(OFFSET($A$2,COUNTA(成績)-MIN(COUNTA(成績),C2),0,MIN(COUNTA(成績),C2),1))

在 Excel 有非常多關於日期時間的格式設定，有人問到如果想要將日期中的月份以英文字來表示該如何處理？(參考下圖)

儲存格B2：=TEXT(A2,"mmmm")

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

透過 TEXT 函數，將參數設定為「"mmmm"」，即可顯示英文字的月份，例如一月為 January、二月為 February 等。

如果你是要在原日期儲存格中只想顯示月份，則可以在[儲存格格式]對話框中的「數值」標籤下，自訂格式為「"mmmm"」。

在 Excel 中如果想要將二進位數、八進位、十六進位數轉換為十進位數，可以利用以下的相關函數：

• BIN2DEC 函數：會將二進位數字轉換成十進位
• BIN2HEX 函數：會將二進位數字轉換成十六進位
• BIN2OCT 函數：會將二進位數字轉換成八進位
• DEC2BIN 函數：會將十進位數字轉換成二進位
• DEC2HEX 函數：會將十進位數字轉換成十六進位
• DEC2OCT 函數：會將十進位數字轉換成八進位
• HEX2BIN 函數：會將十六進位數字轉換成二進位
• HEX2DEC 函數：會將十六進位數字轉換成十進位
• HEX2OCT 函數：會將十六進位數字轉換成八進位

但是，如果要轉換含小數的二進位數為十進制數，則會產生「#NUM!」錯誤訊息。這次來練習如何達成轉換工作。

【模擬轉換過程】

(1) 儲存格B3:B14分別表示2^(-1)、2^(-2)、2^(-3)、2^(-4)、2^(-5)、…。

(2) 儲存格C3：=MID($B$1,ROW(3:3),1)，複製儲存格C3，貼至儲存格C3:C14。取出小數點後的每一位數字(1或0)

有網友根據上一篇文章：Excel-多條件的查詢(INDEX+MATCH+陣列) http://isvincent.blogspot.com/2012/01/excel-indexmatch.html

問到如果要查詢的資料不止一筆時，公式該如何寫？參考下圖，要由外形和尺寸這兩個條件來找符合的零件編號及其售價。

【準備工作】

1. 選取儲存格A1:D19，按一下 Ctrl+Shift+F3 鍵，建立名稱：零件編號、外形、尺寸。

2. 選取儲存格A2:D19，建立名稱：資料。

根據 Excel 中的資料表(參考下圖)，想要根據三個條件(零件編號、外形、尺寸)來求得售價，利用 INDEX 函數應是不錯的選擇。由於條件有三個，所以得藉助陣列公式才能求得結果。

【準備工作】

1. 選取儲存格A1:D19，按一下 Ctrl+Shift+F3 鍵，建立名稱：零件編號、外形、尺寸。

2. 選取儲存格A2:D19，建立名稱：資料。

【公式說明】

在 Excel 的一個處理成績的資料表中(如下圖左)，如果要求取前三名分數的平均，即使已經知道每個分數的名次，也不見得是件容易的事。或許你可以試試「陣列公式」！不用先求名次也能計算。

【準備工作】

選取儲存格B1:B26，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」選項，建立「分數」之名稱。

【說明】

(1) 求取前3名的平均

在 Excel 中經常會放入由網頁中複製而來的資料，有一點麻煩的是，想要消掉資料中的空白字元時會遇到問題(參考如下圖在網頁中的文字有許多的空白字元)：

將文字複製到儲存格A2:A4時，空白字元自動消失到「好像」剩下一個(觀察A欄)，試著以 TRIM 函數來消掉空白字元，竟然完全沒有作用。

儲存格B2：=TRIM(A2)

這是因為網頁中的空白是以「&nbsp;」來表示，這是一個非列印字元(non-printing characters)，所以無法以 TRIM 函數來將空白字元取代為空字串。其實這個看起來像空白的非列印字元為：CHAR(160)，而非空白字元：CHAR(32)。