【Excel】SUMIF関数の使い方 ～指定した条件に一致する行データの値を合計する～

本記事ではExcelのSUMIF関数の使い方を説明します。

SUMIF関数とは。SUMIF関数の書式

SUMIF関数は、検索条件に一致した『行』の数値を合計する関数です。
もう少し詳しく書くと、指定した検索範囲の中で検索条件に一致する行を検索し、その行の合計範囲の数値データを合計します。
検索条件には比較演算子、ワイルドカードを使うことができます。

SUMIF関数の書式は以下の通りです。

SUMIF関数の書式
=SUMIF(検索範囲, 検索条件, 合計範囲)

※検索範囲、合計範囲の注意点
検索範囲、合計範囲の開始行、終了行を必ず合わせます。例えば検索範囲が「A1:A10」の場合は合計範囲を「C1:C10」とします。(検索範囲、合計範囲ともに開始が1行目、終了が10行目)
もし、検索範囲「A1:A10」(開始行1:終了行10)、合計範囲「C5:C10」(開始行5:終了行10)のようにずれていると正しい合計値を求めることができません。

以下画像では、SUMIF関数を使って、商品コードがA100の受注金額の合計と、A101の受注金額の合計を求めています。

商品コードがA100の受注金額の合計は、『=SUMIF($B$3:$B$14,"A100",$F$3:$F$14)』で求めています。
検索範囲『$B$3:$B$14』の中で検索条件『A100』に一致する行に対して、合計範囲『$F$3:$F$14』の値を合計しています。

※商品コードがA101の受注金額の合計は、『=SUMIF($B$3:$B$14,"A101",$F$3:$F$14)』で求めています。

SUMIF関数の考え方は、以下の画像もご参考ください。
(商品コードA100の受注金額の合計を求める例)

SUMIF関数の検索条件に使える『比較演算子』・『ワイルドカード』

SUMIF関数の引数の『検索条件』には、以下の比較演算子・ワイルドカードを使うことができます。比較演算子・ワイルドカードを使用する場合は検索条件とあわせて『"(ダブルクォーテーション)』で囲みます。

比較演算子	説明	使用例
=	等しい	"=10"
<>	等しくない	"<>男"
＞	より大きい	">19"
<	より小さい	"<20"
>=	以上	">=20"
＜＝	以下	”＜＝19"

ワイルドカード	説明	検索条件の使用例
*	任意の文字列	"A*"
?	任意の1文字	"?田"

SUMIF関数の使用例

担当者ごとの受注金額の合計を求める

以下の画像では、担当者ごとの受注金額の合計を求めています。

担当者が佐藤の受注金額の合計は、『=SUMIF($G$3:$G$14,J3,$F$3:$F$14)』で求めています。

検索範囲『$G$3:$G$14』で検索条件『J3』に一致する行の合計範囲『$F$3:$F$14』の合計を求めます。
※検索条件は『"佐藤"』としてもいいですが、仕事ではこの例のようにセル参照させる方法をよく使います。

受注日ごとの受注金額の合計を求める (日付を検索条件にする)

以下の画像では、受注日ごとの受注金額の合計を求めています。

受注日が2022/1/1の受注金額の合計は、『=SUMIF($H$3:$H$14,J3,$F$3:$F$14)』で求めています。

検索条件の日付は、セル参照しない場合は『=SUMIF($H$3:$H$14,"2022/1/1",$F$3:$F$14)』のように書きます。

比較演算子を使った例

以下の画像では比較演算子を使って日付を条件に受注金額の合計を求めています。

受注日が2022/1/2までの受注金額の合計は『=SUMIF($H$3:$H$14,"<=2022/1/2",$F$3:$F$14)』で求めています。

受注日が2022/1/3以降の受注金額の合計は『=SUMIF($H$3:$H$14,">=2022/1/3",$F$3:$F$14)』で求めています。

受注日が2022/1/1以外の受注金額の合計は『=SUMIF($H$3:$H$14,"<>2022/1/1",$F$3:$F$14)』で求めています。

受注日が2022/1/2～2022/1/4の受注金額の合計は『=SUMIF($H$3:$H$14,">=2022/1/2",$F$3:$F$14)-SUMIF($H$3:$H$14,">2022/1/4",$F$3:$F$14)』で求めています。
受注日が2022/1/2以降の受注金額の合計から受注日が2022/1/5以降の受注金額の合計をマイナス(除算)することで、2022/1/2～2022/1/4の受注金額の合計を求めています。

ワイルドカードを使った例

検索条件にワイルドカードを使った例を紹介します。『*』が任意の文字列、『?』が任意の1文字の文字列となります。

商品コードが「A」から始まる商品の受注金額の合計は『=SUMIF($B$3:$B$14,"A*",$F$3:$F$14)』で求めています。

商品コードが「B」から始まる商品の受注金額の合計は『=SUMIF($B$3:$B$14,"B*",$F$3:$F$14)』で求めています。

商品コードに「2」を含む商品の受注金額の合計は『=SUMIF($B$3:$B$14,"*2*",$F$3:$F$14)』で求めています。

商品コードが「A○01」の商品の受注金額の合計は『=SUMIF($B$3:$B$14,"A?01",$F$3:$F$14)』で求めています。
※○は任意の1文字です。

商品コードが「○101」の商品の受注金額の合計は『=SUMIF($B$3:$B$14,"?101",$F$3:$F$14)』で求めています。
※○は任意の1文字です。

VLOOKUP関数のように検索した条件に一致する行の数値を返す方法

検索範囲で指定した列の値が一意な値(重複しない値)の場合、VLOOKUP関数のように条件に一致する行の数値データを返すことができます。
※検索範囲の値が一意な値であれば、合計するものがないので合計範囲の数値をそのまま返します。VLOOKUPのように文字列などは返せません。
※複数条件を指定するSUMIFS関数の方が、この方法をより効果的に活用できます。

以下の画像では、検索範囲がB列(日付)で一意な値なので、特定の日付の神戸支店の売り上げを求めています。

2022/1/3の神戸支店の売り上げは『=SUMIF($B$3:$B$11,G3,$D$3:$D$11)』で求めています。

複数の条件を指定できるSUMIFS関数については、以下記事で紹介していますので、参考にしていただければ幸いです。
auroralights.jp

お読みいただきありがとうございました。

【Excel】COUNTIFS関数の使い方 ～複数の検索条件に一致するデータの件数を数える。データの存在確認、重複確認をする。～

本記事ではExcelのCOUNTIFS関数の使用方法を説明します。

以下の記事で紹介したCOUNTIF関数では1つの条件しか指定ができませんが、COUNTIFS関数は複数の条件を指定して、その条件に一致する件数を数えることができます。

auroralights.jp

COUNTIFS関数とは。COUNTIFS関数の書式

COUNTIFS関数は、指定した範囲の中で検索条件に一致するデータの件数を数える関数です。(検索条件に一致するデータがなければ0を返します)

COUNTIF関数が1つの条件に一致する件数を数える関数なのに対して、COUNTIFS関数は2つ以上の条件に一致する件数を数える関数です。
検索条件には、比較演算子やワイルドカードを使うことができます。

COUNTIFS関数の書式は以下の通りです。

COUNTIFS関数の書式
=COUNTIFS(範囲1,検索条件1,範囲2,検索条件2,...)

範囲1、検索条件1、範囲2、検索条件2は必須となります。範囲3、検索条件3以降は省略可能です。

以下画像では、COUNTIFS関数を使って、住所が兵庫県神戸市の男性、女性の件数を数えています。

性別が男性かつ住所が兵庫県神戸市のデータ件数は『=COUNTIFS($C$3:$C$17,"男",$E$3:$E$17,"兵庫県神戸市")』で求めています。

範囲1、検索条件1には、『$C$3:$C$17,"男"』、範囲2、検索条件2には『$E$3:$E$17,"兵庫県神戸市"』を指定して、性別が男性かつ住所が兵庫県神戸市のデータ件数を数えています。

性別が女性かつ住所が兵庫県神戸市のデータ件数は『=COUNTIFS($C$3:$C$17,"女",$E$3:$E$17,"兵庫県神戸市")』で数えています。

COUNTIFS関数のセル範囲を指定するポイント

COUNTIFS関数で範囲を指定する際は、以下のポイントを意識します。

• 範囲1、範囲2...のセル範囲は、検索条件に一致するデータがある1つの列で範囲を指定する (複数の列にまたがる範囲を指定しない)
• 範囲1、範囲2...のセル範囲は、開始行、終了行をあわせる。

上記画像の場合は、「性別が男性かつ住所が兵庫県神戸市かつ購入回数が10回以上」という3つの範囲、検索条件で件数を数えています。
範囲1～3はそれぞれ、検索条件に一致するデータがある該当の1列を指定しています。
範囲1～3は開始行3、終了行17を指定しており、範囲1「$C$3:$C$17」、範囲2「$E$3:$E$17」、範囲3「$H$3:$H$17」をして指定ます。

複数の検索条件に一致するデータの存在を確認する方法

COUNTIFS関数でデータ群の中に複数の検索対象に一致するデータが存在するかどうかを確認することができます。COUNTIFSで0を返すか、1以上を返すかで存在確認をします。

上記画像では、セルB3：D17の部品一覧の中にセルF3：H9のデータが何件存在しているかどうかを、COUNTIFS関数を使って数えて、I列に件数を返しています。
I列の数値が0のデータは、部品一覧に存在しないことを表しています。

※部品一覧は、部署コード、部品コード、仕入先コードの組み合わせで一意となります。

セルI3の数式は以下のようになっています。
=COUNTIFS($B$3:$B$17,F3,$C$3:$C$17,G3,$D$3:$D$17,H3)
範囲1「$B$3:$B$17」で検索条件「F3」かつ、範囲2「$C$3:$C$17」で検索条件「G3」かつ、範囲3「$D$3:$D$17」で検索条件「H3」に一致するデータが何件あるかをCOUNTIFS関数で数えています。
I列が「0」の場合はデータが存在いないことを表しています。I列が「1」以上の場合はその数値分のデータが存在することを表しています。

複数の検索条件に一致するデータの重複を確認する方法

COUNTIFS関数で、データ群の中で重複しているデータを探すことができます。COUNTIFS関数は条件を複数指定できるため、複数の条件で一意になるデータが重複しているかどうかを調べることができます。
COUNTIFS関数で2以上の値が返ってきたら重複している状態です。

上記画像では、部署コード、部品コード、仕入先コードで一意となる部品一覧で、部署コード、部品コード、仕入先コードを組み合わせたデータが、一覧の中で重複しているかどうかを確認しています。

セルE3では以下の数式で、部署コード(B3)、部品コード(C3)、仕入先コード(D3)を組み合わせたデータが部品一覧で重複しているかどうかを調べています。
=COUNTIFS($B$3:$B$25,B3,$C$3:$C$25,C3,$D$3:$D$25,D3)

範囲1「$B$3:$B$25」検索条件1「B3」、範囲2「$C$3:$C$25」、検索条件2「C3」、範囲3「$D$3:$D$25」、検索条件3「D3」に一致するデータの件数を返しています。
E列の値が「0」の場合はそのデータ存在しない。「1」の場合は重複せずに1件のみ存在している。「2」以上の場合はその数値だけ重複していることを表しています。

ポイントは、数式をコピーして利用するので検索範囲を絶対参照、検索対象を相対参照とすることです。

COUNTIFS関数の検索条件に使える『比較演算子』・『ワイルドカード』

COUNTIFS関数の引数の『検索条件』には、以下の比較演算子・ワイルドカードを使うことができます。比較演算子・ワイルドカードを使用する場合は検索条件とあわせて『"(ダブルクォーテーション)』で囲みます。

比較演算子	説明	使用例
=	等しい	"=10"
<>	等しくない	"<>男"
＞	より大きい	">19"
<	より小さい	"<20"
>=	以上	">=20"
＜＝	以下	”＜＝19"

ワイルドカード	説明	検索条件の使用例
*	任意の文字列	"A*"
?	任意の1文字	"?田"

比較演算子、ワイルドカードの使い方については、以下のCOUNTIF関数の記事で説明していますので、ご参照ください。
auroralights.jp

【Excel VBA スタンダード】参照渡し(ByRef)と値渡し(ByVal)の分かりやすい使い方の解説 (試験対策)

本記事ではVBA エキスパート『Excel VBA スタンダード』の試験範囲でもある『参照渡し』と『値渡し』の使い方について、分かりやすく解説します。

参照渡しと値渡しのポイント

1. 参照渡しはByRef 、値渡しはByVal 〈Reference(参照)のRef、Value(値)のValと覚えましょう〉
2. 参照渡し(ByRef)：呼び出し元から呼び出し先に参照渡しで引数(変数)を渡した場合、呼び出し先で引数の値を変更すると、呼び出し元の変数の値も変更されます。
3. 値渡し(ByVal)：呼び出し元から呼び出し先に値渡しで引数(値)を渡した場合、呼び出し先で引数を変更しても、呼び出し元の変数は変更されません。
4. 参照渡しは呼び出し先で引数が変更されると呼び出し元の変数も変更されるが、値渡しは呼び出し先で引数が変更されても呼び出し元の変数は変更されない。
5. 呼び出し先が参照渡しで受け取るのか、値渡しで受け取るのか、引数の定義が重要です。
6. 引数の定義にByRef、ByValを指定しなかった(定義を省略した)場合は、ByRef(参照渡し)が指定されます。

参照渡しと値渡渡しの動作 (呼び出し先でExcel⇒Wordに変更した場合の呼び出し元の変数の状況)

画像の通り、呼び出し先の引数を変更した場合、参照渡しでは呼び出し元の変数も変更されます。値渡しでは呼び出し元の引数は変更されません

実際のコードで参照渡し、値渡しの動作を見てみましょう。

参照渡しの動作を確認するVBAのサンプルコード

以下のコードではsampleRef1からsampleRef2を呼び出しています。
その際に変数bufを引数として渡しており、sampleRef2ではByRef(参照渡し)で受け取っています。

Sub sampleRef1()
    
    Dim buf As String
    buf = "Excel"
    Call sampleRef2(buf)
    MsgBox buf
    
End Sub
Sub sampleRef2(ByRef A As String)
    
    MsgBox A
    A = "Word"
    MsgBox A

End Sub

結果は以下の通りです。

1回目のMsgBox：Excel ※参照渡しで受け取った引数を表示
2回目のMsgBox：Word ※受け取った引数を変更して表示
3回目のMsgBox：Word ※参照渡しのため呼び出し元のbufも変更されている

値渡しの動作を確認するVBAのサンプルコード

以下のコードではsampleVal1からsampleVal2を呼び出しています。
その際に変数bufを引数として渡しており、sampleVal2ではByVal(値渡し)で受け取っています。

Sub sampleVal1()
    
    Dim buf As String
    buf = "Excel"
    Call sampleVal2(buf)
    MsgBox buf
    
End Sub
Sub sampleVal2(ByVal A As String)
    
    MsgBox A
    A = "Word"
    MsgBox A

End Sub

結果は以下の通りです。

1回目のMsgBox：Excel ※値渡しで受け取った引数を表示
2回目のMsgBox：Word ※受け取った引数を変更して表示
3回目のMsgBox：Excel ※値渡しのため呼び出し元のbufは変更されない

以上、参照渡しと値渡しの解説でした。
お読みいただきありがとうございました。