我正在嘗試找到一種方法來聚合分層資料集中的數據，最好是在資料透視表中，但其他方法也可能可以。考慮一個資料集（該範例已大大簡化），如下所示。根據這些數據，我嘗試建立一組函數來回答以下問題：
“我的水果總庫存是多少？”
“我賣多少種不同的食品？”
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
簡單 - 對於第一行，您只需查看“Apples”作為其他人的類別出現的次數。 （因為它是零，我知道蘋果不是父母......這應該有幫助，但我不確定如何......）現在第五行“水果”作為其他人的類別出現兩次- 因為數字不為零，我知道它是一個類別而不僅僅是一個項目。對於第一級數學來說一切都很好，但是...
這導致我無法解決的部分......我如何知道我總共有多少種「食物」？ 鑑於我的實際數據具有更多層次結構，我需要在樹上走來走去才能弄清楚每個數據中總共有多少個孩子。第一級 COUNTIF 函數告訴我食物有三個子類別（水果、蔬菜和肉類）——但我真正想要的是以某種方式讓它遞歸地確定水果、蔬菜和肉類也可能是分類，並總結這些孩子對應的數字。用Excel術語來說，我真正想要的是能夠建立另一個列，遞歸/迭代地計算整個子樹中的項目總數...在這種情況下，有七個屬於食物的獨特項目：3種肉， 2個蔬菜，2個水果。
一些複雜的因素：
沒有明確的資料中的識別碼告訴我們該特定項目是否也是一個類別，或者它是否是一個底層項目。
每個項目只知道它的類別/父項是什麼 - 沒有明確的數據來告訴它是否有子項。換句話說：所有 Item 都屬於一個 Category，但只有某些 Item 也是 Category。
在實際資料中，父關係的深度可以達到 10 級，但不能保證層次結構中每個分支的深度是一致的：某些項目可能是 3 級深，而下一個可能是 8 級。
根或最終父級沒有附帶類別，但這是我可以輕鬆手動處理的一次性情況。
我完全意識到這對於任何「真正的」程式語言（Perl、Python 等）來說都是一個微不足道的練習......但最終我必須將其交給沒有程式設計經驗的人，所以我非常非常努力地使其適合“標準”Excel 工作簿。
答案1
首先，我完全同意@Raystafarian，Excel 不是合適的工具。
但是，如果您確實想在這裡執行此操作，這裡有一個包含一些輔助列的解決方案：
- level: 層次結構中實際項目的層級（根項目的層級為 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]] - has child: 布林值，告訴專案是否有孩子
=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
我認為馬特有一個很好的答案。我的做法是使用清單：
（所有數組公式，所以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 列命名，以便您可以使用命名範圍。