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

ピボットテーブルの機能や細かい設定を紹介-Excelで都議選予想分析④

更新日:

全体の構成

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

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

まず③で紹介したピボットテーブル作成操作を動画で紹介しておきます。

ということでピボットテーブルの作成方法について知っている前提で説明を開始します。

テーブルを選択した状態で、タブ「挿入」→「ピボットテーブル」をクリックします。
ピボットテーブルの作成のウィンドウが開くので、設定はそのままでOKします。
※チェックを「テーブルまたは範囲を選択」と「新規ワークシート」に入った状態。

ピボットテーブルが作成されます。

全体の操作動画です

フィールドの設定の表示方法を変更する

1.右上にあるネジマークをクリックする
2.メニューが表示されます
3.「フィールドセレクションを左、エリア、セレクションを右に表示」を選びます。

表示が変更されました。
この後の説明はこの表示で説明していきます。

メジャー関数を使える設定をする

デフォルトの状態だとフィールドセレクションは項目名のみがリスト表示され、元データのシートと、新規で作成されたピボットテーブルが表示されたシートの2つがある状態です。

フィールドセレクションの一番下にある「その他のテーブル...」をクリックし、ピボットテーブルの新規作成のメッセージが出てきますので、「はい」をクリックします。

ピボットテーブルが作成されているシートが1つ増えました。
変わったのはフィールドセレクションの表示です。一番上に「ベースデータ」という表示が追加されました。

※メジャー関数については別の記事で紹介します。

ピボットテーブルを作成する

上記の設定よりピボットテーブルを次のように作成しました。
フィルター:選挙区
列:年月
行:所属
値:得票数

値に桁区切りを入れる

値のフィールド上で、右クリック→メニューから「値フィールドの設定」をクリックし

表示されるウィンドウから「表示形式」をクリックし

表示されるウィンドウから分類の「数値」を選び、桁区切り(,)を使用するにチェックを入れます。

数値に桁区切りが入りました。

数値を並べ替える

並べ替えたい値のある列を選択した状態で、右クリック→メニューから「並べ替え」→「降順」をクリックします。
今回は2001(年)07(月)の列

数値が並べ替えられました。

集計値の表示・非表示を切り替える

集計値を表示したり、非表示にしたりすることができます。
初期集計の時は、行と列のどちらも集計値が表示されていますが、行集計を非表示する設定にしてみましょう。

タブ「デザイン」→「総計」→「列の集計のみ行う」をクリックします。

行集計がなくなりました。

立候補者数、当選者数を追加する

「氏名」と「当落」を値フィールドにドラッグ&ドロップします。

赤枠:合計 / 得票数
緑枠:カウント / 氏名
黄枠:合計 / 当落

タイトル行を直接編集して変更します。

設定した値でフィルターをかける

行ラベル右横の▼をクリックし、メニューから「値フィルター」→「トップテン」をクリックし、トップテン フィルター(所属)のウィンドウが表示されますので、各コンボボックスから[得票数]の[上位][10]で「OK」をクリックします。

トップテンのみの表示になりました。

得票率を設定する

追加した得票数の列のどこかのセルを選択した状態で右クリックし、メニューから「計算の種類」→「列集計に対する比率」をクリックします。

パーセンテージ表示に変わりました。

パーセンテージの小数点以下の桁数を変更しましょう。
得票数の列のセルを選択した状態で右クリックし、メニューから「値フィールドの設定」→「表示形式」→「パーセンテージ」の小数点以下の桁数を1に設定します。

1桁に変わりました。
名称を「得票率」に変更しておきましょう。

前回比較値を追加する

フィールドセレクションより値に「得票数」を追加します。

得票数の列のセルを選択した状態で右クリックし、「計算の種類」→「基準値との差分」をクリックし、計算の種類のウィンドウが表示されますので、
基準フィールド:年月
基準アイテム:前の値
を設定します。

前回比較の列が追加されました。
2001(年)07(月)は前回がないので空白になっています。

①722,464から②786,292を引いた数字が③63,828として表示されます。

名前を「得票数前回比」と変えておきましょう。

ドラッグ&ドロップで表示する列の場所を前に移動させました。

立候補者数の前回比較、当選者数の前回比較を追加する

得票数の前回比同様に「氏名」「当落」をフィールドセレクションより氏名を値のボックスへ追加します(立候補者数の下の部分へ)

得票数前回比と同様、「氏名」「当落」の列のセルを選択した状態で右クリックし、「計算の種類」→「基準値との差分」をクリックし、計算の種類のウィンドウが表示されますので、
基準フィールド:年月
基準アイテム:前の値

立候補者数前回比、当選者数前回比が追加されました。

行項目を追加する

「定数」と「選挙区」を「所属」の上に追加します。

行項目が追加されました。
表示が右斜め下に階段になるように表示されました。
このレイアウトは「コンパクト形式」です。

レイアウトを一部「表形式」に変更する

「定数」の右横に「選挙区」が表示されるように変更しましょう。

行ボックス上の「定数」をクリックして表示されるメニューから「フィールドの設定」をクリックします。

タブ「レイアウトと印刷」を選択し、「アイテムのラベルを表形式で表示する」にチェックを入れます。

行項目の2つ目である「選挙区」が表形式、「所属」はコンパクト形式でのレイアウトになりました。

集計行を追加する

行ボックス上の「選挙区」をクリックして表示されるメニューから「フィールドの設定」をクリックします。

タブ「小計とフィルター」では「自動」にチェックを入れます。
タブ「レイアウトと印刷」では「小計を各グループの先頭に表示する」のチェックを外します。

集計行が追加されました。

集計行が見えやすいように色を付けます。

集計行の名前がついている部分にマウスを当てて、行の色が一括してグレーになるので、タブ「ホーム」→「塗りつぶし」をクリックします。

集計行に一括で色が塗られました。

フィールド名の左横にある「+-」ボタンで展開したり、閉じたりすることができます。

まとめてフィールドを展開したり、閉じたりすることもできます。

処理したい列のどこかを選択した状態で右クリックし、「展開/折りたたみ」→「フィールド全体の折りたたみ」をクリックします。

フィールドが折りたたまれました。

スライサーを利用する

スライサーは簡単に説明するとボタン式のフィルターです。
絞り込みたい項目を選択すると、その項目のみ表示されます。

タブ「ピボットテーブル分析」→「スライサーの挿入」をクリックし、「スライサーの挿入」ウィンドウが開きますので、「所属」「選挙区」「定数」「年月」にチェックを入れます。

チェックを入れた項目がスライサーとして作成されます。

スライサーの右下をマウスでつかみ(ポインタマークが+になる)、横長になるようにサイズを変更します。

タブ「スライサー」→列を「7」に変更すると、ボタンは7つ横並びに表示されます。

他のスライサーをこのように表示しました。

「定数」のボタン「1」を選択してみましょう。

「選挙区」ボタンの「青梅市」を選択してみます。
※選挙区の左横の「+」をクリックして、展開しています。

レイアウトを変更する

行ボックスから「定数」と「選挙区」を削除します。

表示が変わりました。

列ボックスにある「年月」を行ボックスにドラッグします。

スライサーの右上にあるフィルターボタンをクリックして、選択を解除します。

「選挙区」と「定数」の解除したのでこのようになりました。

レイアウトを「表形式」に変更します。
タブ「デザイン」→「レポートのレイアウト」→「表形式で表示」をクリックします。

年月をクリックし、フィールドの設定をクリック。
タブ「小計とフィルター」の自動にチェックを入れます。

行集計が追加されました。

得票率の年単位での率になるように設定する

得票率が5回分の総計に対して率が計算されているので、年単位の計に対しての率にします。

現状
200107:19.3%
200507:17.6%
200907:22.7%
201307:18.1%
200707:22.4%

変更後
200107:100.0%
200507:100.0%
200907:100.0%
201307:100.0%
200707:100.0%

得票率の列のどこかのセルを選択した状態で右クリックをし、「計算の種類」→「親集計に対する比率」をクリックし、表示される計算の種類(得票率)ウィンドウの基準フィールドのコンボボックスから「年月」を選択します。

得票率の基準値が年単位での合計に対する比率に変わりました。

次の記事ではメジャー関数を紹介したいと思います。

全体の構成

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

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

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

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

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