coretan tentang autocad dan excel

February 17, 2011

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

Filed under: Excel — Tags: , , — cadex @ 09:51

 

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. image
Pada create data source, pilih [MySQL ODBC 5.1 Driver]
4 Setting koneksi ke MySQl:
image
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 :
image
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] 

image

Ikuti langkah sesuai nomor di atas.

7 Memilih database dan tabel yang akan diquery
image
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 image
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 :

image

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:
image
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
image
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]
image
Drag Nama Field ke lokasi yang ditunjuk garis merah:
image
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:
image
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===

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

Create a free website or blog at WordPress.com.

%d bloggers like this: