- ベストアンサー
エクセルでこんな抽出は可能ですか？
関数を使用して抽出したいと思います。 まずシート１とシート２を使います。 シート２には住所録などの内容が5000件くらいあるものとします。 その住所録には「〒」「住所」「電話番号」「氏名」などのフィールドを作っておきます。 そして、シート１には 例えば「氏名」という欄をA1に書き、B1の空白のセルに「山田」と入力をすると、「山田」が含まれるものをすべて表示したいと思います。 このすべて表示とういうのは「山田」を含む「氏名」だけ表示されるのではなく、「〒」「住所」「電話番号」「氏名」を表示したいのです。 オートフィルタやフィルタオプションは極力使用したくないので、関数で出来ないものかと考え中です。 また、抽出結果を表示する欄はシート１のA3あたりからお願いします。 こんな文書で意味が分かった方、よろしくお願いします
- wave1993
- お礼率55% (81/145)
- オフィス系ソフト
- 回答数4
- ありがとう数3
- みんなの回答 （4）
- 専門家の回答
質問者が選んだベストアンサー
Sheet2で補助セルを使って実現する方法です。 Sheet2の「氏名」列（Ｄ列とします）の右隣列（Ｅ列とします）を補助セルとして使い、E2セルに以下の式を入れます =IF(NOT(ISERROR(FIND(Sheet1!$B$1,Sheet2!D2))),ROW(),"") この式で、検索したい名前があればその行数が表示されます。E3～EnはE2をコピーしてください 次にSheet1でA3に以下の式を入れます。すると氏名が合致した最初の行の〒番号が参照されます。A3の式をA4～Anにコピーすると２番目、３番目も表示されます。 =IF(ISERROR(SMALL(Sheet2!$E$2:$E$5000,ROW()-2)),"",INDIRECT("sheet2!a" & SMALL(Sheet2!$E$2:$E$5000,ROW()-2))) 住所、電話番号、氏名はINDIRECT("sheet2!a"のaをb,c,dに変更すれば参照が可能です。 配列関数を使って補助セルを使用しない方法もありますが、それはご自身で研究して下さい。
その他の回答 (3)
- Wendy02
- ベストアンサー率57% (3570/6232)
こんばんは。 昔、そういうデータベース関数がありましたね。今は、フィルタオプションに替わってしまいました。DGETに似ているけれども、DGET では、ご質問のようなケースではエラーが出ます。 フィルタオプションも、ちょっとした使い方しだいで、関数なみになります。ちょっとした使い方のコツがあるのですが、あまり、最初から拒否されてしまうと、回答側では、どうしようもありません。他にもありますが、それ以上に難しいです。本来は、自分が使う使わないは関係なく、いろんな種類を知っておくと、後々、応用が利くようになると思います。 今回は、配列数式になると思います。（配列の確定はしなくてよいです。)5000件のレベルだと、Version によっては、ちょっと厳しいものがあると思います。 A1： 検索値 を入れます。 B1:～フィルハンドル・コピー =IF(ROW(A1)>COUNTIF(Sheet2!$D$1:$D$50,$A$1),"",INDEX(Sheet2!$A$1:$D$50,SMALL(INDEX((ISERROR(SEARCH($A$1,Sheet2!$D$1:$D$50))=FALSE)*ROW(Sheet2!$D$1:$D$50),,),COUNTA(Sheet2!$D$1:$D$50)-COUNTIF(Sheet2!$D$1:$D$50,$A$1)+ROW(A1)),COLUMN(A1))) 注意：データ範囲は正確に、空白行は入れないでください。 範囲を入れるときは、[編集]－[置換] で入れ替えてください。範囲は、絶対参照がベターです。 必ず、最初のオートフィルでコピーする最初の数式が、上記のものです。 現行の数式は、Sheet2 のD列を検索対象としています。 山田を含む場合は、山田* とA1 に入力してください。
お礼
ありがとうございます。 この関数の方法をいただきます。ちょっとチャレンジしてみます。 オートフィルタやフィルタオプションは拒否しているのではなく、オートフィルタで検索すると、簡単に検索できてしまうのがダメなのです。 これでは個人的に意味を持たないのです・・・ごめんなさい。
- bari_saku
- ベストアンサー率17% (1827/10269)
「“山田”を含むセルの数」でよければ、 データの範囲がA2：D5000とすると、 =COUNTIF(A2:D5000,"*山田*") になると思います。 ただし、一行一件としたい場合、一行に2カ所「山田」があるとダブルカウントしてしまいます。 どこかに「この行に“山田”が含まれるセルがあるか否か」を判断する作業列を作って、その行数を合計するようにすればいいですが、あまりスマートではないですね…
お礼
さっそくありがとうございます。 しかし、個数ではダメなんです。「山田」と入力した時に「山田」の「〒」「住所」「電話番号」「氏名」を表示させたいのです。 「山田」が100人いたら、100件の、「〒」「住所」「電話番号」「氏名」を表示させたいのです・・・
関連するQ&A
- エクセルで、シート1のセルをシート2にコピーしたい
エクセルのシート別のコピーについて教えていただけますでしょうか。 シート1のA列には、下記の通り、氏名、学校、住所、電話番号の順番に、並んでいるとします。 ＜シート1＞ A1 山田太郎 A2 学校 A3 住所 A4 電話番号 A5 山田花子 A6 学校 A7 住所 A8 電話番号 A9 山田みどり A10 学校 A11 住所 A12 電話番号 その情報をシート2の A列に、氏名だけを下記の通り、入力していきたいのです。 ＜シート2＞ A1 山田太郎 A2 山田花子 A3 山田みどり シート別のコピーは、 例えば、シート2のA1に「=Sheet1!A1」 と入力すれば、「山田太郎」と出てくることは知っています。 ですが、シート1の『氏名』だけをシート2に抽出していきたい場合、 どのようにすればよろしいのでしょうか？ 『4セルずつ下』の『氏名』をシート1からシート2へコピーしたいです。 どなたか方法を教えていただけますと、とてもありがたく助かる思いです。 どうぞよろしくお願いいたします！
- ベストアンサー
- Excel（エクセル）
- エクセル 重複データの抽出
エクセルを使って重複データをチェックしようと思っていＣＯＵＮＴＩＦで試したのですがうまくいきません・・・ご存知の方教えてください。m(__)m Ａ Ｂ Ｃ Ｄ Ｅ Ｆ 店番 顧客番号 氏名（カナ） 電話番号 住所1 住所2 という表の電話番号が重複する先を抽出したいのです。データ件数は約3000件ほどあります。できれば重複したデータは別のシートに店番順に表示をしたいのですが関数でできるのでしょうか？
- ベストアンサー
- オフィス系ソフト
- 【EXCEL関数】違うシートに抽出結果を出したい
一覧表から「○」の条件が付いたデータのみを別シートに関数で抽出したいです。 フィルタではなく関数を使用したいのは、抽出したデータから名前の定義を使用して リスト表示をさせたいためです。ご教示のほど、宜しくお願いします。 シート１は一覧表となっており、Ａ列に「○」のついたデータのみをシート２に表示 シート１（一覧表） Ａ Ｂ １ ○ 山田太郎 ２ × 佐藤次郎 ３ ○ 高橋三郎 ４ × 鈴木四郎 ５ × 中村五郎 シート２（抽出結果を表示させる） Ａ Ｂ １ ○ 山田太郎 ２ ○ 高橋三郎 ３ ４ ５ フィルタオプション、当方の考え得る限りの配列関数等、色々試しましたが、 不勉強のために上手く結果が表示されませんでした。 ご教示のほど、宜しくお願い致します。
- ベストアンサー
- Excel（エクセル）
- 【エクセル】無作為抽出
アンケート調査のために無作為抽出をします。 Ａ列に氏名 Ｂ列に住所 が 1万件 ほど入力されています。 そこから300件を抽出したいと思います。 それを 別ブック・別シート もしくは 同シートのＣ列・Ｄ列 にそれぞれ抽出した氏名や住所を表示させるにはどのように したらいいのでしょうか。 なるべくマクロ等は使用したくないのですがご教示くだされば 幸いです。どうぞよろしくお願いいたします。 ＊追伸 =RAND() を使用してオートフィルする方法は理解しています。 Ａ列（氏名）だけを抽出する方法はわかるのですが、それに付随 するＢ列（住所）を同時に抜き出す方法がないのか困っています。
- 締切済み
- オフィス系ソフト
- エクセルで作成した住所録から任意のデータ抽出
まず住所録ですが、形式はＡからＤ列に番号、略号、氏名、住所、行は2行目から最大100行まで同じ形式のものが、10シート以上あります。別形式の証明書用のシートがあり、そこに空欄の氏名と住所が別行にあります。任意の証明書に複数ある住所録から任意の氏名と住所を番号を検索して、1発抽出したいのが今回の質問です。 住所録、証明書用のシートが１つづつならばＶＬＯＯＫＵＰ関数で簡単に抽出できました。 しかし、各々が10シート以上にせざるを得なくなりました。 このような操作の場合、そもそも関数で対応できますか？証明書用シートそれぞれに住所録全部を範囲とすることもできず、困っております。 もし関数でできるならばその方法を教えてください。 ＶＢＡやマクロでないと対応ができないとなるとその知識がないに等しいのでご教授の際は、細かく指示してもらえればありがたいです。 コピペでやれば手間でもできますが、かなりの人数のものが利用しますので必ず氏名と住所を違うものをコピーしてきて貼り付けるのでこの方法は、不採用にしました。 以上、長くなりましたがよろしくご教授お願いいたします。
- 締切済み
- Excel（エクセル）
- エクセル表の値がエラー表示されていないセルだけを別のシートに集計する
新たな職場でエクセルを使うことになり、このような作業ができるのか教えていただきたいです。 ～ワークシート１～元となるデータベース 番号 氏名 住所 ２ Ａさん ○区 ５ Ｂさん □区 １ Ｃさん △区 ※氏名は５０音順、番号は個人判別に使用するもので順不同となっています。 ～ワークシート２～過去から今までに使用したものを管理するシート 番号 氏名 住所 ２ Ａさん ○区 ３ ＃Ｎ／Ａ ＃Ｎ／Ａ ５ Ｂさん □区 ※番号を検索値として氏名・住所をＶＬＯＯＫＵＰで表示しています。過去使用した番号３は元となるワークシートには無いが管理のため入力されエラー値が表示されています。 ～ワークシート３～（今回作りたいもの） 番号 氏名 住所 ２ Ａさん ○区 ５ Ｂさん □区 ※ワークシート２を参照してエラー値では無いセルのみを集計したシートを作成したい。 上記の場合、何らかの関数を使用してワークシート３を作成することは可能でしょうか？ワークシート２にオートフィルタをつけて「＃Ｎ/Ａ」と等しくないものを表示で解決しようと思ったのですが関数結果の表示のためかできませんでした。 どなたかご助言お願い致します。
- ベストアンサー
- オフィス系ソフト
- エクセルの抽出に関するマクロ
(1)OSはVistaです。 (2)エクセル２００７を使用しています。 (3)シートは「会員情報」「抽出条件」「抽出結果」の３枚です。 (4)「会員情報」シートのA5からR588までデータ（氏名、住所、電話番号など）が入っています。A5からR5は、タイトル行（フィールド）です。R列は「フォームコントロール」で挿入したチェックボックスです。。 (5)「抽出条件」シートには、会員の種類による抽出ができるような条件（正会員、準会員、協賛会員など）が設定してあります。 (6)「抽出結果」シートには、抽出された結果がコピーされます。 「会員情報」シートのデータを、「抽出条件」シートで指定した条件で抽出し、「抽出結果」シートにコピーするマクロは成功しました。 チェックボックスにチェックを入れた会員を抽出し、その方たちの郵便用ラベルを作成するのが最終目標ですが、今は「会員情報」シートのチェックボックスを使った抽出とその結果のコピーができないところでストップしています。チェックボックスを使った抽出とその結果のコピーのマクロを教えてください。
- ベストアンサー
- その他MS Office製品
- エクセルで複数シートから、検索して抽出
はじめまして、右も左も分からない初心者です。 説明出来ないんですが、よろしくお願いします。 【ご質問内容】 エクセルは、XPを使用しています。 エクセルのVBAで、指定したキーワードを 含むデータを抽出したいと思っています。 【例】 Sheet1 A B C D E 1 番号 氏名 郵便番号 住所 メールアドレス 2 1 ○ △ 大阪府 ○○ 3 2 △ ○ 茨城県 ○○ Sheet2 A B C D E 1 氏名 番号 郵便番号 住所 メールアドレス 2 ○ １ △ 福岡県 ○○ 3 △ ３ ○ 茨城県 ○○ と各シートに、列がそれぞれバラバラになっています。 茨城県を検索すると、新しいシートに、 Sheet3 A B C D E 1 氏名 番号 郵便番号 住所 メールアドレス 2 2 △ ○ 茨城県 ○○ 3 A B C D E 4 氏名 番号 郵便番号 住所 メールアドレス 5 △ ３ ○ 茨城県 ○○ という風に、抽出したいと思っています。 どうしたらいいでしょうか？？ よろしくお願いします。
- 締切済み
- オフィス系ソフト
- EXCEL２００３で抽出？教えてください。
シート１で入力されている住所、氏名、電話番号を、シート２の１セルに1文字づつ抽出して張り付ける方法をご存知の方お教えいただけませんか？お願いします。 （例） シート１のA1セルに 愛知県碧南市○○町○丁目○番地 と入力されている住所を シート２のA1.B1.C1・・・と１文字づつ表示させる方法です。 □□□□□□□□□□□□□□□ 各セルに1文字づつ振り分けて表示する方法なんですが・・・
- ベストアンサー
- その他MS Office製品
- エクセル2007 データ抽出（関数）について
教えてください。エクセルシートにて郵便番号と住所が重複しているデータを抽出したいのです。 例）ある顧客の情報として、シート１のＡ列に、１から１０までの新規顧客の氏名、Ｂ列には生年月日が入力されています。Ｃ列，Ｄ列は空白で、Ｅ列には全ての顧客（新規顧客を含みます）の氏名、Ｆ列には生年月日、Ｇ列には郵便番号、Ｈ列には住所が入力されています。このふたつの情報の中から、新規顧客データと全ての顧客データの氏名、生年月日が重複している顧客のみ、Ｃ列にその郵便番号、Ｄ列に住所を表示させたいのです。 ＯＳはＸＰ エクセル２００７です。 困っています。よろしくお願いします。
- ベストアンサー
- Windows XP
補足
ありがとうございます。 なかなか理想的な内容にせまってきました。 なるほど、こういう方法がありましたか。