Excel 2003 文字の検索

質問

困っています。教えて下さい。

セルA1に検索したいフィールド（苗字など）
セルB1に検索文字列（あいまい検索したい）

《検索対照の列》　※ソートされています。

顧客番号,苗字,名前,住所

【やりたいこと】
・苗字が”田中”の人を上から10人だけ表示したい。
・苗字に”山”がつく人を上から10人だけ表示したい。

※こんな感じの検索・表示する方法はありますか？？
　 説明が下手ですみません。とりあえず、曖昧な検索を
　 して、結果を10レコード出すようにしたいです。

　 ExcelVBAとかはあまり詳しくないので、易しく教えて下さい。
　 宜しく御願い致します。

回答No.8

kagakusuki · ベストアンサー

>「No.3」の方には実際のこちらの環境を追記させて頂きました。
>その内容で、こちらの回答の内容を反映させた数式を作成していただく事は可能でしょうか？？

　まず、
Q2セルに　　フィールド
R2セルに　　キーワード
Q6セルに　　顧客番号
R6セルに　　姓名(漢字)
U6セルに　　姓名（フリガナ）
W6セルに　　住所
と入力して下さい。
　次に、以下の操作を行って、Q3セルに入力規則のドロップダウンリストを設定して下さい。
Q3セルを選択
　　↓
メニューの[データ]ボタンをクリック
　　↓
現れた選択肢の中にある[入力規則]をクリック
　　↓
現れた「データの入力規則」ダイアログボックスの[設定]タグをクリック
　　↓
「入力値の種類」欄をクリックし、現れた選択肢の中にある[リスト]をクリック
　　↓
「元の値」欄に

姓名(漢字),姓名（フリガナ） ,住所

と入力
※全角文字・半角文字の違いや、印刷には表れない文字も含めて、R6、U6、W6に入力した内容と完全に同じ文字列として下さい
　　↓
「データの入力規則」ウィンドウの[OK]ボタンをクリック

　これで入力規則の設定は完了で、次に、Z19セルに次の数式を入力して下さい。

=IF(AND(COUNTIF($Q$6:$W$6,$Q$3),ROW()>ROW($Q$16)),IF(COUNTIF(INDEX($Q:$W,ROW(),MATCH($Q$3,$Q$6:$W$6,0)),$R$3),ROW(),""),"")

　次に、Z19セルをコピーして、Z20以下に貼り付けて下さい。
　次に、Q7セルに次の数式を入力して下さい。

=IF(ROWS($7:7)>COUNT($Z:$Z),"",INDEX(Q:Q,SMALL($Z:$Z,ROWS($7:7))))

　次に、Q7セルをコピーして、R7～W7の範囲に貼り付けて下さい。
　次に、Q19～W19の範囲に1つ目の元データが入力されている状態で、以下の操作を行って下さい。

Q19～W19の範囲を選択
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせて、マウスを右クリック
　　↓
現れた選択肢の中にある[コピー]をクリック
　　↓
Q7セルにカーソルを合わせて、マウスを右クリック
　　↓
現れた選択肢の中にある[形式を選択して貼り付け]をクリック
　　↓
現れた「形式を選択して貼り付け」ダイアログボックスの中にある[書式]と記されている箇所をクリックし、チェックを入れる
　　↓
「形式を選択して貼り付け」ダイアログボックスの[OK]ボタンをクリック

　次に、Q7～W7の範囲をコピーして、Q8～W16の範囲に貼り付けて下さい。

　以上で準備は完了で、後は、Q3セルを選択すると現れる▼マークのボタンをクリックして、現れた選択肢の中から、姓名(漢字)で検索を行う場合には「姓名(漢字)」、姓名(フリガナ)で検索を行う場合には「姓名(フリガナ)」、住所で検索を行う場合には「住所」を選択して下さい。
　そして、例えば、「姓名(漢字)」を選択してから、
姓名(漢字)が「田中」となっているデータを抽出する場合には、R3セルに

田中

「山」という文字列から始まっている姓名(漢字)のデータを抽出する場合には、R3セルにセルに

山*

姓名(漢字)の何処かに「山」という文字列が含まれているデータを抽出する場合には、R3セルにセルに

*山*

と入力して下さい。（*で囲む文字列は複数であっても構いません）
　すると、Q列～W列の7行目以下に、10人分の抽出結果が元データの上にある方から順番に表示されます。

　又、R列に姓名(漢字)の元データを入力する際に、「山本五十六」という具合に苗字と名前をくっつけるのではなく、「山本 五十六」という具合に苗字と名前の間に必ず空白を開けて入力して置く様にすれば、
R3セルに「 太*」という具合に、「(空白)」+「名前の先頭にある文字列」+「*」と入力すれば、名前の先頭にある文字列で検索する事が可能になります。

　それから、U19セルに次の関数を入力してから、U19セルをコピーして、U20以下に貼り付けますと、U列にフリガナが自動的に表示されます。

=PHONETIC(INDEX($R:$R,ROW()))

　但し、R列の元データを入力する際に、コピー&貼付けで、どこかからコピーして来た文字列を貼り付けた場合には、フリガナは表示されない様ですので、一旦、姓名(漢字)に入力した文字列を選択してから変換し直す必要がある様です。
　又、希にフリガナを間違える事もある様ですから、その場合には、その行だけ、関数を削除して、フリガナを直接キーボード入力した方が手っ取り早いと思います。（その後、R列の元データを新たに変更する際には、関数が残っている他の行のセルをコピーして、関数を削除したセルに貼り付ければ、関数を復活させる事が出来ます）

お礼

本当に分かりやすい説明有難う御座いました。
とても助かりました。

回答No.7

tom04

No.3・6です！
たびたびごめんなさい。

＞検索する文字列をT3・・・　と
表示行番号を見逃していました。

数式が若干変わってきます。

作業列Z19セルに
=IF(OR($Q$3="",$T$3=""),"",IF(ISNUMBER(FIND($T$3,OFFSET($Q19,,MATCH($Q$3,$R$18:$W$18,0)))),ROW(A1),""))
として348行目までコピー！

Q7セルに
=IF(COUNT($Z$19:$Z$348)<ROW(A1),"",INDEX($Q$19:$W$348,SMALL($Z$19:$Z$348,ROW(A1)),MATCH(Q$6,$Q$18:$W$18,0)))
という数式を一旦貼り付けます。

このQ7セルを選択　→　右クリック　→　コピー　
として、残りのR7・U7・W7各セルに貼り付け！
（R7セルはオートフィルのコピーでも構いません）

これらを16行目までオートフィルでそれぞれコピーすれば、お望みの表示にならないでしょうか？

何度も失礼しました。m(_ _)m

お礼

バッチリできました。
有難う御座います。

回答No.6

tom04

No.3です！

要望を読ませていただきました。
もう一度画像をアップします。（小さくて見えにくいと思いますので、画面を拡大して確認してください）

作業列Z19セルに
=IF(COUNTBLANK($Q$3:$R$3),"",IF(ISNUMBER(FIND($R$3,OFFSET($Q19,,MATCH($Q$3,$R$18:$W$18,0)))),ROW(A1),""))
という数式を入れオートフィルでZ348セルまでオートフィルでコピー！

C6セル（行番号が違っていたら表示したいセル）に
=IF(COUNT($Z$19:$Z$348)<ROW(A1),"",INDEX($Q$19:$W$348,SMALL($Z$19:$Z$348,ROW(A1)),MATCH(Q$5,$Q$18:$W$18,0)))

（数式を入れるセルの1行上のセルに項目名が必要です）

通常はこれを列方向・行方向にオートフィルでコピー！なのですが、
今回は表示したい列が飛び飛びのようですので、
この数式をそのまま　コピー　→　貼り付け
と各セル（列ごと）に行います。
そして各列とも10行分オートフィルで下へコピーすると
画像のような感じになります。

※　検索フィールドのQ3セルには表の項目（画像では「姓名」・「フリガナ」・・・）
のように表の項目と同じ文字列を入力しないと、正しい表示にならないと思います。

以上、参考になれば良いのですが・・・m(_ _)m

お礼

なんども回答頂き、有難う御座います。
皆さんのお陰で、本当に助かりました。

回答No.5

kagakusuki

苗字だけではなく、名前や住所に関しても、特定の文字列を含んでいるものを抽出する事が出来る方法です。

　今仮に、Sheet1の
A1セルに　　顧客番号
B1セルに　　苗字
C1セルに　　名前
D1セルに　　住所
と入力されていて、A列～D列の2行目以下に、各元データが入力されているものし、
Sheet3のA列を作業列として使用して、Sheet2のA列～D列の6行目以下に、抽出結果を表示するものとします。

　まず、Sheet1のA1～D1の範囲をコピーして、Sheet2のA5～D5の範囲に貼り付けて下さい。
　次に、Sheet2の
A1セルに　　分類
A3セルに　　キーワード
と入力して下さい。
　次に、以下の操作を行って、Sheet2のB1セルに入力規則のドロップダウンリストを設定して下さい。
Sheet2のB1セルを選択
　　↓
メニューの[データ]ボタンをクリック
　　↓
現れた選択肢の中にある[入力規則]をクリック
　　↓
現れた「データの入力規則」ダイアログボックスの[設定]タグをクリック
　　↓
「入力値の種類」欄をクリックし、現れた選択肢の中にある[リスト]をクリック
　　↓
「元の値」欄に

=$B$5:$D$5

と入力
　　↓
「データの入力規則」ウィンドウの[OK]ボタンをクリック

　これで入力規則の設定は完了で、次に、Sheet3のA2セルに次の数式を入力して下さい。

=IF(COUNTIF(Sheet1!$B$1:$D$1,Sheet2!$B$1),IF(COUNTIF(INDEX(Sheet1!$B:$D,ROW(),MATCH(Sheet2!$B$1,Sheet1!$B$1:$D$1,0)),Sheet2!$B$3),ROW(),""),"")

　次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に貼り付けて下さい。
　次に、Sheet2のA6セルに次の数式を入力して下さい。

=IF(ROWS($6:6)>COUNT(Sheet3!$A:$A),"",INDEX(Sheet1!A:A,SMALL(Sheet3!$A:$A,ROWS($6:6))))

　次に、Sheet2のA6セルをコピーして、Sheet2のA6～D15の範囲に貼り付けて下さい。

　以上で準備は完了で、後は、Sheet2のB1セルを選択すると現れる▼マークのボタンをクリックして、現れた選択肢の中から、苗字の検索を行う場合には「苗字」、名前の検索を行う場合には「名前」、住所の検索を行う場合には「住所」を選択して下さい。
　そして、例えば、「苗字」を選択してから、
苗字が「田中」となっているデータを抽出する場合には、Sheet2のB1セルに

田中

「山」という文字列から始まっている苗字のデータを抽出する場合には、Sheet2のB1セルに

山*

苗字の何処かに「山」という文字列が含まれているデータを抽出する場合には、Sheet2のB1セルに

*山*

と入力して下さい。（*で囲む文字列は複数であっても構いません）
　すると、Sheet2のA列～D列の6行目以下に、10人分の抽出結果が元データの上にある方から順番に表示されます。（Sheet2のA6セルをコピー&貼付けする範囲を、もっと下の行にまで延長すれば、10人以上を表示させる事も出来ます）

お礼

回答有難う御座います。
やりたいことはピッタリです。
VBAを使わなくても、こんなに立派な検索機能を
設けることが出来るとは思いませんでした。

あと例として質問させて頂いたのですが、思ってた
以上に複雑な関数を組み合わせているので、私には
解読出来ませんでした・・・そこで「No.3」の方には
実際のこちらの環境を追記させて頂きました。
その内容で、こちらの回答の内容を反映させた数式を
作成していただく事は可能でしょうか？？
お手間を取らせますが、是非宜しく御願い致します。

回答No.4

MackyNo1

元データがSheet1にあり、検索したいフィールド名がA1、部分検索文字列がB1セルに入力されているなら、以下の式を入力して下方向に10個オートフィルしてください。

=INDEX(INDEX(Sheet1!$A:$D,,MATCH($A$1,Sheet1!$A$1:$D$1,)),SMALL(INDEX(ISERR(FIND($A$2,INDEX(Sheet1!$A$1:$D$1000,,MATCH($A$1,Sheet1!$A$1:$D$1,))))*1000+ROW($A$1:$A$1000),),ROW(A1)))&""

お礼

回答有難う御座います。
数式が横に長すぎて途中で画面に表示出来てなく
試すことが出来ませんでした。

また宜しく御願い致します。

回答No.3

tom04

こんばんは！
一例です。

Sheet1のデータをSheet2に表示するようにしてみました。

↓の画像（小さくて見にくいかもしれません）のように
Sheet2のA1セルに検索したい文字を入力するとします。
Sheet1に作業用の列を1列設けます。

作業列E2セルに
=IF(Sheet2!$A$1="","",IF(ISNUMBER(FIND(Sheet2!$A$1,B2)),ROW(),""))
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

そしてSheet2のA4セルに
=IF(COUNT(Sheet1!$E:$E)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$E:$E,ROW(A1))))
という数式を入れ、列方向と行方向にオートフィルでコピー！
（行方向は10行だけコピー）

これで画像のような感じになります。

以上、参考になれば良いのですが、
ご希望の方法と違っていたらごめんなさいね。m(_ _)m

お礼

有難う御座います。
やりたいこととピッタリです。

あともう少し教えて欲しいことが有ります。
質問には例としてあげましたが、実際は
・検索したいフィールドをQ3
　　　姓名(漢字)、姓名(フリガナ)、住所で検索したい。
・検索する文字列をT3（例で言うと「田中」）
・顧客リストはヘッダー無しで
　　　Q19からQ348までが顧客番号
　　　R19からR348までが姓名（漢字）
　　　U19からU348までが姓名（フリガナ）
　　　W19からW348までが住所
　　　Z19からZ348までが作業列
・検索結果は
　　　Q7からQ16までが顧客番号
　　　R7からR16までが姓名（漢字）
　　　U7からU16までが姓名（漢字）
　　　W7からW7までが住所

※全て同一のシート内です。

お手数ですが、この内容で数式を作っては頂けませんか？
宜しく御願い致します。

回答No.2

keithin

シート２の検索対象リストのＡ列に一列追加して
Ａ１：対象者
Ｂ１：顧客番号
Ｃ１：名字
Ｄ１：名前
Ｅ１：住所
とします

Ａ２セルに
=IF((COUNTA(Sheet1!$A$1:$B$1)=2)*ISNUMBER(FIND(Sheet1!B$1,HLOOKUP(Sheet1!$A$1,$A:$E,ROW(),FALSE))),MAX(A$1:A1)+1,"")
と記入し，リストの下端までコピー貼り付けておきます

シート１の
Ａ２以下に１から１０までの数字を記入しておきます
Ｂ２には
=VLOOKUP($A2,Sheet2!$A:$E,COLUMN())
と記入して，右に下にコピーしておきます。

Ａ１に「名字」や「名前」と記入，Ｂ１に検索したい言葉を記入します。

お礼

回答有難う御座います。
書いている内容で試そうとしましたが、
私の方では上手く再現できませんでした・・・
せっかく教えて頂いたのですが力不足で
申し訳御座いません。有難う御座いました。

回答No.1

noname#204879

「曖昧な検索をして」ということは、苗字が、例えば、“山田中”や“田中山”があった場合は、何れも「苗字が”田中”の人」、および、「苗字に”山”がつく人」に該当するのでしょうか？

お礼

はい。そのとおりです。