EXCELで途中の集計行を除いて集計したい。

質問

EXCEL2002で合計を出したいのですが、途中に「合計」とか「中計」などの集計行があり、最初から最後までSUM関数を使うとその分だけ重複してしまいます。途中の計算式が入っているセルを除いて集計するには、どうしたらよいでしょうか？Ａ列に項目（例えば、事務用品、コピー用紙、小計、固定電話料、携帯電話料、通信費計・・・）、Ｂ列に数値または計算式を入力しています。

回答No.8

imogasi · ベストアンサー

#7です。
>ＥＸＣＥＬ関数を知らない人が多く、
ＳＵＢＴＯＴＡＬの利用を周知させることが難しいと思います。
自分や周りの現状を前提に、甘えちゃいけない。知らないことを勉強する方向しか道はない。
SUBTOTALを今知らないからといって、他の方法は私もVBAやSUMPRODUCTに触れたが、習得にはさらに難しい内容になる。
=SUM()で出している行を個別に指定して足して行く　=A5+A10+A17のようにする方法しかないと思う。
ーー
このコーナーの存在意味は、色んな知識や解法が載せてもらえるところが魅力では無いかと思う。私も本題のケースではないが、そういうつもりで、難しくても別解を挙げるようにしている。エクセルに関して視野や心を開くきっかけになればと思って。

お礼

ご意見をありがとうございました。
各社員のスキルアップは、当然のことと思います。
しかしながら、40歳代以上ではEXCELが使えない人がほとんどで、手書きの書類をデータ化するために種々の様式を作成しつつある状況です。
今回は、「=A5+A10+A17」の方法で処理するように検討します。

回答No.7

imogasi

#1の
＞ＳＵＢＴＯＴＡＬに変えても、ダメのようです。
は,途中行で、今まで集計されている行で使われている関数を=SUBTOTAL(9,C2:C9)のように全て改めましたか。最後だけSUTOTALにしようとしてませんか。
改める気が無いが集計を出したいなら、集計合計の行を特定できる
何かがいるが、そういう方法でやりたいのか。SUMPRODUCT利用などがそれに使えるであろう。
ーー
VBAで、（合計以外のセルには式の設定がないなら）式のあるセル（HasFormura =True)を飛ばして集計する方法はあるが、VBAの経験も無いだろうし、普通そんなことは使わない。
例
行数　　A列　　A列式の内容　　　　　　　　　E列　E列式の内容
1 2 2
2 3 3
3 4 4
4 9 =SUBTOTAL(9,A1:A3) 9 =SUM(E1:E3)
5 2 2
6 4 4
7 5 5
8 6 6
9 7 7
10 24 =SUBTOTAL(9,A5:A9) 24 =SUM(E5:E9)
11 2 2
12 3 3
13 5 5
14 10 =SUBTOTAL(9,A11:A13) 10 =SUM(E11:E13)
15 43 =SUBTOTAL(9A1:A14) 86 =SUBTOTAL(9,E1:E14)
E列E15でSUM、SUM、SUMでSUBTOTALでは２倍になってしまう。
A列A15では正しい

お礼

ありがとうございます。
ＳＵＢＴＯＴＡＬの使い方はわかりました。
でも、ＥＸＣＥＬ関数を知らない人が多く、
ＳＵＢＴＯＴＡＬの利用を周知させることが難しいと思います。
他の方法があるのでしょうか？

回答No.6

gutoku2

>途中の計算式が入っているセルを除いて集計するには、

計算式が入っているセルを除くのであれば、下記の条件が成り立てば可能です。
小計、中計、総合計ともにSUMではなくSUBTOTALを使用する。
　※SUBTOTAL関数で総合計を計算する場合その範囲内にある
SUBTOTALは無視されます。

例　 B1～B10　セルに数値入力、
　　B11セル =SUBTOTAL(9,B1:B10)　→小計とします
B12～B15　セルに数値入力
　　B16セル =SUBTOTAL(9,B12:B15)　→小計とします
　　B17セル =SUBTOTAL(9,B1:B15) 　→中計とします。
B18　セルに数値入力
　　B19セル =SUBTOTAL（B1:B18)　→B1:B10,B12:B15,B18の値だけを加算します。　　　　　　　　　　　　　　　　　　　　　　※計算式入力部分は加算されません。

お礼

ありがとうございます。
ＳＵＢＴＯＴＡＬの使い方はわかりました。
でも、ＥＸＣＥＬ関数を知らない人が多く、
ＳＵＢＴＯＴＡＬの利用を周知させることが難しいと思います。
他の方法があるのでしょうか？

回答No.5

onlyrom

そういった小計、合計、総合計をとる場合は後で集計がし易いように、例えば、

　　（小計）
　　【合計】
　＜＜総合計＞＞

などと他の項目と区別できるようにするのが一般的だと思います。
そうしておけば、SUMIFなどで簡単に総合計を出すことができますよね。

総合計は、【合計】の欄を計算すればいいわけですから

　=SUMIF(A2:A200,"【合計】",B2:B200)

途中の合計もそのようにして（小計）を計算すればいいですね。

お礼

ありがとうございます。
社内で使う共通の様式を作っているところですが、入力担当者に周知できない（ルールを無視する人がいる）ので、様式への入力項目の制限はできるだけ減らして、なおかつ集計をしたいのです。
できれば「計算式の入っているセルを除いて集計する」といった方法を探しています。

回答No.4

mshr1962

>勘定科目に「設計費」がありますので、方法２もダメなんです。
必ず集計行以外が"計"で終わらないなら
=SUMIF(A2:A100,"<>*計",B2:B100)
としてください。それ以外の場合は
=SUMPRODUCT(($A$2:$A$100<>"小計")*($A$2:$A$100<>"中計")*($A$2:$A$100<>"合計")*($B$2:$B$100))

お礼

ありがとうございます。
例えば、「基礎設計」とか「詳細設計」とかのように”計”で終わる場合もあるので、苦労しています。

回答No.3

mshr1962

方法１．SUBTOTAL関数を使う
=SUBTOTAL(109,B2:B100)
方法２．SUMIF関数を使う
=SUMIF(A2:A100,"<>*計*",B2:B100)

お礼

ありがとうございます。
勘定科目に「設計費」がありますので、方法２もダメなんです。

回答No.2

Folly

SUM関数ではなく、SUBTOTAL関数を使って下さい。
例、
=SUM(B1:B50) ---> =SUNTOTAL(B1:B50,9)
として見て下さい。

お礼

早速ありがとうございました。
ＳＵＢＴＯＴＡＬでは、できませんでした。

回答No.1

mu2011

SUM（範囲）をSUBTOTAL(9,範囲)に変えれば途中の集計分は計数されませんが如何でしょうか。

お礼

早速ありがとうございます。
ＳＵＢＴＯＴＡＬに変えても、ダメのようです。