☰

Excel のピボット テーブルに累積カウントを追加する

tag-icon • tag-icon microsoft-excel pivot-table
Excel のピボット テーブルに累積カウントを追加する

Excel に次のようなデータがあります:

Shop   | Division    | Launch Year
-------+-------------+-------------
Shop 1 | Division 1  | 2005
Shop 2 | Division 1  | 2006
Shop A | Division 2  | 2005
Shop B | Division 2  | 2007
...

などなど。4 つの部門にまたがって合計約 100 店舗あります。部門ごとに、年末に何店舗オープンしているかを示す表を取得する必要があります。ピボット テーブルを使用すると、その年にオープンした新しい店舗の数を簡単に表示できますが、合計数は表示できません。

ピボット テーブルに累計を追加する方法はわかっていますが、私の場合は追加する数字がないので、それが機能しません。代わりに累計数を追加する必要があります。どうすれば実行できますか?

私は次のような最終結果を求めています:

Year | Division 1 | Division 2 | ...
-----+------------+------------+----
2005 | 1          | 1          |
2006 | 2          | 1          |
2007 | 2          | 2          |
...

答え1

ランニングカウントは 1 の合計値です。
データが許す場合は、1 でいっぱいの列を追加し、ピボットでそれらを使用して累計を計算します。

答え2

テーブルが比較的静的な場合 (列と行があまり変化しない場合) は、それらの列ヘッダーと行ヘッダーを使用します。本文では、次のような数式を使用します (疑似コード):

=countifs('Launch Year',"<="&'row header', 'Division', 'column header')

適切なアンカーを使用すれば、数式を一度作成し、それを本文全体にコピーして貼り付けることができます。

答え3

これにはピボットテーブルは必要ありません。

希望する出力に示されているようにテーブルを作成します。次の配列数式を入力してCtrl Shift Enter横方向および縦方向にコピーし、データ領域に入力します。

=SUMPRODUCT(--IF($C$2:$C$5<=$E2,1,0),IF($B$2:$B$5=F$1,1,0))

ここに画像の説明を入力してください

アンカーに注意しながら、必要に応じてセル参照を更新します。

積和関数ドキュメンテーションMSから。

ステートメント 2を使用して、IF一致する値の 2 つの同一サイズの配列 (を に--、をに) を生成し、それらを相互に乗算して両方の条件に一致するレコードを検索し、結果として得られる とのリストを合計して合計を取得します。TRUE1FALSE010

編集: コメント/質問に返信します

これまでここで示した設定は、概念実証用の小さなデータと結果のテーブルです。これをさらに拡張可能にするには、入力テーブルを名前付きテーブルに変換することになります。これを行うには、関連する範囲 (例:B4示されている設定内) 内の任意のセルを選択し、Ctrl+を押してT、表示されるモーダル ウィンドウで確定します。これにより、セル参照の記述が容易になり、データが追加されると、それらのデータ範囲へのセル参照が自動的に更新されます。

ここに画像の説明を入力してください

同じ方法を使用して、結果テーブルをテーブルにすることもできます。この方法では、新しい年を追加するときに、そのテーブルの下の次の行に年を入力するだけで、すべての数式が自動的に貼り付けられます。

ここに画像の説明を入力してください

ここに画像の説明を入力してください

出力テーブルのこの自動更新は、新しい部門の追加には機能しないことに注意してください。テーブルはそれに合わせて拡張されますが、数式をSUMPRODUCT新しいフィールドに手動でコピーする必要があります。

頑張って楽しんでください！

関連情報