Modul Excel (Otkp) [PDF]

Citation preview

MODUL EXCEL Otomatisasi dan Tata Kelola Perkantoran (OTKP)



Sumber : https://www.latihanexcel.com



Belajar Microsoft Excel Intermediate (Tingkat Menengah) 1. Fungsi IF pada excel 2. Fungsi IF dengan banyak kondisi 3. Fungsi Vlookup 4. Fungsi Hlookup 5. Fungsi Left, Mid, Right untuk data text 6. Fungsi Upper, Lower dan Proper untuk data text 7. Fungsi Finansial 8. Fungsi Tanggal dengan DATEDIF 9. Fungsi Time untuk kalkulasi data Jam 10. Penamaan Range dengan Define Name 11. Mengatasi Error dengan Formula auditing 12. Memformat Cell dengan Conditional Formatting 13. Menggunakan Hyperlink di Excel 14. Customize Chart/Grafik 15. Menambahkan Sparkline 16. Belajar Sortir data Excel 17. Menggunakan Fungsi Subtotal 18. Filter data Excel 19. Custom Filter Excel 20. Advance Filter



Rumus IF pada Excel Rumus IF atau Fungsi IF adalah fungsi logika pada excel yang digunakan untuk membandingkan dua kondisi atau lebih, fungsi ini sangat sering digunakan dalam skenario untuk menyelesaikan berbagai perhitungan dalam microsoft excel. Fungsi IF bekerja dengan cara cek kondisi dan akan mengembalikan satu nilai yang akan di tampilkan pada cell, jika logika yang di uji bernilai benar (TRUE) maka akan ditampilkan nilai benar, sebaliknya jika kondisi atau logika yang diuji bernilai salah (FALSE) maka akan ditampilkan nilai salahnya. Penulisan Rumus IF Sebelum masuk ke penulisan fungsi atau rumus IF, anda diharapkan terlebih dahulu mengenal operator pembanding yang nantinya akan kita gunakan dalam penulisan rumus IF. 



= : Sama dengan







> : Lebih besar dari







< : Lebih kecil dari







>= : Lebih besar sama dengan







=70,”LULUS”,”GAGAL”)



1. 2. Tekan Enter untuk menjalankan formula. 3. Hasil adalah LULUS, Excel Akan menguji atau cek nilai cell F22 dan kondisinya benar maka akan ditampilkan Nilai benar (value_if_true). 4. Untuk mengisi hasil dibawahnya dapat menggunakan Autofill atau double klik tanda plus pada pojok kanan bawah cell G22.



Rumus IF Bertingkat (Nesting IF) Perhitungan logika yang lebih dari 2 kondisi atau If bertingkat? Sebagai contoh misal kondisinya seperti dibawah ini : 



Jika nilai lebih besar sama dengan 90 maka Hasilnya A







Jika nilai lebih besar sama dengan 70 maka Hasilnya B







Jika nilai lebih besar dari 60 maka Hasilnya C







Jika nilai kurang dari 60 maka Hasilnya D



Penulisan Rumus Excel IF Bertingkat Pada kasus diatas kita akan gunakan 4 kondisi dengan hasil A,B,C, dan D, dalam hal ini kita akan menuliskan IF bertingkat, bagaimana cara penulisannya if lebih dari 2? Syntax IF Bertingkat (Nesting IF) secara umum sebagai berikut :



=IF(Logical_test,Value_IF_true,IF(Logical_test,Value_IF_true,IF(Logical_test,Value_IF_true,... ...Value_IF_False)))



Keterangan : Untuk penulisan IF dengan kondisi lebih dari 2, pertama kita harus deklarasikan tiap-tiap kondisi kedalam rumus beserta nilai benarnya (value_if_true), kondisi terakhir nantinya kita dapat deklarasikan dirumus sebagai Nilai salah (value_if_false). Agar lebih mudah memahami IF bertingkat ada langkah yang sederhana dalam penulisan IF 1. Analisa jumlah kondisi yang akan dipakai “misal ada 5” 2. Jika kondisi nya ada 5 maka IF yang akan di tuliskan pada rumus adalah 4, kenapa 4? untuk penulisan IF berlaku rumus (n-1). jika kondisi ada 3 maka IFnya 2 kali, Jika kondisi ada 6 maka IFnya 5 kali dan seterusnya. 3. Kondisi terakhir kita dapat deklarasikan sebagai nilai salah (Value_if_false) 4. Agar tidak terjadi kesalahan penggunaan operator logika dalam satu formula harus konsisten, jika pada awal menggunakan tanda lebih besar “>” selanjutnya juga harus “>” atau sebaliknya. 5. Kurung tutup di tambahkan sesuai jumlah IF.



Contoh kasus : Seorang manager akan memberikan bonus kepada staffnya yang mempunyai nilai kinerja terbaik. Jika nilai kinerjanya > 80, maka bonusnya Motor, jika nilai kinerjanya antara 50 – 80, maka bonus Sepeda, dan jika kinerjanya < 50, tidak ada bonus Penyelesaian : Kita dapat analisa ada 3 kondisi pada kasus diatas yaitu 



Nilai >80, bonus motor







Nilai >50, bonus Sepeda







Nilai 80 bonus motor dan Nilai >50 bonus Sepeda, sedangkan Nilai 80,”MOTOR”,IF(D13>50,”SEPEDA”,”TIDAK



ADA



BONUS”)) 3. Tekan Enter untuk eksekusi formula. gunakan autofill untuk cell E14 sampai dengan



E24.



Fungsi IF untuk Data Text Penulisan rumus excel IF jika datanya text direkomendasikan dengan menuliskan semua kondisi kedalam rumus IF. dikarenakan penulisan text sangat rawan terjadi kesalahan penulisan. Operator logika yang digunakan untuk data text adalah 



= : sama dengan







>< : Tidak sama dengan



Contoh Kasus :mmUbahlah singkatan Merek HP berikut sesuai data diatas. 



SMS=”SAMSUNG”







NK=”NOKIA”







BB=”BLACKBERRY”







SN=”SONY”



Penyelesaian : 1. Letakan Pointer Pada cell “E10” 2. Ketikan Formula =IF(D10=”NK”,”NOKIA”,IF(D10=”SMS”,”SAMSUNG”,IF(D10=”BB”,”BLA CKBERRY”,IF(D10=”SN”,”SONY”,”NO DATA”)))) 3. Tekan Enter untuk eksekusi formula. gunakan autofill untuk cell E14 sampai dengan



E24.



Pada kode HP saya ubah kode



menjadi SMZ. maka otomatis akan mengambil value “NO DATA”. jika kita hanya menggunakan 3 IF saja dengan SN dijadikan Value IF False, maka ketika terjadi kesalahan penulisan maka otomatis akan mengambil nilai Value IF False yaitu “SONY”. Dari kasus ini fungsi IF juga kita dapat gunakan sebagai alat cek data. Untuk penggunaan IF yang sangat kompleks misal kita punya 30 kondisi bagaimana teknik yang paling mudah? apakah kita akan menuliskannya semua? ada teknik tersendiri untuk kasus ini. simak pada materi selanjutya. Terimakasih, semoga bermanfaat untuk semua sahabat LatihanExcel.com



Rumus VLOOKUP Excel Rumus Vlookup excel adalah Rumus excel yang digunakan untuk mengambil suatu nilai dari tabel sumber (tabel lain) yang akan dijadikan referensi kedalam tabel yang sedang dianalisa. Rumus Vlookup ini sangat populer sebagai rumus dasar yang harus anda pelajari jika anda bekerja dengan program excel. Penggunaan Rumus Vlookup dapat dijadikan solusi ketika kita kesulitan dalam penggunaan fungsi IF dengan banyak kondisi dan kriteria. dari banyak kondisi tersebut kita dapat buatkan satu tabel sebagai referensi yang kemudian kita bisa ambil nilainya menggunakan fungsi Vlookup. secara tidak langsung Fungsi vlookup dapat kita gunakan untuk menanggulangi kelemahan fungsi If yang hanya dapat menampung 64 kondisi saja.



Cara Penulisan Rumus Vlookup Syntax =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) Keterangan : 



VLOOKUP = Fungsi / Rumus Vlookup untuk mengambil data secara vertical (vertical lookup).







lookup_value = adalah nilai yang akan dicocokan dengan nilai yg ada pada table_array.







table_array = adalah tabel sumber yang akan diambil datanya.







col_index_num= adalah nomor kolom yg ada pada table_array







range_lookup = Metode pencarian dalam table sumber yang bernilai TRUE “1” atau FALSE “0”



Penggunaan Rumus vlookup excel



Penggunaan Rumus Vlookup Pada tabel diatas kita diminta untuk mengisi kolom gaji dan bonus dengan dengan mengambil data pada table array/tabel sumber menggunakan Rumus Vlookup. dari tabel



diatas kita dapat analisa terlebih dahulu sebelum menuliskan formula untuk kolom gaji dan bonus. Perhatikan gambar dibawah ini, sebemenggunakan Vlookup yang paling utama adalah kita harus menguasai cara pembacaan datanya.



Cara Pembacaan column_Index_number







Lookup_value harus berada paling kiri pada tabel array dan pada kasus ini adalah kolom golongan yang nilainya akan dicocokan.







Column_index_number dcara membacanya dimulai dari kolom paling kiri ke kanan. dari



gambar



diatas



Kolom



Golongan



Kolom



Gaji



adalah



adalah



dapat



kita



pahami



Column_index_number



Column_index_number



yang



bahwa



yang ke



ke 2,



1, dan



kolom Bonus adalah Column_index_number yang ke 3. 



Range lookup adalah metode pencarian data, nilainya TRUE atau FALSE. jika kita gunakan FALSE artinya jika data pada tabel array tidak ditemukan atau tidak ada yang cocok maka akan ditampilkan errorr N/A (exact match), jika kita gunakan TRUE maka ketika data tidak ditemukan maka akan ditampilkan nilai terdekatnya (approximate match).



Penyelesaian Kasus: 1. Letakan pointer pada cell “J24“ 2. Tuliskan Formula =VLOOKUP( 3. Masukan Lookup value adalah “I24” karena kolom golongan sebagai acuan/nilai yang akan dicocokan dengan kolom golongan pada tabel array. 4. Blok tabel array C24:E26, agar referensinya tidak berubah saat autofill kita dapat kunci



atau



absolutkan



menggunakan



shortcut



F4,



Refensinya menjadi $C$24:$E$26. jika anda belum tahu paham dengan pemahaman cell absolut dapat anda pelajari penguncian cell pada materi sebelumnya. 5. Karena kita akan mengisi kolom gaji maka column_index_Number adalah kolom yang ke 2, tuliskan angka 2. 6. Gunakan Range_lookup dengan type FALSE atau 0. 7. Secara lengkap formulanya adalah =VLOOKUP(I24,$C$24:$E$26,2,FALSE)



8. Tekan Enter untuk menjalankan formula. gunakan autofill atau copy paste Formula untuk cell dibawahnya. Hasilnya adalah sebagai berikut



Untuk



mengisi



kolom



Bonus



langkahnya



sama



yang



membedakan



hanya



nilai colom_index_Numbernya saja yaitu yang ke 3. secara lengkap Formulanya adalah : =VLOOKUP(I24,$C$24:$E$26,3,FALSE)



Fungsi HLOOKUP pada excel Fungsi Hlookup adalah fungsi yang digunakan untuk mengambil suatu nilai dari tabel lain yang akan dijadikan referensi dengan membaca data secara horisontal. Hlookup juga disebut horisontal lookup karena pembacaan tabel array secara horisontal, bedanya dengan Vlookup adalah pembacaan datanya pada tabel array secara vertical. untuk membedakan lihat gambar dibawah ini.



array VLOOKUP



array HLOOKUP



Secara umum penulisan fungsinya Hlookup sebagai berikut Syntax =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) Keterangan : 



HLOOKUP Fungsi hlookup untuk mengambil data secara horisontal







lookup_value = adalah nilai yang akan dicocokan dengan nilai yg ada pada table_array.







table_array = adalah tabel sumber yang akan diambil datanya.







row_index_num= adalah nomor baris yg ada pd table_array.







range_lookup = Metode pencarian dalam table sumber yang bernilai TRUE “1” atau FALSE “0”



Contoh Kasus



hlookup



Pada tabel diatas kita diminta untuk mengisi kolom gaji dan bonus dengan dengan mengambil data pada table array/tabel sumber menggunakan fungsi Hlookup. kita dapat analisa terlebih dahulu sebelum menuliskan formula untuk kolom gaji dan bonus. Perhatikan gambar dibawah ini :







Lookup_value harus berada paling atas pada tabel array dan pada kasus ini adalah kolom golongan yang nilainya akan dicocokan.







Pembacaan row_index_number dimulai dari baris paling atas ke bawah yaitu Golongan yang ke 1, Gaji yang ke 2, dan Bonus yang ke 3.







Range lookup adalah metode pencarian data, nilainya TRUE atau FALSE. jika kita gunakan FALSE artinya jika data pada tabel array tidak ditemukan atau tidak ada yang cocok maka akan Muncul error N/A, jika kita gunakan TRUE ketika data tidak ditemukan maka akan ditampilkan nilai terdekatnya.



Penyelesaian : 1. Letakan pointer pada cell “K24” 2. Tuliskan Formula =HLOOKUP( 3. Masukan Lookup value adalah cell “J24” “kolom golongan sebagai acuan/nilai yang akan diocokan” 4. Blok tabel array D23:F25, agar referensinya tidak berubah saat autofiil kita dapat absolutkan menggunakan shortcut F4 menjadi $D$23:$F$25. 5. Karena kita akan mengisi kolom gaji maka row_index_Number adalah kolom yang ke 2, inputkan nilai 2. 6. Gunakan Range_lookup FALSE.



7. Secara lengkap formulanya adalah =HLOOKUP(J24,$D$23:$F$25,2,FALSE) 8. Tekan Enter untuk menjalankan formula. gunakan autofill untuk cell dibawahnya.



Fungsi Text LEFT, MID, RIGHT untuk data Microsoft excel adalah program yang diprioritaskan sebagai pengolah data angka. bagaimana dengan data dalam bentuk text, excel menyediakan beberapa fungsi text yang dapat membantu anda mengolah data text. Seperti fungsi LEFT, MID, RIGHT, SUBSTITUTE, LEN, UPPER, LOWER, TRIM, AMPERSAND (&), PROPER dan lainnya.



Fungsi LEFT Fungsi ini digunakan untuk mengambil karakter (huruf atau tanda baca) dari sebelah kiri pada alamat cell sumber. Hasil yang ditampilkan berupa karakter yang kita ambil (hasil data text). Syntax =LEFT(text,[num_chars]) Keterangan : 



Text adalah alamat sel yang berisi data teks yang akan kita ambil karakternya dari sebelah kiri







[num_chars] adalah jumlah karakter yang diambil dari sebelah kiri. Jika tidak diisi, excel akan menganggap yang diambil hanya 1 karakter saja.



Contoh penggunaan



Fungsi left



FUNGSI RIGHT Fungsi ini digunakan untuk mengambil karakter (huruf atau tanda baca) dari sebelah kanan pada alamat cell sumber. Hasil yang ditampilkan berupa karakter yang kita ambil (hasil data text). Syntax =RIGHT(text,[num_chars]) keterangan 



Text adalah alamat sel yang berisi data teks yang akan kita ambil karakternya dari sebelah kanan







[num_chars] adalah jumlah karakter yang diambil dari sebelah kanan. Kalau ini tidak diisi, excel akan menganggap yang diambil hanya 1 karakter saja.Contoh penggunaan :



fungsi right



FUNGSI MID Fungsi MID ini digunakan untuk mengambil sejumlah karakter yang berada di tengah karakter lainnya. Hasil yang ditampilkan berupa karakter yang kita ambil (hasil data Text). Syntax =MID(text,start_num, [num_chars]) Keterangan 



Text adalah alamat sel yang berisi data teks yang akan kita ambil sebagian karakternya







Start_num adalah angka yang menunjukkan karakter akan diambil dari karakter ke berapa, dihitung dari kiri







[num_chars] adalah jumlah karakter yang akan diambil. Angka pada Start_num adalah karakter pertamanya yang diambil.



Contoh Penggunaan



Fungsi MID



FUNGSI SUBSTITUTE SUBSTITUE digunakan untuk menggantikan teks yang lama dengan teks yang baru. Perbedaanya dengan fungsi REPLACE, REPLACE digunakan untuk menggantikan bagian atau seluruh karakter teks dengan karakter teks lainnya pada suatu sel yang berisi teks pada posisi tertentu, maka SUBSTITUTE digunakan untuk menggantikan suatu kata/simbol/tanda baca yang ada pada suatu sel dengan suatu kata/simbol/tanda baca yang baru. Syntax =SUBSTITUTE(text,old_text, new_text, [instance_num]) 



Text adalah alamat sel / referensi sel yang berisi data teks yang akan kita rubah.







Old_text adalah teks lama yang akan di ganti. Sifatnya Case Sensitive (Besar kecilnya huruf diperhitungkan). Gunakan tanda petik 2 (“) untuk mengapit teks lama yang akan ganti







New_Text adalah teks baru yang menggantikan teks lama (Old_text), Gunakan tanda petik 2 (“) untuk mengapit teks barunya.







[instance_num] adalah nomor karakter yang akan diganti, jika teks mengandung lebih dari satu teks yang akan diganti



Contoh Penggunaan



Fungsi Substitute



Fungsi LOWER, UPPER, dan PROPER BY BUNG_BRIBIL · PUBLISHED MARCH 2, 2017 · UPDATED NOVEMBER 8, 2017



Pada materi sebelumnya telah dijelaskan beberapa fungsi text untuk mengambil karakter pada data excel menggunakan Left, mid dan right. Selanjutnya kita akan belajar fungsi Lower, Upper, dan proper untuk mengolah data text.



Fungsi UPPER Fungsi UPPER ini digunakan untuk merubah Seluruh isi sel menjadi huruf besar semua (Upper case) Syntax =UPPER(Text) Keterangan : 



Upper = Fungsi upper case







Text adalah alamat cell yang berisi teks yang akan dirubah menjadi huruf besar semua (Upper case)



Contoh penggunaan



Fungsi upper



FUNGSI LOWER Fungsi LOWER ini digunakan untuk merubah seluruh isi cell menjadi huruf kecil semua (Lower case) Syntax =LOWER(text) Keterangan 



Lower Fungsi lower case







Text adalah alamat cell yang berisi teks yang akan dirubah menjadi huruf besar semua (Upper case)



Contoh penggunaan



Fungsi lower



FUNGSI PROPER PROPER digunakan untuk merubah sekumpulan teks dalam satu cell menjadi huruf kapital pada setiap awal katanya dan karakter berikutnya akan dirubah menjadi huruf kecil semua (Title case) Syntax =PROPER(text) Keterangan: 



Proper = Fungsi untuk merubah awal kata menjadi huruf kapital.







Text adalah alamat cell yang berisi teks yang akan dirubah



Contoh penggunaan



Fungsi Proper



FUNGSI LEN LEN digunakan untuk menghitung jumlah/banyaknya karakter yang terdapat dalam satu alamat cell (satu sel). Yang dihitung adalah jumlah karakter artinya Huruf dan tanda baca (spasi, koma, titik koma, simbol) akan dibaca sebagai karakter. Syntax =LEN(text)



Keterangan 



LEN adalah Fungsi Untuk menghitung jumlah karakter







Text adalah alamat cell yang berisi teks yang akan dihitung jumlah karakternya.



Contoh penggunaan



Fungsi Len



Fungsi FIND Fungsi FIND digunakan untuk menampilkan posisi suatu karakter ( huruf, tanda baca ) yang akan dicari pada suatu cell yang berisi teks. Besar kecilnya karakter huruf sangat diperhatikan artinya Case Sensitive. Hasil yang ditampilkan akan menunjukkan karakter tersebut berada pada posisi yang ke berapa (ouputnya berupa angka). Syntax =FIND(find_text,within_text,[start_num]) keterangan 



Find_text adalah karakter yang akan dicari. Gunakan tanda kutip 2 (“) untuk mengapit karakter yang dicari, jika ditulis langsung, contoh : “A”







Within_text adalah klik alamat cell sumber yang berisi karakter yang akan dicari







[Start_num] adalah karakter yang dicari dihitung dari karakter ke berapa (hitung dari kiri (Depan)). Kalau tidak diisi, maka pencarian akan dilakukan dari karakter pertama (paling kiri)



Contoh penggunaan



Fungsi Find



Fungsi Finansial pada excel Anda pernah melakukan peminjaman uang atau kredit di bank, pastinya anda tidak asing dengan suku bunga, jumlah angsuran bulanan, dan jumlah periode angsuran. Excel menyediakan Fungsi yang dapat anda digunakan untuk menghitung informasi pinjaman atau kredit yang berkaitan dengan pengolah data keuangan. fungsi ini dikelompokan kedalam fungsi Finansial



Fungsi Finansial PMT Fungsi PMT digunakan untuk menghitung nilai setiap kali pembayaran angsuran dengan bunga efektif selama periode waktu tertentu. Syntax =PMT(rate, nper, pv, fv, type) Keterangan : 



Rate adalah tingkat suku bunga yang berlaku.







nper adalah jumlah periode pembayaran angsuran pinjaman (sebagai contoh 10 tahun pinjaman maka menjadi 120 bulan sebagai nper )







pv adalah present value (nilai pinjaman saat ini) suatu pinjaman







fv adalah future value (nilai dimasa depan) dari pinjaman







type adalah 0 jika pembayaran pada akhir periode atau 1 jika diawal periode.



Contoh Penggunaan Hitung cicilan perbulan untuk pinjaman senilai Rp. 40,000,000,- jangka waktu 10 bulan dengan bunga 1% perbulan. berapa biaya pembayaran per bulan setiap kali angsuran? Penyelesaian



Fungsi Pmt



Jadi jumlah cicilan yang harus dibayarkan perbulan adalah 3.553.952.



Fungsi RATE Fungsi Rate merupakan fungsi finansial yang digunakan untuk menghitung suku bunga pinjaman dengan jumlah angsuran yang sudah ditetapkan Syntax =RATE(nper, pmt, pv, type) Keterangan : 



Nper adalah jumlah periode pembayaran







pmt adalah jumlah pembayaran setiap periode







PV adalah present value (nilai pinjaman saat ini) suatu pinjaman







type adalah 0 jika pembayaran pada akhir periode atau 1 jika diawal periode



Contoh penggunaan Hitung berapa suku bunga untuk pinjaman senilai Rp. 40,000,000,- jangka waktu 2 tahun dan angsuran 1.000.000 Penyelesaian :



fungsi rate



Fungsi NPER Nper merupakan fungsi finansial yang digunakan untuk menghitung banyaknya periode yang digunakan dalam ansuran dengan bunga yang tetap. Syntax =NPER(rate, pmt, pv, type) keterangan 



rate adalah suku bunga tetap







pmt adalah jumlah pembayaran setiap periode







PV adalah present value (nilai pinjaman saat ini) suatu pinjaman







type adalah 0 jika pembayaran pada akhir periode atau 1 jika diawal periode



Contoh kasus Hitung periode pembayaran untuk pinjaman senilai Rp. 40,000,000,- dengan angsuran perbulan 1.000.000 dan bunga 4%/tahun Penyelesaian



Fungsi Nper



Hasilnya adalah jumlah periode yang harus dibayarkan adalah 45,6 bulan. Catatan : Dalam pengggunaan fungsi finansial terdapat angka negatif dan positi. artinya adalah jika menggunakan nilai negatif berarti nilai yang harus dibayarkan. nilai positif adalah nilai yang diterima.contoh PMT nilainya adala (-), PV nilainya adlah positif.



Rumus DATEDIF untuk Mencari Selisih Antara 2 tanggal Fungsi atau Rumus DATEDIF sangat dipakai dalam perhitungan excel terutama untuk menghitung masa kerja dan menghitung umur seseorang. Fungsi DATEDIF sudah ada dari excel versi (2003), fungsi ini digunakan untuk mencari selisih dari 2 tanggal, selisih yang dicari dapat berupa Jumlah Tahunnya, jumlah bulannya, jumlah harinya, jumlah bulan yang sudah terlewati dan jumlah hari yang sudah terlewati dari dua tanggal tersebut.



Cara Penggunaan Fungsi DATEDIF Syntax : =DATEDIF ( start_date, Finish_date, interval ) =DATEDIF



(



start_date,



Finish_date,



“Y”



)



=DATEDIF



(



start_date,



Finish_date,



“M”



)



=DATEDIF



(



start_date,



Finish_date,



“D”



)



=DATEDIF



(



“YM”



)



start_date,



Finish_date,



=DATEDIF ( start_date, Finish_date, “MD” ) Penjelasan Rumus DATEDIF : 



Start_Date adalah alamat cell atau data tanggal awalnya







Finish_date adalah alamat cell atau data tanggal akhir/finishnya







Interval parameter yang dicari tahun,bulan,atau hari







“Y” yang dicari adalah menentukan selisih tahunnya (jumlah tahunnya) dari kedua tanggal tersebut







“M” yang dicari adalah menentukan selisih bulannya (jumlah bulannya) dari kedua tanggal tersebut







“D” yang dicari adalah menentukan selisih harinya (jumlah harinya) dari kedua tanggal tersebut







“YM” yang dicari adalah menentukan perbedaan bulan yang sudah terlewati (tanpa menghitung tahunnya / mengabaikan komponen tahun)







“YD” yang dicari adalah menentukan perbedaan hari (tanpa menghitung tahunnya / mengabaikan komponen tahun)







“MD” yang dicari adalah menentukan perbedaan hari yang sudah terlewati (tanpa menghitung tahun dan bulan”



Menghitung umur menggunakan rumus Datedif Hitunglah selisih antara 2 tanggal untuk menghitung umur berdasarkan tabel dibawah ini.







Untuk



mengitung



selisih



tahun



saja



anda



dapat



menuliskan



formula



Bulan



saja



anda



dapat



menuliskan



formula



dapat



menuliskan



formula



menuliskan



formula



=DATEDIF(A2,B2,”Y”) 



Untuk



mengitung



selisih



=DATEDIF(32,B3,”M”) 



Untuk



mengitung



selisih



hari



saja



anda



=DATEDIF(A4,B4,”D”) 



Untuk



mengitung



sisa



bulan



dalam



interval



dapat



=DATEDIF(A5,B5,”YM”) 



Untuk mengitung sisa hari dalam interval dengan mengabaikan bulan anda dapat menuliskan formula =DATEDIF(A6,B6,”YD”) “selisih bisa lebih dari 30 hari, tidak di konversi ke dalam bulan”







Untuk mengitung sisa hari dalam interval anda dapat menuliskan formula =DATEDIF(A7,B7,”MD”)



Anda juga dapat menggunakan simbol “&” (ampersand) untuk menggabungkan beberapa formula menjadi satu untuk menghasilkan jumlah tahun, bulan dan harinya pada cell C8 =DATEDIF(A8,B8,”Y”)&”



tahun”&DATEDIF(A8,B8,”YM”)&”



bulan”&DATEDIF(A7,B7,”MD”)” hari” Yang perlu diperhatikan hasil pada cell C8 diatas adalah berupa teks, karena sudah digabungkan dengan symbol “&”. Kalau ingin hasilnya berupa data angka, sebaiknya tidak menggunakan “&” untuk menambahkan keterangan tahun, bulan atau hari, gunakan custom format cell 0 ”tahun” atau 0 “Bulan” atau 0 “hari”. untuk bahasan materi custom format cell sudah kami bahas sebelumnya anda dapat ikuti tautan atau gunakan search pada situs ini.



3 Cara Menghitung selisih Jam (Rumus Jam Excel) Setelah anda mempelajari Fungsi tanggal di excel untuk mencari selisih hari antara 2 tanggal, selanjutnya anda dapat mempelajari fungsi / rumus jam excel. Menghitung selisih jam di microsoft excel sangat berbeda dari menghitung selisih tanggal. Ada beberapa fungsi Date and time yang dapat kita gunakan yaitu Hour, minute, dan Seconds. salah satu contoh Kegunaan dari fungsi ini adalah kita dapat gunakan untuk menghitung jam kerja karyawan, menghitung jam lembur karyawan, dan lain sebagainya yang pastinya berkaitan dengan data jam.



Menghitung Selisih Jam kerja di Excel Ada berbagai cara menghitung selisih jam dengan rumus jam excel. Cara yang pertama anda dapat menghitung dengan pengurangan Biasa dan cara ke 2 anda dapat gunakan Fungsi yang sudah disediakan di excel. sebagai contoh kasus hitung jam kerja karyawan dari tabel di bawah ini.



Dari tabel diatas kita diminta untuk menghitung selisih jam kerja karyawan kemudian kita konversikan ke dalam decimal.



Menghitung Selisih Jam “Cara 1” Cara pertama anda dapat lakukan dengan rumus pengurangan biasa. 1. Letakan kursor di cell E8 2. Tuliskan Rumus =Jam pulang – jam masuk =D8-C8 3. Tekan Enter kelemahan dengan pengurangan biasa ini adalah jika jam pulang melewati hari berikutnya. sebagai contoh dari tabel diatas untuk pegawai LITA dan LUSI. jika kita



menggunakan pengurangan biasa selisih jam tidakbisa di temukan. Solusinya kita dapat gunakan Cara 2.



Menghitung Selisih Jam “Cara 2” Untuk cara ke-2 ini anda dapat tambahkan Rumus IF atau pada jam masuk ditambahkan +1. =Jam



Pulang+1-Jam



Masuk



atau



=Jam



Pulang-Jam



Masuk+IF(Jam



masuk>Jam pulang,1) 



Letakan kursor di cell E8







Tuliskan Rumus =D8+1-C8 atau =D8-C8+IF(C8>D8,1)







Tekan Enter



Penambahan angka 1 ini beefungsi untuk mengabaikan jam jika melebihi 1 hari (sudah melewati hari yang berbeda).



Menghitung Selisih Jam “Cara 3” Cara ketiga ini kita akan menggunakan fungsi Jam di excel yaitu : 



Hour = Mengambil nilai jam pada data jam







Minute = Mengambil nilai Menit pada data jam







Second = mengambil nilai detik pada data jam



Berikut cara penggunaanya : 1. Letakan kursor di cell E8 2. Tuliskan



Rumus



=HOUR(D8-C8)&”



Jam



“&MINUTE(D8-C8)&”



Menit



“&SECOND(D8-C8)&” Detik” 3. Tekan Enter Penggunaan dari formula ini akan menghasilkan data yang sifatnya adalah teks. anda dapat memilih 3 cara ini sesuai dengan kebutuhan.



Cara Membuat Nama Range di Excel Program Microsoft Excel memungkinkan anda untuk memberikan nama untuk sebuah cell ataupun range name range) sehingga memudahkan anda untuk memanggil nilai yang terdapat pada cell tersebut atau berpindah dari satu cell ke lokasi cell lain yang sudah anda berikan nama range, fasilitas excel ini bernama Define name. fungsi dari name manager selain untuk nafigasi antar cell anda juga dapat gunakan untuk referensi range didalam rumus atau fungsi excel. Pada materi kali ini anda akan mempelajari cara pembuatan, penggunaan nama cell, dan mengelola nama cell/range.



Cara membuat nama range di excel Ada 2 cara untuk pemeberian nama cell dan range. cara paling mudah adalah anda dapat gunakan name box yang terletak di atas cell A1. cara kedua adalah anda dapat gunakan menu Define name pada tab Formulas. CARA 1 Untuk memberikan nama range dapat dilakukan dengan cara berikut :



1. Klik Cell atau Seleksi range yang ingin diberikan nama, disini kita seleksi range “C23:E26” 2. Kemudian klik Name Box 3. Tuliskan



nama



range



misal



“Tabel_gaji”



“Perlu diperhatikan bahwa nama yang diberikan tidak boleh sama dengan nama cell yang merupakan perpaduan kolom dan baris seperti “A200”, tidak boleh ada spasi pada penulisan nama cell dan tidak boleh diawali dengan angka” 4. Tekan Enter



CARA 2 Untuk cara ke-2 memberikan nama range dapat dilakukan dengan cara berikut :



1. Masuk ke Tab Formulas pilih Define name. 2. Pada dialog box lengkapi seperti gambar diatas. 3. Pada kolom Name isi dengan nama yang diinginkan : “Tabel_gaji” 4. Pada kolom Scope pilih : Workbook “scope ini adalah ruang lingkup range ini dapat dipanggil atau direferensikan. 5. pada kolom Comment ini optional kita bisa kosongkan atau isi : Tabel gaji karyawan Refers to : VLOOKUP 6. Kemudian Klik OK



Penggunaan Nama Range dalam Excel Penamaan range di excel sangat bermanfaat ketika anda bekerja dengan menuliskan rumus dengan kriteria range yang panjang. dalam hal ini sebagai contoh untuk Penulisan rumus VLOOKUP / HLOOKUP dengan tabel array yang besar. ketika tabel array tersebut kita masukan kedalam rumus kita harus blok tabel tersebut. berikut ini cara penggunaanya dengan Fungsi VLOOKUP. Ketika kita sudah membuat nama range Tabel_gaji



dengan cara diatas kita dapat



manfaatkan untuk penulisan formula VLOOKUP. jika anda belum memahami fungsi Vlookup anda dapat belajar pada artikel kami sebelumnya “BELAJAR VLOOKUP EXCEL” Cara penulisan Syntax =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 1. Letakan kursor pada cell yang akan kita cari hasilnya. 2. Tuliskan formula Vlookup dengan syntax diatas. 3. ketika anda memberikan parameter/range pada Table array anda cukup tulis nama range yaitu Tabel_gaji.



4. Secara lengkap Rumus =VLOOKUP(I24,Tabel_gaji,3,False)



Dari contoh penggunaan nama range diatas, sebenarnya kita memanfaatkan nama tabel yang sudah kita buat untuk digunakan dalam rumus yaitu dengan cara memanggil nama tabel tersebut. untuk memanggilnya cukup tuliskan nama tabelnya saja. Manfaat Penamaan range di excel. 1. Meminimalisir kesalahan pada saat mereferensikan range dalam formula 2. Memudahkan Penulisan Rumus tanpa harus blok range atau tabel 3. menyederhanakan bentuk formula. 4. Sebagai alamat Hyperlink Untuk pemanfaatan sebagai hyperlink dapat anda pelajari pada materi selanjutnya.



Cara Mengatasi Rumus ERROR di Excel Bekerja dengan Formula dan Fungsi di Microsoft Excel seringkali kita mengalami kesalahan pada saat menuliskannya. Microsoft excel menyediakan berbagai fasilitas untuk penggunanya untuk meminimalisir kesalahan pada saat menuliskan fungsi atau rumus yang digunakan. Salah satu fasilitas rumus ini bernama Tooltip fungsi dari fitur ini adalah menampilkan argumen yang dibutuhkan dalam penulisan Fungsi. Bagaimana Rumus error ini dapat kita perbaiki? mari kita simak ulasanya.



Mengenal Jenis Error di Microsoft Excel Sebelum kita menghapus atau edit rumus error ada baiknya kita mengenal jenis-jenis error pada microsoft excel. berikut daftar Pesan kesalahan yang sering munul ketika kita bekerja dengan excel. 



#NULL! = Pesan error dikarenakan kesalahan pada saat memberikan argumen range/cell.







#DIV/0! = Pesan error yang muncul karena membagi sebuah nilai angka dengan angka Nol.







#VALUE! = Pesan Error yang muncul karena kesalahan berkaitan dengan jenis data. contoh (Kalkulasi Teks dengan Number)







#REF! = Pesan Error yang muncul di karenakan rumus kehilangan cell referensinya.







#NAME? =Pesan Error yang terjadi karena kesalahan penulisan fungsi atau penulisan nama range (define name)







#NUM! = Pesan Error ini dikarenakan rumus menghasilkan nilai numeric yang tidak valid atau melebihi batasan excel







#NA. = Pesan Error ini dikarenakan rumus tidak menemukan hasil yang dicari (Not Available)



Cara mengatasi rumus error Ada



berbagai



fasiltas



excel



untuk



menelusuri



kesalahan



rumus



excel.



Pada



tab Formulas terdapat satu group bernama Formula auditing. dalam group ini terdapat tools yang dapat anda manfaatkan untuk menelusuri error dalam suatu rumus atau fungsi excel.



Menggunakan Evaluate Formula Evaluate formula dapat anda gunakan untuk melihat rumus error dengan cara melihat proses perhitungan yang dikerjakan dala satu rumus/formula. berikut cara penggunaanya :



1. Letakan cell pada Rumus yang error 2. Masuk ke tab Formulas Kemudian klik Evaluate Formula 3. Untuk mencari letak kesalahan rumus Klik tombol Evaluate. tools ini akan cek tiap-tiap argumen dalam rumus. 4. Pada form evaluate formula diatas terjadi error #Name pada saat menuliskan Fungsi DATEDIF. Pada argumen terakhir terjadi kesalahan penulisan.



Menggunakan Trace Precedent dan Trace Dependent Fasilitas yang ke-2 adalah menggunakan trace precedent dan dependent, tools ini berfungsi memberikan petunjuk arah terhadap referensi cell yang digunakan dalam suatu rumus.







Trace Dependent : Menampilkan referensi cell yang digunakan dalam suatu rumus







Trace Precedents : Akan Menampilkan petunjuk arah jika cell tersebut direferensikan dalam suatu rumus.



Cara Penggunaanya 1. Letakan cell pada Rumus yang error 2. Masuk ke tab Formulas Kemudian klik Trace Precendent / Dependents 3. Cell Rumus akan Memberikan petunjuk arah berwarna biru menunjuk referensi cell. 4. Untuk menghapus arah dapat menggunakan Remove Arow



Conditional Formatting Pada Excel Lengkap Microsoft Excel mempunyai banyak sekali fitur yang dapat memudahkan penggunanya, salah satunya adalah conditional formatting. Fasilitas ini mirip Format Cell pada excel akan tetapi mempunyai fungsi yang berbeda. Conditional formatting pada excel adalah fitur yang berfungsi untuk memberikan format pewarnaan pada cell sesuai dengan kondisi/kriteria tertentu. Conditional formatting dapat memformat tipe data dalam bentuk number ataupun text semuanya dapat dengan mudah kita format sesuai kebutuhan. sebagian besar user excel, mewarnai suatu cell secara manual (data difilter kemudian diberi warna sendiri secara manual). jika datanya hanya beberapa cell saja mungkin masih bisa manual tapi kalau datanya sudah ribuan akan sangat merepotkan bukan?



Bagian Pemformattan cell meliputi 



Font (Type, size, dan color)







Shading (Warna background)







Border (garis pembatas)



Tipe Conditional Formatting pada Excel



Ada 5 type conditional formatting pada excel yang dapat kita gunakan 1. Highlight Cell Rules, Memformat data dengan syarat lebih besar dari, kurang dari, sama dengan, atau antara. 2. Top/Bottom Rules, Memformat data dengan syarat 10 tertinggi atau terendah. 3. Data Bars, Memformat data dengan warna bar dengan syarat dari nilai terendah sampai tertinggi secara otomatis. 4. Color Scales, Memformat data berdasarkan skala warna yang berbeda dengan syarat dari nilai terendah sampai tertinggi. 5. Icon Sets, Memformat data dengan icon/gambar berdasarkan dengan kondisi tertentu.



Cara Penggunaan Conditional Formatting pada excel Contoh Kasus : Berikan pewarnaan pada tabel dibawah ini untuk kolom gaji dengan kondisi 



Berikan Warna hijau untuk gaji lebih besar atau sama dengan 4.000.000







Berikan Warna kuning untuk gaji lebih besar atau sama dengan 2.000.000







berikan Warna merah untuk gaji dibawa 2.000.000



Penyelesaian 1. Blok range kolom gaji (Nilainya saja) 2. Pada tab Home pilih Conditional formatting. 3. Pemberian warna cell dapat gunakan highlight cell rules, pilih greather than. 4. Pada dialog box ketikan angka 4000000 dengan tipe format yang dapat anda pilih sesuai kebutuhan



default format



pilihan format default hanya ada beberapa saja, anda dapat gunakan custom format pada pilihan paling bawah. 5. Pada custom format cell, klik tab fill pilih bacground warna hijau kemudian tekan OK.



custom format



Untuk mewarnai warna kuning dan merah caranya sama dengan step diatas, dalam keadaan kolom gaji yang masih terseleksi ulangi langkah 2-5. dengan hasil pewarnaanya adalah berikut ini.



Hasil conditional formatting



Edit Conditional Formatting Setelah anda memformat cell dengan conditional formatting, excel akan menyimpan pengaturan didalam rules manager. didalam kita dapat menambahkan rules baru, edit rules yang telah ada, dan hapu rules. untuk mengakses rules manager masuk ke tab Home pilih conditional formatting kemudian pilih manage rules.



rules manager



Anda dapat edit salah satu rules misalnya adalah rules warna kuning diganti menjadi warna orange dan nilainya bukan diatas 2.000.000 tetapi 2.500.000. untuk editing rules ikuti prosedur berikut ini: 1. Pada rules manager klik rules warna kuning 2. Pilih edit rule 3. Pada Window edit formatting rule anda dapat ubah nilai 2.000.000 menjadi 2.500.000 dan warna dapat diubah melalui tombol format. ganti menjadi warna orange. 4. Klik OK untuk menyimpan perubahan.



Hapus Conditional Formatting Untuk menghapus conditional formatting anda dapat melakukanya menggunakan dua cara. cara pertama dapat anda lakukan pada rules manager, cara kedua anda dapat menggunakan clear rules pada conditional formatting tab home. lakukan prosedur berikut ini. 1. Seleksi range pada kolom gaji 2. Masuk ke conditional formatting pilih clear rule. 3. Pilih Clear rules from selected cell. untuk menghapus semua conditional formatting dalam satu worksheet anda dapat memilih clear rules from entire sheets.



clear rule



Batasan conditional formatting pada excel 2013 adalah tak terbatas, anda dapat memberikanya sebanyak banyaknya, tetapi saya sarankan jangan terlalu banyak menambahkan conditioal formatting karena bukannya lebih mudah membaca datanya tetapi malah membingungkan dalam membaca data. gunakan sesuai kebutuhan.



Cara Membuat Hyperlink di Excel Hyperlink di excel berfungsi untuk mempermudah akses cepat kedalam suatu informasi, baik berupa alamat cell, range, alamat website atau alamat email. Hyperlink biasanya juga digunakan untuk memudahkan navigasi di excel agar lebih mudah mengakses suatu data. untuk menggunankan hyperlink anda juga dapat menggunakan suatu objek yang didalamnya disisipkan link ke dalam suatu informasi. Berikut ini cara membuat hyperlink di excel. untuk mengelola hyperlink anda dapat akses di Tab Insert kemudian pilih Link.



Cara membuat Hyperlink Dalam Workbook Excel Anda berbagai fitur hyperlink di microsoft excel yang dapat kita manfaatkan, type hyperlink ini ruang lingkupnya (scope) sebagai berikut : 



Existing file or Web Page, Hyperlink ini dapat kita gunakan untuk ekses file hardisk komputer atau alamat web, anda dapat browse file dalam folder atau tuliskan alamat web pada kolom address.







Place in this Document, Hyperlink ini digunakan untuk akses cepat dalam satu workbook. anda dapat membuat link untuk bernavigasi antar halaman (sheets).







Create Doument, Hyperlink ini digunakan untuk membuat file excel baru. anda dapat mendefinisikan nama file baru yang akan dibuat.







Email Address, Hyperlink ini digunakan untuk membuat email baru ke alamat emailyang telah di tentukan, anda dapat mengisi alamat email di kolom address.



Cara Membuat Hyperlink antar halaman Excel Penggunaan hyperlink di excel sangat bermanfaat ketika kita menjadikan lembar kerja sebagai laporan. kita dapat bernavigasi dengan mudah antar halaman excel.



Langkah membuat hyperlink : 1. Buka file latihan “hyperlink” 2. Pada halaman Menu utama klik cell B3 (“Report JAN) 3. Masuk ke tab Insert pilih Link 4. Link to : Place In this Document 5. Cell Reference : Pilih sheet JAN 6. Klik OK 7. Ulangi step diatas untuk Halam FEB.



Cara membuat Hyperlink excel ke Word / Powerpoint (cell static) Hyperlink dari excel ke word / Powerpoint memungkinkan anda untuk menampilkan file excel di microsoft word atau pada saat presentasi dengan Powerpoint. ketika kita



mereferensikan file excel didalam hyperlink Ms word,excel akan mereferensikan alamat cell terakhir dimana workbook terahir disimpan. agar file excel selalu menampilkan alamat cell yang statis dan selalu menuju alamat cell yang sama, kita dapat memanfaatkan Fitur Define name untuk hyperlink halaman excel. Pengaturan hyperlink di file excel :



1. Buatlah



Define



name



di



excel



untuk



halaman



yang



akan



di



tuju.



Seleksi cell A1, Pada tab Formulas pilih Define name. 2. Beri nama Define name “alamat_cell” dengan Refers to =“JAN!$A$1“ 3. Klik OK 4. Dengan pengaturan ini hyperlink akan selalu menuju pada cell A1 sheets JAN. Pengaturan hyperlink di Ms Word : Pada lembar kerja Ms word. Bualah satu objek boleh berupa tulisan atau gambar. kemudian sisipkan hyperlink didalam objek tersebut.



1. Pada object di word sisipkan hyperlink dengan cara klik kanan pilih Link 2. Browse file excel yang akan di tuju. 3. Pada akhir address file tambahkan “#alamat_cell”



Penggunaan define name ini bertujuan untuk memberikan suatu alamat cell statis agar nantinya ketika dipanggil oleh file lain selalu menuju halaman yang sama yaitu Sheets JAN Cell A1.



Edit Grafik Microsoft Excel Grafik adalah visualisasi dari sederetan data berupa angka untuk memudahkan kita dalam menganalisa data. grafik pada excel biasanya disebut sebagai chart. setelah anda belajar bagaimana cara menambahkan grafik selanjutnya anda dapat edit grafik yang telah anda buat dengan menambahkan elemen pendukung dalam grafik.



Element Pendukung Grafik Setelah anda menambahkan grafik pada workbook excel, anda dapat menambahkan elemen-elemen pendukung untuk tampilan grafik tersebut. untuk memudahkan analisa data dapat anda tambahkan trendline, data table,data label dan elemen lainya. Fitur pada chart excel menyediakan beberapa tampilan layout standar yang dapat anda gunakan untuk merubah semua tampilan sesuai dengan kebutuhan.



Edit grafik dengan merubah tipe grafik Untuk merubah grafik ikuti prosedur berikut ini 1. Klik pada grafik. berfungsi untuk menampilkan menu chart tools 2. Pada tab design pilih change chart type. 3. Akan tampil Window change chart type. pada window ini akan tampil semua type grafik yang ada di excel. 4. Ubah ke bentuk line.



5. Klik Ok.



Cara menambahkan Trend line Elemen grafik standar ketika menambahkan chart yaitu axis, legend data series. Elemen chart



lainnnya



tidak



ditambahkan



secara



otomatis



pada



chart,



anda



dapat



menambahkannya sendiri sesuai dengan keinginan anda.



Cara Menggunakan Sparklines di Excel Sparklines adalah salah satu fitur microsoft excel yang berfungsi untuk menampilkan data dalam bentuk grafik yang sederhana. Sparklines di excel sering digunakan dalam penyajian data dalam bentuk Dashboard biasanya digunakan sebagai alat untuk menampilkan fluktuasi atau pergerakan suatu data. Sparkline pertama kali diperkenalkan pada microsoft Excel 2010 hingga pada versi terahkir 2016 masih dipertahankan.



Tipe Sparklines Ada 3 Type Sparkline yang dapat anda gunakan sesuai dengan tipe data yang ada : 1. Lines : Tipe sparklines ini dapat anda gunakan untuk tipe data number dengan bentuk garis. 2. Column : Tipe sparklines ini dapat anda gunakan untuk tipe data number dengan bentuk diagram batang. 3. Win/Loss : Tipe sparklines ini dapat anda gunakan untuk tipe data number dengan nilai positif dan negatif



Cara Penggunaan Sparklines di Excel



Sebagai objek di microsoft excel Sparklines sangat berbeda penggunaannya dengan Chart, sparklines hanya dapat di tempatkan pada single cell, dan data sumbernya diambil dari 1 data range kolom atau baris saja. berbeda dengan chart kita dapat pindahkan kemanapun dan data dapat di ambil dari suatu tabel dengan banyak baris dan kolom. berikut cara penggunaan sparklines :



1. Letakan kursor pada cell H3 sebagai tempat sparklines 2. Masuk ke tab Insert pilih Lines 3. Seleksi Data Range B3:G3 4. Location Range H3 5. Klik OK 6. Gunakan Autofill untuk data dibawahnya. Anda juga dapat gunakan tipe sparklines Column atau win/loss dengan cara di atas.



Edit Sparklines Agar tampilan Sparklines lebih menarik anda dapat melakukan customisasi pada objek. menambahkan properti pada sparklines dapat di lakukan dengan cara sebagai berikut : 1. letakan pointer pada objek Sparklines 2. Klik Pada Contextual Tab Design dan tambahkan beberapa Point marker.



Pada Group Shows anda dapat menambahkan opsi untuk menampilkan titik pada sparklines. High Point untuk menampilkan point tertinggi, low point untukterendah dan seterusnya. Agar sparklines terlihat lebih menarik kita dapat ubah style pewarnaan pada group Style, dengan edit warna pada menu marker color.



Tips Menggunakan Sparklines 



Agar terlihat lebih besar dan mudah dalam analisa datanya kita dapat letakan Sparklines pada Cell yang telah di Merge.







Untuk data range yang terdapat angka positif dan negatifgunakan tipe win/loss.







Anda juga dapat inputkan teks dalam sparklines dengan menambahkanya langsung dengan mengatur alignment. contoh tampilan dibawah ini.



Cara Mengurutkan Data di Excel (Sorting Data) Mengurutkan data di excel sering sekali dipakai dalam pekerjaan sehari-hari untuk keperluan analisa data. Fasilitas ini mempunya fungsi mengurutkan data dengan 2 cara yaitu Ascending dan Descending. Mengurutkan di excel dapat anda lakukan untuk berbagai tipe data Numeric (angka),Text (Teks), Date (tanggal), Time (jam), dan lain sebagainya. Program Excel akan secara otomatis membaca tipe data tersebut. secara garis besar sorting menggunakan 2 cara :



1. Ascending : Mengurutkan data dengan urutan Terbesar ke Terkecil / Tertinggi ke Terendah (A To Z) 2. Descending : Mengurutkan data dengan urutan Terkecil ke Terbesar / Terendah ke Tertinggi (Z To A)



Cara mengurutkan data di excel Untuk menggunakan sort anda dapat akses fasilitas ini di Tab Data pada Group Sort & Filter. Kemudian bukalah lembar kerja excel anda. untuk file latihan dapat anda unduh di halaman download LatihanExcel.com.



Dari



data



diatas



untuk



melakukan



sorting



cara Ascending. langkahnya berikut ini : 1. Letakan kursor pada kolom “G” di baris manapun. 2. masuk Ke tab Data Pilih A-Z



pada



kolom



“Qty”



dengan



3. Data akan secara otomatis diurutkan berdasarkan kolom Qty dengan data terkecil ke besar 4. Catatan Ketika data diurutkan secara otomatis kolom yang lain dalam satu tabel akan mengikuti sesuai baris datanya. Berikut tampilan data setelah data di urutkan berdasarkan “Qty”. Untuk mengembalikan data seperti semula dapat anda gunakan Sort kembali berdasarkan kolom “No” dengan AZ.



Tips Penggunaan Sorting data excel 1. Tabel harus ideal ( tidak ada merge cell) syarat tabel ideal bisa anda pelajari di materi Membuat tabel. 2. Hindari Blok kolom pada saat sort, “cukup letakan kursor di kolom” kemudian klik AZ / Z-A Setelah anda belajar mengurutkan data, apakah hanya seperti ini fungsinya?? tentu tidak kita dapat kombinasikan dengan fasilitas excel yang lain dengan Group atau subtotal untuk membuat suatu report data.



SUBTOTAL di Excel dan Cara Penggunaannya Subtotal adalah suatu perintah di microsoft excel yang berfungsi untuk menjumlahkan beberapa item data dalam satu tabel. Ada 2 cara penggunaan SUBTOTAL di excel, cara yang pertama kita dapat gunakan Function dan cara ke dua kita dapat gunakan fasiltas/fitur subtotal pada Tab Data. Subtotal di excel berbeda dengan SUM, kedua fungsi ini mempunyai kegunaan masing-masing.



Beda SUBTOTAL dengan SUM Fungsi SUBTOTAL dan SUM secara kegunaan hampir sama, dua fungsi ini dapat di gunakan untuk menghitung jumlah total dalam dalam suatu range. akan tetapi dalam penggunaanya sangat berbeda, Fungsi SUBTOTAL dalam penggunaanya harus menambahkan function number sebagai tipe kalkulasinya, sedangkan Fungsi SUM hanya menjumlahkan



range



referensinya



saja



(Tanpa



Function



Number).



Untuk



membedakannya lihat ulasan kami tentang fungsi SUM sebagai Fungsi Dasar pada Excel.



Penggunaan SUBTOTAL di Excel CARA #1 Fungsi Subtotal dapat anda gunakan untuk menghitung total dalam suatu range dengan menggunaka tipe perhitungan didalam Formulanya. Syntax =SUBTOTAL(Function_num,Ref1,Ref2,….) Ket : 



Function Number : Berisi Nilai (konstanta) antara 1 sampai dengan 11, anda dapat memilih



sesuai



kebutuhan



sesuai



ketrangan



dibawah.



1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN, 6=PRODUCT, 7=STDEV, 8=STDEVP, 9=SUM, 10=VAR, 11= VARP 



Ref : Berisi referensi Range yang akan di hitung.



Contoh Penggunaan : Hitung menggunakan SUBTOTAL untuk Semua kolom data berikut ini dengan tipe perhitunganya adalah SUM?



Penyelsaian : 1. Letakan pointer pada salah satu cell, kita dapat letakan di Cell E51 2. Tuliskan



Rumus



/



Formula



=SUBTOTAL(9,E6:E49)



3. Tekan Enter. Catatan : 



Hasil dari Rumus diatas akan terlihat sama dengan SUM ketika Function Number yang digunakan SUBTOTAL adalah (9) atau SUM.







Anda dapat mengganti Function number Sesuai kebutuhan







Ketika menggunakan Subtotal kemudian data tersebut kita Filter, Perhitungan dengan subtotal akan menyesuaikan dengan data yang ditampilkan (dinamis).



CARA #2 Untuk Cara yang ke-2 ini kita akan coba gunakan SUBTOTAL menggunakan Fitur excel. untuk menggunakan Subtotal ini syarat utamanya adalah datanya harus di Urutkan (Sort) terlebih dahulu. jika anda belum memahami fungsi Sort bisa anda pelajari di cara penggunaan sort di excel. Gunakan data diatas sebagai Contoh latihan : 1. Urutkan data terlebih dahulu berdasarkan “DAY”. Lihat gambar di bawah ini.



2. Letakan Pointer pada area data 3. Masuk Ke Tab Data pilih SUBTOTAL 4. Pada



Dialog



Box



sesuaikan



sperti



berikut



ini



At each change in = “Day“, Use Function = “sum“, add Subtotal To = “Total“ 5. Klik OK Hasil dari penggunaan subtotal adalah pada setiap item “Day” kan di buatkan satu group data, di bawah data tersebut akan ditambahkan satu kolom baru berisi Nilai subtotal masing-masing “Day” . hasil Penampakannya berikut ini.



Catatan : 



Syarat Penggunaan fitur subtotal adalah Data harus di Sort terlebih dahulu







At Each change In di pilih sesuai kolom data yang sort.







Fitur subtotal dapat anda gunakan sebagai report sederhana dengan Klik angka group (1,2,3) yang berada pada Pojok kiri atas.







Untuk menghapus Subtotal pada data gunakan Langkah diatas kemudian pilih Remove all.



Cara Menggunakan Filter data di Excel Autofilter pada excel atau sering disebut filter adalah sebuah menu di excel yang berfungsi untuk menyaring data atau menampilkan data yang kita inginkan berdasarkan kriteria tertentu. filter data di excel sangat bermanfaat jika kita bekerja dengan data yang besar, dari data yang besar tersebut dapat dengan mudah dianalisa dan ditampilkan sesuai dengan kebutuhan. menu filter ini juga dapat anda gunakan sebagai media reporting yang sederhana, untuk menampilkan data dengan pemfilteran satu variabel atau multi variabel. bagaimana cara filter data di excel? Markicob “mari kita coba”.



Cara Filter data di excel Menu filter dapat anda akses pada Tab Data dan dikelompokan kedalam group Sort and filter seperti pada gambar siatas. Semua tipe data (text, angka, tanggal, jam) pada excel dapat dilakukan pemfilteran data, hanya saja kondisi untuk masing-masing tipe data akan berbeda. pada menu filter terdapat 3 icon yaitu : 



Clear : Digunakan untuk mengembalikan data seperti semula setelah data terfilter.







Reapply : Digunakan untuk memfilter ulang ketika terjadi perubahan data.







Advance filter : Digunakan untuk memfilter data dengan kriteria yang kompleks.



Langkah-langkah untuk memfilter data sebagai berikut. 1. Pastikan data anda mempunyai Judul kolom atau header. 2. Letakan kursor (pointer) pada area data. 3. Masuk



ke



tab



data



pilih



menu



Filter



atau



dapat



anda



gunakan



shortcut CTRL+SHIFT+L 4. Pastikan



Setiap



judul



kolom



atau



header



untuk lebih jelasnya lihat gambar dibawah ini



terdapat



dropdown



filter.



Sampai disini langkah awal untuk pemfilteran data sudah selesai. selanjutnya kita dapat memfilter data untuk satu kolom atau multi kolom berdasarkan kondisi tertentu.



Filter data dengan Ceklist Setelah judul kolom sudah terdapat dropdown filter seperti gambar diatas, selanjutnya kita dapat filter data tersebut berdasarkan kolom. yang pertama kita akan mencoba filter data text yaitu kolom product. Contoh kasus : Tampilkan data berdasarkan kolom PRODUCT dengan kode adalah FN0801 1. Klik Dropdown Filter pada kolom Product 2. Pada tampilan dropdown filter akan ditampilkan semua kode product yang semuanya dalam keadaan terceklist. karena kita haya akan menampilkan kode FN0801 saja, maka hilangkan semua Ceklist dengan dengan memilih Select all. Kemudian checklist



FN0801



dan



klik



OK.



3. Hasilnya



sebagai



berikut



“Hanya



kode



FN0801



saja



yang



ditampilkan”



Filter data di excel dengan Search Box Ada beberapa cara untuk melakukan filter data di excel, cara diatas adalah cara dasar filter data di excel yaitu dengan checklist untuk memilihnya. pada versi excel 2010 keatas terdapat fasilitas Search filter dengan fasilitas ini anda cukup ketikan teks untuk pemfilterannya. sebagai contoh lihat gambar dibawah ini. 1. Klik Dropdown Filter pada kolom Product 2. Pada tampilan dropdown filter tuliskan pada search box “FN0801“, excel akan otomatis mencari kode tersebut dan menampilkan kode tersebut dalam keadaan terceklist. 3. Klik OK. hasilnya akan sama dengan cara ceklist diatas.



Filter data dengan kondisi Filter dengan cara ini digunakan ketika kita akan menampilkan data dengan syarat tertentu. filter dengan kondisi memungkinkan kita untuk manampilkan data dengan lebih mudah jika kita bekerja dengan data yang besar. Contoh kasus : Tampilkan data berdasarkan kolom Product yang dimulai dengan huruf “FN”



1. Klik Dropdown Filter pada kolom Product 2. Pada tampilan dropdown filter sorot Text Filter, pilih kondisinya Begins With.



3. Pada window custom autoFilter tuliskan “FN“ 4. Klik OK 5. Hasilnya sebagai berikut “semua yang kode yang dimulai dengan huruf FN akan ditampilkan”



Pada Text filter terdapat beberapa kondisi yang dapat kita gunakan sesuai kebutuhan. 



Equals : Menampilkan data Sama dengan text yang di tuliskan







Does not Equals : Menampilkan data Tidak sama dengan text yang dituliskan







Begins With : Menampilkan data dengan Text yang dimulai dengan text yang dituliskan







Ends With : Menampilkan data Text yang diakhiri dengan text yang dituliskan







Contains : Menampilkan data Text Yang mengandung kata







Does Not Contains : Menampilkan data Text Yang tidak mengandung kata







Custom Filter : Memfilter dengan.



Filter data Multi kolom Filter data di excel juga sangat memungkinkan ketika kita akan menampilkan data berdasarkan beberapa kolom atau multi kolom. setelah kita filter data pada kolom “Product“, kemudian filter kembali pada kolom tanggal “May“. Contoh kasus : Tampilkan data berdasarkan kolom “Product” yang dimulai dengan huruf “FN” dan pada bulan “May” Penjualannya lebih dari “200”. 1. Filter kolom “product” yang dimulai dengan huruf “FN” dengan langkah seperti diatas. 2. Selanjutnya Filter Kolom bulan “May” 3. Klik Dropdown Filter pada bulan “may”, pilih Number Filter dengan kondisi Greather



Than.



4. Ketikan Pada window autofilter box “200”



5. Klik



OK.



hasilnya



sebagai



berikut



Pada saat kita melakukan filter data di excel, aplikasi excel akan secara otomatis mengenali tipe data pada kolom. jika data yang akan di filter adalah data text, maka secara otomatis yang tampil kondisinya adalah Text Filter, jika kolom datanya berupa angka, maka yang tampil kondisinya adalah Number Filter, jika datanya berupa tanggal maka kondisi yang tampil adalah Date Filter. Tutorial Filter ini adalah dasar dari penggunaan filter secara umum, masih banyak lagi yang dapat kita pelajari dalam fasilitas ini. selanjutnya anda dapat mempelajari lebih lanjut lagi penggunaan Custom Filter, Filter By color, dan Advance Filter.



Penggunaan Custom Filter di excel Filter adalah sebuah fasilitas di microsoft excel yang berfungsi untuk menyaring data atau menampilkan data yang kita inginkan berdasarkan kriteria tertentu. sering kali kita kesulitan untuk menampilkan data spesifik sesuai yang kita inginkan ketika kita bekerja dengan data yang kompleks. fasilitas filter pada excel sudah sangat canggih untuk melakukan pemfilteran dengan data yang kompleks atau dengan kondisi yang rumit. pada fasilitas filter terdapat satu menu yang dapat kita gunakan yaitu custom filter. bagaimana cara penggunaan custom filter di excel, yuk di coba!!



Custom Filter di excel dengan 2 kondisi Memfilter data di excel dengan dua kondisi dapat anda lakukan dengan custom filter, contoh pada gambar diatas, excel sudah menyediakan 2 kolom untuk kondisi sesuai yang kita butuhkan. pada custom filter juga terdapat dua operator yaitu : 



AND : Data akan ditampilkan jika kondisi 1 dan 2 terpenuhi, kedua kondisi harus terpenuhi.







OR



: Data akan ditampilkan jika kondisi 1 atau 2 terpenuhi. cukup salah satu saja



kondisi terpenuhi. Contoh kasus : Tampilkan data berdasarkan kolom ” Total” yang nilainya lebih besar sama dengan “1900” dan kurang dari sama dengan “2000”.



Penyelesaian : 1. Letakan pointer pada area data. 2. Masuk ke tab data dan pilih menu Filter. “pastikan semua judul kolom sudah ditambahkan dropdown filter” 3. Klik dropdown filter pada kolom “total”, pilih Number Filters, kemudian klik Custom Filter. 4. Tambahkan Kriteria seperti berikut pada Window custom Filter 



Pada kolom box 1 pilih kondisinya adalah “is grather than or equal to” dengan nilai “1900”







Pada kolom box 2 pilih kondisinya adalah “is less than or equal to” dengan nilai



“2000” 5. Klik



OK.



hasilnya



sebagai



berikut.



Custom Filter Berdasarkan Digit karakter Custom Filterter di excel juga dapat di gunakan untuk membaca urutan digit character, misalnya digunakan untuk memfilter kode produk, NIP pegawai, Nomor induk dan lain sebagainya. custom filter di excel dengan membaca digit character dapat menggunakan 2 kriteria simbol. 



Use ? to represent any single characters.







Use * to represent any series of characters.



Contoh kasus : Tampilkan data berdasarkan kode produk yang digit ke 3 adalah angka 0 (nol)



Penyelesaian : 1. Letakan pointer pada area data. 2. Masuk ke Tab data dan pilih menu Filter. “pastikan semua judul kolom sudah ditambahkan dropdown filter” 3. Klik dropdown filter pada kolom “Product”, pilih Text Filters, kemudian klik Custom Filter. 4. Tambahkan Kriteria seperti berikut pada Window custom Filter 



Tuliskan tanda ” ??0* “







Tanda ?? akan mengabaikan digit pertama dan ke dua. 0 adalah karakter ke 3. kemudian diakhiri tanda * untuk mengabaikan karakter di belakangnya.



5. Klik OK. Hasil sebagai berikut. “kode dengan digit ke-3 adalah angka 0 yang tampil”.



Terimakasih, Semoga bermanfaat untuk anda pengunjung LatihanExcel.com…. ;D



Penggunaan Advance Filter pada excel Setelah anda belajar penggunaan autofilter pada excel



ada satu fitur yang dinamakan



advane filter dan sangat sering digunakan ketika bekerja dengan data excel. Advance filter adalah fasilitas tambahan pada autofilter yang digunakan untuk menyaring data dan menampilkan data dengan kriteria yang kompleks. advance filter pada excel mempunyai beberapa kelebihan dibandingkan dengan autofilter biasa. kelebihanya advance filter : 



Dapat memfilter dua atau lebih kolom secara bersamaan.







Dapat mengcopy hasil Filter secara langsung.



Advance filter dapat kita akses pada Tab data dan masuk ke dalam group sort & filters.



Cara Penggunaan Advance filter pada excel Cara penggunaan advance filter dengan filter biasa caranya sangat berbeda. pada advance filter terdapat beberapa kriteria yang dapat kita atur terlebih dahulu untuk memfilter tabel. Pada advance filter ada beberapa kriteria yang harus kita pahami. lihat gambar dibawah ini !







Filter the list, in place : Advance Filter dengan menampilkan hasil filter pada tabel sumber.







Copy to another location : Advance Filter dengan menampilkan hasil filter pada lokasi cell yang lain.







Criteria Range : Kriteria range filter yang di tuliskan pada cell.







Copy to : Alamat cell tujuan untuk menampilkan hasil filter. “akan aktif jika opsi Copy to another location di pilih.







Unique records only : Opsi ini digunakan untuk menampilkan satu data saja jika di temukan data yang duplikat atau lebih dari satu.



Contoh kasus : Gunakan advance filter untuk menampilkan data berdasarkan kolom Day = “Friday” dengan nilai totalnya > 1500. tambahkan kolom riteria range seperti gambar dibawah ini.



Penyelesaian : 1. Letakan kursor pada area data. 2. Masuk ke Tab Data pilih menu Advance atau gunakan shortut kombinasi ALT+A+Q



3. Pada dialog box advance filter atur kondisi seperti dibawah ini. 



Pada action pilih Filter the list, in place







List Range akan secara otomatis terseleksi Current region yaitu $B$5:$N$50. jika terjadi kesalahan anda dapat blok ulang kembali.







Criteria Range dapat anda blok range ‘FILTER 2’!$P$6:$Q$7







Gunakan Checklist Unique records only untuk menghindari data yang duplikat.







Klik



4. Hasil



OK



advance



filter



sebagai



berikut



Advance Filter untuk copy hasil filter Selain untuk memfilter dengan beberapa kriteria secara cepat, advance filter juga dapat anda gunakan untuk mengkopi hasil filter pada cell tau halaman lain. dalam hal ini memudahkan kita untuk mengkopi hasil filter tanpa harus menggunakan cara yang panjang biasnya di lakukan dengan Go to special. Menggunakan kasus yang sama seperti diatas. lakukan prosedur berikut ini : 1. Letakan kursor pada area data. 2. Masuk ke Tab Data pilih menu Advance atau gunakan shortut kombinasi ALT+A+Q



3. Pada



dialog



box



advance



filter



atur



kondisi



seperti



dibawah







Pada action pilih Copy to another loation







List Range akan secara otomatis terseleksi Current region yaitu $B$5:$N$50.







Criteria Range dapat anda blok range ‘FILTER 2’!$P$6:$Q$7







Pada box Copy to gunakan klik cell ‘FILTER 2’!$B$57







Gunakan Checklist Unique records only untuk menghindari data yang duplikat.







Klik OK



4. Hasil filter akan di tampilkan pada cell tujuan.



ini.



Belajar Microsoft Excel Advance (Tingkat Mahir) Excel advance adalah level belajar excel tingkat mahir, setelah kita belajar tentang persiapan data dan mengolah data pada level intermediate, anda juga diharapkan mampu dalam hal menyajikan data. pada level advance kita akan belajar bagaimana menyajikan data yang interaktif, menggunakan multiple formula, membuat report dan summary data. 1. 2. 3. 4. 5. 6.



Membatasi Input Data dengan Data Validation Membuat Dropdown list dengan Data Validation Fungsi SUMIF, COUNTIF, dan AVERAGEIF Fungsi SUMIFS, COUNTIFS, dan AVERAGEIFS Fungsi INDEX dan MATCH What If Analysis  Goal Seek  Solver  Scenario Manager  Data Table 7. Consolidate 8. Membuat Laporan menggunakan PivotTable 9. Customize PivotTable 10. Menggunakan Slicer dan PivotChart 11. PivotTable dari beberapa tabel 12. File Protection 13. Eksport dan Import data file excel 14. Sharing Workbook



Cara Menggunakan Data Validation di Excel Micrososft excel memiliki banyak sekali fitur yang dapat memudahkan kita dalam pekerjaan sehari-hari salah satunya



adalah Data validation. Data validation di excel



berfungsi untuk membatasi nilai yang masuk ke dalam cell. fitur ini mencegah data yang tidak valid masuk kedalam cell, dengan tujuan dapat mengurangi kesalahan saat kita memasukan data. Untuk Menggunakan Data validation di excel dapat di akses pada Tab / menu DATA. untuk lebih jelasnya lihat gambar dibawah ini.



Langkah langkah 1. Masuk ke tab Data 2. Anda dapat klik langsung Data validation atau Klik Dropdown Data Validation 3. Pada



dropwon



Ada



3



Fungsi



utama



Data



validation,



Data validation : Memberikan batasan untuk data yang akan masuk di cell. Circle Invalid Data : Memberikan tanda circle untuk data yang sudah masuk di cell. Clear Validation Circle : Menghapus Tanda circle pada data. 4. Pilih Data Validation



Menggunakan Data Validation Setelah anda membuka menu data validation maka window data validation akan terbuka dan menampilkan tiga Tab utama yaitu tab setting, input message dan error alert. tab ini berfungsi mengatur seluruh pengaturan dalam data validation.



Menggunakan Data Validation Setting Tab ini berisi pengaturan jenis atau tipe data yang akan masuk ke dalam cell. Pada group validation Criteria terdapat allow dan data .allow adalah tipe data yang dapat digunakan untuk membatasi tipe inputan, dan Data berisi pengaturan scope datanya yang berisi operator pembanding. Allow : Tipe data 



Any Value : Pilihan tipe dafault pada tipe ini semua tipe data dapat masuk ke dalam cell.







Whole number : Hanya tipe number saja yang dapat masuk ke dalam cell







List : Berfungsi membuat dropdown list sebagai pilihan inputan data.







Text Length : Membatasi panjang karakter yang masuk kedalam cell







Decimal : Hanya tipe data dalam bentuk decimal yang masuk ke dalam cell







Date : Mengatur data tanggal yang masuk ke dalam cell.







Time : Mengatur data Jam yang masuk ke dalam cell.







Custom : Membuat data validation custom, contohnya menggunakan Formula.



Data : Operator pembanding berfungsi untuk mengatur scope data. 



Greather than







Graethen then or equal to







Equal to







Not equal to







Beetween







Not Beetween







Less Than







Less than or equal to



Menggunakan Data Validation Input Message Tab ini berfungsi untuk memberikan pesan untuk user ketika akan memasukan data. kita dapat menambahkannya sebagai pesan cara input data. 



Title : Berisi judul pesan. Contoh : “Perhatian cell ini mengandung data validation”







Input message : Isi pesan yang akan di tampilkan. Contoh : “inputkan data antara 1000 s/d 1000000”



Menggunkan Data Validation Error Alert Fungsi error alert adalah untuk mengatur dan memberikan pesan kesalahan yang muncul ketika terjadi kesalahan saat input data. 



Title : judul pesan kesalahan. Contoh : “Perhatian”







Error message : Isi pesan kesalahan. Contoh ” Ups salaaaaaaah baca cara pengisiannya”



Setelah anda mengenal data Data validation ini, bagaimana cara penggunaan dan penerapanya pada data excel, nantinya kita dapat tambahkan data validation untuk membuat isian data sederhana yang akan kami ulas pada modul “membuat formulir dengan data validation”



Cara Menggunakan Data Validation Lengkap BY BUNG_BRIBIL · PUBLISHED JUNE 7, 2017 · UPDATED NOVEMBER 6, 2017



Data Validation adalah fitur pada microsoft excel yang berfungsi untuk membatasi nilai yang masuk ke dalam cell. dengan tujuannya mengurangi kesalahan pada saat user menginputkan data. salah satu kegunaanya adalah untuk membuat list data. Data validation list merupakan yang populer kita gunakan untuk menampilkan dropdown pilihan data tanpa kita ketik manual. dalam latihan ini kita akan membahas Cara menggunakan data validation dengan berbagai tipe. Untuk mengakses Fitur ini ada pada Tab data kemudian pilih data validation. lihat gambar dibawah ini.



Cara Membuat data Validation Contoh kasus : Berikan Batasan inputan data dengan Data validation untuk tabel isian berikut ini.



Ketentuan untuk mengisi data diatas adalah : 1. Jumlah pengajuan Kredit Cell C3 Batasi dengan allow whole number 2. Nomor induk Cell C5 Batasi dengan Allow Text Length 3. Tanggal masuk Cell C6 Batasi dengan allow Date 4. Jabatan Cell C7 Batasi dengan allow List 5. Area Cell C8 Batasi dengan allow List dengan referensi cell 6. Kota Cell C9 Batasi dengan allow List dengan formula



7. Pengajuan Fasilitas Cell C10 Batasi dengan allow dengan Custom Penyelesaian :



Cara menggunakan Data Validation dengan Whole Number Batasan dengan tipe Whole number, yaitu memberikan batasan cell dengan tipe data yang diinput adalah data number saja. sebagai contoh pada soal diatas kita akan batasi jumlah pengajuan kredit adalah antara 10.000.000 s/d 100.000.000. jika kita inputkan data diluar angka tersebut akan terjadi error data tidak dapat masuk. Gunakan Prosedur berikut : 1. Letakan Pointer pada cell C3. 2. Masuk Ke Tab Data pilih data validation 3. Pada tab setting sesuaikan seperti gambar gambar di bawah ini 



Allow : Whole Number







Data : Beetwen







Minimum :10000000







Maximum :100000000



4. Tambahkan Input message sebagai 



Title : “Perhatian”







Input Message : “masukan data antara 10 juta s/d 100 juta”



5. Tambahkan Error alert 



Title : “Data ERROR”







Input Message : “Ups salah,, Baca petunjuk pengisian data”



6. Setelah selesai Klik OK



Cara menggunakan Data Validation Dengan Text Length Fungsi allow ini Membatasi panjang karakter yang masuk kedalam cell. sebagai contohnya kita akan Nomor Induk dengan panjang karakter adalah 5. Gunakan Prosedur berikut : 1. Letakan Pointer pada cell C5. 2. Masuk Ke Tab Data pilih data validation 3. Pada tab setting sesuaikan seperti gambar gambar di bawah ini 



Allow : Text Length







Data : Equal too







Length : 5



4. Tambahkan input message dan Error alert seperti diatas (optional) 5. Klik OK



Menggunakan Data Validation Date Allow ini Mengatur data tanggal yang masuk ke dalam cell. dari tabel pertanyaan diatas misalnya yang dapat masuk pada cell adalah setelah tanggal hari ini. anda dapat mengaturnya sesuai kebutuhan. Gunakan Prosedur berikut : 1. Letakan Pointer pada cell C6. 2. Masuk Ke Tab Data pilih data validation 3. Pada tab setting sesuaikan seperti gambar gambar di bawah ini 



Allow : Date







Data : Greather than







Start Date : Tambahkan Formula =TODAY()



4. Tambahkan input message dan Error alert seperti diatas (optional)



Cara Membuat Dropdown List Berikut ini cara menggunakan data validation list atau membuat dropdown list yang berfungsi membuat dropdown list sebagai pilihan inputan datanya agar nantinya user tidak perlu megetikan data user cukup memilih datanya saja. pilihan yang muncul pada dropdown list adalah “MANAGER, STAFF, ACCOUNTING” Gunakan Prosedur berikut : 1. Letakan Pointer pada cell C7. 2. Masuk Ke Tab Data pilih data validation 3. Pada tab setting sesuaikan seperti gambar gambar di bawah ini 



Allow : List







Source : Anda dapat tuliskan manual MANAGER,STAFF,ACCOUNTING. anda juga dapat gunakan referensi data pada excel



4. Tambahkan input message dan Error alert seperti diatas (optional) 5. Klik OK



Data Validation List dengan Referensi Cell Untuk mengisi Cell C8 kita akan gunakan data validation tipe List, akan tetapi dengan mengambil source dari range cell. untuk mengisi Area pada tabel diatas gunakan referensi tabel di bawah ini.



Tabel referensi



Gunakan Prosedur berikut : 1. Letakan Pointer pada cell C8. 2. Masuk Ke Tab Data pilih data validation 3. Pada tab setting sesuaikan seperti gambar gambar di bawah ini 



Allow : List







Source : Blok Range area dari tabel diatas E3:G3 “otomatis akan diabsolutkan pada “



4. Tambahkan input message dan Error alert seperti diatas (optional) 5. Klik OK



Cara membuat Data Validation List dengan Formula Untuk mengisi Cell C9 kita akan gunakan data validation dengan tipe List dengan mengambil source dari range cell. untuk mengisi Cell Kota gunakan tabel referensi. Pada cell kota akan langsung terpilih list kota sesuai dengan isian Area. Jika Area adalah “I” maka kotanya “medan, padang,pekanbaru, jambi, palembang”.Jika area adalah “II” maka Yang muncul adalah List range kota area 2 begitupun juga untuk area “III”. Gunakan Prosedur berikut : 1. Letakan Pointer pada cell C9. 2. Masuk Ke Tab Data pilih data validation 3. Pada tab setting sesuaikan seperti gambar gambar di bawah ini 



Allow : List







Source : =IF($C$8=”I”,$E$4:$E$8,IF($C$8=”II”,$F$4:$F$8,IF($C$8=”III”,$G$4:$G$8,” ”)))



4. Tambahkan input message dan Error alert seperti diatas (optional) 5. Klik OK



Data Validation Custom Fungsi SUMIF, AVERAGEIF, dan COUNTIF BY BUNG_BRIBIL · PUBLISHED MARCH 15, 2018 · UPDATED MARCH 22, 2018



Anda mungkin sudah familiar dengan fungsi statistik atau rumus dasar excel SUM, COUNT, AVERAGE, MIN, dan MAX. Fungsi ini sering kita gunakan untuk menghitung jumlah data dalam satu range. Selain fungsi dasar diatas Program excel juga menyediakan fungsi perhitungan yang hampir sama, hanya ditambahkan IF dibelakangnya yaitu Fungsi SUMIF , AVERAGEIF, dan COUNTIF. ketiga fungsi ini digunakan untuk menghitung data dengan satu kriteria/kondisi.



Fungsi SUMIF Fungsi SUMIF adalah fungsi yang dapat kita gunakan untuk menjumlahkan total data dengan satu kriteria atau kondisi. berikut ini adalah contoh penggunaannya. Dibawah ini adalah tabel data penjualan “Date Of Sales”. Cara penulisan fungsi SUMIF sebaga berikut. Syntax = SUMIF(Range, Criteria, [Sum_range])







SUMIF = Fungsi Penjumlahan total data dengan kriteria







Range = Range Kriteria yang akan dihitung.







Criteria = Kriteria atau kondisi yang akan dicari hasilnya







[Sum_Range]



=



Range



Kolom



yang



akan



dihitung



Contoh Kasus : Kita diminta untuk menghitung Berapa total penjualan “(sales)” dengan jenis Product adalah “Monitor”. Penyelesaian : 1. Letakan Pointer pada cell yang akan kita cari hasilnya 2. Tuliskan Rumus / Formula =SUMIF(E4:E33,"Monitor",I4:I33)



3. Tekan ENTER Catatan : Untuk nilai Criteria anda dapat menuliskanya langsung pada Rumus/Formula dengan memberikan tanda kutip atau alamat cell yang berisi “Monitor”.



Fungsi AVERAGEIF Fungsi AVERAGEIF adalah fungsi yang dapat kita gunakan untuk menghitung nilai ratarata dengan satu kriteria atau kondisi. berikut ini adalah contoh penggunaannya. Syntax = AVERAGEIF(Range, Criteria, [Sum_range]) 



AVERAGEIF = Fungsi penghitung rata-rata data dengan satu kriteria







Range = Range Kriteria yang akan dihitung.







Criteria = Kriteria atau kondisi yang akan dicari hasilnya







[Sum_Range] = Range Kolom yang akan dihitung



Contoh Kasus : Gunakan Data seperti Diatas, Kita diminta untuk menghitung Berapa Ratarata penjualan “(sales)” dengan jenis Product adalah “Monitor”. Penyelesaian : 1. Letakan Pointer pada cell yang akan kita cari hasilnya



2. Tuliskan Rumus / Formula =AVERAGEIF(E4:E33,"Monitor",I4:I33)



3. Tekan ENTER



Fungsi COUNTIF Fungsi COUNTIF adalah fungsi yang dapat kita gunakan untuk menghitung banyaknya data atau frekuensi dengan satu kriteria / kondisi. berikut ini adalah contoh penggunaannya. Syntax =COUNTIF(Range, Criteria) 



COUNTIF = Fungsi penghitung Frekuensi data dengan satu kriteria







Range = Range Kriteria yang akan dihitung.







Criteria = Kriteria atau kondisi yang akan dicari hasilnya



Pada fungsi countif tidak memerlukan argumen [sum_range] formula akan langsung menghitung kolom range. Contoh Kasus : Gunakan Data seperti Diatas, Kita diminta untuk menghitung Banyaknya data/ frekuensi data penjualan “(sales)” dengan jenis Product adalah “Monitor”. Penyelesaian : 1. Letakan Pointer pada cell yang akan kita cari hasilnya 2. Tuliskan Rumus / Formula =COUNTIF(E4:E33,"Monitor")



3. Tekan ENTER. Berikut hasil lengkapnya dan anda juga dapat mencoba berlatih dengan criteria yang lain, agar



lebih



memahami



kasus



dan



rumusnya.



Contoh soal : 



Berapa total penjualan selama bulan februari,berapa rata-rata dan frekuensi datanya?







Berapa banyak data dengan harga “price” < 10000?



Fungsi SUMIFS, AVERAGEIFS, dan COUNTIFS Pada materi sebelumnya kita sudah belajar Fungsi SUMIF, COUNTIF, dan AVERAGEIF. Tidak jauh berbeda dengan fungsi yang akan kita bahas yaitu Penggunaan Fungsi SUMIFS, AVERAGEIFS, dan COUNTIFS. perbedaan fungsi ini hanya pada penambahan huruf “S” saja, Penambahan Fungsi ini berkaitan dengan jumlah kriteria perhitungan. Fungsi SUMIF tanpa “S” hanya dapat menghitung dengan satu kriteria saja, sedangkan Fungsi SUMIFS dapat di gunkan untuk melakukan perhitungan data statistik dengan kriteria lebih dari satu. maksimum kriteria yang dapat kita gunakan pada fungsi ini adalah 127 Kriteria/kondisi, sangat banyak bukan???



Fungsi SUMIFS Fungsi SUMIFS adalah fungsi yang dapat kita gunakan untuk menjumlahkan total data dengan satu kriteria atau kondisi. berikut ini adalah contoh penggunaannya. Dibawah ini adalah tabel data penjualan “Date Of Sales”. Cara penulisan fungsi SUMIF sebaga berikut. Syntax = SUMIFS(Sum_Range, Criteria_Range1,Criteria1,Criteria_Range2,Criteria2,……….) 



SUMIFS = Fungsi Penjumlahan total data lebih dari satu kriteria







Sum_Range = Range Kriteria yang akan dihitung.







Criteria_range1= Range Kriteria atau kondisi pertama yang akan dicari hasilnya







Criteria1= Kondisi atau kriteria pertama



Contoh Kasus : Kita diminta untuk menghitung Berapa total penjualan “(sales)” dengan jenis Product adalah “Monitor” dari Cabang / Branch “BINTARO”. Dari kasus diatas kita akan menghitung total penjualan pada kolom sales dengan kondisi pertama adalah kolom jenis product dan kondisi ke dua adalah kolom Branch. Penyelesaian : 1. Letakan Pointer pada cell yang akan kita cari hasilnya 2. Tuliskan Rumus / Formula =SUMIFS(I4:I33,E4:E33,"monitor",D4:D33,"Bintaro")



3. Tekan ENTER Catatan : Untuk nilai Criteria anda dapat menuliskanya langsung pada Rumus/Formula dengan memberikan tanda kutip atau alamat cell yang berisi “Monitor”. Jika kriteria perhitungan menggunakan operator matematika anda dapat gunakan “>,=,10000”.



Fungsi AVERAGEIFS Fungsi AVERAGEIFS adalah fungsi yang dapat kita gunakan untuk menghitung nilai ratarata dengan lebih dari satu kriteria atau kondisi. berikut ini adalah contoh penggunaannya. Syntax = AVERAGEIFS(Sum_Range, Criteria_range1,Criteria1, Criteria_range2, Criteria2) 



AVERAGEIFS=



Fungsi



penghitung



rata-rata



data



dengan



lebih



dari satu



kriteria/kondisi 



Sum_Range = Range Kriteria yang akan dihitung.







Criteria_range1= Range Kriteria atau kondisi pertama yang akan dicari hasilnya







Criteria1= Kondisi atau kriteria pertama



Contoh Kasus : Gunakan Data seperti Diatas, Kita diminta untuk menghitung Berapa Ratarata penjualan “(sales)” dengan jenis Product adalah “HP” di Cabang “Glodok” Penyelesaian : 1. Letakan Pointer pada cell yang akan kita cari hasilnya 2. Tuliskan Rumus / Formula =AVERAGEIFS(I4:I33,E4:E33,"HP",D4:D33,"Glodok"



3. Tekan Enter



Fungsi COUNTIFS Fungsi COUNTIFS adalah fungsi yang dapat kita gunakan untuk menghitung banyaknya data atau frekuensi data dengan lebih dari satu kriteria / kondisi. berikut ini contoh penggunaannya. Syntax =COUNTIFS(, Criteria) 



COUNTIFS = Fungsi penghitung Frekuensi data dengan lebih dari satu kriteria







Criteria_Range1 = Range Kriteria pertama yang akan dihitung.







Criteria1 = Kriteria atau kondisi yang pertama akan dicari hasilnya



Pada fungsi countifs tidak memerlukan argumen [sum_range] formula akan langsung menghitung kolom range kriteria yang kita blok. Contoh Kasus : Gunakan Data seperti Diatas, Kita diminta untuk menghitung Banyaknya data/ frekuensi data dengan jenis Product adalah “HP” dengan Quantity “>5000” Penyelesaian : 1. Letakan Pointer pada cell yang akan kita cari hasilnya 2. Tuliskan Rumus / Formula =COUNTIFS(E4:E33,"HP",H4:H33,">5000")



3. Tekan ENTER. Berikut hasil lengkapnya dan anda juga dapat mencoba berlatih dengan criteria yang lain, agar lebih memahami kasus dan rumusnya.



Contoh soal : 



Berapa total penjualan selama bulan februari untuk product Monitor,berapa rata-rata dan frekuensi datanya?







Berapa frekuensi data produk HP dengan “price” < 10000?



Fungsi INDEX MATCH Untuk Mengolah Data Excel Anda pernah melakukan pencarian data dari suatu tabel ke tabel yang lain? Biasannya pengguna excel akan melakukannya dengan menggunakan Fungsi VLOOKUP atau Fungsi HLOOKUP. Akan tetapi kedua fungsi lookup ini mempunyai kelemahan yaitu hanya mampu membaca tabel array ke arah kanan atau kebawah saja. Bagaiamana agar Pencarian data tersebut fleksibel, dalam arti dapat mencari dan mengambil data dari kolom ataupun baris manapun yang kita tentukan, Fungsi INDEX MATCH adalah Solusinya.



Kenapa Menggunakan Fungsi INDEX MATCH? Pada umumnya penggunaan Fungsi INDEX MATCH adalah untuk menanggulangi kelemahan dari fungsi VLOOKUP atau HLOOKUP. Hal ini disebabkan kedua fungsi LOOKUP tersebut hanya mampu membaca tabel array ke arah kanan atau kebawah. Sedangkan dalam dunia kerja, tabel array mempunyai bentuk yang elastis, tidak bisa diprediksi bahwa Cell yang menjadi acuan berada dikiri, kanan, bawah atau atas. untuk itu dengan menggunakan fungsi INDEX MATCH kekurangan tersebut dapat teratasi.



Fungsi INDEX Pada dasarnya Fungsi INDEX MATCH adalah dua fungsi yang berbeda yang mempunyai perhitungan dan argumen berbeda pula. Fungsi INDEX adalah fungsi yang cukup



sederhana, digunakan untuk mendapatkan nilai dari suatu cell berdasarkan pencarian pada



suatu



definisi



table



/



data



range



worksheet



kita.



metode pencariannya berdasarkan informasi posisi kolom dan baris, dengan acuan berupa kolom dan baris pertama table / data range tersebut.



Syntax =INDEX(array,row_num,Column_num)



Keterangan : 



Array: Range data yang akan diambil nilainya.







Row_num: pergerakan nomor baris.







Col_num : Pergerakan nomor kolom.



Contoh Kasus : Kita diminta untuk mengindex data di kolom jenis produk yang akan di ambil dari tabel dibawahnya menggunakan fungsi INDEX.



Penyelesaian : 1. Letakan Pointer pada Cell D13 2. Masukan Formula / rumus =INDEX($B$22:$D$25,2,1)



3. Tekan Enter 4. Hasilnya adalah “Makanan“ Catatan : Pada fungsi Index untuk mengisi argumen Row_number dan Col_Number harus manual.



Fungsi MATCH



Fungsi MATCH adalah fungsi yang digunakan untuk mencari suatu nilai dari suatu range yang terdapat pada suatu kolom atau baris, tapi tidak kedua-duanya. hasil dari fungsi ini adalah bernilai nomor.



Syntax



=MATCH(lookup_value, lookup_array, [match_type])



Keterangan : 



Lookup_value : adalah nilai acuan yang ingin dicari pada lookup_array.







Lookup_array : adalah range data dari suatu kolom ataupun baris.







Match_type : adalah angka yang menunjukkan tipe pencocokan sebagai berikut : *angka 1 : jenis pencocokan dimana lookup_array harus dalam keadaan terurut secara ascending (kecil ke besar). Pencocokan dilakukan dengan mengambil nilai terbesar dari range data yang lebih kecil atau sama dari lookup_value. *angka 0 : jenis pencocokan dimana pada lookup_array dicari data yang sama persis dengan lookup_value. Urutan data tidak menjadi masalah. Jika diketemukan lebih dari satu data yang sama, maka akan diambil data yang pertama kali diketemukan secara



sekuensial.



*angka -1 : -1 : jenis pencocokan dimana lookup_array harus dalam keadaan terurut secara descending (besar ke kecil). Pencocokan dilakukan dengan mengambil nilai terkecil dari range data yang lebih besar atau sama dari lookup_value. Contoh Kasus : Carilah nomor baris dari tabel data berikut ini.



Penyelesaian : 1. Letakan Pointer pada Cell L14



2. Rumus / Formula berikut ini =MATCH(K14,$K$24:$K$27,1)



3. Tekan Enter 4. Hasilnya adalah Nompr Baris ke “2” Catatan : Blok data Lookup_array hanya berupa Range Baris atau Range kolom saja. fungsi match mengembalikan nilai nomor baris atau kolom.



Penggabungan Fungsi INDEX MACTH Dari kedua Fungsi yang sudah kita pelajari diatas dapat kita gabungkan sebagai satu kesatuan rumus untuk mencari data dan mengindek data secara dinamis dalam suatu tabel.



Syntax =INDEX(array,MATCH(lookup_value, lookup_array, [match_type]))



Keterangan : Dari syntax diatas Fungsi INDEX dijadikan sebagai fungsi induk, kemudian fungsi MATCH digunakan untuk menggantikan argumen Row_number dalam fungsi index. Contoh Kasus : Dari tabel dibawah ini kita diminta untuk mengindex data “harga” dari tabel sumber disampingnya menggunakan fungsi index match.



Penyelesaian : 1. Letakan pointer pada cell F4 2. Tuliskan Rumus / Formula berikut ini :



=INDEX($I$4:$I$7,MATCH(D4,$K$4:$K$7,0))



3. Tekan Enter 4. Hasilnya adalah “1.100.000” 5. Copy Formula untuk cell dibawahnya menggunakan autofill. Penjelasan : 



Tanda $ menunjukkan bahwa array yang dijadikan acuan harus dikunci karena ingin dicopy



ke



celll



bawahnya



$I$4:$I$7 = menunjukkan array dari Price, yaitu kolom yang ingin dicari nilainya 



D4 = menunjukkan sel acuan yang menjadi penentu sebagai informasi pengisian kolom Price







$K$4:$K$7 = menunjukkan array dari kolom product yang menjadi sel acuan







0 = menunjukkan tipe match yang dipilih yaitu tipe yang tepat dengan sel D4



Penggunaan fungsi index match diatas digunakan untuk data array vertikal, karena fungsi ini sangat dinamis anda juga dapat gunakan untuk bentuk data horisontal.



Menggunakan What If Analysis di Excel Pengguna microsoft excel pada umumnya mungkin jarang sekali menggunakan Fitur What If Analysis. Fitur ini adalah kumpulan beberapa perintah Microsoft Excel yang umum diterapkan di lingkungan bisnis. biasanya digunakan oleh user dengan level managerial keatas. Sesuai dengan namanya yaitu What If Analysis, atau disebut juga Analisa Bagaimana Jika, perintah ini bisa kita gunakan untuk membuat suatu analisa atau prediksi tentang berbagai kemungkinan yang mungkin terjadi dalam tabel perhitungan. Anda dapat memutar balikan perhitungan yang ada pada tabel dengan tujuan untuk mendapatkan perhitungan yang tepat. Pada Excel 2013, kumpulan perintah What-If-Analysis ini dapat Anda temukan pada tab Data grup Data Tools. Letak fitur ini dapat anda lihat pada gambar dibawah ini.



Fitur what if analysis ini terdapat 3 perintah yaitu : 1. Scenario Manager 2. Goal Seek 3. Data Table Scenario Manager adalah perintah yang digunakan untuk melihat beberapa hasil atau output dari beberapa kemungkinan input yang berlainan. Perintah ini biasanya digunakan dalam analisa data atau analisa suatu project untuk melihat beragam kemungkinan perhitungan yang mungkin bisa terjadi. Goal Seek adalah perintah untuk mencari berapa inputan atau nilai awal agar anda mendapatkan output atau nilai akhir sesuai dengan yang diharapkan. Data Table adalah perintah untuk mencari nilai hasil atau output secara cepat dari beberapa nilai input yang berbeda. Perintah What If Analysis dibuat menggunakan bahasa pemrograman Visual Basic for Aplication (VBA) atau Macro sehingga untuk menjalankanya anda tidak dapat melakukan undo. Contoh penggunaan dari fitur What If Analysis dapat anda lihat pada tulisan kami selanjutnya.



Menggunakan What If Analysis di Excel



BY BUNG_BRIBIL · PUBLISHED SEPTEMBER 8, 2018 · UPDATED MARCH 13, 2019



Pengguna microsoft excel pada umumnya mungkin jarang sekali menggunakan Fitur What If Analysis. Fitur ini adalah kumpulan beberapa perintah Microsoft Excel yang umum diterapkan di lingkungan bisnis. biasanya digunakan oleh user dengan level managerial keatas. Sesuai dengan namanya yaitu What If Analysis, atau disebut juga Analisa Bagaimana Jika, perintah ini bisa kita gunakan untuk membuat suatu analisa atau prediksi tentang berbagai kemungkinan yang mungkin terjadi dalam tabel perhitungan. Anda dapat memutar balikan perhitungan yang ada pada tabel dengan tujuan untuk mendapatkan perhitungan yang tepat. Pada Excel 2013, kumpulan perintah What-If-Analysis ini dapat Anda temukan pada tab Data grup Data Tools. Letak fitur ini dapat anda lihat pada gambar dibawah ini.



Fitur what if analysis ini terdapat 3 perintah yaitu : 1. Scenario Manager 2. Goal Seek 3. Data Table



Scenario Manager adalah perintah yang digunakan untuk melihat beberapa hasil atau output dari beberapa kemungkinan input yang berlainan. Perintah ini biasanya digunakan dalam analisa data atau analisa suatu project untuk melihat beragam kemungkinan perhitungan yang mungkin bisa terjadi. Goal Seek adalah perintah untuk mencari berapa inputan atau nilai awal agar anda mendapatkan output atau nilai akhir sesuai dengan yang diharapkan. Data Table adalah perintah untuk mencari nilai hasil atau output secara cepat dari beberapa nilai input yang berbeda. Scenario Manager adalah salah satu fitur data analysis yang dapat anda gunakan untuk melihat suatu hasil atau output dari beberapa input yang berbeda. Perintah ini digunakan untuk analisa bisnis atau analisa suatu project dengan cara melihat beragam kemungkinan perhitungan yang mungkin terjadi atau sebagai perbandingan suatu nilai output dari berbagai nilai inputan. Cara menggunakan Scenario manager dapat anda coba dengan contoh kasus berikut ini. Contoh Penggunaan Scenario Manager



Pak Anto akan membeli sebuah sepeda motor dengan metode pembayarannya dengan cara kredit. untuk perhitungannya menggunakan bunga, jangka waktu, dan jumlah angsuran. untuk mendapatkan perhitungan yang sesuai dengan kebutuhan, pak anto menggunakan scenario manager excel.



Untuk memudahkan dalam mendapatkan laporan skenario, maka buatlah tabel seperti dibawah ini dengan rumus yang tampak pada gambar.



Berikut langkah-langkahnya: 1. 2. 3. 4. 5. 6. 7. 8.



Klik tab Data Pilih What-If Analysis Pilih Scenario Manager Kemudian Klik Add untuk menambahkan skenario baru Berikan nama pada bagian Scenario Name, pada contoh kali ini Motor A Tentukan lokasi data yang akan dirubah, pada contoh kali ini (B11 sampai B13) Klik Add lalu isi Scenario Name berikutnya dengan nama Motor B Ubah nilai bunga, angsuran/tahun dan jangka waktu, sesuai dengan masingmasing daftar angsuran sehingga menghasilkan tampilan seperti gambar dibawah



ini



9. Selanjutnya untuk menampilkan data, pilih opsi skenario lalu klik Show Seperti yang tampak pada gambar dibawah ini.



Pada Scenario manager anda juga dapat melihat perbandingan atau komparasi tiga scenario yangtelah dibuat. Untuk melihat komparasi output dari ketiga scenario tersebut, klik tombol Summary. Kemudian pada jendela Scenario Summary yang ditampilkan, ketikan alamat cell B17 pada bagian Result cells. Result cells ini adalah alamat sel yang menyimpan nilai output yang akan dikomparasi, dalam contoh ini yaitu sel nilai untuk total pembayaran. Klik tombol OK. Dan secara otomatis Excel akan menampilkan tabel hasil komparasi dari beragam scenario yang telah Anda isikan sebelumnya dalam sebuah Sheet baru yaitu Sheet Scenario Summary. Catatan:



 







 



Scenario Summary juga bisa Anda tampilkan dalam bentuk Pivot Table. Caranya, pada jendela Scenario Summary yang ditampilkan, pilih PivotTable Report. Panel tambahan di bagian kiri dan atas merupakan panel bantuan untuk menyembunyikan atau menampilkan beberapa bagian pada Scenario Summary. Misalnya saja jika Anda ingin melihat hasilnya saja tanpa harus menampilkan selsel inputnya. Scenario-scenario yang telah Anda buat akan tetap disimpan oleh Excel. Pada jendela utama Scenario Manager, gunakan tombol Edit untuk merubah scenario yang ada atau tombol Delete untuk menghapus scenario. Tombol Merge pada jendela Scenario Manager bisa Anda gunakan untuk menggabungkan beberapa scenario secara cepat. Tombol Show pada jendela Scenario Manager bisa Anda gunakan untuk melihat hasil scenario langsung pada tabel data Anda sebelum Anda melihat hasil akhirnya.



Pengguna microsoft excel pada umumnya mungkin jarang sekali menggunakan Fitur What If Analysis. Fitur ini adalah kumpulan beberapa perintah Microsoft Excel yang umum diterapkan di lingkungan bisnis. biasanya digunakan oleh user dengan level managerial keatas. Sesuai dengan namanya yaitu What If Analysis, atau disebut juga Analisa Bagaimana Jika, perintah ini bisa kita gunakan untuk membuat suatu analisa atau prediksi tentang berbagai kemungkinan yang mungkin terjadi dalam tabel perhitungan. Anda dapat memutar balikan perhitungan yang ada pada tabel dengan tujuan untuk mendapatkan perhitungan yang tepat. Pada Excel 2013, kumpulan perintah What-If-Analysis ini dapat Anda temukan pada tab Data grup Data Tools. Letak fitur ini dapat anda lihat pada gambar dibawah ini.



Fitur what if analysis ini terdapat 3 perintah yaitu : 1. Scenario Manager 2. Goal Seek 3. Data Table Scenario Manager adalah perintah yang digunakan untuk melihat beberapa hasil atau output dari beberapa kemungkinan input yang berlainan. Perintah ini biasanya digunakan dalam analisa data atau analisa suatu project untuk melihat beragam kemungkinan perhitungan yang mungkin bisa terjadi. Goal Seek adalah perintah untuk mencari berapa inputan atau nilai awal agar anda mendapatkan output atau nilai akhir sesuai dengan yang diharapkan. Data Table adalah perintah untuk mencari nilai hasil atau output secara cepat dari beberapa nilai input yang berbeda.



Menggabungkan Tabel excel dengan Consolidate BY BUNG_BRIBIL · PUBLISHED MARCH 23, 2018 · UPDATED MAY 24, 2018 Consolidate adalah Fasilitas pada excel yang berfungsi untuk menggabungkan beberapa data dan memprosesnya secara statistik. dengan fasilitas ini kita dapat memperoleh hasil konsolidasi dari data berupa rata-rata, penjumlahan total, simpangan baku, dan lainya. Dalam penggabungan data ini, sumber datanya dapat diperoleh dari beberapa file atau dari beberapa worksheet yang berbeda.



Penggabungan akan terjadi dengan detail walaupun antar data memiliki perbedaan karakteristik. Misalnya data dari file A memiliki data dari tanggal 1 sd 10, namun data file B berasal dari data tanggal 8 sd 16. Dengan menggunakan consolidates, maka data yang digabungkan tadi akan menunjukkan data dari tangal 1 sd 16 yaitu penggabungan dari data file A dan file B. Penggunaan Consolidate di Excel Contoh Kasus : Berikut adalah data penjualan mobil dari bulan Januari – Desember 2012 dari sebuah toko Mobil di Jakarta, Surabaya, dan Semarang yang hasilnya akan di letakan pada worksheet “Consolidate”.



Berikut ini Cara penggunaanya : 1. Buka File latihan Consolidate.xlsx 2. Pilih worksheet tujuan “consolidate” 3. Letakan pointer area data 4. Masuk ke Tab Data kemudian klik Icon Consolidate 5. Selanjutnya akan muncul jendela Consolidate. 



Pada menu Function, Pilih sesuai kebutuhan disini kita akan gunakan “Sum”







Pada menu Reference, Klik worksheet Jakarta lalu blok datanya dari A4 sdd L24







Lalu klik Add







Klik worksheet Surabaya lalu blok datanya dari A2 sdd L21







Lalu klik Add







Klik worksheet Semarang lalu blok datanya dari A3 sdd L23







Lalu klik Add







Selanjutnya tandai pilihan cheklist Top row, Left Column, dan Create link to source data. gunakan top row dan left column untuk mengidentifikasi judul pada baris paling atas dan judul kolom paling kiri. secara otomatis excel akan mengelompokan item data unik dari setiap data untuk di rekap dengan fitur ini.







Sesuaikan



dengan



pengaturan



seperti



tampilan



berikut



ini



:



6. Kemudian Klik OK 7. Hasil dari consolidasi tampilnya seperti berikut ini : “anda dapat klik angka 1 atau 2



pada



pojok



kiri



atas



untuk



melihat



detail



datanya.



Hasil dari consolidasi 3 data tersebut ditampilkan dalam sistem grouping yang di link dengan sumber datanya. anda dapat mencoba beberapa opsi dalam fitur consolidates ini. kami juga sudah mempersiapkan latihan dalam file latihan untuk anda yang dapat anda download pada web ini. Semoga bermanfaat….



Cara Membuat Pivot Table di Excel BY BUNG_BRIBIL · PUBLISHED MARCH 29, 2018 · UPDATED MAY 24, 2019 Pivot Table adalah fasilitas excel yang digunakan untuk merangkum, menganalisa dan membuat suatu laporan data yang disajikan dalam bentuk tabel. Dengan menggunankan Pivot Table kita dapat membuat satu laporan yang dinamis dari sumber data yang besar. pivot tabel bekerja dengan matriks qube sebagai back end aplikasinya, sehingga kita dapat mengolah data secara dinamis untuk menganalisa data dalam bentuk kolom atau baris. pada materi ini kita akan membuat Pivot Table dari satu data



fitur pivot table ini sangat cocok untuk anda yang bekerja di level managerial, perhitungan pada pivot dilakukan secara otomatis tanpa anda belajar fungsi atau rumus excel, dengan pivotTable anda sudah dapat membuat suatu laporan yang dapat anda analisa untuk pengambilan keputusan dengan tepat dan cepat. Cara Membuat Pivot Table Sebelum anda mengolah data dengan PivotTable ada 3 syarat yang harus diperhatikan sebelum anda membuat pivot table, dimana data atau tabel yang akan kita olah memenuhi syarat tabel yang ideal : 



Terdapat judul kolom atau header untuk satu jenis data. hal ini dikarenakan pivot table akan mengambil judul kolom sebagai nama field dalam tabelnya







Tidak terdapat cell yang dimerge dalam satu tabel.







Tidak terdapat kolom dan Baris kosong.



Catatan : Tipe table sebagai sumber data PivotTable harus dalam bentuk data range, anda tidak diperkenankan membuat Pivot Table dari table dalam bentuk “Format As Table” karena mengandung xml.



Langkah-langkah membuat Pivot Table : 1. Buka File latihan “Pivot.xlsx” 2. Letakan Pointer pada area data. 3. Masuk ke Tab Insert kemudian Pilih PivotTable (seperti gambar diatas). 4. Pada Window Create PivotTable Ada 2 pertanyaan, Choose the data that you want to analyse yaitu memilih data yang akan dianalisa. 



Select a table or range (Blok range data yang akan di buat pivotTable)







Use an external data source (mengambil data dari luar file Excel)







Use this workbooks Data model (untuk versi 2013 keatas)



5. Choose where you want the PivotTable report to be placed , yaitu memilih lokasi dimana hasil pengolahan Pivot Tabel akan ditempatkan 



New Worksheet (Pivot akan di buat di worksheet baru)







Existing Worksheet (worksheet yang sedang aktif)



6. Sesuaikan dengan gambar dibawah ini : 



Pada table/range : Akan terisi otomatis jika format tabel sudah ideal, atau anda dapat blok tabel manual pada range B7:N51







Pada pilihan lokasi pivotTable Pilih New Worksheet, agar kita mudah untuk analisa



datanya.



7. Klik OK dan PivotTable akan dibuat pada worksheets baru di “Sheet1” berikut ini tampilannya



8. Selanjutnya siap kita proses datanya sesuai kebutuhan. Cara menggunakan PivotTable Setelah kita membuat PivotTable pada worksheet baru sebelum kita memprosesnya lebih jauh, sebaiknya kita pahami dulu bagian-bagian pada Pivot Table. ada 2 tampilan window pada pivotTable yaitu : “lihat gambar diatas” 



Report Pivot Area adalah area cell untuk menempatkan dan menampilkan data report.







PivotTable Field List adalah window yang berfungsi sebagai tools untuk mengatur report pivotTable.



Pada PivotTable Field list terdapat 4 blok area : 



Filter adalah area untuk menempatkan data field yang akan difilter pada pivotTable.







Rows adalah area untuk menempatkan data field dalam bentuk Baris.







Column adalah area untuk menempatkan data field dalam bentuk kolom.







Values adalah area untuk menempatkan data field yang akan dilakukan perhitungan.



Langkah-Langkah membuat PivotTable :



Contoh kasus : Anda diminta untuk membuat Pivot table dari data “sales summary” diatas, untuk menampilkan laporan data berdasarkan Product, Day, dan hitung berapa Total penjualan masing-masing penjualannya. Lakukan prosedur berikut Ini : 1. Buatlah pivotTable dengan langkah-langkah seperti diatas. 2. Drag Field “Day“, “Product“, dan “Total” dari Pivotable Field List ke Area Pivot, atau Gunakan Ceklist untuk menampilkan data pada PivotTable report. 3. Letakan Field “Day” dan “Product” pada area “ROWS” 4. Letakan Field “Total” pada area “VALUES” (setiap field yang mau di hitung letakan pada field Values”



5. Laporan akan otomatis terbentuk di area PivotTable Report. tampilanya seperti dibawah



ini.



Dari tampilan diatas kita dapat melihat dengan mudah laporan penjualan untuk masing-masing hari beserta kode product yang terjual dan total nilainya tanpa menggunakan rumus atau fungsi. untuk melihat detail penjualan dalam perhitunganya anda juga dapat double klik pada setiap nilai di kolom “sum of total”. Cross tabulation PivotTable Selain meletakan fileld pada area “ROWS”, anda juga dapat meletakan object pada area “COLUMN” atau yang sering disebut analisis Cross tabulation pada pivottable dilakukan untuk melihat laporan data berdasarkan baris “ROWS” dan Kolom “COLUMN”. kita dapat melihat perhitungan yang berhubungan antara 2 variabel. “Caranya



adalah



dengan



memindahkan



area “COLUMN” seperti tampilan berikut ini”



Field



“Product”



atau



“Day”



ke



Filter pada Pivot Table Setelah anda membuat laporan anda juga dapat memfilter laporan sesuai dengan kriteria yang diinginkan. filter pada Pivot Table digunakan untuk analisa data lebih spesifik. sebagai ontoh dari data diatas anda akan menampilakan laporan yang harinya adalah “FRIDAY” saja. 1. Pemindahkan Field



“Day” ke area “FILTER” seperti tampilan berikut ini”



2. Klik



menu



Report



Filter



dan



Cheklist



“Fry”



saja.



3. Klik OK Anda juga dapat melakukan filter pivotTable dengan menampilkan beberapa data dengan cara cheklist item data seperti gambar diatas dengan mengaktifkan pilihan “select multiple item“.



Membuat Dashboard Excel Sederhana dengan Slicer dan PivotChart BY BUNG_BRIBIL · PUBLISHED APRIL 2, 2018 · UPDATED APRIL 23, 2018 Dalam pembuatan laporan di excel penyajian data berupa angka-angka yang kompleks tentu akan menjemukan. biasanya penyajian data dalam bentuk gambar dan diagram tentu akan lebih menarik dan lebih mudah dalam membaca datanya. Kebutuhan Membuat dashboard untuk keperluan reporting di excel anda dapat memanfaatkan Pivot Table yang dinamis dengan kombinasi tools excel seperti PivotChart dan Slicers. Dari sumber data yang sangat besar dan kompleks, kita dapat buatkan laporan yang cukup interaktif. sebelum melanjutkan materi ini diharapkan anda sudah menguasai Penggunaan Pivot Table terlebih dahulu, jika anda baru mulai belajar kami sarankan untuk mempelajarinya terlebih dahulu.



Gambar diatas adalah contoh tampilan dashboard excel sederhana yang dibuat menggunakan PivotTable, Insert Slicer dan PivotChart. Ketiga tools ini dapat kita kombinasikan sesuai dengan fungsinya masing-masing. Insert Slicer adalah fasilitas excel yang digunakan untuk filter tabel dan pivottable dengan bentuk tombol interaktif. untuk penggunaanya anda cukup klik tombol slicer “Day” dan “Product“, dengan pilihan data sesuai kebutuhan. Bagaiman cara membuat dan Langkahlangkahnya, yuk kita coba. Contoh Kasus : Kita diminta membuat laporan dari data “sales summary for january”, buatlah Report berdasarkan “day”, “product”, dan hitung “total”. tampilkan pergerakan data dengan pivotchart dan Slicer sebagai button untuk filter datanya.



Langkah-langkah membuat Dashboard : 1. Buka File Pivot.xlsx 2. Pada Worksheet “january” Letakan kursor pada area data 3. Klik tab Insert kemudian pilih PivotTable, kemudian akan menampilkan dialog box create PivotTable. Pilih table atau range dan buatkan report pada worksheet baru 4. Buatlah laporan menggunakan PivotTable berdasarkan “Day“, “Product“, dan “total“. 



Letakan Field “Day” dan “Product” pada Area ROWS







Letakan Field “Total” Pada Area VALUES



5. Hasilnya tampilanya adalah berikut ini : 



Anda dapat atur Layout PivotTable menggunakan “show In tabular Form“







Agar



tampilan



lebih



menarik



tambahkan



“PivotTableStyles“



6. Tambahkan PivotChart dari data PivotTable 7. Letakan kursor pada area PivotTable, masuk ke tab Analyze pilih PivotChart



8. Pilih tipe Chart sesuai dengan bentuk data yang Cocok, “kita dapat menggunakan PivotChart



bentuk



“Line”



kemudian



Klik







Atur posisi chart, anda dapat letakan di samping data pivot table







Pada



tab



Design



Ubah



Tipe



9. Tambahkan Insert Slicer. 



Letakan pointer pada area PivotTable







Pada Tab Analyze klik Insert Slicer



Chart



dengan



“ChartStyles



OK



10”







Pilih Field berdasarkan “Day” dan “Product” kemudian klok OK.







Atur Posisi Slicer : Anda dapat ubah bentuk slicer di Slicer Option.



10. Hasilnya tampilnnya berikut ini : Untuk penggunaanya anda dapat slicer “day” atau slicer “Product”. untuk memilih laporan slicer lebih dari 2 item gunakan CTRL+klik atau klik Tombol Multi select (ALT+S). ketika button slicer kita klik data pada pivot table dan pivot chart akan upate sesuai dengan seleksi yang



kita



pilih.



pembuatan dashboard ini masih sangat sederhana, anda dapat tambahkan objek yang lain untuk menampilkan data dari tabel ini, sebagai contoh untuk menampilkan data field time dengan sparkline, kemudian menambahkan conditional formating pada pivot untuk menandai data dengan simbol atau warna sesuai kondisi, dan masih banyak lagi yang bisa kita kembangkan.



Cara Proteksi Cell (Range), Worksheet, dan Workbook Excel Ada kalanya dalam bekerja dengan excel, anda ingin agar file-file yang berhubungan dengan pekerjaan anda tidak dapat diakses oleh siapapun tanpa izin dari anda baik itu untuk mengubah nilai, memberikan format, mengubah rumus atau formula dan lain sebagainya. agar file excel anda aman dari tangan-tangan yang tidak bertanggung jawab anda dapat memproteksi file excel dengan encrypt with password yang sudah dibahas pada materi sebelumya. selain proteksi untuk file excel, ada beberapa macam proteksi untuk objek-objek di excel yang dapat anda lakukan untuk melindungi objek didalamnya yaitu Proteksi Cell (range), proteksi worksheet (sheet), dan proteksi workbook.



Proteksi Cell dan Range Excel Biasanya proteksi cell ini digunakan ketika kita bekerja dengan sharing workbook, yaitu bekerja dengan satu file excel yang dikerjakan secara bersama-sama. proteksi ini berguna untuk membatasi user untuk melihat, mengubah, atau menambahkan data.



Contoh Penggunaan : Dalam lembar kerja diatas “BUDGET 2018” akan dikerjakan bersama. untuk tiap-tiap divisi dapat input data sesuai dengan kolom yang telah



ditentukan. divisi IT hanya dapat mengisi di kolom cell IT saja, HRD hanya dapat mengisi kolom HRD saja, dan selanjutnya. Langkah-langkah 1. Buka File Protection_Excel 2. Blok data untuk HRD yaitu blok cell D6 sampai dengan D17 3. Klik



tab



Review,



kemudian



klik



Allow



Users



to



Edit



Range



4. Pada window allow user to edit range. pilih New 5. Selanjutnya



pada



Pada Pada



Title, Refers



to



Cells,



otomatis



New



Range.



Ketik



HRD



blok



cell



D6



sd



D17



Pada Range Password, ketik “hrd” 6. Klik OK, kemudian masukan confirm password yaitu “hrd”. Klik OK kembali. 7. Lakukan langkah seperti diatas untuk kolom IT, STAFF, dan GA. hasilnya tampilannya



seperti



8. Klik Apply kemudian klik Protect Sheet.



berikut



ini



9. Masukan password untuk unprotect sheet misal “123”



10. Klik Ok kembali. 11. Selanjutnya anda dapat simpan workbook ini dan data siap di share. Catatan : proteksi range atau cell akan bekerja setelah worksheet terproteksi. untuk unprotek atau edit password nda harus lepaskan protect sheets terlebih dahulu. Proteksi Sheet Excel Proteksi worksheet dimaksudkan untuk memproteksi halaman kerja/ worksheet secara keseluruhan, jadi bukan hanya cell tertentu saja. ketika kita memproteksi lembar kerja kita dapat melakukan beberapa pengaturan terhadap lembar kerja kita.



Cara proteksi sheet excel : 1. Buka File Protection_Excel



2. Pilih lembar kerja (sheet) yang akan di proteksi 3. Masuk



ke



Tab



Review



Pilih



Protect



sheets



4. Selanjutnya pada Password to unprotect sheet, ketikan : “worksheet” dan lakukan kembali untuk Confirm Password seperti tampilan berikut :



Pada bagian allow all user of this worksheet to : bagian ini merupakan pengaturan user untuk melakukan perubahan data setelah halaman terproteksi. anda dapat seleksi sesuai kebutuhan. 5. Klik OK kemudian simpan worksheets anda. Proteksi Workbook Excel Proteksi workbook dimaksudkan untuk memproteksi suatu workbook agar tidak bisa diubah-ubah. Jika proteksi ini dijalankan maka settingan di workbook, seperti settingan tampilan windows dan strukturnya. Cara Proteksi workbook : 1. Buka file yang akan di proteksi 2. Masuk



ke



tab



3. Masukan password “123”



Review,



lalu



klik



Protect



Workbook



4. Pastikan pilihan protect structure terseleksi kemudian klik OK. 5. Proteksi struktur ini akan membatasi user untuk melakukan insert, delete, rename, move, copy, hide atau unhide worksheets. Ketika kita melakukan proteksi terhadap file excel jangan lupa simpan password anda didalam notes untuk bakup ketika lupa password. dikarenakan excel tidak menyediakan fitur lupa password seperti facebook..hehehhe. tetapi jangan khawatir jika anda lupa password excelanda ada banyak tools yang bisa kita pakai untuk membukanya. kita akan bahas di materi selanjutnya.



Cara Sharing Workbook Excel Semua pekerjaan yang dilakukan secara bersama-sama tentu akan lebih mudah dan cepat selesai. tidak terkecuali di microsoft excel, kita dapat bekerja sama-sama dalam satu file excel dengan fasilitas Sharing workbook excel. biasanya ketika satu file excel dalam jaringan di buka bersama-sama yang terjadi adalah user hanya bisa read



only atau membacanya saja akan tetapi jika kita gunakan fasilitas Sharing Workbook semua user yang mendapatkan akses file tersebut dapat membuka, edit, tambah, menghapus, memformat, dll.



Sharing workboook bekerja dengan cara copy dan merge (gabung) workbook, setiap user yang membuka file tersebut akan dicopykan satu workbook yang sama untuk dilakukan perubahan datanya, setelah selesai dan disimpan perubahan tersebut akan di merge kedalam file masternya. Event yang di gunakan dalam sharing ini adalah save workbook, jadi ketika user selesai edit data kemudian di simpan (SAVE), data akan di merge menjadi satu. pertanyannya adalah apabila ada beberapa user yang mengubah data yang sama, data siapakah yang akan tersimpan?? jawabanya adalah user yang menyimpan terakhir kali pada workbook tersebut. Cara Sharing Workbook Excel “tanpa Proteksi” 1. Buka File Latihan “Sharing workbook.xlsx” 2. Masuk



ke



Tab



Review



kemudian



pilih



Share



Workbook



3. Tampil window “Share workbook” dan atur seperti gambar dibawah ini. 



Pada tab “editing” Aktifkan dengan Checklist “allow changes by more than one



user…..”



Anda juga dapat lihat nama user yang sedang aktif mengakses file sharing. 



Pada tab Advance, atur track changes “keep hangehistory” sesuai dengan batas waktunya. Update Changes berfungsi untuk mengatur event merge WORKBOOK atau PENGGABUNGAN DATANYA.







4. Klik OK 5. Secara otomatis workbook akan tersimpan dan workbook ini sudah siap kita share ke dalam jaringan. 6. Dapat anda Lihat nama file sudah berganti status menjadi “Shared” Kelemahan dengan Sharing workbook biasa ini adalah dari segi sitem keamananya sangat kurang, setiap user dapat mengaktifkan dan menonaktifkan status shared. setiap user juga dapat mengisi di semua area. Agar tidak terjadi hal yang demikian saat sharing workbook sebaiknya kita atur terlebih dahulu menggunakan fasilitas Track changes dan proteksi workbook dan range. Cara Sharing Workbook Excel dengan Track Changes dan Proteksi Sharing workbook excel dengan cara ini dapat anda gunakan untuk mendeteksi perubahan apa saja yang terjadi dalam file excel yang telah di share. siapa yang edit datanya, datanya apa, editnya dimana, jam berapa, semua akan terlihat denga fitur track changes ini. kemudian bagaimana cara penggunaannya, mari kita coba. Contoh kasus : Di sebuah perusahaan Telekomunikasi setiap manager divisi diminta untuk mengisi data budget tiap bulannya dengan cara sharing workbook dalam satu jaringan. User hanya boleh mengisi pada area masing-masing . bentuk tabelnya adalah sebagai berikut.



Langkah sharing workbook sebagai berikut : 1. Sebelum data ini kita share kedalam jaringan kita dapat atur proteksi range agar setiap



user



mengisi



kolom



sesuai



dengan



divisinya.



anda dapat lakukan dengan fasilitas Allow user to edit Range. untuk anda yang belum memahami dapat ikuti link belajar proteksi range. 2. Masuk ke Tab Review kemudian Klik Protect and Share workbook



3. Pada window Protect and share workbook Aktifkan checklist “Sharing with Track Changes” dan masukan password untuk memproteksi agar tidak sembarang user bisa mengaktikan dan menonaktifkan sharing file ini. masukan angka misal “123”. konfirmasi password kembali dengan “123”. kemudian Klik OK



4. Akan tampil peringatan “This ation will now save the workbook, Do you want to ontinue? Klik OK



5. Secara otomatis workbook akan tersimpan dan status workbook ini sudah dapat kita share ke dalam jaringan. 6. Lihat



nama



file



sudah



berganti



status



menjadi



“Shared”



Menghilangkan Sharing workbook Jika Data dalam sharing workbook sudah slesai dikerjakan anda dapat melihat perubahan data menggunakan fitur track changes untuk melihatnya. selanjutanya anda dapat nonaktifkan status shared workbook agar user tidak dapat edit kembali. 1. Pada tab Review tab, di grup Changes, Klik Share Workbook. 2. Dalam tab Editing, pastikan bahwa hanya kamu yang sedang membuka shared Workbook. 3. Nonaktifkan Allow changes by more than one user at the same time. This also allows workbook merging. Klik Yes. 4. Note Jika checkbox tidak ada, lakukan prosedur UnProtect Shared Workbook: 5. Klik OK untuk menutup kotak dialog Share Workbook. 6. Di TabReview,di grup Changes, Klik Unprotect Shared Workbook. 7. Masukkan Password proteksi sharing 8. Tekan OK