Fungsi SUMIF di Excel dan jumlahkan dengan beberapa kondisi

Excel adalah program yang sangat fungsional. Bahkan set fitur bawaan sudah cukup untuk menyelesaikan hampir semua tugas. Dan selain yang standar, akrab bagi banyak orang, ada juga yang jarang didengar orang. Tetapi pada saat yang sama, mereka tidak berhenti berguna. Mereka memiliki spesialisasi yang lebih sempit, dan tidak selalu ada kebutuhan untuk mereka. Tetapi jika Anda tahu tentang mereka, maka pada saat kritis mereka bisa sangat berguna.

Hari ini kita akan berbicara tentang salah satu fungsi tersebut RINGKASAN.

Jika pengguna dihadapkan pada tugas untuk menjumlahkan beberapa nilai, dengan fokus pada kriteria tertentu, maka perlu menggunakan fungsi RINGKASAN. Rumus yang menggunakan fungsi ini mengambil kondisi ini sebagai argumen, lalu menjumlahkan nilai yang memenuhinya, dan kemudian nilai yang ditemukan dimasukkan ke dalam sel di mana ia ditulis. 

Deskripsi Detail Fungsi SUMIFS

Sebelum mempertimbangkan fungsi RINGKASAN, Anda harus terlebih dahulu memahami versi yang lebih sederhana – SUMMELI, karena di situlah fungsi yang kita pertimbangkan didasarkan. Masing-masing dari kita mungkin sudah akrab dengan dua fungsi yang sering digunakan – JUMLAH (melakukan penjumlahan nilai) dan JIKA (menguji nilai terhadap kondisi tertentu).

Jika Anda menggabungkannya, Anda mendapatkan fungsi lain – SUMMELI, yang memeriksa data terhadap kriteria yang ditentukan pengguna dan hanya menjumlahkan angka-angka yang memenuhi kriteria tersebut. Jika kita berbicara tentang Excel versi bahasa Inggris, maka fungsi ini disebut SUMIF. Dengan kata sederhana, nama -bahasa adalah terjemahan langsung dari bahasa Inggris. Fungsi ini dapat digunakan untuk berbagai tujuan. Secara khusus, ini dapat digunakan sebagai alternatif VPR, yaitu, tuliskan

Perbedaan utama antara fungsi RINGKASAN  dari fungsi biasa SUMMELI adalah bahwa beberapa kriteria digunakan. Sintaksnya cukup rumit pada pandangan pertama, tetapi setelah diperiksa lebih dekat, ternyata logika fungsi ini sangat sederhana. Pertama, Anda perlu memilih rentang di mana data akan diperiksa, dan kemudian mengatur kondisi untuk kepatuhan dengan analisis yang akan dilakukan. Dan operasi semacam itu dapat dilakukan untuk sejumlah besar kondisi.

Sintaksnya sendiri adalah:

SUMIFS(jumlah_rentang, kondisi_rentang1, kondisi1, [rentang_kondisi2, kondisi2], …)

Di tempat yang sesuai, perlu untuk menempatkan susunan sel yang sesuai dalam kasus tertentu. 

Mari kita lihat argumen secara lebih rinci:

1. Jumlah_rentang. Argumen ini, serta rentang kondisi 1 dan kondisi 1, diperlukan. Ini adalah satu set sel yang perlu dijumlahkan.
2. Kondisi_rentang1. Ini adalah rentang di mana kondisinya akan diperiksa. Itu dipasangkan dengan argumen berikutnya – Kondisi1. Penjumlahan nilai yang sesuai dengan kriteria dilakukan dalam sel yang ditentukan dalam argumen sebelumnya.
3. Kondisi1. Argumen ini menentukan kriteria yang akan diperiksa. Ini dapat diatur, misalnya, dengan cara ini: "> 32".
4. Rentang kondisi 2, Kondisi 2… ​​Di sini, kondisi berikut diatur dengan cara yang sama. Jika lebih dari beberapa kondisi perlu ditentukan, maka argumen Condition Range 3 dan Condition 3 ditambahkan. Sintaksnya sama untuk argumen berikut.

Fungsi ini memungkinkan pemrosesan maksimum hingga 127 pasang kondisi dan rentang. 

Anda dapat menggunakannya di beberapa area sekaligus (kami akan memberikan beberapa saja, daftarnya sebenarnya lebih panjang):

1. Akuntansi. Misalnya, ada baiknya menggunakan fungsi RINGKASAN untuk membuat laporan ringkasan, per kuartal untuk pengeluaran di atas jumlah tertentu, misalnya. Atau buat laporan tentang satu produk dari kategori harga tertentu.
2. Manajemen penjualan. Di sini juga fungsinya bisa sangat berguna. Misalnya, kita dihadapkan pada tugas menjumlahkan hanya harga pokok barang yang dijual kepada pelanggan tertentu pada waktu tertentu. Dan dalam situasi seperti itu, fungsi RINGKASAN bisa sangat membantu.
3. Pendidikan. Kami akan memberikan contoh yang lebih praktis dari area ini hari ini. Secara khusus, Anda dapat menggunakannya untuk mendapatkan ringkasan nilai siswa. Anda dapat memilih untuk satu mata pelajaran atau untuk nilai individu. Seseorang dapat segera menetapkan beberapa kriteria penilaian yang akan dipilih, yang sangat nyaman dan dapat menghemat banyak waktu.

Seperti yang Anda lihat, jangkauan aplikasi untuk fungsi ini sangat luas. Tapi ini bukan satu-satunya kelebihannya. Mari kita lihat beberapa manfaat lain yang dimiliki fitur ini:

1. Kemampuan untuk menetapkan beberapa kriteria. Mengapa ini merupakan keuntungan? Anda dapat menggunakan fungsi normal SUMMELI! Dan semua karena itu nyaman. Tidak perlu melakukan perhitungan terpisah untuk setiap kriteria. Semua tindakan dapat diprogram terlebih dahulu, bahkan sebelumnya. bagaimana tabel data akan dibentuk. Ini adalah penghemat waktu yang hebat.
2. Otomatisasi. Era modern adalah era otomatisasi. Hanya orang yang tahu cara mengotomatisasi pekerjaannya dengan benar yang dapat menghasilkan banyak uang. Itu sebabnya kemampuan menguasai Excel dan fungsinya RINGKASAN khususnya, sangat penting bagi setiap orang yang ingin membangun karir. Mengetahui satu fungsi memungkinkan Anda melakukan beberapa tindakan sekaligus, sebagai satu. Dan di sini kita beralih ke manfaat berikutnya dari fitur ini.
3. Menghemat waktu. Hanya karena fakta bahwa satu fungsi melakukan beberapa tugas sekaligus.
4. Kesederhanaan. Terlepas dari kenyataan bahwa sintaksnya cukup berat pada pandangan pertama karena ukurannya yang besar, pada kenyataannya, logika fungsi ini sangat sederhana. Pertama, rentang data dipilih, kemudian rentang nilai, yang akan diperiksa kesesuaiannya dengan kondisi tertentu. Dan tentu saja, kondisi itu sendiri juga harus ditentukan. Dan beberapa kali. Faktanya, fungsi ini hanya didasarkan pada satu konstruksi logis, yang membuatnya lebih sederhana daripada yang terkenal VPR terlepas dari kenyataan bahwa itu dapat digunakan untuk tujuan yang sama, juga dengan mempertimbangkan sejumlah besar kriteria. 

Fitur menggunakan fungsi SUMIFS

Ada beberapa fitur penggunaan fungsi ini yang perlu Anda perhatikan. Pertama-tama, fungsi ini mengabaikan rentang dengan string teks atau null, karena tipe data ini tidak dapat ditambahkan bersama dalam pola aritmatika, hanya digabungkan seperti string. Fungsi ini tidak dapat melakukan ini. Anda juga perlu memperhatikan kondisi berikut:

1. Anda dapat menggunakan jenis nilai ini sebagai ketentuan untuk memilih sel untuk menambahkan lebih lanjut nilai yang terkandung di dalamnya: nilai numerik, ekspresi boolean, referensi sel, dan sebagainya. 
2. Jika teks, ekspresi logis atau tanda matematika sedang diperiksa, maka kriteria tersebut ditentukan melalui tanda kutip.
3. Tidak dapat menggunakan istilah yang lebih panjang dari 255 karakter.
4. Dimungkinkan untuk menggunakan kriteria perkiraan untuk memilih nilai menggunakan wildcard. Tanda tanya digunakan untuk mengganti satu karakter, dan tanda perkalian (asterisk) diperlukan untuk mengganti beberapa karakter. 
5. Nilai boolean yang berada dalam rentang penjumlahan otomatis diubah menjadi nilai numerik sesuai dengan jenisnya. Dengan demikian, nilai "TRUE" berubah menjadi satu, dan "FALSE" - menjadi nol. 
6. Jika #VALUE! muncul error pada sebuah sel, artinya jumlah sel pada kondisi dan range penjumlahan berbeda. Anda perlu memastikan bahwa ukuran argumen ini sama. 

Contoh penggunaan fungsi SUMIFS

fungsi RINGKASAN tidak serumit kelihatannya pada pandangan pertama, ternyata. Namun untuk lebih jelasnya, mari kita lihat beberapa contoh praktis bagaimana Anda dapat menggunakan fungsi tersebut RINGKASAN. Ini akan membuatnya lebih mudah untuk mempelajari topik.

Rentang dinamis penjumlahan kondisi

Jadi mari kita mulai dengan contoh pertama. Katakanlah kita memiliki tabel yang berisi informasi tentang bagaimana siswa mengatasi kurikulum dalam mata pelajaran tertentu. Ada satu set nilai, kinerja dinilai pada skala 10 poin. Tugasnya adalah menemukan nilai ujian siswa yang nama belakangnya dimulai dengan huruf A, dan nilai minimumnya adalah 5.

Tabelnya terlihat seperti ini.

Untuk menghitung skor total berdasarkan kriteria yang dijelaskan di atas, kita perlu menerapkan rumus berikut.

Mari kita jelaskan argumen secara lebih rinci:

1. C3:C14 adalah rentang penjumlahan kami. Dalam kasus kami, itu bertepatan dengan kisaran kondisi. Dari situ akan dipilih poin-poin yang digunakan untuk menghitung jumlahnya, tetapi hanya poin-poin yang termasuk dalam kriteria kami.
2. ">5" adalah kondisi pertama kami.
3. B3:B14 adalah rentang penjumlahan kedua yang diproses agar sesuai dengan kriteria kedua. Kami melihat bahwa tidak ada kebetulan dengan rentang penjumlahan. Dari sini kita menyimpulkan bahwa jangkauan penjumlahan dan jangkauan kondisi mungkin identik atau tidak. 
4. “A*” adalah rentang kedua, yang menentukan pemilihan nilai hanya untuk siswa yang nama belakangnya dimulai dengan A. Dalam kasus kami, tanda bintang berarti sejumlah karakter. 

Setelah perhitungan, kita mendapatkan tabel berikut.

Seperti yang Anda lihat, rumus merangkum nilai berdasarkan rentang dinamis dan berdasarkan kondisi yang ditentukan oleh pengguna.

Penjumlahan selektif berdasarkan kondisi di Excel

Sekarang misalkan kita ingin mendapatkan informasi tentang barang apa yang dikirim ke negara mana selama kuartal terakhir. Setelah itu, cari total pendapatan dari pengiriman untuk bulan Juli dan Agustus.

Tabelnya sendiri terlihat seperti ini. 

Untuk menentukan hasil akhir, kita membutuhkan rumus seperti itu.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

Sebagai hasil dari perhitungan yang dilakukan oleh rumus ini, kami mendapatkan hasil berikut.

Perhatian! Rumus ini terlihat cukup besar meskipun kami hanya menggunakan dua kriteria. Jika rentang datanya sama, Anda dapat mengurangi panjang rumus secara signifikan, seperti yang ditunjukkan di bawah ini.

SUMIFS berfungsi untuk menjumlahkan nilai di berbagai kondisi

Sekarang mari kita berikan contoh lain untuk diilustrasikan. Dalam hal ini, tabel tetap sama seperti pada kasus sebelumnya. 

Kami menggunakan rumus berikut (tetapi kami menulisnya sebagai rumus array, yaitu, kami memasukkannya melalui kombinasi tombol CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Setelah fungsi RINGKASAN akan menjumlahkan larik nilai berdasarkan kriteria yang ditentukan dalam rumus (yaitu, negara Cina dan Georgia), larik yang dihasilkan dijumlahkan dengan fungsi biasa JUMLAH, yang ditulis sebagai rumus array.

Jika kondisi dilewatkan sebagai konstanta array untuk lebih dari satu pasangan, maka rumus akan memberikan hasil yang salah.

Sekarang mari kita lihat tabel yang berisi total.

Seperti yang Anda lihat, kami telah berhasil. Anda pasti akan berhasil juga. Sukses besar di bidang ini. Ini adalah fungsi yang sangat sederhana yang dapat dipahami oleh seseorang yang baru saja menginjakkan kaki di jalur pembelajaran Excel. Dan kita sudah tahu bahwa fungsinya RINGKASAN memungkinkan Anda untuk menjadi efektif dalam bidang aktivitas apa pun, mulai dari akuntansi hingga pendidikan. Bahkan jika Anda sedang membangun karier di bidang lain yang belum dijelaskan di atas, fitur ini akan tetap membantu Anda menghasilkan uang. Inilah mengapa dia berharga.

Yang terpenting, ini memungkinkan Anda menghemat waktu, yang sayangnya, sumber daya yang terbatas. Tampaknya ada beberapa detik untuk menerapkan dua fungsi, tetapi ketika Anda harus melakukan sejumlah besar operasi berulang, maka detik-detik ini menambahkan hingga jam yang dapat dihabiskan untuk hal lain. Jadi kami sarankan Anda berlatih menggunakan fitur ini. Selain itu, ini sangat sederhana.