方法 1. 数式
ウォームアップのために、最も簡単なオプションである数式から始めましょう。 入力として日付でソートされた小さなテーブルがある場合、別の列で累計を計算するには、次の基本式が必要です。
ここでの主な機能は、SUM 関数内の範囲のトリッキーな固定です。範囲の先頭への参照は絶対 (ドル記号付き) になり、末尾への参照は相対 (ドル記号なし) になります。 したがって、数式を列全体にコピーすると、範囲が拡大し、その合計が計算されます。
このアプローチの欠点は明らかです。
- テーブルは日付順に並べ替える必要があります。
- データを含む新しい行を追加する場合、数式を手動で拡張する必要があります。
方法 2. ピボット テーブル
この方法は少し複雑ですが、はるかに快適です。 さらに深刻なことに、より深刻な問題を考えてみましょう。2000 行のデータのテーブルで、日付列による並べ替えはありませんが、繰り返しがあります (つまり、同じ日に数回販売することができます)。
元のテーブルを「スマート」（動的）キーボード ショートカットに変換します Ctrlキー+T またはチーム ホーム – 表としてフォーマットする (ホーム — テーブルとしてフォーマット)、次にコマンドでピボットテーブルを作成します 挿入 – ピボットテーブル (挿入 — ピボット テーブル). サマリーの行領域に日付を入れ、値領域に販売された商品の数を入れます。
Excel のバージョンがそれほど古いものではない場合、日付は自動的に年、四半期、月ごとにグループ化されることに注意してください。 別のグループ化が必要な場合 (またはまったく必要ない場合) は、任意の日付を右クリックしてコマンドを選択することで修正できます。 グループ化/グループ解除 (グループ化/グループ解除).
期間ごとの結果の合計と現在の合計の両方を別の列に表示する場合は、フィールドを値領域に入れるのが理にかなっています 売り切れ もう一度フィールドの複製を取得します。その中で、累計の表示をオンにします。 これを行うには、フィールドを右クリックしてコマンドを選択します 追加計算 - 累積合計 (値を次のように表示 — 累計):
そこで、合計をパーセンテージで増加させるオプションを選択することもできます。次のウィンドウで、累積するフィールドを選択する必要があります。この場合、これは日付フィールドです。
このアプローチの利点：
- 大量のデータを高速で読み取ります。
- 数式を手動で入力する必要はありません。
- ソース データを変更する場合は、マウスの右ボタンまたは [データ - すべて更新] コマンドを使用して概要を更新するだけで十分です。
短所は、これが要約であるという事実から生じます。つまり、その中でやりたいこと (行の挿入、数式の作成、図の作成など) ができなくなり、機能しなくなります。
方法 3: パワー クエリ
コマンドを使用して、ソース データを含む「スマート」テーブルを Power Query クエリ エディターに読み込みましょう。 データ – テーブル/範囲から (データ — テーブル/範囲から). ちなみに、最新バージョンの Excel では名前が変更されていましたが、現在は 葉付き (シートより):
次に、次の手順を実行します。
1. コマンドを使用して、テーブルを日付列で昇順に並べ替えます ソート昇順 テーブル ヘッダーのフィルタ ドロップダウン リストで
2. 少し後で、現在の合計を計算するために、序数の行番号を含む補助列が必要になります。 コマンドで追加しましょう 列の追加 – インデックス列 – 1 から (列を追加 — インデックス列 — 1 から).
3. また、現在の合計を計算するには、列への参照が必要です 売り切れ、要約されたデータがある場所。 Power Query では、列はリスト (リスト) とも呼ばれ、そのリンクを取得するには、列ヘッダーを右クリックしてコマンドを選択します。 ディテール （詳細を表示）. 必要な式が数式バーに表示され、前のステップの名前で構成されます #「インデックス追加」、ここからテーブルと列名を取得します [販売】 角括弧内のこの表から:
この式をクリップボードにコピーして、さらに使用します。
4. 不要なさらに最後のステップを削除 売り切れ 代わりに、コマンドで実行中の合計を計算するための計算列を追加します 列の追加 – カスタム列 (列を追加 — カスタム列). 必要な式は次のようになります。
ここで関数 リスト範囲 元のリストを取ります (列 【販売】) を作成し、最初から要素を抽出します (数式では、Power Query の番号付けはゼロから始まるため、これは 0 です)。 取得する要素の数は、列から取得した行番号です [索引]. したがって、最初の行に対するこの関数は、列の最初のセルを XNUMX つだけ返します。 売り切れ. XNUMX 行目はすでに最初の XNUMX つのセル、XNUMX 行目は最初の XNUMX つなどです。
さて、それでは関数 リストの合計 抽出された値を合計し、各行で以前のすべての要素の合計、つまり累積合計を取得します。
不要になった Index 列を削除し、Home – Close & Load to コマンドを使用して結果を Excel にアップロードし直す必要があります。
問題は解決しました。
ワイルド・スピード
原則として、これは止められた可能性がありますが、軟膏には小さな問題があります。私たちが作成した要求は、カメの速度で機能します. たとえば、私の最も弱い PC では、わずか 2000 行のテーブルが 17 秒で処理されます。 さらにデータがある場合はどうなりますか？
高速化するには、特別な List.Buffer 関数を使用してバッファリングを使用できます。この関数は、引数として指定されたリスト (リスト) を RAM にロードします。これにより、将来のアクセスが大幅に高速化されます。 この場合、2000 行のテーブルの各行の累計を計算するときに Power Query がアクセスする必要がある #"Added index"[Sold] リストをバッファリングすることは理にかなっています。
これを行うには、[メイン] タブの Power Query エディターで、[詳細エディター] ボタン ([ホーム] – [詳細エディター]) をクリックして、Power Query に組み込まれている M 言語でクエリのソース コードを開きます。
そして、そこに変数を含む行を追加します 私のリストの値はバッファリング関数によって返され、次のステップでリストへの呼び出しをこの変数に置き換えます。
これらの変更を行った後、クエリは大幅に高速になり、わずか 2000 秒で 0.3 行のテーブルを処理できます。
別のことですよね？ 🙂
- パレート図 (80/20) と Excel での作成方法
- テキストでのキーワード検索と Power Query でのクエリ バッファリング