coretan tentang autocad dan excel

May 20, 2012

[XLS-SVY-14]: Menghitung Poligon dari Data Total Station dengan Excel

Filed under: Excel, Tukang Ukur — Tags: , , , , — cadex @ 20:04
Referensi : — Catatan Rumus Excel untuk Juru Ukur (Bagian #2)
— Perhitungan rerata dan standard deviasi hasil pengukuran sudut dan jarak dari total station (theodolit)
Platform : Excel 2007
Lokasi File :

Salah satu pengunjung di posting tentang perhitungan rerata dan standar deviasi hasil pengukuran sudut dan jarak dari total station memberikan komentar sebagai berikut:

saya mendapat file traverse untuk dihitung hasil koordinatnya tapi setelah saya gunakan excel tidak masuk…apa saya salah baca datanya ya pak…tolong bantuanya pak….ini saya kirimkan datanya pak Level Tertiary

OccupiedStn BM01 InstHt 1.513 Desc 002
RefStn BM03 HA 134°24’39″ VA 90°02’31″ SD 81.514 TargetHt 1.404 Desc 002
RefStn M102 HA 263°38’56″ VA 89°50’53″ SD 81.631 TargetHt 1.473 Desc 002
RefStn M102 HA 83°38’52″ VA 270°09’26″ SD 81.631 TargetHt 1.473 Desc 002
RefStn BM03 HA 314°24’26″ VA 269°57’56″ SD 81.514 TargetHt 1.404 Desc 002
OccupiedStn M102 InstHt 1.473 Desc 002
RefStn BM01 HA 83°38’56″ VA 90°09’36″ SD 81.631 TargetHt 1.513 Desc 002
RefStn M103 HA 201°21’59″ VA 89°56’50″ SD 140.450 TargetHt 1.473 Desc 002
RefStn M103 HA 21°21’43″ VA 270°03’26″ SD 140.450 TargetHt 1.473 Desc 002
RefStn BM01 HA 263°38’21″ VA 269°50’47″ SD 81.631 TargetHt 1.513 Desc 002
Shot S100 HA 160°19’27″ VA 89°50’55″ SD 49.446 TargetHt 0.000 Desc 205
Shot S101 HA 161°36’48″ VA 89°35’30″ SD 49.060 Desc 205
Shot S102 HA 165°33’47″ VA 88°33’51″ SD 66.895 Desc 205
Shot S103 HA 166°32’03″ VA 88°49’00″ SD 66.597 Desc 205
OccupiedStn M103 InstHt 1.473 Desc 002
RefStn M102 HA 21°21’59″ VA 90°03’31″ SD 140.450 TargetHt 1.473 Desc 002
RefStn M104 HA 180°39’20″ VA 89°59’06″ SD 274.803 TargetHt 1.173 Desc 002
RefStn M104 HA 00°39’16″ VA 270°01’16″ SD 274.803 TargetHt 1.173 Desc 002
RefStn M102 HA 201°21’42″ VA 269°56’52″ SD 140.450 TargetHt 1.473 Desc 002
OccupiedStn M104 InstHt 1.176 Desc 002
RefStn M103 HA 00°39’20″ VA 90°01’30″ SD 274.801 TargetHt 1.473 Desc 002
RefStn M105 HA 91°07’29″ VA 89°52’19″ SD 130.210 TargetHt 1.526 Desc 002
RefStn M105 HA 271°07’37″ VA 270°08’13″ SD 130.211 TargetHt 1.526 Desc 002
RefStn M103 HA 180°39’29″ VA 269°58’53″ SD 274.802 TargetHt 1.473 Desc 002
OccupiedStn M105 InstHt 1.527 Desc 002
RefStn M104 HA 271°07’29″ VA 90°08’13″ SD 130.211 TargetHt 1.176 Desc 002
RefStn M106 HA 05°36’00″ VA 90°00’23″ SD 182.781 TargetHt 1.543 Desc 002
RefStn M106 HA 185°35’55″ VA 270°00’00″ SD 182.781 TargetHt 1.543 Desc 002
RefStn M104 HA 91°07’19″ VA 269°52’09″ SD 130.211 TargetHt 1.176 Desc 002
OccupiedStn M106 InstHt 1.542 Desc 002
RefStn M105 HA 185°36’07″ VA 90°00’13″ SD 182.781 TargetHt 1.527 Desc 002
RefStn S002 HA 336°57’54″ VA 89°57’20″ SD 109.348 TargetHt 1.593 Desc 004
RefStn S002 HA 156°57’44″ VA 270°03’09″ SD 109.348 TargetHt 1.593 Desc 004
RefStn M105 HA 05°35’50″ VA 270°00’11″ SD 182.782 TargetHt 1.527 Desc 002
OccupiedStn S002 InstHt 1.593 Desc 004
RefStn M106 HA 156°57’02″ VA 90°03’12″ SD 109.347 TargetHt 1.542 Desc 002
RefStn M110 HA 81°24’02″ VA 89°59’38″ SD 52.851 TargetHt 1.557 Desc 002
RefStn M110 HA 261°24’03″ VA 270°00’47″ SD 52.851 TargetHt 1.557 Desc 002
RefStn M106 HA 336°56’59″ VA 269°57’14″ SD 109.348 TargetHt 1.542 Desc 002

 

note:BM01 Easting 5000.000 Northing 10000.000 Elev 20.000
BM03 Easting 5058.235 Northing 9942.949 Elev 20.059
S002 Easting 4969.762 Northing 9865.355 Elev 20.763

Karena saya tidak mendapat informasi dari jenis total station (TS) data tersebut di download, maka saya coba interpretasi data tersebut sebagai berikut:

* Data tersebut adalah data pengukuran poligon dengan station pertama di BM01 sampai S002. Backsight pertama ke BM03, sedangkan foresight terakhir ke M110. Ada pengamatan detail (sideshot) saat alat berdiri di M102 yaitu ke detil S100, S101, S102 dan S103. Pengamatan detail ini nantinya tidak diikutkan dalam perhitungan poligon.

* Dari tambahan informasi (Note) Koordinat Fix hanya di BM01, BM03 dan S002. Sebetulnya kalo ingin terikat sempurna, perlu informasi koordinat di M110.

* Tiap station, pengukuran sudut dilakukan dengan posisi B-B-LB-LB sehingga tiap staion didapat 2 (dua) sudut horisontal. Untuk ukuran jarak akan didapat 4 (empat) buah data untuk tiap sisinya kecuali untuk backsight pertama ke BM03.

Langkah-langkah yang dijelaskan selanjutnya akan mengacu ke format total station seperti di atas. Jika pembaca mempunyai format yang lain, silahkan disesuaikan atau silahkan kirim contoh format total station nya nanti saya coba bantu proses hitungannya.

Berikut tahapan untuk mengolah data tesebut dengan menggunakan microsoft excel:

1. Extract atau pisahkan data menjadi beberapa kolom  yaitu OccupiedStn,InstHt,RefStn,HA,VA,

SD dan TargetHt

2. Hitung sudut horisontal, jarak datar, jarak vertikal untuk masing-masing ukuran.

3. Hitung rerata dan standard deviasi untuk hasil pengukuran sudut dan jarak.

4. Hitung poligon hasil dari data rerata sudut dan jarak.

Selanjutnya adalah detail langkah-langkah untuk masing tahapan di atas:

1. Extract data menjadi kolom.
  • Buka Excel, kemudian “copy paste” data hasil download TS di atas dalam satu kolom.
  • Hapus baris yang diawali kata ‘Shot’. Baris yang dihapus ini adalah baris pengamatan detil yang tidak akan diikutkan dalam perhitungan poligon
  • Hapus juga baris mulai note sampai ke bawah. Hasil copy paste di excel:

image

  • Buat header di kolom B1 sampai H1. Penulisan nama header OccupiedStn, InstHt, RefStn, HA, VA, SD dan TargetHt harus sama persis yang tertulis di data mentah dari TS.

image

  • Masukkan formula atau rumus excel:
Cell Rumus/Formula
[B3] =SUBSTITUTE(MID(SUBSTITUTE($A3, ” “,REPT(“^”,99)),FIND(B$1,SUBSTITUTE($A3, ” “,REPT(“^”,99)))+LEN(B$1),2*99),”^”,””)
[C3] =–SUBSTITUTE(MID(SUBSTITUTE($A3, ” “,REPT(“^”,99)),FIND(C$1,SUBSTITUTE($A3, ” “,REPT(“^”,99)))+LEN(C$1),2*99),”^”,””)
[D4] =SUBSTITUTE(MID(SUBSTITUTE($A4, ” “,REPT(“^”,99)),FIND(D$1,SUBSTITUTE($A4, ” “,REPT(“^”,99)))+LEN(D$1),2*99),”^”,””)
[E4:F4] copy rumus dari [D4]
[G4] =–SUBSTITUTE(MID(SUBSTITUTE($A4, ” “,REPT(“^”,99)),FIND(G$1,SUBSTITUTE($A4, ” “,REPT(“^”,99)))+LEN(G$1),2*99),”^”,””)
[H4] copy rumus dari [G4]
  • copy rumus di [B3:C3] ke bawah jika di kolom A ada tulisan OccupiedStn
  • copy rumus di [D4:H4] ke bawah jika di kolom A ada tulisan RefStn

Hasil extraction di excel menjadi:

image

2. Menghitung sudut horisontal, jarak datar dan jarak vertikal
  • Buat header baru di kolom [J1:O1]

image

B_LB: posisi bidikan ke target sesuai dengan urutan B-B-LB-LB

HA: sudut horisontal, yaitu sudut hasil pengurangan foresight dengan backsight

HD: Horizontal Distance atau arak datar

VD: Vertikal Distance atau jarak miring

dH: difference of height atau beda tinggi

  • Masukkan rumus untuk menghitung HA, HD, VD dan dH sebagai berikut:
[K4] =–SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E4,”°”,”:”),”’”,”:”),”″”,””)
[L4] copy rumus dari K4
[M4] =ABS(G4*SIN(RADIANS(L4*24)))
[N4] =G4*COS(RADIANS(L4*24))
[O4] =N4+C3-H4
  • Copy rumus [K4:O4] ke bawah dan copy juga urutan baris B-B-LB-LB ke bawah sehingga Tampilan tabel seharusnya seperti di atas.

Catatan:

** Rumus di [K4] dan [L4] adalah merubah format sudut ke format jam di excel. Agar format tanggal ditampilkan sebagai format sudut, maka cell di kolom K dan L harus dirubah format numbernya menjadi :

image

** Silahkan lihat posting Catatan Rumus Excel untuk Juru Ukur (Bagian #2) tentang cara membuat custom format number ini.

** perkalian dengan angka 24 adalah untuk mengkoversi format sudut dari jam ke desimal. Jika data anda berupa dseimal, maka tidak perlu dikalikan angka 24.

3. Hitung Rerata dan Standard Deviasi
  • Buat header baru di sebelah kolom perhitungan beda tinggi.

image

  • Atur atau set format number di kolom P, Q dan R menjadi format jam
  • Buat tabel id_jarak seperti di kolom X, Y dan Z
  • Rumus untuk tabel di atas adalah;
[P5] =IF(J5=”B”,(K5-K4)*24+IF(K4>K5,360),(K4-K5)*24+IF(K5>K4,360))/24
copy dari rumus [P5] ke [P7], [P10],[P12],…..,[P35], [P37]
[Q3] =AVERAGE(P4:P7)
[R3] =STDEV(P4:P7)
copy rumus [Q3:R3] ke [Q8:R8],…..,[Q33:R33]
[T4], [U4] Informasi tempat alat dan target
[V4] =SUM(((T4=$Y$3:$Y$10)*(U4=$Z$3:$Z$10)+(U4=$Y$3:$Y$10)*(T4=$Z$3:$Z$10))*$X$3:$X$10)
Rumus di atas menggunakan formula array. Setelah menulis atau mengcopy rumus diatas.
tekan F2, kemudian tekan CTRL+SHIFT+ENTER atau formula CSE
Copy rumus [V4] ke semua data ukuran atau sampai ke [V37]
[AA3] =AVERAGE(IF($V$3:$V$38=$X3,$M$3:$M$38))
rumus ini juga formula CSE
[AB3] =STDEV(IF($V$3:$V$38=$X3,$M$3:$M$38))
Rumus ini juga formual CSE
Copy rumus [AA3:AB3] ke bawah.

Kesimpulan hasil hitungan rerata dan standard deviasi:

  • standard deviasi untuk pengukuran sudut terbesar ada di station M102 yaitu 13.44”, perlu dilakukan pengecekan ulang di stasion ini terutama untuk ukuran sudutnya apalagi jika diketahui ketelitian alat adalah lebih kecil 10”.
  • standard devisasi untuk pengukuran jarak masih di bawah 1 milimiter.
4. Menghitung Poligon.

Karena informasi koordinat fix untuk Foresight akhir M110 tidak ada, maka jenis poligon menjadi poligon terbuka terikat sempurna di awal tetapi terikat sebagian di akhir. Terikat sempurna di awal yaitu di BM03 dan BM01 dan terikat sebagian karena hanya terikat di S002.

    • Ketelitan sudut dicek dengan cara Azimut hasil hitungan koordinat fix dibandingkan dengan Azimut hasil hitungan dari poligon sebelum dikoreksi.
    • Ketelitian jarak dicek dengan cara beda absis dan ordinat (beda X,Y) dari koordinat fix dibandinkang dengan beda X,Y hasil hitungan dari poligon sebelum dikoreksi
    • Sudut dan jarak di station S002 tidak dimasukkan dalam proses perhitungan. Sudut ini bisa dimasukkan dalam perhitungan jika koordinat fox titik M110 diketahui.

Berikut tahapan perhitungan poligonnya:

  • Buat form atau spreadsheet hitungan poligon :

image

Note: beberapa kolom sengaja diperkecil agar bisa tampil fix on screen.

  • Kolom C diisi besar sudut horisontal rata-rata dalam format jam dan kolom D diisi jarak rata.
  • Jarak di [G4] adalah jarak dari BM01 ke M102, [G5] jarak dari M102 ke M103 begitu seterusnya ke bawah
  • Azimuth di [F3] adalah azimuth dari BM03 ke BM01, [F4] azimuth dari BM01 ke M102 begitu seterusnya ke bawah.
  • Detail rumus yang ada untuk form hitungan poligon:
[Q3] =DEGREES(ATAN2((O4-O3),(N4-N3)))+IF(ATAN2((N4-O3),(N4-N3))<0,360)
[F3] =Q3
[D4] =C4*24
[E4] =$E$16/$C$16
[F4] =F3+(E4/3600)+D4-180+IF(F3+(E4/3600)+D4-180<0,360,IF(F3+(E4/3600)+D4-180>360,-360))
copy rumus [D4:F4] ke bawah sampai [D9:F9]
[G14] =SUM(G3:G13)
[H4] =$G4*SIN(RADIANS($F4))
[I4] =I$16/$G$14*$G4
[J4] =$G4*COS(RADIANS($F4))
[K4] =K$16/$G$14*$G4
copy rumus [H4:K4] ke bawah sampai [H9:K9]
[L3] =N3
[M3] =O3
[L4] =N4
[M4] =O4
[L5] =L4+H4+I4
[M5] =M4+J4+K4
copy rumus [L5:M5] ke bawah sampai [L10:M10]
[L10:M10] koordinat hasil hitungan poligon di S002 yang akan dibandingkan dengan koordinat fix
[Q10] =DEGREES(ATAN2((O10-$O$4),(N10-$N$4)))+IF(ATAN2((O10-$O$4),(N10-$N$4))<0,360)
Azimut fix dari BM01 ke S002
[Q16] =DEGREES(ATAN2((M10-$M$4),(L10-$L$4)))+IF(ATAN2((M10-$M$4),(L10-$L$4))<0,360)
Azimut hasil hitungan poligon dari BM01 ke S002
[Q17] =(Q10-Q16)*3600
Ketelitian sudut dalam detik hasil perbandingan azimuth
[R4] =N10-N4
[S4] =O10-O4
[R4:S4] beda jarak absis, ordinat (beda X, Y) dari koordinat fix BM01 dengan S002
[R16] =L10-L4
[S16] =M10-M4
[R16:S16] beda jarak absis, ordinat (beda X, Y) dari hasil hitungan poligon
[Q18] =G14/SQRT(R18^2+S18^2)
Ketelitian Linear

Kesimpulan sementara hasil hitungan poligon:

  • ketelitian sudut 24.46” jika mengacu ke spesifikasi 10”√n, dimana n adalah banyaknya sudut horisontal, maka  10”√6=24.49”
  • masih masuk toleransi untuk spesifiasi di atas. ketelitian sudut ini bisa ditingkatkan lagi seandainya telah dilakukan pengecekan sudut di M102 atau dengan memasukkan nilai sudut di M102 bukan dari nilai rata2 yaitu 117°43’22″
  • Ketelitian linear 1:41147 masih masuk toleransi.

Untuk selanjutnya adala perataan hitungan poligon. Perataan metode yang dipakai adalah metode Bowditch atau Compass

  • copy >> paste special >> value dari [Q17] ke [E16]
  • copy >> paste special >> value dari [R18] ke [I16]
  • copy >> paste special >> value dari [S18] ke [J16]

Tips:

untuk melakukan copy >> paste special >> value bisa menggunakan shortcut dengan urutan sebagai berikut: (misal akan melakukan copy dari Q17 ke E16):

  • pilih cell tujuan (misal E16), kemudain tekan F2
  • tekan tanda samadengan (=), kemudian pilih Q17
  • tekan F9, kemudian ENTER

Hasil hitungan poligon setelah dilakukan perataan

image

Cek hasil hitungan, koordinat S002 hasil hitungan harus sama dengan koordinat fix nya.

download

Posting berikutnya akan dicoba cara perhitungan data hasil total station di Autodesk Civil 3D

==selamat mencoba==

About these ads

8 Comments »

  1. Met Siang Mas…
    Bisa gak kalau ngitung ( Delta,T,R,L,Lc ) di excel,

    contohnya Seperti ini :

    NO PI = 01
    STA = 01 + 100
    BS = 130° – 52′ – 00″
    FS = 319° – 08′ – 00″

    d =
    ∆ =
    R =
    T =
    E = 3,40
    L =
    Lc =
    L / R = R

    TC =
    CC =
    CT =
    X =
    Y =

    Data dari pengukuran lapangan dengan alat theodoit cuma dapet STA/BS/FS/E dan Arah tikungan L/R.
    Jadi biasanya ngitung pake kalkulator begini:

    FS – BS – 180 = ∆
    ∆ : 4 = tan
    ∆ : 2 = tan
    E : ( ∆ : 4 = tan ) = T
    T : ( ∆ : 2 = tan ) = R

    3,14 : 180 x ∆ x R = Lc
    Lc : 2 = L

    STA – T = TC
    TC + L = CC
    CC + L = CT

    Hasil hitungan tsb lah yang dilampirkan untuk laporan. mohon bantuan nya kalau bisa ngitung dari Excel Mas.

    mohon maaf kalau commet nya tidak pada tempat nya Mas, soal nya saya lihat berhubungan dengan hitungan D,M,S.

    Terima kasih sebelumnya Mas.

    Comment by ant0nc0mmunity — February 19, 2013 @ 13:23

    • ada contoh hitungan dan hasil hitungannya.

      Comment by cadex — February 23, 2013 @ 12:24

      • Maf Mas Pake contoh yang ini aja, yang (Hitungan Manual Saya ambil PI 01 aja, sept ini :) salah FS nya.

        NO PI = 01
        STA = 0 + 075
        BS = 228° – 20′ – 00″
        FS = 10° – 00′ – 20″

        d = 75
        ∆ = 38° – 19′ – 40″
        R = 63,067
        T = 21,918
        E = 3,70
        L = 21,094
        Lc = 42,188
        L / R = L

        TC = 0 + 053.082
        CC = 0 + 074.176
        CT = 0 + 095.270
        X =
        Y =

        [ BS - FS - 180 = ∆ ]
        BS = 228° – 20′ – 00″
        FS = 10° – 00′ – 20″
        _ 228.3333333 (228° 20′ 00″ ) – 10.00555556 (10° 00′ 20″ ) = 218.3277778 (218° 19′ 40″ )
        _ 218.3277778 (218° 19′ 40″ ) – 180 = 38.32777777 (38° 19′ 40″ )
        ∆ = 38° – 19′ – 40″

        [ ∆ : 4 = tan ]
        _ 38.32777777 : 4 = 9.581944444
        _ 9.581944444 tan = 0.168813213

        [ ∆ : 2 = tan ]
        _ 38.32777777 : 2 = 19.16388889
        _ 19.16388889 tan = 0.34753031

        [ E : (∆ : 4 = tan) = T ]
        _ 3.70 : 0.168813213 = 21.91771564
        T = 21,918

        [ T : ( ∆ : 2 = tan) = R ]
        _ 21.91771564 : 0.34753031 = 63.06706199
        R = 63,067

        [ ᴫ : 180 x ∆ x R = Lc ]
        _ 3.14 : 180 = 0.017453292. 0.017453292 x 38.32777777 = 0.668945917. 0.668945917 x 63.06706199 = 42.18845363
        Lc = 42,188

        [ Lc : 2 = L ]
        _ 42.18845363 : 2 = 21.09422681
        L = 21,094

        [ Sta - T = TC ]
        _ 075 – 21.918 = 53.082
        TC = 0 + 053.882
        [ T + L = CC ]
        _ 53.082 + 21.094 = 74.176
        CC = 0 + 074.176
        [ CC + L = CT ]
        _ 74.176 – 21.094 = 95.27
        CT = 0 + 095.270

        Terimakasih Mas,

        Comment by ant0nc0mmunity — February 26, 2013 @ 14:58

      • coba cek di http://cadex.wordpress.com/2010/02/27/xls-svy-02-catatan-rumus-excel-untuk-juru-ukur-bagian-1/
        ada cara perhitungan sudutnya

        Comment by cadex — February 26, 2013 @ 15:42

  2. Mas, Izin Download ya…. Thank’s…

    Comment by Donald Fujico — April 18, 2014 @ 22:21

    • silahkan

      Comment by cadex — April 21, 2014 @ 08:45

  3. Assalamu a’laikum..minta izin Copy..

    Comment by Ahmad nur halim — July 10, 2014 @ 14:03

    • silakan

      Comment by cadex — July 16, 2014 @ 19:40


RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Shocking Blue Green Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 786 other followers

%d bloggers like this: