INDEX関数で住所の途中から抽出する方法

質問

アスタリスクを使用しindex関数で抽出したいです

宜しくお願い致します。

相談内容：
シート参照元にAddressが内容ののデータがあります。
シート参照先に参照元のAddressのデータの住所が"千代田区"のデータだけを絶対参照などを応用し、抜き出し・展開したいです。

その他状況：
=IFERROR(INDEX(参照元!B:B,SMALL(IF(MID(参照元!$B:$B,FIND(参照元!$B:$B,$A$1),LEN($A$1))=$A$1,ROW(B:B),""),ROW(B1))),"")
で抜き出そうとしましたが、何も表示されません。

https://okwave.jp/qa/q9362227.html
質問No.9362227
で県名を抜き出す方法を教えていただいていますが、今回は住所の途中の名称で抜き出したいです。

宜しくお願い致します。

回答No.3

bunjii · ベストアンサー

>アスタリスクを使用しindex関数で抽出したいです
参照元のB列に参照先の$A$1が含まれている行を配列で取得したいと言うことでしょうか？
提示の数式では質問の要素である「アスタリスクを使用」になっていません。
また、前回の質問でベストアンサーの回答の数式を変形したことによる見込み違いかと思われます。
他人が組み立てた数式を知識が無いのに改変すると今の状況になります。
他人が組んだ数式は論理を完全に読み取れないときは参考にする程度にして新たに自分の知識範囲で組み立てることをお薦めします。

>シート参照先に参照元のAddressのデータの住所が"千代田区"のデータだけを絶対参照などを応用し、抜き出し・展開したいです。
「千代田区」はどのセルに入力されていますか？
文字列の比較でワイルドカード（*や?）を使える手法以外に含まれるか否かを調べる方法があります。
その方法がFIND関数を使うのですが提示の数式では目的の値を得られません。
参照元のB列からMID関数で文字列を任意に切り出そうとしているFIND関数が全行でエラーを起こしています。
FIND(参照元!$B:$B,$A$1)　→　すべてが検出できずに#VALUEとなる。
従って、次のように修正すると目的に合うでしょう。
IF(MID(参照元!$B:$B,FIND(参照元!$B:$B,$A$1),LEN($A$1))=$A$1,ROW(B:B),"")
　　　　　　　　　　↓
IF(FIND($A$1,参照元!$B:$B&A1)<LEN(参照元!$B:$B),ROW(B:B),"")
尚、セル範囲を無駄に大きくすると再計算に大きな負担が掛かり動作が鈍くなります。（B:Bや$B:$Bの部分は行番号を付加して有効範囲にすべきです）

お礼

>参照元のB列に参照先の$A$1が含まれている行を配列で取得したいと言うことでしょうか？
はいそうです。

>提示の数式では質問の要素である「アスタリスクを使用」になっていません。
ご指摘のとおりです。聞く立場ですので、まとめて質問すべきでした。

>「千代田区」はどのセルに入力されていますか？
B列の各行です。

>従って、次のように修正すると目的に合うでしょう。
>IF(MID(参照元!$B:$B,FIND(参照元!$B:$B,$A$1),LEN($A$1))=$A$1,ROW(B:B),"")
>　　　　　　　　　　↓
>IF(FIND($A$1,参照元!$B:$B&A1)<LEN(参照元!$B:$B),ROW(B:B),"")
できました！
ちょっと邪道えはありますが、Index関数の検索範囲を各列毎に手入力して、
希望通りに再現することが出来ました。

ありがとうございます！！

回答No.5

bunjii

回答No.3の追加です。
ヒントだけでは解決しないようですから参照先!B2に設定する数式を提示します。
=IFERROR(INDEX(参照元!B$1:B$10,LARGE(INDEX((FIND($A$1,参照元!$B$1:$B$10&$A$1)<LEN(参照元!$B$1:$B$10))*ROW(参照元!B$1:B$10),0),COUNTIF(参照元!$B$1:$B$10,"*"&$A$1&"*")+1-ROWS(B$2:B2))),"")
この数式は配列を扱っていますが、内側のINDEX関数を使うことで数式の確定にはEnterキーのみの打鍵で問題ありません。
但し、参照元の行数を10にしてありますので実際の行数に合わせて変更してください。
参照先!A1セルの文字列が参照元!B1:B10に含まれる行番号を配列で返し、返された行番号が0でないものを小さい順に使って参照元の目的の列から値を抽出しています。
従って、参照先!A1が「東京都」でも「千代田区」でも抽出可能です。
参照先!B2セルの数式を右と下へ必要数コピーしてください。

お礼

bunjiiさん
締め切り後に画像まで付けて御指導ありがとうございます。
２８列、６００行にして応用しても再現することができました。
毎週仕事で使いますので、大変助かります。
ありがとうございます。

回答No.4

Chiquilin

初心者の多くが抽出を数式でやりたがるのは 手持ちの知識の延
長線上に答えがないと不安だからでしょうか……

数式でも無理すればできますけど そもそも無理する必要がない
ように思います。 配列計算の仕組みもろくに理解していないな
ら猶更です。 数式でやりたいならせめて今ある数式を正確に理
解することくらいはすべきかと思います。

フィルタでもいいですが データベースクエリを覚えた方がいい
と思います。パラメータを使えば 数式と使用感は変わりません。

お礼

データ→データの取得→クエリエディターの起動→新しいソース→Excelファイルで取り込むと、フィルタリングに似たような機能が・・・
こんなのあるんですね
パラメーターの管理のところの設定の仕方はよくわからないのですが
これってもしかして、一回一回そうさしなくても自動でデータを取り込んでくれる機能でしょうか
超便利そうです
ありがとうございます！！

回答No.2

msMike

ちょっと斜め読みしただけだが、
書かれた式中の部分「FIND(参照元!$B:$B,$A$1)」
ソレ、順序がアベコベとチャイますか？此れ以上は勘弁！
ヘルプ曰く「FIND(find_text, within_text)」

お礼

ご指摘のとおり、FIND関数は最初に検索文字を入力し、次の引数に対象範囲を指定になっていました。
ありがとうございます。

回答No.1

dynam

参照元シートの住所カラムのデータを先頭から最終レコードまで検索して千代田区を含むものだけを参照先シートに転記するということならば、関数ではなくてexcel vbaで行わないと難しいと思いますが…

補足

関数の限界でここでVBAになるでしょうか。
他の方もアドバイスいただいておりますので、そちらも検証して決めたいと思います。
ありがとうございます。