Excel業務カイゼンブログ

Excelには様々な集計機能がありますが、その中でも最も強力なピボットテーブル機能を、操作方法を交えてわかりやすく紹介します。 ピボットテーブルは、一見難しそうに感じるかもしれませんが、実はとても簡単です。

実際に操作するファイルはこちらからダウンロードできます。

ピボットテーブルの元データ

ピボットテーブルに使用するデータは、一覧表の１行目に項目名が入ったデータベース形式であればOKです。 データの大きさはExcelで扱える大きさであれば10万行のデータでも集計できます。

ピボットテーブルの使い方

ダウンロードファイルをご覧ください。

このデータは、２行目から販売日、商品、部署、金額の売上を記録した一覧表です。一覧表の1行目にそれぞれの項目名が記録されています。

商品の価格は日々の為替などの影響で変動するので、販売金額が一件ごと違います。全部で１年間分の797件の売上データです。

ピボットテーブルを使う時は、元データのどこかをクリックして、［挿入］タブの中のピボットテーブルをクリックします。

テーブル範囲にすでに元データの範囲が設定されています。 ここで、[OK]ボタンをクリックします。

ピボットテーブルが新しいシートに作成されました。

ここからピボットテーブルでどのように集計するか設定していきます。

［ピボットテーブルのフィールド］作業ウィンドウ

ピボットテーブルを作成すると、シートの右側に［ピボットテーブルのフィールド］の作業ウィンドウが表示されます。

この作業ウィンドウを使ってピボットテーブルを設定していきます。

上には、項目名の一覧が表示されています。

下には、フィルター、列、行、値の欄があります。

上の項目名を、下の4つのエリアにドラッグして配置することで集計方法を設定します。

値の集計

今回は、売上金額を集計しますので、［金額］項目が値の集計対象になります。

［ピボットテーブルのフィールド］作業ウィンドウの中の項目名［金額］をドラッグして、下の［値］欄にドラッグします。

すると、すべての売上の合計金額が集計されます。

集計するものが数値の場合は、合計だけではなく、データ個数や平均値や最大値なども求めることができます。

文字データの場合はデータ個数のみが集計できます。

売上は数値なので、様々な集計ができます。

求まった合計値を右クリックして、［値の集計方法］の中の［平均］をクリックすると平均値に変わります。

合計だった集計値が平均になりました。

合計値に戻しましょう。

項目の設定

今はすべての金額の集計しか求まっていませんが、商品ごとや、日付ごとにも集計できます。

例えば、商品ごとに縦（行方向）に集計したいときは、［ピボットテーブルのフィールド］作業ウィンドウの中の項目名［商品］をドラッグして、下の［行］欄にドラッグします。

これだけで商品ごとの売上が一目で見ることができる表ができました。

さらに、部署ごとに横（列方向）に集計したいときは、［ピボットテーブルのフィールド］作業ウィンドウの中の項目名［部署］をドラッグして、下の［列］欄にドラッグします。

商品ごとに行に、さらに部署ごとに列にまとまっている一覧を表示できました。

項目の入れ替え

例えば、部署ごとに商品を並べてみたいという時は、［行］または［列］に両方の項目を入れると良いでしょう。

［列］欄にある［部署］を［行］欄の［商品］の下にドラッグします。

商品ごとに部署を縦に並べた表になりました。

商品ごとに小計が計算されています。

このように行や列に列に複数の項目を配置することもできます。

もしも、部署ごとに商品を並べたいときは、商品と部署の上下の順番をドラッグで入れ替えればできます。

項目の削除

もしも、集計の項目がいらない場合は、その項目を、［ピボットテーブルのフィールド］作業ウィンドウの外にドラッグすれば削除できます。

部署の項目はいらなくて、商品だけで集計します。

［部署］を［ピボットテーブルのフィールド］作業ウィンドウの外、ワークシート領域までドラッグします。

するとマウスポインタに×印が表示されますので、マウスを離します。

部署がなくなり、商品ごとだけの集計表になりました。

集計項目の並べ替えとフィルター

ピボットテーブルの集計結果を並べ替えるときは、並べたい値を右クリックして［並べ替え］の中の［昇順］または［降順］で並べ替えます。

商品ごとの売上の大きい順に並べるときは、売上金額のセルを右クリックして、［並べ替え］の中の［降順］をクリックします。

売上の大きい順に商品が並びました。

集計したくない項目がある場合は、フィルターをすることができます。

商品の中で［サーバー］を表示したくない場合は、項目［商品］の右側の三角をクリックし、［サーバー］のチェックを外すことで集計の対象から外すことができます。

[サーバー］が集計の対象から外れ、表示されなくなりました。

日付項目の扱い

ピボットテーブルで日付を集計する場合は、少し特殊な集計になります。

日付ごとに集計する前に、項目［商品］を［列］欄に移動しましょう。

横方向に商品ごとの集計になりました。

［販売日］を［行］欄にドラッグしましょう。

日付データが、年ごとの集計となり、縦方向に並びます。

年には＋マークが表示されていて、この＋マークをクリックすると、年の詳細が表示され、今度は四半期ごとに集計されていることがわかります。

［ピボットテーブルのフィールド］作業ウィンドウの［行］を確認すると、［年］、［四半期］、［販売日］で集計されていることがわかります。

日付データを集計すると自動的に年や四半期でも集計されることがあります。

もしも四半期が必要ない場合は、［ピボットテーブルのフィールド］作業ウィンドウの［行］から［四半期］をドラッグして削除すればよいです。

［四半期］を［ピボットテーブルのフィールド］作業ウィンドウの外、ワークシート領域までドラッグしましょう。

四半期ごとの集計がなくなりました。

※元データが同じ年のみのデータの場合は、［年］、［四半期］ははじめから表示されません。

今回のデータは最後の一行のみ2018年でそれより前はすべて2017年のデータです。

フィルター欄の使い方

［ピボットテーブルのフィールド］作業ウィンドウの［フィルター］欄に項目を設定することで、その項目を含まない集計を行うことができます。

［部署］を［ピボットテーブルのフィールド］作業ウィンドウの［フィルター］欄にドラッグしてみましょう。

フィルターに［部署］が設定されました。

この状態で、フィルターのマークをクリックすると、部署名一覧が表示されるので、その中から集計したい部署、今回は［新宿店］をクリックし［OK］ボタンをクリックしましょう。

新宿店のみの集計表になりました。

このフィルターは、複数項目を選ぶことができます。

フィルターのマークをクリックします。

［複数のアイテムを選択］のチェックを入れると、それぞれの項目にチェックができるようになるので、例えば［すべて］のチェックを入れた後、［新宿店］のチェックを外して、［OK］ボタンをクリックすれば、新宿店以外の集計になります。

［部署］を［ピボットテーブルのフィールド］作業ウィンドウの外、ワークシート領域までドラッグし、部署のフィルターを解除しましょう。

ピボットテーブルのメリット

今回、ピボットテーブルの基本的な使い方を紹介しました。

ピボットテーブルでは、これだけの大きなデータがすばやく集計できること、縦横に知りたいと思った集計を手軽に実現できることがおわかりいただけたと思います。

最後にピボットテーブルのメリットを簡単にまとめます。

大量の一覧データを見ただけでは、物事の本質が見えてこないのです。

そこで、例えば月ごとにデータを集計することで、毎月のデータの傾向を掴むことができます。

さらに商品ごと、販売部署ごとに集計すれば、また、合計値ではなく平均値、最大値ではどうか、様々な集計が一瞬でできます。

これだけのスピードで分析ができるので、気づけないことに気付けるチャンスが増えます。

単なる集計をするだけでも優秀ですが、簡単に作り替えられるのもピボットテーブルの大きなメリットなのです。

このような縦横集計のことをクロス集計と呼びます。