エクセルABC分析の作り方と実践手順！パレート図とSUMIFで在庫最適化

売上データの山を前にして「どの商品に注力し、どの在庫を絞るべきか」という判断に頭を抱える現場は少なくありません。限られた人員とリソースで最大の利益を生み出すために不可欠となるのが、データを重要度別にランク付けするABC分析です。業務効率化や過剰在庫の削減を急ぐ事業責任者や現場リーダーの間で、専用システムを導入せずとも手元のエクセルだけで高精度な分析を完結させるノウハウが改めて求められています。

エクセルの基本機能であるSUMIF関数やピボットテーブル、そして複合グラフであるパレート図を正しく組み合わせれば、膨大な販売データからわずか数分で優先順位を可視化できます。本稿では、無駄な試行錯誤をゼロにする最も直感的な計算式からグラフ作成のステップ、さらには現場の改善事例や陥りがちな落とし穴まで、実践的な知見を網羅して解説します。

📌 【この記事の重要ポイントまとめ】

• 要点1：ABC分析は売上や在庫の重要度をA（上位70〜80%）・B（15〜20%）・C（残り5〜10%）に分類する手法であり、エクセルの基本関数だけで即日導入できる。
• 要点2：商品別集計にはSUMIF関数、比率算出には売上構成比と累計構成比の計算式を用い、複合グラフ（パレート図）化で視覚的な合意形成が容易になる。
• 要点3：Cランク商品の安易な一括廃止は「ついで買い」を損なうリスクがあり、商品特性やリピート率を加味した多面的な意思決定が不可欠である。

【超速実践】エクセルでABC分析を行う最も簡単な手順と関数計算術

エクセルを用いたABC分析は、難解なマクロや複雑なプログラミングを一切必要としません。実務で最もスピーディーに結果を出すための手順は、以下の5つのステップに集約されます。

まずは日々の受注明細やPOSデータから、商品ごとの売上合計を算出します。ここで極めて役立つのがエクセル SUMIF関数 売上集計です。元データが膨大であっても、商品マスターの一覧に対して次の計算式を入力するだけで、瞬時に重複のない商品別売上テーブルが完成します。

=SUMIF(明細の商品名範囲, 集計先の商品名セル, 明細の売上金額範囲)

商品ごとの売上が集計できたら、表全体を売上金額の降順（大きい順）で並べ替えます。このソート作業を怠ると累計計算が破綻するため、必ず最初に実行してください。

次に、全体売上に対する各商品の割合を示す売上構成比 エクセル 計算を行います。全体の合計売上がセルD20にある場合、個別の売上構成比は=D4/$D$20のように合計セルを絶対参照（$マーク）で固定して算出します。パーセンテージ表示に設定することで、各アイテムの寄与度が明確になります。

続いて、上位からの割合を足し上げるABC分析 累計構成比 計算式を設定します。最上位の行は売上構成比をそのまま参照し、2行目以降は「1行上の累計構成比 ＋ 当該行の売上構成比」という数式（例：=F4+E5）を入力して下方向へオートフィルします。最下行がちょうど100%になれば計算は正確です。

仕上げとして、設定した閾値に応じて商品を自動分類するABC分析 ランク分け IF関数を組み込みます。一般的な「累計80%以下をA、80%超〜90%以下をB、それ以上をC」とする基準の場合、次の入れ子（ネスト）構造の数式を記述します。

=IF(F4<=0.8, "A", IF(F4<=0.9, "B", "C"))

この一連の流れを組むだけで、手持ちのデータシートがそのまま高機能なABC分析 エクセル テンプレートとして機能し、翌月以降も元データを貼り替えるだけで自動更新が可能になります。

視覚化で経営を動かす｜パレート図とABC分析グラフのエクセル作成手順

数字の羅列だけでは、会議の場で関係者の直感的な合意を得ることは困難です。ABC分析の威力を最大限に引き出すには、売上高の棒グラフと累計構成比の折れ線グラフを一体化させたパレート図 エクセル 作り方をマスターしておく必要があります。

ABC分析の根底にあるのは、イタリアの経済学者ヴィルフレド・パレートが提唱した「全体の数値の大部分は、全体を構成するうちの一部の要素が生み出している」というパレートの法則 80対20です。ビジネスにおいては「売上の8割は全商品の2割が生み出している」という経験則として広く知られています。

エクセルでこの構造を可視化するABC分析 グラフ 作成手順は次の通りです。商品名、売上金額、累計構成比の3列を選択した状態で、リボンの「挿入」タブから「おすすめグラフ」を開き、「組み合わせ」を選択します。売上金額を「集合縦棒」、累計構成比を「折れ線」に設定し、累計構成比側の「第2軸」にチェックを入れます。

グラフが生成されたら、第2軸（右側のパーセント軸）の最大値を必ず1.0（100%）に手動固定してください。自動設定のままでは最大値が120%などにずれてしまい、80%の境界線が視覚的に狂ってしまうためです。

さらにデータ量が数万行に及ぶ大規模な集計では、関数を手入力する代わりにエクセル ピボットテーブル ABC分析を活用するアプローチが圧倒的に時短となります。ピボットテーブルの「値フィールドの設定」から「計算の種類」を「指定行/列に対する比率」や「累計比率」に切り替えることで、数式を入力することなく一瞬でパレート図の元データを生成できます。

【データ比較】ABC分析グループ分け基準と在庫管理・発注戦略の一覧表

ABC分析を単なる集計で終わらせず、利益を生み出すオペレーションへ落とし込むには、グループごとの明確なアクションプランが欠かせません。実務で広く採用されているABC分析 グループ分け 基準と、それに対応する具体的な管理方針を一覧表にまとめました。

上記の配分比率は一般的な製造業や小売業の標準値ですが、取り扱う商材の特性によって「70-20-10」や「80-15-5」のように柔軟に境界線を調整することが実務上のポイントです。

【実態検証】店舗改善と在庫管理の現場で起きたABC分析の劇的成果

データ分析が真価を発揮するのは、現場のオペレーションと結びついた瞬間です。ここでは在庫管理 ABC分析 エクセルの運用によって業績を好転させた具体的な現場の取り組みを検証します。

ある地域密着型のライフスタイル雑貨店では、約3,500点に及ぶSKU（在庫管理単位）を抱え、バックヤードの圧迫と資金繰りの悪化に直面していました。店長はエクセルを用いた売上集計を行い、パレート図を作成。その結果、わずか全商品の14%にあたるAランク商品が、総売上の78%を稼ぎ出しているという事実が浮き彫りになりました。

このデータをもとに実行されたABC分析 活用事例 店舗改善の施策は極めてシンプルです。

まず、売上の柱であるAランク商品について、店頭の一等地（ゴールデンゾーン）への陳列拡大と日次発注への切り替えを実施。これにより、これまで頻発していた「週末の売り切れによる機会損失」をほぼゼロに抑え込みました。

一方で、品目数の6割以上を占めていたCランク商品に対しては、棚割りを縮小し、類似アイテムの统廃合を断行。半年間の継続運用により、店舗全体の在庫金額を27%削減しながらも、月間売上高は前年比114%を達成するという劇的な改善を実現させました。現場スタッフからも「どの棚の補充を最優先すべきかが明確になり、残業時間が劇的に減った」という生の声が上がっています。

一般に知られていない盲点とネットの誤解

ABC分析は極めて強力なツールですが、Web上で散見される解説を鵜呑みにして機械的な運用を行うと、かえって売上を落とす危険な罠が存在します。

最も典型的な誤解は、「売上構成比の低いCランク商品はすべて不要であり、即座にカットすべき」という極論です。実務の現場では、この判断によって店舗の魅力そのものが失われてしまう事例が後を絶ちません。

Cランク商品の中には、以下の3つの重要な役割を果たしているアイテムが数多く含まれています。

• フック・ついで買い商品：単価は低いものの、Aランク主力商品と一緒に高確率で購入される消耗品や関連パーツ。
• 発売直後の成長株：投入直後で累計売上が少ないだけで、急激な伸びを示している新商品・テスト販売品。
• 品揃えの専門性を担保する商品：購入頻度は年に数回でも、それがあることで「何でも揃う店」としてのブランド信頼を維持している専門資材。

また、「売上金額」だけでABC分析を行うことも危険です。薄利多売のAランク商品よりも、販売数は少なくても粗利率が圧倒的に高いBランク商品の方が、最終的な営業利益に貢献しているケースは珍しくありません。分析を行う際は、売上高だけでなく「粗利額」や「出荷頻度」を軸にした複合的なクロスABC分析を行う視点が欠かせません。

【プロの結論】業務改革を成功させる現場導入の判断基準と組織行動の罠

ABC分析を組織に導入する際、最大の障壁となるのはエクセルの操作技術ではなく、現場の「心理的バイアス」と「組織のサンクコスト（埋没費用）意識」です。

「長年扱ってきた思い入れのある商品だから外せない」「以前大ヒットした看板商品だからまだ売れるはずだ」という現場の現状維持バイアスは、合理的なデータ分析の結果をしばしば歪めてしまいます。長年放置された不採算在庫は、保管コストだけでなく、新しい売れ筋商品を仕入れるための運転資金とバックヤードの空間を奪い続けます。

【プロの結論】エクセルABC分析を導入すべき現場・慎重になるべき現場

現場の状況に応じて、ABC分析の適用には明確な判断基準が必要です。

▼今すぐ導入すべきケース：

• SKU数が100を超え、どの商品が利益を生んでいるのか感覚値でしか把握できていない現場
• 倉庫やバックヤードの保管容量が限界に達し、欠品と余剰在庫が同時に発生している現場
• 発注業務が属人化しており、担当者の勘と経験に頼った発注で過剰在庫が生じている現場

▼適用に慎重・別手法の併用が必要なケース：

• 受注生産型やフルオーダーメイドを中心としたビジネス（パレートの法則が働きにくい）
• 季節変動が極端に大きい商材（単月データでの分析は誤った判断を招くため、年間動向での評価が必須）
• 取り扱い品目数が20点未満の小規模事業（視覚化せずとも全体像が把握できるため、個別損益管理を優先すべき）

客観的なエクセルの計算結果を「現場を断罪するための刃」にするのではなく、「限られた労力をどこに集中投下すれば全員が楽に成果を出せるか」という共通の地図として共有することこそが、業務改革を成功に導く本質です。

【abc 分析 エクセル】に関するよくある質問（FAQ）

Q1：エクセルのABC分析テンプレートは無料のものをダウンロードして使うべきですか？
A1：構造理解のために一度は自力で数式を組むことを強く推奨します。SUMIF関数、構成比、累計構成比、IF関数という4つの要素さえ理解できれば、自社の業務フローに合わせたシートが15分程度で自作でき、列の追加や集計軸の変更にも柔軟に対応できるようになります。

Q2：パレート図を作成すると折れ線グラフが80%で綺麗に折れ曲がらないのですが？
A2：実データが必ずしも理論通りの「80対20」になるとは限りません。商材や業界によって「上位10%で売上の60%」になることもあれば、「上位30%で売上の70%」になることもあります。理論値に無理に合わせるのではなく、自社データの傾きから自然な変曲点を見つけ出し、A・B・Cのグループ分け基準を調整してください。

Q3：最新バージョンのエクセルでランク分けを簡単に行う関数はありますか？
A3：Microsoft 365やExcel 2019以降であれば、入れ子構造が不要なIFS関数やSWITCH関数、あるいはXLOOKUP関数を用いた近似一致検索を活用することで、よりシンプルで可読性の高いランク分け数式を記述できます。

まとめ：エクセルABC分析を継続的な利益創出につなげる鉄則

エクセルで行うABC分析は、自社のビジネスの「今どこにエネルギーを注ぐべきか」を冷徹かつ明確に指し示してくれる羅針盤です。SUMIF関数による自動集計、累計構成比の算出、そしてパレート図による視覚化という一連のフローを仕組み化してしまえば、分析にかかる時間は毎月わずか数分に短縮されます。

重要なのは、一度の分析で満足するのではなく、月次や四半期ごとの定点観測として定着させることです。商品のライフサイクルに伴い、かつてのAランク商品がBやCへと後退し、新たなCランク商品が次世代の柱へと成長していくダイナミズムを捉え続けること。それこそが、過剰在庫を根絶し、筋肉質で利益率の高い事業体質を築き上げるための決定打となります。 （出典: abc 分析 エクセル（Yahoo!ニュース））