English

Excelでジニ係数を計算

ジニ係数は数学的にサンプル由来の函数が定義されているので Excel で簡単に計算できます。Webメールでジニ係数の求め方について質問がありました。当初何のことか分からなったのですが、経済の低迷で所得格差が問題になっており不平等性を指数化したものの様です。 ジニ係数は 0 ～ 1 の間の数で 0 に近いほど平等、 0.5 では貧富の格差が相当に深刻だということらしいです。

ローレンツ曲線などでの詳しい定義は Web 上で調べてみてください。Excel を使って求めるのはかなり煩雑です。

高等数学で難しいですが で定義されているということなので Excel で次のように計算します。 ｙ1, ｙ2, …… ｙn は n 個のサンプルそれぞれの所得額、 f はその平均で、f = ( ｙ1 + ｙ2 …… + ｙn) / n です。

ｙ1, ｙ2, …ｙnに相当するデータを図の水色で塗りつぶしたセルに入力します。

この際、縦 b3～b7 セルを選択し Ctrl + C でコピーして C2 セルで右クリックして 「形式を選択して貼り付け」 を選び、『行列を入れ替える』にすると便利です。

★このサンプルでは、たった５人しか居ない村で各人の所得が１５万円、２５万円、４０万円…　と考えると不平等性が分かりやすいです。

c3 セルは 『 =abs(c$2-$b3) 』 と入力し、表中の全セルにコピーして貼り付けします。

この操作についてはExcel で掛け算の九九表の 例3 で触れました。

(注)ABS 関数は絶対値です。 | 15 - 40 |　の場合は 25 になります。

b8 のセルには、『 =sum(c3:g7) 』 が入力されていて に相当します。

平均欄の b9 セルには 『 =average(b3:b7) 』 が入っています。 また個数欄の b10 セルには 『 =count(b3:b7) 』 が入っています。

ジニ係数欄の b11 セルには 『 =b8/(2*b10*b10*b9) 』 で計算しています。

b11 セルは、 『 =b8/(2*b10^2*b9) 』 でも勿論 OK です。 (注) 『 ^2 』 は２乗のことで 『 ^3 』 は３乗になります。


サンプル数を増減させる場合は、水色で塗りつぶした表の部分の行および列を挿入、削除して新たなデータを入力するだけで他の部分は変更する必要がありません。 この際、最上端、最下端等は避けて挿入するのが無難です。平均などのセル参照範囲が変化します。 挿入した場合には C3 セル 『 =abs(c$2-$b3) 』 を空白セルにコピーする必要があります。

下の表の sample 以下をコピーして確かめられます。 sample の文字が a1 セルに入るようにします。

また、以下に示すようにマクロを使っても計算できます。VBA を起動しなくても直接セルに結果が表示されます。

sample
15254080180
15=ABS(C$2-$B3)=ABS(D$2-$B3)=ABS(E$2-$B3) =ABS(F$2-$B3)=ABS(G$2-$B3)
25=ABS(C$2-$B4)=ABS(D$2-$B4)=ABS(E$2-$B4) =ABS(F$2-$B4)=ABS(G$2-$B4)
40=ABS(C$2-$B5)=ABS(D$2-$B5)=ABS(E$2-$B5) =ABS(F$2-$B5)=ABS(G$2-$B5)
80 =ABS(C$2-$B6)=ABS(D$2-$B6)=ABS(E$2-$B6)=ABS(F$2-$B6) =ABS(G$2-$B6)
180=ABS(C$2-$B7)=ABS(D$2-$B7)=ABS(E$2-$B7) =ABS(F$2-$B7)=ABS(G$2-$B7)
累計 =SUM(C3:G7)
平均 (f)=AVERAGE(B3:B7)
個数 (n) =COUNT(B3:B7)
ジニ係数（ｇ） =B8/(2*B10*B10*B9)


VBA



Sub CalGini()
  Dim s As String, g, a
                      
       ' ジニ係数の計算 …  セルに直接 『 = gini(30,40, 60, 100) 』
       '『 = Gini(a3:g3) 』等と入力しても表示されます。
      
     s = "15, 25, 40, 80, 180"
  Do
     s = InputBox("所得額をコンマで区切ってタイプします。" & vbCr & _
        " 例 30, 40,60, 100   0 (ゼロ値) は無視されます。" & vbCr & _
        "0 のサンプルが必要な場合 0.0001 等を使うことにします。", _
        "☆所得額の入力　キャンセルで中止", s): If s = "" Then End
            
     g = Gini(s)
  
     a = MsgBox(s & " の場合 ジニ係数は " & Round(g, 3) & " です。" _
          & vbCr & "続行しますか?", vbYesNo): If a = vbNo Then End
  Loop
End Sub
Function Gini(ParamArray Dat() As Variant)
  Select Case VarType(Dat(0))
     Case vbVariant + vbArray
            n = Dat(0).Rows.Count * Dat(0).Columns.Count - 1
        ReDim Ym(n)
            For i = 0 To n: Ym(i) = Dat(0)(i + 1):   Next:  Yn = Ym
     Case vbString:                 Yn = Split(Dat(0), ",")
     Case Else:                     Yn = Dat
  End Select
        m = UBound(Yn):  sum1 = 0:  sum2 = 0:  n = 0
     For i = 0 To m
        If IsNumeric(Yn(i)) Then Yn(i) = Val(Yn(i)) Else Yn(i) = 0
            If Yn(i) > 0 Then n = n + 1: sum1 = sum1 + Yn(i)
     Next
     For i = 0 To m
        For j = 0 To m
          If Yn(i) * Yn(j) > 0 Then sum2 = sum2 + Abs(Yn(i) - Yn(j))
        Next
     Next:    If n = 0 Then Gini = 0: Exit Function
        f = sum1 / n:         Gini = sum2 / (2 * n ^ 2 * f)
End Function

余録

Excel には多くの関数が用意されていますがもし、絶対値を取得する ABS 関数が無ければ c3 セルには 『 = if(c$2-$b3>0, c$2-$b3, $b3-c$2) 』などで、また AVERAGE 関数が無ければ b9 セルは 『 = sum(b3:b7)/b10 』 で 代用することができます。

多くの人が首を傾げる関数に BAHTTEXT 関数があります。通貨としてタイバーツ を使うのは世界でも少数だからです。『 = bahttext(1234) 』 で 『 หนึ่งพันสองร้อยสามสิบสี่บาทถ้วน 』 と出力されます。

負けじと、『 = yentext(1234) 』 で 『 金壱阡弐百参拾四円也 』 と表示するプライベートなファンクションを 次のように作成してみました。 


Function YenText(y)
    Ar1 = Array(22769, 24336, 21442, 22235, 20116, 20845, 19971, 20843, 20061)
          '注） 壱     弐     参      四     五     六     七     八     九
    Ar2 = Array(ChrW(-27103), "", ChrW(25342), ChrW(30334))
          '注）  阡  "" 拾  百  comments may be garbled depending on the OS
        s = Trim(Str(y)):   n = Len(s):   ch = ""
            If Len(s) > 15 Then YenText = "#OvFl_Er": Exit Function
  For i = 1 To n:      a = Val(Mid(s, i, 1)):   k = n - i + 1:   m = k Mod 4
     If a > 0 Then ch = ch & ChrW(Ar1(a - 1)) & Ar2(m): j = 1
     If k = 13 Then ch = ch & ChrW(20806):              j = 0       '兆
     If k = 9 And j = 1 Then ch = ch & ChrW(20740):     j = 0       '億
     If k = 5 And j = 1 Then ch = ch & ChrW(-31700)                 '萬
  Next
      YenText = ChrW(-28207) & ch & ChrW(20870) & ChrW(20063) '金 & ch & 円也
End Function

SUM 関数は 複雑な機能をもつ代表的な関数です。代替えとして、拙い次の GOKEI 関数はどうでしょうか。使えたとしても処理速度には雲泥の差があります。 

Function GOKEI(ParamArray d())
   s = 0
 For i = 0 To UBound(d)
      If IsNumeric(d(i)) Then s = s + Val(d(i))
   If VarType(d(i)) > 8000 Then
        n = d(i).Columns.Count * d(i).Rows.Count
       For j = 1 To n
                              s = s + d(i)(j)
       Next
   End If
 Next
       GOKEI = s
End Function

（注） Excel 関数では大文字と小文字の違いを区別しません。このページでは原則として、関数名は大文字、セル入力は小文字で表記しました。

このサイトで扱った項目