エクセルのSUBTOTALで

質問

=SUBTOTAL(102,E7:E65)
という関数がありますが行数が表によって変化します。
そこで E65 ではなくデータのある行のマイナス１を常に指定したい。
（最終行は合計なのでカウントしたくない）
こんなときどうしたらいいでしょうか？
VBAを使わないと無理でしょうか。
その場合のコードはどう記述すればいいでしょうか。
教えてください。

回答No.10

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

＞特に 8^3^8 のところ。
顔文字です（殴）
ハイ、単なる大きな値です。

=LOOKUP(8^3^8,E7:E1000)
LOOKUP関数は２分探索を行う関数です。
そのアルゴリズムから最後に数値の書いてある値を取得するようにできているようです。よって
=LOOKUP(8^3^8,E7:E1000,ROW(E7:E1000))
で 最終行の行番号を取得しています。
だから、合計値から LOOKUP(8^3^8,E7:E1000) を引き算してもいいかもしれません。
前回の回答でダメだった理由が解明されていませんが
Wendy02さんのおっしゃるように合計にSUBTOTALを使えば2重に加算されることはありませんし、SUMを使っても2で割ればいいようにも感じます。
ちなみに、8^3^8はMAX(E7:E1000)より大きければ何の値でも問題ありません。
単純に大きな値で覚えやすい値が無いかなあって（多汗）

＞追加で VBA でも私の望むことができましたらどなたかお教えください。
VBAは良くわからないですが、数式で入力して数式の結果を値にして対処しています。回答になっていませんね(^^;

補足

ありがとうございます。
CoalTar さんのご回答で満足しています。
もしかして VBAでの回答があるかも知れないので締め切るのはもう少し延ばしたいと思います。

回答No.13

Wendy02

>集計方法 102 ですがフィルター前は正確ですがフィルターを実行すると今度は 1 少なくなってしまいます。？？？

これは、フィルタを設定するときに、範囲を予め設定することで問題が解決するはずです。

>VBAでの回答があるかも知れないので締め切るのはもう少し延ばしたいと思います。

VBAについてお聞きになりたいなら、新たに質問を起こしてください。同じスレで別の質問を起こすのはマナーに反するかと思います。私は、VBAの回答が中心の者ですが、今回は、Excelの難しい数式でない方法を考えましたが、こちらの説明が悪いのか、基本的なことを少しもチェックされていないようですから、とても残念な対応です。他の方の回答も、悪くないと思います。ただ、後出しの条件や問題をずるずる出すようですと、VBAの解決は同じような問題が起きるので、我慢強い人や掲示板の回答者として日の浅い人ならよいのですが、そうでないと難しいのではないかと思います。

お礼

わかりました。
一旦締め切ります。ありがとうございました。

回答No.12

hallo-2007

>フィルター前は正確ですがフィルターを実行すると今度は 1 少なくなってしまいます。
あれま、どうしてでしょうか？
わからないときは、式を分割してみます。空いている3つセルに
=SUBTOTAL(102,E7:E65536)
=MAX(E7:E65536)
=SUBTOTAL(104,E7:E65536)　といれてそれぞれの答えを見てください。
ヒントがあると思います。
VBAですが、こういった内容はVBAで処理してもあまり効果がないと思います。作業の中でVBAでやったら思うのは
>表は全部で１０シートあり月一回、他のデータをコピペします。
こういった作業の自動化です。更に、コピペの際に最後の合計行をコピィしないようすれば、今の問題も解決すると思いますが。

補足

たびたびありがとうございます。
=SUBTOTAL(102,E7:E65536)　はフィルター前は 1 多いです。フィルター後は正解です。　
=MAX(E7:E65536)　は常に 1 です。
=SUBTOTAL(104,E7:E65536)　は常に 1 です。　
フィルター実行前は最終行に合計行がありますがフィルター実行後は合計行は表示されません。
集計方法 102 109　いずれの場合も合計行は集計の対象外です。集計されては困るのです。

回答No.11

hallo-2007

No1、No8です。
>コピペしましたら集計方法 109 は完璧でした。
良かったです。でもコピィするだけでなく式の意味を理解してくださいね。
VBAでもコードだけ丸写ししても、結局使い物にできませんよ。
>でも 102 の方は 1 多くなってしまいます。これは合計行を足してしまってるからでしょうか。
多分そうでしょう。
>ただしフィルター実行後は合計行はないので正確です。
他の皆さんも知りたいと思うのですが、フィルタ操作後、合計行は消えるのでしょうか、消えない場合もある、或いは全て表示したときも使えいる式が知りたい。
いずれのパターンでしょうか
取り合えず
=SUBTOTAL(102,E7:E65536)-IF(MAX(E7:E65536)=SUBTOTAL(104,E7:E65536),1,0)
つまり、全体の最大値（これは合計値になります）とSUBTOTALで計算した最大値が同じがなら1引く
といった感じです。

補足

たびたびありがとうございます。
集計方法 102 ですがフィルター前は正確ですがフィルターを実行すると今度は 1 少なくなってしまいます。？？？

回答No.9

Wendy02

#5 の回答者です。

反応がなかったようですが、こちらが想定する内容とはまったく違うのでしょうか?私は、数式の位置関係が分からなかったから、#5のように書いたけれども。

SUBTOTAL 関数を使う限りにおいて、その数式の部分は読まないのだから、合計欄に、SUBTOTAL関数を使えば、そこは計算に入れないのだから、範囲を設定しておけば、それで済むはずです。
それとも、SUM関数を使っているのでしょうか？

SUBTOTALで、102,109を使っているのですから、オートフィルタではなく、リストを使っているのだとは思います。

最後の行の数を差し引くなんていう作業はいらないはずですが……。
もちろん、こちらの話が理解できないなら、引っ込むしかないけれど、それは質問以前の問題ですよね。

=SUBTOTAL(102,$E$7:E1000)

とでもしておけば、それでいいのではないのかな？

合計は、あくまでも、以下のようにします。
F66;
=SUBTOTAL(109,$F$7:E65)

COUNT 関数やSUM関数と比較すれば分かるはずです。

最後の行だけ、SUMを使っているのでしたら、それは話は別です。この部分が明らかになっていません。運の良い人が解答に行き着いたということでしょうが、本来、このレベルでは、難しい数式は必要ないものだと思います。

最終行のひとつ手前の行さえ、削除したりしなければ、難しいことをしなくてもよいと思います。

SUM関数を使っている場合は、

例えば、
E64に、名前-定義で、「ひとつ手前E」なんて付けておけば、このようになります。(名前は任意)

E3:
=SUBTOTAL(102,E7:OFFSET(ひとつ手前E,0,0))

F3:
=SUBTOTAL(109,F7:OFFSET(ひとつ手前E,0,1))

G3:
=SUBTOTAL(109,G7:OFFSET(ひとつ手前E,0,2))

もしくは、ひとつ手前のセルまで削除したり動かしたりするなら、

E65:合計のところに、名前-定義で、「合計E」として、名前は任意です。

E3:
=SUBTOTAL(102,E7:OFFSET(合計E,-1,0))

F3:
=SUBTOTAL(109,E7:OFFSET(合計E,-1,1))

G3:
=SUBTOTAL(109,G7:OFFSET(合計E,-1,2))

途中の行を挿入削除するのは可能です。しかし、合計欄自体を削除するなら、これは上手くありません。ただ、そういう話はキリがありません。こちらの話が良く分からないなら、レスする必要はありません。ただ、それでも、ヘルプや関数辞典で、SUBTOTALのところは一読することをお勧めします。

お礼

どうもありがとうございました。。。

回答No.8

hallo-2007

No1です。補足を読んでですが
オートフィルター操作後に合計の値が表示されている場合
=SUBTOTAL(109,E7:E65536)-MAX(E7:E65536)
いったん、合計まで含めて合計しますが、その後に合計の値を引き算します。
データが何行に変化しても大丈夫ではないでしょうか。
オートフィルター操作後に合計の値が表示される場合もあり、表示されない場合もあるのであれば
=SUBTOTAL(109,E7:E65536)-IF(MAX(E7:E65536)=SUBTOTAL(104,E7:E65536),MAX(E7:E65536),0)
といったように、オートフィルター後の最大値と全体の最大値が等しいか
どうかで、判断も出来ます。
COUNTについても同様な方法で対応できると思いますが。

補足

ご回答ありがとうございます。
コピペしましたら集計方法 109 は完璧でした。
でも 102 の方は 1 多くなってしまいます。これは合計行を足してしまってるからでしょうか。ただしフィルター実行後は合計行はないので正確です。

回答No.7

某HN クロメート(Chromate)（@CoalTar）

#2です。サンプルが無いので回答もまちまちですが、
＞=SUBTOTAL(102,E7:E65) はE3 にあります。
前回の回答ではできなかったのでしょうか？
では、E3セルに
=SUBTOTAL(102,E7:INDEX(E:E,LOOKUP(8^3^8,E7:E1000,ROW(E7:E1000))-1))
でどうでしょう？

補足

ご回答ありがとうございます。
数式の意味はわかりませんがコピペしてみました。
結果はＯＫ！　でした。すばらしいです。
F3,G3 の数式も完璧でした。
後学のために数式の意味をお教えください。特に 8^3^8 のところ。たぶんすぐには理解できないとは思いますが勉強したいと思います。

追加で VBA でも私の望むことができましたらどなたかお教えください。

回答No.6

KURUMITO

COUNTAやCOUNT関数などを用いる方法では間に空白のセルなどがあれば確実な方法とは言えません。最も確実な方法はたとえばＥ列で最後にSUBTOTAL関数を使うとしてその行のどこかの列に、例えばＡ列の行に「合計」とでも入力しておくことです。
その上でＡ列に合計の入った行のＥ列でのセルに次の式を入力することでしょう。
=SUBTOTAL(102,E7:INDEX(E:E,MATCH("合計",A:A,0)-1))

お礼

どうもありがとうございました。。。

回答No.5

Wendy02

こんにちは。

最終行が常に表示していると過程すれば、その範囲をひとつ狭めればよいだけの話とは違うのでしょうか？

つまり、
=SUBTOTAL(102,$E$7:E65)
というスタイルにして、先頭を絶対参照にして、片方を相対参照にすれば良いと思います。
見かけは、E65 になっていても、特に、そのセルに固定されているわけではありません。

R1C1 型に換えて、その数式を見てみると、列の高さが、いくら多くても少なくても、=SUBTOTAL(109,R7C5:R[-1]C)　という数式になっているはずです。R[-1]C　は、一行手前という意味です。

それに、合計のセルを外すというなら、=SUBTOTAL(109,E7:E64)
とすれば、SUBTOTALの数式は、無視されます。
言葉で説明しても分かりにくいので、一度、ワークシートで試していただかないと理解できないかもしれません。

お礼

どうもありがとうございました。。。

回答No.4

hige_082

＞=SUBTOTAL(102,E7:E65)
=SUBTOTAL(9,INDIRECT("E7:E"&COUNTA(E:E)+5))
参考まで

お礼

どうもありがとうございました。。。

回答No.3

imogasi

私が勘違いしているかも知れないが
Ｄ２：Ｄ１３で合計（第1引数９）の場合
1
2
4
2
5
14 SUB　　ー＞=SUBTOTAL(9,D2:D6)　　Ａ
1
2
33
5
41 SUB　　－＞=SUBTOTAL(9,D8:D11)　　Ｂ
55 TOTAL　－＞=SUBTOTAL(9,D2:D12)　　Ｃ
という風になるのではないか。
ＢがＣのあとに来るのはおかしいし、ＡがあるのにＢが無いのもおかしいのでは。
ーー
Ｄ１２；Ｄ１９をデータが増えるため、余裕を持って空けておいても
Ｄ２０は=SUBTOTAL(9,D8:D19)　ー＞ＳＵＢ
Ｄ２１は=SUBTOTAL(9,D2:D20)でよいのでは。ー＞ＴＯＴＡＬ
行挿入に対してはエクセルが式の番地を変えて対応してくれる。
式を入れる自分自身のセルの行（ＲＯＷ（））を使い、ＯＦＦＳＥＴ関数などで式を組み立てる必要が無いのではないか。
ーー
質問に簡単な実例を書かないから判りにくい。

お礼

どうもありがとうございました。。。

補足

みなさん、ご回答を感謝いたします。全部に補足をするのはたいへんなので imogasi さんのところに代表で書かせていただきます。

すみません。解りにくかったですね。
=SUBTOTAL(102,E7:E65) はE3 にあります。そして F3 には
=SUBTOTAL(109,F7:F65) が　そして　G3 には
=SUBTOTAL(109,G7:G65) があります。
データの最終行は 66行目です。ここには合計が入っていて、これは計算の対象外です。SUBTOTAL は 65行目までを求めたいのです。
今はその都度、行数の増減に合わせて 65 の数値を手で変えています。
表は全部で１０シートあり月一回、他のデータをコピペします。
数式を毎回変えなくてもいい方法があるでしょうか。