複数の基準を持つExcel SumifsとSumif - 式の例

このチュートリアルでは、SumifとSumifsの関数の違いを構文と使用法の観点から説明し、Excel 365、2021、2019、2016、2013、2010、およびLowerの複数および /または基準で値を合計するための多くの式の例を提供します。

誰もが知っているように、Microsoft Excelは、データを使用してさまざまな計算を実行するための一連の関数を提供します。数件前の記事では、それぞれ単一の条件といくつかの条件に基づいてセルをカウントするために設計されたcountifとcountifsを調査しました。先週、指定された基準を満たす値を追加するExcel Sumifを取り上げました。次に、複数の基準で値を合計することを可能にするSumif -Excel Sumifsの複数版を調べる時が来ました。

Sumif関数に精通している人は、それをSumifに変換すると、余分な「S」といくつかの追加の基準が必要だと考えるかもしれません。これは非常に論理的に思えます...しかし、「論理的」マイクロソフトを扱うときは必ずしもそうではありません：）

• Sumif関数-myntax＆usage
• Sumifs関数 - 構文と使用法
• 基本的なサミフ式
• SumifとSumifsの違い
• Excel（および論理）でSumifsを使用する方法
• 複数の基準（またはロジック）を持つExcel Sumif
• 複数または条件のあるExcel Sumif
• 配列式で合計を使用します

Excel sumif関数-myntax＆usage

SUMIF関数は、単一の基準に基づいて条件付きの値を合計するために使用されます。前の記事でSumif構文について詳しく説明しましたが、ここに簡単な復習があります。

sumif（範囲、基準、[sum_range]）

• 範囲- あなたの基準によって評価される細胞の範囲が必要です。
• 基準- 満たさなければならない条件が必要です。
• sum_range-条件が満たされている場合、セルは合計します、オプション。

ご覧のとおり、Excel Sumif関数の構文は1つの条件のみを許可します。それでも、Excel Sumifを使用して、複数の基準で値を合計できると言います。それはどうでしょうか？いくつかのSUMIF関数の結果を追加し、次の例で示されているように、配列基準を持つSumif式を使用することにより。

Excel sumifs関数-myntax＆usage

ExcelのSumifsを使用して、複数の基準に基づいて条件付き値の合計を見つけます。 Sumifs関数はExcel 2007で導入され、Excel 2010、2013、2016、2019、2021、およびExcel 365のすべてのバージョンで利用可能です。

Sumifと比較して、Sumifs構文はもう少し複雑です。

sumifs（sum_range、criteria_range1、criteria1、[criteria_range2、criteria2]、…）

最初の3つの引数は必須であり、追加の範囲とそれに関連する基準はオプションです。

• sum_range要約する1つ以上のセル。これは、単一のセル、さまざまなセル、または名前付き範囲です。数字のセルのみが合計されます。空白とテキスト値は無視されます。
• criteria_range1関連する基準によって評価される最初の範囲が必要です。
• criteria1 1-満たさなければならない最初の条件が必要です。数値、論理式、セル参照、テキスト、または別のExcel関数の形式で基準を提供できます。たとえば、10、 "> = 10"、a1、 "cherries"、または今日（）などの基準を使用できます。
• criteria_range2, criteria2, … - これらに関連する追加の範囲と基準であり、オプションです。 Sumifs式で最大127の範囲/基準ペアを使用できます。

注：

• sumifs式が正しく機能するには、すべての基準_range引数がsum_rangeと同じ次元、つまり同じ数の行と列を持つ必要があります。
• Sumifs関数は、および論理で動作します。つまり、合計範囲のセルは、指定されたすべての基準を満たしている場合にのみ合計されます。つまり、すべての基準はそのセルに当てはまります。

基本的なサミフ式

そして今、2つの条件のExcel Sumifs式を見てみましょう。さまざまなサプライヤーからの果物の委託品をリストするテーブルがあるとします。列Aには果物の名前、サプライヤーの列Bの名前、列Cの数量があります。必要なのは、特定の果物とサプライヤー、たとえばPeteが供給するすべてのリンゴに関連する量の合計を見つけることです。

新しいことを学んでいるとき、簡単なことから始めることは常に良い考えです。そもそも、Sumifs式のすべての引数を定義しましょう。

• sum_range -c2：c9
• Criteria_range1 -a2：a9
• Criteria1-「リンゴ」
• Criteria_Range2 -B2：B9
• Criteria2-「ピート」

上記のパラメーターを組み立てると、次のSumifs式が表示されます。

=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")

式をさらに絞り込むために、テキスト基準「リンゴ」と「ピート」をセル参照に置き換えることができます。この場合、別のサプライヤーから他の果物の量を計算するために式を変更する必要はありません。

=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)

注記。 SumifとSumifの両方の関数は、本質的に症例に依存しません。テキストケースを認識させるには、Excelのケースに敏感なSumifおよびSumifs式を参照してください。

ExcelのSumif vs. Sumifs

このチュートリアルの目的は、いくつかの条件で値を合計するすべての可能な方法をカバーすることであるため、両方の関数を使用して式の例を説明します - Excel SumifsとSumifは複数の基準で説明します。それらを正しく使用するには、これらの2つの機能が共通しているものと、どのように異なるかを明確に理解する必要があります。

一般的な部分は明確ですが（同様の目的とパラメーター）、違いはそれほど明白ではありませんが、非常に不可欠です。

SumifとSumifsの間には4つの大きな違いがあります。

1. 条件数。 Sumifは一度に1つの条件しか評価できませんが、Sumifsは複数の基準を確認できます。
2. 構文。 Sumifでは、 sum_rangeは最後のオプションの引数であり、定義されていない場合、範囲引数の値が合計されます。 sumifsでは、 sum_rangeは最初で必要な引数です。
3. 範囲のサイズ。 Sumif式では、左上のセルが右にある限り、 Sum_rangeは必ずしも範囲と同じサイズと形状である必要はありません。 Excel Sumifsでは、各Criteria_rangeには、 Sum_range引数と同じ数の行と列を含める必要があります。

   たとえば、sumif（a2：a9、f1、c2：c18）は、 sum_range引数（c2）の左端のセルが正しいため、正しい結果を返します。したがって、Excelは修正を自動的に行い、範囲内にあるのと同じくらい多くの列とsum_rangeに列と行を含めます。

   不均等なサイズの範囲を持つSumifs式は#Valueを返します！エラー。

4. 可用性。 Sumifは、365から2000年までのすべてのExcelバージョンで利用できます。SumifsはExcel 2007以降で利用できます。

さて、十分な戦略（つまり理論）、戦術に入りましょう（つまり、式の例：）

ExcelでSumifsを使用する方法 - 式の例

少し前に、2つのテキスト基準を持つ単純なSumifs式について説明しました。同様に、数字、日付、論理式、およびその他のExcel関数で表される複数の基準を持つExcel Sumifを使用できます。

例1。比較演算子を備えたExcel Sumif

フルーツサプライヤーのテーブルでは、マイクによるすべての配達を数量と合計したいとします。 200以上。これを行うには、Criteria2で「より大きい」（> =）の比較演算子を使用し、次のSumif式を取得します。

=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")

注記。 Excel Sumifs式では、比較演算子を持つ論理式を常に二重引用符（ ""）に囲むべきであることに注意してください。

Excel Sumif関数について議論する際に、すべての可能な比較演算子を詳細に説明しました。同じ演算子をSumifs基準で使用できます。たとえば、次の式は、セルC2：C9のすべての値の合計を200以上で300以下の式を返します。

=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<h3 id="例-日付を持つExcel-Sumifを使用します">例2。日付を持つExcel Sumifを使用します</h3> <p>現在の日付に基づいて複数の基準で値を合計する場合は、以下に示すように、sumifs基準でtoday（）関数を使用します。次の式は、列cの対応する日付が今日を含む過去7日間以内に該当する場合、列dの値を合計します。</p> <p> <code>=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/174709832098143.png" class="lazy" alt="複数の基準を持つExcel SumifsとSumif - 式の例" ></p>

注記。基準の論理演算子と一緒に別のExcel関数を使用する場合、Ampersand（＆）を使用して文字列を連結する必要があります。

同様の方法で、Excel Sumif関数を使用して、特定の日付範囲の値を合計できます。たとえば、次のSumif式は、列Bの日付が2014年1月から2014年1月31日までの間に包括的である場合、セルC2：C9の値を追加します。

=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "

この例で示されているように、2つのsumif関数の違いを計算することで同じ結果を達成できます - 特定の日付範囲の合計値を使用する方法。しかし、Excel Sumifsははるかに簡単で理解しやすいですね。

例3.空白のセルと非ブランクセルを備えたExcel Sumif

レポートやその他のデータを分析する場合、多くの場合、空のセルまたは空のセルに対応する値を合計する必要がある場合があります。

	基準	説明	式の例
空白のセル	"="	まったく何も含まれていない空白のセルに対応する合計値 - 式なし、ゼロの長さの文字列なし。	= sumifs（c2：c10、a2：a10、 "="、b2：b10、 "="） セルの合計値c2：c10列aとbの対応するセルが絶対に空である場合。
	""	他のExcel関数によって返された空の文字列を含む「視覚的に」空白のセルに対応する合計値（たとえば、= ""のような式のセル）。	= sumifs（c2：c10、a2：a10、 ""、b2：b10、 ""） 上記の式と同じ条件を持つセルC2：C10の合計値：空の文字列が含まれています。
非ブランク細胞	「」	ゼロの長さの文字列を含む非空白セルに対応する合計値。	= sumifs（c2：c10、a2：a10、 ""、b2：b10、 ""） セルの合計値c2：c10列aとbの対応するセルが空でない場合、空の弦を持つセルを含む。
	sum-sumifまたはsum / len	ゼロの長さの文字列を含まない、空でないセルに対応する合計値。	= sum（c2：c10）-sumifs（c2：c10、a2：a10 "、b2：b10、" "） = sum（（c2：c10） *（len（a2：a10）> 0） *（len（b2：b10）> 0）） セルの合計値c2：c10列aとbの対応するセルが空でない場合、ゼロ長弦を持つセルは含まれていません。

そして今、実際のデータに「空白」と「非ブランク」基準を備えたSumifs式を使用する方法を見てみましょう。

列Bの注文日、列Cの配達日と数量があるとします。列Dでは、まだ配信されていない製品の合計をどのように見つけますか？つまり、列Bの非空白セルに対応する値と列Cの空のセルに対応する値の合計を知りたいです。

解決策は、2つの基準でSumifs式を使用することです。

=SUMIFS(D2:D10, B2:B10,"", C2:C10,"=")

複数または基準でExcel Sumifを使用します

このチュートリアルの冒頭で述べたように、Sumifs関数はロジックで設計されています。しかし、複数または基準で値を合計する必要がある場合、つまり、少なくとも1つの条件が満たされている場合はどうでしょうか？

例1。sumifsumif

最も簡単な解決策は、いくつかのSUMIF関数によって返された結果を合計することです。たとえば、次の式は、マイクとジョンが提供する製品の合計を見つける方法を示しています。

=SUMIF(C2:C9,"Mike",D2:D9) SUMIF(C2:C9,"John",D2:D9)

ご覧のとおり、最初のSUMIF関数は「Mike」に対応する量を追加し、他のSumif関数は「John」に関連する量を返し、これらの2つの数値を追加します。

例2。配列引数を持つsum＆sumif

上記のソリューションは非常に単純であり、いくつかの基準しかない場合、ジョブを迅速に完了させる可能性があります。しかし、複数または条件で値を合計したい場合、Sumif Sumif式は非常に成長する可能性があります。この場合、より良いアプローチは、SUMIF関数で配列基準引数を使用することです。今すぐこのアプローチを調べてみましょう。

まず、コンマで区切られたすべての条件をリストしてから、結果のコンマ分離リストを{Curly Brackets}に囲みます。これは、技術的にアレイと呼ばれます。

前の例では、ジョン、マイク、ピートが提供する製品を合計したい場合、アレイの基準は{"John"、 "Mike"、 "Pete"のようになります。完全なSumif関数はSUMIF(C2:C9, {"John","Mike","Pete"} ,D2:D9)です。

3つの値で構成される配列引数は、Sumif式を3つの別々の結果を返すように強制しますが、式を単一のセルに記述するため、最初の結果のみを返します。つまり、Johnが提供する製品の合計です。この配列基準アプローチを取得するには、もう1つの小さなトリックを使用する必要があります。このように、Sumif式を合計関数で囲む必要があります。

=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

場合には、お好みの方法は、式で直接指定するセルの基準を一部のセルにリストすることです。特定の配列のコンポーネントを掛けて、それらの製品の合計を返す等式関数と組み合わせてSumifを使用できます。

=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))

ここで、G2：G4はあなたの基準を含むセルであり、以下のスクリーンショットに示されているように、私たちの場合のサプライヤーの名前。

しかし、もちろん、次の場合を希望する場合、Sumif関数の配列基準に値をリストすることを妨げるものはありません。

=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))

両方の式で返される結果は、スクリーンショットに表示されるものと同じです。

複数または基準を持つExcel Sumif

複数の条件や条件だけでなく、いくつかの条件を使用してExcelで条件付きで値を合計したい場合は、Sumifの代わりにSumifを使用する必要があります。フォーミュラは、私たちが今まで議論したものと非常に似ています。

いつものように、例はポイントをより良く説明するのに役立つかもしれません。果物のサプライヤーのテーブルで、配達日（列E）を追加し、10月にマイク、ジョン、ピートが配信した総量を見つけましょう。

例1。sumifssumifs

このアプローチによって生成された式には多くの繰り返しが含まれており、扱いにくいように見えますが、理解するのは簡単で、最も重要なことは、それが機能することです。

=SUMIFS(D2:D9,C2:C9, "Mike", E2:E9,">=10/1/2014", E2:E9, "=10/1/2014", E2:E9, "=10/1/2014" ,E2:E9, "

ご覧のとおり、各サプライヤーに個別のSUMIF関数を作成し、OCT -1以上（ "> = 10/1/1/2014"）以上の2つの条件を含め、10月31日（ "

例2。配列引数を持つ合計＆Sumif

このアプローチの本質をSumifの例で説明しようとしたので、その式をコピーして、引数の順序を変更するだけで（SumifとSumifが異なることを覚えているように）、追加の基準を追加できます。結果のフォーミュラは、Sumifs Sumifよりもコンパクトです。

=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "

この式で返される結果は、上記のスクリーンショットで見られるのとまったく同じです。

例3。sumproduct＆sumifs

覚えているように、sumproductアプローチは、式で直接指定するむしろ、個別のセルに各基準を入力する方法で、前の2つとは異なります。いくつかの基準セットがある場合、等式関数は十分ではなく、ISNumberと一致も採用する必要があります。

したがって、供給者の名前がセルH1：H3にあると仮定すると、開始日はセルH4にあり、セルH5の終了日は、次の形状を取得します。

=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/174709832748614.png" class="lazy" alt="複数の基準を持つExcel SumifsとSumif - 式の例" ></p>

多くの人が、なぜsumproduct式でダブルダッシュ（ - ）を使用するのか疑問に思っています。ポイントは、Excel Sumproductは数値を除くすべての値を無視し、式の比較演算子は非数値ではないブール値（True / False）を返します。これらのブール値を1と0に変換するには、技術的には二重単位オペレーターと呼ばれる二重マイナス記号を使用します。最初の単位は、それぞれ-1/0にtrue/falseを強制します。 2番目の単一は値を無効にし、つまり標識を逆にし、それらを1と0に変え、等針板関数が理解できる。

上記の説明が理にかなっていることを願っています。そうでない場合でも、この経験則を覚えておいてください - 類似式で比較演算子を使用している場合は、二重統一演算子（ - ）を使用してください。

配列式でExcel合計を使用します

覚えているように、MicrosoftはExcel 2007でSumifs関数を実装しました。誰かがまだExcel 2003、2000以前を使用している場合、合計配列式を使用して、複数の基準で値を追加する必要があります。当然のことながら、このアプローチはExcel 2013-2007の最新のバージョンでも機能し、Sumifs関数の昔ながらの対応物と見なすことができます。

上記のSumif式では、すでに配列引数を使用していますが、配列の式は違うものです。

例1。Excel 2003以前の複数の基準と合計

特定の果物とサプライヤーに関連する量の合計を見つけた最初の例に戻りましょう。

すでにご存知のように、このタスク=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")通常のSumifs式を使用して簡単に実現されます。

そして今、Excelの初期の「Sumifsフリー」バージョンで同じタスクをどのように満たすことができるかを見てみましょう。まず、範囲= "条件"の形で満たされるべきすべての条件を書き留めます。この例では、2つの範囲/条件のペアがあります。

条件1：A2：A9 = "Apples"

条件2：b2：b9 = "pete"

次に、それぞれが括弧で囲まれているすべての範囲/条件ペアの「掛け分」の合計式を書きます。最後の乗数は合計範囲、C2：C9は次の場合です。

=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))

以下のスクリーンショットに示されているように、フォーミュラは最新のExcel 2013バージョンで完全に機能します。

注記。配列式を入力するときは、 Ctrl Shift Enterを押す必要があります。これを行うと、フォーミュラは{curly braces}に囲まれます。これは、アレイ式が正しく入力されることを視覚的に示しています。ブレースを手動で入力しようとすると、式はテキスト文字列に変換され、機能しません。

例2。最新のExcelバージョンの合計配列式

Excelの現代バージョンでさえ、Sum関数の力を過小評価すべきではありません。合計アレイ式は、単に心の体操ではなく、次の例で示されているように、実用的な価値を持っています。

列Cの値が10より大きい場合、列Cが列Bよりも大きい回数をカウントする必要があります。

=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><p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/174709833235426.png" class="lazy" alt="複数の基準を持つExcel SumifsとSumif - 式の例" ></p> <p>さて、このチュートリアルで説明されている式の例は、Excel SumifとSumif関数の表面を実際に行うことができるものの表面を傷つけただけです。しかし、うまくいけば、彼らはあなたを正しい方向に向けるのに役立ち、今ではあなたが考慮しなければならない複雑な条件に関係なく、あなたのExcelワークブックの値を合計することができます。</p></g2>

以上が複数の基準を持つExcel SumifsとSumif - 式の例の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。