メインコンテンツへスキップ

Excelでの年ごとの合計値

日付と販売額のリストを含むデータの範囲があるとします。次に、以下のスクリーンショットに示すように、特定の年に基づいて合計金額を取得します。 Excelでは、通常のSUMIFS関数とSUMPRODUCT関数を使用すると、このタスクをすばやく簡単に解決できます。 このチュートリアルでは、これらXNUMXつの関数の使用方法を詳しく説明します。


SUMIFSおよびDATE関数を使用したExcelでの年ごとの値の合計

特定の年に基づいて合計値を合計するには、DATE関数を使用してその年の最初と最後の日付を指定してから、SUMIFS関数を使用して、指定した日付内のすべての値を合計する必要があります。一般的な構文は次のとおりです。

=SUMIFS(sum_range,date_range,">="&DATE(year,1,1),date_range,"<="&DATE(year,12,31)
  • sum_range:合計される値の範囲。
  • date_range:特定の年にテストされる日付の範囲。
  • year:値の合計に基づく特定の年。 ((年、1,1、XNUMX 年の最初の日を示し、 年、12,31、XNUMX 年の最後の日を示します。)

Excelでこのタスクを解決するには、以下の数式をコピーするか、空白のセルに入力してから、数式を他のセルにドラッグして結果を取得してください。

=SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31))


この式の説明:

=SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31))

  • $ C $ 2:$ C $ 12:は、合計する値を含む「sum_range」です。
  • $ B $ 2:$ B $ 12、 "> ="&DATE(E2,1,1):セルE2の年の最初の日である12年01月01日以上の範囲B2020:B2の日付をチェックします。
  • $ B $ 2:$ B $ 12、 "<="&DATE(E2,12,31):セルE2の年の最後の日である12年12月31日以下の範囲B2020:B2の日付をチェックします。
  • SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31)):基準を満たした後、C2:C12の範囲の値が検出されて合計されます。

SUMPRODUCT関数を使用したExcelでの年ごとの合計値

ここで、SUMPRODUCT関数によって作成される別の簡単な式も役立ちます。一般的な構文は次のとおりです。

=SUMPRODUCT((YEAR(date_range)=year)*sum_range)
  • date_range:特定の年にテストされる日付の範囲。
  • year:値を合計する特定の年。
  • sum_range:合計する値の範囲。

次の数式をコピーするか、空白のセルに入力してから、数式を他のセルにドラッグして結果を取得してください。


この式の説明:

=SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)

  • YEAR($ B $ 2:$ B $ 12)= E2:YEAR関数は、範囲B2:B12の各日付セルから年番号を返します。 この式は、E2の年が範囲B2:B12の年と等しいかどうかをチェックします。 そして、次のような配列を取得します:{TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE}。
  • (YEAR($B$2:$B$12)=E2)*$C$2:$C$12={TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}*$C$2:$C$12:この式では、TRUEとFALSEが1と0に強制され、この配列にC2:C12の範囲の各量が乗算され、次のような新しい配列が取得されます。{347; 0; 398; 430; 0; 379; 412; 0; 0; 420; 0}。
  • SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)= SUMPRODUCT({347;0;398;430;0;379;412;0;0;420;0}):SUMPRODUCT関数は、最終的にこの配列のすべての数値を合計し、結果を2386として返します。

使用される相対関数:

  • SUMIFS:
  • ExcelのSUMIFS関数は、複数の基準に基づいてセル値を合計するのに役立ちます。
  • SUMPRODUCT:
  • SUMPRODUCT関数を使用して、XNUMXつ以上の列または配列を乗算し、積の合計を取得できます。
  • DATE:
  • DATE関数は、別々のセルの年、月、日の数値を有効な日付に組み合わせるのに役立ちます。
  • YEAR:
  • YEAR関数は、指定された日付に基づいて年を4桁のシリアル番号形式で返します。

その他の記事:

  • 月ごとの合計値(年ありまたはなし)
  • このチュートリアルでは、Excelで特定の月と年ごとに値を合計するか、特定の月ごとに値を合計して年を無視するのに役立つXNUMXつの数式を詳しく説明します。
  • 平日または平日ごとの合計値
  • このチュートリアルでは、Excelで指定された週番号または平日に基づいて、特定の範囲の値を合計するのに役立つ数式を段階的に示します。

最高のオフィス生産性ツール

Kutools forExcel-群衆から目立つのに役立ちます

人気の機能: 重複を検索、強調表示、または識別する  |  空白行を削除する  |  データを失わずに列またはセルを結合する  |  数式なしのラウンド ...
スーパーVルックアップ: 複数の基準  |  複数の値  |  複数のシートにわたって  |  ファジールックアップ...
上級ドロップダウンリスト: 簡単なドロップダウンリスト  |  依存関係のドロップダウン リスト  |  複数選択のドロップダウンリスト...
列マネージャー: 特定の数の列を追加する  |  列の移動  |  Toggle 非表示列の表示ステータス  列を比較する 同じセルと異なるセルを選択する ...
注目の機能: グリッドフォーカス  |  デザインビュー  |  ビッグフォーミュラバー  |  ワークブックとシートマネージャー | リソースライブラリ (自動テキスト)  |  日付ピッカー  |  ワークシートを組み合わせる  |  セルの暗号化/復号化  |  リストごとにメールを送信する  |  スーパーフィルター  |  特殊フィルター (太字/斜体/取り消し線をフィルター...) ...
上位 15 のツールセット12 テキスト 工具 (テキストを追加, 文字を削除する ...)  |  50+ チャート 種類 (ガントチャート ...)  |  40+ 実用的 (誕生日に基づいて年齢を計算する ...)  |  19 挿入 工具 (QRコードを挿入, パスから画像を挿入 ...)  |  12 変換 工具 (数字から言葉へ, 通貨の換算 ...)  |  7 マージ&スプリット 工具 (高度な結合行, Excelのセルを分割する ...)  |  ... もっと

Kutools for Excelは300以上の機能を誇り、 必要なものをワンクリックで手に入れることができます...


Officeタブ-MicrosoftOffice(Excelを含む)でタブ付きの読み取りと編集を有効にする

  • 数十の開いているドキュメントを切り替えるのにXNUMX秒!
  • マウスの手に別れを告げて、毎日何百ものマウスクリックを減らしてください。
  • 複数のドキュメントを表示および編集する際の生産性が 50% 向上します。
  • Chrome、Edge、Firefox と同様に、効率的なタブを Office (Excel を含む) にもたらします。
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations