☰

Excel INDEX MATCH 在我選擇的兩個日期之間查找

tag-icon • tag-icon microsoft-excel worksheet-function array
Excel INDEX MATCH 在我選擇的兩個日期之間查找

螢幕截圖
我正在使用 Excel 記錄項目清單（D 列中的項目名稱），然後記錄該項目在 G 列中產生所需的時間。

我正在執行索引匹配來搜尋本週（或我選擇的兩個日期之間）表現最差的產品

=MAXIFS($G:$G,$E:$E,">="&O67,$E:$E,"<"&O68)

這將返回單元格 O67 和 O68 中兩個日期之間最長生產的時間長度。然後我想做的是返回最長的項目名稱（D 列）。

我試過了

=INDEX(D:F,MATCH(O69,G:G,0),1)

但這只是查看記錄的時間並傳回這次出現在清單中的第一個事件。這是一個很長的列表，所以很多時候會重複，所以這不是可行的方法，我只是希望它引用 O67 和 O68 中的兩個日期，並在每個日期之間查找索引匹配。

我認為要走的路可能是一個數組，但我以前沒有做過這些，所以如果這是要走的路，那麼你能和我一起慢慢地走，這樣我就能理解！

先致謝！

答案1

你可以試試這個大批公式，輸入時不使用Enter，而是使用Ctrl++ Shift：Enter

=INDEX($D$2:$D$1000,MATCH(1,($G$2:$G$1000= (ABSOLUTE REFERENCE TO MAXIFS CELL)
                           *($E$2:$E$1000>=$O$67)
                           *($E$2:$E$1000<$O$68),0))

在陣列公式中，使用明智選擇的範圍而不是完整列的引用在效能方面會更好。因為如果您選擇完整列，則陣列公式實際上將對每一行起作用，即使不應該使用它，也會使工作簿變慢。

我沒有Excel 2019來測試它，並且在Excel 365中只能查看數組公式，而不能輸入。

答案2

您的問題可以使用幾個輔助單元來解決，即開始和結束日期及其計數。

在此輸入影像描述

  • 輸入此公式以取得儲存格中開始和結束日期的計數E193。

    =SUMPRODUCT(($C$179:$C$190>=C193)*($C$179:$C$190<=D193))

  • 在 Cell 中輸入此數組 (CSE) 公式B195，最後輸入Ctrl+Shift+Enter並向下填充。

{=IF(ROWS(B$195:B195)>$E$193,"",INDEX(B$179:B$190,SMALL(IF(($C$179:$C$190>=$C$193)*($C$179:$C$190<=$D$193),ROW(B$179:B$190)-ROW($B$179)+1),ROWS(B$195:B195))))}

注意

  • 開始日期和結束日期的計數避免使用 IFERROR函數並證明產品清單作為答案的合理性，必須等於計數值。

根據需要調整公式中的儲存格引用。

相關內容