ツリー状の集計表の小計、合計を同じ行で計算するには？

質問

Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ　　　Ｇ
　１　　　　　　　　　　　　商品名　　単価　　個数　
　２　合計　　　　　　　　　　　　　　　　　　　　　１８００※
　３　　　　　食物　　　　　　　　　　　　　　　　　１６００※
　４　　　　　　　　　果物　　　　　　　　　　　　　　８００※
　５　　　　　　　　　　　　　りんご　１００　　　５　５００
　６　　　　　　　　　　　　　ばなな　１５０　　　２　３００
　７　　　　　　　　　さかな　　　　　　　　　　　　　８００※
　８　　　　　　　　　　　　　いか　　２００　　　１　２００
　９　　　　　　　　　　　　　さんま　２００　　　３　６００
１０　　　　　文房具　　　　　　　　　　　　　　　　　２００※
１１　　　　　　　　　紙　　　　　　　　　　　　　　　２００※
１２　　　　　　　　　　　　　Ａ３　　　１０　　１０　１００
１３　　　　　　　　　　　　　Ａ４　　　　５　　２０　１００

上のような表があるとします。
Ｇ４、Ｇ７、Ｇ１１には下２品目の小計、Ｇ３、Ｇ１０にはそのその分
類の小計、Ｇ２にはその合計、と言ったように※印ついている数字の部
分を自動的に計算するような良い方法はないでしょうか？

このような感じの表を作ることが多いのですが、その都度sumを使って
計算させてましたが、間違うことが多く（自分のせいなのですが(^^;）
マクロを使って、ボタン一つで計算出来るようにしたいのですが・・。
良い方法をご存じの方、いらっしゃいましたらお願いいたします。

回答No.4

nishi6 · ベストアンサー

＞Ｄが空白でない場合、ＧにＥ＊Ｆの答えを入力させるには、どうしたらよいのでしょうか？

Ｅ＊Ｆを計算できるようにしてみました。
操作方法は前回と同じで、A2～G13を選択状態にしてマクロを実行します。

こういう風に小計を出していくには、このようなTree構造のほうが処理が行いやすいかもしれませんね。判定に未入力セルを有効に使えます。

Sub TreeTotal()
　　Dim Area As Range '選択範囲
　　Dim top As Range '一番左上のセル
　　Dim TTLcolumns As Integer '選択範囲の列数
　　Dim TTLrows As Integer '選択範囲の行数

　　Set Area = Selection
　　　　TTLcolumns = Area.Columns.Count - 1
　　　　TTLrows = Area.Rows.Count - 1
　　Set top = Area.Range("A1")

　　Dim c As Integer '列カウンタ
　　Dim r As Integer '行カウンタ
　　Dim TTL As Long 'トータル
　　Dim Elm As Long '単価×個数の値
　　With top
　　　　'単価×個数の計算
　　　　For r = TTLrows To 0 Step -1
　　　　　　If .Offset(r, TTLcolumns - 3) <> "" Then
　　　　　　　　Elm = .Offset(r, TTLcolumns - 2) * .Offset(r, TTLcolumns - 1)
　　　　　　　　.Offset(r, TTLcolumns) = Elm
　　　　　　End If
　　　　Next

　　　　'小計、合計の計算
　　　　For c = TTLcolumns - 4 To 0 Step -1
　　　　　　For r = TTLrows To 0 Step -1
　　　　　　　　If .Offset(r, TTLcolumns - 3) <> "" Then
　　　　　　　　　　TTL = TTL + .Offset(r, TTLcolumns)
　　　　　　　　ElseIf .Offset(r, c) <> "" Then
　　　　　　　　　　.Offset(r, TTLcolumns) = TTL
　　　　　　　　　　TTL = 0
　　　　　　　　End If
　　　　　　Next
　　　　Next
　　End With
End Sub

お礼

本当にありがとうございます。なんとお礼を言って良いのやら。
早速会社の人間に見せて、驚かせました。
ただ、コードを見てもなぜこれで計算出来るのか、今一つ理解出来ない自分が玉に瑕です。(^^;
本当にありがとうございました。

回答No.3

nishi6

単純なアルゴリズムで集計できます。

質問の表なら、A2～G13を選択状態にして下記マクロを実行します。
質問にある※部分にその合計を書き込んでいきます。

質問のように表構造が守られていれば、何列何行あっても問題ありません。
必要とする表構造の要件は『最下層の計算は選択範囲の右4列で計算されている』ということだけです。

ここから
↓
Sub TreeTotal()
　　Dim Area As Range '選択範囲
　　Dim top As Range '一番左上のセル
　　Dim TTLcolumns As Integer '選択範囲の列数
　　Dim TTLrows As Integer '選択範囲の行数

　　Set Area = Selection
　　　　TTLcolumns = Area.Columns.Count - 1
　　　　TTLrows = Area.Rows.Count - 1
　　Set top = Area.Range("A1")

　　Dim c As Integer '列カウンタ
　　Dim r As Integer '行カウンタ
　　Dim TTL As Long 'トータル
　　With top
　　　　For c = TTLcolumns - 4 To 0 Step -1
　　　　　　For r = TTLrows To 0 Step -1
　　　　　　　　If .Offset(r, TTLcolumns - 3) <> "" Then
　　　　　　　　　　TTL = TTL + .Offset(r, TTLcolumns)
　　　　　　　　ElseIf .Offset(r, c) <> "" Then
　　　　　　　　　　.Offset(r, TTLcolumns) = TTL
　　　　　　　　　　TTL = 0
　　　　　　　　End If
　　　　　　Next
　　　　Next
　　End With
End Sub

補足

お返事、ありがとうございます。
うまくいきまして大変感動しました。これからの作業が大変楽になります。本当にありがとうございます。
ところで、Ｄが空白でない場合、ＧにＥ＊Ｆの答えを入力させるには、どうしたらよいのでしょうか？そこまで行けば、もう項目を入力するだけでもっと簡単になるのですが・・・・。
何卒、宜しくお願いいたします。

回答No.2

imogasi

エクセルを使う場合、エクセルの関数や操作が利用しやすい
表にすることを考える必要もあるのではないでしょうか。
私の会社仕事の経験から
（１）＃１の方も述べられているように、小計と合計を上部に
もってくるやり方は、エクセルで使いづらい。会社ではあまり
お目にかからなかった。見たいものを先にという原則には
合っていますが。
（２）明細と合計を同一表に混合するのは、見る立場からすると見にくい。印象が拡散して良くない（と個人的に思っています。実際は明細・合計形式が優勢）合計部分は合計だけ分けてはどうでしょう。
（３）Ａ列は合計として判るとしても、Ｂ列、Ｃ列が総括品目として分れているのはどうしてですか。
色々言いましたが、データ－集計をクリックして使える
サブトータル機能を使うことを考えること。
また総括項目を出すなら、コード化とＶＬｏｏｋｕｐ関数の利用などを考えられてはどうでしょう。

お礼

お返事ありがとうございます。
土木工事の積算をされている方なら、この表の形式が解ると思うのですが、国土交通省の設計仕様書がこのような形なのです。簡単に集計する色々な方法が考えられますが、検査資料等を作る際にこの表の仕様が必要なため計算に往生していると言うわけです。
説明不足で申し訳ありませんでした。

回答No.1

papayuka

こんにちは。

下に小計、合計を入れる方法なら、分類のような列を作り、メニューのデータ－集計で、分類をグループ基準にし、合計を集計フィールドにして、集計行をデータの下に挿入するにチェックを入れてＯＫすれば完了です。

分類で並び替えておく必要がありますけど。

分類　　商品名　　単価　　個数　　合計
文具　　鉛筆　　　100　　　2　　　200
文具　　紙　　　　 150　　　3　　　450
果物　　りんご　　200　　　2　　　400
果物　　ばなな　 110　　　3　　　330
魚介類　いか　　　60　　　 4　　　240
魚介類　さんま　　50　　　 5　　　250

　　　↓ボタン１つでこうなるし、元にも戻せる。

分類　　商品名　　単価　　個数　　合計
文具　　鉛筆　　　100　　　2　　　200
文具　　紙　　　　 150　　　3　　　450
文具 計　　　　　　　　　　　　　　　 650
果物　　りんご　　 200　　　2　　　400
果物　　ばなな　　110　　　3　　　330
果物 計　　　　　　　　　　　　　　　 730
魚介類　いか　　　 60　　　 4　　　240
魚介類　さんま　　 50　　　 5　　　250
魚介類 計　　　　　　　　　　　　　　 490
総計　　　　　　　　　　　　　　　　　1870