役立つ! 総務マガジン

Excelピボットテーブルの便利な抽出機能

フィルターだけじゃない! 集計をサポートする強力な機能を使いこなそう

Excelのピボットテーブルを使いこなしていくこの特集も、今回で3回目。さらに詳細な集計や分析のため、データを抽出する「スライサー」と結果を見やすくする「グループ化」、そして時系列のデータの抽出に便利な「タイムライン」を解説します。

[2018年11月12日公開]

特集:Excelピボットテーブル

データの集計を効率化する「ピボットテーブル」機能を全4回でたっぷり特集! 独特の仕組みを理解して、総務のさまざまな業務で使いこなしましょう。

集計以外も得意なピボットテーブル

第13回ではフィルターを利用して、集計対象とする消耗品の中から対象の品名を選びました。フィルターも抽出機能の一種ですが、フィルターの一覧を表示しないと何を対象としているのかが分かりにくいという難点があります。

そこで今回は、対象の一覧が常に表示されて、選択・解除も簡単な「スライサー」機能を解説します。

スライサーを表示すれば簡単に抽出条件を変えられる

スライサーは、元のデータの任意の項目から、抽出の対象を選べる機能です。働きはフィルターとほぼ同じですが、常に抽出対象の一覧が表示され、簡単に入れ替えられるのが特徴です。

(1)スライサーを挿入する

ピボットテーブルをクリックして選択した状態で、[分析]タブにある[スライサーの挿入]ボタンをクリックします。

(2)[スライサーの挿入]ダイアログボックスから「持出消耗品名」を選択する

[スライサーの挿入]ダイアログボックスが表示され、元のデータにある項目の一覧が表示されました。ここでは消耗品名を対象とするため、「持出消耗品名」項目にチェックマークを付けて[OK]をクリックします。

(3)「持出消耗品名」のスライサーが表示された

「持出消耗品名」のスライサーが表示されました。最初は全ての品名が選択された状態になっています。

(4)1点だけを抽出する

1点だけ品名を抽出してみましょう。スライサーから「コピー用紙(A3)」をクリックすると、ほかの品名の選択が解除されます。ピボットテーブルにも即座に抽出が反映され、「コピー用紙(A3)」だけの集計結果が表示されました。

(5)複数を抽出する

複数点の品名を抽出するには、[Ctrl]キーを押しながら複数の品名をクリックします。「コピー用紙(A3)」「コピー用紙(A4)」「コピー用紙(B5)」の3点を選択すると、3種類のコピー用紙の集計が行われました。

(6)スライサーによる抽出を解除する

抽出を解除するには、スライサーの右上にある[フィルターのクリア]をクリックします。

(7)抽出が解除された

抽出が解除され、全ての品名が選択された状態になりました。なお、スライサーの使用をやめるには、スライサーが選択されて四隅に[○]が表示されている状態で[Delete]キーを押します。

データをまとめて見やすくする「グループ化」機能

データの点数が多い場合は、適切にグループ化してまとめることで、分かりやすく集計できる場合があります。例えば、一日単位の集計では日ごとのばらつきが大きく、ピボットテーブルが長くなりすぎて視認性がよくありません。しかし、月単位にまとめることで大枠での変動を分かりやすくできます。

第13回では、長期間の日付を[行]にドラッグしたとき、自動的にグループ化機能が働いて月ごとのグループが作成されました。こうした日付以外のデータも、手動でグループ化が可能です。以下の手順では、「持出消耗品名」を「シャープペンシル替え芯」「コピー用紙」など商品カテゴリーごとにグループ化する方法を解説します。

(1)[行ラベル]から「月」と「記入日」項目を削除する

始めに、既に[行]領域に設定している項目を[ピボットテーブルのフィールド]の外にドラッグして削除します。

(2)「持出消耗品名」項目を[行]領域に設定する

続けて、「持出消耗品名」項目を[行]領域にドラッグします。ここでは、既に[フィルター]領域に設定していた「持出消耗品名」項目を、[行]領域にドラッグして移動しています。

(3)品名を選択してグループ化する

[行]領域に「持出消耗品名」項目が設定されました。ここではまず3種類ある「シャープペンシル替芯」をまとめます。[Ctrl]キーを押しながら「シャープペンシル替芯(0.5mmB)」「シャープペンシル替芯(0.5mmH)」「シャープペンシル替芯(0.5mmHB)」の3点をクリックして選択し、右クリックして表示されたメニューから[グループ化]をクリックします。

(4)グループ化された

選択した3点がグループ化され、「グループ1」というグループ名が自動的に挿入されました。また、[ピボットテーブルのフィールド]の[行]領域には、自動的に「持出消耗品名2」という項目が作られます。

このとき同時に、ほかの全ての品名は、1点ずつグループ化された状態になります。例えば「スティックのり(強力接着タイプ)」は「スティックのり(強力接着タイプ)」というグループに属する形になります。

(5)別の品名もグループ化する

続けて、「コピー用紙」類もグループ化しましょう。品名を選択する際には、太字で表示されているグループ名をクリックしても構いません。複数の品名またはグループ名を選択して右クリックし、表示されたメニューから[グループ化]をクリックします。

(6)全てのグループをまとめて閉じる

各グループはアウトライン記号([+]または[-])をクリックすることで詳細を開閉できますが、グループの詳細が展開され、表示されていると分かりづらいため、折りたたんでグループ名のみが表示されるようにします。

任意のグループ名を右クリックし、表示されたメニューの[展開/折りたたみ]から[フィールド全体の折りたたみ]をクリックします。

(7)グループが折りたたまれた

全てのグループ内が折りたまれ、すっきりとした一覧になりました。

(8)グループ名を変更する

グループ名を修正したいセルをクリックし、数式バー上で名前を変更します。ここでは「グループ1」を削除して「シャープペンシル替芯」と入力します。

(9)グループ名が変更された

グループ名が「シャープペンシル替え芯」に変更されました。同様にほかのグループ名も変更しておけば、分かりやすくなります。

時系列のデータをフィルタリングする「タイムライン」

時系列のデータには、「タイムライン」という抽出機能も利用できます。指定された項目から日時のデータを取り出し、直感的な操作で日時の範囲を指定できます。実際に操作してみましょう。

(1)タイムラインを挿入する

[分析]タブの[タイムラインの挿入]をクリックします。

(2)「記入日」を選択する

[タイムラインの挿入]ダイアログボックスが表示されたら「記入日」にチェックマークを付け、[OK]をクリックします。

(3)[時間レベル]を設定する

「記入日」のタイムラインが表示されました。タイムラインでは時間を選択する単位[時間レベル]を、[年][四半期][月][日]から選べます。ここでは[月]を選択します。

(4)抽出したい期間を設定する

タイムライン上に、元のデータにある月の範囲が表示されました。両端をドラッグして開始時期と終了時期を設定します。

(5)指定した期間の集計データが抽出された

6~8月までの3カ月間を選択すると、該当期間だけのデータが抽出され、ピボットテーブルで集計されました。タイムラインで期間を変更すると、すぐにピボットテーブルに反映されます。

高度なデータの加工も自在にできるのがピボットテーブルの強み

ピボットテーブルは軸を変えて集計するだけでなく、データの抽出やグループ化も簡単に行えます。今回の解説で、データを加工する自由度の高さを実感できたでしょうか。元のデータには手を加えないため、初心者でも安心して試せるのもいい点です。

集計・分析ができたら、その結果を部署内や社内で共有したいものですね。次回は、データを視覚化するのに欠かせない「ピボットグラフ」を解説します。

協力メディア

できるネット(https://dekiru.net)

もっと関数のスキルを磨きたい! という人はぜひ大塚商会のExcel教室へ

大塚商会では、企業の人材育成をトータルにサポートする「人材育成支援サービス」をご提供しています。ITやCAD、ビジネススキルなどさまざまなジャンルをご用意しており、もちろんExcelを学ぶコースも充実しています。

Excelを基礎から応用まで体系的に学べる「標準コース」や、関数やマクロなど目的に応じて学べる「目的別コース」をご用意しています。大塚商会の研修施設で定期開催されているオープンコースのほか、貴社の業務に合わせてカスタマイズした企業研修(ご来場または出張)も可能です。

Excel研修の詳細情報を見る

Excelをもっと便利に活用するにはOffice 365をおすすめします

マイクロソフトの「Office 365」は、ExcelやPowerPointをはじめとしたOffice製品、メール、スケジュール管理などをクラウド上で使用できます。非常に利便性の高いシステムを実現することが可能です。

Office 365 について見る