☰

Excel-根据命名范围向列添加顺序名称，但如果相邻列中存在重复名称，则跳过名称

tag-icon • tag-icon microsoft-excel worksheet-function office365
Excel-根据命名范围向列添加顺序名称，但如果相邻列中存在重复名称，则跳过名称

我正在为我们的工程轮岗制定一个简单的时间表。一些背景信息，我们团队目前有 6 名工程师，必须每周早上 5 点轮岗。此外还有第二班轮岗，每位工程师必须轮岗 2 周。

我有一个范围内的工程师名单，可以使用以下函数自动调整其大小。

=OFFSET(LookupLists!$B$2,0,0,COUNTA(LookupLists!$B:$B)-1,1)

这样，如果我需要在轮换中添加或删除人员，只需从该列表中添加或删除姓名即可。

我在单元格中定义了一个变量，标记为“第二班次的天数”（当前为 10 天或 2 个工作周），这样我就可以轻松修改每个工程师在第二班次上花费的时间。

为了列举第二个轮班计划，我使用了以下函数。

=INDEX(Engineers,MOD(ROUNDDOWN((ROW(A1)-1)/LookupLists!$C$2,0),LookupLists!$D$2)+1)

这将枚举一份为期 X 天的工程师列表，其中 x = 第二班的天数，然后转到“工程师”列表中的下一位工程师。

棘手的部分是凌晨 5 点的时间表。如果工程师上第二班，他们就不属于凌晨 5 点的轮班。我正在寻找某种功能，以便通过“工程师”定义的列表枚举凌晨 5 点的时间表，但如果工程师上第二班，他们的名字将被跳过，列表将移动到下一个值，直到所有 X 名工程师都被考虑在内，列表重新开始。最好这个列表基于一个函数自动生成，当我更新工程师列表或“第二班天数”时，该函数将更新。

日程表示例

日程表截图

所用变量的示例

所用变量的屏幕截图

期望输出的示例

期望输出

期望输出

答案1

我能够使用以下公式找到更优雅的答案：

=INDEX(Engineers,AGGREGATE(15,6,ROW(Engineers)-1/(Engineers<>Schedule!$D3),MOD((ROWS($1:1)-1),ROWS(Engineers)-1)+1))

我很感谢你的帮助！

答案2

为了解决这个问题，你需要一些辅助列：

：警告：

为了正确可视化，我只使用了两个数据集，分别是杰克&约翰，您需要根据需要扩展它们。

在此处输入图片描述

怎么运行的：

  • 插入1单元格 M2。

  • 单元格 M3 中的公式：=M2+O$2。

  • 单元格 Q2 中的公式：

    =IFERROR(IF(ROW(A1)<=O$2*P$2,ROWS($A$1:A1),NA()),"")

  • 单元格 R2 中的公式：

    =INT(((ROW(A1)-1)/5))*5+0

  • 单元格 U2 中的公式：

    =VLOOKUP(Q2,M$2:N$7,2)

  • T2 中的最终数组（CSE）公式：

    {=INDEX($N$2:$N$7,SMALL(IF((INDEX($N$2:$N$7,,1)<>$U2),MATCH(ROW($N$2:$N$7),ROW($N$2:$N$7)),""),IF(ROW(A1)<=5,ROWS(T$2:$T2),ROWS(T$2:$T2)-R2)),COLUMNS($A$1:A1))}

注意：

  • 使用以下代码在单元格 T2 中完成数组（CSE）公式Ctrl+Shift+Enter。
  • 根据需要调整公式中的单元格引用。
  • 为了以后整洁，您可以隐藏辅助列。

相关内容