ピボットテーブル Excel で最も強力なツールの XNUMX つです。 それらを使用すると、マウスを数回クリックするだけで、大量のデータのさまざまな要約を分析および要約できます。 この記事では、ピボット テーブルについて知り、それらが何であるかを理解し、それらを作成およびカスタマイズする方法を学びます。
この記事は Excel 2010 を使用して書かれています。ピボットテーブルの概念は何年もの間あまり変わっていませんが、作成方法は Excel の新しいバージョンごとに少し異なります。 2010 以外のバージョンの Excel を使用している場合は、この記事のスクリーンショットが実際の画面と異なることに注意してください。
歴史のビット
表計算ソフトの黎明期、Lotus 1-2-3 ルール ボール。 ロータスの代替として独自のソフトウェア (Excel) を開発しようとする Microsoft の取り組みは、時間の無駄のように思えたほど、その優位性は完全なものでした。 2010年に早送りしましょう！ Excel は、その歴史の中で Lotus コードがこれまでに成し遂げたよりも多くのスプレッドシートを支配しており、Lotus をまだ使用している人の数はほぼゼロです。 これはどのように起こりますか？ このような劇的な変化の理由は何だったのでしょうか?
アナリストは、次の XNUMX つの主な要因を特定しています。
- まず、Lotus は、Windows と呼ばれるこの新しい GUI プラットフォームは一時的な流行にすぎず、長くは続かないと判断しました。 彼らは、Lotus 1-2-3 の Windows バージョンの構築を拒否し (ただし、ほんの数年間)、彼らのソフトウェアの DOS バージョンが、消費者が必要とするすべてのものになるだろうと予測しました。 Microsoft は当然、Windows 専用の Excel を開発しました。
- 1 つ目は、Microsoft が Excel に PivotTables というツールを導入したことです。これは Lotus 2-3-1 では利用できませんでした。 Excel 専用のピボットテーブルは非常に便利であることが判明したため、ピボットテーブルのない Lotus 2-3-XNUMX を使い続けるよりも、新しい Excel ソフトウェア スイートを使い続ける傾向がありました。
ピボットテーブルは、一般的に Windows の成功を過小評価するとともに、Lotus 1-2-3 の死の行進を演じ、Microsoft Excel の成功の先駆けとなりました。
ピボット テーブルとは
では、ピボットテーブルとは何かを特徴付ける最良の方法は何でしょうか?
簡単に言えば、ピボット テーブルはデータの要約であり、このデータの分析を容易にするために作成されます。 手動で作成された合計とは異なり、Excel のピボットテーブルはインタラクティブです。 作成後、期待した画像が得られない場合は、簡単に変更できます。 マウスを数回クリックするだけで、列見出しが行見出しに、またはその逆になるように合計を反転できます。 ピボット テーブルを使用すると、さまざまなことができます。 ピボット テーブルのすべての機能を言葉で説明しようとする代わりに、実際にそれを示す方が簡単です...
ピボットテーブルで分析するデータをランダムにすることはできません。 ある種のリストのような生の生データでなければなりません。 たとえば、過去 XNUMX か月間に会社が行った売上のリストなどです。
次の図に示すデータを見てください。
すでに要約されているため、これは生の生データではないことに注意してください。 セル B3 には $30000 が表示されていますが、これはおそらく James Cook が 30000 月に行った合計結果です。 では、元のデータはどこにあるのでしょうか? XNUMXドルという数字はどこから来たのですか？ この月間合計の元となった元の売上リストはどこにありますか? 誰かが過去 XNUMX か月間のすべての売上データを整理して並べ替え、合計の表に変換するという素晴らしい仕事をしたことは明らかです。 どのくらい時間がかかったと思いますか？ 時間？ XNUMX時？
実は上の表はピボットテーブルではありません。 他の場所に保存されている生データから手作業で作成されたもので、処理に少なくとも数時間かかりました。 このような要約テーブルは、ピボット テーブルを使用してわずか数秒で作成できます。 方法を考えてみましょう…
元の販売リストに戻ると、次のようになります。
驚くかもしれませんが、ピボット テーブルを使用してこの取引リストからわずか数秒で、上記で分析した Excel の月次売上レポートを作成できます。 はい、それ以上のことができます！
ピボットテーブルを作成するには?
まず、Excel シートにソース データがあることを確認します。 金融取引のリストは、発生する最も典型的なものです。 実際には、従業員の連絡先の詳細、CD コレクション、または会社の燃料消費データなど、何でもリストにすることができます。
そこで、Excel を起動し…そしてそのようなリストをロードします…
このリストを Excel で開いたら、ピボット テーブルの作成を開始できます。
このリストから任意のセルを選択します:
次にタブで 挿入 (挿入) select コマンド ピボットテーブル （ピボットテーブル）：
ダイアログボックスが表示されます ピボットテーブルを作成する (ピボット テーブルの作成) XNUMX つの質問があります。
- 新しいピボット テーブルを作成するために使用するデータは?
- ピボットテーブルはどこに置く？
前のステップですでにリスト セルの XNUMX つを選択しているため、リスト全体が自動的に選択されてピボット テーブルが作成されます。 別の範囲、別のテーブル、さらには Access や MS-SQL データベース テーブルなどの外部データ ソースを選択できることに注意してください。 さらに、新しいピボット テーブルを配置する場所を選択する必要があります。新しいシートまたは既存のシートの XNUMX つです。 この例では、オプションを選択します – 新しいワークシート (新しいシートへ):
Excel は新しいシートを作成し、空のピボット テーブルを配置します。
ピボット テーブルの任意のセルをクリックするとすぐに、別のダイアログ ボックスが表示されます。 ピボットテーブルフィールドリスト (ピボット テーブル フィールド)。
ダイアログ ボックスの上部にあるフィールドのリストは、元のリストのすべてのタイトルのリストです。 画面の下部にある XNUMX つの空の領域を使用して、データを集計する方法をピボットテーブルに伝えることができます。 これらの領域が空である限り、テーブルにも何もありません。 上部の領域から下の空の領域に見出しをドラッグするだけです。 同時に、指示に従ってピボット テーブルが自動的に生成されます。 間違えた場合は、下部の領域から見出しを削除するか、他の見出しをドラッグして置き換えることができます。
エリア 価値観 (意味) はおそらく XNUMX つの中で最も重要です。 この領域に配置される見出しは、要約されるデータ (合計、平均、最大、最小など) を決定します。これらはほとんどの場合、数値です。 この地域の場所の優れた候補は、見出しの下のデータです 単位 （費用）弊社オリジナルテーブル。 このタイトルを領域にドラッグします 価値観 (値):
タイトルですのでご了承ください 単位 にチェックマークが付き、エリア内に 価値観 (値) エントリが表示されました 金額の合計 (Amount フィールド Amount)、列が 単位 要約する。
ピボット テーブル自体を見ると、列のすべての値の合計が表示されます。 単位 元のテーブル。
これで、最初のピボット テーブルが作成されました。 便利ですが、特に印象的ではありません。 おそらく、現在持っているデータよりも多くの情報を取得したいと考えています。
元のデータに戻り、この合計を分割するために使用できる XNUMX つ以上の列を特定してみましょう。 たとえば、販売者ごとに合計売上高が個別に計算されるように、ピボット テーブルを作成できます。 それらの。 会社の各営業担当者の名前と合計売上高を含む行がピボット テーブルに追加されます。 この結果を得るには、タイトルをドラッグするだけです 販売員 （営業担当）地域へ 行ラベル (ストリングス):
もっと面白くなる！ ピボットテーブルが形になり始めています…
利点を参照してください。 数回クリックするだけで、手動で作成すると非常に時間がかかるテーブルを作成できました。
他に何ができますか？ ある意味では、ピボット テーブルの準備はできています。 元のデータの有用な要約を作成しました。 重要な情報はすでに受け取りました！ この記事の残りの部分では、より複雑なピボットテーブルを作成する方法と、それらをカスタマイズする方法について説明します。
ピボットテーブルのセットアップ
まず、XNUMX 次元のピボット テーブルを作成します。 列見出しを使用してこれを行いましょう 支払方法 （支払方法）。 タイトルをドラッグするだけ 支払方法 エリアへ 列ラベル (列):
結果は次のとおりです。
とてもかっこよく見えます！
それでは、XNUMX 次元のテーブルを作成してみましょう。 そのようなテーブルはどのように見えるでしょうか？ どれどれ…
ヘッダーをドラッグ パッケージ （コンプレックス）エリアへ レポート フィルタ (フィルター):
彼がどこにいるか注意してください…
これにより、「支払われた休暇施設」に基づいてレポートをフィルタリングすることができます。 たとえば、すべてのコンプレックスの販売者別および支払い方法別の内訳を確認したり、マウスを数回クリックするだけで、ピボット テーブルのビューを変更して、コンプレックスを注文した人についてのみ同じ内訳を表示したりできます。 サンシーカー.
したがって、これを正しく理解すれば、ピボット テーブルは XNUMX 次元と呼ぶことができます。 設定を続けましょう…
小切手とクレジット カードによる支払い (つまり、キャッシュレス支払い) のみをピボット テーブルに表示する必要があることが突然判明した場合は、タイトルの表示をオフにすることができます。 現金 （現金）。 このために、次の 列ラベル 下向き矢印をクリックし、ドロップダウン メニューのチェックボックスをオフにします 現金:
ピボット テーブルがどのように表示されるか見てみましょう。 コラムはご覧の通り 現金 彼女から消えた。
Excel でのピボットテーブルの書式設定
ピボットテーブルは非常に強力なツールであることは明らかですが、これまでのところ、結果は少し単純で退屈に見えます。 たとえば、合計した数字は金額のようには見えません。単なる数字です。 これを修正しましょう。
このような状況で慣れ親しんだ方法でテーブル全体 (またはシート全体) を選択し、ツールバーの標準の数値書式設定ボタンを使用して目的の書式を設定するのは魅力的です。 このアプローチの問題点は、将来ピボット テーブルの構造を変更した場合 (99% の確率で発生します)、書式設定が失われることです。 必要なのは、それを (ほぼ) 永続的にする方法です。
まず、エントリを見つけましょう 金額の合計 in 価値観 (値) をクリックします。 表示されるメニューで、項目を選択します 値フィールド設定 (値フィールドのオプション):
ダイアログボックスが表示されます 値フィールド設定 (値フィールドのオプション)。
プレス 数の書式 (数値形式)、ダイアログ ボックスが開きます。 セルの書式設定 (セル形式):
リストから カテゴリー (数値形式) 選択 会計 (Financial) を選択し、小数点以下の桁数を XNUMX に設定します。 ここで数回押します OKピボット テーブルに戻ります。
ご覧のとおり、数値は金額としてフォーマットされています。
書式設定を行っている間に、ピボットテーブル全体の書式を設定しましょう。 これを行うにはいくつかの方法があります。 よりシンプルなものを使用します…
クリック ピボットテーブル ツール: デザイン (ピボットテーブルの操作: コンストラクター):
次に、セクションの右下隅にある矢印をクリックしてメニューを展開します。 ピボットテーブルスタイル (ピボットテーブル スタイル) インライン スタイルの広範なコレクションを表示します。
適切なスタイルを選択し、ピボット テーブルで結果を確認します。
Excel のその他のピボットテーブル設定
日付でデータをフィルタリングする必要がある場合があります。 たとえば、取引のリストには非常に多くの日付があります。 Excel には、データを日、月、年などでグループ化するためのツールが用意されています。 それがどのように行われるか見てみましょう。
まずエントリを削除します。 支払方法 地域から 列ラベル (列)。 これを行うには、タイトルのリストにドラッグして戻し、その場所にタイトルを移動します 予約日 (予約日):
ご覧のとおり、これにより一時的にピボット テーブルが役に立たなくなりました。 Excel は、取引が行われた日付ごとに個別の列を作成しました。 その結果、非常に広いテーブルができました！
これを修正するには、任意の日付を右クリックして、コンテキスト メニューから選択します。 GROUP （グループ）：
グループ化ダイアログボックスが表示されます。 我々が選択しました ヶ月 (月)をクリックして OK:
出来上がり！ この表はもっと便利です:
ちなみに、この表は、記事の冒頭で示した売上合計を手作業で集計した表とほぼ同じです。
知っておくべき重要なポイントがもう XNUMX つあります。 XNUMX つではなく、複数のレベルの行 (または列) 見出しを作成できます。
…そして、それはこのようになります…
列見出し (またはフィルター) でも同じことができます。
テーブルの元の形式に戻り、合計ではなく平均を表示する方法を見てみましょう。
開始するには、 金額の合計 表示されるメニューから選択します 値フィールド設定 (値フィールドのオプション):
リスト 値フィールドを次のように要約します （操作）ダイアログボックス 値フィールド設定 (値フィールドのオプション) 選択 平均 （平均）：
同時にここにいるうちに変えよう カスタム名 （カスタムネーム）付き 金額の平均 （AmountフィールドAmount）より短いものに。 このフィールドに次のように入力します 平均:
イベント OK そして何が起こるか見てください。 すべての値が合計から平均に変更され、テーブル ヘッダー (左上のセル) が 平均:
必要に応じて、XNUMX つのピボット テーブルに配置された金額、平均、数 (売上) をすぐに取得できます。
空のピボット テーブルから始めて、これを行う方法のステップ バイ ステップ ガイドを次に示します。
- ヘッダーをドラッグ 販売員 （営業担当）地域へ 列ラベル （列）。
- タイトルをXNUMX回ドラッグ 単位 (コスト) までのエリア 価値観 (値)。
- 最初のフィールドの場合 単位 にタイトルを変更 トータル (金額)、このフィールドの数値形式は 会計 （金融）。 小数点以下の桁数はゼロです。
- XNUMX 番目のフィールド 単位 名 アベラグe、その操作を設定します 平均 (平均) となり、このフィールドの数値形式も 会計 (金融) 小数点以下 XNUMX 桁。
- XNUMX 番目のフィールドの場合 単位 タイトルを設定する ワンランク上の そして彼のための手術 - ワンランク上の （量）
- 列ラベル (列) フィールドが自動的に作成されました Σ値 (Σ値) – 領域にドラッグします 行ラベル (行)
最終的には次のようになります。
合計金額、平均値、販売数 – すべてが XNUMX つのピボット テーブルに!
まとめ
Microsoft Excel のピボット テーブルには、多くの機能と設定が含まれています。 このような小さな記事では、すべてをカバーすることさえできません。 ピボット テーブルのすべての可能性を完全に説明するには、小さな本か大きな Web サイトが必要です。 大胆で好奇心旺盛な読者は、ピボット テーブルの探索を続けることができます。 これを行うには、ピボット テーブルのほぼすべての要素を右クリックして、どの機能と設定が開くかを確認します。 リボンには、次の XNUMX つのタブがあります。 ピボットテーブル ツール: オプション （分析）と 設計 （コンストラクタ）。 いつでもピボットテーブルを削除して最初からやり直すことができます。 DOS と Lotus 1-2-3 の長年のユーザーが経験したことのない機会が得られます。