Fungsi Excel seperti SUM, COUNT, LARGE, dan MAX tidak berfungsi jika rentang berisi kesalahan. Namun, Anda dapat dengan mudah menggunakan fungsi AGGREGATE untuk memperbaikinya
1. Misalnya, Excel mengembalikan kesalahan jika Anda menggunakan fungsi SUM untuk menjumlahkan rentang dengan kesalahan
2. Gunakan fungsi AGREGAT untuk menjumlahkan rentang dengan kesalahan
Penjelasan. argumen pertama (4) memberi tahu Excel bahwa Anda ingin menggunakan fungsi MAX. Argumen kedua (7) memberi tahu Excel bahwa Anda ingin mengabaikan nilai kesalahan dan baris tersembunyi. Dalam contoh ini, baris 2 disembunyikan
Excel biasanya merupakan opsi pilihan untuk tugas penyimpanan dan transformasi data, terutama jika Anda bekerja dengan kumpulan data besar. Jika rutinitas harian Anda melibatkan analisis atau interpretasi ribuan baris, maka itu bisa menjadi berlebihan atau, lebih buruk lagi, sama sekali tidak efisien. Jika Anda bekerja di Keuangan atau Penjualan, kemungkinan Anda perlu mengidentifikasi pola untuk membuat prediksi. Ini akan membutuhkan penggabungan rentang sel atau meringkas data kunci sambil meminimalkan kesalahan atau rumus yang rusak
Dalam artikel ini, Anda akan mempelajari cara menggabungkan data di Excel menggunakan fungsi AGGREGATE dengan mendapatkan penjelasan mendalam tentang fungsi AGGREGATE;
Mulailah Dengan Lapisan Sekarang
Bagikan bagian Google Spreadsheet Anda, pantau, tinjau, dan setujui perubahan, serta sinkronkan data dari berbagai sumber – semuanya dalam hitungan detik
Fungsi AGREGAT mengembalikan hasil penghitungan agregat seperti AVERAGE, COUNT, MAX, MIN, dll. dilakukan pada satu atau lebih referensi. Fungsi AGGREGATE seperti versi yang ditingkatkan dari fungsi SUBTOTAL yang lebih lama, dan menyediakan lebih banyak opsi penghitungan, dan lebih banyak kontrol untuk mengabaikan hal-hal tertentu. Ada dua alasan yang membuat fungsi AGREGAT sangat berguna, dibandingkan dengan fungsi lain yang menjalankan operasi yang sama
- AGREGAT memiliki sejumlah opsi untuk mengabaikan error, baris tersembunyi, dan fungsi lain yang mungkin muncul di data
- AGREGAT dapat menangani banyak operasi larik secara asli, tanpa Control + Shift + Enter
AGREGAT dapat menjalankan total , dan fungsi yang akan dijalankan diberikan sebagai angka, yang muncul sebagai argumen pertama dalam fungsi, function_num. Argumen kedua, opsi, mengontrol cara AGREGAT menangani kesalahan dan nilai dalam baris tersembunyi. Lihat untuk semua opsi yang tersedia
Fungsi AGREGAT membutuhkan empat argumen. function_num, opsi, ref1, dan ref2. Untuk 13 fungsi pertama yang didukung, hanya diperlukan tiga argumen pertama. function_num menentukan operasi, opsi menyetel berbagai perilaku, dan ref1 adalah larik nilai untuk diproses
6 fungsi terakhir membutuhkan keempat argumen. function_num menentukan operasi, options sets , ref1 adalah larik nilai yang akan diproses, dan ref2 adalah "argumen kedua" untuk fungsi yang dipanggil. Sebagai contoh, fungsi seperti SMALL dan LARGE menggunakan argumen kedua, k, dan ref2 menyatakan argumen tersebut. Enam fungsi berikut memerlukan argumen ref2
LARGE(array,k) SMALL(array,k) PERCENTILE.INC(array,k) QUARTILE.INC(array,quart) PERCENTILE.EXC(array,k) QUARTILE.EXC(array,quart)Contoh 1
Untuk mengembalikan nilai MAX dalam rentang A1. A10, dengan mengabaikan kesalahan dan baris tersembunyi, berikan 4 untuk nomor fungsi dan 7 untuk opsi
=AGGREGATE(4,7,A1:A10) // max value _Untuk mengembalikan nilai MIN dengan opsi yang sama, ubah nomor fungsi menjadi 5
=AGGREGATE(5,7,A1:A10) // min valueContoh #2
Dalam contoh yang ditunjukkan di atas, rumus di D5 adalah
=AGGREGATE(4,6,values)di mana "nilai" adalah rentang bernama B5. B14. Nomor fungsi adalah 4, yang menentukan MAX. Opsi disediakan sebagai 6, untuk mengabaikan kesalahan saja
Contoh #3 - terbesar ke-n
Rumus di D8. D10 mendemonstrasikan cara mengembalikan nilai "n terbesar".
=AGGREGATE(14,6,values,1) // 1st largest =AGGREGATE(14,6,values,2) // 2nd largest =AGGREGATE(14,6,values,3) // 3rd largest _Nomor fungsi di sini adalah 14, yang menjalankan fungsi BESAR. Karena fungsi LARGE memerlukan argumen k, ini muncul sebagai argumen terakhir dalam ketiga rumus di atas
Contoh #4 - operasi larik
Apa yang membuat AGREGAT sangat berguna untuk rumus yang lebih kompleks adalah bahwa AGREGAT dapat menangani array secara bawaan saat nomor fungsi adalah 14-19. Misalnya, untuk menemukan nilai MAX pada hari Senin, dengan data yang menyertakan tanggal dan nilai, Anda dapat menggunakan AGREGAT seperti ini
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")="Mon"),1)Di sini kami menentukan 14 untuk fungsi (BESAR) dan 6 untuk opsi (abaikan kesalahan). Kemudian kami membuat ekspresi logis menggunakan fungsi TEXT untuk memeriksa semua tanggal pada hari Senin. Hasil dari operasi ini adalah larik nilai BENAR/SALAH, yang menjadi penyebut dari nilai awal. FALSE dievaluasi sebagai nol, dan melempar #DIV/0. kesalahan. TRUE dievaluasi sebagai 1 dan mengembalikan nilai aslinya. Susunan nilai dan kesalahan terakhir bertindak seperti filter. AGREGAT mengabaikan semua kesalahan dan mengembalikan nilai terbesar (maksimum) yang bertahan. Contoh lebih lengkap di sini
Angka fungsi
Tabel di bawah mencantumkan nomor fungsi yang tersedia untuk fungsi AGGREGATE, bersama dengan nama fungsi terkait. Kolom ketiga, Ref2, menunjukkan "argumen kedua" yang diharapkan oleh 6 fungsi terakhir
FunctionFunctionRef21AVERAGE 2COUNT 3COUNTA 4MAX 5MIN 6PRODUCT 7STDEV. S 8STDEV. P 9 JUMLAH 10 VAR. S 11VAR. P 12MEDIAN 13MODE. SNGL 14LARGEk15SMALLk16PERCENTILE. INCk17QUARTILE. INCquart18PERSENTIL. EXCk19QUARTILE. EXCquartOpsi perilaku
Fungsi AGREGAT memiliki banyak opsi untuk mengabaikan kesalahan, baris tersembunyi, dan fungsi lainnya. Opsi diatur dengan argumen opsi. Nilai yang mungkin adalah 0-7, seperti yang ditunjukkan pada tabel di bawah ini
OptionBehavior0Abaikan fungsi SUBTOTAL dan AGGREGATE1Abaikan baris tersembunyi, fungsi SUBTOTAL dan AGGREGATE2Abaikan nilai kesalahan, fungsi SUBTOTAL dan AGGREGATE3Abaikan baris tersembunyi, nilai kesalahan, fungsi SUBTOTAL dan AGGREGATE4Abaikan apa-apa5Abaikan baris tersembunyi6Abaikan nilai kesalahan7Abaikan baris tersembunyi dan nilai kesalahanCatatan. Fungsi AGGREGATE seperti versi yang ditingkatkan dari fungsi SUBTOTAL yang lebih lama, dan menyediakan lebih banyak opsi penghitungan, dan lebih banyak kontrol untuk mengabaikan hal-hal tertentu. Namun, satu perbedaan tipis antara AGGREGATE dan SUBTOTAL adalah perilaku default untuk baris tersembunyi dibalik. Meskipun SUBTOTAL akan selalu mengabaikan nilai dalam baris yang disembunyikan oleh filter, dan memerlukan nomor fungsi yang berbeda untuk mengabaikan baris yang disembunyikan secara manual, AGGREGATE akan selalu mengabaikan baris yang disembunyikan secara manual dan memerlukan opsi khusus untuk mengabaikan baris yang disembunyikan oleh filter. Bahkan ketika argumen opsi disetel ke 4 (abaikan apa pun), AGGREGATE akan mengabaikan nilai dalam baris yang disembunyikan secara manual