我正在为一些员工安排交通,他们的路线详情在蓝色区域给出。登录表示前往办公室,退出表示前往家。相同的路线 ID 表示这些员工乘坐同一辆出租车。员工数量表示每次出行有多少名员工。
我想制作一份如绿色区域所示的汇总报告。我想知道每位员工与多少位员工一起出行 - 即黄色突出显示的区域。单元格显示,登录 05:00 班次的员工 d 要么独自出行,要么只与另一名员工一起出行。我想要整个 Clubbing 列的这些值。
为了实现这一点,我在突出显示的单元格中使用了公式栏中显示的公式。按 F9 键后,我得到的结果与黄色突出显示的单元格中的结果类似。
但是我的公式看起来有点笨拙。另外,为了得到易于理解的结果,我必须转到 Clubbing 列中的每个单元格并按下 F9,这很不方便(这个表很小,我有数十万个条目)。
有没有更有效更清晰的方法来得到这个结果?请分享。我也愿意接受 VBA 解决方案。
答案1
一些数组(CSE)公式,结合 INDEX 和 MATCH 并用 IFERROR 包裹,解决了该问题:
:警告:
- 我使用了 2001 年 2 月 1 日的数据,因为员工 E 只属于该部分。
- 为了正确的可视化,我在 2001 年 2 月 1 日的数据上应用了红色。
怎么运行的:
选择 A12:E29 并按升序对员工列上的数据进行排序。
在单元格 F13 中插入日期 02/01/2001。
单元格 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)),"")}
在单元格 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)),"")}
单元格 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)),"")}
笔记:
- 使用以下方法完成上面显示的数组(CSE)公式Ctrl+Shift+Enter& 向下填充。
- 根据需要调整公式中的单元格引用。