时间戳

首页 > 解决方案 > 基于连续重复值定义范围

excel - 基于连续重复值定义范围

问题描述

我有一份报告说我们的软件吐出我正在尝试从中创建一些更有用的东西。该报告列出了系统上每个人的权限切换，如下所示：

JANE DOE      PERMISSION 1    N
JANE DOE      PERMISSION 2    N
JANE DOE      PERMISSION 3    Y
JOHN SMITH    PERMISSION 1    Y
JOHN SMITH    PERMISSION 2    N
JOHN SMITH    PERMISSION 3    Y

我将其转换为使用 INDEX/MATCH 来检查切换是在 Y 还是 N 的表，如下所示：

             JOHN   JANE
PERMISSION 1  Y      N
PERMISSION 2  N      N
PERMISSION 3  Y      Y

我的问题是我们有超过 100 个权限和 200 名员工。我使用的公式是这样的：

=INDEX(toggle,MATCH(1,INDEX((B$1=employee)*($A4=permission),0,1),0))

所以我的 INDEX/MATCH 正在检查 20,000 个值的列表以填充 20,000 个不同的单元格。不出所料，计算需要很长时间。

原始报告已按字母顺序组织，因此每个员工的权限都分组在一起。我想做的是告诉我的索引只查看构成当前员工权限的范围。因此，例如，我可以有一个辅助行来计算 John Doe 的范围，然后使用 INDIRECT() 将我的索引仅指向该范围。

我发现了一些涉及连续重复的问题，但大多数涉及条件格式。我找到了一个涉及宏的解决方案，该宏会自动为每组连续值创建一个命名范围，但我不想弄乱我的名称管理器（同样，超过 100 名员工，现在只包括一个用户组）我真的不想使用 VBA，除非它是唯一的选择。

标签： excelexcel-formula

解决方案

在我看来，您可以做得比限制当前公式设置中引用的范围的建议更好。

您在当前设置下遭受如此冗长的计算时间的唯一原因是您使用的是数组处理结构。更重要的是，这很容易避免，并且只需要在数据中使用一个附加列，即：

    1) 在该附加列中，输入一个公式，该公式对雇员和权限列中的条目进行简单连接，例如=F2&"|"&G2（例如，其中F2可能包含“Jane”和G2“PERMISSION 1”；另请注意附加的“|”作为分隔符，这通常是一种很好的做法），并复制到该列中的所有单元格。
    2) 在名称管理器中命名这个范围，例如concat。

您发布的公式的简单非数组等效项是：

=INDEX(toggle,MATCH(B$1&"|"&$A4,concat,0))

并且计算起来要快得多。

问候

推荐阅读