陣列公式的規則和範例

陣列公式的規則和範例

附註: 我們想要以您的語言,用最快的速度為您提供最新的說明內容。本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。讓這些內容對您有所幫助是我們的目的。希望您能在本頁底部告訴我們這項資訊是否有幫助。此為英文文章出處,以供參考。

陣列公式是可以在陣列中的一或多個項目上執行多個計算的公式。您可以將陣列為列或欄的值,或組合的列和資料行的值。陣列公式可以傳回多個結果或單一結果。

開始使用Office 365年 9 月 2018年更新,可以傳回多個結果的任何公式會自動落入往下移,或在相鄰儲存格。在 [行為這項變更也伴隨數個新動態陣列函數。動態陣列公式,無論他們使用的現有函數或動態陣列函數,只需要輸入單一儲存格,然後按下Enter來確認。更早版本,舊版的陣列公式需要先選取整個輸出範圍,然後確認公式使用Ctrl + Shift + Enter。這些是通常稱為CSE公式。

若要執行複雜的工作,例如,您可以使用陣列公式:

  • 快速建立範例資料集。

  • 計算儲存格範圍中包含的字元數目。

  • 加總只的數字符合特定條件,例如範圍內的最低值或落在上方和下方邊界之間的數字。

  • 加總值每範圍中的值。

下列範例會顯示如何建立多儲存格及單儲存格陣列公式。可能的話,我們已包含部分動態陣列函數,以及以動態和舊版陣列方式輸入現有的陣列公式的範例。

下載我們的範例

下載所有陣列公式中的範例這篇文章的範例活頁簿

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

  • 多儲存格陣列公式

    儲存格 H10 中的多儲存格陣列函數 = F10:F19 * G10:G19 計算數字的汽車賣出單位價格

  • 以下我們正在輸入來計算的雙門車和房車每位銷售員的總銷售額= F19:F19 * G10:G19儲存格 H10 中。

    當您按下enter 鍵時,您會看到下儲存格 H10:H19 不要的結果。請注意,不要大範圍以醒目提示框線當您選取 [不要大範圍內的任何儲存格。您也可能會發現的公式中的儲存格 H10:H19 會呈現灰色。只要有.參照的因此如果您想要調整公式,您會需要選取儲存格 H10,主版公式所在。

  • 單一儲存格陣列公式

    若要計算總計 =SUM(F10:F19*G10:G19) 與單一儲存格陣列公式

    在範例活頁簿的儲存格 h20 這樣,輸入或複製並貼上=SUM(F10:F19*G10:G19),然後按enter 鍵

    在此情況下,Excel 乘以值的陣列 (儲存格範圍 F10 透過 G19),,並將總計放在一起使用 SUM 函數。結果是 $1,590,000 銷售額的總計。

    此範例顯示此類型的公式可強大功能。例如,假設您有 1000 列的資料。您可以藉由建立不需要公式向下拖曳到 1000 列在單一儲存格中的陣列公式來加總數部分或全部的資料。此外,請注意,在儲存格中 H20 單一儲存格公式完全獨立的多儲存格公式 (透過 H19 的儲存格 H10 中的公式)。這是使用陣列公式的其他優點 — 具彈性。您可以變更欄 H 的其他公式而不會影響 h20 這樣的公式。也可以考慮具備獨立的總計,像這樣,如有助於驗證結果的精確度。

  • 動態陣列公式還具備下列優點:

    • 一致性   如果您按一下任何儲存格 H10 中向下,您會看到相同的公式。一致性,可以協助確保更大的精確度。

    • 安全   您無法覆寫多儲存格陣列公式的元件。例如,按一下 [儲存格 H11,然後按 Delete。Excel 不會變更陣列的輸出。若要變更其,必須先選取的陣列或儲存格 H10 中的左上角儲存格。

    • 檔案較小   您通常可以使用一個陣列公式,而不是幾個中繼公式。車銷售範例,例如使用陣列公式計算結果在欄 e。如果您使用標準公式例如 = F10 * G10、 F11 * G11、 F12 * G12,等,在您想要使用 11 不同的公式計算相同的結果。這不是大問題,但如果您的資料列總計嗎?然後,可顯示較大的差異。

    • 效率   陣列函數可以是有效的方式來建立複雜的公式。陣列公式 =SUM(F10:F19*G10:G19) 等同於此: = SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19)。

    • 溢出   動態陣列公式會自動將落入輸出範圍。如果您的來源資料位於 Excel 表格,然後動態陣列公式會自動調整大小為您新增或移除資料。

    • #SPILL ! 錯誤   動態陣列推出#SPILL ! 錯誤,這表示預定不要大範圍遭到封鎖基於某種原因。當您解決封鎖時,公式會自動不要大。

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

= {1,2,3,4,5}= {「 一月 」、 「 年 2 月 」,"三月"}

如果您是使用逗號分隔項目,您會建立水平陣列 (列)。如果您使用分號分隔項目,您會建立垂直陣列 (欄)。若要建立二維陣列,您可以分隔以逗號,每個資料列中的項目,並分隔每個資料列,以分號區。

下列程序可讓您稍加練習如何建立水平、 垂直及二維常數。我們會顯示使用自動產生矩陣常數的順序函數的範例,以及手動輸入常數陣列。

  • 建立水平常數

    使用先前範例中,從活頁簿或建立新的活頁簿。選取任何空白儲存格,然後輸入=SEQUENCE(1,5)。順序函數來建立的 1 的資料列 5 的資料行陣列= {1} 1,2,3,4,5相同。會顯示下列結果:

    使用 =SEQUENCE(1,5) 建立水平矩陣常數,或 = {1,2,3,4,5}

  • 建立垂直常數

    選取下面,會議室與任何空白儲存格,然後輸入=SEQUENCE(5),或= {1,2,3; 4,5}。會顯示下列結果:

    建立垂直陣列常數 = SEQUENCE(5),或 = {1,2,3; 4,5}

  • 建立二維常數

    選取聊天室的右側和下方的任何空白儲存格,然後輸入=SEQUENCE(3,4)。您會看到下列結果:

    建立 3 的資料列 4 欄 =SEQUENCE(3,4) 使用常數陣列

    您也可以輸入: 或 = {1,2,3,4; 5,6,7,8; 9,10,11,12},但您也需要注意您放置逗號或分號區隔。

    如您所見,[順序] 選項的優點嚴重比手動輸入您的陣列常數值。主要,它會將儲存您的時間,但它也可以協助降低手動輸入錯誤。也很容易閱讀,特別是分號很難辨別從逗號分隔符號。

以下是範例用途的放大公式屬於陣列常數。在範例活頁簿中,移至 [固定在公式中的工作表,或建立新的工作表。

在儲存格 d9 中,我們輸入=SEQUENCE(1,5,3,1),但您也可以在儲存格 A9:H9 輸入 3、 4、 5、 6 和 7。沒有任何相關的特定數字的選取範圍的特殊項目,我們只選擇以外 1-5 的差異。

在儲存格 E11,輸入= SUM (D9:H9*SEQUENCE(1,5)),或= SUM (D9:H9* 1,2,3,4,5 {})。公式會傳回 85。

在公式中使用常數陣列。在此範例中,我們使用 = SUM (D9:H(*SEQUENCE(1,5))

依序函數建置相當於陣列常數 {1,2,3,4,5}。Excel 上執行作業第一次以括弧括住的運算式,因為扮演的兩個項目是儲存格和中的值 D9:H9,乘法運算子 (*)。此時,公式會將已儲存的陣列常數中的對應值的值。是的同等方式:

=SUM(D9*1,E9*2,F9*3,G9*4,H9*5)=SUM(3*1,4*2,5*3,6*4,7*5)

最後,SUM 函數加總的值,並傳回 85。

若要避免使用已儲存的陣列,並將運算整個保存在記憶體,您可以將它取代成另一個矩陣常數:

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

您可以使用常數陣列中的項目

  • 常數陣列可以包含數字、 文字、 邏輯值 (例如 TRUE 和 FALSE),例如 # n/A 錯誤值。您可以使用數字的整數,小數位數,與科學符號格式。如果您要包含的文字,您需要與其周圍加上引號 ("文字")。

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

使用常數陣列的最佳方式是它們的名稱。已命名之常數更易於使用,而他們可以隱藏部分複雜的其他人的陣列公式。若要為常數陣列命名並使用公式中,執行下列動作:

移至公式>已定義之名稱>定義名稱。在 [名稱] 方塊中,輸入 Quarter1。在 [參照到] 方塊中輸入下列常數 (記得要手動輸入大括弧):

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

[] 對話方塊現在看起來應該像這樣:

從公式 > 已定義之名稱 > 名稱管理員 > 新增的已命名的陣列常數

按一下[確定],然後選取任何] 列的三個空白儲存格,然後輸入= Quarter1

會顯示下列結果:

在公式中使用的已命名的陣列常數、 想 = 的 Quarter1,其中 Quarter1 已定義為 = {「 一月 」、 「 2 月 」 「 年 3 月"}

如果您想要垂直不要,而不是水平的結果,您可以使用=轉置(Quarter1)

如果您想要顯示的 12 個月,等建置財務陳述式時,您可能會使用清單,您可以根據一關閉目前的年份順序函數。此函數的好處是,即使只將月份顯示,是有效的日期前,您可以在其他計算中使用。在範例活頁簿中,您會為常數陣列快速的範例資料集的工作表上找到這些範例。

=TEXT(DATE(YEAR(TODAY()),SEQUENCE(1,12),1),"mmm")

使用文字、 日期、 年份,TODAY、 和順序函數的組合,來建立 12 個月的動態清單

這會使用DATE 函數來建立根據今年、 順序常數陣列介於 1 到 12 為年 1 月 12 月,透過然後TEXT 函數將 「 mmm 」 所要的顯示格式 (一月、 二月、 三月、 等)。如果您想要顯示完整月份名稱,例如年 1 月,您可以使用 「 mmmm 」。

當您使用的命名的常數陣列公式時,請記得輸入等號時,為 = Quarter1,而不只是 Quarter1。如果沒有的話,Excel 會解譯的文字字串中的陣列,公式不會如預期運作。最後,請記住,您可以使用函數、 文字和數字的組合。所有取決於您所要取得如何創意。

下列範例會示範幾個中,您可以將在陣列公式中使用常數陣列的方式。部分範例使用TRANSPOSE 函數將資料列的資料行,反之亦然。

  • 多個陣列中每個項目

    輸入= 順序 (1,12) * 2,或= {1,2,3,4; 5,6,7,8; 9,10,11,12} * 2

    您也可以使用 (/) 除以、 (+) 新增] 並減去與 (-)。

  • 求陣列中項目的平方值

    輸入= 順序 (1,12) ^2,或= {1,2,3,4; 5,6,7,8; 9,10,11,12} ^2

  • 在陣列中尋找平方根平方項目

    輸入=SQRT(SEQUENCE(1,12)^2),或=SQRT({1,2,3,4;5,6,7,8;9,10,11,12}^2)

  • 轉置一維列

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

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

  • 轉置一維欄

    輸入=TRANSPOSE(SEQUENCE(5,1)),或= 轉置 ({1,2,3; 4,5})

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

  • 轉置二維常數

    輸入=TRANSPOSE(SEQUENCE(3,4)),或= 轉置 ({1,2,3,4; 5,6,7,8; 9,10,11,12})

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

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

  • 從現有值建立陣列

    下列範例會說明如何使用陣列公式從現有的陣列中建立新的陣列。

    輸入=SEQUENCE(3,6,10,10),或= {10,20,30,40,50,60; 70,80,90,100,110,120; 130,140,150,160,170,180}

    請務必輸入 {(左大括弧) 輸入 10 之前和} (右大括弧) 輸入 180,因為您正在建立數字陣列之後。

    接下來,輸入= d9 中加入 #,或= D9:I11中空白儲存格。3 x 6 的儲存格陣列會顯示您在 D9:D11 中看到相同的值。# 符號稱為溢出範圍運算子,而且的參照整個陣列範圍,而不需要輸入查看 Excel 的方式。

    使用溢出範圍運算子 (#) 來參照現有的陣列

  • 從現有的值建立常數陣列

    您可以採取溢出陣列公式的結果,將會轉換成其組。選取儲存格 D9,然後按F2切換到編輯模式。接下來,請按F9以轉換為 Excel 然後會轉換為常數陣列的值的儲存格參照。當您按下enter 鍵,公式 = d9 中加入 #,應該現在會 = {10,20,30; 40,50,60; 70,80,90}。

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

    下列範例會示範如何計算的儲存格範圍中的字元數。這包含空格。

    計算範圍,並使用文字字串的其他陣列中的字元的總數

    = SUM (LEN(C9:C13))

    在此情況下, LEN 函數會傳回每個文字字串的長度,在每一個範圍內的儲存格。SUM 函數然後將這些值相加,並顯示結果 (66)。如果您想要平均的字元數,您可以使用:

    = AVERAGE (LEN(C9:C13))

  • C9:C13 範圍中最長儲存格的內容

    =INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)

    此公式運作的資料範圍包含單一資料行的儲存格時才。

    讓我們來看看公式中,從內部的項目開始,然後向外工作。LEN 函數會傳回儲存格範圍 d2: d6 中的每個項目長度。MAX 函數會計算最大值之間的項目,其會對應儲存格 D3 中的最長文字字串。

    以下是變得更複雜。MATCH 函數會計算包含最長的文字字串的儲存格的位移 (相對位置)。若要執行這項作業,需要三個引數: 查閱值,以 lookup 陣列中,符合的項目類型。MATCH 函數搜尋指定的查閱值的查閱陣列。在此情況下,查閱值是最長的文字字串:

    MAX(LEN(C9:C13)

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

    LEN(C9:C13)

    符合的項目類型引數在本例中為 0。符合的項目類型可以是 1、 0 或值-1。

    • 1-傳回最大的值小於或等於查閱值

    • 0-傳回第一個值完全等於查閱值

    • -1-傳回大於或等於指定的查閱值的最小值

    • 如果您省略符合的項目類型時,Excel 會假設 1。

    最後, INDEX 函數會採用下列引數: 陣列,與該陣列中的列和欄數字。儲存格範圍 C9:C13 提供陣列、 MATCH 函數提供的儲存格地址,且,最後的引數 (1) 可讓您指定的值是來自陣列中的第一欄。

    如果您想要取得的最小的文字字串的內容,您會取代最上面範例中最小值

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

    此範例會示範如何找出儲存格範圍內三個最小值建立陣列中的儲存格 B9:B18has 的範例資料的位置: = INT (RANDARRAY(10,1) * 100)。請注意,RANDARRAY 動態函數,因此您會收到一組新的隨機數字計算每次。

    若要尋找的第 n 個最小值的陣列公式的 excel: =SMALL(B9#,SEQUENCE(D9))

    輸入=SMALL(B9#,SEQUENCE(D9)= 小型 (B9:B18,{1,2,3})

    此公式使用常數陣列來評估SMALL 函數三次,並傳回最小的 3 個成員儲存格 B9:B18 中所包含的陣列中 3 是變數儲存格 D9 中的值的位置。若要尋找更多的值,您可以增加順序函數中的值,或是將常數新增多個引數。您也可以使用其他的函數,例如加總] 或 [平均此公式。例如:

    =SUM(SMALL(B9#,SEQUENCE(D9))

    =AVERAGE(SMALL(B9#,SEQUENCE(D9))

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

    若要尋找的最大值範圍中,您可以使用LARGE 函數取代 SMALL 函數。此外,下列範例會使用INDIRECT函數。

    輸入= LARGE (B9 #,列 (間接 (「 1:3 」))),或= LARGE (B9:B18,ROW(INDIRECT("1:3")))

    此時,它可能協助認識,列和 INDIRECT 函數。若要建立連續的整數的陣列,您可以使用 ROW 函數。例如,選取 [空白,並輸入:

    =ROW(1:10)

    公式所建立 10 個連續整數資料的行。若要查看的潛在問題,上方插入列包含陣列公式的範圍 (也就是上述列 1)。Excel 會調整列參照與公式現在產生整數 2 至 11。若要修正此問題,您可以新增 INDIRECT 函數的公式:

    = 列 (INDIRECT (「 1:10 」))

    INDIRECT 函數使用文字字串做為其引數 (這是為什麼範圍 1:10 周圍括住)。插入列或否則移動陣列公式時,Excel 會調整文字值。如此一來,ROW 函數一定會產生您想要的整數陣列。您可以輕鬆地使用順序:

    =SEQUENCE(10)

    讓我們逐行查看先前所用的公式: = LARGE (B9 #,列 (間接 (「 1:3 」))),從內部括號和向外使用: INDIRECT 函數會傳回的一組文字值,在本例中的值 1 到 3。ROW 函數產生的三個儲存格] 欄的陣列。LARGE 函數的儲存格範圍 B9:B18,使用的值,則會評估一次的每個 ROW 函數所傳回的參考的三個時間。如果您想要尋找更多的值,則會新增至 INDIRECT 函數的更大的儲存格範圍。最後,如同小型的範例中,您可以使用此公式搭配其他函數,如 SUM 和 AVERAGE。

  • 加總含錯誤值的範圍

    在 Excel 中的 SUM 函數無法運作時要加總範圍包含錯誤值,例如 #VALUE !或 # n/A。此範例為您示範如何加總 「 包含錯誤的資料範圍內的值:

    使用陣列處理錯誤。=SUM(IF(ISERROR(Data),"",Data),例如加總範圍 「 資料,即使其包含錯誤,例如 #VALUE ! 或 #NA !。

  • =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(Sales>0,Sales)) 會加總大於 0,稱為 「 銷售範圍中的所有值。

例如,此陣列公式加總只 「 銷售,表示在上述範例中的儲存格 E9:E24 範圍內正整數:

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

IF 函數會建立一個正數和 false 值的陣列。SUM 函數基本上忽略 false 的值,因為 0 + 0 = 0。任何數目的列與欄可以包含您在此公式中使用的儲存格範圍。

您也可以加總符合多個條件的值。例如,此陣列公式計算值大於 0小於 2500年:

=SUM((Sales>0)*(Sales<2500)*(Sales))

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

您也可以建立使用 OR 條件類型的陣列公式。例如,您可以加總的值大於 0小於 2500年:

=SUM(IF((Sales>0)+(Sales<2500),Sales))

您無法使用 AND 和或在陣列公式中直接函數,因為這些函數: 傳回單一結果,則為 TRUE 或 FALSE,而且陣列函數需要結果的陣列。您可以使用顯示在上一個公式中的邏輯來解決問題。換句話說,您可以執行數學運算,例如加法或乘法或符合的值或條件。

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

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

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

此陣列公式比較兩個命名為我的資料和你的資料的儲存格範圍中的值,並傳回兩者之間的差異的數目。如果兩個範圍的內容完全相同,公式會傳回 0。若要使用此公式,儲存格範圍必須為相同大小和維度相同。例如,如果我的資料是依 5 欄 3 列的範圍,你的資料也必須是 5 的欄 3 列:

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

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

公式可簡化如下:

=SUM(1*(我的資料<>你的資料))

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

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

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

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

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

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

您可以找到類似的範例資料集之間的差異工作表上的活頁簿中。

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

  • 多儲存格陣列公式

複製整個資料表,並將它貼到空白工作表的 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}

E 欄的總計是由陣列公式計算而得

  • 建立單一儲存格陣列公式

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

=SUM(C2:C11*D2:D11)

在此情況下,Excel 將陣列 (儲存格範圍 C2 到 D11) 中的值相乘,並將總計放在一起使用SUM 函數。結果是 $1,590,000 銷售額的總計。此範例顯示此類型的公式可強大功能。例如,假設您有 1000 列的資料。您可以藉由建立不需要公式向下拖曳到 1000 列在單一儲存格中的陣列公式來加總數部分或全部的資料。

此外,請注意,在儲存格中 D13 單一儲存格公式完全獨立的多儲存格公式 (儲存格 E2 透過 E11 中的公式)。這是使用陣列公式的其他優點 — 具彈性。您無法變更 E 欄中的公式,或完全,刪除該資料行,而不會影響 D13 中的公式。

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

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

  • 安全   您無法覆寫多儲存格陣列公式的元件。例如,按一下 E3 的儲存格,然後按 [刪除。您必須是選取整個儲存格範圍 (透過 E11 E2) 和變更整個陣列公式,或離開現狀陣列。為新增的安全量值,您必須按Ctrl + Shift + Enter來確認公式的任何變更。

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

一般而言,陣列公式會使用標準公式的語法。它們都以等號 (=),而且您可以使用大部分的內建的 Excel 函數,在陣列公式。主要差別,使用陣列公式,當您按下Ctrl + Shift + Enter來輸入公式。當您執行此動作時,Excel 會以括號陣列公式,如果您必須手動輸入大括弧公式會轉換為文字字串,並不適用於。

陣列函數可以是有效的方式來建立複雜的公式。陣列公式=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)

重要: 每當您要輸入陣列公式時,請按Ctrl + Shift + Enter 。適用於單一儲存格和多儲存格的公式。

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

  • 您必須在輸入公式「之前」,選取要放置結果的儲存格範圍。您是在選取 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}。請記住,Excel 不括住常數陣列以括號。實際輸入它們。也請記住,您新增至陣列公式的常數之後,按下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

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

從現有的值建立常數陣列

  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來查看總數 (66) 儲存格 a2: a6 中的字元。

  3. 選取儲存格 A10,,然後按Ctrl + Shift + Enter來查看內容的最長儲存格 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函數三次,並傳回最小的 (1)、 第二個最小 (2) 和第三個最小 (3) 中的成員所包含的儲存格 a1: a10 若要尋找 [多個值的陣列常數陣列,您可以新增多個引數常數。您也可以使用其他的函數,例如加總] 或 [平均此公式。例如:

= SUM (小型企業版 (A1: A10,{1 2、 3})

= AVERAGE (小型企業版 (A1: A10,{1 2、 3})

尋找n範圍中的最大值

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

  1. 選取儲存格 D1 透過 D3。

  2. 在資料編輯列中,輸入此公式,,然後按下Ctrl + Shift + Enter:

    = LARGE(A1:A10,ROW(INDIRECT("1:3")))

此時,它可能協助認識INDIRECT函數。若要建立連續的整數的陣列,您可以使用ROW函數。例如,選取 10 的儲存格的空白欄練習活頁簿中輸入此陣列公式,,然後按下Ctrl + Shift + Enter:

=ROW(1:10)

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

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

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

讓我們來看一下先前所用的公式, = 大 (A5:A14,ROW(INDIRECT("1:3"))) ,從內部括號括住開始,然後向外工作: INDIRECT函數會傳回的一組文字值,在本例中的值 1 到 3。ROW函數產生的三個儲存格的欄陣列。LARGE函數的儲存格範圍 A5:A14,使用的值,則會評估一次的每個ROW函數所傳回的參考的三個時間。3200、 2700年和 2000年的值會傳回的三個儲存格的欄陣列。如果您想要尋找更多的值,則會新增至INDIRECT函數的更大的儲存格範圍。

與使用舊版的範例中,您可以使用此公式搭配其他函數,例如SUMAVERAGE

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

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

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

文字 「 bunch of cells that 」 會出現。

讓我們來看看公式中,從內部的項目開始,然後向外工作。LEN函數會傳回儲存格範圍 a2: a6 中的每個項目長度。MAX函數會計算最大值之間的項目,其會對應的儲存格 A3 中的最長文字字串。

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

(最大值 (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 函數。

您不能直接在陣列公式中使用 ANDOR 函數,因為這些函數會傳回單一結果,不是 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*(我的資料<>你的資料))

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

找出範圍內最大值的位置

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

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

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

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

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

通知

這份文件的部分所根據一系列的 Excel 進階使用者專欄,撰寫 Colin Wilcox 和調整從章節 14 與 John Walkenbach,離職的 Excel MVP 所撰寫的活頁簿,Excel 2002 公式 15。

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

另請參閱

動態陣列與溢出陣列行為

與舊版 CSE 陣列公式的動態陣列公式

FILTER 函數

RANDARRAY 函數

SEQUENCE 函數

SINGLE 函數

SORT 函數

SORTBY 函數

UNIQUE 函數

Excel 中的 #SPILL! 錯誤

公式概觀

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與其中一位 Office 支援專員連絡以深入了解您的意見。

×