[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『下4桁を入力すると該当する氏名を表示したい』（tabotabo）
[電話番号の下4桁を入力すると電話帳のシートから該当する氏名を表示したい]
エクセルは多少使える程度の知識しかありません(早い話が初心者です)。
会社の携帯電話で、送られてくる通話明細書に通話先をいちいち記入しなければなりません。携帯電話の番号検索では非常に手間が掛かるためエクセルの検索&印刷シートを作成し、番号の下4桁を入力すると氏名が表示されるようにしたいのです。
元となるデータは、携帯電話からアドレス帳をCSV形式でPCに読み込み、"外部データの取り込み"で、不要な列を排除した
A列 氏名･B列 電話番号1･C列 電話番号2･D列 電話番号3･E列 電話番号4･F列 電話番号5･G列 ｸﾞﾙｰﾌﾟ番号
のシートを使用します。但し、電話番号のため頭の0を表示したい(別途全桁一致で検索する方法も採りたい)ので、文字列で取り込んでいます。
理解するために、簡単な表で試行錯誤していますが、私の知識では限界を遙かに超えてしまっています(以下の内容です)。
Sheet2に A列 氏名3人分、B列 電話番号1､C列 電話番号2、D列 電話番号3
Sheet1を作業ｼｰﾄとして、A列 検索結果(氏名)、B列 4桁の番号入力欄 を設けます。B列の2行から30行に4桁の番号を入力します。
VLOOKUPでは電話番号の複数列に渡る検索は無理そうなので、ネットで教えていただいた
=INDEX(Sheet2!$A$1:$A$3,SUMPRODUCT((0&RIGHT(Sheet2!$B$1:$D$3,4)=0&A1)*ROW(A$1:$A$3)))
を使ってみました。
ところが、入力ミス(該当する番号が無い場合)しても入力行に対応した氏名が表示されてしまうため、実用的ではありません。該当する番号が無い場合は空欄(若しくは"該当無し"などの文字列)表示する方法と、合わせて該当する番号が複数ある場合にｴﾗｰ(若しくは"複数該当"などの文字列)を表示する方法は無いものでしょうか。
関数の内容が全く理解できていないため、何をどうさわったものか判りません(ｴｸｾﾙﾍﾙﾌﾟは参照するのですが、応用となると理解できません)。
難解な4桁検索ではなく全桁検索として
=INDEX(Sheet2!$A$1:$A$3,SUMPRODUCT((Sheet2!$B$1:$E$3=A1)*ROW(Sheet2!$A$1:$A$3)))
でも同様でした。
できれば数式で解決したいのですが、無理ならばVBAを自分で作成するのは無理ですが、地道に記録するなら可能です。
よろしくご教示下さい。
INDEX関数の引数にSUMPRODUCT関数で算出した数値を用いるのには抵抗があります。
=IF(SUMPRODUCT(--(RIGHT(Sheet2!$B$1:$E$3,4)=TEXT(A1,"0000"))), INDEX(Sheet2!$A$1:$A$3,MIN(INDEX((RIGHT(Sheet2!$B$1:$E$3,4)<>TEXT(A1,"0000"))*9^9+ROW($B$1:$E$3),))),"")
(ROUGE)
横から失礼
MIN(INDEX((RIGHT(Sheet2!$B$1:$E$3,4)<>TEXT(A1,"0000"))*9^9+ROW($B$1:$E$3),))
は
MAX(INDEX((RIGHT(Sheet2!$B$1:$E$3,4)=TEXT(A1,"0000"))*ROW($B$1:$E$3),))
SUMPRODUCT((Sheet2!$B$1:$E$3=A1)*ROW(Sheet2!$A$1:$A$3))) と考え方は変わらんな
(??)
> と考え方は変わらんな SUMPRODUCTの方は、複数該当があった時、とんでもない結果になりそうな気がします。
>複数ある場合にエラー(若しくは"複数該当"などの文字列)を表示する方法 >・・・・ >試行錯誤してみます。色々いじってみないと身に付きませんもんね。 「複数該当」と云っても、「同一人で複数該当」と「複数人で複数該当」とは処理が異なるのでしょうから、 結構面倒な処理になりそうな予感がします。
私は作式する積りはないので、気楽にROMしていますが。 (^^ゞ
(半平太) 2009/10/28 18:21
>> と考え方は変わらんな >SUMPRODUCTの方は、複数該当があった時、とんでもない結果になりそうな気がします。
質問の条件に >複数ある場合にエラー(若しくは"複数該当"などの文字列)を表示する とあり、その後の処理ですから、一つの場合という前提で記載しています。 ただし、 (ROUGE)さんの回答は そのような処理をしていませんが・・・
処理後を前提にしていないと、MAX、MIN関数の結果も違いますので・・・
(??)
> その後の処理ですから、一つの場合という前提で記載しています。
そう云う前提でしたら、おっしゃる通りです。
(半平太) 2009/10/28 18:45
INDEX関数の引数としてSUMPRODUCTを使う方法に抵抗があると掲載しただけなんですが、 なぜわざわざ指摘されたのでしょうか？ 意図を理解できません。。。 (ROUGE)
BOOKも実際を反映するために
Sheet1をマクロボタン設定用の初期画面とし、
Sheet3にアドレスデータ
A列 氏名
B列 電話番号1〜F列 電話番号5
Sheet2を作業シートとして
=IF(SUMPRODUCT((Sheet3!$B$1:$F$1000=B1)*ROW(Sheet3!$A$1:$A$1000))=0,"該当無し",IF(B1="","",INDEX(Sheet3!$A$1:$A$1000,SUMPRODUCT((Sheet3!$B$1:$F$1000=B1)*ROW(Sheet3!$A$1:$A$1000)))))
としました。
今のところ問題なさそうなのですが、何か破綻している部分は無いでしょうか。
こんにちは〜♪
>今のところ問題なさそうなのですが、何か破綻している部分は無いでしょうか。
ということですが。。。
>下4桁検索を取り敢えずあきらめ
すべての番号を入力して検索ということでしょうから。。。
検索結果は、Sheet3のデータに重複がなければ検索出来ると思います。。。
でも私なら、こうするかな〜。。。。♪
SUMPRODUCTは、重い関数ですし この場合のSUMPRODUCTは、無駄な計算をしていると思います。
最初の SUMPRODUCTは、COUNTIFで良さそうですし、 あとの SUMPRODUCTは、MAX で良いと思います。。。
Sheet2のセルへ
=IF(B1="","",IF(COUNTIF(Sheet3!B1:F1000,B1),INDEX(Sheet3!A:A,MAX(INDEX((Sheet3!B1:F1000=B1)*ROW(1:1000),))),"該当無し"))
ご参考にどうぞ。。。
。。。Ms.Rin〜♪♪
こんなこと
=IF(B1="","",IF(COUNTIF(Sheet3!B1:F1000,B1),IF(COUNTIF(Sheet3!B1:F1000,B1)=1,INDEX(Sheet3!A:A,MAX(INDEX((Sheet3!B1:F1000=B1)*ROW(1:1000),))),"複数該当"),"該当無し"))
=IF(B1="","",IF(COUNTIF(Sheet3!B1:F1000,B1),IF(COUNTIF(Sheet3!B1:F1000,B1)=1,INDEX(Sheet3!A:A,SUMPRODUCT((Sheet3!B1:F1000=B1)*ROW(1:1000))),"複数該当"),"該当無し"))
どちらにしても、感心しない式ですね（同じことを何回も繰り返していますね）
SUMPRODUCT関数の使用について好き嫌いがありますが、・・・ (SUMPRODUCTは、重い関数 という思いこみがありそうですね)
(??)
(??)さんのはまだ試してませんけど、電話番号なんで複数該当は入力ミス以外はあり得ないんですよね。
でもでも、"複数該当" ができるなら、あきらめていた4桁検索がむくむくやりたくなってきます。
これから家に帰って試してみます。
(tabotabo)
=IF(B1="","",IF(COUNTIF(Sheet3!B1:F1000,B1),IF(COUNTIF(Sheet3!B1:F1000,B1)=1,INDEX(Sheet3!A:A,MAX(INDEX((Sheet3!B1:F1000=B1)*ROW(1:1000),))),"複数該当"),"該当無し"))
を使って実現致しました。マクロに色々と(CSVﾃﾞｰﾀの取り込みから印刷書式の設定など)組み込んだこともあり、実行が重くなりましたが、まあ月に一度のことですし、今までのことを思えばかなり楽になりますので、このあたりで妥協です。
色々、参考になりました。
(ROUGE)さん、(??)さん、Ms.Rin〜さん (半平太) さん、ご協力感謝致します。
また判らないことが出ましたら、よろしくご教示お願いします。
(tabotabo)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.