データ活用の方法｜エクセルだけでできる効果的なデータ分析

この記事を読めば、データ分析の初心者が方法と基本的な流れが分かります。

特にこの記事は、あなたが次のようなお悩みを持っていたら、ぜひ読んでいただきたいです。

1. 担当者の勘や経験だけで仕事をやっている
2. 毎月、集計をして会議に出しているが、出すだけになっている。
3. データはあるけど何もしていない。（まとめ方、活かし方がわからない）
4. データはあるけど、何を作ればよいのかわからない

データは、あなたの会社にしかなく、あなたの会社でしか活かせない貴重な資産です。会社にとって、お金、人材の次に大切な資産といっても過言ではありません。

データには、あなたの欲しい答えやヒントが必ず隠れています。きちんとしたデータ分析行えば、必ず見えます。

この記事では、どんな企業でもできる（データの専門家不要）で、エクセルだけでできる（高価なBIツールは不要）、データ分析の正しい進め方を解説します。

【よくあるケース】データを活かせていない会社、意味の無い分析をしている会社

【ケース１】業務の属人化：担当者の勘や経験だけで仕事をやっている

中小企業で最もよくあるケースです。例えば次のような状況です。

• 営業成績が、個人によって大きく違う
• 発注数や安全在庫の持ち方が担当者によって違う
• Aさんは「良い」と思っているのにBさんは「良くない」と思っている

人は、行動したり何かを決める時、必ず何かの判断をしています。

その判断を行っている「何か」を見える化するのが、データ分析です。

「何か」が見えてくれば、営業成績の良い人のコツを、営業成績の悪い人が活用できるようになる可能性はとても高いです。

発注数や安全在庫の持ち方などは、自動化することもできるかもしれません。

【ケース２】意味の無い分析・集計：毎月、集計や分析結果を出して会議しているが、出すだけになっている

せっかく作っているのに、活かせていないもったいないケースです。

• 前月より、「増えた・減った」を見るだけ。
• 集計・分析を見ても、原因や理由はよくわからない。
• 「来月はもっと頑張ります」という根性論になるだけ

といったような状態です。

データ分析で大切なのは、データを読むことです。（データを読むについては後述します）

結果（例えば、前月より売上が増えた、減った）とかのは、小学生でも見ればわかります。

で、その結果を受けてどうするの？がビジネスでは必要です。

データを読み、次の行動に活せるようにするのが、データ分析です。

時間をかけて作った集計が意味を持ちます。（活用できない集計や分析を作るのは時間の無駄です。）

【ケース３】データマニア：データはあるけど何もしていない。（活かし方がわからない）

一番もったいないケースです。

一生懸命データを集めているのにも関わらず、何もしていない状態です。

データは宝の山です。

データ分析をすれば、自分たちが気づいていないことに必ず気づくことができます。

エクセルでデータ分析することをお勧めする３つの理由

データ分析にエクセルを使うことをお勧めする理由は、

1. どのパソコンにも入っている
2. 普段から使っているので基本操作ができ慣れている
3. 様々な角度から分析が可能

高価なBIツールを使う必要は一切ありません。

気軽に使えるエクセルを使いましょう。

データ分析は自分たちの感覚や知りたい事の可視化すること

データ分析をする最大のメリットは、自分たちの感覚（なんとなく思っていること等）を数値で可視化できることです。

広辞苑によると、分析とは

1. ある物事を分解して、それを成立させている成分・要素・側面を明らかにすること
2. 概念の内容を構成する諸徴表を各個に分けて明らかにすること
3. 証明すべき命題から、それを成立させる条件へつぎつぎにさかのぼってゆく証明の仕方

とあります。

難しく書いていますが、要するに、自分たちが考えていること（仮説や感覚）の元になっている判断材料を数値で具体化する作業です。

具体的には、データ分析とは、集計表（クロス集計）やグラフ、回帰分析などを行い、目的（知りたい事や課題）を数値で可視化することです。

例えば、売上が増えたり減ったりすることについて、担当者が次のような考えを感覚として持っていたとします。

• 暖かくなったら売れ行きが伸びているような気がする。（季節的要因、気温）
• 毎年同じような時期に、注文が来ている気がする
• 九州地方よりも東北地方のほうが、売れ始めるのが早い気がする

長年の業務経験によって培われた「○○のような気がする」という感覚を数値によって可視化するのが、データ分析です。

勘や経験はあながち間違っていないこともありますし、思い込みで全く見当外れのこともあります。

データ分析で数値化することで

• あながち間違っていないこと：自信を深める
• 思い込み、見当違い：今までの思い込みを捨てる

勘や経験だけに頼ることなく、効率良くかつ効果的な意思決定ができるようになりましょう。

データ分析が意思決定や行動につながらない理由

データ分析の役割は、結果から分かったことを意思決定（改善や行動）につなげることです。

分析や集計をするだけで、結果を読めていない会社が本当に多いです。

在庫管理110番の個別相談によくある会話例をご紹介します。

相談者：在庫が減らずに困っています。

弊社：今、何かやっていますか？

相談者：うちは、データがきちんとあるので、毎月「在庫回転率」を集計しています。

弊社：「在庫回転率」で何を見ているのですか？

相談者：取った方がいいといわれて取っていますが、実際にはなぜとっているか、よくわかりません。。

弊社：それでは「データを取っている」だけですか？？

相談者：はい、データを取っているだけで、何もできていません。。

在庫管理アドバイザーの質問の要点は、次の３点です。

1. なぜデータ分析をやっているか？（データ分析の目的）
2. データ分析や結果で何を見ているか？（結果の評価）
3. アクションにつなげられているか？（意思決定）

データ分析や集計は、「会社の課題」を解決するものであって、データ分析や集計するだけでは単なる数値遊びです。

意味のあるデータ分析にするための３ステップ

データ分析や結果から分かったことを、アクションにつなげることができるのが、意味のあるデータ分析です。

意味のあるデータ分析は、次のステップで考えます。

1. 目的：何のためにデータ分析をするのか？をハッキリさせる
2. 評価軸：目的に影響を与えていそうな要素を並べる←これらが影響を与えている確率、大きさを調べる
3. 要素：評価軸を分析するために必要なデータ

意味の無いデータ分析は、「要素」からスタートしてしまっています。

グラフや集計表を使って、何となく分析をやった気になりますが、何のためにデータ分析をするのかがハッキリとしていないため、分析結果を見ても「？？？」です。

データ分析の進める手順

具体的なデータ分析の進め方は以下の通りです。

1. 仮説・目的：（課題や要望、改善したい事）を決める
2. 課題を解決、要望を実現するための仮説（考えられる要因）を列挙
3. 要因を検証するために何が分かれば良いのか？
4. 必要な結果をイメージする
5. 分析結果を作るために必要なデータ項目を列挙する
6. データ項目に挙がったデータを収集する
7. 集めたデータで分析を実際にやってみる

目的（課題や要望）の設定

会社や部署が持っている課題を設定します。

この時点では、イメージ感だけで構いません。

例えば、

• 在庫金額が多いので、在庫削減をしたい
• 誰でも発注できるようにしたい
• 売上を上げたい

目的に影響を与えている要素を出す

目的に、影響を与えていると考えられる評価軸を決めます。

ベテランの勘や経験が活きます。

アイデア出しの要領で構いません。

ここでは、なるべく多くの人や部署から「○○では？」、「○○だと思う」を集めます。

その理由は、一人の部署や一部門だけでは、見方が偏ってしまうためです。

例えば、「在庫金額が多いので、在庫削減をしたい」であれば、

1. 滞留在庫が多いのではないか？
2. 仕入先の納期が遅れがちなので、在庫を多めに持っているのでは？
3. 今年は暖かい日が多かったので、売れ行きが低調だったのでは？
4. 昨年立ち上げた新しいカテゴリーが低調なのでは？

といった具合です。

実際に分析に使う評価軸は、4つくらいに絞り込むと良いでしょう。

要因を検証するために何が分かれば良いのか？

要因の答えは、どのような結果が分かれば良いのかを挙げます。

1. 滞留在庫が多いのではないか？　＞在庫回転率
2. 仕入先の納期が遅れがちなので、在庫を多めに持っているのでは？　＞仕入先の納期遵守率、納品LT
3. 今年は暖かい日が多かったので、売れ行きが低調だったのでは？＞気温
4. 昨年立ち上げた新しいカテゴリーが低調なのでは？　＞商品カテゴリー、カテゴリを立ち上げた時期

例えば、「滞留在庫が多いのでは？」を評価する要素として「在庫回転率」が分かれば、在庫が滞留しているかどうかが分かります。

要因を検証するために必要な結果を書き出す

仮説（考えられる要因）を検証するためにどのような分析結果があれば良いのか、イメージを手書きで良いので書き出します。

データ分析の代表的な方法は、以下のようなものがあります。

• 集計（時系列、比較、比率を見る）
• グラフ化（時系列、比較、比率を見る）
• 回帰分析（データの関係（相関性）を見る）

イメージは次のように手書きで十分です。（私の手書きで、文字が汚くすみません・・・・）

もう一度繰り返しますが、グラフや集計表を作るのが目的ではありません。

分析結果（作ったグラフや集計表）から、この結果を見ればどういうことが分かるのか（推測できるのか？）を書き出します。

分析結果を作るために必要なデータ項目を列挙する

「データ分析の結果」を作るために必要なデータを集めます。

たとえば、「在庫金額が多いので、在庫削減をしたい」であれば、次のようなデータが必要になるでしょう。

例えば、在庫回転率を計算する場合は、次の3つのデータが必要です。

• 開始時在庫
• 終了時在庫
• 期間中の出庫数

在庫回転率の計算方法の詳しい解説はこちら

データ項目に挙がったデータを収集する

データ項目が決まったら、そのデータを収集します。

1. 滞留在庫が多いのではないか？　＞在庫回転率　＞販売データ、現在庫データ
2. 最近始めたネット販売が、低調なのでは？　＞ネット販売の実績　＞販売データ、販売チャネル
3. 今年は暖かい日が多かったので、売れ行きが低調だったのでは？　＞気温　＞気温データ
4. 昨年立ち上げた新しいカテゴリーが低調なのでは？　＞商品カテゴリー　＞商品カテゴリーデータ

収集するデータは次のように分類できます。

1. 社内・あり：社内でとれるまたは整備しているデータであって、既にデータ分析に十分な精度と量がある
2. 社外・あり：購入や公表されているデータであって、データ分析に十分な量を集めることができる（例：気象データや政府統計データ等）
3. 社内・ない：社内でとれていないまたは整備できていないデータ。
4. 社外・ない：非公表データ（販売先の在庫や売上データ、政府の重要機密情報等）

１と２だけであれば、すぐにデータ分析が可能です。

３に該当する場合は、データ収集を行います。どうしてもデータ収集ができない（面倒とか時間が無いといった理由を除く）場合は、代替案を考えます。

４に該当する場合は、データ収集ができないこ都が多いため、代替案を考えます。

集めたデータで分析を実際にやってみる

実際に、データ分析を行います。

なお、グラフや集計表を作る際は、エクセルのピボットテーブルを使うのがおすすめです。

ピボットテーブルの作り方の基本（練習用のサンプルエクセルもあります）

このステップでは恐らく、不足や不備していることが分かります。

• 要因を検証するために必要なデータ分析の結果をイメージが不足
• データ分析の結果を作るために必要なデータ項目が不足
• データ分析の準備に時間がかかる
• データ項目の不備を発見

分析結果が思ったことと違っていた場合

仮説をデータ分析した結果、全然そうでは無かった（影響を与えてなかった）ということもあります。

これもデータ分析の大切な役割です。

データ分析で数値化して、客観的な事実が分かるので、自分たちの感覚が違っていた（思い込みだった）ということも分かります。

この場合は、この仮説は評価から外します。

実際のアクションを決めて行動する

ここまでのステップを繰り返して行って、自社に必要なデータ分析を見つけます。

適切なデータ分析をすると、ぼやけた課題が数値化することでクリアになり、そして、取り組めば効果が高い（優先取り組み事項）がわかります。

さらに、作成したデータ分析や集計を定期的（週次・月次）に作成して、課題の改善具合、目標の達成具合をモニタリングします。

【事例】修理時間を短縮したい会社

ここで、今回紹介した手順に沿って、実際に実施したデータ分析を手順に沿ってご紹介します。

１．課題や要望の設定

市場に出た機械を修理する作業時間を短縮して、1か月に修理できる台数を増やしたい。

※修理の依頼が増えているが、定時で対応ができておらず残業が増えている。残業代によって修理原価が上がっていることと、従業員の負担も大きくなっているので解決したい。

会社の目的を現場の課題に落とし込むと次のようになります。

1. 時間がかかっている原因になっているのは何か？
2. 何を改善すれば、効率的にしかも効果的に時間短縮を実現できるのか？

２．課題を解決、要望を実現するための仮説（考えられる要因）を列挙

まず、「時間がかかっている原因になっているのは何か？」を見つける必要があるので、

経験上、自分たちが思っていることを挙げます。

1. 機種グループBと機種グループBでは、グループBのほうが圧倒的に修理時間がかかるはず。
2. 作業者によって修理時間に差があるのでは？
3. 年式の古い機械は、修理箇所が多いため時間がかるのでは？
4. 機種によって違うのでは？
5. 改造があると、時間がかかるのでは？
6. 修理の作業工程によって、時間に差があるのでは？

３．要因を検証するために必要なデータ分析の結果をイメージして列挙する

「時間がかかっている原因について、次の2点を知る必要があります。

1. 原因の特定
2. 見つかった原因がどれくらい大きな影響を与えているか？

改善のポイントを見つけるために、次の分析を実施することにしました。

• 棒グラフと集計による修理時間の比較：機種グループAと機種グループBの修理時間にどれくらいの差があるのか？
• 回帰分析の実施（修理時間に対して、作業者・年式・機種・改造）がどれだけ影響を与えているか？

４．データ分析の結果を作るために必要なデータ項目を列挙する

1. 作業時間　＞修理時間（社内・データ無し）
2. 機種グループAとBでは、グループBのほうが圧倒的に時間がかかるはず。＞機種グループ（社内・データあり）
3. 作業者によって修理時間に差があるのでは？＞作業者（社内・データ無し）
4. 年式の古い機械は、修理箇所が多いため時間がかるのでは？＞年式（社内・データあり）
5. 機種によって違うのでは？＞年式（社内・データあり）
6. 改造があると、時間がかかるのでは？＞改造有無（社内・データ無し）

５．データを収集する

修理時間を全くとっていなかったので、各工程の事に作業時間（誰がやった可も含む）の１か月くらいのデータ収集を行いました。

（1日当たり30データくらい収集可能、1カ月で600データ）

６．集めたデータでデータ分析を実際にやってみる

集めたデータを使って、回帰分析を行いました。

その結果分かったのは、

1. 機種グループAと機種グループBでは、グループBのほうが圧倒的に修理時間がかかるはず。＞機種グループBのほうが２倍以上時間がかかっている。
2. 作業者によって修理時間に差があるのでは？＞ベテラン作業者と若手作業者には明らかに差があった。
3. 年式の古い機械は、修理箇所が多いため時間がかるのでは？＞年式による差はなかった。（思い込みだった）
4. 機種によって違うのでは？＞機種による差は無かった（思い込みだった）
5. 改造があると、時間がかかるのでは？　＞差は無かった（思い込みだった）
6. 修理の作業工程によって、時間に差があるのでは？＞組立工程が、修理時間の50％を占めていた。

７．実際の意思決定（アクション）

この分析結果を受けて、次のような具体的な改善を実施することにしました。

改善の工程は組立工程に絞り込み、作業者の習熟度による差が大きいため、ベテランの作業のコツを見える化して、若手にもできるようにする。

いかがでしたでしょうか？

会社から与えられた課題を、具体的な施策、しかも効率的で効果が最も見込める改善方法を見つけることができました。

データ分析は主観（個人の経験や勘）を客観（数値化）する作業

事例をご覧いただくと分かるように、データ分析をすれば費用対効果の高い施策を実施することができるようになります。

在庫管理110番がこれまで100社以上の相談に乗ってきて確信したのは、個人の主観（経験や勘）だけではダメ、データ分析だけでもダメということです。

個人の主観（経験や勘）だけではダメというのはわかりやすいと思います。

しかし、「データ分析」というのは、理解ができないという方が多いです。

たとえば、安全在庫を設定する時に有名な安全在庫の公式です。

安全在庫の公式通りに計算すると「こんなに安全在庫を持たないといけなのか・・・！！」と思うはずです。

私も実務で安全在庫を安全在庫の公式で計算してみたことがあって、同じ気持ちになったことがあります。

あなたが思うことは、正解です。

安全在庫の公式を使うのであれば、鵜呑みにせずに、経験に基づいて若干の調整が必要なことが多いです。

つまり、あなたの主観を大切にしつつ、それを客観化（数値化）することが、データ分析の意義といえます。

データ基本や扱い方は、在庫管理からはじめるDXセミナーで詳しく解説しています。

データ分析の準備に時間がかかる原因

日常的にデータの集計や分析をしている会社であっても、「時間がかかるのが負担」ということがよくあります。

主に次の２つの問題が原因です。

1. データの保存形式
2. データ自体の不備

データの保存形式

データの保存形式は、次のような形式が基本です。

データを分析・集計するためには、データが分析・集計しやすい形式で保存されていなければいけません。

データ項目の不備

データの不備とは、データがそのまま使えなかったり、余計なデータがが入っていることです。

例えば、「納期」というデータ項目には、「日付（2022/10/12といったデータ）」が必要です。

しかし、「納期」というデータ項目が次のような場合だとそのまま使えません。

• 10/12 ＞年データが無い
• 2022年10月12日　＞データ形式が違う
• 10月中旬頃　＞　日付データではない
• データが無い

上記のような場合は、データ整形（データの削除や置き換え、加工）といった作業が必要になります。

データの前処理にはパワークエリを使いましょう

データの前処理（データ整形や2つ以上の表のデータ統合等）は、本当に時間と手間のかかる作業です。

しかし、この作業はエクセルの機能であるパワークエリを使えば、解決できることが多いです。

私の例だと、1.5時間くらいかかっていたデータクレンジング作業がわずか5分（生データの貼り付けと、多少の設定のみ）で終わるようになりました。

パワークエリは本当に強力な機能なので、ぜひご活用することをお勧めします。

パワークエリの基本的な使い方と手順（練習用サンプルファイルあり）

データ分析にはピボットテーブルを使いましょう

ピボットテーブルは、クロス集計表やグラフを簡単に作ることができるエクセルの機能です。

一度覚えれば、簡単にしかも様々な角度で分析できるようになるのが、ピボットテーブルの最大の良さです。

ピボットテーブルの基本的な使い方

エクセルでできる！データ分析をやってみましょう

今回、解説したデータ分析の進め方を学んで、ぜひ意味のある分析を効率良く実施してみませんか？

VBAを使わずにエクセルの機能（パワークエリやピボットテーブル等）を使うだけで、だけで、下記のような分析表を作れます。

今回解説した、データ分析の基本的な手順をマスターすれば、分析に無駄な時間と労力をかける必要も無く、また意味の無い分析も無くなります。具体的には、

• 忙しくても分析できるようになる（データ分析自体の省力化、自動化）
• 分析結果を実際の判断や行動につなげられるようになる
• やりたい事に対して、どのような結果が必要で、結果を作るためにどういうデータを用意すれば良いかが分かるようになる

在庫管理システムなら在庫管理110番にお任せください

在庫管理110番は、在庫管理支援の専門会社です。

自分たちだけで、データ分析をする自信が無い・・・

という場合は、在庫管理110番の専門家に相談してみませんか？

初回相談は無料ですので、お気軽にお問い合わせください。

成長する在庫管理システム

そもそもデータを収集する仕組みが無いという場合は、在庫管理システムの導入をお勧めします。

データが活用できるように、データを簡単にエクセルに落とせる仕組みを標準搭載しています。

また、成長する在庫管理システムのコンセプトは使い切れるシステムです。

導入した機能を１００％使える状態にすることで、役立つことはもちろんのこと、シンプルで誰でも使える、余計な機能が無いので低コストを実現しました。

実務を経験したものとして、そして在庫管理の専門家として、不満を解消するために作った在庫管理システムです。

成長する在庫管理システムの特徴や詳しい機能はこちらからご覧ください。

【無料】在庫管理の専門家に相談する

在庫管理アドバイザーに、エクセルを分析に活用する方法を相談してみませんか？

あなたの会社の在庫管理習熟レベルによって、何から始めれば良いか、何が問題でつまづいているかが分かります。