複数条件に一致するデータの抽出方法について

質問

Excel2007を使用しています。
ネット等で色々方法を模索したのですがお手上げだった為、質問させて下さい。
別々の人間が管理している、下記のような二つのシートがあります。

Sheet1
A列（店舗コード） B列（取引先名）　 C列（取扱商品）
0102 　　　　　　○商店□支店 ノート星柄
0100 　　　　　　○商店○支店 ノートストライプ
0102 　　　　　　○商店□支店 消しゴム星柄　　　
0103　　　　 ○商店△支店　　 鉛筆無地　　　 　　
0100 　　　　　　○商店○支店　　 鉛筆水玉

Sheet2
A列（店舗コード） B列（取引先名、商品）　 　　　　C列（商品単価）
0102 　　　　　　○商店□支店　ノート星柄　　　　　　　130
0100 　　　　　　ストライプノート　○商店○支店　　　　100
0100 　　　　　　○商店○支店　水玉模様の鉛筆　　　　　50
0102 　　　　　　星柄の消しゴム　○商店□支店　　　　　80
0103　　　　 ○商店△支店　鉛筆無地　　　　　　　　　40

このうちSheet1にD列を増やして、Sheet2の商品単価を自動的に反映するようにしたいです。
少ない知識から抽出ならVLOOKUPだ！ということで、実際にやってみたのですが…

0102　　○商店□支店　　　　ノート星柄　　　 130（あってる）
0100　　○商店○支店　　　　ノートストライプ 100（あってる）
0102　　○商店□支店　　　　消しゴム星柄　　　 130（本当は80円）
0103　　○商店△支店　　　　鉛筆無地　　　　　　　　40（あってる）
0102　　○商店○支店　　　　鉛筆水玉 　　　　　100（本当は50円）

店舗コード0103は一つしかないので問題なく商品単価を抽出します。
ところが店舗コード0100、0102は二つずつある為、それぞれ一番目のものしか抽出しません。

理想
0102 ○商店□支店 ノート星柄　　　　　　130
0100 ○商店○支店 ノートストライプ　　　100
0102 ○商店□支店 消しゴム星柄　　　　　80
0103　　　○商店△支店　　 鉛筆無地　　　　　　　40
0100 ○商店○支店　　 鉛筆水玉　　　　　　　50

自動的にこうなるように式を組みたいのですが、何関数を使えばいいのでしょうか？
前方一致や後方一致など色々試行錯誤してみましたが、ご覧の通りSheet2のB列は
入力方法がバラバラでいかんともしがたく…。
なお実際のデータは数が膨大なので、最初の一つを設定したあとはセルDを下へ
ドラッグすると一気に反映されるような形にしたいです。
どうぞみなさまのお知恵をお貸しいただければと思います。宜しくお願い致します。

回答No.5

bunjii

Q
重ね重ね恐縮なのですが、
=INDEX(Sheet2!C$1:C$6,MAX((A2=Sheet2!A$2:A$6)*ISNUMBER(FIND(C2,Sheet2!B$2:B$6))*ROW(D$2:D$6)),1)
というをあえて文章にするとどんな内容になりますでしょうか？
A
「数式内の関数を順次解説せよ」と言うことのようですね。
左側から述べてみますと次のようになります。
１．INDEX(配列,行番号,列番号）と言う数式で配列はSheet2のC列（C1:C6）、行番号はMAX関数でA2がSheet2のA列と一致し、然も、C2の文字列がSheet2のB列（B2:B6）に含まれる行番号を計算します。列番号は配列が1列なので1とします。
２．MAX（配列）と言う数式で配列は前項で必要とする論理を論理式で組み立てています。乗算を行うことによって論理演算子の結果を数値化することと論理演算のANDを兼ねています。
３．ISNUMBER（テストの対象）と言う数式でテストの対象はC2の文字列がSheet2のB列の文字列に含まれている位置（数値）の有無を検査し、有=1/無=0に変換します。
４．FIND(検索文字列,対象,開始位置）と言う数式でC2がSheet2のB列の各セルに含まれている位置を検査します。開始位置を省略すると先頭からになります。
５．ROW（参照）と言う数式で参照はD2からD6としてあります。Sheet2の検査対象の行番号の範囲を指定し、列記号はどれでも良いです。

上記の説明は各関数のヘルプで応用例を見ながら学習すれば理解できるでしょう。
但し、論理演算は目的に応じて自分が数式を考えなければなりません。

回答No.4

tom04

こんばんは！

質問のサンプルをよくよく見ると
Sheet1の
B列　→　○商店○支店
C列　→　鉛筆水玉
のデータと
Sheet2の
B列　→　○商店○支店 水玉模様の鉛筆
が一致するものと判断したいようですが、
「鉛筆水玉」と「水玉模様の鉛筆」は別物ですので
関数で完全一致させようとするとかなり無理があると思います。

考え方としてはSheet1のB列「取引先名」は完全一致のようですので、
その中からC列の文字列を一文字ずつ舐めるように検索し、すべての文字があった場合に
Sheet2のC列「金額」を表示させる方法ではどうでしょうか？

VBAになってしまいますが一例です。

Alt+F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, j As Long, k As Long, lastRow As Long, str As String
Dim myFlg As Boolean, wS2 As Worksheet, wS3 As Worksheet
Set wS2 = Worksheets("Sheet2")
Set wS3 = Worksheets("Sheet3")
With Worksheets("Sheet1")
lastRow = wS2.Cells(Rows.Count, "B").End(xlUp).Row
On Error Resume Next
Application.ScreenUpdating = False
For i = 2 To .Cells(Rows.Count, "B").End(xlUp).Row
wS2.Range("A1").AutoFilter field:=2, Criteria1:="*" & .Cells(i, "B") & "*"
Range(wS2.Cells(2, "B"), wS2.Cells(lastRow, "C")).SpecialCells(xlCellTypeVisible).Copy wS3.Range("A1")
For j = 1 To wS3.Cells(Rows.Count, "A").End(xlUp).Row
myFlg = True
For k = 1 To Len(.Cells(i, "C"))
str = Mid(.Cells(i, "C"), k, 1)
If InStr(wS3.Cells(j, "A"), str) = 0 Then
myFlg = False
Exit For
End If
Next k
If myFlg = True Then
.Cells(i, "D") = wS3.Cells(j, "B")
End If
Next j
wS3.Range("A:B").Clear
Next i
wS2.AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub 'この行まで

※　↓の画像のように左側がSheet1・右側がSheet2のような配置になっているという前提です。
※　尚、Sheet3を作業用のSheetとして使用していますので、Sheet3は使用していない状態にしておいてください。

※　関数でないのでデータ変更があるたびにマクロを実行する必要があります。m(_ _)m

回答No.3

keithin

データをきちんと正しくできたなら，既出回答のように重たい数式を使わず

たとえば
シート１のＤ２：
=SUMIFS(Sheet2!C:C,Sheet2!A:A,A2,Sheet2!B:B,"*"&B2&"*",Sheet2!B:B,"*"&C2&"*")
＃Ａ列が該当店舗コード，Ｂ列が取引先名および商品であるＣ列の数字

または
=IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"*"&B2&"*",Sheet2!B:B,"*"&C2&"*")>1,"複数の該当データが存在する",SUMIFS(Sheet2!C:C,Sheet2!A:A,A2,Sheet2!B:B,"*"&B2&"*",Sheet2!B:B,"*"&C2&"*"))

といったもっと軽い計算式を使います。
特に今回はシート１のデータの行数だけ計算式を並べる事になるので，不用意に重たい数式を使用するのは避けてください。

＞手間はなんとか省きたかったのです…。

再掲：
できないなら諦めるのも一考です。

回答No.2

bunjii

提示のデータでSheet1とSheet2で表現が異なっていると検索できませんので、取り敢えず、Sheet2の商品をSheet1に合わせて検証してみました。
Sheet1のD2へ次の式を入力し、Ctrl+Shift+Enterで確定します。
=INDEX(Sheet2!C$1:C$6,MAX((A2=Sheet2!A$2:A$6)*ISNUMBER(FIND(C2,Sheet2!B$2:B$6))*ROW(D$2:D$6)),1)
D2セルを下へD6セルまでコピーしました。
数式は提示されたデータの範囲を指定していますので実際のデータに合わせて対象セルの範囲を変更してください。
貼付画像の赤色の文字部分はSheet1とSheet2の表現を修正した部分です。

補足

ありがとうございます！望んでいた形になりました！
重ね重ね恐縮なのですが、
=INDEX(Sheet2!C$1:C$6,MAX((A2=Sheet2!A$2:A$6)*ISNUMBER(FIND(C2,Sheet2!B$2:B$6))*ROW(D$2:D$6)),1)
というをあえて文章にするとどんな内容になりますでしょうか？
今回教えていただいた事を今後も応用できるようにしていきたいと思っており、そのためにはどうしても文章で内容を把握しておきたいです。
（根っからの文系人間なため、数式だとどうしても理解が遅いのです…）
御手数をお掛けしますが、どうぞ宜しくお願い致します。

回答No.1

keithin

ちょっとどうしようもありませんね。データが「間違っている」ので，打つ手無しです。
「ノートストライプ」と「ストライプノート」，「消しゴム★柄」と「星柄消しゴム」が「同じ」に見えるのは人間の曖昧な思い込みでしか無いので，そういう事では全く計算できません。

>入力方法がバラバラでいかんともしがたく…。

丁寧に正しいデータに修正し統一して，計算できるよう頑張って作業をしてください。
できないなら諦める，のも一つの方法です。

たとえば「正しい商品名称一覧」を別途用意，それに合致しないシート２の記載は条件付き書式等で警告の色を塗り，潰していくとかは良い作業方法の一つです。
シート２のＢ列で店舗と商品名の記載順が前後しているぐらいなら，まぁ計算の方法はあるのでそんなに気にしなくても大丈夫です。

お礼

回答ありがとうございます。
Sheet2のB列に入力する人間が不特定多数で、かつこれまでにSheet2のB列に入力されているデータが膨大な量だった為、こちらでもう一度手入力し直すという手間はなんとか省きたかったのです…。