Cara Membuat Pivot Table dan Beberapa Contoh Pivot Table

Assalamu'alaikum semuaa

Kali ini saya akan membahas tentang pivot table, sebelumnya mungkin ada pertanyaan "kenapa kita harus tau tentang pivot table danapasih kegunaanya?"

Well, jawabannya karena pivot table mampu menganalisis data dengan cepat sehingga biasa digunkaan dalam membuat keputusan bisnis yang baik. Terkadang kita memiliki data yang sangat banyak dan sangat tidak efesien untuk melihat data tertentu dari data yang banyak itu, oleh karena itu pivot table lah solusinya dengan pivot table kita dapat dengan mudah mecari data yang kita mau dengan adanya fitur filter. PivotTable merupakan cara yang hebat untuk meringkas, menganalisis, menjelajahi, dan menyajikan data, dan dapat membuatnya hanya dengan beberapa klik.
 Dan PivotTable sangat fleksibel dan dapat dengan cepat disesuaikan tergantung pada bagaimana kita perlu untuk menampilkan hasil. Kita juga dapat membuat PivotChart berdasarkan PivotTable yang akan diperbarui secara otomatis saat PivotTable sedang diperbarui. 

Lalu bagaimana cara membuat Pivot Table itu sendiri?
baiklah saya akan membahasnya satu persatu mukai dari contoh yang sederhana dengan langkah-lahkah dibawah ini:

Pertama Kita Mulai dari Contoh yang Sederhana

Tapi, sebelumnya kita harus tau beberapa hal dibawah ini:

·    Data kita harus diatur dalam format bertabel, dan tidak memiliki satu pun baris atau kolom kosong. 

·    Tabel merupakan sumber data PivotTable yang hebat, karena baris yang ditambahkan ke tabel secara otomatis disertakan dalam PivotTable saat kita merefresh data, dan setiap kolom baru akan disertakan dalam Daftar Bidang PivotTable. Jika tidak, kita perlu untuk baik memperbarui rentang sumber data secara manual, maupun menggunakan rumus rentang bernama dinamis.

·    Tipe data dalam kolom harus sama. Misalnya, kita tidak boleh mencampur tanggal dan teks di kolom yang sama.

·    PivotTable berfungsi pada jepretan layar data, yang disebut dengan cache, sehingga data aktual kita tidak diubah dengan cara apa pun.

Berikut Langkah-langkahnya:

1. Buat terlebih dahulu tabelnya

2. Drag atau blok dahulu tabelnya

3. Lalu. pilih tabs INSERT, klik command "PIVOT TABLE", karena ingin membuat pivot table maka pilih PIVOT TABLE, seperti dibawah ini:

4. Kemudian akan muncul menu seperti dibawah ini:

     Dalam menu pada gambar diatas, kita pilih table yang sudah kita buat tadi, lalu pilih tempat dimana Pivot Table akan kita letakkan, untuk contoh saya pilih "new workshet" karena saya akan meeletakkanya difile yang sama hanya beda sheet.

5. Lalu klik OK,maka akan muncl menu seperti dibawah ini:

     Pada gambar diatas terdapat beberapa pilihan dikanan lembar kerja, nah pilihan itulah yang akan menentukan bentuk Pivot Table yang kita buat, karena ini contoh sederhana maka kita tidak perlu menggunakan menu filter terlebih dahulu,sebenarnya meneu itu disesuaikan dnegan kebutuhan kita, tapi saya ingen mencontohkan dengan mengisi ROW LABELS dengan "Category", COLOUMS LABELS dengan "Month", dan VALUES dengan menjumlahkan "Amount". Caranya kita klik dahulu kotak kecil yang ada disamping judul tabs pada pilihan CHOOSE FILES TO ADD TO REPORT, lalu pindahkan ke menu ROW LABELS, MONTH, DAN VALUES sesuai keinginan kita.

6. Lalu, klik kiri pada menu VALUES untuk menentukan perhitungan seperti apa yang akan dihitung.

7. Karena saya akann melakukan perhitungan penjumlahan, maka pilih SUM, untuk perhitungan lain tinggal pilih saja sesuai kebutuhan.

8.  Kemudian pilih menu sebelahnya yaitu "show values as", dalam mwnu ini kita bisa memilih dalam bentuk apa hasil perhitungan kita akan ditampilkan, pertama saya akan memilih dahulu menu untuk menampilkan darimana presentase data yang akan ditampilkan, sebagai contoh saya akan menampilkan presentase of grand total seperti dibawah ini:

9. Lalu, pilih dalam pilihan apa akan ditampilahnya, sebagai contoh saya tampilkan berdasarkan category, seperti dibawah ini:

10. Pembuatan Pivot Table sederhana selesai dan bentuknya seperti dibawah ini:

Pembuatan Pivot Chart

            Untuk membuat Pivot Chart hampir sama langkahnya dngan membuat Pivot Table hanya saja saat memilih command di tabs Insert pilih Pivot Chart, lalu langkah selanjutnya sama sperti pivot Table.Untuk contoh saya membuat Pivot Chart dengan langkah yang sama dengan membuat Pivot Table diatas makan bentuknya menjadi seperti dibawah ini:

Untuk memahami lebih dalam tentang Pivot Table maka saya akan menyisipkan contoh lain tentang Pivot Table

Contoh Pembuat Pivot Table Data Penjualan

Langkah-lagkahnya seperti dibawah ini:

1. Buat table terlebih dahulu

     2. Blok tabel dan pilih command Pivot Table di tabs Insert

   3.  Sesuaikan bentuk Pivot Table dengan menyesuaikannya dengan menu, seperti dibawah ini:

Contoh Pivot Table Data Order Barang

Langkah yang digunakan sama dengan yang telah dicontohkan, namun disini kita akan mulai menggunakan menu filter.

1. Buat Tabel terlebih dahulu.

   2. Blok tabel dan pilih command Pivot Table di tabs Insert

   3. Sesuaikan bentuk Pivot Table dengan menyesuaikannya dengan menu, seperti dibawah ini:

     Nah pada contoh ini saya akan mengealkan fungsi menu filter yang pada contph sebelumnya          tidak digunakan, pada contoh ini saya menggunakan menu filter karena memang datanya sudah      cukup banyak dan ada colom tentang Supplier, menu filter disini digunakan untuk memfilter            atau menyaring data yang akan dimuat dalam pivot table dari suppliernya. Maka tampilan                dibawahnya akan mengikuti sesuai apa dasar filternya.

 4. Untuk lebih jelasnya lihat ketika Pivot Table sudah terbentuk kita dapat memilih berdasarkan          data apa yang akan kita lihat, sebagai contoh saya pilih data yang akan dipilih berdasarkan              supplier dari CV Abadi Jaya, seperti dibawah ini:

 5. Maka akan muncul data seperti dibawah ini:

Terakhir saya akan memberi contoh penggunaan Pivot Table dengan data yang lebih banyak dan menggunakan 5 filter

Contoh Pivot Table Data Peserta Suatu Ujian

1.  Buat Table terlebih dahulu

2. Blog dan pilih menu Pivot Table

 3. Pilih tempat peletakan Pivot Table

4. Susun Pivot Table sesuai yang diinginkan, caranya sama sepertiyang telah dijelaskan sebelumnya

Mengubah Table dan Pivot Tablenya

Dalam suatu hal mungkin kita lupa ada kolom baru yang harus ditambahkan sehingga merubah bentuk table, lalu bagaimana dengan pivot tablenya? Apabila penambahan colom atau baris dalam tabel letaknya diluar table yang telah di blog maka Pivot Table tidak akan berubah juga, oleh karea itu jika ingin mengubah table dengan menambah baris atau kolom letakkan baris/kolom itu didalam tabel, kemudian refresh Pivot Tablenya, untuk jelasnya lihat langkah-langkah berikut:

1. Tambahkan Kolom/Baris dengan klik kanan, lalu pilih insert, pilih lagi sesuai apa yang ingin ditambahkan. Disini saya ambil contoh untuk menambahkan kolom nilai rata-rata.

2. Isi datanya sesuai yang ingin ditambahkan

3. Pindah ke sheet Pivot Tabelnya lalu klik command "refresh"

4. Maka otomatis akan muncul pilihan rata pada "fields to report"


Sekian yang dapat saya sampaikan, Terimakasih :)

Read More

Cara Menggabungkan Data-Data untuk Membentuk NIP

Assalamu'alaikum semuaa

Bahasan kali ini seputar cara menggabungkan atau membentuk NIP (Nomor Induk Pegawai). Dalam NIP sendiri terdapat data-data khusus masing-maisng pegawai yaang tiap pegawai mempunyai data yang bebeda, data ini berbentuk angka berupa Tanggal Kelahiran, Tanggal Pengangkatan, Jenis Kelamin, dan Nomor Urut. Untuk merangkainya menjadi bentuk NIP ikuti lagkah beerikut ini:

1. Siapkan dahulu data yang dibutuhkan. Seperti dibawah ini:

2. Berikutnya gunakan rumus =CONCATENATE(YEAR(C3),IF(MONTH(C3)<10,CONCATENATE("0",MONTH(C3)),MONTH(C3)),IF(DAY(C3)<10,CONCATENATE("0",DAY(C3)),DAY(C3)),YEAR(D3),IF(MONTH(D3)<10,CONCATENATE("0",MONTH(D3)),MONTH(D3)),IF(E3="P",2,1),IF(LEN(F3)=1,CONCATENATE("00",F3),IF(LEN(F3)=2,CONCATENATE("0",F3),F3))) . 

Untuk lebih jelasnya lihat gambar dibawah ini:

Rumus CONTATENATE adalah rumus yang digunakan untuk mengabungkan, oleh karena itu letaknya diawal, sementara didalam rumus itu terdapat rumus-rumus lain.

Rumus IF yang pertama digunakan untuk menggabungkan bulan, karena nama bulan jika didalam bentuk angka bisa menjadi 1 digit angka maupun 2 digit sedangkan dalam NIP nama bulan harus 2 digit, oleh karena itu IF disini juga mengandung rumus CONTATENATE yang berfungsi apabila nama bulannya 1 digit maka angka 0 akan digabungkan didepan digit bulan itu. Begitu juga dengan hari dan tahun, hanya pernggunakan rumus MONTH, DAY, dan YEAR saja yang beda.

Selanjutnya penggunakan rumus IF pada status pegawai apakaha laki-laki atau perempuan, jika laki-laki maka diwakili dengan angka 1 dan perempuan dengan anka 2. oleh karena itu fungsi IF disi untuk membedakan keduanya.

Rumus IF yang terakhir memuat rumus LEN juga yaitu rumus yang digunakan untuk mencari banyaknya jumlah digit angka atau huruf dalam suatu kolom. Gabungan rumus ini digunakan karena dalam NIP nomor urut harus terdiri dari 3 digit oleh karena itu yang nomer urutnya hanya 1 digit ditambah "00" didepannya dan yang 2 digit ditambah "0", ini sama halnya dengan kasus bulan, hari,dan tahun tadi oleh karena itu juga menggunakan rumus CONTATENATE, hanya beda dalam rumus teksnya saja, kalau ini pakai LEN.

3. Langkah selanjutnya tinggal menarik rumus itu kebawah atau ke kolom bawahnya agar terisi semua. seperti gambar dibawah ini:

Begitulah cara menggabungkan atau membentuk NIP, Terimakasih :)

Read More

Cara Menghitung Gaji Pegawai Tetap dan Pajaknya di Ms.Excel

Assalamu’alaikum semua...

Pada kesempatan kali ini saya akan memaparkan tentang tutorial penghitungan gaji pegawai beserta pajaknya mengunakan Ms. Excel.

Gaji yang dibahas disini adalah gaji pegawai tetap yang dibayarkan dalam waktu 30 hari/sebulan sekali. Perhitngan ini belom termasuk bila ada tambahan bonus maupun uang lembur, melainkan hanya sebatas gaji pokok dan tunjangan-tunjangan yang bersifat tetap lainnya.

Berikut langkah-langkahnya:

1. Buatlah terlebih dahulu kerangka tabelnya seperti dibawah ini:

Dalam gambar diatas terdapat 2 kerangka, yang pertama kerangka untuk nama dan gaji pegawai masing yang akan dihitung dan yang bawah adalah untuk tabel jenis-jenis gajinya. Untuk jumlah pegawai yang sedikit, mungkin cara ini tidak efesien, tapi dalam jumlah pegawai yang banyak cara ini sangat efesien. 

untuk mengetahinya ikuti langkah selanjutnya.

2. Masukan data-data pokok yang akan dijadikan dasar perhitungan, seperti dibawah ini:

Pada tabel diatas terlihat bahwa dalam tabel terdapat beberapa kolom yang berisi Nomer, Golongan, Nama Pegawai, Gaji-gaji (Gaji Pokok, Tunjangan, Transport), Status. Total Gaji, Total Gaji Setahun, PTKP,PhKP, Pajak setahun dan sebulan. Di tabel kedua ada gaji,tunjangan, dan transport. 

Dalam hal ini data-data pokok yang harus diisi adalah seluruh kolom dalam tabel dua yang merupakan ketetapan tiap-tiap tingkatan gaji yang sesuai dengan tingkat golongan pegawainya serta pada tabel 1 diisi No, Golongan. dan Nama Pegawai.

3. Langkah Selanjutnya adalah mencari gaji pokok, tunjangan, dan transport bagi pegawai sesuai dengan golongannya. untuk hal ini gunakan rumus =VLOOKUP(B4,$B$14:$E$31,2), dalam rumus itu B4 menunjukan golongannya. lalu $B$14:$E$31 menunjukan tabel 2 yang diabsolutkan, 2 menunjukan bahwa data yang akan diambil berada di kolom ke 2 pada tabel tadi. Sistem kerjanya kita mengambil data yang ada dalam tabel 2 untuk dimasukan ke tabel 1 menggunaan salah satu data yang sama diantara kedua tabel yaitu "golongan". Lebih jelasnya lihat gambar dibawah ini:

Setelah itu tinggal melakukal hal yang sama pada kolom tunjangan dan transport yang masih kossong, hanya dirubah bagia kolom keberapa yang akan diambil dalam tabel 2 yg diabsolutkan tadi, untk tunjangan menjadi "3", dan transport"4"

4. Langkah selanjutnya adalahmencari unjangan ststus sebesar 10% dari gaji pokok, caranya dengan memasukan rumus =if(G4=”K”,10%*D4,”-“) sistemnya dengan perumpamaan “jika statusnya K atau kawin maka gaji pokok dikali 10%, jika bukan maka tanda -“

Maka akan terlihat seperti gambar diatas

5. Langkah selanjutnya mencari total gaji yaitu dengan menjumlahkan gaji-gaji beserta tunjangannya dengan rumus =sum(D4:H4)

6. Berikutnya mencari total gaji setahun dengan mengalikan total gaji sebanyak 12 kali dengan rumus =I4*12

7. Selanjutnya mencari  PTKP, sesungguhnya dalam praktek nyata status dalam PTKP tidak hanya Kawin dan Tidak Kawin tapi ada juga tanggunggan, namun kita menggunakan contoh sederhana saja jadi haya menggunakan rumus =if(G4="K",54000000+4500000,54000000) PTKP yang digunakan adalah PTKP Th2016 Rp54.000.000 bagi Wajib Pajak Orang Pribadi, dan Rp4.500.000 untuk status perkawinan.

Jika status PTKPnya beragam atau ada tanggungan anak maupun orang tua lebih baik menggunakan tabel terlebih dahulu dengan sistem seperti mencarigaji pokok pada langkah kedua tadi, sehinga rumus yang digunkan adalah =VLOOKUP atau =HLOOKUP.

8. Kemudian kita mencari PhKP atau penghasilan kena pajak, caranya dengan mengurangkan total gaji setahun dengan PTKP, yaitu dengan rumus =IF(J4-K4>0,J4-K4,0) rumus yang digunakan harus menggunakan rumus IF karena tidak selamanya total gaji setahun lebih besar dari PTKP jika tidak menggunakan IF nanti akan terjadi nilai minus, padahal harusnya perhitungan PhKP tidak boleh minus paling kecil 0 kecuali jika ada kredit pajak.

9.Kemudian mencari Pajak Penghasilan yang terutang yaitu dengan mengalikan PhKP dengan tarif tertentu,  5% untuk rentang  0-50jt, 15% untuk 50-250jt, 25% untuk 250-500jt, dan 30% untuk lebih dari 500jt. Perhitungannya bertahap jika nilai PhKP menggunakan tarif yang lebih dari 5%, lebih jelasnya terlihat pada rumus ini =IF(L4<=50000000,L4*5%,IF(L4<=250000000,(L4-50000000)*15%+50000000*5%,IF(L4<=500000000,(L4-250000000)*25%+200000000*15%+50000000*5%,(L4-500000000)*30%+250000000*25%+200000000*15%+50000000*5%)))

Pada contoh nilai total gaji tidak begitu besar dan banyak yang dibawah PTKP, sehingga pajak yang terutang nilainya banyak yang 0.

10. Terakhir, tinggal mencari pajak terutangperbulan dengan membagi pajak terutang setahun dengan angka 12, rumusnya =M4/12 .

Sekian yang dapat saya sampaikan, Terimakasih :)

Read More