學不完．教不停．用不盡

在 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)

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

在 Excel 中依成績判定等第的作法常見使用 VLOOKUP 函數，這次要使用其他函數來練習。在下圖右為設定好的分數區間對應評等，其定義為 100~85：A，84~70：B，69~60：C，59~40：D，39~0：E。

成績判定的公式如下：

儲存格D2：=CHOOSE(MATCH(C2,$F$2:$F$6,-1),$G$2,$G$3,$G$4,$G$5,$G$6)

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

MATCH(C2,$F$2:$F$6,-1)：找出在C欄中的成績對應分數區間的第幾個分數，其中參數「-1」乃要找出大於且最接近的數值位在第幾個。

快要過年了，整理書房時，挖出了收藏已久的古早的磁碟片，小朋友看到了嚇了一跳，怎麼會有這麼大的磁碟片呢？和一般的磁碟片比一比，大的是8吋，小的是3.5吋。(軟式磁碟片的大小以它的磁碟片直徑來區分)

下圖的白色區域是一張A4紙張，要讀這片軟碟的磁碟機，體積也是不小的。很多人連3.5吋的磁碟片都沒用過(或沒看過)呢！就把這一大一小的磁碟片收好，以後當作古懂囉！

在 Excel 中，如果輸入一個網址，想要自動取出機構名稱，並製成超連結(參考下圖)，該如何處理？(本題假設機購名稱位於第一個「.」和第二個「.」之間)

【方法一】

儲存格B2：=HYPERLINK("http://"&A2,UPPER(MID(A2,SEARCH(".",A2)+1,SEARCH(".",A2,SEARCH(".",A2)+1)-SEARCH(".",A2)-1)))

SEARCH(".",A2)-1)：找出第一個「.」的位置。

SEARCH(".",A2,SEARCH(".",A2)+1)：找出第二個「.」的位置。

在 Excel 中要計算資料的個數方法有很多，這次來練習 EXACT 函數。EXACT 函數可以比較兩個儲存格中的字串是否相同。根據下圖左的資料數列，要找出各個項目的個數(參考下圖右)。

儲存格D2：=SUMPRODUCT(--EXACT($A$2:$A$20,C2))

複製儲存格D2，貼至儲存格D2:D7。

EXACT($A$2:$A$20,C2)：產生Fasle,False,True,False,Fasle,True,…的陣列。

--EXACT($A$2:$A$20,C2)：產生0,0,1,0,0,1,…的陣列。

SUMPRODUCT 函數可以將上式中所有的 0 和 1 加總，即為個數。