學不完．教不停．用不盡

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