エクセル 集計行をデータの次の行に表示させたい。空白セルを削除したい。

質問

基準となるグループの最後のグループの集計行はなぜか、選択範囲の下に表示されます。
例えば
　1行目　　会社　サービス 単価　　回数　　合計金額
　2行目　　　A　　　　1　　 100　　　2 　　　200
　3行目　　　A　　　　2　　　150　　　3　 　　450
　4行目　　　B　　　　4　　　1000　 　4　　 　4000
　5行目　　　C　　　　3　　　500　　　10　　　5000
　　　　以下空白　　　：　 　：　 　：　　　　：
　(20行目）　　　ここまで空白
以上のような任意の表（1行目から20行目までデータが入力されています。上記のような3社で4行で終わる場合もあれば、5社で10行という場合もあります）で会社ごとの合計金額を集計したい時に、（1行～20行を選択し、グループの基準：会社　集計の方法：合計　集計するフィールド：合計金額　集計行をデータの下に挿入という設定）
　1行目　　会社　サービス 単価　　回数　　合計金額
　2行目　　　A　　　　1　　 100　　　2 　　　200
　3行目　　　A　　　　2　　　150　　　3　 　　450
　4行目　　　　　A集計　　　　　　　　　　　　650
　5行目　　　B　　　　4　　　1000　 　4　　 　4000
　6行目　　　　　B集計　　　　　　　　　　　　4000
　7行目　　　C　　　　3　　　500　　　10　　　5000　
　　　　以下空白　　　：　 　：　 　：　　　　：
　23行目　　　　　C集計　　　　　　　　　　　　5000
　24行目　　　　　総計　　　　　　　　　　　　 9650
となってしまいます。上記の参考データならば
8行目から22行目までが無駄に空白ができてしまいます。
これを、8行目にC集計を表示させることはできるのでしょうか？
集計後の表をコピーして他シートに貼り付けるマクロを組んでいます。
データによっては、1つの会社の集計が表示されません。宜しくお願いいたします。上記を満たすマクロでも結構です。

回答No.5

papayuka · ベストアンサー

#2です。

> 「集計後最後の行を除く部分をコピーする」マクロはどうなるのでしょうか？

Range("A1").CurrentRegion.Copy を修正すればOKでしょう。

Sub Test()
　Range("A1").CurrentRegion.Subtotal GroupBy:=1, Function:=xlSum, _
　　　　　　　TotalList:=Array(5), Replace:=True, _
　　　　　　　PageBreaks:=False, SummaryBelowData:=True
'　Range("A1").CurrentRegion.Copy
　With Range("A1").CurrentRegion
　　　 .Resize(.Rows.Count - 1, .Columns.Count).Copy
　End With
End Sub

回答No.4

papayuka

#2です。

> データがある範囲だけを選択すれば希望の結果になるのですが。

であれば、データがある範囲だけを選択すれば良いと思います。
例えば、A1から元の表があるとして、下記でデータ範囲が取れると思いますが如何でしょうか？

Sub Test()
　Range("A1").CurrentRegion.Subtotal GroupBy:=1, Function:=xlSum, _
　　　　　　　TotalList:=Array(5), Replace:=True, _
　　　　　　　PageBreaks:=False, SummaryBelowData:=True
　Range("A1").CurrentRegion.Copy
End Sub

補足

レス遅れまして申し訳ありません。
このマクロは
「データがある範囲だけを集計し、集計後のデータがある範囲をコピーする」という感じでしょうか？

まさしく、求めていた事です。

さらにつっこんで教えて頂きたいのですが、

上記のマクロのコピー範囲は、集計した後のデータがある範囲をコピーしてますが、「総計」とでる行は除きたいのです。

つまり、「集計後最後の行を除く部分をコピーする」マクロはどうなるのでしょうか？

質問ばっかりですみません。

回答No.3

charisan

#1(続) : 書き方があいまいで失礼しました。
「データ」に合計金額　を　「合計」で、
「列」に会社、サービス、単価、回数（つまりのこり全部）
「行」「ページ」は　からっぽにして、
「サービス」以下の項目をドラッグした際にそれぞれの項目で　右ダブルクリックをすると、ピボットテーブルフィールドというのが出ますから、「集計」なしを選ぶと、かなりいい線かと思います。。。

回答No.2

papayuka

Excel2000で同様の操作をしたつもりですが再現しませんでした。
データ行が4行しかなくても必ず20行選択しているのでしょうか？
普通にデータがある範囲を選択しても同じ結果になりますか？

補足

早速のご回答ありがとうございます。
データ行が4行でも必ず20行選択してます。
データがある範囲だけを選択すれば希望の結果になるのですが。

追記ですが、「あるボタンを押すと、データが集計されそれがコピーされ他シートに張り付けれられる」というマクロを組んでおります。

回答No.1

charisan

ピボットを使ってみてはいかがでしょうか。
この例ですと、「合計金額」以外のすべての項目を「列」におき、「会社」以外の項目で集計を「なし」にすれば例に近くなります。

補足

ピボットを利用したいのはやまやまなのですが、ピボットが非常に苦手で、良く理解できていません。
なんか近い感じになっているような気がします。

さらにお伺いしたいのですが、ウィザードに従って進めますと
マトリックスが作成され、ドラッグを指示されますが
「行のフィールド」：合計金額以外の項目として
「列のフィールド」
「データアイテム」
はどうしたらよろしいでしょうか？
お手数おかけいたします。