Prakata
Menetapkan rumus: angka, case, teks, tangal, terbilang, jumlah, dan acuan.
Rumus SUM dan keluarganya adalah,
formula yang saya pakai juga di keseharian,
misalnya di perhitungan penyusutan.
Dari satu contoh data yang sama,
Kita bisa mendapatkan penjumlahan tertentu
dengan cara yang berbeda.
Seperti di gambar berikut:
Daftar Isi
-
Prakata: Daftar Isi
Unduh Berkas
Gratis nih bro!
Lembar kerja spreadsheet tersedia, supaya teman-teman bisa copy-paste.
Excel Engineer
Be Excellent, be an excel engineer!
Kebutuhan orang2 yang mampu membuat form sendiri, membuat pemodelan akunting, maupun model ekonomi, semakin nyata di dunia pekerjaan. Laporan yang bagus dapat menjadi sampah, kalau ditampilkan secara amatiran.
Kita perlu banyak admin, yang bersedia menjadi excel engineer. Menjelajahi sampai dalam. namun memakai secara tepat guna. Kemudian menyuarakan ilmu yang didapatkannya.
Penjumlahan SUM
Kita dapat memulai dari rumus SUM yang sederhana,
misalnya hasil penjumlahan berikut:
Kita dapat melacak dari mana datangnya angka tersebut, dengan melihat kotak di atasnya, dan formula rumus di bawahnya:
=SUM(H5:H16)
Yang ini semestinya cukup jelas.
Penjumlahan SUMIF
Sekarang, misalnya kita mau menjumlahkan untuk nilai tertentu saja, misalnya pembelian pepaya, atau pembelian oleh Fulan, atau pembelian di hari Rabu.
Maka kita dapat menggunakan rumus SUMIF:
=SUMIF(C5:C16;"Pepaya";H5:H16)
=SUMIF(D5:D16;"Fulan";H5:H16)
=SUMIF(E5:E16;"Rabu";H5:H16)Kita ambil satu contoh saja, supaya kita paham dari mana datangnya angka tersebut:
Sayangnya SUMIF ini masih membutuhkan kolom jumlah,
sebagai kolom bantuan.
Dalam perhitungan panjang,
seringkali kita harus menghindari kolom bantuan,
yaitu semacam kolom jumlah tadi.
Penjumlahan SUMPRODUCT
Dengan rumus SUMPRODUCT,
maka kita dapat lebih hemat kolom.
Walaupun hasil akhirnya sama
untuk kasus yang kurang lebih sama.
Sebagaimana berikut, misalnya pembelian pepaya, atau pembelian oleh Fulan, atau pembelian di hari Rabu.
Mari kita tinjau asal usul rumusnya. Kotaknya langsung ke harga dan pembelian. Dan sam sekali mengabaikan kolom jumlah.
=SUMPRODUCT(F5:F16;IF(C5:C16="Pepaya";G5:G16;0))
=SUMPRODUCT(F5:F16;IF(D5:D16="Fulan";G5:G16;0))
=SUMPRODUCT(F5:F16;IF(E5:E16="Rabu";G5:G16;0))
Lebih panjang di rumus, namun lebih hemat di kolom.
Penjumlahan SUMIFS
Masih ada cara lain, yaitu dengan meletakkan beberapa IFS sekaligus.
Ini dapat kita gunakan untuk mencari kriteria.
Misalnya hasil berikut menggunakan rumus SUMIFS:
Mulai dari satu kriteria, dua kriteria, dan seterusnya:
=SUMIFS(H5:H16;C5:C16;"=Pepaya")
=SUMIFS(H5:H16;C5:C16;"=Pepaya";D5:D16;"=Fulan")
=SUMIFS(H5:H16;C5:C16;"=Pepaya";D5:D16;"=Fulan";E5:E16;"=Rabu")Lalu seperti biasa kita lacak dari mana angka tersebut berasal, misalnya untuk dua kriteria, yaitu Pepaya dan Fulan.
Penjumlahan Berdasaran Kriteria
Sekarang kita lihat prakteknya untuk mencari nilai kriteria tertentu dengan cara berbeda:
Yang tiga pertama menggunakan SUMPRODUCT dipadukan dengan IF.
Kita bekerja dengan array.
=SUMPRODUCT(F5:F16;IF(C5:C16=C19;G5:G16;0))
=SUMPRODUCT(F5:F16;IF(D5:D16=D19;G5:G16;0))
=SUMPRODUCT(F5:F16;IF(E5:E16=E19;G5:G16;0))Dan yang terakhir adalah dengan SUMIFS.
=SUMIFS(H5:H16;C5:C16;"="&C19;D5:D16;"="&D19;E5:E16;"="&E19)Mari kita bandingkan keduanya:
SUMIFS ini tetap memakai kolom bantuan.
Sayangnya saya belum berhasil menemukan cara,
untuk menggabungkan SUMPRODUCT dan IFS,
sehingga didapatkan hasil dari beberapa kriteria sekaligus.
Tergantung kebutuhan teman-teman,
kalau dalam keseharian, saya memakai SUMPRODUCT,
dan juga SUMIF, namun tidak menggunakan gabungan keduanya.
Ringkasan Penjumlahan
Sebagai penutup bagian ini maka saya berikan ringkasan.
Bagian atas adalah masukkannya,'
saya berikan alamat cell-nya.
Lalu bagian bawah adalah keluarannya, saya berikan rumusnya untuk memudahkan kawan-kawan.
Tampilannya memang ramai dan berisik. Semoga bermanfaat.
Apa Selanjutnya?
Ada yang lebih seru, yaitu xlookup, switch, index dan kawan-kawan.
Lanjut baca yuk. [Lembar Kerja - Rumus Acuan].