學不完．教不停．用不盡

在 Excel 中如果取得一個Email地址的清單，如果想要將Email中的帳號和伺服器名稱分離出來，該如何處理呢？

儲存格B2：=LEFT(A2,FIND("@",A2)-1)

儲存格C2：=RIGHT(A2,LEN(A2)-FIND("@",A2)-1)

複製儲存格B2:C2，往下貼上即可。

在 Excel 中取得一個數列(如下圖)，想要標示出數列中不重覆的數、重覆的數、重覆的數但不包第1個、排除重覆的數(圖中深色底、紅色字的儲存格為標示)。這次要以設定格式化條件來處理，而且只要使用COUNTIF函數即可。

(1) 重覆的數

選取儲存格B2:B20，設定公式條件：=COUNTIF($B$2:$B$20,B2)=1

(2) 重覆的數

在 Excel 中常見各種數列，以下列舉各種數列的運算。假設數列位於儲存格B2:B16，並且命名為NumList。

(1) 將數列反列

儲存格C2：=OFFSET($B$2,COUNT(NumList)-ROW(1:1),,)，複製後往下貼上。

COUNT(NumList)用以計算全部的數列有多少格。

(2) 由小到大排列

儲存格D2：=LARGE(NumList,ROW(1:1))，複製後往下貼上。

在 Excel 中，如果想要製作一個骰子的六個面(有六種點數)，該如何處理呢？下圖紅色部分代表六種點數。

參考以下的範例，點數位於儲存格B1中：

儲存格B1：=RANDBETWEEN(1,6)，藉以產生1~6的數字。

儲存格B2：=CHOOSE(B1,"","●","●","●","●","●")

儲存格D2：=CHOOSE(B1,"","","","●","●","●")

儲存格B3：=CHOOSE(B1,"","","","","","●")

再次來練習在SUM函數中執行AND和OR運算，參考下圖。

設定名稱A：儲存格B2:B11、名稱B：儲存格C2:C11、名稱S：儲存格D2:D11。

以下是陣列公式者，輸入完成應按 Ctrl+Shift+Enter 鍵。

(1) 計算A>5，求B的和

儲存格H2：=SUMIF(A,">5",B)

在 Excel 中取得一個圖書館圖書借閱的摘要表，其中登錄號代表一本書，借書證號代表一個人。而這個資料表已經先以「借書證號」遞增排序好了。

現在，想要根據這個資料表，要來求得「借閱人次」。其中相同借閱人的借閱日期如果是同一天，則只能算一次。為了說明方便，則於下圖列出「起始列、終止列」的號碼(列號)。

由於這個資料表已經排序完成，假設資料範圍：A2:C2000，要先找出某個借書證號的資料區間。

(1) 計算起始列

儲存格F2：={MIN(IF($C$2:$C$2000=E2,ROW($C$2:$C$2000),""))}

在 Excel 中的資料範圍內有最大值和最小值，如果要找出第一個最小值、最後一個最小值、第一個最大值、最後一個最大值，該如何處理呢？(參考下圖)

(1) 第一個最小值

儲存格E2：{=ADDRESS(MIN(IF(B2:B21=MIN(B2:B21),ROW(B2:B21),"")),COLUMN(B:B))}

這是陣列公式，輸入完請按 Ctrl+Shfit+Enter 鍵。

公式中「IF(B2:B21=MIN(B2:B21),ROW(B2:B21),"")」，

在 Excel 建立的一個成績表中(如下圖)，如果要顯示成績評定，該如何處理？成績評定方式如下表：

你可能會用以下的公式：

儲存格F2：=IF(E2=100,"特優",IF(E2>=90,"優",IF(E2>=80,"良",IF(E2>=70,"佳",IF(E2>=60,"可","差")))))

複製儲存格F2至儲存格F2:F21。

如果想要在 Excel 中查詢單字中各個字母的數量(如下圖)，該如何處理？首先來看看如何產生A欄的內容。

儲存格A2：=CHAR(ROW(65:65))&", "&CHAR(ROW(97:97))

因為A的ASCII碼為65，a的ASCII碼為97，所以複製儲存格A2，往下貼上時，即可產生A,B,C,D,…及a,b,c,d,…。

以查詢Windows為例來查詢是否有d這個字母，可以藉由SUBSTITUTE函數：

=SUBSTITUTE("Windows","w","") = “Windos” (w被空白取代了)

在 Excel 中如果能善用「陣列」功能，將可以節省許多撰寫公式的時間，因為陣列可以將多個值放在一個公式中同時運算。

以下分別就陣列中的AND、OR、XOR、NAND等邏輯運算加以說明，先列出相關邏輯運算的真值表：

(1) AND運算

［例］求人員丙及(And)產品AAA組合的總和

當你在 Excel 中取得一個資料表(如下圖)，如果想要根據其中幾名來計算其總和或是平均，該如何處理呢？你將用到陣列公式和藉助LARGE函數或RANK函數等。

假設共有50筆資料(儲存格B2:B51，以名稱：DATA取代)，而以下全為陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。

(1) 計算前5名的和

儲存格F2：{=SUM(LARGE(DATA,{1,2,3,4,5}))}

以LARGE函數，可以指定第1,2,3,4,5較大的數，所以以陣列{1,2,3,4,5}套入公式是非常明暸的做法。

在 Excel 中取得一個資料表(如下圖左)，要以這些資料來建立一個目前進度的百分比。假設目標總量為10000，根據目前輸入的資料換算完成的百分比。

儲存格B24：=SUM(B2:B21)/B23

現在要根據這些資訊來建立完成進度的圖表(如下圖右)。

1. 選取儲存格B24。

2. 選取[插入/直條圖]按鈕的第一個選項。你會發現 Excel 自動將選取區擴大到儲存格A23:B24。

在 Excel 中的資料表，如果想要分別計算奇數列和偶數列的和、計算奇數和偶數的和，該如何處理呢？

如果你使用這種方式，對於很大的表格，你將無能為力完成這個公式：

A1+A3+A5+…

SUM(A1,A3,A5,…)

(1) 計算奇數編號的數字和

儲存格E2：{=SUM(MOD(ROW(A2:A19)-1,2)*(B2:B19))}

在 Excel 的工作表中，如果表格很大，在公式中選取儲存格範圍時將會非常不方便，而且容易出錯。最好是使用「名稱」，又快又準確。參考以下的範例：

1. 先選取資料範圍(儲存格B1:D16)。

2. 按一下 Ctrl+Shift+F3 鍵，開啟[以選取範圍建立名稱]對話框。

3. 勾選［頂端列］選項，按一下[確定]按鈕。

如此便建立好三個名稱：國文、英文、數學，之後可以很方便的選取這些儲存格範圍。

在 Excel 如果是將兩個數拿來計算其比率，將會得到一個計算值。例如：2和10的比率應該是1:5，但是如果是以計算方式將會得到0.2，如果以分數的格式顯示將會得到1/5。要如何才能得到「1:5」呢？

(一)

先由儲存格E2來看，其內容為16/9，來練習轉變為16:9。(參考下圖)

儲存格F2：=LEFT(E2,FIND("/",E2)-1)&":"&RIGHT(E2,LEN(E2)-FIND("/",E2))

公式中的FIND("/",E2)，找出「/」在第幾個字元位置。

公式中的LEFT(E2,FIND("/",E2)-1)，抓取「/」左邊的字元。

在 Excel 的工作表中輸入一些數字，如果想要這些數字顯示為最接近的千位數，該如何處理？可以直接設定數值格式，不影響原儲存格中的數值內容。

例一：[<=500] "0";00,",000"

低於500者以0顯示，高於500者取最接近的千位數(會四捨五入)，並顯示共五位數(不足者前面補0)。

例二：[<=5000] "0";00,",000"

低於5000者以0顯示，高於500者取最接近的千位數(會四捨五入)，並顯示共五位數(不足者前面補0)。

在 Excel 中的一份資料表，如何選取男或女性別後，能以不同色彩格式區隔資料中的男生和女生？

首先透過「資料驗證」方式，將儲存格H2中加入「男,女」。

接著選取所有的資料內容(本例為儲存格A2:F214)，然後在設定格式化的條件中，輸入二個設定值：

在 Excel 的工作表中取得一個跑步記錄表(如下圖左)，利用B欄的資料要來做統計分析(如下圖右)。B欄中的資料格式固定，透過陣列的應用，只要分別以一行公式即可算出各個統計結果。

假設B欄的資料在儲存格B2:B50中，輸入以下的公式：

(以下公式全為陣列公式，輸入完成要按 Ctrl+Shift+Enter。)

(1) 最多圈

儲存格D3：{=MAX(VALUE(MID(B2:B50,3,2)))}

根據前二篇查表方式的練習：

http://isvincent.blogspot.com/2011/04/excel-indexvlookuphlookup.html

http://isvincent.blogspot.com/2011/04/excel-indexmatch.html

這次要使用OFFSET+MATCH來執行查表。

儲存格G14：=OFFSET(A1,MATCH(F14,A2:A11),MATCH(E14,B1:K1),,)

在 Excel 的工作表中，如果想要輸入一個二進制數、八進制數或十六進制數，然後要產生遞增1的數列(參考下)該如何處理呢？

必須要使用進制轉換的函數：DEC2BIN、BIN2DEC、DEC2OCT、OCT2DEC、DEC2HEX、HEX2DEC。

儲存格B3：=DEC2BIN(BIN2DEC($B$2)+ROW(1:1))

因為要進行加1的運算所以得先把二進制轉成十進制，加1後，再轉回二進制顯示。

八進制和十六進制的加1運算也一樣：

儲存格C3：=DEC2OCT(OCT2DEC($C$2)+ROW(1:1))