社員数が多いため複数行で作成されている内線表から、名称で内線を検索する方法を教えていただけないでしょうか。
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 : :
No.2
- 回答日時:
C1セルに
=INDEX(B4:B6,MATCH("* "&B1&" *"," "&C4:C6&" ",0))
[Ctrl]+[Shift] +[Enter] で確定、配列数式です({}で囲まれる)
「鈴木一郎」と「鈴木一郎太」が区別されなくて良いなら
=INDEX(B4:B6,MATCH("*"&B1&"*",C4:C6,0))
>(3) 同姓の方が居ない場合は名字だけでも検索可能
は まず無理。
↓苗字があるのかわかりませんが
坂上(さかがみ)、坂上(さかのうえ)、坂上田 なんてのがあったらアウト
判別付きません
お礼が遅れて大変申し訳ございませんでした。
一列なら私もできたんですが、複数行にわたって記載されていたので、関数の組み合わせをする段階で挫折しておりました。
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
お礼が遅れて大変申し訳ございませんでした。
そうですよね、私も複数の検索欄を考えてはみたのですが、複数の欄を設けるならば、普通に検索した方が速いので、やはり1っ箇所での検索を試みたかったんです。
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セルに名字を入力することになりますが、名字だけでは別の性の方がいるかもしれませんね。少なくともダブる可能性を持つ入力の仕方は問題がありますね。ダブらないようにする工夫が必要でしょう。
お礼が遅れて大変申し訳ございませんでした。
うゎ~すごい式ですね。
試させていただきましたら、ずばりです。
こんなに長い式を考えていただけるなんて、かなりの時間と能力を費やしていただいたことと思いますが、大変ありがとうございます。
単に式をまねるのではなく、式の意味を理解して、今後は自分でも周りの方の役に立てるよう頑張ってみます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- Excel(エクセル) ExcelのVLOOKUP関数 7 2022/08/23 06:46
- Windows 10 Windows10 ファイル名と期間でファイルを検索するにはどうすればいいか 3 2023/06/07 14:01
- Web・クリエイティブ 「東京メトロ 丸の内線」のカラーコードが複数ある理由は何ですか? 色が途中で変わったのですか? 2 2022/05/10 20:47
- Excel(エクセル) 至急です><Excelの関数を教えてください。 2 2022/03/22 17:56
- その他(Microsoft Office) Outlookメール 連絡先の検索について 〈 ご説明 〉 Windows PC の Outlook 1 2022/09/23 14:43
- Visual Basic(VBA) 列と行の名前(重複あり)が交差するセルに、データを入力したい 2 2022/06/25 22:42
- Excel(エクセル) エクセルの数式で教えてください。 2 2023/02/10 17:07
- 格安スマホ・SIMフリースマホ GooglePixelで端末内のアプリを検索する方法を教えてください。 アプリ一覧に表示される検索ボ 2 2023/05/15 00:55
- Visual Basic(VBA) ユーザーフォーム「frm_基本❶」を立ち上げると新規で入力する行数を右下のNoとして表示しています。 1 2023/03/16 19:02
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルでの作業計算方法について
-
Microsoft1Officeの互換ソフト...
-
【マクロ】その時、その時で変...
-
はがきについて。
-
【マクロ】読取専用のファイル...
-
エクセル初心者です 関数の入れ...
-
【関数】適切な文字数の数字を...
-
LOOKUP関数を使えばいいのでし...
-
【関数】先頭だけにある、半角...
-
Excel ピボットテーブルで日付...
-
Excelのpivotについて質問です
-
時間によってファイル名が変わ...
-
エクセル 白黒印刷で白線を印刷...
-
Aというブックの1というシート...
-
エクセル関数を教えてください
-
WPS OFFICEでの縦書きについて
-
Excelのチェックボックスの使い...
-
エクセルの条件付き書式につい...
-
エクセルのセルに同じ大きさの...
-
エクセルの関数について教えて...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報