使いこなせてる？パレート図で学ぶ絶対参照｜エクセル

エクセルで絶対参照を使うケースは、それなりにあると思いますが、あなたは絶対参照使いこなせてますか？

今回は、絶対参照の機能を駆使して、パレート図を作成してみます

パレート図とは、詳細は後述しますが、一言でいうと、
『物事の優先度を可視化するツール』 と言えるでしょう

ビジネスの現場でも広く使える手法となりますので、
今回は、絶対参照の使い方を覚える上での例題的なツールとして
あなたに、お届けできればと考えております

絶対参照とは？

まず、はじめに
絶対参照とはについて、ご説明します

絶対参照は、エクセル関数で数式コピーする際に参照セルを固定しておく機能です

固定したい列または行の左側に「$」 と記入することで固定参照となります

A1セルを固定したい場合は、$A$1
A列を固定したい場合は、$A1
１行目を固定したい場合は、A$1
となります。

これが絶対参照のルールです

なぜ、絶対参照を知っておくべきか

エクセルを始めたばかりの方の場合、
わざわざ絶対参照を覚えなくとも、指定先のセルを都度変えれば済むことと思うかもしれません

それは、修正するセルが２～３箇所ならできること。
修正するセルが１万箇所、２万箇所の場合、同じことは言えません

よって、今後エクセルでの集計作業を行う際には、
絶対参照の知識は必須となります

難しい話ではないですので、是非この機会にマスターしてみてください！

基本的なケース

上図のようなケースで、C列に販売数✕単価を計算したいとします
販売数は行単位に記載がありますが、単価はE２のセルに共通の単価が記載されています

このようなケースでは、まずC2セルに数式を入力し、以下の行に数式コピーをしたいところです

ですが、絶対参照を使わないと次の図のようになります

B商店以降が０になってしまいます
理由は、単価のセル参照がズレてしまうからです (下図)

今回の場合は、単価（E2セル）は、関数をコピペした際に動いてほしくないです
つまり、E2セルを絶対参照にすればOKとなります

絶対参照を入力してみよう

図のとおりですが、まずコピー元となるセルの数式内の、単価（E2）セルを絶対参照にします

具体的な方法は、固定したい列または行（今回は両方） の左側に「＄」 を付ければよいです
今回は、E2セルを固定するので、$E$2 となります
これが、絶対参照です

あとは、数式をコピーして、B商店、C商店も販売金額が入るか確認するだけです

単価の参照先が固定されて、販売金額が入りましたね
以上が絶対参照の基本的な使い方です

絶対参照のショートカットキー

絶対参照をいちいち手入力するのって面倒ですよね
（私は、しばしば手入力してしまうのですが…）

ショートカットキーは、F4 です

図のように数式内の絶対参照にしたいセルにカーソルを合わせてF4キーを押せば良いです
なお、押す回数によって次のように変化します

１回　→$E$2
２回　→E$2
３回　→$E2
４回　→E2　　…以降、繰り返し

上記の違いは何か？
絶対参照は＄が付いている右側の列または行を固定します
$E$2であれば、E列と２行目を固定となり、
$E2であれば、E列は固定するが、２行目は固定しないとなります

絶対参照でパレート図を作成

それでは、パレート図の作成方法から、絶対参照の理解を深めましょう！
と、本題に入りたいところですが、
なぜ、パレート図なのか？

それは、絶対参照を理解することで容易に作成できるという以外にも、
エクセルで集計分析した情報を元に改善活動する際の基本的な考え方がパレート図には詰まっているからです

パレート図とは

パレート図とは何か？ をざっくりと説明しますと、
項目別に分類したデータを大きい順に並べたグラフです

図の場合、工場における不良品の発生を項目別に整理して、発生件数の大きい順にグラフ化しています

パレート図はQC活動において良く利用されますが、その他にも活用機会は売上、在庫、市場分析など多岐にわたります

それは、なぜか？
パレート図の作成目的は、改善するターゲットを絞ることにあるからです
いわゆる「集中と選択」 を行うための可視化ツールなのです

図の場合、異物混入、キズ、汚れが、不良全体の８割を占めています
もし、不良削減を目的に活動するのであれば、これら３項目を無視して良い結果が得られないのは明白です
闇雲に対策を講じるより、
「まずはこの３項目を対策しよう」 と言ったほうが、
意思統一もでき、大きな結果が期待できます

このように優先順位を可視化でき、他社との情報共有するツールとして非常に有効なのがパレート図です

パレート図の作り方

まず、元となるデータを準備しましょう
今回は、不良内容を項目別に整理したデータを例にご説明します
あなたがデータを準備する際の注意点は、
「漏れなく、ダブりなく」データを集計することです
それだけで、あなたの作成するパレート図の信頼性は確保されるでしょう

工程内不良内容	不良個数
異物混入	50
キズ	39
汚れ	26
洗浄不良	10
操作ミス	5
その他	4
合計	134

上図が、今回のサンプルデータです

この際に、データは降順（数値の大きい順）に並べておくと良いです
図の場合、不良個数を降順にしています

構成比を求めよう

まずは構成比を求めます

構成比とは、全体（合計）に対し、各々の項目が占める割合のことです

先に結果でイメージいただきたいですが、下図のようになります

それでは、構成比をどのような数式で計算したか見ていきましょう

まず、構成比とは、不良個数／不良個数の合計 で計算できます
（各項目が全体に占める割合のことです）

この時、合計は絶対参照により、固定することで、数式はコピペで作成できます

累積比を求めよう

次の求めるのは、累積比です

先に結果をご覧ください

構成比との違い、お分かりいただけたでしょうか？

構成比が項目ごとの合計に占める割合なのに対し、累積比は字のごとく累積です
構成比が上から順に足されたものが累積比です

このD列の関数も絶対参照を用いると１種類で表現できます

計算方法としては、一番上の項目（異物混入）から対象項目までの構成比を合計となります
つまり、C列をSUM関数を用いて求めることができます
ただし、開始位置であるC2セルは固定したいので、
図のように=SUM($C$2:C2)という関数になります

あとはグラフにすれば完成です（絶対参照を使うのはココまで）

グラフを作成しよう

それでは、グラフを作成しましょう
用いるのは下図で選択したエリアです

図のように、項目名、個数、累積比を選択してグラフ挿入ボタンを押しましょう
バージョンによりレイアウトは異なりますが私の場合、下図になります

挿入タブより、グラフを選択すれば良いです
おすすめグラフで表示されれば一発OKですが、今回は棒グラフを選択して、順追って解説します

まず、上図のようなグラフができますので、
累積比を折れ線グラフにして２軸で表示できるように変更を加えます

グラフツールオプション（デザイン）の
グラフの種類の変更というボタンをクリックします

ウィンドウが表示されたら、組み合わせを選択してください

次に累積比の項目を、
グラフの種類：折れ線
第２軸：レ点
に設定して、OKボタンで確定しましょう

プレビュー通りのグラフが表示されるので、
あとはレイアウトを調整すれば完了です
個人的には、２軸の値表示が１２０％までの表示が気になりますので、１００％を最大値に変更します

軸の書式設定より、軸のオプション内にある最大値を1.2→１に変更します

あとはグラフタイトルなど自由に変えればOKです

こんな感じで完成です

まとめ

いかがだったでしょうか？
絶対参照は、行または列、あるいはその両方を固定することで、様々な関数をコピペだけで量産できるようになります
データ量が多くなるほどに数式の修正は面倒になりますので、作り始めに絶対参照を駆使することで、ヒューマンエラーによる集計誤りの防止につながるケースは多いです

XlookupやSUMIFS関数とも相性が良い機能なので、ご存じなかったという方はこの機会にマスターしてみてください

＄マークの付け方に悩んだら、下記の表を参考にしてみてください

	A1	$A1	A$1	$A$1
列固定	☓	◯	☓	◯
行固定	☓	☓	◯	◯

お役に立てたなら幸いです
最後まで読んでいただき、ありがとうございました！