練習問題1/指定した複数列の範囲の合計を求める方法

【練習問題1 問題】

下表は、当小学校の1学期間(4月始業式~7月終業式)の欠席者数を管理している表です。
小学校全体の欠席者数を求めてください。

▶練習問題のExcelデータをダウンロード

Hints!

使用する関数が分からない、もしくは関数を知らない場合でも、自分が使いたい関数を調べることが出来れば、利用上問題ありません。ですので、分からない時に関数を調査する検索キーワードを学ぶことはとても重要です。本サイトに加え、Google、Yahoo!などを利用し検索してみましょう。この問題の場合、たとえば以下のようなキーワードを利用して検索すると目的の関数を見つけやすいので、検索キーワードがわからない場合は参考にして、検索をしてみてください。

【検索キーワード】
合計する関数がわからない場合は・・・Excel 合計 関数
本サイトの検索ボックス
※上記以外にも検索できるキーワードがあります。

 

【練習問題1 解答】

解答は以下の通りです。

※上の図の解答はわかりやすいよう、小学校全体の欠席者数の解答を赤文字で記入しています。

【セル】 【関数式】   【 値 】 
E8 : =SUM(C4:D5,F4:G5,I4:J5)  ⇒   101  

▶解答のExcelデータをダウンロード

【練習問題1 解説】

各人数を求める流れは以下のようになります。
合計するにはSUM関数を利用します。

=SUM(数値1, [数値2・・・])
[数値1] = 合計を求めるセルの範囲を指定します。
[数値2・・・] = 省略可です。他に合計を求めるセルの範囲が有る場合に指定します。

SUM関数において、合計したいセルを選択していくと数字データが計算されます。
セルを指定する範囲において複数行、複数列を指定できます。

下の図のように、合計を求めるセル範囲が連続していない場合、「,」(カンマ)で区切って、別の範囲を指定できます。
下の図はセルE8の記載内容を例示しています。

【参考】SUM関数において、選択したセルが数値データの場合、全て合算されてしまいます。
下の図のように、 =SUM(C4:J5) としてまとめてセルを選択してしまうと
「1・2年生欠席日数平均」や「3・4年生欠席日数平均」も
小学校全体の欠席者数に合計されてしまいます。
求めたい合計と差異が出てしまうので、合計したいセルを関数に記載しましょう。

【練習問題1 まとめ】

Excelのセルの中で、数値データを合計するにはSUM関数を利用することを覚えましょう。
利用する機会が多い関数なので覚えて活用していきましょう。
セルを指定する範囲において複数行、複数列を指定できることや
合計を求めるセル範囲が連続していない場合、「,」(カンマ)で区切って、別の範囲を指定できることを確認しましょう。

=SUM(数値1, [数値2・・・])
[数値1] = 合計を求めるセルの範囲を指定します。
[数値2・・・] = 省略可です。他に合計を求めるセルの範囲が有る場合に指定します。
今回使用した関数は、以下の記事に詳しい使い方の説明が掲載されています。
今回の問題が難しいと感じた方は、ぜひ確認してみてください。

データをカウントする
指定した複数の列の合計を求める/SUM関数について解説します。

 

練習問題2/フィルタ表示されているセルの合計を求める方法/非表示は含めない

【練習問題2 問題】

下表は、4月上旬の売上表です。
配送する商品の金額の10%を配送料として配送業者へ支払っています。
配送業者のシロネコヤマトへ支払う配送料合計をフィルタ機能を利用して求めてください。
配送業者の列にはフィルタが設定してあり、シロネコヤマトか佐山急便が選択できるようになっています。
なお、セルE22の配送料合計の金額はSUM関数を利用して既に求めています(赤字で記載しています)。

▶練習問題のExcelデータをダウンロード

Hints!

使用する関数が分からない、もしくは関数を知らない場合でも、自分が使いたい関数を調べることが出来れば、利用上問題ありません。ですので、分からない時に関数を調査する検索キーワードを学ぶことはとても重要です。本サイトに加え、Google、Yahoo!などを利用し検索してみましょう。この問題の場合、たとえば以下のようなキーワードを利用して検索すると目的の関数を見つけやすいので、検索キーワードがわからない場合は参考にして、検索をしてみてください。

【検索キーワード】
集計する関数がわからない場合は・・・Excel 集計 関数
非表示のセルを合計から除外する関数がわからない場合は・・・Excel 合計 非表示 含めない 関数
非表示のセルも合計する場合は・・・Excel 合計 非表示 含める 関数
本サイトの検索ボックス
※上記以外にも検索できるキーワードがあります。

 

【練習問題2 解答】

それでは解答を見ていきましょう。

※解答はわかりやすいよう、配送業者のシロネコヤマトへ支払う配送料合計の解答を青文字で記入しています。

※配送業者(F列)は「シロネコヤマト」を選択した状態です。

【セル】【関数式】【 値 】   
E20 :=SUBTOTAL(109,E4:E17)⇒ 40,000   

【参考】

【セル】 【関数式】【 値 】        
E22 : =SUM(E4:E17)  ⇒   71,900          

▶解答のExcelデータをダウンロード

【練習問題2 解説】

配送料合計を求める流れは以下のようになります。
非表示のセルを含めずに集計を行うにはSUBTOTAL関数を利用します。

=SUBTOTAL(集計方法, 参照1, [参照2・・・])
[集計方法] = 1~11、または、101~111 の数字で指定します。それぞれの数字の意味は、下記「集計方法について・・」で解説いたします。
[参照1] = 集計するセルの範囲を指定します。
[参照2・・・] = 省略可です。他に集計するセルの範囲が有る場合に指定します。

 

SUBTOTALの[集計方法]について・・・

小計の算出に使用する集計方法を、指定します。 1 ~ 11 には手動で非表示にした行が含まれますが、101 ~ 111 には含まれません。

【集計方法一覧】

指定する値
(非表示も含める)
指定する値
(非表示は含めない)
関数(算出方法)
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

配送業者の列にフィルタが設定されていることを確認してください。

青字のようにセルE20に=SUBTOTAL(109,E4:E17)を記載します。
比較できるように赤字でE22には=SUM(E4:E17)を記載しています。SUM関数は表示/非表示に関係なく、数値データは全て集計されます。

今回は「配送業者のシロネコヤマトへ支払う配送料合計」を求めたいので、
フィルタの設定で「シロネコヤマト」を表示させます。
下の図のように「配送業者」と書かれているセルの右端に▼マークのボタンが有ります。
そのボタンを押すとフィルタの設定を変更できます。

「佐山急便」のチェックを外して、「シロネコヤマト」だけがチェックされている状態にして、「OK」ボタンを押してください。

下の図のように配送業者(F列)には「シロネコヤマト」だけが表示されています。
E20の配送料合計の金額も40,000円に変更されました。

参考として、フィルタの設定を変更して、佐山急便についても配送料合計を確認してみましょう。
「シロネコヤマト」を設定した時と同様に、下の図のようにフィルタで「佐山急便」を設定し「OK」ボタンを押します。

下の図のようにE20の金額が31,900円に変わりました。

今回は算出方法が「SUM」、非表示を含めない合計を計算するために、「109」をSUBTOTAL関数で指定しました。
下の図にて、再度、ご確認ください。

【練習問題2 まとめ】

Excelのセルの中で、非表示のセルを含めずに集計を行うには
SUBTOTAL関数を利用することを覚えましょう。
[集計方法] について数字で指定するので、数字の意味を確認しましょう。
SUBTOTAL関数で算出方法に「SUM」を指定した場合とSUM関数との違いを確認しましょう。

=SUBTOTAL(集計方法, 参照1, [参照2・・・])
[集計方法] = 1~11、または、101~111 の数字で指定します。それぞれの数字の意味は、上記の解説の「集計方法について・・」に記載しています。
[参照1] = 集計するセルの範囲を指定します。
[参照2・・・] = 省略可です。他に集計するセルの範囲が有る場合に指定します。

今回使用した関数は、以下の記事に詳しい使い方の説明が掲載されています。
今回の問題が難しいと感じた方は、ぜひ確認してみてください。

データをカウントする
指定した範囲の合計を求める/SUBTOTAL関数について解説します。

 

練習問題3/集計関数で計算されない書式データ

【練習問題3 問題】

下表は、当小学校の1学期間(4月始業式~7月終業式)の欠席者数を管理している表です。
1年生、2年生は「低学年計」として、3年生、4年生は「中学年計」として、5年生、6年生を「高学年計」として小計したうえで、
「小学校全体の欠席者数」を計算しています。
明らかに欠席者がいるのに欠席者数の合計が「0」となっているセルが有るのを見つけました。

※下図において欠席者の合計が「0」となっているセルはわかりやすいよう赤文字で記入しています。

1.欠席者の合計が「0」となっている理由を答えてください。
2.データを修正し、計算できるデータに変換して、[男子の低学年計]および[女子の中学年計]が正しく計算されるようにしてください。
3.小学校全体の欠席者数(セルE8)に上記2番の結果が反映されるのを確認してください。

▶練習問題のExcelデータをダウンロード

Hints!

使用する関数が分からない、もしくは関数を知らない場合でも、自分が使いたい関数を調べることが出来れば、利用上問題ありません。ですので、分からない時に関数を調査する検索キーワードを学ぶことはとても重要です。本サイトに加え、Google、Yahoo!などを利用し検索してみましょう。この問題の場合、たとえば以下のようなキーワードを利用して検索すると目的の関数を見つけやすいので、検索キーワードがわからない場合は参考にして、検索をしてみてください。

【検索キーワード】
合計する関数がわからない場合は・・・Excel 合計 関数
合計が計算されない場合は・・・Excel 合計 関数 計算されない
本サイトの検索ボックス
※上記以外にも検索できるキーワードがあります。

 

【練習問題3 解答】

それでは解答を見ていきましょう。

※上の図の解答はわかりやすいよう、欠席者の合計が「0」となっていたセルを赤文字で記入しています。

1.計算されていない理由を答えてください。

→【解答例】データ書式が文字列になっている(数値データではない)。

2.データを修正し、計算できるデータに変換して、[男子の低学年計]および[女子の中学年計]が計算されるようにしてください。

→[男子の低学年計]計算されて、0(人)から18(人)に変更されているのを確認してください。
→[女子の中学年計]計算されて、0(人)から22(人)に変更されているのを確認してください。

3.小学校全体の欠席者数に上記2番の結果が反映されるのを確認してください。

→61(人)から101(人)に変更されているのを確認してください。

▶解答のExcelデータをダウンロード

 

【練習問題3 解説】

セルの書式の確認方法と変換方法の流れは以下のようになります。
今回、利用しているSUM関数は指定されたセルの全ての数値データを合計します。

=SUM(数値1, [数値2・・・])
[数値1] = 合計を求めるセルの範囲を指定します。
[数値2・・・] = 省略可です。他に合計を求めるセルの範囲が有る場合に指定します。

(a) セルの書式の確認方法について

まず、下の図のように表示を変えたいセル(今回の場合はC4からD4の複数のセル)を選択しておきます。
その状態で右クリックするとメニューが表示されます。
セルの書式設定」をクリックします。

 

下の図のように「セルの書式設定」という別のメニューが開きました。
表示形式」のタブを見ると「文字列」になっているのが確認できます。
青枠内の説明が有ります。
[文字列]は、数値も文字列として扱います。セルには入力した値がそのまま表示されます。
あわせてご確認ください。

 

(b) セルの書式の変換方法について

セルの書式設定」の「表示形式」のタブを選択してください。
分類の「数値」を選択してください。
OK」ボタンを押してください。

 

(c) データ変換後の確認について

①「低学年計」の人数が「0」人から「18」人へ変更されている。
②「小学校全体の欠席者数」の人数が「61」人から「79」人へ変更されている。


 
(d) データ変換しても計算されない場合の対処について

セルC4を選択してダブルクリックすると
数値データの前に「’」(クォーテ-ション)が入っています。
この場合、セルの数値が文字列として扱われています。
この「’」(クォーテ-ション)を削除して、Enterキーを押してください。

セルC4の修正後、下の図のように「低学年計(セルE4)」の人数が「0」人から「10」人へ
小学校全体の欠席者数(セルE8)」の人数が「61」人から「71」人へ変更されているのをご確認ください。

同様に上の図の緑枠のセルD4、F5、G5についても、「’」(クォーテ-ション)を削除して、Enterキーを押してください。
その後、下の図の解答(上記の解答と同一内容です)と、「低学年計」が18人、「中学年計」が22人、「小学校全体の欠席者数」が101人となり、データ変換が反映されているかご確認ください。

【練習問題3 まとめ】

Excelのセルの中で、数値データを合計するにはSUM関数を利用することを覚えましょう。
利用する機会が多い関数なので覚えて活用していきましょう。
SUM関数を利用しているのにデータが合計されていない場合、今回のように数値データではなく、
文字列として扱われている可能性が有ります。データの書式の確認方法や変換方法を覚えましょう。

=SUM(数値1, [数値2・・・])
[数値1] = 合計を求めるセルの範囲を指定します。
[数値2・・・] = 省略可です。他に合計を求めるセルの範囲が有る場合に指定します。

今回使用した関数は、以下の記事に詳しい使い方の説明が掲載されています。
今回の問題が難しいと感じた方は、ぜひ確認してみてください。

データをカウントする
SUM関数で計算されないパターン/データ型「文字列」について解説します。