お世話になります。VBA初心者です。
下記のような表があった場合、請求書番号が同じものをVBAで1行に集計するにはどうしたらよろしいのでしょうか?
請求書No.|顧客名|摘要|金額
111111 |鈴木 | A |100
111111 |鈴木 | S |160
222222 |佐藤 | F |500
555555 |山田 | A |150
555555 |山田 | D |200
888888 |鈴木 | S |160
↓下記のように集計
請求書No.|顧客名|摘要|金額
111111 |鈴木 | A |260
222222 |佐藤 | F |500
555555 |山田 | A |350
888888 |鈴木 | S |160
摘要は各請求書番号の最初の行を使います。重複は2行とは限りません。また、最終的に何枚の請求書があるのかも計算させたいのです。ただしこれはどこかに関数"=counta()"を使えばVBAでなくても出来るのですが。
よろしくお願いいたします。
No.5ベストアンサー
- 回答日時:
#4の回答者です。
>請求書No.はA列には無くまだ確定では何いのですがH列以降になる予定です。
それは、
Set myData = sh1.Range("A1").CurrentRegion
ここで決めていきます。CurrentRegion ですと、地続きの範囲ということになって、A列から含まれてしまいますので、
Set myData = sh1.Range("H1", sh1.Range("H65536").End(xlUp).Offset( ,4))
というスタイルになります。 Offset( ,4) というのは、H列を含めて右へ4列 という範囲を示します。
sh1.Range("H65536").End(xlUp) というのは、H列の一番下のデータという意味です。
しかし、統合することは可能でも、出力のレイアウト自体が決まっていかないと、今のコードから、そんなに簡単に直せるとは言いにくいですね。
>金額はH列より後の列に入ります(予定ではI列)。
H列を基準に統合すると、現在のマクロですと、その隣の列に、集計の金額が出てきてしまいます。
また、数式は、以下の部分を直せばよいのですが……。
> sh2.Range(.Cells(2, 2), .Cells(.Rows.Count, 3)).FormulaLocal = _
> "=VLOOKUP($A2," & sh1.Name & "!" & myData.Address(1, 1) & ",COLUMN(B1),0)"
> .Value = .Value
=VLOOKUP($A2,Sheet1!$H$1:$K$7,COLUMN(C1),0)
例えば、こんな風にするには、
sh2.Range(.Cells(2, 3), .Cells(.Rows.Count, 4)).FormulaLocal = _
"=VLOOKUP($A2," & sh1.Name & "!" & myData.Address(1, 1) & ",COLUMN(C1),0)"
.Value = .Value
となります。
それを自由に出力列を変更できるようにするには、全面的にマクロコードを変更しなくてはならないような気がします。
今の段階では、例えば、こういうスタイルなら、
請求書No. 金額 顧客名 摘要
こういうスタイルで出てきてしまうことになってしまいます。
いつも本当にありがとうございます。
細かい丁寧な説明なので本当に助かっております。
結局、請求書番号をA列に持ってきて、集計をした後に前回教わったマクロを使い、さらに新しいシートに並べ替えることにしました。
今後ともよろしくお願いいたします。
No.4
- 回答日時:
こんばんは。
例えば、集計をSheet2 に出すように作ってみました。
これは、フィルタオプションと統合を組み合わせれば簡単に出来ます。
他の方法もありますが、記録マクロの延長の方法です。ただ、空白部分の補完の方法などは、多少の技術は必要になってきます。
'標準モジュール
Sub Test1()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim myData As Range
Dim tmpData As Range
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Set myData = sh1.Range("A1").CurrentRegion
'出力する場所を削除しておく
sh2.Range("A1").CurrentRegion.ClearContents
'フィルタオプションで、1列目のユニーク番号を取り出す
myData.Columns(1).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=sh1.Range("AA1"), _
Unique:=True
'出力場所
Set tmpData = sh1.Range("AA1").CurrentRegion
'統合
sh2.Range("A1").Consolidate _
Array(sh1.Name & "!" & myData.Address(1, 1, xlR1C1), _
sh1.Name & "!" & tmpData.Address(1, 1, xlR1C1)), _
xlSum, False, True, False
'VLOOKUPで、データの補完
myData.Rows(1).Copy sh2.Range("A1")
With sh2.Range("A2").CurrentRegion
sh2.Range(.Cells(2, 2), .Cells(.Rows.Count, 3)).FormulaLocal = _
"=VLOOKUP($A2," & sh1.Name & "!" & myData.Address(1, 1) & ",COLUMN(B1),0)"
.Value = .Value
'件数の出力
.Cells(1, .Columns.Count + 1).Value = "=""件数:""&COUNT(" & .Columns(4).Address(0, 0) & ")"
End With
tmpData.ClearContents
Set myData = Nothing
Set tmpData = Nothing
Set sh1 = Nothing
Set sh2 = Nothing
End Sub
この回答への補足
実は例としてあげたものと実際のものとでは少し違いがありまして、請求書No.はA列には無くまだ確定では何いのですがH列以降になる予定です。A~Gまでにはその他の数値・記述(日付やメモ書き)が入り、集計したい金額はH列より後の列に入ります(予定ではI列)。
このような場合はどこをどう変えればよろしいのでしょうか?
毎回すみませんが、よろしくお願いいたします。
No.3
- 回答日時:
VBAで
(1)WorkSheetFunction.SUMIFを使う
(2)総なめで11111(など)を探して足しこむ
(3)請求書NOでソートして、並びがブレークするまで足しこむ
(4)Find、FindNextで111111(など)を探して足しこむ。
人間が電卓で目で見て足す場合はこれでしょう。
などあるが(1)、(2)、(4)は請求書Noのユニークな(重複の無い)一覧がシート上に無いと、出てきた都度集計していると複数回同じこと(111111を集計)をしてしまう。
結局(2)が優れていると思う。ただデータ順序を崩すたくない場合は、作業シートにコピーしてそちらでやるか、初めに連番を振っておき、当初の並ぶ順にソートで戻す必要があるが。
(3)のロジックは、前行の請求書番号を変数に記録し、常に前行と現在行を比較し、
・変わらない同じとき、合計変数に足しこみーー>次行へ
・変わったとき、前の行のA-C列を書き出しセルに代入(A)
合計の書き出し(B)
書き出し行を+1(下行を指す)
合計に現在行の計数足しこみ(0にして現在行の計数足しこみ)
前行変数に現在行データを書く
-->次行へ
終わり行の後だけ、特別に(A)(B)を行う。
上記の意味が読んでわかったらすごいが。
早速のご回答に感謝いたします。
おっしゃるとおり私にはもう少し勉強が必要なようです。
現在の私には難しすぎますので、知識がつきましたら再度読み返させていただきます。
ありがとうございました。
No.2
- 回答日時:
「ツール」→「統合」機能を使う方法もあります。
例)元データがA列~D列に記述されていると過程して、VBAで書くとこんな感じになります。
Range("F1").Consolidate Sources:="C1:C4", Function:=xlSum, LeftColumn:=True
の1行を実行しただけで、請求番号ごとに集計された結果が、F列~I列
に出力されます。
※顧客名・摘要列は無視されるので、G~H列は空白列になります。
あとは、CountIfで個数・VLookupで顧客名引っ張ってくればおおよそ
完成ですね。マクロでセル内に関数を書くのでもいいですし。
まず、びっくりしております。
まさにやりたいことが、こんなにシンプルに出来るとは思っても降りませんでした。サンプルデータで試してみましたが、本当にびっくりしました。
ただし、コラムが上記のサンプルと違い必要なデータがA列から4つ綺麗に並んでおりません。また、これ以外のマクロも色々と組んでおり、すこしフォーマットも考え直さないといけません。
現状ではこのまま使わせていただくことは出来ないのですが、このシンプルさはとても魅力に思っております。私はド素人なのでゴチャゴチャとくっつけては切捨ての繰り返しのためとても複雑なものを作りがちです。とっても参考になりました。本当にありがとうございました。
No.1
- 回答日時:
思われていることと違っていましたらすみませんが参考までに試してみてください。
Altキー押しながらF8キーを押します。
↓
マクロのダイアログが表示されたらマクロ名に自由に名前を入力してください。(例:集計)
↓
名前を入力しましたら、「作成」をクリック
↓
Microsoft Visual Basicの画面が開きますのでSub 集計()の下に次のコードをコピーして貼り付けてください。
Dim シート1カウント, 集計シートカウント, シート1件数, 金額, スイッチ As Long
Dim シート名, 請求NO, 顧客名, 摘要 As String
シート名 = ActiveSheet.Name
スイッチ = 0
On Error GoTo skip1
Sheets("集計シート").Select
スイッチ = 1
skip1:
If スイッチ = 0 Then '集計シートが無かったら作成する
Sheets.Add
ActiveWorkbook.ActiveSheet.Name = "集計シート"
End If
Sheets("集計シート").Cells.ClearContents
'集計
Sheets(シート名).Select
シート1件数 = Application.WorksheetFunction.CountA(Worksheets(シート名).Range("A1:A65536"))
シート1カウント = 1
集計シートカウント = 1
請求NO = Sheets(シート名).Cells(シート1カウント, 1)
顧客名 = Sheets(シート名).Cells(シート1カウント, 2)
摘要 = Sheets(シート名).Cells(シート1カウント, 3)
金額 = Sheets(シート名).Cells(シート1カウント, 4)
シート1カウント = シート1カウント + 1
Do
If 請求NO = Sheets(シート名).Cells(シート1カウント, 1) Then
金額 = 金額 + Sheets(シート名).Cells(シート1カウント, 4)
Else
Sheets("集計シート").Cells(集計シートカウント, 1) = 請求NO
Sheets("集計シート").Cells(集計シートカウント, 2) = 顧客名
Sheets("集計シート").Cells(集計シートカウント, 3) = 摘要
Sheets("集計シート").Cells(集計シートカウント, 4) = 金額
集計シートカウント = 集計シートカウント + 1
請求NO = Sheets(シート名).Cells(シート1カウント, 1)
顧客名 = Sheets(シート名).Cells(シート1カウント, 2)
摘要 = Sheets(シート名).Cells(シート1カウント, 3)
金額 = Sheets(シート名).Cells(シート1カウント, 4)
End If
シート1カウント = シート1カウント + 1
Loop Until シート1カウント > シート1件数
Sheets("集計シート").Cells(集計シートカウント, 1) = 請求NO
Sheets("集計シート").Cells(集計シートカウント, 2) = 顧客名
Sheets("集計シート").Cells(集計シートカウント, 3) = 摘要
Sheets("集計シート").Cells(集計シートカウント, 4) = 金額
集計シートカウント = 集計シートカウント + 1
Sheets("集計シート").Cells(集計シートカウント, 2) = "請求書枚数 = " & 集計シートカウント - 2 & "枚"
Sheets("集計シート").Select
MsgBox "集計しました。 請求書枚数 = " & 集計シートカウント - 2 & "枚です。"
'****コピー貼り付けはここまで ****
Microsoft Visual Basicの画面を×で閉じます。
使い方は、Altキー押しながらF8キーを押します。
マクロのダイアログが表示されるので先ほど名前を付けたマクロを選択して「実行」をクリック。
(選択されている状態でしたら、そのままEnterキーで実行されます。)
集計シートに結果が集計されていると思います。
>思われていることと違っていましたら…
まさにこれがやりたかったことです。ありがとうございました。
色々な方法があるのですね、丁寧な補足説明付ですので色々とアレンジが出来そうです。
また、それぞれのマクロも他で流用できそうなものがございますので、ぜひ使わせていただきます。
本当にありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- PHP MySql PHP 2つのテーブルをJOINで結合 user_idで抽出 1 2023/01/03 14:04
- Excel(エクセル) (教えて下さい)ExcelのSUMIF又はSUMIFSを使って合計値の算出 4 2023/07/24 11:23
- Excel(エクセル) EXCEL 関数を教えてください。(A列の同じ値が複数ある場合vlookupで出来ますか) 4 2022/12/07 20:54
- Visual Basic(VBA) VBAでPDFのアクティブページ番号取得 1 2023/05/25 12:41
- MySQL 【MySQL】本当に困っているので、助けてください。よろしくお願いします。 3 2023/06/03 14:24
- 仕事術・業務効率化 使用中のソフトで、インボイス制度に対応する為の作業料が30万以上。普通? 4 2023/06/15 21:26
- 訴訟・裁判 管理会社の光ファイバー対応の虚偽記載。損害賠償金額の目安は? 4 2022/05/29 13:24
- Visual Basic(VBA) VBA 連続する名前ごとに集計 3 2022/05/21 18:24
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教える店舗&オフィスのセキュリティ対策術
中・小規模の店舗やオフィスのセキュリティセキュリティ対策について、プロにどう対策すべきか 何を注意すべきかを教えていただきました!
-
VBAで重複する項目を1つにまとめて金額を合計したい
Excel(エクセル)
-
EXCEL 複数行のデータを1行にまとめる方法
Excel(エクセル)
-
VBAで重複データを合算したい
Excel(エクセル)
-
-
4
エクセルVBAのIF文の否定の複数条件
Excel(エクセル)
-
5
VBAで重複するデータがあれば1個だけ残して他の重複セルを"(空白)にしたいのですが
Excel(エクセル)
-
6
重複行を削除して数値を合算したい(合算列が多い)
Excel(エクセル)
-
7
重複するIDのデータを1行にまとめるvbaのコード
Access(アクセス)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Access レポート印刷するときに...
-
アクセスクエリの計算
-
エクセルのデータをアクセスに...
-
【至急・画像あり】建物or住所...
-
Accessのリンクテーブルのパス...
-
Accessのクエリで、replace関数...
-
日付のテキストボックスに(例...
-
Accessのスプレッドシートエク...
-
Access VBA を利用して、フォル...
-
Microsoft Accessをクレジット...
-
Accessのデータ型の日付/時刻型...
-
accessの代わりになるもの
-
ms access 2013で、チェックボ...
-
マイクロソフト アクセス2021の...
-
エクセルのマクロについて教え...
-
AccessVBAで任意の複数リンクテ...
-
Access Error3061 パラメータが...
-
Access VBA [リモートサーバー...
-
Accessのフォーム上のテキスト...
-
accessでlaccdbファイルが削除...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Access レポート印刷するときに...
-
Access Error3061 パラメータが...
-
Microsoft365にAccessってあり...
-
Accessのクエリで、replace関数...
-
Accessのリンクテーブルのパス...
-
Access VBA [リモートサーバー...
-
ACCESS VBA でのエラー解決の根...
-
accessデータを指定したExcel、...
-
Accessのスプレッドシートエク...
-
CSVファイルの「0落ち」にVBA
-
【Access】Dcount関数の複数条...
-
Accessのフォーム上のテキスト...
-
Access VBA を利用して、フォル...
-
実行時エラー3131 FROM 句の構...
-
Vba Userformを前面に出すについて
-
Accessでフォームに自動入力し...
-
Accessレポートのチェックボッ...
-
Accessのテキストボックスの入...
-
Access 複数条件検索の設定が上...
-
accessのフォームに設置したボ...
おすすめ情報