このチュートリアルでは、SUMIF関数とSUMIFS関数の構文と使用法、およびExcel 2019、2016、2013、2010、2007、2003以下で複数のAND / OR基準を使用して値を合計するための数式の例をいくつか提供します。
ご存知のとおり、MicrosoftExcelには配列が用意されています。データを使用してさまざまな計算を実行する関数の一覧。数日前に、単一の条件と複数の条件に基づいてセルをカウントするように設計されたCOUNTIFとCOUNTIFSについて説明しました。先週、指定された条件を満たす値を追加するExcelSUMIFについて説明しました。ここで、SUMIFの複数形（複数の基準で値を合計できるExcel SUMIFS）について説明します。
SUMIF関数に精通している人は、SUMIF関数に変換するのに余分な時間がかかると思うかもしれません。 「S」といくつかの追加基準。これは非常に論理的に見えますが、「論理的」であるとは限りません。Microsoftを扱う場合:)
- SUMIF関数-構文&使用法
- SUMIFS関数-構文&使用法
- ExcelでSUMIFSを使用する方法（およびロジック）
- 複数の条件（ORロジック）を使用したExcelSUMIF
- 複数のOR条件を使用したExcelSUMIFS
- 配列式でのExcelSUMの使用
ExcelSUMIF関数-構文&の使用法
SUMIF関数は、単一の基準に基づいて値を条件付きで合計するために使用されます。前回の記事でSUMIF構文について詳しく説明したので、ここで簡単に要約します。
-
range-必要な基準によって評価されるセルの範囲。 -
criteria-満たす必要のある条件必須。 -
sum_range-条件が満たされた場合に合計するセル、オプション。
ご覧のとおり、 Excel SUMIF関数の構文では、1つの条件のみが許可されます。それでも、Excel SUMIFを使用して、複数の基準を持つ値を合計できると言います。どうしてそれができるのでしょうか？次の例に示すように、いくつかのSUMIF関数の結果を追加し、配列条件付きのSUMIF式を使用します。
ExcelSUMIFS関数-構文&の使用法
ExcelでSUMIFSを使用して、複数の基準に基づいて値の条件付き合計を検索します。 SUMIFS関数はExcel2007で導入されたため、Excel 2019、2016、2013、2010、2007のすべての最新バージョンで使用できます。
SUMIFと比較すると、SUMIFS構文は少し複雑です。 ：
最初の3つの引数は必須であり、追加の範囲とそれに関連する基準はオプションです。
-
sum_range-合計する1つ以上のセル。必須。これは、単一のセル、セルの範囲、または名前付き範囲にすることができます。数字のあるセルのみが合計されます。空白とテキストの値は無視されます。 -
criteria_range1-関連する基準によって評価される最初の範囲。 -
criteria1-満たす必要のある最初の条件、必須。数値、論理式、セル参照、テキスト、または別のExcel関数の形式で基準を指定できます。たとえば、10、 “> = 10″、A1、 “cherries”、TODAY（）などの基準を使用できます。 -
criteria_range2, criteria2, …-これらは、それらに関連付けられた追加の範囲と基準であり、オプションです。 SUMIFS数式では、最大127の範囲/基準のペアを使用できます。
では、「 sは、2つの条件でExcel SUMIFS式を確認します。さまざまなサプライヤからの果物の委託を一覧表示するテーブルがあるとします。列Aにフルーツ名、列Bにサプライヤの名前、列Cに数量があります。必要なのは、特定の果物とサプライヤーに関連する金額の合計を見つけることです。ピートが提供するすべてのリンゴ。
何か新しいことを学んでいるときは、常に簡単なことから始めることをお勧めします。それでは、まず、SUMIFS式のすべての引数を定義しましょう。
- sum_range –C2：C9
- Criteria_range1-A2：A9
- criteria1- “apples”
- criteria_range2-B2：B9
- criteria2- “Pete”
次に、上記のパラメーターをアセンブルします。次のSUMIFS式を取得します。
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
改良するには数式をさらに進めると、テキスト条件「apples」と「Pete」をセル参照に置き換えることができます。この場合、別のサプライヤーからの他の果物の量を計算するために式を変更する必要はありません：
=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)
ExcelでSUMIFSとSUMIFを使用する-覚えておくべきこと
このチュートリアルの目的は、いくつかの条件で値を合計するすべての可能な方法をカバーすることなので、両方の関数（ExcelSUMIFSとSUMIF）を使用した数式の例について説明します。複数の基準を使用します。これらを正しく使用するには、これら2つの関数の共通点と、それらの違いを明確に理解する必要があります。
共通部分は明確ですが、宛先とパラメーターは類似していますが、違いは非常に重要ですが、それほど明白ではありません。
1。引数の順序
ExcelのSUMIF関数とSUMIFS関数では、引数の順序が異なります。特に、sum_rangeは1番目です。 SUMIFSのパラメータですが、SUMIFでは3番目です。
一見すると、Microsoftはユーザーの学習曲線を意図的に複雑にしているように見えるかもしれません。Howevよく見ると、その背後にある理由がわかります。重要なのは、SUMIFではsum_rangeがオプションであるということです。これを省略しても問題ありません。SUMIF式は範囲内の値を合計します（最初のパラメーター）。
SUMIFSでは、sum_rangeは非常に重要で必須であるため、最初になります。おそらくMicrosoftの人たちは、10番目または100番目の範囲/基準のペアを追加した後、誰かが合計する範囲を指定するのを忘れるかもしれないと考えました:)
2。合計範囲と基準範囲は同じサイズにする必要があります
SUMIF関数では、左上のセルが右にある限り、sum_range引数は必ずしも範囲引数と同じサイズである必要はありません。 Excel SUMIFSでは、各criteria_rangeにはsum_rangeパラメータと同じ数の行と列が含まれている必要があります。
たとえば、=SUMIF(A2:A9,F1,C2:C18)は、Excelが考慮しているため、正しい結果を返します。 sum_range引数（この例ではC2、これは正しい）の左上のセルのみで、range引数に含まれるのと同じ数の列と行が含まれます。
わかりました。十分な戦略（つまり理論） 、戦術を見てみましょう（つまり、数式の例:)
ExcelでSUMIFSを使用する方法-数式の例
少し前に、2つのテキストを含む単純なSUMIFS数式について説明しました。基準。同様に、数値、日付、論理式、およびその他のExcel関数で表される複数の基準でExcelSUMIFSを使用できます。
例1.比較演算子を使用したExcelSUMIFS
フルーツサプライヤーの表で、マイクによるすべての配達を数量200以上で合計するとします。これを行うには、「oより大きい」比較演算子を使用します。 rはcriteria2の “（> =）に等しく、次のSUMIFS式を取得します。
=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")
同じ演算子であるExcelSUMIF関数について説明するときに、考えられるすべての比較演算子について詳しく説明しました。 SUMIFS基準で使用できます。たとえば、次の数式は、セルC2：C9の200以上300以下のすべての値の合計を返します。
=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")
例2.日付を指定したExcelSUMIFSの使用
現在の日付に基づいて複数の基準で値を合計する場合は、SUMIFSでTODAY（）関数を使用します以下に示すように、基準。次の数式は、列Cの対応する日付が今日を含む過去7日以内の場合、列Dの値を合計します。
=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())
同様の方法で、Excel SUMIF関数を使用して、特定の日付範囲の値を合計できます。たとえば、次のSUMIFS数式は、列Bの日付が2014年10月1日から2014年10月31日までの間にある場合、セルC2：C9に値を追加します。
=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")
この例で示すように、2つのSUMIF関数の差を計算しても同じ結果が得られます-SUMIFを使用して特定の日付範囲の値を合計する方法。ただし、ExcelSUMIFSの方がはるかに簡単でわかりやすいですね。
例3.空白セルと非空白セルを含むExcelSUMIFS
レポートやその他のデータを分析するときは、多くの場合、空のセルまたは空でないセルのいずれかに対応する値を合計する必要があります。
次に、実際の「空白」および「非空白」の基準でSUMIFS数式を使用する方法を見てみましょう。データ。
列Bに注文日、列Cに配達日、数量があるとします。 D列にあります。まだ配達されていない製品の合計をどのように見つけますか？つまり、列Bの空でないセルと列Cの空のセルに対応する値の合計を知りたいとします。
解決策は、次の2つの基準でSUMIFS式を使用することです。
=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")
複数のOR基準でExcelSUMIFを使用する
このチュートリアルの冒頭で述べたように、SUMIFS関数はANDロジックで設計されています。しかし、複数のOR基準で値を合計する必要がある場合、つまり、少なくとも1つの条件が満たされた場合はどうなりますか？
例1.SUMIF + SUMIF
最も簡単な解決策は合計することですいくつかのSUMIF関数によって返される結果。たとえば、次の式は、マイクとジョンによって配信された製品の合計を見つける方法を示しています。
=SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)
ご覧のとおり、最初のSUMIF関数は「マイク」に対応する数量を追加し、他のSUMIF関数は「ジョン」に関連する金額を返し、次にこれら2つの数値を追加します。
例2.SUM &配列引数を指定したSUMIF
上記のソリューションは非常に単純であり、次の場合にのみ作業をすばやく実行できます。いくつかの基準。ただし、複数のOR条件で値を合計する場合、SUMIF + SUMIF式は非常に大きくなる可能性があります。この場合、より良いアプローチは、SUMIF関数で配列基準引数を使用することです。ここで、このアプローチを調べてみましょう。
まず、すべての条件をカンマで区切って一覧表示し、結果のカンマ区切りのリストを{中括弧}で囲みます。これは技術的には配列と呼ばれます。
3つの値で構成される配列引数は、SUMIF数式に3つの別々の結果を返すように強制しますが、数式を1つのセルに書き込むため、最初の結果のみ、つまりJohnによって配信された製品の合計が返されます。 。この配列基準アプローチを機能させるには、もう1つの小さなトリックを使用する必要があります。SUMIF式を次のようにSUM関数で囲みます。
=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
ご覧のとおり、配列条件を使用すると、SUMIF + SUMIFに比べて数式がはるかにコンパクトになり、必要な数の値を追加できます。
このアプローチは、数値とテキスト値で機能します。たとえば、列Cのサプライヤの名前の代わりに、1、2、3などのサプライヤIDがある場合。、その場合、SUMIF数式は次のようになります。
=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))
テキスト値とは異なり、数値を二重引用符で囲む必要はありません。配列引数。
例3.SUMPRODUCT & SUMIF
場合によっては、いくつかのセルに条件をリストするのではなく、数式で直接指定すると、指定された配列のコンポーネントを乗算し、それらの積の合計を返すSUMPRODUCT関数と組み合わせてSUMIFを使用できます。
=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))
G2：G4が条件を含むセルである場合、下のスクリーンショットに示すように、この場合はサプライヤーの名前です。
しかし、もちろん、それを妨げるものは何もありません。必要に応じて、SUMIF関数の配列基準に値をリストします。
=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
両方の数式によって返される結果は次のようになります。スクリーンショットに表示されているものと同じ：
Excel SUMIFS with複数のOR基準
Excelで、複数のOR条件だけでなく、複数の条件セットを使用して値を条件付きで合計する場合は、SUMIFの代わりにSUMIFSを使用する必要があります。数式は、今説明したものと非常によく似ています。
いつものように、例がポイントをわかりやすく説明するのに役立つ場合があります。フルーツサプライヤーの表に、納期（列）を追加しましょう。 E）10月にマイク、ジョン、ピートによって配信された合計数量を見つけます。
例1.SUMIFS + SUMIFS
このアプローチで作成された式には、多くの繰り返しと外観が含まれています。面倒ですが、理解しやすく、最も重要なこととして、機能します:)
例2.SUM &配列引数を使用したSUMIFS
I “SUMIFの例でこのアプローチの本質を説明しようとしたので、その式をコピーし、引数の順序を変更して（SUMIFとSUMIFSでは異なることを覚えているように）、条件を追加するだけです。結果の数式は、SUMIFS + SUMIFSよりもコンパクトです。
=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))
この数式によって返される結果は、次の式とまったく同じです。上のスクリーンショット。
例e 3. SUMPRODUCT & SUMIFS
覚えているように、SUMPRODUCTのアプローチは、各基準を個別のセルに入力する方法が前の2つとは異なります。むしろ、数式で直接指定します。複数の基準セットの場合、SUMPRODUCT関数では不十分であり、ISNUMBERとMATCHも使用する必要があります。
したがって、サプライ品名がセルH1：H3にあり、開始日がセルH4にあり、終了日がセルH5にあるとすると、SUMPRODUCT数式は次の形になります。
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
多くの人は、SUMPRODUCTの数式でなぜ二重ダッシュ（-）を使用するのか疑問に思っています。重要なのは、Excel SUMPRODUCTは数値以外のすべてを無視するのに対し、数式の比較演算子は数値以外のブール値（TRUE / FALSE）を返すということです。これらのブール値を1 “と0″に変換するには、二重のマイナス記号を使用します。これは、技術的には二重単項演算子と呼ばれます。最初の単項演算は、TRUE / FALSEをそれぞれ-1/0に強制します。 2番目の単項演算は値を否定します。つまり、符号を逆にして、SUMPRODUCT関数が理解できる+1と0に変換します。
上記の説明が理にかなっていることを願っています。そうでない場合でも、この目安を覚えておいてください。SUMPRODUCT数式で比較演算子を使用する場合は、二重単項演算子（-）を使用してください。
配列数式でExcelSUMを使用する
覚えているように、MicrosoftはExcel2007にSUMIFS関数を実装しました。まだExcel2003、2000以前を使用している場合は、SUM配列数式を使用して、複数のAND条件で値を追加する必要があります。当然、このアプローチ最新バージョンのExcel2013〜2007でも機能し、SUMIFS関数の旧式の対応物と見なすことができます。
上記のSUMIF式では、すでに配列引数を使用していますが、配列式
例1.Excel2003以前の複数のAND条件を使用した合計
関連する金額の合計を見つけた最初の例に戻りましょう。特定の果物とサプライヤーへ：
すでにご存知のように、このタスクは、通常のSUMIFS式：
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
では、初期の「SUMIFSフリー」バージョンのExcelで同じタスクを実行する方法を見てみましょう。 。まず、満たすべきすべての条件をrange = “condition”の形式で書き留めます。この例では、2つの範囲/条件のペアがあります。
条件1：A2：A9 = “apples”
条件2：B2：B9 = “Pete”
次に、すべての範囲/条件のペアを「乗算」するSUM式を記述します。各ペアは括弧で囲まれています。最後の乗数は合計範囲、この場合はC2：C9です。
=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))
以下のスクリーンショットに示すように、式最新のExcel2013バージョンで完全に機能します。
例2.最新のExcelバージョンのSUM配列数式
最新バージョンのExcelでは、SUM関数の能力を過小評価してはなりません。SUM配列の数式は、単なる心の体操ではなく、次の例に示すように実用的な価値があります。
、BとCの2つの列があり、列Cの値が10以上の場合、列Cが列Bよりも大きい回数を数える必要があります。すぐに思い浮かぶ解決策はSUMを使用することです。配列数式：
=SUM((C1:C10>=10) * (C1:C10>B1:B10))
実用的なものが表示されない上記の式への適用？別の方法で考えてみてください：）
下のスクリーンショットに示すような注文リストがあり、特定の日付までに完全に配達されなかった製品の数を知りたいとします。 Excelの言語に変換すると、次の条件があります。
条件1：列B（注文済みアイテム）の値が0より大きい
条件2：列の値列B未満のC（配信済み）
条件3：列D（期日）の日付が2014年11月1日未満です。
3つの範囲を入力/条件のペアを組み合わせると、次の式が得られます。
=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))
まあ、このチュートリアルで説明した数式の例は、ExcelのSUMIFS関数とSUMIF関数が実際に実行できることのほんの一部にすぎません。しかし、うまくいけば、正しい方向を示すのに役立ち、Excelワークブックの値を合計できます。考慮しなければならない複雑な条件の数。
- ExcelでSUMIFを使用する方法-セルを条件付きで合計する数式の例
- 複数の基準でExcelSUMIFSとSUMIFを使用する方法-数式の例
- VLOOKUPの使用方法& ExcelでのSUMまたはSUMIF関数
- 数式の例を含むGoogleスプレッドシートでのSUMIF
- ExcelでのCOUNTIF-そうでない場合はカウント 空白、より大きい、重複、または一意
- Excelでセルを色別にカウントおよび合計する方法