Saturday, January 28, 2017

Rumus Konversi Huruf Kolom Menjadi Angka

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

Formula Koversi Huruf Kolom Menjadi NomorContohnya :

  • 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:


Sunday, January 22, 2017

3 Alternatif Rumus Cari Data Banyak Kriteria

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 juga sebenarnya sudah dibahas mengenai cara pencarian data dengan dua kriteria. Namun dalam contoh tersebut kita harus menggunakan kolom bantu yang berisikan data gabungan (concatenate) dari kriteria yang ditetapkah. Nah… sedangkan dalam kesempatan ini kita akan menggunakan rumus pencari data banyak kriteria tanpa harus menggunakan kolom bantu.

Adapun rumus pencari data yang dapat digunakan adalah sebagai berikut:

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

Ketiga rumus diatas 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:

apa rumus untuk cari data dengan banyak kriteria

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.

Kemudian, 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 alternative rumus untuk pencarian data dengan banyak kriteria.

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))}

Rumus INDEX MATCH cari data


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)}


Rumus OFFSET MATCH cari data banyak kriteria


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)))}

Contoh Rumus INDIRECT ADDRESS Cari data


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

Baca juga artikel Belajar Excel Lainnya:

Sunday, January 15, 2017

5 Alternative Rumus VLOOKUP Ke Kiri

Formula VLOOKUP ke KiriTeman, barangkali kita pernah berkutat dengan tugas pencarian data di excel. Dan Barangkali juga teman sudah ketahui, bahwa cara yang cukup populer untuk pencarian data di excel adalah menggunakan rumus VLOOKUP. 

Akan tetapi, menggunakan fungsi VLOOKUP saja masih belum cukup. 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 sesuai ID yang sudah diketahui seperti yang ditunjukan dalam gambar berikut:

Studi kasus formula vlookup data di kiri


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. 

Jadi.. rumus apa yang dapat digunakan untuk mencari nama karyawan yang sudah diketahui?

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:

VLOOKUP CHOOSE cari data di kiri


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)


HLOOKUP MATCH cari data di kiri


  • 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))


INDEX MATCH cara vlookup ke kiri


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:

OFFSET MATCH cara vlookup ke kiri

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

INDIRECT ADDRESS cari data kiri


  • 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: