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....
28 Februari 2014
Auto Numbering (Penomoran Otomatis)
Selamat Siang......
Saya ingin mengulas tentang penomoran otomatis, hal ini sering menjadi pertanyaan para pandemen Excel....
1. Penomoran Secara Otomatis Jika Ada Data:
Nah di Kolom B kita isikan nama dan di kolom A adalah nomor. Kebetulan lagi malas nih kasih nomor satu2x. Kebetulan situasinya data tersebut tidak berurutan, loncat 2 baris, 3 baris dsb.... Jadi pengennya kita ketik nama, muncul deh nomornya di kolom sesuai urutan datanya.....
Rumusnya:
Ketik di Kolom A dan copas ke bawah =IF(B1="";"";COUNTA($B$1:B1))
2. Penomoran Tidak Berurutan
Kasusnya kebalikan dari diatas, kali ini tidak urut, gunanya untuk apa ya? mudah2xan ada ya....
Rumus:
=IF(B1="";"";ROWS($B$1:B1))
3.Penomoran Otomatis Digabung Dengan Teks:
Piye rumusnya? ini dia.....
="A000"&ROWS($A$1:A1)
4.Penomoran Dengan Awalan Angka 0
Rumusnya:
=ROW()-1
5.Penomoran Dengan Restart Tiap Kelipatan 5
Nah ini agak menarik, begini kasusnya tiap penomoran sampai 5, penomoran berikutnya mulai lagi dari angka 1, begitu terus berulang-ulang....
Rumusnya:
=MOD(ROW()-1;5)+1
6.Membuat Rentang Angka Secara Otomatis
Ini yang paling menarik menurut saya, membuat seperti ini:
Rumusnya:
=(ROW()-1)*10 +1& "-" & ((ROW()-1)*10) +10
Have a nice days para pembaca budiman....
Unduh File
Saya ingin mengulas tentang penomoran otomatis, hal ini sering menjadi pertanyaan para pandemen Excel....
1. Penomoran Secara Otomatis Jika Ada Data:
Nah di Kolom B kita isikan nama dan di kolom A adalah nomor. Kebetulan lagi malas nih kasih nomor satu2x. Kebetulan situasinya data tersebut tidak berurutan, loncat 2 baris, 3 baris dsb.... Jadi pengennya kita ketik nama, muncul deh nomornya di kolom sesuai urutan datanya.....
Rumusnya:
Ketik di Kolom A dan copas ke bawah =IF(B1="";"";COUNTA($B$1:B1))
2. Penomoran Tidak Berurutan
Kasusnya kebalikan dari diatas, kali ini tidak urut, gunanya untuk apa ya? mudah2xan ada ya....
Rumus:
=IF(B1="";"";ROWS($B$1:B1))
3.Penomoran Otomatis Digabung Dengan Teks:
Piye rumusnya? ini dia.....
="A000"&ROWS($A$1:A1)
4.Penomoran Dengan Awalan Angka 0
Rumusnya:
=ROW()-1
5.Penomoran Dengan Restart Tiap Kelipatan 5
Nah ini agak menarik, begini kasusnya tiap penomoran sampai 5, penomoran berikutnya mulai lagi dari angka 1, begitu terus berulang-ulang....
Rumusnya:
=MOD(ROW()-1;5)+1
6.Membuat Rentang Angka Secara Otomatis
Ini yang paling menarik menurut saya, membuat seperti ini:
Rumusnya:
=(ROW()-1)*10 +1& "-" & ((ROW()-1)*10) +10
Have a nice days para pembaca budiman....
Unduh File
13 Februari 2014
Menggabungkan Data Antara 2 Tabel atau Lebih
Saya perbarui lagi postingan mengenai menggabungkan data, langsung saja, ada beberapa kasus:
1. Menggabungkan 2 Tabel
1. Menggabungkan 2 Tabel
Nah disini kita ingin menggabungkan Nama-nama yang ada di Group1 dengan Group2, hasil yang diinginkan seperti ini:
Bagaimana rumusnya? ini dia...
=IFERROR(IFERROR(INDEX($A$2:$A$4;ROWS($A$2:A2));INDEX($B$2:$B$3;ROWS($B$2:B2)-ROWS($A$2:$A$4)));"")
Ini rumus biasa ya bukan rumus array......
2. Menggabungkan 3 Tabel
Hasilnya seperti ini nanti
=IFERROR(IFERROR(INDEX($A$2:$A$4;ROWS($A$2:A2));INDEX($B$2:$B$3;ROWS($B$2:B2)-ROWS($A$2:$A$4)));"")
Ini rumus biasa ya bukan rumus array......
2. Menggabungkan 3 Tabel
Hasilnya seperti ini nanti
Rumusnya:
=IFERROR(IFERROR(IFERROR(INDEX($A$2:$A$4;ROWS($A$2:A2));INDEX($B$2:$B$3;ROWS($B$2:B2)-ROWS($A$2:$A$4)));INDEX($C$2:$C$5;ROWS($C$2:C2)-ROWS($A$2:$A$4)-ROWS($B$2:$B$3)));"")
Tambah panjang karena tabelnya juga nambah......
3. Menggabungkan Antara 2 Tabel dan Menghilangkan Data Dobel:
Hasil yang diinginkan adalah menggabungkan data dengan menghilangkan data yang dobel
Rumusnya seperti ini: (Array Formula)
=IFERROR(IFERROR(INDEX($A$2:$A$5;SMALL(IF(COUNTIF(A7:$A$7;$A$2:$A$5)=0;ROW($A$2:$A$5)-MIN(ROW($A$2:$A$5))+1;"");1));INDEX($B$2:$B$4;SMALL(IF(COUNTIF(A7:$A$7;$B$2:$B$4)=0;ROW($B$2:$B$4)-MIN(ROW($B$2:$B$4))+1;"");1)));"")
4. Menggabungkan Antara 3 Tabel dan Menghilangkan Data Dobel:
Hasil yang diinginkan:
Rumusnya: (Array Formula)
=IFERROR(IFERROR(IFERROR(INDEX($A$2:$A$5;SMALL(IF(COUNTIF(A7:$A$7;$A$2:$A$5)=0;ROW($A$2:$A$5)-MIN(ROW($A$2:$A$5))+1;"");1));INDEX($B$2:$B$4;SMALL(IF(COUNTIF(A7:$A$7;$B$2:$B$4)=0;ROW($B$2:$B$4)-MIN(ROW($B$2:$B$4))+1;"");1)));INDEX($C$2:$C$5;SMALL(IF(COUNTIF(A7:$A$7;$C$2:$C$5)=0;ROW($C$2:$C$5)-MIN(ROW($C$2:$C$5))+1;"");1)));"")
Happy to learn everyday......
Salam
Silahkan unduh File Latihan
=IFERROR(IFERROR(IFERROR(INDEX($A$2:$A$4;ROWS($A$2:A2));INDEX($B$2:$B$3;ROWS($B$2:B2)-ROWS($A$2:$A$4)));INDEX($C$2:$C$5;ROWS($C$2:C2)-ROWS($A$2:$A$4)-ROWS($B$2:$B$3)));"")
Tambah panjang karena tabelnya juga nambah......
3. Menggabungkan Antara 2 Tabel dan Menghilangkan Data Dobel:
Hasil yang diinginkan adalah menggabungkan data dengan menghilangkan data yang dobel
Rumusnya seperti ini: (Array Formula)
=IFERROR(IFERROR(INDEX($A$2:$A$5;SMALL(IF(COUNTIF(A7:$A$7;$A$2:$A$5)=0;ROW($A$2:$A$5)-MIN(ROW($A$2:$A$5))+1;"");1));INDEX($B$2:$B$4;SMALL(IF(COUNTIF(A7:$A$7;$B$2:$B$4)=0;ROW($B$2:$B$4)-MIN(ROW($B$2:$B$4))+1;"");1)));"")
4. Menggabungkan Antara 3 Tabel dan Menghilangkan Data Dobel:
Hasil yang diinginkan:
Rumusnya: (Array Formula)
=IFERROR(IFERROR(IFERROR(INDEX($A$2:$A$5;SMALL(IF(COUNTIF(A7:$A$7;$A$2:$A$5)=0;ROW($A$2:$A$5)-MIN(ROW($A$2:$A$5))+1;"");1));INDEX($B$2:$B$4;SMALL(IF(COUNTIF(A7:$A$7;$B$2:$B$4)=0;ROW($B$2:$B$4)-MIN(ROW($B$2:$B$4))+1;"");1)));INDEX($C$2:$C$5;SMALL(IF(COUNTIF(A7:$A$7;$C$2:$C$5)=0;ROW($C$2:$C$5)-MIN(ROW($C$2:$C$5))+1;"");1)));"")
Happy to learn everyday......
Salam
Silahkan unduh File Latihan
Langganan:
Postingan (Atom)