月を指定してデータを抽出する

質問

下記のようなデータから（テーブル）

　　　　Ａ　　　　　Ｂ　　　　　　Ｃ　　　　　　Ｄ
１　　年月日　　会社名　　商品名　　　金額
２　10月27日　　Ａ社　　　リンゴ　　　　5000
３　10月29日　　Ｂ社　　　みかん　　　8000　
４　11月1日　　　Ａ社　　　バナナ　　　1000
５　11月2日　　　Ｃ社　　　みかん　　　4000
６　11月3日　　　Ｂ社　　　ぶどう　　　10000

下記のような別シートの見出しのＢ１セルを参照して。フィルタオプションで11月のデータだけを下記のＡ６：Ｄ９セルのように抽出するには抽出条件のＡ４セルにどのような関数を入れたらよいのでしょうか？

　　　　Ａ　　　　　　Ｂ　　　　Ｃ　　　　　　Ｄ
１　平成22年　　11月　　売上表
２
３　年月日
４　
５
６　年月日　　　会社名　　商品名　　　金額
７　11月1日　　　Ａ社　　　バナナ　　　1000
８　11月2日　　　Ｃ社　　　みかん　　　4000
９　11月3日　　　Ｂ社　　　ぶどう　　　10000

Ａ４セルに条件を入れなくても抽出できる方法でも結構です。
エクセルは2010です。
教えていただける方がおりましたらよろしくお願いします。

回答No.2

mitarashi · ベストアンサー

年月日が日付シリアルなら、A4に
=MONTH(A2)=11
という式を入れれば良いです。ここのA2は元の表の、フィールド名年月日の直下のアドレスです。
条件を別のシートに置くなら、
=MONTH(Sheet1!A2)=11
という様にします。
但し、A4の上に「年月日」を入れるとエラーになります。
元の表のフィールド名ではない、無関係な文字列だと問題なさそうですが、
一般には空白にしておけと言われております。
抽出条件としては、空白セルもセットで選択してください。

参考URL：

http://www.excel-jiten.net/database/extract_filter_option_formula_2.html

お礼

ありがとうございます。

シート２のＢ１セルを"11"に
A4セルの=11を=$B$1にして
完璧に出来ました。

感謝、感謝です。

回答No.6

MackyNo1

参考までに

No4の回答の数式を利用して、補助列なしに別シートに該当データを表示させたいなら、以下の式を入力して右方向に４つ、下方向に適当数オートフィルしてください。

=INDEX(Sheet1!A:A,SMALL(INDEX(((($A$1&$B$1&"1日")*1>Sheet1!$A$2:$A$1000)+((DATE(MID($A$1,3,2)+1988,SUBSTITUTE($B$1,"月","")+1,0)<Sheet1!$A$2:$A$1000)))*1000+ROW($A$2:$A$1000),),ROW(A1)))

データのない部分は「0」が表示されますのでExcelのオプションでゼロ値を表示しない設定にしてください。

どうしても、数式で対応したい場合は、B列とC列の文字列データについては、数式の最後に「&""」を付けて、A列とD列は「IF(B2="","",元の式）」というようにB列を参照すると計算負荷が少なくなります。

お礼

ありがとうございました。

説明不足で申し訳ありませんでしたが、抽出条件が他にもANDでありますので
ちょっと違うようでした。

でも、参考にはなりました。

お世話様です。

回答No.5

kagakusuki

作業列を使用しない方法です。
　今仮に、元データが存在するSheetがSheet1であるものとします。
　まず、別SheetのA7セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>SUMPRODUCT((TEXT(OFFSET(Sheet1!$A$1,1,,MATCH(9^9,Sheet1!$A:$A)),"ggge年m月")=$A$1&$B$1)*1),"",INDEX(Sheet1!A:A,SUMPRODUCT(ROW(OFFSET(Sheet1!$A$1,1,,MATCH(9^9,Sheet1!$A:$A)))*(COUNTIF(OFFSET(Sheet1!$A$1,,,ROW(OFFSET(Sheet1!$A$1,1,,MATCH(9^9,Sheet1!$A:$A)))),"<"&DATE(YEAR(VALUE($A$1&"1月1日")),VALUE(SUBSTITUTE($B$1,"月",))+1,1))-COUNTIF(OFFSET(Sheet1!$A$1,,,ROW(OFFSET(Sheet1!$A$1,1,,MATCH(9^9,Sheet1!$A:$A)))),"<"&VALUE($A$1&$B$1&"1日"))=ROWS($1:1))*(TEXT(OFFSET(Sheet1!$A$1,1,,MATCH(9^9,Sheet1!$A:$A)),"ggge年m月")=$A$1&$B$1))))

　次に、別SheetのA7セルをコピーして、別SheetのA7～D7の範囲に貼り付けて下さい。
　次に、別SheetのA7セルの書式設定を、Sheet1のA列のセルと同じにして下さい。
　次に、別SheetのA7～D7の範囲をコピーして、同じ列の8行目以下に貼り付けて下さい。

　以上です。

お礼

ありがとうございました。

説明不足で申し訳ありませんでしたが、抽出条件が他にもANDでありますので
ちょっと違うようでした。

でも、参考にはなりました。

お世話様です。

回答No.4

MackyNo1

補足情報と訂正です。

提示した数式は、月末日以下の数式が未満になっていましたので「＞」を「＞＝」に修正してください。。

ちなみにSheet2のA1セルに「平成＊＊年」B1セルに「＊月」と数字以外のデータが入力されているレイアウトの表なら、条件式は以下のような式になります。

=AND(($A$1&$B$1&"1日")*1<=Sheet1!A2,DATE(MID($A$1,3,2)+1988,SUBSTITUTE($B$1,"月","")+1,0)>=Sheet1!A2)

回答No.3

MackyNo1

フィルタオプションの設定で抽出する場合は、2列の条件でAND条件にもできますが、1つの数式だけを使って以下のような設定にするのが簡単です。

元データがSheet1で、Sheet2のA1セルに「2010」と年、B1セルに「１１」と月の数字が入っている場合、A3セルは空白にするか「条件」とでも入力しておき（重要：項目名は利用できない）、A４セルに以下の式を入力します。

=AND(DATE($A$1,$B$1,1)<=Sheet1!A2,DATE($A$1,$B$1+1,0)>Sheet1!A2)

これで検索条件を「A3:A4」に指定し、抽出範囲を指定した範囲にチェックを入れてA6セルを指定してください。

Sheet2のA1セルとB1セルに「平成22年」「11月」と入力されている場合は、少し複雑な数式にすれば対応することができます。

また、上記の条件を利用すれば、補助列なしの数式でも、同様に該当データを表示することができますが、抽出するデータ数が多くなるとシートが重くなるのであまりお勧めできません。

この場合、重複する日付がないなどの条件があれば数式を簡略化することもできます。

お礼

ありがとうございます。

抽出する事が出来ました。

ただ、他に簡単な方法がありましたので
ベストアンサーにはしませんでした。

申し訳ありません。

回答No.1

tom04

こんばんは！
一例です。
作業用の列を設けています。

↓の画像のように年・月を入力するセルは別にしておいて、単純に年・月の数値のみを入力するようにしています。

Sheet1の作業列E2セルに
=IF(AND(YEAR(A2)=Sheet2!$B$1+1988,MONTH(A2)=Sheet2!$D$1),ROW(),"")
という数式を入れ、オートフィルでずぃ～～～！っとコピーします。

そして、Sheet2のA4セルに
=IF(COUNT(Sheet1!$E:$E)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$E:$E,ROW(A1))))
という数式を入れ、列方向にD4セルまでオートフィルでコピー
そして、A4セルの表示形式は日付にしておきます。

最後のA4～D4セルを範囲指定し、D4セルのフィルハンドルで下へコピーすると
画像のような感じになります。

以上、参考になれば良いのですが・・・m(__)m

お礼

ありがとうございました。

説明不足で申し訳ありませんでしたが、抽出条件が他にもANDでありますので
ちょっと違うようでした。

でも、参考にはなりました。

お世話様です。