Penjumlahan sel-sel tertentu di Excel. Penjumlahan sampel untuk dua kriteria

Perumusan masalah

Kami memiliki tabel untuk penjualan, misalnya, formulir berikut:

Tugas : Untuk meringkas semua pesanan yang telah diterapkan oleh manajer Grigoriev untuk toko kopik.

Metode 1. Fungsi SUMING (SUMIF)

Jika hanya satu syarat dalam tugas kami (semua pesanan grigoriev atau semua pesanan dalam "sen"), maka tugas akan dipecahkan dengan mudah menggunakan fungsi Excel yang disematkanSmerei. Dari kategori matematika. Tentang cara menggunakannya dapat dibaca. Tetapi dalam kasus kami ada dua syarat, dan bukan satu, jadi metode ini tidak cocok ...

Metode 2. Indikator Kolom

Tambahkan ke tabel kami Kolom lain, yang akan berfungsi sebagai jenis indikator: Jika pesanan ada di "sen" dan dari grigoriev, maka di sel kolom ini akan ada nilai 1 selain 0. Formula itu Harus dimasukkan ke dalam kolom ini sangat sederhana:

\u003d (A2 \u003d "kopeyka") * (b2 \u003d "grigoriev")

Kesetaraan logis dalam tanda kurung memberikan nilai kebenaran atau salah, yang setara dengan 1 dan 0. Dengan demikian, karena kita mengubah ekspresi-ekspresi ini, unit pada akhirnya akan berhasil jika kedua kondisi dijalankan. Sekarang biaya penjualan tetap dikalikan dengan nilai kolom yang diterima dan merangkum jumlah yang diperoleh:

Metode 3. Formula Magic Besar

Jika Anda belum menemukan peluang Excel yang luar biasa sebagai formula array, saya menyarankan Anda untuk membaca banyak hal baik tentang mereka.. Nah, dalam kasus kami, tugas diselesaikan dengan satu formula:

SUMS ((A2: A26 \u003d "Kopeck") * (B2: B26 \u003d "Grigoriev") * D2: D26)

Setelah memasukkan rumus ini, perlu untuk mengklik tidak masuk, seperti biasa, tetapiCtrl + Shift + Enter - Lalu Excel akan merasakannya sebagaiformula Massiva. Dan menambah kurung kurawal itu sendiri. Anda tidak perlu memasukkan tanda kurung dari keyboard. Sangat mudah untuk mengetahui bahwa metode ini (seperti yang sebelumnya) mudah ditingkatkan tiga, empat, dll. Kondisi tanpa batasan apa pun.

METODE 4. Fungsi basis data BDSMMM

Dalam kategori basis data Anda dapat menemukan fiturBdsmm (dsum) yang juga dapat membantu kami memecahkan tugas kami:

Nuansa adalah bahwa perlu untuk membuat serangkaian sel pada lembar untuk bekerja fungsi ini yang berisi kondisi pemilihan, dan kemudian tentukan rentang fungsi ini sebagai argumen:

BDSMM (A1: D26; D1; F1: G2)

Metode 5. Jumlah Pihak Wizard

Disebut add-in excel yang membantu membuat formula kompleks Untuk penjumlahan multibriterial. Hubungkan add-in gratis ini dapat melalui menuLayanan - Add-in - Master of penjumlahan(Alat - Add-in - SUM SUM WIZARD). Setelah itu di menuLayanan Tim harus munculJumlah parsial , Running penjumlahan Master:

Di langkah pertama Master penjumlahan Anda harus menentukan kisaran dengan data, I.E. Pilih semua tabel kami. Pada langkah kedua, Anda perlu memilih kolom untuk menjumlahkan dan membentuk kondisi untuk memilih nilai dengan menambahkan setiap kondisi ke daftar tombolTambahkan kondisi:

Dan akhirnya, pada langkah ke-3 dan ke-4, kami menentukan sel di mana perlu untuk mendapatkan hasilnya. Dan kami mendapatkan hal berikut sebagai hasilnya:

Sangat mudah untuk memperhatikan bahwa kami menggunakan sesuatu yang mirip dengan rumus array ini dengan cara 3. Hanya di sini Anda tidak dapat menyentuh keyboard sama sekali - Biarkan diri Anda malas - mesin kemajuan!

Tahukah kamu, Apa itu eksperimen mental, percobaan Gedanken?
Ini adalah praktik yang tidak ada, dunia lain, imajinasi apa yang sebenarnya tidak. Eksperimen pikiran mirip dengan tidur. Mereka melahirkan monster. Tidak seperti eksperimen fisik, yang merupakan hipotesis uji yang sangat baik, "eksperimen mental" dengan fokus menggantikan verifikasi eksperimental yang diinginkan, tidak diuji dalam praktik dengan kesimpulan, memanipulasi konstruksi berbentuk logika yang benar-benar melanggar logika itu sendiri dengan menggunakan paket yang belum terbukti, itu dengan substitusi. Dengan demikian, tugas utama pelamar "eksperimen mental" adalah penipuan pendengar atau pembaca dengan mengganti percobaan fisik saat ini dengan "boneka" - penalaran fiktif dengan jujur \u200b\u200btanpa inspeksi fisik.
Mengisi Imajiner Fisika, "Eksperimen Mental" menyebabkan munculnya gambaran dunia yang absurd, bingung-bingung-kusut. Peneliti ini harus membedakan "permen" seperti itu dari nilai-nilai ini.

Relativis dan positivis berpendapat bahwa "eksperimen mental" adalah intrusi yang sangat berguna untuk memeriksa teori (juga timbul dalam pikiran kita) untuk konsistensi. Dalam hal ini mereka menipu orang, karena setiap cek hanya dapat dilakukan terlepas dari objek inspeksi. Hipotesis pemohon itu sendiri tidak dapat memeriksa pernyataannya sendiri, karena penyebab pernyataan ini sendiri adalah tidak adanya kontradiksi yang terlihat oleh pemohon.

Kami melihat ini pada contoh seratus dan dari, yang berubah menjadi semacam agama, mengelola sains dan opini publik. Tidak ada jumlah fakta yang bertentangan dengan mereka tidak dapat mengatasi formula Einstein: "Jika faktanya tidak sesuai dengan teori - ubah fakta" (dalam versi lain "- Fakta tidak sesuai dengan teori? - Lebih buruk untuk fakta" ).

Maksimal, apa "eksperimen mental" dapat mengklaim hanya pada konsistensi internal hipotesis dalam dirinya sendiri, seringkali bukan logika sebenarnya dari pemohon. Menurut latihan, ini tidak memeriksa. Pemeriksaan ini hanya dapat terjadi dalam eksperimen fisik yang valid.

Eksperimen pada percobaan bahwa dia bukan obat pemikiran, tetapi menguji pemikiran. Secara konsisten dengan sendirinya tidak dapat memeriksa sendiri. Ini terbukti Kurt Gödel.

Berita Forum

Ksatria teori eter

10/30/2017 - 06:17: -\u003e - Karim_hydarov.
10/19/2017 - 04:24: -\u003e - Karim_hydarov.
11.10.2017 - 05:10: -\u003e - Karim_hydarov.
05.10.2017 - 11:03:

Fungsi Smerei. Digunakan jika perlu untuk meringkas nilai rentang yang sesuai dengan kondisi yang ditentukan. Misalkan, misalnya, bahwa dalam kolom dengan angka perlu untuk meringkas hanya nilai yang melebihi 5. Untuk ini, Anda dapat menggunakan rumus berikut: \u003d SMERELY (B2: B25; "\u003e 5")

Video ini adalah bagian kursus pelatihan Penambahan angka di Excel 2013.

Tips:

Sintaksis

Summase (rentang; kondisi; [range_suming])

Fungsi argumen. Smerei. Menjelaskan di bawah ini.

Jarak . Argumen wajib. Rentang sel dinilai untuk kepatuhan dengan kondisi. Sel dalam setiap kisaran harus mengandung angka, nama, array atau referensi ke angka. Sel-sel kosong dan sel yang mengandung nilai teks dilewati. Rentang yang dipilih dapat berisi tanggal dalam format Excel standar (lihat contoh di bawah).

Kondisi . Argumen wajib. Kondisi dalam bentuk angka, ekspresi, tautan ke sel, teks atau fungsi yang menentukan sel mana yang harus dijumlahkan. Misalnya, suatu kondisi dapat diwakili dalam bentuk ini: 32, "\u003e 32", B5, "32", "apel" atau hari ini ().

Penting: Semua kondisi dan kondisi tekstual dengan tanda-tanda logis dan matematika harus dimasukkan dalam kutipan ganda ( " ). Jika kondisinya adalah angka, tidak diperlukan untuk menggunakan kutipan.

Range_suming. . Argumen opsional. Sel, nilai-nilai yang disimpulkan jika mereka berbeda dari sel yang ditentukan sebagai jarak . Jika argumennya range_suming. dihilangkan, Excel merangkum sel yang ditunjukkan dalam argumen jarak (Sel yang sama dengan kondisi mana berlaku).

Dalam argumen kondisi Anda dapat menggunakan wildcard: tanda tanya ( ? ) dan tanda bintang ( * ). Tanda tanya cocok dengan satu karakter, dan tanda bintang - setiap urutan karakter. Jika Anda ingin menemukan tanda tanya (atau tanda bintang) secara langsung, Anda perlu meletakkan tanda "Tilda" sebelumnya ( ~ ).

Catatan

Fungsi diam mengembalikan hasil yang salah jika digunakan untuk mencocokkan string lebih dari 255 karakter atau diterapkan pada garis # Mean!.

Argumen range_suming. mungkin tidak bertepatan dengan argumen jarak . Saat menentukan sel-sel aktual yang akan dirangkum, sel kiri atas argumen digunakan sebagai inisial range_suming. dan kemudian menyimpulkan sel-sel bagian dari kisaran yang sesuai dengan ukuran argumen jarak . Contoh:

Jarak	Rentang penjumlahan	Sel yang sebenarnya

Namun, jika argumen jarak dan range_suming. Fungsi-fungsi diam mengandung jumlah sel yang berbeda, perhitungan ulang daun dapat memakan waktu lebih dari yang diharapkan.

Contohnya

Contoh 1.

Salin data sampel dari tabel berikut dan masukkan ke dalam sel A1. leaf Excel.. Untuk menampilkan hasil formula, pilih dan tekan tombol F2, dan kemudian tombol Enter. Jika perlu, ubah lebar kolom untuk melihat semua data.

Biaya Properti	Komisi	Data




Rumus	Deskripsi	Hasil
Smerely (A2: A5; "\u003e 160000"; B2: B5)	Jumlah komisi untuk properti bernilai lebih dari 1 600.000 ₽.
Smerely (A2: A5; "\u003e 160000")	Jumlah properti bernilai lebih dari 1 600.000 ₽.
Smerely (A2: A5; 300000; B2: B5)	Jumlah komisi untuk harganya harganya 3.000.000 ₽.
Smerely (A2: A5; "\u003e" B2: B5)	Jumlah komisi untuk properti, biaya yang melebihi nilai dalam sel C2.

Contoh 2.

Salin data sampel dari tabel di bawah ini dan rekatkan ke dalam sel A1 dari lembar Excel baru. Untuk menampilkan hasil formula, pilih dan tekan tombol F2, dan kemudian tombol Enter. Selain itu, Anda dapat menyesuaikan lebar kolom sesuai dengan data yang terkandung di dalamnya.

	Produk.	Volume penjualan
	Tomat
	Seledri
	Jeruk



Rumus	Deskripsi	Hasil
Smerely (A2: A7; "buah-buahan"; C2: C7)	Penjualan semua kategori produk "buah".
Smerely (A2: A7; "Sayuran"; C2: C7)	Volume penjualan semua kategori produk "Sayuran".
SMERELY (B2: B7; "* S"; C2: C7)	Penjualan semua produk, yang namanya berakhir pada "S" ("tomat" dan "jeruk").
Smerely (A2: A7; ""; C2: C7)		informasi tambahan Anda selalu dapat mengajukan pertanyaan oleh komunitas Excel Tech, meminta bantuan dalam komunitas Jawaban Komunitas, serta menawarkan fitur atau peningkatan baru di situs web.

Selamat siang!

Lanjutkan keinginan saya untuk menggambarkan berbagai fungsi di Excel dan di samping kami. Ini adalah perwakilan lain dari fungsi penjumlahan, tetapi dengan kondisi spesifiknya. Fungsi BDSMM di Excel Mencari kedua angka di tabel Anda sesuai dengan kriteria ini, ini adalah properti utamanya.

Saya dapat mengatakan tangan pada hati saya bahwa banyak perhitungan dan perhitungan dapat dilakukan tanpa menggunakan berbagai, atau sampel, tetapi jika Anda perlu membuat sampel yang kompleks menggunakan tanda-tanda "wildcard", maka Anda benar-benar perlu menggunakan pahlawan artikel kita.

Untuk memulai, pertimbangkan sintaks yang digunakan:

\u003d Bdsmm (rentang basis data Anda; bidang pencarian; kondisi pencarian)dimana

Saat BEKERJA S. fungsi bdsmmm. Biaya untuk menandai beberapa kondisi yang harus Anda perhatikan saat bekerja:

Ketika melakukan pekerjaan pada seluruh kolom menyelesaikan data, perlu untuk memasukkan string kosong di bawah judul kolom dalam rentang kriteria yang ditentukan;
Rentang kriteria seharusnya tidak memiliki persimpangan dengan daftar;
Meskipun data untuk pembentukan rentang kondisi dapat ditempatkan di setiap tempat pada lembar, tetapi bagaimanapun, itu tidak boleh dilakukan setelah daftar, karena data yang kami tambahkan ke daftar akan dimasukkan ke dalam baris pertama setelahnya Daftar dan jika ada data dalam string, lalu tambahkan baru tidak akan berfungsi;
Anda dapat menggunakan kondisi kisaran apa pun yang mengandung setidaknya satu judul dan setidaknya satu sel, yang terletak di bawah judul dan berisi suatu kondisi.

Jadi, bagian teoretis, saya menganggapnya dibuat, lanjutkan sekarang untuk penggunaan praktis. fungsi BDSUMM. Dalam karyanya, untuk ini kami menganggap beberapa contoh untuk melakukan, saya membuat contoh pada prinsip, tetapi alih-alih mengumpulkan nilai pada kriteria akan ada penjumlahan:

Bdsmm C. bdsm. Satu kriteria numerik.

Jadi, untuk permulaan, pertimbangkan contoh sederhana dengan kriteria numerik tunggal, untuk ini kita akan memilih kolom "panen" dan kami menunjukkan bahwa kita membutuhkan pohon dengan hasil "\u003e \u003d 10". Untuk mendapatkan hasilnya, kita perlu rumus jenis ini (saya menyarankan Anda untuk menggunakan tautan absolut):

\u003d BDSMM ($ B $ 6: $ G $ 12; E6; E2: E3),

di mana, $ B $ 6: $ G $ 12 Kisaran di mana kami akan merangkum, E6 - satu kolom di mana kami akan meringkas dan E2: E3 kisaran di mana kami memasukkan kriteria untuk penjumlahan. Akibatnya, formula menemukan 3 posisi untuk jumlah total 34.

Untuk hasilnya, Anda juga dapat menggunakan formula seperti itu:

\u003d SMEFT (E7: E12; "\u003e \u003d 10")

\u003d SILENT (E7: E12; E3)

Fungsi BDSMM dengan satu kriteria teks

Sekarang pertimbangkan bagaimana fungsi BDSM berperilaku dengan kriteria teks, secara umum semuanya tetap dalam contoh sebelumnya, kecuali sebagai kriteria teks menunjukkan, dan hanya ditunjukkan dalam bentuk ini: \u003d "\u003d s.serovo" dan kemudian hasilnya akan memiliki HASIL Kalau tidak, formula tidak akan mengenali kriteria Anda. Sekarang kami mengganti kriteria ini dalam formula dan mendapatkan:

\u003d BDSMM ($ B $ 6: $ G $ 12; E6; C2: C3)Karena kita hanya dapat melihat perubahan dalam kisaran kriteria.

Untuk mendapatkan hasil yang serupa, Anda akan berguna:

\u003d SMERELY (C7: C12; "S.SEROVO"; E7: E12)

Penjumlahan untuk dua kriteria untuk kolom yang berbeda

Contohnya rumit dengan menggunakan dua kriteria, tetapi kami tidak akan menggunakan apa pun secara fundamental baru, kami tidak akan menerapkan kriteria teks "s.serovo" dan kriteria numerik "\u003e \u003d 10", meninggalkan penjumlahan lapangan "panen. ", Kami mendapatkan perubahan formula hanya dengan argumen terakhir sebagai hasilnya. Sekarang formula kami akan terlihat seperti ini:

\u003d BDSMM ($ B $ 6: $ G $ 12; E6; C2: E3)Sekali lagi, lihat perubahan saja alamat kriteria.

Opsi alternatif dapat diperoleh menggunakan keduanya:

\u003d Smevertimn (E7: E12; C7: C12; C3; E7: E12; E3)

\u003d SMEREVEYLMN (E7: E12; C7: C12; "S.SEROVO"; E7: E12; "\u003e \u003d 10")

Penjumlahan satu dari dua kondisi dalam satu kolom

Pertimbangkan opsi lain seperti yang diterapkan, tetapi sekarang kami akan menggunakan tidak ada kriteria tunggal, tetapi dua kali lipat, tetapi untuk satu bidang. Pastikan dua kriteria harus dalam garis yang berbeda. Inti dari rumus dikurangi menjadi fakta bahwa ia melewati kisaran yang sama dua kali lebih banyak dari kriteria secara terpisah. Untuk contoh ini, rumus akan terlihat seperti ini:

\u003d BDSMM ($ B $ 6: $ G $ 12; E6; C2: C4)Di sini kita mengubah jangkauan kriteria lagi, tetapi tidak lebar, dan tinggi.

Juga pengganti, Anda dapat menggunakan jumlah fungsi;

\u003d SMEFT (C7: C12; C3; E7: E12) + SMEREYS (C7: C12; E12).

Penjumlahan satu dari dua kondisi dalam dua kolom yang berbeda

Contoh aplikasi kelima akan merangkum satu dari dua kondisi dalam dua kolom yang berbeda dan tentu saja argumen harus ditempatkan semua di baris baru, ini akan memungkinkan mereka untuk menyimpulkan. Untuk keperluan ini, kita akan memerlukan formula:

\u003d BDSMM ($ B $ 6: $ G $ 12; D6; C2: D4)Prinsip formula formula dipertahankan, pemisahan kisaran kriteria, yang mencakup tiga baris: tajuk dan dua kriteria.

Penjumlahan untuk dua kriteria teks untuk dua kolom

Dalam contoh pekerjaan ini, kami menganggap analog yang hampir lengkap dari contoh yang sebelumnya dianggap, ketika ada dua kriteria dalam dua kolom, tetapi ada kriteria angka dan teks, dan di sini kita akan mempertimbangkan menjumlahkan dua kriteria teks dan pada dua kolom. Kami menggunakan kriteria "\u003d" \u003d s.ivanovo "dan" \u003d "\u003d ceri" ", yang kami tunjukkan dalam kisaran kriteria. Jadi formula kami akan memiliki formulir berikut:

\u003d BDSMM ($ B $ 6: $ G $ 12; D6; B2: C3).

Menggunakan hasil formula untuk mendapatkan seleksi dan kriteria penjumlahan

Dalam contoh ini, fungsi BDSMM saya menggunakan rumus untuk menentukan kriteria pemilihan dan penjumlahan, dalam hal ini kita akan menemukan pohon apa yang kita bawa penghasilan dan untuk menentukan argumen bersyarat, kita mengambil rata-rata untuk penjualan buah-buahan dari pohon dan semua itu di atas indikator rata-rata yang menarik bagi kita. Untuk menentukan nilai rata-rata yang akan menjadi kriteria kami "Kebenaran" kami membuat formula:

\u003d G7\u003e SRNVOV ($ G $ 7: $ G $ 12)Kita tidak lupa untuk mengamankan kisaran, sehingga ketika ada formula nilai-nilai, mereka tidak meluncur ke bawah, tetapi nilai G7 harus menggeser ke seluruh rentang untuk menentukan "kebohongan" atau "kebenaran".

Sangat penting bahwa judul judul tidak duplikat, memiliki perbedaan, oleh karena itu bidang kriteria yang saya sebut "rata-rata". Dan kemudian formula akan mulai bekerja, itu akan memindahkan seluruh kisaran $ G $ 7: $ G $ 12 untuk kehadiran nilai rata-rata dan setelah menerima hasil positif "kebenaran" itu akan meringkas. Formula berikut akan mengatasi pekerjaan ini:

\u003d BDSMM ($ B $ 6: $ G $ 12; G6; $ G $ 2: $ G $ 3)

Dan jika Anda sangat tertarik dengan solusi alternatif untuk masalah ini, maka coba opsi dengan fungsi sebagai fungsi dalam formulir ini:

\u003d Smerei ($ G $ 7: $ G $ 12; "\u003e" & Srvnah ($ G $ 7: $ G $ 12))

Fungsi BDSMM dalam tiga kriteria

Dalam contoh ini, kami mempertimbangkan rata-rata penjualan dari buah-buahan dewasa di dua desa: "s.ivanovo" dan "S. vechny". Saya telah menggambarkan ide dasar pemilihan kriteria, jadi saya tidak akan diulang, saya hanya akan mengatakan bahwa ini akan menjadi koneksi kriteria yang dianggap sebelumnya. Untuk mendapatkan hasilnya, kita perlu dalam bentuk ini:

\u003d BDSMM ($ B $ 6: $ G $ 12; G6; $ C $ 2: $ G $ 4)

Penjumlahan dengan kriteria teks dengan mempertimbangkan register

Seperti yang saya sebutkan sebelumnya, pencarian dapat dicari tidak hanya dengan simbol wildcard, tetapi juga memperhitungkan huruf surat, sekarang ini adalah pilihannya. Untuk memulai, kami mendefinisikan kondisi untuk pemilihan kriteria jika nama "s.ivanovo", judul, kami memproduksi penjumlahan, kami memerlukan formula untuk menentukan kriteria ini:

\u003d Bertepatan ("s.ivanovo"; c7)

Tetapi sekarang kita dapat mendaftarkan fungsi yang akan memeriksa kisaran untuk keberadaan kriteria yang ditentukan dan setelah menerima nilai "kebenaran" akan menghasilkan penjumlahan. Dalam contoh, saya menentukan secara spesifik sekali dalam kondisi, dan seperti yang kita lihat, rumus berhasil memilih semua pemukiman dan menemukan yang diperlukan dan diperoleh hasil "9". Untuk ini, formula digunakan:

\u003d BDSMM ($ B $ 6: $ G $ 12; D6; $ C $ 2: $ C $ 3)

Yah, saya pikir saya digambarkan oleh saya dan dalam banyak detail, oleh karena itu akan ada beberapa pertanyaan, tetapi banyak manfaat. Jika Anda memiliki pertanyaan, tulis komentar, menunggu husky dan ulasan Anda. Anda dapat membiasakan diri dengan fitur-fitur lain di situs saya.