Excel PurataFIF berfungsi dengan purata sel dengan keadaan

Tutorial menunjukkan cara menggunakan fungsi purata dalam Excel untuk mengira min aritmetik dengan keadaan.

Microsoft Excel mempunyai beberapa fungsi yang berbeza untuk mengira purata aritmetik nombor. Apabila anda mencari sel -sel purata yang memenuhi syarat tertentu, AverageIF adalah fungsi untuk digunakan.

• Fungsi Excel AverageIF
• PurataFIF berdasarkan sel lain
• Cara Menggunakan Purata Di Excel - Contoh
  • Purata jika sel mengandungi teks tertentu
  • Kriteria logik purata
  • Kriteria tarikh purata
  • Purata jika lebih besar daripada 0
  • Purata jika tidak sifar
  • Purata jika tidak sifar atau kosong
  • Purata jika kosong
  • Rata -rata jika tidak kosong
  • Purata dengan kad liar
  • Rata -rata tidak termasuk sel tertentu
• Rata -rata dengan rujukan sel

Fungsi purataFIF dalam Excel

Fungsi purataFIF digunakan untuk mengira purata semua sel dalam julat tertentu yang memenuhi syarat tertentu.

Purataf (julat, kriteria, [purata_range])

Fungsi ini mempunyai sejumlah 3 argumen - 2 yang pertama diperlukan, yang terakhir adalah pilihan:

• Julat (diperlukan) - Julat sel untuk menguji terhadap kriteria.
• Kriteria (diperlukan)- Keadaan yang menentukan sel-sel mana yang menjadi purata. Ia boleh dibekalkan dalam bentuk nombor, ungkapan logik, nilai teks, atau rujukan sel, misalnya 5, "> 5", "kucing", atau a2.
• Average_range (pilihan) - Sel -sel yang anda mahu rata -rata. Jika ditinggalkan, maka julat akan disusun purata.

Fungsi purataFIF boleh didapati di Excel 365 - 2007.

Petua. Untuk purata sel dengan dua atau lebih kriteria, gunakan fungsi purata.

Excel Purperif - Perkara yang perlu diingat!

Untuk menggunakan fungsi purataFIF dengan cekap dalam lembaran kerja anda, perhatikan perkara -perkara utama ini:

• Apabila mengira purata, sel kosong , nilai teks , dan nilai logik yang benar dan palsu diabaikan.
• Nilai sifar dimasukkan dalam purata.
• Jika sel kriteria kosong, ia dianggap sebagai nilai sifar (0).
• Jika purata_range mengandungi hanya sel kosong atau nilai teks, #div/0! ralat berlaku.
• Jika tiada sel dalam julat memenuhi kriteria , #div/0! ralat dikembalikan.
• Hujah purata_range tidak semestinya mempunyai saiz yang sama seperti julat . Walau bagaimanapun, sel -sel sebenar yang akan ditentukan oleh saiz hujah pelbagai . Dalam erti kata lain, sel kiri atas dalam purata_range menjadi titik permulaan, dan seberapa banyak lajur dan baris adalah purata seperti yang terkandung dalam argumen julat .

Formula purataFIF berdasarkan sel lain

Dengan fungsi Excel AverageIF, anda boleh rata -rata lajur nombor berdasarkan:

• kriteria yang digunakan pada lajur yang sama
• Kriteria yang digunakan pada lajur lain

Sekiranya keadaan itu berlaku pada lajur yang sama yang harus disusun secara purata, anda hanya menentukan dua hujah pertama: julat dan kriteria . Sebagai contoh, untuk mencari purata jualan dalam B3: B15 yang lebih besar daripada $ 120, formula adalah:

=AVERAGEIF(B3:B15, ">120")

Untuk purata berdasarkan sel lain , anda menentukan semua 3 argumen: julat (sel untuk memeriksa keadaan), kriteria (keadaan) dan purata_range (sel untuk dikira).

Sebagai contoh, untuk mendapatkan purata jualan yang dihantar selepas Oct-1, formula adalah:

=AVERAGEIF(C3:C15, ">1/10/2022", B3:B15)

Di mana C3: C15 adalah sel -sel untuk memeriksa kriteria dan B3: B15 adalah sel -sel kepada purata.

Cara Menggunakan Fungsi Purata Dalam Excel - Contohnya

Dan sekarang, mari kita lihat bagaimana anda boleh menggunakan Excel Averageif dalam lembaran kerja kehidupan sebenar untuk mencari purata sel yang memenuhi kriteria anda.

Kriteria teks purata

Untuk mencari purata nilai angka dalam lajur yang diberikan jika lajur lain mengandungi teks tertentu, anda membina formula purata dengan kriteria teks. Apabila nilai teks dimasukkan secara langsung dalam formula, ia harus disertakan dalam petikan berganda ("").

Sebagai contoh, rata -rata nombor dalam lajur b Jika lajur A mengandungi "epal", formula adalah:

=AVERAGEIF(A3:A15, "apple", B3:B15)

Sebagai alternatif, anda boleh memasukkan teks sasaran dalam beberapa sel, katakan F3, dan gunakan rujukan sel untuk kriteria . Dalam kes ini, petikan berganda tidak diperlukan.

=AVERAGEIF(A3:A15, F3, B3:B15)

Kelebihan pendekatan ini adalah bahawa ia membolehkan anda jualan purata untuk mana -mana item lain dengan hanya mengubah kriteria teks di F3, tanpa perlu membuat sebarang pelarasan kepada formula.

Petua. Untuk bulat purata ke beberapa tempat perpuluhan tertentu, gunakan peningkatan perpuluhan atau mengurangkan perintah perpuluhan pada tab rumah , dalam kumpulan nombor . Ini akan mengubah perwakilan paparan purata tetapi bukan nilai itu sendiri. Untuk mengelilingi nilai sebenar yang dikembalikan oleh formula, gunakan AverageIF bersama -sama dengan fungsi bulat atau lain -lain. Untuk maklumat lanjut, sila lihat bagaimana untuk bulat purata dalam Excel.

Kriteria logik purata untuk nilai angka

Untuk menguji pelbagai nilai angka dalam kriteria anda, gunakannya bersama -sama dengan "lebih besar daripada" (>), "kurang daripada" (), dan pengendali logik lain.

Apabila termasuk pengendali logik dengan nombor, ingatlah untuk melampirkan keseluruhan pembinaan dalam petikan berganda. Sebagai contoh, rata -rata nombor yang kurang daripada atau sama dengan 120, formula akan menjadi:

=AVERAGEIF(B3:B15, "

Perhatikan bahawa pengendali dan nombor kedua -duanya tertutup dalam petikan.

Apabila menggunakan "sama dengan" kriteria, tanda kesamaan (=) boleh ditinggalkan.

Sebagai contoh, purata jualan yang dihantar pada 9-Sep-2022, formula itu berlaku seperti berikut:

=AVERAGEIF(C3:C15, "9/9/2022", B3:B15)

Menggunakan purataf dengan tarikh

Sama seperti nombor, anda boleh menggunakan tarikh sebagai kriteria untuk fungsi purata. Kriteria tarikh boleh dibina dalam beberapa cara yang berbeza.

Mari kita lihat bagaimana anda boleh jualan purata dihantar sebelum tarikh tertentu, katakan 1 November 2022.

Cara paling mudah ialah melampirkan pengendali logik dan berkadar bersama dalam petikan berganda:

=AVERAGEIF(C3:C15, "

Atau anda boleh melampirkan pengendali dan tarikh dalam petikan secara berasingan dan menggabungkan mereka menggunakan & tanda:

=AVERAGEIF(C3:C15, "

Untuk memastikan tarikh dimasukkan dalam format yang dipahami Excel, anda boleh menggunakan fungsi tarikh yang disatukan dengan pengendali logik:

=AVERAGEIF(C3:C15, "

Kepada purata jualan yang dihantar pada tarikh hari ini, gunakan fungsi hari ini dalam kriteria:

=AVERAGEIF(C3:C15, "

Tangkapan skrin di bawah menunjukkan hasilnya:

Rata -rata lebih besar daripada 0

Dengan reka bentuk, fungsi purata Excel melangkau sel kosong tetapi termasuk 0 nilai dalam pengiraan. Untuk hanya nilai purata lebih besar daripada sifar, gunakan "> 0" untuk kriteria .

Sebagai contoh, untuk mengira purata nombor dalam B3: B15 yang lebih besar daripada sifar, formula dalam E4 adalah:

=AVERAGEIF(B3:B15, ">0")

Perhatikan bagaimana hasilnya berbeza dari purata biasa dalam E3:

Purata jika tidak 0

Penyelesaian di atas berfungsi dengan baik untuk satu set nombor positif. Jika anda mempunyai nilai positif dan negatif, maka anda boleh purata semua nombor tidak termasuk sifar menggunakan " 0" untuk kriteria .

Sebagai contoh, rata -rata semua nilai dalam B3: B15 kecuali sifar, gunakan formula ini:

=AVERAGEIF(B3:B15, "0")

Excel purata jika tidak sifar atau kosong

Oleh kerana fungsi purataFIF melangkau sel -sel kosong dengan reka bentuk, anda hanya boleh menggunakan kriteria "tidak sifar" (" 0"). Akibatnya, kedua -dua nilai sifar dan sel kosong akan diabaikan. Untuk memastikan ini, dalam set data sampel kami, kami menggantikan beberapa nilai sifar dengan kosong, dan mendapat hasil yang sama seperti dalam contoh sebelumnya:

=AVERAGEIF(B3:B15, "0")

Purata jika sel lain kosong

Ke sel -sel purata dalam lajur yang diberikan jika sel dalam lajur lain dalam baris yang sama kosong, gunakan "=" untuk kriteria . Ini termasuk sel-sel kosong yang tidak mengandungi apa-apa -tiada ruang, tiada rentetan panjang sifar, tiada aksara yang tidak dicetak, dll.

Kepada nilai purata yang sepadan dengan sel kosong secara visual termasuk yang mengandungi rentetan kosong ("") yang dikembalikan oleh fungsi lain, gunakan "" untuk kriteria .

Untuk tujuan ujian, kami akan menggunakan kedua -dua kriteria untuk purata nombor dalam B3: B15 yang tidak mempunyai tarikh penghantaran dalam C3: C15 (iaitu jika sel dalam lajur C kosong).

=AVERAGEIF(C3:C15, "=", B3:B15)

=AVERAGEIF(C3:C15, "", B3:B15)

Kerana salah satu sel kosong visual (C12) tidak benar -benar kosong - terdapat rentetan panjang sifar di dalamnya - formula memberikan hasil yang berbeza:

Purata jika sel lain tidak kosong

Untuk purata pelbagai sel jika sel dalam julat lain tidak kosong, gunakan "" untuk kriteria .

Sebagai contoh, formula purataFIF berikut mengira purata sel B3 melalui B15 jika sel dalam lajur C dalam baris yang sama tidak kosong:

=AVERAGEIF(C3:C15, "", B3:B15)

Purata Wildcard (Perlawanan Separa)

Untuk purata sel berdasarkan perlawanan separa, gunakan watak Wildcard dalam kriteria formula purata anda:

• Tanda tanya (?) Untuk memadankan mana -mana watak tunggal.
• Asterisk (*) untuk memadankan sebarang urutan aksara.

Katakan anda mempunyai 3 jenis pisang, dan anda ingin mencari purata mereka. Formula berikut akan mewujudkannya:

=AVERAGEIF(A3:A15, "*banana", B3:B15)

Sekiranya diperlukan, watak wildcard boleh digunakan bersama dengan rujukan sel. Dengan mengandaikan item sasaran berada dalam sel 199, formula mengambil bentuk ini:

=AVERAGEIF(A3:A15, "*"&D4, B3:B15)

Jika kata kunci anda mungkin muncul di mana -mana di dalam sel (pada mulanya, di tengah, atau pada akhirnya), letakkan asterisk di kedua -dua belah pihak:

=AVERAGEIF(A3:A15, "*banana*", B3:B15)

Untuk mencari purata semua item tidak termasuk mana -mana pisang , gunakan formula ini:

=AVERAGEIF(A3:A15, "*banana*", B3:B15)

Cara mengira purata dalam excel tidak termasuk sel tertentu

Untuk mengecualikan sel -sel tertentu dari purata, gunakan pengendali logik "tidak sama dengan" ().

Sebagai contoh, rata -rata nombor jualan untuk semua item kecuali "Apple", gunakan formula ini:

=AVERAGEIF(A3:A15, "apple", B3:B15)

Jika item yang dikecualikan berada dalam sel yang telah ditetapkan (D4), formula mengambil borang ini:

=AVERAGEIF(A3:A15, ""&D4, B3:B15)

Untuk mencari purata semua item tidak termasuk mana -mana "pisang", gunakan "tidak sama dengan" bersama dengan kad liar:

=AVERAGEIF(A3:A15, "*banana", B3:B15)

Sekiranya item Wildcard yang dikecualikan berada dalam sel yang berasingan (D9), kemudian menggabungkan pengendali logik, watak wildcard dan rujukan sel menggunakan ampersand:

=AVERAGEIF(A3:A15,""&"*"&D9, B3:B15)

Cara menggunakan puratafif dengan rujukan sel

Daripada menaip kriteria secara langsung dalam formula, anda boleh menggunakan pengendali logik dalam kombinasi dengan rujukan sel untuk membina kriteria. Dengan cara ini, anda akan dapat menguji keadaan yang berbeza dengan mengubah nilai dalam sel kriteria tanpa mengedit formula purata anda.

Apabila keadaan mungkir kepada " sama dengan ", anda hanya menggunakan rujukan sel untuk hujah kriteria . Formula di bawah mengira purata semua jualan dalam julat B3: B15 yang berkaitan dengan item dalam sel F4.

=AVERAGEIF(A3:A15, F4, B3:B15)

Apabila kriteria termasuk pengendali logik , anda membinanya dengan cara ini: Lampirkan pengendali logik dalam tanda petikan dan gunakan ampersand (&) untuk menggabungkannya dengan rujukan sel.

Sebagai contoh, untuk mencari purata jualan dalam B3: B15 yang lebih besar daripada nilai dalam F9, gunakan formula berikut:

=AVERAGEIF(B3:B15, ">"&F9)

Dengan cara yang sama, anda boleh menggunakan ungkapan logik dengan fungsi lain dalam kriteria.

Dengan tarikh dalam C3: C15, formula di bawah mengembalikan purata jualan yang telah dihantar ke tarikh semasa termasuk:

=AVERAGEIF(C3:C15, "

Begitulah cara anda menggunakan fungsi purataFIF dalam Excel untuk mengira min aritmetik dengan keadaan. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Buku Kerja Amalan untuk Muat turun

Fungsi Excel PurperiF - Contoh (fail .xlsx)

Atas ialah kandungan terperinci Excel PurataFIF berfungsi dengan purata sel dengan keadaan. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!