「エクセル2019の使い方」では、エクセル初心者の方を対象に、２５回に分けてエクセルの基礎から応用までの範囲を説明していきます。
エクセルの使い方⑬では、データベース機能Ⅳとして、データベースから必要なフィールドだけを自由に配置して、さまざまな角度から集計・分析できるピボットテーブル、ピボットグラフについて説明していきます。
YouTubeでみる↓↓↓
目次
１．ピボットテーブル
ピボットテーブルとは、データベースから必要な項目だけを選んで配置した集計表のことです。
項目は、ドラッグするだけで簡単に配置でき、入れ替えや追加、削除をすると、瞬時に再計算されます。
項目の入れ替えが簡単なので、さまざまな角度からデータを分析・集計することができます。
また、同じようにフィールドをドラッグして、ピボットグラフも作成できます。
（１）ピボットテーブルの作成
ピボットテーブルは、データベースを元にして作成します。
次のデータベースを元に、ピボットテーブルを作成してみます。
①データベース内の任意のセルをクリック→《挿入》タブー《テーブル》グループー《ピボットテーブル》をクリック。
②《ピボットテーブルの作成》ダイアログボックスー《テーブル/範囲》に自動的にデータベース全体が認識されます。
《ピボットテーブルレポートを配置する場所を選択してください。》→《新規ワークシート》のままＯＫをクリック。
↓
新規ワークシートが挿入され、空白のピボットテーブルが表示されます。
また、右側には《ピボットテーブルのフィールド》のリストとフィールドをレイアウトするためのボックスが表示されます。
参考
《ピボットテーブルのフィールド》の外観
■移動
《ピボットテーブルのフィールド▼》をクリックすると、リストを移動やサイズ変更ができます。
■表示方法
《ピボットテーブルのフィールド▼》の《歯車▼》をクリックすると、リストの表示方法を変更できます。
デフォルトの《フィールドセクションを上、エリアセクションを下に表示》で表示します。
（２）ピボットテーブルのレイアウト
《ピボットテーブルのフィールド》のリストをボックスにドラッグして、フィールドを簡単に配置できます。
配置したあとも、入れ替え、追加、削除などドラッグで簡単に行い、レイアウトを瞬時に変更できます。
■フィールドの配置
フィールドを次のように配置してピボットテーブルを作成してみます。
| 行 | 店舗 |
| 列 | カテゴリ |
| 値 | 売上金額 |
※値エリアの売上金額は、「合計/売上金額」と表示され、値エリアに数値データを配置すると集計方法は「合計」になることが分かります。
■フィールドの入れ替え
ピボットテーブルは、フィールドを自由に入れ替えて違った角度から分析ができるというメリットがあります。
フィールドの入れ替えも、ドラッグするだけなので簡単です。
《列》の「カテゴリ」フィールドと《行》の「店舗」フィールドを入れ替えてみます。
↓
■フィールドの追加
１つのエリアに複数のフィールドを追加することができます。
《行》の「カテゴリ」フィールドに「商品名」フィールドを追加してみます。
■フィールドの削除
各エリアのフィールドをドラッグで削除できます。
《行》の「カテゴリ」フィールドを削除してみます。方法は３通りあります。
・フィールドセクションの《カテゴリ》の□をオフにする
・エリアセクションの《カテゴリ》フィールドを外側にドラッグする
・エリアセクションの《カテゴリ▼》をクリックして《フィールドの削除》をクリック。
↓
■《フィルター》エリアにフィールドを配置
《フィルター》エリアは、フィールドを配置して必要なデータを抽出するためのエリアです。
《フィルター》エリアの抽出結果が、ピボットテーブル全体に反映します。
例えば、「担当者」フィールドを配置してみます。
「担当者」フィールドを配置しただけでは、何も変化はありません。
「担当者」フィールドでフィルタを実行することにより、分析に役立てることができます。
↓
《担当者▼》をクリックし、「金子」をクリックして抽出。
↓
《フィルター》エリアの抽出結果が、ピボットテーブル全体に反映しました。
複数の条件で抽出する場合には、《☑複数のアイテムを選択》の✔を入れます。
↓
《担当者》の抽出条件には、「複数のアイテム」と表示されます。
（３）フィールドの設定（グループ化、空白セル、集計方法など）
次のピボットテーブルの各エリアに色々なフィールドの設定をしてみます。
■日付や数値のグループ化
《列》《行》エリアの日付や数値のデータをある基準でまとめることをグループ化といい、グループの単位は変更できます。
今回は、「月」でグループ化とグループ化の解除をしてみます。
参考
デフォルトでは、《月》と《日付》が配置されていて、⊞５月の⊞をクリックすると、日付データが展開されます。
↓
■■《月》でグループ化
①《ピボットテーブルツール/分析》タブー《グループ化》グループー《グループの選択》または《フィールドのグループ化》をクリック
②《グループ化》ダイアログボックスー《単位》を「月」に設定してＯＫ。
■■グループ化の解除
《月》や《年》でグループ化したフィールドを解除します。
①ピボットテーブルの《月》のフィールドをクリック→《分析》タブー《グループ》グループー《グループの選択》または《グループ解除》をクリック。
②《グループ化》ダイアログボックスー《単位》を「日」に設定してＯＫ。
さらに詳しく
・月ごとにグループ化した日付を《日》に戻す場合は、《グループ解除》でも設定できます。
・さらに《日》を《月》などにグループ化して表示したい場合は、《フィールドのグループ化》でも設定できます。
・グループ化の単位は、入力されているデータによって次のようになります。
| データの種類 | グループ化の単位 |
| 日付 | 秒、分、時、日、月、四半期、年 |
| 数値 | 1-10、11-20のような10ごとなどの設定した範囲 |
次の操作のために、月ごとにグループ化しておきます。
■空白セルに０を表示
《値》エリアの集計結果を空白にしていると、誤って削除されてしまったのかデータがないのか分からないので、空白セルには「０」を表示しておきます。
「７月」の「紅茶」のセルに「０」を表示してみます。
①ピボットテーブル内をクリック→《ピボットテーブルツール/分析》タブー《ピボットテーブル》グループー《オプション▼》ー《オプション》をクリック。
②《ピボットテーブルオプション》ダイアログボックスー《☑空白セルに表示する値》：「０」と入力。
③「０」が表示されました。
■表示形式の変更
《値》エリアの「売上金額」フィールドの数値データに《桁区切り（，）》の表示形式を設定してみます。
①ピボットテーブルの「売上金額」フィールド内をクリック→《ピボットテーブルツール/分析》タブー《アクティブなフィールド》グループー《フィールドの設定》をクリック。
②《値フィールドの設定》ダイアログボックスー《表示形式》をクリック。
③《セルの書式設定》ダイアログボックスー《分類：》を《数値》ー《☑桁区切り（，）を使用する》に✔を入れてＯＫ。
↓
「売上金額」フィールド全体に（，）が表示されました。
■値エリアの集計方法の変更
値エリアの集計方法は、デフォルトでは次のように設定されていますが、後から変更することができます。
＜データの種類と集計方法＞
| データの種類 | 集計方法 |
| 数値 | 合計 |
| 文字列 | データの個数 |
| 日付 | データの個数 |
今回は、値エリアの集計方法を「合計」から「データの個数」に変更してみます。
①値エリアをクリック→《ピボットテーブル/分析》タブー《アクティブなフィールド》グループー《フィールドの設定》をクリック。
②《値フィールドの設定》ダイアログボックスー《集計方法》タブー《値フィールドの集計》→「個数」をクリックしてＯＫ。
↓
集計方法を「個数」にしたことで、売上の取引が何回あったかが分析できました。
次の操作のために「元に戻す」をクリックして、集計方法を「合計」に戻しておきます。
■値エリアの計算の種類を設定する
値エリアには、デフォルトで決まった集計がされていますが、任意の計算方法に変更することができます。
計算方法は、比率や順位などの登録されたリストが選択することができます。
今回は、「全体に対する比率」や「行集計に対する比率」を求めてみます。
■■全体に対する比率
全体の総計を１００％とした場合の、売上構成比をあらわしてみます。
①《値フィールドの設定》ダイアログボックスー《計算の種類》タブー《総計に対する比率》をクリックしてＯＫ。
↓
■■行集計に対する比率
行の集計を１００％とした場合の売上構成比をあらわしてみます。
①《値フィールドの設定》ダイアログボックスー《計算の種類》タブー《行集計に対する比率》をクリックしてＯＫ。
↓
（４）ピボットテーブルの便利な機能
■詳細データの表示
値エリアの集計結果の元になっている詳細データを新しいシートに表示できます。
「コーヒー」の「６月」の集計結果は、「128,000」です。
この集計結果の元になる詳細データを表示してみます。
①セル【Ｃ５】をダブルクリック。
②新しいシートが作成され、詳細データが表示されました。
■レポートフィルタページの表示
フィルターエリアに配置したフィールドは、項目ごと新しいピボットテーブルを作成し、更に新しいシートに分けて表示できます。
店舗別のピボットテーブルを新しいシートに表示してみます。
①ピボットテーブル内をクリック→《ピボットテーブル/分析》タブー《ピボットテーブル》グループー《オプション▼》ー《レポートフィルタページの表示》をクリック。
②《レポートフィルタページの表示》ダイアログボックスー「店舗」と表示されていることを確認して、ＯＫ。
③店舗別のシートが、３枚表示されました。
■ピボットテーブルスタイル
ピボットテーブルの配色やスタイルは、《ピボットテーブルスタイル》で設定できます。
任意のスタイルに設定してみます。
①《ピボットテーブルツール/デザイン》タブー《ピボットテーブルスタイル》グループから任意のスタイルをクリック。
（５）データの更新
ピボットテーブルの元になるデータベースのデータを変更すると、変更をピボットテーブルに反映できます。
５月の東京のコーヒーのセル【Ｈ５】を「５５」→「１００」に変更し、ピボットテーブルに反映させてみます。
①データベースのセル【Ｈ５】をクリック。
↓
「５５」→「１００」に変更。
②ピボットテーブルのシートに切り替え、ピボットテーブル内をクリック→《データ》グループー《更新》のイラストをクリック。
↓
ピボットテーブルが更新されました。
２．ピボットグラフ
（１）ピボットグラフの作成
ピボットテーブルを元にグラフを作成できます。
①ピボットテーブル内をクリック→《ピボットテーブル/分析》タブー《ツール》グループー《ピボットグラフ》をクリック。
②任意のグラフをクリックしてＯＫ。
■ピボットグラフの種類
ピボットグラフの種類は後から変更できます。
「集合縦棒」に変更してみます。
①ピボットグラフ内をクリック→《ピボットテーブル/デザイン》タブー《種類》グループー《グラフの種類の変更》をクリック。
■フィールドの入れ替え
ピボットグラフをクリックすると、《ピボットグラフのフィールド》のリストが表示されます。
ドラッグでフィールドを入れ替えることもできます。
↓
■フィルタ
ピボットグラフのフィルタを設定することもできます。
↓
データベースの機能は、以上となります。
エクセルは、データーベースの形式でデータを蓄積しておくと、色々な機能を利用することができ便利です。
フィルタ、集計、テーブル、ピボットテーブルが主なデータベース機能になります。
エクセルの使い方⑭へ↓↓
エクセルの使い方 基礎から応用まで①~㉕ 無料オンライン講座へ↓↓