ピボットテーブルは簡単にデータ集計できる便利な機能ですが、定例の業務で毎回フィールド設定行うのは手間でテンションが下がります。
今回は、マクロなど高度な事は一切使わず、ピボットテーブルでの集計を自動化する方法について説明いたします。

効率化できる業務、あまりできない業務

まず、実務でピボットテーブルを使うと効率化しやすい業務と、そうでもない業務について簡単に触れておきます。

仕訳伝票関係は効率化しやすい

前年比や構成比などの指標を追加しないようなデータ集計、例えば、売上明細データから仕訳伝票を起票するための資料を作る。といった経理業務はピボットテーブルで効率化しやすいです。

件数が変動する集計にも便利!

毎回件数が一定ではない得意先別の集計にもピボットテーブルは便利です。
例えば、被請求書の入力データから、仕入先毎に買掛金・未払金の集計表を作る。仕入先別・部門別・勘定科目別の集計表を作るといった時に使えます。

分析資料作成の効率化はあまり期待できない

前年比や構成比などの指標を入れた集計表などは、ピボットテーブルでも多少は効率化できますが、関数を使った集計方法を覚えた方がはるかに効率的です。

また、Zチャートやパレート図のグラフ元データのように単純な集計だけでは作れないものも、ピボットテーブルだけではあまり効率化できません。(使えたほうが便利なのは間違えありませんが。)

エクセルセミナー

ピボットテーブルを自動化するテンプレートの作成

基本的にはピボットテーブルで集計表を作成し、データを空白にしてテンプレートを作ります。
ただし、繰り返し使いやすいように少しだけ工夫する必要があります。

まず、実際にピボットテーブルで集計表を作る

まずは、元になるCSVデータなどをエクセルで開きます。
この時、必ず1行目が項目名(データの見出し)で、2行目から実際のデータにする必要があります。

pivot00

「挿入」の「ピボットテーブル」を実行します。
pivot01

ピボットテーブルの作成から範囲指定をします。
自動で選択される範囲ではなく任意に指定します。毎回データ件数は変動するので、データ件数に依存しないような範囲指定にする必要があります
pivot02
(自動取得される範囲だと行位置が固定されるので、データ件数の変化に対応できません。)

範囲指定する際に、列番号をドラッグして指定します。
pivot03

下図のように列番号のみで範囲指定する事で、データ件数に関係なくピボットテーブルでの集計ができるようになります。
pivot04

この指定方法(列番号での範囲指定)は、1行目が項目名(データの見出し)になっていないとできません。

ピボットテーブルの設定を少し変更します

ピボットテーブルでの集計ができましたら、見栄えを整えたり、ページレイアウトを作ったりします。(これらの設定方法につきましては今回は割愛します。)
pivot05

注意事項:
範囲指定を列番号を指定した場合、下図のようにデータが無い行まで集計表に表示されてしまいます。
必ず最終行・最終列まで目視で確認して下さい。
pivot07

(空白)を集計表に表示させたくない場合は、フィルタで(空白)のチェックを外します。
pivot06

(空白)が集計表から消えました。
pivot08

以上で、ピボットテーブルの設定は完了です。

データを消去して白紙のテンプレートにする

1行目の項目名(データの見出し)を残しデータを消去します。(行削除が望ましい)
pivot09

ピボットテーブルで作成した集計表の再集計表を行います。
pivot10

ピボットテーブルツールから「更新」をクリックするだけです。
pivot11

ピボットテーブルの集計表も白紙になりました。
pivot12

以上でテンプレートの作成は完了です。
普通にファイル名をつけて保存して下さい。この時、わざわざテンプレート形式にする必要はありません。

普通にExcelブック形式で保存して構いません。

テンプレートを誤って上書きしないための一工夫

白紙のテンプレートに間違って上書き保存してしまった・・・。よくある事です。
そのようなミスを未然に防ぐために、ファイルを「読み取り専用」にしておきます。

該当のファイルを右クリックし「プロパティ」を選択します。
pivot13

読み取り専用のチェックをONにするだけです。(チェックを外せば上書き保存できるようになります)
pivot14

※上書き保存はできませんが、名前をつけて保存はできます。

白取

読み取り専用にするという設定を覚えておけば、完成したエクセル資料を誤って上書き保存してしまうというミスもなくなります。
特にファイルサーバーでファイルを共有している場合に有効ですので活用して下さい。

定例集計時はデータをコピー後に更新をクリックするだけ

以上の手順でテンプレートを作っておけば、毎月の定例業務なら、そのテンプレートにCSVファイルのデータを貼り付けて、更新を実行するだけで集計表が完成します。

白紙のテンプレートにCSVファイルからデータを貼り付けます。
pivot09

データを貼り付けました。
pivot00

ピボットテーブルの集計表シートを表示し
pivot12

更新を実行すれば集計表の完成です。
pivot11

ほぼワンクリックで集計ができるので楽です。
pivot05

白取

合計のみで構いませんが集計後は必ず検算して下さい。フィルタで表示しない項目を設定している場合は特に気をつけて下さい。

動画で見る

白紙のテンプレートにCSVファイルのデータを貼り付け、ピボットテーブルを更新する・・・という部分を動画にしました。
イメージが掴みづらかったら御覧ください。

ピボットテーブル自動化の要点

  • データは必ず1行目が項目名(データの見出し)となるようにする
  • データ件数に依存せず集計できるよう、範囲は自動取得ではなく列番号を指定すること
  • (空白)を集計表に表示させたくない場合は、フィルタで(空白)のチェックを外す
  • データを消去して白紙のテンプレートにする
  • 読み取り専用にして誤って白紙テンプレートを上書きしないように
  • 集計後は必ず検算すること

誰でも使えるピボットテーブルですが、ほんの一工夫加える事で、更にデータ集計が簡単便利になります。
ぜひ試してみて下さい。

業務改善サポート