ひとりマーケティングのためのデータ分析

手順解説 | Excel(エクセル)でおこなうビジネスデータの分析

How-to

ローレンツ曲線と
ジニ係数

経済学のテキストではおなじみな感のあるローレンツ曲線とジニ係数は,所得分配の不平等さを計るための指標です。そうした性質から,事務しごとで…というよりも,行政組織や公益団体のレポートなどにおいて,いわゆる「所得格差」を検証するためのツールとして用いられているのをよく見かけます。

ローレンツ曲線は,横軸に累計の人数,縦軸に所得の累計からなるグラフにプロットされる(曲)線です。所得の小さな人から順番に積み上げるという決まりのもと,所得の累積値と人数の累積値との関連から(曲)線の特徴が定まっていきます。もっとも,すべての国民の所得のように人の数(つまり項目値)が膨大となる場合,一般的には全体をいくつかに等分した要約値が用いられることが多いようです。

ジニ係数は,ローレンツ曲線から計算される値です。もし,すべての人がまったく同じ所得であると仮定すると,ローレンツ(曲)線は一定の傾きをもつことになります。仮にこの条件を満たす線を「均等所得線」と呼ぶものとします。ローレンツ(曲)線がこの均等所得線と一致することを「格差が存在しない」状態と定めるなら,転じて格差が存在する状態では傾きが一定ではなくなる,ということになります。ごく限られた一部の人だけに富が集中する状態では,ローレンツ曲線は右下方に大きくふくらんだ(凸)ような形となります。ジニ係数は,ここでいう「格差が存在する」状態のローレンツ曲線と「格差が存在しない」状態のローレンツ(曲)線(均等所得線)」を含むグラフ上の領域の面積を除して計算していきます。

このとき「格差が存在しない」状態ではジニ係数は0に,「格差が存在する」状態ではその程度が大きいほど1に近づいていくことになります(ただしこのページの方法では完全に1になることはありません)。

ただ,この数字をどう判断するかについては,「格差とは」の定義が必要となるでしょう。このサイトでは手の届かない議論ですので,その点は踏み込んでおりません。

ここでは,ローレンツ曲線とジニ係数を「所得格差」でなく販売担当部員間での売上にみるばらつき,いうなれば会社の中での「営業力差異」を時間的な変化のもとで概見する趣旨から使用するものです。当然本来の趣旨に沿ったものではありませんので,使い方としては的外れなものになります。したがって結論としては参考程度のものとして用いることを前提としていますのでご注意ください。

以下,エクセルによるローレンツ曲線の作り方とジニ係数の計算方法です。ここでは一連の手続きをExcel 2016で追っています。Excel 2013,Excel 2010およびExcel 2007についても基本的には同じ流れとなりますが,一部ボタンの配置や名称などが異なる箇所があります(この場合,可能であれば当該箇所に明記します)。

元データ

晴花

1

元のデータです。

この年6月の販売担当部員の売上を集計したものです(単位:円)。


元データ・ローレンツ曲線とジニ係数

小さな値から順にならべる

晴花

2

「売上」データの昇順に並べ替えます。「売上」の任意のデータを選択し

晴花

3

リボンのデータタブ並べ替えとフィルタグループにある昇順ボタンをクリックします。

これによりデータが昇順に並べ替えられます。

データ→昇順

表の基部をつくる

晴花

4

あたらしく見出しを追加します。

ここではセルC1から向かって右に「累積人数」「累積相対人数」「累積売上」「累積相対売上」「累積均等売上」「累積相対均等売上」の順に6個作成しています。

scrollable

[セルC1]累積人数,[セルD1]累積相対人数,[セルE1]累積売上高,[セルF1]累積相対売上高,[セルG1]累積均等売上高,[セルH1]累積相対均等売上高

晴花

5

見出しとデータの間にあたらしい行を挿入します。

つづいて,この行の「累積人数」から「累積相対均等売上」列のすべてに0(ゼロ)を入力していきます。

ここで0を挿入する理由はローレンツ曲線を原点から引くためです。

行の挿入後,[セルC2:H2]0

「人の数」を積み上げる

晴花

6

では,最初にこの強調部分を作成していきます。ここで「累積相対人数」は,“人の数”に関して,当該行時点での累積値の全体に占める割合を示すものです。

説明のみ

晴花

7

累積人数」列にリストの24人分の値を連続データで作っていきます。

晴花

8

累積相対人数」列については= 累積人数 ÷ 総人数となる式を配置します。

具体的には,セルD3

  • =C3/$C$26

と入力します。このとき,「総人数」にあたるセル(ここではC26)はコピーすることを考慮して絶対参照としておきます。

この式を表の最下行までコピーし,ついでにパーセントスタイルを適用します。

[セルD3]=C3/$C$26。この式をセルD26までコピー

「金額」を積み上げる

晴花

9

続いてはこの強調部分を作成していきます。ここで「累積相対売上」は,“売上”に関して,当該行時点での累積値の全体に占める割合を示すものです。

説明のみ

晴花

10

累積売上」列については,先頭行のみ「売上」の先頭データを参照する式を,次行以降は= 直前の累積売上高 + 当該行の売上高となる式を配置します。

具体的には,セルE3

  • =B3

と,セルE4

  • =E3+B4

と入力します。

これらを入力したのち,E4の計算式だけを表の最下行までコピーします。

[セルE3]=B3, [セルE4]=E3+B4。セルE4の式をセルE26までコピー

晴花

11

累積相対売上」列については,= 当該行の累積売上高 ÷ 総売上高となる式を構成します。具体的には,セルF3

  • =E3/$E$26

と入力します。

この式を表の最下行までコピーし,あわせてパーセントスタイルを適用しておきます。

[セルF3]=E3/$E$26。この式をセルF26までコピー

差はないと仮定して再度「金額」を積み上げる

晴花

12

今度はこの強調部分を作成していきます。この部分は「均等所得線」「均等分配線」などと呼ばれる,一定の傾き(グラフが正方の場合は45°)をもつ直線をグラフに描画するために用います。ここでは,さしずめ「均等売上線」とでも呼んでおこうと思います。

説明のみ

晴花

13

累積均等売上」列については,総売上から1人当たりの売上を求め(平均売上),これを累積値として表示するものです。したがって= 総売上高 ÷ 総人数 × 当該行の累積人数となる式で計算します。

具体的には,セルG3

  • =$E$26/$C$26*C3

と入力します。

この式を表の最下行までコピーします。

scrollable

[セルG3]=$E$26/$C$26*C3。この式をセルG26までコピー

晴花

14

累積相対均等売上」列については= 当該行の累積均等売上 ÷ 総売上(ただしここではG26のセルを使用)となる式を配置します。

具体的には,セルH3

  • =G3/$G$26

と入力します。

この式を表の最下行までコピーし,加えてパーセントスタイルを適用しておきます。このとき,「累積相対均等売上」列の値そのものは「累積相対人数」列のそれと一致するはずです。

[セルH3]=G3/$G$26。この式をセルH26までコピー

「散布図」を使ってローレンツ曲線他を図示する

晴花

15

ではこの表をもとに,ローレンツ曲線と均等売上線からなるグラフを作成します。

グラフ化に必要なデータ範囲は「累積相対人数」列,「累積相対売上」列,および「累積相対均等売上」列の3つです。

したがって下のハイライトの領域を選択しておきます。

晴花

16

リボンの挿入タブグラフグループにある散布図(X, Y)またはバブルチャートの挿入ボタンをクリックします。

2010

2007

挿入タブグラフグループにある散布図ボタン

つづいて,プルダウンから散布図(直線とマーカー)ボタンを選択します。

挿入→散布図→散布図(直線とマーカー)

晴花

17

グラフが作成されました。

ここでグラフの横軸は「相対人数(人数の累積比)」を,縦軸は「相対売上(売上の累積比)」をあらわしています。ここでいう青線がローレンツ曲線,オレンジの線が均等売上線です。

この時点でのグラフのイメージ

晴花

18

グラフを任意に調整していきます。

ここでは,主に下の図のような数個の点について設定を加えることにしました。

  • 縦・横軸とも最小値 0,最大値 1
  • 両軸の表示形式 パーセント
  • プロットエリア 枠線を作成,目盛線削除,できるだけ正方に

なお,この場合のグラフの“主役”がローレンツ曲線であることを慮れば,さらなる手数を費やせるなら望ましいだろうなーと私が思う処理として,参考までに

  • 系列「累積相対売上」を最前面に移動(系列の順序設定)

を加えておきます。

「図形の面積」について考える

晴花

19

次はグラフの中の2つの図形に注目してみたいと思います。

これ と これ です。

説明のみ

晴花

20

ジニ係数は上の図の「赤の面積÷青の面積」で求めることができます。

全体を1としたとき(タテ100%× ヨコ100%= 1),青の面積はその半分なので0.5というのはすぐ分かるのですが,赤の面積はちょっと悩みますNe

説明のみ

晴花

21

あと,ええと,参考としてですけど青の面積が0.5という点に着目すれば,ジニ係数は下の式でも計算できることがわかります。

上の式(赤の面積/青の面積)の分子分母にそれぞれ2を掛けてやると,ジニ係数は赤の面積の2倍と等くなることが示されます。ということで,下の式でもOKなワケですが,ここでは先に上で示した方の式を使用していきたいと思います。

説明のみ

晴花

22

いずれの方法にしろ,赤の面積を知る必要があります。とはいえ,赤の面積を直接計算するのはややこしそうです。

したがって,ここでは赤の面積を下の図のような手続きで計算することとします

説明のみ

晴花

23

この最初の三角形の面積は0.5だと分かってますので,ローレンツ曲線下方の面積 部分の面積が計算できればOKというわけです。

晴花

24

となれば,ローレンツ曲線下方の面積 部分(ローレンツ曲線下方)の面積の計算方法について考えておきたいと思います。

ローレンツ曲線下方の面積は,ひとつに下の図のように小さな台形の集合によってできていると考えることができます。そこで11つの台形の面積を求め,後に合算するという方法でいきたいと思います。


より忠実には,グラフにいう左端の「台形」は三角形です。

説明のみ

ローレンツ曲線より下の領域=小さな台形の集合

晴花

25

その11つの台形の面積ですが台形の面積を求めるための公式は

  • (上底+下底)× 高さ ÷ 2

でした。

そこで公式の各要素を,ここで扱う台形に下の図のようにあてはめたいと考えます。

説明のみ

台形の面積の公式=(上底+下底)*高さ/2

晴花

26

この公式の各要素と表の具体的なデータとの対応はこのようになります。

 [上底]… 1 つ前の累積相対人数に対応する累積相対売上の値

 [下底]… 当該箇所の累積相対売上の値

 [高さ]… 当該箇所の累積相対人数の値と,直前の累積相対人数の値との差

説明のみ

ジニ係数をみちびく

晴花

27

と,いうことで元の表の方に視点を移します。

表の右に,あたらしい見出しとなる「台形の面積」を用意します。

さらに,この見出しの直下のセルに0(ゼロ)を入力しておきます。

[セルI1]台形の面積, [セルI2]0

晴花

28

公式に当てはまるよう計算式を組み立てます。

具体的には,セルI3

  • =(F2+F3)*(D3-D2)/2

と入力します。

入力できたらこれを表の最下行までコピーします。

scrollable

[セルI3]=(F2+F3)*(D3-D2)/2。この式をセルI26までコピー

晴花

29

個々の台形の面積を合計すると,下のような結果となります。

これがローレンツ曲線下方の面積 部分(ローレンツ曲線下方)の面積ということになります。

ローレンツ曲線下方の面積=0.41429109

晴花

30

ということで,赤の面積は0.50.41=0.09と計算できました。

0.5-0.41=0.09

晴花

31

そしてジニ係数赤の面積 ÷ 青の面積 でしたので

0.09÷0.5=0.18と求められます。

0.09/0.5=0.18(これがジニ係数)

晴花

32

ローレンツ曲線のグラフの作成,およびジニ係数の計算,完了です。

FINISHED

ローレンツ曲線のグラフ・ジニ係数の計算 完成

  • 本頁で使用したデータはすべて架空のものです。また特定の会社等に実在する人物名,および同場所で実際に観測されたデータ群などを根拠にしたものでもありません。
.

LastUpdate

2017.3.30

.
このページの先頭へ