意外と簡単！？エクセルのピボットテーブルでデータ集計-入門編

2016年4月8日

経理・会計に携わるのであれば、ピボットテーブルを使いこなすだけで、作業効率が２倍・３倍になることも珍しくありません。

極端な場合、従来手作業で丸２日かかっていた作業が、ピボットテーブルを使っただけで20分で終わるようになった、という事例もあります。

ピボットテーブルは、イメージほど難易度も高くない割に、作業効率に与える影響はとても大きいです。
この機会に、ぜひ、ピボットテーブルを使えるようになってください！

ピボットテーブルとは？

ピボットテーブルとは、取引データなどの明細データを指定した項目で分類・集計する機能です。

ピボットテーブルを使うことで、

• 仕訳日記帳→試算表、決算書
• 売上明細→相手先別売掛金管理表
• 仕入明細→相手先別支払予定表
• 在庫の入出庫明細→在庫推移表
• 前期比較

などを短時間で効率的に集計作業ができます。

ピボットテーブルが理解できない理由とは？

残念ながら、エクセル初心者の多くが、ピボットテーブルをマスターできずに挫折してしまいます。

その原因は、
「操作方法しか覚えておらず、意味が理解できていない」
というところにあります。

操作手順だけ覚えてもダメ

ピボットテーブルを使うために、どうマウスを動かせばいいかはわかった。

でも、なぜそうするのか？
そもそも、ピボットテーブルをなぜ使うのかが、理解できていない。

だから、操作手順を覚えられないし、実務で使えないんですね。

ピボットテーブル集計の意味を覚えよう

ピボットテーブルのように、今まで触れたことがない機能を勉強する場合に、

一番いいと思うのが、
「エクセルが行っている作業を１ステップ１ステップ追ってみること」

ピボットテーブル集計の際に、エクセルが何をしているのかを１ステップずつ追ってみることで、

• ピボットテーブルがどういう機能なのか？
• ピボットテーブルを使う際の注意点がどこか？

が理解しやすくなります。

そこで、このページでは、

• 「ピボットテーブル」で操作をするとどうなるか
• ピボットテーブル集計時に、エクセルは何をしているのか？

を説明していきます。

特に、ピボットテーブルの「意味がわからない」という方は、ぜひ「手作業」で再現するとどうやるかもじっくり読んでみてください。

ピボットテーブルの基本型（１つの項目で集計）

経費出納帳（≒お小遣い帳）があります。

ピボットテーブルを使って、この経費出納帳から「勘定科目ごとの」支払額を集計します。

ピボットテーブルで勘定科目ごとの支払額を集計する

最初に、
エクセルのピボットテーブルの機能を使って、集計をしてみます。

１．表全体を選択する

２．ピボットテーブルを挿入

メニューから、
「挿入」→「ピボットテーブル」を選択します

「ピボットテーブルの作成」という
ウィンドウが出るので「OK」を押します。

別シートに、
ピボットテーブルの「枠」が作られます。

３．「金額」を「値」欄に移動

先ほどの手順までは、下準備。
ここからが、ピボットテーブルの本番です。

集計・区分したい「金額」欄を「値」欄にドラッグします。

表全体の金額「合計」が表示されます。

４．「勘定科目」を「行」欄に移動

勘定科目を行欄に移動します。

「金額」が勘定科目ごとに分類されます。

これで、
勘定科目別に、支払額を集計することができました。

ピボットテーブル集計時にエクセルがしていること

ピボットテーブルが理解できない場合。
理解するための一番の早道は、ピボットテーブルの集計作業のイメージを頭の中に作ることです。

そのイメージを作るために、エクセルがピボットテーブル集計でしている処理を１ステップごとになぞってみましょう。

まず、集計対象列がどこかを考えます。

今回の集計対象は「金額」列なのでC列。
金額を区分する基準は「勘定科目」列なのでB列です。

この2つの列を見て金額を集計・区分していきます。

１行目はタイトル行なので、
2行目（C2セル、B2セル）からスタートします。

2行目（C2セル、B2セル）

金額（C2セル）は「2,400円」。
勘定科目（B2セル）は「会議費」です。

集計用欄に「会議費」の行を作り「2,400」円と転記します。

3行目（C3セル、B3セル）

金額（C3セル）は「1,290円」。
勘定科目（B3セル）は「会議費」です。

会議費の欄は既にあるので、
集計用欄の「会議費」行に「＋1,290」円と転記します。

※表の見やすさを考えてB3セルを空欄にしてしまうと、エクセルは、この1,290円がどの費目に該当するのか判断できず、ピボットテーブル集計がおかしくなってしまいます。B3セルに「会議費」と入れておくことが重要です。

4行目（C4セル、B4セル）

金額（C4セル）は「1,100円」。
勘定科目（B4セル）は「旅費交通費」です。

旅費交通費の集計行は、まだないので、
集計用欄に「旅費交通費」の行を作り「1,100」円と転記します。

5行目（C5セル、B5セル）

金額（C5セル）は「1,010円」。
勘定科目（B5セル）は「旅費交通費」です。

旅費交通費の欄は既にあるので、
集計用欄の「旅費交通費」欄に「＋1,010」円と転記します。

6行目（C6セル、B6セル）

金額（C6セル）は「4,383円」。
勘定科目（B6セル）は「書籍費」です。

書籍費の集計行は、まだないので、
集計用欄に「書籍費」の行を作り「4,383」円と転記します。

こういう感じで１件１件埋めていき合計を取ると、次のようになります。

これが、ピボットテーブル集計のイメージです。

ピボットテーブルの集計項目を変更する

今度は、勘定科目別ではなく、
日付ごとの支払額を、ピボットテーブルで集計します。

ピボットテーブルで日付ごとの支払額を集計する

先ほど作成した勘定科目の集計をした際のピボットテーブルがあるので、

このピボットテーブルに対して、追加で操作を行っていきたいと思います。

１．「勘定科目」を「行」欄から削除する

行欄にある「勘定科目」を元の場所にドラッグします。

勘定科目別に集計がされなくなり、合計金額だけが表示されます。

２．「日付」を「行」欄に移動する

日付を行欄に移動します。

これで日付別の集計ができました。

ピボットテーブル集計時にエクセルがしていること

ピボットテーブルが理解できない場合。
理解するための一番の早道は、ピボットテーブルの集計作業のイメージを頭の中に作ることです。

そのイメージを作るために、エクセルがピボットテーブル集計でしている処理を１ステップごとになぞってみましょう。

まず、集計対象列がどこかを考えます。

今回の集計対象は「金額」列なのでC列。
金額を区分する基準は「日付」列なのでA列です。

この2つの列を見て金額を集計・区分していきます。

１行目はタイトル行なので、
2行目（C2セル、A2セル）からスタートします。

2行目（C2セル、A2セル）

金額（C2セル）は「2,400円」。
日付（A2セル）は「2016/3/1」です。

集計用欄に「2016/3/1」の行を作り「2,400」円と転記します。

3行目～7行目

3行目～7行目までは、
全て日付（A列）が「2016/3/1」なので、

集計用欄の「2016/3/1」行に追記していきます。

※表の見やすさを考えてA3～A7セルを空欄にしてしまうと、エクセルは、これらの行がどの日の分かがわからないため、ピボットテーブル集計がおかしくなります。A3～A7セルに「2016/3/1」と入れておくことが重要です。

8行目（C8セル、A8セル）

金額（C8セル）は「1,050円」。
日付（C8セル）は「2016/3/2」です。

2016/3/2の集計行は、まだないので、
集計用欄に「2016/3/2」の行を作り「1,050」円と転記します。

9行目（C9セル、A9セル）

金額（C9セル）は「3,400円」。
日付（A9セル）は「2016/3/2」です。

2016/3/2の欄は既にあるので、
集計用欄の「2016/3/2」欄に「＋3,400」円と転記します。

こういう感じで１件１件埋めていき、合計を取ると、
最終的には、次のような感じになります。

ピボットテーブルを使うメリットは？

手作業だと、データ・集計項目が変わるたびに集計作業が必要になります。

今回は集計すべき日が3日しかありませんでしたので、それほど手間ではありませんでしたが、

これを30日分、365日分の集計をしないといけないとしたら、ものすごい手間がかかりますよね？

一方で、いったんピボットテーブルを作ってしまえば、集計項目の変更も非常に簡単です。
このように、同じ表をいろいろな切り口で見たいときに、ピボットテーブルの真価が発揮されます。

ピボットテーブルを使うときの注意点

ピボットテーブルを使うためには、
表を作るときに、事前に準備をしておくべきことがあります。

全ての列に列タイトルをつける

A列～G列までを使って、ピボットテーブルを作る場合には、A列～G列全てに「列タイトル」をつけておく必要があります。
仮に、F列はピボットテーブルで使わない、という場合でも、F列に列タイトルをつけておかないとエラーが出て、ピボットテーブルが作れません。

たとえば、弥生会計から総勘定元帳データをエクセルにエクスポートすると、次のような列タイトルがつきます。

この表を使ってピボットテーブルを作る場合、
例えば、12行目（「日付」「伝票No」などと書いてある行）をタイトル行としてピボットテーブルを作ろうとすると、I列・N列・P列~S列が空欄になっているのでうまくいきません。

これは非表示にした場合も同じです。
I列、N列など不用な行を「非表示」にした場合でも、列タイトルを入力していないとエラーになります。

こういう表でピボットテーブル集計をしたい、という場合には、次のどちらかの方法を使います。

不用な列を削除する＋必要な列には列タイトルをつける

ピボットテーブル集計に不要な列は思い切って削除します。
一方で、列タイトルがないけれど集計に使う列については、列タイトルをつけましょう。

エクセルの使い方に慣れていない場合は、こちらの方法のほうがわかりやすいのでおすすめです。
ただ、定期的に作業をする場合には、列の削除をしてしまうと表の作成が大変になる場合もあります。

すべての列に列タイトルをつける

使わない列も含め、すべての列に列タイトルを入れる方法です。

頻繁に内容の更新・追加が発生する場合には、こちらの方法が楽です。
例えば、毎月、会計ソフトから１か月分のデータをダウンロードしてデータを足していくことが想定されているのであれば、こちらの方法を使った方が楽です。

上のセルと同じ内容でも入力を省略しない

見ばえの良い表を作るために、
上のセルと同じ内容が連続する場合に入力を省略するときがあります。

でも、ピボットテーブルで集計することを前提とする場合には、セルの入力内容は省略しないで入力をする必要があります。

このルールを守らないと、集計結果に不具合が生じます。

セルの結合をしない

ピボットテーブルで集計することを前提とする場合には、セルの結合をしてはいけません。

セルの結合をしてしまうと、結合セルの中の左上隅のセルにのみ値が入力されている扱いになってしまいます。

結果、先ほどの「入力を省略した場合」と同じ不具合が生じます。

金額列はひとつだけにする

集計対象となる「金額」は、表の中で１列だけになるようにすると、ピボットテーブルの集計がわかりやすくなります。

例えば、総勘定元帳出力時に借方金額と貸方金額があるのであれば、それを１つの列（例えば差引）にまとめると便利です。

分類したい「切り口」ごとに列を作る

ピボットテーブルで集計をするためには、分類したい「切り口」ごとに列を作るのが重要です。

例えば、ピボットテーブルの集計でよく使う切り口として「年度」があります。

年度別にデータを集計したい、という場合には、
「年度」列を作っておくことで、集計が簡単にできるようになります。

元の表を変更してもピボットテーブルには※自動反映しない※

元の表を変更しても、ピボットテーブルの内容は自動で変わりません。

変更内容に応じて、手動での処理が必要です。

元表の「値変更」を反映させる方法

ピボットテーブル内のどこかのセルを選択した後に、リボンから「分析」→「更新」を押すことで、変更内容が反映されます。

元表に対する列・行の「挿入」を反映させる方法

表に新しく列・行を挿入した場合も、先ほどと同じように、
ピボットテーブル内のどこかのセルを選択した後に、リボンから「分析」→「更新」を押すことで、変更内容が反映されます。

元表の「外側」に列・行を追加した内容を反映させる方法

ピボットテーブルの集計元となる表に対して、

• 「右」に列を加えた
• 「下」に新たなデータを追加した

場合には、元々の集計元で指定された場所から修正がはみでてしまうため、「更新」処理をしてもピボットテーブルに反映させません。
こういう場合には「データソースの変更」をします。

ピボットテーブル内のどこかのセルを選択した後に、
リボンから「分析」→「データソースの変更」を押し、
再度、ピボットテーブルの「集計元セル」を選択し直します。