会社の経理部門で働いていた頃、

大量のデータを集計するにあたってはExcelのピボットテーブルを活用していました。

（月次の勘定科目明細や、キャッシュフロー計算書などに活用）

独立した今も、ちょっとした集計に活用しています。

ピボットテーブルは経理部門で必須のスキルだと思っているのですが、

勤めていた当時も現在も、周りであまり活用している方はいないように感じます。

そこで、ピボットテーブルを使うメリット、基本的な使い方を紹介したいと思います。

※経理部員であれば活用したい※

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

複雑な関数を使う必要がない

Excelでは集計を行うための関数がいくつか用意してあります。

Sumifs（条件に合ったデータを合計する関数）、Countifs（条件に合ったデータをカウントする関数）などです。

これらも便利なのですが、引数（関数を実行するために必要な値）をいくつか指定する必要があり、

慣れていない人にとっては時間がかかります。

一方、ピボットテーブルは引数の指定をする必要がなく、

項目をドラッグすることによって集計表作成が可能なので

ハードルは低めです。

瞬時に集計

関数のように長い式を入力する必要がなく、

マウスのドラッグ操作で瞬時に集計してくれる点もメリットです。

また、小計機能や関数を使って集計する場合には、項目を追加したい場合一から作り直さなければいけませんが、

ピボットテーブルは集計の設定を変更するだけで良いので、

作業効率が上がります。

様々な角度から分析できる

ピボットテーブルの場合、行・列を入れ替えたり項目を追加したりできるので様々な角度から分析できます。

例えば、売上集計表であれば顧客別・商品別・地区別等、複数の項目で分析ができます。

ピボットテーブルの注意点

集計可能なデータ作成が必要

ピボットテーブル活用の肝は、いかに集計可能なデータを準備するかにかかっています。

集計可能なデータとは、以下のように統一されたデータのことです。

• 空白行、空白列がない
• 半角、全角が統一されている
• 余計なスペースが入っているセルがない

例えば、商品マスターのデータがあるとして、商品番号に全角と半角が混ざっていたら

その商品は別のものとして集計されています。

もしデータが統一されていないようであれば、

データの置き換え機能、

ASC関数（全角⇒半角に変換）、JIS関数（半角⇒全角に変換）、TRIM関数（余分な空白を取り除く）等の

関数を駆使して統一したデータに変換しましょう。

（手で修正は間違いの元なのでなるべく避けます）

テーブルを活用する

ピボットテーブルの集計元データには、テーブルを指定します。

（データ範囲を選択することも可能ですが、更新があったときに自動で選択されず不便です）

テーブルとは、データを加工をしやすくする機能です。以下のメリットがあります。

• 1行おきに色がつき、デザインも自在なので罫線が不要
• オートフィルター（データ抽出機能）が自動でつく
• 数式が自動コピーされるので数式漏れが発生しない
• 見出し行が固定され、「先頭行の固定」をする必要がない

ピボットテーブルの元データを作る場合に限らず、

何かデータを作るときには、テーブル形式にしておいたほうがその後の作業効率が格段に上がるので

活用をお勧めします。

データ範囲をテーブルに変換するには、データ範囲の任意のセルでCtrlキー＋Tで行うことができます。

ピボットテーブルの作成方法

データ範囲の選択

ピボットテーブルの作成方法は、シンプルです。

集計元のデータのいずれかのセルにカーソルがある状態で、挿入メニュー⇒ピボットテーブルの作成を選択します。

すると、「テーブルまたは範囲を選択」というオプションが表示されますので、

集計元のテーブル又は範囲が選択されていることを確認し、OKをするとピボットテーブルが作成されます。

（下記の例では、「売上リスト」という名前のテーブルをあらかじめ作成しています）

なお作成先は新規のワークシート、既存のワークシートいずれかを選択できます。

行・列・値フィールドに項目をドラッグする

ピボットテーブルを作成すると何も集計されていない空の四角い枠とフィールド（集計したい項目のこと）設定画面が表示されます。

フィールド名には、集計元の先頭の行（見出し）の項目が並んでいます。

これらを列、行、値エリアにドラッグして集計表を作っていくイメージです。

例えば、顧客ごとの売上を行に集計したい場合には、行エリアに顧客名をドラッグさせ、

値エリアに売上金額をドラッグさせます。

これだけで顧客別売上集計表がすぐに作成できます。

もちろん行・列両方に項目をそれぞれ設定することも可能です。（下記は商品分類別・地区別売上高の例）

これらを小計機能、関数でやろうとするとかなり時間がかかりますが、

このようにピボットテーブルはドラッグ1つで自在にアレンジできます。

ピボットテーブルの基本操作

ここでは、よく使うピボットテーブルの基本操作を説明します。

大項目、小項目を追加したい場合

ピボットテーブルでは大きな項目の下に小さな項目、といった形で項目を追加することができます。

例えば、大項目として商品分類を、その下に小項目として商品名を行で集計したい場合には、行エリアの一番上に商品分類、その下に商品名を追加します。

分析を階層的に行いたい場合に便利です。

集計元のデータを修正したい場合

集計元のデータを更新しても、ピボットテーブルには自動的には反映されません。

集計元のデータを更新した場合には、ピボットテーブルにカーソルがある状態で右クリック⇒更新を選択すれば、ピボットテーブルが最新の状態となります。

（なお集計元のデータが単なるデータ範囲であると、範囲を選択し直す必要がありますが、テーブルであれば自動で選択してくれるのでその必要はありません）

集計値の明細を取得したい場合

ピボットテーブルの値フィールドをクリックすると、その金額の明細を別シートに作成することができます。

例えば、商品ごとの売上金額の集計値をダブルクリックすると、集計元のデータから該当するデータだけを抽出して一覧にしてくれます。

より詳細なデータを確認したいときに便利です。

集計フィールドを追加したい場合

集計元のデータに、集計したい項目がない場合もあります。

そんなときには、手動で集計フィールドを設定できる機能が便利です。

例えば、売上金額が税抜の場合に消費税を別途表示させたいときです。

手動でフィールドを設定するには、

ピボットテーブルが選択されている状態で「フィールド/項目/セット」メニュー⇒「集計フィールド」をクリックします。

（下記はMacのExcelです。Windowsは多少異なっていますのでご了承ください）

集計フィールドの設定画面が出てきます。

名前ボックスに追加するフィールド名、

数式ボックスに表示させたい値の数式を入力します。

今回、既存のフィールドから売上金額を選択し、10％をかけて消費税を計算しています。

これで、既存の集計フィールドと並んで手動で設定したフィールド（消費税）を隣に追加できました。

手動で集計したい項目が後から出てきた、

ということはよくあることかと思いますので是非活用してみてください。

まとめ

経理部が是非活用したいExcelのピボットテーブルのメリットと基本的な使い方を紹介しました。

利用されている方が依然少ない中で、

フルに活用できれば差をつけられるはずです。

編集後記

土日は、セミナー資料の準備など。

 

最近のあたらしいこと

Staubの鍋

Zwillingのキッチンバサミ

おうち時間を充実させたく、色々購入中です。

他に食器、植物、枕、タオル、など考えています。

ついでに場所（引っ越し）も検討中。