coretan tentang autocad dan excel

January 26, 2015

[XLS-SVY-22]: Rumus Excel untuk Menghitung Konstanta Pasut (Amplitudo Dan Phase) Dengan Metode Hitung Kuadrat Terkecil (lanjutan)

Filed under: Excel, Tukang Ukur — cadex @ 16:13

Referensi

:

[XLS-SVY-01]: Prediksi Pasut Dengan VBA Excel

Plaform

:

Microsoft Excel

Lokasi File

:

 

Uraian di bawah adalah lanjutan dari posting sebelumnya

A. Input Data Pengamatan

image

Data pengamatan dimasukkan dengan format table seperti di atas dengan terlebih dahulu memasukkan tanggal pengamatan pertama di cell [C6]. Data pengamatan muka air tiap jam pada tanggal yang sama dimasukan sesuai mulai dari kolom [D:AA].

B. Konstanta Astronomis untuk 9 Konstituent

image

Cell

Rumus

Keterangan

[E27]

=360/D27

Merubah periode menjadi kecepatan satuan derajat/jam

   

Copy rumus di [E27] ke bawah untuk menghitung kecepatan sudut tiap konstituen

[F27]

=RADIANS(E27)

Merubah kecepatan sudut dalam satuan radian/jam

   

Copy rumus [F27] ke bawah untuk menghitung kecepatan sudut di tiap konstituen

C. Membuat Persamaan Observasi dan Menghitung dengan Hitung Kuadrat Terkecil

C.1. Matrix Observasi [L]

image

Cell

Rumus

Keterangan

[E74]

=COUNT(B6:B20)

Menghitung lama pengamatan dalam satuan hari

[E75]

=E74*24

Menghitung banyaknya pengamatan atau observasi

[AC6]

=IF(COUNT($AD$6:AD6)<=$E$75,COUNT($AD$6:AD6),NA())

Urutan pengamatan atau urutan persamaan observasi.

   

Copy rumus [AC6] sampai ke [AC365]

[AD6]

=1

Masukkan angka 1 untuk hari pertama

[AD7]

=AD6+($AE7=1)

Jika periode jam di cell [AE7]=1, maka masuk ke hari berikutnya

   

Copy rumus [AD7] sampai ke [AD365]

[AE6]

=IF(MOD(ROWS($AD$6:AD6),24)=0,24,MOD(ROWS($AD$6:AD6),24))

Membuat urutan atau deret periode jam dari jam ke-1 sampai ke-24

   

Copy rumus [AE6] sampai ke [AE365]

[AF6]

=OFFSET($C$5,$AD6,)+OFFSET($C$5,,$AE6)

Mengambil tanggal dan jam pengamatan

   

Copy rumus [AF6] sampai ke [AF365]

[AG6]

=OFFSET($C$5,$AD6,$AE6)

Mengambil nilai muka air sesuai dengan tanggal dan jam pengamatan

   

Copy rumus [AG6] sampai ke [AG365]

     
C.2. Matrix Koefisien [A]

image

Cell

Rumus

Keterangan

[AI1]

=MOD(COLUMNS($AI$2:AI2),2)

Membuat deret angka 1,1,2,2,3,3,4,4,…..,9,9

   

Copy rumus [AI1] sampai ke [AZ1]

[AI2]

=OFFSET($F$26,AI1,)

Mengambil kecepatan sudut untuk masing-masing konstituen

   

Copy rumus [AI2] sampai ke [AZ2]

[AH6]

=1

δht1/δZo

   

Copy rumus [AH6] sampai ke [AH365]

[AI6]

=IF(LEFT(AI$5,1)="A",COS(AI$2*$AC6),-SIN(AI$2*$AC6))

Jika huruf pertama di cells [AI5]=A, maka dihitung δht1/δA1, jika B maka δht1/δB1

   

Copy rumus [AI6] sampai ke [AI6:AZ365]untuk menghitung nilai derivative untuk setaip persamaan observasi

Buat Named Range dengan properties sebagai berikut:

image

C.3. Menghitung Parameter

image

Cell

Rumus

Keterangan

[BD6]

=MMULT(MMULT(MINVERSE(MMULT(TRANSPOSE(MatrixA),MatrixA))
,TRANSPOSE(MatrixA)),MatrixL)

Menghitung Parameter

   

Untuk mengcopy rumus di [BD6}, lakukan langkah berikut:

· Block atau pilih range [BD6:BD24], kemudian tekan tombol F2

· Kemudian tekan bersamaan CTRL+SHIFT+ENTER

[BF6]

=INDEX(MMULT(MatrixA,$BD$6:$BD$24),AC6)-AG6

Menghitung koreksi ukuran

   

Copy rumus di [BF6] sampai [BF365]

[BG6]

=BF6+AG6

Tinggi muka air terkoreksi

D. Menghitung Amplitudo dan Phase

image

Cell

Rumus

Keterangan

[D81]

=BD6

Tinggi muka air rata-rata

[E82]

=VLOOKUP(E$80&$B82,$BC$6:$BD$24,2,0)

Mengambil parameter A1

   

Copy rumus [E82] ke range [E82:F90]

[G81]

=D81

 

[G82]

=SQRT(E82^2+F82^2)

Menghitung amplitude

   

Copy rumus [G82] sampai [G90]

[H82]

=MOD(DEGREES(ATAN2(E82,F82)),360)

Menghitung sudut phase dalam satuan derajat/jam

[I82]

=RADIANS(H82)

Menghitung sudut phase dalam satuan radian/jam

Dengan acuan hitungan di atas, maka untuk pengamatan 30 hari atau lebih bisa dikembangkan lebih lanjut. Demikian juga jika diperlukan untuk perhitungan konstanta pasut yang lebih atau kurang dari 9 konstituent.

Silakan download file di sini untuk dipelajari lebih lanjut.

==semoga bermanfaat==

8 Comments »

  1. Salam kenal Pak, Saya ingin bertanya tentang invers matriks bobot pengamatan. metode inversnya apakah menggunakan model dekomposit baik itu metode cholesky ataupun dengan model gauss? Kemudian setahu saya dari apa yang dipaparkan diatas hanya menghasilkan parameter konstituen phase dan amplitude ‘sementara’ bukan yang sebenarnya, dikarenakan faktor astronomisnya diabaikan. saya mahasiswa akhir kebetulan mengambil Tugas Akhir analisa pasang surut namun metode yang digunakan metode admiralty. Terima kasih Wassalam

    Comment by rickernel[dot]Inc — May 27, 2015 @ 01:26

    • terimakasih sudah berkunjung dan memberi komentar. Terkait dengan pertanyaan Anda saya jelaskan sebagai berikut:
      1. matrix bobot atau biasanya disebut dengan Matrix P belum dimasukkan dalam perhitungan. Jika dimasukkan dalam perhitungan, rumus untuk mencari parameter dalam bentuk matrix adalah X=(AtPA)^-1AtPL. At=Transpose Matrix A, (AtPA)^-1 = inverse dari AtPA. Rumus inverse sendiri saya menggunakan fungsi bawaan di excel yaitu =MINVERSE(). saya kurang tahu metode apa yang dipakai software excel untuk menghitung inversenya

      2. Apakah yang Anda maksud dengan faktor astronomis? Kalau yang dimaksud adalah pengaruh karena faktor pergerakan matahari, bumi dan bulan yang mempengaruhi pasang surut tentunya sudah dimasukkan. Perhatikan nilai kecepatan sudut krn pengaruh bulan, matahari dan bulan yang telah dimasukkan dalam perhitungan. detail tentang faktor astronomis, silakan baca https://cadex.co/2012/11/18/xls-svy-19-simulasi-pasang-surut-laut-berdasarkan-input-9-konstituent-9-phase-bagian-2/

      3. Jika menggunakan metode admiralty, coba cek hasil konstanta pasutnya untuk dimasukkan lagi dalam persamaan tidal harmonic, apakah sama dengan atau mendekati dengan hasil pengukuran?

      Comment by cadex — May 27, 2015 @ 16:14

  2. terima kasih bapak sudah berkenan membalas pertanyaan saya, mengacu pada jawaban bapak no. 2, itu bisa juga dicari dengan bilangan doodson, silahkan dikroscek http://oceanworld.tamu.edu/resources/ocng_textbook/chapter17/chapter17_04.htm pada rumus 17.7 semisal nilai m2 = 14.49205211 X 2 (parameter m2 = 2) maka akan menghasilkan 28.984 begitu juga parameter notasi tiap konstituen yang lainnya yang akan dicari.
    saya kira langkah2 yang diatas masih ada kekurangan dimana node factor, hitungan bulan kabisat serta mengetahui jam yang sebenarnya belum dilakukan perhitungan secara overall, artinya setelah menggunakan rumus 6 dan 7 https://cadex.co/2015/01/26/xls-svy-22-rumus-excel-untuk-menghitung-konstanta-pasut-amplitudo-dan-phase-dengan-metode-hitung-kuadrat-terkecil/ menghasilkan konstituen sementara, sedangkan pembetulan amplitudo dan fase tiap2 komponen tidak hanya berakhir pada rumus 7 dan 8 melainkan ada rumus tambahan. Terima kasih

    Comment by rickernel[dot]Inc — May 27, 2015 @ 18:36

    • terimakasih masukannya.
      memang benar nodal faktor belum dimasukkan dalam perhitungan, sehingga rumus yang Anda maksud butuh penyesuaian. Jika berkenan rumus tambahan apa yang diperlukan sehingga bisa mencakup tahun kabisat?

      Jika seandainya konstanta pasut dihitung dengan admiralty, kemudian hasil konstituent tersebut dipakai untuk menghitung tinggi air di hari dan jam saat pengamatan. apakah mendapatkan hasil yang mendekati? Terus terang, metode perhitungan yang saya gunakan spt yang saya tulis, memberikan hasil yang tidak berbesa jauh.

      terimakasih

      Comment by cadex — May 27, 2015 @ 19:10

  3. silahkan download di http://laboseanografi.mipa.unsri.ac.id/?page_id=113 di (point 6) disana ada dijelaskan dimulai dari mencari besaran2 hingga faktor astronomisnya (bulan kabisat dan lain2). namun metode ini hanya berlaku untuk ‘admiralty’ saja, sedangkan metode least square berbeda. saya tidak memiliki referensinya untuk least square makanya saya cari kesini, hehe dan 1 hal lagi untuk matrik inversnya biasanya menggunakan model dekomposit (segitiga atas dan bawah) silahkan didownload di http://www.mediafire.com/download/egbjrc8e2xda7d7/1692_Matriks+Persamaan+Linier+dan+Pemograman+Linier.pdf
    disana dijelaskan penjelasan matrik dasar hingga persamaan linier. mohon maaf pak saya bukan mengkritik tapi sekedar share saja :) kita sama2 berbagi, terima kasih

    Comment by rickernel[dot]Inc — May 27, 2015 @ 22:00

    • terimakasih masukannya. kritiknya membangun kok. saya sudah lama mencari referensi tentang admiralty. Saya coba pelajari metode dari link telah Anda berikan. Kalo tentang inverse matrik dengan metode LU decompososition tidak akan beda jauh dengan rumus =minverse() di excel. Metode tersebut hanya salah satu cara perhitungan invers saja. Malah ada metode yang tidak pakai inverse langsung didapat nilai parameternya. Tetapi metode ini harus pakai bahasa pemograman atau kalo di excel menggunakan macro/vba.

      Comment by cadex — May 27, 2015 @ 23:38

  4. untuk perbandingan metode least square dan admiralty tidak berbeda jauh, baik dari hasil konstanta maupun prediksi tinggi muka air yang akan datang. namun kelemahan admiralty hanya bisa dihitung mode 15 dan 29 hari, sedangkan least square tidak terbatas. untuk prediksinya baik least square dan admiralty menggunakan rumus yang sama.

    Comment by rickernel[dot]Inc — May 27, 2015 @ 22:04

    • ada contoh hitungan hasil perbandingan yang dimaksud?

      Comment by cadex — May 27, 2015 @ 23:39


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

Blog at WordPress.com.

%d bloggers like this: