Menghitung dengan Multi Kriteria

Postingan saya kali ini ingin mengupas penggunaan rumus Excel untuk menghitung dengan multi kriteria.

Tabel Kasus

Kriteria yang diterapkan:
1. Tempe
2. >= 1 Januari 2013
3. Samidi

Rumus yang bisa digunakan:

Mengambil Data dengan Multi Kriteria

Langsung saja ke TKP

Tabel Kasus:

4 Kriteria :

Dan kita ingin mengambil data pada kolom ke 5 (Kolom Sales).

Rumus 1:
=SUMIFS(E2:E11;A2:A11;B14;B2:B11;B15;C2:C11;C14;D2:D11;C15)

Rumus 2:
=SUMPRODUCT(E2:E11;(B2:B11=B15)*(A2:A11=B14)*(C2:C11=C14)*(D2:D11=C15))

Rumus 3: Rumus Array
=INDEX(E2:E11;MATCH(B14&B15&C14&C15;A2:A11&B2:B11&C2:C11&D2:D11;0))

Rumus 4: Rumus Array
=VLOOKUP(B14;IF((B2:B11=B15)*(C2:C11=C14)*(D2:D11=C15);A2:E11;"");5;FALSE)

Rumus 5: Rumus Array
=SUM(E2:E11*(B2:B11=B15)*(A2:A11=B14)*(C2:C11=C14)*(D2:D11=C15))

Rumus 6: Rumus Array
=VLOOKUP(B14&"/"&B15&"/"&C14&"/"&C15;CHOOSE({1\2\3\4};A2:A11&"/"&B2:B11&"/"&C2:C11&"/"&D2:D11;E2:E11);2;FALSE)

Rumus 7: Rumus Array
=INDEX(E2:E11;MATCH(1;(A2:A11=B14)*(B2:B11=B15)*(C2:C11=C14)*(D2:D11=C15);0))

Rumus 8: Rumus Array
=INDEX(E2:E11;SUMPRODUCT(--(A2:A11=B14)*(B2:B11=B15)*(C2:C11=C14)*(D2:D11=C15)))

Rumus 9: Rumus Array
=INDEX(E2:E11;MATCH(1;IF(A2:A11=B14;IF(B2:B11=B15;IF(C2:C11=C14;IF(D2:D11=C15;1));0))))

Rumus 10: Rumus Array
=IFERROR(INDEX(E:E;MATCH(1;(A:A=B14)*(B:B=B15)*(C:C=C14)*(D:D=C15);0));0)

Rumus 11: Rumus Array
=INDEX(A2:E11;MATCH(B14&B15&C14&C15;A2:A11&B2:B11&C2:C11&D2:D11;0);5)

dan mungkin masih ada variasi rumus yang lain dan belum ditemukan...... silahkan anda coba2x

Download File Latihan

Mencari Data Dengan Kriteria Rentang Tanggal (Date Ranges)

Pernahkah anda mempunyai kasus seperti ini, tabel kita merupakan tabel data jadual kegiatan yang mencantumkan rentang tanggal yang berisikan informasi ataupun kegiatan. Lebih jelasnya cobalah tengok tabel dibawah ini.

Tabel Kasus 1:

Hasil yag kita inginkan adalah informasi atau kegiatan yang tercakup di dalam rentang tanggal yang ada di Tabel Data. Pada contoh diatas, kita input pada cell C8 adalah tanggal 7 Desember 2013, dan tanggal tersebut terletak pada Rentang Tanggal "Trimester 4".

Rumusnya (Array Formula) :

Tabel Kasus 2:

Contoh kedua ini hampir sama dengan contoh 1, hanya beda format penulisan rentang tanggalnya.

Rumusnya (Array Formula):

Tabel Kasus 3: (Date Ranges terpisah di 2 Kolom

Rumusnya (Array Formula):

Tabel Kasus 4: (2 Kegiatan Berbeda di Satu Rentang Tanggal)

Rumusnya (Array Formula):

Tabel Kasus 5:

Rumusnya (Array Formula):

Tabel Kasus 6:

Hasil yang dinginkan:

Download File Latihan

Membuat Data Angka dan Teks Secara Acak

Saya perbaiki lagi konten tentang me-random atau meng-acak baik itu Angka (Number) maupun Teks, nah Excel menyediakan fungsi yang untuk keperluan ini yaitu Fungsi RAND() dan RANDBETWEEN(), sebetulnya mereka bekerja untuk mengacak angka, tetapi jika dikombinasi dengan Fungsi yang lain seperti INDEX dan VLOOKUP maka data teks-pun akan bisa diacak. Untuk mengacak data menggunakan fungsi diatas dengan cara menekan tombol F9.

TEORI DASAR:
1. Random Angka dengan RAND()
Angka yang random adalah lebih besar atau sama dengan 0 dan lebih kecil dari 1.

    


2. Random Angka dengan Kriteria:
    
   Contoh diatas menghasilkan angka antara 1 (CellA8) dan 4 (Cell B8), contoh rumus yang lainnya
   


3. Random dengan Kriteria dengan RANDBETWEEN()
    

PENERAPAN:
Misalkan ada kasus pembuatan jadual shift jaga malam satuan keamanan atau security, nah tabel kasusnya seperti ini misalnya:

Dan hasil yang ingin ditampilkan seperti ini

Jadi pada cell warna kuning adalah nama yang diambil secara acak pada tabel data, nah saya memakai drop down list untuk memudahkan mengacak data, jika saya pilih "Start" maka mulai me-random data, jika saya pilih kosong atau blank, maka blank juga daftar namanya.

Rumusnya untuk mengacak Nama, harus digabungkan dengan Fungsi Lookup, seperti ini:
1. =IF($C$14="Start";VLOOKUP(RANDBETWEEN(1;10);$A$2:$B$11;2;FALSE);"")
2. =IF($C$14="Start";INDEX($B$2:$B$11;RANDBETWEEN(1;10));"")

Alternatif lain :
1.=IF($C$14="Start";VLOOKUP(RANDBETWEEN(1;4);{1\"Yasir";2\"Dedi";3\"Jalal";4\"Fahmi";5\"Agus"};2);"")

2.=IF($C$14="Start";INDEX({"Dedi"\"Agus"\"Fatur"\"Samidi"\"Joko"};RANDBETWEEN(1;5));"")
3.=IF($C$14="Start";INDEX({"Agus"\"Sukar"\"Sayar"\"Nrimo"\"Batin"};INT(RAND()*5+1));"")

 Supaya lebih mudah belajar, silahkan di-unduh file latihannya

Mencari Data dengan Multi Tabel

Ass,Wr,Wb.

Morning guys, saya akan share kasus mencari data dengan 2 tabel atau lebih

Tabel Kasus :

Sebelumnya kita memberikan named ranges pada tabel2x kita

Tabel pertama diberi nama "Tabel1"

Tabel kedua diberi nama "Tabel2"

Solusi 1: VLOOKUP DAN CHOOSE

Solusi 2: VLOOKUP DAN INDIRECT

Contoh diatas adalah mmencari data dengan multi tabel tetapi dengan bantuan cell input pilihan tabel (Cell B11 dan B16). Saya akan berikan contoh yang tanpa itu, data yang diambil dari 2 tabel juga.

Tabel Kasus:

Kemudian kita ingin hasilnya seperti ini:

Hasil yang kita inginkan adalah mengambil data penjualan dari beberapa tabel (contoh menggunakan 2 tabel), rumus yang digunakan adalah rumus array.

=IFERROR(INDEX($A$2:$B$5;SMALL(IF(($A$14=$A$2:$A$5);ROW($A$2:$A$5)-MIN(ROW($A$2:$A$5))+1;"");ROW(A1));2);INDEX($A$8:$B$11;SMALL(IF(($A$14=$A$8:$A$11);ROW($A$8:$B$11)-MIN(ROW($A$8:$B$11))+1;""); ROW(A1)-SUM(--($A$14=$A$2:$A$5)));2))

inputlah pada cell B14 (sesuai contoh diatas) kemudian CTRL-SHIFT-ENTER kemudian copylah ke bawah

Bagaimana dengan data yang diambil dari 3 Tabel? makin ribet ya kayaknya wkwkwkwk......

Tabel Kasus:

Hasil yang diinginkan:

Ada 2 rumus yang bisa diplikasikan dalam kasus diatas, yaitu

=IFERROR(VLOOKUP(B27;A3:D7;4;FALSE);IFERROR(VLOOKUP(B27;A11:D15;4;FALSE);IFERROR(VLOOKUP(B27;A19:D23;4;FALSE);"")))

dan

=IF(NOT(ISERROR(VLOOKUP(B27;A3:D7;4;FALSE)));VLOOKUP(B27;A3:D7;4;FALSE);IF(NOT(ISERROR(VLOOKUP(B27;A11:D15;4;FALSE)));VLOOKUP(B27;A11:D15;4;FALSE);IF(NOT(ISERROR(VLOOKUP(B27;A19:D23;4;FALSE)));VLOOKUP(B27;A19:D23;4;FALSE);"")))

Menjumlahkan Otomatis Sesuai Kriteria

Saya iseng-iseng bagaimana ya bikin rumus yang bisa menjumlahkan otomatis dari data sales (penjualan) dari awal bulan sampai bulan tertentu sesuai kriteria yang ditentukan.

Tabel Kasus 1:

Misalkan saya mengetikkan nama bulan "Agustus, maka rumus akan mengkalkulasi sales (penjualan) dari bulan Januari sampai Agustus.



Silahkan anda cek dengan menjumlahkan secara manual dengan SUM, apakah rumus saya diatas sudah benar?

Bagaimana dengan kriteria data sales sampai hari ini dengan menggunakan fungsi TODAY?

Tabel Kasus 2:
Misalnya saya jualan warung tenda dengan menu :
1. Mie Rebus   Rp.6000
2. Mie Goreng Rp.6500
3. Cap Cay      Rp.5500

Dan saya pengennya pake Excel untuk menghitung penjualan warung tenda saya secara update sesuai item yang laku pada sehari.

Kriteria harga makanan warung tenda saya dimasukkan di dalam formula diatas, karena item makanan hanya ada sedikit, jadi saya pikir tidak perlu pake rumus VLOOKUP wkwkwk..........

Oh ya yang terakhir itu rumus Array ya teman..... atau CSE Formula

Download File Latihan

Pengambilan Data Terakhir (Last Match)

Ass.Wr.Wb

Saya ingin memberikan contoh penerapan rumus Excel untuk mengambil data yang terakhir atau yang ter-update. Kasus ini banyak ditanyakan di forum-forum rumus excel.

KASUS 1

Hasil yang diinginkan adalah, jika kita mengetikkan nama dari Salesman maka akan muncul data penjualan yang terakhir. MIsalnya kita ketik "Samidi" maka hasil yang muncul adalah Rp.210.000

How to do it with Excel?

Bagi yang sudah sering pakai kombinasi INDEX dan MATCH pasti tahu bahwa hasil yang muncul adalah data yang pertama, so kombinasi rumus ini tidak bisa kita pakai.

Tetapi INDEX masih powerful dikombinasi dengan fungsi yang lainnya

Lebih jelasnya hasil yang diinginkan adalah seperti ini

Jadi semuanya adalah hasil terakhir sesuai single kriteria "Nama"

Rumus 1 (rumus reguler):
=INDEX(B2:B12;LOOKUP(2;1/(A2:A12=D2);ROW(A2:A12)-1))

Rumus 2 (rumus array):
=LOOKUP(9,99999999999999E+307;IF(A2:A12=D2;B2:B12))

Rumus 3 (rumus reguler):
=INDEX(B2:B12;SUMPRODUCT(MAX((A2:A12=D2)*(ROW(B2:B12)-1))))

Rumus 4 (rumus array):
=LOOKUP(999;IF(A2:A12=D2;ROW(A2:A12));B2:B12)

Rumus 5 (rumus array):
=INDEX(B:B;MAX((A2:A12=D2)*ROW(A2:A12)))

Rumus 6 (rumus reguler):
=INDEX(B2:B12;MATCH(1;INDEX(1/(D2=A2:A12););1))

Rumus 7 (rumus array):
=INDEX(B2:B12;MATCH(1;1/(D2=A2:A12);1))

KASUS 2

dan hasil yang diharapkan adalah seperti ini

rumus adalah seperti ini (rumus reguler atau non array):
=LOOKUP(9,99E+307;INDEX($B$2:$D$6;MATCH(A9;$A$2:$A$6;0);0))

KASUS 3

dan hasil yang diinginkan tampilannya seperti ini

Jadi kasus ke 3 ini adalah mengambil hasil yang terakhir dengan 2 kriteria, rumusnya seperti ini
=INDEX($C$1:$C$12;SUMPRODUCT(MAX(--($B$2:$B$12=F2)*($A$2:$A$12=E2)*ROW($B$2:$B$12))))

ini rumus reguler ya atau rumus non array

Mudah2xan dapat diterapkan pada kasus yang anda temui sehari-hari, silahkan di unduh file latihannya DISINI

Pelajaran Basic Fungsi INDEX dan MATCH

Bagi yang masih awam mengenai Fungsi INDEX, saya akan memberikan contoh sederhana penggunaannya

Bentuk penulisan :

1. =INDEX(array, row_num, [column_num])
2. =INDEX(reference, row_num, [column_num], [area_num])

column_num and area_num are optional arguments


Contoh 1:

Contoh 2: Penggunaan 2 Tabel dengan Fungsi INDEX

Contoh 3: Array Formula dan Copas ke Kanan

Contoh 4: Array Formula dan Copas ke Bawah

Contoh 5: Meng-copy Data dengan Array Formula

Contoh 6: Modifikasi dengan Array Formula

Fungsi MATCH

Returns the relative position of an item in an array that matches  a specified value in a specific order

Bentuk Penulisan :

=MATCH(lookup_value, lookup_array, [match_type])

match_type:

1 = Finds the largest value that is less than or equal to lookup_value.
      Lookup_array must be placed in ascending order

0 = Finds the first value that is exactly equal to lookup_value.
      Lookup_array can be in any order.

-1 = Finds the smallest value that is greater than or qual to lookup_value.
        Lookup_array must be placed in descending order

Contoh 1: Dengan Argumen 0 (Data tidak perlu di Sort)

Contoh 2: Dengan Argumen 1 (Data harus urut dari kecil ke besar)

Contoh 3: Dengan Argumen -1 (Data harus urut Besar ke Kecil)

Contoh 4: Array Formula