Mengolah Database dengan Excel Pengertian Database Database terdiri dari sekumpulan record, sedangkan record terdiri atas sekumpulan field (data) yang membentuk satu kesatuan dan masing-masing field (data) saling berhubungan satu dengan yang lain sehingga membentuk suatu pengertian tertentu. Microsoft Excel menyediakan berbagai kegiatan pengoperasian database seperti mengurutkan data, melacak dan menampilkan data dengan file melengkapi table dengan sub total membuat rekapitulasi data dengan pivot table, serta konsolidasi data dari berbagai lembar kerja.
Mengolah Database Pada beberapa kesempatan,terkadang Excel juga cukup dapat digunakan sebagai media penyimpanan basis data. Untuk membuat basis data sederhana, excel membutuhkan keteraturan informasi. Untuk itu terdapat aturan dalam membuat basis data sederhana dengan excel, yakni : a. Nama field harus berada dalam satu baris yang merupakan baris pertama pada daftar b. Memasukkan tiap record dalam satu baris c. Tidak menyertakan baris kosong dalam range daftar d. Tidak menggunakan lebih dari satu worksheet untuk sebuah range daftar Untuk membuat sebuah basis data sederhana ala excel, buat label setiap field dalam satu baris, seperti gambar dibawah ini:
FIELD
Mengurutkan Data Pada Basis Data Menggunakan Sort untuk mengurutkan data pada Excell 2007 kali ini jauh lebih efisien karena dapat mengurutkan data berdasarkan field tertentu. Berikut ini adalah mengurutkan data alamat berdasarkan field pada judul kolom. Langkah – langkah yang harus dilakukan adalah : 1. Sorot data / blok data yang akan diurutkan sesuai dengan memilih salah satu field yang hendak diurutkan. Kemudian sorot range yang di maksud. 2. Pilih Tab Data 3. Pada Group Sort&Filter klik Sort. Hasilnya kotak dialog Sort akan tampil . Di bagian
Sort by pilih Column B pada kolom Order pilih A to Z.
1. Klik OK. Hasilnya data diurutkan berdasarkan Nama. 2.
Begitu
pula
pengurutan
menggunakan
field-field
lainnya.
Pilih
jenis
pengurutannya baik itu ascending (urut keatas) atau descending (urut ke bawah) beserta field yang digunakan untuk melakukan pengurutan.
Menyaring Data Untuk menyaring data secara spesifik, anda dapat menyaring data yang diperlukan saja. Data yang tidak diperlukan tidak akan ditampilkan sehingga proses pencarian data akan lebih akurat dan mudah. 1. Pastikan range yang akan disaring masih tersorot 2. Pilih Tab Data 3. Pada Group Sort &Filter klik Filter. Maka hasilnya akan tampil tombol list pada masing-masing field atau judul kolom.
4.
Klik tombol list pada field Kota lalu klik Select All untuk menonaktifkan seluruh pilihan.
5. Pada kolom Nama,pilih hanya nama INTAN dan INDAH saja lalu klik OK. Maka hasilnya yang tampil adalah hanya nilai INTAN dan INDAH saja.
6. Berikutnya tampilkan semua data, atau pilih Select All, pda tombol list. 7.
Setelah menampilkan kembali semua data lengkap, kali ini klik tombol list Nama Peserta lalu aktifkan data peserta dengan nama yang huruf awal A.
Menghitung Subtotal Database Untuk dapat menghitung total dan subtotal data angka secara otomatis dapat dilakukan dengan menggunakan perintah subtotal. Dengan perintah ini data dapat dihitung persub kemudian dijumlah menjadi total yang semuanya ditampilkan secara bersamaan. Langkah-langkah yang perlu dilakukan adalah : Siapkan data yang akan digunakan untuk menghitung subtotal
1. Plih Tab Data 2. Klik Sort 3. Pada Sort By pilih Harga dengan Order : A to Z
Memilih field harga 4. Klik OK. Hasilnya data akan diurutkan sesuai harga. 5. Pada Group Outline klik SubTotal, di bagian At Each Change in pilih Harga, dibagian Use Function pilih Sum, pada Add sub total to aktifkan pilihan Jumlah Harga. 6. Aktifkan Replace Current subtotals dan summary below data lalu klik OK.
7. Berikut adalah perhitungan Subtotal.
Menghitung Validasi Data Data validasi berguna untuk memudahkan anda dalam memasukkan data tanpa harus mengetik data pada sel. Dibawah ini kita akan membuat Input Nama Barang Data Pengambilan. Langkah – langkah yang perlu dilakukan adalah : 1. Buat file dengan field: No, Nama Barang, dan Total Harga 2. Klik kolom B untuk menyorot seluruh sel pada kolom Nama Barang. 3. Kemudian pilih Tab Data 4. Pada Group Data tools klik Data Validation. Setelah itu kotak dialog Data Validation akan tampil.
5. Pilih Tab Settings 6. Dibagian Allow, pilih List untuk memasukkan daftar data. 7. Dibagian Source, ketik beberapa nama barang. 8. Pilih Tab Input Message 9. Didalam Input Message ketik pesan Masukkan Nama Barang 10. Klik OK. Hasilnya akan tampak seperti ini.
dan
11. Berikutnya klik kolom C untuk menyorot seluruh sel pada Total Harga. 12. Pada group Data Tools, klik Data Validation. 13. Pada bagian Allow pilih Whole Number untuk memasukkan data berupa angka 14. Di bagian Data pilih greater than or equal to untuk memasukkan data angka dengan ketentuan lebih besar atau sama dengan. 15. Dibagian Minimum ketik angka 200000. Artinya data yang dimasukkan harus lebih besar dari 500000 atau setidaknya sama dengan 200000.
Pewarnaan dengan Conditional Formatting pada Excel 2010 Seperti namanya, conditional formatting adalah satu fitur pada Excel 2007 ke atas yang digunakan untuk melakukan format pada range dari cell berdasarkan kondisi tertentu, sebagai contoh:
Mewarnai cell dengan hijau, kuning dan merah berdasarkan nilai tertentu. Memberikan intensitas spektrum warna sesuai dengan rentang nilai.
Melalui artikel ini, mari kita coba fitur ini dengan contoh pewarnaan sederhana sebagai berikut: 1. Jalankan aplikasi Microsoft Excel 2010, buka file summary_penjualan.xlsx. 2. Pada Sheet 1 terdapat data dengan tampilan sebagai berikut. Klik pada gambar untuk memperbesar tampilannya. Contoh data ini menunjukkan penjualan dari buah-buahan, makanan dan sayur-sayuran dari bulan ke bulan.
3. Kita akan coba melihat perbandingan penjualan dari seluruh bulan dan dari seluruh kategori. Pilih range B3:M5.
4. Pada ribbon tab Home, klik Conditional Formatting pada group Styles
. 5. Lanjutkan dengan mengarahkan pilihan ke Color Scales | Red – Yellow – Green Color Scale
6. Hasilnya akan terlihat pada gambar berikut, dimana nilai penjualan sangat jelas terbagi berdasarkan nilai penjualannya. Buah-buahan selalu menempati nilai tertinggi (merah), diikuti Sayur-sayuran (kuning) dan terakhir Makanan dan Minuman (hijau)
. 7. Dari hasil pewarnaan tersebut, untuk dilakukan analisa terasa kurang “menggigit”. Cobalah ulangi langkah pewarnaan di atas tapi pemilihan range hanya untuk tiap baris kategor, yaitu berturut range B3:M3, B4:M4 dan B5:M5.Jika semua berjalan lancar, maka hasilnya akan terlihat seperti tampilan berikut.
Terlihat penjualan yang mendominasi ada pada bulan 10, dimana semua warna hampir mendekati merah. 8. Selain warna, sebenarnya conditional formatting ini juga bisa menggunakan indikator lain seperti panjang bar berwarna dengan Data Bars dan gambar menggunakan Icon Sets.Cobalah blok bari Grand Total, yaitu range B6:M6. Kemudian dari menu Conditional Formatting | Icon Sets, dan pilih 3 Signs.
9. Sebagian hasilnya akan terlihat seperti pada gambar berikut. Perhatikan gambar bulat hijau menandakan penjualan terbaik, yaitu di bulan 3, 7, 8 dan 10
. 10. Sekarang hilangkan format warna yang telah kita buat dengan cara mengklik menu Conditional Formatting | Clear Rules, dan pilih Clear Rules from Entire Sheet.
11. Selesai.
Menggunakan Rule dengan Formula pada Conditional Formatting
Pada artikel sebelumnya, ditunjukkan bagaimana memberi pewarnaan dengan conditional formatting. Spektrum warna pada artikel tersebut ditentukan oleh Excel berdasarkan perbandingan otomatis. Rule ini tentunya tidak cocok untuk banyak kondisi. Melalui artikel ini, penulis akan menunjukkan contoh bagaimana membuat dan mengelola rule yang banyak digunakan, yaitu menggunakan formula untuk mewarnai cell kita. Untuk contoh kasusnya adalah membandingkan antara nilai aktual penjualan dibandingkan dengan target yang ingin dicapai. Berikut adalah langkah-langkahnya: 1. Jalankan aplikasi Microsoft Excel 2010, buka file sales_vs_target.xlsx. 2. Pada Sheet 1 terdapat data dengan tampilan sebagai berikut.
Data tersebut adalah data penjualan dari tiga kategori produk (Buah, Makanan & Minuman, Sayur-Sayuran) selama dua semester. Sementara itu terdapat juga target penjualan untuk tiap semester. 3. Dari data tersebut, kita akan memberi dua warna, yaitu hijau dan merah pada kolom penjualan dari kedua semester. Hijau apabila target tercapai, tetapi merah apabila target tidak tercapai. 4. Dengan mouse dan tombol CTRL, pilih dua range yang B3:B5 serta D3:D5 seperti tampak pada gambar berikut.
5. Pada ribbon tab Home, klik Conditional Formatting | Manage Rules.
6. Pada dialog Conditional Formatting Rules Manager, klik tombol New.
7. Pada dialog New Formatting Rule dan pada daftar Select a Rule Type, pilih Use a formula to determine which cells to format.
8. Pada bagian Edit the Rule Description | Format values where this formula is true: ketik manual formula berikut: B3>=C3
9. Setelah itu klik tombol Format. Pada dialog Format Cells yang muncul, klik tab Fill. Pilih warna hijau untuk Background Color. Klik tombol OK.
10. Dialog New Forma Rule sekarang akan terlihat seperti gambar berikut.
11. Klik tombol OK untuk menutup dialog. 12. Sekarang terdapat tambahan satu rule pada Conditional Formatting Rules Manager. Namun, perhatikan pada rule tersebut apakah yang terisi sesuai dengan yang kita isikan
sebelumnya, yaitu B3 >= C3. Pada kasus penulis, rule tidak terisi seperti itu (terlihat pada gambar berikut).
13. Untuk memperbaiki ini, klik tombol Edit Rule dan isikan kembali formula =B3 >= C3 dan klik tombol OK. Kali ini rulenya telah benar.
14. Ulangi langkah 7 s/d 14 untuk rule berikut dengan format warna merah. =B3 < C3 Hasil akhir tambahan rule tersebut akan tampak sebagai berikut.
15. Klik tombol Apply, cell-cell yang kita pilih pada langkah no. 5 akan di-format sesuai kondisi rule-rule yang kita masukkan.
16. Selesai.
What-If Analysis Salah satu bagian terpenting dari penggunaan software spreadsheet (Excel) adalah kemampuan untuk melakukan analisa what-if dengan cepat dan mudah. Sebagai contoh, kita bisa menggunakan Excel untuk mengambil keputusan dalam pengambilan fasilitas kredit rumah/mobil, berapa sebaiknya uang muka yang harus kita sediakan, berapa lama kita hendak melunasi fasilitas kredit. Kita bisa melakukan simulasi dengan berbagai macam asumsi tentang suku bunga, jumlah uang muka dan lama pembayaran. Goal Seek Dengan menggunakan Goal Seek kita bisa menghitung nilai yang belum diketahui yang diperlukan untuk memberikan hasil tertentu. Contoh, jika diinginkan untuk mengetahui nilai maksimum dari sebuah pinjaman selama 30 tahun jika dibatasi bahwa angsuran per bulan maksimum adalah Rp 2 000 000. Dengan menggunakan Goal Seek, hal ini akan bisa diselesaikan. Gambar berikut adalah worksheet yang diperlukan.
Berikut prosedur yang diperlukan: 1. Pilih sel A1:B4
2. Tekan Ctrl+Shift+F3 atau pilih menu Formulas mendefinisikan nama bagi sel B1-B4 3. Window berikut akan muncul
Create From Selection untuk
4. Pilih Left column 5. Letakkan kursor di sel B4 6. Ketikkan Formula =PMT(Bunga/12;Jangka_Waktu*12;Nilai_Pinjaman) 7. Pilih kembali sel B4, kemudian pilih menu Data What-If Analysis Goal Seek...
8. Isikan nilai yang diinginkan seperti pada gambar, lalu klik tombol OK Data Table Data Table atau disebut juga Sensitivity Table menunjukkan pengaruh dari satu atau dua variabel pada formula yang menggunakan variabel tersebut. Contoh berikut menunjukkan penggunaan data table dengan satu buah variabel input. Di sini diberikan ilustrasi seandainya hendak mengambil kredit untuk rumah selama 30 tahun. Nilai pinjaman adalah Rp 300 000 000. Gambar berikut menunjukkan worksheet untuk membuat data table tersebut.
Tahapan yang dilakukan adalah sebagai berikut: 1. Sel B3:B6 diisi dengan suku bunga yang ingin dibandingkan. Sel B3:B6 ini disebut sebagai daerah masukan, karena berisikan nilai yang ingin dibandingkan. 2. Nilai pinjaman diletakkan di luar daerah data table, dalam hal ini kita letakkan di sel C1. 3. Masukkan formula =PMT(A2/12;360;C1) pada sel C2. 4. Pilih Sel B2:C6 5. Pilih menu Data What-If Analysis Data Table...
6.
Masukkan $A$2 pada Column input cell, sesuai dengan formula yang dibuat pada langkah 3. Gambar berikut menunjukkan contoh lain jika diinginkan jumlah pinjaman yang berbeda.
Data Table juga bisa dibuat dengan menggunakan dua variabel input. Misalkan yang ingin divariasikan bukan hanya nilai bunga pinjaman tapi juga lama pinjaman. Gambar berikut menunjukkan worksheet yang dibutuhkan.
Untuk menghasilkan Data Table, lakukan prosedur berikut: 1. Pilih sel B2:F6 2. Pilih menu Data What-If Analysis Data Table... 3. Masukkan $A$2 pada Column input cell dan $B$1 sebagai Row input cell
Daftar Pustaka PT. Putera Handal Indotama, 2012, BelajarExcel.info, http://www.belajarexcel.info/2014/04/pewarnaan-conditional-formatting-padaexcel-2010.html diakses tanggal 13-11 -2014 jam 16:38)
