あらゆる種類の毎日のデータを扱っている場合は、合計を実行する必要があります。

あなたは毎日の販売を追跡想像してみてください。 データには、合計売上金額を含む各日付の行が含まれていますが、各日の月の合計売上金額を知りたい場合があります。 これは実行中の合計であり、現在の日の売上を含むすべての売上の合計です。この記事では、毎日のデータの実行合計を計算するための複数の方法について説明します。

ワークシートの数式、ピボットテーブル、daxとpower queryを使用したpower pivotの使用方法について説明します。また、データの行を挿入または削除するときに実行中の合計計算に何が起こるか、および結果を更新する方法についても説明します。

すべての例を含むファイルを取得します。

単純な式で合計を実行する

+演算子を使用して基本的な合計合計式を作成することができます。ただし、ジョブを完了するには、2つの異なる式を使用する必要があります。

1. =C3が最初の式になり、実行中の合計の最初の行にのみ表示されます。
2. =C4+D3は2番目の行にあり、残りの行の下にコピーして実行中の合計を計算できます。最初の行の数式は、列見出しのテキスト値が含まれているため、その上のセルを合計に追加することはできません。 これにより#VALUEが発生します！ +はテキスト値を処理できないため、実行中の合計に表示されるエラー。 上記のセルを参照していない最初の行に別の数式がある場合は、これを回避します。データに行を挿入または削除すると、実行中の合計はどうなりますか？p>

   

   新しい行を挿入すると、実行中の合計にギャップが発生します。 これを修正するには、新しく挿入された行の上の最初のセルから最後の行まで数式をコピーする必要があります。

   

   行を削除すると、#REF!が表示されます。 行を削除するためのエラーは、その下の式で参照されているセルを削除することを意味します。 これを修正するには、最後のエラーのないセルから最後の行まで数式をコピーする必要があります。

   合計式を使用した合計の実行

   

   +演算子の代わりにSUM関数を使用することで、実行中の合計列に二つの異なる式を使用することのぎこちなさを避けることができます。 SUM関数がテキストセルに遭遇すると、0が含まれていてもaと同じように扱われます。このようにして、最初の行を含むすべての行に対して、次の式を均一に使用できます。この式は、最初の行のテキストを含む列見出しを参照しますが、これは0のように扱われるため問題ありません。行を挿入または削除すると、空白のセルとエラーで同じ問題が発生します。

   行を挿入または削除すると、空白のセルとエラーで同じ問題が発生します。 単純な数式メソッドで合計を実行するのと同じ方法で修正できます。

   部分的に固定された範囲で合計を実行する

   SUM関数を使用する別のオプションは、Sales列のみを参照し、部分的に固定された範囲参照を使用す次の式を使用する場合=SUM(SUM C$3:C3)、これをコピーして範囲内に貼り付けることができます。 列見出しは参照されず、参照される範囲は各行に拡大されます。残念ながら、これも行の挿入または削除で同じ問題（および解決策）を抱えています。

   相対名前付き範囲で合計を実行する

   相対名前付き範囲を使用すると、データから行を挿入および削除する際の問題を回避できます。 これは、挿入または削除する行の数に関係なく、真上のセルを参照します。これは、Excel参照スタイルを一時的にa1からR1C1に切り替えることを含むトリックです。 次に、R1C1表記法を使用して名前付き範囲を定義します。 次に、参照スタイルをA1に戻します。

   R1C1参照スタイルでは、セルは参照を使用してセルからどのくらい離れているかによって参照されます。 たとえば、=RCは、この式を使用して、セルの右側にあるセル2とセル3を参照します。この相対参照を使用して、式=RCを使用して参照セルの上に常に1つのセルである名前付き範囲を作成できます。/p>

   

   参照スタイルを切り替えるには、ファイルタブに移動し、オプションを選択します。 Excelのオプションメニューの数式セクションに移動し、R1C1参照スタイルボックスをオンにして、OKボタンを押します。p>

   

   今、私たちは私たちの名前付き範囲を追加することができます。 Excelリボンの[数式]タブに移動し、[名前の定義]コマンドを選択します。p>

   

   範囲の名前として”Above”のような名前を挿入します。 数式=RCを参照入力に追加し、OKボタンを押します。これで、Excelをデフォルトの参照スタイルに戻すことができます。 ファイルタブに移動します>>R1C1参照スタイルボックスのチェックを外します>OKボタンを押します。これで、running total列で式=SUM（、上記）を使用できます。

   上記の名前付き範囲は、常に真上のセルを参照します。 行を挿入または削除すると、相対的な名前付き範囲がそれに応じて調整され、アクションは必要ありません。実際、データをExcelテーブルに配置すると、数式は列全体で統一されているため、新しい行が自動的に入力されます。 数式をコピーするための操作は必要ありません。ピボットテーブルで合計を実行する

   

   ピボットテーブルは、任意のタイプのデータを要約するのに非常に便利です。 彼らには、平均を追加し、数え、見つけるだけではありません。 そこに組み込まれた計算の他の多くの種類があり、実行中の合計計算が実際にあります！まず、データに基づいてピボットテーブルを挿入する必要があります。 データ内のセルを選択し、[挿入]タブに移動し、[ピボットテーブルコマンド]を選択します。 次に、ピボットテーブルの作成ウィンドウを使用して、新しいワークシートまたは既存のワークシートのどこかにピボットテーブルが必要な場所を選択します。

   

   ピボットテーブルの行領域に日付フィールドを追加し、ピボットテーブルの値領域にSalesフィールドを追加します。 次に、Salesフィールドの別のインスタンスをRows領域に追加します。

   二つの同一のSalesフィールドがあり、そのうちの一つがSum of Sales2というラベルが付けられているはずです。 このラベルの名前は、Running Totalのようなもので入力するだけでいつでも変更できます。

   Sum of Sales2フィールドのいずれかの値を右クリックし、Show Value Asを選択し、Running Total Inを選択します。p>

   

   日付別に実行中の合計を表示したいので、次のウィンドウで日付をベースフィールドとして選択する必要があります。

   これで、ピボットテーブル内に売上の累計を表示する新しい計算ができました。

   ソースデータの行を追加または削除するとどうなりますか、これは実行中の合計にどのように影響しますか？ ピボットテーブルの計算は動的であり、実行中の合計計算で新しいデータを考慮に入れます。

   ピボットテーブル内の任意の場所を右クリックし、メニューから”更新”を選択します。

   Power PivotとDAX Measuresを使用した合計の実行

   このための最初のカップルのステップは、通常のピボットテーブルを使用してまったく同じです。

   データ内のセルを選択し、挿入タブに移動し、ピボットテーブルコマンドを選択します。P>

   ピボットテーブルの作成メニューに移動したら、このデータをデータモデルに追加ボックスをオンにしてデータをデータモデルに追加し、power pivotで使用できるよ

   

   ピボットテーブルの行領域に日付フィールドを配置し、ピボットテーブルの値領域にSalesフィールドを配置します。powerpivotでは、DAX言語を使用して必要な追加の計算を作成する必要があります。 ピボットテーブルフィールドウィンドウでテーブル名を右クリックし、メジャーの追加を選択して新しい計算を作成します。 これはデータモデルでのみ使用できます。p>

   =CALCULATE ( SUM ( Sales ), FILTER ( ALL (Sales ), Sales 

   今、私たちは私たちの新しい実行中の合計メジャーを作成することができます。

   1. メジャーウィンドウで、メジャー名を追加する必要があります。 この場合、新しいメジャーにRunning Totalという名前を付けることができます。また、上記の数式を数式ボックスに追加する必要があります。
   2. power pivotの素晴らしい点は、メジャーに数値形式を割り当てる機能です。 私達は私達の測定のための通貨のフォーマットを選んでもいいです。 ピボットテーブルでこのメジャーを使用するたびに、書式が自動的に適用されます。

   OKボタンを押すと、新しいメジャーが作成されます。P>

   

   ピボットテーブルフィールドウィンドウに新しいフィールドがリストされます。 左側に小さなfxアイコンがあり、メジャーであり、データ内の通常のフィールドではないことを示します。この新しいフィールドは、他のフィールドと同じように使用し、値領域にドラッグして、実行中の合計計算をピボットテーブルに追加できます。

   

   この新しいフィールドを使用して、ピボットテーブルに実行中の合計計算を追加することができます。

   ソーステーブルからデータを追加または削除すると、実行中の合計はどうなりますか？ 通常のピボットテーブルと同様に、ピボットテーブルを右クリックして[更新]を選択して計算を更新するだけです。

   Power queryを使用した合計の実行

   power queryを使用してデータに合計の実行を追加することもできます。

   

   まず、テーブルをpower queryにインポートする必要があります。 データのテーブルを選択し、[データ]タブに移動し、[テーブル/範囲から]オプションを選択します。 これにより、powerクエリエディタが開きます。

   

   次に、データを日付で並べ替えることができます。 これは、ソースデータの順序を変更しても、実行中の合計が日付順に表示されるように、追加できるオプションのステップです。

   日付列見出しのフィルタトグルをクリックし、オプションから昇順に並べ替えを選択します。p>

   

   インデックス列を追加する必要があります。 これは、後で実行中の合計計算で使用されます。 [列の追加]タブに移動し、[インデックス]列の横にある小さな矢印をクリックして、最初の行に1から始まるインデックスを挿入します。実行中の合計を計算するには、クエリに新しい列を追加する必要があります。 [列の追加]タブに移動し、[カスタム列]コマンドを選択します。列にRunning Totalという名前を付け、次の式を追加できます。/p>

   リスト。Sum（リスト。Range(#”Added Index”,0,))

   リスト。Range関数は、インデックス列の値に基づいて行数にまたがる1行目（0番目の項目）から始まるSales列から値のリストを作成します。

   リスト。Sum関数は、実行中の合計であるこの値のリストを加算します。

   

   インデックス列は必要なくなり、目的を果たしており、削除できます。 列見出しを右クリックし、オプションから削除を選択します。

   実行中の合計があり、クエリエディタで終了しました。 クエリを閉じて、結果を新しいワークシートに読み込むことができます。 クエリエディタのホームタブに移動し、閉じる&ロードボタンを押します。

   ソースデータから行を追加または削除すると、実行中の合計はどうなりますか？ 変更で実行中の合計を更新するには、power query出力テーブルを更新する必要があります。 テーブル上の任意の場所を右クリックし、[更新]を選択してテーブルを更新します。

   上記のオプションの並べ替え手順で、ソースデータに順不同の日付を追加すると、power queryは日付順に並べ替え、実行中の合計の正しい順序を返します。

   結論

   Excelで実行中の合計を計算するには、さまざまなオプションがあります。

   ワークシート内の数式、ピボットテーブル、power pivot DAX数式、power queryなどのオプションを検討しました。 データから行を追加または削除するときに、より堅牢なソリューションを提供する方法もあれば、実装が容易な方法もあります。ワークシート内の単純な数式は簡単に設定できますが、新しいデータ行の挿入や削除は簡単には処理されません。

   Pivot tables、DAX、power queryなどの他のソリューションは、より堅牢で、データ行の挿入または削除を簡単に処理できますが、設定が困難です。各方法の長所と短所を認識し、最も適した方法を選択することは良いことです。

   新しいデータを挿入または削除しない場合は、ワークシートの数式が適している場合があります。