coretan tentang autocad dan excel

August 14, 2012

[XLS-SVY-17]: Solver untuk Aplikasi Hitung Kuadrat Terkecil Metode Kondisi

Filed under: Excel, Tukang Ukur — Tags: , , — cadex @ 12:15
Referensi : Mikhail, Edward M Ph.D and Gordon Grace, Ph.D. “Analysis and Adjustment of Survey Measurement”. Van Nostrand Reinhold Ltd, 1981
Platform : Excel 2007
Lokasi File :

Metode perataan hitung kuadrat terkecil di bidang survey measurement yang saya ketahui sampai saat ini adalah

  • Metode Paramater (least square adjustment of indirect observations)
  • Metode Kondisi (least square adjustment of observation only)
  • Metode kombinasi (general least square adjustment)

Contoh aplikasi hitung kuadrat terkecil dengan metode paramater ada di post  [XLS-SVY-12]: Add-Ins Excel untuk Hitung Kuadrat Terkecil (HKT) dan [XLS-SVY-01]: Prediksi Pasut dengan VBA Excel. Pada kedua posting tersebut penyelesain perataan atau adjusment menggunakan bantuan bahasa pemograman visual basic application for excel (vba excel)

Kali ini akan diuraikan cara menggunakan solver untuk penyelesaian perataan metode kombinasi tanpa menggunakan vba excel.

A. Dasar Teori

Semua metode perhitungan kwadrat terkecil atau least square adjustment menggunakan dasar “kwadrat dari koreksi pengukuran harus minimum”. Misalnya kita melakukan pengukuran jarak antar dua titik sebanyak 3 (tiga) kali l1, l2 dan l3 sedangkan jarak sebenarnya kedua titik tersebut adalah la. Karena pengukuran l1, l2 dan l3 mengandung kesalahan maka ada koreksi untuk masing-masing pengukuran misal v1, v2 dan v3. Persamaan antara 3 (tiga) pengukuran tersebut dengan koreksinya terhadap la adalah:

  • (l1+v1)=la
  • (l2+v2)=la
  • (l3+v3)=la

koreksi v1, v2 dan v3 bisa bernilai positif atau negatif.

Pengukuran yang dilaksanakan dengan benar akan menghasilkan nilai koreksi yang kecil  untuk v1, v2 dan v3. Karena nilai koreksi ada yang positif dan negatif, untuk pengukuran yang benar, nilai v12 + v22 + v32 akan minimum. Atau dalam bentuk umum

image

jika ada bobot ukuran (w) persamaan di atas menjadi:

image

(Ref: Mikhail, Edward M Ph.D and Gordon Grace, Ph.D. “Analysis and Adjustment of Survey Measurement”. Van Nostrand Reinhold Ltd, 1981)

B. Aplikasi di jaring pengukuran beda tinggi.

B.1. Skets Pengukuran dan data pengukuran

image

B.2. Menentukan nilai redundancy (pengukuran lebih)

Banyaknya pengukuran (n)= 6
Pengukuran minimum (u) = 3
* hanya dibutuhkan minimum 3 pengukuran untuk menentukan titik B, C dan D (lihat lingkaran merah)
Redundancy (r=n-u)=3
Jumlan persamaan kondisi sesuai dengan nilai r atau 3 persamaan.

B.3. Memodelkan solver dan membuat persamaan kondisi.

image

[D37] =1/E21 Copy [D37] sampai ke [D42]
[G37] =F37^2*D37 Copy [G37] sampai ke [G42]
[I37] =C37+F37 Copy [I37] sampai ke [I42]
[G44] =SUM(G37:G42)
[F48] =obs_1+v_1-obs_3-v_3+obs_2+v_2
[F49] =obs_2+v_2+obs_4+v_4-obs_5-v_5
[F50] =obs_3+v_3+obs_6+v_6-obs_5-v_5

obs_1, obs_2,….,obs_6 dan v_1, v_2,…..,v_6 adalah ‘defined name’. Cara membuat define name untuk obs_1, obs_2,….,obs_6 adalah :

  • Pilih range [B37:C42]
  • Pada Menu [Formula] pada group [Define Names], pilih [Create from Selection]
  • image
  • Pilih atau check [Create names from values in the : Left column]
  • image

Lakukan langkah yang sama untuk v_1, v_2,…..,v_6

B.4. Setting parameter solver dan menjalankannya.

Sebelum solver dijalankan nilai di range F48:F50 tidak sama dengan 0 atau tidak sama dengan nilai di range F48:F50. Solver akan menghitung atau meng-iterasi nilai koreksi di range F37:F42 sehingga didapat nilai  Sum(V2W) atau minimum dan nilai F48:F50=F48:F50.

Langkah-langkah setting di solver adalah:

  • Pada Menu [Data] pada group [Analysis], pilih [Solver]
  • Lakukan setting parameter sebagai berikut:
  • image
[$G$44] Jumlah kuadrat koreksi dicari yang paling minimum Sum(V2W)
[$F$37:$F$42] Nilai koreksi untuk masing-masing pengukuran
[$F$48:$F$50=$G$48:$G$50] Persamaan kondisi

Hasil setelah solver dijalankan adalah :

image

B.5. Uji statistik

Pengukuran yang ideal tanpa kesalahan akan menghasilkan nilai Sum(V2W)=0. Kondisi ini biasanya sangat sulit untuk dipenuhi karena setiap pengukuran pasti mengandung koreksi atau kesalahan. Uji statistik diperlukan untuk mentolerir seberapa besar kesalahan yang diperpolehkan. Biasanya yang dipakai adalah uji Chi kuadrat untuk confidence interval 95%

image

[F53] =G44
[F54] =CHIINV(0.05 / 2, E32)
[F55] =IF(F53 < F54, “Pass at the 5% significant level”, “Fail at the 5% significant level”)

silahkan download filenya, untuk dipelajari lebih lanjut.

C. Aplikasi di jaring pengukuran poligon.

C.1. Sket Pengukuran dan Data Pengukuran

image image
Ada dua loop yaitu:
Loop#1: 2-3-4-5
Loop#2: 2-21-22-23-24-5

Data Pengukuran

Sudut: Jarak:
image image

C.2. Menentukan Redudancy (pengukuran lebih) atau jumlah persamaan kondisi

image Banyaknya data pengukuran (n)=18
Pengukuran minimal untuk menentukan koordinat baru (u)=12. *Lihat 6 (enam) tanda kotak untuk jarak dan 6 (enam) lingkaran untuk sudutRedundance (r=n-u)=18-12=6

 

C.3. Memodelkan solver dan membuat persamaan kondisi

Note: ada koreksi solver model untuk pengukuran poligon. Silahkan lihat [XLS-SVY-20]: Tulisan tentang Aplikasi Excel-Solver untuk Hitung Kuadrat Terkecil untuk koreksi modelnya.

D. Aplikasi di jaring pengukuran triangulasi.

D.1. Sket Pengukuran dan Data Pengukuran

image image

D.2. Menentukan Redudancy (pengukuran lebih) atau jumlah persamaan kondisi

  • Lokasi A dan D sudah diketahui atau Fix
  • L1 sampai L8 adalah pengamatan sudut dalam jaringan triangulasi ABCD
  • Pengamatan minimum untuk menentukan lokasi B : L1,L2,L8
  • Pengamatan minimum untuk menentukan lokasi C : L1,L7, L8
  • Pengamatan lebih L3, L4, L5, L6
  • Banyaknya pengamatan/pengukuran (n)=8
  • pengukruan minimum (u)=4
  • reduncancy (r=n-u)=4

D.3. Memodelkan solver dan membuat persamaan kondisi

image

[D44] =1/(G23^2)
Copy sampai ke [D51]

Persamaan Kondisi:

image

D.4. Setting parameter solver dan menjalankannya

Set Target Cell $G$53
Equal to Min
By Changing Cells $F$44:$F$51
Subject to constraint $J$58:$J$61=0

Hasil koreksi setelah solver dijalankan

image

D.5. Uji statistik

image

silahkan donwload filenya untuk dipelajari lebih lanjut

E. Aplikasi di jaring pengukuran trilaterasi.

E.1. Sket Pengukuran dan Data Pengukuran

image image
  • Koordinat A (X,Y) : 4000.000, 5000.000
  • Azimut A ke G (der-men-det): 125-45-31

E.2. Menentukan Redudancy (pengukuran lebih) atau jumlah persamaan kondisi

Lingkaran merah adalah pengukuran jarak minimum untuk menentukan koordinat baru G, B, D, E dan F. Jumlah lingkaran merah ada 9 buah sedangkan jumlah pengukuran adalah 10 pengukuran sehingga:

n 10
u 9
r=n-u 1

sehingga persamaan kondisi hanya 1 (satu) persamaan. Persamaan yang dipilih adalah jumlah ‘sudut dalam’ sama dengan 360 derajat.

E.3. Memodelkan solver dan membuat persamaan kondisi

image

Persamaan kondisi:

image

Hitungan koordinat:

image

E.4. Setting parameter solver dan menjalankannya

Set Target Cell $G$55
Equal to Min
By Changing Cells $F$44:$F$53
Subject to constraint $J$69=0

image

image

donwload file trilaterasi

5 Comments »

  1. trimakasih postingan ini sangat membantu.
    saya mau tanya, untuk standar deviasi jarak dan sudut didapat dari mana?
    apakah sdh dicantumkan dalam total station?

    Comment by tomi — July 4, 2013 @ 00:20

    • bisa didapat dari pengukuran repetitive atau berulang. untuk pengukuran titik kontrol atau jaringan biasanya untuk jarak dan sudut dilakukan berulangkali sehingga dapat dihitung jarak dan sudut rata-rata. demikian juga dengan standard deviasinya..

      Comment by cadex — July 4, 2013 @ 06:57

  2. kalo boleh saya minta dasar teori yg anda pakai untuk penulisan artikel ini.
    untuk dijadikan dasar teori dlm skripsi.

    Comment by tomi — July 4, 2013 @ 00:26

    • saya menggunakan refensi dari Mikhail, Edward M Ph.D and Gordon Grace, Ph.D. “Analysis and Adjustment of Survey Measurement”. Van Nostrand Reinhold Ltd, 1981
      untuk teori least square sedangkan ide menggunakan solver atas ide sendiri. Hasil hitungan least square di buku sama dengan hasil dari solver

      Comment by cadex — July 4, 2013 @ 07:00

  3. wahhhh,,,,, mas makasih banyak atas tulisan di atas. lagi butuh ilmu buat ujiann hiper 1(hitung perataan 1).
    lumayan banyak pencernaan dari penjelasan di atas. (ehhh, pencerahan mas)

    Comment by arifdebalano@gmail.com — December 18, 2013 @ 22:41


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. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 786 other followers

%d bloggers like this: