☰

Excel：如何验证分层数据的层次一致性

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
Excel：如何验证分层数据的层次一致性

我在 Excel 中有一个分层数据，如下所示：

Level 1             Level 2         Level 3             GL Name
Current Liabilities Trade Creditors Foreign Creditors   Bob
Current Liabilities Trade Debtors   Foreign Creditors   Mary
Current Assets      Trade Creditors Foreign Creditors   John

我需要验证某一级别的所有出现是否具有相同的父级层次结构。在上面的示例中，第 2 行中的外国债权人与第 1 行相比具有不同的 2 级父级。第 3 行中的贸易债权人与第 1 行相比具有不同的 1 级父级。

如果给定名称的级别不一致，则表明数据不正确，我必须向财务团队报告。我该如何进行此验证？

答案1

假设第一次出现是参考

假设对于第 4 行，找到参考出现（行号）

=MATCH(C4, C:C, 0)

检查 2 级匹配

=B4=INDEX(B:B, MATCH(C4, C:C, 0))

检查 1 级比赛

=A4=INDEX(A:A, MATCH(C4, C:C, 0))

或者同时勾选

=AND(A4=INDEX(A:A, MATCH(C4, C:C, 0)), B4=INDEX(B:B, MATCH(C4, C:C, 0)))

答案2

假设为了有效性，层次结构中给定级别的每个子值都与一个且唯一的父值相关联，则每个级别的有效父子值列表不仅可以确定整个层次结构是否有效，而且如果无效，还可以确定无效的位置。

一般方法如下面的简单示例所示。

在此处输入图片描述

由此可见

  • 与原始问题采用相同列表格式的分层数据
  • 对于层次结构中的每个父子级别，每个可能的子值的正确父子关系
  • 父级验证列表，用于标识层次结构数据中的每个子级是否具有正确的父级

如果分层数据列表有 N 层，则必须指定 N-1 组父子关系。父验证列表有 N-1 列，行数与分层数据相同。

该方法涉及检查分层数据列表中的每个子项是否具有正确的父项，如层次结构中该子项级别的父子关系中所指定的。

这种方法的难点在于指定正确的父子关系。每组关系应一次且仅一次地表示层次结构中的所有可能子值，因此给定子列中的值应该是唯一的。（Excel 中有多种方法可以确定列表中的唯一值，包括UNIQUE最新版本 Excel 中的函数。如果此功能不可用，则“数据”菜单提供了一种删除重复项的方法，可以在备份分层数据的列。

在所示的工作表中，条件格式已用于突出显示分层数据中显示为不正确的父子对的单元格。使用了两个格式规则，一个用于单元格A3，另一个用于单元格B3。在两个规则中都使用了公式NOT(J3)。格式刷用于将的格式复制A3到范围A3:B11和B3复制到B3:C11。

在检查标记的任何错误时，必须牢记，突出显示的父子对中的子项或父项可能是不正确的。

相关内容