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