- ベストアンサー
【エクセル】表から条件に合ったデータを別シートに順番に抽出したい
エクセルの表である条件にあてはまる行を 別シートに順番に抽出されるような関数を教えてください。 シート1のB列にあるデータが"渋谷区"のとき シート2のE74からE80くらいまでに シート1のC列のデータを上から順に抽出されるようにしたいです。 【シート1】 A B C 1 東京都 渋谷区 PC 2東京都 港区 PC 3東京都 新宿区 マウス 4東京都 渋谷区 ペン 5東京都 渋谷区 モニタ ・ ・ ・ 10 【シート2】 E 74 PC 75 ペン 76 モニタ ・ ・ ・ 80 【補足】 元の表(シート1)を編集することがあるので 自動的に反映されるよう、関数で作成できたらと思います。 シート2の構成上、抽出後のソートや、フィルタオプションは避けたいです。 わかりづらい文章で申し訳ございませんが どなたかいいアドバイスがありましたら教えてください。 お礼は明日になってしまいますが、必ずご返答いたします。 よろしくお願いいたします。
- usamingolf
- お礼率85% (23/27)
- オフィス系ソフト
- 回答数6
- ありがとう数7
- みんなの回答 (6)
- 専門家の回答
質問者が選んだベストアンサー
>E74に関数を入力し、その後関数をE80までドラッグすればよいのでしょうか? いいえ。 1. =INDEX(シート1!C1:C11,SMALL(IF(シート1!B1:B10="渋谷区",ROW(A1:A10),11),ROW(A1:A10)))&"" の数式を掲示板上でコピーします(シート名は合わせてください) 2. E74:E80セル範囲を選択します 3. 数式バーで[Ctrl]+[V]貼り付け 4. [Ctrl]+[Shift] +[Enter] でセルへの入力を確定させます >その際、絶対参照や相対参照は必要でしょうか? セルのコピーはしませんので不要です。 参考まで
その他の回答 (5)
[回答番号:No.1この回答へのお礼]に対するコメント、 1.例えば Sheet2 のセル H73 に 渋谷区 を入力して、次式を入力し たセル H74 を下方にズズーッとドラッグ&ペースト =IF(Sheet1!B1=H$73,ROW(A1),"") 2.同シートのセル E74 に次式を入力して、此れを下方にズズーッと ドラッグ&ペースト =IF(ISERROR(SMALL(H$74:H$99,ROW(A1))),"",INDEX(Sheet1!C:C,SMALL(H$74:H$99,ROW(A1))))
お礼
わざわざすみません。 やっと、理解できました。 本当にありがとうございました。
- 某HN クロメート(Chromate)(@CoalTar)
- ベストアンサー率40% (705/1742)
配列数式を提示しても意味がわからないと、また、質問することになるんだろうなあ と思いつつ、軽量と思われる配列数式 シート1のC11セルは未入力とする シート2のE74:E80セルを選ぶ =INDEX(シート1!C1:C11,SMALL(IF(シート1!B1:B10="渋谷区",ROW(A1:A10),11),ROW(A1:A10)))&"" と入力後、[Ctrl]+[Shift] +[Enter] で確定({}で囲まれる) 参考まで
お礼
ご回答ありがとうございます。 画面まで貼り付けていただき、わかりやすくて助かります。 ご指摘のとおり、配列数式については知識がなく ちょっとわからないので 再度質問させてください。 >シート2のE74:E80セルを選ぶ とありますが E74に関数を入力し、その後関数をE80までドラッグすればよいのでしょうか? その際、絶対参照や相対参照は必要でしょうか? よろしくお願いいたします。
- masa_019
- ベストアンサー率61% (121/197)
No.2です。 書き忘れましたけど、配列数式なので、 式の確定時はCtrl +Shift + Enter をお忘れなく。
- masa_019
- ベストアンサー率61% (121/197)
こんにちは。 もう少し短い数式でもできるかもしれませんが、 一例ってことで・・・。 Sheet2のE74に {=IF(ISERROR(INDEX(Sheet1!$A$1:$C$5,SMALL(IF((Sheet1!$B$1:$B$5)="渋谷区",ROW(Sheet1!$C$1:$C$5),""),ROW()-73),3)),"",INDEX(Sheet1!$A$1:$C$5,SMALL(IF((Sheet1!$B$1:$B$5)="渋谷区",ROW(Sheet1!$C$1:$C$5),""),ROW()-73),3))} として、下にズリズリ~っとコピーして下さい。 Sheet1のリスト範囲はA1:C5としています。実情に合わせて変更して下さい。 数式中の ROW()-73 は74行目から始まる場合です。
お礼
ご回答ありがとうございます! こんなに難しそうな関数をわざわざ作成していただき 感謝してます。 ちょっと内容を理解するのに時間がかかりそうなので 取り急ぎ、お礼をさせていただきます。
「シート2」の何処かの空き列を1列だけ(作業列として)使ってもOKですか?
お礼
ご回答ありがとうございます。 作業列を作成してもOKです! アドバイス、よろしくお願いいたします。
関連するQ&A
- 【エクセル】表から条件にあったデータを別のシートに抽出する関数
エクセルの表である条件にあてはまる行(複数の列を指定したい)を、 別シートに順番に抽出されるような関数を教えてください。 元の表を編集するので、自動的に反映されるように関数で作成したいと思っています。できれば、抽出後のソートやフィルタオプションは避けたいです。 シート1のコード(A列)が100番台のもの、200番台のもの、300番台のものの、それぞれ食品名(B列)と総量(D列)を、シート2~シート4に別表として抽出したいです。 食材の注文をするのに、業者別にしなければいけませんが、量が多くて毎回大変なんです。お助けください。 【シート1】 元データ A B C D 1 コード 食品名 一人分分量 総量 2 101 牛肉 30 150 3 201 じゃが芋 80 400 4 202 玉葱 40 200 5 203 人参 20 100 6 301 しょうゆ 6 30 7 302 砂糖 4 20 【シート2】 コード100番台 A B 1 牛肉 150 【シート3】 コード200番台 A B 1 じゃが芋 400 2 玉葱 200 3 人参 100 【シート4】 コード300番台 A B 1 しょうゆ 30 2 砂糖 20 無謀な事をやろうとしているのかもしれませんが、どなたかアドバイスをください。 よろしくお願い致します。
- ベストアンサー
- その他MS Office製品
- Excelで条件に合うものを別Sheetに抽出
Excel2003を使用しています。 下記画像で上の表がsheet1で、下の表がsheet2です。 Sheet1の情報をSheet2の表に抽出したいです。 たとえば、Sheet1で、3日の日(E列)にりんご、ばななに「○」がついているので、 Sheet2の3日の列(E列)に、「りんご」「ばなな」と表示されるようにしたいのです。 この条件を満たせる関数はありますか? 方法をご教授頂けると幸いです。 よろしくお願いします。 ※「○」は入力規則のリストで、ドロップダウンリストで表示させてるのですが、問題ないでしょうか?
- ベストアンサー
- Excel(エクセル)
- エクセル(2003・2007)でデータ抽出し、別シートを作成するための
エクセル(2003・2007)でデータ抽出し、別シートを作成するための関数を教えて下さい ●シート名:サンプルA ●シート名:サンプルB A B C D E・・ A B C 名前 日付1 日付2 備考 名前 日付1 備考 1 山田 2010/10/15 2010/10/10 ああ 1 鈴木 2010/10/18 うう 2 佐藤 いい 2 太田 2010/10/21 かか 3 鈴木 2010/10/18 うう → 4 井上 2010/11/20 ええ 5 石田 2010/8/7 2010/10/7 おお 6 太田 2010/10/21 かか 具体的に上記のデータから【B列が本日から10日間以内かつC列がスペース】のデータのA・B・D行を別シートに作成する関数を教えて下さい よろしくお願いします ※なぜか文字がずれてしまってて、すいません・・・
- ベストアンサー
- オフィス系ソフト
- 二つの検索値から別のシートに抽出する
WinXP,Excel2000 下記のような表から例えば氏名コード(A列)103番と 日付(B列)2006/5/3からE列のデータ3.50を導きたい のですが、この場合検索値がA列・B列と2つあるので 同じシートに答えを出す場合は F3=SUMPRODUCT((A1:A9=F1)*(B1:B9=F2)*E1:E9)で できるのですが、別のシートに抽出するにはどうしたら よいでしょうか。ご教授ください。<m(__)m> A列 B列 C列 D列 E列 F列 1 100 2006/5/3 東京 8:10 8.00 103 2 101 2006/5/3 名古屋 9:10 7.25 2006/5/3 3 102 2006/5/3 大阪 7:45 5.00 3.50 4 103 2006/5/3 京都 10:30 3.50 5 104 2006/5/3 札幌 8:40 4.75 6 100 2006/5/4 福岡 8:45 5.00 7 101 2006/5/4 東京 9:05 6.25 8 103 2006/5/4 大阪 8:15 1.50 9 105 2006/5/4 横浜 10:15 3.00
- ベストアンサー
- オフィス系ソフト
- excelでデータを別シートに引っ張ってきたい
excelで値を検索し、返す時、返す値の横にあるデータを違うシートのセルの下に一緒に引っ張ってきたいときの関数(方法)を教えてください。 素人なので分かりにくいと思いますが、お知恵をお借りしたいと思います。 あるデータのシートの検索値を別シートに持ってきたいのですが、シート1をシート2のようにしたいのです。 /は空白 例)シート1 /A B C D E 1(1) □ ○ ▽ ◎ 2 (2) ▼ ■ ◇ ◎ シート2 / A B C D 1 (1) □ 2 /○ ▽ ◎ 3 (2) ▼ 4 /■ ◇ ◎ 分かりにくいかもしれませんが、 シート1の(1)(A1)を検索すると、シート2(B1)に□(これはVLOOKで引っ張ってこれるのはわかります。) 次にシート1の(C1)○をシート2の(B2)へ持ってきたいのです。シート1(D1)(E1)はシート2の(C2)(D2)へ。 しかもシート1のC列は空白セルもあり、シート1のC列が空白の場合、下にずれることなくシート1の(D1)(E1)はシート2の(C1)(D1)へ。下記<図a>のようになるようにしたいのです。 そして、シート2のA列に検索値として入力する(1)や(2)の値は連番ではなく、(2)の次に(5)に飛んだりします。(2)の行のC列にデータがあっても、(5)のC列にデータはないこともあります。<図b> <図a> /A B C D 1 (1) □ ▽ ◎ 2 (2) ▼ ◇ ◎ <図b> / A B C D 1 (1) □ 2 /○ ▽ ◎ 3 (2) ▼ 4 /■ ◇ ◎ 5 (5) □ ▽ ◎ 6 (7) ▼ ◇ ◎ vlookとかCLUMN関数とか考えてはみたのですが、どうもうまくいきません。 毎回作成するデータなのですが、毎回コピペで作成しています。 とても面倒なので(1)を検索したらデータが一瞬で検索できるようにしたいと試みてはみたものの、私の知識では不可能でした。 関数では無理なのでしょうか。。。 関数はあまり詳しくないので分かりやすい方法があれば、教えていただければ助かります。 関数に詳しい方、よろしくおねがいいいたします。 関数にはこだわっていません。違う方法があればそれも含めておねがいいたします。
- ベストアンサー
- Excel(エクセル)
- エクセルで別シートのデータを抽出
~シートA~ A B C D E F G ┌─────────────┐ 1│ │10│20│30│40│50│60│ │ │───────────│ 2│ │11│21│31│41│51│61│ │4│───────────│ 3│月│12│22│32│42│52│62│ │ │───────────│ 4│ │13│23│33│43│53│63│ │ │───────────│ 5│ │14│24│34│44│54│64│ └─────────────┘ 6 ┌─────────────┐ 7│ │10│20│30│40│50│60│ │ │───────────│ 8│ │11│21│31│41│51│61│ │5│───────────│ 9│月│12│22│32│42│52│62│ │ │───────────│ 10│ │13│23│33│43│53│63│ │ │───────────│ 11│ │14│24│34│44│54│64│ └─────────────┘ ・ ・ ・ ~シートB~ A B C D E F G ┌─────────────┐ 1│ ▽ │ │ │ │ │ │ │ │───────────│ 2│ │ │ │ │ │ │ │ │○│───────────│ 3│月│ │ │ │ │ │ │ │ │───────────│ 4│ │ │ │ │ │ │ │ │ │───────────│ 5│ │ │ │ │ │ │ │ └─────────────┘ ↑はリストボックスで1月~12月の値が入っている。 上記条件でシートBのリストボックスから選んだ月のデータをシートAの表から該当する月のデータを全て抽出したいのですが、どのようにすればよろしいでしょうか。
- ベストアンサー
- オフィス系ソフト
- データ表から条件一致データを抽出し別の1シート表示
エクセルでマスター表から、データを抽出して表示させるのに困っています。 具体的には、下図1のB1~D10のデータ表があり、これをSheet2に下図2のように並べて、11行目から、4列おきに表示させたいのですがどうしたらよいのでしょうか。 以前に類似の質問があり、回答(http://qanda.rakuten.ne.jp/qa6497557.html)を参考にさせていただいたのですが、関数のみで表示され、簡明だったのですが、各々別々のシートに各人を抽出するケースのため、 今回の場合の一つのシートに逐次4列おきに表示しようと、コピーペースト等しても、うまくいきません、また数行下がった位置からスタートするだけで正しい表示がされません。これに使われた関数を何か一工夫する形で、できないでしょうか。 もし難しければ、VBAで教えていただけないでしょうか。 なお各図の下に示した数式は、上記回答であった数式です。 宜しくお願い致します。
- ベストアンサー
- Excel(エクセル)
- 指定条件に合うデータを関数で抽出したい
Excelの表のデータで指定の条件に合ったデータだけを、関数を使って抽出したいです。 条件:C列="〇" A列 B列 C列 (抽出結果) 東京 10 〇 東京 10 〇 大阪 20 〇 大阪 20 〇 大阪 25 × 関数としては、ROW、ROWS、COLUMNS関数は理由があって使えません。ROWなどで設定する値は手打ちでも構いませんので、教えていただけると助かります。 どうぞよろしくお願いいたします。
- ベストアンサー
- Excel(エクセル)
- エクセルのVLOOKUP関数で…(複数条件?の抽出)
●シート2、A列に部品正式名称、B列に部品略称の一覧表(部品の種類は約500点) ●シート3、A列に略称、B,C,D,E,F,G列と続けて寸法などの詳細を記した一覧表 があります。 ●シート1に検索一覧表として、B列(B3~B8)は項目、セルC3~C8にVLOOKUPでシート3の情報が抽出されるようにしてあります。 C1で略称を入力し抽出するのはOKなのですが、正式名称で入力しても同じようにシート3の情報を抽出するようにしたいです。 (IFを使って思いつく関数を組合わせてみたりしたのですが、うまくいきませんでした(TT) できたら関数で何か良い方法ありますでしょうか? 宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
- 条件にあうデータを別シートにコピーしたい。
下記のようなデータがシート1にあります。 A B C D E F 1支店 氏名 個数 〆 資格 成績 2大阪 田中 5 10 0 3大阪 小林 12 20 × 100 4東京 佐藤 20 30 50 5東京 倉本 3 10 × 0 6東京 野村 18 15 200 これを資格がD列【×】に該当するデータすべて、 E列【0】に該当するデータすべて を別シート2に一覧にしたいのです。 今回であれば A B C D E F 1支店 氏名 個数 〆 資格 成績 2大阪 田中 5 10 0 3大阪 小林 12 20 × 100 4東京 倉本 3 10 × 0 このようになるような結果にしたいのですが 毎回まずD列が【×】のデータだけを抽出して別シート3に貼り付け 次にE列が【0】のデータでかつ資格が【空白】のデータを抽出して シート3の最下部に張り付ける。 そして支店別に並び替える、という作業を繰り返していますが、 データの更新頻度が早くデータも約1000行あり順次増えていく予定です。 ですので更新のたびに一覧を作成するのに時間がかかり困っています。 何か良い方法があれば教えていただきたいのですが・・・ よろしくお願いします。 なお、例の最上段のABCDEFエクセルの列番号 最左列の12345はエクセルの行番号のつもりです。
- 締切済み
- オフィス系ソフト
お礼
ご連絡おそくなりまして 申し訳ございません! なんとかできました!ありがとうございました。 助かりました!