Semua sama dengan jumlah tertentu. Contoh penggunaan fungsi diam di Excel

Untuk meringkas nilai-nilai yang memenuhi kriteria (kondisi) yang ditentukan, fungsi digunakan; , Versi bahasa Inggris SUMIF ().

Fungsi sintaksis

Smerei.(Jarak; Kondisi; [Range_suming])

Jarak - Rentang sel di mana nilai-nilai argumen yang sesuai dicari Kondisi. Rentang mungkin berisi angka, tanggal, nilai teks, atau tautan ke sel lain. Dalam hal argumen lain - Range_suming. - dihilangkan, lalu argumen Jarak Juga merupakan jangkauan yang menjadi nilai argumen yang memuaskan. Kondisi (pada kasus ini Jarak Harus mengandung angka).

Kondisi - Kriteria dalam bentuk angka, ekspresi atau teks yang menentukan sel mana yang harus disimpulkan. Misalnya, argumen Kondisi Ini dapat dinyatakan sebagai 32, "Apel" atau "\u003e 32".

Range_suming. - Rentang sel yang mengandung angka-angka yang akan diringkas jika sel-sel yang ditentukan dalam argumen Jaraksesuai dengan argumen Kondisi. Range_suming. - Argumen opsional. Jika dihilangkan, penjumlahan akan dibuat dalam kisaran sel yang ditunjukkan dalam argumen Jarak.

Contohnya

Pertimbangkan kasus saat argumen Range_suming. dihilangkan. Dalam hal ini, penjumlahan akan dibuat dalam kisaran sel yang ditunjukkan dalam argumen pertama Jarak (mis., itu harus mengandung angka). Ini juga akan mencari nilai dari argumen yang sesuai Kondisiyang kemudian akan disimpulkan. Biarkan itu menjadi rentang B5: B15. Lihat file contoh.

Kami memecahkan tugas:

  • temukan jumlah semua angka lebih dari atau sama 10. Balas: 175. Formula: \u003d SMEREY (B5: B15; "\u003e \u003d 10")
  • temukan jumlah semua angka kurang dari atau sama dengan 10. Jawaban: 42. Formula: \u003d SILENT (B5: B15; "<=10")
  • temukan jumlah semua angka positif dalam kisaran. Rumus: \u003d SMERELY (B5: B15; "\u003e 0"). Alternatif menggunakan fitur Sumpacy () terlihat seperti ini: \u003d SUMPERED (B5: B15) * (B5: B15\u003e 0))

Bentuk tugas istilahnya(Kriteria) cukup fleksibel. Misalnya, dalam rumus \u003d SMERELY (B5: B15; D7 & D8) kriteria.<=56 задан через ссылку D7&D8 : в D7. Berisi nilai teks<=, а в D8. - Nomor 56 (lihat Gambar di bawah). Pengguna, misalnya, dapat dengan mudah mengubah kriteria menggunakan dalam sel D7. . Formula yang setara \u003d SILENT (B5: B15; "<=56") или \u003d SILENT (B5: B15; "<="&56) atau \u003d SILENT (B5: B15; "<="&D8) atau \u003d SMERELY (B5: B15; D7 & 56).

Fungsi ini digunakan untuk digunakan jika perlu untuk meringkas nilai rentang yang sesuai dengan kriteria yang ditentukan.

Deskripsi fungsi

Fungsi ini digunakan untuk digunakan jika perlu untuk meringkas nilai rentang yang sesuai dengan kriteria yang ditentukan. Misalnya, dalam kolom dengan angka yang diperlukan untuk meringkas hanya nilai yang melebihi angka 5. Untuk ini, Anda dapat menggunakan rumus yang ditentukan di bawah ini:
\u003d DILAKUKAN (B2: B25, "\u003e 5") Dalam contoh ini, nilai yang dapat dirancang diperiksa untuk kepatuhan dengan kriteria. Jika perlu, kondisinya dapat diterapkan pada satu rentang, dan merangkum nilai yang sesuai dari rentang lain. Misalnya, Formula:
\u003d B2: B5; "Ivan"; C2: C5) Hanya nilai-nilai dari kisaran C2: C5 yang dirangkum di mana nilai yang sesuai dari kisaran B2: B5 adalah "Ivan".
Contoh lain dari penggunaan fungsi ditunjukkan pada gambar di bawah ini:

Contoh penggunaan diam untuk menghitung laba dengan kondisi

Sintaksis

\u003d Diam (rentang; kondisi; [range_suming])

Argumen.

kondisi rentang range_suming.

Argumen wajib. Rentang sel diukur dengan kriteria. Sel dalam setiap kisaran harus mengandung angka, nama, array atau referensi ke angka. Sel-sel kosong dan sel yang mengandung nilai teks dilewati.

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

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

Argumen opsional. Sel, nilai yang dijumlahkan, jika berbeda dari sel yang ditentukan sebagai kisaran. Jika argumen range_sum dihilangkan, Microsoft Excel. Sel-sel yang ditentukan dalam rentang argumen (sel yang sama dengannya kondisi diterapkan) dirangkum.

Catatan

  • Dalam argumen, kondisinya dapat digunakan 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 harus meletakkan tanda "Tilda" (~).
  • Fungsi diam mengembalikan hasil yang salah jika digunakan untuk mencocokkan string lebih dari 255 karakter dengan string # rata-rata!.
  • Argumen range_sum mungkin tidak bertepatan dalam ukuran dengan rentang argumen. Saat menentukan sel-sel yang sebenarnya akan dijumlahkan, sel kiri atas rentang argumen digunakan sebagai inisial, dan kemudian sel-sel kisaran rentang yang sesuai dengan rentang argumen disimpulkan.

Kami akan membuat penambahan nilai-nilai dari mereka yang berada di lini yang bidangnya memenuhi dua kriteria sekaligus (kondisi dan). Pertimbangkan kriteria teks, angka dan kriteria dalam format tanggal. Kami akan menganalisis fungsi Summalimn ( ), SUMIFS versi bahasa Inggris ().

Sebagai tabel sumber, ambil meja dengan dua kolom (bidang): teks " Buah"Dan Numerik" Kuantitas dalam stok"(Lihat file contoh).

Task1 (1 kriteria teks dan 1 numerik)

Temukan jumlah kotak barang dengan pasti Buah DAN, yang mana Saldo kotak di gudang tidak kurang minim. Misalnya, jumlah kotak dengan komoditas persik (sel D. 2 ), yang memiliki sisa kotak dalam stok\u003e \u003d 6 (sel E. 2 ) . Kita harus mendapatkan hasil 64. Perhitungan dapat diwujudkan oleh serangkaian formula, kami memberi beberapa (lihat file contoh Teks dan nomor daun):

1. =SMEREVILIMN (B2: B13; A2: A13; D2; B2: B13; "\u003e \u003d" & E2)

Fitur sintaks: Smerevulimn (interval_suming; interval_liviya1; syarat1; interval_loculation2; condition2 ...)

  • B2: B13 Interval_Suming - Sel untuk penjumlahan termasuk nama, array atau referensi yang berisi angka. Nilai dan teks kosong diabaikan.
  • A2: A13.dan B2: B13 interval_lovia1; interval_loculia2; ... mewakili dari 1 hingga 127 dari rentang di mana kondisi yang sesuai diperiksa.
  • D2.dan "\u003e \u003d" & E2 Condition1; Kondisi2; ... Ini dari 1 hingga 127 kondisi dalam bentuk angka, ekspresi, tautan ke sel atau teks yang mendefinisikan sel mana yang akan disimpulkan.


Urutan argumen dituangkan dalam fungsi Summbrann () dan mereka diam (). Dalam argumen Summelimn () interval_Suming. Ini adalah argumen pertama, dan dalam diam () - yang ketiga. Saat menyalin dan mengedit fungsi serupa ini, perlu untuk memastikan bahwa argumen ditentukan dalam urutan yang benar.

2. Pilihan lain \u003d Summipat ((A2: A13 \u003d D2) * (B2: B13); - (B2: B13\u003e \u003d E2))
Kami akan menjelaskan secara lebih rinci penggunaan fungsi Sumpacy ():

  • Hasil perhitungan A2: A13 \u003d D2 adalah array (FALSE: LIES: LIES: Kebenaran: Kebenaran: Salah: Salah: Salah: FALSE: LIE) Nilai kebenaran sesuai dengan kebetulan nilai. kolom TAPI Kriteria, I.E. Kata persik. Array dapat dilihat dengan menyoroti dalam A2: A13 \u003d D2, dan kemudian menekan;
  • Hasil dari Calculating B2: B13 adalah (3: 5: 11: 4: 4: 8: 5: 2: 4: 6: 10: 11), I.E. hanya nilai dari kolom Dgn B. ;
  • Hasil perkalian elemen array (A2: A13 \u003d D2) * (B2: B13) adalah (0: 0: 0: 0: 0: 8: 56: 0: 0: 0: 0: 0). Saat mengalikan jumlah ke nilai nilai diperoleh 0; dan nilai kebenaran (\u003d 1) jumlahnya diperoleh;
  • Kami akan menganalisis kondisi kedua: hasil perhitungan - (B2: B13\u003e \u003d E2) adalah array (0: 0: 1: 1: 0: 1: 1: 0: 0: 1: 1: 1). Nilai di kolom " Jumlah kotak dalam stok", Yang memenuhi kriteria\u003e \u003d E2 (I.E.\u003e \u003d 6) sesuai 1;
  • Selanjutnya, fungsi pasangan Sumpacy () variabel elemen-elemen array dan merangkum produk yang diperoleh. Kami mendapatkan - 64.

3. Perwujudan lain dari fungsi Sumpacy () adalah rumus \u003d SUMPERED ((A2: A13 \u003d D2) * (B2: B13) * (B2: B13\u003e \u003d E2)).

Jika perlu, tanggal dapat dimasukkan langsung dalam rumus \u003d SMEREVEYLMN (B6: B17; A6: A17; "\u003e \u003d 01/15/2010"; A6: A17; "<=06.03.2010")

Untuk menampilkan kondisi pemilihan dalam string teks, gunakan rumus \u003d "Penjualan untuk periode dari" & teks (D6; "dd.mm.yg") & "dengan" & teks (E6; "dd.mm.yg")

Dalam formula terakhir digunakan.

Task4 (Bulan)

Sedikit memodifikasi kondisi tugas sebelumnya: kami akan menemukan total penjualan per bulan (lihat contoh file Bulan Daun).

Formula dibangun dengan cara yang sama dengan tugas 3, tetapi pengguna memperkenalkan bukan 2 tanggal, tetapi nama bulan ini (diasumsikan bahwa tabel dalam 1 tahun).


Untuk mengatasi tugas ke-3, sebuah tabel dengan autofilter yang dikonfigurasi terlihat seperti ini (lihat contoh file Daun 2 tanggal).


Sebelumnya, tabel harus dikonversi ke dan mengaktifkan string hasil.

Selamat sore, situs web pelanggan dan pengunjung blog yang terhormat. Baru-baru ini, kami menemukan rumus perusahaan, dan saya memutuskan untuk menulis tentang fungsi lain yang sangat berguna di Excel tentang fungsi lain yang sangat berguna. Smerei.. Kedua fungsi ini dapat "mengikat" data dari berbagai sumber (tabel) pada bidang utama dan dalam kondisi tertentu dapat dipertukarkan. Pada saat yang sama, ada perbedaan yang signifikan dalam janji temu dan penggunaannya.

Jika penugasan aplikasi hanya "menarik" data dari satu tempat Excel ke yang lain, maka mereka digunakan untuk menyimpulkan data numerik untuk meringkas sesuai dengan kriteria tertentu. Tujuan utama dari formula ini adalah penjumlahan data sesuai dengan beberapa kondisi. Kira-kira ditulis dengan bantuan. Namun, alat ini akan menjadi kenyataan untuk keperluan lain. Misalnya, saya sering menggunakan formula yang sering saya gunakan alih-alih HDP, karena tidak memberikan kesalahan # n / d, ketika sesuatu tidak dapat ditemukan. Dan itu membuatnya hebat dan mempercepat pekerjaan.

Fungsi harus berhasil disesuaikan untuk memecahkan berbagai tugas. Oleh karena itu, kami akan mempertimbangkan tidak 1 (satu) dalam artikel ini, tetapi sebanyak 2 (dua) contoh. Yang pertama akan dikaitkan dengan penjumlahan kriteria yang ditentukan, yang kedua - dengan "menarik" data, yaitu sebagai alternatif untuk perusahaan.

Contoh penjumlahan menggunakan fungsi diam

Tabel menunjukkan posisi, jumlahnya, serta milik kelompok barang tertentu (kolom pertama). Pertimbangkan penggunaan penggunaan yang disederhanakan, ketika kita perlu menghitung jumlah hanya di posisi, nilai-nilai yang sesuai dengan kondisi tertentu. Misalnya, kami ingin tahu berapa teratas yang dijual, I.E. Mereka yang nilainya melebihi 70 unit. Cari barang-barang tersebut dengan mata, dan kemudian rangkum secara manual tidak terlalu nyaman dan cepat, sehingga fungsinya sangat tepat di sini.

Pertama-tama, kami mengalokasikan sel di mana jumlahnya akan dihitung. Selanjutnya, hubungi wizard fungsi. Ini adalah ikon fx. di baris rumus. Selanjutnya, kami mencari fungsi dalam daftar, mereka diam dan mengkliknya. Kotak dialog terbuka, di mana untuk menyelesaikan tugas ini, Anda harus mengisi hanya dua (pertama) bidang dari tiga yang diusulkan.


Karena itu, saya menyebut contoh ini disederhanakan. Mengapa 2 (dua) dari 3 (tiga)? Karena kriteria kami terletak di kisaran penjumlahan.

Di bidang "rentang", area tabel Excel diindikasikan, di mana semua nilai sumber terletak dari mana Anda perlu memilih sesuatu dan kemudian lipat. Biasanya diatur menggunakan mouse.

Dalam bidang "Kriteria", kondisi di mana rumus akan dipilih. Dalam kasus kami, kami menentukan "\u003e 70". Jika Anda tidak memasukkan tanda kutip, maka mereka ditempelkan sendiri. Tetapi secara umum, kriteria seperti itu harus dalam kutipan.

Bidang terakhir "dapad_suming" tidak diisi, karena sudah ditentukan di bidang pertama.

Dengan demikian, fungsi diam mengambil kriteria dan mulai memilih semua nilai dari rentang yang ditentukan yang memenuhi kriteria yang ditentukan. Setelah itu, semua nilai yang dipilih lipat. Begitulah cara kerja algoritma fungsi.

Isi fungsi di Function Wizard, tekan tombol "Enter" besar pada keyboard, atau di jendela Wizard, kami berikan mouse ke OK. Untuk Simcting, nilai yang dihitung akan muncul di situs fungsi yang dimasukkan. Dalam contoh saya ternyata 224 pcs. Artinya, nilai total barang yang dijual dalam jumlah lebih dari 70 buah adalah 224 pc. (Ini terlihat di sudut kiri bawah jendela wizard bahkan sebelum menekan "OK"). Itu saja. Itu adalah contoh yang disederhanakan ketika kriteria dan kisaran penjumlahan berada di satu tempat.

Sekarang mari kita lihat contoh ketika kriteria tidak bertepatan dengan kisaran penjumlahan. Situasi ini terjadi jauh lebih sering. Pertimbangkan data bersyarat yang sama. Mari kita perlu mengetahui jumlahnya tidak lebih atau kurang dari nilai apa pun, tetapi jumlah kelompok barang tertentu, akui, grup

Untuk melakukan ini, kembali lepaskan sel dengan jumlah di masa depan dari jumlah dan hubungi panduan fungsi. Sekarang di jendela pertama, kami menentukan kisaran di mana kriteria terkandung oleh pemilihan untuk penjumlahan, yaitu, dalam kasus kami, ini adalah kolom dengan nama-nama grup produk. Selanjutnya, kriteria itu sendiri diresepkan secara manual, meninggalkan rekaman "Grup G" di bidang yang sesuai, atau hanya menunjukkan sel mouse dengan kriteria yang diinginkan. Jendela terakhir adalah kisaran di mana data yang dapat dirumuskan berada. Saya tentukan dengan mouse, tetapi pencetakan tidak diperbolehkan untuk mencetak gol secara manual.


Hasilnya akan menjadi jumlah barang yang dijual dari Grup G - 153PC.

Jadi, kami melihat cara menghitung satu jumlah pada satu kriteria tertentu. Namun, tugasnya lebih sering terjadi ketika diharuskan untuk menghitung beberapa jumlah untuk beberapa kriteria. Tidak ada yang lebih mudah! Misalnya, kita perlu mengetahui jumlah barang yang dijual untuk setiap kelompok. Yang Anda maksud 4 (empat) nilai dalam kelompok 4 (empat) (a, b, b dan d). Untuk ini, biasanya di suatu tempat pada ekspansi Excel dibuat daftar grup dalam bentuk piring terpisah. Jelas bahwa nama-nama kelompok harus persis sesuai dengan nama-nama kelompok di tabel sumber. Segera tambahkan garis final, di mana jumlahnya masih nol.

Formula kemudian diresepkan untuk grup pertama dan membentang kepada orang lain. Di sini Anda hanya perlu memperhatikan relativitas tautan. Kisaran dengan kriteria dan rentang penjumlahan harus referensi mutlak sehingga ketika meregangkan formula, mereka tidak "turun", tetapi kriteria itu sendiri, pertama-tama Anda perlu menentukan mouse (dan untuk tidak mendaftar secara manual), kedua, Harus ada referensi relatif, sehingga setiap jumlah memiliki kriteria penjumlahan sendiri.

Kolom wizard medan yang diisi dengan perhitungan serupa akan terlihat seperti ini.


Seperti yang dapat dilihat, untuk kelompok pertama, dan jumlah barang yang dijual sebesar 161 pcs (sudut kiri bawah pola). Sekarang klik masukkan dan regangkan formula ke bawah.

Semua jumlah dihitung, dan total hasilnya adalah 535, yang bertepatan dengan hasil dalam data sumber. Jadi, semua nilai diringkas, mereka tidak melewatkan apa pun. Pilihan penggunaan terakhir adalah diam, saya cukup sering bertemu.

Contoh menggunakan fungsi diam untuk membandingkan data

Karena saya tidak lagi mencatat, fungsinya diam, Anda dapat menggunakan ligamen data. Memang, jika Anda meringkas satu nilai, maka nilai ini akan terjadi. Properti aritmatika ini bukan dosa untuk digunakan untuk keuntungan. Bahkan mungkin desainer Excel tidak tahu tentang penggunaan gagasan mereka yang biadab.

Singkatnya, mereka akan dengan mudah disesuaikan dengan banyak data sebagai alternatif dari fungsi FRP. Mengapa menggunakan berat badan, jika ada ARP? Aku jelaskan. Pertama, mereka diam, tidak seperti EPP, tidak sensitif terhadap format data dan tidak mengeluarkan kesalahan di mana setidaknya menunggu; Kedua, mereka diam alih-alih kesalahan karena kurangnya nilai pada masalah kriteria tertentu 0 (nol), yang memungkinkan tanpa gerakan yang tidak perlu untuk menghitung hasil rumus dengan rumus. Namun, ada satu minus. Jika di tabel yang diinginkan, kriteria apa pun akan diulang, maka nilai yang sesuai disimpulkan, yang tidak selalu memiliki "pull-up". Lebih baik diperjuangkan. Di sisi lain, sering kali perlu untuk menarik nilai ke lokasi yang ditentukan, sedangkan posisi yang dikonversi dilipat. Anda hanya perlu mengetahui sifat-sifat fungsi diam dan digunakan sesuai dengan instruksi manual.

Sekarang pertimbangkan contoh bagaimana fungsi diam, ternyata lebih cocok untuk mengencangkan data daripada UPR. Biarkan data dari contoh Anda menjual beberapa barang pada bulan Januari. Kami ingin tahu bagaimana mereka berubah pada bulan Februari. Perbandingannya nyaman untuk diproduksi di tablet yang sama, setelah menambahkan kolom lain ke kanan dan mengisi datanya untuk Februari. Di suatu tempat di file exhelev lain ada statistik untuk Februari di seluruh kisaran, tetapi kami ingin menganalisis posisi ini, yang diperlukan dari file besar dengan statistik penjualan semua barang mengencangkan nilai yang diinginkan ke tanda kami. Untuk mulai dengan, mari kita coba gunakan rumus UPR. Sebagai kriteria, kami akan menggunakan kode produk. Hasilnya ada dalam gambar.


Dilihat dengan jelas bahwa satu posisi tidak mengejar ketinggalan, dan kesalahan # n / d dikeluarkan bukannya nilai numerik. Kemungkinan besar, pada bulan Februari, produk ini tidak dijual dan karenanya tidak ada dalam database untuk Februari. Akibatnya, kesalahan # n / d ditunjukkan dalam jumlah. Jika tidak ada banyak posisi, maka masalahnya tidak besar, cukup untuk menghapus kesalahan secara manual dan jumlahnya akan dihitung ulang dengan benar. Namun, jumlah baris dapat diukur dengan ratusan, dan mengandalkan penyesuaian manual bukanlah solusi yang benar. Sekarang kita akan menggunakan rumus itu bukan HDP.


Hasilnya sama, hanya alih-alih kesalahan # n / d, memberikan nol, yang memungkinkan Anda untuk menghitung jumlah (atau indikator lain, misalnya, rata-rata) di garis final. Ini adalah ide utama mengapa mereka kadang-kadang harus menggunakan alih-alih HDP. Dengan sejumlah besar posisi, efeknya akan lebih nyata.

Pada kenyataannya, sering kali perlu untuk bekerja dengan array data besar, yang berulang kali melebihi dimensi monitor. Untuk melihat dan melacak kesalahan sangat sulit. Tetapi semua beting mengapung dalam perhitungan selanjutnya. Contoh-contoh seperti itu tidak nyaman untuk membongkar menggunakan tangkapan layar di artikel, jadi saya mengundang Anda untuk melihat bahasa video yang direkam secara khusus tentang fungsinya.

Dan ini pelajaran lain tentang diam



Kami merekomendasikan untuk membaca