エクセル2013の関数について

質問

大量の住所録から、一部の文字列を使って検索し対象住所を一覧で表示させることが出来るように関数を組み立てしたいのですが、よくわかりません。どなたかご教授願います。例えば住所録は都道府県から番地まで記載してあります。検索文字列に中央区と入力したら、中央区に該当する住所をすべて一覧として表示させる方法です。ｖｌｏｏｋｕｐなら完全一致しか表示できませんが、文字の一部で検索し対象住所を一覧に表示させたいのです。よろしくお願いします。

回答No.3

mshr1962 · ベストアンサー

=IF(COUNTIF(Sheet1!$C:$C,"*"&$A$1&"*")>=ROW()-1,ROW()-1,"")

IF文の条件部分が　COUNTIF(Sheet1!$C:$C,"*"&$A$1&"*")>=ROW()-1

COUNTIF(Sheet1!$C:$C,"*"&$A$1&"*")　でSheet1の住所上の件数をカウント
ROW()-1は数式のある行の行番号-1で
件数以内の時に数値を表示、件数を超えるとブランクを表示するように設定しています。

COUNTIF(Sheet1!$C:$C,"*"&$A$1&"*")　の部分は MAX(Sheet1!$A:$A） でも代用可能ですが。。。

これで件数分の１から始まる数字がA列に並びます。

=IF(A2="","",VLOOKUP(A2,Sheet1!$A$2:$H$100,行番号,FALSE))

でA列がブランクの時は表示なし、数値の時にVLOOKUP関数で該当行を検索表示という設定です。

お礼

感謝しています。本当に助かりました。有難うございます。ROW関数はイマイチよくわかりませんが、また困ったら質問させてください。感謝感謝感謝！！！

回答No.4

tom04

こんにちは！
すでに解決済みのようですが、一例です。

↓の画像で左側がSheet1で右側のSheet2のA1セルに検索文字を1文字でも入力すると
表示するとします。

画像ではSheet2のA4セルに
=IFERROR(INDEX(Sheet1!C$1:C$1000,SMALL(IF(ISNUMBER(FIND(A$1,Sheet1!C$1:C$1000)),ROW(A$1:A$1000)),ROW(A1))),"")

これは配列数式になってしまいますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　Sheet2のA4セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。

これをフィルハンドルで下へコピー！
これで画像のような感じになります。m(_ _)m

お礼

詳細に教えて頂き、有難うございました。また何かありましたら質問させて頂きます。宜しくお願いします。

回答No.2

mshr1962

＞オートフィルターではなく関数で一覧表を作成したいのです。宜しくお願いします。
作業用の数式列が用意できるなら可能だと思います。
Sheet2!A1が検索文字列のセル、Sheet1のC列が住所の列としてSheet1!A2を

=COUNTIF($C$2:$C2,"*"&Sheet2!$A$1&"*")

として下方にコピーしておきます。

Sheet2!A2に
=IF(COUNTIF(Sheet1!$C:$C,"*"&$A$1&"*")>=ROW()-1,ROW()-1,"")
Sheet2!B2に
=IF(A2="","",VLOOKUP(A2,Sheet1!$A$2:$H$100,行番号,FALSE))

で検索して表示すればOKです。
※数式の範囲や行番号は適切な値に変更してください。

補足

ブラボー！！！！有難うございました。無事に出来ました。大変助かりました。ついでにそれぞれの関数を解説いただけると今後に活かせます。宜しくお願いします。
=COUNTIF($C$2:$C2,"*"&Sheet2!$A$1&"*")
これは検索条件にあうセルを数える関数ですよね。Sheet2の検索文字列に*を入れて一部文字でも検索が出来るようにしておくこと。
=IF(A2="","",VLOOKUP(A2,Sheet1!$A$2:$H$100,行番号,FALSE))
これは調べたい表の中でこの検索文字列がどこの位置にあるのか指示しておいて一致したものを表示させるようにしておくこと。
=IF(COUNTIF(Sheet1!$C:$C,"*"&$A$1&"*")>=ROW()-1,ROW()-1,"")
ここがよくわからないのですが、Ｃ列にある検索文字列該当したものを表示させる関数だと思うのですが、ＲＯＷ関数をどう使ったらよいのか？
>=ROW()-1,ROW()-1,"")ここはサッパリわかりません。
お手数をお掛けしますが、詳細な解説をお願いできますでしょうか。宜しくお願いします。

回答No.1

mshr1962

オートフィルタでは駄目なのでしょうか？
別シートへの抽出なら、フィルタオプションの設定で実行できますけど。。。

http://www4.synapse.ne.jp/yone/excel2013/excel2013_filter.html

補足

早速有難うございます。オートフィルターではなく関数で一覧表を作成したいのです。宜しくお願いします。