現場で役立つエクセル操作(その2)

 前回は、集計が簡単に出来る機能として、エクセルの機能である[並び替え][集計][グループ化]の3つを利用しました。

 集計はひとつの項目だけしか基準に出来ません。しかし、複数の基準で集計というニーズのほうが多いと思いますので、今回は集計機能の第2弾として[ピボットテーブル]をご紹介します。
 ピボットテーブルとは、直訳すると「回転させる表」という意味ですが、一般にクロス集計表と呼ばれます。「表を回転させるって?」と疑問にお思いの方も、使ってみるとその意味を理解していただけるでしょう。

 

 まずは、集計対象の基準になる項目が2つある表を考えてみましょう。例えば、日付と支払先、支払い金額が入った表をイメージしてください。作りたい表は支払先別の月別合計金額表です。

 元の表
 日付 支払先 支払金額
 1/ 1 A社   30,000
 1/11 A社   10,000
 1/21 B社   20,000
 2/ 1 C社   40,000

 集計表 1月  2月
 A社  40,000      0
 B社  20,000      0
 C社       0 40,000

 まず、元になる表を範囲指定します。自動的に指定されますが、うまくいかない場合もありますのでご注意ください。この際、必ず各項目名も含むようにします。
 次に[データー]?[ピボットテーブルとピボットグラフレポート]を選びます。するといくつかの画面が出てきますが、次へ次へと押してください。完了までおすと、新しいシートが挿入され、枠と項目名が表示されます。今回の場合は列に月日、行に支払先をドラッグします。最後に枠の真ん中に金額を入れると、直ちに表が作成されます。

 ここまでの作業では、「日付で集計されているが月ごとではない」という状態です。ここからが、ピボットテーブルの威力の発揮どころ。日付の項目名をク リックした後で表示されているピボットテーブルのツールバーからピボットテーブル▼の▼をクリックしましょう。メニューがいくつか出てきますが、[グルー プと詳細の表示]?[グループ化]をクリックします。すると日付のためのグループが出てきますので、中から月を選びましょう。表が月別に集計されることが確認できるはずです。このように元データをさわることなく集計できるのが、ピボットテーブルの長所です。

 たとえば、日付の項目をドラッグして支払先側に移動してみましょう。支払先別でさらに月別の表に変わります。このように、複数の項目を階層的に集計することも可能です。また、日付の項目をドラッグして項目一覧に戻すと、集計表が支払先別のみで集計しなおされます。(前回の集計機能でも実現できる形ですね。)さらに三次元的な集計も出来ますので、お試しください。

 さて、金額に関して、支払回数を知りたい場合なら「個数」ですね。これも簡単にできます。合計/金額の部分を右クリックし、出てきたメニューから[フィール
ドの設定]をクリックしましょう。データの個数や平均などいろいろなものが選べます。さらにオプションボタンを押すと、全体に対する比率など%であらわすことも可能。いろいろな表現ができることがわかります。
 また、表示形式を使えば、通常と同様にセル書式も変更できます。見栄えを整えることも簡単に出来るのです。

 最後にひとつだけ、最重要ポイントを。元の表を修正したあとにピボットテーブルのメニューから更新を押すと、たちどころに自動修正されます。当たり前のように感じるかもしれませんが、最重要というのにはそれなりの意味があります。たとえば、毎月・毎年、同じような集計作業をしていたとしましょう。データを用いたピボットテーブルを作り、そのファイルのデータを書き換えていけば、集計作業は更新ボタン一個だけ。つまり、集計機能つきのソフトを作ったのと同じです。経理関係や在庫管理などをなさっている方であれば、この便利さを実感していただけるでしょう。
 
 私自身、このピボットテーブルがエクセルの中で一番便利な機能だと思います。しかし、名前と手順から敬遠されがちなのも事実です。今回の例題をきっかけにぜひ、使っていただきたい機能です。お近くに集計作業でお困りの方がいらっしゃたら、この機能を教えて差し上げてください。