☰

Excel公式帮助多个条件下拉

tag-icon • tag-icon microsoft-excel worksheet-function vlookup
Excel公式帮助多个条件下拉

在此处输入图片描述

你好，

我希望在使用两个下拉菜单查找今天的日期（G1 和 G2）时，在 V 查找中获得一些帮助。

右边的表格是每天拨打的电话，包含员工拨打的电话数据，它是一个全年的表格。表格范围是 Q58：AE423。

公式需要使用表格左侧下拉列表中选择的信息来匹配日期并选择正确的首字母。

左侧的两个下拉菜单用于选择今天的日期，然后今天拨打的电话绿色框将查找数据。例如，如果是 1 月 1 日，NL 将显示为 9。

任何帮助都将不胜感激。谢谢，

答案1

如果您有 XLOOKUP，您可以执行以下操作：

在此处输入图片描述

=XLOOKUP($B$1&" "&$B$2,$I$2:$I$32&" "&$J$2:$J$32,$K$2:$N$32,"")

但是，为什么不直接将表格改为单个日期参数，并将日期作为表格的行标题呢？在我看来，这会使它变得简单得多。

在此处输入图片描述

在这里，我使用这个公式来创建日期列表（在单元格 F2 中）：

=SEQUENCE(31,1,"1/1/2020")

如果您的 Excel 版本没有此功能，您可以毫不费力地手动创建列表。

这是 XLOOKUP 公式：

=XLOOKUP($A$2,F2#,$G$2:$J$32,"")

如果您没有 XLOOKUP，则可以在每列中用适当的 VLOOKUP 替换。例如，对于 CP：

=VLOOKUP($A$2,$F$2:$J$32,2,FALSE)

在单元格 A2 中，我使用数据>数据工具>数据验证，配置如下（即仅允许用户从页面列表中选择日期）：

在此处输入图片描述

单元格 A2 和 F 列中的日期列表的自定义单元格格式如下：

ddd, mmm dd, yyyy

XLOOKUP 的优点在于您只需在第一列输入一个公式即可。它可以从主表中匹配的行返回多列。

答案2

您可以使用 COUNTIFS 应用多个条件来计数表中的记录。

只需添加每列月份 H:H 日 I:I 名称 J:J 和引用的单元格，它必须匹配月份 G1 日 G2 名称 A5:A...

=COUNTIFS(H:H,$G$1,I:I,$G$2,J:J,A$5)

对于动态解决方案，命名范围和/或创建表，然后您可以使用如下内容：

=COUNTIFS(data[day],dd_day,data[month],dd_mo,data[name],FILTER(results[#Headers],COLUMN(results[#Headers])=COLUMN(),""))

答案3

FlexYourData 有一个很好的观点，将表格中的日期分成两列不是一个好主意。其中一个原因是，为了使 VLOOKUP 正常工作，它仅根据表格的第一列而不是前两列进行索引。

目前数据显示 VLOOKUP 不起作用，但有以下 3 个潜在选项：

1）辅助列

在参考表中日期列的左侧或右侧放置一个辅助列。在辅助列中连接月份和日期。出于演示目的，可以隐藏此列。执行 VLOOKUP 时，将您的日期连接到参考日期，并且参考查找表从辅助列开始。

Helper column to the right of dates
=VLOOKUP(MONTH&DAY,TABLE,MATCH(INTIALS,INITIALS_RANGE,0)+1,0)
or
Helper column to the left of dates
=VLOOKUP(MONTH&DAY,TABLE,MATCH(INTIALS,INITIALS_RANGE,0)+3,0)

波克

2）替换日期

在您的参考表中，您可以按照 FlexYourData 的建议将日期替换为单个日期列，也可以将其保留为两个日期列，每个日期列包含一个 Excel 日期。我将更详细地描述后者。这里的问题是，您将在参考表中丢失当天的 ST、ND、RD、TH。如果这很重要，那么这种方法将不起作用

在您的列中的 MONTH 单元格和 DAY 单元格中输入 20/01/01（与一年第一天的日期格式相匹配的数字），或者在其中一个单元格中输入它，然后使用公式使另一个单元格等于第一个。

然后自定义单元格格式。使用 MMM 格式表示月份，使用 D 格式表示日期。现在，由于每个单元格中实际上都包含完整日期，因此您只需要在定义查找表时引用日期列。

下一个障碍是将查询日期转换为日期。这可以通过输入完整日期而不是单独的值来实现，也可以将单独的值组合起来以生成日期。要用单独的值生成日期，可以使用 DATE 函数，如下所示：

=DATE(YEAR,MONTH,DAY)

因此，由于您没有年份，您可以手动将其设置为与您设置参考表时使用的年份相匹配，或者您可以将其添加为查找的一部分的附加单元格，或者您可以从表格中的第一个日期单元格中提取它。对于后一种情况，如果您的第一个表格日期在单元格 L4 中，您可以执行以下操作：

=YEAR(L4)

要获取月份（假设在 D4），您可以选择以下公式将文本转换为数字：

=MATCH($D$4,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0)

要将日期作为数字获取，首选方法是将其作为数字，后面没有“st”、“nd”、“rd”或“th”。但是，如果您必须去掉数字，那么您可以使用以下公式，假设您的参考日期在 D5 中。

=LEFT($D$5,len($D$5)-2)

现在您有了所有三个值，将它们插入到日期公式中，您将得到如下所示的结果：

=DATE(YEAR($L$4),MATCH($D$4,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),LEFT($D$5,len($D$5)-2))
or
Hard Coded Year
=DATE(2020,MATCH($D$4,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),LEFT($D$5,len($D$5)-2))

因此假设您的参考表是 L3:R34 您的 vlookup 将变成：

=VLOOKUP(DATE(2020,MATCH($D$4,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0),LEFT($D$5,len($D$5)-2)),$N$3:$R$34,MATCH(INTIALS,INITIALS_RANGE,0)+1),0)

请注意，由于您忽略了月份列，因此查找范围实际上是 N4:R34。

2. 磷酸二酯酶

3）使用替代配方

其他人提到使用 COUNTIFS。这是一个选项，对于数字来说效果很好。如果出于某种原因您需要返回文本值，那就不太好了。INDEX 和 MATCH 的组合会适合您。

MATCH 将被使用两次。一次用于确定适当的行，一次用于确定适当的列（上面的选项中已省略）。MATCH 将返回您在范围/列表中查找的内容的位置。告诉它您要搜索的内容、要查找的范围/列表是什么以及您要进行哪种类型的匹配。

因此，要在假设的参考表 L3:R34 中找到您的姓名首字母，其中第 3 行是姓名首字母，而您要查找的姓​​名首字母在 A10 中，那么您的匹配公式将是：

=MATCH(A10,$N$3:$R$3,0)

请注意 3 件事。第一，我锁定了范围引用，以避免在复制公式时单元格发生变化。第二，我从首字母开始范围，而不是从表格的边缘开始。第三，结果是范围的位置，而不是电子表格的列，也不是引用表的列。

对于 VLOOKUP，您需要对齐结果以提供表格的列。您需要在首字母前手动添加额外列的数量。在您的例子中，它是 2，因此公式变为：

=MATCH(A10,$N$3:$R$3,0)+2

现在要匹配行，这有点棘手，并使用数组公式。连接您要查找的月份 (D4) 和日期 (D5)，对于查找范围，连接两个范围。您的公式变为

=MATCH($D$4&$D$5,$L$4:$L$34&$M$4:$M$34,0)

现在上面是一个数组公式，因此您需要使用CONTROL++ SHIFT，ENTER而不仅仅是ENTER在确认公式时使用。当公式栏中公式周围出现{}时，您就会知道您做对了。请注意，它们不能手动添加。

最后一部分是使用索引来定义要查找的表。与 VLOOKUP 不同，您只需要包含参考数据，而不包含标题行和列。这也意味着 MATCH 公式的结果将与数据对齐而无需调整。因此，假设参考表位于 L3:R34 中，您的索引函数将如下所示：

=INDEX($N$4:$R$34, ROW TO LOOK IN, COLUMN TO LOOK IN)

当你替换其中的匹配公式时，它变成：

=INDEX($N$4:$R$34,MATCH($D$4&$D$5,$L$4:$L$34&$M$4:$M$34,0),MATCH(A10,$N$3:$R$3,0))

请记住，这是一个数组公式，因此需要特殊的 CSE 公式确认，而不仅仅是 Enter。

磷酸二酯酶3

相关内容