我正在尝试找到一种方法来聚合分层数据集中的数据，最好是在数据透视表中，但其他方法也可以。考虑一个如下所示的数据集（为示例大大简化）。根据这些数据，我试图构建一组函数来回答以下问题：
“水果库存总共有多少？”
“我卖多少种不同的食物？”
Item Category
======= ========
Apples Fruit
Bacon Meat
Chicken Meat
Corn Veg
Food
Fruit Food
Grapes Fruit
Meat Food
Squash Veg
Steak Meat
Veg Food
每个物品有（以及许多其他信息）类别，我们实际上可以将其视为“父级”。但还请注意，在数据集中，所有“父级”也都有自己的父级类别。在此数据集中，层次结构的样本“分支”将是食物->肉类->鸡肉。
回答“我卖多少种水果”这样的问题并不难，因为这是第一级类别。我可以使用 COUNTIF 函数并说“有多少商品属于‘水果’类别？”——然后我得到一个如下所示的表格：
Item Category COUNTIF(categories,me)
Apples Fruit 0
Bacon Meat 0
Chicken Meat 0
Corn Veg 0
Food Food 3
Fruit Food 2
Grapes Fruit 0
Meat Food 3
Squash Veg 0
Steak Meat 0
Veg Food 2
很简单 - 对于第一行，您只需查看“苹果”作为其他人的类别出现的次数。（由于它是零，我知道苹果不是父级...这应该有帮助，但我不知道如何...）现在第五行“水果”作为其他人的类别出现两次 - 由于数字不为零，我知道它是一个类别而不仅仅是一个项目。对于第一级数学来说一切都很好，但是...
这使我想到了我一直无法解决的部分......我如何知道我总共有多少种“食物”？ 鉴于我的实际数据有更多层次结构，我需要在树上上下移动，找出每个树中总共有多少个孩子。第一级 COUNTIF 函数告诉我，食物有三个子类别（水果、蔬菜和肉类）——但我真正想要的是让它以某种方式递归地确定水果、蔬菜和肉类也可能类别，并计算这些子项的相应数字。在 Excel 中，我真正想要的是能够构建另一个列，以递归/迭代方式计算整个子树中的项目总数... 在这种情况下，有七个属于食物的独特项目：3 个肉类、2 个蔬菜和 2 个水果。
一些复杂因素：
没有明确的数据中的标识符告诉我们该特定项目是否也是一个类别，或者它是否是底层项目。
每个项目只知道它的类别/父级 - 没有明确的数据来判断它是否有子级。换句话说：所有项目都属于一个类别，但只有一些项目也是类别。
在实际数据中，父关系深度可达 10 级，但不能保证层次结构中每个分支的深度是一致的：某些项目可能有 3 级深度，而下一个项目可能有 8 级。
根或最终父级不带有类别，但这是一个一次性的情况，我可以轻松手动处理。
我完全清楚，这在任何“真正的”编程语言（Perl、Python 等）中都是一个简单的练习......但最终我必须将它交给没有编程经验的人，所以我非常努力地让它适应“标准”Excel 工作簿。
答案1
首先，我完全同意@Raystafarian 的观点，Excel 并不是合适的工具。
但是，如果您确实想在这里执行此操作，这里有一个带有几个辅助列的解决方案：
- 级别：层次结构中实际项目的级别（根项目的级别为 1，子项目的级别会增加）
=IFERROR(INDEX([level],MATCH([@Category],[Item],0))+1,1) - 级别代码：每个项目的运行代码，在级别内唯一
=CHAR(CODE("a")+COUNTIF($C$2:C2,[@level])-1) - 长代码：父项和项的连接代码
=IF([@level]>1,INDEX([long code],MATCH([@Category],[Item],0)),"")&[@[level code]] - 有子项：布尔值，指示项目是否有子项
=COUNTIF([Category],[@Item])>0
使用此模型，类别包含所有项目和子类别，其代码以与父级代码相同的序列开头（例如，如果fruit的代码为aa，则其所有（孙级......）子级的代码都以 开头aa）
问题的答案：
“水果库存总共有多少？”
=COUNTIFS(Table1[long code],VLOOKUP(I3,Table1,5,FALSE)&"*",Table1[has child],FALSE)
根据模型，所有商品的起始顺序都相同。我这里只计算商品，不计算类别（您销售两种水果apples和grapes，没有要fruit销售的产品）。如果您还想计算类别，则只需排除公式的第二部分。
“我卖多少种不同的食物？”
=SUMIF(Table1[long code],VLOOKUP(I10,Table1,5,FALSE)&"*",Table1[inventory])
与...非常相似SUMIF
警告
该解决方案有两个局限性：
- 字符数：目前从 开始
a，其代码为 97，CHAR函数中支持的最后一个字符是 255，因此任何级别中超过 158 个不同类别都会出错（您可以使用第一个代码较小的字符来稍微扩展它） - 随着数据库的增加，其性能可能会下降（计算复杂），您可能需要将计算方法设置为“除数据表外自动”，并在需要时手动计算。
答案2
我认为 Máté 的答案很好。我会使用列表来做到这一点：
（所有数组公式，因此Ctrl++ ）ShftEnter
因此，公式（向下拖）
D2 =INDEX($A$2:$A$12,MATCH(0,IF(ISBLANK($B$2:$B$12),COUNTIF($D$1:$D1,$A$2:$A$12),""),0))
E2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$D$2,COUNTIF($E$1:$E1,$A$2:$A$12),""),0))
F2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$E$2,COUNTIF($F$1:$F1,$A$2:$A$12),""),0))
G2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$E$3,COUNTIF($G$1:$G1,$A$2:$A$12),""),0))
H2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$E$4,COUNTIF($H$1:$H1,$A$2:$A$12),""),0))
现在，根据您决定如何对它们进行排序，您肯定可以创建层次结构或使用数据透视表。
我可能会给 A 和 B 列命名，以便您可以使用命名范围。