エクセル SUMIFを使って下から合計する

質問

先ほど下記質問をして回答を得ましたが欲がでて追加で行いたい事が出てきましたがいっこうに進みません。
先ほどの質問
ほぼ毎日最終行の所にA列に日付　B/C/D/列に数値を加えているという作業を繰り返しています。従って下の方が最近入力した値になっている状態です。
私がしたいのはこの最近入力した(最終行)行から5行上までの各列の合計や平均値をそれぞれ1番上の方に自動で表示したいのですがいろいろと調べても分かりません。（行を追加するたび範囲を指定し直すというのがつらいです。）
・今回は皆さんの回答の中から=SUM(OFFSET(B2,COUNTA(B2:B65536)-5,0,5,1))とう式を使って下から5行上まで合計しました。
追加質問
F列の数値条件に合致したものだけを同様に下から5行上まで合計したいです。
SUMIFを使って見ましたが実力のなさからうまくいきません。
よろしくお願いします。

回答No.3

Wendy02 · ベストアンサー

こんばんは。#1 のWendy02です。

>B1にいれてみましたが84となり79とはなりませんでした。

B1 に入れることは予想外でしたが、今、こちらでやってみましたが、79 で、84 というか、「5」は足されません。ただ、私の数式のひとつの問題点は、一番、最後の65536行目まで、探しているのですね。だから、ちょっと下のほうも調べてみてください。

それで、調べる方法ですが、

LARGE((INDIRECT("F1:F"&MATCH(10^15,B:B,1))="a")*ROW(INDIRECT("F1:F"&MATCH(10^15,B:B,1))),ROW($1:$5))

数式の上記の範囲を、マウスでドラッグして取り、F9 を押すと、配列データの中身{数字,数字,・・・} で、出てきます。

それで、順番や、どこで「5」が足されているか出てくるはずです。

なお、[ESC]を押せば解除できます。

なお、2行目からなら、以下のほうが良いかもしれません。別にそのままでもエラーは出ないはずですが。

=SUMPRODUCT(LARGE((INDIRECT("F2:F"&MATCH(10^15,B:B,1))="a")*ROW(INDIRECT("F2:F"&MATCH(10^15,B:B,1))),ROW($1:$5)))

お礼

今回回答していただき大変ありがとうございました。おかげさまでかなり満足する事ができました。感謝しています。

回答No.4

maron--5

A　B　C　D　E　F
1　　21　　　　　　条件
2　　5　　　　　　　a
3　　8
4　　6　　　　　　　a　　＊
5　　2　　　　　　　a　　＊
6　　1
7　　3　　　　　　　a　　＊
8　　4　　　　　　　a　　＊
9　　5
10　　6　　　　　　　a　　＊

B1=SUMPRODUCT((F2:F100="a")*(ROW(B2:B100)>=LARGE((F2:F100="a")*ROW(F2:F100),5)),B2:B100)

★＊印が、F列が「a」の下から「5つ」の対象です

お礼

回答ありがとうございます。しっかりと計算してくれました。感謝してます。

回答No.2

imogasi

質問の意味が、前回も（したがって今回も）判りにくいのですが、具体例を挙げて説明しては。私流の理解では（極端にそぎ落とすと）
Ａ列　　Ｂ列
氏名 成績
a 11
s 2
d 3
f 43
g 5
h 12
i 18
j 3
k 21
ーー
Ａ２：Ｂ３０までがデータ入力可能性のある範囲としてます。
Ｂ列の数に対する条件を１０にしてます。
それ以外のセルに
=SUMIF(OFFSET(B2,COUNT(B1:B30)-5+1,0,5,1),">10",OFFSET(B2,COUNT(B1:B30)-5+1,0,5,1))
と入れる。
上記例では　５１＜＝２１＋１８＋１２（Ｂ６：Ｂ１０のうち１０以上の数を加えた）
式は、ＣｏｕｎｔでわかるＢ列数字行の下から５行目
COUNT(B1:B30)-5+1
の範囲を対象に、＞１０の条件で抜き出し、同じ範囲の数を足している。
ＯＦＦＳＥＴ関数という、範囲を指定できる関数を使っていますが、
たとえ質問の意図を採り間違えていなくても、＃１のご回答や私のは
関数上級編で理解も難しいのではないですか（特に＃１は）。

どこか

お礼

回答ありがとうございます。
また質問の意味が分かりづらくご迷惑をおかけしました。
勉強したいなぁと気持ちが大きくなりましたが正直今は理解難しいです。
しかしここまで来たのでなんとかできるようになればと思っています。

回答No.1

Wendy02

こんばんは。

質問の用件がわかりにくいのですが、こういうことでしょうか？

　B列・・F列
　11　　　a
　12　　　
　13　　　a　　　↓
　14　　　a　　　↓
　15　　　
　16　　　a　　　↓
　17　　　a　　　↓
　18　　　
　19　　　a　　　←最終行　ここまで。

合計は、「79」ということですか？

>今回は皆さんの回答の中から
？

ご質問者さんは、私の書いた式は何も見ていないような気がしましたが、前回、私が書いたものを応用してみました。数式の得意な人はいっぱいいるでしょうから、間違いましたら、ご容赦のほどを……。

検索値条件：　"a"

最終行の数値を取るのは、B列（数値の最終行-文字列の場合は、書き方が少し変わります）
COUNTやCOUNTA と違うのは、間が空いていても取れること。

=SUMPRODUCT(LARGE((INDIRECT("F1:F"&MATCH(10^15,B:B,1))="a")*ROW(INDIRECT("F1:F"&MATCH(10^15,B:B,1))),ROW($1:$5)))

お礼

こんばんは
　1度ならず2度までも分かりづらい質問に回答いただきありがとうございます。
また1度目の失礼申し訳ありません。数日間の悩みに解決が見え気焦っていました。

　私の言いたかった質問はWendy02さんの言われる通りのことです。
　B列・・F列
　11　　　a
　12　　　
　13　　　a　　
　14　　　a
　15　　　
　16　　　a
　17　　　a
　18　　　
　19　　　a

F列のaを条件として合致する行のB列の数字を下から5行分の合計(今回は79)をB1の位置に表示させたいのです。
但し今は19行ですが毎日追加していくので行の追加にも対応希望です。

回答を頂いてから自分のファイルでうまくいかず、上記例を作ってみて=SUMPRODUCT(LARGE((INDIRECT("F1:F"&MATCH(10^15,B:B,1))="a")*ROW(INDIRECT("F1:F"&MATCH(10^15,B:B,1))),ROW($1:$5)))と
B1にいれてみましたが84となり79とはなりませんでした。
なぜ＋5されるのか分かりません。

自分ではまったく解決する光がまったく見えない状態です。
かなり背伸びしている質問になっていますがよろしくお願いします。

できましたらアドバイスお願いします。