Excelでオートフィルタ後の直近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回は必ず現れます。D列のセルにはどのような計算式を入れればよいか、Excelは2000または2002です。
- ベストアンサー
オートフィルタ後の直近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です。
- goldensea
- お礼率87% (62/71)
- その他MS Office製品
- 回答数7
- ありがとう数4
- みんなの回答 (7)
- 専門家の回答
質問者が選んだベストアンサー
回答No1です。Nが3の場合に式を示してしまいました。 作業列はNo1のままで、D2セルには次の式を入力して下方にオートフィルドラッグしてください。 =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())))
その他の回答 (6)
- 某HN クロメート(Chromate)(@CoalTar)
- ベストアンサー率40% (705/1742)
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下へオートフィル
お礼
いろいろやっていますが、やはり作業列セルがないと、大量のデータの計算結果の検証がしくいことがわかりました。
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 フォント色 白
お礼
条件付き書式を使う方法は考えつきませんでした。
- tom04
- ベストアンサー率49% (2537/5117)
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
お礼
この計算式はシンプルです。 オートフィルタは、見るとき見やすくするためでしたので、計算のために機能の利用の有無はとくに問いませんでした。
- kagakusuki
- ベストアンサー率51% (2610/5101)
作業列不要の方法です。 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)))
- tom04
- ベストアンサー率49% (2537/5117)
こんにちは! 一例です。 ↓の画像のように検索カテゴリ・直近個数を入力するセルを設けています。 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
- KURUMITO
- ベストアンサー率42% (1835/4283)
作業列としてF2セルには次の式を入力して下方にオートフィルドラッグします。 =IF(B2="","",B2&COUNTIF(B$2:B2,B2)) D2セルには次の式を入力して下方にオートフィルドラッグします。 =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())))
関連するQ&A
- エクセルのオートフィルタで合計を出したい
エクセルでA列に会社名、B列に支払い金額が、100行にわたって入力されているとします。 オートフィルタをかけて、ある会社(△△商事)だけ選択したら10件だったとします。この△△商事の支払い金額の合計はどのように出すのでしょうか? 金額が10件ピックアップされて、次のセルでSUMで合計しても100件分の合計になってしまいます。 この△△商事の10件だけの合計金額の出し方を教えてください。
- ベストアンサー
- オフィス系ソフト
- エクセル オートフィルタを使わないで計算したい
例えば A列には1,2,3、・・・とランダムに数字が入っていて C列にも1,2,3・・・とランダムに数字が入っています。 B列D列も計算式を用いてランダムに数字が入っています。 なので2つのデータを並べています。 やりたいことは 「A列とC列に「2」ならば右に隣接する整数の数を求めたい」です。 オートフィルタを使うと1つのデータしか取り出せなくなってしまいます。 A列の「2」に隣接するB列の整数の合計と C列の「2」に隣接するB列の整数の合計の合計額がほしいです。 なにかいい方法はありますでしょうか? よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- EXCEL2000 オートフィルタで。。
お世話になります EXCEL2000 です。 やりたいことは、例えば A列に(もちろん縦に)A,B,C,D,E,F,Gとデータが並んでいたとして、その中で、B,C,Dのデータだけを抽出したい場合はどうしたらいいのでしょう?? オートフィルタでいいでしょうか? データ→フィルタ→オートフィルタで、▼をクリックし、 オプション画面がありますが、[or]だと、2つのデータしか抽出できないようになっていて・・・(涙 しかたがないので、白い入力欄に、,(カンマ)で区切って B,C,Dというふうに入れてみましたが うまくいきませんでした。。。 どうすればいいでしょう?
- ベストアンサー
- オフィス系ソフト
- オートフィルタの合計
こんにちは! 表の合計を種類別に分けるので2段にしました。 オートフィルタで合計を出すのにSUBTOTALを使いました。 しかしオートフィルタで範囲指定すると、合計は1段しか出ません。どうしたらいいのでしょうか? A B C 1 10 10 10 2 20 20 20 3 10 10 10 合計 20 20 20 合計 20 20 20
- 締切済み
- その他(インターネット・Webサービス)
- エクセル 条件付きの合計を速くできないか
エクセルで以下の処理を行うと、時間が5分以上かかり、 もう少し早くできる方法があるなら教えてください。 A列は01から最大99まで、B列はAから最大ZZまで、C列は000から最大999まで、D列はランダムな数字、E列は○か×か△のデータが入っています。 例えば、ある行は、 03 BC 000 654 ○ また、ある行は、 34 C 009 76598 × です。 そして求めたいのは、D列の合計です。 01でAで001から999のうち○の合計と×の合計と△の合計をそれぞれ求めたい。 同様に 01でBで001から999のうち○の合計と×の合計と△の合計をそれぞれ求めたい。 同じことを繰り返し、最後に 99でZZで001から999のうち○の合計と×の合計と△の合計をそれぞれ求めたい。 合計を求める関数は、SUMPRODUCT関数を使いました。
- ベストアンサー
- その他MS Office製品
- エクセルのオートフィルタについて
エクセルのオートフィルタについて教えてください。 エクセル2003です。 データ-並べ替え で複数の列に対してレベルごとに昇順で並べ替えをしますが、これをオートフィルタではできないのでしょうか? 例えば、A列にクラスデータがあり、それをオートフィルタで昇順にしておいて、それをレベル1として、B列に点数があり、それをオートフィルタで降順にして、それをレベル2とするような使い方です。
- ベストアンサー
- オフィス系ソフト
- エクセル2007でのオートフィルタ
エクセル2003にてオートフィルタを活用しています。 先日、エクセル2007が入っている新しいパソコンで このファイルを開き、オートフィルタの機能を使用したところ 思うように動かなかったので質問させて下さい。 2003では A列をオートフィルタで昇順、降順をすると B列のデータも連動して動きました。 しかし、2007では A列のみがソートされ、B列は元のまま。。。 これは仕様なのでしょうか? それとも何か設定が必要なのでしょうか?
- ベストアンサー
- オフィス系ソフト
- エクセル97で、オートフィルタ…
エクセル97でオートフィルタをつかいたいのですが、 A B C D E 1 ○ ▽ ▲ 2 × ● ・ ・ ・ 3 ・ ・ ・ 4 ・ ・ ・ 5 ● × ・ ・ ・ 6 ・ ・ ・ AとBの列でオートフィルタをかけたいのですが、 行の123と345をひとまめてにして検索できるようにしたいんです… そのようなことは可能でしょうか?? またその他によい方法があればよろしくおねがいします。
- ベストアンサー
- オフィス系ソフト
- Excelのオートフィルタのことで質問です。
Excelのオートフィルタのことで質問です。 リストは、A列(月)、B列(名前)、C列(売上)、D列(%)です。年度集計でオートフィルタを使い、名前を重複させずに抽出させ、かつ%順に並べかえたいです。 ちなみに重複する名前の売上と%は合計させて並べ変えたいのですが、うまくできません。 マクロは使ったことがないのですが、どのようにしたらいいでしょうか?
- ベストアンサー
- オフィス系ソフト
お礼
実は元データが数千におよぶので、入れ込みに苦労していましたが、作業列があることで、検証しながらできました。ありがとうございました。