No.10ベストアンサー
- 回答日時:
>特に 8^3^8 のところ。
顔文字です(殴)
ハイ、単なる大きな値です。
=LOOKUP(8^3^8,E7:E1000)
LOOKUP関数は2分探索を行う関数です。
そのアルゴリズムから最後に数値の書いてある値を取得するようにできているようです。よって
=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
- 回答日時:
>集計方法 102 ですがフィルター前は正確ですがフィルターを実行すると今度は 1 少なくなってしまいます。
???これは、フィルタを設定するときに、範囲を予め設定することで問題が解決するはずです。
>VBAでの回答があるかも知れないので締め切るのはもう少し延ばしたいと思います。
VBAについてお聞きになりたいなら、新たに質問を起こしてください。同じスレで別の質問を起こすのはマナーに反するかと思います。私は、VBAの回答が中心の者ですが、今回は、Excelの難しい数式でない方法を考えましたが、こちらの説明が悪いのか、基本的なことを少しもチェックされていないようですから、とても残念な対応です。他の方の回答も、悪くないと思います。ただ、後出しの条件や問題をずるずる出すようですと、VBAの解決は同じような問題が起きるので、我慢強い人や掲示板の回答者として日の浅い人ならよいのですが、そうでないと難しいのではないかと思います。
No.12
- 回答日時:
>フィルター前は正確ですがフィルターを実行すると今度は 1 少なくなってしまいます。
あれま、どうしてでしょうか?
わからないときは、式を分割してみます。空いている3つセルに
=SUBTOTAL(102,E7:E65536)
=MAX(E7:E65536)
=SUBTOTAL(104,E7:E65536) といれてそれぞれの答えを見てください。
ヒントがあると思います。
VBAですが、こういった内容はVBAで処理してもあまり効果がないと思います。作業の中でVBAでやったら思うのは
>表は全部で10シートあり月一回、他のデータをコピペします。
こういった作業の自動化です。更に、コピペの際に最後の合計行をコピィしないようすれば、今の問題も解決すると思いますが。
この回答への補足
たびたびありがとうございます。
=SUBTOTAL(102,E7:E65536) はフィルター前は 1 多いです。フィルター後は正解です。
=MAX(E7:E65536) は常に 1 です。
=SUBTOTAL(104,E7:E65536) は常に 1 です。
フィルター実行前は最終行に合計行がありますがフィルター実行後は合計行は表示されません。
集計方法 102 109 いずれの場合も合計行は集計の対象外です。集計されては困るのです。
No.11
- 回答日時:
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
- 回答日時:
#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
- 回答日時:
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
- 回答日時:
#2です。
サンプルが無いので回答もまちまちですが、>=SUBTOTAL(102,E7:E65) はE3 にあります。
前回の回答ではできなかったのでしょうか?
では、E3セルに
=SUBTOTAL(102,E7:INDEX(E:E,LOOKUP(8^3^8,E7:E1000,ROW(E7:E1000))-1))
でどうでしょう?
この回答への補足
ご回答ありがとうございます。
数式の意味はわかりませんがコピペしてみました。
結果はOK! でした。すばらしいです。
F3,G3 の数式も完璧でした。
後学のために数式の意味をお教えください。特に 8^3^8 のところ。たぶんすぐには理解できないとは思いますが勉強したいと思います。
追加で VBA でも私の望むことができましたらどなたかお教えください。
No.5
- 回答日時:
こんにちは。
最終行が常に表示していると過程すれば、その範囲をひとつ狭めればよいだけの話とは違うのでしょうか?
つまり、
=SUBTOTAL(102,$E$7:E65)
というスタイルにして、先頭を絶対参照にして、片方を相対参照にすれば良いと思います。
見かけは、E65 になっていても、特に、そのセルに固定されているわけではありません。
R1C1 型に換えて、その数式を見てみると、列の高さが、いくら多くても少なくても、=SUBTOTAL(109,R7C5:R[-1]C) という数式になっているはずです。R[-1]C は、一行手前という意味です。
それに、合計のセルを外すというなら、=SUBTOTAL(109,E7:E64)
とすれば、SUBTOTALの数式は、無視されます。
言葉で説明しても分かりにくいので、一度、ワークシートで試していただかないと理解できないかもしれません。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel(エクセル)でフィルター抽出後、非表示の行を計算しないで、合計を算出する方法 【内容】 添 4 2023/01/30 17:17
- Excel(エクセル) SUBTOTAL SUMIF?? 2 2023/03/16 11:25
- Excel(エクセル) エクセル・スプレッドシートで、一定数を超えたらゼロから再累計する方法 8 2022/05/28 03:52
- Excel(エクセル) [オートフィルター]機能について 3 2023/02/04 14:32
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Excel(エクセル) アウトラインの小計のやり方 1 2023/03/20 11:51
- Visual Basic(VBA) マクロで最終行を取得したい 4 2023/05/28 12:14
- Excel(エクセル) Countifよりも早く重複数をカウントする方法ありますか? 18 2022/07/04 13:39
- Visual Basic(VBA) VBAコードを張り付け後のエクセルの進め方 2 2023/02/07 18:24
- Excel(エクセル) 特定文字(数字)で行挿入、挿入された行で合計したい 2 2023/03/13 14:30
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【関数】スペースがいくつ入っ...
-
西暦や和暦の表示をyyyymmdd表...
-
【Microsoft Office Excel Comp...
-
Excelはなんで先頭の0を消すん...
-
Excelのセルを飛ばして入力する
-
別シートからの文字を変更
-
エクセルの行の抽出について質...
-
Excelのオートフィル
-
Excel 2019 のピボットテーブル...
-
スプレッドシート クエリ関数 1...
-
excelの不要な行の削除ができな...
-
Excel初心者です。 詳しい方、...
-
【Excel】セル内の時間帯が特定...
-
Excel初心者です。 詳しい方、...
-
EXACT関数とIF関数の組み合わせ...
-
Excelのグラフ軸について
-
スマートな関数を教えて下さい。
-
Excelで全角を半角にしたいので...
-
【マクロ】エクセルにかいてあ...
-
Excel:一部のフォントでセルの...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報