すべて同じ一定量。合計関数をExcelで使用する例

特定の基準（条件）を満たす値を合計するには、SUMIF（）関数を使用します 、英語版SUMIF（）。

関数構文

SUMIF(範囲; 状態; [Sum_range]）

範囲 -引数に対応する値が検索されるセルの範囲状態..。範囲には、数値、日付、テキスト値、または他のセルへの参照を含めることができます。他の議論が Sum_range -省略した場合、引数範囲引数を満たす値の合計が実行される範囲でもあります状態（この場合範囲数字を含める必要があります）。

状態 -追加するセルを決定する数値、式、またはテキストの形式の基準。たとえば、引数状態 32、「リンゴ」または「\u003e 32」と表現できます。

Sum_range -引数で指定された対応するセルの場合、合計される数値を含むセルの範囲範囲引数に一致する状態. Sum_range -オプションの引数。省略した場合、引数で指定されたセルの範囲で合計が実行されます範囲.

の例

議論が Sum_range 省略。この場合、合計は最初の引数で指定されたセルの範囲で実行されます範囲（つまり、数字が含まれている必要があります）。また、引数に対応する値を検索します状態、それから合計されます。範囲にしましょう B5：B15 、サンプルファイルを参照してください。

問題を解決します。

10以上のすべての数値の合計を求めます。回答：175。式： \u003d SUMIF（B5：B15、 "\u003e \u003d 10"）
10以下のすべての数の合計を求めます。回答：42。式： \u003d SUMIF（B5：B15、 "<=10")
範囲内のすべての正の数の合計を求めます。式： \u003d SUMIF（B5：B15、 "\u003e 0"）..。 SUMPRODUCT（）関数を使用する代替バージョンは次のようになります。 \u003d SUMPRODUCT（（B5：B15）*（B5：B15\u003e 0））

タスクフォーム条件（基準）は十分に柔軟です。たとえば、式では \u003d SUMIF（B5：B15; D7＆D8）基準<=56 задан через ссылку D7&D8 : в D7 テキスト値が含まれています<=, а в D8 -番号56（下の図を参照）。たとえば、ユーザーはセルで使用して基準を簡単に変更できます D7 ..。等価式 \u003d SUMIF（B5：B15、 "<=56") или \u003d SUMIF（B5：B15、 "<="&56) または \u003d SUMIF（B5：B15、 "<="&D8) または \u003d SUMIF（B5：B15; D7＆56）.

SUMIF関数は、指定された基準を満たす範囲の値を合計する必要がある場合に使用されます。

機能の説明

SUMIF関数は、指定された基準を満たす範囲の値を合計する必要がある場合に使用されます。たとえば、数値の列では、5より大きい値を合計するだけで済みます。これを行うには、次の数式を使用できます。
\u003d SUMIF（B2：B25、 "\u003e 5"）この例では、合計値が基準に照らしてチェックされます。必要に応じて、条件を1つの範囲に適用し、別の範囲の対応する値を合計することができます。たとえば、次の式は次のとおりです。
\u003d SUMIF（B2：B5; "Ivan"; C2：C5）は、範囲B2：B5の対応する値が "Ivan"に等しい、範囲C2：C5の値のみを合計します。
この関数を使用する別の例を次の図に示します。

SUMIFを使用して条件付きの利益を計算する例

構文

\u003d SUMIF（範囲、条件、[sum_range]）

引数

範囲条件 sum_range

必須の引数。 基準によって判断されるセルの範囲。各範囲のセルには、数値、名前、配列、または数値参照が含まれている必要があります。空白のセルとテキスト値を含むセルはスキップされます。

必須の引数。 合計するセルを決定する、数値、式、セル参照、テキスト、または関数の形式の条件。たとえば、条件は、32、「\u003e 32」、B5、「32」、「apples」、またはTODAY（）として表すことができます。

重要！ すべてのテキスト条件および論理記号と数学記号を含む条件は、二重引用符（“）で囲む必要があります。条件が数値の場合、引用符を使用する必要はありません。

オプションの引数。 範囲として指定されたセルと異なる場合に値が追加されるセル。 sum_rangeを省略した場合、マイクロソフトエクセル range引数で指定されたセル（条件が適用されるのと同じセル）を合計します。

備考

条件引数には、疑問符（？）とアスタリスク（*）のワイルドカードを使用できます。疑問符は任意の1文字に一致し、アスタリスクは任意の文字シーケンスに一致します。疑問符（またはアスタリスク）を直接検索する場合は、その前にチルダ（〜）を付ける必要があります。
SUMIF関数を使用して255文字を超える文字列を#VALUE！文字列に一致させると、誤った結果が返されます。
sum_range引数は、range引数と同じサイズではない場合があります。合計する実際のセルを決定するときは、sum_range引数の左上のセルが開始セルとして使用され、次に、range引数によってサイズ設定された範囲の部分のセルが合計されます。

フィールドが一度に2つの基準（条件AND）を満たす値を行に追加しましょう。テキスト基準、数値および日付基準を検討してください。 SUMIFS関数を分析してみましょう（ ）、英語版SUMIFS（）。

ソーステーブルとして2つの列（フィールド）を持つテーブルを取り上げましょう：text " 果物「そして数値」 在庫数"（サンプルファイルを参照）。

問題1（1つのテキスト基準と1つの数値）

ある商品の箱の数を見つけましょう フルーツ そして、これ 残りの箱の在庫 最小値以上。たとえば、商品の入った箱の数 桃（細胞 D 2 ), 倉庫内の残りのボックスがある\u003e \u003d 6 (細胞 E 2 ) ..。結果64が得られるはずです。カウントは多くの式で実装できます。いくつかの式を示します（サンプルファイルを参照）。 シートのテキストと番号):

1. =SUMIFN（B2：B13; A2：A13; D2; B2：B13; "\u003e \u003d"＆E2）

関数構文： SUMIF（sum_interval; condition_interval1; condition1; condition_interval2; condition2 ...）

B2：B13 Sum_Interval -名前、配列、または数値を含む参照を含む、合計用のセル。空の値とテキストは無視されます。
A2：A13そして B2：B13 condition_interval1; condition_interval2; ..。 対応する条件がチェックされる1から127の範囲を表します。
D2そして "\u003e \u003d"＆E2 Condition1; 条件2; ..。 合計されるセルを決定する数値、式、セル参照、またはテキストの形式の1から127の条件です。

引数の順序は、SUMIF（）関数とSUMIF（）関数で異なります。 SUMIFS（）引数 summation_interval は最初の引数であり、SUMIF（）では3番目の引数です。これらの同様の関数をコピーおよび編集するときは、引数が正しい順序になっていることを確認してください。

2.その他のオプション\u003d SUMPRODUCT（（A2：A13 \u003d D2）*（B2：B13）;-（ B2：B13\u003e \u003d E2））
SUMPRODUCT（）関数の使用方法を詳しく見てみましょう。

A2の評価結果：A13 \u003d D2は配列です（FALSE：FALSE：FALSE：FALSE：TRUE：TRUE：TRUE：FALSE：FALSE：FALSE：FALSE：FALSE）TRUE値は列の値と一致します A 基準、すなわち語桃..。配列は、A2で強調表示してA13 \u003d D2を押し、次に押すと表示されます。
B2：B13の計算結果は（3：5：11：98：4：8：56：2：4：6：10：11）、つまり列からの値のみ B ;
配列（A2：A13 \u003d D2）*（B2：B13）の要素ごとの乗算の結果は（0：0：0：0：4：8：56：0：0：0：0：0）です。数値にFALSEを掛けると、0になります。値TRUE（\u003d 1）の場合、数値自体が取得されます。
2番目の条件を分析してみましょう：計算の結果-（B2：B13\u003e \u003d E2）は配列（0：0：1：1：0：1：1：0：0：1：1：1）です。列の値 " 在庫のある箱の数»基準を満たす\u003e \u003d E2（つまり、\u003e \u003d 6）は1に一致します。
さらに、SUMPRODUCT（）関数は、配列の要素をペアで乗算し、結果の積を合計します。 -64を取得します。

3. SUMPRODUCT（）関数のもう1つの使用法は、式です。 \u003d SUMPRODUCT（（A2：A13 \u003d D2）*（B2：B13）*（B2：B13\u003e \u003d E2））.

必要に応じて、日付を数式に直接入力できます\u003d SUMIFS（B6：B17; A6：A17; "\u003e \u003d 2010年1月15日"; A6：A17; "<=06.03.2010")

選択条件をテキスト文字列で表示するには、次の式を使用します \u003d "からの期間の販売量"＆TEXT（D6; "dd.MM.yy"）＆ "to"＆TEXT（E6; "dd.MM.yy"）

最後の式で使用されます。

タスク4（月）

前の問題の状態を少し変更します。その月の総売上高を見つけます（サンプルファイルを参照） シート月).

数式はタスク3と同様に作成されますが、ユーザーは2つの日付ではなく、月の名前を入力します（表のデータは1年以内であると想定されています）。

3番目のタスクを解決するために、オートフィルターが構成されたテーブルは次のようになります（サンプルファイルを参照） シート2の日付).

以前は、テーブルを「合計」行に変換して含める必要がありました。

こんにちは、ブログサイトの購読者と訪問者の皆様。ごく最近、VLOOKUPの式を理解し、その直後に別の非常に便利なExcel関数について書くことにしました- SUMIF..。これらの機能は両方とも、キーフィールドによって異なるソース（テーブル）からのデータを「リンク」することができ、特定の条件下では交換可能です。同時に、それらの目的と使用法には大きな違いがあります。

VLOOKUPの目的が単にExcelのある場所から別の場所にデータを「プル」することである場合、SUMIFを使用して、指定された基準に従って数値データを合計します。この式の主な目的は、ある条件に従ってデータを要約することです。このようなものがヘルプに書かれています。ただし、このツールは他の目的でも機能します。たとえば、VLOOKUPの代わりにSUMIF式を使用することがよくあります。これは、何かが見つからない場合に＃N / Aエラーが生成されないためです。そして、これは作業を大幅に容易にし、スピードアップします。

SUMIF関数は、さまざまな問題を解決するためにうまく適合させることができます。したがって、この記事では、1つではなく2つの例を検討します。 1つ目は、指定された基準に従って合計に関連付けられ、2つ目は、データの「プル」、つまりVLOOKUPの代替として関連付けられます。

SUMIF関数を使用した集計例

このテーブルには、位置、数量、および特定の商品グループに属するものが含まれています（最初の列）。今のところ、SUMIFの単純化された使用法を考えてみましょう。これらの位置についてのみ金額を計算する必要がある場合、その値は特定の条件に対応します。たとえば、販売された上位のポジションの数を知りたい、つまり値が70単位を超えるもの。そのような商品を目で検索して手動で要約するのはあまり便利で高速ではないため、ここではSUMIF関数が非常に適しています。

まず、金額を計算するセルを選択します。次に、関数ウィザードを呼び出します。これはアイコンです fx 数式バーに。次に、リストでSUMIF関数を探してクリックします。ダイアログボックスが開きます。この特定の問題を解決するには、提案された3つのフィールドのうち2つ（最初の）フィールドに入力するだけで済みます。

そのため、この例を簡略化したものと呼びました。なぜ3（3）のうち2（2）なのですか？私たちの基準は非常に総和の範囲にあるからです。

「範囲」フィールドは、すべての元の値が配置されているExcelテーブルの領域を示しており、そこから何かを選択して追加する必要があります。通常はマウスで設定します。

「基準」フィールドは、数式が選択される条件を示します。この場合、「\u003e 70」を示します。あなたが引用符を入れないならば、それらは彼ら自身で完成するでしょう。ただし、一般に、このような基準は引用符で囲む必要があります。

最後のフィールド「Sum_range」はすでに最初のフィールドに示されているため、入力しません。

したがって、SUMIF関数は基準を取り、指定された基準を満たす指定された範囲からすべての値の選択を開始します。その後、選択したすべての値が追加されます。これが関数アルゴリズムの仕組みです。

関数ウィザードで必須フィールドに入力したら、キーボードの大きな[Enter]ボタンを押すか、ウィザードウィンドウで[OK]をクリックします。この後、計算値が入力関数の場所に表示されます。私の例では、224個を取得しました。つまり、70個以上の商品の販売総額は224個でした。（これは、[OK]をクリックする前でもウィザードウィンドウの左下隅に表示されます）。それで全部です。これは、基準と合計範囲が同じ場所にある単純化された例です。

次に、基準が合計範囲と一致しない例を見てみましょう。この状況ははるかに一般的です。同じ条件付きデータについて考えてみます。ある値以上または以下の金額を知る必要があるが、特定の商品グループ、たとえばグループGの金額を知る必要があるとします。

これを行うには、金額を将来計算するセルを再度選択し、関数ウィザードを呼び出します。ここで、最初のウィンドウに、合計の選択が行われる基準を含む範囲を示します。つまり、この場合、これは製品グループの名前の列です。さらに、基準自体は、対応するフィールドにエントリ「グループD」を残して手動で指定するか、必要な基準を持つセルをマウスで単に示します。最後のボックスは、要約されたデータが配置されている範囲です。マウスで表示しますが、印刷好きの方は手動で入力することを禁じられていません。

結果は、グループDから販売された商品の量-153個になります。

そこで、1つの特定の基準に対して1つの金額を計算する方法を検討しました。ただし、多くの場合、いくつかの基準に対して複数の金額を計算する必要がある場合に問題が発生します。簡単なことではありません！たとえば、グループごとに販売された製品の量を調べる必要があります。つまり、4つのグループ（A、B、C、D）の4つの値に関心があります。このため、グループのリストは通常\u200b\u200b、Excelのオープンスペースのどこかに別のテーブルの形式で作成されます。グループの名前は、ソーステーブルのグループの名前と正確に一致する必要があることは明らかです。合計がまだゼロである最後の行をすぐに追加しましょう。

次に、最初のグループの式が規定され、他のすべてのグループに拡張されます。ここでは、リンクの相対性に注意を払う必要があります。条件と合計範囲の範囲は絶対リンクである必要があります。これにより、数式をドラッグしても「下がる」ことはありませんが、基準自体は、最初にマウスで指定する必要があります（手動で書き込む必要はありません）。、そして第二に、それは相対リンクでなければならないので、各合計がどのように独自の合計基準を持っているか。

このような計算を使用して関数ウィザードに入力されたフィールドは、次のようになります。

ご覧のとおり、最初のグループAの場合、販売された商品の量は161個でした（図の左下隅）。次に、Enterキーを押して、数式を下にドラッグします。

すべての金額が計算され、それらの総計は535であり、元のデータの合計と一致します。これは、すべての値が合計され、見逃されていないことを意味します。 SUMIFの最後のユースケース私は個人的にかなり頻繁に出くわします。

SUMIF関数を使用してデータを比較する例

何度も述べたように、SUMIF関数はデータをバインドするために賢明に使用できます。実際、1つの値を合計すると、その値自体が得られます。この算術特性は、永久に使用する罪ではありません。 Excelの設計者が彼らの頭脳のそのような野蛮な使用に気づいていない可能性さえあります。

つまり、SUMIFは、VLOOKUPの代わりにデータバインディングに簡単に適応できます。 VLOOKUPが存在するのになぜSUMIFを使用するのですか？説明させてください。まず、SUMIFは、VLOOKUPとは異なり、データ形式の影響を受けず、予期しない場所でエラーを生成しません。第二に、与えられた基準に従った値の欠如によるエラーの代わりにSUMIFは0（ゼロ）を与えます。これにより、不要なジェスチャーなしでSUMIF式を使用して範囲の合計を計算できます。ただし、1つの欠点があります。目的のテーブルでいずれかの基準が繰り返されると、対応する値が合計されますが、これは必ずしも「プルアップ」ではありません。警戒するほうがいい。一方、これはしばしば必要です-値を特定の場所にプルアップすると同時に、重複した位置を追加します。 SUMIF関数のプロパティを理解し、取扱説明書に従って使用する必要があります。

次に、SUMIF関数がVLOOKUPよりもデータのプルに適していることが判明した例を見てみましょう。あなたの例のデータが1月のいくつかの製品の売上を表すとします。 2月にどのように変化したのか知りたい。同じプレートで比較を行うと便利です。以前に右側にもう1つの列を追加し、2月のデータを入力しました。別のExcelファイルのどこかに、品揃え全体の2月の統計がありますが、これらの特定の位置を分析したいので、すべての製品の販売統計を含む大きなファイルから必要な値をテーブルにプルアップする必要があります。まず、VLOOKUP式を使ってみましょう。製品コードを基準として使用します。結果は図にあります。

1つの位置が締め付けられていないことがはっきりとわかり、数値の代わりに＃N / Aエラーが表示されます。ほとんどの場合、この製品は2月に販売されていなかったため、2月のデータベースでは利用できません。その結果、＃N / Aエラーが合計で表示されます。位置が少ない場合、問題は大きくありません。手動でエラーを削除するだけで、金額が正しく再計算されます。ただし、行数は数百単位で測定でき、手動調整を頼りにすることは完全に正しい決定ではありません。次に、VLOOKUPの代わりにSUMIF式を使用しましょう。

結果は同じですが、＃N / A SUMIFエラーの代わりにゼロが返されます。これにより、通常、最終行の量（または別のインジケーター、たとえば平均）を計算できます。これが、VLOOKUPの代わりにSUMIFを使用する必要がある主な理由です。ポジション数が多いと、その効果はさらに顕著になります。

実際には、モニターのサイズの何倍も大きい大量のデータを処理する必要があることがよくあります。エラーを見つけて追跡することは非常に困難です。しかし、その後の計算ではすべてのわき柱がポップアップします。記事のスクリーンショットを使用してこのような例を分解するのは便利ではないので、SUMIF関数について特別に録画されたビデオチュートリアルをご覧になることをお勧めします。

これがSUMIFに関する別のレッスンです