我在 Toastmasters 俱乐部，俱乐部会议由担任不同角色的成员组成。每次会议都有不同的人担任不同的职位。我们目前轮换这些职位，并使用 Excel 电子表格列出他们，该电子表格列出了接下来 4 周的会议内容。
我问你们是否能想出一种方法来自动管理这个列表。此时，我们在日程表中有一列成员，然后是每周一列。然后我们输入每周人员的角色。这个过程非常手动。
基本上，我们有一组动态的成员数量和一组静态的角色（总共 9 个）。只要不让一个人承担角色的负担，轮换就没有必要。
两周议程示例
11-Jan 18-Jan
Person 1 Toastmaster Speaker #1
Person 2 Evaluator # 2 Evaluator # 1
Person 3 Grammarian Table Topics
Person 4 Speaker #1 Timer
Person 5 Evaluator # 1
Person 6 Table Topics Ah Counter
Person 7 Timer Evaluator # 2
Person 8 Speaker #2
Person 9 ah Counter Grammarian
Person 19 Speaker #2
答案1
A 列是数字，代表人员
B 列是字母，代表角色
C 列是第一组公式。将 C1:C8（或 CX，无论有多少）拖到右侧的列上。
1 a =B8 =C8 =D8 =E8 =F8 =G8 =H8 =I8
2 b =B1 =C1 =D1 =E1 =F1 =G1 =H1 =I1
3 c =B2 =C2 =D2 =E2 =F2 =G2 =H2 =I2
4 d =B3 =C3 =D3 =E3 =F3 =G3 =H3 =I3
5 e =B4 =C4 =D4 =E4 =F4 =G4 =H4 =I4
6 f =B5 =C5 =D5 =E5 =F5 =G5 =H5 =I5
7 g =B6 =C6 =D6 =E6 =F6 =G6 =H6 =I6
8 h =B7 =C7 =D7 =E7 =F7 =G7 =H7 =I7
具体来说，对你来说，它看起来是这样的
A B C D E F G H
Person 1 Role 1 =B19 =C19 =D19 =E19 =F19 =G19
Person 2 Role 2 =B1 =C1 =D1 =E1 =F1 =G1
Person 3 Role 3 =B2 =C2 =D2 =E2 =F2 =G2
Person 4 Role 4 =B3 =C3 =D3 =E3 =F3 =G3
Person 5 Role 5 =B4 =C4 =D4 =E4 =F4 =G4
Person 6 Role 6 =B5 =C5 =D5 =E5 =F5 =G5
Person 7 Role 7 =B6 =C6 =D6 =E6 =F6 =G6
Person 8 Role 8 =B7 =C7 =D7 =E7 =F7 =G7
Person 9 Role 9 =B8 =C8 =D8 =E8 =F8 =G8
Person 10 Role 10 =B9 =C9 =D9 =E9 =F9 =G9
Person 11 Role 11 =B10 =C10 =D10 =E10 =F10 =G10
Person 12 Role 12 =B11 =C11 =D11 =E11 =F11 =G11
Person 13 Role 13 =B12 =C12 =D12 =E12 =F12 =G12
Person 14 Role 14 =B13 =C13 =D13 =E13 =F13 =G13
Person 15 Role 15 =B14 =C14 =D14 =E14 =F14 =G14
Person 16 Role 16 =B15 =C15 =D15 =E15 =F15 =G15
Person 17 Role 17 =B16 =C16 =D16 =E16 =F16 =G16
Person 18 Role 18 =B17 =C17 =D17 =E17 =F17 =G17
Person 19 Role 19 =B18 =C18 =D18 =E18 =F18 =G18
这将变成：
Person 1 Role 1 Role 19 Role 18 Role 17 Role 16 Role 15 Role 14
Person 2 Role 2 Role 1 Role 19 Role 18 Role 17 Role 16 Role 15
Person 3 Role 3 Role 2 Role 1 Role 19 Role 18 Role 17 Role 16
Person 4 Role 4 Role 3 Role 2 Role 1 Role 19 Role 18 Role 17
Person 5 Role 5 Role 4 Role 3 Role 2 Role 1 Role 19 Role 18
Person 6 Role 6 Role 5 Role 4 Role 3 Role 2 Role 1 Role 19
Person 7 Role 7 Role 6 Role 5 Role 4 Role 3 Role 2 Role 1
Person 8 Role 8 Role 7 Role 6 Role 5 Role 4 Role 3 Role 2
Person 9 Role 9 Role 8 Role 7 Role 6 Role 5 Role 4 Role 3
Person 10 Role 10 Role 9 Role 8 Role 7 Role 6 Role 5 Role 4
Person 11 Role 11 Role 10 Role 9 Role 8 Role 7 Role 6 Role 5
Person 12 Role 12 Role 11 Role 10 Role 9 Role 8 Role 7 Role 6
Person 13 Role 13 Role 12 Role 11 Role 10 Role 9 Role 8 Role 7
Person 14 Role 14 Role 13 Role 12 Role 11 Role 10 Role 9 Role 8
Person 15 Role 15 Role 14 Role 13 Role 12 Role 11 Role 10 Role 9
Person 16 Role 16 Role 15 Role 14 Role 13 Role 12 Role 11 Role 10
Person 17 Role 17 Role 16 Role 15 Role 14 Role 13 Role 12 Role 11
Person 18 Role 18 Role 17 Role 16 Role 15 Role 14 Role 13 Role 12
Person 19 Role 19 Role 18 Role 17 Role 16 Role 15 Role 14 Role 13
对于每个人来说，这是下降的。
答案2
我想到了一个办法，稍加修改后也许能对你有用。
我的电子表格中有两张表，一张名为“时间表”，另一张名为“活跃”。每当有人履行该周的职责时，我都会在该人的姓名和日期旁边放一个“x”，如果他们在本周履行了职责，我就会输入其他人的姓名。例如，以下是过去履行该周职责的人员列表：
Carson 3/30/17 x
Devlin 4/6/17 x
Chris 4/13/17 x
Jake 4/20/17 x
Andy 4/27/17 x
Clifton 5/4/17 x
Jake 5/11/17 x
Hector 5/18/17 x
Carson 5/25/17 x
Chris 6/1/17 x
Andy 6/8/17 x
然后我在“计划”工作表上有一个链接，该链接引用该数据，查找日期和“x”。这是一张图片，因为我不知道如何添加 Excel 样式的文本。
希望有所帮助。