2021年5月12日水曜日

Excel×地積測定 工程管理の記録表

 今日は地籍調査G工程の工程管理の記録表のExcelで
計算が必要な個所に関数を入れてみようと思います。


これはヘロンの公式を使って面積を出し、
公差の範囲内であるか点検する帳票です。

精度区分をドロップダウンリストで入力し
辺a,b,cの数値、地積を入力すると、点検地積、公差、
地積と点検地積の差が自動で出るようにします。

この帳票を使わないという方も、
関数が使えそうであれば参考にご覧ください。

ここでは、弊社で実際に使っているExcelのセル番地で説明しますので、
セルの結合が多く、セル番地が飛ぶので
少々わかりづらいかもしれません(;^_^A

さてでは、ヘロンの公式からいってみますよ~!

ヘロンの公式↓
面積F=√S(S-a)(S-b)(S-c)
S=(a+b+c)/2

まずはSを出す関数を入れます。
=ROUND(SUM((C8+G8+K8)/2),3)
赤字部分がSを出す式ですね。
青字部分は、Excelと電卓で端数処理を合わせるために入れます。
ここでは、小数第4位を四捨五入し、第3位まで表示という指示にしています。

そこから、S-a,S-b,S-cのセルに引き算を入れていきます。
=O8-C8,=O8-G8,=O8-K8というように、
Sのセル番地-それぞれの辺のセル番地です。

そしてFを出す関数を入れます。
=IFERROR(ROUND(SQRT(O8*U8*AA8*AG8),3),"")
赤字部分がFを出す式、√S(S-a)(S-b)(S-c)です。
ここでも端数処理のROUND関数(青字部分)を加えます。

これだけでFは出るのですが、
ここでは、辺の数値の入力が無いセルがエラー表示されてしまうのを
回避するために、エラーの場合は空白を表示する指示にします。
緑部分がそれですね。
上の表で、No.4とNo.5には入力が無いので、
この行にも関数を入れていると、Fのセルにエラーが出ます。
単純にそれをDeleteすればIFERROR関数は必要ないです。

No.4とNo.5の行のSのセルには、通常「0」が表示されるのですが、
ファイル→オプション→詳細設定→
次のシートで作業するときの表示設定で、
「ゼロ値のセルにゼロを表示する」のチェックを外すと
「0」は表示されず、空白になります。
ここも入力の無い0値はDeleteでもいいですね。

そして、各F値が出たら、点検地積のセルに合計を出します。
=SUM(AM8:AR12)
これで点検地積が出ました。

ここまではそう複雑ではないですが、
めんどくさいのが公差ですよねヽ(`Д´)ノ
精度区分によって違うじゃないですか。

現場が一か所だけで、乙1しかないとかいう場合は、
公差のセルに直接その公式を関数で入れれば良いですが、
ウチみたいな地籍マニアの会社は、
現場がいくつもあるので、それぞれ精度区分もバラバラです。

というワケで、精度区分を入力したら
それに対応した「地積測定の公差」が出るようにしていきます!

まずは、各精度区分の公差の公式をチェック!
地籍調査必携2018(赤本)の35ページを開きましょう!


これを見ながら、先ほどのExcelの欄外の適当な場所に、
甲1から乙3までの公差の計算の欄を作ります。


ここでは精度区分乙1を例に入力します。
乙1の下、1.42と表示されているセルの関数です↓
=ROUNDDOWN((0.1+0.04*AM13^(1/4))*SQRT(AM13),2)
赤字部分が乙1公差の式、(0.10+0.044√F)√F㎡ になります。

4乗根はExcel関数で^(1/4)と入力します。
なので、点検地積のセルAM13の横に^(1/4)を付けます。
ちなみに5乗根は^(1/5)、6乗根は^(1/6)
7乗根は^(1/7)になるだけなので、
電卓で計算するより単純な指示だなと思います。
さすがExcel!

ここは小数第3位を切り捨て、第2位まで表示するので、
青字部分のROUNDDOWN関数を付け加えます。

(現場の精度区分が1種類しか無い場合は、
公差のセルにこの関数を直接入力すればOKです)

これをそれぞれの精度区分の式に当てはめ、
各セルに入力すると、上の写真のように各精度の公差が表示されます。



次に上の図のように表内の精度区分のセルに、
ドロップダウンリストを設定します。
データダブのデータの入力規則→データの入力規則で設定。


上記のように、「入力値の種類」をリストとし、
「元の値」にカーソルを合わせたら、先ほど欄外に作成した
甲1から乙3を入力したセル範囲をドラッグして選択します。
これでOKをクリックすれば、ドロップダウンリストになります。




そして表内の公差のセルに上の図のように関数を入力します。
=CHOOSE(MATCH(W5,BK6:BP6,0),BK7,BL7,BM7,BN7,BO7,BP7)

ここでまず使いたいのはCHOOSE関数です。
インデックスを使って値の引数リストから値を返す関数ですね。
ここでいうインデックスが甲1から乙3の精度になり、
返す値がそれぞれの公差になります。
このインデックスは数値でないといけない関数なので、
このままでは使えません。

そこで赤字部分のMATCH関数を組み合わせます。
欄外に作成した精度区分の入力した範囲から
該当する精度区分を検索し、位置を数値で返してくれます。
これ単体で使う事はほぼ無い関数だと思いますが、ここでは
CHOOSE関数のインデックスは関数でもOKという性質を利用して
MATCH関数を組み合わせます。

MATCH(W5,BK6:BP6,0)
W5は精度区分のドロップダウンリストを入れたセルです。
BK6:BP6がそれを検索する範囲、欄外に作った精度区分の行ですね。
最後の0は、照合の型で、ここでは完全一致の0にします。

,BK7,BL7,BM7,BN7,BO7,BP7)
の部分が、欄外の公差を計算したそれぞれのセルです。

CHOOSE関数は汎用性が高いので、
そういえばそういう関数あったな、、、
程度に記憶にとどめておくと、
何かと使える事があるかもしれません。


最後に、地積から点検地積を引く②-①のセルに、
引き算入れておしまいです。


合否の判定も関数で出せるなと思い、
ちょうどそこにいた測量部Tくんに、
ここの判定はこちらで入れるものなのか聞いてみたら、、、

「あ~、それ使わんのですよね~
2種類あって、もう一つの座標のヤツ使うんで(´-ω-)」

ェエェエェエェエ(゚Д゚ノ)ノエェエェエェエェ!
使わんのかーーーーい!!(`Д´) !!


はい、今日はこれでおしまい( ̄∀ ̄)