Mengekstrak Angka dari Text Data Entri

Meng-extract porsi angka dari sebuah entri data dapat dilakukan dengan berbagai cara. Jika sejumlah entri data mempunya pola urutan huruf dan angka yang tetap serta panjang text-nya konstan, maka kita bisa mengambil porsi angka dengan mudah menggunakan fungsi pengolah text seperti LEFT, RIGHT dan MID dikombinasikan dengan fungsi CONCATENATE. Namun jika pola urutan kombinasi angka dan huruf tidak tetap, maka fungsi standar pengolah text di excel tidak bisa menyelesaikan kasus tersebut. Untuk itu diperlukan sebuah UDF (User Defined Function) untuk menyelesaikan tugas ini.

Catatan pelajaran excel kali ini akan membahas bagaimana membuat dan menerapkan fungsi ambilAngka(), dimana fungsi ini berguna untuk men-ektrak porsi angka dari sebuah entri text.







Screenshot berikut memperlihatkan bagaimana fungsi ambilAngka() bisa mengextract porsi angka dari entri data, tidak peduli bagaimana pola susunan karakter serta panjang data entri.

Selanjutnya mari kita simak baik-baik bagaimana menerapkan code VBA untuk membuat fungsi ambilAngka sehingga bisa diterapkan pada spreadsheet seperti gambar di atas.

Contoh kode VBA untuk extract angka dari text.

Berikut contoh kode VBA yang dapat digunakan untuk extrak porsi angka dari data entri.

Function ambilAngka(txt As String) As String
Dim i As Integer, iKarakter As String, Angka As String
For i = 1 To Len(txt)
  iKarakter = Mid(txt, i, 1)
  If IsNumeric(iKarakter) Then
    Angka = Angka & iKarakter
  End If
Next
ambilAngka = Angka
End Function


Supaya code diatas dapat digunakan maka harus diketikan atau dicopy ke modul VBA. Jika pembaca sudah mengenal dasar – dasar VBA sebelumnya, tentunya bukan hal yang sulit bagi anda untuk segera mengcopy kan code di atas ke modul VBA.

Bagi pembaca yang masih baru mengenal VBA tidak perlu khawatir. VBA itu sangat menyenangkan, apalagi jika kita bisa merasakan manfaatnya yang luar biasa dalam meningkatkan efisiensi dan efektifitas kerja menggunakan microsoft Excel.

Baiklah mari kita lanjutkan. Bagaimana masuk ke modul VBA.

• Untuk excel 2007 atau yang lebih baru, pastikan tab developer tersedia dan setting macro security enable. Demikian juga jika anda masih menggunakan excel 2003, pastikan macro security enable.
• Untuk masuk ke module VBA, tekan shortcut ALT = F11 atau melalui ribbon dengan cara klik icon Visual Basic pada tab developer.

• Pada jendela VBA, klik menu Insert → klik Module

• Langkah selanjutnya ketikan atau copy kode VBA di atas pada module seperti diperlihatkan dalam screenshot di bawah ini.

• Setelah code diketik/ di copy ke modul VBA, maka fungsi ambilAngka() sudah tersedia dan siap digunakan.
• Simpan file dengan extension .xlsm (Excel Macro - Enable Workbook) atau dengan extension .xlsb (Excel Binary Workbook) jika anda menggunakan excel 2007, 2010 atau versi yang lebih baru.

Cara menggunakan fungsi ambilAngka()

Gambaran cara menggunakan fungsi ambilAngka() sudah diperlihatkan pada bagian awal catatan ini., silahkan di scroll kembali ke bagian atas untuk melihat screenshot contoh penerapannya di excel.

Cara penulisan rumusnya sangat sederhana. Yaitu:

=ambilangka(entri)

Misalnya kita menuliskan rumus sebagai berikut:

=ambilangka("AB12cfgR44Db")

Maka ouput dari rumus di atas adalah : "1244" yang merupakan porsi angka dari "AB12cfgR44Db"
Karena data entri terletak dalam sel excel, maka rumus ambilAngka dapat dituliskan dengan menggunakan referensi sel:

Misalnya:

=ambilAngka(A1)

Rumus ini berguna untuk mengambil porsi angka dari text data entri yang terletak pada sel A1.

Demikian pembahasan singkat mengenai contoh kode macro / vba yang dapat digunakan untuk mengekstrak porsi angka dari entri text. Semoga bermanfaat.

Salam.

Artikel terkait:

• Konversi Angka Berformat Text Menjadi Angka Nyata
• Membuat Daftar Isi Otomatis
• Memasukan Data ke Beberapa Worksheet Sekaligus

Rumus Konversi Huruf Kolom Menjadi Angka

Di Excel kita bisa membuat rumus untuk mengkonversi huruf kolom menjadi angka nomor urut kolom dalam spreadsheet.

Contohnya :

• A menjadi 1
• B menjadi 2
• C menjadi 3
• Z menjadi 26
• AA menjadi 27
• … dan seterusnya.

Adapun cara atau formula yang dapat digunakan adalah dengan memanfaatkan kombinasi fungsi COLUMN dan INDIRECT

Contoh Rumus Untuk Mengkonversi Huruf Kolom Menjadi Angka

Untuk lebih jelasnya mari kita lihat contoh rumus berikut. Silahkan copy tabel berikut ke dalam spreadsheet sel A1 sehingga kita bisa melihat hasilnya.

A	B	C
2	Contoh Rumus	Penjelasan
3	=COLUMN(INDIRECT("B1"))	Mengkonversi huruf B menjadi angka. Nomor urut kolom B adalah 2
4	=COLUMN(INDIRECT(B9&1))	Mengkonversi text kolom pada sel B9 (AA) menjadi angka. Nomor urut kolom AA adalah 27
5	=COLUMN(INDIRECT("XYZ1"))	Mencoba mengkonversi huruf XYZ menjadi nomor. Menghasilkan error #REF! karena ZYZ melebihi batas maksimuk kolom pada excel (kolom maksimal excel versi 2007 s.d 2016 adalah XFD=16384)
6	=COLUMN(INDIRECT(B10&1))	Mencoba mengkonversi huruf yang ada pada sel B10 yaitu ZZZ menjadi nomor. Menghasilkan error #REF! karena ZZZ melebihi batas maksimum kolom pada excel (kolom maksimal excel versi 2007 s.d 2016 adalah XFD=16384)
7
8	Test Ref:
9	AA
10	ZZZ

Penjelasan Cara Kerja Rumus Untuk Mengkonversi Huruf Kolom Menjadi Angka (Nomor Urut Kolom)

Pertama :

Fungsi INDIRECT digunakan untuk mendapatkan referensi sel sesuai text referensi yang diberikan.

Misalnya jika text yang diumpankan adalah "B1", maka INDIRECT akan mengarah ke sel referensi B1.

Contoh:

• Rumus INDIRECT("C2") akan mengarahkan ke referensi C2 dan karena sel C2 berisi text "Penjelasan" maka rumus tersebut akan menghasilkan text "Penjelasan"
• Rumus INDIRECT("B2") akan mengarahkan ke referensi B2 dan karena sel B2 berisi text "Contoh Rumus" maka rumus tersebut akan menghasilkan text "Contoh Rumus"

Kedua:

Fungsi COLUMN berguna untuk mendapatkan nomor urut kolom dari referensi yang diberikan.

Contoh:

• Rumus COLUMN(B1) akan mendapatkan angka nomor kolom dari sel B1 yaitu 2
• Rumus COLUMN(A1) akan mendapatkan angka nomor kolom dari sel A1 yaitu 1

Dari penjelasan cara kerja masing-masing fungsi di atas, kemudian dapat dijelaskan alur kerja formula excel untuk mengkonversi huruf kolom menjadi nomor sesuai contoh berikut :

• Rumus masih lengkap
• =COLUMN(INDIRECT("B1"))
• Referensi Text “B1” sudah dirubah menjadi referensi B1 oleh fungsi INDIRECT
• =COLUMN(B1)
• Fungsi COLUMN menghasilkan angka 2 yang merupakan nomor urut kolom B 

Hal yang sama berlaku juga jika kita menggunakan referensi sel untuk menempatkan text referensi (perhatikan contoh pada tabel di atas baris ke-4

• Rumus masih lengkap
• =COLUMN(INDIRECT(B9&1))
• Sel B9 berisi text "AA", dan jika digabung dengan angka 1 (rumus B9&1), maka hasilnya "AA1"
• =COLUMN(INDIRECT("AA1"))
• Referensi Text “AA1” kemudian dirubah menjadi referensi AA1 oleh fungsi INDIRECT, yang selanjutnya diolah oleh fungsi COLUMN
• =COLUMN(AA1)
• Fungsi COLUMN menghasilkan angka 27 yang merupakan nomor urut kolom AA dalam lembar kerja excel.

Perlu diperhatikan: Dalam contoh – contoh formula diatas kita menggunakan index baris 1, misalnya B1, XYZ1. Sebenarnya angka tersebut bisa diubah dengan angka berapa saja sepanjang masih dalam lingkup baris yang dibatasi dalam lembar kerja excel.

Kenapa Error:

Error terjadi karena penggunaan text kolom yang diluar batas jumlah kolom yang disediakan oleh excel.

Dalam contoh diatas jika kita menggunakan kolom XYZ atau ZZZ maka rumus akan menghasilkan nilai error #REF! karena XYZ dan ZZZ tidak tersedia di excel.

Kolom maksimal pada excel versi 2007. 1010, 2013 dan 2016 adalah XFD atau kolom ke-16384. Sedangkan untuk excel versi 2003, kolom maksimalnya adalah kolom IV atau kolom ke-256

Demikian tips singkat mengenai cara membuat rumus excel untuk mengkonversi huruf kolom menjadi angka nomor urut kolom.

Salam..

Baca juga, artikel tutorial belajar excel lainnya:

• Kenapa Header Kolom Berubah Menjadi Angka
• Cara Menghitung Jumlah Huruf dan Kata
• Cara Memisahkan Kata Per Huruf

3 Alternatif Rumus Cari Data Banyak Kriteria

Masih dalam lingkup topik pencarian data di excel. Catatan pelajaran excel kali ini akan membahas mengenai cara mencari data dengan banyak kriteria.

Pada postingan beberapa waktu yang lalu mengenai fungsi VLOOKUP sebenarnya sudah dibahas mengenai cara pencarian data dengan dua kriteria. Namun dalam contoh tersebut diperlukan kolom bantu yang berisikan data gabungan (concatenate) dari kriteria yang ditetapkan.

Nah… sedangkan dalam kesempatan ini kita akan menggunakan rumus pencari data banyak kriteria tanpa harus menggunakan kolom bantu.

Ada beberapa alternative formula yang dapat digunakan. Berikut 3 diantaranya:

1. INDEX MATCH
2. OFFSET MATCH
3. INDIRECT-ADDRESS-MATCH-ROW-COLUMN

Ketiga formula tersebut harus dibuat dalam bentuk rumus array yaitu dengan cara menekan CTR+SHIFT+ENTER setiap kali selesai mengetik atau mengedit rumus.

Baiklah kita lanjutkan dengan Studi Kasus.

Studi Kasus Pencarian Data Dengan Banyak Kriteria di Excel

Anggaplah kita memiliki tabel data harga buah-buahan dari berbagai supplier. Label kolom tabel dari kiri ke kanan adalah:

• Kolom A = No
• Kolom B = Buah
• Kolom C = Supplier
• Kolom D = Harga

Selanjutnya misalnya kita harus mencari data harga buah dari suplier tertentu.

Dengan kata lain, ada dua kriteria yang harus diperhatikan dalam tugas pencarian data ini, yaitu:

• Kriteria 1 = nama buah
• Kriteria 2 = nama suplier

Pertanyaannya: Bagaimana rumusnya untuk mendapatkan data harga buah tersebut secara cepat sehingga tidak harus melihat satu persatu baris dalam tabel .

Contoh kasus dan pertanyaan tersebut dapat digambarkan sebagai berikut:

Maaf, gambar tersebut hanyalah sebagai contoh saja, dengan baris data yang sangat sedikit. Manfaat sebenarnya akan terasa jika bekerja dengan baris data yang besar yang menyulitkan pencarian data dengan mata langsung.

Selain itu, contoh yang ditampilkan juga hanya menggunakan 2 kriteria, namun pada dasarnya prinsip kerjanya adalah sama, baik menggunakan 2 kriteria, 3 kriteria atau lebih banyak lagi.

Selanjutnya mari kita bahas satu persatu.

Perlu di catat: Contoh-Contoh rumus yang ditampilkan dalam pembahasan ini menggunakan rumus array, sehingga ketika  selesai mengetik atau mengedit rumus, maka kita harus menekan CTR+SHIFT+ENTER.

Contoh Rumus Cari Data Dengan Dua Kritera : INDEX-MATCH

Salah satu rumus terpopuler yang biasa digunakan untuk pencarian data adalah kombinasi fungsi INDEX dan MATCH.

Dalam hal contoh kasus di atas, kita bisa menggunakan rumus berikut untuk mencari harga buah tertentu dari supplier tertentu:

{=INDEX(D2:D8,MATCH(G1&G2,B2:B8&C2:C8,0))}

Cara Kerja Rumus INDEX MATCH

• Operator ampersand (&) digunakan untuk menggabungkan kriteria nama buah (G1) dan supplier (G2) serta menggabungkan masing-masing sel sejajar pada kolom  kolom buah (B2:B8) dan supplier (C 2:C8)
• Fungsi MATCH berguna untuk mencari nomor index baris data yang dimana kolom B harus berisi nama buah yang sama dengan nama buah di sel G1, serta kolom C harus berisi nama buah yang sama dengan sel G2. Dalam contoh yang ditampilkan, rumus ini menghasilkan angka 3 yaitu posisi baris yang yang berisi buah pisang dan suplier CV Subur.
• Angka yang diperoleh dari fungsi MATCH kemudian digunakan oleh fungsi INDEX sebagai row_index dalam range D2:D8. 
• Dan sel baris ke-3 dalam range D2:D8 adalah sel D4, sementara itu sel D4 berisi nilai 8000.
• Sehingga hasil akhir adalah 8000.

Contoh Rumus Cari Data Dengan Dua Kritera : OFFSET-MATCH

Fungsi OFFSET berguna untuk mendapatkan referensi sel sesuai jarak berapa kolom dan berapa baris dari sel acuan.

Dengan menggabungkannya dengan fungsi MATCH, maka kita akan menentukan berapa  baris jarak sel yang dicari dari sel acuan, kemudian mendapatkan nilai dari sel yang dicari tersebut.

{=OFFSET(D1,MATCH(G1&G2,B2:B8&C2:C8,0),0)}

Cara Kerja Rumus OFFSET-MATCH

• Fungsi MATCH berperan sama seperti halnya dalam rumus INDEX MATCH, dimana fungsi MATCH  dalam contoh tersebut menghasilkan angka 3.
• Angka 3 terebut kemudian dijadikan sebagai argumen rows oleh fungsi OFFSET, atau sederhana dapat dituliskan =OFFSET(D1,3,0) . Rumus berfungsi untuk mendapatkan nilai dari sel yang jarak nya 3 baris dan 0 kolom dari sel D1.
• Dan sel dimaksud dengan kriteria tersebut adalah sel D4
• Sementara itu sel D4 berisi nilai 8.000.   Bilangan inilah yang merupakan hasil akhirnya.

Contoh Rumus Cari Data Dengan Dua Kritera : INDIRECT-ADDRESS-MATCH-ROW-COLUMN

Fungsi INDIRECT berguna untuk mendapatkan nilai secara tidak langsung dari string yang mereferensikan sebuah alamat sel tertentu.

Sedangkan fungsi ADDRESS digunakan untuk membuat string alamat sel sesuai baris yang diperoleh dari fungsi MATCH dan kolom yang diperoleh dari fungsi COLUMN.

{=INDIRECT(ADDRESS(MATCH(G1&G2,B2:B8&C2:C8,0)+ROW(D1),COLUMN(D2:D8)))}

Cara Kerja Rumus INDIRECT-ADDRESS-MATCH-ROW-COLUMN

• Fungsi MATCH berguna untuk mendapatkan nomor urut kolom dalam range B2:B8 dan C2:C8 dimana sel yang sejajar berisi masing masing kriteria yang ditetapkan. Hasil dari proses tersebut ditambahkan dengan index ROW sel D1 untuk mendapatkan index baris dalam spreadsheet. Dalam contoh, fungsi match menghasilkan angka  3. Kemudian angka tersebut dikurangi 1 (posisi baris sel D1) sehingga mendapatkan angka 4.
• Index Kolom dalam spreadsheet didapatkan menggunakan bantuan fungsi COLUMN,  dan kolom D2:D8 adalah 4 (kolom D)
• Ringkasnya rumus tersebut dapat dikonvert menjadi =INDIRECT(ADDRESS(4,4))
• ADDRESS(4,4) menghasilkan string referensi sel "$D$4"
• Kemudian fungsi INDIRECT mengambil tugasnya untuk mendapatkan nilai dalam sel $D$4
• Dan hasilnya adalah 8.000

  

Ringkasan.

Cari data dengan 2 kriteria atau lebih dapat dilakukan tanpa menggunakan kolom bantu, yaitu menggunakan rumus array, kombinasi INDEX-MATCH, OFFSET-MATCH, dan INDIRECT-ADDRESS-MATCH-ROW-COLUMN. Dari ketiga rumus tersebut ada persamaan peranan fungsi MATCH untuk mencari nomor urut baris yang memenuhi kriteria, yang selanjutnya dapat digunakan sesuai kebutuhan fungsi lainnya.

Demikian semoga bermanfaat.

Salam

Silahkan dibaca juga artikel Belajar Excel Lainnya:

• 3 Alternative Rumus VLOOKUP Ke Kiri
• Fungsi MATCH dan Contoh Rumusnya
• Mengenal Fungsi dan Rumus VLOOKUP
• Memahami Rumus VLOOKUP Dengan Studi Kasus
• Mengenal Rumus Array

5 Alternative Rumus VLOOKUP Ke Kiri

Perlu Rumus excel untuk cari data? VLOOKUP jawabannya. Namun VLOOKUP saja masih belum cukup, terutama jika kita ingin cari data ke sebelah kiri.

Kenapa?... Karena ternyata fungsi ini masih memiliki kelemahan, yaitu hanya dapat mencari data dari kiri ke kanan tabel. Sedangkan dalam prakteknya, struktur atau susunan kolom dalam tabel tidak selalu bisa diprediksi dan tidak selalu cocok dengan kondisi ideal penggunaan formula VLOOKUP.

Namun demikian tidak perlu khawatir, karena excel sudah menyediakan berbagai alternatif solusi pencarian data. Dan atas dasar hal tersebut juga, maka catatan pelajaran excel  ini dipersiapkan untuk membahas 5 alternative rumus vlookup data di sebelah kiri.

Adapun kelima alternative rumus cari data tersebut adalah:

1. Rumus VLOOKUP CHOOSE
2. Rumus HLOOKUP MATCH
3. Rumus INDEX MATCH
4. Rumus OFFSET MATCH
5. Rumus INDIRECT ADDRESS MATCH  COLUMN

Perlu dicatat, meskipun contoh-contoh rumus yang disampaikan dalam pembahasan ini ditujukan untuk melakukan VLOOKUP data di sebelah kiri, namun rumus-rumus tersebut juga dapat digunakan untuk VLOOKUP data ke sebelah kanan. Penekanan pembahasan cari data ke sebelah kiri karena memang itulah yang biasanya sering menjadi pertanyaan, terutama dari pengguna excel yang baru mengenal atau menggunakan rumus VLOOKUP.

Contoh Kasus Rumus VLOOKUP Untuk Cari Data Sebelah Kiri

Anggaplah kita memiliki sebuah tabel berisi data karyawan pada kolom A s.d E, dengan susunan label kolom dari kiri ke kanan: "No", "Nama", "Umur", "ID", dan "Alamat".

Tugas selanjutnya adalah bagaimana mencari nama karyawan berdasarkan nomor ID yang sudah diketahui seperti yang ditunjukan dalam gambar berikut:

Dari gambar diatas, dapat dilihat bahwa data yang diketahui (ID) terletak di kolom D, sedangkan data yang di diinginkan (Nama) terletak di kolom B. Artinya data yang dicari terletak di sebelah kiri kolom acuan.

Jadi.. rumus apa yang dapat digunakan untuk mencari nama karyawan pada kolom yang terletak di sebelah kiri kolom berisi nomor ID ?

Mari kita bahas satu persatu...

Cari Data Ke Kiri Menggunakan Rumus VLOOKUP-CHOOSE

Dengan fungsi CHOOSE, ternyata kita bisa mendefinisikan sendiri kolom mana yang akan dianggap sebagai kolom pertama dan ke dua oleh fungsi VLOOKUP. 

Oleh karenanya kita akan menggunakan fungsi CHOOSE untuk menjadikan kolom "ID" sebagai kolom pertama , dan kolom "Nama" sebagai kolom kedua.

Adapun formula pencarian data di sebeah kiri dapat dituliskan sebagai berikut:

=VLOOKUP(H1,CHOOSE({1,2},D:D,B:B),2,0)

Rumus tersebut dapat menghasilkan nama yang dicari seperti ditunjukan dalam gambar  berikut:

Perhatikan kembali fragmen rumus CHOOSE({1,2},D:D,B:B)

• Dalam hal ini fungsi CHOOSE digunakan untuk menjadikan kolom D (kolom berisi data ID) sebagai kolom pertama dan kolom B (kolom berisi data Nama) sebagai kolom kedua. 

• Kemudian setelah digabung dengan rumus VLOOKUP, maka VLOOKUP akan mencari ID di kolom pertama dan memberikan hasil data nama  dari kolom ke-2, pada baris yang sama dengan baris sel berisi ID yang diketahui.

Cari Data Ke Kiri Menggunakan Rumus HLOOKUP-MATCH

Dengan membalik orientasi vertikal menjadi horizontal, sebenarnya  kita bisa melakukan pencarian data di sebelah kiri. 

=HLOOKUP("Nama",A:E,MATCH(H1,D:D,0),0)

• Rumus MATCH(H1,D:D,0) berguna untuk mendapatkan angka baris sebuah sel di kolom D yang berisi nilai ID yang diketahui. Rumus ini menghasilkan angka 3 yaitu nomor baris sel yang berisi nilai ID=13043

• Kemudian rumus HLOOKUP akan mencari posisi kolom "Nama" pada baris pertama  dalam range “A:E”. Selanjutnya memberikan informasi dari baris 3 pada kolom "Nama". Dan hasilnya adalah "Dulah"

Cari Data Ke Kiri Menggunakan Rumus INDEX-MATCH

Rumus ini tampaknya merupakan rumus terpopuler alternative VLOOKUP ke  kiri. Terbukti dengan banyaknya tutorial yang membahas hal ini. Coba saja cari kata kunci 'vlookup ke kiri' di Google. Maka kebanyakan link di halaman pertama google akan mengarah ke halaman web/blog yang menjelaskan INDEX MATCH, bukannya menjelaskan VLOOKUP.

Terkecuali jika halaman yang sedang anda baca ini ini bisa muncul di halaman pertama Google (penuh harap… 😇😇 ),  maka mungkin akan menambah warna sendiri mengenai cara pencarian data di sebelah kiri…. 

Baiklah, selanjutnya rumus INDEX MATCH dalam contoh kasus ini dapat dituliskan sebagai berikut:

=INDEX(B:B,MATCH(H1,D:D,0))

Fungsi INDEX digunakan untuk mendapatkan nilai dari Sel sesuai index baris dan kolom dalam range yang ditentukan. 

Misalnya:

=INDEX(range,x,y)

Maka rumus tersebut akan menghasilkan nilai dari baris ke-x dan kolom ke-y dalam range. nomor kolom bersifat opsional sehingga dapat ditiadakan seperti ini:

=INDEX(range,x)

Rumus tersebut akan menghasilkan nilai dari baris ke-x ,kolom ke-1 dalam range.

Konsep terakhir inilah yang digunakan dalam fungsi INDEX-MATCH untuk mencari data di sebelah kiri. Sebenarnya tidak mesti di sebelah kiri sich, mencari data ke kanan pun boleh.

Kembali ke contoh rumus INDEX MATCH untuk mendapatkan nama karyawan sesuai ID yang diketahui.

=INDEX(B:B,MATCH(H1,D:D,0))

• Fungsi MATCH digunakan untuk mencari posisi baris sel pada kolom D yang berisi nilai ID yang diketahui (sesuai yang diisi pada sel H1). Dan hasilnya adalah angka 3 (nomor baris sel berisi ID=13043)

• Kemudian fungsi INDEX menggunakan angka 3 tersebut untuk mengambil data padanannya pada kolom B

• Hasilnya adalah "Dulah" yaitu data dalam sel yang terletak pada baris 3 kolom B.

Cari Data Ke Kiri Menggunakan Rumus OFFSET  MATCH

Cara kerja rumus ini adalah dengan menggunakan posisi relative baris data yang dicari dari sel acuan dalam fungsi OFFSET. Perhatikan gambar berikut:

• Fungsi MATCH digunakan untuk mendapatkan angka baris sel yang berisi nilai ID yang diketahui pada range D:D. Hasil dari rumus MATCH ini dikurangi angka 1 untuk menyesuaikan angka posisi relative dari baris pertama (sel B1). Fungsi MATCH dalam contoh menghasilkan bilangan 3, kemudian 3 dikurangi 1 menjadi 2. 

• Hasilnya kemudian digunakan oleh fungsi OFFSET untuk mendapatkan nilai dari sel yang jaraknya 2 baris ke arah bawah dari dari sel B1.

• Dan hasilnya adalah "Dulah" yaitu data dalam sel yang jaraknya 2 baris ke arah bawah dari sel B1.

Cari Data Ke Kiri Menggunakan Rumus INDIRECT - ADDRESS - MATCH - COLUMN

Nah, kalau rumus ini bekerja dengan cara menentukan alamat sel yang berisi data yang dicari, kemudian mendapatkan nilainya.

• Rumus MATCH(H1,D:D,0) menghasilkan angka 3 yaitu posisi baris sel yang berisi ID 13043
• Rumus COLUMN(B:B) menghasilkan angka 2  yaitu nomur urut kolom B dalam lembar kerja excel
• Fungsi ADDRESS digunakan untuk mendapatkan alamat sel sesuai index baris dan kolom yang diperoleh dari rumus MATCH dan COLUMN.  Sehingga jika diringkas menjadi  =ADDRESS(3,2) , rumus ini menghasilkan nilai $B$3
• Fungsi INDIRECT digunakan untuk mendapatkan nilai dalam alamat sel yang diketahui. Dan nilai yang terdapat dalam sel $B$3 adalah “Dulah”

Ringkasan:

Ada banyak cara untuk menyelesaikan tugas Excel. Termasuk untuk melakukan pencarian data di excel. Dalam kasus VLOOKUP ke kiri, kita bisa menggunakan beberapa alternative seperti 5 contoh rumus yang sudah dijelaskan yaitu: kombinasi VLOOKUP – CHOOSE, kombinasi HLOOKUP-MATCH, kombinasi INDEX-MATCH, kombinasi OFFSET-MATCH dan kombinasi INDIRECT-ADDRESS-MATCH-COLUMN.

Demikian semoga bermanfaat.

Salam..

Baca juga tutorial Belajar Excel Lainnya:

• Lookup Gambar Dengan Fungsi INDEX MATCH
• VLOOKUP - Rumus Excel Yang Perlu Dikuasai
• Memahami Rumus VLOOKUP Dengan Studi Kasus
• Fungsi MATCH - Kegunaan dan Contoh Rumusnya
• Penggunaan Fungsi OFFSET untuk Transpose Data

Fungsi DATEDIF Untuk Menghitung Masa Kerja Karyawan

Dalam pekerjaan sehari-hari di kantor, barangkali kita pernah mengerjakan tugas untuk menghitung berapa lama seorang karyawan sudah bekerja di perusahaan. Beberapa teman masih menghitungnya satu persatu secara manual atau menggunakan kalkulator. Atau ada juga yang menghitung menggunakan rumus excel tetapi hanya menghitung selisih tahunnya saja, antara tahun masuk sampai dengan tanggal saat ini. Padahal dengan formula excel yang tepat, kita bisa menghitung masa kerja seseorang dengan lebih spesifik dan detail. Salah satu cara yang dapat dilakukan adalah dengan memanfaatkan fungsi DATEDIF.

Penerapan Fungsi DATEDIF dalam Rumus Untuk Menghitung Masa Kerja Karyawan

Fungsi DATEDIF  merupakan salah satu fungsi tersembunyi di excel versi saat ini.

Kenapa dikatakan tersembunyi? karena kita tidak pernah bisa menemukan fungsi ini dalam list fungsi excel. Bahkan fungsi ini pun tidak dapat ditemukan penjelasannya pada halaman bantuan (help) aplikasi excel. Tetapi anda dapat menemukan penjelasan mengenai fungsi DATEDIF ini di support.office.com.

Fungsi DATEDIF, dapat dituliskan dengan syntax:

DATEDIF(start_date,end_date,unit)

• start_date : adalah tanggal pertama atau tanggal mulai untuk dilakukan perhitungan
• end_date   : adalah tanggal kedua atau tanggal terakhir

Perhatian! : Dalam penulisan tanggal harus dilakukan secara benar. Mengenai cara penulisan dan pengumpulan data tanggal sudah saya jelaskan pada artikel sebelumnya perihal : Solusi Masalah Penulisan Tanggal di Excel.

• unit      : adalah satuan perhitungan untuk mengukur selisih antara start_date dan end_date.

Unit	Kegunaan
"Y"	Mendapatkan jumlah  tahun penuh dalam periode perhitungan
"M"	Mendapatkan jumlah bulan penuh dalam periode perhitungan
"D"	Mendapatkan jumlah hari dalam periode perhitungan
"MD"	Selisih jumlah hari antara hari dalam start_date dan hari dalam end_date (mengabaikan bulan dan tahun) Penting: Office support tidak menganjurkan penggunaan argumen "MD" dikarenakan suatu masalah dan keterbatasan.
"YM"	Mendapatkan selisih bulan antara start_date dan end_date (mengabaikan jumlah tahun dan hari)
"YD"	Mendapatkan selisih jumlah hari antara start_date dan end_date (mengabaikan tahun)

Baiklah mari kita praktekan dengan contoh.

Secara garis besar, rumus untuk menghitung masa kerja karyawan dapat ditulis sebagai berikut:

Masa_kerja =DATEDIF(tgl_masuk,tgl_sekarang,unit)

Untuk mendapatkan tanggal sekarang, kita bisa menggunakan fungsi TODAY. Artikel ini ditulis pada tanggal 9 Januari 2017, sehingga rumus TODAY() menghasilkan angka tanggal 9 Januari 2017

Anggaplah data tanggal mulai masuk kerja karyawan disimpan di sel B2

Maka formula excel untuk mendapatkan masa kerja dapat dituliskan sesuai ilustrasi berikut:

Perhatikan gambar diatas : Dengan menggunakan argumen unit yang berbeda-beda, maka kita bisa mendapatkan beberapa variasi data. 

Nah tugas kita sekarang adalah memilih data yang mana yang dibutuhkan.

Menghitung Berapa Tahun dan Berapa Bulan Masa Kerja Karyawan

Umumnya masa kerja dihitung dengan jumlah tahun dan bulan sejak karyawan masuk kerja.

Untuk mendapatkan angka tersebut, maka kita bisa menggunakan argumen “Y” dan “YM” secara terpisah.

"Y"  = untuk mendapatkan jumlah tahun penuh yang dilewati sejak masuk kerja

"YM" = untuk mendapatkan jumlah bulan penuh yang dilewati yang merupakan sisa dari tahun penuh yang sudah terlewati

Melanjutkan contoh sebelumnya, misalnya kita ingin mendapatkan data  x tahun dan y bulan.

x =DATEDIF(B2,TODAY(),"Y")

y =DATEDIF(B2,TODAY(),"YM")

Dan untuk mendapatkan text yang informatif, maka kita bisa menggabungkan data tahun dan bulan menggunakan rumus penggabung kata (bisa menggunakan CONCATENATE maupun operator & (ampersand).

Sehingga rumus dapat dituliskan sebagai berikut:

=DATEDIF(B2,TODAY(),"Y")&" tahun, "&DATEDIF(B2,TODAY(),"YM")&" bulan"

Atau

=CONCATENATE(DATEDIF(B2,TODAY(),"Y")," tahun, ",DATEDIF(B2,TODAY(),"YM")," bulan")

Sebagai gambaran, selanjutnya perhatikan screenshot contoh berikut:

Sampai pada tahap ini, kita sudah berhasil menghitung masa kerja karyawan berupa berapa tahun dan berapa bulan sejak karyawan tersebut masuk kerja sampai dengan pada saat ini.

Ringkasan:

Cara menghitung masa kerja karyawan menggunakan Ms Excel dapat dilakukan dengan menerapkan fungsi DATEDIF. Fungsi ini menghasilkan variasi unit waktu yang dapat digunakan sesuai kebutuhan. Rumus penggabung kata dapat digunakan untuk mendapatkan data text masa kerja yang lebih informatif dengan cara menggabungkan data jumlah waktu yang dihasilkan oleh formula DATEDIF dengan text “tahun” dan “bulan”

Sekian, semoga bermanfaat.

Belajar Excel..Excellent.!

Baca juga, tutorial belajar excel lainnya:

• Cara Menggabungkan Kata di Excel
• Solusi Masalah Penulisan Tanggal di Excel
• Cara Menampilkan Nama Hari dan Bulan dalam Bahasa  Indonesia
• 3 Cara Cepat Menghapus Baris Kosong di Excel

Cara Memisahkan Kata Per Huruf

Pembaca je-xcel yang budiman, barangkali kita pernah mengisi form-form berupa kotak isian text nama, alamat, nomor telepon dan sebagainya. Masing-masing isian dipisahkan per huruf, bukan per kata. Biasanya hal ini dijumpai dalam form-form tertentu semisal form registrasi rekening di bank, ataupun form-form lainnya.

Tahukan teman?... ternyata kita juga dapat mengerjakan hal itu di excel, tapi bukan dengan mengetik satu huruf per satu kotak/sel. Namun kita bisa merumuskannnya sehingga cukup hanya mengetikan kata-kata atau text lengkap (misal nama atau alamat) dalam satu sel saja. Tugas selanjutnya serahkan saja kepada rumus excel yang akan memisahkan text atau kata-kata tersebut secara otomatis menjadi per  huruf dalam kotak atau sel yang berbeda.

Sebelum membaca lebih lanjut, boleh dong dicek dulu videonya ..😊😊

Bagaimana cara memisahkan kata menjadi per huruf?

Cara memisahkan text menjadi per huruf cukup mudah, yaitu dengan memanfaatkan fungsi MID. Fungsi ini bekerja dengan cara mengambil satu atau beberapa huruf atau karakter sesuai nomor urut karakter tersebut dalam text.

Fungsi MID dapat dituliskan sebagai berikut:

MID(text,start_num,num_char)

Dimana:

• text =  text atau string utama yang akan kita ambil bagian karakter nya
• start_num =  nomor urut karakter pertama dari string yang akan diambil dari text utama
• num_char =  banyaknya huruf atau karakter yang akan diambil dari text utama, dimulai dari start_num.

Contoh Rumus:

=MID("BANDUNG",4,2)

Rumus tersebut akan mengahasilkan text  "DU",  yaitu 2 karakter dimulai dari karakter ke 4

Mari kita coba selanjutnya... Bagaimana jika kita hanya mengambil satu huruf saja per nomor urut karakter dalam text.

=MID("BANDUNG",1,1) menghasilkan  huruf  "B"   (karakter ke 1)
=MID("BANDUNG",2,1) menghasilkan  huruf  "A"   (karakter ke-2)
=MID("BANDUNG",3,1) menghasilkan  huruf  "N"   (karakter ke-3)
=MID("BANDUNG",4,1) menghasilkan  huruf  "D"   (karakter ke-4)
=MID("BANDUNG",5,1) menghasilkan  huruf  "U"   (karakter ke-5)
=MID("BANDUNG",6,1) menghasilkan  huruf  "N"   (karakter ke-6)
=MID("BANDUNG",7,1) menghasilkan  huruf  "G"   (karakter ke-7)

Nah, Konsep tersebut kemudian dapat kita manfaatkan untuk memisahkan kata-kata menjadi huruf atau karakter-karakter tunggal.

Contoh Rumus Untuk Memisahkan Kata Per Huruf

Ada beberapa metode rumus yang dapat digunakan untuk memisahkan text kata-kata menjadi per huruf atau per karakter. Namun entah apapun metode yang digunakan, cara kerjanya hampir sama yaitu mendapatkan angka nomor urut karakter, dan menjadikannya sebagai argumen start_num dalam fungsi MID.

Contoh 1: Memisahkan Kata Menjadi Per huruf Menggunakan Fungsi MID dan Baris  Bantu

Cara Kerja Rumus:

Perhatikan contoh rumus pada gambar diatas.

• Baris bantu pada range (C2:V2) digunakan berisi nomor urut 1 s.d 20 digunakan untuk mendefinisikan argumen start_num dalam fungsi MID. Dengan kata lain berguna untuk mendefinisikan nomor urut huruf dalam text yang akan dimunculkan dalam kolom tersebut.
• Perhatikan rumus pada sel C4 yaitu =MID($B4,C$2,1)  (sesuai tanda panah)
• $B4 adalah text utama, berupa data nama yang akan kita ambil per hurufnya. Tanda dolar ($) dimaksudkan supaya kalau rumus tersebut dicopy ke samping, maka referensi kolomnya akan tetap mengikat ke kolom B yang berisi list Nama
• C$2 berperan sebagai argumen star_num yaitu nomor urut karakter dalam text yang akan diambil.
• Angka 1, menunjukan hanya satu hurus saja yang akan diambil dimulai dari nomur urut (star_num)
• Copy rumus tersebut ke semua kotak di sebelahnya.
• Anda dapat menambahkan baris baru untuk  item list nama yang lainnya, dan copy rumus tadi.
• Selesai...

Contoh 2: Memisahkan kata per huruf menggunakan rumus kombinasi fungsi MID dan COLUMNS

Cara Kerja Rumus:

• Perhatikan rumus pada sel C4 yaitu =MID($B4,COLUMNS($B:B),1)  (sesuai tanda panah)
• $B4 mengikat pada text nama yang akan dipisahkan per karakter, tanda dolar hanya sebelum kolom menandakan referensi sel semi absolute supaya jika rumus dicopy ke sel lain maka referensi kolom tidak berubah relative, tetapi baris bisa berubah.
• COLUMNS($B:B) berguna untuk mendapatkan jumlah kolom pada range $B:B,  rumus tersebut akan menghasilkan angka 1 karena range B:B hanya memiliki 1 kolom. Tanda $ hanya pada salah satu label huruf B dimaksudkan supaya jika dicopy ke samping maka kolom pertama akan terkunci, tetapi kolom akhir tidak terkunci, sehingga jika rumus dicopy ke sel sebelahnya, alamat range akan berubah menjadi $B:C , $B:D, $B:E dan seterusnya dan memberikan angka 2, 3, 4 dan seterusnya
• Angka yang dihasilkan fungsi COLUMNS digunakan oleh fungsi MID sebagai argumen start_num untuk mendapatkan satu huruf atau satu karakter dari text utama.
• Silahkan rumus di sel C4 di copy paste ke sel lainnya.
• Kelemahan dari metode ini adalah hasil akan menjadi tidak relevan jika ada insert column.

Contoh 3: Memisahkan Kata per Huruf Menggunakan Rumus Array CSE

Cara Kerja Rumus:

• Perhatikan rumus pada range C4:V4 (sesuai tanda panah)
• Rumus tersebut dibuat dengan cara menyeleksi range C4:V4, kemudian mengetikan rumus =MID(B4,COLUMN(1:1),1), dan setelah itu tekan CTR+SHIFT+ENTER.
• Perhatikan kembali komponen rumus {=MID(B4,COLUMN(1:1),1)}
• Tanda kurung kurawal {} menandakan rumus array dimana tanda tersebut muncul secara otomatis ketika kita menekan CTR+SHIFT+ENTER setelah mengetik/mengedit rumus.  
• B4 berperan sebagai referensi berisi text utama yang akan diambil per hurufnya
• COLUMN(1:1) merupakan kunci untuk mendapatkan nomor urut karakter, dimana jika fungsi tersebut dimasukan dalam fungsi array maka akan menghasilkan angka 1 s.d 16384 di excel 2007 s.d 2016, sedangkan di excel 2003 akan menghasilkan angka 1 s.d 256 sesuai jumlah kolom yang diakomodir oleh masing-masing versi excel tersebut.
• Sebenarnya tidak masalah apakah kita menggunakan (COLUMN(1:1), COLUMN(1:2), COLUMN(3:10), dan sebagainya. Hasilnya akan sama dalam kasus penyelesaian masalah yang menjadi topik posting blog saat yang sedang anda baca ini.
• Elemen array yang dihasilkan rumus COLUMN(1:1) kemudian dijadikan sebagai parameter start_num dalam rumus MID.  Dan tentunya hanya beberapa elemen array saja yang digunakan sesuai jumlah karakter dalam kata atau text nama yang diproses.
• Untuk mengcopy rumus array tidak bisa satu sel per sel, tetapi harus dengan cara menyeleksi semua sel dalam lingkup array, dalam contoh yaitu range  C4:V4, kemudian paste pada sel C5, C6 dan seterusnya sesuai baris item list nama yang tersedia.

Ringkasan.

Untuk memisahkan kata-kata atau text menjadi per huruf dapat dilakukan menggunakan fungsi MID. Ada beberapa alternative metode yang dapat digunakan, diantaranya: Memisahkan text menjadi perhuruf menggunakan fungsi MID + baris bantu, menggunakan kombinasi fungsi MID dan COLUMNS, serta menggunakan rumus Array fungsi MID dan COLUMN.

Sekian, semoga bermanfaat.

Belajar Excel.! Excellent!

Artikel terkait:

• Cara Meggabungkan Kata di Excel
• Cara Menghilangkan Spasi Berlebih
• Belajar Rumus Array Untuk Pemula
• Fungsi Pengolah Text di Excel