陣列公式的規則和範例

本練習會示範如何使用多儲存格及單儲存格陣列公式來計算一組銷售數字。 第一組步驟使用多儲存格公式來計算一組小計。 第二組步驟則使用單儲存格公式來計算總計。

• 多儲存格陣列公式

複製下方的整份表格，然後將它貼到空白工作表中的儲存格 A1。

銷售人員	汽車 類型	已 售出數位	單 價	總 銷售額
孫哲翰	四門轎車	5	33000
	雙門轎跑車	4	37000
李莉華	四門轎車	6	24000
	雙門轎跑車	8	21000
羅書成	四門轎車	3	29000
	雙門轎跑車	1	31000
盧珮佳	四門轎車	9	24000
	雙門轎跑車	5	37000
吳又倫	四門轎車	6	33000
	雙門轎跑車	8	31000
公式 (總計)			總計
'=SUM(C2:C11*D2:D11)			=SUM(C2:C11*D2:D11)

1. 若要查看每個銷售人員的雙門車和小車總銷售額，請選取儲存格 E2：E11，輸入公式 =C2：C11*D2：D11，然後按 Ctrl+Shift+Enter。

2. 若要查看所有銷售額的總計，請選取儲存格 F11，輸入 =SUM (C2：C11*D2：D11) ， 然後按 Ctrl+Shift+Enter。

當您按Ctrl+Shift+Enter時，Excel 會以大括弧 ({ }) 括住公式，並將公式實例插入所選範圍的每個儲存格中。 這項作業的執行速度很快，因此您在 E 欄中看到的是每位銷售人員的各車種銷售量。 如果選取 E2，然後選取 E3、E4 等，都會看到相同的公式：{=C2:C11*D2:D11}。 

• 建立單儲存格陣列公式

在活頁簿的儲存格 D13 中，輸入下列公式，然後按Ctrl+Shift+Enter：

=SUM(C2:C11*D2:D11)

在此案例中，Excel儲存格範圍 C2 到 D11 (陣列中的值相乘) 然後使用SUM函數將合計相加。 結果是總銷售額為 $1，590，000。 此範例顯示此類型公式的強大功能。 例如，假設您有 1，000 列資料。 您可以在單一儲存格中建立陣列公式，而不是將公式向下拖曳到 1，000 列，以加總部分或所有資料。

此外，請注意，儲存格 D13 中的單儲存格公式與儲存格 E2 (E11 公式中的多儲存格公式完全) 。 這也是使用陣列公式的另一項優點 ——彈性。 您可以變更欄 E 中的公式，或一併刪除該資料行，而不會影響 D13 中的公式。

陣列公式還具備下列優點：

• 一致性    按一下 E2 以下的任何儲存格，都會看到相同的公式。 這種一致性有助於確保提升正確性。

• 安全性    您無法覆寫多儲存格陣列公式的元件。 例如，按一下儲存格 E3，然後按Delete。 您必須選取整個範圍的儲存格 (E2 至 E11) 並變更整個陣列的公式，或將陣列保持現狀。 作為額外的安全措施，您必須按 Ctrl+Shift+Enter 以確認公式有任何變更。

• 較小的檔案大小    您通常可以使用單一陣列公式，而不是多個中間公式。 例如，該活頁簿使用一個陣列公式來計算欄 E 中的結果。如果您使用標準公式 (例如 =C2*D2、C3*D3、C4*D4...) ，則應該會使用 11 個不同的公式來計算相同的結果。

一般而言，陣列公式使用的是標準公式語法， 開頭都是等號 (=)，而且您可以在陣列公式中使用大多數的內建 Excel 函數。 主要的差別在於，使用陣列公式時，要按 Ctrl+Shift+Enter 才能輸入公式。 關鍵差異在於使用陣列公式時，請按 Ctrl+Shift+Enter 以輸入公式。 如果您是手動輸入大括弧，則公式會轉換成文字字串，無法運作。

陣列函數可以是建立複雜公式的有效率方式。 =SUM( C2:C11*D2:D11) 這個陣列公式等同於以下公式：=SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11)。

使用多儲存格公式時，也請記住以下要點：

• 您必須在輸入公式「之前」，選取要放置結果的儲存格範圍。 您是在選取 E2 至 E11 儲存格建立多儲存格陣列公式時進行此動作。

• 您不能變更陣列公式中個別儲存格的內容。 若要嘗試這項操作，請在活頁簿中選取 E3 儲存格，然後按 Delete 鍵。 Excel 會顯示一則訊息，告訴您無法變更陣列的任何部分。

• 您可以移動或刪除整個陣列公式，但不可移動或刪除陣列公式的某個部分。 換句話說，若要縮減陣列公式，必須先刪除現有公式，然後從頭開始。

• 若要刪除陣列公式，請選取整個公式範圍 (例如 E2：E11) ，然後按Delete。

• 您無法將空白儲存格插入多儲存格陣列公式，或刪除儲存格。

有時，您可能需要展開陣列公式。 選取現有陣列範圍中的第一個儲存格，然後繼續，直到您選取要延伸公式的整個範圍。 按 F2 編輯公式，然後在調整公式範圍後按 CTRL+SHIFT+ENTER 以確認公式。 關鍵在於選取整個範圍，從陣列中左上方的儲存格開始。 左上方的儲存格就是要編輯的儲存格。

陣列公式很好用，但也有一些缺點：

• 您偶爾可能會忘記按Ctrl+Shift+Enter。 即使是最有經驗的 Excel 使用者也可能發生這種情況。 凡是輸入或編輯陣列公式時，請記得按這個組合鍵。

• 活頁簿的其他使用者可能不了解您的公式。 實際上，陣列公式一般不會在工作表中說明。 因此，如果其他人需要修改您的活頁簿，您應該避免使用陣列公式，或確定這些人員瞭解任何陣列公式，並瞭解如何變更公式 。如果需要的話。

• 視電腦的處理速度和記憶體而定，大型陣列公式可能會讓計算作業變得緩慢。

矩陣常數是陣列公式的一項元件。 您可以輸入項目清單來建立矩陣常數，然後手動輸入大括弧 ({ }) 括住清單，如下所示：

={1,2,3,4,5}

現在，當您建立陣列公式時，必須按Ctrl+Shift+Enter。 由於常數陣列是陣列公式的一項元件，因此您必須手動輸入大括弧來括住這些常數。 然後使用 Ctrl+Shift+Enter 輸入整個公式。

如果是使用逗號來分隔項目，便會建立水平陣列 (列)。 如果是使用分號來分隔項目，便會建立垂直陣列 (欄)。 若要建立二維陣列，必須使用逗號來分隔每列中的項目，並使用分號來分隔各列。

以下是單列的陣列：{1，2，3，4}。 此為單欄中的陣列：{1;2;3;4}。 此為兩列和四欄的陣列：{1,2,3,4;5,6,7,8}。 在兩列的陣列中，第一列為 1、2、3 及 4，第二列為 5、6、7 及 8。 單一分號放在 4 與 5 之間，分隔了這兩列。

矩陣常數跟陣列公式一樣，可搭配使用 Excel 中所提供的大多數內建函數。 下列各節說明如何建立各種常數，以及這些常數要如何搭配 Excel 中的函數使用。

下列程序可讓您稍加練習如何建立水平、垂直及二維常數。

建立水平常數

1. 在空白工作表中，選取儲存格 A1 到 E1。

2. 在資料編輯欄中，輸入下列公式，然後按Ctrl+Shift+Enter：

   ={1,2,3,4,5}

   在這種情況下，您應該在{ } (中輸入左大括弧) 右大括弧，Excel會新增第二組。

   畫面會顯示下列結果。

   

建立垂直常數

1. 在活頁簿中，選取由五個儲存格組成的一欄。

2. 在資料編輯欄中，輸入下列公式，然後按Ctrl+Shift+Enter：

   ={1;2;3;4;5}

   畫面會顯示下列結果。

   

建立二維常數

1. 在活頁簿中，選取四欄寬三列高的儲存格區塊。

2. 在資料編輯欄中，輸入下列公式，然後按Ctrl+Shift+Enter：

   ={1,2,3,4;5,6,7,8;9,10,11,12}

   您會看到下列結果：

   

在公式中使用常數

以下是使用常數的簡單範例：

1. 在範例活頁簿中，建立一張新的工作表。

2. 在 A1 儲存格中輸入 3，然後在 B1 中輸入 4，C1 中輸入 5，D1 中輸入 6，E1 中輸入 7。

3. 在儲存格 A3 中輸入下列公式，然後按Ctrl+Shift+Enter：

   =SUM(A1:E1*{1,2,3,4,5})

   請注意，Excel 會用另一組大括弧括住常數，因為您將它輸入為陣列公式。

   

   值 85 出現在 A3 儲存格中。

下一節說明公式如何運作。

您剛使用的公式包含數個部分。

1. 函數

2. 儲存的陣列

3. 運算子

4. 矩陣常數

括弧之內的最後一個元素就是矩陣常數：{1,2,3,4,5}。 別忘了！ 另請注意，在陣列公式中新增常數之後，請按 Ctrl+Shift+Enter 以輸入公式。

由於 Excel 會先對用括弧括住的運算式執行運算，因此接下來要計算的兩個元素是活頁簿中所儲存的值 (A1:E1) 和運算子。 此時，公式會將已儲存陣列中的值乘以常數中的對應值。 其結果等於：

=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)

最後，SUM 函數會將值相加，而總和 85 會顯示在 A3 儲存格中。

若要避免使用已儲存的陣列，而只是將運算整個保存在記憶體中，請用另一個矩陣常數來取代已儲存的陣列：

=SUM({3,4,5,6,7}*{1,2,3,4,5})

若要嘗試這項操作，請複製函數、選取您活頁簿中的空白儲存格、將公式貼到資料編輯欄，然後按 Ctrl+Shift+Enter。 您會看到與先前練習中使用陣列公式所得的相同計算結果。

=SUM(A1:E1*{1,2,3,4,5})

矩陣常數可以包含數字、文字、邏輯值 (例如 TRUE 和 FALSE) 及錯誤值 (如 #N/A)。 您可以使用整數、小數和科學記號。 如果要包含文字，則需要用引號 (") 括住文字。

矩陣常數不能包含其他的陣列、公式或函數。 換句話說，只能包含那些以逗點或分號分隔的文字或數字。 當您輸入 {1,2,A1:D4} 或 {1,2,SUM(Q2:Z8)} 這類的公式時，Excel 會顯示警告訊息。 此外，數值不能包含百分比符號、貨幣符號、逗號或括弧。

使用陣列常數最好的方法之一是命名常數。 已命名的常數使用起來更加容易，而且可以隱藏一些陣列公式的複雜性，不讓其他人看見。 若要為矩陣常數命名並用在公式中，請執行下列步驟：

1. 在 [公式] 索引標籤上，按一下 [已定義之名稱] 群組中的 [定義名稱]。
   [ 定義名稱> 對話方塊會出現。

2. 在 [名稱] 方塊中輸入「第一季」。

3. 在 [參照到] 方塊中，輸入以下常數 (記得要手動輸入大括弧)：

   ={"一月","二月","三月"}

   對話方塊的內容現在看起來如下：

   

4. 按一下 [確定]，然後選取由三個空白儲存格組成的一列。

5. 輸入下列公式，然後按 Ctrl+Shift+Enter。

   =第一季

   畫面會顯示下列結果。

   

使用已命名的常數作為陣列公式時，記得輸入等號。 若未輸入等號，Excel 會將陣列解譯為文字字串，而公式會無法如預期般運作。 最後，請記住，文字與數字可以混用。

矩陣常數無法運作時，請查看是否有下列問題：

• 有些元素可能沒有用適當的字元分隔。 如果您省略逗號或分號，或將逗號或分號放在錯誤的位置，陣列常數可能無法正確建立，或者您可能會看到警告訊息。

• 您已選取的儲存格範圍可能與常數中的元素數目不符。 例如，如果選取了由六個儲存格組成的一欄，並使用五儲存格常數，那麼空白儲存格中就會顯示 #N/A 錯誤值。 相反地，如果選取的儲存格太少，Excel 會省略那些沒有對應儲存格的值。

以下範例提出多種方式，為您示範如何在陣列公式中使用矩陣常數。 部分範例使用 TRANSPOSE 函數 將列轉換成欄，反之亦然。

以陣列中各項目相乘

1. 建立新的工作表，然後選取四欄寬三列高的空白儲存格區塊。

2. 輸入下列公式，然後按Ctrl+Shift+Enter：

   ={1,2,3,4;5,6,7,8;9,10,11,12}*2

求陣列中項目的平方值

1. 選取四欄寬三列高的空白儲存格區塊。

2. 輸入下列陣列公式，然後按Ctrl+Shift+Enter：

   ={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

   另外也可以輸入此陣列公式，其中使用脫字符號運算子 (^)：

   ={1,2,3,4;5,6,7,8;9,10,11,12}^2

轉置一維列

1. 選取由五個空白儲存格組成的一欄。

2. 輸入下列公式，然後按Ctrl+Shift+Enter：

   =TRANSPOSE({1,2,3,4,5})

   即使輸入水平矩陣常數，TRANSPOSE 函數也會將矩陣常數轉換至欄中。

轉置一維欄

1. 選取由五個空白儲存格組成的一列。

2. 輸入下列公式，然後按Ctrl+Shift+Enter：

   =TRANSPOSE({1;2;3;4;5})

即使輸入垂直矩陣常數，TRANSPOSE 函數也會將常數轉換至列中。

轉置二維常數

1. 選取三欄寬四列高的儲存格區塊。

2. 輸入下列常數，然後按Ctrl+Shift+Enter：

   =TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})

   TRANSPOSE 函數會將各列轉換成一系列欄。

本節內容提供基本陣列函數的範例。

從現有值建立陣列及矩陣常數

下列範例說明如何使用陣列公式來建立不同工作表裡各儲存格範圍之間的連結。 同時也示範如何從同一組數值建立矩陣常數。

從現有值建立陣列

1. 在 Excel 工作表上，選取 C8:E10 儲存格，然後輸入此公式：

   ={10,20,30;40,50,60;70,80,90}

   因為您正在建立數字陣列，所以在輸入 10 之前，請務必輸入 { (左大括弧)，而在輸入 90 之後，請務必輸入 } (右大括弧)。

2. 按 Ctrl+Shift+Enter，使用陣列公式在儲存格範圍 C8：E10 中輸入此數位陣列。 在您的工作表上，C8 至 E10 看起來應該像這樣：

   10	20	30
   40	50	60
   70	80	90

3. 選取 C1 至 E3 的儲存格範圍。

4. 在資料編輯欄中輸入下列公式，然後按Ctrl+Shift+Enter：

   =C8:E10

   儲存格 C1 到 E3 中會出現一個 3x3 儲存格陣列，其值與在 C8 到 E10 中所看到的值相同。

從現有的值建立矩陣常數

1. 選取儲存格 C1：C3 後，按 F2 以切換到編輯模式。 

2. 按 F9 以將儲存格參照轉換為值。 Excel 就會將數值轉換成矩陣常數。 公式現在應為 ={10，20，30;40，50，60;70，80，90}。

3. 按 Ctrl+Shift+Enter 以陣列公式輸入常數陣列。

計算儲存格範圍內的字元數

下列範例為您示範如何計算儲存格範圍內的字元數，空格包括在內。

1. 複製這整個資料表，並貼到工作表的 A1 儲存格。

   資料
   This is a
   bunch of cells that
   come together
   to form a
   single sentence.
   A2:A6 中的總字元數
   =SUM(LEN(A2:A6))
   最長的儲存格 (A3) 的內容
   =INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

2. 選取儲存格 A8，然後按 Ctrl+Shift+Enter 以查看儲存格 A2：A6 中的字元總數 (66) 。

3. 選取儲存格 A10，然後按 Ctrl+Shift+Enter 以查看儲存格 A2：A6 中最長儲存格 A2：A6 (儲存格 A3) 。

下列公式用於儲存格 A8 中，計算儲存格 A2 (A6) 66 個字元的總字元數。

=SUM(LEN(A2:A6))

在此例中，LEN 函數會傳回範圍內每個儲存格中每個文字字串的長度。 SUM 函數接著會將這些值相加，並顯示 (66) 。

找出範圍內 n 個最小的數值

本範例示範如何找出儲存格範圍內三個最小的數值。

1. 在儲存格 A1：A11 中輸入一些亂數字。

2. 選取儲存格 C1 到 C3。 這一組儲存格會保存由陣列公式傳回的結果。

3. 輸入下列公式，然後按Ctrl+Shift+Enter：

   =SMALL (A1：A11，{1;2;3})

此公式使用常數陣列評估 SMALL函數三次，並返回儲存格 A1：A10 中陣列中最小的 (1) 、第二個最小的 (2) ，以及第三個最小的 (3) 成員。若要尋找更多值，請新增更多引數至常數。 亦可使用其他函數搭配此公式，例如 SUM 或 AVERAGE。 例如：

=SUM (SMALL (A1：A10，{1，2，3})

=AVERAGE (SMALL (A1：A10，{1，2，3})

找出範圍內 n 個最大的數值

若要找出範圍內最大的數值，可以用 LARGE 函數取代 SMALL 函數。 除此之外，也可如下列範例般，使用 ROW 和 INDIRECT 函數。

1. 選取儲存格 D1 到 D3。

2. 在資料編輯欄中輸入此公式，然後按Ctrl+Shift+Enter：

   =LARGE (A1：A10，ROW (INDIRECT ("1：3") ) )

此時，如果對 ROW 和 INDIRECT 函數稍有了解，可能會有幫助。 您可以使用 ROW 函數來建立連續整數的陣列。 例如，選取練習活頁簿中包含 10 個儲存格的空白欄，輸入此陣列公式，然後按Ctrl+Shift+Enter：

=ROW(1:10)

公式隨即建立含 10 個連續整數的欄。 若要查看潛在的問題，請在含陣列公式的範圍上方 (亦即第 1 列上方) 插入列。 Excel 會調整列參照，而公式會產生 2 到 11 的整數。 若要修正該問題，可在公式中加入 INDIRECT 函數：

=ROW(INDIRECT("1:10"))

INDIRECT 函數使用文字字串作為引數 (因此 1:10 範圍才會用雙引號括住)。 您插入列或移動陣列公式時，Excel 並不會調整文字值。 因此，ROW 函數永遠都會產生您所要的整數陣列。

讓我們來看看您先前使用的公式 ：=LARGE (A5：A14，ROW (INDIRECT ("1：3") ) ) -從內括弧開始向外工作 ：INDIRECT 函數會返回一組文字值，在此案例中為值 1 到 3。 ROW函數會進而產生三個儲存格欄陣列。 LARGE 函數會使用儲存格範圍 A5：A14 中的值，而且會針對ROW函數所返回的每個參照評估三次。 值 3200、2700 和 2000 會回到三個儲存格欄陣列。 如果您想要尋找更多值，您可以在 INDIRECT 函數中新增較大的 儲存格 範圍。

與先前範例一樣，您可以將此公式與其他函數一起使用，例如SUM和AVERAGE。

找出儲存格範圍內最長的文字字串

回到先前的文字字串範例，在空白儲存格中輸入下列公式，然後按Ctrl+Shift+Enter：

=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

文字「一堆儲存格」會出現。

讓我們更仔細看一下公式，從內元素開始往外分析。 LEN函數會返回儲存格範圍 A2：A6 中每個專案的長度。 MAX 函數會計算這些專案中的最大值，該值對應到儲存格 A3 中最長的文字字串。

下面的情形就比較複雜了。 MATCH 函數會計算含最長文字字串之儲存格的位移 (相對位置)。 若要執行這項作業，必須有以下三個引數：查閱值、查閱陣列、比對方式。 MATCH 函數會在查閱陣列中搜尋指定的查閱值。 在本範例中，查閱值是最長的文字字串：

(MAX (LEN (A2：A6) )

該字串存放於以下陣列中：

LEN (A2：A6)

比對方式引數是 0。 比對方式可以包含 1、0 或 -1 的值。 如果您指定 1，MATCH 會傳回小於或等於查閱值的最大值。 如果您指定 0，MATCH 會傳回第一個完全等於查閱值的值。 如果您指定 -1，MATCH 函數會尋找大於或等於指定查閱值的最小值。 如果您省略比對方式，Excel 會假設為 1。

最後，INDEX 函數會採用以下引數：陣列，以及該陣列中的列號和欄號。 儲存格範圍 A2：A6 提供陣列 ，MATCH 函數提供儲存格位址，而最後引數 (1) 會指定值來自陣列的第一欄。

本節內容提供進階陣列函數的範例。

加總含錯誤值的範圍

若您嘗試加總的範圍內包含錯誤值 (如 #N/A)，Excel 中的 SUM 函數就無法運作。 這個範例會示範如何加總名為「資料」且包含錯誤的範圍內的值。

=SUM(IF(ISERROR(資料),"",資料))

此公式會建立新陣列，其中包含減去任何錯誤值的原始值。 ISERROR 函數會從內部函數開始往外分析，搜尋儲存格範圍 (資料) 中的錯誤。 IF 函數會在您指定之條件的計算結果為 TRUE 時傳回特定的值，並在結果為 FALSE 時傳回另一個值。 在此例中，它會對所有錯誤值傳回空字串 ("")，這是因為計算結果為 TRUE；而且還會傳回範圍 (資料) 的其餘值，這是因為計算結果為 FALSE，表示當中不包含錯誤值。 SUM 函數接著會計算篩選陣列的總計。

計算範圍內錯誤值的數目

本範例類似於先前的公式，但是會傳回名為「資料」的範圍內的錯誤值而不是篩選除去：

=SUM(IF(ISERROR(資料),1,0))

此公式會建立陣列，其中包含值為 1 的含錯誤儲存格，以及值為 0 的不含錯誤儲存格。 您可以簡化公式，並且移除 IF 函數的第三個引數來得到相同的結果，如下所示：

=SUM(IF(ISERROR(資料),1))

如果不指定引數，只要儲存格不包含錯誤值，IF 函數就會傳回 FALSE。 您可以更進一步將公式簡化如下：

=SUM(IF(ISERROR(資料)*1))

此公式運作無誤，因為 TRUE*1=1 而 FALSE*1=0。

根據條件加總數值

您可能必須根據條件加總數值。 例如，以下陣列公式只加總名為「銷售」的範圍內的正整數：

=SUM(IF(銷售>0,銷售))

IF 函數會建立由正值與偽值組成的陣列。 SUM 函數基本上會忽略偽值，原因在於 0+0=0。 您在此公式中使用的儲存格範圍可以包含任何數目的列和欄。

您也可以加總符合多個條件的數值。 例如，以下陣列公式會計算大於 0 且小於或等於 5 的數值：

=SUM((銷售>0)*(銷售<=5)*(銷售))

請牢記在心，如果範圍內包含一個或多個非數值儲存格，那麼此公式就會傳回錯誤。

您也可以建立一些只使用一種 OR 條件的陣列公式。 例如，您可以加總小於 5 以及大於 15 的數值：

=SUM(IF((銷售<5)+(銷售>15),銷售))

IF 函數會找出小於 5 以及大於 15 的所有數值，然後將這些數值傳遞給 SUM 函數。

您不能直接在陣列公式中使用 AND 與 OR 函數，因為這些函數會傳回單一結果，不是 TRUE 就是 FALSE，而陣列函數需要的是結果陣列。 您可以使用先前公式中出現的邏輯，來解決這項問題。 換句話說，您在符合 OR 或 AND 條件的數值上，執行加法或乘法之類的數學運算。

計算排除零以外的平均值

以下範例為您示範如何在必須取得範圍內的平均值時，將範圍內的零移除。 公式會使用名為「銷售」的資料範圍：

=AVERAGE(IF(銷售<>0,銷售))

IF 函數會建立不等於 0 的值陣列，然後將這些值傳遞給 AVERAGE 函數。

計算兩個儲存格範圍之間差異的數目

此陣列公式會針對「我的資料」與「你的資料」這兩個儲存格範圍內的數值進行比較，然後傳回這兩個範圍之間的差異數目。 如果兩個範圍的內容完全相同，公式會傳回 0。 若要使用此公式，儲存格範圍必須是相同大小及相同維度 (例如，如果「我的資料」的範圍是 3 列 5 欄，「你的資料」也必須是 3 列 5 欄)：

=SUM(IF(我的資料=你的資料,0,1))

此公式會建立一個新陣列，而且該陣列的大小跟您要比較之範圍相同。 IF 函數會用 0 值和 1 值填滿陣列 (0 代表比對不相符，1 代表完全相同的儲存格)。 SUM 函數接著會傳回陣列中數值的總和。

公式可簡化如下：

=SUM (1* (MyData<>YourData) )

此公式就像是可計算範圍內有錯誤值的公式，之所以可以順利運作，就是因為 TRUE*1=1 而 FALSE*1=0。

找出範圍中最大值的位置

以下陣列公式會傳回「資料」單欄範圍內最大值的列號：

=MIN(IF(資料=MAX(資料),ROW(資料),""))

IF 函數會建立新陣列，該陣列對應到名為「資料」的範圍。 若對應的儲存格包含範圍內的最大值，則該陣列會包含列號。 否則，該陣列會包含空字串 ("")。 MIN 函數會使用新陣列作為其第二個引數，並傳回最小值，該值對應的是「資料」中最大值的列號。 如果名為「資料」的範圍包含相同的最大值，則公式會傳回第一個值的列。

如果您要傳回最大數值的實際儲存格位址，請使用以下公式：

=ADDRESS(MIN(IF(資料=MAX(資料),ROW(資料),"")),COLUMN(資料))