私は、昨年から都内でスナックを経営しています。購入した品物を、エクセルで集計を自動でしたいのですが、うまくいかなくて困っています。どなたか教えてください。
表の名前は、経費台帳です。一部、店名等変えてあります。特定されるといけないと思いまして。
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で質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【エクセル】オートフィルタで...
-
エクセルのマクロで特定の文字...
-
エクセルのピボットテーブルで...
-
ピボットを更新すると数式が入...
-
エクセル、リソース不足エラー...
-
EXCELで0を除いた平均値...
-
エクセル 自動で別シートへ並...
-
エクセル 関数を使った横方向の...
-
ピボットテーブル作成後、集計...
-
ピボットの集計方法「合計」初...
-
アンケートの集計
-
Excelで、空白を表示したい
-
エクセルで文字が白くなる
-
エクセルで文字を含む式に、カ...
-
エクセルで円グラフに引き出し...
-
Excel2017 フィルタ昇順並びがA...
-
【ExcelVBA】全シートのセルの...
-
エクセルで日数計算
-
同一セルに日時があるものを日...
-
エクセルの主軸と第2軸の0を合...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【エクセル】オートフィルタで...
-
エクセルで特定の色の数字だけ...
-
ピボットテーブルでは時間の表...
-
フィルタをしても最下行を常に...
-
VBAで重複する項目を1つにまと...
-
他部署からもらう データで、 ...
-
ピボットを更新すると数式が入...
-
アンケートの集計
-
ピボットの集計方法「合計」初...
-
Excelの集計結果だけをコピー貼...
-
いい機能だけど、毎回めんどく...
-
EXCEL 重複データの集計の仕方
-
エクセル、リソース不足エラー...
-
excelで集計の合計を降順に並べ...
-
エクセルの小計を自動的に色づ...
-
ピボットテーブルで同じデータ...
-
マクロ実行時にエラーが出てし...
-
常にタイトル行と合計行を表示...
-
エクセル 関数を使った横方向の...
-
複数のピボットテーブルを一括...
おすすめ情報