【スプレッドシート】SUMIFIF関数の基礎から応用まで
こんにちは、たま(@closuppo)です！
今回は、スプレッドシートの「SUMIF関数」の上級編をお伝えします。
初級編に続いてのマスター編となります。復習しながら今回の記事の内容を実践して、SUMIF関数の理解を深めましょう。
初級編も復習しましょう
※目次をクリックすると
タイトルの先頭まで移動します（↓）
まずはじめに
SUMIF(サムイフ)関数とは、選択した範囲の中から指定した条件に合う数値の合計を求めることができる関数です。
SUM関数は選択した範囲をすべて合計しますが、SUMIF関数は範囲内の条件に合う数値だけを集計することができます。
例えば、下図のように果物の個数や金額が載っている表の中から、リンゴの個数だけを自動で集計することができます。
SUMIF関数では、検索条件に【文字】や【数値】、【日付】、部分一致する文字列も指定することができます。
指定できる条件は1つです。
それでは、サンプルシートを使ってSUMIF関数で合計値を集計してみましょう。
≫サンプルシートはこちらサンプルシートを用意しているので
コピーしてご活用ください
はじめてのSUMIF関数
SUMIF関数の構文は以下の通りです。
SUMIF関数
=SUMIF( ❶範囲,❷検索条件,❸合計範囲 )
「 () 」内の❶❷❸の引数(ひきすう)を指定することで、条件に一致する項目の合計値を返してくれます。
引数が多くてややこしいぞ…
少しかみ砕いて考えてみましょう
詳しくご説明しますので、順番に入力してみてください
下図の表の中からミカンの販売個数をSUMIF関数で集計してみましょう。
結果を表示させたい販売個数合計のセルH3に=SUMIF( と入力
範囲には、調べたい項目が記載されている列のセル範囲B2:B16を指定
「 , (カンマ) 」を入力
セル範囲はマウスで選択できます
検索条件には、条件を入力するセル番地セルB3(ミカン)をクリックして指定
「 , (カンマ) 」を入力
合計範囲には、集計したい列のセル範囲セルD2:D16を合計範囲に指定
検索範囲B2:B16のミカンの個数は合計123個と集計結果で表示されました。
範囲が2つあって、わかりづらいです
日本語に直して読んでみましょう
H3のセル=
まず商品名が載っている範囲からミカンと条件を指定して、個数の範囲からミカンの数値だけを合計するということですね
繰り返し実践すると理解が深まります。条件を変えてみるなどして、何度も実践してみましょう。
プルダウンを使って条件を選択する
=SUMIF( ❶範囲,❷検索条件,❸合計範囲 )の、❷検索条件をプルダウンで選択し、集計結果を出してみましょう。
下図のように、条件として指定するセルG2の項目をプルダウンで選択できるように設定しておくと、選択した条件に一致する集計結果を返すことができます。
それでは一緒に実践してみましょう。
サンプルシートはシート2をお使いください。
プルダウンリストから作成する場合はサンプルシート2、セルA2のデータの入力規則を削除してからお使いください。
結果を表示させたい販売個数合計のセルH２に入力する数式はこうなります。
操作手順はこちらです。
- セルH2に「 = SUMIF( 」と入力
- マウスでB2からB16を選択
- 「 , 」を入力
- マウスでG2をクリック
- 「 , 」を入力
- 合計範囲をマウスでD2:D16と選択
- 「 ) 」で閉じる
条件に一致する項目の集計は50、検索範囲B2:B16のイチゴの個数合計は50個と計算結果で表示されました。
日本語で読むように数式を書くと、こうなります。
H2のセル=
プルダウンでその都度条件を変更できるんですね
プルダウンで選択できるようにしておくと計算結果の欄を一つにできるので、結果が見やすい表をつくることができますね。
SUMIF関数応用編
SUMIF関数は、特定の数値や日付を比較条件に指定して【〇〇より小さい】【〇〇以上】【〇〇を含む】などの結果を求めることができます。
=SUMIF( ❶範囲,❷検索条件,❸合計範囲 )の❷検索条件の数値の前に比較演算子やワイルドカードを使用して、【〇〇より】や【〇〇を】など細かく指定して集計できるようになります。
条件を細かく指定するには、比較演算子とワイルドカードという記号を使うんですよね
比較演算子とワイルドカードについては、COUNTIF関数マスター編で詳しく解説していますので、ぜひご覧ください。
例えば上の表のように、比較演算子を使って下記のように条件をつけることができます。
- 【〇〇より小さい】を条件に、４月より前の月の売上個数を合計する
- 【〇〇以上】を条件に、4月以降の売り上げ金額を調べる
上の表のように、ワイルドカードを使って【〇〇を含む】を条件に、チョコという文字を含む項目の金額だけを合計することもできます。
ポイントをお伝えします
ポイント
- SUMIF関数で記号を使うときは、=SUMIF( 範囲,検索条件,合計範囲 ) 二番目の引数検索条件の前に付ける
- 検索条件の数値の前に「 < (小なり) 」を入れることで、指定した検索条件より小さい(〇〇未満)数値を合計範囲から探して集計してくれる
- 引数に使用する場合は、比較演算子の右側に数値を指定する
- 比較演算子を使う場合、検索条件にテキストを直接入力するか、セルを指定するかの2パターンがある
- テキストを直接入力する場合
- 「 < 」と数値を「 "（ダブルクォーテーション）」で囲む
- (例) "<＝80"
- セルを指定する場合
- 比較演算子を「 "」で囲んで「 & 」を使ってセル番地とくっつける
- (例)"<="&A1
- テキストを直接入力する場合
- ワイルドカードを使う場合、検索条件にテキストを直接入力するか、セルを指定するかの2パターンがある
- テキストを直接入力する場合
- 「 * 」を使う場合は「 * 」と文字を「 " 」で囲む
- (例)"*チョコ*"
- セルを指定する場合
- 「 "」で「 * 」を囲んで「 & 」を使ってセル番地とくっつける
- (例) "*"&A1&"*"
- あいまい検索する場合は「 ? 」と部分的に一致する文字を組み合わせて文字数を限定して指定する
- 「 " 」で「 ? 」を囲む
- セル内の文字数も一致したものを検索する
- (例) "???ティー"(?と文字で6文字) → 6文字の「 ミルクティー 」など
- テキストを直接入力する場合
それでは実践してみましょう。サンプルシートはシート3をお使いください。
〇〇より小さい
比較演算子の「 < (小なり) 」を使って【〇〇】セルの数値を合計することができます。
【〇〇より小さい】を日付に指定すると、〇〇より前、というように指定することができます。
【〇〇より小さい】は〇〇を含まない〇〇よりも小さい数値という意味ですね
【〇〇より小さい】と【〇〇以下】では集計するときのボーダーラインが異なります。違いをしっかり理解しましょう。
検索条件にテキストを直接入力する
比較演算子の「 < 」を使って1.2.3月の売上金額を集計してみましょう。
【〇〇より小さい】は〇〇を含まないので、検索条件に4月より小さいと指定すると4月より前の月、つまり1月2月3月が対象となります。
先ほどの項目でご説明したポイントをおさえて条件をテキストで直接入力します。
1.2.3月の売上金額セルF2に入力する数式はこうなります。
操作の手順はこちらです。
- セルF2に「 =SUMIF( 」と入力
- セル範囲A2からA19を選択
- 「 , 」を入力
- 条件に「 " 」、「 < 」、4月、「 " 」の順で入力
- 「 , 」を入力
- 合計範囲をD2:D19と選択
- 「 ) 」で閉じる
条件に一致する項目の集計は1,250,000、検索範囲A2:A19の4月より小さい月の金額合計は1,250,000と計算結果で表示されました。
日本語で読むように数式を書くと、こうなります。
F2のセル=
セル範囲A2:A19の4月より小さい月を条件に指定。セル範囲D2:D19の4月より小さい月の数値だけを合計して表示する。
検索条件にセルを指定する
先ほどと同じように、比較演算子の「 < 」を使って4月より小さいセルの数値を集計します。
検索条件には4月と入力されているセルA11を指定しましょう。
条件にセル番地を指定する場合は「 " 」で「 < 」を囲み、「 & 」でセル番地とくっつけます。1.2.3月の売上金額セルF2に入力する数式はこうなります。
操作の手順はこちらです。
- セルF2に「 =SUMIF( 」と入力
- セル範囲A2からA19を選択
- 「 , 」を入力
- 条件に「 " 」「 < 」「 " 」「 & 」の順で入力、セルA11をクリック
- 「 , 」を入力
- 合計範囲をD2:D19と選択
- 「 ) 」で閉じる
条件に一致する項目の集計は1,250,000、検索範囲A2:A19のセルA11より小さい月の金額合計は1,250,000と計算結果で表示されました。
日本語で読むように数式を書くと、こうなります。
F2のセル=
セル範囲A2:A19のセルA11の数値より小さいを条件に指定。セル範囲D2:D19のセルA11の数値より小さいの数値だけを合計して表示する。
同じ結果を求める場合でも、条件の指定方法が2つあることがわかりましたね
【〇〇より大きい】の場合はどうなるんですか？
【〇〇より大きい】も操作手順は同じで、数値の前には比較演算子「 > 」を入力します。
〇〇以上
比較演算子の「 >= 」を使って【〇〇以上】のセルの数値を集計することができます。
上の図は30歳以上の売上を集計しています。
条件を【30以上】と指定しているので、30を含む大きい年齢を条件に集計されます。
サンプルシートはシート4をお使いください
検索条件にテキストを直接入力する
比較演算子の「 >= 」を使って30歳以上の売上金額を集計してみましょう。
セルE2に入力する数式はこうなります。
操作の手順はこちらです。
- セルE2に「 =SUMIF( 」と入力
- セル範囲A2からA14を選択
- 「 , 」を入力
- 条件に「 " 」、「 > 」、「 = 」、30、「 " 」の順で入力
- 「 , 」を入力
- 合計範囲をB2:B14と選択
- 「 ) 」で閉じる
条件に一致する項目の集計は184,000、検索範囲A2:A14の年齢30以上の売上合計は184,000と計算結果で表示されました。
日本語で読むように数式を書くと、こうなります。
E2のセル=
セル範囲A2:A14の30以上を条件に指定。セル範囲B2:B14の30以上の数値だけを合計して表示する。
検索条件にセルを指定する
次は、比較演算子の「 <= 」を使って検索条件にセルを指定し、30歳以下の売上金額を集計してみましょう。
条件にセル番地を指定する場合は「 “ 」で「 <= 」を囲み、「 & 」でセル番地とくっつけます。セルD2に入力する数式はこうなります。
操作の手順はこちらです。
- セルD2に「 =SUMIF( 」と入力
- セル範囲A2からA14を選択
- 「 , 」を入力
- 条件に「 " 」、「 < 」、「 = 」、「 " 」、「 & 」の順で入力、セルA8をクリック
- 「 , 」を入力
- 合計範囲をB2:B14と選択
- 「 ) 」で閉じる
条件に一致する項目の集計は135,000、検索範囲A2:A14のセルA8以下の売上合計は135,000と計算結果で表示されました。
日本語で読むように数式を書くと、こうなります。
D2のセル=
〇〇を含む
SUMIF関数は、ワイルドカードを使って特定の文言を含むセルをカウントすることができます。
「 * 」を使って【〇〇を含む】の使い方を見てみましょう。
検索条件の前後を「 * (アスタリスク) 」で囲むことで、条件を含む項目を集計してくれます。
例えば、「 *チョコ* 」と指定した場合、チョコケーキやホットチョコなど、文字列に「 チョコ 」を含んでいれば条件を満たしていると認識します。
「 * 」を置く位置で検索条件が変えられるんですよね
それでは実践してみましょう。サンプルシートはシート5をお使いください。
検索条件にテキストを直接入力する
「 * (アスタリスク) 」を使って「チョコ」を含む文字列のセルの個数を集計してみましょう。
セルD2に入力する数式はこうなります。
操作の手順はこちらです。
- セルD2に「 =SUMIF( 」と入力
- セル範囲A2からA14を選択
- 「 , 」を入力
- 条件に「 " 」、「 * 」、チョコ、「 * 」、「 " 」の順で入力
- 「 , 」を入力
- 合計範囲をB2:B14と選択
- 「 ) 」で閉じる
条件に一致する項目の集計は1330、検索範囲A2:A14のチョコの金額合計は1330と計算結果で表示されました。
日本語で読むように数式を書くと、こうなります。
D2のセル=
検索条件にセルを指定する
先ほどと同じように、「 * (アスタリスク) 」を使って「チョコ」を含む文字列のセルの個数セルの個数を集計します。
検索条件には数値の入ったセル番地を指定しましょう。
条件にセル番地を指定する場合は「 " 」で「 * 」を囲み、「 & 」でセル番地とくっつけます。セルD2に入力する数式はこうなります。
操作手順はこちらです。
- セルD2に「 =SUMIF( 」と入力
- セル範囲A2からA14を選択
- 「 , 」を入力
- 条件に「 " 」、「 * 」、「 " 」、「 & 」の順で入力、セルD1をクリック、「 & 」、「 " 」、「 * 」、「 " 」と入力
- 「 , 」を入力
- 合計範囲をB2:B14と選択
- 「 ) 」で閉じる
条件に一致する項目の集計は1330、検索範囲A2:A14のD1の文字列を含むセルの数値の金額合計は1330と計算結果で表示されました。
日本語で読むように数式を書くと、こうなります。
D2のセル=
〇〇以外
検索条件の数値の前に比較演算子の「 <> 」を加えることで、指定した検索条件を含まない合計値を集計してくれます。
例えば、「 <> チョコケーキ 」と指定するとチョコケーキ以外の項目が条件を満たしていると認識するので、指定した項目を省いて集計します。
サンプルシートはシート5をお使いください
検索条件にテキストを直接入力する
比較演算子の「 <> 」を使って、チョコケーキ以外の金額を集計してみましょう。
セルE2に入力する数式はこうなります。
操作手順はこちらです。
- セルE2に「 =SUMIF( 」と入力
- セル範囲A2からA14を選択
- 「 , 」を入力
- 条件に「 " 」、「 < 」、「 > 」、 チョコケーキ、「 " 」の順で入力
- 「 , 」を入力
- 合計範囲をB2:B14と選択
- 「 ) 」で閉じる
条件に一致する項目の集計は4730、検索範囲A2:A14のチョコケーキ以外の金額合計は4730と計算結果で表示されました。
日本語で読むように数式を書くと、こうなります。
E2のセル=
検索条件にセルを指定する
先ほどと同じ条件で、セルを指定する場合の数式はこうなります。
条件にセル番地を指定する場合は「 " 」で「 <> 」を囲み、「 & 」でセル番地とくっつけます。
操作手順はこちらです。
- セルE2に「 =SUMIF( 」と入力
- セル範囲A2からA14を選択
- 「 , 」を入力
- 条件に「 " 」、「 < 」、「 > 」、「 " 」の順で入力、セルA3をクリック
- 「 , 」を入力
- 合計範囲をB2:B14と選択
- 「 ) 」で閉じる
条件に一致する項目の集計は4730、検索範囲A2:A14のセルA3の文字列以外の項目の金額合計は4730と計算結果で表示されました。
日本語で読むように数式を書くと、こうなります。
E2のセル=
セルを固定する
セルを参照した数式をコピーすると、参照先のセル範囲が移動して、正しく計算できないことがあります。
上の表のようにセルH1でイチゴの販売個数を集計し、下のセルH2、H3に数式をコピーすると範囲がずれてしまうため、正しく集計することができません。
コピペをすると参照先がずれてしまうんですよね…
絶対参照を使って、参照先を固定しましょう
セルの固定方法
固定したいセル番地の列番号(アルファベット)と行番号(数字)の左横にそれぞれ「 $(ドル記号) 」を付けることで、コピペしてもセルが常に固定されるようになります。
サンプルシートはシート6をお使いください。
操作手順はこちらです。
- セルH2に「 =SUMIF( 」と入力
- セル範囲B2からB16を選択
- 列番号と行番号の各左横に「 $ 」を入力
- 「 , 」を入力
- 条件にセルB4をクリック
- 「 , 」を入力
- 合計範囲をD2:D16と選択
- 列番号と行番号の各左横に「 $ 」を入力
- 「 ) 」で閉じる
次に、セル2の数式をセルH3、セルH4にコピペします。
セル範囲が固定され、正しく集計することができました。
- もう1つの例を見てみましょう
絶対参照でセル範囲が固定されているので右にコピペしてもセル範囲が移動せず、正しく集計することができます。
サンプルシート7のセルH2に同じ数式を入力し、右方向へオートフィルでコピペしてみましょう。
このように絶対参照で固定しておけば、参照するセルは動かないままになるので、同じ数値を何度も使いたい場合に使用します。
参照方法は他にもありますので、ぜひ参照についての記事もご覧ください。
まとめ
Googleスプレッドシートの「SUMIF関数」マスター編としてお伝えしました。
SUMIF関数はさまざまな検索条件を指定できる実用性の高い関数の一つです。
色々なパターンの活用方法をマスターして、業務の効率化を図りましょう。
