Saturday, December 31, 2016

Cara Menghilangkan Spasi Berlebih Pada Text

Rumus TRIMBagaimana cara menghilangkan kelebihan spasi pada bagian awal, bagian tengah dan bagian akhir text? Dalam beberapa kasus, mungkin kita bisa menghapus spasi yang tidak diperlukan hanya cukup dengan menggunakan Fungsi TRIM.  Akan tetapi dalam prakeknya tidak selamanya Rumus Excel TRIM dapat memberikan hasil yang diharapkan. Kenapa? Silahkan dibaca selanjutnya.

Spasi berlebih pada data excel biasanya sering dijumpai terutama jika kita sering bekerja dengan data yang di export/import dari aplikasi lain. Meskipun demikian tidak menutup kemungkinan , baik dengan sengaja ataupun tidak, kita menambahkan sendiri spasi pada saat mengetik di excel.

Menghilangkan Spasi Berlebih Menggunakan Fungsi TRIM


Cara yang paling umum digunakan untuk menghapus spasi berlebih yang tidak diperlukan adalah menggunakan fungsi TRIM. Syntax fungsi ini sangat sederhana, dengan satu argumen text.

=TRIM(text)

Argumen text, bisa terdiri atas angka, huruf maupun symbol-symbol.

Contoh:

Anggaplan kita memiliki text  " Kota     Bandung  "

Text tersebut memiliki spasi berlebih yang tidak diperlukan pada bagian awal, bagian tengah antara dua kata, maupun bagian akhir.

Bagian awal ada satu spasi, dan sebenarnya tidak diperlukan
Bagian Tengah ada lima spasi, dan sebenarnya cukup  satu saja yang diperlukan
Bagian akhir ada dua spasi, dan sebenarnya semuanya tidak diperlukan.

Jadi totalnya ada 7 spasi yang tidak diperlukan. Nah kelebihan spasi ini bisa dihilangkan menggunakan rumus excel TRIM.

=TRIM(" Kota     Bandung  ")

Atau jika text disimpan di sel B2

=TRIM(B2)

Hasil = "Kota Bandung"

Berhasil! Spasi berlebih pun dapat dihapus, baik pada bagian awal, tengah maupun akhir.

Fungsi Excel TRIM


Catatan: 
Pada screenshot diatas, saya menggunakan tanda kurung [] untuk memvisualkan batas spasi pada bagian awal dan akhir text, sehingga nampak secara visual perubahannya sebelum dan sesudah TRIM. Sedangkan rumus LEN digunakan untuk mengetes jumlah karakter sebelum dan sesudah TRIM


Sudah Menggunakan Rumus TRIM, Tetapi Spasi Berlebih Tetap Tidak Berkurang.


Nah untuk kasus ini, barangkali anda pernah mengalaminya. Apa penyebabnya? Kenapa rumus TRIM tidak bekerja.

Contohnya:  silahkan dicopy rumus berikut ke dalam lembar kerja excel. Jangan mengetik rumus sendiri ya. Cukup copykan saja rumus di bawah ini.

=TRIM(" Kota     Bandung  ")

Perhatikan hasilnya.. 

Atau lebih jelasnya perhatikan contoh dalam screenshot berikut:

Rumus EXCEL TRIM Tidak Bekerja


Rumus TRIM tidak bekerja, sehingga spasi  berlebih pun tidak berkurang, kenapa?

Ini jawabannya.

Silahkan ganti fungsi TRIM menjadi CODE, atau copy rumus berikut ke sel excel dan lihat hasilnya.

=CODE(" Kota     Bandung  ")
Hasil = 160

Sekarang ambil contoh rumus TRIM yang berhasil sebelumnya dan ganti TRIM menjadi CODE, atau copykan saja rumus berikut ke excel

=CODE(" Kota     Bandung  ")
Hasil = 32

Rumus secara visual nampak sama, namun memberikan hasil yang berbeda. Lalu penyebabnya apa?

Ternyata ada beberapa jenis spasi, diantaranya adalah spasi dengan code 32 atau CHAR(32) , dan spasi dengan code 160 atau CHAR(160) atau nonbreaking space.

Dan ternyata lagi, fungsi TRIM hanya dapat bekerja pada CHAR(32), tetapi tidak pada CHAR(160)

Solusinya bagaimana untuk menghilangkan spasi CHAR(160) yang tidak diperlukan.

Untuk kasus ini kita dapat menggunakan bantuan fungsi SUBSTITUTE untuk merubah CHAR(160) menjadi CHAR(32) sebelum dilakukan TRIM. Sehingga rumusnya menjadi:

=TRIM(SUBSTITUTE(" Kota     Bandung  ",CHAR(160),CHAR(32)))

atau 

=TRIM(SUBSTITUTE(" Kota     Bandung  ",CHAR(160)," "))


Jika contoh text disimpan di sel B2, maka rumusnya menjadi:

=TRIM(SUBSTITUTE(B2,CHAR(160),CHAR(32)))

atau

=TRIM(SUBSTITUTE(B2,CHAR(160)," "))


Untuk lebih jelasnya, perhatikan screenshot berikut:

Solusi Rumus Excel TRIM Tidak Bekerja

Ringkasan:
Umumnya kita bisa menghilangkan spasi berlebih dengan cukup menggunakan fungsi TRIM. Dan untk kasus tertentu jika spasi berlebih berupa nonbreaking space atau CHAR(160) maka spasi berlebih tersebut harus dirubah terlebih dahulu menjadi spasi biasa atau CHAR(32) sebelum proses TRIM.

Semoga bermanfaat.
Belajar excel !  Excellent !

Artikel terkait:



Wednesday, December 28, 2016

Fungsi LOWER, UPPER dan PROPER

Rumus Fungsi LOWER UPPER PROPERPembaca yang budiman… Pada saat bekerja menggunakan microsoft exel, terkadang kita perlu melakukan perubahan huruf kapital maupun huruf kecil. Apalagi jika kita bekerja dengan anggota team yang kadang berbeda preferensinya dalam penggunaan huruf kecil/besar, dan pada akhirnya diperlukan penyeragaman format. Nah,  Apa yang biasanya anda lakukan untuk merubah huruf besar/kecil pada excel? Mudah-mudahan anda sudah menerapkan cara yang efektif untuk melakukan ini. Salah satu cara yang dapat dilakukan adalah menggunakan fungsi LOWER, UPPER dan PROPER yang diterapkan pada rumus / formula excel. 


  • LOWER  :  Merubah semua huruf dalam text menjadi huruf kecil
  • UPPER  :  Merubah semua huruf dalam text menjadi huruf kapital
  • PROPER : Menjadikan semua huruf awal dalam setiak kata dalam kalimat menjadi huruf kapital, sedangkan huruf lainnya menjadi huruf kecil.


Syntax dan cara penggunaan fungsi LOWER, UPPER dan PROPER pun tidak rumit, bahkan dapat dikatakan sangat sederhana dan sangat mudah. Ketiga fungsi tersebut memiliki syntax yang serupa dengan satu argumen text.

=LOWER(text)
=UPPER(text)
=PROPER(text)

Contoh  Rumus LOWER, UPPER dan PROPER.


Anggaplah, kita memiliki text “Saya sedang belajar excel”. Bagaimana jadinya jika text tersebut dijadikan argumen dalam fungsi LOWER, UPPER dan PROPER.

Silahkan untuk dicoba contoh-contoh rumus sederhana berikut dan perhatikan hasilnya.

Rumus : =LOWER("Saya sedang belajar excel")
Hasil  : saya sedang belajar excel

Rumus : =UPPER("Saya sedang belajar excel")
Hasil : SAYA SEDANG BELAJAR EXCEL

Rumus : =PROPPER("Saya sedang belajar excel")
Hasil : Saya Sedang Belajar Excel

Jika text yang akan kita sesuaikan type hurufnya tersebut ditempatkan pada sel, maka kita pun dapat membuat rumus yang menggunakan referensi sel.

Misalnya jika text terdapat pada sel A2,  maka rumusnya dapat dituliskan sebagai berikut:

=LOWER(A2)
=UPPER(A2)
=PROPER(A2)

Begitu mudah dan sederhana bukan?

Secara praktis biasanya rumus ini digunakan untuk merubah type huruf dalam list data text menjadi type huruf yang lebih seragam dan sesuai.

Bonus: merubah huruf besar/kecil menggunakan VBA


Cara ini dapat digunakan terutama jika anda menginginkan cara yang lebih cepat untuk melakukan perubahan type huruf besar/kecil.

Fungsi dalam VBA yang dapat digunakan untuk merubah type huruf besar/kecil adalah LCase, UCase dan StrConv.

Bagi yang masih baru dengan VBA, jendela VBA dapat dimunculkan dengan shoft cut  ALT+F11, kemudian pada jendela VBA editor, klik menu Insert , dilanjutkan klik Module untuk membuat module standar baru.

Silahkan ketik contoh code berikut atau copy kedalam module standar VBA excel.


Menjadikan semua huruf kecil dalam range terseleksi

Sub ubahHurufKecil()
Dim sel As Range
For Each sel In Selection
    sel.Value = LCase(sel.Value)
Next
End Sub


Menjadikan semua huruf Kapital dalam range terseleksi

Sub ubahHurufBesar()
Dim sel As Range
For Each sel In Selection
    sel.Value = UCase(sel.Value)
Next
End Sub


Menjadikan huruf kapital pada setiap awal kata dan sisanya huruf kecil dalam range yang dipilih.

Sub ubahHurufProper()
Dim sel As Range
For Each sel In Selection
    sel.Value = StrConv(sel.Value, vbProperCase)
Next
End Sub


Buatlah shortcut untuk masing-masing code tersebut, maka anda dapat merubah type huruf dengan cepat pada range yang diseleksi dalam lembar kerja excel.


Ringkasan.
Merubah huruf besar/kecil pada microsoft excel dapat dilakukan menggunakan rumus dan fungsi LOWER, UPPER, PROPER. Untuk melakukan perubahan type hurur besar/kecil sekaligus pada range yang diseleksi tanpa menggunakan rumus adalah dengan memanfaatkan VBA. Fungsi VBA yang dapat digunakan untuk tugas ini adalah LCase, Ucase dan StrConv.

Demikian semoga bermanfaat.

Baca juga artikel belajar excel lainnya:



Cara Menghitung Jumlah Huruf Pada Excel

Untuk menghitung jumlah huruf atau karakter dalam microsoft excel , kita dapat menggunakan fungsi LEN. Secara sederhana rumus LEN hanya dapat menghitung jumlah karakter dalam satu sel saja. Supaya fungsi LEN dapat menghitung banyaknya semua karakter dalam sekumpulan sel, maka rumus tersebut harus dibuat dalam bentuk rumus array atau menggunakan bantuan kombinasi fungsi SUMPRODUCT.

Syntax dan cara penggunaan fungsi LEN:

Syntax fungsi LEN sangat sederhana  yaitu:

=LEN(text)

Argumen yang digunakan sangat jelas yaitu berupa text, termasuk string kosong "" dan sel kosong (blank)

Contoh

Untuk menghitung jumlah karakter dalam text ="saya orang indonesia" , maka dapat menggunakan rumus sebagai berikut:

=LEN("saya orang indonesia")

Atau jika text yang akan dihitung jumlah karakternya tersebut terletak dalah referensi sel, anggaplah sel A2, maka rumusnya menjadi seperti di bawah ini:

=LEN(A1)

Untuk lebih jelasnya, perhatikan gambar berikut:

Rumus Excel LEN

Wow… Begitu simple dan mudah.

Menghitung Semua Karakter Dalam Range Sel


Selanjutnya bagaimana cara menghitung jumlah semua karakter dan huruf yang terdapat dalam sekumpulan sel?

Untuk kasus tersebut, kita tetap dapat menggunakan fungsi LEN, tetapi dikombinasikan fungsi SUM dan dibuat dalam bentuk rumus array (CSE), atau dapat juga dikombinasikan dengan fungsi SUMPRODUCT.

Anggaplah, sekumpulan text yang akan dihitung terletak pada range A2:A5
Maka rumus untuk menghitung semua karakter dalam range sel A2:A5 dapat menggunakan rumus berikut:

{=SUM(LEN(A2:A5))}

Ingat : tanda kurung kurawal {} dalam rumus diatas jangan diketikan secara langsung. Rumus tersebut merupakan contoh rumus array CSE, sehingga harus menekan CTR+SHIFT+Enter setiap kali selesai mengetik atau mengedit rumus.

Dan sebagai mana kita ketahui pada artikel sebelumnya perihal fungsi SUMPRODUCT,  bahwa banyak tugas excel yang dapat dilakukan oleh rumus ARRAY, juga dapat diselesaikan menggunakan SUMPRODUCT, termasuk dalam kasus untuk menghitung banyaknya huruf dalam sekumpulan sel.
Inilah rumusnya:

=SUMPRODUCT(LEN(A2:A5))

Berikut penampakan contoh kedua rumus tersebut (Array CSE dan SUMPRODUCT) dalam spreadsheet.

Rumus Excel LEN Menghitung Karakter Range


Cukup Mudah bukan?


Cara Menghitung Jumlah Karakter Tertentu Saja


Selanjutnya, Bagaimana cara menghitung jumlah karakter tertentu saja, misalnya menghitung huruf “O” dalam text “Saya Orang Indonesia”, tanpa membedakan huruf besar atau kecil.

Anggaplah text “Saya Orang Indonesia” ditempatkan pada sel A2, dan sample karakter disimpan di sel A4.

Maka untuk menghitung jumlah huruf “O” dapat menggunakan rumus berikut:

=LEN("saya orang indonesia")-LEN(SUBSTITUTE("saya orang indonesia","o",""))

Atau jika menggunakan referensi, rumusnya dapat ditulis seperti berikut

=LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),UPPER(A4),""))

Untuk lebih jelasnya, perhatikan kenampakan contoh rumus dalam screenshot berikut:


Rumus Excel LEN Menghitung Huruf Tertentu



Barangkali anda bertanya-tanya, kenapa untuk rumus yang menggunakan referensi, ada tambahan fungsi UPPER.

Nah yang ini pun bagi saya masih menjadi tanda tanya…

Fungsi SUBSITUTE ternyata tidak membedakan huruf kecil atau besar, jika text dituliskan secara langsung dalam rumus.

Tetapi jika menggunakan sel referensi, ternyata fungsi SUBSTITUTE ini menganggap beda antara huruf “O” dengan “o” atau huruf kapital dengan huruf kecil.

Nah, untuk mengakalinya maka digunakan fungsi UPPER untuk merubah semua text menjadi huruf besar semua. Sebagai alternative, bisa juga menggunakan fungsi LOWER yaitu untuk merubah semua huruf menjadi huruf kecil semua.


Ringkasan:
  • Dalam microsoft excel kita bisa menghitung jumlah karakter atau huruf dalam text, baik text yang terletak dalam satu sel saja, maupun dalam sekumpulan sel .
  • Untuk menghitung jumlah karakter tersebut dapat digunakan fungsi LEN dengan referensi sel tunggal. Jika referensi kumpulan sel, maka dapat digunakan rumus array CSE  ataupun menggunakan kombinasi fungsi SUMPRODUCT dan LEN. 
  • Kombinasi LEN dan SUBSTITUTE dapat digunakan untuk menghitung banyaknya karakter dan huruf tertentu dalam sel.

Semoga bermanfaat.
Salam

Artikel Terkait:
Pengolah Informasi Text Excel
Belajar Rumus Array Untuk Pemula
Rumus Excel SUMPRODUCT - Satu Fungsi Multi Guna

Tuesday, December 27, 2016

Kenapa Header Kolom Berubah Menjadi Angka.

Masalah mengapa header kolom excel berubah dari huruf menjadi angka, mungkin hanyalah sebuah masalah sepele bagi sebagian pengguna excel. Namun jika anda mengetikan kata kunci ini di pencarian google, maka kemungkinan besar alasanya adalah salah satu diantara dua hal berikut:

Pertama: Anda belum mengetahui penyebabnya dan sedang mencari penjelasan serta cara mengatasinya.

Atau kedua: Anda menulis artikel blog dengan niche excel macam saya ini.... 😉

Terlepas dari itu semua, kenyataanya memang masih ada user yang belum tahu penyebabnya. Dan lebih parahnya lagi ada yang menganggap ini sebagai ulah virus.

Hm… Prasangka yang berlebihan, memang.

Ini dia penampakan header kolom yang berubah menjadi angka.


huruf header kolom excel menjadi angka


Penyebabnya:

Tenang , jangan khawatir dan tidak perlu berprasangka yang engga-engga. Header kolom excel yang berubah dari huruf menjadi angka hanyalah sebagai efek dari pengaturan excel yang menggunakan R1C1 Reference Style. Dan memang demikianlan mode R1C1 bekerja.

Lho… Kenapa tiba-tiba bisa di setting seperti itu.

Jika anda merasa tidak melakukan setting R1C1, lalu siapa tersangkanya?

Mungkin ada seseorang yang melakukan R1C1 Reference Style.

Kok bisa ya, padahal komputer tidak pernah digunakan orang lain?

Ya karena untuk merubah setting, seorang user excel berpengalaman tidak perlu mengerjakannya sendiri. Ia bisa memanfaatkan user lain yang mungkin tidak sadar melakukannya. 

Cukup sisipkan code VBA pada proses tertentu, dan user yang tidak tahu akan cukup kebingungan dibuatnya. 

Codenya sebenarnya singkat saja, seperti ini:

Application.ReferenceStyle = xlR1C1


Bagaimana mengembalikan ke setting semula.


Saya kira jawabannya pun sudah sangat jelas. Untuk merubah setting  semula maka mode R1C1 Reference Style harus di non-activekan.

Bagaimana caranya?

Mengatur Reference Style pada Excel 2003.

Klik menu tools, kemudian klik Options..
Pada form Options, masuk ke tab General
Hilangkan centang pada checkbox R1C1 Reference Style
Klik Ok

Pengaturan R1C1 Reference Style


Mengatur Reference Style Pada Excel 2007, 2010, 2013 dan 2016

  • Excel 2007 : klik office button yang terletak di pojok kiri atas, kemudian klik Excel Options
  • Excel 2010, 2013 dan 2016 : dari tab File, klik Options
  • Masuk ke bagian formulas dan hilangkan centang pada checkbox R1C1 Reference Style.


Setting Reference Style Excel 2010,2013,2016

  • Terakhir, klik OK.


Mengatur Reference Style Menggunakan Makro

Dengan menggunakan makro kita juga bisa mengembalikan mode Referense Style pada kondisi standar (mode A1), Cukup ketikan code berikut pada modul VBA standar:

Sub setRefA1
      Application.ReferenceStyle=xlA1
End sub

Ups, code diatas hanya sebagai contoh. Lupakan saja jika anda tidak memerlukannya.


Ringkasan:

Demikian penjelasan kenapa header kolom excel berubah dari huruf menjadi angka. Untuk mengembalikan pengaturan pun tidak terlalu sulit, baik pada excel 2003, maupun versi 2007 dan versi excel yang lebih tinggi. Cukup masuk ke Option, dan hilangkan centang pengaturan R1C1 Reference Style.

Semoga bermanfaat.
Salam



Monday, December 26, 2016

Cara Display Formula Pada Excel

Belajar Excel : Menampilkan Formula - Rumus
Pembaca yang budiman, jika kita bekerja dengan banyak formula atau rumus yang saling terkait pada microsoft excel, maka adakalanya kita perlu melakukan evaluasi dan pengecekan kembali formula-formula tersebut. Salah satu cara yang bisa dilakukan adalah dengan melihat formula secara keseluruhan. Untuk itu, maka  belajar excel kali ini dikhususkan untuk membahas bagaimana caranya menampilkan/display keseluruhan formula pada lembar kerja excel. Selanjutnya akan dibahas juga penjelasan singkat bagaimana mengeprint formula. Pada bagian akhir akan dijelaskan beberapa hal yang menyebabkan sel excel menampilkan formula, bukan hasil rumus.

Display Formula Pada Excel 2003
Display Formula Pada Exel 2007,2010,2013 dan 2016
Shortcut Display Formula Untuk Semua Versi Excel
Bagaimana Cara Print Formula Pada Excel
Kenapa Excel Menampilkan Formula, Bukannya Hasil

Cara Display Formula Pada Excel 2003


Untuk excel 2003 bisa dilakukan melalui menu Tools.


  • Klik Tools dan kemudian  klik Options..
  • Setelah mumcul form Options, kemudian masuk ke tab View
  • Centang Formulas
  • Klik OK


Cara Display Formula/Rumus Excel 2003


Setelah langkah-langkah tersebut, maka selanjutnya kita akan melihat tampilan formula pada lembar kerja excel.

Langkah diatas “mungkin” berlaku juga pada versi yang lebih lama. Maaf, karena saya belum mengecek versi excel dibawah versi 2003  😅


Cara Display Formula Pada Excel 2007,2010,2013 dan 2016


Untuk excel  versi ini,  formula bisa didisplay  melalui ribbon dan bisa juga melalui Option

Melalui Ribbon

  • Masuk ke tab Formulas, dan kemudian klik tombol Show Formulas


Menampilkan Formula Excel 2007,2010,2013,2016



Melalui Excel Options

  • Untuk excel 2007 : Klik office button yang terletak di pojok kiri atas jendela excel, kemudian klik Excel Options
  • Untuk excel 2010,2013 dan 2017: Klik Tab File, kemudian klik Options
  • Masuk ke bagian Advanced
  • Scroll ke bawah sehingga dijumpai Display Options For Worksheet
  • Centang cekbox Show formula in cells instead of their calculated results



Cara Menampilkan Rumus Excel 2007,2010,2013,2016


  • Kemudian klik OK
  • Maka kita akan mendapati display formula di lembar kerja excel.

Shortcut Excel Untuk Menampilkan Mode Display Formula Atau Sebaliknya Dengan Cepat


Cara tercepat dan sangat dianjurkan untuk menampilkan mode display formula atau sebaliknya adalah menggunakan shortcut keyboard. 

Adapun key  yang digunakan sebagai shortcut adalah CTR dan `

shortcut excel display formula


Cobalah: Tekan tombol CTR + ` secara  bersamaan berulang kali. Perhatikan perubahan tampilan pada lembar kerja excel. Kita akan mendapati tampilan mode display formula, dan display value secara bergantian.

Terbukti: Display formula/value sangat cepat, mudah dan tidak ribet menggunakan shortcut.


Cara Print Formula Pada Excel


Untuk mengeprint formula pada excel sebenarnya tidak sulit. Cukup masuk ke mode display formula dengan  salah satu cara yang sudah dijelaskan sebalumnya. Kemudian print seperti biasa.


Kenapa Sel Menampikan Formula, bukan Nilai Hasilnya.


Ada beberapa hal yang menyebabkan sel pada lembar kerja exel menampilkan formula, bukan hasilnya.

1. Mode Display Formula diaktifkan.

Ini sesuai penjelasan pada bagian atas. Untuk mengatasinya pun cukup mudah. Cukup dengan cara nonaktifkan mode display formula melalui 3 cara-cara yang sudah dijelaskan sebelumnya

2. Format sel = Text.  

Ini biasanya dialami ketika user mengetikan sebuah rumus excel pada sebuah sel. Kemudian menekan enter, tetapi sel malah menampilkan rumus sesuai yang diketik tadi. Padahal yang diharapkan adalah hasil kalkulasi dari rumus dalam sel tersebut.

Solusinya: Ubah format sel ke format general atau format numerik seperti number, dan acounting dan sebagainya.

3. Menggunakan tanda spasi atau tanda ‘ (single quote) di awal rumus.

Hal tersebut bisa terketik tanpa sengaja sehingga rumus tidak berfungsi.

Solusinya: Memeriksa kembali penulisan rumus, dan menghapus karakter-karakter yang menyebabkan rumus terbaca sebagai text biasa.

--
Demikian pembahasan singkat mengenai cara menampilkan mode display formula pada lembar kerja excel,  Penjelasan singkat bagaimana mengeprint formula, serta penjelasan beberapa masalah kenapa sel dalam lembar kerja excel menampilkan rumus, bukannya nilai hasil rumus.

Semoga bermanfaat.
Salam

Artikel terkait.

Saturday, December 24, 2016

Rumus Excel SUMPRODUCT - Satu Fungsi, Multi Guna.

Fungsi Excel SUMPRODUCTApa sebenarnya kegunaan fungsi SUMPRODUCT? Mengapa fungsi ini dikatakan sebagai fungsi multi guna?. 

Mari kita lihat jawabannya dalam catatan pelajaran excel berikut ini. 

Belajar excel kali ini memang khusus dipersembahkan untuk membahas mengenai rumus excel SUMPRODUCT dan kegunaan praktisnya. 

Bahasan dimulai yang paling sederhana yaitu pengenalan basic syntax dan cara penggunaan fungsi tersebut. Kemudian pada bagian selanjutnya akan dibahas contoh-contoh kegunaan praktis fungsi SUMPRODUCT, termasuk kegunaannya sebagai alternative rumus SUMIFS, COUNTIFS, AVERAGEIFS, Lookup 2 dimensi, dan Rumus Array CSE.


  • Syntax dan Cara Penggunaan Fungsi SUMPRODUCT
  • Contoh-Contoh Formula Bagaimana Menggunakan Fungsi SUMPRODUCT Pada Excel
    • Rumus SUMPRODUCT Perhitungan Dengan Satu Kondisi
    • Cara Kerja Rumus SUMPRODUCT Satu Kondisi
    • Rumus SUMPRODUCT Perhitungan Dengan Kondisi Ganda
    • Cara Kerja Rumus SUMPRODUCT Dua Kondisi
    • Rumus SUMPRODUCT Untuk Menghitung Frekuensi Data, Menjumlah dan Merata-Rata Dengan Kriteria.
    • Rumus SUMPRODUCT Untuk Menghitung Berat Rata-Rata
    • Rumus SUMPRODUCT Sebagai Alternative Lookup 2 Dimensi
  • Rumus SUMPRODUCT Sebagai Alternative Rumus Array

Apa yang terbayangkan oleh anda sewaktu pertama kali mendengar istilah rumus SUMPRODUCT? Mungkin anda mengenali fungsi ini sebagai gabungan antara SUM dan PRODUCT.

Mengenai fungsi SUM, saya kira mayoritas pengguna excel sudah sangat familiar dengannya.

Betapa tidak, fungsi penjumlahan yang satu ini merupakan salah satu fungsi yang paling banyak digunakan. Bahkan termasuk oleh pengguna yang masih awam sekalipun.

Pembahasan lebih rinci mengenai fungsi SUM dapat dibaca dalam artikel Fungsi SUM dan Rumus Penjumlahan Pada Excel

Bagaimana dengan fungsi PRODUCT?

Untuk yang satu ini, mungkin masih ada pengguna excel yang belum familiar. Padahal kenyataannya fungsi ini memang tersedia pada microsoft excel.

Fungsi PRODUCT digunakan untuk mendapatkan hasil perkalian antara dua buah bilangan atau lebih.

Contoh:

Rumus =PRODUCT(bil1,bil2,bil3) sama saja hasilnya dengan rumus =bil1*bil2*bil3.

Itulah mengapa, nampaknya fungsi PRODUCT kurang diminati dan jarang digunakan. Hal ini karena rumus perkalian lebih familiar dan lebih mudah difahami oleh kebanyakan user dibandingkan fungsi PRODUCT.

Bagaimana dengan SUMPRODUCT?

SUMPRODUCT sungguh merupakan sebuah fungsi excel yang multiguna.

Jika anda pengguna excel 2003 atau versi yang lebih lama, maka anda dapat menggunakan fungsi SUMPRODUCT untuk melakukan penjumlahan / perhitungan bersyarat ganda.

Sedangkan pada Excel 2007 dan versi yang lebih baru, tugas tersebut juga dapat dilakukan oleh fungsi SUMIFS dan COUNTIFS. Meskipun demikian, Fungsi SUMPRODUCT juga tetap dapat digunakan pada versi ini.

Fungsi SUMPRODUCT juga dapat digunakan untuk melakukan VLOOKUP 2 dimensi, yaitu rumus untuk mencari nilai dalam sel yang merupakan titik temu antara dua kriteria yang terletak dalam baris dan kolom.

Harap diperhatikan setiap contoh-contoh rumus yang akan dibahas. Jika anda sudah mengenal dan memahami rumus array, anda pasti akan menemukan kemiripan antara rumus array dengan SUMPRODUCT.

Dan memang SUMPRODUCT bekerja terhadap array, meskipun dalam aplikasinya tidak perlu menggunakan shortcut CSE seperti biasanya jika kita menggunakan rumus array.

Syntax dan Cara Penggunaan Fungsi SUMPRODUCT


Pada dasarnya fungsi SUMPRODUCT bekerja untuk melakukan perkalian antara bilangan dalam array, dan kemudian menjumlahkan hasil perkalian tersebut.

Adapun syntax fungsi SUMPRODUCT sangat simple dan sangat jelas.

SUMPRODUCT(array1, [array2], [array3], …)

Array1, array2 dan seterusnya adalah range sel atau array yang masing-masing elemennya ingin kita kalikan kemudian hasil perkaliannya dijumlahkan.

Jumlah maksimum argumen array yang dapat digunakan dalam fungsi SUMPRODUCT adalah 255 pada excel 2007,2010,2013 dan 2016. Sedangkan excel 2003 atau versi yang lebih lama, hanya mengizinkan maksimal 30 array.

Meskipun SUMPRODUCT bekerja dengan data array, tetapi fungsi ini tidak memerlukan penggunan shortcut array (CTR + Shift + Enter). Kita cukup menekan Enter setelah mengetikan formula, seperti halnya rumus biasa.

Hal-hal yang perlu diperhatikan seputar penggunaan Fungsi SUMPRODUCT:


  • Semua aray dalam rumus SUMPRODUCT harus memiliki jumlah baris dan kolom yang sama. Jika tidak, maka rumus SUMPRODUCT akan menghasilkan nilai error.
  • Jika array mengandung elemen yang bukan bilangan, maka elemen tersebut akan diperlakukan sebagai angka Nol.
  • Jika elemen array berupa test logika yang bernilai TRUE atau FALSE, maka dalam kebanyakan kasus, kita perlu mengkonversinya menjadi 1 atau 0 menggunakan double unary operator (--), atau menggunakan fungsi SIGN
  • Fungsi SUMPRODUCT tidak mendukung penggunaan karakter wildcard.

Dasar-Dasar Penggunaan Fungsi SUMPRODUCT pada Excel.


Untuk membantu memahami cara kerja fungsi SUMPRODUCT, mari kita perhatikan contoh kasus dibawah ini:

Anggaplah anda memiliki daftar belanja buah-buahan yang dibuat menggunakan microsoft excel.

  • Kolom A berisi nama daftar belanja buah-buahan
  • Kolom B berisi Kuantitas belanjaan
  • Kolom C berisi data harga per satuan.

Pertanyannya: Bagaimana cara kita menghitung (tanpa menggunakan kolom bantu) banyaknya biaya yang diperlukan?

Mari kita lihat gambar berikut:

Cara Menggunakan Fungsi SUMPRODUCT


Perhatikan:

Untuk mendapatkan jumlah biaya yang diperlukan, kita bisa menghitungnya menggunakan rumus perkalian dan penambahan biasa.

=B2*C2+B3*C3+B4*C4+B5*C5

Atau jika referensi tersebut diganti dengan bilangan, maka rumus dapat dituliskan sebagai berikut:

=3*10000+4*5000+5*8000+3*6000
=108.000

Rumus diatas memang sangat mudah untuk difahami. Cara kerjanya sederhana, cukup dengan cara mengalikan quantity produk dengan harganya, kemudian hasilnya dijumlahkan dengan hasil perkalian quantity dan harga produk lainnya.

Bayangkan…

Seandaiya kita memiliki ratusan sampai ribuan barang yang harus dihitung..

Rumus model diatas, sepertinya bukanlah solusi yang tepat, kecuali jika anda memiliki kesabaran tingkat dan ketelitian tinggi untuk mengetikan rumusnya.

Solusinya: Saatnya beralih ke penggunaan fungsi SUMPRODUCT.

=SUMPRODUCT(B2:B5,C2:C5)

Dengan rumus tersebut, sebanyak apapun baris data yang digunakan, anda tidak perlu repot mengetikan rumus yang panjang. Yang diperlukan hanya penyesuaian referensi range datanya saja.

Misalnya: jika baris data mencapai 1000, maka atur saja batas akhir range yang digunakan, misalnya B2:B1000 dan C2:C1000.

Menurut saya, cara ini sangat Simple dan Mudah.

Bagaimana menurut anda?


Contoh-Contoh Rumus – Bagaimana Menggunakan Fungsi SUMPRODUCT pada Excel


Melakukan perkalian pasangan elemen-elemen antara dua atau lebih range dan kemudian menjumlahkan hasil perkaliannya merupakan bentuk paling mendasar dan sederhana dari penggunaan fungsi SUMPRODUCT pada Excel.

Akan tetapi, keanggunan dan kehebatan fungsi SUMPRODUCT tidak terbatas pada definisi dasarnya saja. Ada banyak tugas kompleks yang dapat diselesaikan menggunakan fungsi multiguna ini.

Kehebatan apa saja yang dapat diperoleh menggunakan fungsi SUMPRODUCT?

Mari kita bongkar satu persatu dalam contoh-contoh rumus yang akan digali lebih jauh dalam belajar excel kali ini.

Silahkan dibaca terus artikel ini ya 😉

Fungsi SUMPRODUCT Dengan Satu Kondisi


Semakin lama dan sering menggunakan excel, maka kita akan menyadari sebenarnya ada banyak jalan untuk menyelesaikan tugas excel tertentu.

Namun untuk kaitan membandingkan dua atau lebih array, terutama yang berhubungan dengan kalkulasi yang bersyarat ganda, maka hanya ada dua cara yang paling efektif, yaitu: Menggunakan Rumus Array Atau menggunakan rumus SUMPRODUCT..

Mengenai rumus array sudah dibahas dalam artikel Memahami Rumus Array Untuk Pemula. Sedangkan untuk rumus SUMPRODUCT, inilah inti pembahasan belajar excel kali ini.

Anggaplah kita memiliki tabel dalam lembar kerja excel dengan susunan sebagai berikut:

- Kolom A berisi list produk
- Kolom B berisi target penjualan (Rp)
- Kolom C berisi aktual penjualan (Rp)
- Untuk contoh, baris data dibatasi s.d baris 10.

Pertanyaan: Bagaimana cara menghitung banyaknya item produk yang tidak mencapai target penjualan?

Jawaban: Gunakan fungsi SUMPRODUCT

Caranya: Ketikan rumus dibawah ini:

=SUMPRODUCT(--(C2:C10<B2:B10))

atau

=SUMPRODUCT((C2:C10<B2:B10)*1)


Fungsi SUMPRODUCT Satu Kondisi


Bagaimana Cara Kerja Rumus SUMPRODUCT dengan Satu Kondisi


Mari kita lihat kembali lebih dalam, contoh rumus SUMPRODUCT untuk menghitung berapa kali nilai sel pada range C2:C10 lebih kecil dibandingkan dengan nilai sel sejajar pada range B2:B10.

=SUMPRODUCT(--(C2:C10<B2:B10))

Bagaimana rumus tersebut bisa memberikan nilai akhir = 4 ?

Dengan menggunakan mouse, seleksi porsi  (--(C2:C10<B2:B10) pada formula bar, kemudian tekan F9. Untuk lebih jelasnya perhatikan gambar berikut:

Menganalisa Fungsi SUMPRODUCT



Apa yang kita dapatkan?

Kita akan melihat array yang berisi nilai boolean TRUE dan FALSE.

Perhatikan kembali logika menggunakan operator kurang dari (<).

Operator ini akan mengecek apakah nilai sel pada range C2:C10 kurang dari nilai sel sejajar pada range B2:B10. Jika iya maka akan bernilai TRUE (benar), dan jika kondisi sebaliknya maka akan bernilai FALSE (salah)

Selanjutnya, apa guna tanda minus kembar (--) atau yang secara tehnik disebut double unary operator?

Operator ini digunakan untuk mengkorversi nilai TRUE dan FALSE menjadi 1 dan 0.

Melanjutkan langkah berikutnya, pada formula bar seleksi bagian rumus berikut,
--{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE}

Kemudian tekan F9. Akan lebih mudah memahami dengan memperhatikan ilustrasi berikut:

Kegunaan Shortcut F9 Excel

Dari ilustrasi diatas, kita dapat melihat bahwa nilai TRUE berubah menjadi 1, sedangkan FALSE berubah menjadi 1 dan 0.  Inilah gunanya tanda minus kembar, atau double unary operator, merubah nilai logika menjadi numerik sehingga bisa dikalkulasi lebih lanjut.

Tahap akhir; Bilangan 1 dan 0 dijumlahkan sehingga menghasilkan nilai akhir = 4


Masih ingin tahu alternative lainnya?

Double unary operator ternyata dapat digantikan oleh fungsi SIGN

Cobalah: buat rumus =SIGN(TRUE) atau =SIGN(FALSE) dan perhatikan hasilnya.   Bandingkan dengan rumus =--(TRUE) atau =--(FALSE)

Hasilnya sama saja kan ?

Sehingga formula =SUMPRODUCT(--(C2:C10<B2:B10)) dapat diganti menjadi =SUMPRODUCT(SIGN(C2:C10<B2:B10))


Ternyata masih ada Alternative Lainnya:

Selain menggunakan double unary operator dan fungsi SIGN, kita juga dapat mengkonversi nilai logika TRUE dan FALSE dengan cara mengalikannya dengan bilangan 1.

=SUMPRODUCT((C2:C10<B2:B10)*1)

Jika tidak percaya, silahkan lakukan ujicoba rumus =TRUE*1  atau =FALSE*0, dan lihat hasilnya.

Sampai pada tahap ini, mudahan-mudahan cara kerja perhitungan dengan satu kondisi sudah bisa difahami.

Rumus SUMPRODUCT dengan Dua Kondisi


Selanjutnya mari kita pelajari lebih jauh dengan contoh rumus untuk menghitung banyaknya data yang memenuhi beberapa kondisi tertentu atau bersyarat ganda.

Kita ambil saja 2 kondisi sebagai contoh, karena sebanyak apapun kondisi yang digunakan, prinsip kerjanya sama saja.

Anggaplah kita sebagai penjual buah-buahan. Misalnya  ingin menghitung berapa kali penjualan buah mangga yang  tidak mencapai target.

Untuk tujuan tersebut, kita dapat menggunakan rumus berikut:

=SUMPRODUCT(--(A2:A10="mangga"),--(C2:C10<B2:B10))

Atau

=SUMPRODUCT((A2:A10="mangga")*(C2:C10<B2:B10))


Fungsi SUMPRODUCT Dua Kondisi



Bagaimana Cara Kerja Rumus SUMPRODUCT Dengan Kriteria Ganda


Sebagaimana kita ketahui pada pembahasan awal, bahwa fungsi SUMPRODUCT digunakan untuk melakukan perkalian elemen sejajar dalam array kemudian menjumlahkan hasilnya.

Mari kita perhatikan kembali rumus untuk menghitung berapa kali penjulan buah mangga yang tidak mencapai target sesuai contoh sebelumnya.

=SUMPRODUCT(--(A2:A10="mangga"),--(C2:C10<B2:B10))

Bagaimana bisa diperoleh hasil = 2 ?

  • Seleksi sel dimana rumus tersebut ditempatkan
  • Pada formula bar, seleksi bagian rumus (A2:A10="mangga"), kemudian tekan F9, maka kita akan dapatkan {TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}
  • Nilai boolean TRUE dan false tersebut harus dikonversi menjadi data numerik, salah satunya adalah menggunakan double unary operator (--), cobalah seleksi hasil tahap sebelumnnya, tetapi termasuk 2 tanda negatif yang mendahuluinya yaitu --{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE} , kemudian tekan F9, maka hasilnya adalah {1;0;0;0;1;0;0;0;1}
  • Dengan cara dan tahapan yang sama, lakukan pada porsi rumus (C2:C10<B2:B10) 
  • Secara keseluruhan, rumus menjadi =SUMPRODUCT({1;0;0;0;1;0;0;0;1},{0;1;0;0;1;0;0;1;1})
  • Ingat kembali cara kerja SUMPRODUCT untuk mengalikan elemen sejajar, kemudian menambahkan masing-masing hasilnya. Maka formula tersebut dapat dijelaskan dengan rumus =(1*0)+(0*1)+(0*0)+(0*0)+(1*1)+(0*0)+(0*0)+(0*1)+(1*1)
  • Maka hasilnya adalah 2


Menghitung Banyak data, Menjumlah dan Merata-rata Dengan Kriteria Ganda


Mulai excel versi 2007, kita mengenal adanya fungsi COUNTIFS, SUMIFS dan AVERAGEIFS untuk menghitung banyak (frekuensi) data, menjumlahkan dan merata-rata dengan kriteria ganda.
Pada excel 2003 dan versi yang lebih lama, ketiga fungsi tersebut belum tersedia.

Namun sebenarnya tugas ketiga fungsi tersebut dapat dikerjakakan oleh fungsi SUMPRODUCT. Inilah salah satu alasan kenapa SUMPRODUCT disebut sebagai fungsi Multiguna.

Jadi jangan khawatir meskipun komputer anda sendiri atau komputer di kantor masih menggunakan excel 2003, karena SUMPRODUCT tersedia di semua versi excel, setidaknya pada versi 2003 s.d 2016 yang sudah saya gunakan. Untuk versi excel yang lebih tua dari 2003 silahkan dicek sendiri ya, kebetulan saya tidak punya versinya. 😓

1. SUMPRODUCT dengan Logika AND


Yang dimaksud logika AND adalah dimana kombinasi dua pernyataan atau lebih akan bernilai benar (TRUE) jika semua pernyataan bernilai (Benar)

Anggaplah kita memiliki data berikut yang merupakan data penjualan buah-buahan pada berbagai daerah dari tanggal 1 s.d 12 Desember.

Fungsi SUMPRODUCT Logika AND


Bagaimana cara mengetahui jumlah penjualan di daerah Ciamis, Berapa kali penjualannya dan Berapa rata-ratanya?

Disini kita menggunakan logika AND, yaitu ada dua kenyataan yang harus sama sama benar (TRUE) yaitu:

  • Lokasi penjualan = Ciamis
  • Nama Buah = Mangga

Kedua hal tersebut harus terpenuhi sebagai syarat kalkulasi.

Seperti halnya dalam rumus array CSE, logika AND dalam rumus SUMPRODUCT  juga dapat dipenuhi menggunakan bantuan operator asterisk (*)

Perhatikan contoh rumus berikut dengan asumsi baris terakhir data adalah baris 13.

Berapa Jumlah Penjualan buah mangga di daerah  Ciamis?

=SUMIFS(D2:D13,B2:B13,"Ciamis",C2:C13,"Mangga")

Atau

=SUMPRODUCT((B2:B13="Ciamis")*(C2:C13="Mangga")*(D2:D13))


Berapa kali penjualan buah mangga di daerah Ciamis?

=COUNTIFS(B2:B13,"Ciamis",C2:C13,"Mangga",D2:D13,">0")

Atau

=SUMPRODUCT((B2:B13="Ciamis")*(C2:C13="Mangga")*(D2:D13>0))


Berapa rata-rata Penjualan  buah mangga di daerah Ciamis?

=AVERAGEIFS(D2:D13,B2:B13,"Ciamis",C2:C13,"Mangga"))

Atau

=SUMPRODUCT((B2:B13="Ciamis")*(C2:C13="Mangga")*(D2:D13))/SUMPRODUCT((B2:B13="Ciamis")*(C2:C13="Mangga")*(D2:D13>0))

Dari contoh cotoh rumus diatas, sudah sangat jelas bahwa  fungsi SUMPRODUCT dapat menggantikan fungsi SUMIFS, COUNTIFS dan AVERAGEIFS, meskipun sebagai pengganti AVERAGEIFS, perlu rumus yang lebih panjang.

Supaya lebih fleksible, kita bisa menaruh kriteria perhitungan dalam sebuah sel
Misalnya 

- Kriteria daerah pemasaran disimpan di sel C16
- Kriteria nama buah disimpan di sel C17

Maka rumus penjumlahan, frekuensi dan rata-rata dapat dituliskan sebagai berikut:

Jumlah    : =SUMPRODUCT((B2:B13=C16)*(C2:C13=C17)*(D2:D13))

Frekuensi:  =SUMPRODUCT((B2:B13=C16)*(C2:C13=C17)*(D2:D13>0))

Rata-rata: =SUMPRODUCT((B2:B13=C16)*(C2:C13=C17)*(D2:D13))/SUMPRODUCT((B2:B13=C16)*(C2:C13=C17)*(D2:D13>0))


Untuk menghitung rata-rata, karena merupakan hasil pembagian jumlah dengan frekuensi maka rumusnya dapat menggunakan referensi lokasi rumus jumlah dan frekuensi. Perhatikan ilustrasi berikut:


Logika AND dalam Fungsi SUMPRODUCT



CONTOH2: Rumus SUMPRODUCT Dengan Logika OR


Logika OR akan bernilai benar jika kombinasi pernyataan memiliki minimal satu pernyataan yang bernilai benar (TRUE). 

Misalnya:

Untuk menghitung berapa banyak penjualan buah mangga dan buah pisang pada tabel contoh sebelumnya.

Mangga dan pisang sama-sama terletak dalam satu kolom dan kedua nya harus diperhitungkan dalam proses kalkulasi.

Ini artinya kita harus membuat rumus yang menghasilkan nilai TRUE apabila salah satu kondisi terpenuhi.

Kondisi tersebut adalah:
  • Nama buah = "mangga" atau  "pisang"

Seperti halnya juga dalam rumus array, logika OR dalam rumus SUMPRODUCT dapat dibantu menggunakan operator plus (+).

Perhatikan contoh rumus berikut:

Berapa kali (frekuensi) buah mangga dan  pisang yang terjual tanpa memandang lokasi penjualannya

=SUMPRODUCT((C2:C13="mangga")+(C2:C13="pisang"))


Berapa nilai total penjualan buah mangga dan pisang?

=SUMPRODUCT((C2:C13="mangga")+(C2:C13="pisang"))

Atau

=SUMPRODUCT(((C2:C13="mangga")+(C2:C13="pisang"))*D2:D13)


Fungsi SUMPRODUCT Logika OR




Contoh 3: Formula SUMPRODUCT Dengan Kombinasi Logika AND dan OR


Dalam beberapa kasus, mungkin kita perlu memperhitungkan beberapa kondisi dengan logika AND dan OR sekaligus.

Sayangnya bahkan dalam versi excel terbaru pun, Satu fungsi SUMIFS dan COUNTIFS tidak bisa menghandle kasus ini. Salah satu cara yang dapat dilakukan jika tetap mau menggunakan kedua fungsi tersebut adalah menggunakan kombinasi dua atau lebih dari fungsi SUMIFS +  SUMIFS, atau COUNTIFS+COUNTIFS

Untungnya hal ini bisa dihandle oleh hanya Satu Fungsi SUMPRODUCT saja.

Melanjutkan contoh tabel penjualan buah-buahan sebelumnya, Misalnya kita ingin menghitung berapa kali frekuensi penjualan dan nilai penjualan buah mangga dan buah pisang di daerah ciamis.

Perhatikan syarat yang harus dipenuhi

  • Lokasi penjualan  : Ciamis
  • Nama Buah          : Mangga atau Pisang

Secara sederhana, kriteria ini dapat dituliskan dalam logika AND dan OR sebagai berikut:

AND(lokasi="ciamis",OR(buah="mangga",buah="pisang"))

Ekspresi atau pernyataan tersebut akan bernilai benar (TRUE) jika lokasi ="ciamis" dan buah ="mangga" atau buah="pisang"

Mari kita lihat dalam contoh di bawah ini:


Berapa kali frekuensi penjualan buah mangga dan pisang di daerah Ciamis?

Cara1: Menggunakan rumus COUNTIFS, diperlukan 2 fungsi:

=COUNTIFS(B2:B13,"ciamis",C2:C13,"mangga")+COUNTIFS(B2:B13,"ciamis",C2:C13,"pisang")


Cara2: Menggunakan rumus SUMPRODUCT, hanya diperlukan 1 fungsi:

=SUMPRODUCT((B2:B13="ciamis")*((C2:C13="mangga")+(C2:C13="pisang")))


Berapa Total nilai penjualan buah mangga dan pisang di daerah Ciamis?

Cara1: Menggunakan rumus SUMIFS , diperlukan 2 fungsi

=SUMIFS(D2:D13,B2:B13,"ciamis",C2:C13,"mangga")+SUMIFS(D2:D13,B2:B13,"ciamis",C2:C13,"pisang")


Cara 2: Menggunakan rumus SUMPRODUCT, hanya diperlukan 1 fungsi

=SUMPRODUCT((B2:B13="ciamis")*((C2:C13="mangga")+(C2:C13="pisang"))*D2:D13)

Lebih jelasnya perhatikan ilustrasi dalam screenshot dibawah ini:

Logika OR dan AND Dalam Fungsi SUMPRODUCT


Formula SUMPRODUCT Untuk Menghitung Berat Rata-Rata


Masih berbicara dengan contoh buah-buahan. Kita bisa menggunakan rumus SUMPRODUCT untuk menghitung berat buah rata-rata tanpa harus menggunakan kolom bantu.

Saya mencontohkan dengan buah kelapa sawit. Satuan hitung diukur dengan jumlah tandan atau sering disebut juga janjang kelapa sawit, sedangkan berat rata-ratanya diistilahkan Berat Janjang Rata-rata atau disingkat BJR.

Bagaimana menghitung BJR jika diketahui data sebagai berikut:


Menghitung Berat Rata-Rata Dengan Fungsi SUMPRODUCT




Tanpa menggunakan kolom bantu, sebenarnya kita juga bisa menghitungnya dengan rumus matematika biasa yaitu:

=(C2*D2+C3*D3+C4*D4+C5*D5+C6*D6)/SUM(C2:C6)

Namun rumus tersebut tidak efesien, terutama jika digunakan pada baris data yang banyak.
Oleh karenanya disarankan menggunakan SUMPRODUCT

Rumusnya seperti ini:

=SUMPRODUCT(jjg_blok, bjr_blok) / SUM(jjg_blok)

Dan jika diterapkan dalam lembar excel menjadi

=SUMPRODUCT(C2:C6,D2:D6)/SUM(C2:C6)

Cukup mudah bukan?

Atau bertele-tele?

Tergantung anda dan dari sudut mana anda memandangnya.

Rumus SUMPRODUCT Sebagai Alternative LOOKUP 2 Dimensi.


LOOKUP 2 dimensi disini artinya adalah mencari titik temu antara baris dan kolom sesuai syarat atau kriteria tertentu.

Contohnya:

Sebuah tabel data berisi penjualan buah-buahan per bulan.


  • Baris menunjukan jenis buah
  • Kolom menunjukan bulan per bulannya
  • Data berisi nilai penjualan buah.

Bagaimana rumus untuk mendapatkan nilai penjualan buah tertentu pada bulan tertentu.
Perhatikan contoh dalam prinscreen berikut:

Lookup 2 Dimensi Menggunakan Rumus SUMPRODUCT


Perhatikan bahwa jumlah penjualan pisang pada bulan Maret adalah 7.500.000. 
Jika kita baca dalam lembar kerja excel, data tersebut merupakan titik temu antara baris pisang dan kolom maret. Inilah yang disebut dengan lookup 2 dimensi.

Rumus tersebut dapat dituliskan sebagai berikut:

=SUMPRODUCT((A2:A4="pisang")*(B1:G1="maret")*B2:G4)

Dan supaya lebih fleksible, maka nama buah dan bulan kita simpan dalam sel:

Nama buah : sel B10
Nama bulan : sel B11

Sehingga rumus menjadi:

=SUMPRODUCT((A2:A4=B10)*(B1:G1=B11)*B2:G4)

Sampai pada tahap ini mudah-mudahan dapat difahami.

SUMPRODUCT sebagai alternatif rumus array.


Jika kita sudah memahami rumus array dan memperhatikan contoh-contoh rumus SUMPRODUCT dari awal pembahasan sampai bagian akhir, sebenarnya kita bisa menyimpulkan bahwa tugas-tugas yang dapat diselesaikan menggunakan fungsi SUMPRODUCT, juga dapat diselesaikan menggunakan rumus array (CSE).

Supaya masih hangat, sy menggunakan contoh rumus SUMPRODUCT yang terakhir dibahas yaitu rumus alternative Lookup 2 dimensi.

=SUMPRODUCT((A2:A4=B10)*(B1:G1=B11)*B2:G4)

Rumus tersebut ternyata akan menghasilkan nilai yang sama dengan rumus array berikut:

{=SUM((A2:A4=B10)*(B1:G1=B11)*B2:G4)}

Terbukti….

Silahkan dicoba dengan rumus-rumus yang lainnya.

....

Demikian pembahasan mengenai fungsi SUMPRODUCT. Dimulai dari pembahasan syntax dan basic penggunaanya, Dilanjutkan dengan pembahasan contoh-contoh rumus dan penjelasan cara kerjanya. Hingga pada bagian akhir, kita bisa mengetahui bahwa rumus SUMPRODUCT ternyata bisa menjadi alternatif rumus-rumus dan fungsi lainnya termasuk rumus array.

Tidak salah jika dikatakan bahwa SUMPRODUCT sebagai Rumus Multi Guna

Demikian semoga bermanfaat.
Salam..

Artikel Terkait:

Thursday, December 22, 2016

Shortcut CTR + HOME dan CTR + END Tidak Bekerja - Apa Masalahnya?

Shortcut Excel CTR+END
Pernahkan anda mengalami masalah dengan shortcut keyboard pada saat bekerja menggunakan Microsoft Excel. Salah satu kasus yang sering dikeluhkan pengguna excel adalah ketika short cut CTR + HOME dan CTR + END tidak bekerja sebagaimana mestinya.

Jika anda tidak termasuk pengguna maniak shortcut keyboard, mungkin hal ini tidak terlalu menjadi perhatian. Tapi tidak ada salahnya untuk mencoba mengetesnya. Jika sel aktif berpindah seperti keterangan berikut berarti CTR+HOME dan CTR + END  bekerja normal


CTR + HOME    : Sel Aktif berpindah ke sel A1
CTR + END        : Sel Aktif berpindah ke sel paling akhir yang digunakan dalam spreadsheet

Sedangkan jika shortcut CTR+HOME atau CTR+END ditekan tetapi sel aktif tidak berpindah sesuai keterangan diatas, maka kemungkinan besar  setting Transition navigation keys diaktifkan


Bagaimana Cara Mengatasinya?



Lakukan setting untuk me-nonactive-kan  Transition navigation keys



Excel 2003 

  • Tekan short cut ALT+T+O,  Atau pilih menu Tools --> Options
  • Setelah dialog box Option muncul, masuk ke tab Transition
  • Hilangkan centang pada checkbox Transition navigation keys
  • Klik Ok
Cara setting Transition Navigation Keys Excel 2003


Excel 2007, 2010, 2013 dan 2016


  • Untuk excel 2007: Klik Office Button yang ada pada pojok kiri atas jendela excel, kemudian klik Excel Options.
  • Untuk Excel 2010,2013 dan 2016 : Klik Tab File, kemudian klik Options
  • Masuk ke bagian Advanced, dan scrolldown ke bagian bawah sehingga dijumpai bagian Lotus Compatibility
  • Hilangkan centang pada checkbox Transition navigation keys
  • Klik OK


Excel 2010,2013,2016 - Setting Transition Navigation Keys


Demikian penjelasan singkat mengenai cara mengatasi pemasalahan seputar penggunaan shortcut CTR + HOME dan CTR + END yang tidak bekerja sebagaimana mestinya.

Silahkan dicoba. Semoga bermanfaat :-)

Artikel Terkait:

Referensi:

Tuesday, December 20, 2016

Rumus Excel COUNTIF: Menghitung Sel Dengan Syarat

Fungsi Excel COUNTIF
Fungsi COUNTIF Dalam Rumus Excel dapat digunakan untuk menghitung banyaknya sel yang memenuhi syarat, kriteria atau kondisi tertentu. Misalnya menghitung banyaknya sel kosong, menghitung sel berisi bilangan tertentu, sel berisi tanggal tertentu, maupun sel berisi text atau karakter tertentu. Fungsi COUNTIF tersedia pada berbagai versi excel, baik versi 2003, 2007, 2010, 2013 dan 2016. 

Belajar excel kali ini akan membahas fungsi COUNTIF secara rinci, dimulai dari pembahasan syntax dan dasar cara penggunaannya, kemudian dilanjutkan dengan pembahasan beberapa contoh penerapan rumus COUNTIF. Pada bagian akhir akan diulas problem solving atas beberapa masalah dan pertanyaan seputar penggunaan fungsi COUNTIF dalam rumus Excel.

  • Syntax dan Cara Penggunaan Fungsi COUNTIF
  • Contoh-Contoh Cara Menggunakan Fungsi COUNTIF Pada Excel
  • Rumus COUNTIF Untuk Text dan Bilangan (Exact Match)
  • Rumus COUNTIF Menggunakan Karakter Wildcard (Partial Match)
  • Menghitung Banyaknya Sel Kosong atau Sebaliknya
  • Menghitung Sel Jika Berisi Bilangan Lebih Besar, Lebih Kecil, Atau atau Sama Dengan
  • Rumus COUNTIF Untuk Tanggal
  • Rumus COUNTIF Dengan Dua Kriteria
  • Menghitung Banyaknya Data Duplikat
  • Menjawab Beberapa Permasalahan Seputar Rumus COUNTIF

Syntax dan Cara Penggunaan Fungsi COUNTIF


COUNTIF digunakan untuk menghitung banyaknya sel dengan kondisi atau kriteria tertentu. Syntax-nya pun sangat sederhana dan hanya memiliki 2 argumen:

=COUNTIF(range, criteria)

range : berupa referensi mewakili satu atau beberapa sel untuk dihitung, misalnya A1:A10

criteria: Kondisi tertentu yang menjadi syarat apakah sel tersebut akan dihitung atau tidak. Argumen kriteria bisa berupa bilangan, tanggal, string yang mewakili persamaan, maupun text tertentu. Kriteria ini juga bisa disimpan dalam sel yang dijadikan referensi.

Rumus berikut merupakan contoh sederhana cara menggunakan fungsi COUNTIF untuk menghitung berapa kali karyawan sebuah perusahaan yang tidak hadir karena sakit dalam kurun waktu 10 hari.

=COUNTIF(B2:B11,"sakit")

Jika criteria disimpan dalam sel E1, maka rumus berikut lebih dianjurkan karena lebih fleksible:

=COUNTIF(B2:B11,E1)

Fungsi COUNTIF Menghitung Absensi


Catatan: criteria bersifat case insensitif sehingga tidak ada masalah apakah penulisan criteria menggunakan huruf kecil ataupun huruf kapital.


Contoh-Contoh Rumus Fungsi COUNTIF


Ternyata manfaat fungsi COUNTIF tidak sesederhana syntax nya. Dengan modifikasi argumen criteria, maka kita akan mendapatkan kegunaan beragam dari fungsi COUNTIF.

Rumus COUNTIF untuk Text dan Bilangan (exact match)


Mari kita lihat lembali contoh rumus COUNTIF untuk menghitung jumlah hari absensi karyawan sakit seperti dicontohkan pada sub bab pertama.

=COUNTIF(B2:B11,"sakit")

Perhatikan bagian-bagian dari argumen fungsi tersebut:

range = yaitu berupa referensi sel B2:B11

Tanda koma (,) berguna sebagai delimiter, atau pemisah argumen. Jika komputer anda menggunakan seting Indonesia, mungkin delimiternya adalah tanda titik koma atau semicolon (;)

Sebuah  kata dalam tanda kutip, yaitu kata "sakit" berperan sebagai argumen criteria. Daripada mengetikan  text secara langsung, kita dapat menggunakan sebuah referensi sel yang mengandung text atau kata yang menjadi kriteria. Anggaplah criteria absensi disimpan di sel E1, maka rumus untuk menghitung banyaknya absensi tertentu dapat dituliskan sebagai berikut:

=COUNTIF(B2:B11,E1)

Contoh diatas menggunakan kritera text. Bagaimana dengan kriteria bilangan?

Misalnya untuk menghitung berapa orang anak berumur 10 tahun dapat diilustrasikan sebagai berikut:


Fungsi COUNTIF Menghitung Banyaknya Usia Tertentu


Perhatikan rumus yang digambarkan dalam ilustsrasi tersebut:

=COUNTIF(B2:B9,10)

Berbeda dengan kriteria text, penulisan kriteria bilangan tidak memerlukan tanda petik.

Dalam prakteknya, mungkin kita akan lebih mudah menggunakan kriteria yang disimpan dalam referensi sel daripada mengetikannya langsung pada rumus. Anggaplah kriteria disimpan dalam sel E1, maka rumusnya dapat ditulis begini:

=COUNTIF(B2:B9,E1)


Rumus COUNTIF Menggunakan Karakter Wildcard


Penggunaan karakter wilcard dalam rumus COUNTIF sangat berguna untuk menghitung banyaknya sel berisi text dengan kata kunci tertentu. Sebagai contoh adalah untuk menghitung jumlah nama siswa yang memiliki satu atau beberapa huruf awal tertentu atau mengandung kata tertentu (misal: marga atau nama keluarga)

Anggaplah kita memiliki sekumpulan nama siswa yang disimpan pada kolom A (range A2:A9) dalam lembar kerja excel / spreadsheet.

Pertanyaan: Bagaimana rumus excel untuk menghitung berapa banyak nama siswa yang diawali huruf tertentu? anggaplah nama siswa tersebut diawali huruf “B”.

Jawaban: Gunakan fungsi COUNTIF dengan argumen criteria berupa karakter “B” diikuti karakter wilcard asterisk (*). Sehingga rumus dapat dituliskan sebagai berikut:

=COUNTIF(A2:A9,"B*")

Seandainya text criteria disimpan di sel G1,maka rumus COUNTIF diatas dapat diubah menjadi:

=COUNTIF(A2:A9,G1&"*")

Fungsi COUNTIF Menghitung Nama Dengan Huruf Awal Tertentu



Silahkan dicoba dengan manipulasi berbagai posisi kata kunci:

Menghitung banyaknya siswa yang mengandung huruf  tertentu, tidak peduli dimana posisi huruf tersebut:

=COUNTIF(A2:A9,"*"&G1&"*")

Menghitung banyaknya siswa dengan huruf akhir tertentu

=COUNTIF(A2:A9,"*"&G1)

Dalam prakteknya kita bisa menggunakan text yang panjangnya lebih dari satu huruf sebagai kriteria, misalnya nama depan dan nama belakang. Silahkan dicoba dan dimodifikasi isi sel G1 dengan text yang lebih panjang dan  bervariasi. Kemudian perhatikan hasil yang diperoleh rumus COUNTIF.

Perlu diingat 

  • Karakter wilcard asterisk (*) digunakan untuk mewakili karakter yang tidak ditentukan jumlahnya, sehingga sangat cocok untuk mencari nama dengan huruf awal tertentu, karena karakter yang menyusun nama, jumlahnya tidak tentu.
  • Jika jumlah karakternya ditentukan maka gunakanlah karakter wildarcd tanda tanya (?)
  • Untuk memperlakukan karakter wilcard sebagai karakter biasa, gunakan tanda gelombang (~) sebelum mengetikan karakter wilcard


Berikut contoh cara penulisan karakter wilcard dalam argumen critera fungsi COUNTIF


Contoh
Keterangan
"Mr *"
Mewakili text “Mr “ diikuti karakter bebas lainnya dengan jumlah tidak dibatasi
"Mr ?????"
Mewakili text “Mr “ diikuti 5 karakter bebas setelahnya.
"Mr ~***"
Secara persis mewakili karakter “Mr ***”
"Mr ~???"
Secara persis mewakili karakter “Mr ???”


Rumus Excel COUNTIF Untuk Menghitung Banyaknya Sel Kosong/Tidak Kosong


Contoh-contoh rumus excel berikut akan memperlihatkan bagaimana fungsi COUNTIF  dapat digunakan untuk menghitung banyaknya sel kosong (blank) dan sel tidak kosong (non blank) dalam range tertentu.

COUNTIF not blank - Menghitung Sel Tidak Kosong

Untuk menghitung banyaknya sel tidak kosong, kita dapat menggunakan rumus sebagai berikut:

=COUNTIF(range,"*")

atau

=COUNTIF(range,"<>"&"")


COUNTIF blank - Menghitung Sel Kosong

Sebaliknya, kita juga dapat menghitung banyaknya sel  kosong. Untuk itu gunakan logika terbalik dari contoh sebelumya, sehingga rumus menjadi:

=COUNTIF(range,"<>"&"*")

atau

 =COUNTIF(range,"")

Perlu diingat: Sebenarnya excel sudah menyediakan fungsi khusus untuk menghitung sel kosong dalam range tertentu, yaitu fungsi COUNTBLANK, Cukup ketikan =COUNTBLANK(range), maka kita sudah dapatkan banyaknya sel kosong dalam range.


Menghitung Banyaknya Sel Berisi Bilangan Dengan Nilai Lebih Besar, Lebih Kecil atau Sama Dengan Nilai Kriteria.


Apa yang perlu dilakukan untuk menghitung banyaknya sel yang memiliki nilai lebih besar, lebih kecil, atau sama dengan kriteria yang ditentukan?

Cukup tambahkan saja operator komparasi yang tepat sesuai kriteria, seperti contoh dalam tabel berikut:

Criteria
Contoh Rumus
Keterangan
Hitung jika  nilai lebih dari
=COUNTIF(B2:B9,">11")
Menghitung banyaknya sel dalam range B2:B9 yang bernilai lebih dari 11
Hitung jika nilai kurang dari
=COUNTIF(B2:B9,"<11")
Menghitung banyaknya sel dalam range B2:B9 yang bernilai kurang dari 11
Hitung jika nilai sama dengan
=COUNTIF(B2:B9,"=11")
Menghitung banyaknya sel dalam range B2:B9 yang bernilai sama dengan 11
Hitung jika nilai tidak sama dengan
=COUNTIF(B2:B9,"<>11")
Menghitung banyaknya sel dalam range B2:B9 yang nilainya tidak sama dengan 11
Hitung jika nilai minimal sama dengan
=COUNTIF(B2:B9,">=11")
Menghitung banyaknya sel dalam range B2:B9 yang bernilai 11 atau lebih.
Hitung jika nilai maksimal sama dengan
=COUNTIF(B2:B9,"<=11")
Menghitung banyaknya sel dalam range B2:B9 yang bernilai 11 atau kurang.

Perhatikan: sama halnya dengan penggunaan fungsi SUMIF, maka dalam fungsi COUNTIF juga kita harus menggunakan tanda petik dua untuk kriteria yang menggunakan kombinasi bilangan dan operator komparasi.

Contoh rumus COUNTIF dalam tabel di atas menggunakan kriteria yang diketikan langsung dalam rumus. Jika kita ingin menempatkan kriteria dalam sel supaya kedepannya dapat lebih mudah merubah kriteria tanpa merubah rumus. Maka gunakan operator ampersand (&) untuk menggabungkan operator komparasi dengan referensi yang menjadi kriteria.

Contohnya:
=COUNTIF(B2:B9,">11")

Jika kriteria disimpan di sel D1, maka rumus tersebut dapat dirubah menjadi:
=COUNTIF(B2:B9,">"&D1)

Untuk lebih mudah memahaminya, perhatikan gambar berikut:

Fungsi COUNTIF Usia


Sebagian pembaca mungkin bertanya-tanya dalam hati. Bagaimana cara memperlakukan tanda lebih dari (>), kurang dari (<) dan sama dengan (=) sebagai karakter biasa, bukan sebagai operator komparasi (perbandingan).

Untuk kasus tersebut kita bisa menggagungkan operator komparasi dengan karakter wilcard.

Misalnya : =COUNTIF(B2:B9,"*>11*")

Formula tersebut akan menghitung banyaknya sel dalam range B2:B9 dimana sel tersebut mengandung text “>11”, Dalam contoh tersebut, tanda lebih dari (>) terbaca sebagai karakter biasa

Menggunakan Fungsi COUNTI F dengan Kriteria Tanggal

Jika kita ingin menghitung banyaknya tanggal tertentu, maka gunakan fungsi COUNTIF dengan criteria data tanggal. Hal serupa dapat dilakukan untuk menghitung berapa banyak data tanggal sebelum atau sesudah tanggal tertentu, tentunya hampir sama penggunannya dengan fungsi COUNTIF kriteria bilangan yaitu menggunakan operator komparasi “<” , “>”, dan “=”

Perhatikan contoh rumus dalam tabel berikut:

Criteria
Contoh Rumus
Keterangan
Menghitung banyaknya tanggal tertentu
=COUNTIF(B2:B9,"12/15/2016")
Menghitung banyaknya tanggal 15 Desember 2016 pada range B2:B9
Menghitung banyaknya tanggal tertentu dan tanggal sesudahnya.
=COUNTIF(B2:B9,">=12/15/2016")
Menghitung banyaknya tanggal 15 Desember 2016 dan tanggal sesudahnya pada range B2:B9
Menghitung banyaknya tanggal dimulai dari beberapa hari sebelum tanggal ditentukan
=COUNTIF(B2:B9,">="&G1-7)
Menghitung banyaknya tanggal pada range B2:B9, dimulai dari 7 hari sebelum tanggal yang disimpan di sel G1


Dengan memperhatikan  contoh rumus atau formula dalam tabel diatas, kita bisa mengetikan langsung tanggal sebagai kriteria (contoh: "12/15/2016") dan menggunakan referensi sel (contoh : G1).

Selain dari kedua cara penulisan argumen kriteria diatas (ketik langsung tanggal maupun referensi sel), kita juga dapat menggunakan fungsi lainnya sebagai criteria. Misalnya fungsi TODAY.  Fungsi ini berguna untuk mendapatkan data tanggal sekarang sesuai setting komputer.

Criteria
Contoh Rumus
Menghitung banyaknya tanggal yang sama dengan tanggal sekarang
=COUNTIF(B2:B9,TODAY())
Menghitung banyaknya tanggal sebelum tanggal sekarang
=COUNTIF(B2:B9,"<"&TODAY())
Menghitung banyaknya tanggal setelah tanggal sekarang
=COUNTIF(B2:B9,">"&TODAY())
Menghitung banyaknya tanggal yang sama dengan tanggal seminggu kedepan dari sekarang
=COUNTIF(B2:B9,"="&TODAY()+7)
Menghitung banyaknya tanggal dimulai tanggal tertentu tetapi kurang dari tanggal tertentu
=COUNTIF(B2:B9, ">=12/1/2016")-COUNTIF(B2:B9, ">12/15/2016")


Berikut contoh formula menggunakan fungsi TODAY sebagai argumen criteria dalam fungsi COUNTIF. Rumus ini digunakan untuk menghitung banyaknya tagihan yang sudah jatuh tempo.

Rumus COUNTIF Tanggal


Keterangan: artikel ini ditulis pada tanggal 16 Desember 2016, sehingga rumus TODAY() menghasilkan nilai tanggal 16 Desember 2016


Fungsi COUNTIF dengan Kriteria Ganda


Sebenarnya fungsi COUNTIF di excel tidak di-design untuk menghitung banyaknya sel yang memenuhi kriteria atau kondisi yang lebih dari satu. Namun demikian fungsi ini juga dapat digunakan untuk keperluan tersebut terutama pada excel versi 2003 atau yang lebih lama. Untuk excel versi 2007 ke atas, tugas ini sudah digantikan oleh fungsi COUNTIFS (lihat: ada huruf “S”)

Contoh 1 : Fungsi COUNTIF dengan Kondisi Ganda berupa bilangan

Fungsi COUNTIF kriteria ganda umumnya digunakan untuk menghitung banyaknya data yang memenuhi rentang kriteria atau kondisi tertentu, dibatasi nilai minimal dan nilai maksimal.

Misalnya untuk menghitung jumlah anak berumur 10 s.d 12 tahun sesuai data umur pada range B2:B9 maka dapat dirumuskan sebagai berikut:

=COUNTIF(B2:B9,">=10")-COUNTIF(B2:B9,">12")


Rumus COUNTIF Rentang Umur



Contoh 2 : Fungsi COUNTIF dengan Kondisi Ganda berupa Text

Mari kita perhatikan satu lagi contoh rumus COUNTIF dengan dua kriteria untuk menghitung banyaknya item barang yang mengandung text tertentu.

Anggaplah anda sedang belanja di pasar buah, dan ingin menghitung berapa banyak item belanjaan buah jeruk dan mangga.

=COUNTIF(A2:A9,"*Jeruk*")+COUNTIF(A2:A9,"*mangga*")

Rumus COUNTIF Syarat Ganda

Perhatikan penggunaan karakter wildcard asterisk pada contoh di atas. Karakter tersebut digunakan untuk mewakili sejumlah karakter lainnya termasuk string kosong (tidak ada karakter sama sekali), baik sebelum maupun sesudah kata kunci. Sehingga contoh rumus tersebut dapat menghitung semua benda yang mengandung kata mangga atau jeruk.

Dengan cara yang sama kita juga bisa menambahkan fungsi COUNTIF lainnya untuk menghitung lebih banyak kriteria.

Contohnya: untuk menghitung semua item belanja jeruk, mangga dan pisang maka dapat menggunakan rumus berikut:

=COUNTIF(A2:A9,"*Jeruk*")+COUNTIF(A2:A9,"*mangga*")+COUNTIF(A2:A9,"*pisang*")


Menggunakan Fungsi COUNTIF untuk Menemukan Nilai Duplikat dan Nilai Unik


Kegunaan lainnya dari fungsi COUNTIF pada Excel adalah untuk menemukan nilai duplikat, baik pada satu kolom, antara dua kolom maupun dalam baris.

Contoh 1 : Menemukan dan Menghitung Banyaknya Duplikat dalam 1 Kolom

Sebenarnya dari awal pembahasan kita sudah memahami cara menghitung banyaknya duplikat data di excel. Hal ini karena fungsi COUNTIF sendiri berguna untuk menghitung sel dengan kondisi yang sama.  Artinya jika kita menghitung sel dengan kondisi yang persis sama, maka kita juga bisa mengetahui adanya duplikat jika rumus menghasilkan nilai lebih dari satu.

Menggunakan Kolom Bantu

Untuk duplikat, kita bisa menandainya dengan bantuan operator komparasi lebih dari (>) untuk mengecek hasil fungsi COUNTIF. Jika hasil lebih dari 1 maka ditandai TRUE, dan jika tidak ada dplikat maka ditandai FALSE. cek TRUE/FALSE ini disimpan dalam sebuah kolom bantu.

Sebagai contoh, rumus sederhana =COUNTIF($A$2:$A$10,$A2)>1 dapat digunakan untuk mengecek apakah nilai dalam sel A2 memiliki duplikat atau tidak pada range A2:A10. Dengan mengcopy rumus tersebut ke baris dibawahnya maka kita bisa mengecek semua duplikat pada range A2:A10

Selanjutnya, untuk menghitung banyaknya duplikaat yaitu dengan carmenghitung banyaknya TRUE dalam kolom bantu seperti rumus berikut:
=COUNTIF(B2:B10,TRUE)

Tanpa Kolom Bantu

Untuk menghitung banyaknya nilai duplikat tanpat kolom bantu, maka kita dapat menggunakan fungsi SUMPRODUCT yang dikombinasikan dengan COUNTIF.

=SUMPRODUCT((COUNTIF(A2:A10,A2:A10)>1)*(A2:A10<>""))

Untuk lebih jelasnya, perhatikan screenshoot berikut yang mengilustrasikan bagaimana cara menggunakan rumus excel untuk menghitung nilai duplikat, baik menggunakan kolom bantu ataupun tidak.

Fungsi COUNTIF Menghitung Duplikat Dalam Kolom


Contoh 2 : Menghitung Banyaknya Duplikasi Antara Dua Kolom

Jika kita memiliki dua list terpisah, katakanlah  list nama pada kolom B dan C. Kemudian kita ingin mengetahui berapa kali nama-nama tersebut muncul pada kedua kolom.

Cara menghitungnya adalah menggunakan kombinasi antara fungsi SUMPRODUCT dengan fungsi COUNTIF

Menghitung banyaknya nama di kolom A yang muncul kembali di kolom B

=SUMPRODUCT((COUNTIF(A2:B10,B2:B10)>1)*(B2:B10<>""))

Menghitung banyaknya nama di kolom B yang tidak ada di kolom A

=SUMPRODUCT((COUNTIF(A2:B10,B2:B10)=1)*(B2:B10<>""))


Rumus COUNTIF Menghitung Duplikat Dua Kolom


Contoh diatas hanya digunakan untuk list sampai baris 10. Untuk data yang lebih banyak silahkan dimodifikasi referensi range tersebut. Misal: A2:B10 menjadi A2:B100 atau A2:B1000 dan seterusnya.

Contoh 3 : Menghitung Banyaknya Nilai Duplikat dan Nilai Unik dalam Sebuah Baris

Kombinasi antara fungsi SUMPRODUCT  dan fungsi COUNTIF  juga diperlukan untuk  menghitung banyaknya nilai duplikat maupun nilai unik dalam baris,

Menghitung banyaknya nilai duplikat dalam baris (range A1:M1)

=SUMPRODUCT((COUNTIF(A1:M1,A1:M1)>1)*(A1:M1<>""))

Menghitung banyaknya nilai unik dalam baris (range A1:M1)

=SUMPRODUCT((COUNTIF(A1:M1,A1:M1)=1)*(A1:M1<>""))


Fungsi COUNTIF Duplikat Dalam Row



Penjelasan dan Pemecahan Masalah Seputar Penggunaan Fungsi COUNTIF


Setidaknya ada 5 masalah yang paling sering ditanyakan seputar penggunaan fungsi COUNTIF pada Excel. Berikut akan  akan disampaikan kelima masalah tersebut dan solusinyaj. Semoga bermanfaat.

1. COUNTIF Pada Range Terputus


Pertanyaan: Apakah Fungsi COUNTIF dapat digunakan terhadap range yang terputus, atau beberapa sel yang diseleksi.

Jawaban : Fungsi COUNTIF tidak dapat bekerja pada range yang terputus, gabungan beberapa range atau beberapa sel.

Solusi: untuk mendapatkan perhitungan pada beberapa range atau sel yang terpisah maka kita harus menggunakan beberapa fungsi COUNTIF.

Rumus yang salah:
=COUNTIF(A2,B5,C2,">0")

Rumus yang Benar:
 =COUNTIF(A2,">0") + COUNTIF(B5,">0") + COUNTIF(C2,">0")

Alternatif solusi lainnya adalah menggunakan kombinasi fungsi SUM, COUNTIF dan INDIRECT
Printscreen berikut menggambarkan cara penggunaan jumlah bilangan 0 pada dua range yang berbeda yaitu range B2:B10 dan C2:C10

Cara 1 : Menggunakan beberapa fungsi COUNTIF

=COUNTIF(B2:B10,0)+COUNTIF(C2:C10,0)

Cara 2 : Menggunakan kombinasi SUM, COUNTIF dan INDIRECT.

=SUM(COUNTIF(INDIRECT({"B2:B10","C2:C10"}),0))

Kombinasi SUM, COUNTIF INDIRECT



2. Ampersand dan tanda petik dalam rumus COUNTIF


Pertanyaan: Kapan kita perlu menggunakan tanda ampersand (&) dan tanda petik ("") di dalam formula COUNTIF?

Jawaban: Penggunaan ampersand dan tanda kutip dalam fungsi COUNTIF mungkin agak membingungkan. Saya sendiri merasakan hal demikian ketika pertama kali bergelut dengan rumus ini. Namun dengan menelaahnya dengan sesama, kita akan memahami bahwa penggunaan ampersand dan tanda  petik dua ternyata adalah untuk membangun string argumen criteria. Ikuti aturan main berikut dalam menggunakan ampersand dan tanda petik:


  • Jika kita menggunakan bilangan atau referensi sel sebagai criteria yang persis sama (exact match) maka tanda ampersand maupun tanda petik tidak diperlukan

Contoh:
=COUNTIF(A1:A10,10)
=COUNTIF(A1:A10,C1


  • Jika kriteria yang digunakan berupa text, karakter wildcard atau operator komparasi yang digabung dengan bilangan maka gunakanlah tanda petik.

Contoh:
=COUNTIF(A2:A10,"pisang")
=COUNTIF(A2:A10,"*")
=COUNTIF(A2:A10,">10")


  • Jika kita menggunakan expresi logika dengan operator komparasi yang mengacu pada sel referensi atau fungsi excel lainnya, maka kita perlu meletakan operator komparasi dalam tanda petik ("") dan menggunakan ampersand  (&) untuk menggabungkan operator komparasi dengan referensi atau fungsi lainnya.

Contoh:
=COUNTIF(A2:A10,">"&D2)
=COUNTIF(A2:A10,"<="&TODAY())


  • Jika kita merasa ragu apakah ampersand dan tanda petik diperlukan atau tidak, maka kita dapat mencoba keduanya. Biasanya kedua cara tersebut sama-sama bekerja dan menghasilkan nilai yang sama.

Contoh:
Formula =COUNTIF(C2:C8,"<=5")
Sama saja hasilnya jika dituliskan =COUNTIF(C2:C8,"<="&5)


3. Menghitung Banyaknya Sel Dengan Warna Tertentu


Pertanyaan : Bagaimana cara menghitung sel berdasarkan warna

Jawaban : Sangat disayangkan bahwa fungsi COUNTIF ternyata tidak bisa digunakan untuk menghitung banyaknya sel berdasarkan warna.

Solusi : Satu-satunya cara yang dapat digunakan untuk menghitung banyaknya sel berdasarkan warna adalah menggunakan macro atau code VBA.

Contoh:

Code VBA berikut dapat digunakan untuk menghitung banyaknya sel pada rangeWarna sesuai warna pada selAcuan

Copy code berikut pada modul standar dalam vba editor (untuk membuka vba editor , tekan ALT + F11, kemudian tekan insert, dan tekan module untuk memunculkan module standar baru)

Function hitungWarna(selAcuan As Range, rangeWarna As Range)
Dim sel As Range
For Each sel In rangeWarna
    If sel.Interior.Color = selAcuan.Interior.Color Then
        hitungWarna = hitungWarna + 1
    End If
Next
End Function

Fungsi hitungWarna ini kita kenal sebagai salah satu contoh dari User Defined Function (UDF = fungsi yang dibuat sendiri oleh user). Fungsi ini dapat digunakan dalam lembar kerja excel seperti halnya menggunakan fungsi biasa,

Contoh rumus :   =hitungwarna(D1,A2:A9)

Rumus tersebut digunakan untuk menghitung sel pada range A2:A9 dengan warna yang sama dengan sel D1.

Count If warna tertentu



5. Rumus COUNTIF menghasilkan nilai Error #NAME?


Pertanyaan: Bagaimana cara mengatasi error #NAME? pada rumus COUNTIF?

Jawaban : error #NAME? disebabkan karena kesalahan penulisan/spelling fungsi, penulisan referensi, atau kesalahan penggunaan nama range dan nama sel.
Misal COUNTIF diketik COUNTIFF

Solusi : Cek kembali carai penulisan fungsi, range, nama range atau nama sel. Jika menggunakan nama range atau nama sel, cek apakah nama tersebut sudah didefinisikan.

5. Rumus Excel COUNTIF tidak bekerja


Pertanyaan : Saya sudah membuat rumus COUNTIF dengan syntax yang benar. Namun kenapa tidak menghasilkan data yang benar ketika dicopy ke sel lain.

Jawaban: Hal ini disebabkan calculation setting di-set manual.

Solusi : Tekan F9 atau ubah calculation setting menjadi automatic. Penjelasan lebih detail tentang kenapa rumus excel tidak berfngsi dapat dibaca pada artikel: Rumus Excel Tidak Berfungsi? Inilah Penyebabnya!

Akhir kata, demikian pembahasan fungi COUNTIF dan contoh-contoh rumus terapannya dalam lembar kerja excel. Semoga bermanfaat.

Artikel Terkait



Referensi:
https://www.ablebits.com/office-addins-blog/2014/07/02/excel-countif-examples/

PANDUAN LENGKAP PENJUMLAHAN DI EXCEL

Fungsi SUM dan Rumus Penjumlahan Pada Excel

Penjelasan Lengkap Rumus Penjumlahan Pada Excel Ada banyak cara untuk melakukan penjumlahan pada Microsoft Excel. Cara yang paling sederhan...