Excel ピボットテーブルの 5 つのトリックについては誰も教えてくれませんでしたが、今では毎日使っています

ピボットテーブルを使用すると、Excel の数千行を数秒で要約できますが、多くの人は依然として生データのフィルタリング、重複レポートの作成、ツール内にすでに存在する数式の作成に時間を無駄にしています。これらの見落とされがちな 5 つのトリックにより、余分な作業がなくなり、私の毎日のワークフローの一部になりました。

任意の値をダブルクリックしてソース データを表示します

ピボットテーブル レコードをさらに詳しく調べる

私がこのトリックを発見したのは、突然のスパイクを調査していたときまったくの偶然でしたが、このトリックにより、数値のトラブルシューティングと検証の方法が即座に変わりました。

ピボットテーブル内の値の 1 つについてさらに詳しい情報が必要だとします。タブ間をフリックして勢いを失うのではなく、次のようにします。

1. 見つけて、 ダブルクリック 調査するピボットテーブルの値。

2. を確認してください 新しく生成されたワークシート その値のソース行のみが含まれます。

3. レビューが完了したら、 右クリック ウィンドウの下部にある新しいシートのタブをクリックして、 消去。

カテゴリごとに個別のワークシートを生成する

別々のファイルをコピーして貼り付けるのをやめる

これは、昨年私が最も時間を節約してくれたピボットテーブル機能です。

以前は、別の人が同じレポートのフィルタリングされたバージョンを必要とするたびに、ピボットテーブルを複製して何時間も無駄にしていました。ただし、専用のピボットテーブル機能がこの配布タスク全体を自動的に処理します。たとえば、レポートが地域またはマネージャーによってフィルターされている場合、Excel はフィルター リスト内のカテゴリごとに 1 つのワークシートをすぐに生成できます。

まず、自動化を設定します。

1. 分割したいカテゴリフィールドをドラッグして、 フィルター の箱 ピボットテーブルのフィールド ペイン。

2. クリック ピボットテーブル内 をクリックしてコンテキスト リボン ツールを表示します。

3. を開きます ピボットテーブル分析 タブ。

4. 小さいをクリックしてください ドロップダウン矢印 すぐ隣にある オプション 一番左のボタン。

5. 選ぶ レポートフィルターページを表示 コンテキスト ドロップダウン メニューから。

次に、シートを生成するには、次のようにします。

1. 選択されていることを確認します フィルターフィールド ポップアップ ダイアログ ボックスの内容は、 ターゲット列。

2. クリック わかりました シート生成の自動化を実行します。

3. をクリックして進みます 新しく作成されたワークシートのタブ をクリックすると、個々のレポートが表示されます。

4. 特定のレポートをエクスポートするには、 右クリック ワークシートタブをクリックし、 移動またはコピー。

OS

Windows、macOS、iPhone、iPad、Android

無料トライアル

1ヶ月

Microsoft 365 には、最大 5 台のデバイスで Word、Excel、PowerPoint などの Office アプリ、1 TB の OneDrive ストレージなどへのアクセスが含まれています。

Distinct Count を使用して一意の値を追跡する

アイテムを重複せずに集計する

ピボットテーブルで最初に遭遇した大きな制限の 1 つは、合計だけでなく一意のアイテムをカウントできないことでした。標準ピボットテーブルでは、基本的なカウント計算のみが提供されます。つまり、1 人の顧客が 5 回の個別の購入を行った場合、通常のカウントは 5 を返します。

最初にテーブルを作成するときにソース データを Excel のデータ モデルに追加すると、重複エントリを完全に無視する非表示の個別カウント オプションのロックが解除されます。

まず、Excel のデータ モデル ワークスペースを初期化します。

1. を選択してください 生のソーステーブル そして開きます 入れる タブ。

2. クリック ピボットテーブル をクリックして、標準の作成ダイアログ ボックスを開きます。

3. 宛先のワークシートを選択してください 位置。私はそれらを置くことが多いです 新しいワークシート したがって、ソース データとピボットテーブルは明確に分離されます。

4. チェックしてください このデータをデータモデルに追加します 箱。

5. クリック わかりました 新しいピボットテーブルを生成します。

これで、概要を個別のカウントに切り替える準備が整いました。

1. 識別フィールドを 価値観 箱。

2. 右クリック その中の任意の数字 新しく追加されたコラム そして選択します 値フィールドの設定。

3. 計算リストを下にスクロールして、 個別のカウント。

4. クリック わかりました。

ピボットテーブルはすぐに更新され、個別のカウントが表示されます。つまり、各顧客は購入回数に関係なく、地域ごとに 1 回だけカウントされます。

乱雑なカテゴリをピボットテーブル内に直接統合

他のシステムから、レポートに使用する前に、より広範なバケットにグループ化する必要がある、過度に特殊なカテゴリを持つデータセットを受け取ることがよくあります。マスター データベースを変更したりヘルパー列を作成したりする代わりに、ピボットテーブル自体内で直接統合を処理します。

カスタム グループを作成およびクリーンアップする方法は次のとおりです。

1. 所有 Ctrl それぞれをクリックしながら 個別のテキストラベル 最初のカスタム グループに属する行。

2. それらの項目が選択されたままの状態で、 右クリック いずれか 1 つを選択してください グループ。

3. この操作を行うと、最初はピボットテーブルが乱雑に見えるため、ピボットテーブルを右クリックします。 ピボットテーブルの左端の列ヘッダー そして選択します 展開/折りたたむ > フィールド全体を折りたたむ 物事を整理すること。

4. 汎用グループ ラベル (例: グループ1）、 それから 上書きする 既存のテキストにわかりやすい名前を付けて、 を押します。 入力。

残りの項目に対して選択、グループ化、名前変更の手順を繰り返した後、次のようになります。

1. 右クリック 新しく作成された親 フィールドヘッダー グリッド内で。

2. クリック フィールド設定。

3. 名前の変更 フィールドが表すカテゴリを反映するフィールドを選択し、 わかりました。

すべての関連項目とそれに関連する数値がグループ化されるようになったので、ソース データを手動で再構築することなく、カテゴリをすばやく要約し、より高いレベルの傾向を特定し、より明確なレポートを作成できるようになりました。

数式を書かずに前月比の伸びを計算

ピボットテーブルに計算を任せる

この計算機能を発見する前は、ピボットテーブル データを定期的にエクスポートし、データが更新されるたびに完全に壊れてしまう手動の増加数式を構築していました。ネイティブの[値を次のように表示]オプションは、前月比、四半期比、前年比の動的なレポートに完全に機能します。

前期比成長率ビューを構成するには:

1. ドラッグしてください コアパフォーマンス数値 に 価値観 もう一度ボックスをオンにすると、グリッド内に重複して表示されます。

2. 右クリック その中の任意のセル 新しく複製された値の列。

3. マウスオーバーします 値を次のように表示を選択し、 % との差。

4. を設定します。 ベースフィールド ドロップダウン オプション 月 から作成されたフィールド 日付 グループ化。

5. を設定します。 ベースアイテム ドロップダウン オプションから （前の）をクリックしてから、 わかりました。

これで、ピボットテーブルに前月比のパーセンテージの差が正常に表示されたので、重複した値の列のヘッダーをクリックして、 名前を変更します ピボットテーブル グリッド内で直接 (たとえば、 前月比の伸び）。これは表示ラベルの変更のみであり、基礎となる計算への変更ではないため、ここで名前を変更しても問題ありません。

よりスマートなピボットテーブルで手作業を軽減

これらのピボットテーブルのテクニックを使用して以来、重複したレポートを作成したり、余分な数式を記述したりすることがなくなりました。これらのテクニックにより、大規模なデータセットの操作が合理化され、レポートの効率が大幅に向上しました。これら 5 つのワークフロー アップグレードに加えて、スライサーとタイムライン フィルターを追加することで、ピボットテーブルをさらに進化させることができます。