• 公開日:
  • 更新日:

ピボットテーブルの応用テクニック

この記事では、ピボットテーブルの応用テクニックをご紹介します。

ピボットテーブルで「グループ化」を行う方法や、「ピボットグラフ」を表示させる方法等についてご説明していますので、ぜひ参考にしてみて下さい。

データベースのファイルも作成しましたので、ダウンロードして実際に操作してみましょう。

ピボットテーブルの応用テクニック

ピボットテーブルの様々なテクニックをご紹介します。

同じ操作ができるファイルをご用意しましたので、ダウンロードして練習に活用してみてください。

「前月比を求める」セクションと「フィルターでデータを抽出する」セクションは「ピボットテーブル練習ファイル2」をお使いください。

ピボットテーブル練習ファイル1をダウンロード


ピボットテーブル練習ファイル2をダウンロード

値にカンマを付ける

ピボットテーブルの値に、「,(カンマ)」を付けることでデータが見やすくなります。

カンマを付ける方法は以下の通りです。

今回は日付ごとに商品別の売り上げを表したピボットテーブルを作成して、その売り上げの値にカンマを付けます。

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

まずは、「ピボットテーブル練習問題1」をダウンロードし、任意のピボットテーブルを作成します。

①【データベース(例:A2セルからE48セル)】、②【挿入】タブ、③【ピボットテーブル】の順に選択します。

OKボタンを押す

「テーブルまたは範囲からのピボットテーブル」ダイアログボックスが表示されます。

データベースの範囲が正しく選択できていることを確認して、【OK】ボタンを押します。

日付けをドラッグする

【日付】を選択し、「行」ボックスに【ドラッグ】します。

商品名をドラッグする

【商品名】を選択し、「列」ボックスに【ドラッグ】します。

売上をドラッグする

【売り上げ】を選択し、「値」ボックスに【ドラッグ】します。

右クリックする

ピボットテーブルが完成しました。

①「ピボットテーブルの左上のセル(例:A3セル)」の上で【右クリック】し、②【値フィールドの設定】を選択します。

表示形式ボタンを押す

「値フィールドの設定」ダイアログボックスが表示されます。

【表示形式】ボタンを押します。

チェックマークを入れる

「セルの書式設定」ダイアログボックスが表示されます。

①「分類」の【数値】を選択します。

②「桁区切り(,)を使用する」に【チェックマーク】を入れて、③【OK】ボタンを押します。

OKボタンを押す

「値フィールドの設定」ダイアログボックスに戻ります。

【OK】ボタンを押します。

値にカンマがつく

これで、値にカンマをつけることが出来ました。

ピボットテーブルを更新する

ピボットテーブルには、元のデータベースのデータを変更しても自動的に変更が反映されません。

反映させるには、手動で更新する必要があります。

方法の詳細は以下の通りです。

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

まずは、「ピボットテーブル練習問題1」をダウンロードし、任意のピボットテーブルを作成します。

①【データベース(例:A2セルからE48セル)】、②【挿入】タブ、③【ピボットテーブル】の順に選択します。

OKボタンを押す

「テーブルまたは範囲からのピボットテーブル」ダイアログボックスが表示されます。

データベースの範囲が正しく選択できていることを確認して、【OK】ボタンを押します。

日付けをドラッグする

「ピボットテーブル」を作成します。

今回は日付ごとに商品別の売上が分かるピボットテーブルを作成します。

【日付】を選択し、「行」ボックスに【ドラッグ】します。

商品名をドラッグする

【商品名】を選択し、「列」ボックスに【ドラッグ】します。

売上をドラッグする

【売り上げ】を選択し、「値」ボックスに【ドラッグ】します。

データを変更する

データベースに戻って、値を変更してみます。

【任意のセル(例:D3セル)】を選択して、【任意の数値(例:100)】と入力し、Enterを押します。

右クリックする

ピボットテーブルに移ります。

①「ピボットテーブルの左上のセル(例:A3セル)」の上で【右クリック】し、②【更新】を選択します。

ピボットテーブルに反映される

元のデータベースで「5月1日のりんご」の数量を「100」に変更したため、赤い矢印で示したように5月1日のりんごの売り上げが変更されています。

これでピボットテーブルに、変更したデータを反映させることが出来ました。

データを大きい順に並べ替える

データを大きい順(降順)に並べる方法をご説明します。

大きい順に並べなおすことで、例えば「一番売り上げがあった商品」や「売り上げの多い日はいつか」等が一目で分かるようになります。

今回は、商品別の販売個数が分かるピボットテーブルを、販売個数の大きい順に並べ替える方法を例に挙げます。

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

まずは「ピボットテーブル練習問題1」をダウンロードし、任意のピボットテーブルを作成します。

①【データベース(例:A2セルからE48セル)】、②【挿入】タブ、③【ピボットテーブル】の順に選択します。

OKボタンを押す

「テーブルまたは範囲からのピボットテーブル」ダイアログボックスが表示されます。

データベースの範囲が正しく選択できていることを確認して、【OK】ボタンを押します。

商品名を行にドラッグ

【商品名】を選択し、「行」ボックスに【ドラッグ】します。

個数を値にドラッグ

【数量】を選択し、「値」ボックスに【ドラッグ】します。

降順を選択する

商品別の販売個数が分かるピボットテーブルが完成しました。

①「値(例:B4セル)」の上で【右クリック】します。

②「並べ替え」に【マウスオーバー】し、③【降順】を選択します。

データが上から大きい順に並ぶ

これで、データを大きい順に並べ替えることができました。

グループ化する

ピボットテーブルのデータは、任意のカテゴリでグループ化することが出来ます。

今回は、日付ごとに商品別の売上が分かるピボットテーブルを作成し、売り上げデータを1週間ごとにグループ化する方法を例に挙げます。

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

まずは、「ピボットテーブル練習問題1」をダウンロードし任意のピボットテーブルを作成します。

①【データベース(例:A2セルからE48セル)】、②【挿入】タブ、③【ピボットテーブル】の順に選択します。

OKボタンを押す

「テーブルまたは範囲からのピボットテーブル」ダイアログボックスが表示されます。

データベースの範囲が正しく選択できていることを確認して、【OK】ボタンを押します。

日付けをドラッグする

【日付】を選択し、「行」ボックスに【ドラッグ】します。

商品名をドラッグする

【商品名】を選択し、「列」ボックスに【ドラッグ】します。

売上をドラッグする

【売り上げ】を選択し、「値」ボックスに【ドラッグ】します。

グループ化を選択する

①「日付(例:A5セル)」の上で【右クリック】し、②【グループ化】を選択します。

7日ごとに区切る

「グループ化」ダイアログボックスが表示されます。

①「開始日」と「最終日」を入力します。今回は「開始日」に『2021/5/1』、「最終日」に『2021/5/11』と入力します。

②「単位」で【日】を選択して、③一週間ごとでグループ化したいので「日数」に『7』と入力し、Enterを押します。

④【OK】ボタンを押します。

1週間ごとにデータをまとめられる

ピボットテーブルを確認すると、日付が7日ごとに区切られています。

これで、日付をグループ化することが出来ました。

ピボットグラフを使用する

ピボットテーブルから「ピボットグラフ」と呼ばれるグラフを作成することが出来ます。

今回は「毎日の売上」を縦棒グラフで表すピボットグラフを作成する方法を例に挙げます。

方法の詳細は以下の通りです。

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

まずは、「ピボットテーブル練習問題1」をダウンロードします。

①【データベース(例:A2セルからE48セル)】、②【挿入】タブ、③【ピボットテーブル】の順に選択します。

OKボタンを押す

「テーブルまたは範囲からのピボットテーブル」ダイアログボックスが表示されます。

データベースの範囲が正しく選択できていることを確認して、【OK】ボタンを押します。

日付けをドラッグする

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

【日付】を選択し、「行」ボックスに【ドラッグ】します。

売上を値ボックスにドラッグ

【売り上げ】を選択し、「値」ボックスに【ドラッグ】します。

ピボットグラフを選択

ピボットテーブルが完成しました。

①【ピボットテーブルの左上のセル(例:A3セル)】、②【ピボットテーブル分析】タブ、③【ピボットグラフ】の順に選択します。

グラフを選択してOKボタンを押す

「グラフの挿入」ダイアログボックスが表示されます。

①【縦棒】、②【縦棒】の順に選択し、③【OK】ボタンを押します。

グラフが挿入される

これで、ピボットグラフを挿入できました。

前月比を求める

ピボットテーブルで作成した月ごとの売上表から「前月比」を求める方法をご説明します。

今回は商品別の5月6月7月の総売り上げから、それぞれの前月比を求める方法を例に挙げます。

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

まずは、「ピボットテーブル練習問題2」をダウンロードします。

①【データベース(例:A2セルからE97セル)】、②【挿入】タブ、③【ピボットテーブル】の順に選択します。

OKボタンを押す

「テーブルまたは範囲からのピボットテーブル」ダイアログボックスが表示されます。

データベースの範囲が正しく選択できていることを確認して、【OK】ボタンを押します。

商品名をドラッグ

【商品名】を選択し、「行」ボックスに【ドラッグ】します。

日付けを列にドラッグ

【日付】を選択し、「列」ボックスに【ドラッグ】します。

売上を値にドラッグ

【売り上げ】を選択し、「値」ボックスに【ドラッグ】します。

値フィールドの設定を選択する

①「値(例:B5セル)」の上で【右クリック】し、②【値フィールドの設定】を選択します。

基準値に対する比率を選択する

「値フィールドの設定」ダイアログボックスが表示されます。

①【計算の種類】タブ、②【基準値に対する比率】の順に選択します。

③「基準フィールド」の【月】、④「基準アイテム」の【(前の値)】の順に選択し、⑤【OK】ボタンを押します。

前月比が出せる

これで、前月比が計算できます。

データを増やしていけば、前年比なども求めることが可能です。

フィルターでデータを抽出する

フィルター機能を使って任意のデータを抽出する方法をご説明します。

今回は売り上げのデータから「いちご」のデータのみを抽出する方法を例に挙げます。

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

まずは、「ピボットテーブル練習問題2」をダウンロードします。

①【データベース(例:A2セルからE97セル)】、②【挿入】タブ、③【ピボットテーブル】の順に選択します。

OKボタンを押す

「テーブルまたは範囲からのピボットテーブル」ダイアログボックスが表示されます。

データベースの範囲が正しく選択できていることを確認して、【OK】ボタンを押します。

日付けを行にドラッグ

日付けごとに商品別の売上が分かるピボットテーブルを作成します。

【日付】を選択し、「列」ボックスに【ドラッグ】します。

商品名を列にドラッグ

【商品名】を選択し、「列」ボックスに【ドラッグ】します。

売上を値にドラッグ

【売り上げ】を選択し、「値」ボックスに【ドラッグ】します。

任意のデータのみチェックする

ピボットテーブルが作成できました。

①「列ラベル」の【▼】ボタンを押します。

②「抽出したいデータ(例:いちご)」のみに【チェックマーク】を入れ、③【OK】ボタンを押します。

選択したデータのみが抽出される

これで、いちごのデータのみを抽出することが出来ました。