【エクセル】表から条件にあったデータを別のシートに抽出する関数

質問

エクセルの表である条件にあてはまる行(複数の列を指定したい)を、
別シートに順番に抽出されるような関数を教えてください。
元の表を編集するので、自動的に反映されるように関数で作成したいと思っています。できれば、抽出後のソートやフィルタオプションは避けたいです。

シート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

無謀な事をやろうとしているのかもしれませんが、どなたかアドバイスをください。
よろしくお願い致します。

回答No.4

MackyNo1 · ベストアンサー

>恥ずかしながら、ピボットテーブルを初めて知りました。
とても面白くて便利ですね。すぐにできました。
関数とどちらが自分の仕事に合うのかやってみます。

ピボットテーブルはうまく使いこなすと非常に便利な機能です。

今回の回答では、簡潔に回答するため、操作上の細かい注意点やノウハウなどを書きませんでしたが、今回のケースでピボットテーブルを利用されるなら、たとえばグループ化したコードのフィールドをページフィールド（A1セルの部分）にドラッグすれば、その部分のドロップダウンリストから該当データだけを表示させることができます。
また、予備のデータ範囲を広くとるため、ピボットテーブルのA列に「０」のダミーデータを入れる方法を提示しましたが、名前定義を利用して範囲に名前をつけ、データ数に応じて自動的にピボットテーブルの範囲を広げる設定にするほうがお勧めです。

http://www.officetanaka.net/excel/function/function/offset.htm
http://www.becoolusers.com/excel/pivottable-table.html

お礼

URLまで載せていただきましてありがとうございます。
食材の注文だけでなく支払いや産地集計などの事務処理がありますので、そちらにも応用できそうです。
年度末に向けて使いこなせるようになろうと思います。

回答No.5

noname#204879

[回答番号：No.3この回答へのお礼]へのコメント、

申し訳ないことですが、ステップ１に載せた式を間違えていました。
ステップ３のそれと同じものをコピーしていました。
正しくは、
=IF(AND(Sheet1!A2>=C$1,Sheet1!A2<C$1+100),ROW(A2),"")
でした。

お礼

お返事ありがとうございます。
式を入力しなおしたらできました。

回答No.3

noname#204879

A　　 B　　　　C　　D
1　　　コード　　 200　番台
2
3　　　食品名　　総量
4　　　じゃが芋　 400
5　 3　玉葱　　　 200
6　 4　人参　　　 100
7　 5
8
9
10

Sheet2 において、
１．セル A4 に次式を入力して、此れを（セル A1004 まで）下方にズ
　　ズーッとドラッグ＆ペースト
　　=IF(ISERROR(SMALL($A$4:$A$1004,ROW(A1))),"",OFFSET(Sheet1!$B$1,SMALL($A$4:$A$1004,ROW(A1))-1,(COLUMN(A1)-1)*2))
　　ちなみに、「セル A1004 まで」としたのは「元データ」のレコード数が 1000個以内と仮定したためで、「元データ」のレコード数が 100個以内なら「A1004」は「A104」とするのが得策です。
２．目障りを避けるために、Ａ列全体を非表示に設定

以上の下準備ができたら、

３．セル B4 に次式を入力して、此れを右隣にドラッグ＆ペースト
　　=IF(ISERROR(SMALL($A$4:$A$1004,ROW(A1))),"",OFFSET(Sheet1!$B$1,SMALL($A$4:$A$1004,ROW(A1))-1,(COLUMN(A1)-1)*2))
４．範囲 B4:C4 を下方にズズーッとドラッグ＆ペースト

セル C1 を数値 100 あるいは 300 に変更してみてください。どうなりますか？

お礼

回答ありがとうございます。

シート2のセルA4に式
　=IF(ISERROR(SMALL($A$4:$A$1004,ROW(A1))),"",OFFSET(Sheet1!$B$1,SMALL($A$4:$A$1004,ROW(A1))-1,(COLUMN(A1)-1)*2))
をコピーしましたが、「数式が計算されません」と出て0になります。
かまわず次の工程に進んでも0のまんまです。
シート2のB1にコード、C1に200、D1に番台を入力しています。
式を入れる前に何かしなくてはいけないんでしょうか？

回答No.2

MackyNo1

複雑な数式は管理がしにくい面がありますので、ピボットテーブルとグループ化を利用する方法を提示します（エクセルのバージョンが書いていないので２００３のケースで説明します）。

元データのシートの上にカーソルを置いて、「データ;」「ピボットテーブル・・・」でウィザードを起動し、「次へ」「次へ」で「完了」して、行フィールドに「コード」と「商品名」をデータフィールドに「総量」をドラッグします。

作成されたテーブルのコードの数字の上で右クリックし「グループ化」で表示されるダイアログで先頭の値に欄に「101」、末尾の値に「399」、単位を「100」にしてＯＫします。

集計行などが必要ないなら、右クリックから「表示しない」を選択してください。

データの追加や変更などに対応するには、たとえばＡ列のコードの列の空白セル（たとえば100行目まで）にすべて「０」と入力しておいて、ピボットテーブル上で右クリックし、ピボットテーブルウィザードで「戻る」でデータ範囲の行を100までに変更してください（必要のない表示項目は「表示しない」設定にします）。
データを更新した場合は、右クリックまたはピボットテーブルツールバーから「データの更新」を行います。

お礼

分かりやすく回答していただきましてありがとうございます。
恥ずかしながら、ピボットテーブルを初めて知りました。
とても面白くて便利ですね。すぐにできました。
関数とどちらが自分の仕事に合うのかやってみます。

回答No.1

MackyNo1

Ｓｈｅｅｔ１のＡ列が２００番代のデータを数式で表示させるなら、以下のような関数を表示用のシートのＡ列に入力し、右方向に３つ、下方向に適当数オートフィルし、Ｂ列を選択して右クリックから「削除」してください。

=INDEX(Sheet1!B:B,SMALL(INDEX((INT(Sheet1!$A$2:$A$10/100)<>2)*1000+ROW($A$2:$A$10),),ROW(A1)))&""

１００番代は「<>2」の部分を「<>１」に変更します。

お礼

できました！
エクセルの本やネット上の投稿を見ても全然できなかったのに、あっという間に解決しました。関数の意味は理解できませんが・・・。
これで仕事の事務時間が大幅に短縮できます。
ありがとうございました。