個人によって行数の違う一覧表から顧客管理名簿を作成

質問

office 2003です。件数が500件ほどありまして困っています。
A　　　　B 　　　　　　C　　　　　D　　　　E　　　F
1 ヤマダタロウ　　山田太郎　　皿　　　1500　 2枚
2 サイトウタダシ　斉藤　正　コップ　　2000　1個
　 　　　　　　　　　　　　 茶碗　　 1500　2個
　 　　　　　　　　　　　　 箸　　　 1800　2膳
3 オオイ ヨシコ　大井　良子 クロス　 2000　5枚　
　 　　　　　　　　　　　　　皿　　　 1500　 2枚
4 イマイ ケイコ 今井　恵子　バケツ　　800　 1個
のような一人で何行もある一覧のエクセルデータが残っていますこのデータで顧客管理名簿を作成するときにうまい方法がありますでしょうか。

回答No.5

ruru-12 · ベストアンサー

個人的な好みの作成方法で回答させていただきます。
あと、質問者さんがどの程度関数を理解されているかも分からないので失礼があったらすみません。
--------------------------------------------
まずは販売リストを作成すべきと考えます。
その後、販売リストを利用して顧客一覧なり販売状況なりの分析をします。

その為には、現状ある穴あきリストを販売リストに変える作業をします。(これが今回の質問ですよね？)
その後、販売リストからDM用に抽出・集計をします。
と言うことで、まずは現状のリストを「そのままでは使えない」と考えてください。
(VBAが出来ればそのままのリストを加工することも出来ると思います。)

・1行＝1商品アイテムとすること (これやらないとExcel初級者では集計が大変です)
⇒1顧客が同時に複数アイテム購入したら、複数行入力と言う事になります。

現状のリストがsheet1にあると仮定します。
sheet2へ参照関数とIF関数を使い穴あき部分を埋める作業をします。

<Sheet2>
★1行目は項目名とします。
★A列は通し番号の列とします。
☆A2へ関数を入力し、オートフィルで下へコピー
=IF(B2=””、””、ROW(A1))
☆B2へ関数を入力し、オートフィルで下へコピー
=IF(Sheet1!B2="",B1,Sheet1!B2)
※Sheet1!B2 というのは現状のリストの振り仮名が入力されているセルです

現状のリストで商品名や金額などの穴あきになっていない部分は参照関数で問題ないです。
=Sheet1!D2

これを現状リストの行数分を関数処理し、Sheet2を全て選択してsheet3へでも値貼り付けすれば
販売リストが出来上がります。
今後はSheet3へ追加していけばOK。
ただし、Sheet2のA2に入れた関数をSheet3でも使えば、見た目は自動で番号が振られていくように見えます。

（また、例えば「氏名を入力したら住所が勝手に表示される」などの希望があっても、もうちょっと後回しにしたほうがいいと思います。）

集計方法(住所・顧客一覧など)はピボットをお勧めします。覚えてしまうととても便利です。
webやヘルプを見ていただいて不明な場合は新たに質問をあげられると良いと思います。

補足

ruru-12様、ありがとうございます。添付図の作り方を勉強していたのでご連絡が遅くなりました。いろいろ教えていただいて感謝いたしています。
穴あきのフリガナをコピーするのはうまくいきました。うれしい～！

結果的には追加の上の図のような顧客管理情報を目指しています。
お買い物履歴を必ず記載したいのです。
一人ずつ行数が違うのでフリガナの空白を埋めたらうまくいくかと思っていたのですが違う結論を導くような説明で申し訳ありません。

回答No.6

ruru-12

以下アドバイスです。

添付図の下にある雛形をシートに作っておいて、顧客ナンバーを入れたら全ての項目が自動で表示されるということですよね？それを1件ずつ印刷していく。それともその顧客ナンバーも自動で顧客数分勝手に入力されて勝手に印刷までされる？
もしくは雛形がいくつも作ってあって、全てに顧客ナンバーが自動的に割り当てられて、一気に全顧客の顧客情報表が出来上がるってところでしょうか。

どちらにしてもExcelでやるなら簡単には出来ないと思います。
最初の方法以外はマクロになるかと。(力量により解説できません。。)
でもマクロを駆使するよりは、今後顧客情報を管理していく上でAccessを使ったほうが良さそうに思えました。

もう少し処理の全体像を整理して、何をどの方法でどうやったら出来るのかをまとめて、その上で『この部分がわからない』を再度質問されてはいかがでしょうか。

補足

ruru-12様、アドバイスをありがとうございます。
Accessって難しいと思っていますが、やはりそれならいちからと今、勇気がでました。
やってみます。本当にご親切にありがとうございました。

回答No.4

tom04

こんばんは！
No.3です！

要するに顧客管理のための表を作成したいと言うことですね？

一つの案ですが
↓にもう一度表を作成してみました。
来店回数や購入額等が表示できればいいとおもいますので

画像の表で説明すると
Sheet1のA2セルは
=IF(COUNTIF($B$2:B2,B2)=1,ROW(A1),"")
として1000行目くらいまでオートフィルでコピー

Sheet2のA2セルに
=IF(B2="","",ROW(A1))
B2セルに
=IF(COUNT(Sheet1!$A$2:$A$1000)>=ROW(A1),INDEX(Sheet1!B$2:B$1000,SMALL(Sheet1!$A$2:$A$1000,ROW(A1))),"")
としてC2セルまでオートフィルでコピー

D2セルに
=IF(C2="","",SUMPRODUCT((Sheet1!$C$2:$C$1000=Sheet2!C2)*(Sheet1!$F$2:$F$1000)*(Sheet1!$E$2:$E$1000)))

E2セルに
=IF(C2="","",COUNTIF(Sheet1!$C$2:$C$1000,C2))

（今回は購入額だけの表示にしていますが、嗜好のデータを表示したいのであれば、
COUNTIF関数等を利用して、「日用雑貨」「食品」等々分類別で表示させる方法もあるかと思います。

最後にA2～E2セルを範囲指定した後に、
E2セルのオートフィルハンドルで下へずいっ！ずぃ～～～！とコピーします
（かなり下までコピーしても構いません）
これでSheet1にデータが入力されるたびにSheet2に反映し、
なおかつ重複来店のお客様はSheet2には重複されずに集計だけが増えると思います。

尚、Sheet1のデータは1000行まで対応できる数式にしていますが、
データ量によって範囲指定をアレンジしてみてください。

以上、参考になれば幸いですが
他に良い方法があれば軽く読み流してくださいね。m(__)m

回答No.3

tom04

こんばんは！
No.2さんの疑問と一緒ですが・・・

単にフリガナと氏名を拾い出しすればいいのでしょうか？

一応そういうことだと解釈させてもらっての回答になります。

↓の画像のようにA列を挿入して、作業列とさせてもらいます。

A2セルに
=IF(COUNTIF($B$2:B2,B2)=1,ROW(A1),"")
という数式をいれて下までオートフィルでコピーします。

H2セルに
=IF(COUNT($A$2:$A$100)>=ROW(A1),INDEX(B$2:B$100,SMALL($A$2:$A$100,ROW(A1))),"")
という数式を入れ、列方向と行方向にオートフィルでコピーすると

↓の画像のような感じになります。

尚、数式は元データが100行までの数式にしています。

以上、参考になれば幸いですが、
的外れなら読み流してくださいね。m(__)m

補足

tom04様、ありがとうございます。ruru-12様とcistronezk様にごせつめいの追加をさせていただきましたが、お客様が来店のときに何をいつ買われて嗜好のものなどの覚えがきのある顧客管理名簿をめざしています。その後顧客管理名簿に新規のお買い物を入力すれば、最新の一覧表をDMなどのために作りたいと思っています。道は遠く感じていますがなんとかお知恵をお借りして仕上げたいとおもっています。
的確なご説明になっていないようでしたら申し訳ありません。関数も勉強しながら進めたいとおもいますのでよろしくお願い申し上げます。

回答No.2

cistronezk

「顧客管理名簿」の定義が不明です。
最終的にどういう形式にしたいのかという一番大事なことが書かれていませんが、「氏名の読み」と「氏名漢字」のA、B列だけを取り出せればいいのでしょうか？

補足

cistronezk様、ありがとうございます。説明不足で申し訳ありません。初めから顧客管理をつるつもりで作ったデータではなかったのですが、お客様が何をいつ何を買われて、後ろのほうのセルにはどんな嗜好かとのメモもあります。
１住所　氏名　がわかり、
２購入品の履歴
３覚書　　　　が記載されているものを目指しています。

データーが、１行だと扱いやすいのですがそうなっていなかったので何とかこの振り仮名に番号をいれて購入品の履歴がわかるようにして、新規に買われたら今度は管理名簿から一覧表が作成できたら一番うれしいのですが。
よく写真とか表が添付されていますが、うまく出来ないのでつたない説明で申し訳ありません。まだ説明不足でしたらお伝えしたいのでよろしくお願い申し上げます。

回答No.1

ruru-12

顧客管理名簿がどの形を目指しているのか分からないので、リスト化する方法だけご提案します。

B列とC列が顧客情報、且つ虫食いがあるとして、添付のセルI3のようにIF式を入れて空欄を埋めます。

要は元リストの顧客情報欄が空欄だったら上のセルと同じものを表示させているだけです。

そこから先はピボットなり関数で集計なりになりますが省略します。

補足

ruru-12s様、ありがとうございます。
振り仮名の下の空白のセルに自動でコピーをしたかったのです。
できれば振り仮名に１、２、３と連続の数字をつける関数をお教えくださいますでしょうか。
お買い上げ記録表を作りたいのです。
よろしくお願いいたします。