Excel:具有多个(日期)条件和 If 语句的聚合公式

Question

我有很多客户在 4 个不同的场合(我称之为主题)有约会:第一次访问、第一次检查、第二次检查和最后一次访问。这些客户还与合作伙伴相关联:Kate、Dave、Bart、Will、John 和 Tom。

从这个庞大的客户列表中，我希望能够在两个日期(例如 1 月 1 日和 3 月 1 日)之间提取基于“主题”和“合作伙伴”的客户名称。所以我制作了以下两个下拉列表:

主题:无选择、第一次访问、第一次检查、第二次检查和最终访问。

搭档:无选择，凯特、戴夫、巴特、威尔、约翰和汤姆。

因此，例如，如果我选择查看在 2019 年 1 月 1 日和 2019 年 3 月 1 日之间有约会的所有客户，“主题 = 第一次访问”和“合作伙伴 = 无选择”。我希望能够查看上述两个日期之间所有合作伙伴的第一次访问的所有约会。如果我选择上述日期，“主题 = 未选择”和“合作伙伴 = 未选择”，我希望能够查看所有主题和所有合作伙伴的两个选定日期之间的所有约会。

我的 Excel 工作表中有以下 2 个问题。

第一的
我正在使用 countifs 公式(Excel 表中的 G6 提取“)来计算约会的数量。但我遇到的问题是我似乎无法正确编写公式来计算合作伙伴和“没有选择”部分。我现在的 countifs 公式只计算两个日期之间的主题，所以每当我为主题选择“无选择”时，它不会给我所有的计数(就像我想要的那样)。

第二
我的聚合公式(Excel 表“Extract”中的 G10)一团糟，我对正确编写公式的知识很少。我将不胜感激任何帮助。我认为聚合函数很漂亮，如果有人对我可以在哪里了解更多信息有任何提示，我也将不胜感激。

链接到我的 Excel 表:https://www.dropbox.com/s/hqntzkj2xq0i250/Aggregate%20function%20struggle.xlsx?dl=0

我知道现在是假期，也许不是在网上提问的最佳时间，但这些公式现在就像我脑海中的一个碎片。

Answer 1

我希望这会对您的 有所帮助第一个 问题。

在提取选项卡的单元格 G6 中，粘贴以下公式:

=COUNTIFS(INDIRECT("source[[#All],["&$L$5&"]]"),">="&$G$4,INDIRECT("source[[#All],["&$L $5&"]]"),"<="&$N$4,source[[#All],[PARTNER]],IF($J$5="无选择","<>""",$J$5 ))

至于你的第二问题，您需要在 EXTRACTED 选项卡中取消合并单元格 G10:L64(因为数组公式在合并的单元格中无效)。在单元格 G10 中，使用以下数组公式(使用 Ctrl+Shift+Enter 确认):

=IFERROR(INDEX(source[[#All],[NAME]],(SMALL(IF((--(INDIRECT("source[[#All],["&$L$5&"]]")>=提取!$G$4))*(--(间接("来源[[#All],["&$L$5&"]]")<=提取!$N$4))*IF($J$5= "无选择",1,(--(source[[#All],[PARTNER]]=EXTRACTED!$J$5))),ROW(source[[#All],[NAME]]),"") ,F10))),"")

您现在可以将其向下拖动以查看所有结果。我知道您想使用 AGGREGATE 公式，但我遵循了我的解决方案(对不起!)。输入公式后，您现在可以选择单元格 G10:L10 并单击“合并和居中”。

希望能帮助到你!