解決済み

エクセルで内線表検索

  • 暇なときにでも
  • 質問No.5370229
  • 閲覧数1543
  • ありがとう数6
  • 気になる数0
  • 回答数4
  • コメント数0

お礼率 65% (269/411)

社員数が多いため複数行で作成されている内線表から、名称で内線を検索する方法を教えていただけないでしょうか。
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 : :
通報する
  • 回答数4
  • 気になる
    質問をブックマークします。
    マイページでまとめて確認できます。

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

  • 回答No.4

ベストアンサー率 42% (1835/4283)

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

お礼率 65% (269/411)

お礼が遅れて大変申し訳ございませんでした。

うゎ~すごい式ですね。
試させていただきましたら、ずばりです。
こんなに長い式を考えていただけるなんて、かなりの時間と能力を費やしていただいたことと思いますが、大変ありがとうございます。

単に式をまねるのではなく、式の意味を理解して、今後は自分でも周りの方の役に立てるよう頑張ってみます。
投稿日時 - 2009-10-27 23:07:56
OKWAVE 20th Be MORE ありがとうをカタチに

その他の回答 (全3件)

  • 回答No.3

ベストアンサー率 49% (2537/5118)

こんにちは!
参考になるかどうか判りませんが・・・
↓の画像のように表を作ってみました。

同じ内線番号を使っている方が複数いるみたいなので
その人数分だけ作業用の列が必要になります。
(今回の質問では二人なので作業列を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

お礼率 65% (269/411)

お礼が遅れて大変申し訳ございませんでした。

そうですよね、私も複数の検索欄を考えてはみたのですが、複数の欄を設けるならば、普通に検索した方が速いので、やはり1っ箇所での検索を試みたかったんです。
投稿日時 - 2009-10-27 23:05:13
  • 回答No.2

ベストアンサー率 40% (705/1742)

C1セルに
=INDEX(B4:B6,MATCH("* "&B1&" *"," "&C4:C6&" ",0))
[Ctrl]+[Shift] +[Enter] で確定、配列数式です({}で囲まれる)

「鈴木一郎」と「鈴木一郎太」が区別されなくて良いなら
=INDEX(B4:B6,MATCH("*"&B1&"*",C4:C6,0))

>(3) 同姓の方が居ない場合は名字だけでも検索可能
は まず無理。
↓苗字があるのかわかりませんが
坂上(さかがみ)、坂上(さかのうえ)、坂上田 なんてのがあったらアウト
判別付きません
お礼コメント
usami33

お礼率 65% (269/411)

お礼が遅れて大変申し訳ございませんでした。

一列なら私もできたんですが、複数行にわたって記載されていたので、関数の組み合わせをする段階で挫折しておりました。
投稿日時 - 2009-10-27 23:03:21
  • 回答No.1

ベストアンサー率 41% (563/1347)

普通の検索で探すのでは駄目なんでしょうか

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

お礼率 65% (269/411)

お礼が遅れて大変申し訳ございませんでした。

私も同じところで苦戦していました。
投稿日時 - 2009-10-27 23:01:27
このQ&Aのテーマ
このQ&Aで解決しましたか?
AIエージェント「あい」

こんにちは。AIエージェントの「あい」です。
あなたの悩みに、OKWAVE 3,500万件のQ&Aを分析して最適な回答をご提案します。

関連するQ&A
このやり方知ってる!同じこと困ったことある。経験を教えて!
このQ&Aにはまだコメントがありません。
あなたの思ったこと、知っていることをここにコメントしてみましょう。

その他の関連するQ&A、テーマをキーワードで探す

キーワードでQ&A、テーマを検索する

特集


より良い社会へ。感謝経済プロジェクト始動

ピックアップ

ページ先頭へ