Fungsi SUMIF dalam Excel dan jumlah dengan berbilang syarat

Excel ialah program yang sangat berfungsi. Malah set ciri terbina dalam sudah cukup untuk menyelesaikan hampir semua tugas. Dan selain yang standard, biasa kepada ramai, ada juga yang jarang orang dengar. Tetapi pada masa yang sama, mereka tidak berhenti berguna. Mereka mempunyai pengkhususan yang lebih sempit, dan tidak selalu ada keperluan untuk mereka. Tetapi jika anda tahu tentang mereka, maka pada saat kritikal mereka boleh menjadi sangat berguna.

Hari ini kita akan bercakap tentang salah satu fungsi tersebut − SUMMESLIMN.

Sekiranya pengguna berhadapan dengan tugas merumuskan beberapa nilai, memfokuskan pada kriteria tertentu, maka perlu menggunakan fungsi SUMMESLIMN. Formula yang menggunakan fungsi ini mengambil syarat ini sebagai argumen, kemudian menjumlahkan nilai yang memenuhinya, dan kemudian nilai yang ditemui dimasukkan ke dalam sel di mana ia ditulis. 

Penerangan Terperinci Fungsi SUMIFS

Sebelum mempertimbangkan fungsi SUMMESLIMN, anda mesti terlebih dahulu memahami apakah versi yang lebih ringkas - SUMMESLI, kerana di atasnya fungsi yang kami pertimbangkan adalah berdasarkan. Setiap daripada kita mungkin sudah biasa dengan dua fungsi yang sering digunakan - SUM (menjalankan penjumlahan nilai) dan JIKA (menguji nilai terhadap keadaan tertentu).

Jika anda menggabungkannya, anda mendapat fungsi lain - SUMMESLI, yang menyemak data terhadap kriteria yang ditentukan pengguna dan menjumlahkan hanya nombor yang memenuhi kriteria tersebut. Jika kita bercakap tentang versi bahasa Inggeris Excel, maka fungsi ini dipanggil SUMIF. Dalam kata mudah, nama -language ialah terjemahan langsung dari bahasa Inggeris. Fungsi ini boleh digunakan untuk pelbagai tujuan. Khususnya, ia boleh digunakan sebagai alternatif VPR, iaitu, tulis

Perbezaan utama antara fungsi SUMMESLIMN  daripada fungsi biasa SUMMESLI ialah beberapa kriteria digunakan. Sintaksnya agak rumit pada pandangan pertama, tetapi apabila diteliti lebih dekat, ternyata logik fungsi ini sangat mudah. Mula-mula anda perlu memilih julat tempat data akan disemak, dan kemudian tetapkan syarat untuk pematuhan yang analisis akan dilakukan. Dan operasi sedemikian boleh dilakukan untuk sejumlah keadaan yang agak besar.

Sintaks itu sendiri ialah:

SUMIFS(julat_jumlah, julat_syarat1, syarat1, [julat_keadaan2, syarat2], …)

Di tempat yang sesuai adalah perlu untuk meletakkan tatasusunan sel yang sesuai dalam kes tertentu. 

Mari kita lihat hujah dengan lebih terperinci:

1. Jumlah_julat. Hujah ini, serta julat keadaan 1 dan syarat 1, diperlukan. Ia adalah satu set sel yang perlu dijumlahkan.
2. Keadaan_julat1. Ini ialah julat di mana keadaan akan diperiksa. Ia dipasangkan dengan hujah seterusnya - Syarat1. Penjumlahan nilai yang sepadan dengan kriteria dijalankan dalam sel yang dinyatakan dalam hujah sebelumnya.
3. Syarat1. Argumen ini menentukan kriteria untuk disemak. Ia boleh ditetapkan, sebagai contoh, dengan cara ini: "> 32".
4. Julat keadaan 2, Syarat 2… Di sini, syarat berikut ditetapkan dengan cara yang sama. Jika lebih daripada beberapa syarat perlu dinyatakan, maka argumen Julat Syarat 3 dan Syarat 3 ditambah. Sintaks adalah sama untuk argumen berikut.

Fungsi ini membenarkan pemprosesan maksimum sehingga 127 pasang keadaan dan julat. 

Anda boleh menggunakannya di beberapa kawasan sekali gus (kami akan memberikan hanya beberapa, senarai sebenarnya lebih panjang):

1. Perakaunan. Sebagai contoh, adalah baik untuk menggunakan fungsi tersebut SUMMESLIMN untuk membuat laporan ringkasan, mengikut suku tahun untuk perbelanjaan melebihi jumlah tertentu, contohnya. Atau buat laporan tentang satu produk daripada kategori harga tertentu.
2. Pengurusan jualan. Di sini juga fungsinya boleh menjadi sangat berguna. Sebagai contoh, kita berhadapan dengan tugas merumuskan hanya kos barangan yang dijual kepada pelanggan tertentu pada masa tertentu. Dan dalam keadaan sedemikian, fungsi SUMMESLIMN boleh jadi sangat membantu.
3. Pendidikan. Kami akan memberikan lebih banyak contoh praktikal dari kawasan ini hari ini. Khususnya, anda boleh menggunakannya untuk mendapatkan ringkasan gred pelajar. Anda boleh memilih untuk satu mata pelajaran atau untuk gred individu. Seseorang boleh segera menetapkan beberapa kriteria yang mana penilaian akan dipilih, yang sangat mudah dan boleh menjimatkan banyak masa.

Seperti yang anda lihat, julat aplikasi untuk fungsi ini sangat luas. Tetapi ini bukan satu-satunya meritnya. Mari kita lihat beberapa lagi faedah yang ada pada ciri ini:

1. Keupayaan untuk menetapkan pelbagai kriteria. Mengapa ini satu kelebihan? Anda boleh menggunakan fungsi biasa SUMMESLI! Dan semua kerana ia mudah. Tidak perlu melakukan pengiraan berasingan untuk setiap kriteria. Semua tindakan boleh diprogramkan lebih awal, walaupun sebelum ini. bagaimana jadual data akan dibentuk. Ini adalah penjimat masa yang hebat.
2. Automasi. Zaman moden ialah zaman automasi. Hanya orang yang tahu cara mengautomasikan kerjanya dengan betul boleh memperoleh banyak. Itulah sebabnya keupayaan untuk menguasai Excel dan fungsi SUMMESLIMN khususnya, sangat penting bagi mana-mana orang yang ingin membina kerjaya. Mengetahui satu fungsi membolehkan anda melakukan beberapa tindakan sekaligus, sebagai satu. Dan di sini kita beralih kepada manfaat seterusnya ciri ini.
3. Menjimatkan masa. Hanya disebabkan oleh fakta bahawa satu fungsi melakukan beberapa tugas sekaligus.
4. Kesederhanaan. Walaupun fakta bahawa sintaks agak berat pada pandangan pertama kerana kebesarannya, sebenarnya, logik fungsi ini sangat mudah. Mula-mula, julat data dipilih, kemudian julat nilai, yang akan diperiksa untuk mematuhi syarat tertentu. Dan sudah tentu, syarat itu sendiri juga mesti ditentukan. Dan begitu beberapa kali. Malah, fungsi ini berdasarkan hanya satu binaan logik, yang menjadikannya lebih mudah daripada yang terkenal VPR walaupun pada hakikatnya ia boleh digunakan untuk tujuan yang sama, juga mengambil kira bilangan kriteria yang lebih besar. 

Ciri menggunakan fungsi SUMIFS

Terdapat beberapa ciri menggunakan fungsi ini yang perlu anda perhatikan. Pertama sekali, fungsi ini mengabaikan julat dengan rentetan teks atau nol, kerana jenis data ini tidak boleh ditambah bersama dalam corak aritmetik, hanya digabungkan seperti rentetan. Fungsi ini tidak boleh melakukan ini. Anda juga perlu memberi perhatian kepada syarat-syarat berikut:

1. Anda boleh menggunakan jenis nilai ini sebagai syarat untuk memilih sel untuk menambahkan lagi nilai yang terkandung di dalamnya: nilai angka, ungkapan boolean, rujukan sel dan sebagainya. 
2. Jika teks, ungkapan logik atau tanda matematik sedang disemak, maka kriteria tersebut ditentukan melalui petikan.
3. Tidak boleh menggunakan istilah yang lebih panjang daripada 255 aksara.
4. Anda boleh menggunakan kriteria anggaran untuk memilih nilai menggunakan kad bebas. Tanda soal digunakan untuk menggantikan satu aksara, dan tanda darab (asterisk) diperlukan untuk menggantikan berbilang aksara. 
5. Nilai Boolean yang berada dalam julat penjumlahan ditukar secara automatik kepada nilai angka mengikut jenisnya. Oleh itu, nilai "TRUE" bertukar menjadi satu, dan "FALSE" - menjadi sifar. 
6. Jika #VALUE! ralat muncul dalam sel, ini bermakna bilangan sel dalam keadaan dan julat penjumlahan adalah berbeza. Anda perlu memastikan bahawa saiz hujah ini adalah sama. 

Contoh penggunaan fungsi SUMIFS

fungsi SUMMESLIMN tidak rumit seperti yang kelihatan pada pandangan pertama, ternyata. Tetapi untuk lebih jelas, mari lihat beberapa contoh praktikal tentang cara anda boleh menggunakan fungsi tersebut SUMMESLIMN. Ini akan menjadikannya lebih mudah untuk menyelidiki topik tersebut.

Julat dinamik penjumlahan keadaan

Jadi mari kita mulakan dengan contoh pertama. Katakan kita mempunyai jadual yang mengandungi maklumat tentang cara pelajar menghadapi kurikulum dalam mata pelajaran tertentu. Terdapat satu set gred, prestasi dinilai pada skala 10 mata. Tugasnya adalah untuk mencari gred untuk peperiksaan pelajar yang nama belakangnya bermula dengan huruf A, dan markah minimum mereka ialah 5.

Jadual kelihatan seperti ini.

Untuk kita mengira jumlah markah berdasarkan kriteria yang diterangkan di atas, kita perlu menggunakan formula berikut.

Mari kita huraikan hujah dengan lebih terperinci:

1. C3:C14 ialah julat penjumlahan kami. Dalam kes kami, ia bertepatan dengan julat keadaan. Daripada itu akan dipilih mata yang digunakan untuk mengira jumlah, tetapi hanya mata yang berada di bawah kriteria kami.
2. “>5” ialah syarat pertama kami.
3. B3:B14 ialah julat penjumlahan kedua yang diproses untuk memadankan kriteria kedua. Kami melihat bahawa tidak ada kebetulan dengan julat penjumlahan. Daripada ini kami membuat kesimpulan bahawa julat penjumlahan dan julat keadaan mungkin sama atau tidak. 
4. “A*” ialah julat kedua, yang menentukan pemilihan markah hanya untuk pelajar yang nama akhir mereka bermula dengan A. Dalam kes kami, asterisk bermaksud sebarang bilangan aksara. 

Selepas pengiraan, kami mendapat jadual berikut.

Seperti yang anda lihat, formula merumuskan nilai berdasarkan julat dinamik dan berdasarkan syarat yang ditentukan oleh pengguna.

Penjumlahan terpilih mengikut syarat dalam Excel

Sekarang andaikan kita ingin mendapatkan maklumat tentang barang yang dihantar ke negara mana pada suku terakhir. Selepas itu, cari jumlah hasil daripada penghantaran untuk bulan Julai dan Ogos.

Jadual itu sendiri kelihatan seperti ini. 

Untuk menentukan keputusan akhir, kita memerlukan formula sedemikian.

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

Hasil daripada pengiraan yang dijalankan oleh formula ini, kami mendapat hasil berikut.

Perhatian! Formula ini kelihatan agak besar walaupun kami hanya menggunakan dua kriteria. Jika julat data adalah sama, maka anda boleh mengurangkan panjang formula dengan ketara, seperti yang ditunjukkan di bawah.

SUMIFS berfungsi untuk menjumlahkan nilai merentasi pelbagai keadaan

Sekarang mari kita berikan satu lagi contoh untuk menggambarkan. Dalam kes ini, jadual tetap sama seperti dalam kes sebelumnya. 

Kami menggunakan formula berikut (tetapi kami menulisnya sebagai formula tatasusunan, iaitu, kami memasukkannya melalui kombinasi kekunci CTRL + SHIFT + ENTER).

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

Selepas fungsi SUMMESLIMN akan menjumlahkan tatasusunan nilai berdasarkan kriteria yang dinyatakan dalam formula (iaitu, negara China dan Georgia), tatasusunan yang terhasil dijumlahkan dengan fungsi biasa JUMLAH, yang ditulis sebagai formula tatasusunan.

Jika syarat telah diluluskan sebagai pemalar tatasusunan untuk lebih daripada satu pasangan, maka formula akan memberikan hasil yang salah.

Sekarang mari kita lihat jadual yang mengandungi jumlah keseluruhan.

Seperti yang anda lihat, kami telah berjaya. Anda pasti akan berjaya juga. Kejayaan besar dalam bidang ini. Ini adalah fungsi yang sangat mudah yang boleh difahami oleh seseorang yang baru menjejakkan kaki di laluan pembelajaran Excel. Dan kita sudah tahu bahawa fungsi SUMMESLIMN membolehkan anda menjadi berkesan dalam mana-mana bidang aktiviti, daripada perakaunan sehinggalah pendidikan. Walaupun anda sedang membina kerjaya di mana-mana kawasan lain yang belum diterangkan di atas, ciri ini masih akan membantu anda memperoleh wang. Itulah sebabnya dia berharga.

Paling penting, ia membolehkan anda menjimatkan masa, yang malangnya, sumber yang terhad. Nampaknya terdapat beberapa saat untuk menggunakan dua fungsi, tetapi apabila anda perlu melakukan sejumlah besar operasi berulang, maka detik ini menambah sehingga jam yang boleh dibelanjakan untuk sesuatu yang lain. Jadi kami mengesyorkan anda berlatih menggunakan ciri ini. Lebih-lebih lagi, ia adalah sangat mudah.