[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『条件定義で顧客データの抽出をしたい！』（MAR）
初心者です。
本やネットを色々見ながらなんとか色々とデータ処理が出来るようにExcelと戦っています。
今回は顧客データから過去の取引状況を抽出するデータ処理方法をどうしたらいいかと思って
色々見るのですが、少しも解決しないのでご教授願います。
関数は本を見ながらでしたら、30％位理解できるようになりました。（複雑すぎるのはまだまだ読解するのは大変ですが）あと、マクロは最近やっとかじりだした程度です。
SHEET1
A日付 B注文番号 C名前 D郵便番号 E都道府県 F住所 G電話番号 H商品番号 I商品名 Jサイズ K数量 L備考
別シートで 「名前」と「都道府県」を入力すると該当する顧客データの履歴全て（最新→古い）が抽出されるようにしたいです。（完全一致ではなく、名前を優先した抽出をしたいです。複数候補がいる場合は複数抽出されてもかまいません。）
抽出した際は下記の並びで抽出したいです。お客様によっては一人につき取引件数が多くある方もいらっしゃいます。
A名前 B日付 C注文番号 D郵便番号 E都道府県 F住所 G電話番号 H商品番号 I商品名 L備考
データ数は現在1000件程度です。日々追加していきます。
年度別でシートを別にして管理をするのが理想です。
関数で全て解決するのか
それともマクロにすべきなのか
それさえも分からない状態ですが、教えて頂ける方がいらっしゃったら是非よろしくお願いいたします。
2014/7/16 サンプル不足をご指摘いただきましたので追記します。（（半平太さん）コメントありがとうございました。）
A日付 B注文番号 C名前 D郵便番号 E都道府県 F住所 G電話番号 H商品番号 I商品名 Jサイズ K数量 L備考
SHEET1
A 2013.7.01 B 123 C 観世太郎 D 150-0000 E 東京都 F 渋谷区松涛1丁目11番1号 G 03-3469-5241 H ABC118 I Ｔシャツ J レディース K 1 L クレーム対応
A 201３.９.01 B 456 C 鞍馬花子 D 180-0000 E 東京都 F 調布市2丁目22番2号 G 03-6544-4321 H ABC321 I タオル J キッズ K 3 L サイズ交換希望
SHEET２
A 2014.1.01 B 321 C 観世 太郎 D 150-0000 E 東京都 F 渋谷区松涛1丁目16番4号 G 03-3469-5241 H ABC321 I タオル J メンズ K ３ L サイズ交換希望
A 2014.11.01 B 789 C 翁 健司 D 666-0000 E 兵庫県 F 川西市5丁目43番2号 G 06-6666-8907 H DDD39 I キーホルダー J 赤 K 10 L クレーム対応
A 2014.12.09 B 555 C 羽衣 京子 D 078-0000 E 北海道 F 旭川市9丁目98番 G 098-9879-1234 H AAA87 I 財布 J 水玉 K 1 L 色変更希望
A 2014.12.31 B 98 C 羽衣 京子 D 999-0000 E 広島県 F 安芸群海田町中町22-22 G 098-9879-1234 H AAA87 I 財布 J 水玉 K 1 L 色変更希望
SHEET３
A 2015.1.01 B 1059 C 観世 太郎 D 100-0000 E 宮城県 F 仙台市7丁目777番 G 030-3469-5241 H KJJ98 I ソックス J メンズ K 1 L 色変更希望
A 2015.12.01 B 1234 C 羽衣 京子 D 078-0000 E 北海道 F 旭川市9丁目98番 G 098-9879-1234 H TTT55 I ティーポット J ガラス K 1 L 破損
★抽出用シート
A 名前 B 日付 C 注文番号 D 郵便番号 E 都道府県 F 住所 G 電話番号 H 商品番号 I 商品名 J 備考
例１）名前＋都道府県 で 検索（この場合ですと「観世太郎」と「東京都」で名前優先）
↓
A 観世 太郎 B 2013.7.01 C 123 D 150-0000 E 東京都 F 渋谷区松涛1丁目11番1号 G 03-3469-5241 H ABC118 I Ｔシャツ J クレーム対応
A 観世 太郎 B 2014.1.01 C 321 D 150-0000 E 東京都 F 渋谷区松涛1丁目11番1号 G 03-3469-5241 H ABC321 I タオル J サイズ交換希望
A 観世 太郎 B 2015.1.01 C 1059 D 100-0000 E 宮城県 F 仙台市7丁目777番 G 030-3469-5241 H KJJ98 I ソックス J メンズ K 1 J 色変更希望
例２）名前＋都道府県 で 検索（この場合ですと「羽衣 京子」と「北海道」で名前優先）
↓
A 羽衣 京子 B 2014.12.09 C 555 D 078-0000 E 北海道 F 旭川市9丁目98番 G 098-9879-1234 H AAA87 I 財布 J 色変更希望
A 羽衣 京子 B 2015.12.01 C 1234 D 078-0000 E 北海道 F 旭川市9丁目98番 G 098-9879-1234 H TTT55 I ティーポット J 破損
A 羽衣 京子 B 2014.12.31 C 98 D 999-0000 E 広島県 F 安芸群海田町中町22-22 G 098-9879-1234 H AAA87 I 財布 J 色変更希望
＜ 使用 アプリ：Excel2011(Mac)、使用 OS：MacOSX ＞
＞関数で全て解決するのか ＞それともマクロにすべきなのか ＞それさえも分からない状態ですが、 私なら、フィルターオプションをマクロで制御します。
＞SHEET1 ＞A日付 B注文番号 C名前 D郵便番号 E都道府県 F住所 G電話番号 H商品番号 I商品名 Jサイズ K数量 L備考 ＞別シート ＞A名前 B日付 C注文番号 D郵便番号 E都道府県 F住所 G電話番号 H商品番号 I商品名 L備考 別シートの「L備考」 は、Ｊ列に「備考」の間違いですよね？ つまり２列減少する。 それとも「名前」を先頭に持ってくるだけであって、サイズや数量は単純な書き忘れ？
＞年度別でシートを別にして管理をするのが理想です。 ・・と言われましてもちょっと漠然としています。
何かが複数ある場合は、サンプルは最低２つ示すのが適切です。 もっとも現在、サンプルデータはまだ１つも示されていない状態ですけどね。
今は、タイトルの説明だけしかないです。 まぁ、ほとんど常識で補えるものなので大勢に影響はないですけど、 各回答者はそれぞれテキトーなサンプルを考えなければならない。大量の無駄といえば無駄です。 質問者が適切なサンプルを一つ考えれば、全員それをコピーするだけで済むんです。
おまけに説明不足をそのサンプルで補えることもあります。 たとえば、会社の決算月はまちまちであるから「年度」といっただけでは期間は必ずしも定まらないのですけど １年度のサンプルに4月と翌年の3月が含まれていれば、４－３で１年度なんだなぁと確信することができます。
（半平太） 2014/07/16(水) 09:06
話しの流れを変えない為に、追加コメントは、下方のコメント欄から入れるようにしてください。
＞年度別でシートを別にして管理 ソースデータの方だったのですか、出力側かと思っていました・・・聞いてみるもんですね。
１．「抽出」シートと「WORK」シートを新規に挿入してください。
２．「抽出」シートのA1セルに「名前」と入力してください。 (この入力が無いと正常作動しません）
３．「抽出」シートのA2セルに抽出したい氏名、B2セルに都道府県名を入力したら、 マクロ(dealsByEachCust）を実行してください。(実行ボタンに登録しておくと楽です) <抽出>シート結果図
行 ____A____ _____B_____ ____C____ ____D____ ____E____ __________F__________ ______G______ ____H____ ______I______ _____J_____ 1 名前 都道府県 2 羽衣 京子 北海道 3 名前 日付 注文番号 郵便番号 都道府県 住所 電話番号 商品番号 商品名 備考 4 羽衣 京子 2014/12/9 555 078-0000 北海道 旭川市9丁目98番 098-9879-1234 AAA87 財布 色変更希望 5 羽衣 京子 2015/12/1 1234 078-0000 北海道 旭川市9丁目98番 098-9879-1234 TTT55 ティーポット 破損 6 羽衣 京子 2014/12/31 98 999-0000 広島県 安芸群海田町中町22-22 098-9879-1234 AAA87 財布 色変更希望
’標準モジュールに貼り付けるマクロコード
Sub dealsByEachCust()
Dim ShNamesToProc, ShName
ShNamesToProc = Split("Sheet1 Sheet2 Sheet3") ’増えたら、半角スペースを挟んでシート名を追加する
Application.ScreenUpdating = False
'データをWorkに集める
With Sheets("WORK")
.Cells.ClearContents
Sheets(ShNamesToProc(0)).Rows(1).Copy .Rows(1)
.Range("M1").Value = "KEY"
For Each ShName In ShNamesToProc
With Sheets(ShName)
.Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 12).Copy _
Sheets("WORK").Cells(.Rows.Count, "A").End(xlUp).Offset(1)
End With
Next
With .Range("M2").Resize(Application.Count(.Columns(1)), 1) 'Key作成
.FormulaR1C1 = "=IF(抽出!R2C2=WORK!RC[-8],"" #"",""#"")&RC[-8]&RC[-12]"
.Value = .Value
End With
With .Sort
.SortFields.Clear
.SortFields.Add Key:=Range("M1"), _
SortOn:=xlSortOnValues, Order:=xlAscending
.SetRange Sheets("WORK").Range("A1").CurrentRegion
.Header = xlYes
.Orientation = xlTopToBottom
.Apply
End With
End With
'フィルタオプションで抽出
Dim WshToExtract As Worksheet '抽出シート
Dim rngSource As Range
Set WshToExtract = ThisWorkbook.Sheets("抽出")
'
If Application.CountBlank(WshToExtract.Range("A2:B2")) = 2 Then
Application.EnableEvents = False
WshToExtract.UsedRange.Offset(3).ClearContents
Application.EnableEvents = True
Exit Sub
Else
'タイトルを再配置
With Sheets(ShNamesToProc(0))
.Range("C1").Copy WshToExtract.Range("A3")
.Range("A1:B1").Copy WshToExtract.Range("B3:C3")
.Range("D1:I1").Copy WshToExtract.Range("D3:I3")
.Range("L1").Copy WshToExtract.Range("J3")
End With
Set rngSource = Sheets("WORK").Range("A:L")
Application.EnableEvents = False ’フィルタオプション実行
rngSource.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=WshToExtract.Range("A1:A2"), _
CopyToRange:=WshToExtract.Range("A3").Resize(1, 10), _
Unique:=False
Application.EnableEvents = True
End If
Application.ScreenUpdating = True
End Sub
（半平太） 2014/07/16(水) 15:45
早速マクロ実行を行ってみた所エラーがでてしまったので、報告いたします。
Sub dealsByEachCust()
Dim ShNamesToProc, ShName
ShNamesToProc = Split("2012 2013 2014 2015 2016") '増えたら、半角スペースを挟んでシート名を追加する
Application.ScreenUpdating = False
'データをWorkに集める
With Sheets("WORK")
.Cells.ClearContents
Sheets(ShNamesToProc(0)).Rows(1).Copy .Rows(1) ←ここでエラーが出てしまい止まってしまいました。
.Range("M1").Value = "KEY"
＜エラー内容＞
実行時エラー ’９’:
インデックスが有効範囲にありません。
ブックには 2012～2016 を仮シートを作ってみて実行してみました。
存在するシートですので、有効範囲内だとおもうんですが、
何かチェックする点がありましたら教えて頂きたいです。
よろしくお願いします。
（MAR） 2014/07/16(水) 18:38
こんなプログラムを流してみてください。
Sub test()
Dim sh
Dim ShNamesToProc, ShName
ShNamesToProc = Split("2012 2013 2014 2015 2016")
For Each sh In Sheets
Debug.Print "#" & sh.Name & "#", Application.Match(sh.Name, ShNamesToProc, 0)
Next
End Sub
イミディエイトウィンドウにどんな表示が出ますか？ (手を加えず、そのままコピペしてください）
ちなみに、私のは、こんな風になりました。
#抽出# エラー 2042 #2012# 1 #2013# 2 #2014# 3 #2015# 4 #2016# 5 #Sheet1# エラー 2042 #Sheet2# エラー 2042 #Sheet3# エラー 2042 #WORK# エラー 2042
（半平太） 2014/07/16(水) 19:03
頂いたマクロを実行した所、 イミディエイトウィンドウ（初めてこのウインドウの存在をしりました。何をする場所なのかは調べて知識に変えます！）には下記が表示されました。
ご確認をよろしくお願いします。
#抽出# エラー 2042
#WORK# エラー 2042
#２０１２# エラー 2042
#２０１３# エラー 2042
#２０１４# エラー 2042
#２０１５# エラー 2042
#２０１６# エラー 2042
（MAR） 2014/07/17(木) 12:28
＞ShNamesToProc = Split("2012 2013 2014 2015 2016")
↑
こちらは半角数字で指定
実際のシート名は全角数字。どちらかに統一してください。 ↓ #２０１２# エラー 2042
（半平太） 2014/07/17(木) 13:22
ぱっと見は同じようにみえたのですが、ご指摘の通りシート名を再度入力し直しました。
→抽出ができました！
test マクロ実行
＜イミディエイトウィンドウ＞
#抽出# エラー 2042
#WORK# エラー 2042
#2012# 1
#2013# 2
#2014# 3
#2015# 4
#2016# 5
以上の様なコードがでてきました。
問題なく動いているという認識でよろしいでしょうか？
（MAR） 2014/07/17(木) 17:26
＞→抽出ができました！ エラーなく抽出ができたのでしたら、testプログラムは実行する必要は無いです。
＞ 問題なく動いているという認識でよろしいでしょうか？ 最終的な検証はそちらで行ってください。
なお、他の質問で作ったものを焼き直して作った関係で 不要なステートメント(※）が消し忘れになっています。 消した方がいいですが、そのままでも多分無害だと思います。
※ Application.EnableEvents = False
（半平太） 2014/07/17(木) 18:40
また分からないこと等ありましたら、質問をさせて頂くかもしれません。
Excelの学校運営者様、このようなサイトを運営して頂いてありがとうございます。
すこしでも役に立てるよう、自分でも勉強をがんばります！
（MAR） 2014/07/18(金) 10:36
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.