Bagaimana cara menghitung rata-rata mtd di excel?

Dalam contoh ini, sasarannya adalah menghitung rata-rata bulanan untuk jumlah yang ditampilkan di kolom C menggunakan tanggal di kolom B. Artikel di bawah ini menjelaskan dua pendekatan. Satu pendekatan didasarkan pada fungsi AVERAGEIFS, yang didesain untuk menghitung rata-rata menggunakan beberapa kriteria. Pendekatan kedua didasarkan pada fungsi FILTER dan fungsi AVERAGE. Demi kenyamanan saja, kedua solusi menggunakan jumlah rentang bernama (C5. C16) dan tanggal (B5. B16)

Catatan. nilai-nilai di E5. E10 adalah tanggal Excel yang valid, diformat untuk menampilkan nama bulan saja dengan format angka "mmm". Lihat di bawah untuk informasi lebih lanjut

fungsi AVERAGEIFS

Fungsi AVERAGEIFS menghitung rata-rata sel dalam rentang yang memenuhi satu atau beberapa ketentuan, yang disebut sebagai kriteria. Sintaks umum untuk AVERAGEIFS terlihat seperti ini

=AVERAGEIFS(avg_range,range1,criteria1,range2,criteria2,...)

Dalam masalah ini, kita perlu mengonfigurasi AVERAGEIFS ke jumlah rata-rata per bulan menggunakan dua kriteria. (1) tanggal lebih besar atau sama dengan hari pertama bulan itu, (2) tanggal kurang dari hari pertama bulan berikutnya. Kita mulai dengan rentang rata-rata, yang berisi nilai rata-rata dalam data (C5. C16)

=AVERAGEIFS(amount,

Selanjutnya, kita perlu memasukkan kriteria yang diperlukan untuk menargetkan tanggal yang sesuai untuk setiap bulan. Untuk mempermudah langkah ini, nilai di E5. E10 adalah semua tanggal awal bulan yang diformat untuk menunjukkan nama bulan yang disingkat. Untuk memasukkan kriteria tanggal mulai, kami menggunakan rentang tanggal bernama (B5. B16) diikuti oleh operator yang lebih besar dari atau sama dengan (>=)  disambungkan ke sel E5

=AVERAGEIFS(amount,date,">="&E5,

Untuk memasukkan kriteria tanggal akhir, kami menggunakan fungsi EDATE untuk memajukan satu bulan penuh ke hari pertama bulan berikutnya

=EDATE(E5,1) // first of next month

Kami kemudian dapat menggunakan operator kurang dari (<) untuk memilih tanggal yang benar. Rumus terakhir di F5, disalin, adalah

=AVERAGEIFS(amount,date,">="&E5,date,"<"&EDATE(E5,1))

Diterjemahkan secara kasar, arti dari rumus ini adalah "Rata-rata jumlah dalam B6. B16 saat tanggal di C5. C16 lebih besar atau sama dengan tanggal di E5 dan kurang dari hari pertama bulan berikutnya". Perhatikan bahwa kita perlu menggabungkan tanggal ke operator logika, seperti yang diwajibkan oleh fungsi AVERAGEIFS. Saat rumus disalin, ia mengembalikan total setiap bulan di kolom E. Rentang bernama berperilaku seperti referensi absolut dan tidak berubah, sedangkan referensi ke E5 bersifat relatif dan berubah di setiap baris baru

Catatan. kita bisa menggunakan fungsi EOMONTH untuk mendapatkan hari terakhir bulan ini, lalu gunakan "<=" sebagai operator logis kedua. Namun, karena EOMONTH mengembalikan tanggal yang secara teknis tengah malam, ada bahaya mengecualikan tanggal dengan waktu yang terjadi pada akhir bulan. Menggunakan EDATE adalah solusi yang lebih sederhana dan lebih kuat

FILTER dengan RATA-RATA

Cara bagus lainnya untuk menghitung rata-rata per bulan adalah dengan menggunakan fungsi FILTER dengan fungsi AVERAGE seperti ini

=AVERAGE(FILTER(amount,TEXT(date,"mmyy")=TEXT(E5,"mmyy")))

Pada tingkat tinggi, fungsi FILTER mengekstrak jumlah untuk bulan tertentu, dan mengembalikan jumlah ini ke fungsi AVERAGE, yang menghitung rata-rata. Fungsi FILTER dikonfigurasi seperti ini

FILTER(amount,TEXT(date,"mmyy")=TEXT(E5,"mmyy"))

Argumen pertama, array, diatur ke jumlah (C5. C16). Argumen kedua, termasuk, adalah di mana sebagian besar pekerjaan diselesaikan

TEXT(date,"mmyy")=TEXT(E5,"mmyy")

Di sini, kami menggunakan fungsi TEXT untuk mengonversi tanggal menjadi string teks dalam format "myy". Karena ada 12 tanggal dalam daftar, hasilnya adalah array dengan 12 nilai seperti ini

{"0122";"0222";"0222";"0322";"0322";"0322";"0422";"0422";"0422";"0522";"0522";"0522"}

Selanjutnya, fungsi TEXT digunakan dengan cara yang sama untuk mengekstrak bulan dan tahun dari tanggal di E5

TEXT(E5,"mmyy") // returns "0122"

Kedua hasil di atas kemudian dibandingkan satu sama lain. Hasilnya adalah larik nilai TRUE dan FALSE seperti ini

=AVERAGEIFS(amount,

Dalam larik ini, nilai BENAR menunjukkan tanggal di B5. B16 dengan bulan dan tahun yang sama dengan tanggal di E5. Seperti yang Anda lihat, hanya tanggal pertama yang terjadi pada Januari 2022. Fungsi FILTER menggunakan larik ini untuk memilih hanya nilai dalam data yang memenuhi kriteria. Hasilnya dikirim langsung ke fungsi AVERAGE seperti ini

=AVERAGEIFS(amount,

AVERAGE kemudian mengembalikan hasil 100. Saat rumus disalin, FILTER mengirim jumlah setiap bulan ke fungsi AVERAGE, yang mengembalikan hasil akhir

Salah satu fitur bagus dari rumus ini adalah bahwa rumus ini secara otomatis mengabaikan nilai waktu yang mungkin dilampirkan pada tanggal, sehingga tidak perlu khawatir untuk mengecualikan tanggal pada hari terakhir bulan yang menyertakan nilai waktu, seperti pada AVERAGEIFS di atas. Ini karena logika yang dibuat dengan fungsi TEXT hanya membandingkan nilai bulan dan tahun. Sebaiknya gunakan juga fungsi TEXT di dalam rumus AVERAGEIFS, tetapi fungsi AVERAGEIFS tidak akan menerima operasi larik dalam argumen rentang

Tampilkan tanggal sebagai nama

Untuk menampilkan tanggal di E5. E10 sebagai nama saja, Anda dapat menerapkan format angka khusus. Pilih tanggal, lalu gunakan Control + 1 untuk memunculkan kotak Dialog Format Cells dan terapkan format tanggal "mmm" seperti yang ditunjukkan di bawah ini

Ini memungkinkan Anda untuk menggunakan tanggal Excel yang valid di kolom E (diperlukan untuk rumus) tetapi hanya menampilkannya sebagai nama bulan

Solusi Tabel Pivot

Tabel pivot adalah solusi bagus lainnya saat Anda perlu meringkas data berdasarkan tahun, bulan, kuartal, dan seterusnya, karena dapat melakukan pengelompokan semacam ini untuk Anda tanpa rumus sama sekali. Untuk perbandingan rumus vs. tabel pivot, lihat video ini. Mengapa tabel pivot

Bagaimana cara menghitung MTD di Excel?

Apa itu rata-rata MTD?

Bagaimana Anda menghitung rata-rata pergerakan dinamis di Excel?

Bagaimana cara melakukan rolling average 3 bulan di Excel?