私の最高の Excel ピボットテーブルは次の 4 つの関数から始まります

Posted on 2時間前

ピボットテーブルは、数千行を Excel で有用な概要に変換するのに最適ですが、その有効性は、与えられたデータによって決まります。そのため、ビルドする前に、4 つの簡単な関数を使用してソースデータを準備するために少し時間を取ります。

以下の例では、売上テーブル (tbl_売上) OrderID、Date、ProductID、Customer、Location、Amount に加えて、別の製品テーブル (tbl_製品) ProductID、ProductName、Category を含みます。次の 4 つの関数をデータセットに適用すると、ピボットテーブルを構築するためのより便利なソースが得られます。

XLOOKUP: データを充実させる

情報をひとつにまとめる

私の tbl_売上 テーブルには ProductID が含まれていますが、販売しているものを分析する場合には特に役に立ちません。各トランザクションと一緒に実際の製品名とカテゴリが必要です。したがって、ピボットテーブルを作成する前に、ProductName と Category という 2 つの新しい列を sales テーブルに追加します。その後、XLOOKUP を使用して関連情報を取得できます。 tbl_製品 それらの列に。

ProductName 列には次のものを使用します。

=XLOOKUP((@ProductID), tbl_Products(ProductID), tbl_Products(ProductName))

カテゴリ列の場合は次のようになります。

=XLOOKUP((@ProductID), tbl_Products(ProductID), tbl_Products(Category))

この段階では、ピボットテーブルを作成する際の柔軟性を高めるために、できる限り多くの有用で関連性のある情報をピボットテーブルに提供しようとしています。 ProductName と Category は、行、列、およびフィルター領域で自由に使用できるフィールドになりました。そのため、暗号的な ID を中心にピボットテーブルを構築する代わりに、販売している実際の製品ごとに売上を分類したり、カテゴリごとにグループ化したりできるようになりました。

IF: データを分類する

カテゴリの作成

数値によって注文の価値がわかりますが、場合によっては、それらの数値を分析しやすいテキストカテゴリに変換したいことがあります。たとえば、1,000 ドル以上の注文が高価値で、それ以外はすべて標準であると判断した場合、新しい OrderType 列で次の数式を使用します。

=IF((@Amount)>=1000, "High Value", "Standard")

ピボットテーブル自体を構築すると、成果が得られます。何千もの個別の金額を理解するように要求する代わりに、OrderType を行またはフィルター領域にドロップするだけで、高額注文と標準注文を即座に比較できます。

3 つ以上のカテゴリが必要な場合は、IFS または別の論理関数を使用して同じアイデアを拡張できます。

TEXTSPLIT: データを構造化する

結合した情報を個別のフィールドに変換する

[現在地]列には別の問題が存在します。各セルには、都市、州、地域の 3 つの情報が含まれています。この列をそのままにすると、ピボットテーブルは「シカゴ | イリノイ | 中西部」を 1 つのフィールド値として扱います。テキストには、個別に分析したい 3 つの個別の情報が含まれていることを知りません。ここでTEXTSPLITが役立ちます。

重要な注意点の 1 つは、TEXTSPLIT は動的配列関数であるため、Excel テーブル内に溢れないことです。このため、結果がこぼれる可能性があるテーブルの外側に別のヘルパー領域を作成し、結果をコピーして、テーブル内の 3 つの新しい列に値として貼り付けます。

最初の販売記録の横に次の式を入力し、それを下にドラッグして各場所を 3 つの部分に分割します。

=TEXTSPLIT(tbl_Sales(@Location)," | ")

はい、テーブルに 3 つの新しい列を追加すると、ソースデータの見た目が整然としなくなる可能性があります。しかし、それは問題ではありません。なぜなら、私は実際にそれをはるかに便利にしたからです。ピボットテーブルで都市、州、地域ごとの売上を表示できるようになりました。また、地域 > 州 > 都市の階層を作成し、これらのフィールドのいずれかをフィルターとして使用できるようになりました。

TRIM: データをクリーンアップします

結果を台無しにする可能性のあるスペースを削除します

特に、不規則な間隔を持つ可能性のある別のアプリからインポートされたデータを扱う場合、私が行う最後のステップは、データをクリーンアップすることです。[顧客]列の一部の名前には先頭と末尾に余分なスペースが含まれており、単語の間にも二重スペースがいくつかあります。これをそのままにしておくと、ピボットテーブルは、見た目は同じでも間隔が異なるテキスト値を別個の項目として扱うことができます。

元のデータをすぐに変更するのではなく、CustomerClean という名前の一時列を作成し、次のように入力します。

=TRIM((@Customer))

一時列を削除する前に、クリーンアップされた名前をコピーし、値として元の Customer 列に貼り付けたことがわかります。以前に City、State、Region 列を作成したとき、これらはテーブルにさらに具体性を追加するためにありました。ただし、今回は CustomerClean 列によって既存の Customer フィールドが修正されるため、ピボットテーブル内の潜在的なエラーの原因を取り除くために元の値を置き換えました。

これにより、問題の原因となる可能性のあるスペースがすべて削除されるため、ピボットテーブルで類似の顧客名をグループ化できるようになります。ピボットテーブルはテキストをグループ化するのに完全に機能しますが、テキストを修復したい場所ではありません。 TRIM は、ピボットテーブルが値を確実にグループ化して集計できるように値を準備します。

TRIM は、Web サイトからコピーされたデータに現れる可能性のある非改行スペースを削除しません。これらには、CHAR(160) を使用した SUBSTITUTE など、別のアプローチが必要です。

ピボットテーブルがついに機能するようになりました

有用なフィールドが増え、潜在的な問題が減少

ソースデータの準備が完了したので、いよいよピボットテーブルを構築できます。違いは、作業に役立つフィールドが増えたことと、基礎となるデータの潜在的な問題が減少したことです。

「行」領域に「カテゴリ」を入力し、「値」領域に「金額」を入力して、どのタイプの製品が最も多くの売上を生み出しているかを確認できます。地域、州、市を追加して結果を地理的に分類したり、OrderType をフィルターとして使用したり、ProductName または Customer を使用して結果をさらに分類したりできます。

ピボットテーブルは最後に来ます

この時点で、ワークフローは次のようになります。

未加工データ > 強化 > 分類 > 構造 > クリーン > ピボットテーブル

すべてのデータセットに 4 つの関数すべてが必要なわけではありませんし、常にこの正確な順序で使用するとは限りません。重要なのは、ピボットテーブルの作成を開始する前にデータを確認し、データをより便利にするために何が必要かを考えることです。最初にデータの準備に数分を費やすと、最適なピボットテーブルを構築するのがはるかに簡単になることがわかりました。