PENGGUNAAN RUMUS VLOOKUP, HLOOKUP, IF, SUMIF, SUMIFS PADA DATA YANG KOMPLEKS

Hello, kali ini saya akan berbagi pelajaran mengenai Penggunaan Rumus HLOOKUP, IF, SUMIF, SUMIFS pada Data yang Kompelks. Mengapa dikatakan data yang kompleks?..itu karena kita akan menggunakan rumus ini dengan melakukan kombinasi pada sebuah data yang rumit. Saya akan menjelaskan satu persatu supaya saudara cepat mengerti.

•         Vlookup digunakan untuk mengisi data berdasarkan data pada tabel referensi yang disusun berdasarkan kolom / memanjang kebawah secara vertikal. Penulisan rumusnya adalah : =VLOOKUP(nilai kunci;tabel referensi; nomor index kolom; tipe)

•         Hlookup digunakan untuk mengisi data berdasarkan data pada tabel lain atau tabel referensi yang disusun berdasarkan baris / memanjang ke samping secara horizontal. Penulisan rumusnya adalah : =HLOOKUP(nilai kunci; tabel referensi; nomor index kolom; tipe)

      =>If adalah rumus logika suatu keadaan yang benar atau salah.

      => SUMIF dan SUMIFS  sama – sama digunakan untuk menjumlahkan cell dengan kriteria                     tertentu, SUMIF hanya menangani satu kriteria penjumlahan sedangkan SUMIFS dapat                         menangani lebih dari satu kriteria.

Untuk memulainya, seperti biasanya buatlah data seperti berikut ini:

Langkah selanjutnya yaitu, seperti biasanya kita beri nama pada tabel referensi untuk memudahkan kita dalam pemakaian rumus. Pertama, blok tabel kode pertama dari kolom BI hingga kolom Executive dan beri nama misalnya kode1 pada Kotak nama ( name box ) pada unjung kiri atas. 

Kedua, blok tabel kode 2 dari kolom anak-anak sampai kolom  75.000 dibagian EX dan kita beri nama  M dibagian name boxnya  supaya lebih singkat dalam penggunaaan rumus nantinya karena kita akan menggunakan banyak rumus untuk tabel kode kedua. Langkah pengerjaannya seperti dibawah ini:

Sekarang kita akan menentukan nama kelasnya pada kolom kelas berdasarkan kode yang ada menggunakan rumus HLOOKUP karena kita akan membaca kolom referensi secara horizontal atau menyamping. Caranya adalah =HLOOKUP(klik pada kolom kode yang ingin dibaca;nama tabel referensi;urutan tabel yang ingin dibaca). jika mengikuti tabel seperti digambar, maka rumus untuk mencari kelas pada pesanan Titik adalah =HLOOKUP(C13;kode1;2). Jika sudah dapat, maka untuk mencari data berikutnya cukup ditarik kebawah dan hasilnya akan seperti berikut ini :

Untuk mencari harga setiap kelasnya maka kita akan memakai rumus kombinasi antara IF dan VLOOKUP menggunakan tabel referensi kode dua ( M ). Dalam hal ini saya tidak akan menjelaskan panjang lebar karena rumusnya hanya bisa dimengertdengan memahaminya. Rumus untuk mencari harga pada pesanan Titik seperti pada gambar adalah:

=IF(D13="Bisnis";VLOOKUP(E13;M;2);IF(D13="Ekonomi";VLOOKUP(E13;M;3);VLOOKUP(E13;M;4))). Rumus tersebut menjelaskan bahwa jika kolom D13 adlah Bisnis maka kita menggunakan rumus VLOOKUP yang seperti tertulis, dan begitu seterusnya. Untuk mencari yang lainnya maka cukup ditarik kebawah, dan hasilnya seperti pada gambar:

Untuk mencari total harga maka cukup mengalikan jumlah tiket dengan dengan harganya. Seperti gambar berikut:

Sekarang kita akan mencari total penjualan berdasarkan kelasmenggunakan SUMIF. Caranya, adalh =SUMIF(blok semua kolom kelas dari 1 sampai 10 lalu kunci dengan F4;”tulis nama kelas total penjualan yang Ingin dicari”;blok semua kolom total harga dari 1 sampai 10). Jika sudah berhasil, maka carilah yang lainnya dengan menyeret kebawah dan yang diubah cukup nama kelas yang mau dicari total penjualannya dan hasilnya akan seperti dibawah ini.

Catatan : penguncian kolom atau baris dengan F4 dilakukan untuk mempermudah menyeret secara vertical maupun secara horizontal.

Selanjutnya kita akan mencari total penjualan berdasarkan status. Cara pengerjaannya sama dengan total penjualan berdasarkan kelas yang tadi, hanya saja menggunakan kolom yang berbeda. Caranya adalah : =SUMIF(blok semua kolom status  dari 1 sampai 10 lalu kunci dengan F4;”tulis nama Status  total penjualan yang Ingin dicari”;blok semua kolom total harga dari 1 sampai 10). Lakukan hal yang sama, maka hasilnya akan seperti dibawah ini:

Nah, kita telah mencari total penjualan dengan satu criteria. Sekarang kita akan mencari total penjualan dengan dua kriteria sekaligus yaitu total penjualan berdasarkan kelas dan status menggunakan SUMIFS. Caranya adalh : kita akan mencari bagian kolom pertama, =SUMIFS(blok semua kolom total harga dari 1 sampai 10 kunci dengan F4 ;blok semua kolom kelas dari 1 sampai 10 kunci dengan F4;”Bisnis”;blok semua kolom status dari 1 sampai 10 dan kunci dengan F4;”Anak-anak”). untuk mencari yang lainnya cukup seret kebawah dan ganti kelas serta statusnya secara berurutan sesuai yang ingin dicari, hasilnya akan seperti dibawah ini:

Demikian cara Penggunaan Rumus VLOOKUP, HLOOKUP, IF, SUMIF, SUMIFS pada Data yang Kompelks. Jangan berhenti mencoba jika terjadi situsi yang error atau rumusnya tidak terbaca, periksa lagi penempatan tanda kurung, tanda koma, titik dua, tanda kutip, dan lainnya yang  mungkin terlupkan. 

Jika ada yang ragu, silahkan berikan di kolom komentar ya.

Terimakasih telah berkunjung dan semoga bermanfaat

Firmanzaro.blogspot.com