[XLS-MAP-05]: Plotting Koordinat Excel ke Civil 3D

Referensi	:	[XLS-MAP-03]: Plotting List Koordinat dari Excel ke AutoCAD
Platform	:	Excel dan Civil3D 2011
Lokasi File	:

Pada post [XLS-MAP-03]: Plotting List Koordinat dari Excel ke AutoCAD telah dijelaskan cara plotting koordinat ke Autocad dengan program visual basic application for excel. Tulisan kali ini akan diuraikan cara plotting koordinat ke AutocadMap atau Civil 3D menggunakan fasilitas ‘Connect a Feature Source’ yang ada di civil 3D atau AutocadMap, tanpa menggunkan visual basic maupun autolisp

Pada tulisan ini, saya menggunakan civil 3D 2011.

Tahapan plotting adalah:

1	Membuat [named range] pada list koordinat di file excel yang akan di plot 1.1. Block tabel berisi koordinat yang akan diplot. 1.2. Dari menu [Insert], pilih [Define] 1.3. Masukkan Nama range, misal ‘KoordGeo’ 1.4. Simpan file Excel, kemudian tutup file excel.
2	Setting ODBC agar connect ke file excel di atas. Seperti yang telah diuraikan di [GEN-PMG-05]: Pivot Table Excel data dari Query Database MySQL Ikuti langkah sesuai nomor di gambar berikut :  Pada nomor 5 (lima), click tombol [Select Workbook], kemudian pilih file excel yang telah didefinisikan ‘named range’ seperti di langkah 1 Pada langkah ini User data Source telah didefinisikan..
3	Melakukan koneksi AutocadMAP/Civil 3D ke Excel data Source 3.1. Aktifkan civil 3D atau Autocad map 3.2. Jika Command Prompt belum aktif, tekan tombol CTRL+9 untuk mengaktifkannya 3.3. Pada command prompt ketik Command: mapconnect 3.4. Setelah diclick tombol [Select] di langkah 5(lima), click tombol [Connect] 3.5. Tidak perlu diisi user name dan password, langsung clik [Login]
4	Melakukan setting system koordinat:  diplih LatLongs, karena koordinat yang dipilih adalah koordinat lintang bujur, jika koordinat yang akan diplot adalah projected coordinat seperti UTM maka harus diseesuaikan datum dan letak zonenya..
5.	Setting X dan Y dari field Database Excel 5.1. Click ruang kosong di bawah field X, kemudian pilih [Bujur] 5.2. Ulang langkah 5.1 untuk field di bawah Y, kemudian pilih [Lintang] 5.3 Click tombol [Add to Map]
6.	Titik-titik koordinat sudah tergambar di civil 3D

===selamat mencoba===

[GEN-PMG-05]: Pivot Table Excel data dari Query Database MySQL

 

Referensi	:
Platform	:	Excel ODBC Connector for MySQL (download)
Lokasi File	:

Tahapan / Langkah-langkahnya adalah:

1.	Download file ODBC Connector for MySQL. Install file yang sudah didownload
2.	Setting ODBC lewat windows control panel Pada windows XP, dari menu control panel, pilih administrative tool kemudian pilih [Data Sources (ODBC)] Pada Windows 7, ketik ODBC pada menu isian [Search], kemudian pilih [Data Sources (ODBC)]  Karena program yang aku miliki adalah Windows 7 dan Excel 2003, maka tahapan dan langkah-langkah berikutnya adalah dengan asumsi menggunakan excel 2003 dan windows 7:
3.	Pada create data source, pilih [MySQL ODBC 5.1 Driver]
4	Setting koneksi ke MySQl: Isikan nama data source (Nomer 4). Nama Server, User Name dan Password di (Nomer 5, 6 dan 7) Pilih Database (Nomer 8) Click tombol [Test], jika berhasil, maka ada pesan “Connection successful”
5	ODBC Data source sudah ditambahkan :
6	Melakukan Query dengan microsoft query: Pada Excel 2003, dari menu [Data]>>[Import External Data]>>[Import Data] Pada pilihan [Select Data Source], click tombol [New Data Datasource]  Ikuti langkah sesuai nomor di atas.
7	Memilih database dan tabel yang akan diquery Pilih database yang akan dipilih di tahap nomer 5. Masukkan nama koneksi dan keterangan koneksi di nomer 7 dan nomor 8.
8	Setelah di click tombol Finish dan meyimpan hasil koneksi, maka akan tampil pilihan table yang akan di query. Pilih table yang diinginkan, kemudian click OK. Tabel yang saya pilih untuk ditampilkan di pivot table table adalah tabel timesheet yang mencatat jumlah jam kerja tiap karyawan perhari.
9	Saya hanya ingin menampilkan kolom [dated], [positionCode], [REG] dan [OT], maka kolom yang lain diblock dengan cara memilih (menge-click di kolom) yang akan dihilangkan, kemudian tekan tombol [delete] di keyboard  Saya akan mengambil data dari tanggal 21 Desember 2010 sampai tanggal 20 Januari 2011 untuk position code yang diawali dengan kode ‘CS’ Setting di Microsoft Query adalah Dari menu [View], pilih [Criteria] Lakukan setting filter criteria :
10	Jika sudah selesai menghilangkan field atau kolm yang tidak dipakai, masih di Microsoft Query, dari menu [File] pilih [Return Data to Microsoft Office Excel]
11	Tabel hasil query:
12	Menghitung TimeSheet dan upah lembur dari file timesheet yang diambil dari database mysql. rumus perhitungan upah lembur mengacu ke post [GEN-PMG-04]:Menghitung Upah Lembur dengan Excel (Kep Men. NOMOR KEP. 102/MEN/VI/2004) Jam Kerja adalah penjumlahan dari REG+OT
13	Masukkan rumus di [E2]=SUMPRODUCT((LiburNasional=A2)*1)+(WEEKDAY(A2,2)>5) untuk menandai hari kerja dan hari libur. [E2]=Hari Kerja, [E2]>0 hari Libur.
14	Masukkan rumus di [F2]=C2+D2 untuk menghitung jumlah jam kerja
15	Masukkan rumus di [G2] =N(IF(E2=0,IF(F2>8,1.5+(F2-9)*2),IF(F2>0,MIN(F2,8)*2+IF(F2>=9,3)+IF(F2>=10,(F2-9)*4))))
16	Copy [E2:G2] ke bawah.
17	Hasil tabel menjadi
18	Merangkum hasil hitungan ke pivot tabel: Block Data semua data di atas. Data masih ter-block, pada menu excel, pilih [Data]>>[Pivot Table and Pivot Chart Report] Pada Step wizard 1 dan 2 click tombol [Next] Pada Step 3 of 3, click [Lay Out] Drag Nama Field ke lokasi yang ditunjuk garis merah: Double click field [Dated], setting format [Number] menjadi ‘Date’ Double click field [Dated], kemudian click tombol [Advance], pilih sort ascending  Jika Selesai Click tombok [OK]
19	Hasil report pivot table:
20	Dari hasil table pivot table terlihat jumlah faktor pengali lembur perhari dan totalnya untuk masing position code (personel), jika ada data tambahan tentang unit price lembur/jam, maka upah lembur dapat dihitung.

==selamat mencoba===

[GEN-PMG-04]:Menghitung Upah Lembur dengan Excel (Kep Men. NOMOR KEP. 102/MEN/VI/2004)

Referensi	:	Keputusan Menteri Tenaga Kerja & Transmigrasi NOMOR KEP. 102/MEN/VI/2004: WAKTU KERJA LEMBUR DAN UPAH KERJA LEMBUR
Platform	:	Excel
Lokasi File	:	ada di sini

Mengacu ke pasal 11:

Cara perhitungan upah kerja lembur:

Cara perhitungan upah kerja lembur sebagai berikut :

a. Apabila kerja lembur dilakukan pada hari kerja :
a.1. untuk jam kerja lembur pertama harus dibayar upah sebesar 1,5 (satu setengah) kali upah sejam;
a.2. untuk setiap jam kerja lembur berikutnya harus dibayar upah sebesar 2(dua) kali upah sejam.

b. Apabila kerja lembur dilakukan pada hari istirahat mingguan dan/atau hari libur resmi untuk waktu kerja 6 (enam)
hari kerja 40 (empat puluh) jam seminggu maka :
b.1. perhitungan upah kerja lembur untuk 7 (tujuh) jam pertama dibayar 2 (dua) kali upah sejam, dan jam
kedelapan dibayar 3 (tiga) kali upah sejam dan jam lembur kesembilan dan kesepuluh dibayar 4 (empat) kali upah
sejam.
b.2. apabila hari libur resmi jatuh pada hari kerja terpendek perhitungan upah lembur 5 (lima) jam pertama dibayar
2 (dua) kali upah sejam, jam keenam 3(tiga) kali upah sejam dan jam lembur ketujuh dan kedelapan 4 (empat)
kali upah sejam.

c. Apabila kerja lembur dilakukan pada hari istirahat mingguan dan/atau hari libur resmi untuk waktu kerja 5 (lima)
hari kerja dan 40 (empat puluh) jam seminggu, maka perhitungan upah kerja lembur untuk 8 (delapan) jam
pertama dibayar 2 (dua) kali upah sejam, jam kesembilan dibayar 3(tiga) kali upah sejam dan jam kesepuluh dan
kesebelas 4 (empat) kali upah sejam.

Pada contoh hitungan kali ini akan menggunakan asumsi 5 (lima) hari kerja dan 40 jam seminggu. Sehingga dari pasal tersebut yang digunakan adalah ayat a.2 dan c.

Persiapan dan tahapan perhitungan:

1.	Buat list hari libur Nasional di sheet tersendiri. Contoh List hari libur Nasional Tahun 2011 (pada contoh, cuti bersama tidak saya masukkan) Block (select/pilih) range A1:A15, kemudian dari menu [insert], pilih [Define]. setelah diklik [OK], maka ‘Defined Name Range’ dengan nama [LiburNasional] sudah terdefinisi di workbook aktif
2.	Contoh spreadsheet dengan jumlah jam kerja yang akan dihiutung jam kerjanya: Agar di kolom [A], ditampilkan nama hari dalam bahasa indonesia, ganti format number di kolom [A] menjadi: di cell [C3], masukkan rumus =SUMPRODUCT((LiburNasional=A3)*1)+(WEEKDAY(A3,2)>5) a. Keterangan dari rumus SUMPRODUCT((LiburNasional=A3)*1) adalah, apabila tanggal di cell [A3] adalah libur nasional, maka akan menghasilkan nilai 1 (satu). b. Sedangkan WEEKDAY(A3,2)>5 akan TRUE atau bernilai 1, jika tanggal di [A3] adalah hari Sabtu atau Minggu. Jika kondisi a dan b dijumlahkan , maka untuk hari libur akan bernilai > 0, sedangkan untuk bukan hari libur bernilai = 0. Copy rumus di [A3] ke bawah
3.	Kolom [D2:G2] adalah angka faktor pengali lembur yang telah ditentukan sesuai dengan Pasal 11 ayat a.2 dan c. Sedangkan kolom [H] adalah hasil dari penjumlahan faktor pengali lembur dari kolom [D2:G2]
4.	Menghitung faktor pengali lembur di hari kerja biasa (bukan hari libur) Asumsi 5 (lima) hari kerja dan 40 jam seminggu, maka jam kerja tiap hari yang tidak dihitung lembur adalah 8 jam/hari Misal: [B3]= jumlah jam kerja/hari [C3]=0 untuk hari kerja, dan [C3]>0 untuk hari libur. [D2],[E2],[F2] dan [G2]=faktor pengali 1.5, 2.0, 3.0 dan 4.0Maka Pasal 11 ayat a jika ditulis dalam rumus Excel: a. Apabila kerja lembur dilakukan pada hari kerja =IF(C3=0,ke a.1,ke langkah 5) a.1. untuk jam kerja lembur pertama harus dibayar upah sebesar 1,5 (satu setengah) kali upah sejam; IF(B3>8,1)*D$2 Rumus di gabung =IF(C3=0,IF(B3>8,1))*D$2 a.2. untuk setiap jam kerja lembur berikutnya harus dibayar upah sebesar 2(dua) kali upah sejam =IF(C3=0,IF(B3>9,B3-9))*E$2
5.	Pasal 11 ayat c: c.1. perhitungan upah kerja lembur untuk 8 (delapan) jam pertama dibayar 2 (dua) kali upah sejam =MIN(B3,8)*E$2 rumus MIN(B3,8) untuk antisipasi, jika seandainya ada yang masuk di hari libur tetapi kurang dari 8 jam c.2. jam kesembilan dibayar 3(tiga) kali upah sejam =IF(B3>=9,1)*F$2 c.3. jam kesepuluh dan kesebelas 4 (empat) kali upah sejam =IF(B3>=10,B3-9)*G$2
6.	Rumus di [D3], [E3], [F3] dan [G3]
	[D3]=IF(C3=0,IF(B3>8,1))*D$2 [E3]=IF(C3=0,IF(B3>9,B3-9),MIN(B3,8))*E$2  ‘gabungan fungsi a.2 dan c.1 [F3]=IF(C3>0,IF(B3>=9,1))*F$2 [G3]=IF(C3>0,IF(B3>=10,B3-9))*G$2
7.	[H3]=SUM(D3:G3)
8.	Jika upah perjam adalah 15000, maka [I3]=15000. Rumus di [J3]=H3*I3
9.	Copy rumus di [D3:J3] ke bawah untuk menghitung upah lembur di hari yang lain…
10.	Pengecekan rumus perhitungan Masukkan rumus di [K3] =B3=(SUMPRODUCT(D3:G3,1/$D$2:$G$2)+IF(C3=0,MIN(B3,8))) Kemudian copy ke bawah. Jika nilai-nilai dikolom [K], bernilai TRUE, maka perhitungan faktor pengali lembur sudah benar.
11.	Jika ingin disederhanakan, rumus di [H3] bisa dirubah menjadi (revisi.01 14 Februari 2011) [H3]=N(IF(C3=0,IF(B3>8,1.5+(B3-9)*2),IF(B3>0,MIN(B3,8)*2+IF(B3>=9,3)+IF(B3>=10,(B3-9)*4))))

==silahkan mencoba==
Untuk 6 (enam) hari kerja silahkan click di sini

[XLS-PMG-02]: Rumus Terbilang di Excel (Tanpa Macro/VBA)

Referensi	:
Platform	:	Excel 2003
Lokasi File	:	click di sini

Rumus terbilang ini dimaksudkan untuk mengubah angka ke huruf yang biasanya dituliskan di kwitansi ataupun di lembar invoice.

Fungsi sejenis sudah banyak yang membuatnya (hasil googling) tetapi biasanya menggunakan fasilitas pemrograman visual basic application for excel (vba) atau juga dikenal sebagai macro. Pada tulisan ini akan diuraikan tahapan untuk membuat fungsi terbilang menggunakan fasilitas ‘named range’ tanpa menggunakan macro.

Contoh penggunaan fungsi yang akan dibuat

Tahapan pembuatan fungsi ‘Terbilang’:

Misal angka yang akan dikonversi adalah 112148712.25:

Untuk membuat ‘Named Range’ di excel 2003, pada menu ‘Insert’, pilih ‘Names’ kemudian click ‘Define’.

Masukkan ‘Names in workbooks’ sesuai dengan isi tabel dr kolom ‘Named Range’  dan ‘Refers to’ sesuai dengan kolom Formula dari tabel yang sama.

No	Tahapan	Formula	Hasil	NamedRange (Variable)
1	Konversi angka ke text dengan format (“000000000.00″) maksimal angka yang diproses adalah sampai jutaan dengan dua desimal	=TEXT(A1,REPT(“0″,9) & “.00″)	112148712.25	Angka
2	Mengambil angka bulat	=LEFT(Angka,9)	112148712	Bulat
3	Mengambil angka desimal	=RIGHT(Angka,2)	25
4	Membuat List Angka	={0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19}	ListAngka
5	Membuat List Huruf	={“-”,”satu”,”dua”,”tiga”,”empat”,”lima”,”enam”,”tujuh”,”delapan”,”sembilan”,”sepuluh”,”sebelas”,”dua belas”,”tiga belas”,”empat belas”,”lima belas”,”enam belas”,”tujuh belas”,”delapan belas”,”sembilan belas”}		ListHuruf
6	Angka Bulat (112148712) dibagi menjadi tiga bagian : yaitu 112, 148 dan 712. Masing-masing bagian diambil satu angka di awal dan dua angka sisanya. Misal untuk angka 712, akan dipecah menjadi 7 dan 12
7	Mengambil angka pertama dari 712	=LEFT(RIGHT(bulat,3))	7	rat1
8	Mengambil dua angka sisanya dari 712	=MID(RIGHT(bulat,3),2,2)	12	sat1
9	Mengambil angka pertama dari 148	=LEFT(RIGHT(LEFT(bulat,LEN(bulat)-3),3))	1	rat2
10	Mengambil dua angka sisanya dari 148	=MID(RIGHT(LEFT(bulat,LEN(bulat)-3),3),2,2)	48	sat2
11	Mengambil angka pertama dari 112	=LEFT(RIGHT(LEFT(bulat,LEN(bulat)-6),3))	1	rat3
12	Mengambil dua angka sisanya dari 112	=MID(RIGHT(LEFT(bulat,LEN(bulat)-6),3),2,2)	12	sat3
13	Konversi rat1 ke angka	=SUBSTITUTE(IF(–rat1>0,LOOKUP(–rat1,ListAngka,ListHuruf) & ” ratus “,”-”),”satu ratus”,”seratus”)	tujuh ratus	Ratusan1
14	Konversi sat1 ke angka	=IF(–sat1>19,LOOKUP(–LEFT(sat1,1),ListAngka,ListHuruf) & ” puluh ” & LOOKUP(–RIGHT(sat1,1),ListAngka,ListHuruf),LOOKUP(–sat1,ListAngka,ListHuruf))	dua belas	Satuan1
15	Konversi rat2 ke angka	=SUBSTITUTE(IF(–rat2>0,LOOKUP(–rat2,ListAngka,ListHuruf) & ” ratus “,”-”),”satu ratus”,”seratus”)	seratus	Ratusan2
16	Konversi sat2 ke angka	=IF(–sat2>19,LOOKUP(–LEFT(sat2,1),ListAngka,ListHuruf) & ” puluh ” & LOOKUP(–RIGHT(sat2,1),ListAngka,ListHuruf),LOOKUP(–sat2,ListAngka,ListHuruf))	empat puluh delapan	Satuan2
17	Konversi rat3 ke angka	=SUBSTITUTE(IF(–rat3>0,LOOKUP(–rat3,ListAngka,ListHuruf) & ” ratus “,”-”),”satu ratus”,”seratus”)	seratus	Ratusan3
18	Konversi sat3 ke angka	=IF(–sat3>19,LOOKUP(–LEFT(sat3,1),ListAngka,ListHuruf) & ” puluh ” & LOOKUP(–RIGHT(sat3,1),ListAngka,ListHuruf),LOOKUP(–sat3,ListAngka,ListHuruf))	dua belas	Satuan3
19	Konversi sen	=IF(–RIGHT(Angka,2)>0,RIGHT(Angka,2) &”/100″,”")	25/100	sen
20	Menulis bagian jutaan	=IF((VALUE(rat3)+VALUE(sat3))>0,Ratusan3 & Satuan3 & ” juta “,”-”)	seratus dua belas juta	strJutaan
21	Menulis bagian ribuan	=IF((VALUE(rat2)+VALUE(sat2))=0,”-”,IF(VALUE(sat2)=1,”seribu “,Ratusan2 & Satuan2 & ” ribu “))	seratus empat puluh delapan ribu	strRibuan
22	Menulis bagian ratusan	=IF((VALUE(rat1)+VALUE(sat1))>0,Ratusan1 & Satuan1,”-”)	tujuh ratus dua belas	strRatusan
23	Menggabung Jutaan+Ribuan+Ratusan	=IF(LEN(Angka)>12,”#BELUM SAMPAI MILYARAN!!#”,TRIM(SUBSTITUTE(SUBSTITUTE(strJutaan & ” ” & strRibuan & ” ” & strRatusan & ” ” & sen, “-”,”"),” “,” “)))	seratus dua belas juta seratus empat puluh delapan ribu tujuh ratus dua belas 25/100

Jika diinginkan penyebutan angka dibelakang koma, pada formula di step ke 19 (konversi sen) dirubah menjadi:

=IF(–RIGHT(Angka,2)>0,”koma ” & IF(–RIGHT(Angka,2)>19, LOOKUP(–LEFT(RIGHT(Angka,2),1),ListAngka,ListHuruf) & ” puluh ” & LOOKUP(–RIGHT(RIGHT(Angka,2),1),ListAngka,ListHuruf),LOOKUP(–RIGHT(Angka,2),ListAngka,ListHuruf)),”")

sehingga hasil dari formula terbilang menjadi:

download file untuk versi terbilang dengan menyebutkan angka desimal ada di sini

update 26 Mei 2011:

jika diinginkan penyebutan misalnya

112148712.25 = “seratus dua belas juta seratus empat puluh delapan ribu tujuh ratus dua belas rupiah dua puluh lima sen”

silahkan dunlut filenya

Silahkan mencoba…..