[[20210519105631]]　『SUBTOTAL関数について教えてください。』（たけちゃん）　ページの最後に飛ぶ

『SUBTOTAL関数について教えてください。』（たけちゃん）

 お疲れ様です。
 以下について教えて下さい。

 オートフィルタがかかったデータがあります。
 データは行数が増えたり減ったりします。
 データはA列のフィルタで抽出しますが、
 そのA列のデータの最終行の次の行に =SUBTOTAL(9,A2:AXXX) を入力してあります。

 データは増えたり減ったりするので、上記の XXX の部分を絶対値ではなく、最終行の値をセットしたいのですが、どうすればよいでしょうか？

 色々と試したのですが、A列に SUBTOTAL関数を置いているので循環参照エラーが出て困っています。

 どのような関数にすればよいかお教えください。
 よろしくお願いいたします。

＜使用 Excel：Office365、使用 OS：Windows10 ＞

＞AXXX
が仮に　A12　だった場合
=SUBTOTAL(9,A2:OFFSET(A12,-1,0))
のようにします
（チオチモリン） 2021/05/19(水) 11:15

 2行目を削除したり、2行目に行挿入したりすることはないのなら

 =SUBTOTAL(9,A2:INDEX(A:A,ROW()-1))

 以上
（笑） 2021/05/19(水) 11:28

（チオチモリン）様

 ご回答有難うございます。
 A12 等の絶対値ではなく、汎用的にしたいのです。

（笑）様

 有難うございました。
 うまくいきました。これをセットします。

（たけちゃん） 2021/05/19(水) 11:39

 ＞2行目を削除したり、2行目に行挿入したりすることはないのなら
 この前提で本当によかったんですかね？

 　↓ の方がいいような気がしてきました
 =SUBTOTAL(9,A1:INDEX(A:A,ROW()-1))
            ~~~
 以上
（笑） 2021/05/19(水) 13:14

ああ　失礼　説明を間違えてました
＞AXXX
が仮に　A12　だった場合
=SUBTOTAL(9,A2:OFFSET(A13,-1,0)) 　でした

つまり　SUBTOTAL関数のあるセルの一つ上という指定です。
「A13」は自セルのアドレスで、お勧めは　↓　です

 _____ _________ __________________________________________
|_____|____A____|_____________________B____________________|
|___2_|        1
|___3_|        1
|___4_|        2
|___5_|        2
|___6_|        3
|___7_|        3
|___8_|        4
|___9_|        4
|__10_|        4
|__11_|        5
|__12_|        5
|__13_|       34 [A13]=SUBTOTAL(9,A$2:OFFSET(A13,-1,0))

（チオチモリン） 2021/05/19(水) 14:04

 今さらですが、表をテーブルに変換してもいいのなら
 数式は　=SUBTOTAL(9,A2:A10)　のようなものでも行挿入や行削除に対応できます。

 テーブルにできるのならその方がいいかも･･･

 ところで
 ＞お勧めは　↓　です
 ＞=SUBTOTAL(9,A$2:OFFSET(A13,-1,0))
 なんでこれが「お勧め」なんですかね？

 A2 → A$2 にしたところで、2行目を削除すればエラーになるし
 2行目で行挿入をすれば A$3 になってしまって A2の値は含まれないし
 おまけに揮発性関数だし･･･

 以上
（笑） 2021/05/20(木) 14:37

私がお勧めとした理由ですが
(1）
＞2行目を削除
＞2行目で行挿入
も想定していません
行の挿入・削除はSUBTOTAL関数の直前で行われるという推測です
(2）
追加行の確保には
SUBTOTAL関数の前に行を挿入の他に
SUBTOTAL関数を下にドラッグして移動
SUBTOTAL関数を適当な下のセルにコピー
が考えられます。
相対参照よりも複合参照をお勧めするのはそのためです。
尚　揮発性関数の使用は体感で遅く感じないようなら私は気にしません。
毛嫌いするようなものでもないと思いますが…

こんなところでしょうか？
（異論があるとは思いますが）

（チオチモリン） 2021/05/20(木) 21:14

 ＞行の挿入・削除はSUBTOTAL関数の直前で行われるという推測です
 何を根拠にした推測なのか知りませんが
 本当にそう思っていたのなら回答時にその旨を明記すべきでは？
 本当にそう思っていたのなら、ですけど。

 少なくとも削除が SUBTOTAL の直前の行だけなんて考えにくいですね。
 オートフィルターで抽出したものを削除することもあるのかもしれない。

 A2 → A1 にするだけでその問題は回避できるのに、なんでそう頑なに意地を張っているのか不思議でなりません。

 ＞毛嫌いするようなものでもないと思いますが…
 毛嫌いて･･･何を言ってるんですかね。
 揮発性関数を使えば簡単になること、もしくは揮発性関数を使わないとできないことなら
 もちろんそれを使うことにヤブサカではありません。

 この質問のように INDEX で簡単にできることなら、たとえ結果は同じでも
 OFFSET より INDEX の方がお勧めと答えるでしょう。
 体感的にどうとか関係ない。無駄な再計算をしまくる方を敢えて選ぶ理由がないということ。

 ＞私は気にしません。
 気にする人は少なからずいます。

 ＞相対参照よりも複合参照をお勧めするのはそのためです。
 行を追加するのにそんな方法をとる人がいることに驚愕ですが
 本当にそう思っていたのなら、これもその旨を明記しないと意図が伝わりませんよ。
 本当にそう思っていたのなら、ですけど。
 相対参照にしている質問者はそんな激レアさんではないと推測されますし･･･

 以上
（笑） 2021/05/21(金) 18:47

 > 何を根拠にした推測なのか知りませんが
 初レスの↓です
 > そのA列のデータの最終行の次の行に =SUBTOTAL(9,A2:AXXX) を入力してあります。
 > データは増えたり減ったりするので、上記の XXX の部分を絶対値ではなく、最終行の値をセットしたい
 ↑から、データを別シートから「A2:AXXX」にコピーして集計等に利用する様な状況を想定しました。

 私はそういう場合には、データコピーの前に
 A2:AXXX　は値をクリア
 集計用の数式はデータ量に応じて（データの貼り付けで上書きされない適当な）位置にあらかじめ移動
 データの貼り付け
 不要行の削除、ないしは集計用の数式の移動
 という手順の作業をしていました

 > 本当にそう思っていたのなら回答時にその旨を明記すべきでは？
  質問者の状況が↑だと思っているので、それを前提とした回答のつもりです。

 > 行を追加するのにそんな方法をとる人がいることに驚愕
 そうですか。
 まあ　実務をR1C1形式で通しましたので変人の部類には入ると思います。
 （※　ブックはＡ1形式に変更してから保存してました。）

 > 少なくとも削除が SUBTOTAL の直前の行だけなんて考えにくいですね。
 > A2 → A1 にするだけでその問題は回避できる
 　データ（行）の削除は頭にありませんでした。

 >なんでそう頑なに意地を張っているのか不思議でなりません。
  よくわかりません。
  何故そう感じられるのでしょうか？

 >本当にそう思っていたのなら、これもその旨を明記しないと意図が伝わりませんよ。
 ご忠告痛み入ります。

 >相対参照にしている質問者はそんな激レアさんではないと推測されますし･･･
 ちょっと意味がわかりません。

 余談
 R1C1形式常用のせいか未だに「Ａ２」という表記が相対参照であるということになじめません。
 相対といいながらもセルが特定できますよね。

（チオチモリン） 2021/05/21(金) 22:59

 ＞行の挿入・削除はSUBTOTAL関数の直前で行われるという推測です
 ＞データ（行）の削除は頭にありませんでした。

 矛盾してますね。

 以上
（笑） 2021/05/23(日) 20:07

コメント返信：

[ 一覧(最新更新順) ]

[[20210519105631]] 『SUBTOTAL関数について教えてください。』（たけちゃん） ページの最後に飛ぶ

[[20210519105631]]　『SUBTOTAL関数について教えてください。』（たけちゃん）　ページの最後に飛ぶ