EXCEL：オートフィルタで

質問

いつもお世話になってます。
月末の集計と売上計画表を作るのにどうしてもやりたい事があるのですが、どうやったらいいものか困っています。
いい方法がありましたら教えて下さい。

Sheet元データとSheet伊藤～Sheet山田（印刷用でもともと形作られています）があり、
Sheet元データには　列A～Z　行2～300　程度のデータが入っています。
これにオートフィルタをかけて、抽出されたデータをコピー→任意のシート・セルに貼り付けたいと思います。

抽出／コピーしたい列は、
A（カテゴリー）：1　2
C（担当者）：伊藤　佐藤　鈴木　田中　山田
E（商品名）：文字列
G（単価1）：数値
H（単価2）：数値
I（合計金額）：数値
K（売上月）：日付
で、まず、
（担当者）伊藤／（売上月）今月／（カテゴリー）1／（単価1）空白以外のセル
でフィルターをかけて抽出されたデータを、
Sheet伊藤の指定したセル（商品名をC5～C20、単価1をD5～D20、合計金額をE5～E20）に貼り付け、
（担当者）伊藤／（売上月）翌月／（カテゴリー）1／（単価1）空白以外のセル
Sheet伊藤の指定したセル（商品名をG5～G20、単価1をH5～H20、合計金額をI5～I20）に貼り付け、
（担当者）伊藤／（売上月）次四半期／（カテゴリー）1／（単価1）空白以外のセル
Sheet伊藤の指定したセル（商品名をK5～K20、単価1をL5～L20、合計金額をM5～M20）に貼り付け、
・・・といった具合に、抽出＆コピー貼り付けをSheet伊藤～山田まで繰り返します。

（売上月）の項目は、
例えば「今月」が「4月」だった場合、「翌月」は「5月」「6月」、「次四半期」は「7月」「8月」「9月」の
データを抽出したいのですが、こちらも一発で抽出するにはどうしたらいいでしょうか。

かなりわかりにくい説明だとは思いますが、
よろしくお願いします。

回答No.5

suzusan7

こんにちわぁ

私の回答の方はもう一度確認願いたいのですが。

まず、一点目についてですが、
数式の中のドルマークがはずれていませんか？
それで下にコピーしたことによって参照している範囲が
移動していませんか？
それであれば５行目といいながらも実はG列は違う行を
参照しているという可能性が高いように思えるのですが・・・・

二点目ですが、
indirectで参照しているセルは参照できるセルでしょうか？
たとえば、シート名が実は違っているとか、
あるいは参照先はセルの結合がされているとか・・・

これができれば、
どこかのセルに担当者、月、カテゴリーを設定するだけで
各シートに一発で集計できるんですけどねぇ。

回答No.4

mshr1962

＞抽出範囲：「A1」は何を指しているのでしょうか？
現在開いているシートの抽出データの開始位置（左上）です。

すみません。設定を一箇所間違えてました。
D（売上月）：=">=2005/4/1"
E（売上月）：="<=2005/4/30"
としてください。

回答No.3

mshr1962

各シートの検索条件範囲のシートを作成
A（カテゴリー）：1
B（担当者）：伊藤
C（単価1）：">0"
D（売上月）：2005/4/1
E（売上月）：2005/4/30

抽出したいシートで「データ」「フィルタ」「フィルタオプションの設定」
抽出先を「指定した範囲」にして
リスト範囲：「元データの範囲」
検索条件範囲：「上記の検索条件範囲」
抽出範囲：「A1」
で抽出できます。

翌月、次四半期はD,E列の日付を変更するか、
別に条件用の範囲を指定してください。
※範囲の1行目には必ず元データの項目名が必要です。
※複数行に条件を設定した場合はOR条件で抽出されます。

お礼

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

すみません。。抽出範囲：「A1」は何を指しているのでしょうか？
A列が消えただけで抽出されませんでした。。

回答No.2

suzusan7

こんにちわぁ。

さて、一点目のうまく検索がいっていないのではいか？
という点ですが、セルP7以降に#NUM!が出ているのであれば
５行目は該当しないデータということです。
（シートを見てるわけではないので間違っていたらごめんなさい）
たとえば、元データの"伊藤"の後ろにスペースがあったりしても該当しません。
どの条件で該当していないかを見るためには、数式の
　　(Sheet元データ!$C$2:$C$300="伊藤")*
　　(month(Sheet元データ!$K$2:$K$300)=4)*
　　(Sheet元データ!$A$2:$A$300=1)*
　　(Sheet元データ!$G$2:$G$300<>"")
の項目を順番に削除してみてはいかがでしょうか？
もしくはif文で
　　=if(Sheet元データ!$C$5="伊藤",true,false)
という感じで順番に５行目のどの項目が該当していないかを
確認してみてください。

２点目のindirectですが、
C5、C6に#REF!が出るのであれば、参照がうまくいっていないということです。
こちらで確認した分では（Excel2000ですが）問題なくいくようです。
これもindirectをはずして
　="Sheet元データ!E" & $P5
と入力してまずセルの番地が表示されるか確認してください。
セルの番地が出るのであれば、それをindirectを使うことで
参照ができるはずですから。

手間かもしれませんが、こちらではシートをみていないので
何が原因なのかはわからないものですから。
原因がわかれば対処方法もあると思うのですが・・・

お礼

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

1点目ですが、
順番に削除してみながら確認してたら、何かの拍子（何の拍子？）に「5」と出ました。。。
そして試しに、元データの方で、2行目を行コピーして6～8行目に貼り付けてみましたが、
「6」「7」「8」は表示されません。行丸ごとコピーなので検索条件に合ってないというわけではないと思うのですが・・。

2点目は、
="Sheet元データ!E" & $P5
と入れてみたら
Sheet元データ!E2
と表示されるのですが、
=indirect("Sheet元データ!E" & $P5)
に直すとまた「#REF!」となってしまいます。

Excelは2003です。

回答No.1

suzusan7

こんにちわぁ

やりたい事はSheet元データにあるデータのうち
担当者、売上月、カテゴリー、単価1の該当するデータを抜き出し、
それらを別の印刷用のシートに表示するってことですよね。
それであればオートフィルターより配列関数を
使用したほうが、作業はしやすいと思います。

伊藤さんの4、5、6月、カテゴリー１、単価が空白以外の場合で考えるものとします。
作業列を使用するほうが効率がいいと思いますので、
PQR列の5～20行を作業列とします。

Sheet伊藤のセルP5に次式を入力します。
=Small(if((Sheet元データ!$C$2:$C$300="伊藤")*(month(Sheet元データ!$K$2:$K$300)=4)*
(Sheet元データ!$A$2:$A$300=1)*(Sheet元データ!$G$2:$G$300<>""),row($A$2:$A$300),""),row()-4)
最後に確定する時にCtrlキーととShiftキーを押しながらEnterキーを押して確定してください。
（これが配列関数の使い方です。入力後は式の前後に｛　｝のかっこが追加されます。）
これはC列で伊藤、K列の月が4、A列が1、G列がブランクでない行の行番号を
表示する関数です。
これをP20までコピーしてください。
セルQ5にもコピーし、4月を5月に変更して、同様にR5にコピーして6月に変更して
下にコピーします。
これで、行番号が表示されるばずです。
（該当するものがなければNUM!というエラーがでますので、適当にエラー処理を
しておいてくださいね。）

次にセルC5に次式を入力します。
=indirect("Sheet元データ!E" & $P5）
そうするとSheet元データのデータが表示されます。
５月ならQ5、６月ならR5にし、
単価1ならEをGに、合計金額ならIにして必要なだけコピーすれば
お望みの表ができると思います。

ただ、配列関数を使用しているので、数によっては
少し動作が鈍くなるかもしれません。
それに我慢できないようでしたら、
マクロですかねぇ？
組み方にもよりますが、３０行前後でできるのではないかと思います。

補足

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

Sheet伊藤のセルP5に
=Small(if((Sheet元データ!$C$2:$C$300="伊藤")*(month(Sheet元データ!$K$2:$K$300)=4)*
(Sheet元データ!$A$2:$A$300=1)*(Sheet元データ!$G$2:$G$300<>""),row($A$2:$A$300),""),row()-4)
と入力して下へコピーしてみたところ、
P5に「2」P6に「3」、P7以降は「#NUM!」と表示されました。
元データを見ると、条件に合うデータは2行目と3行目と5行目があります。。。
これは上手く検索されてないということでしょうか。
どうすればいいのでしょうか。。

それから、セルC5に
=indirect("Sheet元データ!E" & $P5）
と入力したところ、C5とC6に「#REF!」、C7以降は「#NUM!」と出ました。

申し訳ありませんが、引き続きアドバイスいただけると助かります。