Fungsi SUMMESLE, serta SUMMESLES dengan dua kriteria

1. Cari berdasarkan tag

Bayangkan sebuah tabel di mana nama-nama departemen (atau akun, atau sesuatu yang lain) dicantumkan dalam baris berturut-turut.

Jumlahkan sel berdasarkan kriteria
Perlu untuk menghitung jumlah total untuk setiap departemen. Banyak yang melakukannya dengan filter dan menulis dengan pena di sel.
Meskipun dapat dilakukan dengan mudah dan sederhana hanya dengan satu fungsi - SUMMESLI .
SUMMESLES (SUMIF) —Menyimpan sel yang memenuhi kondisi tertentu (hanya satu kondisi yang dapat ditentukan). Fungsi ini juga dapat digunakan jika tabel dibagi menjadi kolom berdasarkan periode (bulanan, di setiap bulan, tiga kolom - Penghasilan | Biaya | Selisih) dan Anda perlu menghitung jumlah total untuk semua periode hanya dengan Penghasilan, Biaya, dan Perbedaan.

Ada tiga argumen total untuk SUMMESLI: Range , Criterion , Range_Summing .
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

• Rentang (A1: A20000) - menunjukkan rentang dengan kriteria. Yaitu Kolom tempat mencari nilai yang ditunjukkan oleh argumen Kriteria .
• Kriteria (A1) adalah nilai (teks atau angka, serta tanggal) yang harus ditemukan dalam Rentang . Mungkin mengandung karakter wildcard "*" dan "?". Yaitu menentukan Kriteria "* massa *" untuk merangkum nilai-nilai di mana kata "massa" muncul. Pada saat yang sama, kata "massa" dapat muncul di mana saja dalam teks, atau hanya ada satu kata ini dalam sel. Dan menentukan "massa *", semua nilai yang dimulai dengan "massa" akan diringkas. "?" - menggantikan hanya satu karakter, mis. menentukan "mas? a" Anda dapat meringkas garis dengan nilai "massa" dan nilai "topeng", dll.
  Jika kriteria ditulis dalam sel dan Anda masih perlu menggunakan karakter wildcard, maka Anda dapat membuat tautan ke sel ini dengan menambahkan yang diperlukan. Misalkan Anda perlu meringkas nilai-nilai yang mengandung kata "total." Kata "total" ditulis dalam sel A1, sedangkan di kolom A mungkin ada berbagai nilai ejaan yang mengandung kata "total": "total untuk Juni", "total untuk Juli", "total untuk Maret". Rumusnya akan terlihat seperti ini:
  = MUSIM PANAS (A1: A20000; "*" & A1 & "*"; B1: B20000)
  "*" & A1 & "*" - tanda & (ampersand) menggabungkan beberapa nilai menjadi satu. Yaitu hasilnya adalah "* hasil *".
  Untuk lebih memahami prinsip cara kerja rumus, lebih baik menggunakan alat Calculate Formula : Cara melihat langkah-langkah untuk menghitung formula
  Semua kriteria dan kriteria tekstual dengan tanda logis dan matematis harus dilampirkan dalam tanda kutip ganda (= SUMMESLI (A1: A20000; "total"; B1: B20000)). Jika kriterianya adalah angka, kutipan tidak diperlukan. Jika Anda ingin menemukan tanda tanya atau tanda bintang secara langsung, Anda harus meletakkan tilde (~) di depannya.
  Tentang tilde dan fitur-fiturnya dapat ditemukan di artikel ini: Bagaimana cara mengganti / menghapus / menemukan tanda bintang?
• Sum_Range (B1: B20000) (argumen opsional) - menentukan kisaran jumlah atau nilai numerik yang akan dijumlahkan.

Cara kerjanya: fungsi mencari Rentang untuk nilai yang ditentukan oleh argumen Kriteria , dan ketika kecocokan ditemukan, menjumlahkan data yang ditunjukkan oleh argumen Range_Amount. Yaitu jika kita memiliki nama departemen di kolom A dan jumlah di kolom B, maka menentukan Departemen Pembangunan sebagai kriteria akan menghasilkan jumlah semua nilai kolom B, yang berlawanan dengan Departemen Pembangunan ditemukan di kolom A. Faktanya, SumArrangement mungkin tidak memiliki ukuran yang sama dengan argumen Range dan ini tidak akan menyebabkan kesalahan fungsi itu sendiri. Namun, ketika mendefinisikan sel untuk penjumlahan, sel kiri atas argumen Range_Amount akan digunakan sebagai sel awal untuk penjumlahan, dan kemudian sel yang sesuai dalam ukuran dan bentuk untuk argumen Range akan dijumlahkan.

Beberapa fitur
Argumen terakhir dari fungsi (Sum_And_Band: B1: B20000) adalah opsional. Ini berarti bahwa itu tidak dapat ditentukan. Jika Anda tidak menentukannya, fungsi akan menambah nilai yang ditentukan oleh argumen Range . Untuk apa ini? Misalnya, Anda harus mendapatkan jumlah hanya angka-angka yang lebih besar dari nol. Di kolom A dari jumlah tersebut. Maka fungsinya akan terlihat seperti ini:
= MUSIM PANAS (A1: A20000; "> 0")

Apa yang harus dipertimbangkan: range_summing dan range harus sama dalam jumlah baris. Jika tidak, Anda bisa mendapatkan hasil yang salah. Secara optimal, jika akan terlihat seperti dalam rumus yang saya berikan: rentang dan rentang penjumlahan mulai dari satu baris dan memiliki jumlah baris yang sama: A1: A20000; B1: B20000

Penjumlahan atas dua kriteria atau lebih
Tetapi apa yang harus dilakukan ketika kriteria untuk penjumlahan 2 dan lebih banyak? Misalkan Anda perlu meringkas hanya jumlah-jumlah yang dimiliki oleh satu departemen dan hanya untuk tanggal tertentu. Pemilik bahagia versi office 2007 dan di atasnya dapat menggunakan fungsi SUMMESLIMN:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - range_summing. Argumen pertama menentukan rentang sel yang berisi jumlah yang akan dikumpulkan menjadi satu.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Menentukan rentang sel tempat Anda ingin mencari kecocokan berdasarkan kriteria.
$ I $ 3, $ H8 - kriteria. Di sini, seperti pada SUMMESLI, karakter wildcard * dan ? Diizinkan . dan mereka bekerja dengan cara yang sama.

Spesifik dari argumen yang menentukan: pertama, rentang kriteria ditentukan (mereka diberi nomor), kemudian nilai (kriteria) ditunjukkan secara langsung dalam titik koma, yang dalam kisaran ini harus ditemukan - $ A $ 2: $ A $ 50; $ I $ 3. Dan tidak ada yang lain. Anda seharusnya tidak mencoba menentukan terlebih dahulu semua rentang, dan kemudian kriteria untuknya - fungsi tersebut akan memberikan kesalahan, atau tidak akan meringkas apa yang diperlukan.

Semua kondisi dibandingkan menurut prinsip I. Ini berarti bahwa jika semua kondisi yang tercantum terpenuhi. Jika setidaknya satu syarat tidak terpenuhi, fungsi melompati garis dan tidak menambahkan apa pun.
Sedangkan untuk MUSIM PANAS, rentang penjumlahan dan kriteria harus sama dalam jumlah baris.

Karena SUMMESLIMN hanya muncul di versi Excel, mulai dari 2007, lalu bagaimana mungkin pengguna yang tidak puas dari versi sebelumnya dalam kasus seperti itu? Sangat sederhana: gunakan fungsi lain - SUMPRODUCT. Saya tidak akan melukis argumen, karena Ada banyak dari mereka dan mereka semua adalah array nilai. Fungsi ini mengalikan array yang ditunjukkan oleh argumen. Saya akan mencoba menjelaskan prinsip umum penggunaan fungsi ini untuk merangkum data pada beberapa kondisi.
Untuk mengatasi masalah penjumlahan dengan beberapa kriteria, fungsi akan terlihat seperti ini:
= SUMPRODUCT (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - rentang tanggal. $ I $ 3 adalah tanggal kriteria yang diperlukan untuk menjumlahkan data.
$ B $ 2: $ B $ 50 - nama-nama departemen. H5 - nama departemen, data yang harus diringkas.
$ C $ 2: $ C $ 50 - kisaran dengan jumlah.

Kami menganalisis logika, karena bagi banyak orang, itu akan sepenuhnya tidak jelas hanya dengan melihat fungsi ini. Kalau saja karena dalam bantuan aplikasi ini tidak dijelaskan. Untuk keterbacaan yang lebih besar, kurangi ukuran rentang:
= SUMPRODUCT (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Jadi, ekspresi ($ A $ 2: $ A $ 5 = $ I $ 3) dan ($ B $ 2: $ B $ 5 = H5) adalah logis dan mengembalikan array FALSE dan TRUE logis. BENAR jika sel rentang $ A $ 2: $ A $ 5 sama dengan nilai sel $ I $ 3 dan sel rentang $ B $ 2: $ B $ 5 sama dengan nilai sel H5. Yaitu kami memiliki yang berikut:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE}; $ C $ 2: $ C $ 50)
Seperti yang Anda lihat, dalam larik pertama ada dua kecocokan untuk kondisi tersebut, dan pada larik kedua. Lebih lanjut, kedua array ini dikalikan (tanda multiplikasi (*) bertanggung jawab untuk ini). Ketika perkalian terjadi, konversi implisit array FALSE dan TRUE ke konstanta numerik 0 dan 1, masing-masing ({0; 1; 1; 0} * {0; 0; 1; 0}) terjadi. Seperti yang Anda tahu, ketika dikalikan dengan nol, kami mendapat nol. Dan hasilnya adalah satu array:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Kemudian array {0; 0; 1; 0} dikalikan dengan array angka dalam kisaran $ C $ 2: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
Dan sebagai hasilnya, kami mendapat 30. Apa yang kami butuhkan - kami hanya mendapatkan jumlah yang memenuhi kriteria. Jika ada lebih dari satu jumlah yang memenuhi kriteria, maka mereka akan disimpulkan.

Keuntungan SUMMYROIZV
Jika argumen memiliki tanda tambah, bukan tanda multiplikasi:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
maka kondisinya akan dibandingkan menurut prinsip OR: yaitu jumlah total akan dijumlahkan jika setidaknya satu syarat terpenuhi: baik $ A $ 2: $ A $ 5 sama dengan nilai sel $ I $ 3 atau sel rentang $ B $ 2: $ B $ 5 sama dengan nilai sel H5.
Ini adalah keunggulan SUMMPRODUCT daripada SUMMESLIMN. SUMMESLIMN tidak dapat menjumlahkan nilai sesuai dengan prinsip OR, hanya sesuai dengan prinsip AND (semua kondisi harus dipenuhi).

Kekurangan
SUMPRODUCT tidak dapat menggunakan wildcard * dan ?. Dimungkinkan untuk menggunakan lebih tepat, tetapi mereka akan dianggap bukan sebagai karakter khusus, tetapi sebagai tanda bintang dan tanda tanya. Saya pikir ini adalah kerugian yang signifikan. Dan meskipun ini bisa di-bypass, saya menggunakan fungsi lain di dalam SUMPRODUCT - masih bagus jika fungsinya bisa menggunakan wildcard.

Dalam contoh Anda akan menemukan beberapa contoh fungsi untuk pemahaman yang lebih baik tentang apa yang ditulis di atas.

Unduh sebuah contoh

Jumlahnya berdasarkan beberapa kriteria (41.5 KiB, 10.477 Unduhan)

Lihat juga:
Menjumlahkan sel dengan mengisi warna
Penjumlahan sel dengan warna font
Menjumlahkan sel dengan format sel
Hitung jumlah sel dengan mengisi warna
Hitung jumlah sel berdasarkan warna font
Cara meringkas data dari beberapa lembar, termasuk berdasarkan kondisi

{"Bilah bawah": {"gaya teks": "statis", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," efek teks ":" slide "," efek teks ":" easyOutCubic "," texteffectduration ": 600," texteffectlidedduration ": 600," texteffectslidedtection ": 600," texteffectslidedlected "lection: " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easyOutCubic", "text1ffectation" , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectlidedistance2": 120, "texteffecteasing2": "easyOutCubic", "texteffectduration2": 600, "textdffectduration2": 600 textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: blok; posisi: absolut; atas: 0px; kiri: 0px; lebar: 100%; tinggi: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; posisi: relatif; font: tebal 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; warna: #fff; "," descriptioncss ":" display: block; posisi: relatif; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; warna: #fff; margin-top: 8px; "," buttoncss ":" display: block; posisi: relatif; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" ukuran font: 12px; "," descriptioncssresponsive ":" display: none: important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Cari berdasarkan tag