エクセル（Excel）/ ｢SUM 関数を使って、集計シートを各シートの時数集計欄に反映させる｣ ～ エクセルで作った簡単な学習計画を例に
前回は、表計算ソフト｢エクセル｣(Excel)で作った簡単な学習計画を例に、｢COUNTIF 関数や SUM 関数を使って作成された集計シート｣を紹介しました。また、｢INDIRECT 関数を使ったシート・セル参照｣も紹介しました。
今回は、同じ学習計画を例に、｢SUM 関数を使って、集計シートを各シートの時数集計欄に反映させる｣方法や、｢IFERROR 関数を使って集計シートのエラー表示を空白セルにする方法｣を紹介します。
※ 操作は、Office365版Excelで行っています。
集計シートの内容
入力されている数式
集計シート
＜図１＞は、ブック｢エクセルで作った簡単な学習計画｣のシートで、シート見出しは｢集計｣である。
＜図１＞
※ このブックのタイトルは、この記事用に変更したものです。また、このシートは提出用ではなく、あくまで、各シートの集計欄にこの数値を反映させるために作られたものです。
※ シート見出し｢集計｣を、以下、集計シートと呼ぶことにします。
１学期のコピー
※ 集計シート｢２学期分｣の最初にある｢B12｣セル～｢B15｣セルは、集計シート｢１学期分｣｢B4｣セル～｢B7｣セルをコピーして作成しました。
※ 集計シート｢２学期分｣を作成した手順を再現するために、｢B12｣セル～｢B15｣セルの数値(数式)を空欄にします。＜図２＞
＜図２＞
① ｢B4｣セル～｢B7｣セルをドラッグ(マウス左ボタンを押したまま移動)して選択後＜図３＞、②［Ctrl］キーと［Ｃ］キーを押してコピー＜図４＞、③コピー先の先頭となる｢B12｣セルをクリックして(マウス左ボタンを押して)選択後＜図５＞、④最後に［Ctrl］キーと［Ｖ］キーを押して貼り付ける。＜図６＞
＜図３＞
＜図４＞
＜図５＞
＜図６＞
貼り付けた後の数式
｢B12｣セル～｢B15｣セルの数式をセル内で表示すると＜図７＞にようになっている。
＜図７＞
※ セル内に数式を表示するには、｢数式｣タブの｢ワークシート分析｣グループにある［数式の表示］ボタンをクリックします。
(数式のセル内表示を解除するときは、［数式の表示］ボタンを再度クリックします)
※ INDIRECT 以下の( )内は、｢B11｣セル内に入力してある 12 をシート見出しとした｢シート12｣の｢A6｣セル～｢M16｣セルを｢参照シート・セル｣にするという意味です。INDIRECT は、インダイレクトと読みます。
※ また、COUNTIF の最後にあるクラス名は、INDIRECT関数で設定した｢参照シート・セル｣内の各｢クラス名｣の個数をカウントするということです。COUNTIF は、カウントイフと読みます。
※ 文字列は、" (ダブルクォーテーションマーク)で囲みます。""は、空白セルを表します。
※ : (コロン)は、セル参照範囲を表します。
※ 集計シートで使っている関数については、下記リンクをご覧ください。
｢２学期｣分全体の作成
コピー元を選択
※ ｢C12｣セル～｢N15｣セルの数値(数式)を空欄にした後、集計シート｢B12｣セル～｢B15｣セルに入力されている数式をコピー元とし、集計シート｢M12｣セル～｢M15｣セルへコピーします。
｢B12｣セル～｢B15｣セルをドラッグ(マウス左ボタンを押したまま移動)して選択し、コピー元にする。＜図８＞
＜図８＞
｢M列｣までコピーする
※ ｢オートフィル機能｣を使って、４つのセルを｢M列｣までコピーします。
｢B15｣セル右下のフィルハンドルにマウスポインターを合わせると、マウスポインターが黒い十字に変わるので、それを ｢M15｣セルまでドラッグしてコピーする。＜図９の１＞
＜図９の１＞
マウス左ボタンを離すと、数式がコピーされ、各セルに数値が表示される。＜図９の２＞
＜図９の２＞
SUM 関数で合計を求める
集計シートの合計欄に合計を求める SUM (サム)関数を入力するため、合計欄の｢N12｣セルをクリックして選択後、 ｢ホーム｣タブの｢編集｣グループにある［オートSUM］ボタンをクリックする。＜図10の１＞
＜図10の１＞
数式バーに ＝SUM()と表示されるので、合計したい範囲｢B12｣セル～｢M12｣をドラッグした後＜図10の２＞、［Enter］キーで確定する。
＜図10の２＞
合計欄｢N12｣セルを同じ列内にコピー
合計欄の｢N12｣セルを選択後＜図11の１＞、｢オートフィル機能｣を使って、同じ列内の｢N13｣セル～｢N15｣セルへコピーする。＜図11の２＞
＜図11の１＞
＜図11の２＞
｢シート19｣の時数集計欄
時数集計欄の数式
｢シート19｣の数式の表示
｢シート19｣の｢K20｣セルをクリックして選択＜図12の１＞、［数式の表示］ボタンで、｢シート19｣時数集計欄の２学期の数式をセル内に表示させる。＜図12の２＞
＜図12の１＞
＜図12の２＞
｢シート18｣コピー直後
｢４の１｣(｢K20｣セル)の２学期時数の数式が =SUM(集計!B12:H12)と表示されている。＜図13＞
＜図13＞
※ ｢シート19｣は、｢シート18｣をコピーして作成後、シート見出しを｢19｣に変更し、新しい学習計画を入力したものです。
※ ただし、時数の部分は｢シート18｣をコピーしたままの数値(数式)になっています。＜図14の１＞
※ そこで、集計シートですでに計算されている時数を、｢シート19｣の時数集計欄に反映させる作業、具体的には、集計シートの｢B12｣セル～｢I12｣セルまでの合計＜図14の２＞に変える作業が必要となります。
＜図14の１＞
＜図14の２＞
※ ＜図14の１＞と＜図14の２＞を比べると、４の１、４の２、４の３はそれぞれ10時間、４の４は９時間、２学期時数が増えることになります。
時数集計欄の列を修正する
時数集計欄の｢K20｣セルをクリックして選択、数式バーのH12の H をドラッグして反転表示(そのまま入力できる)状態で＜図15の１＞、 I (アイ)を入力し＜図15の２＞、［Enter］(エンター)キーで数式を確定する。
＜図15の１＞
＜図15の２＞
※ ｢４の１｣(｢K20｣セル)の時数集計欄に反映されている集計シート｢B列｣～｢H列｣を１列増やして、集計シート｢B列｣～｢I列｣にしました。合計の数値が 47 から 57 に変わりました。＜図15の３＞
＜図15の３＞
他のクラスにコピーする
｢オートフィル機能｣を使って、４の１の集計を他のクラス(｢K21｣セルから｢K23｣セル)へコピーする。＜図16＞
＜図16＞
※ 他のクラスの合計の数値も変わりました。
時数集計欄の数式
＜図17＞は、［数式の表示］ボタンをクリックし、時数集計欄の数式をすべて表示したものである。(列の幅は変えています)
＜図17＞
※ １学期、２学期の各クラスの時数集計は、集計シートに表示された数値の合計をこの時数集計欄に反映させていることがわかります。
※ ｢合計｣と｢年間時数計｣は、この時数集計欄の数値を SUM 関数で合計していることがわかります。
｢シート19｣の全体
下図は、時数集計欄の数式を変更し、数値が変わった｢シート19｣の全体です。
集計シートのエラー表示
エラー表示を空白セルにする
※ 集計シートのエラー表示が気になるときは、IFERROR (イフエラー)関数を使うことができます。
２学期のコピー元セル
集計シートの｢J12｣セル～｢M16｣セルがエラーになっている。
｢J12｣セルを選択し、数式バーを見ると、COUNTIF 関数と INDIRECT 関数を組み合わせた数式が入力されているが、｢J11｣セル内に入力されている 20 をシート見出しとした｢シート20｣がないので、エラー表示になっている。＜図18＞
＜図18＞
IFERROR 関数
｢J12｣セルをクリックして選択、数式バーの = (イコール)と COUNTIF の C の間にカーソルを置き、if と入力すると表示される関数名(ここでは｢関数名のリスト｣とよびます)の中から IFERROR をダブルクリック＜図19の１＞すると、数式バーに IFERROR ( と表示される。＜図19の２＞
＜図19の１＞
＜図19の２＞
※ IFERROR 関数は、数式がエラーと評価された場合に指定した値を返し、それ以外の場合は、数式の結果が返されます。
エラー表示を空白セルにしたい
① IFERROR( の後の COUNTIF 以下の数式はそのままで後ろに , (カンマ)を入力＜図20の１＞、② 次にエラー表示を｢空白セル｣にしたいので""を入力、③ 数式の最後を ) で閉じ＜図20の２＞、 ［Enter］(エンター)キーで数式を確定する。
＜図20の１＞
＜図20の２＞
※ ｢J12｣セルが空白になります。＜図21＞
＜図21＞
※ 集計シート｢B12｣セルから｢B15｣セルに数式を入力する段階で、 IFERROR 関数を入れておくこともできます。＜図22＞
＜図22＞
※ すると、新しいシートをコピーして作成し、シート見出しを変えたときだけ、COUNTIF 関数による数値が表示され、シートがないセルは、エラー表示ではなく、空白セルを表示するような集計シートを作ることができます。＜図23＞
＜図23＞
今回は、前回紹介した集計シートを、｢SUM 関数を使って各シートの時数集計欄に反映させる方法｣や、｢IFERROR 関数を使って集計シートのエラー表示を空白セルにする方法｣を紹介しました。繰り返しになりますが、関数の使い方が効率的でない部分があると思います。一例として参考にしていただければ幸いです。
簡単な学習計画を例にした｢エクセル｣(Excel)の操作方法については、今回で終わります。
