學不完．教不停．用不盡

再次來練習在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. 勾選［頂端列］選項，按一下[確定]按鈕。

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