Fungsi AVERAGE dirancang untuk mengabaikan sel kosong secara otomatis, tetapi ada beberapa contoh yang gagal. Gambar di atas sepertinya ada sel kosong di B3. B8, bagaimanapun, mereka dihitung sebagai nol. Mengapa?
1. 1 Sel yang berisi 0 [nol] diformat sebagai kosong
Sel-sel di B3. B8 tidak benar-benar kosong, pilih B3. B8 dan tekan CTRL + 1 untuk membuka pengaturan "Format Sel".
Di sini kita melihat bahwa itu telah diformat dengan pemformatan sel khusus. Nilai yang mengandung 0 [nol] disembunyikan, untuk menampilkannya tekan dengan tombol kiri mouse pada General lalu OK
Ini akan menampilkan nol Anda lagi di lembar kerja Anda, jika tidak baca bagian selanjutnya. Jika Anda tertarik mempelajari lebih lanjut tentang kode pemformatan khusus, baca artikel ini. Fungsi TEXT Excel dijelaskan
1. 2 Periksa pengaturan Excel Anda
Jika ini tidak berhasil, buka Opsi Excel Anda lalu tekan dengan tombol kiri mouse pada "Lanjutan"
Pengguna Excel 2016 tekan dengan tombol kiri mouse pada "File" pada pita dan kemudian tekan dengan tombol kiri mouse pada "Options"
Temukan "Tampilkan nol di sel yang memiliki nilai nol", apakah diaktifkan?
2. Rata-rata - abaikan kosong dan kesalahan
Fungsi AVERAGE mengabaikan sel kosong di luar kotak secara otomatis, tidak perlu khawatir tentang itu. Namun, jika Anda bekerja dengan kemungkinan nilai kesalahan dan menggunakan fungsi IFERROR untuk menangani kesalahan, hal-hal aneh akan terjadi
Fungsi IFERROR menghilangkan kesalahan tetapi memiliki efek samping yang mengubah sel kosong menjadi nol yang pada gilirannya membuat fungsi AVERAGE mengembalikan hasil yang salah
Coba rumus ini. =AVERAGE[IFERROR[B3. B8, ""]] mengembalikan 2 jika Anda menggunakan contoh nilai di atas dan itu salah. Gunakan sebagai gantinya rumus berikut
Rumus di sel D3
=AVERAGE[IFERROR[JIKA[B3. B8"",B3. B8,NA[]],""]]
Rumus di atas mengonversi sel kosong menjadi nilai kesalahan #N/A. lalu hilangkan nilai errornya, berikut langkah-langkahnya secara detail
Menjelaskan rumus
Langkah 1 - Identifikasi sel kosong
Tanda kurang dari dan lebih besar dari gabungan dievaluasi menjadi "tidak sama dengan", dalam hal ini, tidak sama dengan tidak ada [kosong]. Hasilnya adalah nilai boolean TRUE atau FALSE
B3. B8""'
menjadi
{5; . ;
dan kembali
{BENAR; . ;
Langkah 2 - Ganti nilai kosong dengan nilai kesalahan
Fungsi IF mengembalikan satu nilai jika uji logika BENAR dan nilai lain jika uji logika SALAH
JIKA[uji_logis, [nilai_jika_benar], [nilai_jika_salah]]
Fungsi IF menggantikan TRUE dengan nilai yang sesuai dan FALSE dengan nilai kesalahan. Nilai kesalahan dalam ekspresi uji logika mengembalikan nilai yang sama dalam fungsi IF
JIKA[B3. B8"", B3. B8, NA[]]
menjadi
JIKA[{BENAR; BENAR; #DIV/0. ; . B8, NA[]]
dan kembali
{5; . ;
Langkah 3 - Tangani nilai kesalahan
Fungsi IFERROR menggantikan nilai kesalahan dengan nilai tertentu yang Anda tentukan
IFERROR[nilai, nilai_jika_kesalahan]
Dalam hal ini menggantikan nilai kesalahan dengan tidak ada [kosong]
IFERROR[JIKA[B3. B8"",B3. B8,NA[]],""]
menjadi
IFERROR[{5; #N/A; #DIV/0. ;
dan kembali
{5;
Langkah 4 - Hitung rata-rata
Fungsi AVERAGE menghitung rata-rata sekelompok angka, teks dan sel kosong diabaikan
RATA-RATA[angka1, [angka2],. ]
RATA-RATA[IFERROR[JIKA[B3. B8"",B3. B8,NA[]],""]]
menjadi
RATA-RATA[{5; ""; ""; ""; ""; 1}]
dan mengembalikan 3 di sel D3. 5 + 1 = 6. 6/2 = 3
3. Rata-rata - abaikan kosong di sel yang tidak bersebelahan
Rumus berikut berisi dua rentang sel yang tidak bersebelahan B3. B8 dan D3. D4, fungsi AVERAGE mengabaikan sel kosong secara otomatis. Jika tidak demikian, pastikan Excel tidak menggunakan pemformatan sel yang berbeda, lihat bagian 1. 1 dan 1. 2 di atas
Rumus di sel B11
= RATA-RATA[B3. B8, D3. D4]
5+3+1+5 = 14. 14/4 = 3. 5
4. Rata-rata tertimbang abaikan yang kosong
Rumus di sel C14
=SUMPRODUCT[JIKA[B3. B12"", LOOKUP[$B$3. $B$12, $E$3. $E$8, F3. F8], 0]*C3. C12]/JUMLAH[JIKA[B3. B12"", LOOKUP[$B$3. $B$12, $E$3. $E$8, F3. F8], 0]]
4. Menjelaskan rumus di sel C14
Langkah 1 - Temukan bobot berdasarkan nilai
Fungsi LOOKUP memungkinkan Anda menemukan nilai dalam rentang sel dan mengembalikan nilai terkait pada baris yang sama
LOOKUP[lookup_value, lookup_vector, [result_vector]]
LIHAT[$B$3. $B$12, $E$3. $E$8, F3. F8]
menjadi
LOOKUP[{0; 210; 890; 440; 430; 50; 0; 540; 320; 310},{0; 100; 200; 300; 500; 800},{1; 2; 3; 4; 5; 6
dan kembali
{1;
Langkah 2 - Ganti nilai kosong dengan 0 [nol]
Fungsi IF mengembalikan satu nilai jika uji logika BENAR dan nilai lain jika uji logika SALAH
JIKA[uji_logis, [nilai_jika_benar], [nilai_jika_salah]]
JIKA[B3. B12"", LOOKUP[$B$3. $B$12, $E$3. $E$8, F3. F8], 0]
menjadi
JIKA[{""; 210; 890; 440; 430; 50; ""; 540; 320; 310}"", LOOKUP[$B$3. $B$12, $E$3. $E$8, F3. F8], 0]
menjadi
JIKA[{FALSE; BENAR; BENAR; BENAR; BENAR; BENAR; SALAH; BENAR; BENAR; BENAR}, LOOKUP[$B$3. $B$12, $E$3. $E$8, F3. F8], 0]
menjadi
JIKA[{SALAH; BENAR; BENAR; BENAR; BENAR; BENAR; SALAH; BENAR; BENAR; BENAR}, {1; 3; 6; 4; 4; 1; 1; 5; 4; 4}, 0]
dan mengembalikan {0;
Langkah 3 - Kalikan array dengan bobot
JIKA[B3. B12"", LOOKUP[$B$3. $B$12, $E$3. $E$8, F3. F8], 0]*C3. C12
menjadi
{0; . C12
menjadi
{0;
dan kembali
{0;
Langkah 4 - Tambahkan angka dan kembalikan total
Fungsi SUMPRODUCT menghitung hasil kali dari nilai yang sesuai, lalu mengembalikan jumlah dari setiap perkalian
SUMPRODUK[JIKA[B3. B12"", LOOKUP[$B$3. $B$12, $E$3. $E$8, F3. F8], 0]*C3. C12]
menjadi
SUMPRODUK[{0; 120; 180; 360; 320; 20; 0; 50; 40; 160}]
dan mengembalikan 1250
Langkah 5 - Hitung berat total
Fungsi SUM menghitung jumlah berdasarkan rentang sel atau larik
JUMLAH[JIKA[B3. B12"", LOOKUP[$B$3. $B$12, $E$3. $E$8, F3. F8], 0]
menjadi
JUMLAH[JIKA[B3. B12"", {1;
menjadi
JUMLAH[{0; 3; 6; 4; 4; 1; 0; 5; 4; 4}]
dan mengembalikan 31
Langkah 6 - Bagi total dengan berat total
SUMPRODUK[JIKA[B3. B12"", LOOKUP[$B$3. $B$12, $E$3. $E$8, F3. F8], 0]*C3. C12]/JUMLAH[JIKA[B3. B12"", LOOKUP[$B$3. $B$12, $E$3. $E$8, F3. F8], 0]]
menjadi
1250/JUMLAH[JIKA[B3. B12"", LOOKUP[$B$3. $B$12, $E$3. $E$8, F3. F8], 0]]
menjadi
1250/31
dan mengembalikan 40. 3225806451613
5. Rata-rata abaikan 0 [nol] dan kosong
Fungsi AVERAGE mengabaikan sel kosong secara otomatis. Jika tidak demikian, pastikan Excel tidak menggunakan pemformatan sel yang berbeda, lihat bagian 1. 1 dan 1. 2 di atas
Rumus di sel B11
=AVERAGEIF[B3. B8,"0"]
5+3+1=9. 9/3 = 3
Dapatkan Excel *. file xlsx
Dapatkan file Excel
berkas yang salah
Fungsi dalam artikel ini
RATA-RATAIFERRORJIKANASUMPRODUCTLOOKUPAVERAGEIF
Lebih dari 1300 rumus Excel
kategori Excel
Halaman rumah
Artikel terupdate terbaru
Fungsi Excel
Lebih dari 300 fungsi Excel dengan informasi mendetail termasuk sintaks, argumen, nilai kembalian, dan contoh untuk sebagian besar fungsi yang digunakan dalam rumus Excel
Formula Excel
Lebih dari 1300 formula disusun dalam subkategori
Tabel Excel
Tabel Excel menyederhanakan pekerjaan Anda dengan data, menambah atau menghapus data, memfilter, menjumlahkan, menyortir, meningkatkan keterbacaan menggunakan pemformatan sel, referensi sel, rumus, dan lainnya
Filter Lanjutan
Memungkinkan Anda memfilter data berdasarkan nilai yang dipilih , teks tertentu, atau kriteria lainnya. Ini juga memungkinkan Anda memfilter data yang ada atau memindahkan nilai yang difilter ke lokasi baru
Validasi data
Memungkinkan Anda mengontrol apa yang dapat diketik pengguna ke dalam sel. Ini memungkinkan Anda menentukan kondisi dan menampilkan pesan khusus jika data yang dimasukkan tidak valid
Daftar Turun
Memungkinkan pengguna bekerja lebih efisien dengan menampilkan daftar yang nilainya dapat dipilih pengguna. Ini memungkinkan Anda mengontrol apa yang ditampilkan dalam daftar dan lebih cepat daripada mengetik ke dalam sel
Rentang bernama
Memungkinkan Anda menamai satu sel atau lebih, ini memudahkan untuk menemukan sel menggunakan kotak Nama, membaca dan memahami rumus yang berisi nama, bukan referensi sel
Pemecah Excel
Excel Solver adalah add-in gratis yang menggunakan sel objektif, batasan berdasarkan rumus pada lembar kerja untuk melakukan analisis bagaimana-jika dan masalah keputusan lainnya seperti permutasi dan kombinasi
Bagan
Fitur Excel yang memungkinkan Anda memvisualisasikan data dalam grafik
Pemformatan Bersyarat
Memformat sel atau nilai sel berdasarkan kondisi atau kriteria, ada beberapa alat Pemformatan Bersyarat bawaan yang dapat Anda gunakan atau gunakan rumus pemformatan bersyarat yang dibuat khusus
Tabel pivot
Memungkinkan Anda dengan cepat meringkas sejumlah besar data dengan cara yang sangat ramah pengguna. Fitur Excel yang kuat ini memungkinkan Anda menganalisis, mengatur, dan mengkategorikan data penting secara efisien
VBA
VBA adalah singkatan dari Visual Basic for Applications dan merupakan bahasa pemrograman komputer yang dikembangkan oleh Microsoft, memungkinkan Anda untuk mengotomatiskan tugas yang memakan waktu dan membuat fungsi khusus
Makro
Program atau subrutin bawaan VBA yang dapat dibuat oleh siapa saja. Gunakan perekam makro untuk membuat makro VBA Anda sendiri dengan cepat