エクセルで2つ以上の条件に当てはまるセルの合計・平均を計算するSUMIFS(エス)関数とAVERAGEIFS(エス)関数の使い方 365・2019・2016・2013・2010・2007対応

スポンサーリンク

先日、条件に当てはまるセルのみの合計・平均を計算するために、「SUMIF関数」と「AVERAGEIF関数」の使い方について説明しました。

今回は、さらに2つ以上の条件に当てはまるセルのみの合計・平均を計算する「SUMIFS(エス)関数」と「AVERAGEIFS(エス)関数」の使い方についての解説です。

スポンサーリンク

2つ以上の条件に当てはまるセルの合計を求める「SUMIFS関数」

2つ以上の条件に当てはまるセルの合計を求めるためには、「SUMIF関数」ではなく「SUMIFS(エス)関数」を利用する必要があります。

下図のように、男女のみの合計を求めるには「SUMIF関数」で計算可能ですが、「クラス別」「男女別」の合計を求めるためには「SUMIFS(エス)関数」を利用します。

2つ以上の条件では「SUMIF関数」では計算できない

それでは、クラス「A」で男女が「男」の合計をF6セルに表示する数式を入力します。

F6セルを選択し、「数式の挿入」をクリックして数式を挿入します。

関数の挿入

「関数の挿入」ダイアログボックスが開きますので、「関数の検索」ボックスに「sumifs」と入力し「検索開始」をクリックします。

すると、下図のように「SUMIFS」が表示されますので、選択して「OK」をクリックします。

SUMIFS関数の挿入

「SUMIFS関数」では引数が必要に応じて引数が増減します。

まずは、合計する数値が入力された「合計対象範囲」をドラッグするか入力します。

ここでは「得点」が入力されているA列を選びます。ただし、範囲は絶対参照(セルを選択してキーボードのF4キーを一度押す)にしておく必要があります。

合計対象範囲は「絶対参照」に

続いて、「条件範囲1」に最初の条件である「男女」の区別が入力されているセルの範囲を入力またはドラッグします。やはりこちらも絶対参照にしておきます。

そして、「条件1」には男女の「男」だけを合計しますから「男」と入力します。

確認後、一度「OK」ボタンを押してみましょう。

「条件範囲1」と「条件1」を入力

ご覧のように、SUMIF関数(条件が1つの場合)と同じ結果になっています。

SUMIF関数と同じ結果に

SUMIFS関数で2つ目の条件を設定

続いて、2つ目の条件である、クラス「A」を条件として追加してみましょう。

再び、F6セルを選択した状態で、「関数の挿入」をクリックします。

「条件範囲2」はクラスが含まれているC列に、また「条件2」にはクラス名「A」を設定して「OK」をクリックします。

j条件範囲2と条件2の指定

すると、F6セルにはAクラスでかつ男子だけの合計が表示されます。

SUMIFS関数の引数の意味は

SUMIFS関数では、条件に応じて複数の引数を必要とします。

=SUMIFS(合計を計算したい範囲,1つ目の条件に当てはまるセルが含まれているセル範囲,1つ目の条件にしたい文字列,2つ目の条件範囲,2つ目の条件,3つ目の条件範囲,3つ目の条件,・・・)

上記のように、「合計を計算したい範囲」は1つだけになります。その後、セットで「条件範囲1,条件1」,「条件範囲2,条件2」,「条件範囲3,条件3」と追加していくことができます。

もちろん今回は利用しませんが、条件に”>40″(40以上)などの数値も可能です。

条件は最大127個も設定することが可能です。まあ使い切る方はいらっしゃらないと思いますが。

Excel(エクセル)2019の新機能! 追加された新関数IFS(イフエス)の使い方 IF関数よりもシンプルに条件分岐が可能!
Excel(エクセル)2019で追加された機能Excel2019になっていろいろな機能が追加されています。CONCATIFSMAXIFSMINIFSSWICHITEXTJOIN最後にSがついている関数以外は...

「他の条件の合計」はコピーを利用して条件を変更するだけで可能

Aクラス男子以外の合計は、数式のコピーだけで簡単に条件を変更して計算が可能です。

まずは、Aクラス男子の数式が入力されたF6セルをドラッグでF9セルまでコピーします。

範囲を「絶対参照」に設定しているので範囲がずれることはありません。

数式をドラッグでコピー

A女子の合計を表示させるF7セルを選択し、「関数の挿入」をクリックします。

別の条件を設定

ここでは、「条件1」を「女」と設定するだけで完了です。

条件1を設定変更

以下のように条件が変更され、「A女子」の合計が表示されました。

別の条件も簡単に変更可能

同様にF8セルでは、「条件2」を変更します。

条件2を変更

最後に、F9セルでは「条件1」と「条件2」を変更するだけです。合計対象範囲と条件範囲が同じなので、簡単ですね。

合計対象範囲と条件範囲が同じなので設定は簡単

以上で、「SUMIFS関数」による複数条件に合ったセルの合計を表示させることができました。

複数の条件に合ったセルの合計が表示された

「AVERAGEIFS関数」で複数の条件に合致したセルの平均を求める

続いて、「AVERAGEIFS関数」で複数の条件に合致したセルの平均を求めます。

「AVERAGEIFS関数」の引数は、「SUMIFS関数」と全く同じです。

=AVERAGEIFS(平均を計算したい範囲,1つ目の条件に当てはまるセルが含まれているセル範囲,1つ目の条件にしたい文字列,2つ目の条件範囲,2つ目の条件,3つ目の条件範囲,3つ目の条件,・・・)

先ほど「SUMIFS関数」を利用した方にはもうご理解いただけるかと思います。

「SUMIFS関数」の数式をそのまま利用します。「SUMIFS関数」の数式が入力されているF6~F9セルを選択し、G列にドラッグでコピーします。この際に、範囲は絶対参照にしていますので範囲の変更は必要ありません。

「SUMIFS関数」の数式をコピーして「AVERAGEIFS関数」に利用

一つ一つのセルの数式を「SUMIFS」から「AVERAGEIFS」に変更しても構いませんが、今回は「置換」機能を利用して、一気に関数名を変更します。

1つ1つの関数名を変更するのは手間がかかる

具体的には、先ほどコピーして「SUMIFS関数」になっているG6セルからG9セルを選択します。

置換をするセルを選択

「SUMIFS」から「AVERAGEIFS」に変更したいセルを選択後、「ホーム」「検索と選択」「置換」をクリックします。

「検索と選択」「置換」

「検索と置換」ダイアログボックスで「検索する文字列」に「sum」を、「置換後の文字列」に「average」と入力して、「すべて置換」をクリックします。

すべて置換

すると、「4件を置換しました。」とメッセージボックスが表示されますので、「OK」をクリックします。

4件を置換しました。

するとご覧のように一気に「AVERAGEIFS関数」に置換され、それぞれの条件に合ったセルの「平均」が表示されます。

男女クラス別のへ平均点が表示された

エクセルの「S(エス)」関数にはいろいろな関数がある

今回紹介したエクセルの関数「S(エス)」シリーズはたくさんあります。

今回紹介した「SUMIFS関数」と「AVERAGEIFS関数」以外に、最大値を求める「MAXIFS関数」や最小値を求める「MINIFS関数」などがあります。もちろん一番シンプルな「IFS関数」も含まれます。

いずれも、複数の条件に合致したセルの合計や平均、最大値や最小値などを求める関数です。一つの「S(エス)」関数をマスターしたら、それぞれのS(エス)関数の使い方は理解するのにそれほど困難ではないと思います。

ただ、「COUNTIFS関数」少し複雑に思われるかもしれません。一度、下記の記事を参考にチャレンジしてみてください。

Excel(エクセル)関数 Sシリーズ 便利なCOUNTIFS関数 今での苦労は何だったんだ? 2019新機能の新関数IFSより難しいけど超役立つ!
Excel(エクセル)2019の新関数 IFSExcel2019になってIFS関数が追加されました。僕的には前からあったような気がするんですが、Office365を使っていたからでしょうかね。ちゃんとマイクロソフト...

コメント

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