Search

» » 【図解】ピボットテーブルの使い方 基本から応用テクニックまで ... - ツギノジダイ

【図解】ピボットテーブルの使い方 基本から応用テクニックまで ... - ツギノジダイ

　ピボットテーブルとは、Excelに搭載されたデータ集計・分析ツールの一つです。ピボット（pivot）とは「回転軸」、テーブル（table）とは「表」を表す英単語で、大量のデータから自分が見たい項目を軸に、集計表を作成することができる機能です。

　特に、二つの項目を同時に集計できる「クロス集計」を得意としています。例えば、POSレジで取得した日々の売上データから、月別×商品別の売上集計表を作成したいといった場合に便利です。

　ピボットテーブルならば複雑な関数を組み合わせる必要もなく、マウス操作だけで簡単に自分が見たい表をつくることができます。

大量のデータを一瞬で集計
大量のデータを一瞬で集計（筆者作成）

　ピボットテーブルを使うには、まず集計の元となるデータが表で用意されている必要があります。その表を元にピボットテーブルをシート上に挿入し、集計したい項目を表の形になるようにマウスでドラッグしていくことで、集計結果を求めていきます。

　ピボットテーブル機能を活用する際、最も大切なのがこの表の作成です。

　基本でありますが、表の間に空行や空列があると、表の範囲を正しく読み込めなかったり、読み込んだ先で正しい集計ができなかったりする事象が発生します。

　ピボットテーブルを利用するためには、縦横のデータがすべて埋まっている「きれいな表」を作成します。

　そして、集計の元となるデータは、三つのルールに則った表の形に整えることが必要です。

　ルールを順に確認していきましょう。

  1. 項目名を1行目に入力する
  2. 縦方向（列）には同じ種類のデータを入力する
  3. 1件のデータを1行に入力する

　ルール1は、「項目名を1行目に入力する」です。項目名はフィールド名と呼ばれることもあります。

　図1では、A列に「取引コード」、B列に「販売日」といった項目名が設定されており、その列に入力された値が何を示しているかがわかります。この項目名はピボットテーブルを作成する操作において重要な役割を担っているため、わかりやすい名前にすることがポイントです。

正しい形式の表
正しい形式の表（筆者作成）

　図1を見るとわかる通り、それぞれの列には同じ種類のデータが入力されています。ルール2では、この「縦方向（列）には同じ種類のデータを入力する」がポイントとなります。セルに入力できる値には、文字列と数値があり、数値の一種として日付がありますが、それらが一つの列のなかで混在してはいけません。

　また、図2にあるように、作成された表の周囲に余計なデータがあると正しく範囲を読み込めない場合があります。対象となる表に隣接するセルには、何も入力しないようにしましょう。

誤った形式の表
誤った形式の表（筆者作成）

　ルール3は、「1件のデータを横1行に入力する」です。例えば項目数の多い表の場合、印刷をしたときに横幅を1枚に収めるために1件のデータを2行に分けてつくることがありますが、それでは集計の元データとして使用できません。

　また、図2にあるように、別の行を補完する形で行を追加している場合にも、正しい結果を求めることができません。

　表が作成できたら、以下の手順でピボットテーブルを挿入します。

  1. 表内のセルを選択した状態にする
  2. 「挿入」タブ→「テーブル」グループ→「ピボットテーブル」ボタンをクリック
  3. 表示されたダイアログボックスで、以下を確認して「OK」ボタンをクリック
  • 「テーブル／範囲」として集計したい表の範囲が選択されている
  • ピボットテーブルの配置先に「新規ワークシート」が選択されている
ピボットテーブルの挿入
ピボットテーブルの挿入（筆者作成）

　そうすると新しいシートが挿入され、シート上に「ピボットテーブル」と表示された枠、画面右側には「ピボットテーブルのフィールド」作業ウィンドウが表示されます。作業ウィンドウには「フィールドリスト」と「ボックス」があります。

　また、リボンには「ピボットテーブルの分析」「デザイン」の二つのタブが表示されます。ピボットテーブルの枠以外のセルを選択すると、それらの作業ウィンドウやタブは非表示になります。

ピボットテーブル作成時の画面
ピボットテーブル作成時の画面（筆者作成）

　いよいよピボットテーブルを作成していきます。

　商品別売上集計をしたい場合を例に挙げて解説します。

  1. フィールドリストから「商品名」フィールドを「行」ボックスにドラッグ＆ドロップ
  2. 集計したい値である「計」フィールドを「値」ボックスにドラッグ＆ドロップ
  3. シート上のピボットテーブルの枠に、商品ごとの売上集計結果を求めた表が作成される
ピボットテーブルの作成
ピボットテーブルの作成（筆者作成）

　続いて、「担当者名」フィールドを「列」ボックスにドラッグ＆ドロップすると、クロス集計表が作成されます。

クロス集計表の作成
クロス集計表の作成（筆者作成）

　フィールドをドロップする順序には、特に決まりはありません。自分のわかりやすい手順でドロップしましょう。

　集計が不要になったフィールドは、ボックスの外へドラッグして削除することができます。フィールドを次々と入れ替えながら、多様な視点で素早く集計結果を確認できることがピボットテーブルの真骨頂です。

　また、複数の項目を一つのボックスに入れることもできます。

　例えば、行のボックスに「商品分類」「商品名」のフィールドを追加すると、各商品別の売り上げが、分類ごとの集計も維持しながら表示されます。

フィールドの追加・削除
フィールドの追加・削除（筆者作成）

　簡単に集計ができるピボットテーブルですが、実践的に活用するために、さらにいくつかの機能を知っておくと良いでしょう。

　グループ化とは、日付や時刻などのデータをまとめて表示できる機能です。日付のデータを持つフィールドを、行または列ボックスに追加すると、月ごとの集計に自動的にグループ化されます。

　グループ化されたラベル（リストのタイトル）には「＋」マークが付くため、クリックして必要に応じてその詳細を見ることもできます。

　日付や時刻だけではなく、四半期ごとや年ごとにグループ化することも可能です。

  1. 日付の値が表示されているフィールドを右クリック
  2. 「グループ化」メニューで表示されるダイアログボックスを使って、見たい単位を指定
グループ化の方法
グループ化の方法（筆者作成）

　ピボットテーブルでは、フィールドを入れ替えるたびに勝手に列幅が変わってしまい、困ることがあります。

　設定した列幅を固定して使用するには、以下の操作方法をおこないます。

  1. ピボットテーブル内を右クリック
  2. 表示されるメニューから「ピボットテーブルオプション」を選ぶ
  3. 表示されるダイアログボックスの「更新時に列幅を自動調整する」のチェックを外す
列幅の固定方法
列幅の固定方法（筆者作成）

　大量のデータを集計すると数値の桁数が多くなるため、桁区切りのカンマを表示させたり、桁数を千単位・百万単位の表示に変えたりするなど、表示形式を調整したい場面が出てきます。

　通常のExcelシートのようにセル範囲を選択して書式設定すると、列数や行数が変わったときに思わぬ結果になる場合もあります。

　その場合は、以下の操作で設定することをおすすめします。

  1. 値のあるセルを右クリック
  2. 表示されるメニューから「値フィールドの設定」を選択
  3. 表示されるダイアログボックスの「表示形式」のボタンから「セルの表示形式」ダイアログボックスを表示

　カンマを付けたいだけであれば、下記の操作となります。

  1. 「数値」を選択
  2. 「桁区切りを使用する」にチェックをつける

　千単位の表示に桁数を調整したいのであれば、下記の操作となります。

  1. 「ユーザー定義」を選択
  2. 種類に「#,##0,」と入力

　最後のカンマの数を増やせば、3桁ずつ表示桁数を詰めることができます。

カンマと表示桁数の設定方法
カンマと表示桁数の設定方法（筆者作成）

　ピボットテーブルに慣れないうちは、どんな軸で集計したら良いか迷うこともあるかもしれません。

　そんなときに頼りになるのが「おすすめピボットテーブル」です。

　「ピボットテーブル」タブ→「ツール」グループ→「おすすめピボットテーブル」ボタンから起動できます。

　開いた画面には、縦にさまざまな軸で集計した結果がサムネイルで並んでいますが、気になるものを選ぶと右側の画面にプレビュー表示がされます。そのまま自由にこれだと思うものを選択して「OK」ボタンで確定しましょう。

おすすめピボットテーブルの操作方法
おすすめピボットテーブルの操作方法（筆者作成）

　さらに便利にピボットテーブルを活用するための、とっておきの応用テクニックをご紹介します。

　値の大きい順や小さい順に並べ替えたい場合には、以下の操作方法となります。

  1. 該当列の値のセルを選択
  2. 右クリックから「並べ替え」を選択
  3. 「昇順」「降順」とそれぞれの並べ替えが可能
並び替えの操作方法
並び替えの操作方法（筆者作成）

　列や行を任意の順に並べ替えたいときは、ラベル部分を選択してからドラッグすると、行や列の順序を入れ替えることができます。

　ドラッグを開始するときは、ポインタの形に注意しましょう。

列・行を任意の順に並べ替える方法
列・行を任意の順に並べ替える方法（筆者作成）

　スライサーを活用すると、他のフィールドを使って今あるピボットテーブルをさらに細かく分析することができるようになります。

　以下の操作方法で、シート上にスライサーが表示できます。

  1. 「ピボットテーブル分析」タブ→「フィルター」グループ→「スライサーの挿入」ボタンをクリック
  2. 表示される画面から、分析したいフィールドにチェックを入れて「OK」

　この機能により、分析したいフィールドをクリックするだけで、結果が絞り込まれた形に変わります。

　複数選択をオンにすることで、複数のフィールドを選択できます。

スライサー機能の操作方法
スライサー機能の操作方法（筆者作成）

　ピボットテーブルをそのままグラフ化したものが、ピボットグラフです。視覚化されるため、表よりもデータの推移や大小の比較などが実施しやすくなります。

　以下の操作方法だけで、簡単にピボットグラフを作成することができます。

  1. 「ピボットテーブル分析」タブ→「ツール」グループ→「ピボットグラフ」ボタンで進む
  2. 表示される画面内で、データの内容に適した種類のグラフを選ぶ

　元となるピボットテーブルのフィールドを変更すれば、グラフも自動的に再描画されます。

ピボットグラフの挿入方法
ピボットグラフの挿入方法（筆者作成）

　レコードとは、元の表で1行1件で入力されているそれぞれのデータのことです。フィルターとはそのなかから一定の条件を指定して、自分が見たいレコードだけを抽出することです。

　Excelにはさまざまなフィルターの方法がありますが、ピボットテーブルではテーブル内の値をダブルクリックすることで、元のテーブルから該当のレコードだけを抽出した新しいシートを作成できます。

　集計結果を見ながら「この売上だけ妙に多いのはなぜだろう？」といった疑問を抱いた場合に、根拠となるデータを簡単に一覧できるようになります。

対象レコードの表示方法
対象レコードの表示方法（筆者作成）

　大量のデータを集計するのに大変便利なピボットテーブルですが、使うときにはいくつかの注意点があります。

　ピボットテーブルの集計結果が表示されているセルを選択して数式バーを見ると、表示されている値がそのまま入力されていることがわかります。

　集計結果は関数などの数式で求められてはいないため、元となるデータが変更されても、その結果が即反映されることはありません。

　元データの変更をすぐに反映したい場合には、「ピボットテーブル分析」タブ→「データ」グループ→「更新」ボタンをクリックして変更を反映させましょう。

変更があった元データの更新方法
変更があった元データの更新方法（筆者作成）

　アクティブセルがピボットテーブル内にあるのに、ピボットテーブルのフィールド作業ウィンドウが消えてしまうことがあります。

　その場合は「ピボットテーブル分析」タブ→「表示」グループ→「フィールドリスト」で再表示ができます。

ピボットテーブルのフィールドの再表示方法
ピボットテーブルのフィールドの再表示方法（筆者作成）

　デジタル化を進めると、社内にはさまざまなデータが大量に蓄積していくことになります。そのデータを活用すればきっと何か見えるはず……と思っていても、データ量が増えれば増えるほど、その集計や分析が面倒になると感じている人もいるでしょう。

　ピボットテーブルを使えば、素早く簡単に大量のデータを集計することができます。ぜひピボットテーブルを活用して、社内のデータを有効に活用してください。

Adblock test (Why?)



from "応用" - Google ニュース https://ift.tt/gjYsbEV
via IFTTT

Bagikan Berita Ini

0 Response to "【図解】ピボットテーブルの使い方 基本から応用テクニックまで ... - ツギノジダイ"

Post a Comment

Subscribe to: Post Comments (Atom)
Powered by Blogger.