Fungsi SUMMESLE, serta SUMMESLES dengan dua kriteria

  1. Cari berdasarkan tag
Trik »11 Juni 2011 Dmitry 243582 dilihat

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

Artikel membantu? Bagikan tautan dengan teman Anda! Tutorial video

{"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

Pergi Akses jam tangan apel Multex Outlook Kueri Daya dan Daya BI VBA bekerja di editor Manajemen kode VBA Add-in gratis Tanggal dan waktu Grafik dan grafik Catatan Perlindungan data Internet Gambar dan benda Seprai dan buku Macro dan VBA Add-on Kustomisasi Cetak Cari data Kebijakan Privasi Mail Program Bekerja dengan aplikasi Bekerja dengan file Pengembangan aplikasi Tabel Ringkasan Daftar Pelatihan dan webinar Keuangan Memformat Formula dan fungsi Fungsi Excel Fungsi VBA Sel dan rentang MulTEx membagikan analisis data bug dan gangguan di Excel tautan

Новости

Социальные сети в России, зима 2015-2016 Цифры, тренды, прогнозы | Блог Brand Analуtics — все о бренд мониторинге и социальной аналитике
Представляем данные регулярного исследования активной аудитории социальных сетей в России, зима 2015-2016. В исследовании представлены данные по аудитории, возрасту, полу и региональному распределению

SMM маркетинг от А до Я
Развитие социальных сетей породило новую огромную бизнес отрасль — СММ маркетинг. Это когда мы приходим с нашим продуктом в какую-нибудь соцсеть, находим там нашу целевую аудиторию, и предлагаем ей наш

Полезно знать
20.02.2014 Что такое Pinterest. Визуальный PR и маркетинг Pinterest или ПИНТЕРЕСТ – социальная сеть, использующая вместо текстов картинки и изображения, которые можно добавлять в аккаунт,

Фейсбук Моя страница: ВХОД на свою страницу Facebook
Фейсбук (Facebook) Моя страница — это личная страница каждого зарегистрированного пользователя самой большой в мире социальной сети.  Фейсбук даёт возможность людям с разных уголков Земли общаться между

Анализ социальных сетей
АНАЛИЗ СОЦИАЛЬНЫХ СЕТЕЙ (social network analysis) - новое направление структурного подхода, основными целями которого являются исследование взаимодействий между социальными объектами и выявление условий

Социальные сети для продвижения бизнеса: какие выбрать?
  Привет, друзья! Социальные сети занимают львиную часть времени в нашей жизни. Ежедневный просмотр новостей, любимых групп и переписка с друзьями – это любимое дело большинства молодых людей. Вот и

ВРЕДНОЕ ВЛИЯНИЕ СОЦИАЛЬНЫХ СЕТЕЙ НА ЗДОРОВЬЕ ЧЕЛОВЕКА - VI Студенческий научный форум (15 февраля - 31 марта 2014 года)
ВРЕДНОЕ ВЛИЯНИЕ СОЦИАЛЬНЫХ СЕТЕЙ НА ЗДОРОВЬЕ ЧЕЛОВЕКА Гущина Н. В., Люлина Н.В. С распространением всемирной паутины рост социальных сетей по всему миру стал глобальным явлением. С каждым

Влияние социальных сетей на подростков. Пропаганда суицида в сетях
Почему мы любим интернет? Почему молодым людям, подросткам сказать «Привет» стало проще, познакомившись в интернете, чем просто подойти в реальности? Почему все «сидят» в социальных сетях? Социальные сети

Маркетинг в социальных сетях (SMM) для продвижения компании
Деятельность в социальных сетях, на блогах и форумах по повышению лояльности клиентов получила название SMM — от аббревиатуры английского звучания этого термина – Social Media Marketing. Аудитория

Фейсбук (Facebook) - что это за сеть: как пользоваться ФБ
Фейсбук — это самая популярная и большая социальная сеть в мире. Если Вам интересно узнать больше о Facebook, про то как пользоваться сетью и о некоторых «фишках» ФБ — этот материал именно для вас!