Excelのテーブルから特定のデータを抽出し、集計する手段の一つである「ピボットテーブル」。実例を基に、データの取りまとめ方を解説する。
第3回までは組織全体で使える「Microsoft Excel」(以下、Excel)の活用法を紹介しました。第4回となる本稿では、特に情報の取りまとめが必要なマネジャー層や管理職で使える、ピボットテーブルを使ったデータの取りまとめ方を紹介します。
部門のマネジャーがExcelの定型フォーマットを準備してメンバーに入力を求めるシーンは、業務でよく見かける光景です。例えば、図1のように総務部が部内のセキュリティ対策の状況を取りまとめるために総務部の各課に依頼して、部門メンバーに状況を入力してもらうといった場合です。
図1のような情報を図2のように集計する場合、手作業では情報の転記ミスが起こりやすく、作業者の負荷も高まります。最近は、業務の効率化やデータ精度の向上の観点から「Microsoft Power Apps」などのフォームベースアプリを使うケースもあります。これをExcelの代替ツールとして利用することもできますが、集計単位ごとにフォームを作成する必要があり、その分工数も膨らみがちです。
人手による作業を減らし、集計処理の手間を削減する手段としてExcelの「ピボットテーブル」」があります。図1、図2の例を基に、ピボットテーブルを使ったデータの取りまとめ方を見ていきましょう。
「ピボット」とは、Excelの表から特定のデータを取り出す方法です。それをテーブル上に加工したものがピボットテーブルです。
ピボットテーブルを作成するには、まず図1のようなデータを作成して、ショートカット「Ctrl + T」(Windowsの場合)を押してテーブル化します。次に、テーブルのセルを選択して「挿入」タブの「おすすめピボットテーブル」をクリックします。
おすすめピボットテーブル機能は、データの内容に応じておすすめのピボットテーブルを提案します。図1のように数値がなく総量も少ないデータの場合は何も提案がないケースもありますが、まずは確認してみるといいでしょう。残念ながら図1の例では提案がなかったので、「新しいワークシート」ボタンをクリックしてピボットテーブルを新たに作成します。
新しいワークシートにピボットテーブルを作成するとオブジェクトができますが、「ピボットテーブルレポートを操作するにはこの領域をクリックしてください」と表示され、何も表示されません。ピボットテーブルにデータを表示させるには、作成されたオブジェクトをクリックしてピボットテーブルを完成させる必要があります。
右側にピボットテーブルのフィールドが表示されたら、図6のように上部にある項目を「行」と「値」にドラッグします。行には縦方向に並べたい内容を、値には行に対応する数値情報としたい項目を設定します。すると列の箇所に「Σ」が表示されます。このΣは合計を列に表示するという内容です。
行と値を設定すると、ピボットテーブルに値が表示されます。1つのテーブルからデータを集計するにはこの方法が簡単です。これで総務課のデータのとりまとめが完了しました(図7)。
次に、複数の課にまたがる情報のまとめ方です。複数のExcelファイル(ブック)やシートのデータを取りまとめて1つのピボットを作りたい場合、「ピボットテーブル ウィザード」を使います。ピボットテーブル ウィザードで図1のデータを基に図 2のように集計する方法を見てみましょう。
まず対象のExcelファイルを開き、「Alt」「D」「P」の順にキーを押下すると、ピボットテーブル ウィザードが表示されます。「複数のワークシート範囲」を選択し、「次へ」をクリックします。
続いて表のまとまりであるページフィールドの作成方法を指定します。ページはワークシートそのものを指しますが、「指定」にすることでワークシートごとに名前を設定できるようになります。
続いてワークシートを指定します。まずは「範囲」で集計したい範囲を選択します。ここで選択する列数は全てのワークシートで同じとする必要があるので注意しましょう。「ページ フィールド数」の項目を1以上に設定すると、そのワークシートに設定できる属性を作成できます。フィルターなどに活用できるので、1以上を設定して一意になる値を入力します。
複数選択すると図11のように選択範囲に内容が増えていきます。図11では絶対値による範囲を設定していますが、テーブル名も指定できます。ここで選んだ列はピボットに全て表示される仕様となっています。列を選びたい場合はこのタイミングで選びましょう。
最後にピボットの配置位置を決定すれば設定終了です。
この手順によって、複数のデータから特定の情報を抽出し、表にまとめることが可能になります。課の担当者は収集したデータを課別に集計する必要はなく、収集したデータをそのまま部門の担当者に渡すだけで済みます。事前にファイルを共有するだけでデータの取りまとめができ、全体の作業効率を上げることが可能です。
なお、「Microsoft 365 Apps」を利用している場合、おすすめのピボットテーブル以外に、「データ分析」機能も利用できます。この機能はMicrosoft 365 Appsのホームリボンにあり、AI(人工知能)が最適な分析観点を提示します。
また、おすすめのピボットテーブルとは異なり、複数の分析視点を提案します。ピボットテーブルだけではなくピボットグラフも提示するため、どのように表現するかが決まっていないデータの場合は、利用してみるといいでしょう。
このように、情報を俯瞰(ふかん)する方法はExcel以外にも「Microsoft Power BI」などさまざまな手法があります。情報のリアルタイム性や準備のしやすさなどを指標にどの方法が最適なのかを判断して選択するといいでしょう。今回のようにまだまだExcelによって効率化できる場面は多くあります。標準機能や仕組みを覚えて、Excelの活用に挑戦してみてください。
Copyright © ITmedia, Inc. All Rights Reserved.
製品カタログや技術資料、導入事例など、IT導入の課題解決に役立つ資料を簡単に入手できます。