| エクセル | の学校 |
| 8.一覧 | 9.HOME |
| 1.Top | 2.Last |
『エクセルの計算式で指定行数の合計を表示したい』（栗栄太）
エクセルの計算式で フィルターをかけて表示されたシートの データの最下行に、表示されているデータの１０行の合計または平均値を 表示するにはどうすればいいですか？
A B C あ 1 い 2 う 3 ： ： 15150 1515 ←最終行合計と平均値 「あ」でフィルターをかけて A B C あ 1 あ 2 あ 3 ： あ 100 955 95.5 ←最終行合計と平均値 表示されている下の１０行 B列が91〜100 の合計９５５、平均値95.5を表示させる。
＜ 使用 Excel：Microsoft365、使用 OS：Windows11 ＞
このようなことだろうか？
1行目が見出しでデータは200行目まであるとして。 合計：=SUM(TAKE(FILTER(B2:B200,SUBTOTAL(103,OFFSET(A2,SEQUENCE(199,,0,1),))=1),-10)) 平均：=B201/10
なお、フィルタをかけるときには数式を入力したセルもフィルタ範囲になってしまうので空白にもチェックするようにしてくれ。 （ねむねむ） 2026/02/16(月) 16:35:10
データが100行有るとして
=SUM(TAKE(FILTER(B1:B100,SUBTOTAL(2,INDIRECT("B"&SEQUENCE(100)))),-10))
=AVERAGE(TAKE(FILTER(B1:B100,SUBTOTAL(2,INDIRECT("B"&SEQUENCE(100)))),-10))
（はてな） 2026/02/16(月) 16:54:45
1行目は見出しとして、平均だけ書きます。 フィルタは使わずに、数式で対応します。
=LET(
a,FILTER(B2:B200,A2:A200="あ"),
b,TAKE(a,-10),
s,SUM(b),
cnt,ROWS(b),
s/cnt
)
ではどうですか？
セル範囲等は調整下さい。
# 表示されているもののうち、最後の10行に限定する意味がわかりませんけど。
（ねむねむさんの回答とまる被りですが、一応提示しておきます）
（xyz） 2026/02/16(月) 17:03:12
HSTACK(s, s/n) とすれば、合計と平均の2項目を出力できます。 （xyz） 2026/02/16(月) 17:20:56
すごい根本的な話ですが、フィルター条件(A列の種類）が複数存在するなら ピボットテーブルが適切なのでは？ フィルターを切り替えながら合計と平均を確認するって手間じゃありません？ （Asa） 2026/02/16(月) 20:29:43
最下行10行分、を見落としておりました。ご放念ください
（Asa） 2026/02/16(月) 20:33:02
行＼列 | A |B |C -------+------+--------+-------- 1 |キー |バリュー| -------+------+--------+-------- 2 |あ |1 | 3 |い |2 | 4 |う |3 | 5 |あ |4 | 6 |い |5 | 7 |う |6 | 8 |あ |7 | : | : |: | 145 |う |144 | 146 |あ |145 | 147 |い |146 | 148 |う |147 | -------+------+--------+-------- 149 | |合計 |平均 -------+------+--------+--------
この場合、148〜149の間にデータを追加したときに 自動的に再計算をするようにできますか？ 最終行の場合は、追加してもエクセルが自動的に 計算範囲を修正してくれると思って最下行にしたのですが 表示されないので［C1］に合計表示を［D1］に平均の表示をしようと 思ったのですが、こうなると追加した場合は計算式の範囲を 手動で変更しなければならなくなりますよね。
（栗栄太） 2026/02/17(火) 09:12:38
解決したようだが式をたてたので。
C1セル:=SUM(TAKE(FILTER(B:.B,SUBTOTAL(103,OFFSET(A1,SEQUENCE(COUNTA(A:A),,0,1),))=1),-10)) D1セル:=AVERAGE(TAKE(FILTER(B:.B,SUBTOTAL(103,OFFSET(A1,SEQUENCE(COUNTA(A:A),,0,1),))=1),-10))
平均についてはフィルタをかけた結果が10件以下もあり得るということではてなさんの方式に変更。 （ねむねむ） 2026/02/17(火) 10:37:54
ねむねむさんもはてなさんもoffsetを使った式にしているんですけれど offsetの方がいい理由って何かあるんですか？
:=LET(_arr,b:.b,
SUM(TAKE(FILTER(_arr,MAP(_arr,LAMBDA(_c,SUBTOTAL(103,_c)=1))),-10))
)
と比較したんですけどよくわからなくて質問者とは関係ないんですけど教えていただけると嬉しいです。 こういうのは新しく質問を建てた方がいいですかね？
（ちくわ） 2026/02/17(火) 11:20:21
私はまだ新関数にそこまで慣れてなくまず思い浮かぶのが以前の関数なので。 （ねむねむ） 2026/02/17(火) 11:30:20
> ［C1］に合計表示を［D1］に平均の表示をしようと > ・・・ > こうなると追加した場合は計算式の範囲を > 手動で変更しなければならなくなりますよね。
Trim参照を使うとよいのではないですか？
私の式で言うと
a,FILTER(B.:.B,A.:.A="あ"),
などとしておけば、自動的にデータのある最終行を認識して
くれると思います。
（xyz） 2026/02/17(火) 12:06:37
a,FILTER(B2:B200,A2:A200="あ"), 「あ」以外の選択の場合にも自動的に集計する方法はありますか？ （栗栄太） 2026/02/18(水) 07:34:19
SUBTOTAL(103,OFFSET(A1,SEQUENCE(COUNTA(A:A),,0,1),))=1)
説明してもらえませんか？
（栗栄太） 2026/02/18(水) 09:07:48
まず SUBTOTAL(103,範囲) は範囲内の表示されている行の個数を返す。 (103はCOUNTA関数の機能であり、SUBTOTAL関数では非表示の行は計算の対象外とするため）
でセル範囲を OFFSET(A1,SEQUENCE(COUNTA(A:A),,0,1),) としているのはSUBTOTAL関数にデータのある範囲のセルを一つづつ与えてセル毎に表示されているかを判断するため。
例えばA1セルからA10セルまでデータがある場合、
SUBTOTAL(103,A1:A10)
だとそれこそ表示されている行数が返ってくるが
SUBTOTAL(103,OFFSET(A1,SEQUENCE(10,,0,1),))
だと結果は
SUBTOTAL(103,A1)
SUBTOTAL(103,A2)
SUBTOTAL(103,A3)
〜
SUBTOTAL(103,A10)
の結果の配列となり、セル毎に表示されているかどうかを確認できる。
（ねむねむ） 2026/02/18(水) 10:55:32
既にフィルタがあるのに、再度フィルタリングを行うのは、 認められない、想定範囲外のことということでしょう。 考えが足りず、失礼しました。却下してもらって一向構いません。 # ダイレクトにそう言ってもらったほうがスッキリします。
（xyz） 2026/02/18(水) 13:20:55
>a,FILTER(B2:B200,A2:A200="あ"), 「あ」以外の選択の場合にも自動的に集計する方法はありますか？
に関しては例えばE1セルに選択したい文字列を入力するようにして a,FILTER(B2:B200,A2:A200=E1) とする、というこのではどうだろうか？ （ねむねむ） 2026/02/18(水) 13:35:04
subtotal()関数を使えばフィルターの要素更新で再計算されると思いますので 問題は解決していると思いますが、作りたいものと違うのだろうと思って 少ない知識で書いてみました。
>複数入力できないので、 同一セルで複数条件を書けない？ということでしょうか 関数だけで作るなら、 REGEXTEST()関数を使っていいなら filter(合計範囲, regextext(判定範囲,正規表現で書いたフィルター条件のセル))などで複数条件を簡単に指定できると思います。 フィルタの入力形式とかチェックリストのドロップダウン表示みたいなUIはいじれないので 理想には合わないかと思います。 正規表現無しで作るなら textsplit(フィルター条件のセル,結合文)で文字列を配列にできます。 さらに否定条件にする場合は"!"とか"-"とかを検索して falseで返してとか一文字づつ処理していけば作れるかと
>現在設定されているフィルタの情報を取得して 関数にはないですね VBAにはあるみたい[1]です。 [1]https://learn.microsoft.com/ja-jp/office/vba/api/excel.worksheet.autofilter
（ちくわ） 2026/02/24(火) 15:11:34
ねむねむさんから 説明がありました。（2026/02/18(水) 10:55:32） これについての反応は何もないのですか？
ねむねむさんの案で質問に答えたことになっていると思いますが、どこが不満なのですか？
手作業でオートフィルターを掛けるあなたの前提説明どおりに条件設定はできていて、 しかも、その結果だけから最後の10件に限定して平均値や総和等が計算できてますよね。 仕様どおりじゃないですか？
質問を追加するなら、質問の前提をキチンと説明したらどうですか？
（xyz） 2026/02/24(火) 16:42:50
ちくわさんの 2026/02/17(火) 11:20:21 のコメントですが、 揮発性関数（OFFSETやINDIRECTなど)を使うと、シート上のセルが変更された際、 それが当該計算に無関係なセルの変更であっても、再計算が行われてしまうので、 パフォーマンス低下の原因になります。 それを避ける意味からご提案の方法が適切かと思います。 （実際にどのくらいの遅延が生じるか試したことはないですけど、 理屈の上からはそのとおりかと思います。） （xyz） 2026/02/24(火) 23:23:45
（栗栄太） 2026/02/25(水) 09:10:15
> もう一つ動きが理解できないのですが > SUBTOTAL(103,OFFSET(A1,SEQUENCE(COUNTA(A:A),,0,1),))=1) > 説明してもらえませんか？ とリクエストしたんだから、説明を受けてどうだったんですか？ よくわかりましたとか、ちょっとまだわかりませんとか 返事するのが筋じゃないですか、と申し上げました。まだ通じませんか？
ねむねむさん、はてなさん、ちくわさん、の回答案は、いずれも フィルタの結果のシートの状態をもとに、 最終10行の結果から総和、平均を求めるものですが、 それでなにがまずいのか説明して下さい。
フィルタの選択を一切変更するな、とか誰か言っているんですか？
フィルタの選択条件を各人が変更すれば、それに応じて、提示された関数が それに応じた結果を自動的に返すんですよ。理解していますか？
> シートのチェンジイベントから > 現在設定されているフィルタの情報を取得して > それを再処理する方法とかはありませんか？ シートのチェンジイベントは発生しないはずですが、仮にできたとして、 それで何をしたいのですか？ 提示案では何が困りますか？ それを説明して下さい、ということです。 （提示された案では、フィルタの選択を変更しても結果は変わらないという認識なんですか？）
# 作業用シートではないので再計算は発生しないはずということですね。了解。 # 揮発性関数の話はさておきましょう。 # 使わないほうがベターとは思いますが、使ったからアウトというわけでもないでしょう。 # （ブックの別のシートに数でなく文字列を入れてさえ再実行されるんですねえ。実験して知りました。
（xyz） 2026/02/25(水) 12:19:17
> 複数の人がそれぞれ見たいフィルタでそれぞれ確認します。 > また、データは毎日最下段に各自が追加するプログラムを使って追加されます。 > 最初の１０行と最後の１０行を比べてどれだけ作業時間が短縮または効率がアップされているかを比較することを行います。
データ(データベース)とビューワを分離すればいいんじゃないですか？ パワークエリとかVBA+SQLで整形すればいいのでは？ （tkit） 2026/02/25(水) 14:03:49
スレチだったんですけど、2件も回答もらったので subtotal()ってtake()とかで範囲参照渡しても返り値1個になるんですけど indirect()とoffset()で範囲参照を渡したときだけはなぜかそれぞれのセルに対して返り値くれるっていうのを今回知りまして これを利用したメリットがあるのかと思って質問してました。 揮発性関数によるパフォーマンス低下はスピル機能を使って呼び出し回数を減らせられるようになったので誤差ですね
スレチな内容だけだと悪いので適当に妄想を垂れ流しますが >現在設定されているフィルタの情報を取得して >それを再処理する方法とかはありませんか？ >効率がアップされているかを比較することを行います。
やりたいことって 複数条件→単一結果 ではなく 複数条件→各条件の結果 ではないですか？
ほしいのが集計結果であるのであればこんな感じでいかがですか？
D1:=LET(
_検索列, TRIMRANGE(a:b),
_数値列, TRIMRANGE(c:c),
_u, UNIQUE(_検索列),
_fx, LAMBDA(_r,_n,
LET(
_bool, BYROW(_検索列, LAMBDA(_row, AND(_row=_r))),
SUM(TAKE(FILTER(_数値列, _bool, 0), _n))
)),
_latest, BYROW(_u, LAMBDA(r, _fx(r, -10))),
_init, BYROW(_u, LAMBDA(r, _fx(r, 10))),
HSTACK(_u,_latest)
)
↓表示サンプル↓
D
1あ あの最新10件の合計値
い いの最新10件の合計値
う うの最新10件の合計値
サンプルに対して過剰実装なんですけどこういうことをしたいのでは？
データ構造 と 最終的に何をしたいのか を書いてくれれば ロジックごと教えてくれると思いますよ
（ちくわ） 2026/02/26(木) 11:38:21
別の掲示板で知ったんですけど ↑で無駄な式書いてましたが groupby関数っていうのがあって =GROUPBY(b:b,c:c,LAMBDA(x,SUM(TAKE(x,-10)))) で同じことができるみたいですね 勉強になった （ちくわ） 2026/02/27(金) 11:33:31
]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.