多くの場合、月ごとにいくつかの値を計算したいと思います。たとえば、特定の月にどれだけの売上があったかなどです。これはピボットテーブルを使用して簡単に実行できますが、動的レポートを作成する場合は、SUMPRODUCTまたはSUMIFS式を使用して月ごとに合計できます。

0013

SUMPRODUCTソリューションから始めましょう。

これはExcelで月ごとの合計を取得するための一般的な式です

=SUMPRODUCT(sum_range,--( TEXT(date_range,"MMM")=month_text))

Sum_range：月ごとに合計する範囲です。

Date_range：数か月間調べる日付範囲です。

Month_text：値を合計するテキスト形式の月です。

次に例を見てみましょう：

例：Excelでの月ごとの合計値ここでは、日付に関連付けられた値がいくつかあります。これらの日付は、2019年の1月、2月、3月です。

0014

上の画像でわかるように、すべての日付は2019年のものです。ここで、E2：G2の値をE1：G1の月ごとに合計する必要があります。

月ごとに値を合計するには、E2に次の式を記述します。

=SUMPRODUCT(B2:B9,--(TEXT(A2:A9,"MMM")=E1)))

隣接するセルにコピーする場合は、 `link：/ excel-generals-relative-and-absolute-reference-in-excel [absolutereferences]`または `link：/ excel-range-name-all-about-を使用します。画像のように、named-ranges-in-excel [名前付き範囲] `。

0015

これにより、毎月の正確な合計がわかります。

どのように機能しますか？

内側から始めて、

を見てみましょう=== link：/ excel-text-formulas-the-text-function-in-excel [TEXT]（A2：A9、 “MMM”）

部。ここで、TEXT関数は、テキスト形式の範囲A2：A9の各日付から月を配列に抽出します。式を= SUMPRODUCT（B2：B9、[。custom-span]＃-＃[。custom-span]＃-＃（\ {“Jan”; “Jan”; “Feb”; “Jan”; “Feb “;” Mar “;” Jan “;” Feb “} = E1））

次に、TEXT（A2：A9、 “MMM”）= E1：ここでは、配列内の毎月がE1内のテキストと比較されます。 E1には「Jan」が含まれているため、配列内の各「Jan」はTRUEに変換され、その他はFALSEに変換されます。これにより、数式が= SUMPRODUCT（$ B $ 2：$ B $ 9、-\ {TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}）に変換されます

次の[.custom-span]＃-＃[。custom-span]＃-＃（TEXT（A2：A9、 “MMM”）= E1）は、TRUEFALSEをバイナリ値1と0に変換します。式は= SUMPRODUCTに変換されます。 （$ B $ 2：$ B $ 9、\ {1; 1; 0; 1; 0; 0; 1; 0}）。

最後に、SUMPRODUCT（$ B $ 2：$ B $ 9、\ {1; 1; 0; 1; 0; 0; 1; 0}）：SUMPRODUCT関数は、$ B $ 2：$ B $ 9の対応する値を配列\ {に乗算します。 1; 1; 0; 1; 0; 0; 1; 0}そしてそれらを合計します。したがって、E1では20052として値で合計を取得します。

異なる年の月の合計上記の例では、すべての日付が同じ年のものでした。彼らが異なる年から来た場合はどうなりますか？上記の式は、年に関係なく月ごとに値を合計します。たとえば、上記の式を使用すると、2018年1月と2019年1月が追加されます。ほとんどの場合、これは間違っています。

0016

これは、上記の例ではその年の基準がないために発生します。年の基準も追加すれば、うまくいくでしょう。

Excelで月と年ごとに合計を取得する一般的な式

=SUMPRODUCT(sum_range,--( TEXT(date_range,"MMM")=month_text),--( TEXT(date_range,"yyyy")=TEXT(year,0)))

ここでは、年をチェックする基準をもう1つ追加しました。他のすべては同じです。

上記の例を解いて、セルE1にこの数式を記述して、2017年の1月の合計を取得しましょう。

=SUMPRODUCT(B2:B9,--(TEXT(A2:A9,"MMM")=E1),--(TEXT(A2:A9,"yyyy")=TEXT(D2,0)))

以下のセルにコピーする前に、名前付き範囲または絶対参照を使用してください。

この画像では、隣接するセルにコピーするために名前付き範囲を使用しています。

0017

これで、月と年ごとの値の合計も確認できます。

どのように機能しますか？

式の最初の部分は前の例と同じです。年の基準を追加する追加の部分を理解しましょう。

link:bbbb
=== （A2：A9、 “yyyy”）= TEXT（D2,0））：

TEXT（A2：A9、 “yyyy”）は、A2：A9の日付をテキスト形式の年として配列に変換します。 \ {“2018”; “2019”; “2017”; “2017”; “2019”; “2017”; “2019”; “2017”}。

ほとんどの場合、年は数字形式で書かれています。数値とテキストを比較するために、TEXT（D2,0）を使用してyearintテキストを変換しました。次に、このテキストの年を、 link：/ excel-text-formulas-the-text-function-in-excel [TEXT]（A2：A9、 “yyyy”）= TEXT（D2,0）として年の配列と比較しました。 。

これは、true-false \ {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}の配列を返します。次に、[。custom-span]＃-＃[。custom-span]＃-＃演算子を使用して、truefalseを数値に変換しました。

これにより、\ {0; 0; 1; 1; 0; 1; 0; 1}が得られます。

したがって、最終的に、数式は= SUMPRODUCT（B2：B9、\ {1; 1; 0; 1; 0; 0; 1; 0}、\ {0; 0; 1; 1; 0; 1; 0に変換されます。 ; 1}）。ここで、最初の配列は値です。次は一致する月で、3番目は年です。最後に、値の合計を2160として取得します。

* SUMIFS関数を使用して月ごとに合計する

===

一般式

=SUMIFS(sum_range,date_range,”>=” & startdate, date_range,”<=” & EOMONTH(start_date,0))

ここで、Sum_range：月ごとに合計したい範囲です。

Date_range：数か月間調べる日付範囲です。

開始日：合計する開始日です。この例では、指定された月の1日になります。

例：Excelでの月ごとの合計値

ここに、日付に関連するいくつかの値があります。これらの日付は、2019年の1月、2月、3月です。

0018

これらの値をその月までに合計する必要があります。今では、月と年を別々にすれば簡単でした。しかし、そうではありません。ここではヘルプ列を使用できません。

そこで、レポートを作成するために、月と値の合計を含むレポート形式を用意しました。月の列には、実際にはその月の開始日があります。月だけを表示するには、開始日を選択してCTRL +1を押します。

カスタムフォーマットで「mmm」と書いてください。

0019

0020

これでデータの準備が整いました。月ごとに値を合計しましょう。

この式をE3に記述して、月ごとに合計します。

=SUMIFS(B3:B10,A3:A10,">="&D3,A3:A10,"<="&EOMONTH(D3,0))

0021

`link：/ excel-generals-relative-and-absolute-reference-in-excel [absolutereferences]`または `link：/ excel-range-name-all-about-named-ranges-in-excel [namedranges]を使用します] `式をコピーする前に。

それで、ついに結果が得られました。

では、どのように機能しますか？

SUMIFS関数は、複数の基準で値を合計できることがわかっています。

上記の例では、最初の基準はB3：B10のすべての値を合計することです。ここで、A3：A10の日付はD3の日付以上です。 D3には1月1日が含まれます。これも翻訳されます。

=SUMIFS(B3:B10,A3:A10,">="& “1-jan-2019” ,A3:A10,"<="EOMONTH(D3,0))

次の基準は、A3：A10の日付がEOMONTH（D3,0）以下の場合にのみ合計になります。

link：/ excel-date-time-formulas-finding-the-last-day-of-a-given-month [EOMONTH]

関数は、指定された月の最後の日付のシリアル番号を返すだけです。

最後に、数式も変換されます。

=SUMIFS(B3:B10,A3:A10,">=1-jan-2019” ,A3:A10,"<=31-jan-2019”)

したがって、月ごとの合計をExcelで取得します。

この方法の利点は、値を合計するための開始日を調整できることです。

日付の年が異なる場合は、ピボットテーブルを使用するのが最適です。

ピボットテーブルを使用すると、データを年次、四半期、月次の形式で簡単に分離できます。

そうそう、これが月ごとに値を合計する方法です。どちらの方法にも独自の専門性があります。あなたが好きな方法を選択してください。

この記事に関する質問、またはその他のExcelおよびVBA関連の質問がある場合は、コメントセクションを開いてください。

関連記事：

link：/ excel-formula-and-function-excel-sumif-function [ExcelでSUMIF関数を使用する方法]

link：/ tips-sumifs-with-dates-in-excel [Excelの日付付きSUMIFS]

link：/ Tips-excel-sumif-not-blank-cells [非空白セルのSUMIF]

link：/ summary-excel-sumifs-function [ExcelでSUMIFS関数を使用する方法]

AND-ORロジックを使用するSUMIFS

人気の記事

link：/ keyboard-formula-shortcuts-50-excel-shortcuts-to-increase-your-productivity [生産性を高めるための50Excelショートカット]：タスクをより速くします。

これらの50のショートカットにより、Excelでの作業がさらに高速になります。

link：/ expression-and-functions-introduction-of-vlookup-function [ExcelでVLOOKUP関数を使用する方法]：これは、さまざまな範囲から値を検索するために使用される、Excelで最も使用され人気のある関数の1つです。とシート。

link：/ Tips-countif-in-microsoft-excel [ExcelでCOUNTIF関数を使用する方法]：この驚くべき関数を使用して条件付きの値をカウントします。特定の値をカウントするためにデータをフィルタリングする必要はありません。ダッシュボードを準備するには、Countif関数が不可欠です。

link：/ excel-formula-and-function-excel-sumif-function [ExcelでのSUMIF関数の使用方法]：これは、ダッシュボードに不可欠なもう1つの関数です。これは、特定の条件で値を合計するのに役立ちます。