Featured Post

Cara Menggunakan Rumus untuk Data Validation

Bagaimana cara menggunakan rumus untuk validasi data di Excel? Dengan rumus, kita bisa melakukan kustomisasi data validation. Yuk kita lihat...

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. Ketika 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

Artikel Terkait




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.





Kenapa Rumus Excel Tidak Berfungsi

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, kita bisa melihat bahwa ada banyak tugas yang dapat diselesaikan menggunakan fungsi SUMPRODUCT, juga dapat diselesaikan menggunakan rumus array (CSE).

Supaya masih hangat dalam benak pembaca, saya 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: