よろしくお願いします。
データリストに
東京都三鷹市
東京都武蔵野市
東京都港区
福岡県福岡市
福岡県北九州市
があり、この中から、三鷹市と武蔵野市を含むデータを抽出したいと思います。
フィルタの検索条件では、複数条件(三鷹市または武蔵野市を含む)での検索ができず、Vlookupも一つの条件でしか検索できないとネットで読みました。
実際は抽出条件がもっと多くあり(政令指定都市+首都など)、データのフィルタなどで
一つづつvlookupやフィルタやっていくことは非現実的です。
複数の文字リスト(三鷹市、武蔵野市、西東京市など)のどれかを含む列を抽出するにはどうしたらいいでしょうか?
教えてください。よろしくお願いします。
No.1
- 回答日時:
今仮に、
東京都三鷹市
東京都武蔵野市
東京都港区
福岡県福岡市
福岡県北九州市
等の住所がSheet1のA2以下に入力されていて、Sheet1のB列~C列には、住所以外のデータが入力されているものとします。
尚、Sheet1の1行目は、「住所」といった文字列等の、その列に入力されているデータの項目名が入力されているものとします。
そして、Sheet2のA1~A5の範囲内に、
東京都三鷹市
東京都武蔵野市
東京都西東京市
・
・
・
という具合に、抽出条件となる住所が入力されていて(Sheet2のA1~A5の範囲内には空欄のセルもあり)、Sheet2のC列~E列に、抽出結果を表示するものとします。
又、Sheet3のA列を作業列として使用するものとします。
まず、Sheet3のA2セルに次の数式を入力して下さい。
=IF(INDEX(Sheet1!$A:$A,ROW())="","",IF(SUMPRODUCT(ISNUMBER(FIND(CHAR(1)&Sheet2!$A$1:$A$5,CHAR(1)&INDEX(Sheet1!$A:$A,ROW())))*(Sheet2!$A$1:$A$5<>"")),ROW(),""))
次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に貼り付けて下さい。
次に、Sheet2のC1セルに次の数式を入力して下さい。
=Sheet1!$A$1
次に、Sheet2のD1セルに次の数式を入力して下さい。
=Sheet1!$B$1
次に、Sheet2のE1セルに次の数式を入力して下さい。
=Sheet1!$C$1
次に、Sheet2のC2セルに次の数式を入力して下さい。
=IF(ISERROR(1/(INDEX(Sheet1!$A:$C,SMALL(Sheet3!$A:$A,ROWS($2:2)),MATCH(C$1,Sheet1!$A$1:$C$1,0))<>"")),"",INDEX(Sheet1!$A:$C,SMALL(Sheet3!$A:$A,ROWS($2:2)),MATCH(C$1,Sheet1!$A$1:$C$1,0)))
次に、Sheet2のC2セルをコピーして、Sheet2のD2~E2の範囲に貼り付けて下さい。
次に、Sheet2のC2~E2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。
そして最後に、Sheet2のA1~A5の範囲に、抽出条件となる住所を、都道府県名から入力して下さい。
以上で準備は完了で、後はSheet1の2行目以下に、各データを入力するだけで、Sheet2のC列~E列に抽出したデータが表示されます。
No.3
- 回答日時:
三鷹市と武蔵野市のどちらかを含む場合の抽出でしたら「並べ替えとフィルター」から「フィルター」を選択します。
その後に「フィルター」の▼印をクリックして「テキストフィルター」から「指定の値を含む」で表示される画面の上の窓には三鷹市、ORを選択してから下の窓には武蔵野市を入力してOKすればよいでしょう。
3つ以上に検索項目が増える場合には上記の方法では対応することができませんので次のようにすればよいでしょう。
例えばシート1のA2セルから下方に住所が入力されているとします。
作業列を使って対応します。
なお、SUMPRODUCT関数などを使う方法はデータが多くなった場合や検索項目が多くなった場合には計算の速度も遅くなります。出来るだけ簡単な式で対応することです。
お求めの抽出結果をシート2に表示させるとしてシート2のA2セルから横方向に検索したい文字列を入力します。
例えばシート2のA2セルに三鷹市、B2セルに武蔵野市、C2セルに西東京市・・・・
ここではD2セルまでにデータが入力される場合にも対応できる方法を示します。
シート1ではA2セルから下方に住所が入力されG列まで他の項目列が有り利用されているとします。
H列を作業列としてH2セルには次の式を入力して下方にドラッグコピーします。
=IF(A2="","",IF(AND(Sheet2!$A$2<>"",ISNUMBER(FIND(Sheet2!$A$2,A2))),MAX(H$1:H1)+1,0)+IF(AND(Sheet2!$B$2<>"",ISNUMBER(FIND(Sheet2!$B$2,A2))),MAX(H$1:H1)+1,0)+IF(AND(Sheet2!$C$2<>"",ISNUMBER(FIND(Sheet2!$C$2,A2))),MAX(H$1:H1)+1,0)+IF(AND(Sheet2!$D$2<>"",ISNUMBER(FIND(Sheet2!$D$2,A2))),MAX(H$1:H1)+1,0))
シート2のA4セルからG4セルにはシート1の1行目の項目名をコピーして貼り付けます。
シート2のA5セルには次の式を入力してG5セルまでドラッグコピーしたのちに下方にもドラッグコピーします。
=IF(ROW(A1)>MAX(Sheet1!$H:$H),"",IF(INDEX(Sheet1!$A:$G,MATCH(ROW(A1),Sheet1!$H:$H,0),COLUMN(A1))="","",INDEX(Sheet1!$A:$G,MATCH(ROW(A1),Sheet1!$H:$H,0),COLUMN(A1))))
No.4
- 回答日時:
>…とネットで読みました。
んと? ネットは読んだけどご自分のエクセルは見なかったのですか?
フィルタで「または」も出来ますよ。
でも
>複数の文字リスト(三鷹市、武蔵野市、西東京市など)のどれかを含む列を抽出するには
絞り込みが沢山あるのでしたら、
A列に住所があるとして
=IF(OR(ISNUMBER(FIND({"三鷹市","武蔵野市","西東京市"},A1))),"HIT","")
以下コピー
とでも入れてずらりコピーしておき、こちらでオートフィルタしてもらった方が簡単です。{ }の中に、必要なだけ追記してください。
No.5ベストアンサー
- 回答日時:
こんにちは!
一例です。
↓の画像のようにSheet1のデータをSheet2に表示するようにしてみました。
Sheet1(別Sheetでも構いません)に検索データを入力する表を作成しておきます。
今回はSheet1のF1~F10セルに検索データを入力するとします。
空白セルは参照しないようにします。
そのため、画像のアスタリスクはまず使用することはないであろう文字として入力しています。
作業用の列D2セルに
=IF(OR(ISNUMBER(FIND(F$1:F$10,A2))),ROW(),"")
これは配列数式になりますので、Shift+Ctrl+Enterで確定!
この画面からコピー&ペーストする場合はD2セルに貼り付け後、数式バー内で一度クリック!
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定!
数式の前後に{ }マークが入り配列数式になります。
D2セルをオートフィルでずぃ~~~!っと下へコピーしておきます。
そしてSheet2のA2セル(配列数式ではありません)に
=IFERROR(INDEX(Sheet1!A:A,SMALL(Sheet1!$D:$D,ROW(A1))),"")
という数式を入れ、列・行方向にオートフィルでコピー!
これで画像のような感じになります。
尚、今後検索データが増える場合は「*」部分に検索データを入れるだけでSheet2に表示されます。m(_ _)m
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 【Excel】住所に郵便番号を付記する方法 3 2022/05/07 17:15
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Excel(エクセル) ExcelのVLOOKUP関数 7 2022/08/23 06:46
- 転職 長く続けられる好条件の求人でしょうか? 3 2023/07/12 18:45
- その他(国内) 北九州市と川崎市はどちらが都会ですか。 9 2022/10/10 12:40
- 企画・マーケティング 例えば、東京都下の26市にドーム球場を作るとします。 どの辺に作れば、最も高い経済効果がありますか? 2 2023/04/20 08:08
- 関東 ※東北の人からのにわか質問で不快に来たりムカついたらすいません、悪気はありません。 東京方面に住む人 8 2022/08/03 18:17
- その他(国内) 3時!夜中なのか早朝なのか微妙な時間、5時に出るならシャワー浴びてお茶淹れて神棚、仏壇 2 2022/05/20 03:58
- 電車・路線・地下鉄 【空想】学研都市線に新快速を作るとしたら 4 2022/06/26 21:42
- 地域研究 日本の三大都市は、東京と大阪と、もう一つはどこ? 五大都市だと、東京、大阪と、残り三都市は福岡、名古 5 2022/07/15 09:43
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
MicrosoftOfficeの1ユーザー2...
-
MicrosoftOffice2019なんですが、
-
Excel 日付を比較したら、同じ...
-
Microsoft365の「お支払いを更...
-
ウィンドウィズ メモ帳で日付だ...
-
Excelで空白以外の値がある列の...
-
エクセルのシフト表を簡単にGoo...
-
理由を教えてください。
-
VBA
-
web上にあるエクセルをショート...
-
バソコンが二台とも壊れ後換装...
-
【マクロ】文字を1文字づつ、...
-
Excelのセルの重複チェックが出...
-
マイクロソフト 一時使用コード...
-
office365って抵抗感ないですか?
-
Outlook 電源OFFの受診の仕方
-
エクセルで例えば、A1に㈱ベ...
-
自分の専門分野の仕事。初見で...
-
excelの画面のグリッド線の消滅。
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Office 2021 Professional Plus...
-
エクセルで例えば、関数を使っ...
-
Microsoft Formsの「個人情報や...
-
1つのPCに「Excel 2010」「Exc...
-
エクセルで例えば、A1に㈱ベ...
-
理由を教えてください。
-
エクセルでXLOOKUP関数...
-
マイクロソフト オフィスについて
-
VLOOKUP関数について
-
teams設定教えて下さい。 ①ビデ...
-
Googleのスプレッドシートでシ...
-
【Excel VBA】PDFを作成して,...
-
Microsoft365で写真をアルバム...
-
Outlook で宛先が複数の場合の人数
-
Excel テーブル内の空白行の削除
おすすめ情報