☰

在 Excel 中提取具有公共點的日期

tag-icon • tag-icon microsoft-excel
在 Excel 中提取具有公共點的日期

想像一下下雨時記錄的一堆數據處於什麼狀態，

State/Date 01Jan 02Jan 03Jan 04Jan 05Jan 
Alabama     YES   YES   NO    YES   NO
Alaska      YES   YES   YES   NO    YES
Florida     NO    NO    NO    YES   NO
Nevada      NO    YES   NO    YES   NO

如何寫一個公式，給出特定州下雨的日期清單？例如，我想知道內華達州什麼時候下雨。我能02Jan, 04Jan在牢房裡得到答案嗎？

答案1

下面的顯示方法解決了這個問題：

在此輸入影像描述

怎麼運作的：

  • 為了更快地獲得結果，請在儲存格 A28 中為狀態建立下拉式功能表。
  • 點選資料選項卡，然後點選資料驗證。
  • 對於清單來源，選擇 D28:D31。

在此輸入影像描述

  • 現在，對於“有雨”或“無雨”，在儲存格 B28 中建立下拉式選單，使用資料驗證，並為“清單來源”新增“是”和“否”。

在此輸入影像描述

  • 現在儲存格 A35 中的最終數組 (CSE) 公式：

    {=IFERROR(INDEX($E$27:$I$27,1,SMALL(IF(INDEX($E$28:$I$31,MATCH($A$28,$D$28:$D$31,0),)=$B$28,COLUMN($A$26:$E$26)),ROW(1:1))),"")}

注意

  • 完成公式Ctrl+Shift+Enter並填寫。

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

答案2

說實話，從資料中製作資料透視表可能是最好的方法。但如果你想要一個公式方法：

假設您的資料位於儲存格 A1:F5 中，包括標題。然後，如果您的查找值位於儲存格 G8 中，則如下：

=TEXTJOIN(", ",TRUE,TEXT(IFERROR(IF(IF(XLOOKUP(G8,A2:A5,B2:F5)="YES",B1:F1,"0")/IF(XLOOKUP(G8,A2:A5,B2:F5)="YES",B1:F1,"0")>0,IF(XLOOKUP(G8,A2:A5,B2:F5)="YES",B1:F1,"0"),""),""),"dd-mmm"))

給你一個看起來像這樣的輸出：

一月 2 日、一月 4 日

如果尋找“內華達州”。

等等。

基本上，它使用第一個XLOOKUP()來尋找適用於該狀態的 YES 和 NO 資料行。換行方式IF()為「否」提供 0，為「是」提供標題行中的日期。它被包裝IF()在一個檢查中，使用前面的結果除以自身（這會產生錯誤和 1），以查看結果是否 >0（即 1）。這些錯誤將繼續出現，但第二個錯誤IF()再次用日期替換 1。然後IFERROR()將其包裝起來，將結果減少為僅包含日期的項目，而不是錯誤的項目。此時它們是 Excel 整數日期值（例如 43563），而且它們似乎需要以標題行中顯示的形式出現，因此TEXT()對其進行格式化。事實上，他們這樣做時失去了「數字性」並不是一個問題，因為如果列出來，他們在任何情況下都會這樣做（好吧，除非只返回一個日期......如果有人想要「技術性」的話。最後，TEXTJOIN()給出一個「逗號空格」分隔符號以提高可讀性（因此它們有一個分隔符，可用於提取一個或全部，如果進一步工作需要的話）。

可能還有其他方法，XLOOKUP()長期存在的“雙索引/匹配”技術的版本可能是其中之一，儘管獲得多個回復XLOOKUP()給了我多個回复（如預期的那樣，有5 個），但它們是三個01-Jan 和兩個02-例如，一月的，不是我想要的一月 2 日和一月 4 日。現在是凌晨 3 點，所以也許我錯過了一些事情。FILTER()行動太快，讓我失去了獲取內華達州兩個日期的位置資訊（立即只有兩個結果，但現在是 1-2，而不是 2-4，所以沒有好方法從標題中獲取日期）。可能還有其他想法。不過，凌晨 3 點…

可能並不重要，因為每個元素都是一堆元素，如上面所示，因此將其簡化為看起來簡單的東西可能不會發生。

相關內容