Excelで現在の合計（累積合計）を計算する-5つの簡単な方法

累計（別名累積合計）多くの場合、非常に一般的に使用されます。これは、これまでの値の合計が何であるかを示すインジケーターです。

たとえば、月間売上データがある場合、現在の合計は、その月の初日から特定の日付までに行われた売上の数を示します。

銀行取引明細書/元帳での現金残高の計算、食事プランでのカロリーの計算など、合計が頻繁に使用される他の状況があります。

Microsoft Excelでは、現在の合計を計算するいくつかの異なる方法があります。

選択する方法は、データの構造によっても異なります。

たとえば、単純な表形式のデータがある場合は、単純なSUM数式を使用できますが、Excelテーブルがある場合は、構造化参照を使用することをお勧めします。PowerQueryを使用してこれを行うこともできます。

このチュートリアルでは、それらすべてについて説明します。Excelで現在の合計を計算するさまざまな方法。

それでは始めましょう！

表形式のデータを使用して現在の合計を計算する

表形式のデータ（つまり、Excelテーブルに変換されていないExcelのテーブル）がある場合は、いくつかの簡単な数式を使用して現在の合計を計算できます。

加算演算子を使用する

日付別の売上データがあり、で 列C計算する累計。

これを行う手順は次のとおりです。

ステップ1 –合計を実行する最初のセルであるセルC2に、次のように入力します。

= B2

これは、セルB2で同じ売上値を取得します。

ステップ2–セルC3に、次の数式を入力します。

= C2 + B3

ステップ3–数式を列全体に適用します。塗りつぶしハンドルを使用して選択してドラッグするか、セルC3を残りのすべてのセルにコピーして貼り付けることができます（これにより、参照が自動的に調整され、正しい結果が得られます）。

これにより、以下に示す結果が得られます。

これは非常に単純な方法であり、ほとんどの場合にうまく機能します。

ロジックは単純です。各セルはその値（前日までの累積合計）を取得し、隣接するセルの値（当日の売上値）を加算します。

欠点はXNUMXつだけです。このデータセットの既存の行を削除すると、下のすべてのセルが参照エラー（#REF！）を返します。

データセットにこの可能性がある場合は、SUM式を使用して次の方法を使用してください

部分的にロックされたセル参照でSUMを使用する

日付ごとの売上データがあり、列Cで現在の合計を計算するとします。

以下は、現在の合計を示すSUM式です。

= SUM（$ B $ 2：B2）

この式がどのように機能するかを説明しましょう。

上記のSUM式では、参照を使用して$ B $ 2：B2として追加しました

• $ B $ 2-これは絶対参照です。つまり、下のセルに同じ数式をコピーしても、この参照は変更されません。したがって、下のセルの数式をコピーすると、数式はSUM（$ B $ 2：B3）に変わります。
• B2-これは参照のXNUMX番目の部分であり、相対参照です。つまり、数式を下または右にコピーすると、これが調整されます。したがって、下のセルの数式をコピーすると、値はB3になります。

このアプローチの良いところは、データセット内の行を削除した場合、この数式が調整され、正しい現在の合計が得られることです。

Excelシートで現在の合計を計算する

Excelで表形式のデータを操作する場合は、Excelテーブルに変換するのが最適です。データの管理が容易になり、PowerQueryやPowerPivotなどのツールも簡単に使用できるようになります。

Excelテーブルを使用すると、構造化参照（テーブル内のデータを参照して数式で使用するのが非常に簡単になります）や、テーブルにデータを追加または削除するときに参照を自動的に調整するなど、いくつかの利点があります。

Excelシートで示した上記の数式を引き続き使用できますが、それを行うためのより良い方法をいくつか紹介します。

次のようなExcelテーブルがあり、列Cの現在の合計を計算するとします。

これを行うための式は次のとおりです。

= SUM（SalesData [[＃Headers]、[Sale]]：[@ Sale]）

上記の式は少し長いように見えるかもしれませんが、自分で書く必要はありません。集計式に表示されるものは構造化参照と呼ばれ、ExcelがExcelテーブル内の特定のデータポイントを参照するための効率的な方法です。

たとえば、SalesData [[＃Headers]、[Sale]]は、SalesDataテーブルのSalesヘッダーを参照します（SalesDataは、テーブルを作成したときに指定したExcelテーブルの名前です）。

一方、[@ Sale]は、Sale列の同じ行にあるセルの値を指します。

ここでは理解のために説明しているだけですが、構造化参照について何も知らなくても、この式を簡単に作成できます。

これを行う手順は次のとおりです。

1. セルC2に、= SUM（と入力します。
2. 販売額のある列のヘッダーであるセルB1を選択します。マウスを使用するか、矢印キーを使用できます。Excelがセルへの構造化された参照を自動的に入力することに気付くでしょう
3. 追加:(コロン）
4. セルB2を選択します。Excelは、セルへの構造化参照を再び自動的に挿入します
5. ブラケットを閉じてEnterキーを押します

また、列全体の数式をコピーする必要がないことにも気付くでしょう。Excelシートが自動的にコピーします。

このアプローチのもうXNUMXつの利点は、このデータセットに新しいレコードを追加すると、Excelシートがすべての新しいレコードの現在の合計を自動的に計算することです。

数式に列ヘッダーを含めましたが、数式はヘッダーテキストを無視し、列のデータのみを考慮することに注意してください。

PowerQueryを使用して現在の合計を計算する

Power Queryは、データベースへの接続、複数のソースからのデータの抽出、およびExcelに配置する前の変換に関しては素晴らしいツールです。

すでにPowerQueryを使用している場合は、Power Query Editor自体でデータを変換するときに積算合計を追加する方が効率的です（最初にExcelでデータを取得してから、上記の方法のいずれかを使用して積算合計を追加するよりも）。

Power Queryには、現在の合計を追加するための組み込み機能はありませんが（あったらいいのですが）、簡単な数式で追加できます。

次のようなExcelテーブルがあり、このデータに現在の合計を追加するとします。

これを行う手順は次のとおりです。

1. Excelテーブルの任意のセルを選択します
2. データをクリック
3. [取得と変換]タブで、[テーブル/範囲から]アイコンをクリックします。これにより、Power QueryEditorでテーブルが開きます
4. [オプション]日付列がまだ並べ替えられていない場合は、日付列のフィルターアイコンをクリックしてから、[昇順で並べ替え]をクリックします
5. PowerQueryエディタの[列の追加]タブをクリックします
6. [全般]グループで、[インデックス列]ドロップダウンメニューをクリックします（[インデックス列]アイコンをクリックしないでください。横にある小さな黒い斜めの矢印をクリックすると、その他のオプションが表示されます）
7. 「1から」オプションをクリックします。これを行うと、1から始まり、列全体に1ずつ増加する数値を入力する新しいインデックス列が追加されます。
8. [カスタム列]アイコンをクリックします（[列の追加]タブにもあります）
9. 開いた[カスタム列]ダイアログで、新しい列の名前を入力します。この例では、「RunningTotal」という名前を使用します
10. [カスタム列の数式]フィールドに、次の数式を入力します。List.Sum（List.Range（＃ "インデックスを追加" [Sale]、0、[Index]））
11. ダイアログの下部に「構文エラーは検出されませんでした」というチェックボックスがあることを確認してください
12. [OK]をクリックします。これにより、新しい累計列が追加されます
13. ドロップインデックス列
14. [ファイル]タブをクリックし、[閉じて読み込む]をクリックします

上記の手順により、現在の合計を含むテーブルを含む新しいワークシートがブックに挿入されます。

さて、単純な数式を使用した以前のアプローチと比較して、これらのステップが多すぎると思われる場合は、その通りです。

すでにデータセットがあり、現在の合計を追加するだけの場合は、PowerQueryを使用しないことをお勧めします。

Power Queryの使用は、データベースからデータを抽出したり、複数の異なるワークブックからデータを結合したり、プロセスでそれらに現在の合計を追加したりする必要がある状況で意味があります。

また、Power Queryを使用してこれを自動化すると、次にデータセットが変更されたときに再度行う必要はなく、クエリを更新するだけで、新しいデータセットに基づいた結果が得られます。

これはどのように作動しますか？

ここで、このアプローチで何が起こるかを簡単に説明しましょう。

Power Query Editorで最初に行うことは、1から始まり、セルを下るにつれて増加するインデックス列を挿入することです。

これを行うのは、次のステップで挿入される別の列の現在の合計を計算するときにこの列を使用する必要があるためです。

次に、カスタム列を挿入し、次の式を使用します

List.Sum（List.Range（＃ "インデックスを追加" [Sale]、0、[Index]））

これは、指定された範囲の合計を提供するList.Sum式です。

範囲は、List.Range関数を使用して指定されます。

List.Range関数は、sale列で指定された範囲を出力として受け取り、範囲はIndex値によって異なります。たとえば、最初のレコードの場合、範囲は最初の販売額です。この範囲は、セルを下に行くにつれて拡大します。

だから、最初のセルのために。List.Sumは最初の販売額の合計のみを提供し、XNUMX番目のセルは最初のXNUMXつの販売額の合計を提供します。

このアプローチはうまく機能しますが、大規模なデータセット（数千行）では非常に遅くなる可能性があります。

基準に基づいて現在の合計を計算する

これまで、列内のすべての値の現在の合計を計算する例を見てきました。

ただし、場合によっては、特定のレコードの現在の合計を計算したい場合があります。

たとえば、以下に、XNUMXつの異なる列のプリンターとスキャナーの現在の合計をカウントするデータセットがあります。

これは、指定された条件が満たされていることを確認しながら現在の合計を計算するSUMIF式を使用して実行できます。

プリンタ列に対してこれを行う式は次のとおりです。

=SUMIF($C$2:C2,$D$1,$B$2:B2)

同様に、スキャナーの現在の合計を計算するには、次の式を使用します。

=SUMIF($C$2:C2,$E$1,$B$2:B2)

上記の式では、指定された条件が満たされたときに範囲内の合計を取得するSUMIFを使用しました。

式はXNUMXつのパラメーターを取ります。

1. 範囲：これは、指定された条件に対してチェックされる条件の範囲です。
2. 基準：これは、この基準が満たされた場合にのみチェックされる基準であり、XNUMX番目のパラメーターの値である合計範囲が追加されます
3. [合計範囲]：これは、条件が満たされたときに追加する値の合計の範囲です

また、範囲と合計範囲パラメータ、参照のXNUMX番目の部分をロックして、セルを下に移動しても範囲が拡大し続けるようにしました。これにより、その範囲までの値のみを考慮して追加することができます（したがって、現在の合計）。

この式では、ヘッダー列（プリンターとスキャナー）を基準として使用します。列見出しが標準テキストと完全に同じでない場合は、標準をハードコーディングすることもできます。

複数の条件をチェックする必要がある場合は、SUMIFS式を使用できます。

ピボットテーブルの現在の合計

ピボットテーブルの結果に現在の合計を追加する場合は、ピボットテーブルの組み込み機能を使用して簡単に追加できます。

次のようなピボットテーブルがあり、XNUMXつの列に日付があり、別の列に売上値があるとします。

日付ごとの累積売上を表示する列を追加する手順は次のとおりです。

1. 「販売」フィールドをドラッグして、「値」領域にドロップします。
2. これにより、売上値を含む別の列が追加されます
3. [値]領域の[総売上高2]オプションをクリックします
4. [値フィールドの設定]オプションをクリックします
5. [値フィールドの設定]ダイアログで、[カスタム名]を[現在の合計]に変更します
6. [値を表示]タブをクリックします
7. [値を表示]ドロップダウンメニューで、[現在の合計]オプションを選択します
8. [基本フィールド]オプションで、[日付]が選択されていることを確認します
9. [OK]をクリックします

上記の手順により、XNUMX番目の売上列が「累計」列に変更されます。

したがって、これらは、Excelで現在の合計を計算するために使用できるいくつかの方法です。表形式のデータがある場合は単純な数式を使用でき、Excelテーブルがある場合は、構造化参照を利用する数式を使用できます。

また、PowerQueryとピボットテーブルを使用して現在の合計を計算する方法についても説明しました。

このチュートリアルがお役に立てば幸いです。