Contoh ringkasan lapan penggunaan fungsi AGREGATE dalam Excel

Artikel ini membawa anda pengetahuan yang berkaitan tentang excel, yang terutamanya memperkenalkan kandungan yang berkaitan tentang fungsi AGGREGATE Penggunaan fungsi ini adalah serupa dengan fungsi SUBTOTAL, tetapi ia lebih berfungsi daripada fungsi SUBTOTAL . Memang berkuasa, mari kita lihat, semoga bermanfaat untuk semua.

Cadangan pembelajaran berkaitan: tutorial cemerlang

Penggunaan fungsi AGREGATE adalah serupa dengan fungsi SUBTOTAL, tetapi ia lebih berkuasa daripada Fungsi SUBTOTAL , bukan sahaja dapat merealisasikan fungsi 19 fungsi seperti SUM, PURATA, COUNT, LARGE, MAX, dll., tetapi juga boleh mengabaikan baris tersembunyi, nilai ralat, nilai nol, dll, dan menyokong tatasusunan malar.

Parameter pertama fungsi ini ialah nombor antara 1 dan 19, digunakan untuk menentukan kaedah ringkasan yang akan digunakan:

Parameter kedua ialah pengenalan Nombor antara 0 dan 7, menyatakan jenis nilai yang akan diabaikan dalam kawasan pengiraan:

Seterusnya, mari kita bincangkan tentang beberapa kegunaan biasa fungsi ini :

1. Penjumlahan langsung berbilang kawasan terputus, mengabaikan nilai ralat

Kuasa fungsi ini ialah parameter ke-2 boleh ditentukan untuk mengabaikan nilai ralat dan terus mengira

seperti Rajah berikut, kawasan biru mengandungi nilai ralat yang berbeza, kini kita perlu menjumlahkan kawasan terputus ini.

Formulanya ialah:

=AGGREGATE(9,6,A3:A7,C3:C4,D6:F7)

2 Abaikan nilai ralat dalam keadaan penapisan

seperti yang ditunjukkan di bawah, Jika kawasan data yang ditapis mengandungi nilai ralat, bagaimana untuk melaksanakan statistik pada sel yang boleh dilihat?

Formulanya ialah:

=AGGREGATE(9,7,B6:B18)

Parameter pertama menggunakan 9, yang bermaksud penjumlahan, dan parameter kedua menggunakan 7 , yang bermaksud baris tersembunyi dan nilai ralat diabaikan.

3 Satu formula menyelesaikan pelbagai kesan statistik

Seperti yang ditunjukkan di bawah, data yang ditapis dalam kawasan sel A3:B14 mesti dikira dalam kawasan yang boleh dilihat dan maksimum dan maksimum semua data masing-masing. . Minimum, purata, jumlah, kiraan dan median.

Hanya satu formula cukup:

=AGGREGATE({4;5;1;9;3;12},{5,0},B4:B14)

Perhatikan bahawa ia ialah formula tatasusunan kawasan, mula-mula pilih c17: d22, Kemudian tulis formula dalam bar edit, dan akhirnya tekan ctrl shift masukkan tiga kekunci untuk masuk.

4. Anda semua tahu cara menjumlahkan ke atas, walaupun ia ditapis, bagaimana dengan ke bawah?

=AGGREGATE(9,3,A4:A)*2-AGGREGATE(9,7,A4:A)

Selain arah penjumlahan ke bawah, terdapat nilai tersembunyi dan ralat, yang tidak boleh dicapai dengan jumlah subtotal(jika) Statistik kesan

(Kaedah input adalah untuk memilih nilai nol kedudukan kawasan dan kemudian tulis formula dalam bar edit dan ctrl enter untuk mengisi kelompok)

5. statistik nilai ekstrem bersyarat

Fungsi ini merealisasikan kesan statistik fungsi maxif dan minif yang hanya tersedia pada 2016 lima tahun lalu, dan tidak memerlukan tiga kekunci.

Seperti yang ditunjukkan di bawah, untuk mengira nilai minimum yang sepadan dengan 1 bengkel, formulanya ialah:

=AGGREGATE(15,6,B4:B15/(A4:A15="1车间"),1)

Yang pertama dalam formula Gunakan 15 untuk parameter untuk menggunakan fungsi SMALL, dan gunakan 6 untuk parameter kedua untuk mengabaikan nilai ralat. Kawasan yang akan dikira ialah bahagian B4:B15/(A4:A15="1 bengkel")

A4:A15="1 bengkel" Mula-mula bandingkan sama ada bengkel dalam lajur A adalah sama dengan yang dinyatakan syarat. Jika julat sel A4:A15 sama dengan "1 bengkel", nilai logik TRUE dikembalikan, jika tidak nilai logik FALSE dikembalikan. Kemudian bahagikan tatasusunan memori ini dengan B4:B15, hasilnya ialah:

{70;69;87;77;55;46;#DIV/0!;#DIV/0!;#DIV/ 0! ;#DIV/0!;19;47}

Akhir sekali, fungsi AGGREGATE mengabaikan nilai ralat di dalam dan mendapat nilai minimum pertama.

Jika anda ingin mengira nilai minimum ketiga yang sepadan dengan bengkel 1, anda hanya perlu menukar 1 terakhir kepada 3.

Jika kita ingin mengira nilai maksimum yang sepadan dengan bengkel 1, kita boleh mengubah suai parameter pertama dan menggunakan 14, iaitu nilai maksimum ke-k.

6. Pertanyaan satu-ke-banyak

Jika anda mahukan pertanyaan satu-ke-banyak, ramai orang memikirkan gabungan tiga kekunci fungsi INDEX SAMLL IF. Malah, ia juga mungkin untuk menggantikannya dengan fungsi agregat.

Seperti yang ditunjukkan di bawah, untuk mengekstrak semua nombor kerja bengkel kedua, anda boleh menggunakan formula berikut:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(:)/(A:A=D),ROW(A1))),"")

Idea formula ini pada asasnya sama dengan formula kelima.

7 Kira nilai maksimum dalam sel yang sama

Seperti yang ditunjukkan di bawah, status penilaian berbilang orang dalam lajur B ditulis dalam sel yang sama, dan nilai maksimum antara mereka mestilah dikira. Formulanya ialah:

=AGGREGATE(14,6,--MID(B4,ROW(:),COLUMN(A:AZ)),1)

Bahagian MID(B4,ROW($1:$50), COLUMN(A:AZ)) formula, Gunakan fungsi MID untuk mengekstrak rentetan dengan panjang 1 hingga 50, bermula dari aksara pertama hingga ke-50, dan dapatkan susunan memori yang besar. Kemudian gunakan dua tanda negatif untuk menukar teks dalam tatasusunan memori menjadi nilai ralat, dan nilai itu masih nilainya sendiri.

Akhir sekali, gunakan fungsi AGGREGATE untuk mengabaikan nilai ralat dalam tatasusunan memori dan mengira nilai minimum pertama di antaranya.

8 Pada masa yang sama mengira nilai maksimum dan minimum syarat yang ditetapkan

Seperti yang ditunjukkan dalam rajah di bawah, adalah perlu untuk mengira nilai maksimum dan minimum sepadan dengan 1 bengkel. pada masa yang sama.

Mula-mula pilih sel F4:G4 pada masa yang sama, masukkan formula berikut dalam bar edit, tekan Ctrl Shift dan tekan Enter.

=AGGREGATE({16,15},6,B4:B15/(A4:A15=E4),1)

AGREGAT Parameter pertama menggunakan tatasusunan malar {16,15}, yang mewakili peraturan pengiraan menggunakan nilai maksimum dan nilai minimum masing-masing.

Hasil akhir juga ialah tatasusunan memori, jadi dua sel mesti dipilih untuk input pada masa yang sama.

Ciri fungsi ini ialah apabila parameter pertama ialah 14~19, anda boleh menggunakan parameter keempat Pada masa ini, parameter keempat menyokong tatasusunan, jadi anda boleh memainkan pelbagai aplikasi untuk menggantikan KECIL langsung , LARGE dan fungsi lain yang mengabaikan nilai ralat.

Cadangan pembelajaran berkaitan: tutorial cemerlang

Atas ialah kandungan terperinci Contoh ringkasan lapan penggunaan fungsi AGREGATE dalam Excel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!