Ketahui fungsi AGGREGATE yang menakjubkan dalam Excel, satu bernilai 19!

Dalam artikel sebelumnya "Berkongsi kemahiran Excel praktikal: Menggunakan "Cari dan Ganti" untuk menapis data tarikh ", kami mengetahui tentang beberapa operasi praktikal "Cari dan Ganti". Hari ini kita akan bercakap tentang fungsi statistik Excel ajaib, yang sebenarnya boleh bernilai 19. Ia hanyalah artifak! Kumpul dengan cepat.

Fungsi yang saya ingin perkenalkan kepada anda hari ini dipanggil AGREGATE. Walaupun ia adalah fungsi yang wujud dalam Excel 2010, tidak ramai yang mengetahui tentang fungsi ini. Ini adalah perkara yang sangat dikesali, kerana fungsi AGREGATE bukan sahaja dapat melaksanakan fungsi 19 fungsi seperti SUM, AVERAGE, COUNT, LARGE, dll. . Dan anda boleh mengabaikan baris tersembunyi, nilai ralat, nilai nol, dsb. Jika julat mengandungi nilai ralat, fungsi seperti SUM akan mengembalikan ralat dan fungsi AGGREGATE sangat mudah dalam kes ini.

Ini semua tentang bercakap dan bukan berlatih helah, mari kita lihat kemahiran AGREGATE.

Kami menggunakan jadual skor untuk menggambarkan penggunaan asas AGREGATE Sumber data adalah seperti yang ditunjukkan dalam rajah:

Setiap pelajar mengambil enam ujian, berdasarkan markah mereka Untuk mendapatkan lima kandungan statistik kawasan biru, saya percaya bahawa bagi kebanyakan rakan, tidak sukar untuk melengkapkan borang ini tidak lebih daripada menguasai beberapa fungsi asas: PURATA (skor purata), SUM (. jumlah skor), MAX (Skor tertinggi), MIN (skor terendah) dan COUNT (subjek rujukan sebenar) digunakan untuk mengira lima kandungan masing-masing. Mungkin ada beberapa rakan baru yang tidak memahami lima fungsi yang dinyatakan di atas, jadi untuk berjaga-jaga, anda hanya perlu mempelajari fungsi AGGREGATE untuk merealisasikan statistik data di atas.

Format asas AGREGATE ialah: = AGGREGATE(统计功能，忽略哪些值，数据区域) Mari kita lihat cara melengkapkan lima kandungan statistik dalam contoh.

1. Skor purata statistik

Formula statistik skor purata jadual semasa ialah: =AGGREGATE(1,,B2:G2). Masukkan formula dalam sel H2 dan isikan keseluruhan lajur dengan formula untuk mendapatkan purata markah setiap pelajar.

Penjelasan: Apabila fungsi statistik ialah 1, fungsi tersebut melaksanakan fungsi mengira purata. Dalam contoh ini, kita tidak perlu menentukan data untuk mengabaikan statistik, jadi parameter kedua boleh diabaikan (dua koma ditulis di sini, dan satu parameter ditinggalkan di tengah Parameter terakhir ialah kawasan data B2:G2). untuk dikira. Penggunaan fungsi Sangat mudah, jadi adakah hasilnya betul? Anda juga boleh menggunakan fungsi AVERAGE untuk mengesahkan:

Anda boleh melihat bahawa hasilnya adalah sama!

Seterusnya mari kita lihat cara menggunakan fungsi AGGREGATE untuk mengira jumlah markah.

2. Jumlah skor statistik

Formula statistik jumlah jadual semasa ialah: =AGGREGATE(9,,B2:G2). Masukkan formula dalam sel I2 dan isikan keseluruhan lajur dengan formula untuk mendapatkan jumlah markah setiap pelajar.

Cuma tukar parameter pertama kepada 9, kerana 9 sepadan dengan fungsi penjumlahan.

Bercakap mengenai perkara ini, sesetengah rakan mungkin bimbang bahawa 1 dalam parameter pertama mewakili purata dan 9 mewakili jumlah Fungsi ini mempunyai sejumlah 19 fungsi, jadi ia akan menjadi sukar untuk diingat.

Malah, tidak perlu risau tentang perkara ini sama sekali Excel memberikan kita fungsi peringatan yang sangat pintar Selepas kita memasuki fungsi tersebut, terdapat pilihan untuk fungsi parameter yang sepadan:

Ikuti sahaja gesaan ini dan pilih fungsi yang anda perlukan.

3 Statistik skor tertinggi

Selepas memahami fungsi ini, tiga item statistik terakhir akan mudah dilengkapkan mestilah Pilih 4, jadi formula sel J2 ialah: =AGGREGATE(4,,B2:G2)

4. Statistik skor terendah

Pilih 5 untuk skor terendah, dan formula sel K2 ialah: =AGGREGATE(5,,B2:G2)

5. Statistik Subjek Rujukan Sebenar

Subjek rujukan sebenar ialah bilangan nombor dalam kawasan data statistik Gunakan fungsi COUNT dan pilih 2, jadi formulanya ialah: =AGGREGATE(2,,B2:G2)

Okay. , lulus Daripada lima contoh di atas, rakan-rakan harus memahami penggunaan asas AGREGATE Walaupun hanya satu fungsi digunakan untuk menyelesaikan kerja lima fungsi, ia telah meningkatkan tahap kecekapan tertentu berbanding penggunaan lima fungsi sebelum ini. untuk menyelesaikan kerja, tetapi setiap formula masih perlu diubah suai sebelum ia boleh digunakan. Alangkah baiknya jika anda boleh menggunakan formula untuk menarik ke bawah dengan betul. (Rakan yang merasakan perkara yang sama boleh meninggalkan mesej di akhir artikel)

Enam atau lima jenis statistik boleh dilakukan dalam satu langkah

Bagi rakan-rakan yang mempunyai idea ini, Pujian harus diberikan Lagipun, kita belajar formula fungsi Excel bukan sahaja untuk menyelesaikan kerja, tetapi juga untuk meningkatkan kecekapan. Jadi adakah mungkin untuk menggunakan formula untuk menarik ke bawah ke kanan untuk melengkapkan lima statistik dalam contoh? Jawapannya ya: ya! Walau bagaimanapun, sepasang kombinasi fungsi diperlukan, iaitu pilih dan lajur.

Sebelum mendedahkan formula, mari kita jalankan analisis mudah masalah Dalam formula yang kita gunakan AGREGATE untuk melengkapkan lima statistik data, hanya parameter pertama, iaitu kaedah statistik, berubah, mengikut urutan: 1 , 9 ,4,5,2. Jika anda ingin menggunakan formula untuk menarik ke bawah ke kanan, anda perlu membuat parameter pertama perubahan formula dalam susunan ini apabila menarik ke bawah ke kanan (tiada perubahan diperlukan semasa menarik ke bawah, kerana kaedah statistik adalah sama ).

Biasanya fungsi lajur digunakan apabila anda perlu menggunakan formula untuk menarik ke kanan untuk mendapatkan data yang berubah secara berurutan:

Fungsi lajur adalah untuk mendapatkan korespondensi parameter Nombor lajur, sebagai contoh, lajur (a1) akan mendapat nombor lajur sel a1, iaitu 1. Apabila anda menariknya ke kanan, a1 akan menjadi b1, c1..., hasil formula akan menjadi mengikut urutan 1, 2, 3... tukar.

Dalam contoh ini, apa yang perlu kita dapatkan bukanlah urutan yang sangat biasa, tetapi urutan tidak tertib 1, 9, 4, 5, 2. Dalam kes ini, kita perlu menggunakan fungsi pilih Untuk mencapai :

Format asas fungsi Choose ialah: =choose(选择指数，值1，值2，值3……)

Fungsi Choose mengembalikan nilai dalam senarai parameter berdasarkan nombor parameter pertama. Sebagai contoh, dalam rajah di atas, apabila parameter pertama ialah 1, nilai pertama "1" dalam senarai parameter dikembalikan apabila parameter pertama ialah 2, nilai kedua "9" dalam senarai parameter dikembalikan , menggunakan lajur sebagai parameter pilihan pertama boleh mengembalikan urutan yang ditentukan.

Di atas adalah penjelasan gabungan fungsi pilih dan lajur Sekarang kembali kepada masalah kita, formula yang boleh digunakan untuk tarik-bawah kanan ialah: =AGGREGATE(CHOOSE(COLUMN(A1),1,9,4,5,2),,$B2:$G2)

<.>

Sesetengah orang baru mungkin masih pening. Saya percaya melalui pembelajaran berterusan, anda akan dapat menggunakan formula ini dengan bebas.

7. Pengumpulan fungsi parameter pertama

Melalui pengenalan di atas, kita dapat melihat bahawa apabila kita menggunakan fungsi AGREGATE dengan betul, kecekapan kerja telah bertambah baik. Apakah 19 fungsi parameter pertama fungsi ini? Anda boleh melihatnya dengan jelas melalui jadual perbandingan berikut:

Malah, terdapat hanya beberapa yang biasa digunakan .

8 Pengumpulan fungsi parameter kedua

Seterusnya mari kita lihat fungsi parameter kedua, atau melalui jadual perbandingan Untuk. fahami secara intuitif:

1 Abaikan nilai nol

Mari kita lihat cara menggunakan parameter kedua melalui dua. contoh. Pilih kandungan untuk diabaikan:

=AGGREGATE(9,1,B2:B15)

Pilih 9 untuk parameter pertama, yang mewakili penjumlahan dan pilih 1 untuk parameter kedua, yang mewakili mengabaikan baris tersembunyi apabila semua data dipaparkan, hasil penggunaan fungsi AGGREGATE untuk menjumlahkan adalah konsisten dengan menggunakan fungsi SUM (jumlah skor dalam baris 16 menggunakan fungsi SUM untuk menjumlahkan), apabila kita menyembunyikan baris data tertentu, kita dapat melihat perbezaannya. :

Selepas menyembunyikan baris 4, 8 dan 11, formula =AGGREGATE(9,1,B2:B15) hanya meringkaskan data yang dipaparkan pada masa ini.

Bercakap mengenai perkara ini, pelajar yang telah mempelajari fungsi SUBTOTAL pasti akan menyangka bahawa SUBTOTAL juga mempunyai fungsi sedemikian. Walau bagaimanapun, fungsi AGGREGATE yang muncul hari ini adalah lebih berkuasa daripada fungsi SUBTOTAL, kerana SUBTOTAL tidak dapat mengendalikan nilai ralat dan ringkasan ringkasan bersarang, tetapi AGGREGATE masih boleh mengendalikannya.

2. Abaikan nilai ralat

Contoh terakhir hari ini, mari lihat apa yang berlaku apabila nilai ralat ditemui:

Seperti yang ditunjukkan dalam gambar di atas, markah Cina setiap pelajar diperolehi daripada jadual skor menggunakan fungsi vlookup (fungsi ini telah dibincangkan dalam tutorial sebelum ini, dan mereka yang belum mengetahuinya boleh klik pada pautan untuk mengetahui tentangnya: Sisipkan pautan ). Apabila nama tiada dalam jadual skor, nilai ralat akan diperoleh, seperti Li Si dan Zhang San Pada masa ini, tidak kira kita menggunakan fungsi SUM atau fungsi SUBTOTAL, kita tidak boleh mendapatkan jumlah markah yang betul. Skor bahasa Cina hanya AGREGATE boleh mengabaikan nilai ralat kepada hasil yang betul. Sudah tentu, anda boleh menggunakan iferror dan fungsi lain untuk memproses dan kemudian menggunakan SUM untuk menjumlahkan, tetapi ini tidak menutup kuasa AGREGATE.

19 jenis fungsi statistik ditambah dengan 7 jenis item yang diabaikan. AGREGATE ialah raja fungsi statistik yang layak, kumpulkan ia sekarang!

Cadangan pembelajaran berkaitan: tutorial cemerlang

Atas ialah kandungan terperinci Ketahui fungsi AGGREGATE yang menakjubkan dalam Excel, satu bernilai 19!. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!