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

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

How-to

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

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

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

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

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

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

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

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

元データ

晴花

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

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

説明のみ

晴花

13

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

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

と入力します。

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

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

晴花

14

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

  • =G3/$G$26

と入力します。

入力できたら表の最下行までコピーし、ついでにパーセントスタイルを適用しておきます。値そのものは「累積相対人数」列と同じものになっているはずです。

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

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

晴花

15

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

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

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

晴花

16

そして、リボンの挿入タブグラフグループにある散布図ボタンをクリックします。

2013

2016

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

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

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

晴花

17

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

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

晴花

18

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

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

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

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

晴花

19

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

これ と これ です。

説明のみ

晴花

20

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

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

説明のみ

晴花

21

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

上の式(赤の面積/青の面積)の分子分母にそれぞれ 2 をかけてやると、ジニ係数は赤の面積の 2 倍と等くなることが示されますね…。ここでは上に示した式で計算していきますが、こっちでもOKです。

説明のみ

晴花

22

いずれの方法にしろ…赤の面積を知る必要があります。…ただ、赤の面積を直接計算するのはややこしそうです。したがって、ここでは赤の面積を下の図のような手続きで計算することとします

説明のみ

晴花

23

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

晴花

24

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

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

説明のみ

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

晴花

25

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

  • (上底+下底)× 高さ ÷ 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.5 - 0.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

2016.7.12

.
このページの先頭へ