こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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


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

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

投稿日時 - 2004-08-08 10:57:44

QNo.954834

困ってます

質問者が選んだベストアンサー

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

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

>集計のための並び替えや、3番のデータのコピーに関しても、その時々によって量が違うため、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

投稿日時 - 2004-08-08 14:18:45

お礼

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

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

投稿日時 - 2004-08-08 15:38:32

ANo.5

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.4

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

投稿日時 - 2004-08-08 12:15:41

補足

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

投稿日時 - 2004-08-08 13:38:51

ANo.3

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

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

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

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

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

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

6.データを全て表示

7.マクロ記録終了

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

投稿日時 - 2004-08-08 12:12:33

ANo.2

マクロ・VBAで出来ますが
関数式で抜きだして見ますのでこれで辛抱できないか考えて見てください。
(1)1条件(質問のコードだけ)
(2)課名で小計を出すのが別途になる
(3)作業列を使う
など制約はありますが。
(例データ)
組織課コード課名税抜金額消費税備考コード
aaa 123   経理3243282xxx1451
aaa 123 経理8227411yyy  4181
aaa125総務 154377zzz  1452
aaa123  経理 9810490nnn  1453
-------
Sheet2に条件の145を入れる
-------
Sheet3の第1行にSheet1の第1行を複写
(関数式)Sheet1
H列を作業列で使います。
H2セルに=COUNTIF($G$2:G2,Sheet2!$A$1)
H5複写
(関数式)Sheet3
A2に=OFFSET(Sheet1!$A$2,MATCH(ROW()-1,Sheet1!$H$2:$H$5,0)-1,COLUMN()-1)
G2までまず横方向へ式を複写。
A2:G2を範囲指定し、G2で+ハンドルを出して3行分複写。
#N/Aなどのエラー値を出さなくする方法はありますが略。
(結果)Sheet3
組織課コード課名税抜金額消費税備考コード
aaa  123   経理8227411yyy   418
aaa  125総務 154377zzz   145
aaa  123    経理  9810490nnn   145

投稿日時 - 2004-08-08 11:48:15

お礼

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

投稿日時 - 2004-08-08 13:36:22

ANo.1

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

投稿日時 - 2004-08-08 11:17:01

補足

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

投稿日時 - 2004-08-08 11:31:20

あなたにオススメの質問