Data Validation di Dalam Excel

Memberi combo box (list) pada suatu sell

Pada kesempatan ini, kita akan mencoba belajar membuat validasi data menggunakan combo box (list), yaitu suatu kumpulan data yang dapat kita pilih. Fungsi combo box ini adalah agar data yang kita input memiliki nilai kepresisian yang tinggi, yakni tidak mungkin berbeda dengan isi pada combo box tersebut. Jadi dengan membuat combo box pada suatu sell, maka kita tidak dapat menginput data selain yang terdapat pada daftar di combo box tersebut.

Misalnya, suatu sell seharusnya berisi data = “Pensil 2B”. Maka jika ia diinput dengan nama yang berbeda, misalnya saja = “Pensil2B”, maka ia akan memberikan suatu peringatan bahwa data yang kita input ini salah, dan proses tidak dapat dilanjutkan sampai kita membetulkan penulisannya. Demikianlah sekilas fungsi penggunaan combo box ini.

Baiklah, kita mulai saja pembuatan combo box ini pada Excel. Silahkan rekan-rekan ikuti langkah-langkah sbb:

1. Buatlah suatu sample database seperti gambar 1 dibawah ini.



Gambar 1. Sample database

2. Silahkan ke sell IV1, lalu ketikkan data seperti gambar 2 dibawah ini.



Gambar 2. Range sumber data yang digunakan untuk combo box


Ket : Anda dapat menyimpan data combo box di range yang lain selain yang diatas, namun karena ini latihan, dianjurkan untuk mengikuti sebagaimana pada langkah-langkah yang dijelaskan diatas. Range yang digunakan untuk combo box hanya dapat disimpan pada sheet yang sama.

3. Silahkan ke sell C2, lalu klik menu Data è Validation. Set nilai properties pada tab “Settings” dan tab “Error Alert” seperti gambar 3 dan gambar 4 dibawah ini (jangan dulu klik tombol OK).



Gambar 3. Settingan untuk combo box pada tab “Settings”


Gambar 4. Settingan untuk combo box pada tab “Error Alert”

4. Silahkan klik tombol OK. Maka kini sel C2 menjadi berjenis combo box.



Gambar 5. Combo box pada sell C2

5. Silahkan copy sell C2 ini, lalu sorot / block Sell C3:C15. Klik menu Edit è Paste Special. Silahkan pilih : Validation, lalu klik OK.



Gambar 6. Mempaste sell dibawahnya dengan paste validation nya saja.


6. Silahkan klik tombol OK. Maka kini seluruh sell C2:C15 menjadi berjenis combo box.

Sebenarnya cukup banyak jenis validasi yang dimiliki Excel, silahkan dicoba sendiri untuk menggunakan jenis validasi yang lain.

PivotTable Dan PivotChart

I. Membuat PivotTable

PivotTable adalah salah satu fasilitas didalam Excel, dimana ia berupa table interaktif yang sangat cepat dalam menganalisa, mengkombinasikan dan membandingkan sejumlah data baik sedikit maupun banyak.

Dalam PivotTable, kita dapat memilih suatu baris atau kolom yang hendak dijadikan referensi dalam melihat jumlah total dari suatu data. PivotTable hanya bersifat report / laporan, artinya Anda tidak dapat mengedit data pada PivotTable. Jika ingin mengedit data, maka Anda harus melakukannya pada table aslinya (sumber data). Adapun PivotChart adalah sama saja dengan PivotTable, hanya saja ia ditampilkan berupa Grafik. Untuk lebih jelasnya, dapat melihat contoh berikut ini:







Gambar 1. Contoh penggunaan PivotTable

Penjelasan dari nomor-nomor yang berwarna biru pada gambar 1 diatas adalah sebagai berikut:

1) Sumber data (Source data / table aslinya)
2) Sumber nilai sales bulan Jan untuk pensil 2B
3) Laporan PivotTable
4) Jumlah sales bulan Jan untuk pensil 2B
5) PivotChart yang menyatakan grafik dari laporan PivotTable (no.3)

Terlihat gambar 1 diatas, untuk menampilkan total sales per-bulan untuk masing-masing pensil, kita dapat menggunakan PivotTable. Baiklah, kita mulai saja latihan dalam membuat PivotTable ini. Silahkan ikuti langkah-langkah dibawah ini:

1. Buatlah table “Daftar Penjualan Barang” seperti dibawah ini :



Gambar 2. Daftar penjualan barang


2. Sorotlah range B4:E18.

Catatan: dalam menyorot range, harap mencangkup seluruh nama field (judul kolom) berikut dengan seluruh data yang ada dibawahnya.

3. Klik menu Data è PivotTabel and PivotChart Report. Maka kotak dialog PivotTabel and PivotChart Wizard – Step 1 of 3 akan ditampilkan.



Gambar 3. Kotak dialog PivotTabel and PivotChart Wizard – Step 1 of 3

4. Pada kotak dialog PivotTabel and PivotChart Wizard – Step 1 of 3 ini terdapat 4 pilihan pada “Where is the data that you want to analyze ?”, yaitu:

· Microsoft Excel lists or databases

Pilihan ini jika sumber data yang hendak dibuat PivotTablenya berasal dari Excel worksheet.

· External data sources

Pilihan ini jika sumber data yang hendak dibuat PivotTablenya berasal dari database external, seperti Microsoft SQL Server, Microsoft Access database, text file database (misalnya dBase), dll.

· Multiple consolidation ranges

Pilihan ini jika sumber data yang hendak dibuat PivotTablenya berasal dari beberapa (lebih dari satu) Excel worksheet.

· Another PivotTable report

Pilihan ini jika sumber data yang hendak dibuat PivotTablenya berasal dari PivotTable lainnya (bukan dari PivotChart).

Pada latihan ini, pilihlah : Microsoft Excel lists or databases



5. Juga pada kotak dialog PivotTabel and PivotChart Wizard – Step 1 of 3 ini terdapat 2 pilihan pada “What kind of report do you want to create ?”, yaitu:

· PivotTable

Pilihan ini jika kita hendak membuat suatu PivotTable

· PivotChart report

Pilihan ini jika kita hendak membuat PivotChart berikut dengan PivotTable-nya.

Pada latihan ini, pilihlah : PivotTable

6. Klik tombol Next. Maka akan muncul kotak dialog PivotTabel and PivotChart Wizard – Step 2 of 3 sbb:



Gambar 4. Kotak dialog PivotTabel and PivotChart Wizard – Step 2 of 3

Karena pada awalnya kita sudah menyorot Range B4:E18, maka secara otomatis pada field Range sudah terisi range yang kita sorot tersebut.

7. Klik tombol Next. Maka akan muncul kotak dialog PivotTabel and PivotChart Wizard – Step 3 of 3 sbb:



Gambar 5. Kotak dialog PivotTabel and PivotChart Wizard – Step 3 of 3

8. Klik tombol Layout. Maka akan muncul kotak dialog sbb:


Gambar 6. Kotak dialog Layout pada PivotTabel and PivotChart Wizard – Step 3 of 3

Pada kotak dialog ini, klik & drag (digeser dengan mouse kiri tidak dilepas sampai objek telah sampai ke tempat tujuan) tombol-tombol nama field yang ada disebelah kanan ke diagram PivotTable yang disediakan. Silahkan ikuti langkah-langkah ini:

· Tombol “Barang” ditempatkan (klik & drag) ke ROW

· Tombol “Kota” ditempatkan (klik & drag) ke COLUMN

· Tombol “Unit” ditempatkan (klik & drag) ke DATA

· Tombol “Bulan” ditempatkan (klik & drag) ke PAGE

Sehingga akan didapatkan hasil sbb :


Gambar 7. Hasil yang diperoleh setelah tombol-tombolnya di geser ke diagram PivotTable

Kemudian klik tombol OK. Kemudian kotak dialog PivotTabel and PivotChart Wizard – Step 3 of 3 akan aktif kembali. Pada kotak dialog PivotTabel and PivotChart Wizard – Step 3 of 3, pilihlah New Worksheet.


9. Klik tombol Finish. Dengan langkah ini, maka akan muncul sheet baru lengkap dengan PivotTable-nya. Lihat gambar dibawah ini:


Gambar 8. PivotTable untuk Daftar Penjualan Barang PT.Mastermedia

Terlihat pada gambar 8 diatas, pada field Bulan, Barang, dan Kota berupa ComboBox yaitu suatu field berjenis pilihan, dimana kita bisa memilih checklist ketika kita klik field tersebut. Silahkan dicoba untuk klik field Barang (Sell A4). Akan ditampilkan gambar sbb :



Gambar 9. Pilihan yang tersedia dalam menampilkan Barang apa saja yang hendak diperlihatkan di PivotTable

II. Mengedit / Mengubah Layout (tampilan) PivotTable

Apabila diperlukan, kita dapat mengedit tampilan PivotTable ini, seperti mengubah posisi tombol-tombol nama field yang ada pada layout PivotTable ke posisi yang lain sesuai yang kita inginkan. Sebagai contoh, silahkan Anda coba menempatkan field “Bulan” pada posisi Row berdampingan dengan field “Barang”. Caranya adalah, field “Bulan” tersebut di klik & drag ke posisi ROW. Hasilnya dapat dilihat pada gambar 10 dibawah ini.



Gambar 10. Hasil yang diperoleh setelah field “Bulan” di klik & drag ke posisi ROW

III. Mengedit Data Sumber PivotTable

Sebagaimana sudah saya jelaskan sebelumnya, kita tidak dapat mengedit data di dalam PivotTable. Kita hanya dapat mengedit pada sumber data. Setelah mengedit data, jangan lupa PivotTable tersebut harus di refresh. Caranya, klik kanan PivotTable tersebut, lalu pilih menu Refresh Data. Atau bisa juga dengan mengklik icon dengan gambar tanda seru () yang terdapat pada toolbar PivotTable.


IV. Membuat PivotChart

Untuk membuat PivotChart, pastikan toolbar PivotTable belum Anda close. Gambar toolbar-nya adalah sbb :



Gambar 11. Toolbar PivotTable.

Klik tombol pada toolbar PivotTable. Maka akan muncul sheet baru yang menampilkan grafik dari PivotTable yang sedang Anda buka ini.


Gambar 12. Tampilan Grafik (PivotChart)

Apabila dibutuhkan, Anda dapat mengedit tampilan grafik ini sesuai yang Anda inginkan. Untuk mengeditnya, lakukan langkah-langkah berikut ini :

· Klik gambar grafik tersebut.
· Klik kanan mouse Anda, pilih : Chart Type. Maka akan muncul kotak dialog sbb :


Gambar 13. Kotak dialog Chart Type

· Pilihlah Chart Type : Column dan Chart sub-type seperti yang terdapat pada gambar 13 diatas (background warna hitam).
· Klik tombol OK.
· Maka akan ditampilkan grafik baru sbb :


Gambar 14. Hasil yang diperoleh setelah mengedit Chart Type.