%3F.png)
階層的なデータ セットでデータを集計する方法を探しています。できればピボット テーブル内で集計したいのですが、他の方法でもかまいません。次のようなデータ セット (例のために大幅に簡略化されています) を考えてみましょう。このデータから、次のような質問に答える関数のセットを作成しようとしています。
「果物の総在庫はどれくらいですか？」
「何種類の食品を販売していますか？」
Item Category
======= ========
Apples Fruit
Bacon Meat
Chicken Meat
Corn Veg
Food
Fruit Food
Grapes Fruit
Meat Food
Squash Veg
Steak Meat
Veg Food
それぞれアイテム（他の多くの情報の中でも）カテゴリーは、実際には「親」と考えることができます。ただし、データセット内では、すべての「親」にも独自の親カテゴリがあることにも注意してください。このデータセットでは、階層の1つのサンプル「ブランチ」は次のようになります。食べ物->肉->鶏肉。
「何種類の果物を販売しているか」のような質問に答えるのは難しくありません。これは第 1 レベルのカテゴリだからです。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
簡単です。最初の行では、「リンゴ」が他の誰かのカテゴリとして何回表示されるかを確認します。(0 なので、リンゴは親ではないことがわかります... これで役立つはずですが、どのように役立つかはわかりません...) 次に、5 行目の「果物」が他の誰かのカテゴリとして 2 回表示されます。数字が 0 ではないので、これは単なるアイテムではなくカテゴリであることがわかります。最初のレベルの数学としてはこれで十分ですが...
これが、私が解決できなかった部分につながります...自分が持っている「食べ物」の種類の合計数を知るにはどうすればいいですか? 実際のデータにはさらに多くの階層レベルがあるため、ツリーを上下にたどって、各階層に何人の子がいるかを把握する必要があります。最初のレベルのCOUNTIF関数は、食品のサブカテゴリが3つあることを示しています（果物、野菜、肉）。しかし、私が本当に望んでいるのは、果物、野菜、肉がかもしれないカテゴリを選択し、それらの子に対応する数を合計します。Excel で言えば、私が本当に欲しいのは、サブツリー全体のアイテムの合計数を再帰的/反復的にカウントする別の列を作成できることです。この場合、食品に属する一意のアイテムは 7 つあります。肉が 3 つ、野菜が 2 つ、果物が 2 つです。
いくつかの複雑な要因:
ありません明示的なデータ内の識別子を使用して、その特定の項目がカテゴリでもあるかどうか、または最下位レベルの項目であるかどうかを判断します。
各アイテムは、そのカテゴリ/親が何であるかのみを認識します。子があるかどうかを示す明示的なデータはありません。言い換えると、すべてのアイテムはカテゴリに属しますが、一部のアイテムだけがカテゴリでもあります。
実際のデータでは、親関係は 10 レベルまで深くなる可能性がありますが、階層内の各ブランチの深さが一貫しているという保証はありません。一部の項目が 3 レベルの深さである一方で、次の項目が 8 レベルの深さである可能性があります。
ルートまたは最終的な親にはカテゴリが付属していませんが、これは手動で簡単に処理できる 1 回限りのケースです。
これは、どんな「本物の」プログラミング言語 (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)
モデルによれば、すべてのアイテムは同じ開始シーケンスを持ちます。ここでは、カテゴリではなくアイテムのみをカウントします (2 種類の果物applesとを販売している場合grapes、販売する製品はありませんfruit)。カテゴリもカウントする場合は、式の 2 番目の部分を除外します。
「何種類の食品を販売していますか？」
=SUMIF(Table1[long code],VLOOKUP(I10,Table1,5,FALSE)&"*",Table1[inventory])
かなり似ているSUMIF
警告
このソリューションには 2 つの制限があります。
- 文字数: 現在は から始まり
a、コードは 97 です。CHAR関数で最後にサポートされている文字は 255 です。そのため、どのレベルでも 158 を超える異なるカテゴリがあるとエラーが発生します (最初の文字に小さいコードの文字を使用して、少し拡張できます)。 - データベースが大きくなると、パフォーマンスが低下する可能性があります (計算が複雑になる)。計算方法を「データ テーブル以外は自動」に設定し、必要なときにだけ手動で計算することをお勧めします。
答え2
Máté は良い答えを持っていると思います。私ならリストを使って答えます:
（すべて配列数式なので、Ctrl+ Shft+ Enter）
それで、数式は（下にドラッグ）
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 列に名前を付けることをお勧めします。