Tag: solver

[XLS-SVY-20]: Tulisan tentang Aplikasi Excel-Solver untuk Hitung Kuadrat Terkecil

Referensi	:	[XLS-SVY-17]: Solver untuk Aplikasi Hitung Kuadrat Terkecil Metode Kondisi
Platform	:	Excel 2007
Lokasi File	:

Pada tulisan [XLS-SVY-17]: Solver untuk Aplikasi Hitung Kuadrat Terkecil Metode Kondisi telah diuraikan cara menggunakan excel-solver untuk perataan hasil ukuran levelling, triangulasi, trilaterasi dan traverse (poligon). Catatan tersebut kemudian saya coba tulis dalam bahasa inggris kemudian saya coba share ke forum http://landsurveyorsunited.com walaupun bahasa inggris saya kurang bagus, tetapi yang penting kalo nggak dicoba, kapan lagi? Silahkan download filenya dalam format PDF. Mohon bagi pembaca yang pinter bahasa inggris untuk mengoreksinya.

Ada perubahan kecil di solver model levelling, triangulasi & trilaterasi dibandingkan dengan posting terdahulu. Khusus untuk poligon ada perubahan cukup mendasar yaitu dengan ditambahkan nilai konversi dari detik ke radian. Konversi ini diperlukan karena dalam pengukuran poligon ada dua unit yaitu unit jarak dalam satuan meter dan satuan sudut dalam satuan detik/derajat.

Silahkan download solver model terbaru untuk pengukuran levelling, triangulasi, trilaterasi dan traverse (poligon).

saya tunggu comments dan masukkannya.

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

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 v1² + v2²+ v3²akan minimum. Atau dalam bentuk umum

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

(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

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.

[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]
Pilih atau check [Create names from values in the : Left column]

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(V²W) 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:

[$G$44]	Jumlah kuadrat koreksi dicari yang paling minimum Sum(V²W)
[$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 :

B.5. Uji statistik

Pengukuran yang ideal tanpa kesalahan akan menghasilkan nilai Sum(V²W)=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%

[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

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

Data Pengukuran

Sudut:	Jarak:

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

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

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

[D44]

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

Persamaan Kondisi:

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

D.5. Uji statistik

silahkan donwload filenya untuk dipelajari lebih lanjut

E. Aplikasi di jaring pengukuran trilaterasi.

E.1. Sket Pengukuran dan Data Pengukuran

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

Persamaan kondisi:

Hitungan koordinat:

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

donwload file trilaterasi

[GEN-PMG-03]: Pemakaian Solver untuk Multiple Goal Seek

Referensi	:	Contoh Rumus
Platform	:	Excel 2003
Lokasi File	:	spreadsheet

Program (Add-in) solver harus sudah terinstall di excel, sebelum menjalan proses multiple goal seek. Cara menginstall Add-in solver silahkan click di sini.

Misalkan kita punya data spread sheet excel yang berisi list karyawan yang akan dihitung gross up PPH-21 sebagai berikut:

[F8:F22]	: Nilai ‘THP GROSS UP’ yang akan dicari dengan solver, sehingga ‘THP GROSS UP’ dikurangi ‘PPH21’ samadengan THP_MASAKERJA
[G8:G22]	: PTKP adalah Penghasilan Tidak Kena Pajak, sesuai dengan masa kerja dan status masing-masing personel [G8]=SUMPRODUCT(($A$1:$A$5=C8)($B$1:$B$5)D8) copy [G8] sampai [G22]
[H8:H22]	:PKP , Penghasilan Kena Pajak. yaitu nilai ‘THP GROSS UP’ dikurangi PTKP [H8]=F8-G8
[I8:I22]	: Rumus hitungan PPH 21 [I8]=IF(H8>500000000,30%(H8-500000000)+95000000,IF(H8>250000000,25%(H8-250000000)+32500000,IF(H8>50000000,15%(H8-50000000)+2500000,H85%)))
[J8:J22]	: ‘THP GROSS UP’ dikurangi ‘PPH21’ [J8]=F8-I8
[K8:K22]	: Kolom CEK, harus bernilai 0. Untuk mengecek [E8:E22]=[J8:J22] [K8]=J8-E8
[K5]	: target cell yang digunakan dalam proses hitungan solver. =SUM(K8:K22)

Click Menu Tools, kemudian pilih Solver

Set parameter solver seperti berikut:

Rev.01: Untuk mempercepat proses hitungan, constraint $F$8:$F$22>=0 diganti menjadi $F$8:$F$22>=$E$8:$E$22

Click tombol ‘Solve’

Hasil hitungan ‘THP GROSS UP’ :

—selamat mencoba—

[XLS-SVY-02]: Catatan Rumus Excel untuk Juru Ukur (Bagian #2)

Referensi	:
Platform	:	Excel, dengan add in solver
Lokasi File	:	click di sini

Perhitungan Poligon dengan menggunakan Solver

Program solver harus sudah terinstall, jika akan menggunkan metode ini.

Pada excel 2003, menu ‘Tool’ kemudian pilih ‘Add-Ins’

Pastikan bahwa ‘Solver Add-in’ terpilih (checked)

Misal jalur pengukuran poligon adalah :

Berdasarkan data di atas, kemudian disusun atau ditabulasi di excel sebagai berikut:

E5=B5+C5/60+D5/3600, kemudian dicopy ke E7 dan E9.

E13=SUM(E3:E11)

F5=$F$13, kemudian dicopy ke F7 dan F9

F5 adalah koreksi sudut dalam satuan derajat yang akan dicari dengan menggunakan solver

G4=R4

G4 adalah azimuth awal dari Q ke R yang dihitung berdasarkan koordinat Q dan R

R4=DEGREES(ATAN2((Q5-Q3),(P5-P3)))+IF(ATAN2((Q5-Q3),(P5-P3))<0,360)

G6=G4+E5+F5-180+IF(G4+E5+F5-180<0,360,IF(G4+E5+F5-180>360,-360)), kemudian dicopy ke G8 dan G10

G6, G8 dan G10 adalah azimuth yang dihitung berdasarkan sudut horizontal dan koreksi sudut

Hasil azimuth di G10, seharusnya adalah sama dengan azimuth di R10. R10 adalah azimuth dari titik tetap S ke T.

Dalam perhitungan solver kondisi G10=R10 akan dipakai sebagai constraint nomer #1.

Solver akan merubah nilai di F13 sampai diperoleh nilai G10=R10.

H13=SUM(H3:H11)

I6=H6*SIN(RADIANS(G6)), kemudian dicopy ke I8

J6=(H6/$H$13)*$J$13, kemudian dicopy ke J8

K6=H6*COS(RADIANS(G6)), kemudian dicopy ke K8

L6=(H6/$H$13)*$L$13, kemudian dicopy ke L8

J13 dan L13 adalah koreksi absis dan ordinat yang akan dicari dengan menggunakan solver.

M7=M5+I6+J6, kemudian dicopy ke M9

N7=N5+K6+L6, kemudain dicopy ke N9

Hasil koordinat di M9,N9 harusnya sama dengan nilai koordinat di P9,Q9.

P9,Q9 adalah koordinat titik fix(tetap) dari titik S.

Kondisi M9=P9 dan N9=Q9 adalah contraint nomer #2 dan #3.

Solver akan merubah nilai di J13 dan L13 sampai diperoleh nilai M9=P9 dan N9=Q9.

S9=–AND(P9=M9,Q9=N9)

nilai S9=1, jika koordinat koordinat di M9,N9 sama dengan P9,Q9.

Pada menu excel (excel-2003), pilih ‘Tool’ kemudian ‘Solver’.

Set solver seperti gambar di bawah:

Perhatikan nilai atau cell di ‘Set Target’, ‘Changing Cells’ dan ‘Constraint’

Jika setting sudah sesuai di atas, click tombol ‘Solve’

Hasilnya adalah hitungan poligon yang sudah terkoreksi, sudut, absis dan ordinatnya

~selamat mencoba~