エクセルで条件に合ったセルの合計・平均を計算するSUMIF関数・AVERAGEIF関数の使い方

スポンサーリンク

今回はエクセルで、ある条件に合ったセルだけの合計・平均を計算・表示する方法です。

「SUMIF関数」と「AVERAGEIF関数」を利用します。

以前は「データベース関数」を利用するような複雑な関数が必要だったのですが、今や簡単に条件にあったセルの合計・平均を計算をすることが可能です。

下のようなエクセルのシートから、男女別の合計・平均を「SUMIF関数」と「AVERAGEIF関数」を用いて計算します。

男女別に合計と平均を計算
スポンサーリンク

「SUMIF関数」で条件に合ったセルの合計を計算

まずは「男子の合計」を表示させる方法からです。

「男子の合計」を表示するセル「F2」セルを選択後、「関数の挿入」ボタンをクリックします。

関数の挿入

「関数の検索」ボックスに「sumif」と入力し、「検索開始」ボタンをクリックします。

すると「関数名」ボックスに「SUMIF」が表示されますので、「SUMIF」を選択し「OK」をクリックします。

SUMIF関数が表示される

「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」関数になっているのが分かります。

「SUMIF」関数も「AVERAGEIF」関数も引数は全く同じ

同様にG3セルも、「AVERAGEIF」関数に変更するだけで、「女」の「平均」が自動計算されます。

「女」の「平均」も自動計算される

2つ以上の条件を満たすセルの合計・平均を求めるには

2つ以上の条件を満たすセルの合計・平均を求めるには「SUMIFS関数」や「AVERAGEIFS関数」という「S(エス)関数」を利用する必要があります。

例えば図中の、「Aクラスの男子の合計・平均」などを計算する場合に利用します。

そちらはもう少し複雑な引数が必要になりますので、こちらの記事をご覧ください。

2つ以上の条件に当てはまるセルの合計・平均を計算するSUMIFS(エス)関数とAVERAGEIFS(エス)関数の使い方
先日、条件に当てはまるセルのみの合計・平均を計算するために、「SUMIF関数」と「AVERAGEIF関数」の使い方について説明しました。今回は、さらに2つ以上の条件に当てはまるセルのみの合計・平均を計算する「SUMIFS(エス)関...
Excel(エクセル)2019の新機能! 追加された新関数IFS(イフエス)の使い方 IF関数よりもシンプルに条件分岐が可能!
Excel(エクセル)2019で追加された機能Excel2019になっていろいろな機能が追加されています。CONCATIFSMAXIFSMINIFSSWICHITEXTJOIN最後にSがついている関数以外は...
Excel(エクセル)関数 Sシリーズ 便利なCOUNTIFS関数 今での苦労は何だったんだ? 2019新機能の新関数IFSより難しいけど超役立つ!
Excel(エクセル)2019の新関数 IFSExcel2019になってIFS関数が追加されました。僕的には前からあったような気がするんですが、Office365を使っていたからでしょうかね。ちゃんとマイクロソフト...
スポンサーリンク
Office・Windows
スポンサーリンク
リーダーの独り言

コメント

タイトルとURLをコピーしました