通常、ピボットテーブルを作成したら、分析を拡張して、より多くのデータ/計算を含める必要があります。
ピボットテーブルの既存のデータポイントを使用して取得できる新しいデータポイントが必要な場合は、戻ってソースデータに追加する必要はありません。代わりに、ピボットテーブルの計算フィールドこれをする。
内容
ピボットテーブルの計算フィールドとは何ですか?
ピボットテーブルの基本的な例から始めましょう。
小売業者のデータセットがあり、次のようなピボットテーブルを作成するとします。
上記のピボットテーブルは、小売業者の売上高と利益の値をまとめたものです。
では、これらの小売業者の利益率(利益率は「利益」を「売上高」で割ったもの)も知りたい場合はどうでしょうか。
これを行うにはいくつかの方法があります。
- 元のデータセットに戻り、この新しいデータポイントを追加します。したがって、ソースデータに新しい列を挿入し、その列の利益率を計算できます。これが完了したら、ピボットテーブルのソースデータを更新して、この新しい列をその一部として取得する必要があります。
- このアプローチは機能しますが、手動でデータセットに戻って計算を行う必要があります。たとえば、別の列を追加して、ユニットあたりの平均売上(売上/数量)を計算したい場合があります。この場合も、この列をソースデータに追加してから、ピボットテーブルを更新する必要があります。
- このメソッドは、新しいデータを追加するときにピボットテーブルも肥大化します。
- ピボットテーブルの外部に計算を追加します。ピボットテーブルの構造が変更される可能性が低い場合は、このオプションを選択してください。ただし、ピボットテーブルを変更すると、それに応じて計算が更新されず、間違った結果やエラーが発生する可能性があります。以下に示すように、列に小売業者がいる場合の利益率を計算しました。しかし、顧客から地域に変更すると、式が間違ってしまいます。
- ピボットテーブルを使用してフィールドを計算します。これは既存のピボットテーブルデータを使用して、最も効率的な方法。計算フィールドは、ピボットテーブルの既存の列を使用して追加する仮想列と考えてください。ピボットテーブルの計算フィールドを使用すると、多くの利点があります(後で説明します)。
- 数式を操作したり、ソースデータを更新したりする必要はありません。
- ピボットテーブルに追加する可能性のある新しいデータが自動的に考慮されるため、拡張可能です。計算フィールドを追加すると、ピボットテーブルの他のフィールドと同じように使用できます。
- 更新と管理は簡単です。たとえば、メトリックが変更された場合、または計算を変更する必要がある場合は、ピボットテーブル自体から簡単に行うことができます。
計算フィールドをピボットテーブルに追加します
ピボットテーブルの計算フィールドを既存のピボットテーブルに追加する方法を見てみましょう。
次のようなピボットテーブルがあり、各小売業者の利益率を計算するとします。
ピボットテーブルの計算フィールドを追加する手順は次のとおりです。
計算フィールドを追加すると、ピボットテーブルフィールドリストのフィールドのXNUMXつとして表示されます。
これで、この計算フィールドを他のピボットテーブルフィールドとして使用できます(ピボットテーブル計算フィールドをレポートフィルターまたはスライサーとして使用できないことに注意してください)。
前に述べたように、ピボットテーブルの計算フィールドを使用する利点は、ピボットテーブルの構造を変更でき、自動的に調整されることです。
たとえば、地域を行領域にドラッグアンドドロップすると、次のような結果が得られ、小売業者と地域の利益率の値が報告されます。
上記の例では、単純な数式(= Profit / Sales)を使用して計算フィールドを挿入しました。ただし、一部の高度な数式を使用することもできます。
高度な数式を使用してピボットテーブルの計算フィールドを作成する例を示す前に、知っておく必要のあることがいくつかあります。
- ピボットテーブルの計算フィールドを作成するときに、参照または名前付き範囲を使用することはできません。これにより、VLOOKUP、INDEX、OFFSETなどの多くの数式が除外されます。ただし、参照なしで機能する数式(SUM、IF、COUNTなど)を使用できます。
- 数式で定数を使用できます。たとえば、10%の成長が見込まれる売上の予測を知りたい場合は、式= Sales * 1.1(1.1は定数)を使用します。
- 計算フィールドを生成する数式では、優先順位に従います。ベストプラクティスとして、かっこを使用して、優先順位を覚えておく必要がないようにします。
次に、高度な数式を使用して計算フィールドを作成する例を見てみましょう。
以下に示すデータセットがあり、予測された売上をピボットテーブルに表示する必要があるとします。
予測値には、大規模小売業者の場合は300%の売上増加(5万以上の売上)を使用し、中小規模の小売業者の場合は300%の売上増加(10万未満の売上)を使用します。
注:ここでの売上高は偽物であり、このチュートリアルの例を説明するために使用されています。
方法は次のとおりです。
これにより、売上予測値を含む新しい列がピボットテーブルに追加されます。
ピボットテーブルの計算フィールドの問題
計算フィールドは、すべてをスケーラブルで管理しやすくしながら、フィールド計算でピボットテーブルの価値を実際に高める素晴らしい機能です。
ただし、ピボットテーブルの計算フィールドには問題があり、使用する前に理解しておく必要があります。
以下のようなピボットテーブルがあり、計算フィールドを使用して予測販売数を取得するとします。
小計と合計が正しくないことに注意してください。
これらは小売業者ごとに個別の売上予測値を追加する必要がありますが、実際には、作成したものと同じ計算フィールド式に従います。
したがって、South Totalの場合、値は22,824,000であるはずですが、SouthTotalは誤って22,287,000と報告します。これは、式21,225,800 * 1.05を使用して値を取得するために発生します。
残念ながら、この問題を修正することはできません。
これを処理する最良の方法は、ピボットテーブルから小計と総計を削除することです。
合格することもできます デブラによって提示この問題に対処するためのいくつかの革新的な回避策。
ピボットテーブルの計算フィールドを変更または削除するにはどうすればよいですか?
ピボットテーブルの計算フィールドを作成した後、次の手順を使用して数式を変更または削除できます。
計算されたすべてのフィールド数式のリストを取得するにはどうすればよいですか?
ピボットテーブルの計算フィールドを多数作成する場合は、各フィールドで使用されている数式を追跡する必要はありません。
Excelを使用すると、計算フィールドの作成に使用されるすべての数式のリストをすばやく作成できます。
計算されたすべてのフィールド式のリストをすばやく取得する手順は次のとおりです。
- ピボットテーブルで任意のセルを選択します。
- [ピボットテーブルツール]-> [分析]-> [フィールド、アイテム、セット]-> [数式の一覧表示]に移動します。
リストの数式をクリックすると、Excelは、ピボットテーブルで使用したすべての計算フィールド/アイテムの詳細を含む新しいワークシートを自動的に挿入します。
これは、作業をクライアントに送信したり、チームと共有したりする必要がある場合に非常に便利なツールになります。