Contents
練習問題１／指定した複数列の範囲の合計を求める方法
【練習問題１ 問題】
下表は、当小学校の１学期間（４月始業式～７月終業式）の欠席者数を管理している表です。
小学校全体の欠席者数を求めてください。
▶練習問題のExcelデータをダウンロード
使用する関数が分からない、もしくは関数を知らない場合でも、自分が使いたい関数を調べることが出来れば、利用上問題ありません。ですので、分からない時に関数を調査する検索キーワードを学ぶことはとても重要です。本サイトに加え、Google、Yahoo！などを利用し検索してみましょう。この問題の場合、たとえば以下のようなキーワードを利用して検索すると目的の関数を見つけやすいので、検索キーワードがわからない場合は参考にして、検索をしてみてください。
【練習問題１ 解答】
解答は以下の通りです。
※上の図の解答はわかりやすいよう、小学校全体の欠席者数の解答を赤文字で記入しています。
【セル】 【関数式】 【 値 】
E8 ： =SUM(C4:D5,F4:G5,I4:J5) ⇒ 101
▶解答のExcelデータをダウンロード
【練習問題１ 解説】
各人数を求める流れは以下のようになります。
合計するにはSUM関数を利用します。
[数値1] = 合計を求めるセルの範囲を指定します。
[数値2・・・] = 省略可です。他に合計を求めるセルの範囲が有る場合に指定します。
SUM関数において、合計したいセルを選択していくと数字データが計算されます。
セルを指定する範囲において複数行、複数列を指定できます。
下の図のように、合計を求めるセル範囲が連続していない場合、「,」（カンマ）で区切って、別の範囲を指定できます。
下の図はセルE8の記載内容を例示しています。
【参考】SUM関数において、選択したセルが数値データの場合、全て合算されてしまいます。
下の図のように、 =SUM(C4:J5) としてまとめてセルを選択してしまうと
「１・２年生欠席日数平均」や「３・４年生欠席日数平均」も
小学校全体の欠席者数に合計されてしまいます。
求めたい合計と差異が出てしまうので、合計したいセルを関数に記載しましょう。
【練習問題１ まとめ】
Excelのセルの中で、数値データを合計するにはSUM関数を利用することを覚えましょう。
利用する機会が多い関数なので覚えて活用していきましょう。
セルを指定する範囲において複数行、複数列を指定できることや
合計を求めるセル範囲が連続していない場合、「,」（カンマ）で区切って、別の範囲を指定できることを確認しましょう。
[数値1] = 合計を求めるセルの範囲を指定します。
[数値2・・・] = 省略可です。他に合計を求めるセルの範囲が有る場合に指定します。
今回使用した関数は、以下の記事に詳しい使い方の説明が掲載されています。
今回の問題が難しいと感じた方は、ぜひ確認してみてください。
練習問題２／フィルタ表示されているセルの合計を求める方法／非表示は含めない
【練習問題２ 問題】
下表は、4月上旬の売上表です。
配送する商品の金額の10％を配送料として配送業者へ支払っています。
配送業者のシロネコヤマトへ支払う配送料合計をフィルタ機能を利用して求めてください。
配送業者の列にはフィルタが設定してあり、シロネコヤマトか佐山急便が選択できるようになっています。
なお、セルE22の配送料合計の金額はSUM関数を利用して既に求めています（赤字で記載しています）。
▶練習問題のExcelデータをダウンロード
使用する関数が分からない、もしくは関数を知らない場合でも、自分が使いたい関数を調べることが出来れば、利用上問題ありません。ですので、分からない時に関数を調査する検索キーワードを学ぶことはとても重要です。本サイトに加え、Google、Yahoo！などを利用し検索してみましょう。この問題の場合、たとえば以下のようなキーワードを利用して検索すると目的の関数を見つけやすいので、検索キーワードがわからない場合は参考にして、検索をしてみてください。
【練習問題２ 解答】
それでは解答を見ていきましょう。
※解答はわかりやすいよう、配送業者のシロネコヤマトへ支払う配送料合計の解答を青文字で記入しています。
※配送業者（F列）は「シロネコヤマト」を選択した状態です。
【セル】【関数式】【 値 】
E20 ：=SUBTOTAL(109,E4:E17)⇒ 40,000
【参考】
【セル】 【関数式】【 値 】
E22 ： =SUM(E4:E17) ⇒ 71,900
▶解答のExcelデータをダウンロード
【練習問題２ 解説】
配送料合計を求める流れは以下のようになります。
非表示のセルを含めずに集計を行うにはSUBTOTAL関数を利用します。
[集計方法] = 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関数で指定しました。
下の図にて、再度、ご確認ください。
【練習問題２ まとめ】
Excelのセルの中で、非表示のセルを含めずに集計を行うには
SUBTOTAL関数を利用することを覚えましょう。
[集計方法] について数字で指定するので、数字の意味を確認しましょう。
SUBTOTAL関数で算出方法に「SUM」を指定した場合とSUM関数との違いを確認しましょう。
[集計方法] = 1～11、または、101～111 の数字で指定します。それぞれの数字の意味は、上記の解説の「集計方法について・・」に記載しています。
[参照1] = 集計するセルの範囲を指定します。
[参照2・・・] = 省略可です。他に集計するセルの範囲が有る場合に指定します。
今回使用した関数は、以下の記事に詳しい使い方の説明が掲載されています。
今回の問題が難しいと感じた方は、ぜひ確認してみてください。
練習問題３／集計関数で計算されない書式データ
【練習問題３ 問題】
下表は、当小学校の１学期間（４月始業式～７月終業式）の欠席者数を管理している表です。
１年生、２年生は「低学年計」として、３年生、４年生は「中学年計」として、５年生、６年生を「高学年計」として小計したうえで、
「小学校全体の欠席者数」を計算しています。
明らかに欠席者がいるのに欠席者数の合計が「0」となっているセルが有るのを見つけました。
※下図において欠席者の合計が「0」となっているセルはわかりやすいよう赤文字で記入しています。
１．欠席者の合計が「0」となっている理由を答えてください。
２．データを修正し、計算できるデータに変換して、［男子の低学年計］および［女子の中学年計］が正しく計算されるようにしてください。
３．小学校全体の欠席者数（セルE8）に上記２番の結果が反映されるのを確認してください。
▶練習問題のExcelデータをダウンロード
使用する関数が分からない、もしくは関数を知らない場合でも、自分が使いたい関数を調べることが出来れば、利用上問題ありません。ですので、分からない時に関数を調査する検索キーワードを学ぶことはとても重要です。本サイトに加え、Google、Yahoo！などを利用し検索してみましょう。この問題の場合、たとえば以下のようなキーワードを利用して検索すると目的の関数を見つけやすいので、検索キーワードがわからない場合は参考にして、検索をしてみてください。
【練習問題３ 解答】
それでは解答を見ていきましょう。
※上の図の解答はわかりやすいよう、欠席者の合計が「0」となっていたセルを赤文字で記入しています。
１．計算されていない理由を答えてください。
→【解答例】データ書式が文字列になっている（数値データではない）。
２．データを修正し、計算できるデータに変換して、［男子の低学年計］および［女子の中学年計］が計算されるようにしてください。
→［男子の低学年計］計算されて、0（人）から18（人）に変更されているのを確認してください。
→［女子の中学年計］計算されて、0（人）から22（人）に変更されているのを確認してください。
３．小学校全体の欠席者数に上記２番の結果が反映されるのを確認してください。
→61（人）から101（人）に変更されているのを確認してください。
▶解答のExcelデータをダウンロード
【練習問題３ 解説】
セルの書式の確認方法と変換方法の流れは以下のようになります。
今回、利用しているSUM関数は指定されたセルの全ての数値データを合計します。
[数値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人となり、データ変換が反映されているかご確認ください。
【練習問題３ まとめ】
Excelのセルの中で、数値データを合計するにはSUM関数を利用することを覚えましょう。
利用する機会が多い関数なので覚えて活用していきましょう。
SUM関数を利用しているのにデータが合計されていない場合、今回のように数値データではなく、
文字列として扱われている可能性が有ります。データの書式の確認方法や変換方法を覚えましょう。
[数値1] = 合計を求めるセルの範囲を指定します。
[数値2・・・] = 省略可です。他に合計を求めるセルの範囲が有る場合に指定します。
今回使用した関数は、以下の記事に詳しい使い方の説明が掲載されています。
今回の問題が難しいと感じた方は、ぜひ確認してみてください。
