MOS Excel|ピボットテーブルの基本|申込締切11月16日・試験12月13日

記事
学び
MOS Excelの申込締切は11月16日、試験は12月13日です。締切まであと1日となりました。今回は、Excel試験でよく出る「ピボットテーブル」の基本を、操作の手順を中心に整理します。ピボットテーブルは、大量のデータをドラッグするだけで集計表に変える機能です。昨日は条件付き書式のルール設定を整理しましたが、今日は集計の切り替え方に絞ります。なお、この記事の問題例は公式問題ではなく、MOSの出題範囲に沿って作ったオリジナルの練習問題です。

1. ピボットテーブルとは——集計表をドラッグで作る機能
ピボットテーブルは、表のデータを集計して見やすく並べ替える機能です。たとえば商品別の売上表があるとき、「商品ごとの売上合計」「地域ごとの売上合計」「月ごとの売上合計」を、表を作り直さずに切り替えて見ることができます。関数で合計を求めるのと違い、集計の軸をドラッグで入れ替えるだけで、見たい切り口に変えられます。試験では、ピボットテーブルを作る操作と、集計の軸を変える操作が出ます。「ピボットテーブル」という言葉が出たら、集計表をドラッグで作る機能だと押さえておきましょう。

2. 基本の作り方——挿入からフィールドの配置まで
ピボットテーブルを作る基本の手順は、次のとおりです。まず、元の表の中のセルを1つ選びます。次に、挿入タブの「ピボットテーブル」を開き、表の範囲を確認して「OK」を押します。新しいシートに、ピボットテーブルの枠と「ピボットテーブルのフィールド」という一覧が出ます。ここで、集計したい項目を4つの場所にドラッグします。「行」には、縦に並べたい項目を入れます。「列」には、横に並べたい項目を入れます。「値」には、合計や個数を出したい数値の項目を入れます。「フィルター」には、絞り込みに使いたい項目を入れます。たとえば商品を行、地域を列、売上を値に入れると、商品ごと・地域ごとの売上合計の表ができます。

3. 値フィールドの設定——合計・個数・平均の切り替え
値に入れた項目は、最初は「合計」で集計されることが多いですが、集計の方法は変えることができます。値の項目を右クリックして「値フィールドの設定」を開くと、合計・個数・平均・最大値・最小値などから選べます。たとえば売上の合計を見たいときは「合計」、注文の件数を数えたいときは「個数」、1件あたりの平均を出したいときは「平均」を選びます。試験では、課題文の「合計を求めなさい」「件数を数えなさい」という言葉と、集計方法の対応が問われます。「個数」を選ぶべきところで「合計」のままにしておくと、答えが合いません。値フィールドの設定を開く操作と、集計方法の切り替えをセットで覚えておきましょう。

4. グループ化と並べ替え・フィルター
ピボットテーブルでは、作ったあとの見せ方も操作できます。日付の項目は、グループ化で「月ごと」や「四半期ごと」にまとめられます。日付のセルを選んで右クリックし、「グループ化」を選ぶと、単位を選ぶ画面が出ます。並べ替えは、行ラベルの横の矢印から「昇順」「降順」を選びます。売上の多い順に並べ替えたいときは、値の項目で降順を選びます。フィルターは、行や列のラベルの横の矢印から、表示する項目にチェックを入れて絞り込みます。たとえば地域を「関東だけ」に絞ると、関東の集計だけが表示されます。試験では、グループ化と並べ替え・フィルターの操作が、課題文の指示通りにできるかが問われます。

5. 試験でよく出るミス
よくあるミスは3つです。1つ目は、元データを変えたあとに更新を忘れることです。ピボットテーブルは、元の表を変えても自動では変わりません。「更新」を押して、集計し直す必要があります。2つ目は、元の表に空白の行や列があることです。空白があると、ピボットテーブルの範囲が途中で切れてしまい、集計が欠けます。元の表は、空白の行や列を入れずに整えておきましょう。3つ目は、値の集計方法の確認忘れです。「合計」のはずが「個数」になっていたり、その逆だったりすると、答えが変わります。表ができたら、値の項目が何で集計されているかを一度確認する習慣をつけましょう。

確認問題3問
問1.ピボットテーブルの説明として、正しいものはどれか。
1.関数を使って1つずつ合計を求める機能
2.表のデータをドラッグで集計し、集計の軸を入れ替えて見られる機能
3.表の見た目を整える書式設定の機能
→答えは2です。集計表をドラッグで作り、切り口を変えられる機能です。
問2.商品を行、地域を列、売上を値に入れたとき、できる表はどれか。
1.商品ごと・地域ごとの売上合計の表
2.商品ごとの売上だけの表
3.地域ごとの売上だけの表
→答えは1です。行と列の組み合わせで集計されます。
問3.元の表のデータを変えたあと、ピボットテーブルに反映させる操作はどれか。
1.何もしなくても自動で変わる
2.「更新」を押して集計し直す
3.ピボットテーブルを作り直すしかない
→答えは2です。元の表を変えても自動では変わらないため、更新が必要です。

※この記事の問題例は公式問題ではなく、MOSの出題範囲に沿って作ったオリジナルの練習問題です。
※MOS Excelの関数対策は、ココナラの有料記事『MOS Excel「条件集計」直前ノート』で、COUNTIFとSUMIFの使い分けを確認問題つきで整理しています。
サービス数100万件のスキルマーケット、あなたにぴったりのサービスを探す