[[20070919152707]]　『検索について』（パンダ）　ページの最後に飛ぶ

『検索について』（パンダ）

 Excel2000　OS　Xp
検索で同じ姓の人が複数の場合の検索で姓のみで名がわからない場合の検索で関数はあるのでしょうか？
エクセルで「編集」の「検索と置換」で「ブック」の「すべて検索」の要領ですが。

 例
　　　A　　　　B　　　　　　　C
１　斉藤照　さいとう　あきら　123
２　斉藤学　さいとう　まなぶ　233
３　斉藤勉　さいとう　つとむ　244
４　齋藤照　さいとう　あきら　115

 斉藤で検索すると斉藤照、斉藤学、斉藤勉が標示される、仮名で検索の場合、全て表示される、この質問で判るでしょうか。よろしくお願いいたします。

 「検索」ってどういう結果が欲しいのですか？
 数を数えたいならCOUNTIFで「"斉藤*"」数えられると思います

 >斉藤で検索すると斉藤照、斉藤学、斉藤勉が標示される
 表示されるとおっしゃられていますので、抽出したいのでしょうか？
 「オートフィルタ」や「フィルタオプションの設定」では、ダメなのですか？

 （e1L）オートフィルタ
http://www.excel.studio-kazu.jp/lib/e1L/e1L.html

 (Ohagi)

ごめんなさい、シートを串刺しした常態を説明していませんでした、オートフィルタの機能でシートを串刺しと言ったら良いのでしょうか、たとえばシート１、２とあったとして会社の各部署の氏名、電話番号が入っているものをシート１、２の書式は触らず別シートに検索結果を抽出したいのですが。

 抽出だけにこだわらなければ、ピボットテーブルを利用してはいかがですか？

 (Ohagi)

ごめんなさい、抽出にこだわり関数で複数のシートから入力した文字を抽出してくれるやりかたです。

「>抽出だけにこだわらなければ」

 お知りになりたい内容は表示されると思いますけど？

 （e2d）ピボットテーブル入門
http://www.excel.studio-kazu.jp/lib/e2d/e2d.html

 (Ohagi)

 要するに「検索画面」の専用シートを作って、
 キーワードを入力したら、ヒットしたものと
 それに関連した（ヒット行の全部の）データが表示されればいいのですよね。
 ピボットテーブルだのオートフィルターだのを使わずに、
 デザインされた画面で結果がズラ～っと出てくればいいのでしょう？

 簡単です。
 ①先ず、参照するシートのレコードとフィールドがどうなっているのかを
 　教えてください。
 　縦方向に１列目には何が記入されていますか?
 　縦方向に２列目には何が記入されていますか?
 　縦方向に３列目には何が記入されていますか?
 　縦方向に４列目には何が記入されていますか?
 　このくらいでよいでしょう。たとえのサンプルではなく、
 　実際にあるデータのリストを出した方が良いですよ。
 ②次にキーワードの項目はどれとどれなのでしょうか？
 　縦方向の１列目の項目ですか？
 　縦方向に２列目の項目ですか?
 　縦方向に３列目の項目ですか?
 　縦方向に４列目の項目ですか?

 VLOOKUP()関数だとヒットした最初のものしか得られませんね。
 このときのヒットしたデータの行が「何行目」かを得られればシメタもの。
 次はその行より下の行を検索すればよいのです。
 キーワードを入力したら、そのワードの両端に「＊」がつくように別のセルを用意
 すると、ワイルドカード機能が使えます。「藤」と入力したら「*藤*」となるように。
 =TEXT("*",0)&$D$2&TEXT("*",0) で。$D$2はキーワード入力セルです。
 この方法だと「斉藤」「斎藤」「近藤」「藤井」がヒットします。
 もちろん「斉藤」と入力すれば、「斎藤」ヒットせずに「斉藤」のみがヒットします。

 =MATCH()関数と、=OFFSET()関数を使うとうまくいきますよ。

 【Match(Offset())】

　　　A　　　　B　　　　　C　　　　D

 １　氏名　　フリ仮名　　部署　　電話番号
 ２　斉藤勉　ｻｲﾄｳﾂﾄﾑ　　会計　　３３４２
 ３　斉藤勉　ｻｲﾄｳﾂﾄﾑ　　総務　　２２１３
 ４　斎藤敦　ｻｲﾄｳｱﾂｼ　　営業　　４３２１
 ５　伊藤弘　ｲﾄｳﾋﾛｼ　　 総務　　２２１５
 こんな感じでAorB若しくはCで検索しA,B,C,Dのデータ(データは出来れば複数シート）が
 抽出されるようにしたいのです。
 Aの斉藤であれば２、３のデータ（氏名、フリ仮名、部署、電話番号）が抽出され、
 Bのｻｲﾄｳであれば２、３、４が、
 Cの総務であれば３、５と言う様に、
 Dの電話番号でも同じことが言えるのですが、
 よろしくお願いいたします。

 ごめんなさい。読みやすくするために、改行の編集をしました。
 各行の先頭には半角のスペースを入れ、一行の文字数も全角４５文字くらいに
 します。自分のネームをお忘れなく。最後に----と記入します。
 【Match(Offset())】

 ということで、本題に入ります。【Match(Offset())】
 解決へのアプローチは決して１通りではなく、
 いろいろな工夫と自分なりの作法があります。
 ここでは、そのうちのもっとも単純な方法を紹介します。
 A列からD列まで入力されたデータのSheet名を「リスト」としましょうか。
 各行は"レコード"といいますね。各列は"フィールド"と呼ばれます。
 今回は新たにフィールドを追加します。E列に「合成データ」という項目を設定します。
 もちろん自動的にデータが合成されるようにします。
　　　A　　　　B　　　　　C　　　　D           E
 １　氏名　　フリ仮名　　部署　　電話番号   合成データ
 ２　斉藤勉　ｻｲﾄｳﾂﾄﾑ　　会計　　３３４２   斉藤勉ｻｲﾄｳﾂﾄﾑ会計３３４２
 ３　斉藤勉　ｻｲﾄｳﾂﾄﾑ　　総務　　２２１３   斉藤勉ｻｲﾄｳﾂﾄﾑ総務２２１３
 ４　斎藤敦　ｻｲﾄｳｱﾂｼ　　営業　　４３２１   斎藤敦ｻｲﾄｳｱﾂｼ営業４３２１
 ５　伊藤弘　ｲﾄｳﾋﾛｼ　　 総務　　２２１５   伊藤弘ｲﾄｳﾋﾛｼ総務２２１５

 E列の各セルには
 =IF(AND($A2="",$B2=""),"",TEXT($A2,"0")&TEXT($B2,"0")&TEXT($C2,"0")&TEXT($D2,"0"))
 という風に文字列を合成してくれる数式を入れます。この数式はE2セルの記入例です。
 "氏名"と"フリ仮名"が入力されていないならば、まだブランクのままにしようという
 一種のエラー処理のようなものを組み込んでいます。
 今回は、このE列の内容を検索して、ヒットしたら、A列・B列・C列・D列のデータ内容を
 プレゼンしてくれるという方法なのです。

 別のSheetにSheet名「検索画面」とつけて、検索するための機能をレイアウトします。
 たとえば、
 B1セルに検索キーワードを入力するとして、その結果、A1セルにワイルドカードを
 使った合成キーワードが自動的に作られるようにします。

      A           ↓ここの改行は関係なし　           　B     　　　　　C          D       E   
 1  =IF($B$1="","",　　　　　　　　　　　　　　　　　　検索キーワード            
        TEXT("*","0")&TEXT($B$1,"0")&TEXT("*","0"))    の入力セル
 2 「レコードの行」と記入表示。右セルも同様に記入。   「氏名」　　　「フリ仮名」「部署」「電話」
 3
 4
 5
 とりあえずここまでやってください。長くなりすぎましたので。

 エラー処理がうまく実行されるかを検証するのに、手間取りました。【Match(Offset())】

 続いてA3セルへ記入する数式です。
 「検索画面」SheetのA3セルには、
 「リスト」Sheet上で最初にヒットしたデータの「記入行」が表示されようにします。
 つまり、何行目のデータに最初の検索キーワードが含まれている、と教えてくれます。
 該当するものが無い場合は、”該当なし”と返してくれます。
 =IF($A$1="","",IF(ISERROR(MATCH($A$1,リスト!$E$1:$E$65536,0)),"該当なし"MATCH($A$1,リスト!$E$1:$E$65536,0)))

 B3セルへ記入する数式は、
 =IF($A$1="","",IF(ISERROR(OFFSET(リスト!$A$1,$A3-1,0,1,1)),"該当なし",OFFSET(リスト!$A$1,$A3-1,0,1,1)))

 C3セルへ記入する数式は、
 =IF($A$1="","",IF(ISERROR(OFFSET(リスト!$B$1,$A3-1,0,1,1)),"該当なし",OFFSET(リスト!$B$1,$A3-1,0,1,1)))

 D3セルへ記入する数式は、
 =IF($A$1="","",IF(ISERROR(OFFSET(リスト!$C$1,$A3-1,0,1,1)),"該当なし",OFFSET(リスト!$C$1,$A3-1,0,1,1)))

 E3セルへ記入する数式は、
 =IF($A$1="","",IF(ISERROR(OFFSET(リスト!$D$1,$A3-1,0,1,1)),"該当なし",OFFSET(リスト!$D$1,$A3-1,0,1,1)))

 です。これで検索の最初の結果が得られます。
 キーワードが入力されていない場合は、ブランクのままです。
 検索の結果、何も該当するものが無ければ、「該当なし」と表示されます。
 検索の第２の結果を得られるようにするには、次回の書き込みを参考にしてください。

 では、検索の第２の結果を得られるようにします。【Match(Offset())】
 A4セルに書き込む数式は、
 =IF($A$1="","",IF($A$3="該当なし","",IF(ISERROR(MATCH($A$1,OFFSET(リスト!$E$1,$A3,0,1,1):$E$65536,0)+$A3),"",MATCH($A$1,OFFSET(リスト!$E$1,$A3,0,1,1):$E$65536,0)+$A3)))

 B4セルに書き込む数式は、
 =IF($A$1="","",IF(ISERROR(OFFSET(リスト!$A$1,$A4-1,0,1,1)),"",OFFSET(リスト!$A$1,$A4-1,0,1,1)))

 C4セルに書き込む数式は、
 =IF($A$1="","",IF(ISERROR(OFFSET(リスト!$B$1,$A4-1,0,1,1)),"",OFFSET(リスト!$B$1,$A4-1,0,1,1)))

 D4セルに書き込む数式は、
 =IF($A$1="","",IF(ISERROR(OFFSET(リスト!$C$1,$A4-1,0,1,1)),"",OFFSET(リスト!$C$1,$A4-1,0,1,1)))

 E4セルに書き込む数式は、
 =IF($A$1="","",IF(ISERROR(OFFSET(リスト!$D$1,$A4-1,0,1,1)),"",OFFSET(リスト!$D$1,$A4-1,0,1,1)))

 と、なります。検索の結果が１つしかない場合は、ブランクとなります。
 では、検索の第３の結果、第４の結果・・・を得て、表示させる方法を、また次回に。

 【Match(Offset())】です。
 最後の仕上げはとても簡単です。それぞれの列下へコピーすればよいのです。

 A4セルの数式を↓    B4セルの数式を↓    C4セルの数式を↓    D4セルの数式を↓    E4セルの数式を↓
 A5セルへコピー↓    B5セルへコピー↓    C5セルへコピー↓    D5セルへコピー↓    E5セルへコピー↓
 A6セルへコピー↓    B6セルへコピー↓    C6セルへコピー↓    D6セルへコピー↓    E6セルへコピー↓
 A7セルへコピー↓    B7セルへコピー↓    C7セルへコピー↓    D7セルへコピー↓    E7セルへコピー↓
      ↓                 ↓                 ↓                 ↓                 ↓
      ↓                 ↓                 ↓                 ↓                 ↓

 どこまでコピーするかはあなたのお好み次第です。検索の結果をいくつくらいまで求めるかによります。
 個人名をキーワードにするならば、検索の結果は大した数ではありません。
 しかし、大規模なデータで、組織が大きくて、部署名で検索した場合には、
 そのメンバーをすべて表示させなければなりませんね。
 ま、足りなければ列下へコピーすれば済むことですですケド。

 これであなたは、「リスト」の65536行目までのレコードを検索可能となります。
 EXCEL2007だと100万行以上!?が可能とか。
 自前で作る検索パッケージは、結構、応用が利きますよ。「リスト」の追加や更新が大変ですけれど。
 GOOD LUCK!

 ン？
 > データ(データは出来れば複数シート）が

 データが書き込まれているSheetが複数ある?!
 それらのSheetを統合した「リスト」を作ればいいと思います。
 たとえば、
 統合「リスト」の1000行まではSheet1の
                2000行まではSheet2の
                3000行まではShhet3の データが流し込まれるようにします。

 統合「リスト」の各セルに書き込むは数式は、
 =IF(Sheet1!A2="","",Sheet1!A2)　という具合にします。
 右の方向と下の方向にコピーすれば、データが流し込まれてきますね。

 1001行目から2000行目までは
 =IF(Sheet2!A2="","",Sheet2!A2) ということになりましょう。

 【Match(Offset())】

 ありがとうございます、助かります。教授いただいた中の２つ質問があります、
 １、2 「レコードの行」と記入表示。右セルも同様に記入。の意味が分らないのですが、
 ２、Ｂ１にキーワードを入力するとＡ３レコードに表示されますがＡ４レコード以下に何も表示されません、
 ただしＡ４若しくはＡ５に数字を入力すると正しいデータではありませんが表示されます。
 ご教授よろしくお願いいたします。【パンダ】

 【Match(Offset())】さんが、帰ってこられない様ですね。
 ご本人さんではないので、推測になりますが

 ＞１、2 「レコードの行」と記入表示。右セルも同様に・・・
 これは、２行目は自動生成されないので　事前に見出しを入力しておいて下さい
 と言う事だと思います。
 　A2「レコードの行」
 　B2「氏名」　　　
 　C2「フリ仮名」
 　D2「部署」
 　E2「電話」

 ＞２、Ｂ１にキーワードを入力するとＡ３レコードに表示されますが・・・
 これは式中の
 >MATCH($A$1,OFFSET(リスト!$E$1,$A3,0,1,1):$E$65536,0)
             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 この部分の書き方に問題がありそうな気がします。
 該当個所を
 MATCH($A$1,OFFSET(リスト!$E$1,$A3,0,65536-$A3,1),0)
 と変えてみてやってみるとどうなりますかね？

 ちなみに、単なる好みですが・・・・
 「合成データ」の部分の式はそのままでその隣に
 「確認列」として
=IF(COUNTIF(E2,"*"&検索画面!$B$1&"*"),ROW(),"")

 「検索画面」B1に検索値を入れる場合
 2行目は↑とおなじみだし
 A3セルに
=SMALL(リスト!F:F,ROW(A1))
 として、「リスト」シートの行数以上　下にフィルコピー
 B3セルに
=IF(ISERROR($A3),"",INDEX(リスト!$A:$D,$A3,COLUMN(A1))&"")
 として、E列までと「リスト」シートの行数以上　下にフィルコピー

 でもよいかもしれません。

 (HANA)

 有難うございました、大変助かりました、今後の受付業務に役立てたいと思います。
 また疑問があった時は、ご教授お願いいたします。【パンダ】

 【パンダ】さん、ごめんなさい。仕事が忙しくてPCを開いていませんでした。
 (HANA)さん、ありがとうございます。【Match(Offset())】
 パンダさんは、おそらく、一生懸命関数を入れてトライされていたのでしょうね。
 うまくいかなかったら、また書き込んでください。

 MATCH()関数にOFFSET()関数を入れ子構造にするのは、実をいうと
 この「エクセルの学校」で教わったのです。
 以来、私はいろんなリストの検索にこの手法を使っています。
 商品リストで曖昧にキーワードを入れて、
 正確な商品記号、商品コード、価格などを表示させる。
 見積書や請求書の履歴リストから、
 過去に提示したり請求した相手先、商品、商品単価などを表示させる。
 構内電話帳から作ったリストを使って、独自の検索パケを作る。
 などなど。 ぜひ自分のモノにしてください。
 日本人の名前には、かな併記も必須ですね。
 「ワタナベ」さん、「サイトウ」さんなど、異体漢字の組合せがゴチャマンと
 あるので、漢字検索ではうまくいかないですよね。

 ごめんなさい、過去の質問になりますが、仕事をしていて便利に使わせていただいております、ありがとうございます。
１つ問題が出ました。

 問題とはデータの中に空白があると「0」で返ってきますが、その「0」を表面上消したいのですが、
条件付書式で「=MOD(ROW(),2)=0」１行ごとに色をつけているため、
条件付書式で「0」の時、セルに色の付かないセルはいいのですが、
色の付くセルは文字色が変わらないため「0」が見えてしまいます、
よろしくお願いいたします。（パンダ）

 どの式を使っておられますか？
 おそらく全て文字列だと思いますので
 式中の適切な場所に「&""」をつければ良いと思いますが・・・。

 或いはセルの書式設定で「0;0;;@」とか・・・。

 (HANA)

 A2に=IF($B$2="","",TEXT("*","0")&TEXT($B$2,"0")&TEXT("*","0"))
A5に=IF($B$2="","",IF(ISERROR(SMALL(リスト!G:G,ROW(A1))),"",SMALL(リスト!G:G,ROW(A1))))
B5に=IF($A5="","",INDEX(リスト!$A:$F,$A5,COLUMN(A5))&"")
C5に=IF($A$2="","",IF(ISERROR(OFFSET(リスト!$B$1,$A5-1,0,1,1)),"該当なし",OFFSET(リスト!$B$1,$A5-1,0,1,1)))
D5に=IF($A$2="","",IF(ISERROR(OFFSET(リスト!$C$1,$A5-1,0,1,1)),"該当なし",OFFSET(リスト!$C$1,$A5-1,0,1,1)))
E5に=IF($A$2="","",IF(ISERROR(OFFSET(リスト!$D$1,$A5-1,0,1,1)),"該当なし",OFFSET(リスト!$D$1,$A5-1,0,1,1)))
F5に=IF($A$2="","",IF(ISERROR(OFFSET(リスト!$E$1,$A5-1,0,1,1)),"該当なし",OFFSET(リスト!$E$1,$A5-1,0,1,1)))

 A6に=IF($B$2="","",IF(ISERROR(SMALL(リスト!G:G,ROW(A2))),"",SMALL(リスト!G:G,ROW(A2))))
B6に=IF($A6="","",INDEX(リスト!$A:$F,$A6,COLUMN(A6))&"")
C6に=IF($A$2="","",IF(ISERROR(OFFSET(リスト!$B$1,$A6-1,0,1,1)),"",OFFSET(リスト!$B$1,$A6-1,0,1,1)))
D6に=IF($A$2="","",IF(ISERROR(OFFSET(リスト!$C$1,$A6-1,0,1,1)),"",OFFSET(リスト!$C$1,$A6-1,0,1,1)))
E6に=IF($A$2="","",IF(ISERROR(OFFSET(リスト!$D$1,$A6-1,0,1,1)),"",OFFSET(リスト!$D$1,$A6-1,0,1,1)))
F6に=IF($A$2="","",IF(ISERROR(OFFSET(リスト!$E$1,$A6-1,0,1,1)),"",OFFSET(リスト!$E$1,$A6-1,0,1,1)))
でA6以降はコピーです。

シート「リスト」は
A2、B2、C2、D2、E2は各名前等のデータで
F2に=IF(AND($A2="",$B2=""),"",TEXT($A2,"0")&TEXT($B2,"0")&TEXT($C2,"0")&TEXT($D2,"0")&TEXT($E2,"0"))
G2に=IF(COUNTIF(F2,"*"&検索画面!$B$2&"*"&検索画面!$C$2&"*"&検索画面!$D$2&"*"&検索画面!$E$2&"*"&検索画面!$F$2&"*"),ROW(),"")

F2,G2は下にコピーしています。

上記のように構成しています、よろしくお願いいたします。
（パンダ）

 この時のC:Fに「0」が表示されるのですか？
 6行目の式は、5行目の式を下に一つコピーした物ですよね？
 B5に入れてある式をF5迄フィルドラッグして
 B5:F5を必要行フィルドラッグでは駄目なのですかね？

 ＞ B3セルに
 ＞=IF(ISERROR($A3),"",INDEX(リスト!$A:$D,$A3,COLUMN(A1))&"")
 ＞ として、E列までと「リスト」シートの行数以上　下にフィルコピー
 と書いたのですが・・・？

 駄目な場合は、B列とそれ以降の列とで何が違うのかもう一度教えて下さい。
（上を読み返すのはちょっと大変です。
　私の記憶が合っていれば　参照列以外の違いは無いと思うのですが・・・。）

 もしかして、B列に入れた式でも「0」が返される？

 (HANA)

 ご迷惑をおかけいたしました、「0;0;;@」で解決いたしました。
ありがとうございました。
（パンダ）

コメント返信：

[ 一覧(最新更新順) ]

[[20070919152707]] 『検索について』（パンダ） ページの最後に飛ぶ

[[20070919152707]]　『検索について』（パンダ）　ページの最後に飛ぶ