使用数组函数进行多条件搜索并返回多个值

使用数组函数进行多条件搜索并返回多个值

蓝色区域表示我的原始数据。绿色区域表示我的操作,黄色单元格表示所需结果。

我正在为一些员工安排交通,他们的路线详情在蓝色区域给出。登录表示前往办公室,退出表示前往家。相同的路线 ID 表示这些员工乘坐同一辆出租车。员工数量表示每次出行有多少名员工。

我想制作一份如绿色区域所示的汇总报告。我想知道每位员工与多少位员工一起出行 - 即黄色突出显示的区域。单元格显示,登录 05:00 班次的员工 d 要么独自出行,要么只与另一名员工一起出行。我想要整个 Clubbing 列的这些值。

为了实现这一点,我在突出显示的单元格中使用了公式栏中显示的公式。按 F9 键后,我得到的结果与黄色突出显示的单元格中的结果类似。

但是我的公式看起来有点笨拙。另外,为了得到易于理解的结果,我必须转到 Clubbing 列中的每个单元格并按下 F9,这很不方便(这个表很小,我有数十万个条目)。

有没有更有效更清晰的方法来得到这个结果?请分享。我也愿意接受 VBA 解决方案。

为了澄清起见,我需要的最终输出是这样的 - R 列中给出的值。 在此处输入图片描述

答案1

一些数组(CSE)公式,结合 INDEX 和 MATCH 并用 IFERROR 包裹,解决了该问题:

:警告:

  1. 我使用了 2001 年 2 月 1 日的数据,因为员工 E 只属于该部分。
  2. 为了正确的可视化,我在 2001 年 2 月 1 日的数据上应用了红色。

在此处输入图片描述


怎么运行的:

  1. 选择 A12:E29 并按升序对员工列上的数据进行排序。

  2. 在单元格 F13 中插入​​日期 02/01/2001。

  3. 单元格 G13 中的数组公式:

    {=IFERROR(INDEX(B$13:B$29, SMALL(IF(COUNTIF($F$13, $A$13:$A$29), ROW($A$13:$D$29)-MIN(ROW($A$13:$D$29))+1), ROW(A1)), COLUMN(A1)),"")}
    
  4. 在单元格 H13 中输入此数组公式:

    {=IFERROR(INDEX($C$13:$C$29, SMALL(IF(COUNTIF($F$13, $A$13:$A$29), ROW($A$13:$D$29)-MIN(ROW($A$13:$D$29))+1), ROW(A1)), COLUMN(A1)),"")}
    
  5. 单元格 I13 中的数组公式:

    {=IFERROR(INDEX($E$13:$E$29, SMALL(IF(COUNTIF($F$13, A$13:A$29), ROW($A$13:$D$29)-MIN(ROW($A$13:$D$29))+1), ROW(A1)), COLUMN(A1)),"")}
    

笔记:

  1. 使用以下方法完成上面显示的数组(CSE)公式Ctrl+Shift+Enter& 向下填充。
  2. 根据需要调整公式中的单元格引用。

相关内容