Serba Serbi SUMPRODUCT

Hari ini saya mencoba posting topik tentang Fungsi SUMPRODUCT, kelebihan Fungsi ini adalah SUMPRODUCT dapat meng-handel Array tanpa kita menekan tombol CSE (CTRL-SHIFT-ENTER), hanya dengan tekan ENTER saja maka Fungsi ini akan melakukan kalkulasi. Kelebihan yang lainnya dibanding Fungsi SUM adalah, SUMPRODUCT dapat mengkalkulasi suatu data berdasarkan multi kriteria.

Contoh 1:

Rumus Alternatif:
=SUM((B2:B4)*(C2:C4))


Contoh 2:

Pada contoh diatas SUMPRODUCT melakukan kalkulasi berdasarkan 1 kriteria yaitu "Jawa" yang muncul sebanyak 2 kali pada Tabel, coba perhatikan rumusnya, disana ditambah 0, apa maksudnya?

Saya akan jelaskan langkah demi langkah, cobalah anda blok range B11:B15=B17 sesuai contoh diatas, kemudian tekanlah tombol F9 untuk mengetahui kalkulasi yang dilakukan oleh Excel.

Excel melakukan kalkulasi berupa kode Boolean yaitu True dan False, langkah selanjutnya bloklah seperti contoh dibawah

Kemudian tekanlah F9, apa yang akan terjadi yaaa........?

So dengan fungsi ditambah dengan 0 adalah merubah kode Boolean "True dan :False" menjadi kode 1 dan 0, dengan demikian SUMPRODUCT dapat melakukan kalkulasi data sesuai kriteria tertentu.

Ada beberapa cara untuk merubah kode Boolean menjadi kode 1 dan 0, yaitu:
1. Dengan karakter dobel negatif "--"
2. Dengan ditambah angka 0
3. Dikurangi dengan angka 0
4. Dengan dikalikan angka 1
5. Atau dibagi angka 1

Saya akan beri contoh penggunaan masing2xnya.

Rumus Alternatif:
=SUM(--(B11:B15=B17)) --> Rumus Array
=SUM((B11:B15=B17)*1) --> Rumus Array
=SUM((B11:B15=B17)+0) --> Rumus Array
=COUNTIFS(B11:B15;B17)
=COUNTIF(B11:B15;B17)
=SUMPRODUCT(--(B11:B15=B17))
=SUMPRODUCT((B11:B15=B17)/1)
=SUMPRODUCT((B11:B15=B17)*1)
=SUMPRODUCT((B11:B15=B17)-0)


Contoh 3:

Pada contoh diatas SUMPRODUCT melakukan kalkulasi sesuai 2 kriteria "Jawa" dan "Yogyakarta", dan disana ada tanda dobel negatif pada  --(B22:B28=B30) dan --(C22:C28=C30), inilah contoh merubah kode Boolean menjadi angka 1 dan 0 sesuai keterangan saya diatas.

Rumus Alternatif:
=SUMIFS(D22:D28;B22:B28;B30;C22:C28;C30)
=SUM((B22:B28=B30)*(C22:C28=C30)*(D22:D28)) --> Rumus Array
=SUMPRODUCT((B22:B28=B30)*(C22:C28=C30);(D22:D28))
=SUMPRODUCT((B22:B28=B30)*(C22:C28=C30)*(D22:D28))
=SUMPRODUCT(--(B22:B28=B30);--(C22:C28=C30);--(D22:D28))


Contoh 4:

Rumus Alternatif:
=SUM((B35:B41=B43)*(D35:D41))+SUM((C35:C41=C43)*(D35:D41)) --> Rumus Array
=SUMIFS(D35:D41;B35:B41;B43)+SUMIFS(D35:D41;C35:C41;C43)
=SUMIF(B35:B41;B43;D35:D41)+SUMIF(C35:C41;C43;D35:D41)


Contoh 5:

Contoh diatas merupakan variasi formula SUMPRODUCT digunakan melakukan kalkulasi berdasarkan 2 kriteria.

Rumus Alternatif:
=SUM((B47:B52>=C54)*(B47:B52<=C55)*(C47:C52)) --> Rumus Array
=SUM(IF(B47:B52>=C54;IF(B47:B52<=C55;C47:C52;0);0)) --> Rumus Array
=SUMIFS(C47:C52;B47:B52;">="&C54;B47:B52;"<="&C55)
=SUMIF(B47:B52;">="&C54;C47:C52)-SUMIF(B47:B52;">"&C55;C47:C52)


Contoh 6:

Contoh 7:

Contoh disini SUMPRODUCT melakukan perhitungan berdasarkan 4 kriteria dipadukan dengan COUNTIF.

Berlatihlah menggunakan contoh diatas supaya anda semakin mahir menggunakan fungsi ini.

Download File Latihan

Penerapan Fungsi INDIRECT dan ADDRESS (2)

Posting kali ini masih membahas penggunaan fungsi INDIRECT dan ADDRESS, terutama fokus ke INDIRECT.

Langsung saja ke TKP, Tabel Kasus INDIRECT

Hampir mirip ya Tabelnya dengan postingan saya sebelumnya, tetapi sebetulnya berbeda

Langkah 1: Blok tabel database untuk men-definisikan nama label baris dan label kolom

Langkah 2: Tekan tombol CTRL-SHIFT-F3

Langkah 3: Klik-lah pilihan Top Row dan Left Column, kemudian nama label baris dan label kolom tampil di Name Box seperti ini.

Ada yang berbeda disana, coba diperhatikan pada Label Baris yaitu nama-nama Propinsi, Excel merubah penulisan nama-nama Propinsi dengan menyambungnya dengan tanda GARIS BAWAH --> Jawa_Timur, Jawa_Barat dst.

Ini mengakibatkan rumus INDIRECT gagal meng-kalkulasi,

 See muncul #REF! disana karena INDIRECT tidak menemukan referensi cell yang benar.

Pemecahannya adalah di dalam rumus INDIRECT kita harus merubah format penulisan nama-nama Propinsi
Jawa Tengah --> menjadi Jawa_Tengah
Jawa Barat --> menjadi Jawa_Barat
Jawa Timur --> menjadi Jawa_Timur
DI. Yogyakarta --> menjadi DI._Yogyakarta

Dengan Fungsi SUBTITUTE hal ini bisa dilakukan...

Kemudian masukkanlah tadi di dalam rumus INDIRECT

dan selanjutnya inputlah Rumus INDIRECT yang kedua

And hit ENTER

Kasus Lain dengan INDIRECT:

Tahapan penyelesaian kasus sama seperti contoh sebelumnya, yaitu kita memberi nama paa Label Baris dan Kolom dengan menekan CTRL-SHIFT-F3.

Let see, kita menemukan problem yang hampir sama format tanggal dari Excel adalah _01_-1_2013, so kita harus merubah format tanggal kita menjadi format Excel tersebut.

And then......put in INDIRECT function

And then.....

Download File Latihan

Penerapan Fungsi INDIRECT dan ADDRESS (1)

Seperti janji saya di postingan sebelumnya, saya akan memberikan contoh aplikasi atau penerapan dari kedua fungsi ini.

Kasus 1:

Berdasarkan Tabel database diatas kita ingin mengambil data berdasarkan kriteria:

Region = Bali

Month= Juni

Tetapi pertama-tama kita harus memberi nama pada masing LABEL KOLOM dan LABEL BARIS yang nantinya akan digunakan Fungsi INDIRECT untuk mengambil data.

How To Do It?

Bloklah Tabel Database dengan menggunakan tombol mouse atau keyboard....

Kemudian tekan tombol CTRL-SHIFT-F3 bersamaan.............. dan akan muncul Dialog Box "Create Names From Selection"

Klik-lah menggunakan mouse pada pilihan Top Row dan Left Column yang disana terletak Label Kolom dan Label Baris di tabel kita.

Kemudian ceklah pada Name Box......

Ok, nama-nama label baris dan kolom sudah masuk disana

RUMUS 1:

= INDIRECT(C8) INDIRECT(C9)  ---> 2 fungsi INDIRECT dipisahkan oleh spasi.

RUMUS 2:

Pada rumus kedua ini terdiri dari 2 MATCH yang mengembalikan Row Number dan Column Number, yang diperlukan oleh Fungsi ADDRESS untuk mengembalikan alamat cell dari kriteria "Bali" dan "Juni".

Bagaimana cara men-ceknya?

Pertama kliklah 2 kali atau tekan F2 pada cell C16 yang mengandung rumus diatas... kemudian blok-lah menggunakan mouse fungsi MATCH yang pertama...

Kemudian tekanlah tombol F9 dan akan tampil row number "6"

Dengan tampilnya angka "6" berarti rumus kita tidak mengalami error, untuk kembali menampilkan rumus tekanlah CTRL-Z.

Selanjutnya lakukan hal yang sama dengan Fungsi MATCH yang kedua

Kemudian tekan F9, dan tampillah Column Number yaitu "7"

CTRL-Z untuk menampilkan rumus kembali.

Silahkan anda ber-eksperimen dengan tombol F9 untuk melihat bagaimana Excel mengkalkulasi rumus-rumus yang kita tulis.

F9 dan muncullah alamat cell sesuai kriteria

Dengan INDIRECT diambillah value dari alamat cell G6, yaitu 4400

Download File Latihan

Fungsi INDIRECT dan ADDRESS

Rekan-rekan pernah mendengar atau menggunakan Fungsi INDIRECT? Kalau belum, baiklah saya jelaskan sedikit mengenai kegunaan fungsi ini. Dari arti nama berarti "Tidak Secara Langsung", ya betul fungsi ini secara tidak langsung mengambil value tertentu dari suatu referensi cell. Contoh konkrit seperti ini.

1. Inputlah angka 100 pada Cell A1.

2. Inputlah A1 (yang merupakan referensi cell A1 berisi angka 100) pada Cell A5.





Kemudian
3. Ketiklah rumus =INDIRECT(A5) PADA CELL A7

Akan muncul hasil

See, fungsi ini mengambil value dari cell A1 secara tidak langsung berdasarkan alamat referensi cell.



Cuman gitu doang? ya enggak dong, fungsi ini cukup powerful jika digabung dengan dengan fungsi2x yang lain.

Selanjutnya mari kita bahas fungsi ADDRESS, kita masih menggunakan contoh yang diatas,

 Ketiklah rumus =ADDRESS(1;1) pada cell A5

Angka 1 pertama adalah baris pertama

Angka 1 kedua adalah kolom pertama

Rumus ini akan menghasilkan alamat cell dari cell A1 yang terletak pada baris dan kolom pertama.

See? semoga jelas ya...

Selanjutnya.........

Inputlah rumus =INDIRECT(A5) yang secara tidak langsung juga mengambil value dari Cell A1 yaitu angka 100.





Sekarang cobalah rumus ini =INDIRECT(ADDRESS(1;1)) akan menghasilkan hasil yang sama yaitu  angka 100.

Kelemahan fungsi ADDRESS adalah dia hanya dapat menampilkan alamat cell sebagai teks, untuk dapat mengambil data diperlukan fungsi INDIRECT, seperti contoh diatas.

Secara default fungsi ADDRESS menampilkan alamat cell dalam keadaan absolut

Tanda dollar mengunci kolom B dan baris 1.

Ada tips khusus jika anda ingin menampilkannya dengan posisi yang relatif. Yaitu tambahkanlah argumen dengan angka 4.






Perhatikan contoh berikut:

Tampilan alamat cell menjadi.....

Atau anda ingin menggunakan INDIRECT dengan gaya R1C1 ? dengan menggunakan contoh diatas...

Huruf "R" disambung dengan value dari cell B2 adalah "1" menjadi "R1" atau Row One (Baris Pertama).

Huruf "C" disambung dengan value dari cell B3 ada "2" menjadi C3 atau Column Third (Kolum Ketiga).


Bisa ditebak kan data yang tampil......?

Sekian dulu rekan2x, postingan berikutnya Insya Allah saya akan memberikan contoh penerapan atau aplikasi yang menggunakan rumus INDIRECT dan ADDRESS.