CONDITIONAL FORMATTING & PIVOT TABLE
Conditional Formatting • Merupakan suatu fitur dari MsExcel yang memudahkan untuk membuat highlight(tanda hal penting) berdasarkan rule(aturan2) tertentu dalam suatu lembar kerja. • Sebagai contoh, misalnya kita memiliki data penerimaan mahasiswa UB lima tahun berturut-turut. Kemudian kita akan memberi tanda pada data tersebut menggunakan conditional formatting. FAKULTAS FISIP FT FMIPA FIB FEB FK FILKOM
2010 750 800 780 800 825 800 700
2011 800 935 750 850 800 830 800
2012 950 875 850 900 830 860 850
2013 1050 888 900 950 860 820 830
2014 1000 1100 950 900 950 900 900
2015 1200 1050 1100 1050 1100 1050 1100
Conditional Formatting (Highlight rules) • Highlight rules merupakan fitur untuk memberi tanda berdasarkan rule (Greater, Less than, between, Equal to, Text Contains, Date Occuring, Duplicate Values, etc) dimana jika data memenuhi syarat tersebut diatas, maka data akan ditandai. • Sebagai contoh, misalnya kita memiliki data penerimaan mahasiswa UB lima tahun berturut-turut. Kemudian kita ingin membuat highlight data yang berada pada range 750 sampai 800 pada tahun 2010.
Conditional Formatting (Highlight rules) Langkah2nya 1. Pilih sel c3 sampai c9 2. Klik conditional formatting 3. Klik highlight cell rules 4. Klik between 5. Masukkan 750 sampai 800 6. Pilih warnanya 7. Klik ok
2 3
4
1 6 5
7
Output • Yang berwarna merah adalah jumlah penerimaan yang berada pada range antara 750 dan 800
Conditional Formatting (Top/bottom rules) • Top bottom rules merupakan fitur untuk memberi tanda berdasarkan aturan rangking (top 10 items, top 10 %, bottom 10 items, bottom 10 %, above average, below average, etc) dimana jika data memenuhi syarat tersebut diatas, maka data akan ditandai. • Sebagai contoh, misalnya kita memiliki data penerimaan mahasiswa UB lima tahun berturut-turut. Kemudian kita ingin membuat highlight data yang berada diatas rata-rata pada tahun 2011.
Conditional Formatting (Top/bottom rules) 2 3
4
1
5
Langkah2nya 1.Pilih sel D3 sampai D9 2.Klik conditional formatting 3.Klik Top/bottom cell rules 4.Klik Above Average 5.Pilih pewarnaan 6.Lalu klik ok
Output • Yang berwarna merah itu adalah data2 yang nilainya berada diatas rata-rata penerimaan pada tahun 2011
Conditional Formatting (Data Bars) • Data Bars adalah sebuah fitur untuk menampilkan jumlah data menjadi bentuk persegi panjang(bars) dimana panjang dari bars menunjukkan jumlahnya, jika jumlah semakin banyak maka bars akan semakin panjang. (Bars berbeda dengan histogram chart). • Misalkan kita ingin menampilkan data penerimaan tahun 2012 ke dalam bentuk bars.
Conditional Formatting (Data Bars) 2 3
1
4
1.Pilih sel E3 sampai E9 2.Klik conditional formatting 3.Klik Data Bars 4.Klik Gradient Fill
Conditional Formatting (Color Scales) • Color Scales adalah fungsi untuk memberi tanda berupa warnawarna, dimana jika nilainya terkecil maka akan diberi warna tertentu (ex. Merah) kemudian jika nilainya terbesar juga akan diberi nilai tertentu (ex. Hijau) sedangkan jika nilainya berada pada range juga akan diberi warna yang berada range antara hijau dan merah (ex. Kuning).
Conditional Formatting (Data Bars) 2
4 3
1
1.Pilih sel F3 sampai F9 2.Klik conditional formatting 3.Klik Color Scales 4.Klik Green yellow Red
Conditional Formatting (Icon Sets) • Icon Sets adalah fungsi untuk memberi tanda berupa icon, dimana jika nilainya terkecil maka akan diberi icon tertentu (ex. Icon Panah bawah) kemudian jika nilainya terbesar juga akan diberi nilai tertentu (ex. Icon Panah bawah) sedangkan jika nilainya berada pada range juga akan diberi warna yang berada range antara hijau dan merah (ex. Icon Panah diagonal).
Conditional Formatting (Icon Sets) Langkahnya sbb: 1.Pilih sel G3 sampai G9 2.Klik conditional formatting 3.Klik Icon Sets 4.Klik 4 arrows colored
2
4 3
1
Modifikasi dan Menghapus Rules • Kita juga bisa memodifikasi dan menghapus rules pada data yaitu dengan menyorot data yang sudah ada rules kemudian diklik quick analysis, lalu pilih formatting. • Kemudian pilih formatting yang diinginkan atau pilih clear format untuk menghapus rules nya
Charts (Grafik) • Charts adalah fungsi untuk menampilkan data yang awalnya angka menjadi bentuk grafik (pie, column, bar, scatter, etc). • Untuk membuat grafik langkahnya cukup mudah yaitu dengan memilih data kemudian klik di insert lalu pilih chart yang ada.
Contoh Charts • Langkah2nya sbb: 1.Pilih sel 2.Klik insert 3.Klik salah satu chart (column chart) 4.Pilih chart yang diinginkan lalu klik ok
2
3
1
4
Pivot Table • Pivot Table adalah fungsi dalam Excel untuk membuat laporan yang beraneka ragam berdasarkan kelompok data dengan mudah tanpa harus merubah data asli. • Sebagai contoh yaitu perusahaan waralaba fisip mart memiliki data penjualan sudah berbentuk excel. Perusahaan ini memiliki banyak cabang. Permasalahannya adalah kemudian si pemilik toko ingin mengetahui laporan penjualan sayur per cabang. • Bagaimanakah caranya?
Cara manual 1. Buka Excellnya lalu lakukan filter cabang=‘A’ dan namabarang =‘sayur’ 2. Kemudian hitung jumlah masing hasil filtrasi tersebut dan kopi ke dalam sheet baru hasilnya. 3. Ulangi langkah 1 ke cabang=‘B’ dst. 4. Ulangi langkah 2.
Cara Pivot Table • Pilih range sel nya. • Pilih insert pivot table • Lalu pilih new worksheet
Cara Pivot Table •Filter : pada kotak ini field akan digunakan sebagai filter yang mempengaruhi hasil data pada PivotTable. •Column : isi data dari field akan ditempatkan pada bagian kolom sesuai urutan. •Row : isi data dari field akan ditempatkan pada bagian baris sesuai urutan. •Values : nilai field yang terdapat pada kotak ini akan ditampilkan sebagai perhitungan summary dari count, sum, average, dan lain-lain.
Cara Pivot Table • Filter berisi field NamaKategori = ‘Sayur-sayuran’ artinya data yang diambil hanya yang kategorinya sayur. • Rows = berisi field nama_cabang artinya semua cabang akan muncul sekali saja di Rows. • Ʃ values = berisi field jumlah unit artinya data field jumlah_unit akan dihitung berdasarkan kelompok row labels dan nama kategorinya.
contoh laporan per cabang per jenis • Rows = berisi field Nama Kategori dan nama_cabang artinya akan dibuat kelompok berdasarkan kategori dan cabang akan di Rows. • Ʃ values = berisi field jumlah unit artinya data field jumlah_unit akan dihitung berdasarkan kelompok row labels dan nama kategorinya.
Grafik dalam pivot tables • Grafik juga dapat digunakan untuk merepresentasikan hasil pivot tables. • Caranya pilih insert pivot chart • Kemudian pilih jenis grafiknya.
Latihan • Buat laporan penjualan cabang dikelompokkan berdasarkan cabang dan kolomnya berisi kelompok buah!
• Buat laporan jumlah omzet(total penjualan) percabang! Cabang
Omzet
PHI Mini Market - Jakarta Pusat 01
14560
PHI Mini Market - Makassar 01
14560
PHI Mini Market - Surabaya 01
14559
Grand Total
43679
Latihan • Buat laporan penjualan sebaran produk berdasarkan nama barang dan cabangnya Nama Barang air mineral 600 ml alpukat 1 kg apel 1 kg Bawang Merah / Onion 1kg belimbing 1 kg bumbu fried chicken buncis 1 kg durian 1 kg
PHI Mini Market - Jakarta Pusat 01 PHI Mini Market - Makassar 01 PHI Mini Market - Surabaya 01 Grand Total 364 364 364 364 364 364 364 364 364 364 364 364 364 364 364 364 364 364 364 364 364 364 364 364
• Buat laporan grafik jumlah omzet(total penjualan) percabang!
1092 1092 1092 1092 1092 1092 1092 1092
