エクセル関数で下記表の電話番号(A)と同じものを検索して住所(B)を導き出す際、電話番号(A)のセルに空白があるとうまく検索できません。
空白を無視するまたはセル内の文字列のみを対象にして検索できませんか。
シート1
A B C
03-3333-**** 東京都 犬
045-642-**** 神奈川県 ねこ
03-1111-**** 東京都 兎
シート2
A B
03-3333-**** VLOOKUP(A1.シート1A1:A3,2) =東京都
045-642-****□□ VLOOKUP(A2.シート1A1:A3,2) =エラー
←A2の欄にある資料から電話番語をコピーすると、番号の後に
空白スペースがコピーされてしまうためエラーが出てしまう。
※空白を無視して検索することはできませんか。
わかりにくい質問で申し訳ありません。
No.2ベストアンサー
- 回答日時:
尚、やむを得ない事情により、空白スペースを削除する訳には行かない場合において、もし、Sheet1のA列の方に入力されている電話番号には空白スペースが入っていないという事でしたら、次の様な方法もあります。
【空白スペースが電話番号の前か後ろのみに付いているだけで、電話番号の途中に空白スペースが挟まっている事は無い場合】
=VLOOKUP(TRIM($A1),Sheet1!$A:$A,2,FALSE)
【電話番号の途中に空白スペースが挟まっている事もある場合】
=VLOOKUP(SUBSTITUTE(SUBSTITUTE($A1," ",)," ",),Sheet1!$A:$A,2,FALSE)
尚、もしも、空白スペースが入っている理由が、単に「電話番号がセル内に表示される位置を左詰めや中央揃えにしたい」というだけの事である場合には、それはやむ得ない事情とは言えません。
その様な場合には、空白スペースを削除してしまった上で、セルの書式設定の配置を[左詰め]や[中央揃え]にするのが一般的なやり方です。
No.6
- 回答日時:
もし、空白スペースが電話番号の後ろにのみ付いていて、電話番号の途中に空白スペースが挟まっている事は無い場合には、次の様な方法もあります。
=VLOOKUP(TRIM($A1)&IF(COUNTIF(Sheet1!$A:$A,TRIM($A1)&" *")," *",IF(COUNTIF(Sheet1!$A:$A,TRIM($A1)&" *")," *","")),Sheet1!$A:$C,2,FALSE)
No.5
- 回答日時:
尚、Sheet1のA列に入力されている電話番号の中にも空白スペースが含まれているものがあり、尚且つ、処理が重くなる事は避けたい、という場合には、関数を使用して一旦、別の列に空白スペースを削除した電話番号を表示させておき、その新たに表示させた空白スペース抜きの電話番号を使用して、検索を行う様にします。
この様に、検索等の情報処理が行い易い様にするために使用する列の事を作業列と言います。
今仮に、Sheet3のA列を作業列として使用するものします。
まず、Sheet3のA1セルに次の様な関数を入力して下さい。
=SUBSTITUTE(SUBSTITUTE(INDEX(Sheet1!$A:$A,ROW())," ",)," ",)
次に、Sheet3のA1セルをコピーして、Sheet3のA2以下に(Sheet1において電話番号の表として使用されている最下段の行を上回るのに十分な行数となるまで)貼り付けて下さい。
その上で、Sheet2のB1セルには次の様な関数を入力して下さい。
=INDEX(Sheet1!$A:$C,MATCH(SUBSTITUTE(SUBSTITUTE($A1," ",)," ",),Sheet3!$A:$A,0),2)
No.4
- 回答日時:
配列数式は処理が重くなってしまうため、前に投稿した回答No.1や回答No.2では御勧めしなかったのですが、もし、配列数式の様な処理が重くなってしまう方法でも構わないという場合には、SUMPRODUCT関数を使用するという方法もあります。
(こちらは、配列数式ではなく通常の関数ですので、「Ctrl+Shift+Enter」は不要です)
【空白スペースが電話番号の前か後ろのみに付いているだけで、電話番号の途中に空白スペースが挟まっている事は無い場合】
=INDEX(Sheet1!$A:$C,SUMPRODUCT((TRIM(Sheet1!$A$1:$A$3)=TRIM($A1))*ROW(Sheet1!$A$1:$A$3)),2)
【電話番号の途中に空白スペースが挟まっている事もある場合】
=INDEX(Sheet1!$A:$C,SUMPRODUCT((SUBSTITUTE(SUBSTITUTE(Sheet1!$A$1:$A$3," ",)," ",)=SUBSTITUTE(SUBSTITUTE($A1," ",)," ",))*ROW(Sheet1!$A$1:$A$3)),2)
No.3
- 回答日時:
>A2の欄にある資料から電話番語をコピーすると、番号の後に空白スペースがコピーされてしまうためエラーが出てしまう。
>※空白を無視して検索することはできませんか。検索値の前後に空白(半角/全角)があるときはTRIM関数で両端の空白を除けば正常に検索できます。
=VLOOKUP(TRIM(A1),シート1!A$1:A$3,2) → 東京都
=VLOOKUP(TRIM(A2),シート1!A$1:A$3,2) → 神奈川県
範囲のキー列の文字列の右側に空白があるときはTRIM関数で処理しなくても無視されます。
左側に空白があるときはTRIM関数で処理する必要があります。
=VLOOKUP(TRIM(A1),TRIM(シート1!A$1:A$3),2) → 東京都
この時、TRIM関数の返り値は配列値を必要とするため、式を入力後Ctrl+Shift+Enterで確定します。
尚、シート名とセルの間に ! を忘れないようにしてください。
また、式を他のセルへコピーするときはセルのアドレスを絶対アドレスと相対アドレスを使い分けるようにしてください。
今回の例では範囲のA1:A3をA$1:A$3とすることで下へコピーしても範囲が固定されますので正確に処理されます。
No.1
- 回答日時:
空白スペースが入っている事が問題になっているのでしたら、A列に入力されている空白スペースを全て削除してしまえば良いと思います。
その際、一々、1セル毎に削除して行ったのでは手間が掛かり過ぎますので、次の様な操作を行う事で、A列に入力されている空白スペースを一括して削除されては如何でしょうか。
A列全体をまとめて範囲選択
↓
[Ctrl]キーを押しながら[H]キーを押す
↓
現れた「検索と置換」ダイアログボックスの[置換]タブをクリック
↓
「検索する文字列」欄に半角の空白スペースを1文字だけ入力
↓
「置換後の文字列」欄には(空白スペースも含めて)何も入力されていない状態にする
↓
「検索と置換」ダイアログボックスの[すべて置換]ボタンをクリック
↓
「検索する文字列」欄に全角の空白スペースを1文字だけ入力
↓
「置換後の文字列」欄には(空白スペースも含めて)何も入力されていない状態にする
↓
「検索と置換」ダイアログボックスの[すべて置換]ボタンをクリック
↓
「検索と置換」ダイアログボックスの[閉じる]ボタンをクリック
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBA 改行コードの取り方 1 2022/03/22 14:14
- Excel(エクセル) ユーザー定義について質問です。 2 2023/06/28 13:21
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- Excel(エクセル) Excel_マクロ_複数のシートのVLOOKUPで表示された#N/A以外に色付けをしたいです 1 2023/02/16 22:37
- Visual Basic(VBA) セルに抜けた番号の代わりに空白を挿入する 4 2023/04/10 20:29
- Excel(エクセル) IFERROR、SMALL関数について 2 2022/08/22 23:40
- Excel(エクセル) xlDownの使い分けについての質問です vbaでxlDownを使って一覧近い空白までのセルをコピー 3 2022/08/04 12:20
- Excel(エクセル) 指定した語句と隣のセルを空欄にする 1 2023/06/18 12:54
- Visual Basic(VBA) ExcelVBAのマクロについて。 9 2022/05/04 14:50
- Excel(エクセル) Excelの関数についての質問です。(vlookup関数) A列 B列. C 1 大阪 50. 検索 6 2023/08/11 13:35
このQ&Aを見た人はこんなQ&Aも見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
VLOOKUPのあいまい検索(スペース入り)
その他(パソコン・スマホ・電化製品)
-
EXCEL95 VLOOKUP関数で半角と全角
Excel(エクセル)
-
リストと一致する値のセルを塗りつぶしたい。
その他(Microsoft Office)
-
-
4
数式による空白を無視して最終行を取得するマクロ
Excel(エクセル)
-
5
エクセルVBAで5行目からオートフィルタモードに設定したいたい
Excel(エクセル)
-
6
エクセル:マクロ「Application.CutCopyMode = False」って?
Excel(エクセル)
-
7
エクセルマクロ 【空白セルを無視する方法を教えてください】
Excel(エクセル)
-
8
最新の日付をVLOOKUP
Word(ワード)
-
9
exeファイルの中身を見ることは可能ですか?
フリーソフト
-
10
複数の条件に合う行番号を取得するには
その他(Microsoft Office)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
絶対にいけるWindowsXPsp3の無...
-
Windows XP SP3のインストール...
-
アウトルックを「タスクバー」...
-
シムズ4(PS4版) マイライブラリ...
-
WindowsXPを最近のパソコンに...
-
パソコンが操作できなくなりま...
-
Windows xpにWindows10を無理や...
-
プロダクトキー
-
Windows10 32ビットから64ビッ...
-
Windows11のPCの、ショートカッ...
-
パソコンでCDを見たい、見る...
-
WindowsXPのプロダクトキーを紛...
-
メモリ、SSD、HDDの違いを教え...
-
HDDがフォーマット出来なくなった
-
アカウント名とユーザー名との...
-
最近、パソコンを起動するとこ...
-
フリープリントソフト「かんた...
-
win10の立ち上がりが」非常に遅...
-
パソコンで一定の文字数を打つ...
-
平仮名変換
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
絶対にいけるWindowsXPsp3の無...
-
Windows XP SP3のインストール...
-
WindowsXPを最近のパソコンに...
-
アウトルックを「タスクバー」...
-
シムズ4(PS4版) マイライブラリ...
-
ピクチャーが無くなった
-
Windows11のPCの、ショートカッ...
-
セーフモード解除
-
パソコンでCDを見たい、見る...
-
WindowsXPのプロダクトキーを紛...
-
やっぱり現代のPCにWindowsXPを...
-
最近、パソコンを起動するとこ...
-
メモリ、SSD、HDDの違いを教え...
-
ラズパイでwindows XPは動くのか
-
Windows xpにWindows10を無理や...
-
パソコン起動時に2つのユーザー...
-
XPが起動しない、バックアップ...
-
アカウント名とユーザー名との...
-
プロダクトキー
-
フリープリントソフト「かんた...
おすすめ情報