VBA

VBA for Excel



RAS

さらにディープなレオンチェフマニア用。この時点で何のことだかわからない人はパスしてください。

産業連関表の作成に必要なMatrix Balancingを、収束計算によって求めるRAS法と、解析的に求めるKEO-RAS法のExcel用マクロです。

Matrix Balancing

下の図のような行列を考えよう。 ここに、X0 = [x0ij, i = 1,...,m, j = 1,...,n]は、m 行 n 列の行列、 Z0 = [z0j, j = 1,...,n]は行列 X0 の各列の和を要素とする長さ n のベクトル、 Y0 = [y0i, i = 1,...,m]は行列 X0 の各行の和を要素とする長さ m のベクトルである。

X0Y0Y
Z0'
Z'

たとえば、行列 X0 は産業連関表の財貨・サービスの取引表の部分で、 ベクトル Y0 および Z0 は列および行のコントロール・トータルである。 この行列 X0 とベクトル Y0、Z0 の間には、次のようなバランスに関する制約が成立している。 行列の各列および行に関して、
n
Σ
j = 1
x0ij = y0i ,  (i = 1,...,m)
m
Σ
i = 1
x0ij = z0j ,  (j = 1,...,n)
が成立し、さらに行列全体のバランスより、
m
Σ
i = 1
y0i = n
Σ
j = 1
z0j
が成立している。

ここで一般にMatrix Balancingの問題は次のように定式化される。

m×n の行列 X0、m 次のベクトル Y、n 次のベクトル Z が与えられたとき、 X0 の近似として
n
Σ
j = 1
xij = yi ,  (i = 1,...,m)
(1)
m
Σ
i = 1
xij = zj ,  (j = 1,...,n)
(2)
を満たすXを求めよ。ただし、ベクトル Y と Z は、
m
Σ
i = 1
yi = n
Σ
j = 1
zj
(3)
を満たす。

たとえば、産業連関表のコントロール・トータルに関して新しい観察値 Y、Z が得られるが、 行列の要素 xij に関しては対応する観察値が得られない場合などが具体的な問題である。
このようなMatrix Balancingの問題を解くアルゴリズムとして計算負荷の少ない2つの方法として、 スケーリング手法によるRAS法と最適化によるKEO-RAS法について説明する

RAS法

RAS法は、行と列の両面から制約(1)と(2)を満たすまで行列X0をスケーリングして、新しい行列を求めるアルゴリズムである。 RAS法のアルゴリズムは次のように書くことができる。

Step 0
k = 0とする。


Step 1
(行のスケーリング) i = 1,...,m に関して
rik =
yi
Σjxijk
を計算して
X k ^
R
· X k
( ^ )は、ベクトルを対角線上に並べた正方行列
でXkを更新する。


Step 2
(列のスケーリング) j = 1,...,n に関して
sjk =
zj
Σixijk
を計算して
X k+1 = X k · ^
S
( ^ )は、ベクトルを対角線上に並べた正方行列
でXk+1を定義する。


Step 3
十分に小さな許容水準εに対して、max ( |1 - r1|,...,|1 - rn|,|1 - s1|,...,|1 - sm| ) < εが満たされたら終了。 さもなくば、k ← k + 1 としてStep 1に戻る。

[使用方法]

  1. データ範囲を選択します。自動的に選択範囲の右端列が列CT、下端行が行CTと認識されるので、 最初の選択範囲に含めておくと設定が楽です。
  2. [ツール]-[RAS]-[RAS]コマンドを選択します。

  3. 行列範囲、貼付け先、各種オプションを設定し、[OK]をクリックします。

    [RAS]ダイアログボックス
    -[比率行列] ;投入係数行列範囲を指定します。 起動時には最初の選択範囲の右端列、下端行を除いた範囲が指定されています。
    -[行CT,列CT] ;行CT,列CT範囲を指定します。 起動時には最初の選択範囲の右端列,下端行がそれぞれ列CT,行CTとして指定されています。
    -[出力先] ;出力先範囲を指定します。 左上セルだけ指定すれば、自動的に必要な範囲に出力されます。
    -[集計] ;チェックを入れると、出力先の右側と下側にSUM関数による集計を作成します。 右下端セルにはCOUNTIF関数によるマイナス値のセル数チェックが作成されます。
    -[オプション]
    -[0値の置換] ;チェックを入れると、係数行列の0値を、指定した値に置換します。
    -[収束判定変化率] ;収束判定の変化率 ε を指定します。
    -[最大反復数] ;反復数の最大値を指定します。 (行のスケーリング+列のスケーリング)で1回とカウントします。
    -[警告ダイアログ] ;チェックを入れると、各種の警告ダイアログを表示します。 複数シートグループでの連続処理を行うとき、 チェックを外しておくと途中で中断されることがなくなります。
  4. 「警告ダイアログの表示」にチェックを入れてあると、計算結果のレポートが表示されます。
  5. 計算結果が貼り付けられます。
注1)
別のブックへの貼り付けは出来ません。同じブック内に一時的に貼り付けてから移動して下さい。
注2)
複数シートグループでの作業では、各シート内の指定アドレスへ貼り付けます。
注3)
行CTと列CTの総計が一致していないとエラーになります。
注4)
与えられた比率行列とCTに論理的な矛盾があると判断される場合、 その問題の原因となる部分だけを指定した出力範囲に出力します。
注5)
最大反復回数に達しても収束しなかった場合、その出力結果に対してKEO-RAS法を実行するとうまく解ける場合があります。
KEO-RAS法

行列 X0 から計算される m×n 縦比(投入係数)行列 A と m×n 横比(配分係数)行列 D を次のように定義する。
A = X 0 · ^
Z
-1
(4)
D = ^
Y
-1 · X 0
(5)
( ^ )は、ベクトルを対角線上に並べた正方行列
Matrix Balancingの問題を解いた結果得られる行列 X から計算される縦比(投入係数) xij / zj と 原行列 X0 から計算される縦比(投入係数) aij の加重残差自乗和、 および X から計算される横比(配分係数) xij / yi と X0 から計算される 横比(配分係数) dij の加重残差自乗和を同時に最小化するように 行列 X を決定することを考えよう。 それは、次のような等号制約付き最小化問題で表現できる。
min
1
2
m
Σ
i = 1
n
Σ
j = 1
xij
zj
- aij 2 · -
w
 
ij
+
1
2
m
Σ
i = 1
n
Σ
j = 1
xij
yi
- dij 2 · ~
w
 
ij
(6)
s.t. yi = n
Σ
j = 1
xij  (i = 1,...,m)
zj = m
Σ
i = 1
xij  (j = 1,...,n)
ここで、 -
w
 
ij
~
w
 
ij
は、X0, Y0, Z0から得られる適当なウェイトである。
KEO-RAS法とは、ウェイトを
-
w
 
ij
=
1
aij2
  (i = 1,...,m, j = 1,...,n)
~
w
 
ij
=
1
dij2
  (i = 1,...,m, j = 1,...,n)
と定式化することによって、この最小化問題を解く方法である。 即ち、n×1 ベクトル λ、m×1 ベクトル ζ をLagrange乗数ベクトルとすると、Lagrange関数 ψは、
ψ =
1
2
m
Σ
i = 1
n
Σ
j = 1
xij
aijzj
- 1 2 +
1
2
m
Σ
i = 1
n
Σ
j = 1
xij
dijyi
- 1 2
(7)
+ n
Σ
j = 1
λj zj - m
Σ
i = 1
xij + m
Σ
i = 1
ζi yi - n
Σ
j = 1
xij
となる。
最小化の必要条件より、
∂ψ
∂xij
=
1
aijzj
xij
aijzj
- 1 +
1
dijyi
xij
dijyi
- 1 + λj + ζi = 0
(8)
(i = 1,...,m, j = 1,...,n)
∂ψ
∂λj
= zj - m
Σ
i = 1
xij = 0     (j = 1,...,n)
(9)
∂ψ
∂ζi
= yi - n
Σ
j = 1
xij = 0     (i = 1,...,m)
(10)
(8)式を xijについて解いて、
xij = tijsij - λjsij - ζisij  (i = 1,...,m, j = 1,...,n)
(11)
ここで、
sij =
dij2yi2 aij2zj2
dij2yi2 - aij2zj2
  (i = 1,...,m, j = 1,...,n)
tij =
1
dijyi
+
1
aijzj
  (i = 1,...,m, j = 1,...,n)
である。 (11)式を最小化の必要条件(9)式と(10)式に代入して、
m
Σ
i = 1
tijsij - λj m
Σ
i = 1
sij - m
Σ
i = 1
ζi sij = zj    (j = 1,...,n)
(12)
n
Σ
j = 1
tijsij - n
Σ
j = 1
λj sij - ζi n
Σ
j = 1
sij = yi     (i = 1,...,m)
(13)
を得る。 これは、λ と ζ に関する (n + m) 本の連立方程式体系である。 しかし、(3)式より、このうち1本は一次従属であるから、一般性を失うことなく ζm = 0 とすることができる。 行列表示して、
m
Σ
i = 1
si1
s11s21sm1
m
Σ
i = 1
si2
s12s22sm2
·  
 · 
  ·
·
·
·
·
·
·
·  
 · 
  ·
·
·
·
m
Σ
i = 1
sin
s1ns2nsmn
s11s12s1n
n
Σ
j = 1
s1j
s21s22s2n
n
Σ
j = 1
s2j
·
·
·
·
·
·
·  
 · 
  ·
·
·
·
·  
 · 
  ·
sm1sm2smn
n
Σ
j = 1
smj
λ1
·
·
·
·
·
·
λn
ζ1
·
·
·
·
·
·
ζm
=
m
Σ
i = 1
si1ti1 - z1
·
·
·
·
·
·
m
Σ
i = 1
sintin - zn
n
Σ
j = 1
s1jt1j - y1
·
·
·
·
·
·
n
Σ
j = 1
smjtmj - ym
(14)
となる。 先に述べたように体系の一次従属性より実際に解かれるのは最初の (n + m - 1) 本である。 この体系を (n + m - 1) 本について解いた後に ζm = 0 として、(11)式に代入することによって バランスのとれた行列 X を求めることができる。 このようにKEO-RAS法は、RAS法と異なり推計上収束計算に頼ることがないという長所がある。 また、ウェイトを
-
w
 
ij
=
zj
aij
  (i = 1,...,m, j = 1,...,n)
~
w
 
ij
= 0   (i = 1,...,m, j = 1,...,n)
とすることにより、解がRAS法の近似解になることが示される。

[出典]
黒田昌裕,新保一成,野村浩二,小林信行(1996), "KEOデータベース", 慶應義塾大学産業研究所, p96-100

[使用方法]

  1. データ範囲を選択します。自動的に選択範囲の右端列が列CT、下端行が行CTと認識されるので、 最初の選択範囲に含めておくと設定が楽です。
  2. [ツール]-[RAS]-[KEO-RAS]コマンドを選択します。

  3. 行列範囲、貼付け先、各種オプションを設定し、[OK]をクリックします。

    [KEO-RAS]ダイアログボックス
    -[比率行列]
    -[投入(縦比)] ;投入係数行列範囲を指定します。 起動時には最初の選択範囲の右端列、下端行を除いた範囲が指定されています。
    -[配分(横比)] ;配分係数行列範囲を指定します。起動時には投入係数範囲が指定されています。 別に指定する場合は、左上セルだけを指定すれば 自動的に投入係数と同サイズの範囲が取得されます。
    -[行CT,列CT] ;行CT,列CT範囲を指定します。 起動時には最初の選択範囲の右端列,下端行がそれぞれ列CT,行CTとして指定されています。
    -[出力先] ;出力先範囲を指定します。 左上セルだけ指定すれば、自動的に必要な範囲に出力されます。
    -[集計] ;チェックを入れると、出力先の右側と下側にSUM関数による集計を作成します。 右下端セルにはCOUNTIF関数によるマイナス値のセル数チェックが作成されます。
    -[オプション]
    -[0値の置換] ;チェックを入れると、係数行列の0値を、指定した値に置換します。
    -[警告ダイアログ] ;チェックを入れると、各種の警告ダイアログを表示します。 複数シートグループでの連続処理を行うとき、 チェックを外しておくと途中で中断されることがなくなります。
  4. 計算結果が貼り付けられます。
注1)
別のブックへの貼り付けは出来ません。同じブック内に一時的に貼り付けてから移動して下さい。
注2)
複数シートグループでの作業では、各シート内の指定アドレスへ貼り付けます。
注3)
行CTと列CTの総計が一致していないとエラーになります。
注4)
与えられた比率行列とCTに論理的な矛盾があると判断される場合、 その問題の原因となる部分だけを指定した出力範囲に出力します。
注5)
配分係数行列範囲は、自動的に投入係数と同サイズの範囲が取得されます。
注意事項

プログラム本体はAddinですが、パスワードは設定してありません。 メインメニューの[ツール]-[アドイン...]から登録できます。 あるいは、[\Program Files\Microsoft Office\Office\XLStart]に置いておくと、 Excel起動時に自動的に読み込まれます。

Office2000,XPの場合は[ツール]-[マクロ]-[セキュリティ]から セキュリティダイアログボックスを呼び出し、セキュリティーレベルを[中]に設定してください。 ファイルを開くときに、マクロを有効にするか問い合わせがあるので [マクロを有効にする]を選択してください。

実際に使用する前にテストを行い、どのように動作するのか把握しておいて下さい。 なお、本プログラムによってどのような障害が出たとしても、 作者は一切の責任を負いません。

アドインおよびそのソースコードに関する著作権はDamfinoにあります。 作者の許可なく配布、掲載、販売を目的に利用することを禁じます。

Download

クリックしてファイルに保存して下さい。 lzh形式で圧縮してあるので、lhasaなどで解凍してください。
     --->Ras_jp.lzh (日本語版)
     --->Ras.lzh (English Version)

最後に

ご意見、ご要望があればメール・BBSでお知らせ下さい。



Program Top