月末の集計作業や上司から急に言われた分析作業に追われていませんか？
「品番や日付ごとに手作業でデータを集計し、転記ミスで作り直し…」
その手間のほとんどは、元データを集計表の形に整える作業に費やされいるはずです。
毎回同じような手間のかかる単純作業に悩まさた経験を持つ実務担当者は多いのではないでしょうか？
そこで、覚えておいて損がないのがエクセルのピボットテーブル機能です。
Excelの「ピボットテーブル」は、面倒な集計作業を自動化して、誰でも簡単に在庫状況を分析できるとても優れた機能です。
ピボットテーブルに難しいイメージ、苦手意識を持つ方は多いです。
実は、ピボットテーブルはとても簡単です。
関数やマクロの知識がなくても、マウス操作だけでたった4か所を設定するだけで瞬時に集計表やグラフを作成できます。
この記事を読めば、ピボットテーブルの設定方法が分かるので月次の集計作業にかかっていた時間を大幅に削減できます。
さらに品番別・期間別・グループ別など、ピボットテーブルを使えば、様々な角度から在庫分析ができるようになります。欠品や過剰在庫の防止につなげることができます。
この記事の執筆者である在庫管理アドバイザーの岡本は在庫管理の専門家で500件以上の相談実績を持っています。
相談やコンサルティングの場において、課題発見や在庫分析にピボットテーブルは欠かせません。
だからこそ、強くお勧めします。
今回は、練習用のエクセルファイルをご用意しました！このファイルをダウンロードして、実際に手順に沿って記事を読み進めながら練習してみてください。
目次
なぜピボットテーブルが必須スキルなのか？
エクセルを使う人、特に管理者やデータ分析を行いたい人全員に覚えてもらいたいのがピボットテーブルです。
現場レベルの分析は、難しいことは不要で、ピボットテーブルで全て可能です。
複雑な関数が一切不要｜ピボットテーブルは難しくない
通常、データを集計するにはSUMIFやCOUNTIF、VLOOKUPといった関数を組み合わせて使用しなければいけません。
しかし、ピボットテーブルなら、集計したい項目をマウスでドラッグ＆ドロップするだけで、Excelが自動ですべて計算してくれます。一見、ハードルが高いと思われがちなピボットテーブルですが、実は基本的な使い方を覚えれば非常に簡単です。
ピボットテーブルの構成要素はたった４つだけ！
ピボットテーブルの4つのみで構成されています。
行: 表の「行」方向に見たい項目（例：日付）
列: 表の「列」方向に見たい項目（例：出庫先）
値: 集計したい数値データ（例：数）
フィルタ：表全体を特定の条件で絞り込みたい項目（例：品番、入出庫）
「品番A0001の出庫先別の日付単位の出庫数を知りたい」場合は、下記のように設定します。
フィルターの品番を切り替えれば、別の品番の出庫数がすぐに分かります。
たったこれだけで、知りたい事が簡単に集計できます。
大量のデータを一瞬で集計できる
何万行もあるような膨大なデータでも、ピボットテーブルは瞬時に集計表を作成します。
手作業や関数でありがちな計算ミスや、参照範囲の間違いといったヒューマンエラーの心配もありません。
集計や分析の切り口（行・列）を自由自在に変更可能
作成した集計表は、後からでも簡単に見せ方を変えられます。
例えば、
- 支店別の売上表を、商品カテゴリ別のクロス集計に変更する
- 担当者別の実績も追加して
- 年別の売上データの比較
上司からの急なリクエストにも、項目を入れ替えるだけで瞬時に対応可能。あらゆる角度からデータを分析し、隠れた気づきを発見する手助けをしてくれます。
ピボットテーブルを作成する手順
それでは、実際にピボットテーブルの作成方法を解説します。
練習用のサンプルファイルをダウンロードして、実際にエクセルを操作しながら読み進めてください。
ピボットテーブルを作るためのデータの準備
ピボットテーブルを作成するための元データは、次の３つのルールに従います。
- １行１データのテーブル形式のデータであること
- 先頭行にタイトル行（見出し）があること
- 結合セルがないこと
自分で作る場合は、サンプルファイルのようなイメージで元データを準備しましょう。
「挿入」タブからピボットテーブルを作成する
まず、ピボットテーブルを作成したいデータを選択した状態で、メニューバーの「挿入」→「ピボットテーブル」をクリックし、「テーブルまたは範囲から」を選択します。
「テーブルまたは範囲からのピボットテーブル」という画面が表示されます。
既にピボットテーブルにしたいデータの範囲は選択されています。（今回は「テーブル１」）
次に、「ピボットテーブルを配置する場所を選択してください」から「新規ワークシート」を選びましょう。
※既存のワークシートは入出庫管理のみ記載し、別シートに分析シートを用意することをお勧めします。
すると新しいシートに以下ように、ピボットテーブルのレイアウトエリアと、画面右側に「ピボットテーブルのフィールド」作業ウィンドウが表示されます。
これがピボットテーブルの基本シートです。
「画面右側のピボットテーブルのフィールド」で条件を指定すると、画面左側にピボットテーブルが自動で作成されます。
ピボットテーブルと自動で連動するピボットグラフを作成する
ピボットテーブルには、グラフを表示する「ピボットグラフ」という機能もあります。
ピボットグラフはピボットテーブルと連動するので、数値だけではなくビジュアルで確認しながら集計・分析を進めることができます。
まず元データのテーブルを選択した状態で、メニューバーの「挿入」→「ピボットグラフ」をクリックします。
「ピボットグラフの作成」という画面が表示されるので、「新規ワークシート」を選びましょう。
※既存のワークシートは入出庫管理のみ記載し、別シートに分析シートを用意することをお勧めします。
すると新しいシートに以下画面のような項目が表示されます。
ピボットテーブルと連動してグラフも一緒に変化します。
準備が整いましたので、ここから本格的にピボットテーブルとピボットグラフの作成を開始します。
今回は、１品番の1日当たりの入出庫の数量を集計します。
フィルターの設定｜表全体を特定の項目だけを集計
フィルターを設定すると、ピボットテーブルで特定の項目だけで集計・分析できます。（※1つの商品における月別の入出庫情報など）
今回は、商品ごとの入出庫数の集計表を作るので、品名を選択し、フィルターにドラッグ＆ドロップします。
※フィルターは指定しなくても、ピボットテーブルは作成できます。
列の設定
テーブルの横方向に伸びる値です。（グラフでは、ここが縦軸の項目になります。）
今回は、入出庫のデータを集計するので、「入出庫」を選択し、「列」にドラッグ＆ドロップします。
ピボットテーブルの列ラベルには、入出庫に含まれているデータである「入」と「出」が表示されます。
これで、フィルターに設定した商品の入庫数と出庫数がそれぞれ集計できるようになります。
行の設定
テーブルの縦方向に伸びる値です。（グラフでは、ここが横軸になります。）
日付ごとの入出庫データを集計したいので、「日付」を選択して、「行」にドラッグ＆ドロップします。
日付に含まれているデータである「日付」が表示されます。
これで、日付ごとの入出庫データの集計ができるようになります。
値の設定
行と列で集計したい値です。（グラフでは、ここが縦軸の値に該当します。）
今回は、入出庫の数を集計したいので、「数」を選択して、「値」にドラッグ＆ドロップします。
これで、商品ごとの日当たりの入出庫数の合計値の集計表とグラフが作成できました。
このようにして、フィルター、列、行、値の4項目に集計したい項目を指定すれば、簡単にピボットテーブル（グラフ）を作成できます。
データの更新｜ボタンひとつで簡単にできる
元データに新しい行を追加したり、数値を変更したときは、ピボットテーブルを更新するだけで最新データが反映されます。
ピボットテーブル内のどこかをクリックし、リボンに表示される[ピボットテーブル分析]タブ（または[分析]タブ）から、[更新]ボタンをクリックしてください。これで、最新のデータが反映されます。
ピボットグラフで知っておくと便利な２つのテクニック
ピボットグラフで知っていると役立つテクニックを２つご紹介します。
値の無い部分をグラフに表示したい
通常、ピボットテーブルから作成したグラフは、「値」にデータのある横軸しか表示しません。
入出庫の傾向を分析したい時は、日当たりの入出庫があったかどうかを見るためには、データの無い部分も表示したほうが良いでしょう。
まずグラフを選択した状態で、「ピボットグラフのフィールド」内の軸（分類項目）に日付を入れます。
軸に入れた日付を左クリックし、「フィールドの設定」をクリックしてください。
「フィールドの設定」画面が出てくるので、「レイアウトと印刷」内の「データのないアイテムを表示する」にチェックを入れます。
グラフにデータの無い部分の日付が表示されるようになります。
グラフ内のレポートフィルターボタンを非表示にしたい
グラフを作成すると、レポートフィルター表示されます。
テーブル上だけではなくグラフ上でもフィルターをかけられるので便利ではありますが、レポートなどで提出する際は見栄えが悪いため消したい場合もあるでしょう。
そんな時は以下の方法でグラフ上にあるレポートフィルターを全て非表示にできます。
まずレポートフィルターボタンの上にカーソルを合わせて右クリックし、次に「グラフのすべてのフィールドボタンを非表示にする」をクリックします。
するとグラフ内にあったフィールドボタンが非表示になり、グラフがすっきりします。
ピボットテーブルを使いこなす実務で活躍する３つの応用テクニック
ピボットテーブルは、値を様々な方法で集計できるだけでも便利ですが、他にも便利な機能がとてもたくさんあります。
その中でも、実務での分析の幅を拡げる、実務で役立つ応用テクニックをご紹介します。
グループ化：日付を月や四半期で集計する
ダイアログボックス「グループ化」で「月」や「四半期」を選ぶだけで、日次のデータを選んだ範囲で自動でまとめてくれます。
手順は以下の通りです。
- 日付データを「行」エリアに入れた後、日付の上で右クリックし[グループ化]を選択します。
- ダイアログボックス「グループ化」で「月」や「四半期」を選択する
この日付グループ化の利用シーンとして考えられるのが、前年同月の比較です。
例えば実務では、季節性の強い商品などで前年同月との比較をする機会が多いと思います。
横軸で1本の折れ線グラフで並べるよりも、年別の折れ線を作った方がより傾向が分かりやすいです。
手順は以下の通りです。
- ダイアログボックス『グループ化』で「月」やと「年」を選ぶ
- ピボットテーブルの『行』エリアに「年」と「月」が表示される
- 『行』エリアに「年」を『列』エリアに移動する
前年同月とのデータを比較できるので、今年の傾向が前年とどう違うのかが一目瞭然です。
データが増えれば、数年分の同月データを比較することも可能です。
集計方法の変更：合計、個数、平均などを使い分ける
「値」エリアの項目は、デフォルトでは「合計」が集計されます。
実は「合計」以外の「個数、平均、最大、最小」に変換することも可能です。
手順
「値」エリアで右クリックして、[値フィールドの設定]を選択し、集計したい計算方法を選択します。
例えば、上記のピボットテーブルで選択した集計方法で次のようなことが分かります。
- データの個数：出庫回数（1か月で何回出庫があったか？）
- 平均：１回当たりの平均出庫数
- 最大：その月で一番大きな出庫数
- 最小：その月で一番最小の出庫数
- 積：出庫数全ての掛け算 ※使用しません。
計算フィールド：構成比（％）や前年比などを追加する
集計値を数値ではなく、例えば総計に対する割合（各商品の売上構成比）などを表示することが可能です。
手順は以下の通りです。
- 「値」エリアの項目を右クリックする。
- 「計算の種類」を選択して、計算したい方法を選択する
上記の例で、「列集計に対する比率」を選択したので、各年度の総出庫数を１００％として、各月が何パーセントだったか？ということが分かります。
この機能を使って「前月との比較（何パーセントくらいアップ増減したか）や、年初からの増減率等の表示も可能です。
さらに、下記のようなABC分析の表もピボットテーブルの機能を使えば簡単に作成できます。
ピボットテーブルの機能を使って、ABC分析の表を作る手順は、ABC分析のやり方と活用方法｜誰でも簡単エクセル在庫分析で詳しく解説しています。
ピボットテーブルのよくあるエラーと対処方法
ここでは、ピボットテーブルを作っていて思ったように動かなかったり、エラーが発生するよくある原因を解説します。
ピボットテーブルを作成できない・更新できない時の原因
元データに原因があることが多いので以下の点をチェックしてみてください。
- 元データの見出しが正しく設定されているか？
- 空白行があるか？
- 結合セルがあるか？
- データ範囲が正しく選択されているか？
意図しない集計結果になる
数値データなのに「合計」ではなく「個数」として集計されてしまう場合は、元データに数値以外の文字列や空白セルが混入している可能性があります。
元データを確認・修正し、ピボットテーブルを更新してください。
レイアウトを元に戻したい
色々と操作しているうちにレイアウトが崩れてしまった場合は、一度フィールドリストから項目をすべてドラッグして外に出して削除して、ゼロから作り直すのが一番早くて確実です。
どうしても、残しておきたい場合はコピーしておきましょう。
まとめ
本記事では、Excelのピボットテーブル機能の使い方、さらに効率的で在庫分析に役立つ応用テクニックを解説しました。
まずはこの記事で解説した重要なポイントを振り返ってみましょう。
- ピボットテーブルは、関数やマクロを覚えるよりも簡単
- ピボットテーブルは大量の在庫データを瞬時に集計できる
- マウス操作だけで、品番別・期間別などの様々な切り口で分析が可能
- グループ化、そして集計方法や計算の種類などで、売上構成比や前月との増減などに集計方法を変更できる
- 集計結果からグラフを簡単に作成でき、会議資料などにすぐ活用できる
ピボットテーブルは、あなたの会社の在庫データを、ただの数字の羅列から「意思決定に役立つ情報」へと変える力を持っています。
まずは第一歩として、身近なデータでピボットテーブルを試し、その便利さを実感してみてください。
エクセルだけでOK！効率的・効果的なデータ分析の方法を学ぶ
ピボットテーブルの便利さを実感した上で、さらに一歩進んだデータ活用や、より根本的な課題解決を目指す方へ、もっと実践的なデータ分析手法を学びたい方へ在庫管理の専門家がお手伝いします。
「基本操作はわかったけど、自社のデータでどう分析すればいいか分からない…」
そんな方には、Excelだけでできるデータ分析・活用研修がおすすめです。
一般的なPCスクールとは違い、在庫管理のコンサルティングを行っているプロが講師を務めます。
単なる機能解説ではなく、あなたの会社が実際に使っているデータを使って実務に即した分析手法や改善点の見つけ方を直接学べる、超実践的な研修です。
この研修を受ければ、現場データの分析は大丈夫と言えるでしょう。
エクセルだけでできる！
在庫管理システムを導入する
データ分析をするためには、分析の元になる正確なデータが必要です。
例えば、季節性の分析をするのであれば少なくとも３年分のデータが必要です。
エクセルはデータの分析には向いていますが、そもそも表計算ソフトなのでデータの蓄積には向いていません。
データ活用の基盤となる「データの蓄積」に課題を感じているなら、在庫管理システムを導入しましょう。
在庫管理110番が開発した成長する在庫管理システムは、日々の入出庫を正確かつ簡単に記録できるだけでなく、蓄積したデータは右クリックひとつでいつでもExcelに出力可能。
分析したい時にすぐに最新データを取り出せるため、Excelでのデータ活用がさらにスムーズになります。
クラウド型でユーザー数制限がないので、いつでもどこでも何名でも、安心してご利用できます。
補助金や助成金を使って導入することもできますので、ぜひお気軽にご相談ください。
他社様のカスタマイズ事例もご覧いただけますので、どんなことが実現できるのか知りたい場合も遠慮なくご相談ください。
自社仕様のシステムを低コストで導入
在庫管理のお問合せ・ご相談
「どこから手をつければ良いか分からない」「自社の課題はこれで合っているのだろうか…」
そんな漠然としたお悩みでも構いません。
500件以上の相談実績を持つ経験豊富な在庫管理の専門家が、あなたの会社の状況を丁寧にヒアリングし、最適な解決策をアドバイスします。
どんな些細なことでも、まずはお気軽にご相談ください。
在庫管理の専門家があなたのお悩みにお応えします！