ツリー状の集計表の小計、合計を同じ行で計算するには?
- 表の中にある小計、合計を同じ行で自動的に計算する方法を教えてください。
- 通常、このような表を作成する際にはsum関数を使用して計算しますが、間違いが多く、マクロを使用して簡単に計算したいと考えています。
- 良い方法をご存じの方がいらっしゃいましたら、教えてください。
- ベストアンサー
ツリー状の集計表の小計、合計を同じ行で計算するには?
A B C D E F G 1 商品名 単価 個数 2 合計 1800※ 3 食物 1600※ 4 果物 800※ 5 りんご 100 5 500 6 ばなな 150 2 300 7 さかな 800※ 8 いか 200 1 200 9 さんま 200 3 600 10 文房具 200※ 11 紙 200※ 12 A3 10 10 100 13 A4 5 20 100 上のような表があるとします。 G4、G7、G11には下2品目の小計、G3、G10にはそのその分 類の小計、G2にはその合計、と言ったように※印ついている数字の部 分を自動的に計算するような良い方法はないでしょうか? このような感じの表を作ることが多いのですが、その都度sumを使って 計算させてましたが、間違うことが多く(自分のせいなのですが(^^;) マクロを使って、ボタン一つで計算出来るようにしたいのですが・・。 良い方法をご存じの方、いらっしゃいましたらお願いいたします。
- suika_no1
- お礼率57% (56/98)
- オフィス系ソフト
- 回答数4
- ありがとう数2
- みんなの回答 (4)
- 専門家の回答
質問者が選んだベストアンサー
>Dが空白でない場合、GにE*Fの答えを入力させるには、どうしたらよいのでしょうか? E*Fを計算できるようにしてみました。 操作方法は前回と同じで、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
その他の回答 (3)
- nishi6
- ベストアンサー率67% (869/1280)
単純なアルゴリズムで集計できます。 質問の表なら、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
補足
お返事、ありがとうございます。 うまくいきまして大変感動しました。これからの作業が大変楽になります。本当にありがとうございます。 ところで、Dが空白でない場合、GにE*Fの答えを入力させるには、どうしたらよいのでしょうか?そこまで行けば、もう項目を入力するだけでもっと簡単になるのですが・・・・。 何卒、宜しくお願いいたします。
- imogasi
- ベストアンサー率27% (4737/17068)
エクセルを使う場合、エクセルの関数や操作が利用しやすい 表にすることを考える必要もあるのではないでしょうか。 私の会社仕事の経験から (1)#1の方も述べられているように、小計と合計を上部に もってくるやり方は、エクセルで使いづらい。会社ではあまり お目にかからなかった。見たいものを先にという原則には 合っていますが。 (2)明細と合計を同一表に混合するのは、見る立場からすると見にくい。印象が拡散して良くない(と個人的に思っています。実際は明細・合計形式が優勢)合計部分は合計だけ分けてはどうでしょう。 (3)A列は合計として判るとしても、B列、C列が総括品目として分れているのはどうしてですか。 色々言いましたが、データ-集計をクリックして使える サブトータル機能を使うことを考えること。 また総括項目を出すなら、コード化とVLookup関数の利用などを考えられてはどうでしょう。
お礼
お返事ありがとうございます。 土木工事の積算をされている方なら、この表の形式が解ると思うのですが、国土交通省の設計仕様書がこのような形なのです。簡単に集計する色々な方法が考えられますが、検査資料等を作る際にこの表の仕様が必要なため計算に往生していると言うわけです。 説明不足で申し訳ありませんでした。
- papayuka
- ベストアンサー率45% (1388/3066)
こんにちは。 下に小計、合計を入れる方法なら、分類のような列を作り、メニューのデータ-集計で、分類をグループ基準にし、合計を集計フィールドにして、集計行をデータの下に挿入するにチェックを入れてOKすれば完了です。 分類で並び替えておく必要がありますけど。 分類 商品名 単価 個数 合計 文具 鉛筆 100 2 200 文具 紙 150 3 450 果物 りんご 200 2 400 果物 ばなな 110 3 330 魚介類 いか 60 4 240 魚介類 さんま 50 5 250 ↓ボタン1つでこうなるし、元にも戻せる。 分類 商品名 単価 個数 合計 文具 鉛筆 100 2 200 文具 紙 150 3 450 文具 計 650 果物 りんご 200 2 400 果物 ばなな 110 3 330 果物 計 730 魚介類 いか 60 4 240 魚介類 さんま 50 5 250 魚介類 計 490 総計 1870
関連するQ&A
- エクセル、小計を足して合計を出してくれない
Excel2002を使っています。 以下の表があります。 支給小計 1516 2456 支給小計 4563 4756 1435 : この支給小計の合計を出そうと、ΣアイコンでSUM関数を使い、 支給小計 1516 2456 3972←SUMで求めた合計 支給小計 4563 4756 1435 10754←SUMで求めた合計 : としました。 そして、SUMで求めた10754の下のセルに、支給総合計を出そうとΣアイコンで求めたところ、合計を足してきてくれません。見当違いのセルを探してしまいます。 SUMの計算式の途中、名前ボックスがROUND~とでます。普通はTODAYが出る?? 多分、表が悪いと思いますが‥‥小計同士を足してきてくれない場合、どんな原因が考えられますか?
- ベストアンサー
- オフィス系ソフト
- Excelの集計表で固定していない小計があり、計算結果を上位の小計行に入れたい
エクセル2K使用で300行程度の表があり、2行目まではタイトル行です。 品名L列 個数M列 単位N列 単価O列 金額P列 備考Q列← 2行目 完成品 1 組 (小計の計)(総合計) ← 3行目 小計 2 口 (O5:O8) (M*O) ← 4行目 品名A 2 個 10 20 ← 5行目 品名B 3 本 10 30 ← 6行目 品名C 2 個 5 10 ← 7行目 品名・ ・ ・ ・ 20 ← 8行目 品名・ ・ ・ ・ 20 ← 9行目 小計 (O :O ) (M*O) ←・行目 品名・ ・ ・ ・ ・ ←・行目 品名・ ・ ・ ・ ・ ←・行目 品名・ ・ ・ ・ ・ ←・行目 品名・ ・ ・ ・ ・ ←・行目 ・ ・ ・ ・ ・ ・ ←・行目 ・ ・ ・ ・ ・ ・ ←・行目 合計 (小計の計) L列の一番上の小計は固定です、2ツ目以降の小計は固定していません。 P列の金額を上位の小計列の単価に入力して再計算したい 合計はP列(表の最終行)の合計金額欄に表示したい、3行目完成品単価は合計 金額の(小計の計)と同じ金額です、3行目を見積書に転記したいと思っています。 集計表には空白セルが存在します。 宜しくお願いします。
- 締切済み
- オフィス系ソフト
- EXCELで途中の集計行を除いて集計したい。
EXCEL2002で合計を出したいのですが、途中に「合計」とか「中計」などの集計行があり、最初から最後までSUM関数を使うとその分だけ重複してしまいます。途中の計算式が入っているセルを除いて集計するには、どうしたらよいでしょうか?A列に項目(例えば、事務用品、コピー用紙、小計、固定電話料、携帯電話料、通信費計・・・)、B列に数値または計算式を入力しています。
- ベストアンサー
- オフィス系ソフト
- ピボットテーブルで小計と合計が合わない
おねがいします。ピボットテーブルで購入先別、購入品別に税抜き額と消費税率1.1を記入した表から、税込み額を集計したのですが、A社の製品別の税込み額を足してもA社合計税込み額と合いません。(1.1×100+1.1×200=330なのに集計表では660になっている) この原因はピボットテーブルの計算ではA社合計額は税込み小計額の合計(正しい計算方法)ではなく、消費税率の合計(1.1+1.1=2.2)×税抜き額合計(100+200=300)をしているからですが、どうしたら正しい方法で計算するようにできるでしょうか?
- ベストアンサー
- Excel(エクセル)
- エクセルでページ毎の小計から最終ページで合計を出したい。
各ページ毎に小計を出し、最終ページにおいて、小計の下に合計を計算するような表があります。 毎回、ページ数の増減があります。 合計にどのような小計の集計の式を作成したら、よいでしょうか? お教えくださいますよう、お願いいたします。
- ベストアンサー
- オフィス系ソフト
- 集計表の作成
集計表の作成 AccessとExcelを使用して集計Aを作成したいと思っています。 (ボタン1クリックだけで行なうのが理想です) 1.グループ毎に小計行を追加したい(小計行はグループ毎の合計 2.エクスポートしたExcelの最終に合計行の追加したい (合計行は全グループの合計) A列はデータ数によって毎回行数が変わる為にあらかじめレイアウトを 作成して、Vlookuopで当てはめるという事も出来ません。 元となる金額のデータはAccessで作成してExcelにエクスポートする 所までは出来たのですがそれ以降(小計行の追加・計算、合計行の追加・計算)が分かりません。どの様にすればよいでしょうか? Access:仕入先テーブル コード |テキスト型 名称 |テキスト型 仕入額 |数値型 前年比 |数値型 グループ |数値型 Excel:集計A A | B | C | D | E | F | 1 |コード | 名称 |仕入額 |前年比|グループ| 2 |1030 | A | 1,000 | 49%|1 | 3 |1040 | B | 1,050 | 32%|1 | 4 | |小計(A+B) | 2,050 | 81%| 5 |1050 | C | 1,000 | 50%|2 | 6 |1060 | D | 1,050 | 52%|2 | 7 |1070 | E | 1,050 | 49%|2 | 8 | |小計(C+D+E)| 3,100|50.3%| 9 |1080 | F | 1,000 | 60%| | 10|1090 | G | 2,000 | 38%| | 11|2000 | H | 1,000 | 60%| | ・ ・ ・ ・ | |合計 | 9,150 |49% | ・・・C4+C8+C9+合計行の直前まで
- 締切済み
- その他MS Office製品
- Excelの集計表で固定していない小計があり、計算結果を上方の小計行に入れたい。
エクセル2K使用で300行程度の表があり、2行目まではタイトル行です。 A列 B列 C列(金額)2行目 あ 小計 60 い (空白) 10 う (空白) 20 え (空白) 30 お 小計 90 か (空白) 40 き (空白) 50 く 小計 400 ←計算が合いません け (空白) 60 こ (空白) 70 さ (空白) 80 し (空白) 90 す (空白) 100 カテゴリOffice系で上記の質問をいたしましたが、 "VBAで無いと難しいと思う"とアドバイスいただきましたので、 こちらで質問させていただきます。 VBAは超初心者ですが、色々の例題を検索し試行錯誤して下記マクロを 作成しましたが、一番下の小計が合いません宜しくお願いします。 Sub SYOUKEI() Dim i As Long Dim myLAST_ROW As Long Dim myTOP_ROW As Long Dim myBOTTOM_ROW As Long Dim myRANGE As Range With ActiveSheet myLAST_ROW = .Cells(Rows.Count, 1).End(xlUp).Row myTOP_ROW = 3 For i = myLAST_ROW To 1 Step -1 If .Cells(i, 2).Value = "小計" Then myBOTTOM_ROW = i + 1 Set myRANGE = _ .Range(.Cells(myTOP_ROW, 3), .Cells(myBOTTOM_ROW, 3)) .Cells(i, 3).Value = WorksheetFunction.Sum(myRANGE) myTOP_ROW = i - 1 End If Next i End With Set myRANGE = Nothing End Sub
- ベストアンサー
- Visual Basic
- 表計算で、SUM関数でもとめた合計を10%アップルされる関数は?
初歩的な質問で、恥ずかしいのですが、 エクセル表計算で、 A(個数)×B(単価)=合計 ←SUM関数でもとめています。 この合計を10%アップされる関数を教えて下さい。
- 締切済み
- その他(インターネット・Webサービス)
- エクセルの計算表について(縦計と横計)
仕事をしててふと疑問が出たので、質問させてもらいます。縦計と横計の合計を表す数式はありますか? A B C 1 1 2 3 2 3 4 7 3 4 6 ? 上表のように「A1+A2=A3」の縦計と「A1+B1=C1」の横計が計算されますよね。それぞれ計算され小計が出て、「?」の所に合計を入れたいと思ったのですが…はたして何か数式はあるのかな?と思い、質問しました。 SUMで横計を入れると縦計が合っているかわからないし、SUM縦計を入れると横計が合っているかわからないし…。今日の仕事はSUM横計で入れてきましたが。 わかりにくい文章かもしれませんが、回答補助をしていきますのでよろしくお願い致します。
- ベストアンサー
- オフィス系ソフト
- エクセルで行を挿入し、小計、合計を出したい
質問いたします。 A列に支店コード(4桁の数値)、J列に金額、K列に手数料があります。 支店は5箇所でデータは1支店あたり100~500行ほどあります。全支店のデータが連続しています。 1.支店コードの最終行の下に1行挿入し、J列,K列の小計を計算する。 2.最後の支店の小計の下に一行あけてJ列,K列の合計をしたい。 どのようにしたら良いか教えてください。
- ベストアンサー
- Visual Basic
お礼
本当にありがとうございます。なんとお礼を言って良いのやら。 早速会社の人間に見せて、驚かせました。 ただ、コードを見てもなぜこれで計算出来るのか、今一つ理解出来ない自分が玉に瑕です。(^^; 本当にありがとうございました。