今回はエクセルで、ある条件に合ったセルだけの合計・平均を計算・表示する方法です。
「SUMIF関数」と「AVERAGEIF関数」を利用します。
以前は「データベース関数」を利用するような複雑な関数が必要だったのですが、今や簡単に条件にあったセルの合計・平均を計算をすることが可能です。
下のようなエクセルのシートから、男女別の合計・平均を「SUMIF関数」と「AVERAGEIF関数」を用いて計算します。
「SUMIF関数」で条件に合ったセルの合計を計算
まずは「男子の合計」を表示させる方法からです。
「男子の合計」を表示するセル「F2」セルを選択後、「関数の挿入」ボタンをクリックします。
「関数の検索」ボックスに「sumif」と入力し、「検索開始」ボタンをクリックします。
すると「関数名」ボックスに「SUMIF」が表示されますので、「SUMIF」を選択し「OK」をクリックします。
「SUMIF関数」は3つの引数を必要とします。
=SUMIF(検索する文字列の範囲,検索する文字列,合計を計算する範囲）
の3つです。
B列に「男女」がありますので、文字列の検索範囲はB列とします。また、まずは検索する文字列を「男」にしますので、検索条件は「男」と入力します。「””（ダブルクォーテーションマーク）」の入力は不要です。自動的に入力されます。そして、合計を計算する範囲（列）はA列ですので、3つ目の引数にはA列を選択します。
ここでは、=SUMIF($B$2:$B$12,“男”,$A$2:$A$12)とそれぞれの引数を入力またはドラッグして設定します。
絶対参照と相対参照
注意点は、文字列を検索する範囲と合計を計算する範囲をそれぞれ「絶対参照」（$をつける ） にしておく必要があります。
「相対参照」のままでは、コピーをしたときに範囲がずれてしまいます。
「絶対参照」と「相対参照」の切り替えは、セル範囲上でキーボードの「F4」キーを押すことで変更できます。「F4」キーを押すごとに、下記のように変動します。今回は行・列とも「絶対参照」にしておきます。
| F4キー1回 | $B$2 | 行・列とも「絶対参照」 |
| F4キー2回 | B$2 | 行は「絶対参照」、列は「相対参照」 |
| F4キー3回 | $B2 | 行は「相対参照」、列は「絶対参照」 |
| F4キー4回 | B2 | 行・列とも「相対参照」（もとに戻る） |
あとで数式をコピーして利用しますので、範囲は行・列とも「絶対参照」にしておく必要があります。
確認後、「OK」をクリックします。
以上で、「男」の「合計」が入力されました。
数式をコピーして次の条件を入力
続いて「女」の合計の表示です。
先ほどの数式をコピーして、数式の一部を編集します。
F2セルをF3セルにドラッグしてコピーします。この際に、範囲を絶対参照にしておけば範囲がずれることはありません。
コピーした先の「F3」セルをクリックし、再び「関数の挿入」をクリックします。
今度は「検索条件」を「女」に変更して「OK」をクリックします。
これで「女」の合計も表示されました。
条件に合ったセルのみの平均を計算する「AVERAGEIF関数」
「AVERAGEIF関数」は、条件に合ったセルのみの平均を計算する関数です。
引数は「SUMIF関数」と全く同じです。
合計の代わりに、条件に合ったセルのみの平均を表示します。
ここでは、先ほど入力した「SUMIF関数」の式をコピーして編集します。
F2とF3セルを選択し、G列にオートフィルでコピーします。
「数式バー」でG7セルの先頭の文字列「SUM」を「AVERAGE」に変更します（先頭をAVERAGEIFにします）。
入力後、エンターキーを押すと数式が確定されます。
これで「男」の「平均」が自動計算されます。
「AVERAGEIF」関数も3つの引数を必要とします。 ただし、引数は「SUMIF」関数と全く同じです。
=AVERAGEIF(検索する範囲,検索する文字列,合計をする範囲）
下記のダイアログボックスを見るとお分かりいただけると思いますが、SUMIF関数と全く同じ引数になります。ですから今回は数式をコピーすることによって簡単に数式を作成することができます。下記が数式となります。
=AVERAGEIF($B$2:$B$12,“男”,$A$2:$A$12)
数式バーをクリックすると、ちゃんと「AVERAGEIF」関数になっているのが分かります。
同様にG3セルも、「AVERAGEIF」関数に変更するだけで、「女」の「平均」が自動計算されます。
2つ以上の条件を満たすセルの合計・平均を求めるには
2つ以上の条件を満たすセルの合計・平均を求めるには「SUMIFS関数」や「AVERAGEIFS関数」という「S（エス）関数」を利用する必要があります。
例えば図中の、「Aクラスの男子の合計・平均」などを計算する場合に利用します。
そちらはもう少し複雑な引数が必要になりますので、こちらの記事をご覧ください。
コメント