関数で複数データを取り出し、さらに開始終了日指定

質問

A列日付、B列商品、C列販売数が並んでいるシートが有ります。

同シートにG2に日付を設定、その日付に一致するデータだけを
G5以下日付、H5以下商品、I5以下販売数を反映させます。

G5セルに=IF(COUNTIF($A$2:$A$11,$G$2)<ROW(A1),"",INDEX(A$2:A$11,SMALL(IF($A$2:$A11=$G$2,ROW($A$1:$A$10)),ROW(A1))))
の関数を記入します。

上記が参照サイト：http://www.eurus.dti.ne.jp/~yoneyama/Excel/waza/fukusu_data.html　の関数で複数データを取り出す関数です。

今回、この条件に開始日と終了日を設定したいです。
H2に終了日を指定したいのですが、自分で考えた画像の数式ではエラーになりました・・・
開始終了日指定を指定する関数を教えて下さい。

回答No.2

bunjii · ベストアンサー

>今回、この条件に開始日と終了日を設定したいです。
>H2に終了日を指定したいのですが、自分で考えた画像の数式ではエラーになりました
COUNTIF関数が誤りです。
検索条件の and(">="&$G$2,"<="&$H$2) は使えませんので他の方法を使います。
COUNTIF($A$2:$A$11,$G$2) の代替数式は次のような方法が良いでしょう。
１．COUNTIF($A$2:$A$11,">="&$G$2)-COUNTIF($A$2:$A$11,"<"&$H$2)
２．COUNTIFS($A$2:$A$11,">="&$G$2,$A$2:$A$11,"<="&$H$2)
３．SUM(($A$2:$A$11>=$G$2)*($A$2:$A$11<=$H$2))
また、SMALL関数の内がはにあるIF関数は2つの条件を満たす行番号に変更しなければなりません。
IF($A$2:$A11=$G$2,ROW($A$1:$A$10)) この数式ではG2と一致する日付の行のみ行番号を拾えますがG2セルとH2セルの期間の行番号を拾えません。
次のように変更する必要があります。
IF(($A$2:$A11>=$G$2)*($A$2:$A11<=$H$2),ROW($A$1:$A$10))
全体の数式はあなたの応用力で対処してください。

お礼

お礼が遅くなり、大変申し訳ありません。
例を３つもあげてもらいありがとうございます。
いつもありがとうございます。

回答No.7

Chiquilin

ピボットテーブルを使わない理由が分かりません。
なんでこんなものを数式でやろうとしているのやら。それも作業列も使わずに。

=IFERROR(INDEX(A:A,SMALL(IF(($A$2:$A$11>=$G$2)*($A$2:$A$11<=$H$2),ROW($2:$11)),ROW(A1))),"")
Ctrl + Shift + Enterで確定。

お礼

アドバイスを貰った後、ピボットの本を一冊読みました。
確かにピボット便利です。
ありがとうございます。

回答No.6

kagakusuki

回答No.5です。

　もしすべて配列数式で行いたいという場合には、G5セルに次の様な配列数式を入力してから、[Shift]+[Ctrl]+[Enter]で確定し、G5セルをコピーして、G列～I列の5行目以下に数式のみを貼り付けて下さい。

=IF(COUNT($G$2,$H$2),IF(COUNTIFS($A$2:$A$11,">="&$G$2,$A$2:$A$11,"<"&$H$2+1)<ROWS(G$5:G5),"",INDEX(A$2:A$11,SMALL(IF($A$2:$A$11>=$G$2,ROW($A$1:$A$10)),ROWS(G$5:G5)))),"")

回答No.5

kagakusuki

G列の日付に関しては配列数式を使わずに通常の関数で抽出する事が出来るのですから、以下の様な方法は如何でしょうか？(配列変数を使うセルが少ない方が早く処理する事が出来ますし)

　まず、G5セルに次の関数を入力して下さい。

=IF(COUNT($G$2,$H$2)=2,IF(COUNTIFS($A$2:$A$11,">="&$G$2,$A$2:$A$11,"<"&$H$2+1)<ROWS(G$5:G5),"",LARGE($A$2:$A$11,COUNTIF($A$2:$A$11,">="&$G$2)-ROWS(G$5:G5)+1)),"")

　次に、H5セルに次の様に入力してから[Shift]+[Ctrl]+[Enter]で確定して下さい。(配列数式)

=IF($G5="","",INDEX(B$2:B$11,SMALL(IF($A$2:$A$11=$G5,ROW($A$1:$A$10)),COUNTIF($G$4:$G5,$G5))))

　次に、H5セルをコピーしてI5セルに貼り付けて下さい。
　次に、G5～I5のセル範囲をコピーして、G列～I列の行目以下に貼り付けて下さい。

　以上です。

お礼

お礼が遅くなり申し訳ありません。
徹夜続きで時間がなくて・・・
LARGE関数は把握しておりませんでした。
ありがとうございます。

回答No.4

msMike

関数で苦労されているようだけど、[フィルタオプションの設定]を使えば“毎度の”関数の悩みから開放されますよ。
それでも、あくまでも関数に固執されるなら、その理由を教えてください。

お礼

フィルタやピボットにマダなれていませんが、一冊、ピボットの本を読破し、今後活用していきたいと思います。
ありがとうございます。

回答No.3

bunjii

回答No.2の一部訂正です。
不等式の向きに1つの誤りがありました。
１．COUNTIF($A$2:$A$11,">="&$G$2)-COUNTIF($A$2:$A$11,"<"&$H$2)
　　　　　　↓
１．COUNTIF($A$2:$A$11,">="&$G$2)-COUNTIF($A$2:$A$11,">"&$H$2)

回答No.1

dogs_cats

配列数式に拘るたいのでしょうか？
1000行まで対応します。
G5式
=IF(SUMPRODUCT(($A$1:$A$1000>=$G$2)*($A$1:$A$1000<=$H$2))<ROW(A1),"",INDEX(A$1:A$1000,SMALL(IF($A$1:$A$1000>=$G$2,IF($A$1:$A$1000<=$H$2,ROW($A$1:$A$1000))),ROW(A1))))
数式バーにカーソルを合わせて、Ctrl キーと Shift キーを押しながら Enter キーを押す、数式バーに数式が｛｝で挟まれた表示で確定。確定されないとデータ抽出は出来ませんので注意下さい。
右方向下方へオートフィル

質問の添付画像のように日付が昇順データなら、配列数式で無くても可能です。
=IF(COUNTIFS($A:$A,">="&$G$2,$A:$A,"<="&$H$2)<ROW(A1),"",OFFSET(INDEX($A:$A,MATCH($G$2,$A:$A,0)),ROW(A1)-1,COLUMN(A1)-1))
数式コピー後右方向下方へオートフィル
開始日のセルを取得後、offset関数でA列の期間のセル数をカウント分だけ表示させる方法。

配列数式が分からない場合は、添付サイトの作業列方式を採用すべきでしょう。
countif又はcountifsで作業列を作成すれば、inndex、matchで簡単に抽出出来ます。

D列に作業列を設ける方法（該当期間の場合に行番号を返す、違えば空白）
D2式
=IF(AND(A2>=$G$2,A2<=$H$2),ROW(),"")
下方へオートフィル

G5式
=IFERROR(INDEX(A:A,MATCH(SMALL($D:$D,ROW(A1)),$D:$D,0)),"")
右方向下方へオートフィル

お礼

お礼が遅くなり、大変申し訳ありません。
徹夜続きなど出遅れました。
OFFSET関数は把握しておりませんでした。
ありがとうございます。