顧客の履歴検索について

質問

まず今していることが、

商品一覧（品名と単価が書いてあるもの）を作り、
それぞれに商品番号を割り振った表を作っています。

そしてその隣に
伝票を作って、型番と個数を打てばVLOOKUPで
情報を引っ張ってきて合計金額まで計算するような伝票を作っています。

これは
売上が合わないということと迅速化するためです。

それで、シートの終わりに合算し、
各店舗分のシートを用意し、また最後に合算できるようにしました。

ただ、これだと
何がいくら出たかがわかりません。
商品数が多いので
A　0
B　0
C　1
D　0
E　0
F　0
・
・

というような表は避けたく、
売れたもののみが表示されるような表が作りたいです。

そして、
顧客名簿（顧客番号と顧客情報を乗せたもの）を別のブックにつくり
先ほどの伝票に顧客番号を併記して管理し、
ある顧客情報が知りたい場合、
（顧客名簿と同じブックでOK）顧客番号をいれると
いつ来店で何を買ったかわかるような検索欄を作りたいです。

ただ、
先ほどの伝票から検索すると思うのですが、
伝票は1日1つのブックで保存したいのです。

ですので、同一フォルダ内で管理していたとしても
1年運用すれば
365ブックの中から検索するようになると思うのですが、

そもそもこういったことは可能でしょうか。

お店で実際運用されているようなものは
マクロとか使われている複雑なものなのでしょうか。

回答No.3

MarcoRossiItaly · ベストアンサー

No.1・2 です。何度もすみません。

No.1 のような数式だと、データを持ってくることはできますが、「入力した番号に応じて」という要素が盛り込まれていませんでした。例えば添付図では、次式を入力しています。

Sheet1
G3　=e3*f3

Sheet2
K1　3
K3　=vlookup($K$1,sheet1!$A$3:$G$7,2,)
K6　=int(k5*5%)
K7　=sum(k5:k6)

K3:K5 のセル範囲は Sheet1 のデータを Sheet2 に持ってきていますが、K6:K7 は Sheet2 の中で参照・計算しています。

お礼

何度もありごとうございます。
挫折しかけていて放置していました。

回答についてですが、
データーベース（DB）に常に記入し
その情報をそのつど伝票に飛ばして印刷だとかということだと思うのですが、
伝票自体も会員番号や、商品番号、個数の入力欄以外はロックしての運用を考えています。
なので
DBは新規会員などの必要時以外はデータ損失などを防ぐために
あくまでデータの保管場所として
あまり通常時は使用を避けたいのです。

そのために
伝票から（伝票のデータが損しても大丈夫なように）
顧客の履歴情報を吸い上げたいのです。

また、
サービス業なので
上に１日の結果を提出しないといけないのです。

そのため
1日１ブックで保存したいのです。

ここまで考えた結果、
ブックに伝票とDBを併置しようかと思いました。

多少ブックサイズは大きくなりますが、
シートの最後に各店舗との合計などをまとめないといけないので、
そのついでに会員登録もできますし、
DBのファイルをいちいち開くということもないし
合理的かなとおもいます。

ただ、顧客履歴の保存

>>全データ（番号、顧客名、日付、商品、金額、担当者名、etc.）を 1 行に載せた表を保存しておき、番号などを入力すれば、空の伝票に表の情報が表示されるようにしておきます。

これはDB→伝票のばあいはできるけど、
伝票→DBの場合はできなさそうなので
対策を考えてみようと思います。

回答No.4

MarcoRossiItaly

＞データーベース（DB）に常に記入し
　その情報をそのつど伝票に飛ばして印刷だとかということだと思うのですが、
　伝票自体も会員番号や、商品番号、個数の入力欄以外はロックしての運用を考えています。
　なので
　DBは新規会員などの必要時以外はデータ損失などを防ぐために
　あくまでデータの保管場所として
　あまり通常時は使用を避けたいのです。

No.3 に書いてあるとおり、「飛ばす」ための入力は、伝票側で行うのが一般的です。

損失云々を考えるなら、No.1 で言ったことを応用して、シートを分けるだけです。No.3 で作った Sheet1 を丸ごとコピーし、Sheet3 とします。次いで、Sheet3 の B3:G7 のデータ部分を消す。No.1 の数式を使って、Sheet3 の B3 セルに、

B3　=sheet1!b3
G3　=e3*f3

と記入。同様にして Sheet3 の B、C、E、G 列を全て埋めます。このほか必要な場合は、VLOOKUP 関数を含む数式などもどこかに記入してもよい。さらに、Sheet3 の D3:D7、F3:F7 のセル範囲を選択した状態で右クリック、セルの書式設定の保護タブで「ロック」のチェックを外して OK、シート全体を保護すれば、D・F 列しか変更できなくなります。

Sheet1 は、D、F、G 列が不要になるので削除し、やはりシート全体に保護をかけて変更できないようにします。

伝票とする Sheet2 においては、Sheet1 ではなく Sheet3 から参照するように書き換えればいいだけ。また Sheet2 も、「飛ばす」ための入力セルだけ上と同様にロックを外し、シート全体に保護をかけます。

それと、本当に堅牢なデータベースを構築したいなら、Access を利用してください。Excel は表計算ソフトなので、それっぽいことはできても、安全性という意味では元々十分ではありません。うっかり消してしまった、ということは常にあり得るということをお忘れなく。

＞また、
　サービス業なので
　上に１日の結果を提出しないといけないのです。
　そのため
　1日１ブックで保存したいのです。

必要な場合は、表示させた伝票を PDF や紙媒体に印刷して別途保存、ということを No.1 でお答えしたとおりです。

繰り返しになりますが、Excel は手軽に計算・加工できるのが利点の表計算ソフトです。ファイルが細かく分かれていると、そういったことは困難になっていきます。

単なる記録として残したいだけなら、PDF、DocuWorks といった編集しづらい形のほうがむしろ良いのではありませんか。ファイルを分けてしまうなら、Word のほうが繊細な書式設定もできます。

上級者で、365 個の Excel ファイルを作製しようと考える人は恐らくいないと思いますよ。管理や操作が大変になることは明らかなので。決め付けないことです。

回答No.2

MarcoRossiItaly

No.1 です。一つ言い忘れたことを補足します。

質問文にあるような VLOOKUP 関数ほかによる種々の計算も、データベースの各行で行えばいいです。その計算結果も、分析や抽出の手がかりに使えることになります。

伝票シート側では、基本的に、データベースの情報を転載するのみとします。伝票という書類を作成するためだけのシートということです。

回答No.1

MarcoRossiItaly

＞お店で実際運用されているようなものはマクロとか使われている複雑なものなのでしょうか。

さほどマニアックな処理ではなさそうなので、マクロはシンプルなコードになるでしょうが、質問者さんが行おうとしている管理の仕方のほうが複雑です。お勧めしかねます。

お店とかプロでなくても、この程度は Excel が上手になれば簡単にできます。

＞先ほどの伝票から検索すると思うのですが、伝票は1日1つのブックで保存したいのです。

お勧めできません。ファイルの数はムダに増やさないほうが管理も処理もラクです。

ではどうすべきなのかと言うと、伝票からデータベースに対して何か（検索とか）をするのではなく、逆に、データベースから伝票を作成するようにします。つまりデータベース、もっと分かりやすく言えば、全データ（番号、顧客名、日付、商品、金額、担当者名、etc.）を 1 行に載せた表を保存しておき、番号などを入力すれば、空の伝票に表の情報が表示されるようにしておきます。

そうすれば、伝票用 Excel ファイルをたくさん作る必要はないですね。必要になったときに番号などを入力するだけで済むので。表示させた伝票を PDF や紙媒体に印刷して別途保存するかどうかは、お好きなように。

すると Excel ファイルには、データベース用のシートが 1 つと、伝票用のシート 1 つ、合わせて 2 シートを含む 1 ファイルのみ作れば足りることになりますね。

別シートから情報を転載する方法は、ごく簡単な数式で参照するだけです。例えば次式を Sheet1 以外のシートのどこかのセルに入力すると、そのセルに、Sheet1 の A1 セルの値を転載します。

=sheet1!a1

＞顧客番号をいれるといつ来店で何を買ったかわかるような検索欄を作りたいです。

データベースの表にオートフィルタを取り付けたり、ピボットテーブルを適用するなどすれば、並べ替えや抽出は自在にできます。伝票からではありません。

＞商品数が多いので　A　0　B　0　C　1　D　0　E　0　F　0　・・　というような表は避けたく、売れたもののみが表示されるような表が作りたいです。

販売数が 0 より大きなレコード（行）のみ抽出するだけです。最もお手軽な方法は上で言ったとおり、オートフィルタです。したがって、売れていないものが載っていても、不自由ありません。