人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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

●質問者: icta
●カテゴリ:コンピュータ
✍キーワード:にわか ほと エクセル コメント コード
○ 状態 :終了
└ 回答数 : 2/2件

▽最新の回答へ

1 ● SALINGER
●500ポイント ベストアンサー

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

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

コメントの表は左上詰め(A1から始まっている)と仮定します。

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


「購入回数順」シート

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)

◎質問者からの返答

早々のご回答ありがとうございます。

ただ「シートがアクティブになったときに表を更新するコードです。」という部分が具体的にどのようなことを行えばよいのか検索し、こちらも参考にしてみたのですがわかりませんでした。

http://www.efcit.co.jp/cgi-bin2/exqalounge.cgi?print+200512/0512...

このマクロを使うためにどのような手順を踏めばよいのか教えてください。よろしくお願いいたします。


2 ● akunin
●10ポイント

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

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

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

ポイントは

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


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

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

ポイントは

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

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

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

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

(サンプルはOffice 2000で作成しています)

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

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

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ