エクセル：複数の不明なデータを平均で埋める

質問

エクセルについて質問させてくださいませ。
ネットで数日間調べたのですが、いまいちわかりません。

各国の複数年のジニ係数など、莫大な量のデータを扱っているのですが、
データが入手できない年が多々あります。
そこで、とりあえずそのような年（＝空白のセル）にはその前後の年からの平均を入れたいと思っています。
しかし、数値が不明な年が複数年続く場合には前の年から後の年への推移を入れたいのですが、方法が分かりません。

例えば、
Ａ国：　１、不明、３、不明、５とある場合なら、１と３の平均をとって２、３と５の平均で４が入るのですが、
Ｂ国：　１、不明、不明、不明、５とある場合に、１と５から予測して、１，２，３，４，５を導く方法はないでしょうか？

データの量が多いので、もし一括で出来るような方法があれば尚嬉しいです。

分かりにくい説明で申し訳ございませんが、何卒よろしくお願い致します。

回答No.1

keithin · ベストアンサー

計算自体はごく簡単な中学(?)の式ですが，「一括で出来る方法」がエクセルの構造上ありません。
エクセルの関数や数式には，自分の上や下にあるのが「生数字」なのか「（自分と同じ）式なのか」を見分ける事が出来ないからです。

という訳で，一括にしたいのが重要ならマクロを併用する必要があります。
ところで平均は「上下」で見たいのか「左右」方向で見たいのか，ご相談に書かれていません。
とりあえず上下方向だけ書いておきますので，実は左右だったときは適宜修正して利用してください。

手順：
マクロのセキュリティを中にしてエクセルを再起動する
ALT+F11でVBE画面を出す
挿入メニューから標準モジュールを選ぶ
現れたシートに下記をコピー貼り付ける

Function myavrV()
　Dim x As Long
　Dim s As Long
　Dim e As Long
　Dim c As Long
　Dim LB As Long
　Application.Volatile
　x = Application.Caller.Row
　s = x
　e = x
　c = Application.Caller.Column
　LB = Cells(65536, c).End(xlUp).Row
　Do
　s = s - 1
　Loop Until s < 2 Or Not Cells(s, c).HasFormula
　Do
　e = e + 1
　Loop Until e > LB Or Not Cells(e, c).HasFormula
　myavrV = (Cells(e, c) - Cells(s, c)) / (e - s) * (x - s) + Cells(s, c)
End Function

終了してエクセルに戻り，表中の空欄に
=myavrV()
とまとめて記入する。

お礼

早速のご対応ありがとうございます。
エクセルは毎日のように使うのですが、マクロに苦手意識がありました。
「左右方向」だったのでこちらで対応して、
教えて頂いた方法で解決させて頂きました。

しかし、実はこの方法だと表の最後の数字以降に、見当違いな予測が記入されます。
説明が難しいのですが、
１、不明Ａ、不明Ｂ、不明Ｃ、５の場合は問題が無いのですが、
１、不明Ａ’、不明Ｂ’、４、不明Ｃ’のように、表の最後の数字（この場合は４）以降に不明な蘭があった場合、不明Ｃ’に平均ではない何らかの数字（有意とは思えない数字）が書き込まれてしまいます。
これはこちらで適宜削除していきましたが、今後大量のデータでご活用なさる方は、ご注意くださいませ。