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] |
