Excel：条件にあったものを集計したい

質問

下記のような表があります。
sheet1
組織　課コード　課名　税抜金額　消費税　備考　コード
aaa 　123 　　　　経理　　3243　　　　282　　xxx　 0145
aaa 　123 　　 　 経理　　8227　　　　 411　　yyy 0418
aaa 　125　　 　　総務 　 1543　　　　　77　　zzz 0145
aaa 　123 　　　　経理 　 9810　　　　 490　　nnn 0145

データが約1000件くらい入力されてます。

条件を別シート(別sheet)に入力し、その条件に合うものを導きだし、集計をさせたものを、sheet3に表示をさせたいのです。
条件…コードが一致するもの

sheet3
sheet2に条件を0145と入力した場合
課コード　課名　税抜金額　消費税　備考
123 　　　　経理　　3243　　　　282　　xxx
123 　　　　経理 　 9810　　　　 490　　nnn
　　　　　　 合計　 13053　　　　512

125　　 　　総務 　 1543　　　　　77　　zzz
　　　　　　合計　　 1543　　　　　77

月により、それぞれの件数は変わってきます。
このようなことは、可能でしょうか？
できれば、マクロを使用し、マクロボタンを押すと集計されるようにしたいです。

よろしくお願いいたします。

回答No.5

web2525 · ベストアンサー

＞実際には、A列～L列まで項目があり、データも月々の量が、変更のため3000列でためして見たのですが、
デバックで終了してしまうところがあるのです。

そんな大きなデータは持ってないんで100行くらいのデーターで試したんですが問題なく実行できたのですが・・・

＞集計のための並び替えや、３番のデータのコピーに関しても、その時々によって量が違うため、1500列にしてみました。それが、いけないのでしょうか？

データのコピーに関してはL列が集計項目ならL1を選択して〔Shift〕+〔Ctrl〕+〔↓〕キーを押して〔Shift〕+〔Ctrl〕+〔←〕キーで選択するとマクロ修正しなくても良いみたいです。
L列が集計項目で無い場合はM列にダミーの集計項目を置いて選択コピー後削除したらよいかと思います。

参考までに記録したマクロを：
Sub Macro1()
'

'

'
Application.CutCopyMode = False
Range("A1:l3000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Sheet2").Range("A1:l2"), Unique:=False
Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(7, 8), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Range("l1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Copy
Sheets("Sheet3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.RemoveSubtotal
ActiveSheet.ShowAllData
Range("A1").Select
End Sub

お礼

無事に仕上げることができました。

本当にありがとうございました。

回答No.4

web2525

補足
=0145はコードの下に入力です
表示が変わっていたスペースだけの入力は無視されるのね ^^;;

補足

実際には、A列～L列まで項目があり、データも月々の量が、変更のため3000列でためして見たのですが、
デバックで終了してしまうところがあるのです。
集計のための並び替えや、３番のデータのコピーに関しても、その時々によって量が違うため、1500列にしてみました。それが、いけないのでしょうか？

回答No.3

web2525

私も言葉足らずだったかな？
まず別シートにフィルターオプション用の条件指定欄を作成し条件を指定します。
組織　課コード　課名　税抜金額　消費税　備考　コード
=0145

1.マクロの記録ON→その後フィルターオプションを実行します。※検索条件範囲は条件指定欄を選択

2.集計機能を使い〔グループの基準〕を"課名"を指定、〔集計方法〕は合計、〔集計フィールド〕は"税抜金額"と"消費税"を指定。

3.データの選択してコピー ※後でマクロの手直しが必要かな？

4.保存用シートに値のみ貼り付け

5.アウトラインのクリア

6.データを全て表示

7.マクロ記録終了

これで出来ると思うけど、3の選択するところのマクロを若干の手直しが必要。

回答No.2

imogasi

マクロ・ＶＢＡで出来ますが
関数式で抜きだして見ますのでこれで辛抱できないか考えて見てください。
（１）１条件（質問のコードだけ）
（２）課名で小計を出すのが別途になる
（３）作業列を使う
など制約はありますが。
（例データ）
組織 課コード 課名 税抜金額 消費税 備考 コード
aaa  123    経理 3243 282 xxx 145 1
aaa  123  経理 8227 411 yyy   418 1
aaa 125 総務  1543 77 zzz   145 2
aaa 123   経理  9810 490 nnn   145 3
-------
Sheet2に条件の145を入れる
-------
Sheet3の第１行にSheet1の第１行を複写
（関数式）Ｓｈｅｅｔ１
Ｈ列を作業列で使います。
Ｈ２セルに=COUNTIF($G$2:G2,Sheet2!$A$1)
Ｈ５複写
（関数式）Ｓｈｅｅｔ３
Ａ２に=OFFSET(Sheet1!$A$2,MATCH(ROW()-1,Sheet1!$H$2:$H$5,0)-1,COLUMN()-1)
Ｇ２までまず横方向へ式を複写。
Ａ２：Ｇ２を範囲指定し、Ｇ２で＋ハンドルを出して３行分複写。
#N/Aなどのエラー値を出さなくする方法はありますが略。
（結果）Ｓｈｅｅｔ３
組織 課コード 課名 税抜金額 消費税 備考 コード
aaa   123    経理 8227 411 yyy    418
aaa   125 総務  1543 77 zzz    145
aaa   123     経理   9810 490 nnn    145

お礼

ありがとうございます。
チャレンジしてみましたがうまくいきませんでした。
たぶん、私のやり方がいけないのですね。

回答No.1

web2525

〔フィルターオプション〕＋〔集計〕で表示された結果を別シートにコピーする動作をマクロで記録できるすれば可能かな？

補足

早速の回答ありがとうございます。
言葉が足りなかったです。
条件の数字は、変わることがあるので、条件を入力したセルを参照できればと思うのですが…。