Analisis data menggunakan Query Excel (Pivot Table)

Membina Pivot Table dalam Excel

Pivot Table merupakan sebuah tools pada aplikasi MS EXCEL yang digunakan untuk meringkaskan, menganalisa, serta menjelajah data untuk dibentangkan menjadi sebuah laporan dalam bentuk jadual-jadual.

Fungsi pivot table ini adalah bertujuan agar sebuah data boleh dibentangkan secara ringkas berdasarkan parameter-parameter yang diberikan sesuai dengan keperluan data.  Pivot table mempunyai sedikit persamaan dengan fungsi query di dalam MS Access.

Dengan menggunakan pivot table diharapkan mempercepatkan analisa suatu data untuk mengambil suatu keputusan.

Perkara-Perkara yang Perlu  Ada Sebelum Membina Pivot Table

  • Setiap kumpulan data yang digunakan untuk menjana pivot table mesti mengandungi header. setiap lajur dalam kumpulan data mesti mengandungi tajuk bagi memudahkan Excel mengenalpasti lajur.
  • Pivot table akan mengambil baris paling atas sebagai field yang akan digunakan untuk report.
  • Lajur yang tersembunyi (Column hide) akan tetap muncul pada field di pivot table field list.
  • Baris yang tersembunyi akan tetap dihitung nilainya jika terdapat di dalam array yang dipilih sebagai sumber data.
  • Pemilihan jenis data (value field settings) akan berpengaruh pada hasil nilai yang akan dipaparkan.

Cara Membina Pivot Table

Bagi membina sebuah pivot table, anda harus mempunyai satu kumpulan data yang mengandungi tajuk (header). Contoh data yang boleh digunakan untuk simulasi cara membuat pivot table, boleh dilihat pada  Rajah 1  dibawah ini.
pivot 1.1
Rajah 1

Dari data-data tersebut akan dipaparkan jumlah kakitangan berdasarkan JAWATAN (lajur E) dengan menggunakan tools pivot table. Contoh data diatas terdiri dari KOD, NAMA, KP, NO FAIL, JAWATAN, dan STATUS pada ruang A sampai ruang F.

Langkah-langkah dalam membuat pivot table (rujuk Rajah 2):

  • Dari menu insert pilih PivotTable pada kumpulan table (disitu terdapat dua pilihan iaitu pivot table dan pivot chart), pilih saja pada pivot table kemudian akan muncul form create pivot table.
  • Pada select a table or range higlight data dan tajuk lajur sahaja (pada contoh simulasi ini adalah array A3: F27 yang dipilih).  Abaikan tajuk table.
  • Kemudian pilih lokasi pivot table yang hendak ditempatkan pada choose where you want the PivotTable report to be place. Disini terdapat 2 pilihan iaitu:
    1. New worksheet jika anda ingin meletakkan hasil pivot table pada worksheet baru yang masih kosong.
    2. Existing worksheet jika anda ingin meletakkan hasil Pivot Table pada worksheet yang sama dengan sumber data pivot table dengan memilih sel atau sheet yang hendak ditempatkan.
pivot1
Rajah 2
Jika pemilihan data dan penempatan data sudah dilakukan, anda boleh klik butang ok untuk membuat dan memilih penyajian data pada Jadual Pangsi field list.

Menggunakan Pivot Table

Pada kotak dialog PivotTable field list, anda boleh memilih (filter) lajur-lajur atau item-item yang akan dipilih untuk dipaparkan dalam bentuk table.
Contoh pemilihan rekod (filter items), anda boleh lihat pada paparan Rajah 3 dibawah ini.

 

pivot 3.1

Rajah 3

Bagaimana untuk memilih item / data yang akan digunakan adalah dengan menanda pada check box item yang terdapat pada paparan choose fields to add to report.

Item berdasarkan data yang akan dicari di tempatkan pada Report Filter / row labels / column labels dan untuk memaparkan bilangan pada item, letakkan pada values .
Item-item pada data yang dicari anda boleh pindahkan dengan drag item di tempat yang sesuai dengan keinginan data pivot table yang hendak dipaparkan.
  • Report Filter jika anda ingin menapis data.
  • Row labels untuk paparan baris (seperti pada contoh diatas).
  • Column labels untuk paparan lajur.
Selain memilih salah satu cara paparan diatas, jika anda perlukan paparan data lebih dari satu kriteria, anda boleh menggabungkan penggunaannya.
Bagi values ​​terdapat beberapa pilihan nilai yang akan dipaparkan. Secara default pencarian values adalah sum (berdasarkan jumlah nilai).  Untuk bilangan rekod memerlukan anda menetapkan fungsi Count.

Apabila anda ingin mencari nilai berdasarkan bentuk lain (jumlah sel, nilai purata dan sebagainya) gantikan dengan cara klik pada (sum of KP) lalu pilih value field settings kemudian pilih pada pilihan yang diinginidalam tab Summarize Values By. Rujuk Rajah 5

pivot4

kesimpulan

Dengan menggunakan tools pivot table pada excel, anda boleh merumuskan laporan suatu kumpulan data untuk memudahkan anda memanipulasi data dalam jumlah yang banyak dan mendapat hasil yang cepat dan tepat.
Video Tutorial Membina Pivot Table

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