Tutorial ini akan mengajar anda formula yang paling berkesan untuk jumlah purata, peratusan dan masa dalam Excel dan mengelakkan kesilapan.
Di Microsoft Excel, terdapat segelintir formula yang berbeza untuk mengira purata. Tutorial ini memberi tumpuan kepada yang paling popular - fungsi purata.
Fungsi purata dalam Excel digunakan untuk mencari min aritmetik nombor yang ditentukan. Sintaks adalah seperti berikut:
Purata (Nombor1, [Number2], ...)Di mana Number1, Number2 , dan lain -lain adalah nilai angka yang anda mahu mendapatkan purata. Mereka boleh dibekalkan dalam bentuk nilai angka, tatasusunan, sel atau rujukan. Hujah pertama diperlukan, yang berikutnya adalah pilihan. Dalam satu formula, anda boleh memasukkan sehingga 255 argumen.
Purata boleh didapati dalam semua versi Excel 365 walaupun Excel 2007.
Untuk sebahagian besar, menggunakan fungsi purata dalam Excel adalah mudah. Walau bagaimanapun, terdapat beberapa nuansa yang perlu anda ketahui.
Nota. Apabila menggunakan fungsi purata dalam lembaran Excel anda, sila ingat perbezaan antara sel dengan nilai sifar dan sel kosong - 0 dikira, tetapi sel kosong tidak. Ini mungkin sangat mengelirukan jika pilihan " menunjukkan sifar dalam sel yang mempunyai nilai sifar " tidak dicentang dalam lembaran kerja yang diberikan. Anda boleh mencari pilihan ini di bawah Pilihan Excel > Advanced > Pilihan Paparan untuk Lembaran Kerja ini .
Untuk membina formula Excel asas untuk purata, yang perlu anda lakukan ialah membekalkan nilai sumber. Ini boleh dilakukan dengan beberapa cara yang berbeza.
Untuk mengira purata nombor tertentu, anda boleh menaipnya secara langsung dalam formula. Sebagai contoh, rata -rata nombor 1,2,3, dan 4, formula adalah:
=AVERAGE(1,2,3,4)
Untuk purata lajur dalam Excel, membekalkan rujukan seluruh lajur:
=AVERAGE(A:A)
Untuk rata-rata satu baris , gunakan rujukan seluruh baris:
=AVERAGE(1:1)
Untuk purata pelbagai sel , tentukan julat itu dalam formula anda:
=AVERAGE(A1:C20)
Untuk mengembalikan purata sel-sel yang tidak bersebelahan , setiap rujukan sel harus dibekalkan secara individu:
=AVERAGE(A1, C1, D1)
Untuk purata pelbagai julat , gunakan beberapa rujukan pelbagai dalam formula tunggal:
=AVERAGE(A1:A20, C1:D10)
Dan secara semulajadi, tidak ada yang menghalang anda daripada termasuk nilai, sel dan rujukan dalam formula yang sama seperti yang diperlukan oleh logik perniagaan anda. Contohnya:
=AVERAGE(B3:B5, D7:D9, E11, 100)
Dan inilah senario kehidupan sebenar. Dalam dataset di bawah, kami menggunakan 3 formula yang berbeza untuk mengira purata - dalam keseluruhan julat, dalam setiap baris dan dalam setiap lajur:
Selain daripada nombor, purata Excel dapat dengan mudah mencari min aritmetik nilai angka lain seperti peratusan dan masa, seperti yang ditunjukkan dalam contoh berikut.
Untuk mendapatkan purata peratusan, anda menggunakan formula Excel biasa untuk purata. Perkara utama adalah untuk menetapkan format peratus untuk sel formula.
Sebagai contoh, untuk mengira peratusan purata dalam sel C2 melalui C11, formula adalah:
=AVERAGE(C2:C11)
Mengira unit masa yang berbeza secara manual, akan menjadi kesakitan yang nyata ... bernasib baik, fungsi purata Excel mengatasi masa dengan sempurna. Untuk purata masa untuk dipaparkan dengan betul, ingatlah untuk menggunakan format masa yang sesuai untuk sel formula.
Sebagai contoh, untuk mencari masa purata dalam dataset di bawah, formula adalah:
=AVERAGE(B3:B13)
Fungsi purata Excel melangkau sel kosong, teks dan nilai logik, tetapi bukan sifar. Dalam imej di bawah, perhatikan bahawa purata dalam sel E4, E5 dan E6 adalah sama seperti dalam E3 sebagai sel kosong dan nilai tidak sah dalam lajur C diabaikan dan hanya nombor dalam lajur B dan D diproses. Walau bagaimanapun, nilai sifar dalam C7 dimasukkan dalam purata dalam E7, kerana ia adalah nilai angka yang sah.
Untuk mengecualikan sifar, gunakan fungsi purata atau purata. Contohnya:
=AVERAGEIF(B3:D3, "0")
Untuk mendapatkan purata nilai 3, 5, 10 atau n teratas dalam julat, gunakan purata dalam kombinasi dengan fungsi yang besar:
Purata (besar ( julat , {1,2,3, ..., n}))Sebagai contoh, untuk mendapatkan purata 3 nombor terbesar dalam B3: B11, formula adalah:
=AVERAGE(LARGE(B3:B11, {1,2,3}))
Untuk mengira nilai bawah 3, 5, 10 atau N nilai dalam julat, gunakan purata bersama -sama dengan fungsi kecil:
Purata (kecil ( julat , {1,2,3, ..., n}))Sebagai contoh, rata -rata 3 nombor terendah dalam julat, formula berjalan seperti berikut:
=AVERAGE(SMALL (B3:B11, {1,2,3}))
Dan inilah hasilnya:
Bagaimana formula ini berfungsi :
Biasanya, fungsi besar menentukan nilai terbesar ke dalam array yang diberikan. Untuk mendapatkan nilai N atas, pemalar array seperti {1,2,3} digunakan untuk hujah kedua.
Dalam kes kami, pulangan besar 3 nilai tertinggi dalam julat, iaitu 94, 93 dan 90. Purata mengambilnya dari sana dan mengeluarkan min.
Gabungan purata kecil berfungsi dengan cara yang sama.
Untuk mengira purata dengan keadaan, anda boleh memanfaatkan purata atau purata dalam Excel 2007 - 365. Dalam versi terdahulu, anda boleh membina purata anda sendiri jika formula.
Untuk purata nombor yang memenuhi syarat tertentu, gunakan formula generik ini:
Purata (jika ( kriteria_range = kriteria , purata_range ))Dalam Excel 2019 dan lebih rendah, ini hanya berfungsi sebagai formula array, yang bermaksud anda perlu menekan kekunci CTRL Shift Enter untuk menyelesaikannya dengan betul. Dalam Excel 365 dan 2021, formula biasa akan berfungsi dengan baik.
Sebagai contoh, mari kita cari skor matematik purata dalam jadual di bawah. Untuk ini, gunakan "matematik" untuk kriteria:
=AVERAGE(IF(C3:C11="Math", B3:B11))
Atau anda boleh memasukkan keadaan dalam beberapa sel dan merujuk sel (F2 dalam kes kami):
=AVERAGE(IF(C3:C11=F2, B3:B11))))
Bagaimana formula ini berfungsi
Ujian logik fungsi IF membandingkan setiap subjek dalam C3: C11 terhadap sasaran satu dalam F2. Hasil perbandingan adalah pelbagai nilai yang benar dan palsu, di mana tru mewakili pertandingan:
{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}
Untuk argumen value_ if_true , kami membekalkan julat skor (B3: B11), jadi jika ujian logik adalah benar, skor yang sepadan dikembalikan. Sebagai nilai value_ if_false ditinggalkan, palsu muncul di mana keadaan tidak dipenuhi:
{FALSE;FALSE;FALSE;74;67;FALSE;FALSE;59;FALSE}
Arahan ini diberi makan kepada fungsi purata, yang mengira min aritmetik angka -angka yang mengabaikan nilai palsu.
Untuk nombor purata dengan beberapa kriteria, formula generik adalah:
Purata (jika (( kriteria_range1 = kriteria1 ) * ( kriteria_range2 = kriteria2 ), purata_range ))Sebagai contoh, rata -rata skor matematik di Kelas A, anda boleh menggunakan formula ini:
=AVERAGE(IF((C3:C11="Math") * (D3:D11="A"), B3:B11))
Dengan rujukan sel untuk kriteria, ini berfungsi dengan baik:
=AVERAGE(IF((C3:C11=G2) * (D3:D11=G3), B3:B11))
Dalam Excel 2019 dan lebih rendah, kedua -dua perkara di atas harus menjadi formula array, jadi ingatlah untuk menekan CTRL Shift Enter untuk mendapatkan hasil yang tepat. Dalam Excel 365 dan 2021, kunci masuk biasa akan berfungsi dengan baik kerana sokongan terbina untuk tatasusunan dinamik.
Hasil yang sama dapat dicapai dengan bantuan bersarang jika pernyataan:
=AVERAGE(IF(C3:C11=G2, IF(D3:D11=G3, B3:B11)))
Formula yang hendak digunakan hanyalah soal keutamaan peribadi anda.
Bagaimana formula ini berfungsi
Dalam ujian logik jika, dua operasi perbandingan dilakukan - pertama, anda menyemak senarai subjek dalam C3: C11 terhadap nilai dalam G2, dan kemudian anda membandingkan kelas dalam D3: D11 terhadap nilai dalam G3. Kedua -dua tatasusunan nilai sebenar dan palsu didarabkan, dan operasi pendaraban berfungsi seperti dan pengendali. Dalam mana -mana operasi aritmetik, benar bersamaan dengan 1 dan palsu sama dengan 0. Mengalikan dengan 0 sentiasa memberikan sifar, jadi array yang dihasilkan mempunyai 1 hanya apabila kedua -dua keadaan adalah benar. Arahan ini dinilai dalam ujian logik fungsi IF, yang mengembalikan skor yang sepadan dengan 1 (benar):
{FALSE;FALSE;FALSE;74;67;FALSE;FALSE;FALSE;FALSE}
Arahan akhir ini disampaikan kepada purata.
Sekiranya anda ingin melengkapkan hanya purata yang dipaparkan tanpa mengubah nilai asas, gunakan perintah perpuluhan penurunan pada reben atau kotak dialog sel format seperti yang dijelaskan dalam cara bulat purata dalam Excel.
Untuk mengelilingi nilai yang dikira itu sendiri, menggabungkan purata dengan salah satu fungsi pembulatan Excel.
Untuk mengikuti peraturan matematik umum untuk pembulatan, purata sarang dalam fungsi bulat. Dalam hujah ke -2 ( num_digits ), tentukan bilangan digit untuk bulat purata ke.
Sebagai contoh, untuk mengelilingi purata kepada integer terdekat , gunakan formula ini:
=ROUND(AVERAGE(B3:B11), 0)
Untuk bulat purata ke satu tempat perpuluhan , gunakan 1 untuk argumen num_digits :
=ROUND(AVERAGE(B3:B11), 1)
Untuk bulat purata ke dua tempat perpuluhan , gunakan 2 untuk argumen num_digits :
=ROUND(AVERAGE(B3:B11), 2)
Dan sebagainya.
Untuk bulat ke atas, gunakan fungsi Roundup:
=ROUNDUP(AVERAGE(B3:B11), 1)
Untuk membulatkan ke bawah, pusingan adalah fungsi untuk digunakan:
=ROUNDDOWN(AVERAGE(B3:B11), 1)
Jika pelbagai sel yang anda cuba hitung tidak mempunyai nilai angka, formula purata akan mengembalikan pembahagian dengan ralat sifar (#div/0!). Untuk membetulkannya, anda boleh mendapatkan sejumlah nilai angka dengan fungsi kiraan dan jika kiraan lebih besar daripada 0, maka purata; Jika tidak - kembalikan rentetan kosong.
Jika (kiraan ( julat )> 0, purata ( julat ), "")Sebagai contoh, untuk mengelakkan ralat #div/0 dengan purata dalam set data di bawah, gunakan formula ini:
=IF(COUNT(B6:B16)>0, AVERAGE(B6:B16), "")
Apabila cuba rata -rata pelbagai sel yang mempunyai sebarang kesilapan, hasilnya akan menjadi kesilapan. Untuk mengelakkan ini berlaku, gunakan salah satu penyelesaian berikut.
Sebelum purata, tapis kesilapan dengan bantuan fungsi IFError:
Purata (iferror ( julat , ""))Dalam semua versi kecuali Excel 365 dan 2021 di mana tatasusunan ditangani secara asli, tekan kekunci CTRL Shift Enter bersama -sama untuk menjadikannya formula array.
Sebagai contoh, rata -rata julat sel di bawah tanpa kesilapan, formula adalah:
=AVERAGE(IFERROR(B3:B13, ""))
Satu lagi cara mudah untuk mengabaikan kesilapan purata ialah menggunakan fungsi agregat. Untuk mengkonfigurasi agregat untuk tujuan ini, anda menetapkan argumen function_num kepada 1 (fungsi purata), dan argumen pilihan kepada 6 (abaikan nilai ralat).
Contohnya:
=AGGREGATE(1, 6, B3:B13)
Seperti yang anda lihat dalam tangkapan skrin, kedua -dua fungsi berfungsi dengan indah:
Sekiranya anda menghadapi masalah dengan formula purata di Excel, petua penyelesaian masalah kami akan membantu anda menyelesaikannya dengan cepat.
Jika julat yang anda cuba purata tidak mempunyai nilai angka tunggal, ralat #div/0 berlaku. Ini sering berlaku apabila nombor diformat sebagai teks. Untuk menetapkan ralat, hanya tukar teks ke nombor.
Segitiga hijau kecil di sudut kiri atas sel adalah petunjuk yang jelas mengenai kes ini:
Jika formula purata merujuk kepada sel -sel yang mengandungi beberapa kesilapan, katakan #Value!, Formula akan menghasilkan kesilapan yang sama. Untuk menyelesaikan masalah ini, gunakan gabungan purata bersama dengan IFError atau fungsi agregat seperti yang diterangkan dalam contoh -contoh ini. Atau menggantikan ralat nilai dalam data sumber dengan beberapa teks jika berkenaan.
Jika sel anda memaparkan formula dan bukannya jawapan, maka kemungkinan mod formula pertunjukan berada di lembaran kerja anda. Sebab -sebab lain boleh menjadi formula yang dimasukkan sebagai teks, dengan ruang terkemuka atau apostrophe sebelum tanda yang sama. Penyelesaian kepada semua masalah ini disediakan di sini: Formula Excel tidak mengira.
Begitulah cara anda menggunakan fungsi purata dalam Excel untuk mencari aritmetik. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!
Formula purata dalam Excel - Contoh (fail .xlsx)
Atas ialah kandungan terperinci Fungsi purata excel dengan contoh. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!
Apakah yang berlaku apabila saya tidak dapat menyambung ke rangkaian?
connectionresetSolution
Bagaimana untuk mengoptimumkan satu halaman
Bagaimana untuk membeli dan menjual Bitcoin di Huobi.com
Bagaimana untuk menyelesaikan 500 ralat pelayan dalaman
tekan sebarang kekunci untuk memulakan semula
Bagaimana untuk melaksanakan fungsi paging jsp
Pengenalan kepada perisian pemodelan parametrik
Bagaimana untuk melaksanakan skrip shell
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
