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

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

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....

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

Menggabungkan Data Antara 2 Tabel atau Lebih

Saya perbarui lagi postingan mengenai menggabungkan data, langsung saja, ada beberapa kasus:

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

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

Gelar Akademik

Pagi Bro semua..... saya pernah diminta tolong teman untuk memanipulasi daftar nama wisudawan S2 yang namanya terlalu panjang karena dicantumkannya gelar S1 dan dia ingin menyingkatnya menjadi nama + gelar S2 nya saja, so gelar S1-nya dihilangkan.

Kasusnya :

Selain itu teman saya itu ingin mengambil Nama+Gelar S1 dan terakhir namanya saja...... akhirnya utak atik dan ini rumusnya......

untuk ambil Nama dan Gelar S2:
=TRIM(LEFT(A1;SEARCH(",";A1;1)-1)&", "&TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";LEN(A1)));LEN(A1))))

untuk ambil Nama dan Gelar S1:
=TRIM(LEFT(A1;FIND(",";A1;1)-1))&", "&TRIM(MID(A1;SEARCH(", ";A1)+1;SEARCH(", ";A1;SEARCH(",";A1)+1)-SEARCH(", ";A1)-1))

untuk ambil Nama saja:
=TRIM(LEFT(A1;FIND(",";A1;1)-1))

Tapi ingat ya, format penulisannya harus sesuai contoh, terutama penulisan titik, koma dan spasinya......

Lebih jelasnya silahkan unduh File Latihannya....

2 Way Lookup

Apa itu 2 Way Lookup, yaitu mengambil data pada pertemuan kriteria Column Header (Judul Kolom) dan Row Header (Judul Baris) tertentu, untuk jelasnya, biasanya Lay Out tabelnya seperti ini:

Lay out table seperti ini lazim digunakan oleh para pengguna Excel.

PERMASALAHAN 1:
Bagaimana jika kita ingin mengetahui Nilai dari Sarah di mata pelajaran MS Word?

Tabel Data berada pada range A1:E5, dan kriteria berada di A8 dan A9:

Beberapa rumus yang bisa diaplikasikan:

1. =INDEX(B2:E5;MATCH(A8;A2:A5;0);MATCH(A9;B1:E1;0))

2. =INDEX(B2:E5;0;MATCH(A9;B1:E1;0)) INDEX(B2:E5;MATCH(A8;A2:A5;0);0)

3. =LOOKUP(2;1/(A2:A5=A8);INDEX(B2:E5;0;MATCH(A9;B1:E1;0)))

4. =VLOOKUP(A8;A2:E5;MATCH(A9;A1:E1;0);0)

5. =OFFSET(A1;MATCH(A8;A2:A5;0);MATCH(A9;B1:E1;0))

PERMASALAHAN 2:
Umpama kita sudah punya data angka "87", nah dari data ini siapa sih yang mendapatkan nilai 87 dan di mata pelajaran apa?








Untuk mengambil Column Header, rumusnya:
=INDEX(B1:E1;;MAX(IF(B2:E5=D8;COLUMN(B21:E21)-COLUMN(B21)+1)))

Untuk mengambil Row Header, rumusnya:
=INDEX(A2:A5;MAX(IF(B2:E5=D8;ROW(A2:A5)-ROW(A2)+1)))

2 terakhir ini Array Formula ya.... dan untuk catatan kondisi value/data angka yang ada di tabel tidak terdapat data yang sama/dobel. Untuk data dobel dengan lay out tabel seperti ini rumusnya akan berbeda lagi.

Unduh FIle Latihan

Salam.....

Mempersingkat Nama Depan, Tengah dan Nama Belakang

Ass.Wr.Wb.

Selamat siang para pembaca, hari ini saya akan posting rumus untuk memodifikasi teks, kebetulan ada kasus mempersingkat nama. Nah biasanya yang lazim yang disingkat adalah nama depan, nama tengah, tapi nama belakang disingkat juga lazim sih....

Joko Santoso Mulyono disingkat menjadiJ. Santoso Mulyono atau Joko S. Mulyono atau Joko Santoso M.

Solusi Mempersingkat Nama Depan:

=LEFT(A1;1)&". "&MID(A1;FIND(" ";A1)+1;255)

atau

=REPLACE(A1;2;FIND(" ";A1)-2;".")

Solusi Mempersingkat Nama Tengah:

=REPLACE(A1;FIND(" ";A1)+2;FIND(" ";A1;FIND(" ";A1)+1)-FIND(" ";A1)-2;".")

atau

=CHOOSE(LEN(A1)-LEN(SUBSTITUTE(A1;" ";""));A1;SUBSTITUTE(A1;MID(A1;SEARCH(" ";A1)+1;SEARCH(" ";A1;SEARCH(" ";A1)+1)-SEARCH(" ";A1));MID(A1;SEARCH(" ";A1)+1;1)&". "))


Solusi Mempersingkat Nama Belakang:

=TRIM(LEFT(A1;SEARCH(" ";A1;SEARCH(" ";A1;1)+1)))&" "&MID(A1;FIND(" ";A1;FIND(" ";A1;1)+1)+1;1)&"."

atau

=LEFT(TRIM(A1);FIND("~";SUBSTITUTE(A1;" ";"~";LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1);" ";""))))-1)&" "&MID(A1;FIND("~";SUBSTITUTE(A1;" ";"~";LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))))+1;1)&"."

 Solusi Mempersingkat 2 Nama Depan:

=LEFT(A1;1)&"."&(MID(A1;FIND(" ";A1;1)+1;1))&". "&TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";256));256))


Solusi Membuat Inisial:

Misalkan dari contoh nama diatas kita ingin membuat inisialnya, dari Joko Santosa Mulyono menjadi JSM, nah rumusnya.....

=IFERROR(LEFT(A1;1)&(MID(A1;FIND(" ";A1;1)+1;1))&MID(A1;FIND(" ";A1;FIND(" ";A1)+1)+1;1);LEFT(A1;1)&(MID(A1;FIND(" ";A1;1)+1;1)))

atau

=IF(LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))=0;LEFT(A1;1);IF(LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))=1;LEFT(A1;1)&MID(A1;FIND(" ";A1)+1;1);LEFT(A1;1)&MID(A1;FIND(" ";A1)+1;1)&MID(A1;FIND(" ";A1;FIND(" ";A1)+1)+1;1)))

Dicoba ya.....matursuwun

Rizky

Download File Latihan

Partial Lookup 2

Ass. Wr.Wb.

Long time no see you guys, wish you all the best......... ;)

Psotingan kali ini masih melanjutkan tentang Partial Lookup dengan contoh untuk memfilter data sesuai keinginan kita.

Kasus 1:
Misalkan kita punya daftar alamat Restoran beserta alamatnya, seperti ini :

Data tersebut acak dengan alamat resto di Kota Yogyakarta dan Jakarta, bayangkan kalo kita punya database ratusan atau ribuan alamat yang terkadang kita ingin mengambil data tersebut sesuai kebutuhan kita saja. Sesuai contoh diatas, misalnya saja saya ingin menampilan data Resto yang alamatnya di Yogyakarta saja, bisa ndak ya? Pengennya seperti ini hasilnya :

Rumusnya seperti ini
=IF($A$13="";"";IFERROR(INDEX($A$3:$B$10;SMALL(IF(ISNUMBER(SEARCH($A$13;$B$3:$B$10));ROW($A$3:$A$10)-ROW($B$2));ROW(A1:C1));2);""))

di input mulai di Cell B13 dan ci-copas ke bawah


Kasus 2:
Mirip dengan yang diatas hanya saja rumusnya dan data yang diambil berbeda, kasus ke 2 ini kita ingin memfilter Nama Orang.

Dengan Kriteria dan hasilnya yang diinginkan seperti ini:

Rumusnya :
=IFERROR(INDEX(A$26:A$32;SMALL(IF(ISNUMBER(SEARCH($A$35;$A$26:$A$32));ROW($A$26:$A$32)-MIN(ROW($A$26:$A$32))+1);ROWS($1:1)));"")

Versi lainnya:
=IFERROR(INDEX($A$26:$A$32,SMALL(IF(ISERROR(SEARCH($A$35,$A$26:$A$32)),"",MATCH(ROW($A$26:$A$32),ROW($A$26:$A$32))),ROW(A1))),"")

Sumonggo di download File Latihannya

Partial Lookup

Happy Weekend Guys.....

Mohon maaf karena saya baru sempet posting lagi.......... ok apa artinya partial lookup? Misalnya kita punya Kode 007-2BR-121, Nah kode tersebut dipisahkan oleh tanda sambung "-". Masing2x kode tersebut mempresentasikan seperti ini :
007 = Kode untuk Nama Barang
2BR = Kode untuk Nama Supplier
121 = Kode untuk Harga Barang

Jadi masing2x kode memiliki tabel rujukan sehingga ada 3 tabel rujukan....... jadi secara parsial kode tersebut merujuk adanya informasi dari 3 tabel rujukan. Nah psotinga kita kali ini membahas bagaimana sih cara mengambil data dengan partial lookup.

Kasus 1:

Sesuai contoh diatas, kita ingin mengambil data Nama Supplier, Item Barang dan Harga Barang, jadi fungsi LOOKUP kita gabungkan dengan Fungsi Teks seperti LEFT, MID ataupun RIGHT.

Untuk mencari Data Supplier :
=VLOOKUP(LEFT(A3;SEARCH("-";A3)-1);$D$3:$E$5;2;0)

Untuk mencari Data Harga:
=VLOOKUP(MID(A10;SEARCH("-";A10)+1;3)+0;$D$10:$E$12;2;0)

Untuk Mencari Data Item:
=VLOOKUP(RIGHT(A17;4);$D$17:$E$19;2;0)

Kasus 2:

Ini agak sedikit rumit dimana kriteria yang akan kita cari seperti ini Adams.John.EJ04 dimana data yang kita cari ambil hanya EJ04 padahal EJ04 jadi satu kalimat di Adams.John.EJ04

rumusnya :
=VLOOKUP(MID(B11;SEARCH(".";B11;SEARCH(".";B11;1)+1)+1;4);A2:B8;2;0)

Kasus 3:

Lha bagaimana jika Tabel Rujukannya hanya 1 Kolom? Ya itu tambah rumit.... tapi masih bisa diatasi dengan Excel.....

Rumusnya:
=VLOOKUP(MID(A11;FIND(".";A11;FIND(".";A11;1)+1)+1;4)&"*";A2:A8;1;0)

Kasus 4:

Ini mirip dengan kasus 3, hanya saja dengan rumus yang berbeda:
=INDEX(A1:A7;MATCH(LEFT(A10;5);LEFT(A1:A7;5);0))

Mungkin masih ada yang awam mengenai fungsi teks seperti LEFT, MID, RIGHT, SEARCH, FIND, dll mungkin perlu belajar basic-nya terlebih dahulu.....karena mereka fungsi yang cukup sering digunakan untuk manipulasi text string.

Silahkan Download FIle Latihan