Excel cara meringkas kriteria tertentu. Keuangan di Excel

Suatu hari keadaan kehidupan memaksa Anda untuk menguasai alat perangkat lunak baru untuk menyelesaikan tugas sehari-hari atau kantor. Waktu yang lama dan tidak nyaman untuk melakukannya secara manual, dan kemungkinan membuat kesalahan meningkat. Oleh karena itu, mari kita lihat cara kerja SUMIF di Excel dan menunjukkan contoh penggunaan.

Fungsi sintaks dan prinsip kerja

Kemungkinan penambahan diketahui setiap pengguna yang telah memulai Excel setidaknya sekali. SUMIF adalah kelanjutan logis dari SUM dasar, perbedaannya adalah penjumlahan berdasarkan kondisi.

Sintaks untuk SUMIF adalah sebagai berikut:

\u003d SUMIF (rentang, kriteria, [rentang penjumlahan])dimana:

range - daftar nilai yang akan diterapkan pembatasan;
kriteria - kondisi tertentu;
rentang penjumlahan - daftar nilai yang akan dijumlahkan.

Jadi, kami memiliki fungsi dengan tiga komponen. Perlu dicatat bahwa argumen terakhir dapat dihilangkan - SUMIF dapat bekerja tanpa rentang penjumlahan.

Contoh penggunaan

Mari pertimbangkan semua kemungkinan situasi menggunakan fungsi dengan nama yang sama.

Bentuk umum

Mari kita lihat contoh sederhana untuk mengilustrasikan manfaat menggunakan SUMIF.

Ada tabel yang mencantumkan nomor seri, jenis produk, nama, harga satuan, dan jumlah stok.

Katakanlah kita perlu mencari tahu berapa banyak item yang tersedia. Semuanya sederhana di sini - kami menggunakan SUM dan menentukan interval yang diinginkan. Tetapi bagaimana jika Anda tertarik dengan jumlah item pakaian? Di sinilah SUMIF datang untuk menyelamatkan. Fungsinya akan terlihat seperti ini:

\u003d SUMIF (C3: C12; "pakaian"; F3: F12)dimana:

C3: C12 - jenis pakaian;
"Pakaian" adalah kriteria;
F3: F12 - interval penjumlahan.

Hasilnya, kami mendapat 180. Fungsi tersebut bekerja melalui semua kondisi yang kami tentukan dan menghasilkan hasil yang benar. Anda juga dapat mengulangi manipulasi serupa untuk jenis barang lain dengan mengubah kriteria "pakaian" ke kriteria lain.

Dengan berbagai kondisi

Kami memerlukan opsi ini dalam kasus di mana, selain pakaian, kami juga tertarik pada biaya satu unit barang. Itu. kecuali untuk satu kondisi, dua atau lebih dapat digunakan. Dalam hal ini, kita perlu mendaftarkan fungsi yang sedikit dimodifikasi - SUMIF. Sufiks MH berarti banyak kondisi (setidaknya 2), yang sekarang akan kami tunjukkan kepada Anda. Fungsinya akan terlihat seperti:

\u003d SUMIFS (F3: F12; C3: C12; "pakaian"; E3: E12; 50)dimana:

F3: F12 - rentang penjumlahan;
"Pakaian" dan 50 - kriteria;
C3: C12 dan E3: E12 masing-masing adalah rentang tipe produk dan nilai unit.

Perhatian! Dalam fungsi SUMIFS, argumen rentang jumlah muncul di awal rumus. Hati-Hati!

Dengan kondisi dinamis

Ada situasi ketika kita lupa menambahkan salah satu produk ke tabel dan perlu ditambahkan. Situasi ini diselamatkan oleh fakta bahwa fungsi SUMIF dan SUMIFS secara otomatis menyesuaikan dengan perubahan data dalam tabel dan langsung memperbarui nilai total.

Untuk menyisipkan baris baru, Anda perlu mengklik kanan pada sel yang diinginkan dan memilih "Sisipkan" - "Garis".

Kemudian masukkan saja data baru atau salin dari lokasi lain. Nilai akhir akan berubah sesuai. Transformasi serupa terjadi saat mengedit atau menghapus garis.

Di sinilah saya berakhir. Anda telah melihat contoh dasar penggunaan fungsi SUMIF di Excel. Jika Anda memiliki rekomendasi atau pertanyaan - Anda dipersilakan di komentar.

Fungsi SUMIF. Yang pertama dari tiga paus Excel. Saya mulai meninjau alat utama program Microsoft favorit kami. Dan tentu saja, pertama-tama saya ingin berbicara tentang fungsi saya yang paling sering digunakan ("formula"). Lebih tepatnya, fungsi SUMIF. Jika Anda tidak tahu apa itu dan bagaimana menggunakannya - saya iri padamu! Itu adalah penemuan nyata bagi saya.

Apakah Anda harus meringkas data karyawan atau pelanggan dari tabel besar, memilih berapa pendapatan untuk item tertentu? Anda memfilter berdasarkan nama belakang / posisi, dan kemudian memasukkan nomor dengan tangan ke dalamnya sel individu? Mungkinkah mereka mengandalkan kalkulator? Dan jika ada lebih dari seribu baris? Bagaimana cara menghitung dengan cepat?

Di sinilah SUMIF berguna!

Tugas 1... Ada statistik barang, kota dan indikator apa yang telah dicapai untuk barang-barang ini. Itu perlu untuk menghitung: "untuk berapa banyak yang terjual item Commodity1? "

Bagaimana cara kerja fungsi SUMIF di Excel? Menghitung jumlah dengan nilai

Sebelum melanjutkan ke solusi dari masalah pertama, mari kita analisis terdiri dari apa saja fungsi SUMIF:

Jarak. Rentang yang berisi istilah pencarian. Ini wajib diisi. Untuk tugas pertama, kolom Produk.
Kriteria. Dapat diisi dengan angka (85), ekspresi ("\u003e 85"), referensi sel (B1), fungsi (hari ini ()). Menentukan kondisi di mana (!) Dijumlahkan. Semua kondisi teks diapit oleh tanda kutip ( « ) "\u003e 85". Ini wajib diisi. Untuk tugas pertama, kolom \u003d Produk1
Sum_range. Sel yang akan ditambahkan jika berbeda dari sel dalam Rentang. Untuk tugas pertama, kolom Pendapatan.

Jadi, mari kita tulis rumusnya, setelah sebelumnya memasukkan argumen kondisi ke dalam sel F3

Jangan lupa untuk memeriksa;) Apakah itu dihitung? Baik? Baik!

Bagaimana cara kerja fungsi SUMIFS di Excel? Menghitung jumlah dengan nilai

Itu. pemilihan harus dilakukan menurut dua parameter. Untuk ini, fungsi SUMIF digunakan untuk beberapa kondisi - SUMIF, di mana urutan pencatatan dan jumlah argumen sedikit berubah.

Sekarang kita akan melihat fungsi Excel lain yang sangat sering digunakan - SUMIF.

Mari kita lihat salah satu contoh penggunaan fungsi SUMIF. Misalkan dalam sumber data kita memiliki tabel untuk pesanan di kota yang berbeda. Setiap baris menampilkan jumlah pesanan di kota tertentu pada tanggal tertentu (tanggal mana kami tidak tertarik dengan tugas ini).

Tugas kita adalah menghitung jumlah semua pesanan menurut kota untuk seluruh periode. Tugas ini dapat ditangani dengan mudah fungsi Excel - SUMIF. Sesuai dengan namanya, fungsi ini jumlahmengulang nilai, jika sebuah mereka memenuhi kriteria tertentu.

SUMIF (rentang, kriteria, jumlah_rentang)

Dalam contoh kita, kita perlu mengisi tabel kedua di sebelah kanan - kolom jumlah pesanan menurut kota. Seperti yang Anda lihat dari sintaks fungsi SUMIF, kami membutuhkan tiga argumen.

jarak - ini adalah rentang perbandingan, yaitu larik tempat kriteria akan dibandingkan. Dalam contoh kami, ini adalah A5: A504 sehingga saat meregangkan rumus, rentang kita tidak bergeser, kita akan membuatnya absolut dengan memberi tanda $. Kita mendapatkan -$ A $ 5: $ A $ 504

kriteria - Ini adalah argumen, yang dapat dilambangkan sebagai angka, ekspresi, teks, dll., Yang menentukan sel mana yang perlu dijumlahkan. Dalam contoh kami, kriterianya adalah kota.

jumlah_rentang - ini adalah rentang yang perlu dijumlahkan jika kriteria cocok. Dalam contoh kami, ini adalah rentangnya: B5: B504 , yang juga akan kami ubah menjadi $ B $ 5 mutlak untuk kenyamanan: $ B $ 504

Contoh penggunaan fungsi SUMIF

Untuk mengatasi masalah dengan contoh ini, tulis rumus berikut di sel F5:

SUMIF ($ A $ 5: $ A $ 504; E5; $ B $ 5: $ B $ 504)

Logika fungsi SUMIF adalah sebagai berikut: dalam kisaran $ A $ 5: $ A $ 504 mencari kriteria E5 (St. Petersburg), jika St. Petersburg berada, maka jumlah pesanan dari baris ini dijumlahkan, yaitu dari kisaran $ B $ 5: $ B $ 504

Ini adalah contoh sederhana penggunaan fungsi SUMIF, kedepannya kita akan mempertimbangkan fungsi ini secara lebih detail dengan contoh penggunaan fungsi ini.

Kami harap artikel ini bermanfaat bagi Anda. Jika Anda memiliki pertanyaan, pastikan untuk menanyakannya.

Kami akan berterima kasih jika Anda mengklik +1 dan / atau saya suka di bagian bawah artikel ini atau bagikan dengan teman Anda menggunakan tombol di bawah.

Terima kasih atas perhatiannya.

Fungsi JUMLAHmungkin yang paling umum digunakan. Tetapi terkadang Anda membutuhkan lebih banyak fleksibilitas daripada yang dapat disediakannya. Fungsi SUMIF berguna saat menghitung jumlah nosional. Misalnya, Anda mungkin ingin mendapatkan jumlah nilai negatif saja dalam satu rentang sel. Artikel ini menunjukkan cara menggunakan fungsi tersebut SUMIF untuk penjumlahan bersyarat menggunakan satu kriteria.

Rumusnya didasarkan pada tabel yang ditunjukkan pada Gambar. 118.1 dan dikonfigurasi untuk melacak akun. Kolom F berisi rumus yang mengurangi tanggal di kolom E dari tanggal di kolom D. Angka negatif di kolom F berarti pembayaran terlambat. Sheet menggunakan rentang bernama yang sesuai dengan label pada baris 1.

Ara. 118.1. Nilai negatif di kolom F menunjukkan keterlambatan pembayaran. Menjumlahkan hanya nilai negatif

Rumus berikut mengembalikan jumlah nilai negatif di kolom F. Dengan kata lain, mengembalikan jumlah hari jatuh tempo untuk semua akun (untuk tabel di Gambar 118.1, rumus akan mengembalikan -63):
\u003d SUMIF (Selisih; ".

Fungsi SUMIF bisa mengambil tiga argumen. Karena Anda menghilangkan argumen ketiga, argumen kedua (") merujuk ke nilai rentang Perbedaan.

Jumlahkan nilai berdasarkan rentang yang berbeda

Rumus berikut mengembalikan jumlah volume faktur yang jatuh tempo (di kolom C): \u003d SUMIF (Selisih; ". Rumus ini menggunakan nilai dalam rentang Perbedaanuntuk menentukan apakah nilai yang sesuai dalam rentang berkontribusi pada penjumlahan Jumlah.

Menjumlahkan nilai berdasarkan perbandingan teks

Rumus berikut mengembalikan jumlah total penagihan untuk kantor Oregon: \u003d SUMIF (Kantor; "\u003d Oregon"; Jumlah).

Tanda sama dengan adalah opsional. Rumus berikut memberikan hasil yang sama:

Untuk menjumlahkan tagihan untuk semua kantor kecuali Oregon, gunakan rumus ini:
\u003d SUMIF (Kantor; "Oregon"; Jumlah).

Jumlahkan nilai berdasarkan perbandingan tanggal

Rumus berikut mengembalikan total faktur jatuh tempo pada atau setelah 10 Mei 2010:
\u003d SUMIF (Tanggal; "\u003e \u003d" & DATE (2010: 5; 10); Jumlah).

Perhatikan bahwa argumen kedua untuk fungsi tersebut SUMIF adalah ekspresi. Ekspresi tersebut menggunakan fungsi tersebut TANGGALyang mengembalikan tanggal. Selain itu, operator perbandingan yang dikutip digabungkan (menggunakan & ) dengan hasil dari fungsi tersebut TANGGAL.

Rumus berikut mengembalikan jumlah total faktur yang belum jatuh tempo (termasuk faktur hari ini):
\u003d SUMIF (Tanggal; "\u003e \u003d" & HARI INI (); Jumlah).
Stomatitis akut pada orang dewasa adalah penyakit virus yang terjadi baik pada orang dewasa maupun anak-anak. DI baru-baru ini Stomatitis herpes akut dianggap sebagai manifestasi dari infeksi herpes primer dengan virus herpes simpleks di rongga mulut. Masa inkubasi penyakit ini berlangsung 1-4 hari. Periode prodromal ditandai dengan peningkatan kelenjar getah bening submandibular (dalam kasus yang parah, serviks). Suhu tubuh naik menjadi 37-41 ° C, kelemahan, pucat pada kulit, sakit kepala dan gejala lainnya dicatat.