商品別売上分析を自動化するメリットとは？
Excelで商品別売上分析を自動化する最大のメリットは、「集計作業にかかる時間を減らし、判断に使う時間を増やせること」です。
たとえば、毎週・毎月の売上データを見ながら「どの商品が売れているのか」「売上が落ちている商品は何か」「利益に貢献している商品はどれか」を確認する作業は、多くの職場で行われています。しかし、データをコピーして、商品名ごとに並べ替えて、合計を出して、グラフを作る……という作業を毎回手作業で行っていると、それだけでかなりの時間が取られてしまいます。
特に20代のビジネスパーソンにとって、Excel作業の効率化は大きな武器になります。単純な集計作業を自動化できれば、上司への報告資料を早く作れるだけでなく、「なぜこの商品が売れているのか」「次にどの商品を強化すべきか」といった一歩先の分析に時間を使えるようになります。
商品別売上分析を自動化することで、具体的には次のようなメリットがあります。
- 毎回の集計作業を短縮できる
- 手入力やコピペによるミスを減らせる
- 売れ筋商品・不振商品をすぐに把握できる
- 報告資料や会議資料をスムーズに作成できる
- データに基づいた提案がしやすくなる
たとえば、商品別の売上ランキングを自動で更新できる仕組みを作っておけば、新しい売上データを追加するだけで、最新のランキングや合計金額をすぐに確認できます。これにより、「今月はA商品が伸びている」「B商品は先月より売上が下がっている」といった変化にすばやく気づけます。
また、自動化は特別なプログラミングスキルがなくても始められます。Excelには、ピボットテーブル、関数、グラフ、条件付き書式、Power Queryなど、売上分析を効率化できる機能が標準で備わっています。これらを組み合わせることで、日々の売上データを見やすく整理し、分析しやすい形に変えることができます。
重要なのは、最初から完璧な仕組みを作ろうとしないことです。まずは「商品別の売上合計を自動で出す」「売上上位の商品を色で目立たせる」といった小さな自動化から始めれば十分です。少しずつ改善していくことで、Excelは単なる表計算ソフトではなく、売上の状況を把握するための分析ツールとして活用できるようになります。
次の章では、商品別売上分析を正しく行うために欠かせない、売上データの整え方について解説します。
分析に必要な売上データの整え方
商品別売上分析を自動化するうえで、最初に重要になるのが売上データを分析しやすい形に整えることです。Excelでは、元データの形が整っていないと、ピボットテーブルや関数を使っても正しく集計できないことがあります。
たとえば、商品名の表記が「A商品」「Ａ商品」「A 商品」のようにバラバラになっていると、Excel上では別の商品として扱われてしまいます。その結果、本来は1つにまとめるべき売上が分かれて集計され、正しい分析ができなくなります。
まず、売上データには最低限、次のような項目を用意しておきましょう。
- 売上日：いつ売れたのかを確認するため
- 商品名：商品別に集計するため
- 商品カテゴリ：カテゴリ別の傾向を見るため
- 数量：販売数を把握するため
- 単価：商品ごとの価格を確認するため
- 売上金額：売上合計を集計するため
データは、1行に1件の売上データが入る形にするのが基本です。たとえば、1回の販売ごとに「売上日・商品名・数量・単価・売上金額」を横並びで入力します。逆に、商品名が横方向に並んでいたり、月ごとに別シートへ分かれていたりすると、後から自動集計しにくくなります。
また、見た目を整えるための空白行や結合セルは避けるようにしましょう。人間にとっては見やすくても、Excelの集計機能にとっては扱いづらいデータになります。特にピボットテーブルを使う場合、見出し行が1行でそろっており、その下にデータが連続して入力されている状態が理想です。
売上金額は、手入力ではなく計算式で求めるのがおすすめです。たとえば、数量がD列、単価がE列にある場合、売上金額の列には次のような式を入れます。
=D2*E2このようにしておけば、数量や単価を修正したときに売上金額も自動で更新されます。手計算や手入力によるミスを防げるため、分析結果の信頼性も高まります。
さらに、データ範囲はテーブル化しておくと便利です。売上データの範囲を選択し、Excelの「挿入」タブから「テーブル」を選ぶと、データを追加したときに範囲が自動で拡張されます。これにより、後でピボットテーブルやグラフを更新するときも、新しく追加した売上データを反映しやすくなります。
商品別売上分析では、データを集計する前の準備がとても大切です。商品名の表記を統一し、必要な項目をそろえ、1行1データの形で管理しておくことで、次のステップであるピボットテーブル集計がスムーズに進められます。
ピボットテーブルで商品別売上を集計する方法
売上データを整えたら、次に使いたいのがピボットテーブルです。ピボットテーブルを使うと、商品別の売上合計や販売数量を、数クリックで自動集計できます。関数を何行も入力しなくても集計できるため、Excelで売上分析を始めるならまず覚えておきたい機能です。
まずは、2章で整えた売上データの表を選択します。データをテーブル化している場合は、表の中のどこか1セルをクリックするだけで問題ありません。その状態で、Excel上部の「挿入」タブから「ピボットテーブル」を選択します。
表示される画面で、集計元の範囲が正しく選ばれていることを確認し、ピボットテーブルを作成する場所を指定します。基本的には、元データとは別のシートに作成するのがおすすめです。元データと集計結果を分けておくことで、後から確認や更新がしやすくなります。
ピボットテーブルを作成すると、右側にピボットテーブルのフィールドが表示されます。ここで、どの項目をどこに配置するかを決めていきます。商品別売上を集計する場合は、まず「商品名」を「行」エリアにドラッグします。すると、商品名ごとに一覧が表示されます。
次に、「売上金額」を「値」エリアにドラッグします。これだけで、商品ごとの売上合計が自動で表示されます。もし「合計」ではなく「個数」になっている場合は、値フィールドの設定を開き、集計方法を「合計」に変更しましょう。
さらに、販売数も確認したい場合は、「数量」を「値」エリアに追加します。これにより、商品別の売上金額と販売数量を同時に見られるようになります。たとえば「売上は高いが数量は少ない商品」や「数量は多いが単価が低く売上が伸びにくい商品」など、より具体的な傾向をつかみやすくなります。
商品別の売上ランキングを見たい場合は、売上金額の列を右クリックし、「並べ替え」→「降順」を選びます。すると、売上金額が高い商品から順番に並びます。上位商品が一目でわかるため、報告資料や会議用の分析にも使いやすくなります。
また、月別やカテゴリ別に分析したい場合は、「売上日」や「商品カテゴリ」を活用します。たとえば「商品カテゴリ」を「列」エリアに入れると、カテゴリごとの商品別売上を比較できます。「売上日」を行や列に入れれば、時期ごとの売上推移も確認しやすくなります。
ピボットテーブルの便利な点は、元データを追加したあとでも、集計表を簡単に更新できることです。新しい売上データを追加したら、ピボットテーブル上で右クリックし、「更新」を選ぶだけで最新の内容が反映されます。毎回集計表を作り直す必要がないため、定期的な売上分析を大幅に効率化できます。
まずは、「商品名を行に入れる」「売上金額を値に入れる」「売上順に並べ替える」という基本操作だけでも十分です。ピボットテーブルを使いこなせるようになると、商品別売上の集計作業は一気にラクになります。
関数・グラフ・条件付き書式で分析を見える化する
ピボットテーブルで商品別売上を集計できたら、次はその結果をひと目で判断できる形に見える化していきましょう。数字の一覧だけでも分析はできますが、売上の高い商品や伸び悩んでいる商品をすばやく把握するには、関数・グラフ・条件付き書式を組み合わせるのが効果的です。
まず活用したいのが、ランキングや達成率を表示する関数です。たとえば、商品別売上の順位を出したい場合は、売上金額の列に対して次のような関数を使います。
=RANK.EQ(B2,$B$2:$B$20,0)この式では、B2セルの売上金額が、B2からB20の中で何位なのかを表示できます。売上ランキングを自動で出せるようにしておくと、毎月のレポート作成時に「今月の売上上位商品」をすぐ確認できます。
また、全体売上に対する構成比を出すのもおすすめです。商品別売上がB列にある場合、次のような式で各商品の売上構成比を計算できます。
=B2/SUM($B$2:$B$20)構成比を表示すれば、「売上の大部分をどの商品が占めているのか」がわかります。特定の商品に売上が偏っている場合は、在庫切れリスクや販売戦略の見直しにもつなげられます。
次に、集計結果をグラフ化しましょう。商品別売上であれば、棒グラフが見やすくおすすめです。商品名と売上金額の範囲を選択し、Excelの「挿入」タブから「縦棒グラフ」または「横棒グラフ」を選びます。商品数が多い場合は、横棒グラフのほうが商品名を読みやすくなります。
さらに、月別の売上推移を見たい場合は、折れ線グラフが便利です。売上が上がっている商品、下がっている商品を視覚的に確認できるため、会議や上司への報告でも説明しやすくなります。
最後に設定しておきたいのが、条件付き書式です。条件付き書式を使うと、売上が高い商品を濃い色で表示したり、目標未達の商品を赤く表示したりできます。たとえば、売上金額の列を選択し、「ホーム」タブの「条件付き書式」から「上位/下位ルール」を選べば、上位10商品だけを自動で強調できます。
- 売上上位の商品：緑や青で強調する
- 売上が低い商品：赤や薄い色で目立たせる
- 構成比が高い商品：データバーで割合を表示する
- 前月比がマイナスの商品：赤文字にする
このように、関数で指標を追加し、グラフで全体像をつかみ、条件付き書式で重要なポイントを目立たせることで、売上分析は一気にわかりやすくなります。数字をただ並べるだけでなく、「どの商品に注目すべきか」がすぐわかる状態にしておくことが、実務で使えるExcel分析のポイントです。
次の章では、マクロやPower Queryを使って、売上データの読み込みや集計更新をさらに自動化する方法を解説します。
マクロ・Power Queryで売上分析をさらに自動化する
ピボットテーブルや関数だけでも商品別売上分析はかなり効率化できますが、毎回同じ作業を繰り返しているなら、マクロやPower Queryを使うことで、さらに一歩進んだ自動化ができます。
まず活用したいのがPower Queryです。Power Queryは、Excelに標準搭載されているデータ取り込み・加工機能で、CSVファイルや別ブックの売上データを読み込み、不要な列の削除、列名の変更、データ型の変換などを自動で行えます。
たとえば、毎月「売上データ_202406.csv」のようなCSVファイルを受け取り、それをExcelに貼り付けている場合、Power Queryを使えばファイルを指定して読み込むだけで、分析用の形に整えたデータを自動作成できます。
基本的な流れは次のとおりです。
- 「データ」タブから「データの取得」を選ぶ
- CSVやExcelブックなど、読み込み元を指定する
- Power Queryエディターで不要な列や空白行を削除する
- 商品名、売上日、売上金額などのデータ型を整える
- 「閉じて読み込む」を押してExcelに反映する
一度設定しておけば、次回以降は「更新」ボタンを押すだけで、同じ加工処理を自動で実行できます。手作業でのコピペや整形が減るため、ミスの防止にもつながります。
一方、マクロはExcel上の操作を記録し、ボタンひとつで再実行できる機能です。たとえば、売上データの更新後に「ピボットテーブルを更新する」「グラフの表示範囲を整える」「レポート用シートを印刷する」といった一連の作業をマクロ化できます。
初心者の場合は、いきなりVBAコードを書く必要はありません。Excelの「開発」タブにある「マクロの記録」を使えば、自分が行った操作をそのまま記録できます。よく使う処理を記録しておけば、次回から同じ作業を短時間で完了できます。
特におすすめなのは、次のような作業の自動化です。
- 売上データ読み込み後のピボットテーブル更新
- 商品別売上ランキングの並べ替え
- 分析シートの表示形式や列幅調整
- レポート用シートのPDF出力
ただし、マクロを使う場合は注意点もあります。マクロ付きファイルは拡張子が「.xlsm」になり、会社のセキュリティ設定によっては実行できないことがあります。また、誤った処理を記録すると不要な操作まで自動実行されてしまうため、最初はコピーしたファイルで試すのがおすすめです。
実務では、Power Queryでデータを整え、ピボットテーブルで集計し、マクロで更新や出力を自動化する流れが非常に効率的です。この仕組みを作っておけば、新しい売上データを追加して更新ボタンを押すだけで、商品別売上の集計表やグラフが最新状態になります。
Excelでの商品別売上分析は、最初に仕組みを作る手間こそありますが、一度整えれば毎月の作業時間を大きく削減できます。単なる集計担当で終わらず、データをもとに提案できる人材になるためにも、ぜひ小さな自動化から始めてみてください。
コメント