SUM関数の設定方法を教えて下さい

質問

◇使用機器
Win７　エクセル2007
◇状況
B2＝１０００　B３＝１０００　B４＝２０００（合計）
B5＝マイナス５００　B6=マイナス５００
B7＝１０００
という収支表をSUM関数をつって作成したい。この場合B4以降の差し引き計算箇所に
（１）次の数式を入力するとエラーが出ます。
B７＝選択　　ＳＵＭ関数を指定後　＝ＳＵＭ（Ｂ４－（Ｂ５：Ｂ６））・・・エラーが（#VALUE！）出る

（２）一方基本的には同じように設定するが部分的に＋記号を使うとＯＫです
B７＝選択　　ＳＵＭ関数を指定後　＝ＳＵＭ（Ｂ４－（Ｂ５＋Ｂ６））・・・１０００が表示される

上記の違いを教えて下さい。私なりには双方とも内容は同じと思っていましたが、結果が違うのに戸惑っています。

（３）：コロンと＋の違いが分かりません。：の場合はセルの選択が2か所で簡単

回答No.1

KURUMITO · ベストアンサー

SUM関数の正しい使え方は次のようにすることです。
Ｂ7セルには
=SUM(B4:B6)
あるいは
=SUM(B4,B5,B6)
それ以外の使え方で＝ＳＵＭ（Ｂ４－（Ｂ５＋Ｂ６））に使って正しい答えになっていますが正しい使い方とは言えませんね。
それなら上の2番目の式で十分なのですから。
＝ＳＵＭ（Ｂ４－（Ｂ５：Ｂ６））は引数の中に＝ＳＵＭ（Ｂ４－（Ｂ５＋Ｂ６））も同じですが式が使われていますね。そこでB4-(B5+B6)ではこの式を例えば別のセルに=B4-(B5+B6)のように入力すればＳＵＭ関数を使わなくとも答えが得られますね。ところが=B4-(B5:B6)ではエラー表示になってしまいます。単独でB5:B6といった形を使うことはできません。=B4-SUM(B5:B6)なら計算はできますが。

ところで同じＢ列の中で2か所にＳＵＭ関数を使っていますが、もっと高度な関数を使うことで次のようにすることもできます。
Ｂ4セルには次の式を入力します。
=SUBTOTAL(9,B2:B3)
Ｂ7セルには次の式を入力します。
=SUBTOTAL(9,B2:B6)
列の途中の段階でどの行に集計が入ってもSUBTOTAL関数で求められた値は集計から外れて計算が行われます。

お礼

KURUMITO様
早速のご教示有り難う存じました。
＞単独でB5:B6といった形を使うことはできません。これがポイントでした。ＳＵＢTOTALの９の意味が分からなかったのでイ、ンターネットで調べましたら「合計を表す記号」と説明がありました。御教示頂いた通り入力して確かめました。又縦の欄に行を追加し、範囲を拡大変更して計算結果を確認しまし多処、変更後の正規の数値が表示されました。この方式の方が使いやすい気もします。大変助かりました。厚く御礼を申し上げ解決済みにさせて頂きます。

ｕｍｉｙａｍａｄａｉ

回答No.5

koko88okok

Excelでは、合計を出したいセルに、　
=B4+B5+B6　の計算をする時、関数を使って計算する場合は、
=SUM(B4,B5,B6)、=SUM(B4:B6)　などと表します。
（ご質問のセルB7の数式です。）

=SUM(B4,B5,B6)は、「セルB4、セルB5、セルB6 を合計しなさい。」
=SUM(B4:B6)は、「セルB4からセルB6の範囲を合計しなさい。」という意味になります。
（B4:B6　はセル範囲を指定しています。）

> （３）：コロンと＋の違いが分かりません。：の場合はセルの選択が2か所で簡単

セル番地とセル番地の間に入力される「：」は、左側のセルから右側のセルまでの連続したセル範囲を表しています。
そのため、合計するセル範囲が連続している場合は、=SUM(B2:C6) のような場合もあります。
一方、「＋」は、単に右側のセルの値を足すだけです。

「エクセルの「オート SUM」ボタンはスゴ技いろいろ! あんな計算、こんな計算もボタン一つでささっと集計!」
http://www.microsoft.com/japan/athome/magazine/office/special/0906_2/default.aspx

お礼

ｋｏｋｏ８８ｏｋｏｋ様
早速のご丁寧な御教示有り難う存じました。
セルの数値（Ｂ４＝２０００）とセルの範囲（Ｂ５：Ｂ６）の区別が良く分かりました、「数値」と「範囲」を足しても答えにならない事が理解出来ました。又教えて戴いたURLも拝見いたしました。離れた箇所の合計方法もわかりました。

何よりも「小計」が複数あって最後に「合計」欄が設定されている場合、「合計」欄を選択して「ＳＵＭ関数」をクリックするだけで合計欄に数値が自動的に入力される（複数の小計欄を選択する必要がない）
機能は大変役立ちました。
厚く御礼を申し上げ解決済みにさせて頂きます。
ｕｍｉｙａｍａｄａｉ

回答No.4

mu2011

>（１）次の数式を入力するとエラーが出ます。
　⇒＝ＳＵＭ（Ｂ４－（Ｂ５：Ｂ６））は配列数式といって、セル範囲の項目単位に
　　計算する場合に使用しますが、配列数式宣言（Shift＋Ctrl＋Enterキー同時押下）
　　をしていないので＃ＶＡＬＵＥとなります。
　　従って、この配列数式宣言すればエラーが消えますが期待値になりません。
　　結果としては、Ｂ４－Ｂ５（2500)・Ｂ４－Ｂ６(2500)を合計することになり
　　５０００が表示されます。

>（２）一方基本的には同じように設定するが部分的に＋記号を使うとＯＫです
　⇒これは、単に加減乗除をしているだけだからエラーにはなりませんが
　　SUM関数の使い方ではありません。

>（３）：コロンと＋の違いが分かりません。：の場合はセルの選択が2か所で簡単
　⇒=SUM(B4:B6)とか=SUM(B4,B5:B6)の構文になります。

お礼

ｍｕ２０１１様
早速の御教示有難く御礼申し上げます。配列数式宣言は残念ながら理解できませんでしたが、セルの数値Ｂ４に、範囲（B5:B6）を加えるという式は、元々計算のできない異質のもの同士をくっつけて計算させようとしているので、エラーが出る。正式には数値同士、即ち「Ｂ４」と「B5:B6」を合計しなさい。括弧で括ってはいけません。いう事が良く分かりました。

又正解=SUM(B4,B5:B6)は最初意味が分かりませんでした。私がセルの数値５００にマイナス記号を伏してなかったため間違った数値３０００が表示され戸惑いましたが、はたと気づいて数値の前にマイナス記号を伏して計算したところ、目的を達しました。お蔭様で視野が広がりました。厚く御礼を申し上げ解決済みにさせて頂きます。
ｕｍｉｙａｍａｄａｉ

回答No.3

keithin

＞コロンと＋の違いが分かりません。：の場合はセルの選択が2か所で簡単

SUM関数に食べさせるだけなら，＋も：も，どちらも問題なく使用できます。
正解：
=SUM(B4, B5:B6)
計算できます：
=SUM(B4, B5+B6)
無問題：
=SUM(B4, -B5-B6)

SUM関数のせいではなく，かっこの中でセルとセル範囲を足したり引いたり計算しようとしているので，間違っています。

次のようにそれぞれ必ず実際にご自分の手を動かして，ご自分のエクセルで動作を確認してください。

計算できません：
Ｃ７セルに
=SUM(B4-B5:B6)

計算できます：
Ｃ６セルに
=SUM(B4-B5:B6)

計算できます：
Ｃ５セルに
=SUM(B4-B5:B6)

計算できません：
Ｃ４セルに
=SUM(B4-B5:B6)

計算できません：
Ｄ７セルに
=B4-B5:B6

計算できます：
Ｄ６セルに
=B4-B5:B6

計算できます：
Ｄ５セルに
=B4-B5:B6

計算できません：
Ｄ４セルに
=B4-B5:B6

ただし計算できるＣ５，Ｃ６セルに記入した場合も，「アナタが思っていた計算」の結果とは違います。
セルとセル範囲の足したり引いたりは，この例で現れるような特別の計算を指示する命令になります。

お礼

keithin様
早速のご教示有り難う存じました。正解=SUM(B4, B5:B6)。最初この意味が分かりませんでした。この数式を適用すると、本来正規の答えは１０００であるべきところ、３０００と間違った値が表示され戸惑いました。よく考えてみるとB5・B6は「マイナス」と私が記入してありましたが、セル内の表示はマイナス記号を付していませんでした。これは数式の中でマイナスの処理をすればよいと判断していました。
セル内の数値５００にマイナス記号を伏して計算した結果、目的を達しました。
又ご教示頂いた例題全部確認致しました。計算可能なC6・C5・D６・D5セルの計算では２５００となり正規の数値１０００と異なっている事を確認できました。ご丁寧な御教示厚く御礼を申し上げ解決済みにさせて頂きます。
ｕｍｉｙａｍａｄａｉ

回答No.2

acha51

（Ｂ５：Ｂ６）は範囲であり＋記号は演算子で混在はできません、
こういった途中に小計を含む集計をする場合は
案１．小計の列を変える
　　C列に小計を入れB列とｃ列別々に集計する
案２．それぞれに小計計算し、全体を集計して２で割る
　 B7に差し引き小計 =sum(B5:B6)
B8を合計 =sum(B2:B7)/2
とします

お礼

ａｃｈａ５１様
早速の御教示痛み入りました。
＞（Ｂ５：Ｂ６）は範囲であり＋記号は演算子で混在はできません。このことは知らなかったので大変助かりました。案２を確かめました。結果見事に目的を達しました。貴重な戦力になりました。厚く御礼を申し上げ解決済みにさせて頂きます。
ｕｍｉｙａｍａｄａｉ