こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

EXCEL 部分一致でLookup 条件が増えてしまいました

お世話になります。
先日、こちらの質問をさせていただいた者です。

EXCEL 部分一致でLookup
http://oshiete1.goo.ne.jp/qa5552745.html


同じようなエクセルの処理なのですが、
さらに条件が増えたものが登場し困っております。

以下のような形式になっています。

Sheet1 (☆に値段を入れたい)
  機種名 値段
1 A5     ☆
2 AA23SS   ☆
3 ABB101   ☆
4 A56DAB   ☆
5 BA1234   ☆



(以下数千行)

Sheet2 (値段表のマスターデータ、ソート済)
  機種名 値段
1 A5   1000
2 A56D  2000
3 AA23  3000
4 ABB101 4000
5 B1234 5000





【望む結果】
Sheet1
  機種名 値段
1 A5    1000
2 AA23SS  3000
3 ABB101  4000
4 A56DAB  2000
5 BA1234  5000





数字の後にアルファベットが付くものは派生機種です。
(上記Sheet1の「AA23『SS』」「A56D『AB』」のようなもの)


数字の前が「アルファベット1文字+ハイフン」だった場合は、先日ご回答いただいた

B2~
=VLOOKUP(MID(A2,1,MATCH(TRUE,INDEX(ISERROR(MID(A2,ROW($A$1:$A$10)+2,1)*1),,),0)+1),Sheet2!A:B,2,0)

で解決したのですが、今回は

・数字の前のアルファベットが1~3文字(ハイフンは無し)
・派生機種でなくとも、後ろにアルファベットがついている場合がある

と、さらにややこしいものになってしまいました。


なお、派生記号に関してはアルファベットのみとなっています。



当方EXCEL2003を使用しておりますので、関数のネスト制限の問題もあり途方に暮れています。


どなたか知恵をお貸しいただけませんでしょうか。
よろしくお願いいたします。

投稿日時 - 2010-01-05 13:49:24

QNo.5569259

すぐに回答ほしいです

質問者が選んだベストアンサー

参考程度に。

=INDEX(Sheet2!B:B,LOOKUP(10000,MATCH(MID(A2,1,ROW($A$1:$A$6)),Sheet2!A:A,0)))
こんな感じの関数で参照する事は可能です。
※『10000』...はマスタデータの最大行数より多く設定する必要があります。
※『ROW($A$1:$A$6)』...の範囲行数(6)はマスタデータの最大文字数より多く設定する必要があります。

...ですが、マスタデータが数千件で、参照データも数千件あるとなると、重くて使い物にならないと思われます。

Sheet1に入力する派生機種とは、いわゆる付加データでしょうから、マスタのkeyである文字列とは別の列に入力するなど、
データの持たせ方を見直したほうが良いです。
数千件ものデータを関数で部分一致させるのはちょっと無理があります。

投稿日時 - 2010-01-05 20:39:54

お礼

ありがとうございます!
望んでいた結果が出ました!

データは他者が作ったものなので、どうにもなりませんでした。
確かに重くはなりましたが、再計算を手動にするとなんとか使えました。

本当にありがとうございました。

投稿日時 - 2010-01-06 15:50:30

ANo.2

このQ&Aは役に立ちましたか?

2人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.3

こんばんは!
お役に立てるかどうか判りませんが・・・

考え方として、Sheet1・2共に数字のみを拾い出し、それを参照して値段を表示させてみるようにしてみました。

尚、この場合、機種名の列にA5 B5というように数値のみを表示させた場合同じ物があると使えないと思いますので、
とりあえず、数値のみの重複はないものとしています。

↓の画像のようにSheet1・2共に作業用の列を使わせてもらっています。

Sheet1開いたまま、Ctrlキーを押しながらSheet2のSheetタブをクリックします。
これでSheetのグループ化ができましたので、Sheet1の作業列C2セルに
数式を入力するとSheet2も同じ数式が入ります。

配列数式になってしまいますので
Shift+Ctrl+Enterキーで確定してください。
C2セルに
=IF(A2="","",MID(A2,MATCH(TRUE,ISNUMBER(MID(A2,ROW($A$1:$A$20),1)*1),0),COUNT(MID(A2,ROW($A$1:$A$20),1)*1)))
としてShift+Ctrl+Enterキーで確定です。
(A列のセル内が20文字まで対応できるようにしています)
これをオートフィルで下へずぃ~~~!っとコピーします。
C列は数値のみの表示になりますので、この数値を参照します。

B2セルに
=IF(C2="","",INDEX(Sheet2!$B$2:$B$1000,MATCH(C2,Sheet2!$C$2:$C$1000,0)))
(配列数式ではありません!)
としてフィルハンドルでダブルクリック、またはオートフィルで下へコピーしてみてください。

以上、長々と書きましたが
参考になれば幸いです。
しかし、最初に書いたように数値だけの参照では
希望通りにならなかったら読み流してくださいね。m(__)m

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2010-01-05 20:58:14

お礼

わざわざ画像までご用意いただきありがとうございます!

ですが、今回は数値の重複がありましたので使えませんでした。
私の説明不足だったようで申し訳ございません。

同様のケースがあった場合にはぜひ利用させていただきたいと思います。

本当にありがとうございました。

投稿日時 - 2010-01-06 15:50:49

ANo.1

こんにちは。
ユーザー定義関数を使用したほうが簡単かと思います。
以下を標準モジュールに追記してください。
Option Explicit

Function NVLOOKUP(Str As String)
Dim I As Integer
For I = Len(Str) To 1 Step -1
NVLOOKUP = Application.VLookup(Left(Str, I), Range("Sheet2!A1:B5").Value, 2, False)
If IsError(NVLOOKUP) = False Then Exit For
Next I
End Function

Sheet1のB1に
=NVLOOKUP(A1)
にして、以降コピーで良いです。
これで結果が出ると思います。

動作の説明ですが
ユーザー定義関数NVLOOKUPの中で引数をVLOOKUPしています。
ただし、引数をLEFTを使い一文字づつ減らして検索します。
VLOOKUPの戻り値がエラーでなければそのときのVLOOKUPの戻り値が返されます。

投稿日時 - 2010-01-05 14:37:34

お礼

ご回答ありがとうございます!
VBAはあまりわからないので勉強になりました。

試してみましたが、なぜか上手くいかない箇所がありました。
私のやり方がまずかったのかもしれませんが、今回はend-u様の方法を使わせていただきました。
VBAも学んでいきたいと思います。

本当にありがとうございました。

投稿日時 - 2010-01-06 15:50:11

あなたにオススメの質問