入力セルが○○の時に計算される値を書き出す

質問

お世話になります。以下、もし可能ならVBAを使わずにできる方法があれば（簡単ならVBAでも）、ご教示いただけると大変助かります。

47都道府県の各都道府県と他のどの都道府県が最も似ているかを見つけるに当たり、人口、面積の広さ、県民所得などいくつかの数値を基準に探します。たとえば、京都と似たものを探す時、他の46都道府県の人口、面積、所得と、京都の人口、面積、所得の差の二乗の和が最小になるものを類似都道府県とします。具体的には（人口差の２乗＋面積差の２乗＋所得差の２乗）÷３。数値はそれぞれ基準化しておきます。

一つの列（A列）で二乗和を求めて、最小値を探し出し、その値に該当する都道府県を表記するところまでは来ました。

47の都道府県に対し、47の答えを出すには47回インプットセルに入力して出てきた結果をコピペするか、47の列を作って解決するかのどちらかですが、できれば一つの列で計算をし、京都の場合に山口となるなら、都道府県を書いた別の列の「京都」の隣の列に「山口」が記入されるような形にしたいと思っています。

と言いますのは、本当にやりたい分析は、約1000のものに対して、それぞれ1000の答えが出てくるので、個別に列を作ってやる場合は1000列作る必要があるので、できれば避けたいと思っています。

「入力セルが○○の時に計算される値を書き出す」、というイメージで、もしWhen関数なるものが存在するなら、そういうイメージです。if関数、match関数などで何とかできないかと画策しましたが、うまく行っていません。

分かりにくくて恐縮ですが、よろしくお願いいたします。

回答No.2

DOUGLAS_ · ベストアンサー

Ａ列２行目から１０００行目に「約1000のもの」の名称が、Ｂ・Ｃ・Ｄ列に比較したい数値が入っているものとします。
　ちょっとまどろっこしい式ですが、Ｅ２セルに
=INDEX($A$2:$A$1000,MATCH(SMALL((B2-B$2:B$1000)^2+(C2-C$2:C$1000)^2+(D2-D$2:D$1000)^2,2),(B2-B$2:B$1000)^2+(C2-C$2:C$1000)^2+(D2-D$2:D$1000)^2,0))
と入力し、[Shift] + [Ctrl] + [Enter] で確定します(配列数式)。
　これをＥ列最終行までオートフィルしてください。

　式の成り立ちは、「人口差の２乗＋面積差の２乗＋所得差の２乗」の部分に当たる「基準」が
基準 = (B2-B$2:B$1000)^2+(C2-C$2:C$1000)^2+(D2-D$2:D$1000)^2
で、この部分を「基準」に置き換えると
=INDEX($A$2:$A$1000,MATCH(SMALL(基準,2),基準,0))
という式になります。

　この式は、[Shift] + [Ctrl] + [Enter] で確定し配列数式となっておりますので、「基準」は「定数」ではなくて「配列」になっています。
　従って、自分同士を比較したときの基準値が「0」になりますので、「最小値」が
最小値 = SMALL(基準,2)
となり、配列である「基準」の中から「二乗和を求めて、最小値を探し出」すと、その「位置」は
位置 = MATCH(最小値,基準,0)
となります。
　次に、この最小値の対象(相手)である名称はＡ列から拾うことになりますので、
該当者 = INDEX($A$2:$A$1000,位置)
となりました。

お礼

ありがとうございます！
ビンゴで出ました！感激です。これで睡眠時間が確保できそうです。

しかも、分かりやすい関数のご説明、ありがとうございます。完全に構造を理解することができて助かります。今後、いろんなことに生かせそうです。

ちなみにですが、1,000行ｘ4列の中に空白行が含まれている場合には#valueとなってしまいますが、空白行が含まれている場合でも計算できたりするものでしょうか？

回答No.1

sige1701

参考までに

　　　A　　　B　　　C　　　D
1　　秋田　　2　　　3　　　7
2　　　　　 人口　　面積　所得
3　　北海道　4　　　10　　　5
4　　青森　　1　　　2　　　3
5　　秋田　　2　　　3　　　7
6　　岩手　　3　　　7　　　4
7　　山形　　5　　　5　　　6
8　　新潟　　6　　　6　　　2
9

A1に調査地点を入力
B1=VLOOKUP($A$1,$A$3:$D$1000,COLUMN(),0)
といれ　D1　まででコピー

=INDEX(A:A,MOD(SMALL(INDEX(((B3:B10-B1)^2+(C3:C10-C1)^2+(D3:D10-D1)^2)*10000+ROW(A3:A10),),2),10000))

こんな感じかな

お礼

ありがとうございます！

このケースですと秋田に対して山形、という答えが出てきますが、もし可能であればEの列に北海道に対応する県、青森に対応する県、秋田に対応する県・・・と一度にダーッと並べられるといいなと思っているのですが、ここまで教えていただいたので、なんとか頑張ってみます。

Indexやmodなどの関数は初めて見ました。勉強になります。