學不完．教不停．用不盡

以下是常見將COUNTIF、SUMIF、AVERAGEIF改用陣列處理例子：

E1儲存格：=COUNTIF(C1:E6,"<5")

E2儲存格：{=COUNT(IF(C1:E6<5,C1:E6))}

E3儲存格：=SUMIF(C1:E6,"<5")

E4儲存格：{=SUM(IF(C1:E6<5,C1:E6,0))}

E5儲存格：=AVERAGEIF(C1:E6,"<5")

如下圖，如果想要將一個儲存格中的數字，對應到某些儲存格中勾選，該如何做呢？可以利用SEARCH函數。在儲存格B2中存入公式：IF(ISERROR(SEARCH(B$1,$A2)),"","V")，如果不使用ISERROR函數，則當找不到要搜尋的字時，便會顯示 #VALUE! 的錯誤訊息。

利用這個公式，即使輸入的數字，沒有依一定的數序，但仍可順利的將對應位置打勾。

SEARCH 函數，會在某個文字字串內找到另一個文字字串，並傳回該文字字串在第一個文字字串中的起始位置。例如，若要找出字母 "n" 在單字 "printer" 中的位置：

=SEARCH("n","printer")

此函數會傳回 4，因為 "n" 在單字 "printer" 中位於第四個字元。

您也可以搜尋其他單字內的單字。例如，

=SEARCH("base","database")

函數會傳回 5，因為單字 "base" 在單字 "database" 中是從第五個字元開始。可以使用 SEARCH 函數來判斷某個字元或文字字串在另一個文字字串中的位置，然後再使用 MID 函數傳回文字，或使用 REPLACE 函數來變更文字。

如果想要將下表中的資料摘要出來，試試使用Offset指令。

如果想要摘要成以下的表格。

則儲存格J2的內容輸入為「=OFFSET(A$2,(ROW(1:1)-1)*4,0,1,1)」，

再往右、往下複製儲存格即可完成。

其中，ROW(1:1)=1，(ROW(1:1)-1)*4=0

Excel自訂日期時間格式可以使用的參數如下：

以下為各種自訂格式及其結果之對照：

如果想要使用Excel將類似A欄中的不固定長度，輸出成像B欄固定寬度的結果，要如何做呢？

(一)如果只是要螢幕顯示，不用在儲存格中添加0。

選取儲存格後，在[儲存格格式]對話框中的[數值]標籤下設定。

於自訂中輸入例如「00000000」(即不滿8位數者，全部補0)

儲存格的數值格式除了以選取儲存格後，再設數值格式之外，還可以使用TEXT函數。

然而你也可以在自訂數值格式中，複製格式的設定值，然後貼在TEXT公式中，或是自己定義各種格式。

例如：在B2儲存格中顯示儲存格A2的另一種格式。

例如：在B2儲存格中取用A2和A3儲存格，格式可以重新調整(將月和日的格式都調整為2位，並且年月日以「-」隔開)。儲存格B2的公式為：

Excel中的註解或許不常用到，其註解的格式屬性是可以設定的。

在編輯註解的狀態下，在儲存格的邊框上按一下右鍵，選取[註解格式]，即會出現設定格式的對話框。

你可以設定各種字型、色彩、底色透明、文字直書/橫書等格式。

當你選取註解的邊框時，還可以選取圖片工具列上的[繪圖]功能表中的[變更快取圖案]選項，然後挑一種不同的圖案。爾後只要滑鼠移到這個有註解的儲存格上，不一樣的註解就會出現。

在Excel中，通常一個儲存格中若是輸入公式，則按下Enter鍵後，會顯示其結果。如果想要如下圖般顯運算式和運算結果，但不是在兩個儲存格中輸入相同運算，該如何處理呢？

試試這樣做：

先定義個名稱(例如ABC，任何合法的名稱都可以)，輸入其參照內容為「=EVALUATE(A2)」。

如上圖，在B2儲存格中輸入公式「=ABC」，然後將B2儲存格往下複製。

即可以在B欄顯示A欄(輸入運算式)的運算結果。

假設想要將日常生活的一些事項加以記錄，並且以週為單位來統計和分析，產生一個摘要表，試著以Excel來練習看看。

例如：(參考下圖)

先建好第一組日期，以下的日期則間隔7，即A8儲存格的公式為「=A3+7」；A10儲存格的公式為「=A5+7」，再往下複製。

記錄時以「V」或「X」標示是否執行，而時數部分以數字表示。

並且以格式化條件方式設定輸入「X」時，以灰色顯示(用以凸顯紅色字部分)。

其中小計儲格存J2的公式為「{=SUM(IF(C2:I2="V",1,0))}」，用以計算幾個「V」的數量。

如果要將一個零散輸入單位和姓名的表格，摘要出各單位出現的次數和包含的人數(不重覆)，該如何做呢？

計算各單位出現的次數，F2儲存格的公式：

{=SUM(IF($A$2:$A$28=E2,1,0))}

上式利用陣列判斷合於單位的設定1，然後再將這些1加總，即可得出現的數量。

計算包含的人數，G3儲存格的公式：

{=SUM(IF(FREQUENCY(IF($A$2:$A$28=E2,$B$2:$B$28,""),IF($A$2:$A$28=E2,$B$2:$B$28,""))>0,1))}

如下圖有兩組資料(欄A、欄B)，現在要使用這些資料建立一個摘要表，計算兩組資料交互對應的個數有多少。可以利用SUMPRODUCT函數，例如儲存格E2：

=SUMPRODUCT(($A$1:$A$18=E$1)*($B$1:$B$18=$D2))

再將儲存格E2複製到其他各個儲存格。

SUMPRODUCT 函數：傳回各陣列中所有對應元素乘積的總和。

語法 ：SUMPRODUCT(array1,array2,array3, ...)

Array1, array2, array3, ...   是 2 到 255 個欲求其對應元素乘積之和的陣列。

如果你要將記錄完整的請假記錄表，轉換成摘要資訊，而不想使用樞紐分析工具，則使用陣列公式應該是不錯的選擇。重點說明相關做法：

在記錄表中若要對齊日期，而不要出現例如:2008/1/4、2008/12/4、2008/10/10等字數不一樣多的對齊問題，則在日期儲存格設定格式為自訂：yyyy/mm/dd。

在星期幾的欄位，其公式為B3儲存格為「=A3」，再設定其格式為「星期X」。

請假假別和時數則自行輸入。

整個記錄表應依日期順序，由小到大記錄。

如下圖的基本資料，假設要依星期幾來計算各天的數量小計。

其中：

儲存格B17的公式為：{=SUM(IF(WEEKDAY($B$2:$B$15,2)=ROW(1:1),D$2:D$15))}

儲存格B17的公式為：{=SUM(IF(WEEKDAY($B$2:$B$15,2)=ROW(1:1),E$2:E$15))}

結果如下：

如果想要在一串學生姓名欄位中，挑出任意幾個名字，做為抽籤之用，該如何處理呢？

將名字列在A欄中，然後在B欄中輸入公式「=RAND()」，即產生任意亂數值。

接著在D4儲存格中輸入公式：

=INDEX($A$1:$A$19,MATCH(LARGE($B$1:$B$19,ROW(1:1)),$B$1:$B$19,))

再將公式複製到D5:D8。其中ROW(1:1)會變為ROW(2:2) … ROW(5:5)。

如果想要抓取某一欄位(例如A欄)中的最後一個數值，可以使用以下的公式：

=LOOKUP(9.99999999999999E+307,A:A)

=LOOKUP(9.9E+307,A:A)

公式的意思是要在A欄中找尋Excel可容許的最大正數(9.99999999999999E+307)。

因為LOOKUP函數是以二分搜尋法方式來找尋資料，例如：

=LOOKUP(10,{1,2,3,4,5,6,7,8,9})

如果你想要計算一群欄位中，奇數欄位的和或是偶數欄位的和，可以使用以下的公式：

COLUMN：傳回參照位址中的欄名。

語法：COLUMN(reference)

Reference是指某一單一儲存格或儲存格範圍。(如果省略 reference，則COLUMN函數會引用本身的儲存格位址。)

當在一個儲存格中要使用多個條件來計算個數或是總和，可以透過陣列，藉由「*」符號，將多個條件「AND」在一起。例如：

有沒有覺得比countif和sumif好用呢？

SUMPRODUCT函數：傳回各陣列中所有對應元素乘積的總和。

語法 ：SUMPRODUCT(array1,array2,array3, ...)

Array1, array2, array3, ...   是 2 到 255 個欲求其對應元素乘積之和的陣列。

如果想要根據一個人員缺曠的明細表，來統計每個人的缺曠時數小計。若利用SUMPRODUCT函數，在本例的應用中，符合公式中的條件會傳回True(否則為False)，再將其X1，可以將True/False陣列轉換為1/0陣列。如此SUMPRODUCT函數中的各元素相乘積，將只會留下符合條件者的和，因為不符合條件者(False,0)都會是0。(參考下圖)

因此，F3的公式應為「=SUMPRODUCT(符合人員的陣列X1,符合類別的陣列X1,金額的陣列)」，

如果想要在十五次成績中挑選出較高的十個成績，然後計算其平均，可以利用陣列來處理。

例如在Q2中輸入公式={=AVERAGE(IF(RANK(B2:P2,B2:P2)<=10,B2:P2))}

VLOOKUP：在表格陣列的第一欄中搜尋某個數值，並傳回該表格陣列中同一列之其他欄中的數值。

• VLOOKUP中的 V 代表「垂直」。當比對值位於要尋找之資料左方的某一欄中時，請使用 VLOOKUP，而非 HLOOKUP。
• 語法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

INDIRECT 函數：傳回一文字串所指定的參照位址。該參照位址內容會被立刻計算並顯示出來。

• NDIRECT 函數通常是您在公式中想要改變參照位址卻不想改變公式本身時使用。
  語法：INDIRECT(ref_text,a1)

在本例中先設定三組範圍名稱，分別是一年級、二年級、三年級，代表三個藍色區塊。參考C4的寫法，可以在三個表格中查詢指定的資料。本例是指定在三年級範圍名稱中，由成績別來查詢成績比重。