☰

使用 Excel 安排 10 个互相不喜欢的人之间的会议

tag-icon • tag-icon microsoft-excel worksheet-function
使用 Excel 安排 10 个互相不喜欢的人之间的会议

只是开玩笑，我们确实如此。

我们将参与者称为 Alpha、Beta、Gamma、Delta、Epsilon、Zeta、Eta、Theta、Iota 和 Kappa。

我该如何安排这些会议才能使以下所有情况都得到满足？

  • 每个参与者与其他参与者会面的次数尽量少。也就是说，Alpha 不会与 Beta 会面很多次，也不会与 Gamma 会面一次，而是尽可能与每个人会面的次数相等。

  • 每位参与者每周举行一次会议，不多也不少。

  • 其中有两次三人会议，一次四人会议。

  • 类似的会议尽可能分散。也就是说，Gamma 和 Kappa 不会连续三周在同一次会议上开会。

我已经在键盘上敲了一个星期试图满足前三个标准，但甚至还没有开始考虑第四个标准。

这里有该文件的链接：https://paste.c-net.org/CreditedHopes

我的方法是：会议 1 字段搜索可能的三方会议列表，查找第一个与三个最不频繁配对相匹配的会议。如果 N/A，则搜索两个，然后搜索一个：

(ISNUMBER(SEARCH(MID([@[Least frequent pairs for meeting 1]],1+(FIND(";",[@[Least frequent pairs for meeting 1]],1)),(FIND(";",[@[Least frequent pairs for meeting 1]],1+(FIND(";",[@[Least frequent pairs for meeting 1]],1))))-(FIND(";",[@[Least frequent pairs for meeting 1]],1))-1),Table1[All pairs in trio])))+
ISNUMBER(SEARCH(MID([@[Least frequent pairs for meeting 1]],1+(FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),2))),
FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),2))-1),Table1[All pairs in trio]))
)>=3,0))),
IF([@Fortnight]=1,INDEX(Table1[Trios],1),INDEX(Table1[Trios],MATCH(TRUE,(ISNUMBER(SEARCH(LEFT([@[Least frequent pairs for meeting 1]],(FIND(";",[@[Least frequent pairs for meeting 1]],1)-1)),Table1[All pairs in trio]))+
(ISNUMBER(SEARCH(MID([@[Least frequent pairs for meeting 1]],1+(FIND(";",[@[Least frequent pairs for meeting 1]],1)),(FIND(";",[@[Least frequent pairs for meeting 1]],1+(FIND(";",[@[Least frequent pairs for meeting 1]],1))))-(FIND(";",[@[Least frequent pairs for meeting 1]],1))-1),Table1[All pairs in trio])))+
ISNUMBER(SEARCH(MID([@[Least frequent pairs for meeting 1]],1+(FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),2))),
FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),2))-1),Table1[All pairs in trio]))
)>=2,0)))),
IF([@Fortnight]=1,INDEX(Table1[Trios],1),INDEX(Table1[Trios],MATCH(TRUE,(ISNUMBER(SEARCH(LEFT([@[Least frequent pairs for meeting 1]],(FIND(";",[@[Least frequent pairs for meeting 1]],1)-1)),Table1[All pairs in trio]))+
(ISNUMBER(SEARCH(MID([@[Least frequent pairs for meeting 1]],1+(FIND(";",[@[Least frequent pairs for meeting 1]],1)),(FIND(";",[@[Least frequent pairs for meeting 1]],1+(FIND(";",[@[Least frequent pairs for meeting 1]],1))))-(FIND(";",[@[Least frequent pairs for meeting 1]],1))-1),Table1[All pairs in trio])))+
ISNUMBER(SEARCH(MID([@[Least frequent pairs for meeting 1]],1+(FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),2))),
FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),2))-1),Table1[All pairs in trio]))
)>=1,0))))

另外两个会议公式类似，但包含一个条件，即不包括该周同一场会议的任何人。第三个会议搜索四个人组合的单独列表。

“最不常见配对公式”是：

SUBSTITUTE(SUBSTITUTE(TEXTJOIN(";",TRUE,IF(COUNTIF(INDEX([Meeting 1 pair 1],1):INDEX([Meeting 3 pair 6],MATCH([@Fortnight]-1,[Fortnight],0)),Table3[All pairs])=1,Table3[All pairs])),"FALSE;",""),"FALSE","")

其中“会议#对#”单元格包含每对会议参与者。

答案1

下面我展示了每周有一次四人会议（比如，i=1-4）和两次三人会议（比如，i=5-7 和 i=8-10），你已经满足了规则 1、2、3。然后你轮换人员来满足规则 4。

我整理了一个简单的工作表来显示按照上述时间表举行的会议。

在此处输入图片描述

唯一需要考虑的是：

  • E3 中的公式是=VLOOKUP(MOD(E$2+$D3-1,10),$A$3:$B$12,2)，向下并向右复制。
  • 您可以根据需要延长周数并复制公式。
  • 为了公式方便，我使用任意编号 0-9 而不是 1-10。
  • E3-N3 中的数字 0-9 与 A3-A12 不对应。它们只是一种计算参与者的方法。如果这让您感到困惑，您可以隐藏它们，或者改用字母 AJ，并将CODE(q10)-CODE("A")单元格 q10 中的字母转换为数字，从 A->0 开始。

总结——“最优”时间表

我不确定您所要求的核心（#1）是否是满足您的 4 条规则的算法。然后是 Excel 中的“实现”（#2），见上文。对于 #1，这个问题可能更适合另一个 SE 网站。我将在这里回答这个问题，使用数字 i 代替人员名称。您的要求在这里以编号形式列出。

  1. 每个参与者与其他参与者会面的次数尽量少。也就是说，Alpha 不会与 Beta 会面很多次，也不会与 Gamma 会面一次，而是尽可能与每个人会面的次数相等。
  2. 每位参与者每周举行一次会议，不多也不少。
  3. 其中有两次三人会议，一次四人会议。
  4. 类似的会议尽可能分散。也就是说，Gamma 和 Kappa 不会连续三周在同一次会议上开会。

我将推迟每个要求的数学公式（注意：我开始写它，但我认为它在这里不会增加什么），并从概念上写出解决方案。我只会使用以下内容：

  • Nij 表示 i 与 j 会面的次数（无论是 2 人、3 人还是 4 人会面）。
  • 对于 i 来说，每周的会议总数为 Mi=sum(j,Nij)。
  • 每周总开会人数为Q=sum(i,Mi)。

每周举行一次四人会议（例如，i=1-4），以及两次三人会议（例如，i=5-7 和 i=8-10），这样您就已经满足规则 1、2、3。我将展示如何操作。

̲R̲u̲l̲e̲ ̲2̲：Q 的绝对最小值为 0（简单，没有会议，Mi=0）。如果您希望每个人每周只开一次会，则 Mi=1 且 Q=10。因此每个人只有一个 Mij=1，而其他所有人 Mij=0。

̲R̲u̲l̲e̲ ̲3̲：满足此规则和规则 1 的唯一方法是举行一次四人会议（例如，i=1-4），以及两次三人会议（例如，i=5-7 和 i=8-10）。因此，其他规则要么与规则 2 和规则 3 完全兼容，要么使解决方案无法实现。

̲R̲u̲l̲e̲ ̲1̲：这似乎是一条表述不当的规则。我能想到的最好的办法是，取 i 与任何其他人会面的最大次数 pi、i 与任何其他人会面的最小次数 qi，以及它们之间的差异 ri=pi-qi，最小化所有 ri。由于我们没有办法对差异 ri 进行优先排序，因此无论在哪种优化中，我们都会给予所有 ri 相同的权重。无论如何，对于所有人，我们已经拥有的唯一解决方案是 pi=1、qi=0、ri=1。

至于规则 4，不清楚你如何称呼“同一场会议”。如果只有当两场会议的参与者完全相同时，它们才是相同的，那么根据上述建议，在重复会议之前，你可以选择多种组合。例如，你可以每周轮换一个职位，这样你每 10 周只需要重复一次会议。

这是一个简单的时间表，可以满足您编写的所有要求（并且不需要对 Excel 进行任何修改）。我不确定这是否是您真正想要的，但如果不是，那么我猜您没有在规范中正确反映它。如果是这样，请发布另一个问题。

答案2

我已经创建了一个月的时间表，因此您必须创建另外几个月的时间表，我所做的是，使用下面显示的公式检查参与者的出现情况，如果计数不超过一。

在此处输入图片描述

注意：

  • 单元格 K6 中的数组 (CSE)公式，统计参与者的出现次数。

    {=SUM(((LEN(C$6:E$6)-LEN(SUBSTITUTE(C$6:E$6,I6,""))) / LEN(I6))-((LEN(C$6:E$6)-LEN(SUBSTITUTE(C$6:E$6,"_I6",""))) / LEN("_I6")))}

  • 完成配方Ctrl+Shift+Enter& 向下填充。

  • 您需要根据其他周的需要更改公式中的单元格引用。

  • 单元格 E3 包含该月的开始日期。

  • 单元格 B7 从 E3 读取值，其他的则为 + 7。

相关内容