SUMIF Dengan VLOOKUP | Gabungkan SUMIF dengan Fungsi VLOOKUP Excel

Penggunaan Gabungan sumif (vlookup)

Sumif dengan VLOOKUP adalah gabungan dari dua fungsi bersyarat yang berbeza, SUMIF digunakan untuk menjumlahkan sel berdasarkan beberapa keadaan yang mengambil argumen jarak yang mempunyai data dan kemudian kriteria atau syarat dan sel untuk dijumlahkan, bukan kriteria yang kita gunakan VLOOKUP sebagai kriteria apabila terdapat sejumlah besar data yang tersedia dalam beberapa lajur.

SUMIF adalah fungsi yang disajikan dalam Excel dari versi 2007 untuk menjumlahkan pelbagai nilai sesuai dengan kriteria. VLOOKUP adalah salah satu formula terbaik untuk mengumpulkan data dari jadual lain. Apabila terdapat banyak keadaan dan lajur, sumif (vlookup) digunakan untuk melakukan beberapa pengiraan pada lembaran excel. Kelemahan fungsi SUMIF mengembalikan satu-satunya nombor diatasi dengan menggunakan VLOOKUP. VLOOKUP membantu mengembalikan segala jenis data dari jadual berdasarkan kriteria yang sesuai.

Penjelasan

Fungsi SUMIF: Ini adalah fungsi Trigonometri dan Matematik untuk menjumlahkan nilai apabila keadaan yang ditetapkan benar. Nilai jumlah diperoleh berdasarkan hanya satu kriteria.

Apabila kita menangani fungsi SUMIF dalam excel, formula berikut digunakan

Julat: Ini adalah jarak sel yang digunakan untuk menilai kriteria yang ditetapkan
Kriteria: Ini adalah syarat untuk menjumlahkan nilai. Ini mungkin rujukan sel, nombor, dan fungsi excel lain. Apabila kita ingin menggabungkan SUMIF dan VLOOKUP, fungsi vlookup akan dimasukkan sebagai pengganti kriteria
Jumlah Julat: Ini adalah julat sel yang ditentukan untuk menjumlahkan nilai berangka.

Sekarang, formula diubah menjadi

Rumus = SUMIF (Julat, Vlookup (nilai carian, susunan jadual, nombor indeks lajur, [rentang carian]), [julat jumlah])

Nilai carian: Ini menentukan nilai yang akan dicari dalam jadual. Ia mungkin menjadi rujukan atau nilai.
Susunan jadual: Ini adalah julat jadual yang mengandungi dua atau lebih daripada dua lajur.
Nombor indeks lajur: Ini adalah indeks relatif lajur untuk menentukan untuk mengembalikan data yang diperlukan dari lajur tertentu.
[Range lookup]: Adalah 0 atau 1 untuk menentukan sama ada nilai tepat harus dikembalikan atau nilai anggaran harus dikembalikan. Tetapi, adalah pilihan bagi pengguna. 0 menunjukkan padanan tepat dan 1 menunjukkan padanan anggaran.

Bagaimana Menggunakan SUMIF dengan Fungsi VLOOKUP?

Gabungan penggunaan sumif (vlookup) sangat membantu dalam mencari data berdasarkan satu kriteria. Ini digunakan dalam banyak cara di Excel untuk melakukan pengiraan dengan mencari data. Ini digunakan secara kolektif dalam persekitaran perniagaan untuk melakukan pelbagai tugas untuk membuat keputusan yang baik. Untuk menggunakan fungsi ini dengan berkesan,

Pertama, fungsi SUMIF dimasukkan dengan menggunakan dua kaedah.

Kaedah Pertama: Formula perlu menaip dari papan kekunci seperti yang ditunjukkan dalam gambar di bawah.

Kaedah Kedua: Fungsi SUMIF dapat dimasukkan dari Tab 'Formula' seperti yang ditunjukkan dalam gambar.

Setelah memasuki fungsi SUMIF, formula untuk VLOOKUP dimasukkan ke dalam fungsi SUMIF dengan menggantikan elemen 'Kriteria'. Semua parameter VLOOKUP termasuk nilai carian, susunan jadual, nombor indeks lajur, dan carian julat. Ini harus dilampirkan dalam kurungan untuk mengelakkan kesalahan formula. Julat nilai yang akan dijumlahkan termasuk dalam elemen penjumlahan fungsi SUMIF. Akhirnya, kekunci CTRL, SHIFT, dan ENTER ditekan bersama untuk memudahkan nilai sebagai tatasusunan.

Contoh

Anda boleh memuat turun Templat Excel SUMIF Dengan VLOOKUP ini di sini - Templat SUMIF Dengan VLOOKUP Excel

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

Contoh ini menunjukkan cara menggunakan sumif (vlookup) bersama-sama untuk mencari jumlah penjualan pada bulan yang sama dalam tahun yang berbeza. Data berikut dipertimbangkan untuk contoh ini seperti yang ditunjukkan dalam tangkapan skrin.

Jadual pencarian yang dipertimbangkan ditunjukkan seperti yang disebutkan di bawah. Ini merangkumi nilai rujukan untuk bulan dari Januari hingga Disember.

Setelah memasukkan data ke dalam jadual utama dan jadual pencarian, fungsi SUMIF digunakan untuk menentukan jumlah penjualan yang dihasilkan dalam bulan yang berlainan dalam setahun. Di sini, nilai Carian yang dipertimbangkan adalah bulan. Rumus yang menggabungkan sumif (vlookup) ditunjukkan sebagai,

Jumlah penjualan pada bulan Januari ditentukan sebagai 17263.3 . Apabila kami menukar nilai penampilan menjadi bulan lain, jumlah penjualan masing-masing dihasilkan.

Contoh # 2 - Menentukan Jumlah Berdasarkan Kriteria Pencocokan dalam Lembaran Kerja yang Berbeza

Dalam contoh ini, jadual carian dan jadual utama diambil dalam helaian yang berbeza dan bukannya satu helaian. Data jadual carian ditunjukkan dalam tangkapan skrin di bawah.

Data jadual utama ditunjukkan dalam tangkapan skrin di bawah.

Untuk menentukan jumlah penjualan, Nama jurujual diambil sebagai nilai carian dan ID pekerja digunakan untuk tujuan rujukan. Rumus dimasukkan seperti yang ditunjukkan di bawah ini dan sedikit perubahan diperhatikan pada formula jika dibandingkan dengan contoh pertama.

Dalam contoh ini, bukannya memilih tatasusunan, hanya Lookup_table yang disebutkan. Dengan menekan tiga kekunci termasuk CTRL, SHIFT, dan ENTER hasil yang tepat dihasilkan.

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

Kebaikan

Berikut adalah faedah menggunakan fungsi ini.

Kita dapat mengekstrak nilai dari jadual lain dengan mudah untuk melakukan pengiraan.
Menentukan jumlah nilai yang disajikan dalam julat yang memenuhi kriteria yang disebutkan dalam pelbagai aspek perniagaan.

Perkara yang Perlu Diingat

Semasa menggunakan fungsi VLOOKUP, nombor indeks lajur tidak boleh lebih rendah dari 1 untuk mengelakkan kesalahan.
Indeks harus diberikan ke lajur jadual pencarian dengan menunjukkan dengan nombor 1, 2, 3, dan seterusnya.
CTRL + SHIFT + ENTER dalam excel harus digunakan dan bukannya memasukkan Key kerana VLOOKUP dimasukkan sebagai formula array.
Menentukan dua jadual termasuk Main dan Lookup diperlukan untuk mengekstrak nilai dan menentukan jumlah nilai array.
SUMIF memberikan hasil yang tepat untuk hanya data berangka, ia tidak akan berfungsi untuk jenis data lain.