Category: Excel

[XLS-PMG-08]: Analisa Data dari Google Sheet dengan Power Query

Reference

:

Power Query – Overview & Learning

Platform

:

Minimum Microsoft Excel 2010

Download

:

 

 

Pada tulisan ini akan diuraikan tahapan untuk mengambil data dari file Google Sheet kemudian diolah lebih lanjut di Microsoft excel dengan menggunakan tool Power Query. Tool ini sudah ada di Microsoft Excel 2010 pro sampai sekarang yang digunakan untuk melakukan koneksi ke beberapa type database, halaman web, text file, transformasi data dan analisa data. Khusus di Microsoft Excel 2010 pro dan 2013, Power Query tersedia dalam bentuk add-in sedangkan untuk versi setelah itu sudah menjadi built-in tool. Cara instalasi power query silakan mengacu ke https://www.howtoexcel.org/power-query/how-to-install-power-query/

Saat menulis tutorial ini, saya menggunakan dimana tool Power Query ini ada di menu [Data] pada ribbon [Get & Transform Data]

Gambaran file google sheet yang akan diolah berupa catatan progress harian pekerjaan pengukuran bidang tanah yang terbagi dalam beberapa sheet. Tiap sheet menunjukkan desa sedangkan dalam sheet terdapat catatan harian yang dibedakan berdasarkan blok.

Contoh salah satu sheet di google sheet:

Tiga (3) desa lainnya menggunakan format yang sama tetapi disimpan dalam sheet terpisah.

Hasil yang akah dihasiklan adalah rekap progress tiap blok di masing-masing desa lengkap dengan tanggal terakhir laporan di masing-masing blok.

Berikut Langkah-langkah yang aku kerjakan. Mohon saran dan usulan jika ada metode yang lain selain yang aku uraikan dalam tutorial ini.

Setting Shareable Link di Google Sheet

Buka file google sheet kemudian click tombol di kanan atas Google Sheet.

Pilih setting:

Copy url sheet download file google sheet

Url ini nantinya digunakan sebagai alamat web saat menggunakan get data from web di Power Query.

Pada menu Google Sheet di menu [File] pilih [Download] kemudian pilih [Microsoft Excel (.xlsx).

Tentukan lokasi download di komputer. File di komputer ini sifatnya hanya sementara saja karena pada saat nanti program dijalankan proses pengambilan data akan selalu mengambil data dari google sheet yang tersimpan di google drive.

Jika proses download selesai, tekan Ctrl+J di chrome untuk melihat link hasil download. Click kanan di link download, kemudian pilih [Copy link address]

Simpan / Paste link address tersebut di notepad atau di file lainnya untuk nantinya akan digunakan di power query.

Setting connection ke web di Power Query

  1. Jalankan program Excel.
  2. Pilih sheet yang kosong kemudian buat table seeperti di bawah ini:

Cell [A2] berisi alamat link download yang dicopy dari google sheet di Langkah sebelumnya.

  1. Rubah table di atas menjadi dengan cara blok range [A1:A2], kemudian tekan CTRL+T. Check pilihan [My table has headers].
  2. Namai table misalnya : tbl_sheet

  1. Convert tabe tbl_gsheet menjadi data connection dengan memilih menu [Data] kemudian [From Table/Range]

Setelah Langkah di atas, maka ditampilkan window Power Query Editor.

  1. Click kanan, Task Pane Queries (area kosong di bawah tulisan tbl_gsheet), pilih [New Query]>[Other Sources]>[Web]

Pada isian URL, masukkan lagi alamat link google sheet.

  1. Pada setting Navigator, Pilih salah satu sheet yang akan diolah, misal Bantarwuni, kemudian click [OK]

 

Data yang akan diambil mulai baris ke 5 ke bawah.

Setelah tahap di atas Task Pane Queries menampilkan tambahan query [Bantarwuni]

  1. Pilih query [Bantarwuni] kemudian di pilih [Source[ di tahapan Query Setting.

Copy formula di formula bar:

Formula ini akan ditambahkan di query tbl_gsheet.

  1. Pilih Query tbl_gsheet kemudian di Power Query pilih menu [Add Column], pilih [Custom Column]

  1. Pada column name isikan nama kolom misal Data, kemudian paste formula dari Langkah sebelumnya ke custom colum formula:

  1. Text dalam tanda petik hasil copy dari Langkah sebelumnya adalah sama dengan kolom Gsheet, sehingga kita bisa ganti text tersebut, termasuk tanda petiknya, dengan columns [Gsheet]. Blok mulai tanda petik awal sampai tanda petik akhir, lalu pilih kolom Gsheet di Available columns kemudian click [<<Insert], custom column formula menjadi

Click tombol [OK], kemudian click [Continue] saat ada warning data privacy. Anda bisa juga set data privacy setting dengan pilihan yang telah disediakan. Pada tutorial saat ini saya pilih “Ignore Privacy Level”

Click [Save]

Sampai tahap ini , sudah dilakukan setting agar Power Query menjalankan query dengan terlebih dahulu mencari sumber data yang ditulis ditabel tbl_gsheet. Jika menggunakan data yang lain, cukup merubah alamat url di table tbl_gsheet tanpa perlu mengulang Langkah 1-11.

Untuk menyimpan Langkah di atas, sebelum dilanjutkan ke Langkah berikutnya, pada menu [File] di query editor, pilih [Save & Load to] kemudian pilih [Only create connection]

Menggabungkan sheet yang diinginkan

Seperti pada Langkah sebelumnya , proses penggabungan menggunakan fungsi yang di-copy dari query tbl_bantarwuni.

  1. Pilih query [Bantarwuni] kemudian di pilih [Source[ di tahapan Query Setting, click setting [Navigation]

    Pada tampilan table di atas, baris yang akan diproses adalah mulai baris ke-5 dengan sedangkan untuk baris ke-4 tidak diikutkan dalam proses agar saat penggabungan dari sheet lainnya text Desa, No dan Tanggal tidak muncul di hasil query penggabungan.

     

  2. Agar baris 1-3 tidak tampil di table hasil import, saat setting [Navigation] terpilih rubah formula

     

    Semula:

    = Source{[Item=”Bantarwuni”,Kind=”Sheet”]}[Data]

     

    Menjadi:

    =Table.Skip(Source{[Item=”Bantarwuni”,Kind=”Sheet”]}[Data],3)

     

    Sehingga tampilan table menjadi:

     

    Formula pada langkah 2 di atas kemudian dicopy ke query tbl_gsheet sehingga nantinya akan berlaku di sheet yang lain tidak hanya di sheet Bantarwuni.

     

  3. Pilih query bantarwuni, kemudian click tanda panah / tombol [Expand] di samping tulisan Data

    Pilih [Select all columns] kemudian click [OK]

    Lakukan filter Data.Kind = Sheet dan [Data.Item]=Petahunan, Pangebatan dan Bantarwuni. [Data.Item] berisi data sheet yang mempunyai struktur table yang sama dengan table/sheet bantarwuni yang akan kita gabung menjadi satu table untuk proses Analisa selanjutnya.

     

  4. Pilih kolom [Data.Data] kemudian dari menu [Home], pada pilihan [Remove Columns], click [Remove Other Columns]

     

    Table menjadi:

  5. Langkah selanjutnya adalah membuat kolom baru yang berisi table baru dengan tidak mengikutkan data di baris 1 sampai 3 (Langkah 2).

    Click [Add Colum]>[Custom Column]

     

    Pada New Column Name isikan Data kemudian paste formula =Table.Skip(Source{[Item=”Bantarwuni”,Kind=”Sheet”]}[Data],4) ke custom column formula kemudian rubah formula tersebut menjadi

    Kemudian click [OK]

  6. Pilih column [Data] kemudian pilih [Remove Other Column] dari menu [Home], sehingga query hanya menampilkan kolom [Data]
  7. Click tombol [Expand] di samping kanan tulisan [Data], kemudian pilih [Select All Columns], Hasil dari proses menghasilkan table baru hasil gabungan dari sheet yang dipilih pada Langkah 3, tetapi belum ada nama headernya.

     

     

  8. Jadikan baris pertama sebagai nama header dengan cara click tombol [Use First Row as Header] di menu [Transform]

     

  9. Hasil dari Langkah-9, text header dari sheet yang lain masih masuk di hasil query gabungan, untuk menghilangkannya filter header [Desa] kemudian uncheck tulisan “Desa” dan (null)

Analisa Produktivitas Harian dengan Menggunakan Fungsi Agregat

Pada tahap ini akan dilakukan rangkuman progress pengukuran tiap blok di masing-masing desa dengan menjumlahkan progress harian di masing-masing blok.

  1. Pilih header atau kolom [Blok-01] sampai [Blok-68]
  2. Kemudian dari menu [Transform], pilih [Unpivot Column]
  3. Tekan tombol CTRL, pilih kolom [Desa], [Tanggal], [Atribute] dan [Value] kemudian dari menu [Home] pilih [Remove other column] sehingga tampilan table menjadi:

     

     

  4. Tekan tombol CTRL, pilih kolom [Desa],dan [Atribute], kemudian dari menu [Transform], pilih [Group by]
  5. Lakukan setting sebagai berikut:

     

    Kemudian click OK

     

  6. Expand tombol Rangkuman, kemudian pilih [Agregate]
  7. Pada Agregate [tanggal], pilih Minimum dan Maximum untuk mengetahui tanggal awal dan akhir pengukuran

  8. Kemudian pilih Sum of Value untuk menampilkan pemjumlah progress tiap bloknya.
  9. Clik [OK]
  10. Rubah nama header, kemudian lakukan sorting [Desa] kemudian [Blok] sehingga tabel menjadi

     

     

    Pada tahap ini table query bantarwuni tidak diperlukan lagi, table tersebut hanya digunakan untuk keperluan copy formula saja. Cara menghapus query, click kanan query di task pane query kemudian pilih [Delete]

  11. Tutup dan simpan hasil query dengan memilih menu [Home], kemudian [Close and Load]

     

     

Menampilkan Hasil Query di Worksheet Excel Sebagai Tabel

  1. Pada menu [Data] di Microsoft excel, pilih [Queries & Connections]

  2. Pada group queries, click kanan query tbl_gsheet kemdian pilih [Load To]
  3. Pilih [Table] kemudian pilih [New Worksheet] atau [Existing Worksheet]
  4. Maka table akan ditampilkan dalam excel.
  5. Lakukan format angka dan tanggal jika diperlukan.

Tabel ini bersifat dinamis, jika ada perubahan di google sheet, maka dengan menkan tombol [Refresh All], maka data otomatis akan terupdate

#silakan dicoba#

 

 

 

 

[CAD-CIV-18]: Import titik format: Bujur, Lintang , Tinggi (Latitude, Longitude, Elevation) sebagai point cloud di Civil 3D

Pada tulisan [CAD-CIV-17]: Membuat Surface dari Data LIDAR / Point Clouds di Civil 3D telah diuraikan langkah-langkah membuat surface dari data point cloud ekteksi LAS yaitu format file dari proses pemetaan dengan LIDAR. Sedangkan pada tulisan ini akan dijelaskan cara import titik koordinat format PointNumber, Bujur, Lintang , Tinggi dari ribuan titik dan diperlakukan sebagai point clount bukan sebagai object titik di civil 3d.

Sumber data saya dapatkan dari anggota grup I love geodesy yang menanyakan cara import titik forma bujur,lintang dalam format csv (comma delimited) terdiri dari 104285 titik ke civil 3D. Pada grup tersebut sudah saya jelaskan cara import titik tersebut dengan membuat custom format kemudian ditentukan type proyeksinya (zone utm) sebelum dilakukan import ke civil 3D. Titik yang didapat pada proses tersebut berupa object titik civil 3D bukan berupa point clouds.

Berikut adalah cara import titik csv dalam format Bujur, Lintang dan Tinggi ke Civil 3D sebagi point cloud

  1. Buka file csv dan tentukan urutan kolomya.

Pada contoh file tersebut urutan kolomnya adalah:

PointNumber(PN), Bujur (Long), Lintang (Lat), Elevasi (Z)

  1. Tentukan Zone (Projection/Grid Coordinate)

    Misalkan data akan diproyeksikan ke koordinat UTM, maka berdarkan lokasi bujur dan lintang, zone utm-nya terletak di Zone 48N

  1. Jalankan Civil 3D dengan template metric, kemudian lakukan setting coordinate system gambar ke Zone UTM84-48N

    >>Pada [Toolspace] civil 3D, pilih [Setting]

    >>Click kanan [Drawing1], kemudian pilih [Edit Drawing Setting..]

    >>Pada tab [Units and Zone], masukkan di Selected coordinate system code : UTM84-48N, kemudian click [Apply] lalu [OK]

  1. Membuat Database Point Clouds

    >> Pada Civil 3D [Toolspace], pilih tab [Prospector]

    >> Click kanan [Point Clouds] kemudian pilih [Create Point Cloud…}

>> Pada setting [Information] , pilih salah satu [Point Cloud Style] misal [Elevation Ranges], kemudian click [Next]
>> Pada setting [Source Data]:

>> pilih point cloud format [PENZ (comma delimited). (1)

>> Klik icon kemudian pilih file yang akan diimport dengan terlebih dahulu menyesuaikan extensi filenya. Pada contoh extensi filenya adalah csv. (2)

>> Kilk icon untuk menentukan Coordinat sistem (3), kemudian isikan pada [Selected coordinate system code]: LL84

Artinya adalah: file sumber coordinate systemnya Latitute-Longitude (lintang, bujur) dalam ellipsoid wgs 84

>> Kilk [Finish]

  1. Proses pembuatan point clouds sedang diproses
  1. Jika proses sudah selesai, maka pada civil 3d [Toolspace] di tab [Propector] di bawah group [Point Clouds] akan muncul database database point cloud baru
  1. Lakukan zoom extent untuk melihat hasil import
  1. Untuk merubah point cloude menjadi surface,:

    >> click kanan batas point cloud, kemudian click [Add points to surface]

    >> pada setting [Surface Option], pilih nama dan surface stylenya, misalnya [Contours 1m and 5m (Background)], kemudian click [Next]

    >> pada setting [Region Setting], pilih [Point cloud extent], kemudian click [Finish]

  1. Pada civil 3D [Toolspace], di tab [Propestocs] terdapat surface baru di bawah grup [Surface]

===selemat mencoba==

[XLS-SVY-26]: Menghitung Koreksi Koordinat karena Salah Input Koordinat Backsight dan Station dengan Microsoft Excel

Seperti diuraikan pada post sebelumnya bahwa disarankan untuk menyimpan data pengukuruan total sation dalam bentuk data ukuran yang minimal berisi informasi tentang:

  • Tempat berdiri alat dan backsight
  • Tinggi alat dan tinggi target
  • Nama detail
  • Ukuran jarak miring, bacaan sudut horisontal, bacaan sudut vertikal

Dengan informasi di atas, maka jika ada perubahan data atau editing data ukuran misal salah input tinggi alat, tinggi target atau salah input koordinat station dan backsigh dapat dilakukan tanpa harus melakukan pengukuran ulang lagi.

Posting sebelumnya telah diuraikan bagaimana cara mengkonversi data ukuran dari koordinat xyz ke dalam file field book di civil 3d, sedangkan kali ini diuraikan cara menghitung data hasil ukuran koordinat dari total station dengan menggunakan microsoft excel.

Data yang akan diolah adalah data yang saya terima dari surveyoer yaitu berupa data koordinat dalam bentuk XYZ atau lebih tepatnya berformai PENZD (PointNumber, East, North, Z dan Description). Setelah saya gambarkan dalam Civil 3D ternyata posisi koordinat tersebut tidak tepat. Berdasarkan informasi dari surveyornya, ternyata ada salah memasukkan koordinat titik berdiri alat dan koordinat backsight.

Koordinat XYZ yang benar ada dikolom masing FIX_E, FIX_N dan FIX_Z untuk masing-masing station dan backsightnya sehingga perlu dilakukan hitungan koreksi untuk titik SS (sight shot) mulai titik 3 sampai akhir.

Ururutan untuk menghitung koreksi secara garis besar adalah:

  1. Menghitung Jarak dan Sudut dari Station ke Detail (SS).

    Dengan menggunakan koordinat awal (koordinat belum dikoreksi):

  • Hitung Azimut arah station ke backsight (AZ_BS)
  • Hitung Azimuth arah Station ke Foresight / SS (AZ_FS)
  • Hitung sudut horisontal dengan rumus (HZ)
  • Hitung jarak datar dari station ke detail (HD)
  • Hitung beda tinggi dari station ke detail (DH)
  1. Menghitung Koordinat Detail berdasarkan Jarak dan Sudut dari titik berdiri alat (Station)
  • Dengan koordinat fix, Hitung Azimuth arah station ke backsight (AZ_FIX_BS)
  • Hitung Azimuth dari station ke Foresight (SS) (AZ_FIX_FS)
  • Hitung koordinat terkoreksi dengfan rumus:

    X_FIX=X_FIX_STA + HD*SIN(AZ_FIX_FS)

    Y_FIX=Y_FIX_STA + HD*COS(AZ_FIX_FS)

    Z_FIX=Z_FIX_STA+DH

Untuk mempermudah input data data , spreadsheet dibuat menggunakan object table. Detail mengenai keuntungan menggunakan object data silakah dibaca di https://www.excelefficiency.com/excel-table-benefits/

Kolom

Rumus

Keterangan

NO

=ROW()-ROW(tbl_before_adj[[#Headers],[NO]])

Memberi rumus nomer urut secara otomatis. JANGAN DIHAPUS KOLOM INI

PNT,E, N,Z,CODE

 

Input coordinat yang akan dikoreksi

REC

 

Masukkan kode ST : untuk station, BS : untuk backsight dan , SS : untuk sightshot

FIX_E, FIX_N, FIX_Z

 

Masukkan koordinat ST dan BS yang benar (FIX)

ST_E

ST_N

=INDEX([E],MATCH(“ST”,[REC],0))

=INDEX([N],MATCH(“ST”,[REC],0))

Menampilkan koordinat [E] berdasarkan pencarian kata “ST” dalam kolom REC.

Menampilkan koordinat [N] berdasarkan pencarian kata “ST” dalam kolom REC.

BS_E

BS_N

ST_Z

=INDEX([E],MATCH(“BS”,[REC],0))

=INDEX([N],MATCH(“BS”,[REC],0))

=INDEX([Z],MATCH(“ST”,[REC],0))

Menampilkan koordinat [E] berdasarkan pencarian kata “BS” dalam kolom REC.

Menampilkan koordinat [N] berdasarkan pencarian kata “BS” dalam kolom REC.

Menampilkan koordinat [Z] berdasarkan pencarian kata “ST” dalam kolom REC.

AZ_BS

 

=MOD(

DEGREES(

    ATAN2(

        [@[BS_N]]-[@[ST_N]],

        [@[BS_E]]-[@[ST_E]])

)

,360)

Menghitung azimuth dari station ke back sight

Fungsi MOD(….,360) akan otomatis menambahkan 360 derajat jika hasil ATAN2() bernilai negatif

Fungsi DEGRESS() merubah sudah RADIANS hasil fungsi ATAN2()

Fungsi ATAN2() menghitung sudut azimuth dalam unit RADIANS dengan input:

DY = (Y backsight – Y station)

DX= (X backsight – X station)

AZ_FS

 

=IF([@REC]=”SS”,

    MOD(DEGREES(

    ATAN2([@N]-[@[ST_N]],[@E]-[@[ST_E]])),

    360),

0)

Menghitung azimuth dari station ke FS (SS)

Dihitung jika kolom [REC]=”SS”

 

 

 

Jika kolom [REC] tidak sama dengan “SS” isi dengan 0 (nol)

HZ

 

=IF([@REC]=”SS”,

    MOD([@[AZ_FS]]-[@[AZ_BS]],360)

,0)

Menghitung sudut horisontal dihitung dari backsight

Dihitung jika kolom [REC]=”SS”

 

Jika kolom [REC] tidak berisi kode “SS”, isi dengan 0

HD

=IF([@REC]=”SS”,

    SQRT(([@E]-[@[ST_E]])^2+([@N]-[@[ST_N]])^2)

,0)

Menghitung jarak datar dari station ke ss, jika kolom [REC]=”SS”

DH

=IF([@REC]=”SS”,

    [@Z]-[@[ST_Z]],

0)

Menghitung beda tinggi dari station ke SS, jika kolom [REC]=”SS”

ST_FIX_E,ST_FIX_N

BS_FIX_E,BS_FIX_N

ST_FIX_Z

 

Menampilkan koordinat [FIX_E], [FIX_N] berdasarkan pencarian kata “ST” dalam kolom REC.

Menampilkan koordinat [FIX_E], [FIX_N] berdasarkan pencarian kata “BS” dalam kolom REC.

Menampilkan koordinat [FIX_Z] berdasarkan pencarian kata “ST” dalam kolom REC.

AZ_FIX_BS

=MOD(

    DEGREES(

    ATAN2(

    [@[BS_FIX_N]]-[@[ST_FIX_N]],

    [@[BS_FIX_E]]-[@[ST_FIX_E]])

    ),

360)

Menghitung azimuth dari station ke back sight dari koordinat fix

AZ_FIX_FS

=IF([@REC]=”SS”,

    MOD([@[AZ_FIX_BS]]+[@HZ],360)

,0)

Menghitung azimuth dari station ke SS/FS menggunakan sudut horisontal [HZ]

X_FIX

=IF([@REC]=”SS”,

    [@[ST_FIX_E]]+[@HD]*SIN(RADIANS([@[AZ_FIX_FS]])),

[@[FIX_E]])

Menghitung koordinat X terkoreksi, jika kolom [REC]=”SS”

 

Jika bukan “SS”, isi koordinat fix

Y_FIX

=IF([@REC]=”SS”,

    [@[ST_FIX_N]]+[@HD]*COS(RADIANS([@[AZ_FIX_FS]])),

[@[FIX_N]])

Menghitung koordinat Y terkoreksi, jika kolom [REC]=”SS”

 

Jika bukan “SS”, isi koordinat fix

Z_FIX

=IF([@REC]=”SS”,

    [@DH]+[@[ST_FIX_Z]],

[@[FIX_Z]])

Menghitung koordinat Z terkoreksi, jika kolom [REC]=”SS”

 

Jika bukan “SS”, isi koordinat fix

 

==semoga bermanfaat==

[XLS-SVY-25]: Menghitung Jarak, Sudut dan Beda Tinggi dari data Pengukuran Total Station Format X,Y,Z untuk diolah di Civil 3D (Bagian-02)

Referensi

:

Bagian-01

Perhitungan Poligon di Civil 3D

Platform

:

Excel & Civil 3D

Download

:

 

 

Hasil konversi pengukuran total station menjadi data ukur dalam format FBK pada posting sebelumnya adalah:

PRISM 0.000

STN 35 0.0000

BS 34 0.0000

AD VD 72 194.09204147 81.5184 0.0260 “TP.A2”

AD VD 3500004 220.48593049 18.7763 0.1510 “BG-0”

STN 72 0.0000

BS 35 0.0000

AD VD 7200001 346.34495583 13.3961 -0.6610 “BG-1”

AD VD 7200002 5.06596998 14.4380 -0.2620 “BG-2”

AD VD 7200003 13.07168004 15.2957 0.7410 “BG-3”

AD VD 73 167.11254012 202.2203 -0.0170 “TP.A3”

STN 73 0.0000

BS 72 0.0000

AD VD 7300001 46.17031719 11.6259 -0.4080 “REL-1”

AD VD 74 41.51344548 10.7289 -0.4130 “TP.A4”

 

Saat dilakukan survey, koordinat titik awal di station (STN) di titik 35 dan backsight (BS) awal di titik 34 adalah koordinat hasil pengukuran GPS geodetic dengan nilai sebagai berikut:

TITIK

X

Y

Z

PNT

TP.35A

410,328.331

9,540,256.075

44.608

35

TP.34A

410,098.009

9,540,447.075

45.903

34

 

Sedangkan untuk koordinat ikat terakhir hasil pengukuran total station yaitu titik 73 dan 74, selanjutnya dicek lagi dengan hasil pengukuran GPS geodetic sehingga akan didapat pengukuran polygon terbuka terikat di awal dan di akhir.

Berikut tahapan pengolahan data fbk tersebut di Civil 3D:

  1. Setting Survey Database

  2. Menentukan Folder / Lokasi Penyimpanan Survey Database

Pilih toolspace [Survey], kemudian click kanan [Survey Databases] lalu pilih [Set working folder..]

Pilih atau buat folder baru untuk menyimpan survey database, misal di folder : C:\Civil 3D Projects\dbsurvey_project1.

Clik kanan [Survey Databases] lalu pilih [New local survey database..] kemudian isikan nama local survey database, misalnya dbsvy_01

Tampilan toolspace [Survey] menjadi:

Sedangkan susunan folder survey database menjadi:

  1. Setting Survey Database

Pada toolspace [Survey], click kanan local survey database (dbsvy_01) kemudian pilih [Edit survey database setting..]. Set survey database seperti gambar di bawah:

  1. Menjalankan File FBK

  2. Membuat Network

Pada toolspace [Survey] di bawah folder local database dbsvy_01, click kanan icon [Network] kemudian pilih [New]

Masukkan nama network misalnya net1, sehingga tampilan folder local survey database menjadi:

  1. Memasukkan Control Points.

Masih di toolspace survey, di bawah network net1, click kanan [Control Points] kemudian pilih [New]

Masukkan nilai koordinat fix (control point) titik 35 dan 34

Cek nilai koordinat control points, dengan click kanan [Control Points] kemudian pilih [Edit]

  1. Memasukkan Hasil Ukuran (file fbk) ke Network

Click kanan [net1], pilih [Import] kemudian [Import fieldbook]..

Pilih file fieldbook dari latihan sebelumnya kemudian lakukan setting sebagai berikut:

Clik [OK] untuk memulai import fbk.

Koordinat hasil import fbk:

  1. Insert Titik dari Survey Database ke Gambar.

Masih di local survey database dbsvy_01, click kanan [Survey Points] kemudian [Points]>>[Insert into drawing]

Pilih point style yang diinginkan untuk menampilkan titik

  1. Perhitungan Poligon

  2. Input Tambahan Control Points

Misal hasil pengukuran GPS geodetic di titik ikat 73 dan 74 adalah sebagai berikut:

Lakukan input control points seperti langkah B-2

  1. Membuat Object Traverse Baru

Dalam folder [ net1], Click kanan [Traverses] kemudian pilih [New]

Masukkan dengan setting sebagai berikut:

Property

Value

Keterangan

Name

Pol-01

Isikan nama jalur polygon jalur pengukuran

Initial Station

35

Titik berdiri alat pertama kali di jalur titik polygon

Initial Backsight

34

Backsight pertama kali di jalur titik polygon

Stations

72,73

Titik tempat berdiri alat berikutnya

Final Foresight

74

Bidikan terakhir di titik tetap

  1. Perhitungan Poligon

Dalam folder [ net1], Click [Traverses] kemudian click kanan object polygon yang telah dibuat sebelumnya (pol-01), lalu pilih [Traverse Analysis]

Lakukan setting perhitungan polygon sebagai berikut:

Property

Value

Keterangan

Do Traverse analysisi

Yes

Analisa ketelitian jarak dan sudut

Do angle balance

Yes

Perataan sudut

Horisontal adjustment method

Transit rule

Koordinat 2D (X,Y) dikoreksi dengan metode “Transit”. Untuk meotode lainnya silakan dibaca di https://knowledge.autodesk.com

Vertical adjustment method

Length weighted distribution

Elevasi dikoreksi dengan metode distribusi perbandingan jarak

Horizontal closure limit 1:X

15000

Tolerasi kesalahan penutup horizontal

Vertical closure liit 1:X

15000

Toleransi kesalahan penutup vertical (elevasi)

Angular error per set

0.0005

Toleransi kesalan sudut / set

Update survey database

Yes

Update survey database setelah proses adjustment / koreksi

Click [OK]

Hasil perhitungan polygon akan ditampilkan empat (4) text file dengan penamaan file diawali dengan nama polygon/traverse yaitu:

  1. pol-01 Raw Closure.trv à kesalahan penutup sebelum dikoreksi
  2. pol-01 Balanced Angles.trv à pengukuran sudut terkoreksi
  3. pol-01 Vertical Adjustment.trvà elevasi terkoreksi
  4. pol-01.lsoà koordinat telah terkoreksi

Kesimpulan dari Bagian-01 dan Bagian-02 bahwa dengan melakukan perhitungan jarak dan sudut dari data koordinat (X, Y, Z) di Excel, maka Analisa polygon dan perhitungan detail/side shot bisa dilakukan di civil 3D dengan syarat, diketahui informasi titik berdiri alat (station) dan setting backsight.

[XLS-SVY-25]: Menghitung Jarak, Sudut dan Beda Tinggi dari data Pengukuran Total Station Format X,Y, Z untuk diolah di Civil 3D (Bagian-01)

Saya pernah menerima data survey dari surveyor hasil download total station dalam format nama Titik, X, Y, Z (PXYZ) tanpa menyertakan data mentah atau raw data yang berisi informasi tempat berdiri alat, backsight, jarak miring, sudut vertikal, sudut horisontal, tinggi alat dan tinggi target. . Contoh data yang aku terima:

Format hasil download berupa titik, X, Y dan Z mempunyai kelemahan antara lain:

  1. Jika ada koreksi data ukuran seperti data jarak, sudut, tinggi alat atau tinggi target maka otomatis koordinat tidak bisa dihitung ulang.
  2. Tidak bisa dilakukan pengolahan data poligon atau network adjustment.

Agar data bisa diolah lebih lanjut di civil, maka data di atas perlu dimodifikasi dan diberi data tambahan sebagai berikut:

  1. Rubah nama titik menjadi angka bulat karena civil 3d tidak bisa menerima nama titik dalam selain angka bulat. Nama titik menjadi dijadikan point description dalam civil 3d. Format titik menjadi PENZD atau (PointNumber, East, North, Elevation dan Description)
  2. Menambahkan koordinat tempat berdiri alat dan koordinat backsight yang nantinya akan digunakan untuk menghitung sudut horisontal
  3. Setelah data yang terdiri sudut horisontal, jarak dan beda tinggi sudah dihitung, maka dilakukan konversi ke format autodesk fieldbook (fbk) untuk selanjutnya diproses dengan civil 3d dengan menggunakan tool civil 3d survey.

Format data setelah diedit menjadi:

 

Kolom

Keterangan

A

Infomasi keterangan job atau job-id

B

Informasi urutan tempat berdiri alat, atau setting alat

C

Kode titik: ST: Stasiun atau tempat berdiri alat BS: Backsight bidikan belakang atau orientasi SS: Sightshot bidikan detail

D,E,F,G

Berturut-turut Nama titik, X, Y , Z

H

Point Number dalam format angka bulat. Titik detail (SS) dengan format angka misal 7200001 artinya titik nomer 1 saat alat berdiri di station 72. Dengan cara pengkodean SS seperti di atas, maka tiap titik detail bisa dilacak tempat berdiri alatnya

 

Tahapan berikutnya adalah untuk menghitung kembali jarak dan sudut horisontal dari data koordinat:

 

Cell

Rumus

Keterangan Rumus

[I4]

=IF(LEN(D4)=0,”-“,LOOKUP(9^9,$B$4:$B4,E$4:E4))

Jika cell [D4] kosong atau panjang charactes (Len(D4)=0), maka isikan dengan tanda “-” Jika [D4] ada isinya, maka cari angka di kolom [B] atau kode angka tempat berdiri alat, kemudian jika ketemu diambail nilai koordinat X nya dari kolom [E]

[J4] [K4]

=IF(LEN($D4)=0,”-“,LOOKUP(9^9,$B$4:$B4,F$4:F4)) =IF(LEN($D4)=0,”-“,LOOKUP(9^9,$B$4:$B4,G$4:G4))

Sama dengan keterangan di atas tapi untuk mengambil nilai Y dari kolom F dan Z dari kolom G

[L4]

=IF(OR(C4=”ST”,LEN($D4)=0),”-“, IF($C4=”BS”, MOD(DEGREES(ATAN2(($F4-$F3),($E4-$E3))),360) ,L3))

Jika C4 sebagi tempat berdiri alat (ST) atau D4 kosong, maka tidak dihitung azimuth atau (“-“) Jika C4 sebagai titik bidikan belakang atau backsight (BS) Maka dihitung azimuth dari ST ke BS Jika kode SS yaitu kode selain ST dan BS, maka azimuth mengikuti hitungan baris sebelumnya

[M4]

=IFERROR(IF(C4=”SS”, MOD(DEGREES(ATAN2(F4-J4,E4-I4)),360) ,”-“),”-“)

Jika C4 sebagai titik detail (SS) Hitung azimuth dari ST ke SS Jika bukan kode SS, isikan dengan “-“

[N4]

=IFERROR(IF(C4=”SS”,MOD(M4-L4,360),”-“),”-“)

Jika C4 sebagai titik detail, hitung sudut horisontal dengan mengurangkan sudut azimut detail ke azimut statiion (M4-L4)

[O4]

=IFERROR(IF(C4<>”ST”,SQRT((E4-I4)^2+(F4-J4)^2),”-“),”-“)

Hitung jarak horisontal dari ST ke detail (SS)

[P4]

=IFERROR(G4-K4,”-“)

Menghitung beda tinggi ST ke SS

[Q4]

=””””&D4&””””

Merubah format nama titik menjadi diapit tanda ” agar bisa diproses di FBK

[R4]

=LOOKUP(“ZZZzzz”,A$4:A4)

Copy kode JOB

[I4:R4]

 

Copy rumus di range tersebut ke bawah sampai akhir data

Pada tahap ini jarak datar, sudut horisontal dan beda tinggi sudah didapatkan. Tahap berikutnya adalah merubah data di atas menjadi FBK

 

Cell

Rumus

Keterangan Rumus

[T4]

=INDEX($D$22:$D$24,MATCH($C4,$C$22:$C$24,0))

Merubah kode di kolom C menjadi kode di FBK

[U4] [V4] [W4] [U4:W4]

=IF($C4<>”SS”,”-“,TRUNC($N4)) =IF($C4<>”SS”,”-“,TRUNC(($N4-TRUNC($N4))*60)) =IF($C4<>”SS”,”-“,3600*($N4-TRUNC($N4))-60*TRUNC(($N4-TRUNC($N4))*60))

Mengambil nilai derajat dari hitungan sudut dari [N4] Mengambil nilai menit dari hitungan sudut dari [N4] Mengambil nilai detik dari hitungan sudut dari [N4] hasil konversi sudut dari desimal menjadi derajat-menit-detik

[X4] [X4]

=IF(C4<>”SS”,”-“, U4&”.”& TEXT(V4,”00″)& TEXT(INT(W4),”00″)& INT(MOD(W4,1)*10000))

Jika C4 tidak bidikan detail (SS), maka isikan dengan “-“, tetapi jika SS, maka Ambil nilai derajatnya diikuti dengan simbol “.” Ambil nilai menit dengan format dua digit “00” Ambil angka bulat dari nilai detik format dua digit “00” Ambil nilai desimal dari nilai detik jika ada. Hasil konversi suder derajat-menit-detik menjadi format d.mmss00

[Y4]

=REPT(TEXT(O4,”0.0000″),$C4=”SS”)

Rubah nilai jarak menjadi text format 4 digit desimal

[Z4]

=REPT(TEXT(P4,”0.0000″),$C4=”SS”)

Rubah nilai beda tinggi menjadi text format 4 digit desimal

[AA4]

=T4&” “&H4&REPT(” 0.0000 “,C4<>”SS”) & REPT(” “&X4&” “&Y4&” “&Z4&” “&Q4,C4=”SS”)

Rubah data menjadi format FBK

[AA3]

PRISM 0.000

Set tinggi target menjadi 0.000 meter

[T4:AA4]

 

Copy rumus di range tersebut dampai akhir data

Pada tahap ini, file ukuran sudah dirubah menjadi format FBK dan siap untuk di proses di civil 3d. Pilih range [AA3:AA17], kemudian paste di notepad lalu simpan dengan ekstensi *.fbk

 

((Lanjut ke Bagian-2))

[CAD-MAP-20]:Autolisp untuk Menampilkan Tabel Peta Bidang di Autocadmap dan Microsoft Excel

Reference : [CAD-MAP-19]: Membuat tabel Microsoft Access dari Hasil Topology Analysis di AutoCAD Map (Lanjutan-2: Menjalankan Autolisp)
Platform : AutocadMap
Download : download
     

Pada tulisan [CAD-MAP-19]: Membuat tabel Microsoft Access dari Hasil Topology Analysis di AutoCAD Map (Lanjutan-2: Menjalankan Autolisp) tabel hasil topology analisis disimpan dalam bentuk database microsoft access. Pada tulisan kali ini akan diuraikan bagaimana cara melakukan topology analisis dengan menggunakan autolisp dan menampilkan hasilnya dalam bentuk block attribute dan spreadsheet excel.

Fungsi isi block attribute diambil dari fungsi yang dibuat oleh Lee Mac sedangkan untuk proses penulisan ke Microsoft Excel diambil dari http://cadtips.cadalyst.com/export/export-text-a-drawing-excel-spreadsheet.

Contoh aplikasi penggunaan fungsi overlay ini adalah membuat table peta bidang pada pekerjaan PTSL yaitu membuat tabel yang berisi informasi kolom / field nomer urut bidang, nomer identifikasi bidang dan luas tanah.

Secara garis besar, tahapan untuk melakukan proses pembuatan tabel bidang tanah:

  1. Persiapan dan Pengecekan Standard Layer.
  2. Melakukan Proses Overlay Topology.
  3. Membuat Tabel Daftar Tanah

1. Persiapan dan Pengecekan Standard Layer.

1.a. Copy file program autolisp dan block ke folder Support File Search Path.

Copy files dibawah ini ke salah satu folder Support File Search Path:

image

Sedangkan cara menegetahui file support path, ketik pada command: options

kemudian pilih File>>Support File Search Patah :

image

1.b. Load/Panggil program autolisp.

  • Ketik pada command:appload
  • pilih file: tab_nib.lsp
  • click Load

Agar program selalu dipanggil atau di-uploaded saat program autocadmap dijalankan, tambahakan program tab_nib.lsp di [Startup Suite]

1.c. Cek insertion point text dan standard layer

Syarat proses overlay untuk pembuatan table adalah:

No Keterangan Tindakan
1. Topology bidang sudah terbuilt sempurna atau topology dengan centroid NIB sudah bebas dari kesalahan Lakukan re-build topology sebelum proses dilakukan
2. Harus ada hanya satu NUB (Nomor Urut Bidang) dan Satu Nama di dalam gambar bidang tanah Geser insertion point text NUB dan Nama ke dalam bidang tanah, jika ditemukan insertion point NUB dan Nama ada di luar bidang tanah
3 Nama harus tertulis dalam satu baris Jika nama tertulis dalam lebih dari satu baris, maka penulisan nama tersebut harus digabung menjadi satu.
4 Layer untuk text nub: tn_nub
Layer untuk text nama: tn_nama
untuk merubah layer ke tn_nub, ketik di command:z_lu
untuk merubah ke layer tn_nama, ketik di command:z_ln

2. Melakukan Proses Overlay Topology.

Proses overlay dijalankan setelah syarat-syarat pada langkah 1.c sudah terpenuhi.

Ketik pada command:z_xdata

sehingga keluar notifikasi:

Overlay successfully created.

Hasil proses overlay ini akan terbentuk object titik pada layer [nub_nib] di tiap-tiap insertion point text NUB dan text Nama. Jika object titik tidak terlihat, ketik di command:pdmode, kemudian masukkan value=3.

Command: pdmode
Enter new value for PDMODE <3>:

image

Titik merah adalah, titik hasil topology overlay yang berisi informasi NIB, NUB, Nama dan Luas.

Untuk melihat data hasil overlay, ketik di command:adeeditdata

kemudian pilih salah satu titik di dekat nub, misal nub 18, maka akan ditampilkan informasi sebagai berikut:

image

Pada tabel di atas sudah terbentuk tabel korelasi atau link antara NIB dan NUB.

kemudian pilih juga titik di dekat text nama, maka akan ditampilkan informasi sebagai berikut:

image

Pada tabel di atas sudah terbentuk korelasi atau link antara NIB dan Nama Pemilik.

Gabungan antara NIB,NUB dan luas akan dibuat pada tahap berikutnya.

3. Membuat Tabel Daftar Tanah.

Ketik di command:Z_TABELPBT

Kemudian pilih lokasi tabel di autocadmap.

Hasil tabel di autocad dan excel:

image

==selamat mencoba===

[GEN-PMG-07]: Menghitung upah lembur dengan excel (Kep Men. NOMOR KEP. 102/MEN/VI/2004) Bagian 3

Referensi : [GEN-PMG-04]:Menghitung Upah Lembur dengan Excel (Kep Men. NOMOR KEP. 102/MEN/VI/2004)
[XLS-PMG-07]: Menghitung upah lembur dengan excel (Kep Men. NOMOR KEP. 102/MEN/VI/2004) Bagian 2
Platform : Microsoft Excel
Download : Download

 

Pada tulisan sebelumnya telah diuraikan tahapan untuk menghitung lembur dengan kondisi 5 (lima) hari kerja/minggu dan 6 (enam) hari kerja/minggu dengan input jam kerja yang sudah diketahui. Pada tulisan ini akan saya sharing template yang pernah saya buat untuk aplikasi di suatu proyek dengan kondisi yang lebih komplek yaitu:

  • 1. Data yang dimasukkan adalah [Jam Masuk] dan [Jam Keluar].
  • 2. Jam Istirahat bisa diatur fleksibel atau bisa dirubah.
  • 3. Perhitungan lima(5) hari kerja, jika karyawan bekerja di kantor pusat dan menggunakan perhitungan 6 (enam) hari kerja jika bekerja di lapangan
  • 4. Slip gaji sudah dilengkapi dengan perhitungan BPJS dan Pajak Penghasilan (PPH21).

Berikut tutorial cara penggunaan spreadsheet excel yang sudah di download:

1. Memasukkan Setting Hari Libur di Sheet [hr_libur]

Masukkan tanggal hari libur di kolom [tgl] atau kolom [A] dengan format tanggal yang sesuai dengan settingan di windows. Contoh setting tanggal di komputer saya yang terlihat di task bar windows adalah image, maka untuk tanggal 30 Mei 2018 dimasukkan dengan format 5/30/2018 atau 5/30/18.

Setelah memasukkan tanggal sesuai dengan format yang benar, masukkan juga keterangan hari libur di kolom [Keterangan]. Bagian kolom [No] tidak perlu diisi karena kolom ini akan terisi otomatis tiap ada penambahan record atau dta baru.

2. Memasukkan Setting Jam istirahat di sheet [jm_istrht]

image

Kolom [No], [Hari] dan [Kode Hari] berisi formula. Input jam kerja dimasukkan di kolom:

[Rest01 Start], [Rest01 End] : untuk jam istirahat pertama

[Rest02 Start1], [Rest02 End] : untuk jam istirahat kedua

[Rest03 Start], [Rest03 End] : untuk jam istirahat ketiga

Jam dimasukkan dengan format hh:mm, dalam format ini jika akan memasukkan jam 1 siang atau 1:00 PM maka jam dimasukkan dengan format 13:00 demikain juga untuk jam 11:00 pm dimasukkan dengan format 23:00.

Format hh:mm ini juga berlaku saat pengisian jam masuk kerja dan jam keluar kerja.

3. Memasukkan Isian di sheet [ts] dan [slip]

Jika diinginkan format tanpa detail perhitungan gaji, dapat mengunakan format di sheet [ts] sedangkan jika diinginkan input jam kerja disertai dengan detail perhitungan gaji dapat menggunakan format di sheet [slip].

Pada prakteknya di lapangan, saya menggunakan 2 (dua) sheet tersebut untuk aplikasi yang berbeda. Sheet [ts] diberikan ke karyawan digunakan untuk mendapatkan approval dari atasan, sedangkan sheet [slip] digunakan untuk menghitung gaji atau sebagai slip gaji.

Masing-masing sheet [ts] dan [slip] terdapat kolom [Q]:[AE] yang berisi rumus bantu untuk perhitungan timesheet, Anda bisa hide kolom ini saat pencetakan atau distribusi form ke karyawan. Jangan dihapus atau dimodifikasi rumus ini, kecuali Anda benar-benar memahami isi dan maksud dari rumus tersebut.

Format sheet [ts]:

image

Isikan ID, Nama dan Posisi atau bila diperlukan bisa ditambahkan nama supervisi, department atau isian lain pada area K2:O3

Pilih Bulan dan Tahun di cell [D2] dan [E2], maka kolom [Tgl] dan [Hari] akan terisi otomatis, kolom berwarna hijau berisi formula, sehingga tidak perlu diisi kolom hijau akan otomatis terupdate jika ada perubahan tanggal termasuk perubahan warna huruf jika tanggal bertepatan dengan hari libur, jumat, sabtu dan minggu.

Kolom yang perlu diisi manual adalah:

Nama Kolom Keterangan
Kode Hari Kerja Masukkan angka 5 jika bekerja untuk 5 hari kerja/minggu dan masukkan angka 6 jika untuk bekerja 6 hari kerja / minggu
Masuk , Keluar Isikan jam masuk, jam keluar sesuai dengan format tanggal di windows
Kolom [Durasi] akan terisi otomatis jika jam masuk dan jam keluar sudah terisi. Format durasi adalah dalam satuan desima sehingga jika durasinya adalah 8 jam 30 menit, maka masukkan angka 8.5
Koreksi Isikan koreksi jam kerja jika diperlukan dalam satuan desimal. Misalnya dalam ada koreksi penambahan jam setengan jam atau 00:30 maka dimasukkan 0.5 atau sebaliknya ada pengurang jam 00:30, maka dimasukkan -0.5

Sedangkan tampilan untuk sheet [slip]:

image

Ada tambahan input yaitu:

Nama Input Keterangan
Gaji Dasar Masukkan gaji pokok atau gaji dasar /bulan
Tunjangan Tetap Masukkan Tunjangan tetap /bulan
Rate Lembur Masukkan unit rate lembur / jam.
“maaf tulisan di template masih /bulan, mohon untuk dikoreksi /jam”
Tunj. Makan Masukkan tunjangan makan /hadir
Tunj. Transport Masukkan tunjangan transport /hadir
Tunj. Kehadiran Masukkan tunjangan kehadiran /hadir
PTKP Karyawan Masukkan golongan PTKP (Penghasilan Tidak Kena Pajak) karyawan sesuai dengan peraturan yang berlaku.

Detail perhitungan gaji disertai dengan perhitungan BPJS dan PPH21 adalah:

image

Silakan dicoba dan mohon koreksinya jika ada kesalahan.

Terimakasih telah berkunjung di blog saya.