☰

在 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 中创建状态下拉列表。
  • 点击“数据 TAB”，然后点击“数据验证”。
  • 对于列表源，选择 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"))

给出如下输出：

1月2日、1月4日

如果搜索“内华达州”。

等等。

基本上，它使用第一个XLOOKUP()来查找适用于该州的 YES 和 NO 数据行。包装IF()为 NO 提供 0，为 YES 提供标题行中的日期。包装由一个IF()使用前一个结果除以自身（产生 ERROR 和 1）来检查结果是否 >0，即 1。这些 ERROR 将继续向前，但第二个IF()再次用日期替换 1。然后包装IFERROR()它以将结果减少到只有日期而不是 ERROR 的项目。此时它们是 Excel 整数日期值（例如 43563），并且似乎希望它们以标题行中显示的形式出现，因此将TEXT()它们格式化。事实上，它们在这样做时会失去“数字性”，但这不是问题，因为如果列出它们，它们在任何情况下都会这样做（好吧，除非只返回一个日期……如果有人想“技术化”……）。最后，TEXTJOIN()给出一个“逗号空格”分隔符以提高可读性（因此，如果在进一步的工作中需要，它们有一个可以用来提取一个或全部的分隔符）。

可能还有其他方法，XLOOKUP()长期存在的“双 INDEX/MATCH”技术的版本可能就是其中一种，尽管从 获得多个响应XLOOKUP()给了我多个响应（如预期的那样是 5 个），但它们是三个 01-Jan 和两个 02-Jan，而不是我想要的 02-Jan 和 04-Jan。不过现在是凌晨 3 点，所以也许我错过了什么。FILTER()行动太快了，让我失去了获取内华达州两个日期的位置信息（立即只有两个结果，但现在是 1-2，而不是 2-4，所以没有好的方法将日期从标题中取出）。可能是其他想法。不过，还是凌晨 3 点……

但这可能并不重要，因为每个都是一堆元素，就像上面的一样，所以将其简化为简单的东西可能无法实现。

相关内容