☰

确定 Excel 中是否需要 VBA，或者数据透视图/表是否足够

tag-icon • tag-icon microsoft-excel vba worksheet-function pivot-table
确定 Excel 中是否需要 VBA，或者数据透视图/表是否足够

我希望对这个问题有一个更精确的描述性标题，但我对 Excel 电子表格或 VBA 不是很了解（我主要是一名 Java 开发人员，在 Microsoft 世界中玩得不多）。

我正在尝试使用 Excel 电子表格来完成某项工作。我需要确定是否可以使用数据透视表、数据透视图或其他内置功能来实现目标...或者我是否需要编写自定义 VBA 代码（或者可能完全使用其他平台）。

电子表格的目的是帮助为一个组织（Toastmasters 俱乐部）创建日程表。该组织有一个成员名册，每周举行会议，并在特定会议中为不同的成员分配不同的角色。

我的电子表格如下所示：

第一个标签

一列...代表会员名册的姓名列表。

第二个标签

每行代表一个过去的会议日期。每个角色都有一列，单元格中应填写当天担任该角色的人员。我使用数据验证将第一个选项卡的花名册作为下拉菜单放在每个单元格内。

第三个标签（也许是多个标签？）

这就是整个事情的要点。对于每个可能的会议角色，我希望看到哪些成员最“迟到”被分配到该角色。基本上，我想要一份所有俱乐部成员的列表，按他们上次担任该角色的时间长短排序。已经绝不担任该角色的人将被排在列表顶部。

第三个选项卡数据是否可以通过数据透视表等来完成？或者我误解了这些工具的目的和局限性？

答案1

如果我理解错了，请纠正我。您的数据如下：

第一个标签（工作表）：

Name
----
Alice
Bruce
Chuck
Derek
Emily

第二个标签：

Date        Role1    Role2    Role3
--------    -----    -----    -----
13-01-11    Alice    Bruce    Chuck (Each selected from dropdown)
14-01-11    Derek    Emily    Bruce
15-01-11    Alice    Chuck    Derek
16-01-11    Emily    Bruce    Alice
17-01-11    Chuck    Derek    Bruce
18-01-11    Chuck    Bruce    Alice
19-01-11    Alice    Chuck    Bruce

如果是这样，数据透视表将不适用，除非您将其转换为 Mike 编写的格式。

相反，（编辑：在同一个选项卡中分离每个角色列并按字母顺序排序后）即制作第二个选项卡如下（剪切粘贴排序）：

Date        Role1
--------    -----
13-01-11    Alice
15-01-11    Alice
19-01-11    Alice
17-01-11    Chuck
18-01-11    Chuck
14-01-11    Derek
16-01-11    Emily

Date        Role2
--------    -----
13-01-11    Bruce
16-01-11    Bruce
18-01-11    Bruce
15-01-11    Chuck
19-01-11    Chuck
17-01-11    Derek
14-01-11    Emily etc

然后，您可以为每个角色创建选项卡（或将它们放在同一个选项卡中），并使用 LOOKUP 函数查看成员上次担任该角色的时间。例如，对于角色 1，选项卡 3 应该是这样的：

Name     Last Done (Date)
----     -------- 
Alice    19-01-11 [=LOOKUP(A2,Sheet2!$B$2:$B$8,Sheet2!$A$2:$A$8)]
Bruce    N/A
Chuck    18-01-11
Derek    14-01-11
Emily    16-01-11

如果按从最新到最旧进行排序，您可以在顶部看到谁从未扮演过这个角色，以及谁在底部最后一次扮演过这个角色。

您还可以使用 COUNT 添加列来表示某人执行特定角色的次数，以及使用 TODAY 添加列来表示自上次执行以来已经过了多少天/周并获取差值。

在不同的选项卡上为其他角色（假设角色不多）复制粘贴相同的功能。

答案2

我个人会选择 VBA，但我一般会倾向于 VBA。如果您不是 VBA 爱好者，使用公式和数据透视表可能更容易维护。不过，您必须做出一些牺牲。因此，值得研究非 VBA 方式，看看这些牺牲是否适合您。以下是其中一种方法。

在名册表上，为所有角色添加列。在我的示例中，有两个角色：总裁和秘书，因此 A 列是姓名，B 列的头衔是总裁，C 列的头衔是秘书。在 B2 中，输入此公式

{=MAX((OFFSET(Meetings!$A$1,1,MATCH(B$1,Meetings!$B$1:$C$1,FALSE),20,1)=$A2)*(OFFSET(Meetings!$A$2,0,0,20,1)))}

此公式中有一些假设，我肯定会使其更加可靠，但这是一个很好的概念证明。您可以使用 Control+Shift+Enter 输入它，因为它是一个数组公式。它将返回它在 President 列中找到的该名称的最大日期。向下和向右填充该公式以适合您的数据。

现在，使用名册表上的数据，为每个“角色”列制作一个数据透视表。将姓名放在行区域中，将角色（例如总裁）放在数据区域中。它可能默认为总裁数量。右键单击该字段并选择字段设置。将汇总方式更改为最大值，并将数字格式设置为日期。

接下来右键单击其中一个名称并选择字段设置。单击高级按钮。将自动排序选项更改为“总统最大时升序”。

结果应为已排序的姓名列表。排在最上面的姓名是最久未担任总统的人。

没有什么比示例更好了。点击此处下载示例http://www.dailydoseofexcel.com/excel/Rotary.zip 它包含 1 个 Excel 2003 工作簿，显示我上面描述的内容。

答案3

是的，只要您在第二个选项卡中适当地设置数据，数据透视表就足够了：

会议成员角色 自几周前以来
2010 年 1 月 12 日 成员 1 角色 1 8 [ =(TODAY()-A2)/7 ]
2010 年 1 月 12 日 成员 2 角色 2 8
2010 年 1 月 12 日 成员 3 角色 3 8
2010 年 1 月 12 日 成员 4 角色 4 8
2010 年 1 月 12 日 成员 5 角色 5 8
2010 年 8 月 12 日 成员 1 角色 1 7
2010 年 8 月 12 日 成员 3 角色 2 7
2010 年 8 月 12 日 成员 7 角色 3 7
2010 年 8 月 12 日 成员 6 角色 4 7
2010 年 8 月 12 日 成员 4 角色 5 7
2010 年 12 月 15 日 成员 1 角色 1 6
2010 年 12 月 15 日 成员 3 角色 2 6
2010 年 12 月 15 日 成员 4 角色 3 6
2010 年 12 月 15 日 成员 2 角色 4 6
2010 年 12 月 15 日 成员 6 角色 5 6
2010 年 12 月 22 日 成员 1 角色 1 5
2010 年 12 月 22 日 成员 2 角色 2 5
2010 年 12 月 22 日 成员 5 角色 3 5
2010 年 12 月 22 日 成员 3 角色 4 5
2010 年 12 月 22 日 成员 4 角色 5 5
2010 年 12 月 29 日 成员 2 角色 1 4
2010 年 12 月 29 日 成员 3 角色 2 4
2010 年 12 月 29 日 成员 4 角色 3 4
2010 年 12 月 29 日 成员 5 角色 4 4
2010 年 12 月 29 日 成员 5 角色 5 4
...

然后您可以按如下方式创建数据透视表：

报告过滤器：无

列标签：无

行标签：角色、成员

值：分钟数周以来

然后：

  • 删除不必要的总计字段
  • 编辑成员字段设置并选择布局和打印/显示没有数据的项目。这将显示从未担任过任何角色的成员。
  • 将排序顺序从最短周数改为从最小到最大。

您的枢轴表应看起来类似于以下内容：

角色成员 最少已用周数
角色 1 成员 4        
            会员 7        
            成员 3        
            成员 5        
            成员 6        
            会员 2 4
            会员 1 5
角色 2 成员 4        
            会员 7        
            成员 6        
            成员 1        
            成员 5        
            会员 3 4
            会员 2 5
角色 3 成员 6        
            成员 1        
            成员 2        
            会员 4 4
            会员 5 5
            会员 7 7
            会员 3 8
角色 4 成员 7        
            成员 1        
            会员 5 4
            会员 3 5
            会员 2 6
            会员 6 7
            会员 4 8
角色 5 成员 7        
            成员 1        
            成员 3        
            成员 2        
            会员 5 4
            会员 4 5
            会员 6 6

在 Excel 2010 中测试。

答案4

这里的方法略有不同，分为三个部分：

  • 数据输入表——每次会议，都会有人输入角色扮演者和角色。表格按日期排序（降序）。
  • 交叉引用表——每个名字都与其担任每个职位的最近日期进行交叉引用
  • 下一個清單

维护成本很低。设置完成后，唯一的工作就是每天输入三个名字，并根据需要扩展日期范围。

数据输入表

这是表格 (A1:D10)。注意：这里没有公式。每周，根据下面的下一个列表输入三个名字。

Date    Role1   Role2   Role3  
21-Jan            
20-Jan            
19-Jan  Alice   Chuck   Bruce  
18-Jan  Chuck   Bruce   Derek  
17-Jan  Chuck   Derek   Alice  
16-Jan  Emily   Bruce   Chuck  
15-Jan  Alice   Chuck   Emily  
14-Jan  Derek   Emily   Alice  
13-Jan  Alice   Bruce   Emily

交叉引用表

下面的交叉引用表（A14:D19）将显示每个名字扮演每个角色的最近一天。

=IF(ISNA(MATCH($A15,B$2:B$10,0)),"",INDEX($A$2:$A$10,MATCH($A15,B$2:B$10,0)))

Name    Role1   Role2   Role3   Role4  
Alice   19-Jan          17-Jan  18-Jan  
Bruce           18-Jan  19-Jan  14-Jan  
Chuck   18-Jan  19-Jan  16-Jan  13-Jan  
Derek   14-Jan  17-Jan  18-Jan  19-Jan  
Emily   16-Jan  14-Jan  15-Jan  17-Jan

如果所有姓名都至少扮演过一次该角色，则它将提供匹配的最近日期。如果姓名从未扮演过该角色，则为空白单元格

下一步列表

下一个部分 (A12:D12) 查找每个角色列中的最早日期并提供匹配的名称。它的工作原理是查找列中的最小数字，然后返回其行。空单元格（从未执行）优先。

=IF(ISNA(MATCH("",B$15:B$19,0)),INDEX($A$15:$A$19,MATCH(MIN(B$15:B$19),B$15:B$19,0)),INDEX($A$15:$A$19,MATCH("",B$15:B$19,0)))

Next-Up Role1   Role2   Role3  
Name    Bruce   Alice   Emily

所有公式都可以向下和向右拖动。如果您希望扩展日期范围，请在第 2 行下方插入其他行，公式将继续起作用。例如，要扩展到 1 月 31 日，请选择第 3:12 行并插入，然后选择单元格 A13:A14，并将手柄向上拖动到单元格 A2。

相关内容