• ベストアンサー

エクセルで内線表検索

社員数が多いため複数行で作成されている内線表から、名称で内線を検索する方法を教えていただけないでしょうか。 VLOOKUPの組み合わなのかと思うのですが・・・ 内線表は印刷しても良いように部署/内線/名前が複数行で記載されております。 名前は氏名で記載されており、複数名で内線を使用する場合は スペース区切りで複数名の名前が記載されております。 氏と名は区切りなく連続で記載されております。 希望 (1) B1に氏名を入力するとC1に該当者の内線を表示 (2) 検索範囲はシート内の全データ(複数行で記載されているため) (3) 同姓の方が居ない場合は名字だけでも検索可能   A   B   C       D   E  F 1    検索名 検索結果 2 3 部署 内線 名前      部署 内線 名前 4 ABC 100 Aさん Bさん BCD 200 Cさん 5 ABC 101 Dさん  BCD 201 Eさん Fさん 6 ABC 102 Gさん Hさん BCD 202 Iさん 7 : : 8 : :

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

  • ベストアンサー
  • KURUMITO
  • ベストアンサー率42% (1835/4283)
回答No.4

C1セルには次の式を入力します。 =IF(COUNTIF(C3:C1000,"*"&B1&"*")=0,"",OFFSET(INDEX(A:I,MATCH("*"&B1&"*",C3:C1000,0)+2,COLUMN(C1)),0,-1))&IF(COUNTIF(F3:F1000,"*"&B1&"*")=0,"",OFFSET(INDEX(A:I,MATCH("*"&B1&"*",F3:F1000,0)+2,COLUMN(F1)),0,-1))&IF(COUNTIF(I3:I1000,"*"&B1&"*")=0,"",OFFSET(INDEX(A:I,MATCH("*"&B1&"*",I3:I1000,0)+2,COLUMN(I1)),0,-1)) この式では行が1000行まで列がI列までの表に対応しています。適宜変形してください。 なお、式ではB1セルに入力された文字をあるセルの位置から内線を求める式となっています。同姓がいない場合には名字だけでも検索可能とはどういうことでしょう。名字だけしか入力されていないということでしょうか? その場合にはB1セルに名字を入力することになりますが、名字だけでは別の性の方がいるかもしれませんね。少なくともダブる可能性を持つ入力の仕方は問題がありますね。ダブらないようにする工夫が必要でしょう。

usami33
質問者

お礼

お礼が遅れて大変申し訳ございませんでした。 うゎ~すごい式ですね。 試させていただきましたら、ずばりです。 こんなに長い式を考えていただけるなんて、かなりの時間と能力を費やしていただいたことと思いますが、大変ありがとうございます。 単に式をまねるのではなく、式の意味を理解して、今後は自分でも周りの方の役に立てるよう頑張ってみます。

その他の回答 (3)

  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.3

こんにちは! 参考になるかどうか判りませんが・・・ ↓の画像のように表を作ってみました。 同じ内線番号を使っている方が複数いるみたいなので その人数分だけ作業用の列が必要になります。 (今回の質問では二人なので作業列を2列にしています) そして、内線番号だけ表示したのではどこの部署の○○さんか判らないとおもいますので、 部署も一緒に表示させるようにしています。 表のC2セルに氏名の一部でも入力するとその文字列が含まれている人をピックアップさせるようにしてみました。 作業列のA7セルに =IF($C$2="","",IF(COUNTIF(E7:F7,"*"&$C$2&"*"),ROW(A1),"")) B7セルに =IF($C$2="","",IF(COUNTIF(I7:J7,"*"&$C$2&"*"),ROW(A1),"")) として、A7・B7セルを範囲指定し、B7セルのフィルハンドルでオートフィルで下へコピーします。 (数式が100行まで対応できるようにしていますので、100行目くらいまでコピーしても構いません) 次に D2セルに =IF(COUNT($A$7:$A$100)>=ROW(A1),INDEX($D$7:$D$100,SMALL($A$7:$A$100,ROW(A1))),"") E2セルに =IF(D2="","",INDEX($C$7:$C$100,SMALL($A$7:$A$100,ROW(A1)))) F2セルに =IF($C$2="","",IF(COUNT($B$7:$B$100)>=ROW(A1),INDEX($H$7:$H$100,SMALL($B$7:$B$100,ROW(A1))),"")) G2セルに =IF(F2="","",INDEX($G$7:$G$100,SMALL($B$7:$B$100,ROW(A1)))) という数式を入れ、D2~G2セルを範囲指定し、G2セルのフィルハンドルで下へコピーすると画像のような感じになります。 これでC2セルに入力文字が増えていけば絞り込めるはずです。 本来であれば同じ項目データは列方向に複数あるよりも、行方向にあった方がもっと簡単なのですが、 条件として最初から列方向に複数ある!ということなので こんな感じに考えてみました。 以上、参考になれば幸いですが、 他に良い方法があったり、的外れなら読み流してくださいね。 どうも長々と失礼しました。m(__)m

usami33
質問者

お礼

お礼が遅れて大変申し訳ございませんでした。 そうですよね、私も複数の検索欄を考えてはみたのですが、複数の欄を設けるならば、普通に検索した方が速いので、やはり1っ箇所での検索を試みたかったんです。

回答No.2

C1セルに =INDEX(B4:B6,MATCH("* "&B1&" *"," "&C4:C6&" ",0)) [Ctrl]+[Shift] +[Enter] で確定、配列数式です({}で囲まれる) 「鈴木一郎」と「鈴木一郎太」が区別されなくて良いなら =INDEX(B4:B6,MATCH("*"&B1&"*",C4:C6,0)) >(3) 同姓の方が居ない場合は名字だけでも検索可能 は まず無理。 ↓苗字があるのかわかりませんが 坂上(さかがみ)、坂上(さかのうえ)、坂上田 なんてのがあったらアウト 判別付きません

usami33
質問者

お礼

お礼が遅れて大変申し訳ございませんでした。 一列なら私もできたんですが、複数行にわたって記載されていたので、関数の組み合わせをする段階で挫折しておりました。

  • kmetu
  • ベストアンサー率41% (562/1346)
回答No.1

普通の検索で探すのでは駄目なんでしょうか Vlookupだと名前と内線が逆じゃないと駄目ですね。 B列とE列に名前C列とF列に内線番号 名前はB列とE列にしか書かない で C1 と D1 にそれぞれどちらかに結果を出すようにすればいかがでしょう =VLOOKUP(B1,B2:B100,2,FALSE) =VLOOKUP(B1,E2:E100,2,FALSE) 名前は完全一致です。

usami33
質問者

お礼

お礼が遅れて大変申し訳ございませんでした。 私も同じところで苦戦していました。

関連するQ&A

  • EXCELの 編集 検索キーを使わず 検索したい

    EXCELの 編集 検索キーを使わず シート上で検索文字を指定して 検索された 全ての セルを表示したい。  第1表 CODEー名前表 CODEを探す A列 B列 C列 D列 1行 CODE 名前 CODE 2行 1001 あいうえお 1001 3行 1002 ああいうえ 1002 4行 1003 おあいうえ 1003 5行 1004 えおいうえ 1004 6行 1005 うえおあい 1005 7行 1006 うえああい 1006 第1表 CODEー名前表 から”あ”の含まれる全ての 名前 CODEを 第2表みたいに 作りたいのです 第2表 CODEを探す A列 B列 C列 D列 1行 検索する 名前 *あ* 2行 3行 検索された 名前 検索された CODE 4行 あいうえお 1001 5行 ああいうえ 1002 6行 おあいうえ 1003 7行 うえおあい 1005 8行 うえああい 1006 マクロを使わない方法で お願いします。 1:検索された 文字の足し算 2:文字の足し算をするごとに 1行増やす 3:改列 4:SUMPRODUCTで 文字の足し算は出来ますか?

  • エクセルの表で複数の検索で結果を返すには?

    グランドゴルフのスコアー表にチーム名を入力したらチームの名前がチームナンバー通りに出るようにするには、どうしたら良いですか? sheet1 チームのデータ       A      B      C     D 1 チームNo.  チーム名   氏名  生年月日 2  1    なかよし    まり 3  2    なかよし    なな 4  3    なかよし    みみ 5  1    ひまわり   ひろゆき 6  2    ひまわり    こと 7  3    ひまわり    ひな sheet2 スコアー表 1 チーム名  ひまわり           shee2のB1のセルにチーム名を入れたら 2  1                    チームNo.どおりに名前が検索されるようにしたいのですが   3  2                    どのような関数を使うと良いですか? 4  3  

  • エクセルで別ブックを検索するマクロ、VBA

    エクセルで以下の処理を行えるマクロを作成したいです。 当方、マクロについてほとんど知識がありません。 恐縮ですが、教えていただけると嬉しいです。 ・主にしたいこと  [検索]ブックで一致するコードを探して、  [結果]ブックの対応するコードの行にそれぞれの項目を返したい。 ●ブック1 [検索]  シートが12個あります(それぞれ、1、2、3…12というシート名=1~12月分)  ↓各シートの内容    A    B    C    D 1  氏名  数値  コード  内容 2  abc   111  SS1234 あいうえお 3  bcd   123  SS3456 かきくけこ ・ ・ ・ といった感じです。 12個のシートの中身はそれぞれ似たようなものですが、 「コード」や「内容」などは少しずつ違います。 ●ブック2 [結果]  ↓シートの内容    A    B    C    D 1  氏名  コード  内容  数値 2      SS3456 3      SS1234 ・ ・ ・ といった感じです。 (注)検索用ブックとは列の並びが異なっています。 ここでやりたいことの詳細ですが、 ・[結果]ブックの「コード」(B列)にコードを入力すると、  [検索]ブックで一致するコードを検索し、  A列「氏名」、C列「内容」、D列「数値」に、[検索]シートの内容を  自動的に表示させたい。  (ただし[結果]ブックに入力した「コード」は、[検索]ブックの1~12のうち、   どのシートにあるかわからない) ・入力したコードが見つからない場合は何も表示しない。 ということです。 最初VLOOKUP、MATCH等の関数で表示することを考えましたが、 シートが複数にまたがっているのと、 列の並び方が[検索][結果]ブックで違うのでわかりませんでした。 長くなってしまい申し訳ありませんが、どうかおしえてください。 よろしくお願いします。

  • エクセルでの表作成について

    エクセルを使って勤怠一覧を管理しています。 シート1のA1には所属部門名・B1には氏名、そしてC3には残業時間が入力されています。 この一覧をシート2のA2からC2には総務部・残業時間・氏名を表示。 そして、1行空けてE1からG1には営業部・残業時間・氏名を表示するように表を作成したいのですが、エクセルの機能を使って簡単にシート2にこれらの表を作成する事は可能でしょうか。 ご存じの方、いらっしゃればご教授願います。

  • エクセルの表作成

    エクセルの表作成 懇親会開催通知を20の部署に送り、その後返信されるメールをチェックして参加者の集計を頼まれました。 エクセルで表作成し、部署名、○○期、役職、氏名、合計人数、備考欄を作り、 各部署の最終行などに部署の参加人数を入れて一番下にSUMで合計が出るようにしたいのですが・・・ 参加者が各部署によって違うので(だいたい多くて3,4人ほどなのですが)各部署の人数を入れるセルの位置で引っかかっています。そして項目が多いので極端な縦長か横長になってしまいます。 部署を10×2にして同じ表が2つになっても構いません。シンプルな表になるかと思うのですが表作成に慣れていませんのでぜひ教えていただけると助かります。

  • excelで検索用シートを作りたい。

    お世話になります。 excelでわからない事があり、困っています。 検索用シート      データシート    A  B      A    B   C 1 部署名 (空欄)  1 部署名 担当者 書類名 2 担当者 (空欄) 2 総務部 鈴木  新人用の書類 3 書類名 (空欄) 3 人事部 田中  部長の書類 上図の様に検索用シートとデータ用シートが同じBook内にあります。 検索用シートのB列は空欄になっており、例えば(1-B)に検索したい部署名の一部、又は全てを入れると、(2-B)及び(3-B)にその部署名に対応した、担当者、書類名が表示 される。 また、検索結果が複数ある場合には、上のものから順々に検索用シートの空欄に表示される。 部署名での検索以外にも、同様に担当者、書類名でも検索できる。 この様な仕様にしたいのですが、どのすれば良いでしょうか? どうぞよろしくお願い致します。

  • 2つのエクセル表について。

    エクセル表でクラス名、所属クラブ、氏名、住所等が記載された表が2つ(例:エクセル名 秋・冬)あるとします。 秋のエクセル表のAさん、Bさん・・・の所属クラブを変更したら、冬のエクセル表のAさん、Bさん・・・の所属クラブも自動で変更になる関数を教えて下さい。宜しくお願い致します。

  • 複数表から検索値を検索し、検索値の存在した表から結果を求める方法

    ソフトはエクセルの2000です。 やりたいことは、 「複数表から検索値を検索し、検索値の存在した表から値を抽出する方法」もしくは 「A2:H11から検索値を検索し、検索値から○行下の値を抽出する方法」 になると思います。 以下に例を記入します。 下記のように「項目」と「名称」2列で構成された表を下に作成しているとします。       A列   B列    C列   D列   E列    F列   G列   H列 1行 2行 3行   社名 ○○○社       社名 □□□社       社名 ◆◆◆社 4行   場所 ○○○県       場所 □□□県       場所 ◆◆◆県 5行   件名 ○○作業       件名 □□作業       件名 ◆◆作業 6行   担当 ○○○殿       担当 □□□殿       担当 ◆◆◆殿 7行    8行   社名 ×××社       社名 △△△社 9行   場所 ×××県       場所 △△△県 10行   件名 ××作業       件名 △△作業 11行   担当 ×××殿       担当 △△△殿 A1のセルに「△△△社」と社名を入力すると、B1セルに場所の「△△△県」が表示するようにしたいのです。 複数のIF関数で括れば可能だとは思いますが、表の数が制限され、関数式も長くなるためIF関数の使用は控えたいと思っています。 また、マクロを使わない方法でお願いします。 可能なら関数で、関数で不可能なら名前の定義を使用した方法を教えてください。よろしくお願いします。

  • エクセルで複数列の検索をマクロで行いたい

    A列、B列、C列に項目が、D列以降にデータが入っているシートがあります。 具体的には、  A列:商品名  B列:地域名  C列:店舗名 となっていて、ABCの順で昇順にソートがかけられています。 マクロの記録を使って一行だけを検索することは出来たのですが、(Selection.find(What:="商品名"~ となっていました)本当は、"商品名"+"地域名"+"店舗名"が一致するものを検索したいのです。 現在は、一行目で検索をかけて、後はactivecell.offset(*,*).value="地域名"のような感じで、しらみつぶしに探しています。 複数列で検索するよい方法などありましたら教えてください。

  • エクセルの表計算で、検索値が含まれる列を検索後、同じ列の違う行にある数

    エクセルの表計算で、検索値が含まれる列を検索後、同じ列の違う行にある数値を返す場合はどのようにすればいいのでしょうか? ※下表を例としますと、検索値 150 が含まれる1行目のC列を検出後、同じ列の2行目に入力されている 4300 を返す場合の関数式を教えてください。     A      B         C        D 1   1~50  51~100   101~250   251~500 2   5000    4500      4300     4000

専門家に質問してみよう