excel 式

質問

ｅｘｃｅｌで2点間の距離を表す式
例えば　ｄとｂの交点の(11)には(11)の式が入っておりd点とb点の座標から
d点とb点の距離をいわゆるピタゴラスの距離の式で求める式がきちんと
入っています。また同じ点同士の距離「対角線の交点は０です。
　　ａ ｂ ｃ　　　ｄ　　ｅ　　ｆ　ｇ　　（ｈ）
ａ　　(1)　(2)　　　(3)　　　(4)　　(5)　　(6)　(7)　　＊1
ｂ　　(8)　(9)　　　(10)　　　(11)　　(12)　　(13)　(14)　　＊2
ｃ
ｄ
ｅ
ｆ
ｇ
(2)=SQRT((D7-$D$6)*(D7-$D$6)+(E7-$E$6)*(E7-$E$6))
(3)=SQRT((D8-$D$6)*(D8-$D$6)+(E8-$E$6)*(E8-$E$6))
(4)=SQRT((D9-$D$6)*(D9-$D$6)+(E9-$E$6)*(E9-$E$6))
(5)略
(6)略
(7)=SQRT((D12-$D$6)*(D12-$D$6)+(E12-$E$6)*(E12-$E$6))
(11)=SQRT((D9-$D$7)*(D9-$D$7)+(E9-$E$7)*(E9-$E$7))
(12)略
(13)略
(14)=SQRT((D12-$D$7)*(D12-$D$7)+(E12-$E$7)*(E12-$E$7))
（さて問題は）
今7点同士なのですがここで点を8点に増やしたとき、その式を当然
1点座標を定義しているセルを増やし、式をひとつずつ書いて左下のセルは対角線で対象なセルに等しいとすればいいわけですが
ｈを追加したとき
*1=SQRT((D13-$D$6)*(D13-$D$6)+(E13-$E$6)*(E13-$E$6))
*2=SQRT((D13-$D$7)*(D13-$D$7)+(E13-$E$7)*(E13-$E$7))
このような式を書いていけばいいのですが、通常
よく列などを増やすときはコピーでいいのですが行列７＊７を８＊８
などに増やしたときの増やしたものへの式を拡張する簡易な方法はないものでしょうか。

回答No.1

noname#52504 · ベストアンサー

「列方向にフィルしたとき、参照するセルを行方向に変化させる」
というのは無理なので、数式の側で工夫する必要があります。

セル位置を具体的に示していただかないと説明しにくいんですが、
元々の座標データはＤ,Ｅ列の６行目以降に順に入力されている、
ということでよろしいですね。

仮に、(1)の数式が入るセルの位置がH3であるとします。

Ａ案：地味に…

　H3：=SQRT((OFFSET($D$6,ROW()-ROW($H$3),0)-OFFSET($D$6,COLUMN()-COLUMN($H$3),0))^2+(OFFSET($E$6,ROW()-ROW($H$3),0)-OFFSET($E$6,COLUMN()-COLUMN($H$3),0))^2)
　として右方,下方にフィル。

Ｂ案：X,Y座標を配列としてまとめて処理

　H3：=SQRT(SUMPRODUCT((INDEX($D$6:$E$20,ROW()-ROW($H$3)+1,)-INDEX($D$6:$E$20,COLUMN()-COLUMN($H$3)+1,))^2))
　として右方,下方にフィル。

Ｃ案：全体を行列として一括して処理(15*15の場合)

　H3:V17を選択した状態で、
　=SQRT((D6:D20-TRANSPOSE(D6:D20))^2+(E6:E20-TRANSPOSE(E6:E20))^2)
　としてCtrl+Shift+Enterで配列数式として確定

お礼

期待する回答を得ることができどうもありがとうございました。