Bagaimana menjumlahkan jika rumus bekerja. Contoh penggunaan fungsi penjumlahan di excel

Fungsi SUMIF digunakan saat Anda perlu menjumlahkan nilai dalam rentang yang memenuhi kriteria yang ditentukan.

Deskripsi fungsi

Fungsi SUMIF digunakan saat Anda perlu menjumlahkan nilai dalam rentang yang memenuhi kriteria yang ditentukan. Misalnya, dalam kolom angka, Anda hanya perlu menjumlahkan nilai yang lebih besar dari 5. Untuk melakukannya, Anda dapat menggunakan rumus berikut:
\u003d SUMIF (B2: B25, "\u003e 5") Dalam contoh ini, nilai yang dijumlahkan diperiksa terhadap kriteria. Jika perlu, kondisi dapat diterapkan ke satu rentang, dan nilai yang sesuai dari rentang lain dapat dijumlahkan. Misalnya rumusnya:
\u003d SUMIF (B2: B5; "Ivan"; C2: C5) hanya menjumlahkan nilai tersebut dari rentang C2: C5 yang mana nilai yang sesuai dari rentang B2: B5 sama dengan “Ivan”.
Contoh lain dari penggunaan fungsi tersebut ditunjukkan pada gambar di bawah ini:

Contoh penggunaan SUMIF untuk menghitung keuntungan dengan kondisi

Sintaksis

\u003d SUMIF (kisaran, kondisi, [sum_range])

Argumen

kondisi jangkauan jumlah_rentang

Argumen yang diperlukan. Rentang sel yang akan dinilai berdasarkan kriteria. Sel di setiap rentang harus berisi angka, nama, larik, atau referensi angka. Sel kosong dan sel berisi nilai teks dilewati.

Argumen yang diperlukan. Kondisi dalam bentuk angka, ekspresi, referensi sel, teks, atau fungsi yang menentukan sel mana yang akan dijumlahkan. Misalnya, kondisi dapat direpresentasikan dalam bentuk berikut: 32, "\u003e 32", B5, "32", "apel", atau HARI INI ().

Penting! Semua kondisi dan ketentuan teks dengan tanda logika dan matematika harus diapit dengan tanda petik ganda (“). Jika kondisinya berupa angka, Anda tidak perlu menggunakan tanda petik.

Argumen opsional. Sel tempat nilai ditambahkan jika berbeda dari sel yang ditentukan sebagai rentang. Jika sum_range dihilangkan, Microsoft Excel menjumlahkan sel yang ditentukan dalam argumen rentang (sel yang sama dengan kondisi yang diterapkan).

Catatan

  • Anda dapat menggunakan karakter pengganti dalam argumen kondisi: tanda tanya (?) Dan tanda bintang (*). Tanda tanya cocok dengan satu karakter apa pun, dan tanda bintang cocok dengan urutan karakter apa pun. Jika Anda ingin mencari tanda tanya (atau asterisk) secara langsung, Anda harus meletakkan tilde (~) di depannya.
  • Fungsi SUMIF mengembalikan hasil yang salah jika digunakan untuk mencocokkan string yang lebih panjang dari 255 karakter ke string #VALUE!.
  • Argumen sum_range tidak boleh berukuran sama dengan argumen range. Saat menentukan sel aktual yang akan dijumlahkan, sel kiri atas argumen sum_range digunakan sebagai sel awal, lalu sel di bagian rentang yang diukur oleh argumen rentang dijumlahkan.

Mari tambahkan nilai di baris, yang bidangnya memenuhi dua kriteria sekaligus (Kondisi DAN). Pertimbangkan kriteria Teks, kriteria Numerik dan Tanggal. Mari kita menganalisis fungsi SUMIFS ( ), Versi bahasa Inggris SUMIFS ().

Mari kita ambil tabel dengan dua kolom (bidang) sebagai tabel sumber: teks " Buah-buahan"Dan numerik" Kuantitas dalam stok"(Lihat file contoh).

Tugas1 (1 kriteria teks dan 1 numerik)

Mari kita cari jumlah dus barang dengan jumlah tertentu Buah DAN, yang Kotak tersisa dalam stok tidak kurang dari minimum. Misalnya, jumlah kotak dengan barang persik (sel D 2 ), yang memiliki kotak sisa di gudang\u003e \u003d 6 (sel E 2 ) ... Kita harus mendapatkan hasil 64. Penghitungan dapat dilakukan dengan banyak rumus, kita akan memberikan beberapa (lihat file contoh Teks dan Angka Lembar):

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

Sintaks fungsi: SUMIF (sum_interval; kondisi_interval1; kondisi1; kondisi_interval2; kondisi2 ...)

  • B2: B13 Jumlah_Interval - sel untuk penjumlahan, termasuk nama, larik, atau referensi yang berisi angka. Nilai dan teks kosong akan diabaikan.
  • A2: A13dan B2: B13 condition_interval1; kondisi_interval2; ... mewakili dari 1 hingga 127 rentang di mana kondisi terkait diperiksa.
  • D2dan "\u003e \u003d" & E2 Kondisi1; kondisi2; ... dari 1 hingga 127 kondisi dalam bentuk angka, ekspresi, referensi sel, atau teks yang menentukan sel mana yang akan dijumlahkan.


Urutan argumen berbeda dalam fungsi SUMIF () dan SUMIF (). Argumen SUMIFS () sumation_interval adalah argumen pertama, dan dalam SUMIF () yang ketiga. Saat menyalin dan mengedit fungsi serupa ini, pastikan argumen berada dalam urutan yang benar.

2. opsi lain \u003d SUMPRODUCT ((A2: A13 \u003d D2) * (B2: B13); - (B2: B13\u003e \u003d E2))
Mari kita lihat lebih dekat menggunakan fungsi SUMPRODUCT ():

  • Hasil evaluasi A2: A13 \u003d D2 adalah larik (FALSE: FALSE: FALSE: FALSE: TRUE: TRUE: TRUE: FALSE: FALSE: FALSE: FALSE: FALSE) Nilai TRUE sesuai dengan nilai dari kolom SEBUAH kriteria, yaitu kata persik... Array dapat dilihat dengan menyorot di A2: A13 \u003d D2 dan kemudian menekan;
  • Hasil penghitungan B2: B13 adalah (3: 5: 11: 98: 4: 8: 56: 2: 4: 6: 10: 11), yaitu hanya nilai dari kolom B ;
  • Hasil perkalian elemen array (A2: A13 \u003d D2) * (B2: B13) adalah (0: 0: 0: 0: 4: 8: 56: 0: 0: 0: 0: 0). Mengalikan angka dengan hasil SALAH menjadi 0; dan untuk nilai TRUE (\u003d 1) diperoleh nomor itu sendiri;
  • Mari kita analisis 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 yang tersedia»Yang memenuhi kriteria\u003e \u003d E2 (yaitu\u003e \u003d 6) sesuai dengan 1;
  • Selanjutnya, fungsi SUMPRODUCT () mengalikan elemen array secara berpasangan dan menjumlahkan produk yang dihasilkan. Kami mendapatkan - 64.

3. Penggunaan lain dari fungsi SUMPRODUCT () adalah rumusnya \u003d SUMPRODUCT ((A2: A13 \u003d D2) * (B2: B13) * (B2: B13\u003e \u003d E2)).

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

Untuk menampilkan kriteria pemilihan dalam string teks, gunakan rumus \u003d "Volume penjualan untuk periode dari" & TEXT (D6; "dd.MM.yy") & "hingga" & TEXT (E6; "dd.MM.yy")

Digunakan dalam rumus terakhir.

Task4 (Bulan)

Kami sedikit mengubah kondisi masalah sebelumnya: temukan total penjualan untuk bulan tersebut (lihat file contoh Lembar Bulan).

Rumus dibuat mirip dengan tugas 3, tetapi pengguna tidak memasukkan 2 tanggal, tetapi nama bulan (diasumsikan bahwa data dalam tabel dalam 1 tahun).


Untuk menyelesaikan tugas ke-3, tabel dengan autofilter yang dikonfigurasi terlihat seperti ini (lihat file contoh Lembar 2 Tanggal).


Sebelumnya, tabel harus diubah menjadi dan menyertakan baris Total.

Untuk menjumlahkan nilai yang memenuhi kriteria (kondisi) tertentu, gunakan fungsi SUMIF () , Versi bahasa Inggris SUMIF ().

Sintaks fungsi

SUMIF(Jarak; Kondisi; [Sum_range])

Jarak - rentang sel di mana nilai yang sesuai dengan argumen dicari Kondisi... Rentang dapat berisi angka, tanggal, nilai teks, atau referensi ke sel lain. Dalam kasus argumen lainnya adalah Sum_range - dihilangkan, lalu argumennya Jarak juga rentang di mana penjumlahan nilai yang memenuhi argumen dilakukan Kondisi (pada kasus ini Jarak harus mengandung angka).

Kondisi - kriteria dalam bentuk angka, ekspresi atau teks yang menentukan sel mana yang harus ditambahkan. Misalnya argumentasi Kondisi dapat dinyatakan sebagai 32, "apel" atau "\u003e 32".

Sum_range - rentang sel yang berisi angka yang akan dijumlahkan, jika sel terkait ditentukan dalam argumen Jarakcocok dengan argumennya Kondisi. Sum_range - argumen opsional. Jika dihilangkan, maka penjumlahan akan dilakukan pada rentang sel yang ditentukan dalam argumen Jarak.

Contoh dari

Pertimbangkan kasus dimana argumennya Sum_range dihilangkan. Dalam kasus ini, penjumlahan akan dilakukan pada rentang sel yang ditentukan dalam argumen pertama Jarak (yaitu harus berisi angka). Ini juga akan mencari nilai yang sesuai dengan argumen Kondisi, yang kemudian akan diringkas. Biarkan itu menjadi rentang B5: B15 , lihat file contoh.

Mari selesaikan tugas:

  • temukan jumlah semua angka yang lebih besar dari atau sama dengan 10. Jawaban: 175. Rumus: \u003d SUMIF (B5: B15, "\u003e \u003d 10")
  • cari jumlah semua bilangan kurang dari atau sama dengan 10. Jawaban: 42. Rumus: \u003d SUMIF (B5: B15, "<=10")
  • temukan jumlah semua bilangan positif dalam suatu rentang. Rumus: \u003d SUMIF (B5: B15, "\u003e 0")... Versi alternatif menggunakan fungsi SUMPRODUCT () terlihat seperti ini: \u003d SUMPRODUCT ((B5: B15) * (B5: B15\u003e 0))

Formulir tugas kondisi(kriteria) cukup fleksibel. Misalnya di rumus \u003d SUMIF (B5: B15; D7 & D8) kriteria<=56 задан через ссылку D7&D8 : в D7 berisi nilai teks<=, а в D8 - nomor 56 (lihat gambar di bawah). Pengguna, misalnya, bisa dengan mudah mengubah kriteria menggunakan dalam sel D7 ... Rumus yang setara \u003d SUMIF (B5: B15, "<=56") или \u003d SUMIF (B5: B15, "<="&56) atau \u003d SUMIF (B5: B15, "<="&D8) atau \u003d SUMIF (B5: B15; D7 & 56).

Selamat siang, para pelanggan dan pengunjung situs blog yang budiman. Baru-baru ini, kami menemukan rumus VLOOKUP, dan segera setelah itu saya memutuskan untuk menulis tentang fungsi Excel lain yang sangat berguna - SUMIF... Kedua fungsi ini dapat "menghubungkan" data dari berbagai sumber (tabel) dengan kolom kunci dan dalam beberapa kondisi dapat dipertukarkan. Pada saat yang sama, ada perbedaan signifikan dalam tujuan dan penggunaannya.

Jika tujuan VLOOKUP hanyalah untuk "menarik" data dari satu tempat di Excel ke tempat lain, maka SUMIF digunakan untuk meringkas data numerik sesuai dengan kriteria yang diberikan. Tujuan utama dari rumus ini adalah untuk meringkas data sesuai dengan beberapa kondisi. Sesuatu seperti ini tertulis dalam bantuan. Namun, alat ini akan berfungsi untuk tujuan lain juga. Misalnya, saya sering menggunakan rumus SUMIF daripada VLOOKUP, karena tidak menghasilkan kesalahan # N / A ketika tidak dapat menemukan sesuatu. Dan ini sangat memudahkan dan mempercepat pekerjaan.

Fungsi SUMIF dapat berhasil diadaptasi untuk menyelesaikan berbagai macam masalah. Oleh karena itu, dalam artikel ini, kami tidak akan membahas 1 (satu), tetapi 2 (dua) contoh. Yang pertama akan dikaitkan dengan penjumlahan sesuai dengan kriteria yang diberikan, yang kedua - dengan data "menarik", yaitu, sebagai alternatif untuk VLOOKUP.

Contoh Penjumlahan Menggunakan Fungsi SUMIF

Tabel berisi posisi, jumlahnya, serta milik suatu kelompok barang tertentu (kolom pertama). Pertimbangkan untuk saat ini penggunaan SUMIF yang disederhanakan, ketika kita perlu menghitung jumlah hanya untuk posisi itu, nilai yang sesuai dengan kondisi tertentu. Misalnya, kami ingin mengetahui berapa banyak posisi teratas yang terjual, mis. yang nilainya melebihi 70 unit. Sangat tidak nyaman dan cepat untuk mencari barang seperti itu dengan mata Anda, dan kemudian menambahkannya secara manual, jadi fungsi SUMIF sangat tepat di sini.

Pertama-tama, pilih sel tempat jumlahnya akan dihitung. Selanjutnya, kami memanggil Wizard Fungsi. Ini adalah ikonnya fx di bilah rumus. Selanjutnya, cari fungsi SUMIF dalam daftar dan klik di atasnya. Kotak dialog akan terbuka, di mana, untuk mengatasi masalah khusus ini, Anda hanya perlu mengisi dua bidang (pertama) dari tiga bidang yang diusulkan.


Itulah mengapa saya menyebut contoh ini disederhanakan. Mengapa 2 (dua) dari 3 (tiga)? Karena kriteria kita berada dalam kisaran penjumlahan.

Bidang "Rentang" menunjukkan area tabel Excel tempat semua nilai asli berada, dari mana Anda perlu memilih sesuatu dan kemudian menambahkannya. Biasanya diatur dengan mouse.

Bidang "Kriteria" menunjukkan kondisi yang akan dipilih rumus. Dalam kasus kami, kami menunjukkan "\u003e 70". Jika Anda tidak mencantumkan tanda kutip, maka tanda kutip akan selesai dengan sendirinya. Namun secara umum, kriteria seperti itu harus diberi tanda petik.

Kami tidak mengisi kolom terakhir "Sum_range", karena sudah ditunjukkan di kolom pertama.

Jadi, fungsi SUMIF mengambil kriteria dan mulai memilih semua nilai dari rentang tertentu yang memenuhi kriteria yang ditentukan. Setelah itu, semua nilai yang dipilih ditambahkan. Beginilah cara kerja algoritma fungsi.

Setelah mengisi kolom wajib di Function Wizard, tekan tombol besar "Enter" di keyboard, atau klik "OK" di jendela Wizard. Setelah ini, nilai yang dihitung akan muncul di tempat fungsi input. Dalam contoh saya, ternyata 224 buah. Artinya, total nilai barang yang terjual lebih dari 70 buah adalah 224 buah. (Ini dapat dilihat di sudut kiri bawah jendela Wizard bahkan sebelum mengklik "ok"). Itu saja. Ini adalah contoh yang disederhanakan di mana kriteria dan rentang penjumlahan berada di tempat yang sama.

Sekarang mari kita lihat contoh di mana kriteria tidak cocok dengan rentang penjumlahan. Situasi ini jauh lebih umum. Pertimbangkan data bersyarat yang sama. Misalkan kita perlu mengetahui jumlah tidak lebih atau kurang dari suatu nilai, tetapi jumlah suatu kelompok barang tertentu, misalnya kelompok G.

Untuk melakukan ini, pilih kembali sel dengan perhitungan jumlah di masa mendatang dan panggil Wizard Fungsi. Sekarang, di jendela pertama, kami menunjukkan rentang yang berisi kriteria yang akan digunakan untuk menjumlahkan pemilihan, yaitu, dalam kasus kami, ini adalah kolom dengan nama grup produk. Selanjutnya, kriteria itu sendiri ditentukan secara manual, membiarkan entri "grup D" di bidang yang sesuai, atau cukup tunjukkan dengan mouse sel dengan kriteria yang diperlukan. Kotak terakhir adalah kisaran tempat data yang diringkas berada. Kami menandainya dengan mouse, namun pecinta printing tidak dilarang mengetik secara manual.


Hasilnya adalah jumlah barang yang terjual dari kelompok D - 153 buah.

Jadi, kami melihat cara menghitung satu jumlah untuk satu kriteria tertentu. Namun, masalah lebih sering muncul ketika diharuskan menghitung beberapa jumlah untuk beberapa kriteria. Tidak bisa lebih mudah! Misalnya, kita perlu mengetahui jumlah produk yang terjual untuk setiap kelompok. Artinya, saya tertarik dengan 4 (empat) nilai untuk 4 (empat) kelompok (A, B, C dan D). Untuk ini, daftar grup biasanya dibuat di suatu tempat di ruang terbuka Excel dalam bentuk tabel terpisah. Jelas bahwa nama grup harus sama persis dengan nama grup di tabel sumber. Mari segera tambahkan baris terakhir, di mana jumlahnya masih nol.

Kemudian rumus untuk kelompok pertama ditentukan dan diperluas ke semua yang lain. Di sini Anda hanya perlu memperhatikan relativitas tautan. Rentang dengan kriteria dan rentang penjumlahan harus berupa tautan absolut, sehingga ketika Anda menyeret rumus, rumus tidak "turun", tetapi kriteria itu sendiri, pertama, Anda perlu menentukan dengan mouse (dan tidak menulis secara manual) , dan kedua, harus berupa tautan relatif, jadi bagaimana setiap jumlah memiliki kriteria penjumlahannya sendiri.

Kolom Function Wizard yang terisi dengan perhitungan seperti itu akan terlihat seperti ini.


Seperti yang Anda lihat, untuk kelompok pertama A, jumlah barang yang terjual adalah 161 buah (pojok kiri bawah gambar). Sekarang tekan enter dan seret rumus ke bawah.

Semua jumlah dihitung, dan jumlah keseluruhannya adalah 535, yang sesuai dengan total data asli. Artinya semua nilai sudah dijumlahkan, tidak ada yang terlewat. Kasus penggunaan terakhir untuk SUMIF secara pribadi cukup sering terjadi pada saya.

Contoh penggunaan fungsi SUMIF untuk membandingkan data

Seperti yang telah saya catat lebih dari sekali, fungsi SUMIF dapat digunakan dengan bijaksana untuk mengikat data. Memang, jika Anda menambahkan satu nilai, Anda mendapatkan nilai itu sendiri. Properti aritmatika ini bukanlah dosa untuk digunakan demi kebaikan. Bahkan mungkin saja para perancang Excel tidak menyadari penggunaan biadab dari gagasan mereka.

Singkatnya, SUMIF mudah diadaptasi untuk pengikatan data sebagai alternatif VLOOKUP. Mengapa menggunakan SUMIF jika ada VLOOKUP? Biar saya jelaskan. Pertama, SUMIF, tidak seperti VLOOKUP, tidak sensitif terhadap format data dan tidak menghasilkan kesalahan di tempat yang tidak Anda duga; kedua, SUMIF sebagai ganti kesalahan karena kurangnya nilai menurut kriteria yang diberikan memberi 0 (nol), yang memungkinkan Anda menghitung total rentang dengan rumus SUMIF tanpa gerakan yang tidak perlu. Namun, ada satu kekurangannya. Jika ada kriteria yang diulang dalam tabel yang diinginkan, maka nilai yang sesuai akan dijumlahkan, yang tidak selalu merupakan "pull-up". Lebih baik berjaga-jaga. Di sisi lain, ini sering diperlukan - untuk menarik nilai ke tempat tertentu, dan menambahkan posisi duplikat. Anda hanya perlu mengetahui properti dari fungsi SUMIF dan menggunakannya sesuai dengan instruksi manual.

Sekarang mari kita lihat contoh bagaimana fungsi SUMIF ternyata lebih cocok untuk menarik data daripada VLOOKUP. Biarkan data dari contoh Anda menjadi penjualan beberapa produk di bulan Januari. Kami ingin tahu bagaimana mereka berubah di bulan Februari. Mudah untuk membuat perbandingan di piring yang sama, setelah sebelumnya menambahkan kolom lain di sebelah kanan dan mengisinya dengan data untuk bulan Februari. Di suatu tempat di file Excel lain terdapat statistik untuk bulan Februari untuk keseluruhan bermacam-macam, tetapi kami ingin menganalisis posisi-posisi khusus ini, di mana kami perlu menarik nilai yang diperlukan dari file besar dengan statistik penjualan untuk semua produk ke dalam tabel kami. Pertama, mari coba gunakan rumus VLOOKUP. Kami akan menggunakan kode produk sebagai kriteria. Hasilnya ada pada gambar.


Terlihat jelas bahwa satu posisi belum dikencangkan, dan alih-alih nilai numerik, kesalahan # N / A ditampilkan. Kemungkinan besar, produk ini tidak dijual di bulan Februari dan oleh karena itu tidak tersedia di database bulan Februari. Akibatnya, kesalahan # N / A ditampilkan secara total. Jika tidak banyak posisi, maka masalahnya tidak besar, cukup menghapus kesalahan secara manual dan jumlahnya akan dihitung ulang dengan benar. Namun, jumlah garis dapat diukur dalam ratusan, dan mengandalkan penyesuaian manual bukanlah keputusan yang tepat. Sekarang mari gunakan rumus SUMIF sebagai ganti VLOOKUP.


Hasilnya sama, hanya saja kesalahan SUMIF # N / A yang diberikan nol, yang memungkinkan Anda menghitung jumlah secara normal (atau indikator lain, misalnya, rata-rata) di baris terakhir. Ini adalah ide utama mengapa SUMIF terkadang harus digunakan sebagai pengganti VLOOKUP. Dengan banyak posisi, efeknya akan semakin terlihat.

Pada kenyataannya, seringkali diperlukan untuk bekerja dengan data dalam jumlah besar yang berkali-kali lebih besar dari ukuran monitor. Sangat sulit untuk menemukan dan melacak kesalahan. Tapi semua tiang tembok muncul dalam perhitungan selanjutnya. Contoh-contoh seperti itu tidak nyaman untuk dibongkar menggunakan tangkapan layar dalam artikel, jadi saya mengundang Anda untuk menonton tutorial video yang direkam khusus tentang fungsi SUMIF.

Berikut pelajaran lain tentang SUMIF



Kami merekomendasikan membaca