RumusHitung.Com

RumusHitung.com – Halo sobat, apa kabar? Semoga tetap sehat dan semangat yaa.. Pada kesempatan ini, rumushitung akan membahas rumus Excel. Dilihat dari judulnya, ada beberapa rumus gabungan dari fungsi AVERAGE. Langsung saja simak penjelasannya.

Rumus Average (Rata-Rata) dalam Excel

Pada umumnya, Fungsi Excel AVERAGE adalah fungsi yang menghitung rata-rata dari bilangan tertentu. Fungsi ini bisa menangani hingga 225 argumen individu yang bisa mencakup angka, referensi sel, rentang, larik, dan konstanta.

=AVERAGE(number1;[number2];….)

Ada 15 contoh rumus-rumus Excel gabungan dari fungsi AVERAGE, diantaranya :

• Rata-rata dan mengabaikan kesalahan
• Rata-rata per bulan
• Rata-rata jika kriteria tidak kosong
• Rata-rata 5 nilai terakhir
• Rata-rata 5 nilai terakhir dalam kolom
• Angka rata-rata
• Angka rata-rata mengabaikan nol
• Gaji rata-rata per minggu
• Waktu rata-rata per bulan
• Rata-rata 3 nilai numerik terakhir
• Rata-rata 3 skor teratas
• Rata-rata dengan beberapa kriteria
• Rumus rata-rata bergerak
• Harus lulus 4 dari 6 mata pelajaran
• Rata-rata tertimbang

Rata-Rata dan Mengabaikan Kesalahan

Untuk menghitung rata-rata daftar nilai dengan mengabaikan kesalahan yang mungkin ada dalam rentang, bisa menggunakan fungsi AVERAGEIF atau AGGREGATE, seperti yang ditunjukkan pada contoh di atas. Penulisan rumusnya :

=AVERAGEIF(B3:B12;”>=0″)

Fungsi AVERAGEIF bisa menghitung rata-rata data numerik dengan satu atau lebih kriteria. Dalam hal ini, kriterianya adalah ekspresi “> = 0”. Ini memfilter nilai kesalahan, dan AVERAGEIF menghasilkan 91,75 (rata-rata dari delapan nilai yang tersisa).

Rumus alternatif dengan AGGREGATE

Fungsi AGGREGATE juga bisa mengabaikan kesalahan ketika menghitung rata-rata. Untuk menghitung rata-rata dengan fungsi AGGREGATE, bisa menggunakan rumus seperti :

=AGGREGATE(1;6;B3:B12)

Di sini, angka 1 menentukan rata-rata, dan angka 6 adalah opsi untuk mengabaikan kesalahan. Seperti contoh AVERAGEIF di atas, AGGREGATE menghasilkan 91,75 (rata-rata dari delapan nilai yang tersisa).

Rata-Rata per Bulan

Untuk rata-rata per bulan, gunakan rumus berdaasarkan fungsi AVERAGEIFS, dengan bantuan dari fungsi EOMONTH. PAda contoh yang ditunjukkan di atas, penulisan rumus di sel G5 :

=AVERAGEIFS(amounts;dates;”>=”&F5;dates;”<=”&EOMONTH(F5;0))

Rumus ini menggunakan rentang bernama “amounts” (D5:D104) dan “dates” (C5:C104).

Fungsi AVERAGEIFS bisa menghitung rata-rata rentang berdasarkan beberapa kriteria. Pada kasus ini, AVERAGEIFS dikonfigurasi ke jumlah rata-rata berdasarkan bulan menggunakan dua kriteria :
(1) mencocokkan tanggal yang lebih besar dari atau sama dengan hari pertama setiap bulan,
(2) mencocokkan tanggal kurang dari atau sama dengan hari terakhir bulan itu.
Jika memasukkan tanggal untuk Januari 2016 ke dalam rumus menggunakan fungsi DATE, akan terlihat seperti :

=AVERAGEIFS(amounts;dates;”>=”&DATE(2016;1;1);dates;”<=”&DATE(2016;1;31))

Rata-Rata Jika Kriteria Tidak Kosong

Rumus Umum

=AVERAGEIFS(range1;range2;”<>”)

Untuk menghitung rata-rata yang tidak termasuk nilai kosong dalam rentang kriteria, bisa menggunakan fungsi AVERAGEIFS dan peranan logika sederhana. Pada contoh yang ditunjukkan di atas, rumus di sel F4 :

=AVERAGEIFS(C3:C13;D3:D13;”<>”)

Rata-Rata 5 Nilai Terakhir

Untuk meratakan 5 titik data terakhir, bisa menggunakan fungsi AVERAGE bersama dengan fungsi COUNT dan OFFSET, menggunakan pendekatan ini untuk rata-rata titik data N terakhir : 3 hari terakhir, 6 pengukuran terakhir, dll. Pada contoh yang ditunjukkan, rumus di E3 :

=AVERAGE(OFFSET(C2;COUNT(C:C);0;-5))

Rata-Rata 5 Nilai Terakhir dalam Kolom

Untuk meratakan 5 nilai data terakhir dalam rentang kolom, gunakan fungsi AVERAGE bersama dengan fungsi COUNT dan OFFSET. Pada contoh yang ditampilkan, rumus di C3 :

=AVERAGE(OFFSET(D3;0;COUNT(D3:J3)-5;1;5))

Kurang dari 5 nilai

Jika ada kurang dari 5 nilai, rumus akan menghasilkankesalahan referensi melingkar, karena rentang akan diperpanjang kembali ke sel yang berisi rumus. Untuk mencegah kesalahan ini, bisa mengadaptasi rumus seperti :

=AVERAGE(OFFSET(first;0COUNT(range)-MIN(N;COUNT(range));1;MIN(N;COUNT(range))))

Angka Rata-Rata

Untuk memperoleh rata-rata dari satu set angka, gunakan fungsi AVERAGE.

Pada contoh yang ditunjukkan di atas, rumus di sel E3 :

=AVERAGE(B3:D3)

AVERAGE adalah fungsi otomatis di Excel. Pada kebanyakan kasus, rentang sel akan diberikan untuk dirata-ratakan, dan fungsi akan memperoleh hasil. Namun, dapat juga memberikan argumen individual ke fungsi tersebut, jika sel tidak bersebelahan.

Perhatikan bahwa AVERAGE secara otomatis mengabaikan sel dan teks kosong, tetapi nilai nol disertakan.

Angka Rata-Rata Mengabaikan Nol

Untuk memperoleh rata-rata dari satu set angka yang tidak termasuk atau mengabaikan nilai nol, gunakan fungsi AVRAGEIF. Pada contoh yang dtunjukkan di atas, rumus di sel E4 :

=AVERAGEIF(B4:D4;”<>0″)

Kriteria yang diberikan adalah “<> 0” yang artinya “tidak sama dengan nol”.

Pada contoh yang ditunjukkan, rumus dalam E3 didasarkan pada fungsi AVERAGE :

=AVERAGE(B3:D3) / / menghasilkan 60

Penjabaranya seperti, (90 + 90 + 0)/3 = 60

Nilai data kosong

Fungsi AVERAGE, AVERAGEIF, dan AVERAGEIFS semuanya secara otomatis mengabaikan sel kosong (dan sel yang berisi nilai teks), jadi tidak perlu memberikan kriteria untuk memfilter sel kosong.

Gaji Rata-Rata per Minggu

Untuk menghitung gaji rata-rata per minggu yang tidak termasuk minggu di mana tidak ada jam yang dicatat, dan tanpa total pembayaran per minggu sudah dihitung, bisa menggunakan rumus berdasarkan fungsi SUMPRODUCT dan COUNTIF. Pada contoh yang ditampilkan, rumus di J3 :

=SUMPRODUCT(D3:I3*D4:I4)/COUNTIF(D3:I3;”>0″)

Yang menghasilkan gaji rata-rata per minggu yang tidak termasuk minggu di mana tidak ada jam yang dicatat. Ini adalah rumus array, tetapi tidak perlu masuk dengan control + shift + enter karena fungsi SUMPRODUCT secara native dapat menangani sebagian besar operasi array.

Waktu Rata-Rata per Bulan

Untuk waktu rata-rata per bulan, gunakan rumus berdasarkan fungsi AVERAGEIFS, bersama dengan fungsi EOMONTH.

Catatan : Dari contoh gambar di atas adalah versi Excel bawaan dari komputer.

Pada contoh yang ditunjukkan, rumus di G5 :

=AVERAGEIFS(duration,dates,”>=”&F5,dates,”<=”&EOMONTH(F5,0))

Rumus ini menggunakan rentang bernama “tanggal” (B5: B25) dan “durasi” (D5: D25). Kolom durasi D dalam menit, dihitung dengan mengurangkan tanggal buka dari tanggal tutup.

Fungsi AVERAGEIFS dirancang untuk rentang rata-rata berdasarkan beberapa kriteria. Dalam kasus ini, kami mengonfigurasi AVERAGEIFS ke durasi rata-rata menurut bulan menggunakan dua kriteria :
(1) Mencocokkan tanggal yang lebih besar atau sama dengan hari pertama bulan itu.
(2) Mencocokkan tanggal kurang dari atau sama dengan hari terakhir bulan itu.

Untuk mengurung tanggal menurut bulan, gunakan trik sederhana untuk mempermudah : Di kolom F, alih-alih mengetik nama bulan (“Jan”, “Feb”, Mar “, dll.), menambahkan tanggal sebenarnya untuk tanggal pertama setiap bulan (1/1/2016, 2/1/2016, 3/1/2016, dll.), Kemudian, gunakan format tanggal kustom (“mmm”) untuk menampilkan nama bulan.

Ini membuatnya jauh lebih mudah untuk membangun kriteria yang dibutuhkan untuk AVERAGEIFS menggunakan nilai di kolom F. Untuk mencocokkan tanggal yang lebih besar dari atau sama dengan tanggal pertama bulan itu, bisa menggunakan :

“>=”&F5

Untuk mencocokkan tanggal kurang dari atau sama dengan hari terakhir setiap bulan, bisa menggunakan :

“<=”&EOMONTH(F5;0)

Didapatkan EOMONTH untuk menghasilkan hari terakhir di bulan yang sama dengan memberikan nol untuk argumen bulan.

Solusi Tabel Pivot

Tabel pivot adalah solusi yang sangat baik saat Anda perlu meringkas atau rata-rata data berdasarkan tahun, bulan, kuartal, dan seterusnya, karena tabel pivot menyediakan kontrol untuk mengelompokkan tanggal secara otomatis.

Rata-Rata 3 Nilai Numerik Terakhir

Untuk menghitung rata-rata 3 nilai numerik terakhir dalam suatu rentang, bisa menggunakan rumus larik berdasarkan kombinasi fungsi untuk memasukkan n nilai numerik terakhir ke dalam fungsi AVERAGE. Dalam contoh yang diperlihatkan, rumus di D4 :

=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(B3:B11);ROW(B3:B11));{1;2;3});ROW(B3:B11);B3:B11)) / / versi Excel 365

Untuk versi Ms Office Excel bawaan :

{=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(B3:B11),ROW(B3:B11)),{1,2,3}),ROW(B3:B11),B3:B11))} / / versi Ms Office Excel bawaan

Catatan : untuk versi Excel ini, harus tekan Ctrl + Shift + Enter saat setelah memasukkan rumus.

Rata-Rata 3 Skor Teratas

Untuk mendapatkan rata-rata skor 3 teratas dalam kumpulan data, bisa menggunakan rumus berdasarkan fungsi LARGE dan AVERAGE. Pada contoh yang ditunjukkan, rumus di I3, kemudian disalin ke bawah :

=AVERAGE(LARGE(B3:F3;{1;2;3}))

Fungsi LARGE dirancang untuk mengambil nilai n teratas dari serangkaian angka. Jadi, misalnya =LARGE(A1: A10;1) akan menghasilkan nilai tertinggi, =LARGE(A1: A10;2) akan menghasilkan nilai tertinggi ke-2, dan seterusnya :

LARGE(range;1) / / nilai tertinggi ke-1
LARGE(range;2) / / nilai tertinggi ke-2
LARGE(range3) / / nilai tertinggi ke-3

Pada kasus ini, diminta lebih dari satu nilai dengan meneruskan konstanta array {1,2,3} ke LARGE sebagai argumen kedua. Hal ini menyebabkan LARGE menghasilkan hasil larik yang menyertakan 3 nilai tertinggi. Penjabaran pada contoh rumus di sel I3 :

LARGE(B3:F3;{1;2;3})

Maka, hasil LARGE adalah :

{10;10;10}

HAsil LARGE kemudian dicarikan rata-rata dengan menggunakan fungsi AVERAGE :

=AVERAGE({10;10;10}) / / menghasilkan 10

Rata-Rata dengan Beberapa Kriteria

Untuk menghitung rata-rata angka berdasarkan beberapa kriteria, gunakan fungsi AVERAGEIFS. Pada contoh yang ditunjukkan, rumus di I3 :

=AVERAGEIFS(D3:D12;B3:B12;F3;C3:C12;G3)
atau
=AVERAGEIFS(D3:D12;B3:B12;”A”;C3:C12;”Timur”)

Rumus Rata-Rata Bergerak

Untuk menghitung rata-rata bergerak atau bergulir, gunakan rumus sederhana berdasarkan fungsi AVERAGE dengan referensi relatif. Pada contoh yang ditampilkan, rumus di E6 :

=AVERAGE(C4:C6)

Ketika hasil rumus disalin, rumus menghitung rata-rata akan bergerak 3 hari berdasarkan nilai penjualan untuk hari ini dan dua hari sebelumnya.

Di bawah ini adalah opsi yang lebih fleksibel berdasarkan fungsi OFFSET yang menangani periode variabel.

Tentang rata-rata bergerak

Rata-rata bergerak (juga disebut rata-rata bergulir) adalah rata-rata berdasarkan sub kumpulan data pada interval tertentu. Menghitung rata-rata pada interval tertentu memperlancar data dengan mengurangi dampak fluktuasi acak. Ini mempermudah untuk melihat tren secara keseluruhan, terutama dalam grafik. Semakin besar interval yang digunakan untuk menghitung rata-rata bergerak, maka semakin banyak pemulusan yang terjadi, karena semakin banyak titik data yang disertakan dalam setiap rata-rata yang dihitung.

Pada contoh tang ditampilkan di atas, rata-rata 3 hari, 5 hari, dan 7 hari dihitung dengan cara yang sama. Pada setiap kasus, rentang yang disediakan AVERAGE diperbesar untuk menyertakan jumlah hari yang dibutuhkan :

=AVERAGE(C4:C6) / / rata-rata dalam 3 hari
=AVERAGE(C4:C8) / / rata-rata dalam 5 hari
=AVERAGE(C4:C10) / / rata-rata dalam 7 hari

Harus Lulus 4 dari 6 Mata Pelajaran

Untuk menghasilkan “LULUS” jika ada 4 mata pelajaran yang memiliki skor kelulusan, dan “GAGAL” jika tidak, bisa menggunakan rumus berdasarkan fungsi IF dan COUNTIF. Pada contoh yang ditunjukkan, rumus di I3 :

=IF(COUNTIF(C3:H3;”>=70″)>=4;”LULUS”;”GAGAL”)

Dimana niai lebih dari 70 mewakili nilai kelulusan untuk semua data.

Rata-Rata Tertimbang

Untuk menghitung rata-rata tertimbang, Anda bisa menggunakan fungsi SUMPRODUCT bersama dengan fungsi SUM. Dalam contoh yang ditunjukkan, rumus di G3, kemudian disalin ke bawah :

=SUMPRODUCT(I$3:K$3;C3:E3)/SUM(I$3:K$3)

Di lembar kerja yang diperlihatkan, skor untuk 3 tes muncul di kolom C hingga E, dan bobot ada di kisaran bobot bernama (I3:K3). Rumus di sel G3 :

=SUMPRODUCT(I&3:K$3;C3:E3)/SUM(I$3:K$3)

Bekerja dari dalam ke luar, pertama-tama kami menggunakan fungsi SUMPRODUCT untuk mengalikan bobot dengan skor yang sesuai dan menjumlahkan hasilnya :

=SUMPRODUCT(I$3:K$3;C3:E3) / / menghasilkan 88,3

Hasilnya kemudian dibagi dengan jumlah bobot :

=88,3/SUM(I$3:K$3)
=88,3/1
=88,3

Saat rumus disalin ke kolom G, kisaran bobot I3:K3 diberi lambang dollar ($) supaya saat disalin ke bawah tidak berubah. Namun, skor di C3:E3, dimasukkan sebagai referensi relatif, dan saat disalin ke bawah akan berubah. Hasilnya adalah rata-rata tertimbang untuk setiap nama dalam daftar seperti yang ditunjukkan. Rata-rata di kolom F dihitung untuk referensi hanya dengan fungsi AVERAGE :

=AVERAGE(C3:E3)

Demikian penjelasan mengenai gabungan rumus AVERAGE dalam Excel, semoga bermanfaat. Sekian terima kasih.