3つのシートから適合する項目を抽出したい

質問

エクセル関数にて何かいい方法があればお教えください。
シート名"商品名A"、シート名"商品名B"シート名"商品名C"と3つのシートがあります。
それぞれのシートにはA列からW列まで統一した項目が入っているのですが、
商品Aを購入した顧客が商品Bや商品Cを購入した場合で、今まではそれぞれの商品の顧客番号を基準にVlookupでAとBとCを照らし合わせ、
A/B/C、A/B、A/C、B/C、Aのみ、Bのみ、Cのみの購入と分けて更に、担当IDをCOUNTIFを使って集計していました。
現状だと何回も同じ作業を繰り返していてとても面倒なんです。

もっと効率の良い関数式はありますでしょうか？
最終的には別のシートの列にそれぞれの商品購入項目（A/B/C、A/B・・・）を、行に営業IDを入れて集計をしたいと思っております。
またはマクロを組んだ方が良いのでしょうか？マクロはまだ初心者で余り自信が無いのですが。。。
どなたかお知恵を拝借頂けないでしょうか。よろしくお願いいたします。

回答No.5

noname#52504

複合的な課題なので焦点がぼやけてしまっていますが、
個別の段階ごとに順次処理すればさほど難しい点はないように思います。
関数式の問題と言うより、作業手順の問題ですね。

コピー&ペーストやフィルタを併用した方が簡単で処理も軽いですが、
週２～３回は行うということですので、
処理速度は度外視で全て数式処理、全自動にしてしまうのもアリかもしれません。
ただしその場合は、元表に追記するたびに再計算されてはたまりませんから、
作業シートや集計シートは別のブックに独立させた方が吉です。

マクロという選択肢もありますが、
サクッと作れるスキルや体制が現時点であれば別として、
仕事しながら勉強して作成してデバッグして運用保守していつか引き継ぐ、
となると割に合うかどうか微妙なところです。

以下、大雑把な記述になってしまいますが、必要があれば補足しますので。

Step１．各商品の顧客ID，担当IDを一つのシートにまとめる。

　私は正直これが一番面倒だなと思ったのですが、
　#1さんへの補足を見ますとそうでもないご様子ですね。

　苦でなければ単純にコピー&ペーストを３回繰り返しても良いでしょうし、
　数式でやるなら、行数に十分余裕を持たせてリンク貼り付けとか、配列参照とか。
　数式を工夫すればびっちり埋めることもできますが、かえって重くなりそうです。

　以下、"作業シート"のA列2行目以降に総ての顧客ID、B列にその担当IDが入ったものとします。

Step２．「重複のない顧客ID」の一覧を生成する。

　S1の表は、商品を複数購入した顧客のIDがダブっていますから、重複を除きます。
　フィルタオプションの[重複するレコードは…]オプションを使うのが標準的ですが、
　数式でやることもできます。
　例えば、
　C2：=IF(A2=0,"",IF(COUNTIF($A$2:A2,A2)=1,ROW()-1,""))
　D1：=COUNT($C$2:$C$9999)
　D2：=IF(ROW()-1<=$D$1,SMALL($C$2:$C$9999,ROW()-1))
　E2：=INDEX(A$2:A$9999,$D2)
　F2：=INDEX(B$2:B$9999,$D2)
　C2，D2，E2，F2をそれぞれ下方にフィル。
　一度にやると固まるかもしれないので、各列毎にフィルしてください。

　以下、"作業シート"のE列に重複のない顧客ID、F列にその担当IDが入ったものとします。

Step３．各顧客ごとの購入商品パターンコードを生成する。

　表品別シート（元表）のB列2行目以降に顧客IDがあるとします。
　"作業シート"のG列に下記のような数式を入れます。
　（３行で一つの数式です）

=IF(ISNA(MATCH(E2,商品Ａ!$B$2:$B$9999,0)),"","A")
&IF(ISNA(MATCH(E2,商品Ｂ!$B$2:$B$9999,0)),"","B")
&IF(ISNA(MATCH(E2,商品Ｃ!$B$2:$B$9999,0)),"","C")

　購入パターンに応じて、"ABC"とか"AC"とか"B"とかいったコードが表示されます。
　これらは集計に用いるコードに過ぎないので、実際の商品名に置き換える必要はありません。

Step４．「重複のない担当ID」の一覧を生成する。

　これは既にどこかにあるんじゃないかと思いますが…。
　もし必要があれば、S2の方法で作ります。

　以下、"集計シート"のA3セル以降に重複のない担当IDが入ったものとします。
　第1列は見出し行に使うものとして、
　第2行にパターンコードを"ABC","AB","AC"…と順に入力しておきます。

Step５．"作業シート"の表から、担当IDと購入パターンが一致するものをそれぞれカウントする。

　"複数条件でCOUNT"という頻出の課題です。
　私は配列数式の方が好みですが、SUMPRODUCTの方が人気があるようです。

　配列数式の場合
　B3：=SUM((作業シート!$F$2:$F$9999=$A3)*(作業シート!$G$2:$G$9999=B$2))
　を配列数式として入力し、右方、下方にフィル
　　※通常の数式は、数式を入力した後Enterキーで確定しますが、
　　　これは配列数式なので、CtrlとShiftを押しながらEnterで確定してください。

　SUMPRODUCTの場合
　B3：=SUMPRODUCT((作業シート!$F$2:$F$9999=$A3)*(作業シート!$G$2:$G$9999=B$2))
　を普通に入力し、右方、下方にフィル

以上です。

「件数延3000－顧客1000人弱－担当50人－購入パターン完全ランダム」
というダミーデータでテストしてみましたが、全て数式で処理すると、
単一のブックで運用するのはちと重たいかなぁ…という感じでした。

長乱文陳謝

お礼

お返事が遅くなりまして済みませんでした。
ご回答いただきまして有難うございます。
一番応用にも利きそうですし、関数の使い方の勉強にもなりそうですね。早速試していきたいと思います。
有難うございました。

回答No.4

suku0

やりたいことが、今ひとつ理解できていないのですが、ピボットテーブルの利用はいかがですか。
きれいな書式にはなりませんが、欲しいデータは簡単にまとめられそうな気がするのですが・・・・

#２さんのように、データを一つのシートにする。商品の列を作る。
担当ID 顧客 商品
1　　　　　　あ A
1　　　　　　い A
2 　　う A
2 　　え A
3 　　お A
3 　　か B
3 　　き C
・・・・・・

全範囲を指定して、「データ」→「ピボットテーブル・・・」
範囲を確認して、「次へ」→「レイアウト」へ
行に、「担当ID」、「顧客」の順にドラッグ
列に、「商品」をドラッグ
データに、「担当ID」をドラッグし、合計／担当ID　になっていることを確認して、「ＯＫ」→「完了」

できあがった表が、役に立てば幸いです。
きっとはずしていると思うのですが、参考までに。

お礼

お返事が遅くなりまして済みませんでした。
ご回答いただきまして有難うございます。
ピボットならスグに試せそうですね。
有難うございました。

回答No.3

n-jun

シートレイアウトが不明なので参考案。

商品シートを左からＡＢＣの順で並べて、以下を実行。
・顧客番号毎にどの商品を購入したかＡＢＣの文字の連結で表しています。
・担当ＩＤ毎にどの商品を販売したかＡＢＣの文字数で表してます。
正直見当外れかも知れないので、違っていたらすいません。

Sub test()
Dim Dic1 As Object
Dim Dic2 As Object
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rko As Range
Dim rta As Range
Dim i As Integer
Dim j As Integer
Dim v

Set Dic1 = CreateObject("Scripting.Dictionary")
Set Dic2 = CreateObject("Scripting.Dictionary")
Set ws2 = Worksheets("Sheet4") '集計するシート名

v = Array("", "A", "B", "C")

For i = 1 To 3

Set ws1 = Worksheets(i)
Set rko = ws1.Range("A1") '顧客番号の列　仮にＡ列
Set rta = ws1.Range("C1") '担当ＩＤの列　仮にＣ列

For j = 1 To ws1.Range("A" & Rows.Count).End(xlUp).Row - 1

Set rko = rko.Offset(1)
Set rta = rta.Offset(1)

Dic1(rko.Text) = Dic1(rko.Text) & v(i)
Dic2(rta.Text) = Dic2(rta.Text) & v(i)
Next
Next

With ws2
.Range("A:A").NumberFormatLocal = "@"
.Range("A2").Resize(Dic1.Count).Value = Application.Transpose(Dic1.keys)
.Range("B2").Resize(Dic1.Count).Value = Application.Transpose(Dic1.items)

.Range("E:E").NumberFormatLocal = "@"
.Range("E2").Resize(Dic2.Count).Value = Application.Transpose(Dic2.keys)
.Range("F2").Resize(Dic2.Count).Value = Application.Transpose(Dic2.items)

.Range("A2").Resize(Dic1.Count, 2).Sort Key1:=Range("A2"), _
Order1:=xlAscending, DataOption1:=xlSortTextAsNumbers
.Range("E2").Resize(Dic1.Count, 2).Sort Key1:=Range("E2"), _
Order1:=xlAscending, DataOption1:=xlSortTextAsNumbers
End With
End Sub
３箇所ほど実際の状況に合わせて変更願います。

お礼

お返事が遅くなりまして済みませんでした。
ご回答いただきまして有難うございます。
じっくりと確認しながら、試していきたいと思います。
有難うございました。

回答No.2

imogasi

>もっと効率の良い関数式はありますでしょうか？
単独の関数ではありません。
複雑に組み合わせても、難しい（回答が無い？）のではないかな。
エクセル（表計算ソフト）の関数は数の計算から始まっています。
検索してや条件をかけて、抜き出しは苦手なままあまり進歩してないです。
よっぽどエクセルマニアで凝り性で経験もある方で無いと無理です。
それに加えて、シート数が２つ３つに分かれると（合計的な統合はあるが）個別明細行・列の統合は難しいとおもう。
上司等に挙げる課題レポートや手元資料を作るのが精一杯で、対顧客の取引情報などは、関数でやろうというのは、無理で「レコード」を扱う、ＶＢＡでプログラムを作る必要があると思う。
そのプログラムも、易しくは無い。
ーーーー
こういうのを試してはどうか。
商品Ａ、商品Ｂ、商品Ｃのシートの列構成（項目有無と順序）が同じとして
（１）作業シートに商品Ａ->商品Ｂ->商品Ｃと、順にコピー貼り付けして、継ぎ足し１シートにデータを１本化する。
ＶＢＡ化は下記（注）
（２）顧客番号でソートする。ＶＢＡで自動化はマクロの記録で判る。
（３）（１）（２）の結果のシートで考える。
例えばピボットテーブルが使えないか考える。
（注）
ツールーマクローＶＢＥ－挿入ー標準モジュールにコピペ
Sub test01()
k = 1
For i = 1 To 3
d = Worksheets(i).Range("A65536").End(xlUp).Row
rng = "A1:H" & d
Worksheets(i).Range(rng).Copy Worksheets(4).Cells(k, 1)
k = k + d
Next
End Sub
問題のシート３つと集約したデータを置くシート１つ計４つを一番左に寄せておくこと。
集約するシートが４つならFor i = 1 To 4
集約するシートの名が「集約」とするならWorksheets(4).ーー＞
Worksheets("集約").Cells(k, 1)

補足

ご回答いただきまして有難うございます。
テストをしてみたところデバッグになってしまい、
『変数が・・・』と出てきてしまいました。
変数はまだ勉強中でこのコードの中に4つくらい（？）あるのでしょうか。
もしお時間があればもう少し詳細を伺えればと思います。
お手数をおかけします。ｍ（_　_）ｍ

回答No.1

n-jun

ある顧客が商品Ａを購入すると、商品名Ａのシートに情報として載せる。
あわせて担当者ＩＤも記載される。
その顧客が商品Ｂも購入したならば、同様に記載される。
とする。
顧客は同じ商品を複数回購入し、且つその情報は都度記載される？

>今まではそれぞれの商品の顧客番号を基準にVlookupでAとBとCを照らし合わせ、
>A/B/C、A/B、A/C、B/C、Aのみ、Bのみ、Cのみの購入と分けて
顧客毎に購入した商品の組み合わせをみている？

>更に、担当IDをCOUNTIFを使って集計していました。
顧客毎に購入した際の担当者のカウントをする？

>現状だと何回も同じ作業を繰り返していてとても面倒なんです。
どのような作業を繰り返すのでしょう？
繰り返しの手間を省く⇒マクロで対応する。
とも言えますし。

>最終的には別のシートの列にそれぞれの商品購入項目（A/B/C、
>A/B・・・）を、行に営業IDを入れて集計をしたいと思っております。
購入商品の組み合わせ毎の担当者の集計？
組み合わせ毎の集計は必要なものですか？

補足

ご返答有難うございます。
以下問答。
>顧客は同じ商品を複数回購入し、且つその情報は都度記載される？
1人の顧客は最大でA,B,Cの3つまで購入する場合があり、Aを2つ等はありません。
>顧客毎に購入した商品の組み合わせをみている？
そうなんです。営業マンは3種類の商品を販売して優秀とされそれ以外は順次評価が落ちていきます。
>顧客毎に購入した際の担当者のカウントをする？
担当IDでカウントして全セールス数とそれに伴った１顧客がどれだけ商品を購入したかを集計し、どの営業マンが優秀か、どの商品をセールするのが得意か苦手かなどを判断していきます。
>どのような作業を繰り返すのでしょう？
・3つの商品のリストを抽出してそれぞれのシートに分ける（下の回答にあるように1つのシートへまとめることも可能です。）
・作業シートに商品Aの顧客番号をコピペ。
○VlookupでBとCの顧客番号を抽出。
○オートフィルタでA,B,Cの3つとも購入した顧客番号を抽出。（A/B/C購入）
・○を基準を替えて繰り返し作業して（A/B、A/C、B/C、A、B、C）とそれぞれの顧客番号を抽出。
△顧客番号をもとに販売した営業マンをVlookupで抽出。
・別の作業シートに、△と複合商品で集計するため、
A列に担当ID、B列以降に（A/B、A/C、B/C、A、B、C）と並べ
(例)　=Countif(B$1:B$1500,$A2)
この式を営業マン分コピペして集計としての作業が終了します。

これを上司が『出して』といわれるとこの作業が始まり、週に2～3回あり、面倒だなぁと思っております。
何か良いお知恵はございますでしょうか？