[[20260216154505]]　『エクセルの計算式で指定行数の合計を表示したい』（栗栄太）　ページの最後に飛ぶ

『エクセルの計算式で指定行数の合計を表示したい』（栗栄太）

エクセルの計算式で
フィルターをかけて表示されたシートの
データの最下行に、表示されているデータの１０行の合計または平均値を
表示するにはどうすればいいですか？

 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

ありがとうございます。
TAKE関数を使ってできました。
ただ、最終行に表示すると
フィルタの表示で合計と平均のセルが表示されなくなるってことに
今気づいてしまいました。
それと、

 行＼列 | 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

COUNT,OFFSETとかを試行錯誤して何とかできるようになったと思います。
ありがとうございました。
（栗栄太） 2026/02/17(火) 10:36:28

 解決したようだが式をたてたので。

 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

コメント返信：

[ 一覧(最新更新順) ]

[[20260216154505]] 『エクセルの計算式で指定行数の合計を表示したい』（栗栄太） ページの最後に飛ぶ

[[20260216154505]]　『エクセルの計算式で指定行数の合計を表示したい』（栗栄太）　ページの最後に飛ぶ