Note: The other languages of the website are Google-translated. Back to English

Jumlahkan nilai berdasarkan tahun di Excel

Misalkan, Anda memiliki berbagai data yang berisi daftar tanggal dan jumlah penjualan, sekarang Anda ingin mendapatkan jumlah total berdasarkan tahun tertentu seperti gambar di bawah ini. Di Excel, fungsi SUMIFS dan SUMPRODUCT normal dapat membantu menyelesaikan tugas ini dengan cepat dan mudah. Tutorial ini akan mendemonstrasikan cara menggunakan kedua fungsi ini secara detail.


Jumlahkan nilai berdasarkan tahun di Excel dengan fungsi SUMIFS dan DATE

Untuk menjumlahkan nilai total berdasarkan tahun tertentu, Anda harus menentukan tanggal pertama dan terakhir tahun tersebut dengan menggunakan fungsi DATE, dan kemudian menggunakan fungsi SUMIFS untuk menjumlahkan semua nilai dalam tanggal yang ditentukan, sintaks generiknya adalah:

=SUMIFS(sum_range,date_range,">="&DATE(year,1,1),date_range,"<="&DATE(year,12,31)
  • sum_range: Rentang nilai yang akan dijumlahkan;
  • date_range: Rentang tanggal yang akan diuji untuk tahun tertentu;
  • year: Tahun tertentu Anda ingin menjumlahkan nilai berdasarkan. (tahun, 1,1 menunjukkan hari pertama tahun itu, dan tahun,12,31 menunjukkan hari terakhir tahun ini.)

Untuk menyelesaikan tugas ini di Excel, silakan salin atau masukkan rumus di bawah ini ke dalam sel kosong, lalu seret rumus ke sel lain untuk mendapatkan hasilnya:

=SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31))


Penjelasan dari rumus ini:

=SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31))

  • $ C $ 2: $ C $ 12: adalah “sum_range” yang berisi nilai yang ingin kita jumlahkan.
  • $B$2:$B$12,">="&DATE(E2,1,1): memeriksa tanggal dalam rentang B2:B12 yang lebih besar dari atau sama dengan 01/01/2020 – hari pertama tahun ini di sel E2.
  • $B$2:$B$12,"<="&DATE(E2,12,31): memeriksa tanggal dalam rentang B2:B12 yang kurang dari atau sama dengan 12/31/2020 – hari terakhir dalam setahun di sel E2.
  • SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31)):Setelah memenuhi kriteria, nilai dalam rentang C2:C12 akan ditemukan dan dijumlahkan.

Jumlahkan nilai berdasarkan tahun di Excel dengan fungsi SUMPRODUCT

Di sini, rumus sederhana lain yang dibuat oleh fungsi SUMPRODUCT juga dapat membantu Anda, sintaks generiknya adalah:

=SUMPRODUCT((YEAR(date_range)=year)*sum_range)
  • date_range: Rentang tanggal yang akan diuji untuk tahun tertentu;
  • year: Tahun tertentu Anda ingin menjumlahkan nilai berdasarkan;
  • sum_range: Rentang nilai yang akan dijumlahkan.

Silakan salin atau masukkan rumus berikut ke dalam sel kosong, lalu seret ke bawah rumus ke sel lain untuk mendapatkan hasilnya:


Penjelasan dari rumus ini:

=SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)

  • TAHUN($B$2:$B$12)=E2:Fungsi YEAR mengembalikan nomor tahun dari setiap sel tanggal dalam rentang B2:B12. Ekspresi ini memeriksa apakah tahun di E2 sama dengan tahun dalam rentang B2:B12. Dan Anda akan mendapatkan array seperti ini: {TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}.
  • (YEAR($B$2:$B$12)=E2)*$C$2:$C$12={TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}*$C$2:$C$12: Dalam ekspresi ini, TRUEs dan FALSEs akan dipaksa menjadi 1s dan 0, dan array ini dikalikan dengan setiap jumlah dalam rentang C2:C12, dan Anda akan mendapatkan array baru seperti ini: {347;0;398;430; 0;379;412;0;0;420;0} .
  • SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)= SUMPRODUCT({347;0;398;430;0;379;412;0;0;420;0}): Fungsi SUMPRODUCT akhirnya menjumlahkan semua angka dalam larik ini dan mengembalikan hasilnya sebagai 2386.

Fungsi relatif yang digunakan:

  • SUMIFS:
  • Fungsi SUMIFS di Excel dapat membantu menjumlahkan nilai sel berdasarkan beberapa kriteria.
  • SUMPRODUK:
  • Fungsi SUMPRODUCT bisa digunakan untuk mengalikan dua atau lebih kolom atau larik bersama-sama, lalu mendapatkan jumlah produk.
  • TANGGAL:
  • Fungsi DATE dapat membantu kita untuk menggabungkan nomor tahun, bulan dan hari dari sel yang terpisah ke tanggal yang valid.
  • TAHUN:
  • Fungsi YEAR mengembalikan tahun berdasarkan tanggal yang ditentukan dalam format nomor seri 4 digit.

Artikel lainnya:


Alat Produktivitas Kantor Terbaik

Kutools for Excel - Membantu Anda Menonjol Dari Kerumunan

Apakah Anda ingin menyelesaikan pekerjaan harian Anda dengan cepat dan sempurna? Kutools for Excel menghadirkan 300 fitur canggih yang canggih (Gabungkan buku kerja, jumlahkan berdasarkan warna, pisahkan konten sel, tanggal konversi, dan seterusnya...) dan hemat 80% waktu untuk Anda.

  • Dirancang untuk 1500 skenario kerja, membantu Anda memecahkan 80% masalah Excel.
  • Kurangi ribuan klik keyboard dan mouse setiap hari, hilangkan mata dan tangan Anda yang lelah.
  • Menjadi ahli Excel dalam 3 menit. Tidak perlu lagi mengingat rumus dan kode VBA yang menyakitkan.
  • Uji coba gratis tanpa batas selama 30 hari. Jaminan uang kembali 60 hari. Upgrade dan dukungan gratis selama 2 tahun.
Ribbon of Excel (dengan Kutools for Excel diinstal)

Tab Office - Aktifkan Pembacaan dan Pengeditan dengan Tab di Microsoft Office (termasuk Excel)

  • Satu detik untuk beralih di antara lusinan dokumen terbuka!
  • Kurangi ratusan klik mouse untuk Anda setiap hari, ucapkan selamat tinggal pada tangan mouse.
  • Meningkatkan produktivitas Anda sebesar 50% saat melihat dan mengedit banyak dokumen.
  • Membawa Tab Efisien ke Office (termasuk Excel), Sama Seperti Chrome, Firefox, dan Internet Explorer Baru.
Cuplikan Layar Excel (dengan Office Tab diinstal)
Urutkan komentar berdasarkan
komentar (0)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
Belum ada komentar yang diposting di sini
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan

Ikuti kami

Hak Cipta © 2009 - www.extendoffice.com. | Seluruh hak cipta. Dipersembahkan oleh ExtendOffice. | Peta Situs
Microsoft dan logo Office adalah merek dagang atau merek dagang terdaftar dari Microsoft Corporation di Amerika Serikat dan / atau negara lain.
Dilindungi oleh Sectigo SSL