わえなび ワード&エクセル問題集 waenavi

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel合計】表の意味を考えてSUM関数を使ってもよいか判断する練習問題


Excelで合計を求めるときにオートSUM(SUM関数)を用いることがありますが注意点が2つあります。

  • SUMが使えるように表を作る
  • 合計だからといってSUMとは限らない

表を作るときにSUMを意識し「SUMが使えるような表」を作ることが大事です。表の作り方を工夫して、できるだけSUMが使いやすい表を作るべきです。そして、合計を求めるときに、何も考えずに適当にオートSUMボタンを押せば良いというものではありません。SUMを使いたいのであれば、表の意味を考えるべきです。

そこで、今回は、SUM関数で合計を求める際の注意点について出題します。

f:id:waenavi:20190625102220j:plain

 

 

目次

問題文のあとに簡単な操作方法を解説していますが、静止画では、わかりにくいと思いますので、最後に動画を載せています。ぜひご覧ください。

1.文字列を含むセルは除外される

問題

次の表で合計を求めなさい。また、第2回の数学を欠席した場合はどのようにすればよいか。

f:id:waenavi:20190625095434j:plain

 

解説

合計を求める表の基本的なパターンには、縦計、横計、そしてL字型の縦計・横計を両方出すパターンがあります。SUM関数を使えば一発で合計が出ます。

f:id:waenavi:20190625100545j:plain

 

合計の部分を含めて範囲選択します。

f:id:waenavi:20190625100548j:plain

 

オートSUMのボタンを押します。これで完成です。

f:id:waenavi:20190625100550j:plain

 

ここで第2回の数学を欠席にします。合計が減りました。

f:id:waenavi:20190625100553j:plain

 

本来、数値を入力する場所に文字列を入れることは正しくありません。

f:id:waenavi:20190625100555j:plain

 

できるだけ避けるべきですが、空欄にすると、試験を受けなかったのか、もともと受ける資格がなかったのかがわからないので、やむを得ず、このように得点が入れなかった理由を書き込むことがあります。

f:id:waenavi:20190625100558j:plain

f:id:waenavi:20190625100601j:plain

 

SUM関数の範囲の中に文字列があったときは、計算から除外されます。数字から始まっていたとしても、文字列を含むものはすべて除外です。

f:id:waenavi:20190625100604j:plain

 

2.合計とはいってもSUMとは限らないパターン

問題

次の表で合計を求めなさい。

f:id:waenavi:20190625095558j:plain

 

解説

SUM関数は足し算を省略したものです。12万円を何回も足す場合の合計は、足し算なので、SUM関数ですが、12万円を8倍すればよいときの合計は、掛け算なので、SUMは無関係です。

f:id:waenavi:20190625102220j:plain

 

単価と数量は単位が違います。原則として単位の違うものにSUM関数は使えません。

f:id:waenavi:20190625102222j:plain

 

横の合計は、単価*数量で求めるものなので、掛け算です。

f:id:waenavi:20190625102226j:plain

f:id:waenavi:20190625102229j:plain

 

縦計は足し算をするものなので、オートSUMが使えます。

f:id:waenavi:20190625102231j:plain

f:id:waenavi:20190625102235j:plain

 

単価を足しても意味がないので空欄にします。

f:id:waenavi:20190625102238j:plain

 

単価のように、単位量を足す計算は全く意味がないので、合計を求めてはいけません。

f:id:waenavi:20190625102242j:plain

 

3.合計を計算するべきか、それとも参照するべきか

問題

次の表でそれぞれ合計を求めなさい。

f:id:waenavi:20190625095646j:plain

 

解説

この表のように合計が上にあって、その内容が下にあるというパターンがあります。必ずしも合計が右や下にあるとは限りません。

f:id:waenavi:20190625103102j:plain

 

オートSUMのボタンを押します。かっこの中にカーソルがあります。

f:id:waenavi:20190625103105j:plain

 

この状態でドラッグします。

f:id:waenavi:20190625103108j:plain

 

確定します。これで完成です。

f:id:waenavi:20190625103112j:plain

 

ちなみに、下に合計を出す欄があって、その結果を上にも表示させるパターンの場合、下に合計を出しておいて、上は参照するだけです。

f:id:waenavi:20190625103114j:plain

 

オートSUMを絶対に2回使ってはいけません。

f:id:waenavi:20190625103118j:plain

f:id:waenavi:20190625103121j:plain

 

4.合計だからSUMを使うのではなく「足し算」だからSUMを使う

問題

次の表で、勝ちを3点、引き分けを1点として、各チームの試合数と合計点数を求めなさい。また、7行目にチームAの勝ち点を表示しなさい。

f:id:waenavi:20190625111455j:plain

 

解説

試合数は勝ちと引き分けと負けの数を足します。これはSUM関数が使えます。オートSUMのボタンを押します。

f:id:waenavi:20190625112021j:plain

 

オートフィルをします。

f:id:waenavi:20190625112024j:plain

 

このとき、1行目の勝ち点の合計をしても意味がないので空欄にします。

f:id:waenavi:20190625112027j:plain

 

次に、合計点数を求めます。合計と表現していますが、この表では掛け算を使います。3点が3回と、1点が3回です。勝ち点は絶対参照です。

f:id:waenavi:20190625112030j:plain

 

オートフィルをします。

f:id:waenavi:20190625112033j:plain

 

これで完成です。このように、合計と書いてなくてもSUMを使うこともあれば、合計と書いてあるのにSUMを使ってはいけない場合があります。

f:id:waenavi:20190625112036j:plain

 

最後に、チームAの勝ち点ですが、すでに表の中にある場合は、これを参照します。

f:id:waenavi:20190625112040j:plain

f:id:waenavi:20190625112045j:plain

 

5.合計に加えてはいけないもの

問題

次の図で、A選手の合計スコアを求めなさい。

f:id:waenavi:20190625112801j:plain

 

解説

合計とはいっても種類の異なるものを足してはいけません。表の形から判断して、明らかに合計から除外するものは、除外して求めます。

f:id:waenavi:20190626060611j:plain

 

例えば、合計をする3日間のスコアの前に、目標などの関係のないデータがある場合は、除外して合計を出します。

f:id:waenavi:20190626060615j:plain

 

オートSUMのボタンを押します。範囲を直します。

f:id:waenavi:20190626060619j:plain

 

これで完成です。

f:id:waenavi:20190626060622j:plain

 

6.数量合計と売上合計

問題

次の図で、合計を求めなさい。

f:id:waenavi:20190625112811j:plain

 

解説

単価の後の、6月、7月、8月は数量であると考えられます。したがって、この3つの合計を出します。オートSUMのボタンを押します。範囲を直します。

f:id:waenavi:20190626061056j:plain

 

これで完成です。

f:id:waenavi:20190626061058j:plain

 

なお、合計の列を含めて範囲選択をする方法でも構いませんが、単価の列を含めてはいけません。

f:id:waenavi:20190626061100j:plain

 

オートSUMのボタンを押します。

f:id:waenavi:20190626061103j:plain

 

別解

ちなみに、金額の合計を求める場合は、まず、先ほどと同じように、個数の合計を求めます。

f:id:waenavi:20190626061240j:plain

 

このSUM関数の後に、単価をかけます。

f:id:waenavi:20190626061243j:plain

 

これで完成です。

f:id:waenavi:20190626061246j:plain

 

7.小計と総合計

問題

次の図で、合計と総計を求めなさい。

f:id:waenavi:20190625112823j:plain

 

解説

行の途中に合計を求めることがあります。合計の範囲を間違えないように注意します。総合計は小計を足します。

f:id:waenavi:20190626061918j:plain

 

中学生の合計を求めます。オートSUMのボタンを押します。1年から3年までの合計です。

f:id:waenavi:20190626061920j:plain

 

確定します。次に高校生の合計を求めます。オートSUMのボタンを押します。1年から3年までの合計です。

f:id:waenavi:20190626061923j:plain

 

確定します。総計を求めます。中学生の合計55人と高校生の合計61人と大学生153人を足せば終わりです。

f:id:waenavi:20190626061925j:plain

 

これを、あえてオートSUMを使って求めるのであれば、Ctrlキーで3つを選択します。

f:id:waenavi:20190626061928j:plain

 

8.交互に並んでいる数の合計

問題

次の図で、受験者Bの合計得点を求めなさい。

f:id:waenavi:20190625112831j:plain

 

解説

この表は、受験者の氏名、テストの回数、教科の3つの項目で、得点を入力しています。Excelの表は、縦と横しかない2次元の表なので、3つ以上の項目があると、データが離れてしまいます。

f:id:waenavi:20190626062408j:plain

 

このように項目が連続していない場合、連続しないセルの合計を求めることになります。

f:id:waenavi:20190626062412j:plain

 

英語の合計を求めます。オートSUMのボタンを押します。

f:id:waenavi:20190626062415j:plain

 

Ctrlキーで、英語を3つ選択します。

f:id:waenavi:20190626062418j:plain

 

これで完成です。右にオートフィルをします。

f:id:waenavi:20190626062420j:plain

 

このとき、相対参照によって右に移動します。

f:id:waenavi:20190626062422j:plain

 

9.目標達成率の「目標」とは何か

問題

次の図で、数量合計・金額合計及び達成率を求めなさい。

f:id:waenavi:20190626062610j:plain

 

解説

合計を求めるときには、合計の範囲を考えます。この場合、数量合計は6月、7月、8月の合計です。オートSUMのボタンを押します。

f:id:waenavi:20190626062908j:plain

 

範囲を直します。

f:id:waenavi:20190626062911j:plain

f:id:waenavi:20190626062914j:plain

 

金額の合計は、数量の合計に単価をかけますが、すでに数量合計を求めていますので、単に掛け算をするだけです。

f:id:waenavi:20190626062916j:plain

 

最後に達成率ですが、目標となる金額があります。これと3か月の金額の合計が近いです。これを比べて達成率を出そうとしていることは明らかです。

f:id:waenavi:20190626062919j:plain

 

実績/目標です。

f:id:waenavi:20190626062923j:plain

 

オートフィルをします。これで完成です。

f:id:waenavi:20190626062926j:plain

 

10.合計と合計の差額

問題

次の図で、収入と支出の差額を求めなさい。

f:id:waenavi:20190626062617j:plain

 

解説

収入の合計を求めます。オートSUMのボタンを押します。

f:id:waenavi:20190626063055j:plain

 

支出の合計を求めます。オートSUMのボタンを押します。家賃からその他までです。

f:id:waenavi:20190626063057j:plain

 

収入から支出を引きます。このように小計から小計を引くこともあります。

f:id:waenavi:20190626063100j:plain

 

これで完成です。

f:id:waenavi:20190626063103j:plain

 

11.SUMを使った構成比率

問題

次の図で各年代の人数の構成比率を求めなさい。

f:id:waenavi:20190626063202j:plain

 

解説

構成比率は、内訳/合計です。したがって、合計を求める必要があります。オートSUMのボタンを押します。

f:id:waenavi:20190626063555j:plain

 

合計人数は536人です。これで構成比率を求めます。合計は絶対参照です。

f:id:waenavi:20190626063558j:plain

 

これで完成です。

f:id:waenavi:20190626063601j:plain

 

別解

ところで、セルC2に合計を求めたとします。オートSUMボタンを押します。

f:id:waenavi:20190626064009j:plain

 

ここに536人と出ます。

f:id:waenavi:20190626064011j:plain

 

この数式の前に、「B2/」を付け足したら、合計で割ったことになりますので、構成比率になります。

f:id:waenavi:20190626064013j:plain

 

この数式をオートフィルをしてもうまくいかないのは、合計が絶対参照ではなく、相対参照のため合計の範囲が下に下がっているからです。

f:id:waenavi:20190626064017j:plain

 

SUM関数で絶対参照にするときには、かっこの中を全部選択して$マーク(F4キー)を付けます。

f:id:waenavi:20190626064020j:plain

 

オートフィルをします。これで完成です。

f:id:waenavi:20190626064024j:plain

 


解説は以上です。


12.動画版はこちら(無料)

この記事は、わえなび実力養成講座「ファンダメンタルExcel」Program 6-3、6-4、6-4-2 のYoutube動画を書き起こして、加筆修正したものです。

 

 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ