Fungsi SUM
Untuk menjumlahkan angka dalam rentang (sekelompok sel), tetapi fungsi SUM lebih mudah digunakan ketika bekerja dengan lebih dari beberapa angka. Misalnya =SUM(A2:A6) kemungkinan kecil memiliki kesalahan pengetikan daripada =A2+A3+A4+A5+A6.
Berikut adalah rumus yang menggunakan 2 rentang sel: =SUM(A2:A4,C2:C3) menjumlahkan angka dalam rentang A2:A4 dan C2:C3. Enter untuk mendapatkan total.
Untuk membuat rumus:
Ketik =SUM dalam sel, diikuti dengan tanda kurung buka (.
Untuk memasukkan rentang rumus pertama, yang disebut argumen (bagian data yang perlu dijalankan rumus), ketik A2:A4 (atau pilih sel A2 dan seret melalui sel A6).
Ketik koma (,) untuk memisahkan argumen pertama dari argumen berikutnya.
Ketikkan argumen kedua, C2:C3 (seret untuk memilih sel).
Ketik tanda kurung tutup ), lalu tekan Enter.
Fungsi Average
Fungsi Average di Microsoft Excel digunakan untuk mencari nilai rata rata dari suatu range tertentu. Bentuk penulisan fungsi Average di Microsoft Excel adalah =Average(cell awal : cell akhir). Maka rumusnya adalah =Average(C2:C6). Contoh seperti Pada gambar dibawah ini :
Fungsi Max
Fungsi Max di Microsoft Excel digunakan untuk mencari nilai tertinggi dari range tertentu. Bentuk penulisannya adalah =Max(cell awal : cell akhir). Maka rumusnya adalah =MAX(C2:C6). Contoh seperti pada gambar dibawah ini :
Fungsi Min
Fungsi Min di Microsoft Excel digunakan untuk mencari nilai terendah dari range tertentu. Bentuk penulisannya adalah =Min(cell awal : cell akhir). Maka rumusnya adalah =Min(C2:C6). Contoh seperti pada gambar dibawah ini :
Fungsi Count
Fungsi Count di Microsoft Excel digunakan untuk mencari jumlah total data yang ada pada range tertentu. Bentuk penulisannya adalah =Count(cell awal : cell akhir). Maka rumusnya adalah =Count(C2:C6). Contoh seperti pada gambar dibawah ini :
Fungsi Kondisi
Rumus IF Tunggal
IF(Logika; KondisiTerpenuhi; [KondisiTidakTerpenuhi])
Dari sintaksis di atas, dapat ketahui bahwa rumus atau fungsi IF pada excel terdiri dari 3 argumen:
Logika : Argumen ini berisi ekspresi logika yang bisa dievaluasi ke dalam nilai TRUE atau FALSE. Argumen ini merupakan kriteria acuan untuk menentukan nilai yang akan dihasilkan oleh rumus excel IF.
KondisiTerpenuhi : Nilai yang akan dihasilkan jika hasil evaluasi Logika bernilai TRUE. Argumen ini merupakan nilai yang akan dihasilkan jika kondisi terpenuhi.
KondisiTidakTerpenuhi : (Opsional) Nilai yang ingin hasilkan jika argumen Logika mengevaluasi ke FALSE.
Contoh Rumus IF Excel
=IF(1=1; "Benar"; "Salah")
hasil : 1=1 adalah True atau benar.
=IF(1=0; "Benar"; "Salah")
hasil : 1=0 adalah False atau Salah
Rumus IF Ganda
IF(Logika1; IF(Logika2; KondisiTerpenuhi2; [KondisiTidakTerpenuhi2]); [KondisiTidakTerpenuhi1])
Perhatikan contoh Rumus IF ganda di bawah ini :
Pada contoh tersebut rumus if ganda yang digunakan pada sel F2 adalah:
rumus : =IF(C2>60;IF(D2>60;"LULUS";"GAGAL");"GAGAL")
Rumus IF diatas melakukan uji logika ganda pada dua sel sekaligus secara bertahap:
Jika C2 lebih dari 60 maka lakukan uji logika lanjutan dengan rumus IF ke-2. Jika C2 tidak lebih dari 60 maka "GAGAL"
Pada rumus IF ke-2 jika D2 lebih dari 60 maka "LULUS", jika tidak maka "GAGAL".
Hasil akhir dari rumus if diatas adalah LULUS, sebab nilai cell C2 dan D2 sama-sama lebih dari 60.
Selanjutnya silahkan perhatikan contoh nomor 4. Pada contoh nomor 4 rumus if excel yg dipakai pada sel F5 adalah:
rumus : =IF(C5>60;IF(D5>60;"LULUS";"GAGAL");"GAGAL")
Rumus excel diatas menghasilkan nilai GAGAL sebab pada rumus if kedua, nilai dari D4 (60) tidak lebih dari 60.
Contoh if untuk dianalisa bersama :
Fungsi LOWER, UPPER dan PROPER
Ketiga fungsi ini digunakan untuk untuk mengkonversi huruf dalam text menjadi huruf kecil atau sebaliknya
LOWER : Konversi semua huruf menjadi huruf kecil
UPPER : Konversi semua huruf menjadi huruf kapital
PROPER : Konversi semua huruf pertama dalam setiap kata menjadi huruf kapital dan semua huruf lainnya menjadi huruf kecil
Syntak :
=LOWER(text)
=UPPER(text)
=PROPER(text)
Fungsi LEFT, RIGHT dan MID
Fungsi LEFT, RIGHT, dan MID digunakan untuk mengambil porsi atau bagian text berdasarkan posisi bagian tersebut didalam text induk.
LEFT : Mengambil satu atau lebih karakter dari sebelah kiri
RIGHT : Mengambil satu atau lebih karakter dari sebelah kanan
MID : Mengambil satu atau lebih karakter dimulai dari nomor urut karakter tertentu
Syntak
=LEFT(text,jumlah_karakter)
=RIGHT(text,jumlah_karakter)
=MID(text,nomor_urut_mulai,jumlah_karakter)
Penjelasan Rumus
Rumus LEFT digunakan untuk mendapatkan 4 karakter pertama dari text "INDONESIA memang hebat". Hasilnya adalah text "INDO"
Penggunaan rumus RIGHT hampir sama dengan rumus LEFT, hanya mengambil bagian dari sebelah kanan sebanyak 5 karakter. Hasilnya adalah text "hebat"
Rumus MID mengambil 6 karakter dimulai dari karakter ke-11, menghasilkan text "memang"
Fungsi FIND dan SEARCH
Kedua fungsi tersebut digunakan untuk menemukan text tertentu dari sebuah text induk dan mendapatkan angka/bilangan yang merupakan nomor urut posisi karakter pertama dari text yang dicari dalam text induk. Yang dimaksud dengan karakter dalam excel adalah semua angka, huruf maupun symbol yang dapat diketik atau insert symbol. Termasuk spasi dan pemisah baris juga merupakan sebuah karakter sehingga dapat dihitung posisinya dalam text.
FIND bersifat Case Sensitif artinya membedakan huruf kecil dan huruf kapital
SEARCH tidak bersifat Case Sensitif artinya tidak membedakan huruf kecil dan huruf kapital
Syntax
=FIND(text_dicari,text_induk,nomor_mulai_pencarian)
=SEARCH(text_dicari,text_induk,nomor_mulai_pencarian)
Penjelasan Rumus
Rumus SEARCH digunakan untuk mencari karakter text “e” di dalam text "INDONESIA memang hebat". Pencarian dimulai dari nomor urut 1. Dikarenakan fungsi SEARCH bersifat tidak case sensitif, maka rumus ini akan menghasilkan bilangan 6 yang merupakan posisi huruf E dalam text "INDONESIA memang hebat". Dengan kata lain rumus search tidak membedakan antara huruf “e” dan “E”
Dengan cara yang sama dengan nomor 1 tetapi menggunakan rumus FIND. Diperoleh hasil 12 karena fungsi FIND bersifat case sensitif sehingga membedakan antara huruf “e” dan E.
Dengan cara yang sama dengan nomor 2, tetapi pencarian dilakukan mulai huruf ke 13. Menghasilkan bilangan 19, yang merupakan nomor urut huruf “e” yang ditemukan pertama dengan pencarian dimulai nomor urut 13.
Rumus SEARCH digunakan untuk mencari karakter text “MEMANG” di dalam text "INDONESIA memang hebat". Pencarian dimulai dari huruf ke-1. Menghasilkan nilai 11
Hampir sama dengan nomor 4 tetapi menggunakan fungsi FIND. Menghasilkan nilai error #VALUE karena fungsi FIND tidak bisa menemukan text “MEMANG” di dalam text "INDONESIA memang hebat". Hal ini karena fungsi FIND memilah antara huruf kecil dan besar (kapital) atau bersifat case sensitif
Fungsi SUBSTITUTE dan REPLACE
Fungsi SUBSTITUTE digunakan untuk mengganti text tertentu dalam sebuah text induk dengan text lainnya
Fungsi REPLACE digunakan untuk menghapus bagian tertentu dalam sebuah text induk dan menggantinya dengan text lain.
Syntak
=SUBSTITUTE(text_induk,text_diganti,text_pengganti)
=REPLACE(text_induk,nomor_awal,nomor_akhir,text_sisip)
Penjelasan Rumus
Rumus SUBSTITUTE digunakan untuk mengganti text “hebat” menjadi “Mantap” di dalam text INDONESIA memang hebat" sehingga hasilnya menjadi “INDONESIA memang Mantap"
Rumus REPLACE digunakan untuk mengganti dimulai huruf ke-6 sebanyak 4 huruf dalam text "INDONESIA memang hebat" dengan text “”, atau dengan text kosong. Oleh karena itu seakan-akan menghapus bagian “ESIA” dari text “INDONESIA”
Fungsi Vlookup di Excel
Vlookup merupakan singkatan dari Vertical Lookup, adalah sebuah formula di dalam Excel yang berfungsi untuk mencocokkan data dari dua daftar. VLOOKUP merupakan bagian dari fungsi LOOKUP dan juga HLOOKUP, INDEX dan MATCH. Dengan rumus Vlookup ini dapat mengisi data pada sebuah tabel berdasarkan data pada tabel referensi lain dengan menggunakan sebuah nilai yang spesifik. Bisa menggunakan fungsi Vlookup pada Excel mengikuti rumus sebagai berikut
Syntax : =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Dari syntax diatas bisa ketahui bahwa cara Vlookup pada rumus Excel mempunyai empat komponen yaitu:
Lookup_value: Nilai yang ingin dicari. Sebuah nilai yang akan dicocokkan dengan nilai yang ada pada table_array lookup_value harus berada paling kiri pada tabel array.
Table_array: Tabel referensi yang akan diambil datanya. Ingat, nilai pencarian harus berada di kolom pertama agar Vlookup berfungsi dengan baik. Hal itulah yang menyebabkan mengapa rumus Excel Vlookup tidak berfungsi.
Col_index_num: Suatu nomor kolom yang ada pada table_array yang mengandung nilai data dan ingin diambil nilainya.
Range_lookup: Diisi menggunakan nilai Boolean TRUE atau FALSE. Nilai logika dalam rumus Vlookup dipakai sebagai acuan untuk menetapkan apakah ingin Vlookup menemukan hasil yang sama persis atau juga hanya menemukan nilai yang mendekati.
TRUE: Vlookup akan menemukan lookup_value dalam table_array yang sama atau hanya mendekati. Jika pada nilai yang sama tidak dapat ditemukan maka nilai terbesar selanjutnya yang kurang dari lookup_value untuk dihasilkan.
FALSE: Vlookup akan menemukan lookup_value dalam table_array yang hanya sama. Jika tidak dapat ditemukan maka Vlookup akan menghasilkan pesan terakhir yaitu error #NA!
Contoh Rumus VLOOKUP dengan Nilai yang Sama
Dalam contoh kasus kali ini, akan dicoba untuk mengisi besaran gaji masing-masing karyawan berdasarkan golongan mereka menggunakan rumus Excel Vlookup. Ada beberapa langkah yang harus dilakukan untuk mencari besaran gaji secara otomatis menggunakan Vlookup, dan berikut ini langkah-langkah selengkapnya.
Ketikkan =VLOOKUP( pada kolom Gaji Tabel B.
Klik kolom Golongan karyawan yang akan diisi gajinya dan tambahkan tanda ;. Jadi di kolom H2, akan terlihat rumus VLOOKUP menjadi =VLOOKUP(G2;
Blok data yang ada pada tabel A.
Sisipkan tanda $ pada angka dan nomor kolom agar bisa di-drag ke bawah, dan beri tanda ;. Rumus akan terlihat menjadi =VLOOKUP(G2;$A$2:$C$4;.
Tambahkan angka 2 dan juga ; pada rumus, sehingga menjadi =VLOOKUP(G2;$A$2:$C$4;2. Ini dikarenakan yang akan dicari adalah gaji, dan gaji ada di kolom ke 2 pada tabel A.
Tambahkan FALSE, tutup rumus dengan tanda ) lalu tekan enter. Tampilan akhir rumus menjadi =VLOOKUP(G2;$A$2:$C$4;2;FALSE).
Contoh Rumus VLOOKUP dengan Nilai Mendekati
Contoh kedua adalah cara Vlookup Excel untuk mencari nilai yang mendekati atau tidak sama persis. Biasanya hal ini digunakan untuk menentukan kelompok nilai dari besaran angka tertentu. Contoh yang digunakan kali ini adalah mencari nilai akhir mahasiswa berdasarkan poin yang dikumpulkan. Berikut ini beberapa langkah yang bisa diikuti untuk membuat formula Vlookup dengan nilai yang mendekati.
Ketikkan =VLOOKUP( pada kolom Nilai Akhir di Tabel B.
Klik jumlah poin yang didapatkan oleh mahasiswa tersebut dan tambahkan tanda ;. Tampilan rumus akan menjadi =VLOOKUP(G2;.
Blok tabel yang digunakan sebagai acuan nilai pada Tabel A.
Sisipkan tanda $ di huruf dan angka agar bisa di-drag ke bawah. Tambahkan tanda ;. Tampilan rumus menjadi =VLOOKUP(G2;$A$2:$B$5;.
Tambahkan angka 2 dan juga tanda ; ke dalam rumus. Ini dikarenakan nilai berada di kolom ke 2. Tampilan rumus menjadi =VLOOKUP(G2;$A$2:$B$5;2.
Tambahkan TRUE dan tutup formula VLOOKUP. Tampilan akhir rumus akan menjadi =VLOOKUP(G2;$A$2:$B$5;2;TRUE).
Alhasil, Excel akan secara otomatis melakukan pembulatan ke kelompok nilai terdekat dan menentukan nilai yang paling sesuai. Pastikan bahwa rentang nilai dalam tabel pembanding ditulis dari yang paling kecil ke besar, karena rumus ini akan mencari nilai yang paling sesuai dari atas ke bawah.
Contoh Rumus VLOOKUP Kombinasi IFNA
Selain dua contoh kasus di atas, salah satu rumus yang paling sering dikombinasikan dengan Vlookup adalah IFNA. Fungsi ini digunakan untuk mengubah jika rumus Vlookup menghasilkan #N/A menjadi nilai tertentu. Cara menuliskan kombinasi rumus IFNA dan VLOOKUP dalam satu penulisan dengan menggunakan contoh kasus berikut:
Pertama, ketik Fungsi IFNA pada cell C13 seperti berikut: =IFNA(.
Kedua, masukkan fungsi VLOOKUP dan sesuaikan dengan tujuan. Dalam contoh ini, rumus akan menjadi seperti berikut:
=IFNA(VLOOKUP(A13;$A$2:$C$6;3;FALSE);VLOOKUP(A13;$E$2:$G$5;3;FALSE).
Ketiga, ketik operator pemisah rumus ( , atau ; ).
Keempat, masukkan nilai yang diinginkan jika Vlookup menghasilkan #N/A. Misalnya, kita ingin hasil menjadi Tidak Ada, maka ketik “Tidak Ada”.
Terakhir, ketik tanda tutup kurung, =IFNA(VLOOKUP(A13;$A$2:$C$6;3;FALSE);VLOOKUP(A13;$E$2:$G$5;3;FALSE);”Tidak Ada”) kemudian tekan Enter.
Membuat grafik di Excel
1. Pilih data
Adapun langkah pertama adalah memilih data yang ingin dijadikan bentuk grafik. Biasanya dapat memilih bagian dari tabel yang ingin dijadikan grafik. Misalnya pada contoh gambar di bawah, terdapat data pemasukan selamat setengah tahun pertama. Jika ingin memasukkan seluruh data tinggal memblok semuanya. Namun, jika hanya ingin data tertentu bisa memilih tabel dan kolom yang diinginkan.
2. Pilih Insert pada menu bar
Setelah memilih data yang diinginkan, pilihlah insert yang ada di menu bar pada bagian kiri atas. Nantinya akan melihat ikon dari beberapa jenis grafik. Jika ingin melihat jenis grafik secara jelas, dapat memilih Recommended Charts.
3. Pilih jenis grafik yang diinginkan
Jika memilih Recommended Charts nantinya akan muncul halaman pop-up dengan berbagai pilihan grafik. Untuk melihat pilihan grafik secara lengkap dapat memilih All Charts. Selanjutnya akan muncul semua jenis grafik yang ada di Ms Excel yang bisa dipilih.
Selain itu, juga bisa memilih berbagai macam bentuk atau style dari masing-masing jenis grafik. Bisa dilihat pada contoh gambar di atas, terdapat berbagai style dari jenis column.
4. Atur posisi grafik ke dalam worksheet
Setelah memilih grafik yang cocok, nantinya grafik akan muncul secara otomatis pada worksheet. Namun, agar tabel data tidak terganggu, maka perlu mengatur posisi dan juga ukuran. Adapun langkah yang perlu dilakukan adalah mengarahkan kursor ke bagian grafik, klik dan geser. Sedangkan untuk mengubah ukuran grafik bisa mengarahkan kursor pada bagian sudut grafik dan menariknya.
5. Ubah judul grafik
Selanjutnya juga bisa mengubah nama atau judul dari grafik dengan mengklik bagian tulisan yang ada di bagian atas grafik.
Mengubah bentuk dan value pada grafik
Selain memasukkan grafik ke dalam worksheet, terkadang bagi beberapa orang merasa ada bagian yang kurang pada grafik yang telah dibuat sebelumnya. Oleh karena itu, bagi yang ingin mengubah, menambah, atau mengurangi bagian-bagian yang ada pada grafik, berikut beberapa caranya.
1. Menambah elemen pada grafik
Elemen merupakan bagian-bagian yang dapat membuat grafik menjadi lebih detail seperti, garis bantu, angka pada setiap bar, dan lainnya. Untuk menambah atau mengurangi elemen ini, cukup mengklik grafik dan secara otomatis akan muncul icon (+) di bagian samping kanan. Nantinya tinggal menceklis elemen yang ingin dimasukkan atau dihilangkan.
2. Mengubah style dan warna grafik
Adapun cara untuk mengubah bentuk tampilan grafik tidak jauh berbeda dengan menambah elemen. Tinggal mengklik ikon kuas pada bagian samping grafik. Nantinya akan muncul berbagai bentuk grafik pada pilihan style dan pilihan tema warna pada pilihan color.
3. Mengedit value grafik
Selain Mengubah tema, juga bisa mengedit value atau nilai-nilai yang ada pada grafik. Dengan mengklik ikon filter yang ada di samping grafik, bisa untuk menambah atau menghilangkan value. Contoh dapat melihat gambar di bawah. Pada grafik, bisa melihat beberapa value seperti series yang merupakan nama bulan dan categories untuk value pemasukan per minggu. Misalnya dengan menghilangkan ceklis pada 'Pemasukan Minggu 3', maka bar dan value 'Minggu 3' akan hilang pada grafik.
