私は、昨年から都内でスナックを経営しています。購入した品物を、エクセルで集計を自動でしたいのですが、うまくいかなくて困っています。どなたか教えてください。
表の名前は、経費台帳です。一部、店名等変えてあります。特定されるといけないと思いまして。
B列11から20まで例を挙げました。実際は月で300ほどの行になります。
_______B_______C___________D_________E________F_____G________H_______
10____月日___商品コード____商品名_______数量______単価___金額______備考
11___6月1日____20________ビール________20_______400___8000_____ケイ商事
12___6月1日____40________乾物__________10______188___1880_____スーパーS
13___6月2日____84________焼酎A_________6_______500___3000_____Y店
14___6月2日____40________乾物__________5_______190___950______スーパーS
15___6月2日____120_______グラス________12______100___1200______河童橋
16___6月6日____20________ビール________20______400____8000_____ケイ商事
17___6月6日____132_______領収書________2_______85____170______スーパーS
18___6月6日____20________ビール________50______355____17750____業務スーパー
19___6月9日____132_______領収書________2_______108____216______100均
20___6月9日____84________焼酎A________5________475____2375____スーパーS
これを同じシート内のX列に、Cの商品コードを元に集計したいです。
昇順で、合計の行をゴチック体で、C(Y)の商品コードが変わるときに挿入したいのです。
ゴチック体になるのは、合計、数量、金額です。
_____X________Y__________Z__________AA______AB______AC______AD
10____月日___商品コード____商品名_______数量______単価___金額______備考
11___6月1日____20________ビール________20_______400___8000_____ケイ商事
12___6月6日____20________ビール________20______400____8000_____ケイ商事
13___6月6日____20________ビール________50______355____17750____業務スーパー
14_______________________合計_________90_____________33750____________ 15___6月1日____40________乾物__________10______188___1880_____スーパーS
16___6月2日____40________乾物__________5_______190___950______スーパーS 17______________________合計__________15____________2830_____________
18___6月2日____84________焼酎A_________6_______500___3000_____Y店
19___6月9日____84________焼酎A________5________475___2375_____スーパーS
20______________________合計_________11_____________5375_____________
21___6月2日____120_______グラス________12______100___1200______河童橋
22______________________合計__________12____________1200_____________
23___6月6日____132_______領収書________2_______85____170______スーパーS
24___6月9日____132_______領収書________2_______108____216______100均
25______________________合計_________4______________386____________
すいません。よろしくご指導くださいませ。
No.3ベストアンサー
- 回答日時:
こんばんは!
データは10行目が項目行でデータは11行目以降にあるのですね。
X~AD列の10行目も項目名が入っているとします。
一例です。
Sub Sample1()
Dim myDic As Object
Dim i As Long, lastRow As Long
Dim myCode As String
Dim FoundCell As Range, FirstCell As Range
Dim myKey
Application.ScreenUpdating = False
Set myDic = CreateObject("Scripting.Dictionary")
lastRow = Cells(Rows.Count, "Z").End(xlUp).Row
If lastRow > 10 Then
Range(Cells(11, "X"), Cells(lastRow, "AD")).ClearContents
End If
For i = 11 To Cells(Rows.Count, "C").End(xlUp).Row
myCode = Cells(i, "C")
If Not myDic.exists(myCode) Then
myDic.Add myCode, ""
End If
Next i
myKey = myDic.keys
Range("AE:AE").Insert
For i = 0 To UBound(myKey)
Cells(i + 1, "AE") = myKey(i)
Next i
Range("AE:AE").Sort key1:=Range("AE1"), order1:=xlAscending, Header:=xlNo
For i = 1 To Cells(Rows.Count, "AE").End(xlUp).Row
Set FoundCell = Range("C:C").Find(what:=Cells(i, "AE"), LookIn:=xlValues, lookat:=xlWhole)
Set FirstCell = FoundCell
GoTo 処理
Do
Set FoundCell = Range("C:C").FindNext(after:=FoundCell)
If FoundCell.Address = FirstCell.Address Then Exit Do
GoTo 処理
処理:
Cells(Rows.Count, "Z").End(xlUp).Offset(1, -2).Resize(, 7).Value = FoundCell.Offset(, -1).Resize(, 7).Value
Loop
With Cells(Rows.Count, "Z").End(xlUp).Offset(1)
.Value = "合計"
.Offset(, 1) = WorksheetFunction.SumIf(Range("C:C"), Cells(i, "AE"), Range("E:E"))
.Offset(, 3) = WorksheetFunction.SumIf(Range("C:C"), Cells(i, "AE"), Range("G:G"))
End With
Next i
Range("AE:AE").Delete
Set myDic = Nothing
Application.ScreenUpdating = True
MsgBox "完了"
End Sub
※ じっくり考えればもっと簡単になるかもしれませんが
まずはこの程度で・・・m(_ _)m
ありがとうございます。できました。
締め切りがきたら、ベストアンサーにさせていただきます。
で、最後にひとつだけ教えてください。調べてだいたい意味は理解できたのですが、下のところがよくわかりません。
説明していただけると助かります。よろしくお願いいたします。
Cells(Rows.Count, "Z").End(xlUp).Offset(1, -2).Resize(, 7).Value = FoundCell.Offset(, -1).Resize(, 7).Value
Loop
No.5
- 回答日時:
お薦めしたいのはテーブル化とピボットテーブルです
①既存の表(経費台帳)をテーブル化
・対象の表を選択→挿入タブ→テーブル
・テーブルツール→デザインタブ→デーブル名を『経費台帳』に変更
②ピボットテーブルの作成
・テーブルツール→デザインタブ→ピボットテーブルで集計
・テーブル/範囲がテーブル名(経費台帳)になっていることを確認
・作成場所を既存のワークシートの場所に該当シートのX10を指定してOK
③ピボットテーブルのフォーマット編集
ⅰ.項目追加
・ピボットテーブルのフィールドで行に『商品コード』『月日』『商品名』『備考』を追加
・ピボットテーブルのフィールドで値に『数量』『金額』を追加
ⅱ.フィールド設定
・商品コード:小計とフィルター→指定[合計]、レイアウトと印刷
→レイアウト[アイテムのラベルを表形式で表示する]
・月日 :小計とフィルター→指定[なし]
→レイアウト[アイテムのラベルを表形式で表示する]
・商品名 :小計とフィルター→指定[なし]
→レイアウト[アイテムのラベルを表形式で表示する]
といった感じで完成です
ピボットテーブルの構成による都合上項目の並び順が制限されますが、ご要望の項目コードごとの集計が日付順に表示され、且つ総合計の集計まで自動化されています。
先の経費台帳は行追加や削除があっても問題ありません、ピボットテーブルの方で右クリック→更新とすると再集計を行います
ただし!ピボットテーブルに重なる行の削除が出来ません
本来集計シートは別シートにする方が良いと考えます
また、入力のシートに関しても入力規則でリスト選択したり、コードを入力すると名称が自動表示されたり(VLOOKUPやINDEX)金額欄は自動計算したりなどなど、少し手を加えると日常業務がもっと簡素化しますので頑張ってください♪
No.4
- 回答日時:
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBA 連続する名前ごとに集計 3 2022/05/21 18:24
- 高校 日商簿記3級の勉強中なのですが 精算表が完成せず困っています。 こちらの問題の回答を教えていただきた 2 2023/03/02 09:07
- ヤフオク! 関税 詐欺でしょうか?ヤフオク 3 2023/06/25 11:22
- お酒・アルコール このビールのサブスク あまりメリットがない気がするのですが。 3 2022/09/01 18:05
- Excel(エクセル) グループの最後の行に書式、計算式なども同じ行を追加するマクロを教えてもらえませんか。 7 2022/05/18 10:13
- 先物取引 【LNG液化天然ガスのBTU(英国熱量単位)の100万BTUは何リットルですか?】 もしかして、水よ 1 2022/04/04 19:23
- 格安スマホ・SIMフリースマホ Reno7 A(OPPO)人気がありますが何がそんなにいいのですか 3 2022/07/05 23:23
- Excel(エクセル) ②Excel 簡単にシートコピーしたら前日の残高と日付を変更させたい→マクロの記録でエラーが出ます 8 2022/07/16 20:40
- ヒーター・こたつ・ホットカーペット 教えて!gooに10分おきに質問をしていれば給料がもらえる仕事があれば、やりたいですか? 毎週金土日 4 2023/07/04 21:17
- 減税・節税 ふるさと納税について 5 2023/02/13 03:48
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで特定の色の数字だけ...
-
VBAで重複する項目を1つにまと...
-
【エクセル】オートフィルタで...
-
フィルタをしても最下行を常に...
-
ピボットテーブルでは時間の表...
-
複数のピボットテーブルを一括...
-
ピボットテーブルのページエリ...
-
ピボットテーブルで同じデータ...
-
アンケートの集計
-
excelで集計の合計を降順に並べ...
-
ピボットを更新すると数式が入...
-
EXCEL 重複データの集計の仕方
-
ピボットの集計方法「合計」初...
-
SUBTOTALが変わってしまいます。
-
エクセルのピボットテーブルで...
-
Excelで毎日の売上を入力すると...
-
ピボットテーブル 合計の表示
-
EXCELで0を除いた平均値...
-
エクセルのプルダウン リスト...
-
エクセルで文字が白くなる
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【エクセル】オートフィルタで...
-
エクセルで特定の色の数字だけ...
-
ピボットテーブルでは時間の表...
-
フィルタをしても最下行を常に...
-
VBAで重複する項目を1つにまと...
-
ピボットの集計方法「合計」初...
-
EXCEL 重複データの集計の仕方
-
アンケートの集計
-
ピボットを更新すると数式が入...
-
excelで集計の合計を降順に並べ...
-
複数のピボットテーブルを一括...
-
エクセルの小計を自動的に色づ...
-
いい機能だけど、毎回めんどく...
-
エクセル 関数を使った横方向の...
-
常にタイトル行と合計行を表示...
-
エクセル、リソース不足エラー...
-
Excelの集計結果だけをコピー貼...
-
EXCELで増減率を計算したいです
-
ピボットテーブルで同じデータ...
-
ピボットテーブルのページエリ...
おすすめ情報