SUMIF Dengan VLOOKUP

Penggunaan Gabungan sumif (vlookup)

Sumif dengan VLOOKUP merupakan kombinasi dari dua fungsi bersyarat yang berbeda, SUMIF digunakan untuk menjumlahkan sel berdasarkan beberapa kondisi yang membutuhkan argumen rentang yang memiliki data dan kemudian kriteria atau kondisi dan sel untuk dijumlahkan, bukan kriteria yang kita gunakan VLOOKUP sebagai kriteria ketika ada banyak data yang tersedia di beberapa kolom.

SUMIF adalah fungsi yang disajikan di Excel dari versi 2007 untuk menjumlahkan berbagai nilai yang cocok dengan kriteria. VLOOKUP adalah salah satu rumus terbaik untuk mengumpulkan data dari tabel lain. Ketika beberapa kondisi dan kolom ada, sumif (vlookup) digunakan untuk melakukan beberapa perhitungan pada lembar excel. Kerugian fungsi SUMIF yang mengembalikan satu-satunya angka diatasi dengan menggunakan VLOOKUP. VLOOKUP membantu mengembalikan semua jenis data dari tabel berdasarkan kriteria yang cocok.

Penjelasan

Fungsi SUMIF: Ini adalah fungsi Trigonometri dan Matematika untuk menjumlahkan nilai ketika kondisi yang ditetapkan benar. Nilai jumlah diperoleh hanya berdasarkan satu kriteria.

Saat kita berurusan dengan fungsi SUMIF di excel, rumus berikut digunakan

  • Rentang: Ini adalah rentang sel yang digunakan untuk menilai kriteria yang ditetapkan
  • Kriteria: Ini adalah kondisi untuk menjumlahkan nilai. Ini mungkin referensi sel, angka, dan fungsi excel lainnya. Saat kami ingin menggabungkan SUMIF dan VLOOKUP, fungsi vlookup akan dimasukkan sebagai pengganti kriteria
  • Rentang Jumlah: Ini adalah rentang sel yang ditentukan untuk menjumlahkan nilai numerik.

Sekarang, rumusnya diubah menjadi

Rumus = SUMIF (Rentang, Vlookup (nilai pencarian, larik tabel, nomor indeks kolom, [pencarian rentang]), [jumlah kisaran])

  • Nilai pencarian: Ini menentukan nilai yang akan dicari dalam tabel. Ini bisa menjadi referensi atau nilai.
  • Larik tabel: Ini adalah rentang tabel yang berisi dua atau lebih dari dua kolom.
  • Nomor indeks kolom: Ini adalah indeks relatif sebuah kolom untuk menentukan pengembalian data yang diperlukan dari kolom tertentu.
  • [Pencarian jarak]: Ini adalah 0 atau 1 untuk menentukan apakah nilai pasti harus dikembalikan atau nilai perkiraan harus dikembalikan. Tapi, ini opsional bagi pengguna. 0 menunjukkan kecocokan tepat dan 1 menunjukkan kecocokan perkiraan.

Bagaimana Cara Menggunakan SUMIF dengan Fungsi VLOOKUP?

Penggunaan gabungan sumif (vlookup) sangat membantu dalam mencari data berdasarkan kriteria tunggal. Ini digunakan dalam banyak cara di Excel untuk melakukan penghitungan dengan mencari data. Ini secara kolektif digunakan dalam lingkungan bisnis untuk melakukan berbagai tugas untuk membuat keputusan yang baik. Untuk menggunakan fungsi ini secara efektif bersama-sama,

Pertama, fungsi SUMIF harus dimasukkan menggunakan dua metode.

Metode Pertama: Formula perlu mengetik dari keyboard seperti yang ditunjukkan pada gambar di bawah ini.

Metode Kedua: Fungsi SUMIF dapat disisipkan dari Tab 'Formula' seperti yang ditunjukkan pada gambar.

Setelah memasukkan fungsi SUMIF, rumus untuk VLOOKUP dimasukkan ke dalam fungsi SUMIF dengan mengganti elemen 'Kriteria'. Semua parameter VLOOKUP termasuk nilai pencarian, larik tabel, nomor indeks kolom, dan pencarian rentang. Ini harus diapit dalam tanda kurung untuk menghindari kesalahan dengan rumus. Kisaran nilai yang akan dijumlahkan termasuk dalam elemen rentang jumlah fungsi SUMIF. Akhirnya, tombol CTRL, SHIFT, dan ENTER ditekan bersama untuk memfasilitasi nilai sebagai array.

Contoh

Anda dapat mengunduh Template Excel SUMIF Dengan VLOOKUP ini di sini - SUMIF Dengan Template Excel VLOOKUP

Contoh # 1 - Penggunaan sumif (vlookup) bersama-sama untuk menentukan beberapa nilai

Contoh ini menunjukkan cara menggunakan sumif (vlookup) bersama-sama untuk menemukan jumlah penjualan di bulan yang sama di tahun yang berbeda. Data berikut dipertimbangkan untuk contoh ini seperti yang ditunjukkan pada tangkapan layar.

Tabel pemeta yang dipertimbangkan ditampilkan seperti yang disebutkan di bawah ini. Ini termasuk nilai referensi untuk bulan-bulan dari Januari hingga Desember.

Setelah memasukkan data ke dalam tabel utama dan tabel pencarian, fungsi SUMIF digunakan untuk menentukan total penjualan yang dihasilkan dalam bulan yang berbeda dalam satu tahun. Di sini, nilai Pencarian yang dipertimbangkan adalah bulan. Rumus yang menggabungkan sumif (vlookup) ditampilkan sebagai,

Total penjualan di bulan Januari ditentukan sebagai 17263,3 . Saat kami mengubah nilai tampilan ke bulan lain, total penjualan masing-masing dihasilkan.

Contoh # 2 - Menentukan Jumlah Berdasarkan Kriteria Pencocokan di Lembar Kerja Berbeda

Dalam contoh ini, tabel pemeta dan tabel utama diambil dalam lembar yang berbeda, bukan dalam satu lembar. Data tabel pencarian ditunjukkan pada gambar di bawah ini.

Data tabel utama ditunjukkan pada gambar di bawah ini.

Untuk menentukan total penjualan, Nama penjual diambil sebagai nilai pencarian dan ID karyawan digunakan untuk tujuan referensi. Rumusnya dimasukkan seperti yang disajikan di bawah ini dan beberapa perubahan diamati pada rumus tersebut jika dibandingkan dengan contoh pertama.

Dalam contoh ini, alih-alih memilih array pencarian, hanya Lookup_table yang disebutkan. Dengan menekan tiga tombol termasuk CTRL, SHIFT, dan ENTER hasil yang akurat dihasilkan.

Saat nama penjual diubah menjadi nama yang disebutkan dalam pencarian, jumlah penjualan bervariasi dan menghasilkan hasil baru.

Manfaat

Berikut ini adalah keuntungan menggunakan fungsi-fungsi ini.

  • Kita dapat dengan mudah mengekstrak nilai dari tabel lain untuk melakukan penghitungan.
  • Menentukan jumlah nilai yang disajikan dalam rentang yang memenuhi kriteria yang disebutkan dalam berbagai aspek bisnis.

Hal-hal untuk diingat

  • Saat menggunakan fungsi VLOOKUP, nomor indeks kolom tidak boleh lebih rendah dari 1 untuk menghindari kesalahan.
  • Indeks harus diberikan ke kolom tabel pencarian dengan menunjukkan angka 1, 2, 3, dan seterusnya.
  • CTRL + SHIFT + ENTER di excel harus digunakan sebagai ganti memasukkan Kunci karena VLOOKUP dimasukkan sebagai rumus array.
  • Mendefinisikan dua tabel termasuk Main dan Lookup diperlukan untuk mengekstrak nilai dan menentukan jumlah nilai array.
  • SUMIF memberikan hasil yang akurat hanya untuk data numerik, tidak akan berfungsi untuk jenis data lain.