ピボットテーブルを使えば、毎月発生する月例の集計業務を自動化することができます
ピボットテーブルを使えば、リスト形式の「表（CSV）」を簡単に集計することができます。
ピボットテーブルの設定は「フィールド（項目）」をドラッグ操作するだけでとても簡単ですが、毎月同じ操作をするのは大変です、そして操作を間違えてしまう可能性もあります。
そんな時は、一度作成した「ピボットテーブル」をテンプレートとして登録しておき、それを使うことにより複雑な「マクロ」などを使うことなく、毎月の定例業務を自動化することができます。
今回の様な集計は、関数を使っても可能ですが、ピボットテーブルを使うメリットは「顧客」や「支店」などが増減しても数式の変更が必要ないことと、集計方法を簡単に変更できることです。
ピボットテーブルを使って集計します
集計表作成時には何点か注意する点があります。
- シートの「A１」からリストを作成する
- １行目は「項目名（見出し）」にして、データは２行目から
- 「見出し行」は、１行にする
- セルの「連結」はしない
集計する「表」を用意します
「表」をテーブル化します
「表」をテーブル化しておくことで、データ（行）件数が増減しても自動的にテーブルが広がるので「範囲選択」をやり直す必要がありません。
※ 「テーブル化」せずに「直接範囲指定」する場合は、予想される最大数の行を指定しておきます。
ピボットテーブルを作成します
テーブル化した「テーブル１」をピボットテーブル化します。
- 表内のどこか１カ所にカーソルを置きます
- 「挿入」タブを選択します
- 「ピボットテーブル」をクリックします
- ピボットテーブルの作成のダイアログで、テーブルが正しく選択されているのを確認します
- 「OK」をクリックします
「Sheet〇」が追加され、ピボットテーブルが作成されます
集計方法は簡単に変更できます
集計方法は、ドラッグ操作だけで簡単に変更できるので「目的別」に複数作成しておけば便利です。
「顧客名」と「支社名」でクロス集計
「顧客名」と「商品名」でクロス集計
「顧客名」と「日付」で複合集計し、「分類」でクロス集計
ピボットテーブルを「テンプレート」として保存します
作成したピボットテーブルの元のデータになっている「表」の内容を削除して、翌月から使えるようにテンプレート化します。
ファイルを「読み取り専用」として保存し、誤って書き込んでしまうことを防ぎます。
集計が終わって保存する時は、「名前を付けて保存」で名前を変えて保存します。
「表」のデータを削除します
- 「名前ボックス」でテーブル名「テーブル１」を選択します。
- テーブルの「データ」が選択状態になるので「Del」キーを押してデータを削除します。
「行」を削除します
「表」をテーブル化しているので、貼り付ける「行数」に応じてテーブルが広がるので、画面に収まる程度の行数にしておきます。
「読み取り専用」で保存します
テンプレートのファイルを、誤って上書きしないように「読み取り専用」のオプションを設定します。
- 「名前を付けて保存」を選択します
- 名前を付けて保存のダイアログで「ツール」を選択します
- 「全般オプション」を選択
- 全般オプションで「読み取り専用を推奨する」にチェックを入れる
- 「OK」をクリック
※ ブック保護で「最終版にする」にしても「読み取り専用」になります
- 「ファイル」タブを選択します
- 「ブックの保護」を選択します
- プルダウンメニューで「最終版にする」を選択します
「テンプレート」を呼び出して、月次の集計をする
分析したいデータ（当月分）をコピーして、テンプレートの「表」に貼り付けます。
集計が終わって保存する時は「上書き」せずに、「名前を付けて保存」で保存するようにします。
当月分のデータをコピーします
カーソルキーのコマンド（ショートカット）はとても便利な機能です、数百行を選択するのも一瞬でできます。
- コピーする範囲の「左上」で、「 CTRLキー」と「SHIFTキー」と「 ⇩ （下向きのカーソルキー）」をクリックすることで、データ範囲の最下行までが選択されます。
- 続けて「 CTRLキー」と「SHIFTキー」と「 ⇒ （下向きのカーソルキー）」を推すことで「範囲全体」が選択状態になります。
- 選択範囲内で「右クリック」してコピーを選択します
コピーしたデータを、テンプレートの「表」に貼り付けます
「表」をテーブル化しているので、張り付けたデータに合わせて「テーブル」が広がります（※ ピボットテーブルの参照範囲も広がる）。
ピボットテーブルを「更新」します
ピボットテーブルの参照範囲のデータを変更しても、ピボットテーブルは自動的には更新されないので、手動で「更新」します。
- ピボットテーブル内のどこか１カ所を選択します。
- 「ピボットテーブルの分析」タブを選択（※ ピボットテーブル内を選択していないと表示されません）
- 「更新」をクリック
「タイムライン」を使えば、月毎（単月）に分ける必要はありません
今回は月毎（単月）で集計する方法を説明しましたが、ピボットテーブルの「タイムライン」を使えば複数月（累計）で集計することができます。
複数月（累計）で集計するメリットは「前月比」や「前年同月比」の集計ができることです。
今回のテーマは業者支払いなどの単純な事務作業についてですが、「仕訳表」を単月で区切らずに集計すれば高度な分析作業が簡単にできます。
タイムラインを作成
- 「ピボットテーブル分析」タブを選択
- 「タイムラインの挿入」をクリック
タイムラインが作成されました
ドラッグ操作で表示する期間を選択します
タイムラインの「バー」をドラッグ操作することで、表示（集計）する範囲を選択することができます。
「前年同月比」の集計もできます
「行」のボックスに「日付」➡「年」の順番で配置することで、「日付」と「年」の複合集計になり、前年同月比での集計になります。
ピボットテーブルで「前年同月比」の集計資料を作成する - Excelの機能を活用して、事務作業の省力化や経営分析をする
- ピボットテーブルを使えば、毎月発生する月例の集計業務を自動化することができます
