Selamat Siang Semuanya........
Glad to be back, mohon maaf agak lama tidak posting karena saya sakit flu, maklum lagi musim pancaroba, penyakit berjangkit dimana-mana.
Yang terpenting hari ini ada postingan baru yak... kali ini membahas 2 Way Lookup lagi ya tetapi berbeda dengan yang pertama , tulisan terdahulu hasilnya hanya satu saja (single result), nah kenapa tidak dengan hasil yang beberapa atau multi results. Langsung saja tabel kasus dibawah ini :
Seperti kita paham 2 way lookup mempunyai bentuk tabel seperti diatas terdapat Column Header (Mata Pelajaran) dan Row Header (Nama). Nah Pertanyaannya, mungkin ndak ya jika kita ingin mengambil data berdasar Nilainya? Misal:
Dari kriteria "Nilai", hasil yang diinginkan adalah Nama2x yang mendapatkan nilai tersebut, sekalian dengan Mata Pelajaran yang diambil.
Rumus Nama:
=IFERROR(INDEX($A$2:$A$6;SMALL(IF(($B$2:$E$6=$A$9)*($B$2:$E$6<>"");ROW($B$2:$E$6)-ROW($B$2)+1);ROWS(A$1:A1)));"-")
Rumus Mata Pelajaran:
=IFERROR(INDEX($B$1:$E$1;SMALL(IF(INDEX($B$2:$E$6;MATCH($B9;$A$2:$A$6;0);0)=$A$9;COLUMN($B$2:$E$6)-COLUMN($B$2)+1);COUNTIF($B$9:B9;B9)));"-")
Pertanyaan 2:
Kriterianya adalah Nama, dan ingin mengambil data Nilai dan Mata Pelajarannya.
Rumus Nilai:
=IFERROR(INDEX($B$2:$E$6;SMALL(IF(($A$2:$A$6=$A$18)*($B$2:$E$6<>"");MATCH(ROW($B$2:$E$6);ROW($B$2:$E$6));"");ROW(A1));1*(SMALL(IF(($A$2:$A$6=$A$18)*($B$2:$E$6<>"");MATCH(ROW($B$2:$E$6);ROW($B$2:$E$6))+1*MATCH(COLUMN($B$2:$E$6);COLUMN($B$2:$E$6));"");ROW(A1))-SMALL(IF(($A$2:$A$6=$A$18)*($B$2:$E$6<>"");MATCH(ROW($B$2:$E$6);ROW($B$2:$E$6));"");ROW(A1))));"-")
Rumus Mata Pelajaran:
=IFERROR(INDEX($B$1:$E$1;SMALL(IF(INDEX($B$2:$E$6;MATCH(A$18;$A$2:$A$6;0);0)=$B18;COLUMN($B$1:$E$1)-COLUMN($B$1)+1);COUNTIFS($A$18:$A$18;$A$18;$B$18:B18;B18)));"-")
Pertanyaan 3:
Kriterianya adalah Mata Pelajaran, hasil yang diambil adalah data Nilai dan Nama Siswa.
Rumus Nilai:
=IFERROR(INDEX($B$2:$E$6;SMALL(IF((INDEX($B$2:$E$6;0;MATCH($A$25;$B$1:$E$1;0))<>"");MATCH(ROW($A$2:$A$6);ROW($A$2:$A$6));"");ROW(A1));MATCH($A$25;$B$1:$E$1;0));"-")
Rumus Nama Siswa:
=IFERROR(INDEX($A$1:$A$6;SMALL(IF($B$1:$E$1=$A$25;IF(ISNUMBER(MATCH($B$2:$E$6;$B$25:$B$29;0));IF(ISNA(MATCH($A$2:$A$6;$B2:E$6;0));ROW($A$2:$A$6)-ROW($A$1)+1)));ROWS($A$1:A1)));"-")
Semuanya array formulas ya....dan copy kebawah sampai hasilnya blank.
Download File Latihan
11 November 2014
26 September 2014
Penomoran Otomatis Pada Data Yang Sama (Duplikat)
Saya pernah memberikan memberikan materi autonumbering (memberi nomor otomatis pada data), tetapi kali ini untuk data yang muncul lebih dari satu kali alias data yang sama atau duplikasi.
Kasus Pertama, kita ingin memberikan nomor pada data yang pertama muncul dan data yang muncul berikutnya diisi blank atau kosong.
Rumus (diletakkan di cell A2 dan copas ke bawah):
Kasus Ketiga, memberikan nomor yang sama pada data yang sama
Salam
Rizky
Kasus Pertama, kita ingin memberikan nomor pada data yang pertama muncul dan data yang muncul berikutnya diisi blank atau kosong.
Rumus (diletakkan di cell A2 dan copas ke bawah):
Rumusnya
=IF(COUNTIF($B$2:B2;B2)=1;MAX($A$1:A1)+1;"")
Kasus Kedua untuk penomoran data berulang diberikan urutan pada grup data yang sama dan akan restart jika menemukan data baru, perhatikan contoh tabel diatas
Kasus Kedua untuk penomoran data berulang diberikan urutan pada grup data yang sama dan akan restart jika menemukan data baru, perhatikan contoh tabel diatas
rumusnya
=IF(B2="";"";COUNTIF($B$2:B2;B2))
rumusnya:
=IFERROR(IF(COUNTIF(B$2:B2;B2)=1;MAX(A$1:A1)+1;VLOOKUP(B2;CHOOSE({1\2};$B$1:B1;$A$1:A1);2;0));"")
Salam
Rizky
25 September 2014
Memisahkan Data Alamat
Salah satu yang saya sukai di rumus excel adalah manipulasi data teks, nah kali ini yang di utak atik adalah data alamat.
Coba perhatikan data alamat diatas, ya disana data dipisahkan oleh koma dan ada jarak spasi disana, itu yang menjadi kunci untuk rumusnya nanti.
Hasil yang di-inginkan
Yang dipisahkan adalah alamat, kemudian kota, propinsi dan terakhir adalah kode pos. Mohon diingat jika ingin memisahkan seperti ini, anda harus punya data yang polanya konsisten, karena rumus excel tidak akan mengenal "Sutrabaya" sebagai kota atau "Jawa Tengah" sebagai propinsi, yang penting polanya sama sehingga logika pemisahannya bisa diterapkan pada rumus.
Pertama-tama yang harus dilakukan adalah mengambil data "Kota", "Propinsi" dan "Kode Pos", karena ini sebagai kunci.
Rumusnya adalah:
=TRIM(RIGHT(SUBSTITUTE(A2;",";REPT(" ";90));270)) , dari sini yang lain jadi agak mudah.
Rumus Kota:
=TRIM(LEFT(TRIM(RIGHT(SUBSTITUTE(A2;",";REPT(" ";90));270));SEARCH(" ";TRIM(RIGHT(SUBSTITUTE(A2;",";REPT(" ";90));270));1)-1))
Rumus Alamat:
=MID(TRIM(LEFT(A2;SEARCH(D2;A2;1)-1));1;LEN(TRIM(LEFT(A2;SEARCH(D2;A2;1)-1)))-1)
Rumus Propinsi:
=TRIM(MID(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A2;", ";REPT(" ";90));270));" ";REPT(" ";99));100;200))
Rumus Kode Pos:
=TRIM(RIGHT(SUBSTITUTE(A2;" ";REPT(" ";99));99))
Nah mungkin ada dari pembaca yang punya rumus lain? silahkan di-share disini
Salam
Rizky
Download File Latihan
Coba perhatikan data alamat diatas, ya disana data dipisahkan oleh koma dan ada jarak spasi disana, itu yang menjadi kunci untuk rumusnya nanti.
Hasil yang di-inginkan
Yang dipisahkan adalah alamat, kemudian kota, propinsi dan terakhir adalah kode pos. Mohon diingat jika ingin memisahkan seperti ini, anda harus punya data yang polanya konsisten, karena rumus excel tidak akan mengenal "Sutrabaya" sebagai kota atau "Jawa Tengah" sebagai propinsi, yang penting polanya sama sehingga logika pemisahannya bisa diterapkan pada rumus.
Pertama-tama yang harus dilakukan adalah mengambil data "Kota", "Propinsi" dan "Kode Pos", karena ini sebagai kunci.
Rumusnya adalah:
=TRIM(RIGHT(SUBSTITUTE(A2;",";REPT(" ";90));270)) , dari sini yang lain jadi agak mudah.
Rumus Kota:
=TRIM(LEFT(TRIM(RIGHT(SUBSTITUTE(A2;",";REPT(" ";90));270));SEARCH(" ";TRIM(RIGHT(SUBSTITUTE(A2;",";REPT(" ";90));270));1)-1))
Rumus Alamat:
=MID(TRIM(LEFT(A2;SEARCH(D2;A2;1)-1));1;LEN(TRIM(LEFT(A2;SEARCH(D2;A2;1)-1)))-1)
Rumus Propinsi:
=TRIM(MID(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A2;", ";REPT(" ";90));270));" ";REPT(" ";99));100;200))
Rumus Kode Pos:
=TRIM(RIGHT(SUBSTITUTE(A2;" ";REPT(" ";99));99))
Nah mungkin ada dari pembaca yang punya rumus lain? silahkan di-share disini
Salam
Rizky
Download File Latihan
24 September 2014
SHARE MOMENT
Assalamualaikum
Hi to you all......
Postingan kali ini hanya berbagi pengalaman kenapa saya membuat blog ini. Awalnya biasa saja dengan program excel ini yah hanya untuk mengetik saja, tetapi terkadang muncul masalah tatkala saya disuruh bos membuat sebuah report summary, padahal datanya banyak. Wah bingung kan, ndak tau rumus waktu itu, terpaksa manual awalnya, sangat waste of time. Berawal dari situ saya mikir2x mungkin gak ya ada cara yang lebih gampang? Saya mulai beli2x buku2x excel, puyeng juga waktu pertama kali baca, step by step memulai dari rumus yang sederhana =IF(A1="A";"Lulus";IF(A1="B";"Agak Bagus" hehehe, berhasil dengan ini tambah semangat belajar lagi dan mendalami tentang excel, terutama rumus, tentang pivot tabel, graphic chart nol besar :p, apalagi yang namanya macro/VBA nyerah deh
Yang paling berkesan adalah sewaktu saya bergabung dengan forum excel online, kemampuan semakin terasah, bukan dengan bertanya masalah saya kepada yang orang lain , tetapi saya mencoba membantu memecahkan persoalan rumus/permasalahan yang dilontarkan di forum, kadang gagal ada juga yang sukses, dan di forum saya bisa melihat dan mempelajari cara para pakar excel memecahkan persoalan.
Forum yang saya ikuti:
1. www.excelforum.com
2. forum.chandoo.org
3. www.mrexcel.com
4. www.ozgrid.com
Silahkan jadi member (gratis), mau belajar atau posting masalah anda, silahkan saja. Jika ingin melihat postingan saya, search aja nickname saya "azumi" di semua forum tsb diatas.
Suatu keahlian tidak mutlak membutuhkan kepandaian tetapi lebih penting adalah ketekunan dan niat yang kuat dari anda. Dan jika sudah ahli atau mempunyai skills, tetaplah rendah hati dan berbagilah ke orang lain serta jangan merasa puas tetaplah merasa kurang ilmu yang kita miliki. Terakhir walaupun pengetahuan excel masih sangat terbatas,keinginan untuk menyebarkan ilmu pengetahuan sangatlah kuat, jadi guru sudah telat/kasep haha... paling mudah dan tidak mengganggu pekerjaan akhirnya saya pilih media blogger ini.
Salam
Rizky
Hi to you all......
Postingan kali ini hanya berbagi pengalaman kenapa saya membuat blog ini. Awalnya biasa saja dengan program excel ini yah hanya untuk mengetik saja, tetapi terkadang muncul masalah tatkala saya disuruh bos membuat sebuah report summary, padahal datanya banyak. Wah bingung kan, ndak tau rumus waktu itu, terpaksa manual awalnya, sangat waste of time. Berawal dari situ saya mikir2x mungkin gak ya ada cara yang lebih gampang? Saya mulai beli2x buku2x excel, puyeng juga waktu pertama kali baca, step by step memulai dari rumus yang sederhana =IF(A1="A";"Lulus";IF(A1="B";"Agak Bagus" hehehe, berhasil dengan ini tambah semangat belajar lagi dan mendalami tentang excel, terutama rumus, tentang pivot tabel, graphic chart nol besar :p, apalagi yang namanya macro/VBA nyerah deh
Yang paling berkesan adalah sewaktu saya bergabung dengan forum excel online, kemampuan semakin terasah, bukan dengan bertanya masalah saya kepada yang orang lain , tetapi saya mencoba membantu memecahkan persoalan rumus/permasalahan yang dilontarkan di forum, kadang gagal ada juga yang sukses, dan di forum saya bisa melihat dan mempelajari cara para pakar excel memecahkan persoalan.
Forum yang saya ikuti:
1. www.excelforum.com
2. forum.chandoo.org
3. www.mrexcel.com
4. www.ozgrid.com
Silahkan jadi member (gratis), mau belajar atau posting masalah anda, silahkan saja. Jika ingin melihat postingan saya, search aja nickname saya "azumi" di semua forum tsb diatas.
Suatu keahlian tidak mutlak membutuhkan kepandaian tetapi lebih penting adalah ketekunan dan niat yang kuat dari anda. Dan jika sudah ahli atau mempunyai skills, tetaplah rendah hati dan berbagilah ke orang lain serta jangan merasa puas tetaplah merasa kurang ilmu yang kita miliki. Terakhir walaupun pengetahuan excel masih sangat terbatas,keinginan untuk menyebarkan ilmu pengetahuan sangatlah kuat, jadi guru sudah telat/kasep haha... paling mudah dan tidak mengganggu pekerjaan akhirnya saya pilih media blogger ini.
Salam
Rizky
19 September 2014
Utak Atik Data Penjualan
Ass.Wr.Wb
Happy morning I hope to you all the best
Di pagi yang cerah ini mungkin asyik kalau memberikan sesuatu lagi kepada para pembaca sekalian, kali ini tentang data penjualan lagi, soalnya menarik untuk dibahas.
Tabel Kasus:
Dan pertanyaannya adalah:
Nah ada 6 pertanyaan yang dikelompokkan ke 3 kasus, nah satu persatu dikupas.
CASE 1:
Untuk siapa yang menghasilkan penjualan terbesar, rumusnya:
=INDEX($A$2:$A$10;MATCH(MAX($C$2:$C$10);$C$2:$C$10;0))
atau
=VLOOKUP(MAX(C2:C10);CHOOSE({1\2};$C$2:$C$10;$A$2:$A$10);2;0)
Untuk penjualan terkecil, anda tetap bisa menggunakan rumus diatas, hanya saja MAX diganti dengan MIN.
CASE 2:
Siapakah yang mempunyai Sales = 1316?
=VLOOKUP(1316;CHOOSE({1\2};C3:C10;A3:A10);2;FALSE)
Berapakah customer/nasabahnya?
=VLOOKUP(1316;CHOOSE({1\2\3};C3:C10;A3:A10;B3:B10);3;FALSE)
CASE 3:
Siapakah yang mempunyai profit ratio yang terbesar?
=VLOOKUP(MAX(E3:E10);CHOOSE({1\2};E3:E10;A3:A10);2;FALSE)
Berapakah profit rationya?
=VLOOKUP(VLOOKUP(MAX(E3:E10);CHOOSE({1\2};E3:E10;A3:A10);2;FALSE);A3:E10;5;FALSE)
Nah, semua rumus diatas bukan rumus array ya, rumus biasa saja, kelebihan rumus non array adalah menghemat resources dari komputer kita alias RAM/Memori, tetapi biasanya rumusnya jadi lebih panjang. Kalau array formula lebih efisien dan lebih pendek (untuk kasus tertentu), tetapi makan RAM-nya lebih banyak. Dua2xnya mempunyai kelebihan dan kekurangannya masing2x, tinggal kita saja yang memilih mana yang terbaik untuk kasus yang kita punyai.
Cheers --> File Latihan
Happy morning I hope to you all the best
Di pagi yang cerah ini mungkin asyik kalau memberikan sesuatu lagi kepada para pembaca sekalian, kali ini tentang data penjualan lagi, soalnya menarik untuk dibahas.
Tabel Kasus:
Dan pertanyaannya adalah:
Nah ada 6 pertanyaan yang dikelompokkan ke 3 kasus, nah satu persatu dikupas.
CASE 1:
Untuk siapa yang menghasilkan penjualan terbesar, rumusnya:
=INDEX($A$2:$A$10;MATCH(MAX($C$2:$C$10);$C$2:$C$10;0))
atau
=VLOOKUP(MAX(C2:C10);CHOOSE({1\2};$C$2:$C$10;$A$2:$A$10);2;0)
Untuk penjualan terkecil, anda tetap bisa menggunakan rumus diatas, hanya saja MAX diganti dengan MIN.
CASE 2:
Siapakah yang mempunyai Sales = 1316?
=VLOOKUP(1316;CHOOSE({1\2};C3:C10;A3:A10);2;FALSE)
Berapakah customer/nasabahnya?
=VLOOKUP(1316;CHOOSE({1\2\3};C3:C10;A3:A10;B3:B10);3;FALSE)
CASE 3:
Siapakah yang mempunyai profit ratio yang terbesar?
=VLOOKUP(MAX(E3:E10);CHOOSE({1\2};E3:E10;A3:A10);2;FALSE)
Berapakah profit rationya?
=VLOOKUP(VLOOKUP(MAX(E3:E10);CHOOSE({1\2};E3:E10;A3:A10);2;FALSE);A3:E10;5;FALSE)
Nah, semua rumus diatas bukan rumus array ya, rumus biasa saja, kelebihan rumus non array adalah menghemat resources dari komputer kita alias RAM/Memori, tetapi biasanya rumusnya jadi lebih panjang. Kalau array formula lebih efisien dan lebih pendek (untuk kasus tertentu), tetapi makan RAM-nya lebih banyak. Dua2xnya mempunyai kelebihan dan kekurangannya masing2x, tinggal kita saja yang memilih mana yang terbaik untuk kasus yang kita punyai.
Cheers --> File Latihan
5 September 2014
Pengambilan Data Komisi Penjualan
Hi readers
Saya ingin membahas mengenai pengambilan data (komisi) berdasarkan sales (penjualan).
KASUS 1.
Nah misalnya kasus tabelnya seperti gambar diatas, jadi setiap sales mendapatkan kesempatan mendapatkan bonus/komisi berdasarkan sales-nya. Jika Sales>150 maka gaji + 25% komisi, dan bila Sales>250 maka gaji+50% komisi.
Nah bagaimana caranya jika kita punya kriteria Nama dan Gaji, seperti contoh diatas, bisa otomatis bisa menemukan komisi yang sesuai dengan hasil penjualannya?
Step by stepnya:
Pada gambar diatas yang diwarna kuning (ada 3 cell) itu tugas kita ya untuk bikin rumusnya,
Yang Pertama : =VLOOKUP(B9;IF(B2:B3=C9;A2:D3);IF(B10>250;4;3);FALSE) , ini adalah rumus array, coba perhatikan pada bagian nomor index yaitu IF(B10>250;4;3), nah disini kuncinya, jika penjualan >50 maka mengambil data di kolom ke-4, jika tidak terpenuhi maka mengambil data di kolom ke-3
Yang Kedua : =VLOOKUP(B9;IF(B2:B3=C9;A2:D3);IF(B10>250;4;3);FALSE)*C9
Yang Ketiga : =B14+C9
KASUS 2:
Nah kali ini ada 2 Tabel, untuk tabel perhitungan komisinya terpisah dari tabel data seperti contoh diatas, step-step rumusnya seperti digambar ini, jadi initinya bagaimana menemukan pengahsilan dari karyawan yang ditentukan berdasarkan jabatannya dan komisi tergantung pada tercapai atau tidaknya target tersebut.
Saya menjelaskan lebih detail di FILE LATIHAN
Salam
Saya ingin membahas mengenai pengambilan data (komisi) berdasarkan sales (penjualan).
KASUS 1.
Nah misalnya kasus tabelnya seperti gambar diatas, jadi setiap sales mendapatkan kesempatan mendapatkan bonus/komisi berdasarkan sales-nya. Jika Sales>150 maka gaji + 25% komisi, dan bila Sales>250 maka gaji+50% komisi.
Nah bagaimana caranya jika kita punya kriteria Nama dan Gaji, seperti contoh diatas, bisa otomatis bisa menemukan komisi yang sesuai dengan hasil penjualannya?
Step by stepnya:
Pada gambar diatas yang diwarna kuning (ada 3 cell) itu tugas kita ya untuk bikin rumusnya,
Yang Pertama : =VLOOKUP(B9;IF(B2:B3=C9;A2:D3);IF(B10>250;4;3);FALSE) , ini adalah rumus array, coba perhatikan pada bagian nomor index yaitu IF(B10>250;4;3), nah disini kuncinya, jika penjualan >50 maka mengambil data di kolom ke-4, jika tidak terpenuhi maka mengambil data di kolom ke-3
Yang Kedua : =VLOOKUP(B9;IF(B2:B3=C9;A2:D3);IF(B10>250;4;3);FALSE)*C9
Yang Ketiga : =B14+C9
KASUS 2:
Nah kali ini ada 2 Tabel, untuk tabel perhitungan komisinya terpisah dari tabel data seperti contoh diatas, step-step rumusnya seperti digambar ini, jadi initinya bagaimana menemukan pengahsilan dari karyawan yang ditentukan berdasarkan jabatannya dan komisi tergantung pada tercapai atau tidaknya target tersebut.
Saya menjelaskan lebih detail di FILE LATIHAN
Salam
8 Agustus 2014
Konversi Data (Menyatukan dan/atau Memisahkan Data)
KASUS 1:
Nah berdasar data sederhana diatas, hasil yang diinginkan adalah sebagai berikut (urut baris)
Jadi dari data yang multi kolom menjadi 1 kolom, rumusnya berikut ini
=INDEX($A$1:$D$3;MOD(ROWS(A$1:A1)-1;ROWS($A$1:$D$3))+1;INT((ROWS(A$1:A1)-1)/ROWS($A$1:$D$3))+1)
Atau hasil yang diinginkan seperti ini (urut kolom)
=INDEX($A$1:$D$3;INT((ROWS(A$1:A1)-1)/COLUMNS($A$1:$D$3))+1;MOD(ROWS(A$1:A1)-1;COLUMNS($A$1:$D$3))+1)
KASUS 2:
Dan hasil yang diinginkan
Nah kasus ke-2 ini adalah kebalikan dari yang pertama, yaitu men-split data dari 1 kolom menjadi multi kolom, dan rumusnya
=IFERROR(IF(ROWS(A1:$A$1)<=$C$1;INDEX($A$1:$A$12;ROWS($A$1:A1)+(COLUMNS($A$1:A1)-1)*$C$1);"");"")
Nah pada gambar anda perhatikan di Cell C1, terdapat angka 2, bisa dirubah sesuai kebutuhan, 2 itu artinya 2 baris, anda bisa rubah menjadi 3, 4 dan seterusnya.
Rumus2x tersebut rumus reguler ya bukan rumus array
Sekian dari saya, happy learning guys....
File Latihan
25 Juli 2014
8 Mei 2014
Kasus Tabel Dengan Lay Out Yang Sulit
Kebetulan saya ada beberapa kasus menarik yang menyangkut Lookup Data tetapi dengan bentuk tabel yang agak sulit dengan rumus yang reguler. Maksud saya rumus reguler adalah rumus yang lazim digunakan atau yang sudah digunakan secara luas.
Seperti ini misalnya:
Kasus 1:
Nah mungkin pernah liat yang beginian? Terus misal kriterianya adalah Buah dan kita ingin mengambil hasilnya dari Kolom B, itu berarti "Apel", "Jeruk" dan "Durian"
Hasil yang diinginkan adalah seperti yang diatas, kriteria yang dipakai adalah Kopi dan Apel, nah coba memakai rumus VLOOKUP atau INDEX + MATCH pasti gagal deh... huehehe....
Rumus 1 (Kriteria "Kopi") :
=IFERROR(INDEX($B$2:$B$10;SMALL(IF(LOOKUP(ROW($A$2:$A$10);ROW($A$2:$A$10)/($A$2:$A$10<>"");$A$2:$A$10)=$A$13;ROW($B$2:$B$10)-ROW($B$2)+1);ROWS(A$1:A1)));"")
Rumus 2 (Kriteria "Apel") :
=IFERROR(INDEX(LOOKUP(ROW($A$2:$A$10);ROW($A$2:$A$10)/($A$2:$A$10<>"");$A$2:$A$10);SMALL(IF($B$2:$B$10=$D$13;ROW($B$2:$B$10)-ROW($B$2)+1);ROWS(A$1:A1)));"")
Kasus 2:
Kemudian hasil yang diinginkan adalah mengambil data QTY atau jumlah barang yang terjual oleh kriteria "Nama Sales", "Jenis Barang" dan "Merek Barang"
Rumusnya:
=INDEX(B22:E25;MATCH(1;(INDEX(B22:D25;0;MATCH($A28;B21:D21;0))=B29)*(A22:A25=A29);0);MATCH($A28;B21:D21;0)+1)
Kasus 3:
Contoh diatas ada juga Column Header yang Cell-nya digabung atau Merged, sebetulnya untuk penggunaan rumus pada Cell yang di-merged tidak disarankan biasanya akan gagal waktu di-ENTER, atau kalau jika digunakan sebagai kriteria agak ribet rumusnya, sebisa mungkin tabel data bersih yaitu tidak ada Cell yang di-MERGED dan tidak ada karakter spasi kosong pada tabel anda.
Sesuai Tabel Kasus 3 diatas, hasil yang diinginkan seperti ini
Nah kriterianya adalah "Area" kemudian hasil yang diinginkan adalah semua baris data yang ada dibawahnya...
Rumus Untuk Tanggal :
=IFERROR(INDEX($B$38:$H$38;SMALL(IF(LOOKUP(COLUMN($B$37:$H$37);COLUMN($B$37:$H$37)/($B$37:$H$37<>"");$B$37:$H$37)=$A$44;COLUMN($B$37:$H$37)-COLUMN($B$37)+1);COLUMNS($A20:A20)));"")
Rumus Untuk Qty:
=IFERROR(INDEX($B$39:$H$41;SMALL(IF((INDEX($B$39:$H$41;0;MATCH(1;($B$38:$H$38=B$44)*(LOOKUP(COLUMN($B$37:$H$37);COLUMN($B$37:$H$37)/($B$37:$H$37<>"");$B$37:$H$37)=$A$44);0))<>"");MATCH(ROW($A$39:$A$41);ROW($A$39:$A$41));"");ROWS($A$20:$A20));MATCH(1;($B$38:$H$38=B$44)*(LOOKUP(COLUMN($B$37:$H$37);COLUMN($B$37:$H$37)/($B$37:$H$37<>"");$B$37:$H$37)=$A$44);0));"-")
Semuanya adalah Rumus Array, tekan tombol CTRL-SHIFT-ENTER bersamaan
Salam
File Latihan
Seperti ini misalnya:
Kasus 1:
Nah mungkin pernah liat yang beginian? Terus misal kriterianya adalah Buah dan kita ingin mengambil hasilnya dari Kolom B, itu berarti "Apel", "Jeruk" dan "Durian"
Hasil yang diinginkan adalah seperti yang diatas, kriteria yang dipakai adalah Kopi dan Apel, nah coba memakai rumus VLOOKUP atau INDEX + MATCH pasti gagal deh... huehehe....
Rumus 1 (Kriteria "Kopi") :
=IFERROR(INDEX($B$2:$B$10;SMALL(IF(LOOKUP(ROW($A$2:$A$10);ROW($A$2:$A$10)/($A$2:$A$10<>"");$A$2:$A$10)=$A$13;ROW($B$2:$B$10)-ROW($B$2)+1);ROWS(A$1:A1)));"")
Rumus 2 (Kriteria "Apel") :
=IFERROR(INDEX(LOOKUP(ROW($A$2:$A$10);ROW($A$2:$A$10)/($A$2:$A$10<>"");$A$2:$A$10);SMALL(IF($B$2:$B$10=$D$13;ROW($B$2:$B$10)-ROW($B$2)+1);ROWS(A$1:A1)));"")
Kasus 2:
Kemudian hasil yang diinginkan adalah mengambil data QTY atau jumlah barang yang terjual oleh kriteria "Nama Sales", "Jenis Barang" dan "Merek Barang"
Rumusnya:
=INDEX(B22:E25;MATCH(1;(INDEX(B22:D25;0;MATCH($A28;B21:D21;0))=B29)*(A22:A25=A29);0);MATCH($A28;B21:D21;0)+1)
Kasus 3:
Contoh diatas ada juga Column Header yang Cell-nya digabung atau Merged, sebetulnya untuk penggunaan rumus pada Cell yang di-merged tidak disarankan biasanya akan gagal waktu di-ENTER, atau kalau jika digunakan sebagai kriteria agak ribet rumusnya, sebisa mungkin tabel data bersih yaitu tidak ada Cell yang di-MERGED dan tidak ada karakter spasi kosong pada tabel anda.
Sesuai Tabel Kasus 3 diatas, hasil yang diinginkan seperti ini
Nah kriterianya adalah "Area" kemudian hasil yang diinginkan adalah semua baris data yang ada dibawahnya...
Rumus Untuk Tanggal :
=IFERROR(INDEX($B$38:$H$38;SMALL(IF(LOOKUP(COLUMN($B$37:$H$37);COLUMN($B$37:$H$37)/($B$37:$H$37<>"");$B$37:$H$37)=$A$44;COLUMN($B$37:$H$37)-COLUMN($B$37)+1);COLUMNS($A20:A20)));"")
Rumus Untuk Qty:
=IFERROR(INDEX($B$39:$H$41;SMALL(IF((INDEX($B$39:$H$41;0;MATCH(1;($B$38:$H$38=B$44)*(LOOKUP(COLUMN($B$37:$H$37);COLUMN($B$37:$H$37)/($B$37:$H$37<>"");$B$37:$H$37)=$A$44);0))<>"");MATCH(ROW($A$39:$A$41);ROW($A$39:$A$41));"");ROWS($A$20:$A20));MATCH(1;($B$38:$H$38=B$44)*(LOOKUP(COLUMN($B$37:$H$37);COLUMN($B$37:$H$37)/($B$37:$H$37<>"");$B$37:$H$37)=$A$44);0));"-")
Semuanya adalah Rumus Array, tekan tombol CTRL-SHIFT-ENTER bersamaan
Salam
File Latihan
26 April 2014
Rumus Kalkulasi Tanggal Untuk Berbagai Keperluan
Saya akan "all out" kali ini, memberikan berbagai rumus untuk berbagai keperluan yang berkaitan kalkulasi menggunakan tanggal, langsung saja satu persatu dibawah ini:
KASUS A:Menghitung Jarak Antara 2 Tanggal
1. Dengan DATEDIF:
=IF(DATEDIF(A4;B4;"y")=0;"";DATEDIF(A4;B4;"y")&" tahun ")&IF(DATEDIF(A4;B4;"ym")=0;"";DATEDIF(A4;B4;"ym")&" bulan ")&IF(DATEDIF(A4;B4;"md")=0;"";DATEDIF(A4;B4;"md")&" hari")
2. Tanpa DATEDIF:
=TRIM(IF(INT((B4-A4)/365,25)=0;"";INT((B4-A4)/365,25)&" tahun")&" "&IF(INT(MOD((B4-A4)/365,25;1)*12)=0;"";INT(MOD((B4-A4)/365,25;1)*12)&" bulan")&" "&IF(INT(MOD((B4-A4)/30,44;1)*30,44)=0;"";INT(MOD((B4-A4)/30,44;1)*30,44)&" hari"))
Kasus B: Menghitung Umur
1. Dengan DATEDIF:
=IF(DATEDIF(A9;TODAY();"y")=0;"";DATEDIF(A9;TODAY();"y")&" tahun ")&IF(DATEDIF(A9;TODAY();"ym")=0;"";DATEDIF(A9;TODAY();"ym")&" bulan ")&IF(DATEDIF(A9;TODAY();"md")=0;"";DATEDIF(A9;TODAY();"md")&" hari")
2. Tanpa DATEDIF:
=TRIM(IF(INT((TODAY()-A9)/365,25)=0;"";INT((TODAY()-A9)/365,25)&" tahun")&" "&IF(INT(MOD((TODAY()-A9)/365,25;1)*12)=0;"";INT(MOD((TODAY()-A9)/365,25;1)*12)&" bulan")&" "&IF(INT(MOD((TODAY()-A9)/30,44;1)*30,44)=0;"";INT(MOD((TODAY()-A9)/30,44;1)*30,44)&" hari"))
atau
=IF(YEAR(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1900=0;"";YEAR(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1900&" Tahun ")&IF(MONTH(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1=0;"";MONTH(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1&" Bulan ")&IF(DAY(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1=0;"";DAY(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1&" Hari")
Kasus C: Rumus Untuk Mengetahui Hari Ulang Tahun pada Tahun Ini
Hari Ulang Tahun pada tahun ini adalah……
=TEXT(DATE(YEAR(TODAY());MONTH(A21);DAY(A21));"dddd")&", "&TEXT(DATE(YEAR(TODAY());MONTH(A21);DAY(A21));"dd mmm yyyy")
Rumus untuk menghitung sisa hari menjelang ulang tahun atau sudah lewat
=IF(TODAY()>DATE(YEAR(TODAY());MONTH(A21);DAY(A21));"Wah sudah lewat tuh...";IF(TODAY()=DATE(YEAR(TODAY());MONTH(A21);DAY(A21));"Selamat Ulang Tahun !!";"Kurang "&TRIM(IF(INT((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/365,25)=0;"";INT((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/365,25)&" tahun")&" "&IF(INT(MOD((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/365,25;1)*12)=0;"";INT(MOD((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/365,25;1)*12)&" bulan")&" "&IF(INT(MOD((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/30,44;1)*30,44)=0;"";INT(MOD((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/30,44;1)*30,44)&" hari"))&" lagi"))
Kasus D: Membuat Reminder dari Agenda Kegiatan/Peristiwa atau Masa Kadaluarsa SIM/KTP/STNK
Di Cell C31 dan dicopy kebawah:
=IF(B31="";"";IF(B31=TODAY();"Hari ini Acaranya/Jatuh Tempo";IF(B31>TODAY();IF(DATEDIF(TODAY();B31;"y")=0;"";DATEDIF(TODAY();B31;"y")&" tahun ")&IF(DATEDIF(TODAY();B31;"ym")=0;"";DATEDIF(TODAY();B31;"ym")&" bulan ")&IF(DATEDIF(TODAY();B31;"md")=0;"";DATEDIF(TODAY();B31;"md")&" hari")&" lagi";"Sudah lewat "&IF(DATEDIF(B31;TODAY();"y")=0;"";DATEDIF(B31;TODAY();"y")&" tahun ")&IF(DATEDIF(B31;TODAY();"ym")=0;"";DATEDIF(B31;TODAY();"ym")&" bulan ")&IF(DATEDIF(B31;TODAY();"md")=0;"";DATEDIF(B31;TODAY();"md")&" hari"))))
Kasus E: Menghitung Jarak Antara 2 Tanggal Dalam Minggu dan Hari:
=IF(TRUNC((B44-A44)/7)=0;"";TRUNC((B44-A44)/7)&" Minggu")&" "&IF(MOD((B44-A44);7)=0;"";MOD((B44-A44);7)&" Hari")
atau
=IF(INT((B44-A44)/7)=0;"";INT((B44-A44)/7)&" Minggu ")&IF(MOD(((B44-A44)/7);1)*7=0;"";MOD(((B44-A44)/7);1)*7&" Hari")
atau
=IF(INT(DATEDIF(A44;B44;"D")/7)=0;"";INT(DATEDIF(A44;B44;"D")/7)&" Minggu ")&IF(MOD(DATEDIF(A44;B44;"D");7)=0;"";MOD(DATEDIF(A44;B44;"D");7)&" Hari")
Kasus F: Rumus untuk mengetahui minggu ke berapa dari tanggal (dalam 1 bulan)
="Minggu ke "&WEEKNUM(A55)-WEEKNUM(A55-DAY(A55)+1)+1
atau
="Minggu ke "&INT((DAY(A55)+6)/7)
Kasus G: Rumus untuk mengetahui jumlah hari dari suatu bulan
=DAY(DATE(YEAR(A63);MONTH(A63)+1;0))&" Hari"
atau
=DAY(EOMONTH(A21;0))&" Hari"
Atau kasus seperti ini, anda punya list nama-nama bulan:
Rumus untuk menghasilkan jumlah hari dari suatu bulan dengan kriteria nama bulan (teks):
=DAY(DATE(YEAR(TODAY());MONTH($A69&1)+1;0))&" Hari"
Kasus H: Konversi Angka Hari ke Tahun, ulan dan Hari
=IF(DATEDIF(366;366+A78;"y")=0;"";DATEDIF(366;366+A78;"y")&" tahun ")&IF(DATEDIF(366;366+A78;"ym")=0;"";DATEDIF(366;366+A78;"ym")&" bulan ")&IF(DATEDIF(366;366+A78;"md")=0;"";DATEDIF(366;366+A78;"md")&" hari ")
Kasus I: Bulan ini tinggal berapa hari lagi yaaa….?
="tinggal "&EOMONTH(TODAY();0)-TODAY()&" hari lagi"
atau
="tinggal "&DATE(YEAR(TODAY());MONTH(TODAY())+1;0) - TODAY()&" hari lagi"
Kasus J:
Yang satu ini mungkin untuk just for fun karena bisa untuk men-cek, hari dan tanggal gajian kita kurang berapa hari sih? Dan rumus ini saya buat otomatis akan men-skip hari "Sabtu" dan "Minggu". Seperti contoh diatas, pada cell A90 tertulis angka 1, rumus akan mengkonversi tanggal 1 tersebut adalah tanggal 1 Maret 2015 dan itu jatuh pada hari minggu, dan itu weekend, nah ndak mungkin toh terima gaji? Nah pasti mundur ke Senin. Ke-2 rumus di cell A92 dan A94(warna kuning) tidak saya tulis disini karena terlalu panjang, tetapi silahkan dilihat di file latihan.
Rumus untuk mencari hari dan tanggal terakhir Bulan Ini:
=TEXT(EOMONTH(TODAY();0);"dddd")&", "&TEXT(EOMONTH(TODAY();0);"dd mmmm yyy")
atau
=TEXT(DATE(YEAR(TODAY());MONTH(TODAY())+1;0);"dddd")&", "&TEXT(DATE(YEAR(TODAY());MONTH(TODAY())+1;0);"dd mmmm yyyy")
Rumus untuk mencari hari dan tanggal pertama Bulan ini:
=TEXT(EOMONTH(TODAY();0)+1;"dddd")&", "&TEXT(EOMONTH(TODAY();0)+1;"dd mmmm yyyy")
atau
=TEXT(DATE(YEAR(TODAY());MONTH(TODAY())+1;1);"dddd")&", "&TEXT(DATE(YEAR(TODAY());MONTH(TODAY())+1;1);"dd mmmm yyyy")
Rumus untuk cek tahun kabisat atau bukan:
=IF(DAY(DATE(A108;2;29))=29;"Tahun Kabisat";"Normal")
atau
=IF(MONTH(DATE(A108;2;29))=2;"Tahun Kabisat";"Normal")
atau
=IF(ISNUMBER(--(A108&"-02-29"));"Tahun Kabisat";"Normal")
atau
=IF(MOD(A108;4)=0;"Tahun Kabisat";"Normal")
Mudah2xan bermanfaat Wassalamualaikum Warahmatullahi Wabarakatuh
DOWNLOAD FILE LATIHAN
KASUS A:Menghitung Jarak Antara 2 Tanggal
1. Dengan DATEDIF:
=IF(DATEDIF(A4;B4;"y")=0;"";DATEDIF(A4;B4;"y")&" tahun ")&IF(DATEDIF(A4;B4;"ym")=0;"";DATEDIF(A4;B4;"ym")&" bulan ")&IF(DATEDIF(A4;B4;"md")=0;"";DATEDIF(A4;B4;"md")&" hari")
2. Tanpa DATEDIF:
=TRIM(IF(INT((B4-A4)/365,25)=0;"";INT((B4-A4)/365,25)&" tahun")&" "&IF(INT(MOD((B4-A4)/365,25;1)*12)=0;"";INT(MOD((B4-A4)/365,25;1)*12)&" bulan")&" "&IF(INT(MOD((B4-A4)/30,44;1)*30,44)=0;"";INT(MOD((B4-A4)/30,44;1)*30,44)&" hari"))
Kasus B: Menghitung Umur
1. Dengan DATEDIF:
=IF(DATEDIF(A9;TODAY();"y")=0;"";DATEDIF(A9;TODAY();"y")&" tahun ")&IF(DATEDIF(A9;TODAY();"ym")=0;"";DATEDIF(A9;TODAY();"ym")&" bulan ")&IF(DATEDIF(A9;TODAY();"md")=0;"";DATEDIF(A9;TODAY();"md")&" hari")
2. Tanpa DATEDIF:
=TRIM(IF(INT((TODAY()-A9)/365,25)=0;"";INT((TODAY()-A9)/365,25)&" tahun")&" "&IF(INT(MOD((TODAY()-A9)/365,25;1)*12)=0;"";INT(MOD((TODAY()-A9)/365,25;1)*12)&" bulan")&" "&IF(INT(MOD((TODAY()-A9)/30,44;1)*30,44)=0;"";INT(MOD((TODAY()-A9)/30,44;1)*30,44)&" hari"))
atau
=IF(YEAR(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1900=0;"";YEAR(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1900&" Tahun ")&IF(MONTH(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1=0;"";MONTH(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1&" Bulan ")&IF(DAY(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1=0;"";DAY(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1&" Hari")
Kasus C: Rumus Untuk Mengetahui Hari Ulang Tahun pada Tahun Ini
Hari Ulang Tahun pada tahun ini adalah……
=TEXT(DATE(YEAR(TODAY());MONTH(A21);DAY(A21));"dddd")&", "&TEXT(DATE(YEAR(TODAY());MONTH(A21);DAY(A21));"dd mmm yyyy")
Rumus untuk menghitung sisa hari menjelang ulang tahun atau sudah lewat
=IF(TODAY()>DATE(YEAR(TODAY());MONTH(A21);DAY(A21));"Wah sudah lewat tuh...";IF(TODAY()=DATE(YEAR(TODAY());MONTH(A21);DAY(A21));"Selamat Ulang Tahun !!";"Kurang "&TRIM(IF(INT((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/365,25)=0;"";INT((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/365,25)&" tahun")&" "&IF(INT(MOD((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/365,25;1)*12)=0;"";INT(MOD((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/365,25;1)*12)&" bulan")&" "&IF(INT(MOD((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/30,44;1)*30,44)=0;"";INT(MOD((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/30,44;1)*30,44)&" hari"))&" lagi"))
Kasus D: Membuat Reminder dari Agenda Kegiatan/Peristiwa atau Masa Kadaluarsa SIM/KTP/STNK
Di Cell C31 dan dicopy kebawah:
=IF(B31="";"";IF(B31=TODAY();"Hari ini Acaranya/Jatuh Tempo";IF(B31>TODAY();IF(DATEDIF(TODAY();B31;"y")=0;"";DATEDIF(TODAY();B31;"y")&" tahun ")&IF(DATEDIF(TODAY();B31;"ym")=0;"";DATEDIF(TODAY();B31;"ym")&" bulan ")&IF(DATEDIF(TODAY();B31;"md")=0;"";DATEDIF(TODAY();B31;"md")&" hari")&" lagi";"Sudah lewat "&IF(DATEDIF(B31;TODAY();"y")=0;"";DATEDIF(B31;TODAY();"y")&" tahun ")&IF(DATEDIF(B31;TODAY();"ym")=0;"";DATEDIF(B31;TODAY();"ym")&" bulan ")&IF(DATEDIF(B31;TODAY();"md")=0;"";DATEDIF(B31;TODAY();"md")&" hari"))))
Kasus E: Menghitung Jarak Antara 2 Tanggal Dalam Minggu dan Hari:
=IF(TRUNC((B44-A44)/7)=0;"";TRUNC((B44-A44)/7)&" Minggu")&" "&IF(MOD((B44-A44);7)=0;"";MOD((B44-A44);7)&" Hari")
atau
=IF(INT((B44-A44)/7)=0;"";INT((B44-A44)/7)&" Minggu ")&IF(MOD(((B44-A44)/7);1)*7=0;"";MOD(((B44-A44)/7);1)*7&" Hari")
atau
=IF(INT(DATEDIF(A44;B44;"D")/7)=0;"";INT(DATEDIF(A44;B44;"D")/7)&" Minggu ")&IF(MOD(DATEDIF(A44;B44;"D");7)=0;"";MOD(DATEDIF(A44;B44;"D");7)&" Hari")
Kasus F: Rumus untuk mengetahui minggu ke berapa dari tanggal (dalam 1 bulan)
="Minggu ke "&WEEKNUM(A55)-WEEKNUM(A55-DAY(A55)+1)+1
atau
="Minggu ke "&INT((DAY(A55)+6)/7)
Kasus G: Rumus untuk mengetahui jumlah hari dari suatu bulan
=DAY(DATE(YEAR(A63);MONTH(A63)+1;0))&" Hari"
atau
=DAY(EOMONTH(A21;0))&" Hari"
Atau kasus seperti ini, anda punya list nama-nama bulan:
Rumus untuk menghasilkan jumlah hari dari suatu bulan dengan kriteria nama bulan (teks):
=DAY(DATE(YEAR(TODAY());MONTH($A69&1)+1;0))&" Hari"
Kasus H: Konversi Angka Hari ke Tahun, ulan dan Hari
=IF(DATEDIF(366;366+A78;"y")=0;"";DATEDIF(366;366+A78;"y")&" tahun ")&IF(DATEDIF(366;366+A78;"ym")=0;"";DATEDIF(366;366+A78;"ym")&" bulan ")&IF(DATEDIF(366;366+A78;"md")=0;"";DATEDIF(366;366+A78;"md")&" hari ")
Kasus I: Bulan ini tinggal berapa hari lagi yaaa….?
="tinggal "&EOMONTH(TODAY();0)-TODAY()&" hari lagi"
atau
="tinggal "&DATE(YEAR(TODAY());MONTH(TODAY())+1;0) - TODAY()&" hari lagi"
Kasus J:
Yang satu ini mungkin untuk just for fun karena bisa untuk men-cek, hari dan tanggal gajian kita kurang berapa hari sih? Dan rumus ini saya buat otomatis akan men-skip hari "Sabtu" dan "Minggu". Seperti contoh diatas, pada cell A90 tertulis angka 1, rumus akan mengkonversi tanggal 1 tersebut adalah tanggal 1 Maret 2015 dan itu jatuh pada hari minggu, dan itu weekend, nah ndak mungkin toh terima gaji? Nah pasti mundur ke Senin. Ke-2 rumus di cell A92 dan A94(warna kuning) tidak saya tulis disini karena terlalu panjang, tetapi silahkan dilihat di file latihan.
Rumus untuk mencari hari dan tanggal terakhir Bulan Ini:
=TEXT(EOMONTH(TODAY();0);"dddd")&", "&TEXT(EOMONTH(TODAY();0);"dd mmmm yyy")
atau
=TEXT(DATE(YEAR(TODAY());MONTH(TODAY())+1;0);"dddd")&", "&TEXT(DATE(YEAR(TODAY());MONTH(TODAY())+1;0);"dd mmmm yyyy")
Rumus untuk mencari hari dan tanggal pertama Bulan ini:
=TEXT(EOMONTH(TODAY();0)+1;"dddd")&", "&TEXT(EOMONTH(TODAY();0)+1;"dd mmmm yyyy")
atau
=TEXT(DATE(YEAR(TODAY());MONTH(TODAY())+1;1);"dddd")&", "&TEXT(DATE(YEAR(TODAY());MONTH(TODAY())+1;1);"dd mmmm yyyy")
Rumus untuk cek tahun kabisat atau bukan:
=IF(DAY(DATE(A108;2;29))=29;"Tahun Kabisat";"Normal")
atau
=IF(MONTH(DATE(A108;2;29))=2;"Tahun Kabisat";"Normal")
atau
=IF(ISNUMBER(--(A108&"-02-29"));"Tahun Kabisat";"Normal")
atau
=IF(MOD(A108;4)=0;"Tahun Kabisat";"Normal")
Mudah2xan bermanfaat Wassalamualaikum Warahmatullahi Wabarakatuh
DOWNLOAD FILE LATIHAN
11 April 2014
Mengambil Data Dengan Multiple Results
Saya pernah menulis posting tentang mengambil data dengan beberapa hasil atau multiple results..... saya ingin refresh materi itu kali ini. Saya ada beberapa contoh yang mungkin berguna untuk diterapkan dan dikembangkan sesuai kebutuhan anda semua para pembaca budiman.
Kasus 1: Multi Result 1 Kriteria
Dengan tabel kasus diatas kita ingin mengambil data "Item" sesuai dengan "Kode" 101, disana terlihat kode 101 punya beberapa item. Hasil yang kita inginkan nanti seperti ini...
Jadi hasil multi results bisa dibuat secara vertical ataupun horizontal.
Rumusnya:
Untuk Hasil Vertical:
=IFERROR(INDEX($B$2:$B$6;SMALL(IF(($A$2:$A$6=$B$9)*($B$2:$B$6<>"");ROW($B$2:$B$6)-ROW($B$2)+1);ROWS($B$1:B1)));"")
Kemudian copas ke bawah
Untuk Hasil Horizontal:
=IFERROR(INDEX($B$2:$B$6;SMALL(IF(($A$2:$A$6=$B$15)*($B$2:$B$6<>"");ROW($B$2:$B$6)-ROW($B$2)+1);COLUMNS($B$1:B1)));"")
Kemudian copas kesamping kanan
Kasus 2: Multi Results 2 Kriteria
Hampir sama, hanya ada penambahan kriteria yaitu Kolom "Group", hasil yang diinginkan...
Pada gambar diatas dengan 2 kriteria yaitu Kolom "Group" dan Kolom "Kode" dan hasil yang diambil adalah Kolom "Item", baik secara vertical maupun horizontal.
Yang kedua adalah 2 kriteria pada Kolom "Kode", ditampilkan secara vertical.
Rumus untuk yang vertical (di Cell C9 dan copas ke bawah):
=IFERROR(INDEX($C$2:$C$6;SMALL(IF(($B$2:$B$6=$B$9)*($A$2:$A$6=$B$10)*($C$2:$C$6<>"");ROW($C$2:$C$6)-ROW($C$2)+1);ROWS($C$1:C1)));"")
Rumus untuk hasil horizontal (di Cell C15 dan copas ke kanan):
=IFERROR(INDEX($C$2:$C$6;SMALL(IF(($B$2:$B$6=$B$15)*(A2:A6=B16)*($C$2:$C$6<>"");ROW($C$2:$C$6)-ROW($C$2)+1);COLUMNS($C$1:C1)));"")
Rumus untuk 2 Kriteria "101" dan "202" (sama2x kriteria Kode):
=IFERROR(INDEX($B$2:$C$6;SMALL(IF(COUNTIF($B$19:$B$20;INDEX($B$2:$C$6;;1;1))>0;ROW($B$2:$C$6)-MIN(ROW($B$2:$C$6))+1);ROW(A1));2);"")
Kasus 3: Multi Results pada 2 Tabel
Nah bagaimana dengan 2 Tabel? mari kita tampilkan hasil yang diinginkan....
Nah menurut gambar diatas, kita diminta menampilkan data "Item" pada 2 tabel sesuai 2 kriteria
Rumus untuk hasil vertical:
=IFERROR(IFERROR(INDEX($C$2:$C$6;SMALL(IF(($A$2:$A$6=$A$9)*($B$2:$B$6=$B$9);ROW($C$2:$C$6)-MIN(ROW($C$2))+1);ROWS($A$1:A1)));INDEX($G$2:$G$6;SMALL(IF(($E$2:$E$6=$A$9)*($F$2:$F$6=$B$9);ROW($G$2:$G$6)-MIN(ROW($G$2))+1);ROWS($A$1:A1)-COUNTIFS($A$2:$A$6;$A$9;$B$2:$B$6;$B$9))));"")
Dan untuk hasil horizontal:
=IFERROR(IFERROR(INDEX($C$2:$C$6;SMALL(IF(($A$2:$A$6=$A$9)*($B$2:$B$6=$B$9);ROW($C$2:$C$6)-MIN(ROW($C$2))+1);COLUMNS($A$1:A1)));INDEX($G$2:$G$6;SMALL(IF(($E$2:$E$6=$A$9)*($F$2:$F$6=$B$9);ROW($G$2:$G$6)-MIN(ROW($G$2))+1);COLUMNS($A$1:A1)-COUNTIFS($A$2:$A$6;$A$9;$B$2:$B$6;$B$9))));"")
Kasus 4: Multi Results dengan Menghilangkan Duplikasi
Kali ini coba perhatikan, misal kriterianya adalah "101", tetapi kok ada item yang sama ya? Nah kita inginnya membuang yang dobel itu. Hasil yang diinginkan..
Jadi item "Kemoceng" ditampilkan hanya satu saja... ini dia rumusnya
=IFERROR(INDEX($B$2:$B$6;SMALL(IF(($A$9=$A$2:$A$6)*(COUNTIF($B$8:B8;$B$2:$B$6)=0);ROW($A$2:$A$6)-MIN(ROW($A$2:$A$6))+1;"");1));"")
kemudian copas ke bawah
Oh iya semua rumus diatas adalah rumus array atau rumus CSE (CTRL+SHIFT+ENTER)
Semoga bermanfaat.... cheers
File Latihan
Kasus 1: Multi Result 1 Kriteria
Dengan tabel kasus diatas kita ingin mengambil data "Item" sesuai dengan "Kode" 101, disana terlihat kode 101 punya beberapa item. Hasil yang kita inginkan nanti seperti ini...
Jadi hasil multi results bisa dibuat secara vertical ataupun horizontal.
Rumusnya:
Untuk Hasil Vertical:
=IFERROR(INDEX($B$2:$B$6;SMALL(IF(($A$2:$A$6=$B$9)*($B$2:$B$6<>"");ROW($B$2:$B$6)-ROW($B$2)+1);ROWS($B$1:B1)));"")
Kemudian copas ke bawah
Untuk Hasil Horizontal:
=IFERROR(INDEX($B$2:$B$6;SMALL(IF(($A$2:$A$6=$B$15)*($B$2:$B$6<>"");ROW($B$2:$B$6)-ROW($B$2)+1);COLUMNS($B$1:B1)));"")
Kemudian copas kesamping kanan
Kasus 2: Multi Results 2 Kriteria
Hampir sama, hanya ada penambahan kriteria yaitu Kolom "Group", hasil yang diinginkan...
Pada gambar diatas dengan 2 kriteria yaitu Kolom "Group" dan Kolom "Kode" dan hasil yang diambil adalah Kolom "Item", baik secara vertical maupun horizontal.
Yang kedua adalah 2 kriteria pada Kolom "Kode", ditampilkan secara vertical.
Rumus untuk yang vertical (di Cell C9 dan copas ke bawah):
=IFERROR(INDEX($C$2:$C$6;SMALL(IF(($B$2:$B$6=$B$9)*($A$2:$A$6=$B$10)*($C$2:$C$6<>"");ROW($C$2:$C$6)-ROW($C$2)+1);ROWS($C$1:C1)));"")
Rumus untuk hasil horizontal (di Cell C15 dan copas ke kanan):
=IFERROR(INDEX($C$2:$C$6;SMALL(IF(($B$2:$B$6=$B$15)*(A2:A6=B16)*($C$2:$C$6<>"");ROW($C$2:$C$6)-ROW($C$2)+1);COLUMNS($C$1:C1)));"")
Rumus untuk 2 Kriteria "101" dan "202" (sama2x kriteria Kode):
=IFERROR(INDEX($B$2:$C$6;SMALL(IF(COUNTIF($B$19:$B$20;INDEX($B$2:$C$6;;1;1))>0;ROW($B$2:$C$6)-MIN(ROW($B$2:$C$6))+1);ROW(A1));2);"")
Kasus 3: Multi Results pada 2 Tabel
Nah bagaimana dengan 2 Tabel? mari kita tampilkan hasil yang diinginkan....
Rumus untuk hasil vertical:
=IFERROR(IFERROR(INDEX($C$2:$C$6;SMALL(IF(($A$2:$A$6=$A$9)*($B$2:$B$6=$B$9);ROW($C$2:$C$6)-MIN(ROW($C$2))+1);ROWS($A$1:A1)));INDEX($G$2:$G$6;SMALL(IF(($E$2:$E$6=$A$9)*($F$2:$F$6=$B$9);ROW($G$2:$G$6)-MIN(ROW($G$2))+1);ROWS($A$1:A1)-COUNTIFS($A$2:$A$6;$A$9;$B$2:$B$6;$B$9))));"")
Dan untuk hasil horizontal:
=IFERROR(IFERROR(INDEX($C$2:$C$6;SMALL(IF(($A$2:$A$6=$A$9)*($B$2:$B$6=$B$9);ROW($C$2:$C$6)-MIN(ROW($C$2))+1);COLUMNS($A$1:A1)));INDEX($G$2:$G$6;SMALL(IF(($E$2:$E$6=$A$9)*($F$2:$F$6=$B$9);ROW($G$2:$G$6)-MIN(ROW($G$2))+1);COLUMNS($A$1:A1)-COUNTIFS($A$2:$A$6;$A$9;$B$2:$B$6;$B$9))));"")
Kasus 4: Multi Results dengan Menghilangkan Duplikasi
Kali ini coba perhatikan, misal kriterianya adalah "101", tetapi kok ada item yang sama ya? Nah kita inginnya membuang yang dobel itu. Hasil yang diinginkan..
Jadi item "Kemoceng" ditampilkan hanya satu saja... ini dia rumusnya
=IFERROR(INDEX($B$2:$B$6;SMALL(IF(($A$9=$A$2:$A$6)*(COUNTIF($B$8:B8;$B$2:$B$6)=0);ROW($A$2:$A$6)-MIN(ROW($A$2:$A$6))+1;"");1));"")
kemudian copas ke bawah
Oh iya semua rumus diatas adalah rumus array atau rumus CSE (CTRL+SHIFT+ENTER)
Semoga bermanfaat.... cheers
File Latihan
15 Maret 2014
Mengambil Data Penjualan Tertinggi Berdasar Group
Langsung aja ya kasusnya seperti ini......
Nah data ada beberapa sales person yang membawahi area atau daerah penjualan, kita ingin ambil penjualan tertinggi berdasar area/daerah penjualan, hasil yang diinginkan seperti ini:
Rumusnya array lagi ya, seperti ini:
Cell A13:
=IFERROR(INDEX($A$2:$A$10;MATCH(0;COUNTIF($A$2:$A$10;"<"&$A$2:$A$10)-SUM(COUNTIF($A$2:$A$10;"="&A$12:A12));0));"")
Cell B13:
=INDEX($B$2:$B$10;MATCH(LARGE(IF($A$2:$A$10=A13;$C$2:$C$10;"");COUNTIF($A$13:A13;A13));IF($A$2:$A$10=A13;$C$2:$C$10;"");0))
kemudian copas ke bawah.
Kasus yang lain, dari data duatas, kita ingin menjumlahkan data penjualan ranking pertama dan kedua, jadi penjualan Top 2.
Cell B19:
=SUMPRODUCT(LARGE(($A$2:$A$10=A19)*($C$2:$C$10);{1\2}))
copas ke bawah
Happy to learn guys.....
Cheers....
Nah data ada beberapa sales person yang membawahi area atau daerah penjualan, kita ingin ambil penjualan tertinggi berdasar area/daerah penjualan, hasil yang diinginkan seperti ini:
Rumusnya array lagi ya, seperti ini:
Cell A13:
=IFERROR(INDEX($A$2:$A$10;MATCH(0;COUNTIF($A$2:$A$10;"<"&$A$2:$A$10)-SUM(COUNTIF($A$2:$A$10;"="&A$12:A12));0));"")
Cell B13:
=INDEX($B$2:$B$10;MATCH(LARGE(IF($A$2:$A$10=A13;$C$2:$C$10;"");COUNTIF($A$13:A13;A13));IF($A$2:$A$10=A13;$C$2:$C$10;"");0))
kemudian copas ke bawah.
Kasus yang lain, dari data duatas, kita ingin menjumlahkan data penjualan ranking pertama dan kedua, jadi penjualan Top 2.
Cell B19:
=SUMPRODUCT(LARGE(($A$2:$A$10=A19)*($C$2:$C$10);{1\2}))
copas ke bawah
Happy to learn guys.....
Cheers....
Langganan:
Postingan (Atom)