【Excel】ピポットテーブルの攻略

ピポットテーブルは、データの分析や集計、グラフ作成等、様々な分野で役立つ機能です。 意外に使用方法は簡単です。これを機に使えるようになりましょう。 この記事ではピボットテーブルの基本的な使い方から、具体的な活用例、知っておくと得するテクニックを紹介します。

ピポットテーブルとは？

ピポットテーブルの前提知識としては、【表形式のデータ】を【集計表】に作成する機能の事です。
膨大なデータを目でおって、そのデータや傾向を知るのは大変難しく感じます。ピポットテーブルを活用すれば、複雑な数式や関数を挿入しなくても、集計表にまとめることが出来ます。

データソースを準備　ステップ①

①表の中のどこかをアクティブにします。
②【挿入】タブをクリックします。
③【テーブル】をクリックします。

すると【テーブルの作成】ダイアルボックスが出てきます。
④【先頭行をテーブルの見出しとして使用する】にチェックを入れます。
【OK】ボタンを押します。

するとこの様に、【表】が【テーブル】になります。

テーブルに名前を入れる

①テーブルの中のセルをアクティブセルにします。
②【テーブルデザイン】タブをクリックします。
③【テーブル名】が【テーブル1】となってます。

【テーブル名】に【支店別売上表】と入力してキーボードの【Enter】キーを押します。

⑤【名前ボックス▼】をクリックすると【顧客別売上表】と出てきます。この【顧客別売上表】をクリックすると

データが一括で選択出来ます。

元データの5つの注意点

【ルール①】各データの見出しが1行である事
【ルール②】それぞれの見出しに名前が全て入力されていること
【ルール③】表の中に結合されたセルがないこと
【ルール④】表の中に空白行がないこと（行全体が空白）
【ルール⑤】ピポットテーブルは列単位の集計になる為、列単位でデータの種類を統一しておくこと

ピポットテーブルで集計表作成

今回は、顧客別売上表の【テーブル】を用いて、商品別の売上金額の合計を算出していきます。

①表の中のどこかのセルをアクティブセルにします。
②【挿入】タブをクリックします。
③【ピポットテーブル】をクリックします。

すると【テーブルまたは範囲からのピポットテーブル】ダイアルボックスが出てきます。
【テーブル名】を確認して、今回は新規のワークシートにピポットテーブルを作成します。
この状態で【OK】ボタンを押します。

ピポットテーブルのフィールド

すると、新規に上記の様なワークシートが現れます。

【エリアセクション】は
フィルタボックス
列ボックス
行ボックス
値ボックス
の4つで構成されてます。

ウィンドウの基本操作は、フィールドセクションにドラック＆ドロップで該当のボックスにおいていきます。

すると、結果がシートに反映されます。金額の合計値になります。

顧客別売上表の金額の合計値が全て集計されます。

商品名を行ボックスにドラック＆ドロップします。

するとこの様に、商品名別売上合計表が算出されます。行に配置すると、フィールドのボックスは縦に並びます。

商品名をExcelシート上にドラックします。

フィールドが削除されます。

ピポットテーブルの書式設定

①シートのデータのどこかをアクティブセルにします。
②【デザイン】タブをクリックします。
③【デザイン】を選択します。

するとフィールドの書式のデザインが変更されます。

①シートのデータのどこかをアクティブセルにします。
②【デザイン】タブをクリックします。
③【縞模様行】を選択します。

すると、行がこの様に縞模様になります。

①シートのデータのどこかをアクティブセルにします。
②【デザイン】タブをクリックします。
③【縞模様列】を選択します。

すると、列がこの様に縞模様になります。

集計表の金額に【コンマ】を入れたい場合

①ピポットテーブルのフィールドの【合計/金額▼】をクリックします。
②【値フィールドの設定】をクリックします。

【値フィールドの設定】ダイアルボックスが出てきます。
③【表示形式】をクリックします。

④【数値】をクリックします。
⑤【桁区切り（,）を使用する】にチェックを入れます。
【OK】ボタンをクリックします。

すると、集計表の金額に【コンマ】がつきます。

①集計表のどこかのセルを右クリックします。
②【値フィールドの設定】をクリックします。

すると、同じように【値フィールドの設定】ダイアルボックスが表示されます。

並び替えの方法

①【プルダウン】をクリックします。

②【降順】をクリックします。

すると降順で並び替えることが出来ます。

小計を表示しない

【エリアセクション】の【行】に顧客名をドラック＆ドロップします。

すると、集計行に【顧客名】が集計されます。

①集計表の中のセルをアクティブセルにします。
②【デザイン】タブをクリックします。
③【小計】をクリックします。
④【小計を表示しない】をクリックします。

小計が非表示になります。（ピポットテーブルの場合、デフォルトは小計・総計と表示されます。）

データソースの追加

ピポットテーブルとデータソースの関係性で、常時、ピポットテーブルとデータソースは連携してません。
元データに新しいデータを追加したら、ピポットテーブルソースを更新する必要が有ります。

ピポットテーブルのデータの総計は、上記の様になっています。

データソースを追加で入力します。

【ピポットテーブル分析】タブから【更新】をクリックして【更新】を押します。

総計が更新されます。

フィルターの追加

【フィルター】ボックスに【フィールドセクション】の【年】をドラック＆ドロップします。

するとピポットテーブル集計表がこの様になります。

①プルダウンリストをクリックします。
②【2024年】を選択し、【OK】ボタンを押します。

すると、2024年のピポットテーブル集計表が完成します。

数値のグループ化

現在、このピポットテーブルは、【行】ボックスに【金額】、【値フィールド】に【合計/金額】が配置されています。

【値フィールド】の【合計/金額】をクリックすると、【値フィードの設定】が表示されますので、クリックして選択します。

すると、【値フィールド設定】ダイアルボックスが表示されますので、【集計方法】を【個数】にして、【OK】ボタンを押します。

すると、【個数】が表示されます。

行ラベルは様々なデータがありますが、今回、5分割に分解して、傾向を見てみます。

①【行ラベル】をクリックします。
②【ピポットテーブルの分析】タブから【フィールドのグループ化】をクリックして選択します。

すると【グループ化】ダイアルボックスが表示されます。現在、【先頭の値】と【末尾の値】は、データの最小値と最大値が表示されています。

今回は、先頭の値を0、末尾の値を500,000と入力します。そして【単位】の所は、100,000にします。

すると、10万単位で5分割にする事が出来ます。10分割にしたい時は、単位の所を50,000にすると、10分割することが出来ます。このデータを見ると、10,0000以下のデータは、496ありますので、半分位を占めています。

参考に比率を見て行きます。

表内を【右クリック】して【値フィールドの設定】をクリックします。

すると【値フィールドの設定】ダイアルボックスが表示されるので【計算の種類】タブに行き、【総計に対する比率】を選択して【OK】ボタンを押します。

すると、【金額】の比率が表示されます。

テキストのグループ化

ここでは、テキストのグループ化について説明します。

【行ラベル】は、【ピポットテーブルのフィールド】作業ウインドゥの【行ボックス】に【商品名】をドラック＆ドロップした集計行があります。

ここはで、PC関係商品を、【PC関連】というグループにします。

まず、【行ラベル】からPC周辺にあたるものを選択します。※キーボードの【Ctrl】キーを押しながら選択すると、個別で選択する事が出来ます。
【ピポットテーブル分析】タブから【グループの選択】をクリックします。

すると、先ほど選択した、【PC】関連が【グループ1】に集約します。

【グループ1】のセルをクリックして、【数式】バーに【PC関連】と入力して、キーボードの【Enter】キーを押します。

すると、この様に、【PC関連】というカタチでグループ化することが出来ます。

この【商品2】はグループ化する事によって、【行ボックス】に追加されたモノです。ですので、【商品】を外して、【商品2】だけを残してみます。

グループ化した、【PC周辺】がピポットテーブルに表示されています。これが、テキストのグループ化になります。

日付のグループ化

ここでは、日付のグループ化の操作方法を学びます。

表をテーブル化

【データ】の任意のセルをクリックして、キーボードの【Ctrl】+【T】で表をテーブル化にします。

表内をクリックして、【挿入】タブから、【ピポットテーブル】をクリックします。

すると、【テーブルまたは範囲からのピポットテーブル】ダイアルボックスが表示されます。【既存のワークシート】にチェックを入れ【E1】セルをクリックして【OK】ボタンを押します。

すると、空のピポットテーブルが表示されます。

すると、【ピポットテーブル作業ウインドゥ】が表示されるので、【顧客名】を【行ボックス】にドラック＆ドロップ、【年】を【列】ボックスにドラック＆ドロップします。又、【顧客名】を【値ボックス】にドラック＆ドロップします。

すると、年度別の顧客別の集計表が表示されます。

【ピポットテーブル分析】タブから【グループの解除】をクリックして選択します。

すると、日付別の集計表が表示されます。

ピポットテーブルの集計フィールドとは

元データに存在しない項目を新しい数式で定義し、ピボットテーブル内で分析できる機能です。消費税込みの売上合計など、元の表のフィールドを組み合わせた計算結果を表示させたい場合に便利で、元データにはない独自の集計値をピボットテーブルに直接追加できます。

それでは、ピポットテーブルの集計フィードを作成して、ピポットテーブルの集計単価を作成します。

この様なカタチのピポットテーブル表があります。

①ピポットテーブル表の何処かをクリックします。
②【ピポットテーブル分析】タブをクリックします。
③【ピポットテーブルアイテムセット】から【集計フィード】をクリックします。

【集計フィードの挿入】ダイアルボックスが表示されます。
【名前】の箇所に【平均単価】と入力します。

【数式】の0を消して、【フィールド】から【金額】をクリックして、【フィールドの挿入】をクリックします。

すると【数式】に【金額】が表示されます。

【数式】に【/】を入力した、【フィールド】から【発注数】をクリックして、【フィールドの挿入】をクリックします。

すると、数式に【金額/発注数】と表示され、【OK】ボタンを押します。

ピポットテーブルに【平均単価】が追加された事が分かります。全体の平均単価は【￥13,541】になっていることが分かります。

ピポットテーブルの集計フィールドの変更・削除

【合計】の箇所を右クリックします。すると【合計/平均単価の削除】が表示されるので、クリックすると、
表内から、平均単価が削除されます。

ピポットテーブルの集計フィールドに関数を使用する

ピポットテーブル集計フィードには、集計の際、関数を使用する事が出来ます。今回はピポットテーブルに送料の関数を使用します。

【F2】セルに【100000】と入力し、

上記の様に設定します。

すると、100000万なら、送料が750円、80000万なら送料が、350円と表示されます。
上記の式を応用します。

①ピポットテーブル内の任意のセルをクリック
②【ピポットテーブル分析】タブから
③【フィードアイテムセット】の【集計フィード】を選択します。

すると、【集計フィード挿入】ダイアルボックスが表示されます。
【名前】には、【送料】と入力します。

次に、【売上が10以上であれば】という条件ですので【IF関数】を使用します。

上記の関数を使用して、【OK】ボタンを押します。

すると、【送料】がこの様に、表示されます。

【ピポットテーブルのフィールド】作業ウインドゥにも【送料】が追加されている事が分かります。

数式の確認

①表の中の任意のセルをクリックします。
②【ピポットテーブル分析】タブをクリックします。
③【フィールドアイテムセット▼】から【数式の一覧表】をクリックして選択します。

すると、新しく、シートが作成され、集計フィードの計算が表示されます。

ピポットテーブルでスライサー

①ピポットテーブル集計表の何処かをアクティブセルにします。
②【ピポットテーブル分析】タブをクリックします。
③【スライサーの挿入】をクリックします。

すると、【スライサーの挿入】ダイアルボックスが表示されます。

【日付】【顧客名】【商品名】にチェックを入れ、【OK】ボタンを押します。

するとこの様なスライサーが出てきます。

【Ctrl】キーを押しながらクリックすると、上記の様に複数選択できます。

【フィルターのクリア】を押すと解除されます。

【Del】ボタンを押すと、スライサーの画面が消えます。

タイムラインの挿入

ピボットテーブルのタイムライン機能とは、日付データを扱うピボットテーブルやピボットグラフにおいて、時系列の期間を指定してデータを絞り込むための視覚的なツールです。

①ピポットテーブル集計表の何処かをアクティブセルにします。
②【ピポットテーブル分析】タブをクリックします。
③【タイムラインの挿入】をクリックします。

上記は、データの可視化の記事です。併せて読むと格段にピポットテーブルのテクニックがUPします。

ピボットテーブルはどんな時に使う？

ピボットテーブルはどんな時に使う？ ピボットテーブルは、売上管理・売上解析、データをグラフ化したいときなどに便利です。

ピボットのメリット・デメリットは？

ピボットを行うことで、新しく路線変更などを行うことが出来るため新規顧客の獲得や販路拡大などのメリットがあります。 しかし何度もピボットを行ってしまうと、顧客や投資家からは信念や粘り強さがないといった判断を下されてしまう可能性もあります。

Excelのテーブルとピボットテーブルの違いは何ですか？

テーブルがデータ管理と簡易な集計に向いているのに対し、ピボットテーブルは複雑なクロス集計や詳細なデータ分析に特化しています。

業務効率化をもっと深く

ここまでお読みいただき、ありがとうございます！ピポットテーブルを使用すれば、あなたの業務はもっと効率的になります。
ただ、記事を読んだだけでは、 「自分の業務にどう活かせばいいか分からない…」 「エラーが出たときに、一人で解決できるか不安…」 と感じる方もいらっしゃるかもしれません。
もしあなたが、最短・最速でExcelスキルを習得し、周りから「仕事ができる人」と評価されたいなら、プロから直接学ぶのが一番の近道です。