陣列公式的規則和範例

陣列公式的規則和範例

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

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

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

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

  • 快速建立範例資料集。

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

  • 符合特定條件,例如範圍內的最低值的加總唯一數字或落在上限與下限之間的數字。

  • 加總值每範圍中的值。

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

下載我們的範例

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

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

  • 多儲存格陣列公式

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

  • 以下我們正在計算的雙門車和房車每位銷售員的銷售額,藉由輸入= F10: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 函數範例,以及手動輸入常數陣列。

  • 建立水平常數

    使用先前範例的活頁簿,或建立新的活頁簿。 選取任何空白的儲存格,然後輸入=SEQUENCE(1,5)。 SEQUENCE 函數 1 的資料列,也就是 5 的資料行陣列= {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 {1})。 公式會傳回 85。

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

SEQUENCE 函數建置陣列常數 {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 月"}

如果您想要讓溢出垂直,而不是水平結果,您可以使用=TRANSPOSE(Quarter1)

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

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

使用文字、 日期、 YEAR 今天和 SEQUENCE 函數的組合來建立 12 個月的動態清單

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

當您使用的命名的常數做為陣列公式時,請記得輸入等號時,為 = Quarter1,而不只是 Quarter1。 若未輸入等號,Excel 會將陣列解譯為文字字串,而公式會無法如預期般運作。 最後,請記住,您可以使用函數、 文字與數字的組合。 這完全取決於您所要取得如何創意。

以下範例提出多種方式,為您示範如何在陣列公式中使用矩陣常數。 部分範例使用TRANSPOSE 函數來轉換資料列的資料行,反之亦然。

  • 多個陣列中每個項目

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

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

  • 求陣列中項目的平方值

    輸入= SEQUENCE (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)),或= TRANSPOSE ({1,2,3; 4,5})

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

  • 轉置二維常數

    輸入=TRANSPOSE(SEQUENCE(3,4)),或= TRANSPOSE ({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 函數會計算包含最長的文字字串的儲存格的位移 (相對位置)。 若要執行這項作業,必須有以下三個引數:查閱值、查閱陣列、比對方式。 MATCH 函數會在查閱陣列中搜尋指定的查閱值。 在本範例中,查閱值是最長的文字字串:

    MAX(LEN(C9:C13)

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

    LEN(C9:C13)

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

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

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

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

    • 如果您省略比對方式,Excel 會假設為 1。

    最後, INDEX 函數會採用這些引數: 陣列,與該陣列中的列和欄數字。 儲存格範圍 C9:C13 提供陣列、 MATCH 函數提供的儲存格位址,並最終的引數 (1) 指定值來自陣列中的第一欄。

    如果您想要取得的最小的文字字串的內容,則應該將取代 MAX 在上述範例中的最小值

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

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

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

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

    此公式使用常數陣列來評估SMALL 函數三次,並傳回儲存格 B9:B18 中所包含的陣列中的最小的 3 個成員,3 所在儲存格 D9 中的變數值。 若要尋找多個值,您可以增加 SEQUENCE 函數中的值,或新增多個引數常數。 亦可使用其他函數搭配此公式,例如 SUMAVERAGE。 例如:

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

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

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

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

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

    此時,如果對 ROW 和 INDIRECT 函數稍有了解,可能會有幫助。 您可以使用 ROW 函數來建立連續整數的陣列。 例如,選取空,並輸入:

    =ROW(1:10)

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

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

    INDIRECT 函數使用文字字串做為其引數 (這是為什麼範圍 1:10 雙方引號)。 您插入列或移動陣列公式時,Excel 並不會調整文字值。 因此,ROW 函數永遠都會產生您所要的整數陣列。 您可以輕鬆地使用順序:

    =SEQUENCE(10)

    讓我們來檢查您先前使用的公式: = 大 (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 函數基本上會忽略偽值,原因在於 0+0=0。 您在此公式中使用的儲存格範圍可以包含任何數目的列和欄。

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

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

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

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

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

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

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

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

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

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

=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(資料))

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

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

  • 多儲存格陣列公式

複製下方的整份資料表,並將它貼到空白工作表中的儲存格 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,然後按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)

重要: 每當您要輸入陣列公式時,請按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}。 別忘了! 也請記住,您新增了常數陣列公式後,按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

    3 x 3 陣列的儲存格會出現在相同的值就會與 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 要找出多個值的陣列,則新增多個引數常數。 亦可使用其他函數搭配此公式,例如 SUMAVERAGE。 例如:

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

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

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

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

  1. 選取儲存格 D1 到 D3。

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

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

此時,如果對 ROWINDIRECT 函數稍有了解,可能會有幫助。 您可以使用 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*(MyData<>YourData))

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

找出範圍中最大值的位置

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

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

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

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

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

認可

組件的本文根據一系列的 Excel 進階使用者專欄撰寫 Colin Wilcox,並採用 14 與 15 Excel 2002 公式,撰寫 John Walkenbach,前者 Excel MVP 書籍的章節。

需要更多協助嗎?

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

另請參閱

動態陣列與溢出陣列行為

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

FILTER 函數

RANDARRAY 函數

SEQUENCE 函數

SINGLE 函數

SORT 函數

SORTBY 函數

UNIQUE 函數

Excel 中的 #SPILL! 錯誤

公式概觀

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×