Excelのピボットテーブル ExcelのPower Query(パワークエリ)

ピボットテーブルの基本と注意点-Excelで都議選予想分析③

更新日:

全体の構成

①WEB上のデータをPowerQueryでデータを作成する
②PowerQuery作成データにデータを修正・追加する
③ピボットテーブルの基本と注意点
④ピボットテーブルの機能や細かい設定を紹介
⑤ピボットテーブルで高度な集計可能なメジャー関数
⑥ピボットグラフの作成

都議会議員選挙サンプル.xlsx(実際に作成できるデータとは異なります)

ピボットテーブルとは

ピボットテーブルとは、簡単な操作でさまざまな集計をすることができるExcelの機能です。

このようなデータから・・・

画像5

このような集計表を作成することができます。

画像9

こんなグラフもできます。

画像12

ピボットテーブルの素晴らしいところは、高機能でありながら、操作が直感的でわかりやすいところです。
私が会社内で作成する集計資料、分析資料はほぼピボットテーブルで作成しています。
ピボットテーブルの何がいいかというと、1つのデータから何十、何百といった集計が可能であるため、複数の部署、複数の人から資料の作成依頼があったときに、ささっと作成することができるところです。

ピボットテーブルを上手く使いこなすには、元となるデータがきちんとしているかが非常に重要です。
この「きちんとしている」とは主に、
①データのルールを守られているか
②集計に必要なデータが用意されているか

まずはここを十分に押さえたうえで、より高度なピボットテーブルを活用する方法を含め一通りの使い方を紹介します。

Excelの基本

まずはExcelそれぞれの名称について説明しておきましょう。
※私が説明するExcelのバージョンはmicrosoft365です。

画像4

A.「列」または「フィールド」
Excelシートの縦方向(初期状態ではA,B,C...と表示されています)
B.「行」または「レコード」
Excelシートの横方向(初期状態では1,2,3...と表示されています)
C.「セル」
シート内の1つ1つの枠をセルといいます。また選択中のセルをアクティブセルといいます。
D.「クイックアクセスツールバー」
Excelのコマンドで利用頻度の高いものを配置しておく場所です。Alt+数字キーで素早く起動することができます。
E.「タブ」
リボンを切り替えるときに選択します。
F.「リボン」
Excelのコマンドがカテゴリーごとにまとめられている場所です。
G.「シート見出し」
ブック内の各シートの名称です。

ピボットテーブルの簡単なイメージ

まず最初にピボットテーブルのイメージを簡単に説明します。
直観的にわかるように動画で紹介しましょう。

見てわかるようにマウスの操作だけで、ピボットテーブルが作成されたのがわかると思います。

図でイメージすると点線の上が(テーブル)データ、下がピボットテーブルです。

画像10

ピボットテーブルの作成

(テーブル)データを選択した状態で、タブ「挿入」→「ピボットテーブル」をクリックします。

画像12

「ピボットテーブルの作成」ウィンドウが表示されるので変更せず「OK」をクリックします。

画像13

ピボットテーブルの下地が作成されました。

画像14

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

ピボットテーブルの作成には、「ピボットテーブルのフィールド」作業ウィンドウを使います。
初期状態では作業ウィンドウは上下に表示されており、上部を「フィールドセクション」、下部を「エリアセクション」と呼びます。
※ちなみに表示方法は変更することは可能です。

画像12

フィールドセクションとエリアセクション
「フィールドセクション」は(テーブル)データの1行目の項目名がチェックボックスの形式で並びます。
「エリアセクション」には「フィルター」・「列」・「行」・「値」の4つの枠が並んでいます。この部分を「ボックス」と呼びます。

この「ボックス」には、フィールドセクションにある項目を選ぶとピボットテーブルに表示される仕組みになっています。
項目名をドラッグで下のエリアセクションに持っていきドロップするとピボットテーブルに表示されます。

(テーブル)データをそれぞれの領域(行ラベル、列ラベル、値、レポートフィルター)にセットするとこのようにクロス集計表が作成されます。

画像13

ピボットテーブルの4つのエリアがあります。

画像11

行ラベル:表左の縦に並んだ見出しの部分
列ラベル:表の上に横に並んだ見出しの部分
値:右下に表示される数値の部分。行ラベルと列ラベルの項目が交差する位置のセルに「値」が表示されます。
レポートフィルター:集計したい項目に絞っての集計表を表示することができます。

(テーブル)データの注意点

ピボットテーブルの元となる(テーブル)データはルールにのっとったものでなければなりません。
まずは(テーブル)データについて押さえておきましょう。

・先頭行を1行、列見出しとして設定する
・列見出しに2回以上同じ名前を使わない
・列(フィールド)には同じ形式のデータ入力する(日付は日付のみ、数値であれば数値のみ)
・1列(フィールド)には1列分のデータを入力する
・1行(レコード)に1件分のデータを入力する

画像6

次のようなデータはピボットテーブルが作成できないません

複数行や複数列が1つにまとまっている

画像7

列見出しが空欄であったり、空白の行がある

画像8

以上です。

全体の構成

①WEB上のデータをPowerQueryでデータを作成する
②PowerQuery作成データにデータを修正・追加する
③ピボットテーブルの基本と注意点
④ピボットテーブルの機能や細かい設定を紹介
⑤ピボットテーブルで高度な集計可能なメジャー関数
⑥ピボットグラフの作成

都議会議員選挙サンプル.xlsx(実際に作成できるデータとは異なります)

03投稿記事下_関連コンテンツ

-Excelのピボットテーブル, ExcelのPower Query(パワークエリ)

Copyright© 経理と総務の効率化 , 2021 All Rights Reserved.