3 Cara Rekap Data Tahunan di Excel Secara Otomatis dari Data Harian

Dalam pengolahan data keuangan atau laporan bisnis, kita sering dihadapkan pada data harian yang jumlahnya sangat banyak. Agar lebih mudah dianalisis, data tersebut biasanya direkap menjadi laporan bulanan, triwulanan, atau bahkan tahunan. Salah satu kebutuhan umum adalah cara rekap data tahunan di Excel sehingga kita bisa melihat total penjumlahan untuk tiap tahun secara otomatis.

Pada artikel ini, kita akan membahas 3 cara rekap data tahunan di Excel yang bersumber dari data harian. Contoh tabel bisa dilihat pada gambar terlampir, di mana terdapat kolom Tanggal dan Jumlah (Rp) sebagai sumber data, serta kolom Tahun dan Total s.d. Target sebagai hasil rekap.

Rumus yang digunakan antara lain:

1. =SUMPRODUCT(--(YEAR(E3)=YEAR($B$3:$B$20));$C$3:$C$20)
2. =SUMIFS($C$3:$C$20;$B$3:$B$20;"<="&E3;$B$3:$B$20;">="&DATE(YEAR(E3);1;1))
3. =SUM(FILTER($C$3:$C$20;($B$3:$B$20>=DATE(YEAR(E3);1;1))*($B$3:$B$20<=E3)))

Mari kita bahas satu per satu agar lebih mudah dipahami.

1. Cara Rekap Data Tahunan di Excel dengan Rumus SUMPRODUCT

Sebagai contoh, kita punya tabel penjualan seperti berikut:

Rumus pertama yang bisa digunakan di sel F3 adalah:

=SUMPRODUCT(--(YEAR(E3)=YEAR($B$3:$B$20));$C$3:$C$20)

Rumus ini bekerja dengan cara:

• YEAR($B$3:$B$20) mengambil tahun dari setiap tanggal di kolom B.
• YEAR(E3) mengambil tahun yang menjadi acuan di kolom E.
• Bagian --(YEAR(E3)=YEAR($B$3:$B$20)) akan menghasilkan nilai TRUE/FALSE yang dikonversi menjadi 1 atau 0.
• SUMPRODUCT kemudian menjumlahkan semua data di kolom C (Jumlah) yang memiliki tahun sama dengan tahun di E3.

Dengan metode ini, kita bisa langsung mendapatkan total jumlah per tahun secara otomatis. Berikut hasilnya:

Metode ini sering menjadi pilihan karena fleksibel dan sederhana, sehingga banyak dipakai untuk cara rekap data tahunan di Excel.

2. Cara Rekap Data Tahunan di Excel dengan Rumus SUMIFS

Rumus kedua adalah:

=SUMIFS($C$3:$C$20;$B$3:$B$20;"<="&E3;$B$3:$B$20;">="&DATE(YEAR(E3);1;1))

Penjelasannya:

• SUMIFS digunakan untuk menjumlahkan nilai dengan kriteria tertentu.
• $C$3:$C$20 adalah kolom jumlah yang ingin dijumlahkan.
• $B$3:$B$20;"<="&E3 berarti hanya mengambil data tanggal yang lebih kecil atau sama dengan tahun acuan.
• $B$3:$B$20;">="&DATE(YEAR(E3);1;1) berarti hanya mengambil data tanggal yang lebih besar atau sama dengan 1 Januari tahun tersebut.

Dengan demikian, rumus ini menghitung jumlah data dari 1 Januari hingga 31 Desember tahun tertentu. Berikut hasilnya:

Bagi pengguna Excel yang sering membuat laporan keuangan, metode SUMIFS ini sangat populer karena lebih intuitif dan mudah dipahami. Tidak heran jika banyak orang mencari tutorial tentang cara rekap data tahunan di Excel menggunakan SUMIFS.

3. Cara Rekap Data Tahunan di Excel dengan Rumus FILTER + SUM

Rumus ketiga adalah versi modern yang hanya tersedia di Excel 365 atau Excel 2021 ke atas:

=SUM(FILTER($C$3:$C$20;($B$3:$B$20>=DATE(YEAR(E3);1;1))*($B$3:$B$20<=E3)))

Penjelasan:

• Fungsi FILTER digunakan untuk menyaring data pada kolom C sesuai kriteria tanggal.
• $B$3:$B$20>=DATE(YEAR(E3);1;1) artinya hanya mengambil data mulai dari 1 Januari tahun tersebut.
• $B$3:$B$20<=E3 artinya hanya mengambil data sampai tanggal akhir tahun yang dipilih.
• SUM kemudian menjumlahkan hasil filter tersebut.

Kelebihan metode ini adalah fleksibilitas tinggi. FILTER memungkinkan kita menyaring data dengan berbagai kondisi tanpa perlu membuat kolom tambahan.

Bagi yang sudah menggunakan Excel terbaru, cara ini bisa menjadi alternatif paling efisien untuk cara rekap data tahunan di Excel.

Perbandingan 3 Metode

Ketiga cara di atas sama-sama bisa digunakan untuk melakukan rekap data tahunan. Berikut perbandingannya:

• SUMPRODUCT → Cocok untuk semua versi Excel, ringkas, dan cepat digunakan.
• SUMIFS → Lebih intuitif, mudah dipahami, dan sudah tersedia di Excel 2007 ke atas.
• FILTER + SUM → Hanya tersedia di Excel modern, tetapi paling fleksibel dan elegan.

Dengan mengetahui ketiganya, Anda bisa memilih metode yang paling sesuai dengan kebutuhan. Jika data Anda besar dan kompleks, SUMIFS biasanya lebih aman. Namun, jika Anda sudah menggunakan Excel 365, FILTER akan sangat membantu dalam membuat laporan otomatis.

Kesimpulan

Mengelola data harian menjadi rekap tahunan bukanlah hal yang sulit jika kita memahami rumus-rumus Excel. Pada artikel ini, kita sudah membahas 3 cara rekap data tahunan di Excel, yaitu:

1. Menggunakan SUMPRODUCT.
2. Menggunakan SUMIFS.
3. Menggunakan FILTER + SUM.

Ketiga metode ini mampu menghasilkan laporan otomatis dengan hasil yang sama, hanya berbeda pada cara penulisan dan fleksibilitasnya.

Jika Anda sedang mencari cara rekap data tahunan di Excel untuk laporan keuangan, penjualan, atau target bisnis, maka tiga cara di atas bisa menjadi solusi praktis. Anda hanya perlu menyesuaikan formula dengan struktur data yang dimiliki.

Semoga penjelasan ini membantu Anda lebih memahami cara rekap data tahunan di Excel. Dengan menguasai teknik ini, pekerjaan mengolah data akan jauh lebih cepat, akurat, dan profesional.

Download Template Gratis

Bagi Anda yang ingin mencoba teknik ini secara langsung, kami telah menyiapkan template gratis yang dirancang khusus untuk membantu Anda mempraktikkan 3 Cara Rekap Data Tahunan di Excel Secara Otomatis dari Data Harian ini. File Excel bisa didownload melalui tautan berikut:

Atau juga jika ingin mempelajari langkah demi langkah secara visual, bisa kunjungi Youtube Channel Depot Excel. Di sana teman-teman akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!

—

Tags: cara cepat rekap data tahunan di excel, cara membuat laporan tahunan dengan excel, cara menjumlahkan data per tahun di excel, cara rekap data harian menjadi tahunan di excel, cara rekap data tahunan di excel, contoh rumus rekap data tahunan excel, contoh tabel rekap tahunan excel, formula excel untuk rekap target tahunan, menggunakan filter untuk rekap data tahunan di excel, rekap data tahunan otomatis di excel, rekap penjualan tahunan dengan excel, rumus excel untuk rekap data tahunan, sumifs rekap data tahunan excel, sumproduct untuk rekap data tahunan di excel, tutorial rekap data tahunan di excel.

—