題 Excel公式:用簡單的英語,在INDEX(MATCH())中的數組中是什麼?


我正在嘗試用Excel幫助一個朋友 INDEX(MATCH()) 她在工作中繼承了,但這讓我很難過。人們可以幫助我更好地理解其中發生的事情嗎?這是格式:

=INDEX(
    '[Spreadsheet2.xlsx]TabOfInterest'!$B$B
    ,
    MATCH(
        1
        ,
        (
            (D34='[Spreadsheet2.xlsx]TabOfInterest'!$M:$M)
            *
            (F34='[Spreadsheet2.xlsx]TabOfInterest'!$P:$P)
            *
            (K34='[Spreadsheet2.xlsx]TabOfInterest'!$Y:$Y)
        )
        ,
        0
    )
    ,
    1
)

我可以看到,如果數字“1”(代表“TRUE”,似乎,這裡)出現在某種TRUE / 1和FALSE / 0的數組中,這些數組是通過AND-ing一起構建的其他BOOLEAN填充的陣列 (但是這應該在Excel中工作......),然後INDEX將拿起“行號” 無論-混亂 - 這,是 導致“1”存在於其中,INDEX將返回該行和列B交叉處的Spreadsheet 2單元格的值。

但是我不知道在MATCH的第二個參數中發生了什麼。

我試著玩一個空白的電子表格,甚至無法推斷出什麼 =(SingleCell=RangeOfCells)    - 或者就此而言,是什麼 =RangeOfCells 確實 - 返回值似乎取決於公式鍵入的單元格(它的行偏離它正在檢查的數據),並添加 $ 在那些公式中的行號之前沒有區別。

關於引用範圍以及在單元格和範圍之間進行“平等”比較的基本原理,我是否需要知道以更好地了解此公式中發生的情況,最終是什麼 INDEX(MATCH()) 正試圖抬頭?

究竟是什麼  這個陣列?

(D34='[Spreadsheet2.xlsx]TabOfInterest'!$M:$M)
*
(F34='[Spreadsheet2.xlsx]TabOfInterest'!$P:$P)
*
(K34='[Spreadsheet2.xlsx]TabOfInterest'!$Y:$Y)

此外,如果我不得不猜測,我想我會說整體功能會這樣做,但是我無法解釋為什麼困擾我:

如果 有個 單排 Spreadsheet2中所有這三個條件都為TRUE:

  • 此INDEX-MATCH所在的任何電子表格的單元格D34出現在Spreadsheet2的列“M”AND中
  • 此INDEX-MATCH所在的任何電子表格的單元格F34出現在Spreadsheet2的列“P”AND中
  • 此INDEX-MATCH所在的任何電子表格的單元格K34出現在Spreadsheet2的列“Y”中

然後 返回Spreadsheet2,B列中的單元格的值,無論是-read-of-Spreadsheet2-that-c​​ompound-condition-was-true-for。


2
2017-07-21 18:26


起源


@HackSlash - 聽起來像 D34='[Spreadsheet2.xlsx]TabOfInterest'!$M:$M 檢查看 哪一個 M列中的值與D34的值匹配,而不是 是否所有人都這樣做 (然後返回一個trues和falses數組,指出哪些做了)。請參閱Bandersnatch的回答。 - k..


答案:


讓我們簡化一下,看看我們是否能弄清楚發生了什麼。在下面的圖片中,我設置了D1,E1和F1來模擬公式中的D34,F34和K34,並用值填充它們。對於笑臉,我在前三個單元格下面添加了兩行值。更多關於那些以後。

然後在H,I和J列中,我添加了相當於M,P和Y列的數組。我也將它們縮短了一些,主要是因為如果它們是整列,下面的調試技巧將不起作用。

在G7中,我只輸入了上面公式的MATCH()部分。如包圍它的大括號所示,它是一個數組公式。通過鍵入CTRL-Shift Enter輸入,Excel添加大括號。完成後,您可以再填充兩行。很明顯,MATCH()函數提出了一些答案。

enter image description here

你是正確的,乘法邏輯值相當於AND()操作,除了它給出1和0作為結果而不是True和False。您可以通過輸入來檢查 =True*True 要么 =True*False 例如,在一個單元格中。

你問相當奇怪的數組中可能是什麼(是的,它是一個數組)。那麼讓我們來看看Excel認為它的價值是什麼。

單擊其中一個公式,然後單擊要查看其值的公式元素 - 在本例中,它是“lookup_array”。這突出顯示了lookup_array,如下圖所示。

enter image description here

現在點擊F9。這使Excel顯示了  突出顯示的元素。

enter image description here

並且Excel告訴我們公式的突出顯示部分的值是數組{1; 0; 0; 0; 0}。如果對G8和G9執行此操作,您將看到值分別為{0; 0; 0; 0; 1}和{0; 0; 0; 1; 0}。因此,MATCH()函數在這些數組中找到1的位置,即1,5和4。

您可以這樣想:lookup_array的第一部分檢查D1等於H1:H5 - 該部分返回{1; 1; 0; 0; 0}。然後檢查I1中的E1:I5給出{1; 0; 1; 0; 0}。檢查J1中的F1:J5給出{1; 0; 1; 0; 0}。對所有這三個進行AND運算得出{1; 0; 0; 0; 0}這是Excel顯示整個(不可否認的奇怪)數組的結果。類似地,G8中的公式找到{0; 0; 0; 0; 1},{0; 1; 0; 0; 1}和{0; 0; 0; 0; 1)然後將它們與它們取得{0; 0; 0; 0; 1}。

你對正在發生的事情的猜測是正確的:MATCH()找到了 第一 H,I和J中的行(匹配類型= 0),其值與D,E和F中的單元格匹配,然後INDEX()返回B列中的該位置。

您可以調整上面的值並自行檢查。記得使用CTRL-Shift Enter輸入數組公式 - 你會做很多事情,因為Excel有一個錯誤/功能,點擊公式,然後點擊return或tab給出#VALUE!錯誤。 CTRL-Z將撤消該操作。

希望這會有所幫助,祝你好運。


4
2017-07-22 03:54



E1=$I$1:$I$5:“檢查I1中的E1:I5給出{1; 0; 1; 0; 0}“ - 謝謝!這很難說,因為當你輸入正常的公式時,”#VALUE“怪癖 (按“輸入”或“標籤”)。我還看到,因為在文檔中,當Excel“對兩個數組執行操作而不是兩個標量”時,它只對數組中每個相同位置的標量對應用相同的操作 (我回去查找代數,關於矢量乘法在我死之前是如何工作的,並蒐索了這樣的文檔......)  偉大,徹底的崩潰 - 萬分感謝! - k..


這是作為數組函數寫的嗎?即,兩側的花括號並使用ctrl / enter輸入?如果是這樣,則中間部分將比較與其所在行對應的數組中的值。考慮到乘法,我會說你是正確的,它正在尋找所有3個值都是真的東西。

我懷疑,沒有看到你的電子表格(並且無可置疑地通過你的帖子的詳盡細節),有一個範圍具有這個功能,你碰巧向我們展示了其中一個。我懷疑之前的行和之後的行(以及許多其他行)具有相同的公式,行號相應地調整。如果是這樣的話,那麼我會期望這是一個數組函數,寫入一次並應用於一系列單元格,應用ctrl / enter使其在運行時遍歷整個單元格。

如果是這種情況,編輯公式並使用enter保存它(即使您沒有進行任何實際編輯或更改)也會改變行為,從而改變結果。

如果這些都沒有意義,請隨時向我發送電子表格,我會看一看。


2
2017-07-22 01:52



Bob Smiley,我的朋友沒有這麼說,但是當我自己構建一個樣本電子表格來檢查像這樣的公式的細微差別時,這聽起來像是因為我在渲染任何合理的東西時遇到了很多麻煩 =E1=$I$1:$I$5 與不知道按Ctrl-Shift-Enter有關。實際上,我自己無法訪問我朋友的電子表格。但我想我理解這一切,在Bandersnatch的回答中顯示了這個函數返回的數組的內容,還有一些關於數組之間的操作如何工作的文檔。 - k..