オートフィルタ後の直近Nの合計

質問

Excelでオートフィルタされたあとの、直近のデータNコの合計を右列（図でD列）に出したいのです。

毎日出力されたデータ（C列）が届き、そのデータはカテゴリ（B列）が決まっています。ここではAかBとします。
カテゴリは毎日ユニークに分類するので、AかBのどちらか（同じ日にAとBの混在はない）。

やりたいことは、ExcelのD列に、次の数字を表示したい
・最新の日のカテゴリがAだった場合、直近NコのAのデータの合計を
・最新の日のカテゴリがBだった場合、直近NコのBのデータの合計を

NはG1に変数として入力。ただし1～9の整数のみ。

B列に現れるカテゴリは、同じものは通常30日以内に最大9は必ず現れる。
（B列を過去31[＝本日＋過去30日]検索すれば、Nが最大の9でも必ずサンプリングできる）

D列のセルには、どのような計算式を入れて下へオートフィルすればよいでしょうか。

SUBTOTAL関数だと過去Nコの指定ができないようです。
Excelは2000または2002です。

回答No.2

KURUMITO · ベストアンサー

回答No１です。Nが３の場合に式を示してしまいました。
作業列はNo１のままで、D２セルには次の式を入力して下方にオートフィルドラッグしてください。

=IF(OR(B2="",COUNTIF(B$2:B2,B2)<G$1),"",SUMIF((INDEX(B:B,MATCH(B2&COUNTIF(B$2:B2,B2)-G$1+1,F:F,0)):INDEX(B:B,ROW())),B2,INDEX(C:C,MATCH(B2&COUNTIF(B$2:B2,B2)-G$1+1,F:F,0)):INDEX(C:C,ROW())))

お礼

実は元データが数千におよぶので、入れ込みに苦労していましたが、作業列があることで、検証しながらできました。ありがとうございました。

回答No.7

某HN クロメート(Chromate)（@CoalTar）

E2セル
=B2&COUNTIF(B$1:B2,B2)
D2セル
=IF(COUNTIF(B$1:B2,B2)<$G$1,"",
SUMIF(INDEX(B:B,MATCH(B2&SUBSTITUTE(E2,B2,"")-$G$1+1,E:E,0)):B2,B2,
INDEX(C:C,MATCH(B2&SUBSTITUTE(E2,B2,"")-$G$1+1,E:E,0)):C2))
D2:E2下へオートフィル

お礼

いろいろやっていますが、やはり作業列セルがないと、大量のデータの計算結果の検証がしくいことがわかりました。

回答No.6

noname#204879

I2: =ROW()
J2: =COUNTIF($B$2:$B2,B2)
D2: =SUMPRODUCT((OFFSET(B2,,,SUMPRODUCT(MAX(($B$2:B2=B2)*(J$2:J2=J2-G$1+1)*ROW(A$2:A2)))-I2-1,)=B2)*OFFSET(C2,,,SUMPRODUCT(MAX(($B$2:B2=B2)*(J$2:J2=J2-G$1+1)*ROW(A$2:A2)))-I2-1,))

ただし、セル D2 には次の[条件付き書式]を設定して、この書式を下方にもコピーしています。

条件1　　　数式が　　　=ISERROR(D2)
　　　　　 フォント色　白
条件2　　　セルの値が　次の値に等しい　0
　　　　　 フォント色　白

お礼

条件付き書式を使う方法は考えつきませんでした。

回答No.5

tom04

No.3です！
前回は質問の意味を取り違えていた感じですね！

オートフィルタした後に質問にあるような表示にしたい訳ですよね？

もう一度画像をアップしておきます。
今回は作業列は使っていません。

↓の画像のD2セルに
=IF(COUNTIF($B$2:B2,B2)<$G$1,"",SUMIF(INDIRECT("B"&LARGE(IF($B$2:B2=B2,ROW($A$2:A2)),$G$1)):B2,B2,INDIRECT("C"&LARGE(IF($B$2:B2=B2,ROW($A$2:A2)),$G$1))))

※　これは配列数式になってしまいますので、この画面からD2セルに貼り付ける場合は
D2セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

D2セルをオートフィルで下へコピーすると画像のような感じになります。

こんな感じではどうでしょうか？m(_ _)m

お礼

この計算式はシンプルです。
オートフィルタは、見るとき見やすくするためでしたので、計算のために機能の利用の有無はとくに問いませんでした。

回答No.4

kagakusuki

作業列不要の方法です。
　D2セルに次の関数を入力してから、D2セルをコピーして、D3以下に貼り付けて下さい。

=IF(OR(COUNTIF($B$1:INDEX($B:$B,ROW()),INDEX($B:$B,ROW()))<$G$1,ISERROR(1/($G$1=ABS(INT($G$1)))+1/$G$1)),"",SUMPRODUCT((INDEX($C:$C,ROW($C$1)+1):INDEX($C:$C,ROW()))*(INDEX($B:$B,ROW($B$1)+1):INDEX($B:$B,ROW())=INDEX($B:$B,ROW()))*(COUNTIF(OFFSET(INDEX($B:$B,ROW()),ROW(INDEX($B:$B,ROW($B$1)+1):INDEX($B:$B,ROW()))-ROW(),,ROW()-ROW(INDEX($B:$B,ROW($B$1)+1):INDEX($B:$B,ROW()))+1),INDEX($B:$B,ROW()))<=$G$1)))

回答No.3

tom04

こんにちは！
一例です。

↓の画像のように検索カテゴリ・直近個数を入力するセルを設けています。
E列を作業列として、E2セルに
=IF(OR($G$1="",B2<>$G$1),"",ROW())
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

そしてD2セルに
=IF(OR(COUNT($E$2:E2)<$G$2,B2<>$G$1),"",SUMIF(INDIRECT("B"&LARGE($E$2:E2,$G$2)):B2,$G$1,INDIRECT("C"&LARGE($E$2:E2,$G$2)):C2))
という数式を入れオートフィルで下へコピーすると画像のような感じになります。

他に良い方法があればごめんなさいね。m(_ _)m

回答No.1

KURUMITO

作業列としてF２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B2="","",B2&COUNTIF(B$2:B2,B2))

D２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(B2="",COUNTIF(B$2:B2,B2)<G$1),"",SUMIF((INDEX(B:B,MATCH(B2&COUNTIF(B$2:B2,B2)-2,F:F,0)):INDEX(B:B,ROW())),B2,INDEX(C:C,MATCH(B2&COUNTIF(B$2:B2,B2)-2,F:F,0)):INDEX(C:C,ROW())))