[[20110913093442]]　『あいまい検索』（みい）　ページの最後に飛ぶ

『あいまい検索』（みい）

初心者です。マクロを使用せずに、できれば関数でよい方法があれば教えて下さい。

sheet1に

     A          B         C         D            E          F       G
 発行所　　   書名　　　分類　　   記号　　  出版年月日　　登録番号
 ○○出版   ああああ   S123.4   078(1998)ア  1991/1/1  　　3569874
 ××社　　 いいいい   S456/8   124          2000/10/7     3987156

このようなデータがあります。
sheet2には

     A          B         C         D            E          F       G
    №　　　番号　　　　連番　　　発行所　　　発行元２　　書名　　・・・・
　　1         1           1　　　●●出版　　▲▲部　　かかかか
    2         2           2      ■■会社　　　　　　　きききき　　・・・・
というデータがあります。
sheet2の書名（F列）と同じものをsheet1のB列から探してsheet2のI列にsheet1の「分類」sheet2のJ列に「記号」sheet2のｋ列に「出版年月日」sheet2のL列に「登録番号」を
反映させたいのです。しかし、sheet1とsheet2の書名は完全一致ではありません。
2009年度、昭和53年などの年号が入っていたり括弧も複数使われていたり、スペースも
統一されていません。手探りでvlookupを試してみたのですが上手くいきません。
何か良い方法はありますか？宜しくお願いします。

 =VLOOKUP("*"&検索文字列&"*",範囲・・・・

 のようにすると、検索文字列を含むデータを検索できます。
 ただし、その文字列を含むデータが複数ある場合は、上の行にあるデータだけしか検索できませんが。
 (tora)

 何か明確なルールが無いと、関数でもマクロでも難しいと思います。
 まずは、書名を一致させる作業からしたら同でしょうか。
 少なくとも検索のためには、一意に検索できる列を用意する必要があります。

 同じものがあるかどうかは
 H2=COUNTIF(Sheet1!B:B,F2) のようにして、１でないところが確認部分だと思います。
 (Mook)

toraさん、Mookさん、早速のご回答ありがとうございます。
toraさんの方法で試してみましたが、やはり余分な文字が多いためか全くヒットしませんでした。
Mookさんのおっしゃるように書名が一致しているものを抽出してみたところ、３００件近いデータのうち２１件しかヒットしなかったので、途方にくれています。やはり書名の統一から始めるしか無いでしょうか？

 ＞３００件近いデータのうち２１件しかヒットしなかったの

 こう云う問題を考える上では、データの量と云うのはかなり重要な意味を持ちます。

 私の個人的感覚では全部で300件なら、すごく少ないと思うのですが、
 実際のデータ量は本当に全部で300程度なのですか？

 (半平太) 2011/09/13 12:11

 >やはり書名の統一から始めるしか無いでしょうか？

 だよね。たとえば　今日の料理 (2009) という書名　と　今日の料理2009年度版　が同じだと判断できるのは 人間だけ。
 というか、Sheet2のＦ列の値でマスタとしてのSheet1のＢ列を検索しようとした時
 Sheet2側　"今日の料理"  Sheet1側　"今日の料理2009年度版" なら、あいまい検索でマッチするけど
 Sheet2側　"今日の料理2009年度版"  Sheet1側　"今日の料理" だと、Sheet2からはマッチングできないよね。
 そこは、どうなっているんだろうか。

 (ぶらっと)

ぶらっとさんのご指摘通り、Sheet2側とSheet1側で逆の場合もあります。その上（○○編）が付いていたりその間のスペースがあったり、無かったりなどなどです。半平太さんのおっしゃるように数字的には少ないですが、上記のような入力を全て修正して完全一致させるとなるとかなり手作業で行わなくてはならないのかなというところです。

 考え方
 　Sheet1は書名マスタであり、書名のダブリは無いハズである。
 　Sheet1の書名は正規書名である。

 　従って、本作業後、Sheet2の書名は正規書名に書き換えられるべきである。
 　その作業も見通すなら、照合結果にマスタの書名も加えるべきである。

 照合方法
 　スペースの有無で不一致になるのを避ける為、スペース抜き書名で照合する

 　余計な文字(例；○○年度版)がある為に不一致になるのを「できるだけ」避ける為、
 　頭から数文字(始めは5文字程度から)の書名で照合する。

 　数文字だけで照合を行うと、一致すべきでない書名も余分にヒットすることがあるため、
 　１つの書名になるまで、人間がその文字数を増加させる。

 　文字数を増加させた結果、複数冊からゼロ冊に転落する場合は、
 　その複数冊を人間が見比べて、最終的にどれか決定する
 　つまり、候補行番号(V列より右のセルに出ている番号)の中から、確定させる行番号を決めて、T列に手入力する。

 　見比べし易くする為、候補セルをクリックすると、マスタシートの候補行へハイパーリンクで飛べる様に塩梅する。

 １．Sheet1の処置

 　H2セル =SUBSTITUTE(ASC(B2)," ","")

 　下にフィルコピー

 <Sheet1>マスタデータのサンプル
 行____A____ __________B__________ ___C___ _____D_____ _____E_____ ____F____ _G_ __________H__________
 1 発行所    書名                  分類    記号        出版年月日  登録番号      書名(スペース排除)   
 2 ○○出版  ああああ              S123.4  078(1998)ア 1991/1/1     3569874      ああああ             
 3 ××社    いいいい              S456/8          124 2000/10/7    3987156      いいいい             
 4 ××社    今日の料理 2009年度版 S456/9          125 2000/10/8    3987157      今日の料理2009年度版 
 5 ××社    今日の料理 2010年度版 S456/10         126 2000/10/9    3987158      今日の料理2010年度版 
 6 ××社    明日の料理            S456/9          127 2000/10/10   3987159      明日の料理           
 7 ××社    今日の料理            S456/9          126 2000/10/9    3987158      今日の料理

 ２．Sheet2の処置
 <Sheet2>　その１　
 下準備
 O列には1を入力してください。書名の始めの位置を仮決めします。
 P列には5を入力して下さい。　書名の文字数を仮決めします。複数該当する場合は、これを手で順次増加させることになります。

 数式の入力
 (1) Q2セル =REPT("*",O2>1)&MID(SUBSTITUTE(ASC(F2)," ",""),O2,P2)&"*"
 (2) R2セル =COUNTIF(Sheet1!H:H,Q2)
 (3) S2セル =IF(R2=1,MATCH(Q2,Sheet1!H:H,0),"照合続行")
 (4) U2セル =IF(T2<>"",T2,S2)
 (5) V2セル =IF($R2<COLUMN(A:A),"",HYPERLINK("#Sheet1!A"&MATCH(Q2,Sheet1!H:H,0),MATCH(Q2,Sheet1!H:H,0)))
 (6) W2セル =IF($R2<COLUMN(B:B),"",HYPERLINK("#Sheet1!A"&MATCH($Q2,OFFSET(Sheet1!$H$1,V2,0,500),0)+V2,MATCH($Q2,OFFSET(Sheet1!$H$1,V2,0,500),0)+V2))

 　　(6)の式を右へフィルコピーしたあと2行目全体を下にフィルコピー

 　　　　　　　　　　　　　　　　　　　　　　　　　※行番号を手入力すると、それが優先されます
 　  　　　　　　　　　　　　　　　　                   ↓
 行_O_ __P__ _____Q_____ _____R_____ _____S_____ _____T_____ ____ U ____ __V__ __W__ __X__ __Y__
 1 St  字数  検索書名    曖昧合致数  自動合致行  手動確定行  最終確定行  候補1 候補2 候補3 候補4
 2   1    5  かかかか*            0  照合続行                照合続行                           
 3   1    5  あああ*              1           2                       2      2                  
 4   1    5  今日の料理*          3  照合続行                照合続行        4     5     7      ←※候補数字をクリックすると
 5   1    5  きききき*            0  照合続行                照合続行                           　　マスタに飛びます
 6   1    5  明日の料理*          1           6                       6      6

 <Sheet2>　その２
 (1) I2セル =IF(ISNUMBER($U2),INDEX(Sheet1!C:C,$U2),"")
 　　右へフィルコピー

 (2) M2セル =IF(ISNUMBER($U2),INDEX(Sheet1!B:B,$U2),"")
 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　※作業後、F列をこの値で上書きすべきと思料
 　　　　　　　　　　　　　　　  　　　　　　　　　　　　　　　　　　　　　　　　　　　↓
 行_________ F _________ ____G____ _H_ __ I __ _____J_____ _____K_____ ___ L ___ _____M_____
 1 書名                  項1       項2 分類    記号        出版年月日  登録番号  マスタ書名 
 2 かかかか                                                                                 
 3 ああ　あ              ・・・・      S123.4  078(1998)ア 1991/1/1     3569874  ああああ   
 4 今日の料理 (2009)     ・・・・                                                           
 5 きききき              ・・・・                                                           
 6 明日の料理2009年度版  ・・・・      S456/9          127 2000/10/10   3987159  明日の料理

 (半平太) 2011/09/13 15:27

コメント返信：

[ 一覧(最新更新順) ]

[[20110913093442]] 『あいまい検索』（みい） ページの最後に飛ぶ

[[20110913093442]]　『あいまい検索』（みい）　ページの最後に飛ぶ