難解なエクセル集計の仕方を教えてください！！

質問

以下のように集計したいのですが、いい方法はありませんか？
解りにくいですが、宜しくお願いします！！！

－－－－－－－－－－－－－－－－－－－－－－－－
【関係図】
　(1)(2)という２つグループの関係を表しています。
　組織図のようなものと思ってくださって結構です。

■元データ
【表あ】
　関係図を分解し、2つごとの関係に直したデータです。

【表い】
　それぞれA-Oに対応する数字が入っています。

■集計要望
【表あ】と【表い】ふたつのデータを元に、関係図に基づいた集計をしたい。

例：
１．Aを集計すると、Aの下位のものはすべて集計したい。（A～Gまでの集計）

２．Dを集計すると、Dの下位のみが集計されるようにしたい。(D+E+F+G)

３．Fを集計すると、Fの下位のみ集計したい。(F+G)

４. Iを集計＝I+K、Jを集計＝J+L+M+N+O

【関係図】
　(1)A-B-C
　　　　 D-E
　　　　　 F-G

　(2)H-I-K
　　　 J-L-M
　　　　 　N-O

【表あ】

　A ― B
　B ―　C
　B ― D
　D ― E
　D ― F
　F ― G
　H ― I
　I ― K
　H ― J
　J ― L
　L ― M
　L ― N
　N ― O

【表い】

A=1
B=2
C=4
D=5
E=6
F=7
G=8
H=9
I=10
J=11
K=12
L=13
M=14
N=15
O=16

回答No.2

grumpy_the_dwarf · ベストアンサー

まず「表あ」ですが、AとHが親であることを示すために、
　- A
A - B
:　:
F - G
　- H
H - I
のように変更します。B列を中心にして、すべての文字について自分
の直系の親が記載されているわけです。1行目は空けて2行目から表を
作ってください。で、C列を

C2: =IF(ISERROR(VLOOKUP(A2,B1:C$2,2,0)),"",VLOOKUP(A2,B1:C$2,2,0))&B2

のようにすると、一番上の世代から自分に至る経路が表示されます。
「表あ」のB列にすべての文字が1度ずつ出てくるようになったのです
から、「表い」は不要です。D列にでも数値を書いておけばオッケー
ですね。でもまあ、後々のメンテナンスを考えて、

D2: =VLOOKUP(B2,表い,2,FALSE)

とでもしておきましょうか。

さて、いよいよ結果です。文字"A"の集計値は
=SUMPRODUCT(1*NOT(ISERROR(FIND("A",$C$2:$C$16))), $D$2:$D$16)
となります。find()関数で探している"A"を適当に置き換えればどの
文字でも探せます。Aは33、Dは26、Fが15でIが22、Jは69になりまし
た。こんな感じでいかがでしょう。

回答No.6

imogasi

#4です。ＶＢＡで表を作ってみました。
（２者関係＝表あ）
Ａ，Ｂ、Ｃ・・・を数値１，２、・・に（都合で）置換えました。
Ｓｈｅｅｔ１のＡ１：Ｂ１３に
1 2
2 3
2 4
4 5
4 6
6 7
8 9
9 11
8 10
10 12
12 13
12 14
14 15
Ｓｈｅｅｔ２にＡ２：Ａ１６にＡ－Ｏ、Ｂ１：Ｐ１に
Ａ－Ｏを入れておきます。
（コード）
Sub TEST01()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = Worksheets("sheet1")
Set sh2 = Worksheets("sheet2")
sh1.Range("c1:c15").ClearContents
sh2.Range("b2:o15").ClearContents
GoTo p01
p02:
For i = 13 To 1 Step -1
If sh1.Cells(i, "B") = R Then
sh1.Cells(i, "C") = 1
sh2.Cells(R + 1, R + 1) = 1
L = sh1.Cells(i, "A")
sh2.Cells(L + 1, L + 1) = 1
For k = 2 To 16
If sh2.Cells(R + 1, k) = "" Then
Else
sh2.Cells(L + 1, k) = sh2.Cells(R + 1, k)
End If
Next k
End If
L = R
Next i
'---
p01:
For j = 13 To 1 Step -1
If sh1.Cells(j, "C") = "" Then
sh1.Cells(j, "C") = 1
R = sh1.Cells(j, "B")
GoTo p02
End If
Next j
End Sub
（結果）
A B C D E F G H I J K L M N O
A 1 1 1 1 1 1 1
B 1 1 1 1 1 1
C 1
D 1 1 1 1
E 1
F 1 1
G 1
H 1 1 1 1 1 1 1 1
I 1 1
J 1 1 1 1 1
K 1
L 1 1 1 1
M 1
N 1 1
O 1
この表もＯＫＷＥＢでは列が崩れるでしょう。
＃４での誤りが（正しくはＬ１１はブランク。Ｊは６９）見つかりました。

回答No.5

taisuke555

＃１で回答した者です。

＃２さんの仰るようにフォーマットを変えれるのであれば

　 親 値 合計
A　-　1　=C2+SUMIF($B$2:$B$16,A2,$D$2:$D$16)
B　A　2　=上のセルをコピー貼り付け（以下同じ）
C　B　4
D　B　5
E　D　6
F　D　7
G　F　8
H　-　9
I　H　10
J　H　11
K　I　12
L　J　13
M　L　14
N　L　15
O　N　16

のように１つの表でできます。
（１行目は見出しとして２行目から[A]が入力されています）

回答No.4

imogasi

変数をＡからＯの１５個使う例とします。間の列で飛んでいて（使わない変数があっても）も良い。
第１行と第１列はＡからＯまでの見出しを（判りやすくするため）セットします。
（１）そして変数の値を第２行目にセットします。
Ｂ２＝１、Ｃ２＝２、・・以下「表い」に書いておられる通りです。
（２）そして求める（行うべき計算）式は、単純に加算だけですから、足すか足さないかの表情報を作れば、計算できます。
該当列が加算する場合は１、加算しない場合は０（または空白。本回答ではこちらを採用）をセットすれば良いのです。
（３）その表はＡ３：Ｐ１７の１５行に作ることにします。
例えば質問例ではＤについてのその情報がＤ＋Ｅ＋Ｆ＋Ｇですから、
Ｄ６、Ｅ６、Ｆ６、Ｇ６に１を入れます。
（４）すると=SUMPRODUCT($B$2:$P$2*B6:P6)で求めるものが求まります。＄は必要です。
（５）Ａに付いての答えは=SUMPRODUCT($B$2:$P$2*B3:P3)です。
（６）Ｂに付いては=SUMPRODUCT($B$2:$P$2*B4:P4)ですから、式の複写をすると良いのです。したがってＡからＯまでの値は、式の複写で一発で求められます。
（７）それで結局（３）の表をどのようにして作るかが残された問題です。
関数では難しいと思いＶＢＡで組もうとしましたが、複雑そうで出きれば
考えます。
（８）取りあえず私が（人間が）表を作ってみました。

A B C D E F G H I J K L M N O
1 2 4 5 6 7 8 9 10 11 12 13 14 15 16
A 1 1 1 1 1 1 1
B 1 1 1 1 1 1
C 1
D 1 1 1 1
E 1
F 1 1
G 1
H 1 1 1 1 1 1 1 1
I 1 1
J 1 1 1 1 1 1
K 1
L 1 1 1 1
M 1
N 1 1
O 1
（ＯＫＷＥＢでは上表はメチャメチャに成るかもしれませんが、「１」の入るべきセルを推定してください。）
(結果）
A 33
B 32
C 4
D 26
E 6
F 15
G 8
H 100
I 22
J 81
K 12
L 58
M 14
N 31
O 16

M 14
N 31
O 16
（その他）
この問題は「データ構造」の「多分木」（本件では２分木）の構造が背景にあるものと思われれます。
親（上位）は１つしかないことに特徴が出ています。
よほど数学や論理学的にしっかりした理屈で考えないと
頭がこんがらがるが、奥が深い種類のテーマです。コンピュータメモリに実装する方法も、ＷＥＢ上でも（有名な問題の割には）見つからなかった。
この本質問の問題をデータベースに組みこむと、例えば部品ヒエラルキーにおける、任意の部品（ブロック）の価格総額が計算できますね。

回答No.3

zinchan

２関係から、全体を把握することで、集計を困難にしているように思います。
　いっそ、表いに、表あの関係を組み合わせた、全体の上位下位のつながりが理解しやすいような表を作成したらいかがでしょうか。
　例えば、AはBとの直接関係しかないが、BはCとDに関係し、Cの下位はないがDにはE、Fと関係があるということでは、計算上、AとC、AとD、AとE・Ｆ等、間接的な関係および全体の関係がつかめません。よって、集計上間接的に関係するグループをそれぞれ表の列にまとめたらいかがでしょうか。
　下記のような表を新たに作成し、「表い」に相当する部分は項目名とその数値を入力しておき、その左にA～Oまでの直接・間接的な関係を一連で記号(としての数値)にして列で入力しておきます。すなわち、「表あ」の直接関係をはずし、間接関係にまで及ぶ表を、「表い」に付け加える様式です。
　例えば、一行目に記載されたA～OのAにはA～Gで一グループ、BにはB～Gで一グループ、CはBの下位でCの下位には何もないのでC単独、DにはD～Gで一グループという関係が一目で判別できる表です。
例
　値　A　B　C　D　E　F　G　H　I　J　K　L　M　N　O
A 1　1
B 2　1　1
C 4　1　1　1
D 5　1　1　　 1
E 6　1　1　　 1　1
F 7　1　1　　 1　 　1　
G 8　1　1　 　1　 　1　1
H 9　　　　　　　　　　　　1
I 10　　　　　　　　　　　　1　1
J 11　　　　　　　　　　　　1　1　1
K 12　　　　　　　　　　　　1　1　1　1
L 13　　　　　　　　　　　　1　　 1　 　1
M 14　　　　　　　　　　　　1　　 1　　 1　1
N 15　　　　　　　　　　　　1　　 1　 　1　　 1
O 16　　　　　　　　　　　　1　　 1　 　1　 　1　1

　１行目のA列の同列最上行か最下行に、
=SUMIF(C2:C16,1,$B$2:$B$16)
※　C2:C16はそれぞれ関係グループを表す列の範囲、$B$2:$B$16はA～Oの値の範囲として置き換えてください。

と入力し、Oの列までコピーします。
　直接、間接関係に及ぶグループの、それぞれの値を合計した値がそこに表示されます。
　この方法のメリットは、関係に変化が生じた場合、簡単に表の数値を入れ替えられ、集計式の変更をしなくて済む点にあります。また、新たな項目が増えても、表を拡大して集計式をコピーすれば済むので、メンテナンスが楽な点もあります。
　デメリットは、直接関係以外にも間接関係等全体の関係を把握して、関与するグループ全てに同じ記号をつけなければならないのが面倒な点です。
　しかし、いずれにしろ関係図が書けるのであればその把握は容易ですし、逆に関係の全体を把握しやすく、集計も難しく考えずに済むので、メリットの方が大きいと思うのですが、いかがでしょうか。

回答No.1

taisuke555

もっと簡単な方法があったらすみません。
結果が[Sheet2]のＣ列に表示されます。

[Sheet1]（表あ）
Ａ列　Ｂ列　Ｃ列
　Ａ　　Ｂ　　=VLOOKUP(B1,Sheet2!$A$1:$B$15,2,FALSE)+SUMIF($A$1:$A$13,B1,$C$1:$C$13)
　Ｂ　　Ｃ　　１行目をコピー貼り付けしてください
　Ｂ　　Ｄ　　〃
　・・・・・・・・・

[Sheet2]（表い）
Ａ列　Ｂ列　Ｃ列
　Ａ　　１　　=B1+SUMIF(Sheet1!$A$1:$C$13,A1,Sheet1!$C$1:$C$13)
　Ｂ　　２　　１行目をコピー貼り付けしてください。
　Ｃ　　４　　〃
　・・・・・・・・・

いかがでしょうか？（考え方が違っていたら補足してください）