[[20190614120151]]　『階層ごとの計算について』（大宮）　ページの最後に飛ぶ

『階層ごとの計算について』（大宮）

下記のように階層毎に合計をまとめている表があるのですが
階層を判定して各階層の単価を入力したら
自動で各合計をまとめられる表にしようと今試行錯誤しています。

階層をLEN関数で文字数を取得してSUMPRODUCT関数で合計を出すように
しようとしたのですが、枝番が2ケタの場合に対応できず

他に良い書き方等思いつかず
VBAを使ってプログラムしようとしたのですが、
自分が使用する表ではなく他の方が使用する為、
メンテがしやすいよう関数を出来れば使ってほしいという要望でして
困っています。

SUMPRODUCT関数の条件判定をAND（LEN(階層)、LEN(階層)+1）
など思いつく限り対応しようとしているのですが上手くいかず
関数を使った書き方で解決できるのでしょうか？
良い方法があれば教えて頂きたいです。

階層　　　　　単価　　　　　　合計

1　　　　　　　10　　　　　　　　30　※（階層1・2・3）の合計
1-1　　　　　　 5　　　　　　　　15　※（階層1-1・1-2・1-3）の合計
1-1-1　　　　　 1　　　　　　　　 2　※（階層1-1-1　～　1-1-10）の合計
1-1-1-1
1-1-1-1
1-1-1-2
1-1-1-3
1-1-1-4
・
・
・
1-1-10　　　　　 1　　　　　　　 2
1-2　　　　　　 5　　　　　　　　15
1-3　　　　　　 5　　　　　　　　15

2　　　　　　　10　　　　　　　　30
2-1　　　　　　20　　　　　　　　40
2-1-1　　　　　 2　　　　　　　　 4
2-2　　　　　　20　　　　　　　　40
2-2-1　　　　　 2　　　　　　　　 4
3　　　　　　　10　　　　　　　　30

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 1階層目
 =SUMPRODUCT((LEN(A$1:A$15)-LEN(SUBSTITUTE(A$1:A$15,"-",""))=0)*B$1:B$15)
 2階層目
 =SUMPRODUCT((LEN(A$1:A$15)-LEN(SUBSTITUTE(A$1:A$15,"-",""))=1)*B$1:B$15)
 3階層目
 =SUMPRODUCT((LEN(A$1:A$15)-LEN(SUBSTITUTE(A$1:A$15,"-",""))=3)*B$1:B$15)
 ではどうか？

（ねむねむ） 2019/06/14(金) 13:03

 また、
 =SUMPRODUCT((LEN(A$1:A$15)-LEN(SUBSTITUTE(A$1:A$15,"-",""))=ROW(A1)-1)*B$1:B$15)
 と入力して下へフィルコピーすると上から1階層目、2階層目、…の合計を表示する。
 なお、
 LEN(A$1:A$15)-LEN(SUBSTITUTE(A$1:A$15,"-","")
 で階層内の-の個数を数え、0個であれば1階層目、1個であれば2階層目…と判断している。
（ねむねむ） 2019/06/14(金) 13:06

 おっと
 >3階層目
 >=SUMPRODUCT((LEN(A$1:A$15)-LEN(SUBSTITUTE(A$1:A$15,"-",""))=3)*B$1:B$15)
 は
 =SUMPRODUCT((LEN(A$1:A$15)-LEN(SUBSTITUTE(A$1:A$15,"-",""))=2)*B$1:B$15)
 で。

（ねむねむ） 2019/06/14(金) 13:08

 すまない。
 ちょっと解釈を間違えていたようだ。
 考え直してみる。

（ねむねむ） 2019/06/14(金) 15:31

 例の
 2-1-1
 2-2-1
 はそれぞれ2-1-Xのもの、2-2-Xの物の計なのでどちらも2なのでは？
 それが正しければ階層がA列、単価がB列で合計がC列としてD列を作業列に使う。
（ねむねむ） 2019/06/14(金) 15:46

 D1セルに
 =IFERROR(LEFT(A1,FIND("#",SUBSTITUTE(A1,"-","#",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))),"")
 と入力して下へフィルコピー。
 C1セルに
 =IF(B1="","",SUMIF(D:D,D1,B:B))
 と入力して下へフィルコピーでどうだろうか？
（ねむねむ） 2019/06/14(金) 15:47

すいません
自分もいろいろ考えてみます。
ご返信遅れて申し訳ないです。
（大宮） 2019/06/14(金) 16:04

＞2-1-1
＞ 2-2-1
＞はそれぞれ2-1-Xのもの、2-2-Xの物の計なのでどちらも2なのでは？
上記については下の例が正しいです。
　　　　　単　　　　合
2-1　　　　20　　　 40
2-1-1　　　2　　　　4
2-1-2　　　2　　　　4
2-2　　　　20 40

（大宮） 2019/06/14(金) 16:26

 そうすると
 1-1-1
 は
 1-1-X
 のものだけではなく
 1-2-X
 1-3-X
 ～
 の合計となるのだろうか？
 そうすると
 1-1
 は
 1-X
 だけではなく
 1-X
 2-X
 3-X
 ～
 の合計？
（ねむねむ） 2019/06/14(金) 16:37

 よく見ていなかった。
 2-2-1
 が間違いで
 2-1-2
 だと。
 では
 （ねむねむ） 2019/06/14(金) 15:47
 の式で。
（ねむねむ） 2019/06/14(金) 16:39

コメント返信：

[ 一覧(最新更新順) ]

[[20190614120151]] 『階層ごとの計算について』（大宮） ページの最後に飛ぶ

[[20190614120151]]　『階層ごとの計算について』（大宮）　ページの最後に飛ぶ