Contoh Rumus Excel Praktis

Tuesday, April 10, 2018

Rumus VLOOKUP Untuk Mencari Nama Barang

Salah satu kegunaan rumus VLOOKUP adalah untuk mencari nama barang berdasarkan code yang sudah diketahui. Tidak perlu repot-repot melelahkan mata untuk mencari nama barang dalam sebuah list data. Namun cukup buat saja sebuah rumus VLOOKUP menggunakan program Ms Excel. Selanjutnya kita cukup menginput code tertentu, maka nama barang seketika akan muncul.

Sebelum melanjutkan terlebih dahulu mari kita simak video yang menjelaskan cara membuat rumus VLOOKUP PALING GAMPANG berikut ini.

Untuk membuat rumus menggunakan fungsi VLOOKUP ini tidaklah terlalu sulit. Silahkan perhatikan baik-baik contoh berikut:

Contoh dibawah ini akan lebih mudah difahami jika anda meng-copy tabel contoh ke dalam program Ms Excel. Copy kan tepat pada sel A1.

A1	B	C	D
2	Code :	11345
3	Nama Barang :	=VLOOKUP(C2,B7:C13,2,0)	>> Mencari code 11345 (sel C2) pada range B7:C13 kolom pertama (kolom code) dan mendapatkan data pada kolom ke-2 (kolom nama barang) yang sebaris dengan code yang dicari. Untuk code 11345, nama barangya = mendoan. Jika kode barang yang sesuai tidak ditemukan maka rumus akan menghasilkan nilai error #N/A
4
5
6	Code	Nama Barang
7	11232	Pisang Goreng
8	11234	Ikan Asin
9	11345	Mendoan
10	11355	Bala-Bala
11	11357	Tempe Kering
12	11564	Kue Donat
13	12664	Tahu Bulat

Cara kerja rumus VLOOKUP untuk mencari nama barang berdasarkan code yang sudah diketahui:

Tentukan code yang akan dicari, bisa diketikan langsung dalam rumus VLOOKUP, namun disarankan code tersebut diletakan dalam sebuah sel seperti contoh tabel di atas yaitu sel C2 yang berisi nilai code 11345
Tentukan sebuah tabel yang memiliki kolom berisi list code dan kolom lainnya berisi list nama barang. Pastikan list code harus berada pada kolom paling kiri atau kolom pertama. Dalam contoh diatas kita menggunakan sebuah tabel pada range B7:C1 yang berisi 2 kolom. Kolom ke-1 berisi list code, dan kolom ke-2 berisi list nama barang. Anda bisa memodifikasi cara penulisan range B7:C1 menjadi $B$7:$C$1 supaya jika dicopy ke sel lainnya akan tetap mengacu pada referensi range tersebut. Alternative lainnya adalah dengan membuat nama range.
Tentukan urutan kolom dalam tabel yang berisi nama barang, dalam contoh di atas adalah kolom ke-2. Angka ini bisa disesuaikan tergantung tabel referensi yang tersedia. Dalam praktek nyata mungkin saja anda memiliki tabel lain yang berisi banyak kolom. Namun yang perlu diingat adalah kolom yang menjadi kunci pencarian harus terletak di sebelah kiri.
Angka 0 (Nol) menunjukan bahwa rumus VLOOKUP akan mencari benar-benar angka yang matching dengan code yang diketahui, bukan sekedar angka pendekatan. Jika menghilangkan angka nol pada parameter paling akhir dari rumus VLOOKUP, maka mungkin saja rumus akan menghasilkan nilai error ataupun hasil yang tidak benar.

Demikian penjelasan ringkas cara menggunakan Rumus VLOOKUP untuk mencari nama barang.

Semoga bermanfaat.

Sunday, April 8, 2018

Rumus IF Untuk Range Nilai Ujian

Dalam penilaian akhir ujian siswa, Nilai siswa tidak hanya disajikan dalam bentuk bilangan, tetapi juga biasanya dikelompokan dalam kategori huruf, misalnya A, B, C, D, dan E, dimana masing-masing huruf tersebut mewakili range nilai tertentu. Untuk mengerjakan tugas ini, excel dapat dengan mudah melakukannya secara otomatis dengan bantuan rumus IF. Nah pembahasan kali ini akan membahas bagaimana cara menggunakan Rumus IF untuk konversi range nilai menjadi huruf.

Contoh kasus.

Anggaplah kita ditugaskan membuat formula excel untuk menyajikan kategori range nilai siswa menjadi 5 kelompok nilai A, B, C, D dan E dengan ketentuan klasifikasi range pengelompokan nilai sebagai berikut:

• Kelompok A, nilai 90 atau lebih

• Kelompok B, nilai 80 lebih tetapi kurang dari 90

• Kelompok C, nilai 70 lebih tetapi kurang dari 80

• Kelompok D, nilai 60 lebih tetapi kurang dari 70

• Kelompok E, nilai kurang dari 60

Perlu dingat bahwa:

Untuk penglompokan nilai dengan range meningkat atau menurun, maka kita memerlukan sejumlah N-1 rumus IF, dimana N = jumlah kategori.

Sehingga untuk pengelompolan 2 kategori diperlukan 1 fungsi IF, contohnya adalah rumus IF untuk keterangan lulus dan tidak lulus. Untuk mengelompokan nilai menjadi 3 kategori diperlukan 2 fungsi IF bertingkat, contohnya kategori negatif, netral dan positif.

Maka untuk menyajikan range nilai siswa menjadi 5 kategori, kita memerlukan 4 fungsi IF.

Anggaplah data nama siswa terletak pada kolom A, data nilai pada kolom B dan kategori huruf terletak dalam kolom C. Baris pertama berisi header kolom, sedangkan data dimulai pada baris ke-2. Selanjutnya maka kita dapat mengetikan rumus IF pada sel C2 sebagai berikut:

=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","E"))))

Copy rumus tersebut sampai ke baris akhir data, sehingga kita bisa mendapatkan kategori nilai huruf pada masing-masing baris, seperti diperlihatkan dalam screenshot berikut:

Cara kerja rumus IF untuk range nilai (5 kategori)

Adapun cara kerja rumus IF untuk mendapatkan nilai huruf kategori tersebut dapat dijelaskan sebagai berikut:

Fungsi IF pertama akan mengecek apakah nilai memenuhi syarat pertama (>=90). Jika iya maka rumus akan langsung menghasilkan nilai kategori pertama yaitu huruf A. jika tidak maka lanjut ke IF ke-2
Fungsi IF ke-2 akan mengecek apakah nilai memenuhi syarat ke-2 (>=80). Jika iya maka rumus akan langsung menghasilkan nilai kategori kedua yaitu huruf B. jika tidak maka lanjut ke IF ke-3
Fungsi IF ke-3 akan mengecek apakah nilai memenuhi syarat ke-3 (>=70). Jika iya maka rumus akan langsung menghasilkan nilai kategori ketiga yaitu huruf C. jika tidak maka lanjut ke IF ke-4
Fungsi IF ke-4 akan mengecek apakah nilai memenuhi syarat ke-4 (>=60). Jika iya maka rumus akan langsung menghasilkan nilai kategori ketiga yaitu D.
Jika range nilai tidak memenuhi syarat empat kategori sebelumnya maka rumus akan menghasilkan nilai kategori ke-5 haitu huruf E. ini terjadi jika nilai kurang dari 60.

Demikian cara menggunakan rumus IF untuk pengelompokan range nilai, beserta penjelasan ringkas bagaimana rumus IF bertingkat ini bekerja.

Semoga bermanfaat.

Rumus VLOOKUP Untuk Pengelompokan Nilai

Rumus VLOOKUP Untuk Pengelompokan Nilai Ujian

Rumus VLOOKUP ternyata dapat menggantikan rumus IF dalam pengelompokan nilai. Bagi anda yang masih sering dibingungkan dengan penggunaan rumus IF bertingkat, maka beralih ke penggunaan fungsi VLOOKUP bisa memberikan anda alternative baru dan memberikan solusi yang tepat dan mudah karena rumus ini jauh lebih sederhana dan pendek dibandingkan menggunakan fungsi IF.

Studi kasus

Anggaplah anda ingin mengkategorikan rentang nilai 0 sd 100 menjadi 10 kelompok nilai.

Kelompok A, nilai 90 atau lebih
Kelompok B, nilai 80 ke atas tetapi kurang dari 90
Kelompok C, nilai 70 ke atas tetapi kurang dari 80
Kelompok D, nilai 60 ke atas tetapi kurang dari 70
Kelompok E, nilai 50 ke atas tetapi kurang dari 60
Kelompok F, nilai 40 ke atas tetapi kurang dari 50
Kelompok G, nilai 30 ke atas tetapi kurang dari 40
Kelompok H, nilai 20 ke atas tetapi kurang dari 30
Kelompok I, nilai 10 ke atas tetapi kurang dari 20
Kelompok J, nilai 0 ke atas tetapi kurang dari 10

Bayangkan jika kita lebih memilih menggunakan rumus IF untuk mengelompokan data sesuai kriteria di atas. Tentu kita harus memeras otak lebih bayak untuk menyelesaikannya. Dan jika kebetulan masih menggunakank versi excel yang lama (2013 ke sana) pengelompokan ini tidak bisa dilakukan menggunakan rumus IF, karena limitasi IF bertingkat pada versi excel tersebut.

Untuk excel versi 2017 atau yang lebih baru, fungsi IF memang bisa menjadi solusi kasus tersebut. Tetapi menurut saya hal ini kurang efisien. Jika ada rumus yang lebih pendek, kenapa harus bersusah payah membuat rumus yang panjang.

Yuk, kita perhatikan penggunaan rumus IF untuk menyelesaikan kasus ini (asumsi nilai yang dicek ada di sel A1.

=IF(A1>=90,"A",IF(A1>=80,"B",IF(A1>=70,"C",IF(A1>=60,"D",IF(A1>=50,"E",IF(A1>=40,"F",IF(A1>=30,"G",IF(A1>=20,"H",IF(A1>=10,"I","J")))))))))

Rumus yang cukup panjang bukan??

Selanjutnya mari kita lihat dan bandingkan jika kita menggunakan rumus VLOOKUP

Buat list nilai kategori nilai dalam sebuah range misalnya pada range E1:F10 seperti diperlihatkan dalam screenshot di bawah ini.

Ketikan rumus VLOOKUP berikut pada sel B1:

=VLOOKUP(A1,E1:F10,2)

Maka hasilnya akan nampak seperti gambar berikut:

Silahkan di-test dengan memasukan sebarang angka pada sel A1, maka secara otomatis sel B1 akan berisi huruf kategori nilai A s.d J tergantung pada bilangan yang kita input di sel A1.

Keunggulan menggunakan fungsi VLOOKUP untuk mengelompokan nilai adalah rumus yang sederhana dan simple. Selain itu, kita juga dapat dengan mudah merubah kriteria nilai dan kategorinya pada tabel bantu, tanpa harus merubah atau mengutak-atik rumus.

Demikian semoga bermanfaat.

Artikel terkait:

Rumus IF

Wednesday, December 7, 2016

Rumus IF Untuk Mencari Diskon

Bagaimana Menggunakan Rumus Fungsi IF Untuk Mencari dan Menghitung Diskon?. Nampaknya ini bukanlah hal yang terlalu sulit, karena kasus dan logikanya sangat pas untuk penerapan rumus excel IF. Fokus perhatiannya adalah apa yang menjadi syarat ada tidaknya diskon. Dalam kesempatan ini akan dibahas bagaimana cara menggunakan rumus fungsi IF untuk menghitung diskon dengan syarat jumlah pembelian tertentu dan ada tidaknya member card atau kartu anggota.

Anggaplah sebuah grosir menerapkan pemberian diskon dengan menggunakan aturan main sebagai berikut:

Jika Pembelian barang dibawah harga 100.000

Jika memiliki membercard akan mendapat diskon 5%
Jika tidak memiliki member card, tidak mendapatkan diskon

Jika Pembelian barang Rp 100.000 atau lebih

Jika memiliki member card akan mendapatkan diskon 20%
Jika tidak memiliki member card, akan mendapatkan diskon 5%

Dengan memperhatikan aturan main tersebut, maka kita perlu menggunakan fungsi IF Bertingkat dan Bercabang untuk menghitung berapa nilai diskon yang bisa diberikan kepada pembeli.

=IF(harga<100000,IF(membercard=Ada,harga*5%,0),IF(membercard=Ada,harga*20%,harga*5%))

Anggaplah anda menggunakan excel untuk mencatat data penjualan, Kolom A berisi nama barang, kolom B berisi data harga, Kolo C berisi keterangan jika ada membercard, dan kolom D berisi nilai jumlah diskon. Misalnya baris data digunakan dimulai dari baris ke-5 , maka ketikan rumus berikut di sel D5.

=IF(B5<100000,IF(C5="Ada",B5*5%,0),IF(C5="Ada",B5*20%,B5*5%))

Demikian contoh rumus ringkas ini bagaimana cara menggunakan rumus Fungsi IF untuk mencari, menentukan dan menghitung diskon yang diberikan kepada pembeli.

Semoga bermanfaat.

Baca Juga Contoh Praktis Rumus Excel Lainnya:

Tuesday, December 6, 2016

Rumus IF Untuk Mengelompokan Waktu.

Banyak sekali contoh kasus dimana kita bisa memanfaatkan rumus fungsi IF Excel. Salah satunya adalah untuk mengelompokan waktu. Misalnya dalam satu harian, mungkin kita mengelompokan waktu bekerja, istirahat dan beribadah. Nah dalam kesempatan ini kita akan melihat contoh bagaimana menggunakan formula/rumus IF bertingkat untuk mengelompokan waktu.

Anggaplah kita menetapkan pengelompokan waktu seperti berikut:

Waktu	Aktifitas
< 09:00	Dunia-Pagi
09:00-12:00	Semangat-Kerja-Level-1
12:00-13:00	ISHOMA
13:00-15:30	Semangat-Kerja-Level 2
15:15-15:30	Sholat
15:30-17:00	Semangat-Kerja-Level 3
>17:00	Ambil-Waktumu-Kawan

Dengan berpedoman pada pengelompokan waktu tersebut, maka untuk mendapatkan aktifitas apa yang sedang dilakukan pada waktu tertentu, kita dapat menggunakan rumus IF bertingkat 6 seperti contoh berikut:


=IF(waktu<TIME(9,0,0),"Dunia-Pagi",IF(waktu<TIME(12,0,0),"Kerja-Semangat-Level-1",IF(waktu<TIME(13,0,0),"ISHOMA",IF(waktu<TIME(15,15,0),"Kerja-Semangat-Level-2",IF(waktu<TIME(15,30,0),"Sholat",IF(waktu<TIME(17,0,0),"Semangat-Kerja-Level-3","Ambil-Waktumu-Kawan"))))))

Ups, apakah rumusnya panjang. Nampaknya iya, tapi sebenarnya tidak serumit itu. Karena menggunakan text aktifitas yang agak panjang maka seakan-akan rumusnya panjang.

Silahkan diganti parameter waktu dengan referensi sel yang berisi data waktu, misalnya di C5 yang isi selnya adalah data waktu seperti 09:00, 10:00 dan sebagainya.

Perhatikan penggunaan fungsi TIME dalam contoh formula di atas, Fungsi ini berguna untuk mendapatkan nilai waktu dengan parameter jam, menit dan detik, dimana syntax fungsi TIME adalah sebagai berikut:

=TIME(hour,minute,second)

Hour = jam (0 s.d 23)

Minute = Menit (0 s.d 59)

Second = Detik (0 s.d 59)

Demikian contoh rumus sederhana dan praktis bagaimana cara menggunakan fungsi IF excel untuk mengelompokan aktifitas berdasarkan waktu.

Semoga bermanfaat :-)

Silahkan dibaca contoh-contoh rumus excel lainnya:

Monday, December 5, 2016

Rumus IF Bertingkat Untuk Penilaian Prestasi Kerja

Salah satu kegunaan praktikal dari rumus fungsi IF adalah untuk untuk mengelompokan penilaian prestasi kerja karyawan di sebuah perusahaan. Penilaian Prestasi kerja biasanya dikelompokan menjadi lebih dari dua macam kriteria, sehingga diperlukan rumus IF bertingkat untuk mendapatkan kelompok prestasi kinerja yang sesuai dengan nilai nya.

Anggaplah prestasi kerja karyawan di sebuah perusahaan dibagi menjadi 4 kelompok yaitu: “Kurang”, “Baik”, “Memuaskan” dan “Istimewa”, dengan kriteria rentang/range nilai pengelompokan sebagai berikut:

Nilai	Kriteria
< 60	Kurang
60 - 74	Baik
75 - 90	Memuaskan
> 90	Istimewa

Untuk mengelompokan prestasi kerja menjadi 4 kriteria maka diperlukan 3 fungsi IF yang disusun dalam rumus IF bertingkat.

=IF(nilai<60,"Kurang",IF(nilai<75,"Baik",IF(nilai<90,"Memuaskan","Memuaskan")))

Cara kerja rumus IF bertingkat tersebut dapat dijelaskan sebagai berikut:

Fungsi IF akan mengecek test logika pertama yaitu nilai < 60, jika terbukti nilai < 60 maka fungsi IF langsung menghasilkan kriteria ”Kurang” dan selesai
Jika test logika pertama gagal atau nilai tidak terbukti kurang dari 60 maka fungsi IF akan mengecek test logika berikutnya yaitu nilai < 75. Jika terbukti nilai < 75 maka fungsi IF akan memberikan hasil ”Baik” dan selesai
Jika test logika kedua gagal, maka fungsi IF akan mengecek test logika berikutnya yaitu nilai < 90. Jika terbukti nilai < 90, maka fungsi IF akan memberikan kriteria ”Memuaskan”
Jika test logika ketiga gagal, maka fungsi IF akan memberikan nilai ”ISTIMEWA”

Dengan membalik logika "kurang dari" menjadi logika "lebih dari" maka rumus IF di atas dapat diubah menjadi :

=IF(nilai>90,"Istimewa",IF(nilai>=75,"Memuaskan",IF(nilai>=60,"Baik","Kurang")))

Praktek

Anggaplah kita memiliki data list karyawan dimulai dari baris ke-5 di kolom A, data angka penilaian di kolom B, dan Keterangan kelompok kinerja di kolom C.

Di sel C5, ketikan rumus berikut: =IF(B5<60,"Kurang",IF(B5<75,"Baik",IF(B5<90,"Memuaskan","Istimewa")))
Copy rumus tersebut ke baris dibawahnya.
Selesai, kita akan mendapatkan pengelompokan kinerja karyawan sesuai kriteria yang sudah ditetapkan.

Rumus IF Bertingkat Untuk Menentukan Range Nilai

Dengan membalik logika "kurang dari" menjadi logika "lebih dari", maka rumus di sel C5 tersebut juga dapat dituliskan sebagai berikut: =IF(B5>90,"Istimewa",IF(B5>=75,"Baik",IF(B5>=60,"Baik","Kurang")))

Demikian contoh cara menggunakan Fungsi IF bertingkat untuk mengelompokan prestasi kinerja karyawan. Contoh ini juga dapat diterapkan pada pengelompokan lainnya, misalnya : pengelompokan prestasi siswa, pengelompokan kualitas produk, pengelompokan kualitas layanan, dan sebagainya.

Dengan cara yang sama, kita juga bisa menggunakan fungsi IF bertingkat untuk menentukan penilaian ujian siswa. Caranya mudah yaitu dengan mengkonversi nilai ujian menjadi huruf A, B, C, D dan sebagainya.

Silahkan dicoba.

Terimakasih.

Artikel Terkait:

Rumus IF Untuk Keterangan Lulus Tidak Lulus

Fungsi IF Tunggal sangat cocok digunakan untuk mengelompokan kelulusan siswa sesuai nilai ujian. Rumus IF Excel untuk kasus ini sangat mudah dibuat, tidak ribet, dan hanya diperlukan satu IF Tunggal saja. Hal ini dikarenakan hanya ada dua pilihan hasil saja, yaitu "LULUS" atau "TIDAK LULUS". Rumus yang sangat sederhana dan Mudah.

Mari mengingat kambali syntax fungsi IF

IF(logical_test,value_if_true,value_if_false)

Atau

IF(test_logika, nilai_jika_benar, nilai_jika_salah)

Anggaplah sudah ditentukan nilai kelulusan adalah minimal 70, maka rumus IF tunggal untuk menentukan nilai kelulusan dapat dituliskan sebagai berikut:

=IF(nilai>=70,"LULUS","TIDAK LULUS")

Rumus IF tersebut dapat dibaca sebagai berikut:

Jika nilai lebih dari atau sama dengan 70 maka rumus IF akan menghasilkan "LULUS"

Sebaliknya jika test logika nilai >=70 tidak terpenuhi maka rumus IF akan menghasilkan "TIDAK LULUS"

Sangat Sederhana dan Mudah!!!

Praktek:

Anggaplah sekumpulan data siswa sudah tersedia pada lembar kerja excel. Kolom A berisi data nama siswa, kolom B berisi nilai ujian, sedangkan kolom C berisi keterangan LULUS/TIDAK LULUS.
Bagaimana cara membuat rumus IF untuk keterangan Lulus/Tidak Lulus pada kolom C ?

Ikuti Langkah-Langkah berikut: