Bagaimana Menggunakan COUNTIFS, SUMIFS, AVERAGEIFS dalam Excel

Tiga formula yang paling biasa digunakan dalam Excel yang melakukan pengiraan matematik mudah ialah COUNT, SUM dan AVERAGE. Sama ada anda menguruskan belanjawan kewangan dalam Excel atau hanya menjejaki percutian anda yang seterusnya, anda mungkin pernah menggunakan salah satu fungsi ini sebelum ini.

Dalam artikel ini, kita akan meneruskan dasar-dasar tiga fungsi ini dan rakan-rakan mereka yang relevan dan berguna: COUNTIFS, SUMIFS dan AVERAGEIFS.

Katakanlah kita memulakan perniagaan dalam talian yang baru menjual telefon bimbit dan kami mempunyai helaian yang menyenaraikan jualan yang telah kami buat dalam dua bulan pertama. Muat turun contoh hamparan Excel di sini.

Excel COUNT, SUM dan AVERAGE

Untuk mengetahui berapa banyak telefon bimbit yang telah kami jual, kami boleh dengan cepat menggunakannya COUNT formula seperti yang ditunjukkan di bawah:

= COUNT (E2: E16)

Sebaliknya, untuk mendapatkan jumlah jualan yang telah kami buat, kami boleh menggunakannya SUM formula seperti yang ditunjukkan di bawah:

= SUM (E2: E16)

Terakhir, untuk mengetahui purata jualan yang kami buat untuk semua telefon, kami boleh menggunakannya AVERAGE formula seperti berikut:

= RINGKASAN (E2: E16)

Hasilnya sepatutnya seperti berikut:

Formula COUNT, SUM dan RATA akan hanya berfungsi untuk rekod di mana nilai sel dalam format nombor. Sebarang rekod dalam julat formula (iaitu. E2: E16 dalam contoh ini) tidak dalam format nombor akan diabaikan.

Oleh itu, sila pastikan semua sel dalam formula COUNT, SUM dan AVERAGE semua diformatkan sebagai Nombor, tidak Teks. Cuba gunakan formula yang sama, tetapi dengan E: E sebagai julat bukannya E2: E16. Ia akan mengembalikan hasil yang sama seperti sebelumnya kerana ia mengabaikan pengepala (iaitu. Harga jualan), yang terdapat dalam format teks.

Sekarang, bagaimana jika kita ingin mengetahui jumlah jualan, jumlah jualan dan jumlah purata jualan bagi setiap telefon, hanya untuk mereka yang dijual di Amerika Syarikat? Di sinilah COUNTIFS, SUMIFS dan AVERAGEIFS memainkan peranan penting. Perhatikan formula di bawah:

COUNTIFS

Pecahan formula:

1. = COUNTIFS ( - The "=" menunjukkan permulaan formula dalam sel dan COUNTIFS adalah bahagian pertama fungsi Excel yang kami gunakan.
2. D2: D16 - Merujuk kepada pelbagai data untuk menyemak sama ada ia memenuhi kriteria untuk dimasukkan ke dalam formula kiraan.
3. "USA" - Kriteria untuk mencari dalam julat data yang ditentukan (D2: D16)
4. ) - Penutup penutup menandakan berakhirnya formula.

Formula mengembalikan 6 iaitu jumlah jualan untuk produk yang dihantar dari gudang AS.

SUMIFS

Pecahan formula:

1. = SUMIFS ( - The "=" menunjukkan permulaan formula sekali lagi.
2. E2: E16 - Merujuk kepada pelbagai data yang kami mahu jumlahnya, iaitu harga jualan dalam contoh kami.
3. D2: D16 - Merujuk kepada pelbagai data untuk menyemak sama ada ia memenuhi kriteria untuk dimasukkan ke dalam jumlah keseluruhan.
4. "USA" - Kriteria untuk mencari dalam julat data yang ditentukan (D2: D16)
5. ) - Penutup penutup menandakan berakhirnya formula.

Formula menunjukkan $ 6,050 jumlah jualan yang dibuat untuk produk yang dihantar dari gudang AS.

AVERAGEIFS

Pecahan formula:

1. = AVERAGEIFS ( - The "=" nyatakan permulaan formula.
2. E2: E16 - Merujuk kepada pelbagai data yang kami ingin rata-rata. Dalam contoh ini, kami ingin mendapatkan purata jualan untuk semua telefon yang dijual di Amerika Syarikat.
3. D2: D16 - Merujuk kepada pelbagai data untuk menyemak sama ada ia memenuhi kriteria untuk dimasukkan ke dalam formula purata.
4. "USA" - Kriteria untuk mencari dalam julat data yang ditentukan
5. ) - Penutup penutup menunjukkan hujung formula.

Formula ini menunjukkan bahawa kami menjual produk untuk sekitar $ 1,008 setiap telefon di Amerika Syarikat.

Semua tiga formula boleh mengambil lebih dari satu kriteria. Contohnya jika kita ingin tahu angka yang sama (iaitu. COUNT, SUM dan AVERAGE) untuk produk yang dijual di USA, tetapi khusus hanya untuk Samsung jenama, kita hanya perlu menambah julat data untuk diperiksa diikuti dengan kriterianya.

Sila lihat contoh di bawah di mana kriteria kedua ditambah pada pemeriksaan kriteria awal. (Teks biru menunjukkan kriteria pertama dan merah menunjukkan kriteria kedua)

= COUNTIFS (D2: D16, "USA", B2: B16, "Samsung") = SUMIF (E2: E16, D2: D16, "USA", B2: B16, "Samsung") = AVERAGEIFS (E2: E16, D2: D16, "USA", B2: B16, "Samsung")

Anda akan melihat bahawa Excel juga mempunyai COUNTIF, SUMIF dan AVERAGEIF formula tanpa akhiran "S". Mereka digunakan serupa dengan COUNTIFS, SUMIFS dan AVERAGEIFS. Walau bagaimanapun, mereka tanpa akhiran "S" dalam formula mempunyai batasan hanya membenarkan satu kriteria bagi setiap formula.

Oleh kerana sintaksnya sedikit berbeza, saya akan mengesyorkan menggunakan COUNTIFS, SUMIFS dan AVERAGEIFS hanya kerana ia boleh digunakan untuk salah satu kriteria atau lebih, jika perlu. Nikmati!