エクセル（Excel）/ ｢COUNTIF 関数や SUM 関数を使って集計シートを作る｣ ～ エクセルで作った簡単な学習計画を例に

前回は、表計算ソフト｢エクセル｣(Excel)で作った簡単な学習計画を例に、｢シートをドラッグしてコピーする方法｣の他、｢シート見出しをダブルクリックしてシート名を変更する方法｣、｢セルを結合せず、選択範囲内で中央に配置する方法｣、｢『書式』を検索する方法｣について紹介しました。

今回は、同じ学習計画を例に、｢COUNTIF 関数や SUM 関数を使って作成した集計シート｣を紹介します。

※　操作は、Office365版Excelで行っています。

時数を集計するシート

コピー元のセルを作成する

集計シート

＜図１＞は、ブック｢エクセルで作った簡単な学習計画｣のシートで、シート見出しは｢集計｣である。
＜図１＞

※ このブックのタイトルは、この記事用に変更したものです。また、このシートは提出用ではなく、あくまで、各シートの集計欄にこの数値を反映させるために作られたものです。

※ 各シートは、前回の記事で｢シート19｣まで作成しました。

※ シート見出し｢集計｣を、以下、集計シートと呼ぶことにします。

集計シートの作成

｢B4｣セルをクリックする(マウスの左ボタンを押す)と、数式バーに COUNTIF (カウントイフ)から始まる数式が表示される。＜図２＞
＜図２＞

※ COUNTIF 関数は、｢指定した範囲内で、1 つの検索条件に一致するセルの個数を返す｣関数です。

※ ｢B4｣セルの数値｢7｣は、｢シート1｣の｢A6｣セル～｢M16｣セルの中の｢４の１｣の個数をカウントしたものです。

COUNTIF 関数の入力

※ COUNTIF 関数を入力してみます。

｢B4｣セルをクリックして選択、数式バーを空欄にした後、数式の入力が始まることを意味する = (イコール)を数式バーに直接入力する。＜図３＞
＜図３＞

｢関数名のリスト｣

= (イコール)に続いて、cou と入力すると、cou で始まる関数名(ここでは｢関数名のリスト｣とよびます)が表示される。

｢関数名のリスト｣の中から COUNTIF をダブルクリック＜図４の１＞すると、数式バーに COUNTIF( と表示される。＜図４の２＞
＜図４の１＞

＜図４の２＞

シート見出しをクリック

｢シート1｣の中を検索するので、シート見出しの 1 をクリックする。クリックと同時に数式バーに '1'! と表示される。＜図５＞
＜図５＞

※ 数式では、シート見出しに ! (エクスクラメーションマーク)が付きます。

※ また、数字で始まるシート見出しは、' (シングルクォーテーションマーク)で囲みます。

※ 数式バーの '1'! は、｢シート1｣を表します。シート見出しをクリックしないで、数式バーに直接 '1'! と入力することもできます。

セル範囲をドラッグ

｢シート1｣の左側の週の１時間目｢A6｣セルから右側の週の６時間目｢M16｣セルまでの間をドラッグ(マウス左ボタンを押したまま移動)すると、ドラッグしたセルの範囲が緑の点線の枠で囲まれ、ドラッグと同時に、数式バーにセル範囲が表示される。＜図６＞
＜図６＞

※ 数式バーには、参照するセル範囲が A6:M16 と表示されます。

※ セル範囲を事前に｢A6～M16｣とメモしておいて(覚えておいて)、数式バーに直接 A6:M16 と入力してもかまいません。

※ : (コロン)は、セル参照範囲を表します。

セル範囲を絶対参照に変える

数式をコピーしても、参照先のセルが移動しないように、［Ｆ４］キーを押し、参照するセル範囲を絶対参照｢$A$6:$M$16｣に変える。＜図７＞
＜図７＞

カンマを入力する

範囲指定が終わったので、, (カンマ)を入力する。＜図８＞
＜図８＞

検索条件を入力する

目的は、｢４の１｣の時数をカウントすることなので、検索条件に"４の１"と入力し、最後に数式を ) で閉じる。＜図９＞
＜図９＞

※ 文字列は、" (ダブルクォーテーションマーク)で囲みます。

※ 数式は、ここまで半角英数モードで行ってきましたが、この"４の１"だけは、４も１も全角で入力します。
(各シートのクラス名の表記が全角で入力されていて、それを探してカウントするからです)

エンターキーを押す

［Enter］(エンター)キーを押すと、集計シートの｢B4｣セルに 7 と表示される。＜図10の１＞
＜図10の１＞

｢シート1｣の学習計画を見ると｢４の１｣が７時間あることがわかる。＜図10の２＞
＜図10の２＞

数式を同じ行へコピーする

※ 集計シート｢B4｣セルに入力されている数式を、｢オートフィル機能｣(セル右下のフィルハンドルをドラッグしてコピーする)を使って同じ行番号｢L4｣のセルまでコピーします。

オートフィル機能を使う

｢B4｣セルをクリックして選択、｢緑色の太枠｣右下の｢四角形のフィルハンドル｣にマウスポインター(以下、ポインターとよびます)を合わせると、ポインターが｢黒い十字｣に変わる。＜図11の１＞
＜図11の１＞

｢黒い十字｣を｢L4｣セルまでドラッグ＜図11の２＞し、マウス左ボタンを離すと、数式がコピーされ、各セルに数値が表示される。＜図11の３＞
＜図11の２＞

＜図11の３＞

※ 各セルの数値がすべて 7 になっていて、すべてのセルに同じ数式がコピーされているのが予想されます。後で、他の関数を使用して修正しますので、このまますすめます。

SUM 関数で合計を求める

集計シートの合計欄に合計を求める SUM (サム)関数を入力するため、合計欄の｢M4｣セルをクリックして選択する。

SUM 関数の入力方法は、

① ｢ホーム｣タブの｢編集｣グループにある［オートSUM］ボタンをクリックし、参照範囲が合っていれば＜図12の１＞、［Enter］キーで確定する。(合っていなければ、参照したい範囲をドラッグして、［Enter］キーで確定する)
＜図12の１＞

② 数式バーに =SUM() と入力し、()内にカーソルを置き、｢B4｣セル～｢L4｣セルをドラッグして参照範囲を決め＜図12の２＞、［Enter］キーで確定する。
＜図12の２＞

③ 数式バーに直接 =SUM(B4:L4) と入力し、［Enter］キーで確定する。

など、いろいろな方法がある。

合計欄｢M4｣セルを同じ列内にコピー

｢オートフィル機能｣を使って、合計欄の｢M4｣セルを同じ列内の｢M5｣セル～｢M7｣セルへコピーする。＜図13＞
＜図13＞

※ ここでは、｢オートフィル機能｣を使ってコピーした後の数式が、どのように変わっているか調べることにします。

※ ｢数式｣タブの｢ワークシート分析｣グループにある［数式の表示］ボタンをクリックし、数式バーの数式をセル内に表示させて確認します。＜図14＞
＜図14＞

※ コピー元のセル範囲 B4:L4 が相対参照なので、コピー後の参照先のセル範囲が、自動的に変更された数式(１行ずつ行番号がずれた数式)になっています。｢オートフィル機能｣の便利さがわかります。

※ 数式のセル内表示を解除するときは、［数式の表示］ボタンを再度クリックします。

シート見出しにオートフィル機能を使う

オートフィル機能が使える文字列へ

変わっていないシート見出し

集計シート｢B4｣セルに入力されている数式を、｢オートフィル機能｣を使って同じ行のセルへコピーしたが、シート見出しは '1'! のままで変わらなかった。＜図15＞
＜図15＞

INDIRECT 関数を挿入する

｢B4｣セルの数式バーに表示されている '1'!$A$6:$M$16 を消し、in と入力する。

表示された｢関数名のリスト｣の中から INDIRECT (インダイレクト)をダブルクリック＜図16の１＞すると、数式バーに INDIRECT( と表示される。＜図16の２＞
＜図16の１＞

＜図16の２＞

※ INDIRECT 関数は、｢指定される文字列への参照を返す｣関数です。

シート名が入ったセルを選択

シート見出し 1 が入力されている｢B3｣セルをクリックし、＆(アンパサンド)を入力する。＜図17＞
＜図17＞

※ ＆(アンパサンド)は、文字列を結合する演算子です。

COUNTIF 関数と INDIRECT 関数の組み合わせ

"!A6:M16"を入力し、 ) で閉じた後＜図18の１＞、［Enter］キーで数式を確定し、再び｢B4｣セルをクリックして選択する。＜図18の２＞
＜図18の１＞

＜図18の２＞

※ 文字列を結合する＆(アンパサンド)、文字列を表す " (ダブルクォーテーションマーク)、シート見出しを表す ! (エクスクラメーションマーク)を入力しました。

※ このことで、(B3&"!A6:M16")は、｢B3｣セル内に入力されている文字とシート見出しを表す ! が文字列として認識されます。つまり、文字列 1 ! となり、｢シート1｣を意味するようになります。

※ また、"!A6:M16"と入力することで、｢A6｣セルから｢M16｣セルを文字列として表す(参照元が移動しない)ようになります。

４クラス分のコピー元のセルを作る

※ INDIRECT関数を挿入した｢B4｣セルの数式を、｢オートフィル機能｣を使って同じ列のセルへコピーします。

｢B4｣セルを同じ列内にコピー

｢オートフィル機能｣を使って、｢B4｣セルを同じB列の｢B5｣セル～｢B7｣セルへコピーする。＜図19＞
＜図19＞

※ ｢オートフィル機能｣を使ってコピーした後の数式を［数式の表示］ボタンをクリックし、セル内に表示させて確認します。＜図20＞
＜図20＞

※ コピー元のシート名を表す B3 が相対参照なので、コピー後の参照先のセル範囲(シート名)が、自動的に変更された数式(１行ずつ行番号がずれた数式)になっています。

※ 結果として、｢オートフィル機能｣ではできなかった｢参照するシート名｣の変更＜図15参照＞が可能になっています。

※ ここでは、同じ列内でコピーしたので行番号が自動的に変更されましたが、同じ行内でコピーすると列番号が自動的に変更されることが予想されます。

参照シートとクラス名を修正する

数式バーの中の｢B5｣セル～｢B7｣セルの参照シートをすべて｢B3｣セルにし、検索条件を｢４の１｣から、各クラスの名前に変える。

以下、｢４の３｣の修正を例示する。

｢B6｣セルの数式バーの中で、

① 参照シート B5 をドラッグして反転表示(そのまま入力できる)状態で、｢B3｣セルをクリックする。＜図21の１＞
＜図21の１＞

② "４の１"の１をドラッグして反転表示(そのまま入力できる)状態で、全角で３と入力する。＜図21の２＞
＜図21の２＞

③ 入力を確定した後、［Enter］キーで数式を確定する。その後、再び｢B6｣セルをクリックして数式バーを見る。＜図21の３＞
＜図21の３＞

※ もちろん、数式バーに直接入力して修正してもかまいません。

修正後の数式を確認する

※ ｢B5｣セルから｢B7｣セルの数式の修正がすべて終わった後、数式を確認する場合は、［数式の表示］ボタンをクリックし、修正後の数式をセル内に表示させます。＜図22＞
＜図22＞

４クラスのセルを一度にコピーする

コピー元を選択

｢B4｣セルから｢B7｣セルをドラッグして選択し、この４つのセルをコピー元にする。＜図23＞
＜図23＞

｢L列｣までコピーする

｢オートフィル機能｣を使って、４つのセルを｢L列｣までコピーする。＜図24＞
＜図24＞

※ ｢B4｣セルを｢L4｣セルへ、｢B5｣セルを｢L5｣セルへ、｢B6｣セルを｢L6｣セルへ、｢B7｣セルを｢L7｣セルへ、１行ずつ｢オートフィル機能｣を使ってコピーしても同じ結果になります。

コピー後の数式の確認

※ ｢オートフィル機能｣を使ってコピーした後の数式を［数式の表示］ボタンをクリックし、セル内に表示させて確認します。＜図25＞
＜図25＞

※ シート見出しを表す B3 が相対参照なので、コピー後の参照先のセル範囲(シート名)が、自動的に変更された数式(１列ずつ列番号がずれた数式)になっています。

※ また、各シートのセル範囲は変わっていません。

※ INDIRECT 関数を使ったシート・セル参照の便利さがわかります。

今回は、表計算ソフト｢エクセル｣(Excel)で作った簡単な学習計画を例に、｢COUNTIF 関数や SUM 関数を使って作成した集計シート｣を紹介しました。

また、この記事では、｢INDIRECT 関数を使ったシート・セル参照｣も紹介しています。同僚のまねをするところから始めたので、うまく説明できていない部分があったり、関数の使い方が非効率的な部分もあるかと思いますが、少しでも参考になれば幸いです。

次回は、｢SUM 関数を使って、今回紹介した集計シートを各シートに反映させる方法｣を紹介します。

簡単な学習計画を例にした｢エクセル｣(Excel)の操作方法については、次回で終わります。