（旧バージョンの時に苦労した、エクセル関数の覚え書きです。図中のデータシートフォーマットも旧バージョンです。）

展開図を作ってみる

ｴｸｾﾙｼｰﾄに測点を入力して、防護柵の展開図を作ってみました。
高さを持たない展開図ならこんな作り方も有ります。

○ﾃﾞｰﾀｼｰﾄに測点を記入します。（aの部分）

ﾃﾞｰﾀｼｰﾄの項目”P”まではガブガブで使用していますので、”P”以降にちょっとした工夫をします。

○入力された測点から距離を計算する関数を書き込みます。（bの部分）

通常測点は２０ｍ間隔なので、例えば５行目の関数は”=H5*20+J5”になります。

○A列に測点を作る関数を書き込みます。（cの部分）

A列に表示されるﾃﾞｰﾀがCAD図面に出力されます。
G,H,I,J列のﾃﾞｰﾀを結合してA列のﾃﾞｰﾀにします。
５行目だとすると関数は”=G5&H5&I5&J5”になります。 　　

　　

○CAD図面に出力される位置（座標値）を設定します。（dの部分）

　　

数学座標では、”ｘ”座標がCAD図面の横方向、測量座標では”ｙ”座標がCAD図面の横方向になります。
（例の場合、ガブガブは測量座標系に設定されています。）
横方向だけの展開図で良いので、”ｘ”座標にはﾀﾞﾐｰで適当な同じ数値を入れておきます。
（CADに出力後、配置は修正すれば良いので。）
”ｙ”座標（C列）に関数を書き込みます。
距離が入れば良いだけなので、９行目だとすると関数は”=K9”になります。
　　

作成したﾃﾞｰﾀｼｰﾄでCADに作図したものが下図の赤になります。

　　

青は寸法線を使って作図しました。
寸法線の設定で、矢印が出ないものを登録しておき使っています。
　　

　　
　　

さて、あまり無い例ですが、測点間の距離が違う箇所がある場合について考えてみます。

　　

測点間の距離が違うので、単純に２０ｍを掛けるわけにはいきません。
下図のような測点間距離管理表を作ってみました。
　　

　　

○測点13,14間で測点間距離が違っています。（eの部分）

測点間距離が違うものでも下図fのように正しい出力をさせます。

簡単に考えると、例えば測点14なら測点管理表の測点0～測点14までの距離を足して、それに”+4”すれば良いわけです。
まず関数”SUM”が使えることに気付きます。
例えばfの場合、測点の付加記号が”N.”なので関数は”=SUM(測点管理表!$F$4: ??? )+J77”
になりそうです。この???のところが少し複雑になります。

関数”INDIRECT”と”ADDRESS”を使います。
　”INDIRECT”は文字列として入力したｾﾙの番地や範囲名を、計算式で参照できる形に変換します。
　”ADDRESS”は行番号と列番号からｾﾙのｱﾄﾞﾚｽ（文字列）を作成します。

”=SUM(測点管理表!$F$4: ??? )”を考えてみます。
これは測点0から求めたい測点までの測点間距離を加算したものです。
SUMの開始位置は４行目（測点0固定）で問題ありませんが、終了位置の行数は”測点番号+4”になります。
これをどう作るかです。
測点番号の入っているｾﾙは７７行目を例にすると”H77”になります。これに”4”を足すので”H77+4”になり、”H77”の値が”14”なので数値は”18”になります。
これで行数が取得できます。
そして、加算したい測点管理表の列は、F行なので”6”になります。
ここで”ADDRESS(H77+4,6)”を試してみると、出力は”$F$18”（文字列）になります。
関数を書き込んでいるｼｰﾄと加算したいｼｰﾄが同一なら”INDIRECT(ADDRESS(H77+4,6))”でSUMの終了位置になるのですが、加算したいｼｰﾄが別（測点管理表）ですので、文字列を連結して”INDIRECT("測点管理表!" & ADDRESS(H77+4,6)”になります。
（”INDIRECT”の中は”測点管理表!$F$18”になっています。）
ここまでの結果からSUM関数を作ってみると、”SUM(測点管理表!$F$4:INDIRECT("測点管理表!" & ADDRESS(H77+4,6)))”になります。
これに”J77”の値を足せば、求めたい測点距離になります。
関数を書き込むｼｰﾄと別ｼｰﾄから値を求めていますので、関数のどの部分がどのｼｰﾄのどの値を参照しているのかを正しく把握するのがとても重要です。

以上の結果と工区ごとに付けた付加記号を考慮した関数は以下になります。

=IF(G77="B.",SUM(測点管理表!$B$4:INDIRECT("測点管理表!" & ADDRESS(H77+4,2)))+J77,
　IF(G77="D.",SUM(測点管理表!$D$4:INDIRECT("測点管理表!" & ADDRESS(H77+4,4)))+J77,
　IF(G77="N.",SUM(測点管理表!$F$4:INDIRECT("測点管理表!" & ADDRESS(H77+4,6)))+J77,
　IF(G77="I.",SUM(測点管理表!$H$4:INDIRECT("測点管理表!" & ADDRESS(H77+4,8)))+J77,))))

※INDIERECT関数のｶｯｺ内は別解もあると思います。
　お分かりになられた方はお教えください。