エクセルで来店客の履歴を「来店記録」というファイルに残しています。

Question

icta

46

45もっと見る

500pt

コンピュータ

エクセルで来店客の履歴を「来店記録」というファイルに残しています。

「来店記録」を元に顧客ごとに購入回数、売上金額を多→少の順番に並び替えたデータを、「お得意様」というファイルに、「購入回数順」、「売上金額順」というシートにそれぞれ取り込みたいと考えています。
並び替えられたデータを実際に見るのはエクセルの知識がほとんどない販売スタッフです。
そのため誰でも簡単にわかるようにする必要があります。
データの並び替えなどをスタッフにさせず、「来店記録」を開いた状態で「お得意様」を開くと常に最新の情報が「お得意様」に載っているようにするにはどのようにしたらよいでしょうか？
ピボットテーブル、関数、マクロなどを活用してできるだけ最小ステップで実現する方法を教えていただけませんでしょうか。
もしそのまま使える完全なコードややり方を回答していただいた回答者の方には500ポイントを差し上げます。よろしくお願いいたします。
※なお文字制限のため「来店記録」、「お得意様」のデータはこの下にある「この質問・回答へのコメント」に記載いたします。

回答の条件

1人3回まで

登録：2008/04/13 21:41:10
終了：2008/04/14 16:14:11

※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

No.2

akunin3322008/04/14 09:45:09

10pt

完璧ではないですが、ヒントになれば．．．

案1. ピボットテーブルの例（同日に複数売り上げがあった場合はその回数になってしまいます）

http://akunin.hp.infoseek.co.jp/temp/uriage.xls

ポイントは

キャンセル分のデータをカウントさせないために元データのシートに「売り上げのあった日」の式を入れます
ピボットテーブルの「顧客番号」のところで「フィールドの設定」「詳細」を選択し、「自動並べ替えオプション」で売り上げ金額または売り上げのあった日の降順を選択します
ピボットテーブル上の「売り上げのあった日」は「フィールドの設定」で「数値の個数」を選択し、キャンセルされたデータを除外します（が、複数の売り上げがあった日はその回数でカウントされてしまいます．．．苦笑）

更新のタイミングですが．．．どなたかにシートの選択時にマクロが動く方法を聞いてみてください（汗）

案2. 邪道にAccessを使ってみます

http://akunin.hp.infoseek.co.jp/temp/uriage.zip

ポイントは

元データのExcelの金額の欄の書式設定で表示形式を「会計」で選び、0をキャンセルとして扱います
Access上でクエリを2つ作成します

一つ目は「顧客別日別売上集計」でキャンセルされたものを除いた単純な日別の集計を作成します

（ここで同日複数売り上げを1行に集計します）

二つ目は「売上集計」で「顧客別日別売上集計」の日別のカウントと金額の合計を集計します

Excelに戻り、Accessの「売上集計」を「外部データの取り込み」で取り込みます。

「売上金額順」のシートは売上金額の降順、「売上回数順」は売上回数の降順でソートして取り込みます

（サンプルはOffice 2000で作成しています）

このときに「データ範囲プロパティ」で「更新の周期」を1分に設定してください。そうすれば元データを修正してしばらくした後に自動的に更新されます

（シート移動時に強制的に更新するマクロに関してはどなたかに確認してください）

icta 2008/04/13 21:41:34

以下が「来店記録」、「お得意様」です。
注意点は次の通りです。
※同日に複数の注文があっても1件とする。
※売上金額に「-」（ハイフン）が入っているものは返品、キャンセルのため購入回数に含まない。売上金額も0とする。
※最新データは最終行に追加していく。
※返品は数日後に行われることがある。返品により購入回数、売上金額が変われば、それに応じて「お得意様」の順位も変わる

<pre>
■ファイル「来店記録」シート「来店記録」

|顧客番号|顧客名|来店日|売上金額|　
+--------+------+------+--------+
|98 |瀬戸 |10/3 |20000 |
+--------+------+------+--------+
|21 |山本 |10/3 |10000 |
+--------+------+------+--------+
|13 |松田 |10/3 |5000 |
+--------+------+------+--------+
|21 |山本 |10/3 |2000 |
+--------+------+------+--------+
|45 |鈴木 |10/4 |30000 |
+--------+------+------+--------+
|52 |神田 |10/4 |8000 |
+--------+------+------+--------+
|83 |平井 |10/4 |4000 |
+--------+------+------+--------+
|21 |山本 |10/5 |10000 |
+--------+------+------+--------+
|65 |原沢 |10/5 |9000 |
+--------+------+------+--------+
|21 |山本 |10/6 |2000 |
+--------+------+------+--------+
|98 |瀬戸 |10/6 |7000 |
+--------+------+------+--------+
|74 |菊池 |10/6 |30000 |
+--------+------+------+--------+
|11 |篠原 |10/6 |15000 |
+--------+------+------+--------+
|99 |吉森 |10/7 |- |
+--------+------+------+--------+
|21 |山本 |10/7 |10000 |
+--------+------+------+--------+
|98 |瀬戸 |10/7 |30000 |
+--------+------+------+--------+
|21 |平井 |10/8 |10000 |
+--------+------+------+--------+
| | | | |
+--------+------+------+--------+

■ファイル「お得意様」シート「購入回数順」

|顧客番号|顧客名| 回数 |売上金額|　
+--------+------+------+--------+
|21 |山本 |4 |34000 |
+--------+------+------+--------+
|13 |瀬戸 |3 |57000 |
+--------+------+------+--------+
|74 |菊池 |1 |30000 |
+--------+------+------+--------+
|65 |原沢 |1　　 |9000 |
+--------+------+------+--------+
|11 |篠原 |1 |15000 |
+--------+------+------+--------+
|13 |松田 |1 |5000 |
+--------+------+------+--------+
|52 |神田 |1 |8000 |
+--------+------+------+--------+
|21 |平井 |1 |10000 |
+--------+------+------+--------+
|45 |鈴木 |1 |30000 |
+--------+------+------+--------+
|99 |吉森 |0 |- |
+--------+------+------+--------+

■ファイル「お得意様」シート「売上金額順」

|顧客番号|顧客名| 回数 |売上金額|　
+--------+------+------+--------+
|13 |瀬戸 |3 |57000 |
+--------+------+------+--------+
|21 |山本 |4 |34000 |
+--------+------+------+--------+
|74 |菊池 |1 |30000 |
+--------+------+------+--------+
|45 |鈴木 |1 |30000 |
+--------+------+------+--------+
|11 |篠原 |1 |15000 |
+--------+------+------+--------+
|21 |平井 |1 |10000 |
+--------+------+------+--------+
|65 |原沢 |1　　 |9000 |
+--------+------+------+--------+
|52 |神田 |1 |8000 |
+--------+------+------+--------+
|13 |松田 |1 |5000 |
+--------+------+------+--------+
|99 |吉森 |0 |- |
+--------+------+------+--------+

</pre>
akunin 2008/04/14 01:08:27

＞※売上金額に「-」（ハイフン）が入っているものは返品、キャンセルのため購入回数に含まない。売上金額も0とする。
このハイフンは文字として考えてよいのでしょうか？

また、データは平井さんと山本さんが顧客番号21で同じですが、同じ顧客番号の人が複数いることが前提でしょうか？
Mook 2008/04/14 01:27:12

解答できない状態なのでアドバイスだけですが、来店記録は今後数年にわたって使用していくつもりでしょうか。
EXCEL の2003以前のバージョンでは、１シートの記録数（行数）は65535という上限があります。

一日の来客人数にもよりますが、これが数か月分にしかならないようなら、これ以上のデータをどう処理するか
も検討されておいたほうがよいかと思います。
（シートを月単位で管理する等）
icta 2008/04/14 02:57:18

>akuninさん
ハイフンは文字です。
顧客番号の件、失礼しました。コピーのときのミスです。
顧客番号は1人につき1番号です。

>Mookさん
エクセルの限界行数は存じております。
以前、業者からデータベースを導入しましたが、煩雑すぎて失敗しました。パソコンに初めて触ったという女性スタッフたちには、今と変わらぬ”エクセルで”、できるだけ”簡単に”、ボタンひとつ”ワンタッチで”というのがいちばん大切なようです。
幸い、限界行に達するには１～２年くらいは持ちそうです。
SALINGER 2008/04/14 07:49:41

説明が少なくて申し訳ありませんでした。
メニューのツール→マクロ→Visual Basic EditerからVBEを起動して、
プロジェクトエクスプローラ（表示してなければCtrl+Rで表示）の
購入回数順と売上金額順と名前のついたシートをダブルクリックして開くシートに
コードをそのままコピペしてみてくだださい。
icta 2008/04/14 16:24:16

>SALINGERさん
説明の補足ありがとうございました。
私の理解不足でした。深夜でよく頭がまわりませんでした。
教えていただいたコードで希望通りの動作を確認しました。
こんなに短いコードでこれまで手作業で何時間もかけてきたことが達成できてまるで嘘のようです。
この来店記録を元に実現したいマクロがあと２つあります。
またご協力いただければ大変うれしいです。

人力検索はてなエクセルのマクロの質問です。来店客の購入.. 2008-06-08 19:56:16

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

リクエスト送信済

回答リクエストを送信したユーザーはいません

SALINGER · Accepted Answer · 2008-04-14T01:44:45+09:00

最小ステップではないかもしれませんが、マクロで作ってみました。

それぞれ「来店記録」「購入回数順」「売上金額順」というワークシートがあるとして、

コメントの表は左上詰め（Ａ１から始まっている）と仮定します。

「購入回数順」シートと「売上金額順」シートがアクティブになったときに表を更新するコードです。

「購入回数順」シート

Private Sub Worksheet_Activate()
    Dim wr As Worksheet
    Dim wk As Worksheet
    Dim r1 As Long
    Dim r2 As Long
    Dim r3 As Long
    Dim f1 As Boolean
    Dim f2 As Boolean
    
    Set wr = Worksheets("来店記録")
    Set wk = Worksheets("購入回数順")
    
    Application.ScreenUpdating = False
    
    Rows("2:65536").ClearContents
    
    r1 = 2
    While wr.Cells(r1, 1).Value <> ""
        r2 = 2
        f1 = False
        If wr.Cells(r1, 4).Value <> "-" Then
            While wk.Cells(r2, 1).Value <> ""
                If wk.Cells(r2, 1).Value = wr.Cells(r1, 1).Value Then
                    wk.Cells(r2, 4).Value = wk.Cells(r2, 4).Value + wr.Cells(r1, 4).Value
                    
                    f2 = False
                    For r3 = 2 To r1 - 1
                        If wr.Cells(r3, 1).Value = wr.Cells(r1, 1).Value And _
                            wr.Cells(r3, 3).Value = wr.Cells(r1, 3).Value And _
                            wr.Cells(r3, 4).Value <> "-" Then
                            f2 = True
                            Exit For
                        End If
                    Next
                    
                    If f2 = False Then
                        wk.Cells(r2, 3).Value = wk.Cells(r2, 3).Value + 1
                    End If
                    f1 = True
                End If
                r2 = r2 + 1
            Wend
            If f1 = False Then
                wk.Cells(r2, 1).Value = wr.Cells(r1, 1).Value
                wk.Cells(r2, 2).Value = wr.Cells(r1, 2).Value
                wk.Cells(r2, 3).Value = 1
                wk.Cells(r2, 4).Value = wr.Cells(r1, 4).Value
            End If
        End If
        r1 = r1 + 1
    Wend
    
    wk.Range("A2:D65536").Sort Key1:=Range("C2"), Order1:=xlDescending
    
    Application.ScreenUpdating = True
End Sub

「売上金額順」シートのコードもほとんど同じで

　　・
    Set wk = Worksheets("売上金額順")
    ・
　　・
    wk.Range("A2:D65536").Sort Key1:=Range("D2"), Order1:=xlDescending
　　・

対象になるワークシートを変えるのと、ソートするときの基準となる列を変更するだけです（C2→D2）

SALINGER · Accepted Answer · 2008-04-14T01:44:45+09:00

最小ステップではないかもしれませんが、マクロで作ってみました。

それぞれ「来店記録」「購入回数順」「売上金額順」というワークシートがあるとして、

コメントの表は左上詰め（Ａ１から始まっている）と仮定します。

「購入回数順」シートと「売上金額順」シートがアクティブになったときに表を更新するコードです。

「購入回数順」シート

Private Sub Worksheet_Activate()
    Dim wr As Worksheet
    Dim wk As Worksheet
    Dim r1 As Long
    Dim r2 As Long
    Dim r3 As Long
    Dim f1 As Boolean
    Dim f2 As Boolean
    
    Set wr = Worksheets("来店記録")
    Set wk = Worksheets("購入回数順")
    
    Application.ScreenUpdating = False
    
    Rows("2:65536").ClearContents
    
    r1 = 2
    While wr.Cells(r1, 1).Value <> ""
        r2 = 2
        f1 = False
        If wr.Cells(r1, 4).Value <> "-" Then
            While wk.Cells(r2, 1).Value <> ""
                If wk.Cells(r2, 1).Value = wr.Cells(r1, 1).Value Then
                    wk.Cells(r2, 4).Value = wk.Cells(r2, 4).Value + wr.Cells(r1, 4).Value
                    
                    f2 = False
                    For r3 = 2 To r1 - 1
                        If wr.Cells(r3, 1).Value = wr.Cells(r1, 1).Value And _
                            wr.Cells(r3, 3).Value = wr.Cells(r1, 3).Value And _
                            wr.Cells(r3, 4).Value <> "-" Then
                            f2 = True
                            Exit For
                        End If
                    Next
                    
                    If f2 = False Then
                        wk.Cells(r2, 3).Value = wk.Cells(r2, 3).Value + 1
                    End If
                    f1 = True
                End If
                r2 = r2 + 1
            Wend
            If f1 = False Then
                wk.Cells(r2, 1).Value = wr.Cells(r1, 1).Value
                wk.Cells(r2, 2).Value = wr.Cells(r1, 2).Value
                wk.Cells(r2, 3).Value = 1
                wk.Cells(r2, 4).Value = wr.Cells(r1, 4).Value
            End If
        End If
        r1 = r1 + 1
    Wend
    
    wk.Range("A2:D65536").Sort Key1:=Range("C2"), Order1:=xlDescending
    
    Application.ScreenUpdating = True
End Sub

「売上金額順」シートのコードもほとんど同じで

　　・
    Set wk = Worksheets("売上金額順")
    ・
　　・
    wk.Range("A2:D65536").Sort Key1:=Range("D2"), Order1:=xlDescending
　　・

対象になるワークシートを変えるのと、ソートするときの基準となる列を変更するだけです（C2→D2）

エクセルで来店客の履歴を「来店記録」というファイルに残しています。

ベストアンサー

SALINGER34549692008/04/14 01:44:45

その他の回答（1件）

SALINGER34549692008/04/14 01:44:45ここでベストアンサー

akunin3322008/04/14 09:45:09

コメント（6件)

この質問への反応（ブックマークコメント）

トラックバック