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

Kombinasi OFFSET dan MATCH

Beberapa postingan sebelumnya kombinasi dari INDEX + MATCH dan VLOOKUP + MATCH, sekarang mari kita lihat OFFSET + MATCH, yah memang jarang dipakai, but knowledge is power. Semakin kita banyak tahu sesuatu, Insya Allah kita jadi orang yang beruntung karena berpengetahuan lebih.

Tabel Kasus :

Aplikasi 1: Reguler

Aplikasi 2: LEFT LOOKUP

Aplikasi 3: Cross Referencing

Tabel Kasus

Penerapan Fungsi LOOKUP

Walaupun fungsi kurang populer karena mempunyai beberapa keterbatasan dibandingkan VLOOKUP dan Kombinasi INDEX + MATCH, tidak ada salahnya belajar cara penerapan dari fungsi ini.

Syarat yang harus dipenuhi atau Fungsi ini mengandung kelemahan :

1. Fungsi ini tidak mempunyai opsi untuk argumen EXACT MATCH atau FALSE seperti yang kita tahu tersedia pada Fungsi VLOOKUP. So kita harus hati menggunakan fungsi ini. Jika nikai yang kita cari tidak ada, maka nilai terbesar atau tertinggi yang akan diambil.
2. Data yang tersedia pada Tabel Database harus tersortir, jika tidak fungsi akan menampilkan hasil yang keliru.

Tabel Database

Contoh penyelesaian kasus dengan Fungsi LOOKUP
Aplikasi 1:

Aplikasi 2:

Aplikasi 3:

Aplikasi 4:

Aplikasi 5: Mencari Nilai dan Status dari Score Range

Aplikasi 6:

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