Access2000をつかって飲食店の売上計算

質問

Access2000をつかって飲食店の売上計算をしたいと思っています。
営業日　、商品ID　、商品名　、単価　、売上個数　を日々の売上伝票を見ながら入力しようと思います。
その際に商品IDと売上個数（伝票一枚ごとの）のみを入力して最終的に
一日の売上品目別の売上総数と総売上がエクセルに表示できれればいいのですがどうすればよいでしょうか？
現在、エクセルで縦軸に商品名、横軸に個数を配置した表を使っています。
どこかに飲食店用の売上管理データーベースのFree素材はないでしょうか？
やはり自分で作ったほうがよいのでしょうか？
制作方法などご指導いただければ幸いです。

回答No.9

kazuhiko5681 · ベストアンサー

こんばんわ。
１日の集計表と年間集計表の構成についてですが、教えていただきたいことがあります。

まず、商品コードと単価を１日の集計表に表示させる必要がどうしてもあるのでしょうか。入力の時や計算の時に必要だから表示させるのでしたら、シート１に表示させてありますのでこの表には表示させる必要がないかと思います。シート１に表示された値を使って操作することができます。

次に、あなた様は行方向に商品コードと商品名を表示させ、列方向に日付を表示させるような表構成をお考えになっておられるようですが、これを行方向に日付・列方向に商品名を表示させるように変更することは不可能でしょうか。
と言いますのは、一枚のシートに１年分のデータが入力されているのが一番簡単にマクロを書くことができます。エクセルの仕様で列方向は２５６列（エクセル２０００で)しかないので、列方向に日付けを表示させると１年分のデータが１枚のシートに表示できません。
月ごとにブックを作るという手もありますが、今度は年間集計表を作る上でマクロが非常に煩雑になってしまい、私はこの方法は適当ではないと思います。
また、月ごとにシートで管理するという方法もありますが、この場合は１つのブックに１４枚のシート(入力用のシート・年間集計用のシート・月別の日計表)が必要になってしまいます。

あなた様のおっしゃられているアクセスでこのシステムを構築すれば、このような問題は発生しないのですが、アクセスは全く使ったことがありませんので、システム構築のお手伝いができません。

お手数をおかけいたしますが、あなた様のお考えをお聞かせ下さい。

お礼

kazuhiko5681様

　いろいろと御丁寧に御指導賜りありがとうございました。
もっと勉強してがんばってみようと思います。

　本当にありがとうございました。お体にご自愛なさって、
ご活躍くださいませ。

　　　ya-ya-ya-ya

補足

お返事が送れて申し訳ありません。

一日の営業が終わってから集計をしますので出来るだけ入力がシンプルなものがいいのではないかと思っております。日付入力→入力フォームによる商品コードと個数（伝票によっては同一商品の入力もあります。）→商品毎の個数集計。という感じを考えておりました。

列方向と行方向の変更しなければならないようですね。

今まで、私が使用していた計算シートは商品数が９１件あるので横スクロールを避けるように行に商品名、列に“正”の字を書き連ねるようにして最終的にその商品の合計が出来るようにしたエクセルのシートを使用しております。31列分のセルに全て“０”が入力してあり売上伝票を見ながら該当の商品欄の“０”を書き換える形で個数を入力しております。一日の集計は商品別の合計をコピーして一旦メモ帳にペーストしてそれをまたコピーして、同形式の新たなシートの該当日にペーストして３１日間データを埋めて一か月分とし、同形式の（１２ヶ月分の列の）シートに商品別の年間集計を出しております。　印刷もしやすかったのでこの様にしておりました。

　No.8でご指摘いただきました様に、既存データがございますのでＧ～Ｉ列にコピーして使用しましたところおっしゃっていることが良く分りました。
ありがとうございました。

　またマクロを保存するにはどうすれば良いのでしょうか？
日計表と月間集計表とあわせて御指導いただければ幸いです。

回答No.8

kazuhiko5681

こんにちは。
＞シート１にあらかじめ商品コード、商品名、単価、リスト入力（ユーザー＞フォームによる商品コードと個数の入力）
＞シート２に商品コード、個数、一日の集計表示（A列に商品コード、Ｂ列に＞商品名、Ｃ列に単価、Ｄ列に個数合計、Ｄ列のみ集計されたものが表示）
＞シート３に月別の年間集計表示という形に出来ないでしょうか？
できます。私は、まさにこのことを商品ＩＤと個数を入力するだけで実現できるようなコードをあなた様にアドバイスしようと考えています。

また、入力をユーザーフォームからしたいと思われているようですが、ユーザーフォームを使用するとユーザーフォームを使用するとマクロのコードが複雑となるため、なるべく簡潔なコードで動作させるためにシート１のＡ列～Ｅ列を利用してユーザーフォームで入力した時と同じことを実現させるように考えました。もし、ユーザーフォームから入力したいのであれば、お知らせ下さい。そのような仕様にすることも可能です。

＃６でも書きましたが、あなた様が考えておられる商品ＩＤ・個数を入力するだけでシート２にA列に商品コード・Ｂ列に商品名・Ｃ列に単価・Ｄ列に個数合計を表示させるためには、シート１のＧ列～Ｉ列にあらかじめ商品ＩＤ・商品名・単価が入力されていなければなりません。これが、あなた様が言われている既存のデータになります。
このことから、あなた様が考えておられる商品ＩＤ・個数を入力するだけでシート２にA列に商品コード・Ｂ列に商品名・Ｃ列に単価・Ｄ列に個数合計を表示させるためには、Ｇ列に商品ＩＤ・Ｈ列に商品名・Ｉ列に単価を９１品目すべて入力する作業が必要になってきます。私は、既存のデータが入力されていないことを前提に考えておりましたので、この既存データの作成を簡略化するために、１回だけＡ列からＥ列のすべての項目を手入力する必要があると申し上げているのです。

もしこの既存のデータがどこかにもう入力されているのであれば、Ｇ列に商品コード・Ｈ列に商品名・Ｉ列に単価をコピーして下さい。Ａ列に商品コード・Ｄ列に個数を入力するだけであなた様の考えられている動作を実現することができます。

ご理解のほどよろしくお願い申し上げます。

回答No.7

kazuhiko5681

こんにちは。
＞シート１にあらかじめ商品コード、商品名、単価、リスト入力（ユーザー＞フォームによる商品コードと個数の入力）
＞シート２に商品コード、個数、一日の集計表示（A列に商品コード、Ｂ列に＞商品名、Ｃ列に単価、Ｄ列に個数合計、Ｄ列のみ集計されたものが表示）
＞シート３に月別の年間集計表示という形に出来ないでしょうか？
できます。私は、まさにこのことをマクロで実現しようとしているのです。

回答No.6

kazuhiko5681

こんにちは。
あなた様は、商品ＩＤとその個数を打ち込んだだけで自動でその日の商品別の売上個数を計算させたいと考えておられるようですね。この方法を実現するためには、専門的なプログラムを組まなければ実現することはできません。アクセスやエクセルで実現させるためには、どうしてもＡ列からＥ列のデータが必要となるのです。

入力の際に、商品ＩＤとその個数を入力しただけでＡ列からＥ列の表を作り上げるためには、Ｇ列～Ｉ列にデータが入力されていることが必要になります。

つまり、商品ＩＤとその個数を入力しただけでＡ列からＥ列の表が出来上がる仕組みは、次のようになっているのです。私の作った表で説明いたします。
　１.Ａ３に0001と打ち込むとＧ列に入力されている0001を探しにいく。
　２.0001があった場合その行のＨ列の値をＢ３に表示する。
　３.0001があった場合その行のＩ列の値をＣ３に表示する。
　４.0001がなかった場合は何も起こらない。

これで、Ａ列～Ｅ列・Ｇ列～Ｉ列にデータの必要な理由がお解りいただけたと思います。

私のサンプルマクロは、Ｇ列～Ｉ列にデータの入っていた時は、商品ＩＤを入力すると自動でＢ列とＣ列の値が表示され、売上個数を入力すると売上金額が自動で計算されＥ列に表示されるようになっています。ただ、Ａ列に入力した商品ＩＤがＧ列になかった時は、１回だけＡ列からＥ列のすべての項目を手入力する必要があります。

また、Ａ列からＥ列の表が完成した時点でシート２を開けば、自動でその日の商品別の売上個数と売上金額がシート２に表示されるようになっています。もし、その日の商品別の売上総数がわかっているときは、話が変わってきますので、お知らせ下さい。

Ａ列～Ｅ列・Ｇ列からＩ列のデータは絶対に必要ですが、表の構成につきましては、自由に作ることができますのでご希望の構成がありましたらお知らせ下さい。

お手数をおかけいたします。よろしくお願いいたします。

補足

おはようございます。
ご指摘の「１回だけＡ列からＥ列のすべての項目を手入力、、、」につきまして
シート１にあらかじめ商品コード、商品名、単価、リスト入力（ユーザーフォームによる商品コードと個数の入力）
シート２に商品コード、個数、一日の集計表示（A列に商品コード、Ｂ列に商品名、Ｃ列に単価、Ｄ列に個数合計、Ｄ列のみ集計されたものが表示）
シート３に月別の年間集計表示という形に出来ないでしょうか？
エクセルの本に「社員の出勤表」というのがありユーザーフォームの利用で既存のデータを呼び出して表示させる例が紹介されていました。
これを応用できないでしょうか？

専門的なプログラムもがんばって勉強しようと思います。
基本的な製作の流れをご指導いただけないでしょうか？
ご無理を言って申し訳ありませんが、
ご指導の程宜しくお願いいたします。

回答No.5

kazuhiko5681

操作手順を書かないままＯＫボタンを押してしまいました。操作手順をお知らせします。

１.Ａ３に商品コードを入力する。Ｇ２に商品コードが表示される。
２.Ｂ３に商品名を入力する。Ｈ２に商品名が表示される。
３.Ｃ３に単価を入力する。Ｉ２に単価が表示される。
４.Ｄ３に売上個数を入力するとＥ３に売上金額が自動で表示されカーソルがＡ４に移る。
５.Ａ４にＡ３で入力した商品コードと同じ番号を入力する。Ｂ４に商品コードに対応する商品名が自動で表示され、Ｃ４に商品コードに対応する単価が自動で表示されてカーソルがＤ４に移る。
６.Ｄ４に売上個数を入力するとＥ４に売上金額が自動で表示され、カーソルがＡ５に移る。

つまり、Ｇ・Ｈ・Ｉ列へ表引元のデータを入力するように作ってあります。
初めて使い始める時と、新規メニューを追加する場合のことを考慮して商品コードがＧ列になかった時は１～４、あった場合は５～６の操作方法で入力ができるようにしました。

後は、先程も書きましたがシート２を開くと１日の商品別の売上個数と金額がシート２に表示されるようになっています。

補足

御丁寧なご指導心より感謝しております。
ご指示の通り製作し稼動させましたところ
日々の業務では入力項目が多すぎてかなり難しいかと思いました。

IDや商品名、単価は記録されていて、入力フォームから個々の伝票の項目
（IDと商品名は入力担当者が記憶しているとして）のIDと個数のみを入力すると統一されたIDの商品が合計され表1.に合計個数のみ格納。

（表1.はＩ1に表題「日計表」、Ａ2にＩＤ、Ｂ2に商品名、Ｃ2～ＡＧに日付として１日～３１日としています。）
飲食店ですので日々の商品動向が知りたいのでこの様な表を使っています。
これに単価と売上の欄を付けたいと思っています。

また、表１で集計された商品別の個数は、表2「月計表」としてＡ2にＩＤ、Ｂ2に商品名、Ｃ2～に月の１２ヶ月として作成したものに整理したいと思っています。これに単価をかけると個別の商品の売上が算出され
その総合計が累積総売上となるようにしたいと思っています。

やはりアクセスの方がいいのでしょうか？
kazuhiko5681さんのご指導に対応できるように図書館でエクセルとアクセスの本を借りて勉強しておりますので、引き続きのご指導をいただければ幸いです。

回答No.4

kazuhiko5681

こんばんわ。遅くなりました。まだ完成品ではありませんが、下記のように操作して動作を確認し、ご意見をお聞かせ下さい。

１.用意したブックを立ち上げ、ＡＬＴ＋Ｆ１１キーを押してＶＢＥ画面を開く。
２.画面左上にあるVBEProjectと書いてある下のSheet1をダブルクリックし、開いたコードエディター(右側の白い部分)に下記のコードをコピー・ペーストする。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRow As Long
Dim myRange As Range
Dim myCell As String

Application.EnableEvents = False
myRow = Target.Row
If Target.Address = "$B$1" Then Exit Sub
If Target.Address = Range("A" & myRow).Address _
Or Target.Address = Range("B" & myRow).Address _
Or Target.Address = Range("C" & myRow).Address Then
If Target.Value <> "" Then
myCell = Cells(Rows.Count, 7).End(xlUp).Address
Set myRange = Range("G1:" & myCell).Find(Target.Value, lookat:=xlWhole)

If myRange Is Nothing Then
If Target.Address = Range("A" & myRow).Address Then
Range(myCell).Offset(1, 0).Value = Target.Value
ElseIf Target.Address = Range("B" & myRow).Address Then
Range(myCell).Offset(0, 1).Value = Target.Value
myCell = ThisWorkbook.Worksheets(2).Cells(1, Columns.Count).End(xlToLeft).Address
If ThisWorkbook.Worksheets(2).Range(myCell).Value = "" Then
With ThisWorkbook.Worksheets(2).Range("A1:A2")
.HorizontalAlignment = xlDistributed
.VerticalAlignment = xlCenter
.AddIndent = True
.MergeCells = True
End With
ThisWorkbook.Worksheets(2).Range(myCell).Value = "日付"

With ThisWorkbook.Worksheets(2).Range("B1:B2")
.HorizontalAlignment = xlDistributed
.VerticalAlignment = xlCenter
.AddIndent = True
.MergeCells = True
End With
ThisWorkbook.Worksheets(2).Range(myCell).Value = "売上合計"
End If
myCell = ThisWorkbook.Worksheets(2).Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Address
ThisWorkbook.Worksheets(2).Range(myCell).EntireColumn.ColumnWidth = 16
ThisWorkbook.Worksheets(2).Range(myCell).Offset(0, 1).EntireColumn.ColumnWidth = 16
With ThisWorkbook.Worksheets(2).Range(myCell & ":" & Range(myCell).Offset(0, 1).Address)
.HorizontalAlignment = xlDistributed
.VerticalAlignment = xlCenter
.AddIndent = True
.MergeCells = True
End With

ThisWorkbook.Worksheets(2).Range(myCell).Value = Target.Value
ThisWorkbook.Worksheets(2).Range(myCell).Offset(1, 0).Value = "売上個数"
myCell = ThisWorkbook.Worksheets(2).Cells(2, Columns.Count).End(xlToLeft).Offset(0, 1).Address
ThisWorkbook.Worksheets(2).Range(myCell).Value = "売上金額"
With ThisWorkbook.Worksheets(2).Range(Range(myCell).Offset(0, -1).Address & ":" & myCell)
.HorizontalAlignment = xlDistributed
.VerticalAlignment = xlCenter
.AddIndent = True
End With
Else
Range(myCell).Offset(0, 2).Value = Target.Value
End If
Else
Target.Offset(0, 1).Value = myRange.Offset(0, 1).Value
Target.Offset(0, 2).Value = myRange.Offset(0, 2).Value
Target.Offset(0, 3).Select
End If
End If
ElseIf Target.Address = Range("D" & myRow).Address Then
Target.Offset(0, 1).Value = Target.Value * Target.Offset(0, -1).Value
Target.Offset(1, -3).Select
End If

Application.EnableEvents = True

End Sub

３次に、同じく画面左上にあるVBEProjectと書いてある下のSheet2をダブルクリックし、開いたコードエディター(右側の白い部分)に下記のコードをコピー・ペーストする。

Private Sub Worksheet_Activate()

Dim myMsb As Integer
Dim i As Integer
Dim myRange1 As Range
Dim myRange2 As Range
Dim myRange3 As Range
Dim myVlu1 As String
Dim myVlu2 As String

myMsb = MsgBox("商品別集計処理を実施します。よろしいですか？", vbYesNo + vbQuestion, "作　業　確　認")
If myMsb = vbNo Then Exit Sub
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = ThisWorkbook.Worksheets(1).Range("B1").Value
Set myRange1 = Worksheets(1).Range("E3:" & Worksheets(1).Cells(Rows.Count, 5).End(xlUp).Address)
Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = Application.WorksheetFunction.Sum(myRange1)
For i = 3 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2
myVlu1 = Cells(1, i).Value
Set myRange1 = ThisWorkbook.Worksheets(1).Range("B2:" & ThisWorkbook.Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Address)
Set myRange2 = ThisWorkbook.Worksheets(1).Range("D2:" & ThisWorkbook.Worksheets(1).Cells(Rows.Count, 4).End(xlUp).Address)
myVlu2 = Application.WorksheetFunction.SumIf(myRange1, myVlu1, myRange2)
Cells(Rows.Count, i).End(xlUp).Offset(1, 0).Value = myVlu2
Set myRange2 = ThisWorkbook.Worksheets(1).Range("E2:" & ThisWorkbook.Worksheets(1).Cells(Rows.Count, 5).End(xlUp).Address)
myVlu2 = Application.WorksheetFunction.SumIf(myRange1, myVlu1, myRange2)
Cells(Rows.Count, i + 1).End(xlUp).Offset(1, 0).Value = myVlu2
Next i

End Sub

４.ＡＬＴ＋Ｆ１１を押してエクセルの画面にもどり、シート１を開いてＢ１に今日に日付を入力する。
５.データーを適当に入力してみる。入力が終わったら、シート２を開く

自動で１日の商品別の売上個数と金額がシート２に表示されます。

操作が終わったら、このブックは保存をせずに終了して下さい。

１年分のデーターがシート１に入力できます。その時月別の商品ごとの売上個数の合計と売上金額の合計は必要でしょうか。お知らせ下さい。なお、コードの説明は必要があれば後程させていただきます。

お手数をおかけいたしますが、よろしくお願いいたします。

回答No.3

kazuhiko5681

早速補足していただき、お手数をおかけいたしました。
１.新規ブックを立ち上げ、Sheet1を開く。項目名を次のように設定する。
　・Ａ１に売上日と入力する。Ｂ１に日付を入力する
　・Ａ２とＧ1に商品ＩＤと入力　・Ｂ２とＨ１に商品名と入力　・Ｃ２とＩ２に単価と入力
　・Ｄ２に個数・Ｅ２に売上金額と入力
あとは、マクロで自動的に表が出来上がります。ここまで準備しておいて下さい。マクロにつきましては、追ってお知らせいたします。

補足

おはようございます。

ご指示の通り準備出来ました。
「日計売上表」というタイトルに致しました。

ご指導心より感謝しております。
お体にご留意されますように。

回答No.2

kazuhiko5681

こんばんわ。早速補足いただき有難うございます。あなた様のおやりになりたいことは、エクセルだけでできると思われます。
あなた様のおやりになりたいことを実現するに当たり、次のようにしてみたいと思うのですが、あなた様のご意見をお聞かせ下さい。

１.新規ブックを立ち上げる。
２.データの入力専用シートとしてシート１を使う。
３.１日の１日の売上品目別の売上総数と総売上を自動でシート２に表示させる。シート２の内容のみ保存し、シート１の内容は、自動でシート２に表示後削除する。

＜それぞれのシートの構成内容＞
シート１
・Ａ１に商品ＩＤ、Ｂ１に商品名、Ｃ１に単価、Ｄ１個数、Ｅ１に売上金額、Ｆ１に売上日と項目名を入力する。売上日は、Ｆ２のみに入力する。

シート２
・Ａ１とＡ２を範囲選択し、セル結合して売上日と入力する。
・Ｂ１とＣ１を範囲選択し、セル結合して商品名を入力する。
・Ｂ２に個数・Ｃ２に金額と入力する。
つまり、行方向に売上日・列方向に商品名を自動入力させる。

＜操作の方法>
・シート１のＡ列に商品ＩＤを入力したら、自動で商品ＩＤに対応する商品名と単価を自動でＢ列・Ｃ列に表示させ、Ｄ列にカーソルを飛ばす。
・シート１のＤ列に個数を入力したら自動で売上金額を表示させ、Ａ列にカーソルを飛ばす。
・１日の入力が終了したら、Sheet2を表示させると同時に各商品に対応する売上合計数と合計売上金額をそれぞれのセルに自動で表示させる。

それから、売上伝票の内容についてお尋ねいたします。
売上伝票は、顧客別例えば４名様ご来店でラーメン１個・チャーハン１個・中華丼２個・合計○○円という風になっているのでしょうか。それともその日の商品別の売上個数と売上金額が解っているのでしょうか。
これよってマクロの書き方が違ってきますので教えて下さい。

お手数をおかけいたしますが、よろしくお願いいたします。

補足

御丁寧なご指導ありがとうございます。

売上伝票の内容は
ラーメン１個・チャーハン１個・中華丼２個・合計○○円という風になっています。メニューは９１件ありますので、
ＩＤナンバーを基本に、その日の売上伝票を一枚一枚入力しようと思います。

ご指導のようにシート１とシート２を用意しました。
お手数をおかけいたしますが、マクロの書き方のご指導
宜しくお願いいたします。

回答No.1

kazuhiko5681

はじめまして。
飲食店用の売上管理のデータベースの作成方法を私でよろしけれ一緒に考えていきたいと思います。あなた様は、アクセスとエクセルの両方を使うことを考えておられるようですが、エクセルだけで管理することも可能かと思います。ご希望の節は、以下の点をお知らせ下さい。

１.エクセルだけで管理するようにしてよいか。
２.１日の売上品目別の売上総数と総売上を別表に表示しているようですが、これを保存しておく必要があるかどうか。
３.保存する場合、１日の売上品目数がどのくらいあるのか。
４.できれば、あなた様が今使用しているデータベースの項目の内容

お手数をおかけいたしますが、よろしくお願いいたします。

補足

早々のご指導ありがとうございます。

お尋ねの件につきまして
1.はい。できればエクセルだけで管理したいです。
2.保存しておく必要があります。
3.１日の売上品目数は91件です。
4.項目は　商品ＩＤ、商品名、単価、個数、売上日です。

できれば、入力フォームからは個々の売上げ伝票を参照しながら
ＩＤと個数を入力すると表の中に整理されて一日の各商品ごとの
総数、総売上が表示され、毎日の売上日の欄にデータが表示され、
毎月の表に格納、年間の商品別の動向が最終的に表示出来るようにしたいのです。
込み入っていて申し訳ありません。出来るでしょうか？
ご指導お願いいたします。