coretan tentang autocad dan excel

April 20, 2015

Aplikasi Microsoft Excel untuk Menghitung Konstanta Pasang Surut dengan Metode Hitung Kwadrat Terkecil

Filed under: Geodesy — cadex @ 14:27

Tulisan ini merupakan lanjutan dari tulisan sebelumnya di http://cadex.co yaitu [XLS-SVY-22] dengan masih menggunakan dasar teori https://cadex.co/2015/01/26/xls-svy-22-rumus-excel-untuk-menghitung-konstanta-pasut-amplitudo-dan-phase-dengan-metode-hitung-kuadrat-terkecil/.

Perbedaan dengan tulisan sebelumnya adalah:

  • Data pengukuran water level (tinggi muka air) adalah tiap jam selama 29 hari
  • Penggunakaan fungsi OFFSET() diganti dengan fungsi INDEX() untuk mempercepat proses perhitungan karena fungsi OFFSET() adalah termasuk dalam category Volatile Function dalam Microsoft Excel

Berikut adalah tahapan pengolahan data pengukuran tinggi water level untuk menghitung 9 Konstanta Pasan Surut (Pasut):

Input Data Pengukuran

Data dimasukkan dalam sheet [input]

 

 

[A3] =”D-“&ROWS($B$3:B3) Membuat kode urut D-1 yang merupakan kode pengamatan hari pertama.
Copy rumus [A3] sampai ke kode D-29 atau pengamatan hari terakhir
[B3] Masukkan tanggal awal pengamatan
[B4] =B3+1 Tanggal hari berikutnya, yaitu tanggal pertama [B3] ditambah 1 (hari)
Copy rumus di [B4] ke bawah sampai hari terakhir pengamatan
Format cells [B4:B31] : Date
[C2] =TIME(0,0,0) Jam pengamatan dimulai di 00:00
[D2] =C2+TIME(1,0,0) Pengamatan 1 (satu) jam berikutnya
Copy rumus [D2] ke kanan sampai pada jam 23:00
Format cells [D2:Z2] : Time
[C3:Z31] Masukkan tinggi muka air sesuai dengan tanggal di kolom B dan jam di baris 2 dengan satuan meter

 

Buat Named Range dengan ketentuan sebagai berikut

Name Data
Refer to =$C$3:INDEX($Z$3:$Z$3000,COUNT($Z$3:$Z$3000))

 

Cara membuat Named Range, silakan click [XLS-PMG-02]: Rumus Terbilang Di Excel (Tanpa Macro/VBA)

 

Proses Hitungan Kuadrat Terkecil (Least Square)

Misal proses hitungan Least Square (Hitung Kuadrat Terkecil) yang terdiri dari proses pembentukan [Matrix L], [Matrix A], [Matrix X] dan [[Matrix V] ada di sheet [Calc-9], maka urutan perhitungannya adalah sebagai berikut:

Matrix Observasi [Matrix L]

Data yang telah diisikan di sheet [input] kemudian dirubah menjadi data matrix observasi (Matrix L).

 

 

[B1] =COUNT($A$4:$A$40000) menghitung jumlah data pengamatan berdasarkan isian jam di kolom [A4:A40000].pada contoh kali ini, maksimal data jam diisikan sampai A40000.
[B2] =2*9+1 banyaknya parameter yang akan dicari yaitu A1–A9 (9 parameter), B1–B9 (9 parameter) dan Z0 (1 parameter). Total parameter=19
[A4] =IF(MOD(ROWS($B$4:B4),24)=0,24,MOD(ROWS($B$4:B4),24)) membuat array atau deret 1 – 24 di kolom A yaitu nomer urut jam pengamatan
copy rumus [A4] ke [A5]
[B4] =1
[B5] =B4+(A5=1) membuat array nomer urut hari pengamatan
copy rumus [A5:B5] ke bawah sampai [A699:B699] atau sampai ke hari pengamatan terakhir
[C1] =input!$B$3 tanggal pertama pengamatan
[D4] =ROWS($E$4:E4) membuat nomer urut
[E4] =$C$1+$B4-1+TIME($A4-1,0,0) membuat urutan tanggal dan jam pengamatanset format cell atau format number di [E4] menjadi “d-mmm-yy h:mm;@”
[F4] =INDEX(Data,B4,A4) Mengambil data pengamatan pada hari ke 1 jam ke 1
Copy [D4:F4] ke bawah sampai ke [D699:F699]

 

Buat Named Range dengan ketentuan sebagai berikut

Name MatrixL
Refer to =$F$4:INDEX($F$4:$F$39970,$B$1)

 

Membuat Matrix Koefisien (Matrix A)

 

Untuk menghitung Matrix Koefisien (Matrix A) dibutuhkan informasi kecepatan sudut untuk masing-masing konstanta pasut seperti dalam table di bawah:

 

 

Berdasarkan table di atas dan dasar teori perhitungan pasut di https://cadex.co/2015/01/26/xls-svy-22-rumus-excel-untuk-menghitung-konstanta-pasut-amplitudo-dan-phase-dengan-metode-hitung-kuadrat-terkecil/, maka spreadsheet untuk menghitung Matrix A adala

 

 

[I1] =1 Atur format number “w 0”
[J1] =MOD(COLUMNS(H$1:$I1),2)+I1 Copy rumus [J1] ke kanan sampai [Z1] untuk membuat deret 1,1,2,2,3,3,……..,9,9
[I2] =INDEX($AO$5:$AO$13,I$1) Mengambil nilai kecepatan sudut dari range [AO5:AO13]
[H3] Z0 Parameter muka air rata-rata
[I3:Z3] Isian parameter A1—A9, B1—B9
[H1:H699] 1 Masukkan angka 1 di kolom [H1:H699]
[I4] =IF(LEFT(I$3,1)=”A”,COS(I$2*$D4),-SIN(I$2*$D4)) Copy rumus[I4] ke [I4:Z699]

 

Name MatrixA
Refer to =$H$4:INDEX($Z$4:$Z$39970,$B$1)

 

Menghitung Matrix Parameter (Matrix X), Matrix Koreksi (Matrix V) dan Matrix Ukuran Terkoreksi (Matrix La)

 

 

[AD4] =INDEX(MMULT(MMULT(MINVERSE(MMULT(TRANSPOSE(MatrixA),MatrixA)),TRANSPOSE(MatrixA)),MatrixL),AB4)
Rumus di atas adalah rumus array atau array formula. Saat menggunakan rumus array, setelah menuliskan rumus di atas akhir dengan combinasi CTRL+SHIFT+ENTER
Copy rumus [AD4] ke bawah sampai [AD22]
[AF4] =MMULT(H4:Z4,$AD$4:$AD$22)-$F4
[AG4] =F4+AF4
Copy rumus [AF4:AG4] ke bawah sampai [AF699:AG699]

 

Menghitung Konstanta Pasut

 

 

[AP5] =VLOOKUP(AP$3&$AI5,$AC$5:$AD$22,2,0) Mengambil nilai parameter A1
Copy rumus [AP5] ke range [AP5: AQ13] untuk mengambil nilai parameter yang lain
[AR4] =AD4 Mengambil nilai parameter Z0
[AR5] =SQRT(AP5^2+AQ5^2) Menghitung amplitude
[AS5] =MOD(DEGREES(ATAN2(AP5,AQ5)),360) Menghitung sudut phase
[AT5] =RADIANS(AS5) Merubah unit sudut dari derajat ke radians
Copy rumus [AR5:AT5] ke bawah sampai [AR13:AT13]

 download contoh file

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: