VLOOKUP Excel- Rumus Jitu Yang Wajib Dikuasai

Diakui atau tidak, masih banyak pengguna excel yang belum memahami atau bahkan belum tau apa itu rumus VLOOKUP.

 Padahal fungsi excel yang satu ini sangat penting dan sangat berguna terutama jika kita bekerja dengan banyak data yang terdiri atas bayak baris.

Untuk pembaca yang sudah pakar, hal ini pasti tidak asing. Mohon dikoreksi saja jika ada yang salah dalam artikel tutorial VLOOKUP Excell ini.

Sebelum melanjutkan pembahasan mengenai fungsi VLOOKUP secara lebih terperinci. Alangkah baiknya anda untuk menyimak video sederhana ini. Video ini menunjukan mengenai bagaimana dasar-dasar cara menggunakan fungsi VLOOKUP. Simak baik-baik ya...

Nah, dari Video diatas, teman-teman bisa mendapatkan gambaran bagaimana cara menggunakan fungsi VLOOKUP, dan apa kegunaannya.

Selanjutnya kira-kira apa yang akan kita bahas perihal rumus VLOOKUP secara lebih terperinci?



• Apa itu Rumus VLOOKUP
• Rumus VLOOKUP antar Sheet
• Rumus VLOOKUP antar Workbook atau File Excel Yang Berbeda
• Referensi Nama Range Cell dalam Rumus VLOOKUP
• Rumus VLOOKUP untuk Klasifikasi Grade Nilai
• Bisakah Rumus VLOOKUP Bekerja dari Kanan Ke Kiri?

......


Berikut ilustrasi contoh kasus dimana fungsi VLOOKUP diperlukan.

Misalnya: ada data nama peserta sebuah kegiatan dan disimpan dalam sebuah tabel terdiri atas 4 kolom yaitu Nomor, Nama, Jenis Kelamin dan Alamat.

Kemudian seseorang meminta anda untuk menemukan data yang terkait dengan nomor urut tertentu.

Bagaimana caranya supaya untuk memperoleh data dimaksud, secara cepat menggunakan rumus.

Jawabnya : ya pakelah rumus VLOOKUP, bukan diPELUK yach ...:-)

Apa Rumus VLOOKUP itu ?

VLOOKUP merupakan akronim dari Vertikal Lookup, atau secara harfiah lookup dapat diartikan sebagai melihat dengan seksama. Jadi fungsi VLOOKUP digunakan untuk melihat dengan seksama tabel secara vertikal (dari atas ke-bawah). Adapun kegunaannya adalah untuk mendapatkan nilai dari kolom lain yang terkait dengan data yang sudah diketahui dalam baris yang sama.

Berikut syntax atau cara penulisan fungsi VLOOKUP dalam lembar kerja m s excel

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Ah sok inggris, baiknya kita coba indonesiakan saja..., mungkin seperti ini jadinya:

=VLOOKUP(nilai_dilihat,tabel_dilihat,nomor_kolom,[lingkup_lihat]

Ups.. agak beratakan terjemahan bahasa indonesia nya.  Tapi tak apalah, yang penting saya dan anda dapat dapat memahaminya.

• Nilai_dilihat adalah data berupa angka maupun text yang akan dicari
• Tabel_dilihat adalah tabel melihat nilai_dilihat dimana posisinya pada kolom pertama (paling kiri) dari tabel tersebut
• Nomor_kolom adalah nomor urut kolom dalam tabel_dilihat untuk mendapatkan data tertentu yang terkait dengan nilai_dilihat
• Lingkup_lihat adalah cara pencarian apakah menginginkan data yang persis sama atau pendekatan. Parameter ini bersifat opsional tetapi sangat penting. Nilai 0 atau FALSE jika menginginkan hasil yang persis sama atau nilai 1 atau TRUE jika menginginkan data pendekatan. Secara default jika parameter ini diabaikan maka akan menggunakan nilai 1 atau TRUE. Dalam prakteknya mungkin kita akan lebih sering menggunakan 0 atau FALSE. Oleh karena itu parameter ini wajib diisi meskipun bersifat opsional.

Kembali ke contoh kasus:

Bagaimana cara mendapatkan data Nama, Jenis Kelamin dan Alamat dari tabel data peserta dengan cepat menggunakan rumus?

Berikut ilustrasi jawabannya:

Belum Faham ?

Kira-kira beginilah penjelasannya dengan analogi:

Lihat Seksama Dengan Mata Kepala		Lihat Seksama Dengan Rumus VLOOKUP
Kita ingin melihat Nama Peserta nomor 10		Kita ingin melihat Nama Peserta nomor 10 mengunakan bantuan rumus excel VLOOKUP
Lihat dengan seksama dari atas ke bawah pada kolom Nomor sampai dijumpai 10		Rumus mengecek satu persatu dari atas kebawah kolom nomor (paling kiri) tabel A7:D22 sampai dijumpai 10
Setelah nomor 10 dijumpai, Mata kita bergeser ke sebelah kanannya, pada kolom berikutnya yaitu kolom ke-2 yang berisi Nama		Begitu 10 dijumpai, excel mengecek pada kolom urutan ke-2 sesuai rumus  =VLOOKUP(10,A7:D22,2,0)
Berteriaklah : Ya Aku Tahu Kau Nomor 10, Namamu Wati kan?		Excel memberitahu anda: nomor urut 10 kolom ke-2 adalah si Wati
Anda Penasaran Si Wati itu laki-laki atau perempuan. Lihat kolom sebelahnya lagi, kolom ke-3		Modifikasi rumus untuk mendapatkan data jenis kelamin yang terletak pada kolom ke-3 dari tabel =VLOOKUP(10,A7:D22,3,0)
Anda Tersenyum : Oh si Wati itu perempuan toh		Excel memberitahu anda: Nomor urut 10 , kolom ke-3 adalah perempuan
Anda masih penasaran : Si Wati itu orang mana ya? Sambil melihat kolom sebelahnya lagi, kolom ke-4		Modifikasi rumus untuk mendapatkan data Alamat yang terletak pada kolom ke-4 dari tabel =VLOOKUP(10,A7:D22,4,0)
Anda tersenyum lebar sambil berfikir dan membayangkan dalam peta. Cirebon itu dimana ya		Excel memberitahu anda: Nomor urut 10 , kolom ke-4 adalah Cirebon
Selanjutnya: Terserah anda :-)		Selanjutnya : Terserah anda juga.:-)

Karena kita ingin mencari data secara cepat pada berbagai nomor urut, maka angka 10 itu terletak dalam sebuah cell. Misal cell C1,  maka rumusnya menjadi sebagai berikut:

Nama ,  =VLOOKUP(C1,A7:D22,2,0)

Jenis Kelamin,  =VLOOKUP(C1,A7:D22,3,0)

Alamat, =VLOOKUP(C1,A7:D22,4,0)

Cobalah:  ganti angka nomor urut di sel C1, maka kita akan dapati data terkait nomor urut tersebut secara otomatis. 

Sampai disini seharusnya cara kerja rumus VLOOKUP sudah dapat difahami.

Rumus VLOOKUP Beda Sheet

Pada contoh diatas, kita menggunakan rumus vlookup  untuk melihat seksama nilai tertentu pada tabel yang berada pada sheet yang sama dengan rumus dibuat.

Bagaimana jika tabel terletak pada sheet yang beda, misalnya pada sheet dengan nama “Data”, pada range A7:D22

Bagaimana rumus atau formulanya?

Tidak Sulit, cukup ketikan rumus dengan referensi tabel_dilihat pada sheet data

• Nama =VLOOKUP(C1,DATA!A7:D22,2,0)

• Jenis Kelamin =VLOOKUP(C1,DATA!A7:D22,2,0)

• Alamat   =VLOOKUP(C1,DATA!A7:D22,2,0)

Perhatikan formula di atas, untuk parameter nilai_dilihat dan tabel_dilihat mengacu pada  referensi. Sebenarnya tidak perlu mengetikan langsung, cukup pilih referensi yang sesuai menggunakan mouse.

Contoh cara mengetik rumus VLOOKUP antar sheet.

• Ketik =VLOOKUP(  

• Pilih referensi nilai_dilihat menggunakan mouse yaitu sel C1

• Ketik koma (,) 

• Masuk atau Pilih sheet “DATA” 

• Pilih tabel pada range A7:D22  di dalam sheet “DATA”

• Ketik koma (,) 

• ketik parameter nomor_kolom , misal 2 (kolom ke-2)

• Ketik koma (,) 

• Ketik parameter lingkup_lihat, misalnya angka 0 atau False

• Ketik tutup kurung

Oh iya ... Saya lupa,  anda sebenarnya sudah paham untuk hal remeh cara mengetik rumus seperti ini. Tapi tak apalah, biar artikel ini agak panjang sikit. :-)

Rumus VLOOKUP antar Workbook Atau File Excel Yang Berbeda

Bagaimana jika tabel data terletak di workbook atau file excel lainnya? Bagaimana rumus atau formula-nya?

Untuk yang ini juga tidak terlalu sulit. Yakin ! apalagi kalau sudah terbiasa dengan excel dan sering menggunakan formula. Hampir sama dengan pembuatan rumus VLOOKUP antar sheet.

Cukup ketikan =VLOOKUP( , kemudian pilih referensi nya menggunakan mouse, untuk parameter nilai_dilihat dan tabel_lihat.

Misalnya tabel peserta terletak dalam sebuah file excel bernama  peserta.xlsx.  Maka rumus vlookup menjadi:

• Nama  =VLOOKUP(C1,[peserta.xlsx]DATA!$A$7:$D$22,2,0)

• Jenis Kelamin =VLOOKUP(C1,[peserta.xlsx]DATA!$A$7:$D$22,3,0)

• Alamat =VLOOKUP(C1,[peserta.xlsx]DATA!$A$7:$D$22,4,0)

Perhatikan penggunaan tanda $. Biasanya jika kita mengetikan formula dengan referensi dari workbook atau file lain maka otomatis referensi  menggunakan atribut $ atau absolute.  Artinya range referensi dikunci dan akan tetap meskipun rumus di copy ke sel lain.

Referensi Nama Range Cell dalam Rumus VLOOKUP

Cara lain yang lebih simple dan lebih mudah dibaca (readable) ialah penggunakan nama range sebagai referensi. Tata cara penamaan range cell sudah dibahas dalam artikel lainnya, silahkan dibaca Memberikan Nama Cell dan Range Menggunakan Name Box

Atau kalau anda tidak mau repot membuka artikel lain, ikuti saja langkah berikut:

Membuat nama range cell adalah dengan cara menyeleksi range cell (misal tabel) kemudian ketikan nama range pada name box yang biasanya terletak pada kiri atas lembar kerja excel (disebelah kiri tanda fx). Misalnya nama range tabel data peserta kita namakan dataPeserta. 

Lalu apa kaitan nama range sel dengan rumus vlookup?

Menggunakan nama sel atau nama range dalam rumus vlookup menjadikan rumus lebih readable, atau lebih mudah dibaca dan dimengerti alurnya.

Penggunaan nama range cell juga menjadikan referensi yang sama. Tidak peduli lokasi tabel baik dalam sheet yang sama atau tidak, tetapi masih dalam satu file.

Misalnya: 

Dengan menamai tabel data peserta dengan nama dataPeserta, maka rumus vlookup dalam satu sheet maupun antar sheet menjadi sama yaitu:

Nama  =VLOOKUP(C1,dataPeserta,2,0)

Jenis kelamin  =VLOOKUP(C1,dataPeserta,3,0)

Alamat  =VLOOKUP(C1,dataPeserta,4,0)

Secara analogi, dapat dijelaskan bahwa rumus VLOOKUP diatas adalah mencari data sesuai nilai pada C1. Pencarian dilakukan pada kolom pertama dari tabel bernama dataPeserta. Selanjutnya mengembalikan nilai dari kolom ke 2 (nama), 3 (Jenis Kelamin) dan 4 (Alamat) pada baris yang sama dengan nilai ditemukan.

Rumus VLOOKUP untuk Klasifikasi Grade Nilai

Kebanyakan pengguna excel sudah familiar dengan cara melakukan klasifikasi data menggunakan fungsi IF. Namun kalau kita membuat rumus IF bertingkat, kita akan memerlukan rumus IF yang semakin panjang dengan semakin banyaknya kriteria yang digunakan.

Semakin panjang rumus, tentunya semakin sulit untuk mengikuti alur logikanya. Dalam kata lain, penggunaan rumus IF dengan kriteria yang banyak menjadikan rumus tidak readable.

Contoh Kasus:

Dalam mengklasifikasikan grade nilai ujian mahasiswa di sebuah perguruan tinggi, ditentukan kriteria sebagai berikut:

Nilai	Grade
< 20	E
20-29	E+
30-39	D
40-49	D+
50-59	C
60-69	C+
70-79	B
80-89	B+
90-100	A

Bagaimana cara merumuskan grade nilai ujian mahasiswa tersebut?

Bagi anda yang maniak dengan fungsi IF mungkin akan langsung mengatakan: Wah ini harus menggunakan rumus IF nich...!

Perhatikan bahwa untuk klasifikasi nilai menjadi 9 grade (E , E+ , D , D+ , C , C+ , B , B+ , A ), kita memerlukan fungsi IF bertingkat 8.

=IF(G4<20,"E",IF(G4<30,"E+",IF(G4<40,"D",IF(G4<50,"D+",IF(G4<60,"C",IF(G4<70,"C+",IF(G4<80,"B",IF(G4<90,"B+","A"))))))))

Rumus di atas tidak dapat digunakan pada excel 2003 karena excel versi tersebut hanya mengizinkan fungsi IF bertingkat sampai level 7.

Bahkan, meskipun anda sudah menggunakan versi excel 2007, 2010, 2013 atau 2016, penggunaan rumus tersebut tetap saja menurut saya kurang praktis.

Kenapa?

Karena rumus terlalu panjang. Rumus ini juga kurang fleksibel, misalnya jika ada perubahan dari kriteria grade, maka kita harus melakukan edit kembali pada rumus IF.

Apa Solusinya?

Gunakan rumus VLOOKUP

Dalam hal ini menggunakan VLOOKUP TRUE, artinya nilai parameter range_lookup atau lingkup_lihat harus bernilai TRUE atau 1.

Caranya:

• Buat tabel bantu nilai dan grade, 
• Kolom pertama berisi nilai minimum masing-masing grade dan harus berurut dari terkecil ke tertinggi.
• Kolom kedua berisi grade
• Perhatikan range berwarna kuning (C3:D11) dalam ilustrasi berikut adalah referensi kriteria grade nilai mahasiswa yang harus dimasukan kedalam rumus

• Perhatikan sel D15 pada ilustrasi.  Rumus pada sel tersebut adalah:

=VLOOKUP(C15,$C$3:$D$11,2)

Atau

=VLOOKUP(C15,$C$3:$D$11,2,1)

Atau

=VLOOKUP(C15,$C$3:$D$11,2,TRUE)

Terbukti..! Rumus VLOOKUP sangat jauh lebih pendek dibandingkan menggunakan rumus IF.  

Kita juga dapat memberi nama pada referensi tabel kriteria nilai grade mahasiswa sehingga rumus menjadi lebih readable. Misalnya tabel kriteria kita beri nama kriteriaGrade, maka rumus tersebut menjadi:

=VLOOKUP(C15,kriteriaGrade,2)

atau

=VLOOKUP(C15,kriteriaGrade,2,1)

atau

=VLOOKUP(C15,kriteriaGrade,2,TRUE)

Anda perhatikan bahwa rumus VLOOKUP ini sangat simple. Rumus inipun tidak akan bertambah panjang dengan bertambahnya kriteria yang digunakan. Hal ini karena kriteria terletak dalam tabel lain yang dijadikan referensi tabel_dilihat. Hal ini sangat berbeda dengan rumus IF yang akan bertambah panjang dengan bertambahnya kriteria.

Namun pada akhirnya terserah anda untuk menggunakan rumus yang mana. Jika lebih nyaman dengan rumus IF, silahkan saja dengan resiko harus menulis formula yang panjang tentunya.

Kalau saya sich, lebih suka yang simple dan fleksible. Maka saya memilih VLOOKUP.

Bisakah Rumus VLOOKUP bekerja dari Kanan Ke Kiri.

Pertanyaan ini sering muncul diantara pengguna excel dan acapkali dilontarkan dalam forum-forum diskusi. Para pakar excel dalam grup biasanya langsung menyarankan untuk menggunakan fungsi INDEX MATCH sebagai alternative pengganti VLOOKUP.

Rumus INDEX MATCH memang banyak memiliki keunggulan dibanding VLOOKUP, Namun, karena kita sedang membahas VLOOKUP, maka solusi yang akan ditawarkan adalah tetap menggunakan rumus VLOOKUP.

Lalu bagaimana caranya? karena VLOOKUP hanya dapat bekerja mulai dari kolom pertama (kolom paling kiri) sebuah tabel untuk mencari sebuah nilai.

Tenang...

Ternyata nomor urut kolom dalam sebuah tabel dapat kita manipulasi.

Caranya?  Gunakan fungsi CHOOSE

Contoh kasus:

Dalam data mahasiswa seperti contoh ilustrasi sebelumnya dari kolom pertama sampai kolom ke-4 yaitu NIM, Nama Mahasiswa, Nilai, Grade.

Seandainya kita mengetahui nama mahasiswa namun belum tahu NIM Nya. Bagai mana cara mendapatkan NIM dengan rumus? padahal kolom NIM berada disebelah kiri Kolom Nama.

Disinilah kita harus memerintahkan VLOOKUP supaya dapat bekerja dari kanan ke kiri.

Perhatikan terutama rumus VLOOKUP untuk mendapatkan Nomor Induk Mahasiswa (NIM) yaitu:

=VLOOKUP(B29,CHOOSE({1,2},B15:B25,A15:A25),2,0)

Data yang sudah diketahui yaitu nama mahasiswa terletak kolom kedua pada tabel. Namun dengan menggunakan bantuan fungsi CHOOSE, kita dapat menjadikan kolom tersebut seakan-akan sebagai kolom pertama.

Perhatikan contoh rumus yang dengan font warna biru dan merah. Fungsi CHOOSE mendefinisikan kolom ke-1 adalah range B15:B25 yang berisi nama mahasiswa. Sedangkan kolom ke-2 adalah range A15:A25 yang berisi data NIM 

Penting dalam penggunaan fungsi CHOOSE

Perhatikan angka dalam tanda kurung kurawal {} yaitu 1 dan 2 yang dipisahkan tanda koma ( , ). Jika seting komputer anda menggunakan tanda koma sebagai pemisah desimal, tentunya rumus tersebut akan menghasilkan error. 

Pada kasus demikian maka tanda koma dalam kurung kurawal harus diganti tanda back slash ( \ ) dan koma pemisah parameter rumus diganti dengan tanda semi colon ( ; )

Sehingga rumusnya menjadi sebagai berikut:

=VLOOKUP(B29;CHOOSE({1\2};B15:B25;A15:A25);2;0)

Sampai pada tahap ini dapat disimpulkan bahwa dalam fungsi VLOOKUP excel dapat bekerja dari kanan ke kiri. Tentunya dengan bantuan fungsi CHOOSE.

...................

Sebagai penutup artikel ini. Perlu ditekankan kembali bahwa VLOOKUP merupakan salah satu fungsi Excel yang sangat disarankan untuk dikuasai. Penulis sendiri sangat merasakan manfaat dari rumus ini terutama pada saat bekerja dengan banyak data, apakah itu untuk pengelompokan data, mencari padanan atau data terkait, mencari nama lain dari sebuah data dan sebagainya.

"Penggunaan Rumus VLOOKUP Terbukti Dapat Menghemat Waktu"

Untuk itu, jika pembaca baru mengenal rumus ini, penulis sangat menyarankan untuk mempelajarainya lebih lanjut.

Demikian, semoga bermanfaat
Salam

Artikel terkait




Cara Ampuh Lookup Gambar Dengan fungsi INDEX MATCH
Lebih Lanjut Memahami Rumus VLOOKUP Dengan Studi Kasus
Rumus Excel Tidak Berfungsi ? Inilah Penyebabnya

Referensi:
https://support.office.com/en-US/article/VLOOKUP-function-0BBC8083-26FE-4963-8AB8-93A18AD188A1

Belajar Rumus Array Untuk Pemula

Formula Array merupakan salah satu fitur penting dalam microsoft excel. Kemampuannya tidak diragukan lagi sangat powerful untuk meningkatkan efisiensi pengolahan data excel.

Namun sangat disayangkan sebagian besar pengguna excel belum memanfaatkan fasilitas ini. Menurut informasi, hanya sekitar 10 % saja pengguna Excel yang menerapkan rumus array dalam pekerjaannya.

Dalam kesempatan ini kita akan mempelajari prinsip dasar fungsi dan formula aray dan beberapa contoh penerapannya.



Secara garis besar, belajar excel kali ini akan membahas:

• Pengertian Fungsi dan Formula Array
• Rumus Array Untuk Mendapatkan Hasil Kalkulasi Ganda/Banyak
• Rumus Array Untuk Mendapatkan Hasil Kalkulasi Tunggal

Pengertian Fungsi dan Formula Array

Sebelum melangkah lebih jauh, alangkah baiknya kita memahami terlebih dahulu apakah yang dimaksud dengan istilah Array?

Array dapat diartikan sebagai kumpulan item data. Di dalam excel, kumpulan data dapat disusun dalam kolom, baris maupun kombinasi keduanya.

Lantas apa yang dimaksud dengan Fungsi dan Formula Aray?

Fungsi dan Formula array merupakan Fungsi dan Rumus excel yang bekerja pada kumpulan item data, alih-alih pada single data.

Sebuah formula array dapat menghasilkan data ganda maupun data tunggal.

Jika kita menggunakan formula biasa, kita cukup menekan Enter atau Ctr + Enter setelah mengetikan formula, maka untuk formula array kita harus menekan Ctr + Shift + Enter, oleh karena itu rumus array sering juga disebut formula CSE.

Kita akan mengenali sebuah formula array dengan ditandai kurung kurawal {} (curly braces). Untuk lebih jelasnya perhatikan ilustrasi berikut:

• Dalam ilustrasi diatas, kita memiliki data pupuk dan dosisnya per Hektar pada range A1:B6
• Seleksi range D1:E6 dan ketikan formula berikut =A1:B6   atau lebih mudahnya ketikan tanda samadengan (=) kemudian sorot range A1:B6
• Tekan Ctr + Shift + Enter

Perhatikan formula masing-masing cell pada range D1:E6. Apa yang dapat kita amati dari data tersebut? Ya kita mendapatkan rumus yang sama persis pada masing-masing cell di dalam range D1:E6, yaitu {=A1:B6}.

Lalu kenapa? meskipun masing-masing cell pada range D1:E6 memiliki formula yang sama persis, tetapi kita dapati hasil/nilainya berbeda pada masing-masing cell?

Jawabannya sudah jelas, yaitu karena kita menggunakan formula array. Rumus array bekerja pada koleksi item data dan dapat menghasilkan data ganda/banyak/multiple dengan hanya satu formula.

Perhatikan tanda kurung kurawal {} pada rumus array. Jangan mengetikan secara langsung tanda tersebut karena tidak akan berguna. Cukup tekan Ctr + Shift + Enter  setiap kali selesai mengetikan rumus array. Jika kita tekan F2 atau double click pada rumus aray,  tanda tersebut akan hilang.

Baiklah untuk menambah pemahaman kita, mari kita lanjutkan tahapan belajar rumus array berikutnya.

• Seleksi range D1:E6 yaitu data dengan formula array yang sudah kita buat pada tahapan sebelumnya.
• Tekan F2, lalu pada formula bar seleksi A1:B6 seperti ilustrasi berikut

• Tekan F9,  lalu tekan Ctr + Shift + Enter

• Perhatikan formula range A1:B6 dimana referensi range sudah berubah menjadi array data yang bersumber dari range tersebut. Tanda koma (,) berfungsi sebagai pemisah (delimiter) kolom, sedangkan titik koma atau semicolon berfungsi sebagai pemisah baris. Pemisah baris atau kolom ini bisa berbeda tergantung setting komputer. Jika komputer anda menggunakan tanda koma sebagai pemisah desimal, maka pemisah baris array biasanya menjadi tanda “\” .

Formula Array Untuk Mendapatkan Hasil Kalkulasi Ganda/Multiple/Banyak

Pada contoh formula array di atas sebenarnya sudah menggambarkan rumus array untuk multiple result, namun tentu saja dengan rumus yang sangat sederhana tanpa ada operasi matematika.

Baiklah mari kita praktekan lagi contoh berikut untuk lebih memahami penggunaan rumus array untuk mendapatkan hasil ganda menggunakan operasi matematika.

• Anggaplah kita memiliki data pada kolom A s.d C yaitu nama pupuk , dosis aplikasi per hektar dan luas hektar aplikasi 
• Selanjutnya kita ingin menghitung Jumlah Kebutuhan Pupuk pada kolom D dimana kebutuhan pupuk dihitung sebagai perkalian antara dosis dengan luas aplikasi.

• Tentu saja tidak ada yang dapat melarang anda untuk mengetikan rumus biasa =B1*C1 pada cell D1 kemudian mengcopy formula tersebut ke range D2:D6
• Namun jika anda ingin memahami formula array, mari lakukan hal yang berbeda. Seleksi range D2:D6 kemudikan ketikan formula berikut = B2:B6*C2:C6 kemudian tekan Ctr + Shift + Enter

Dengan cara tersebut kita mendapatkan formula array {=B2:B6*C2:C6} pada range D2:D6. Masing-masing sel akan memperlihatkan hasil jumlah pupuk yang merupakan hasil perkalian antara dosis dengan luas aplikasi pada row yang sama.

Satu rumus, Hasil beragam. Inilah yang dimaksud rumus array untuk mendapatkan hasil kalkulasi ganda atau multiple result.

Formula Array Untuk Mendapatkan Hasil Kalkulasi Tunggal

Salah satu manfaat dari rumus array adalah dapat menjadi jalan pintas kalkulasi sehingga dapat meminimalkan penggunaan kolom atau data bantuan.





Pada contoh sebelumnya kita sudah menghitung jumlah kebutuhan pupuk yang merupakan hasil perkalian antara dosis dengan luas aplikasi.

Nah sekarang bagaimana caranya untuk menghitung total jumlah kg pupuk tanpa memandang jenisnya?

Jika anda terbiasa dengan data excel, tentu saja akan memandang mudah hal ini. Ya kita tinggal menjumlahkan data pada kolom range (D2:D6) dengan menggunakan fungsi SUM atau menggunakan fasilitas Autosum.

Namun lupakan sejenak cara tersebut jika kita ingin memahami cara kerja rumus array.

Kita sebenarnya bisa mengabaikan penggunaan kolom D  dan dapat melakukan kalkulasi secara langsung dengan formula berikut:

=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6).

Ups, tunggu dulu... tapi bukan itu yang saya maksudkan.

Contoh rumus di atas memang dapat menjadi solusi. Namun bukan solusi yang tepat terutama jika anda ingin bekerja lebih cepat dengan data yang lebih banyak.

Untung contoh tabel yang digunakan hanya terdiri dari 5 baris dan 2 kolom data.

Bayangkan jika kita harus menggunakan rumus tersebut untuk ratusan baris, seberapa panjang formula yang harus dibuat?

Untuk itu, Rumus Array merupakah salah satu solusi yang tepat. Ikuti langkah-langkah berikut:

• Menggunakan data seperti contoh pada sebelumnya, kita akan mendapatkan total Kg pupuk dan ditempatkan pada cell C8
• Pada cell C8 ketikan rumus berikut  =SUM(B2:B6*C2:C6) kemudian tekan Ctr + Shift + Enter

Sampai pada tahap ini kita sudah berhasil mendapatkan data total jumlah kg pupuk dengan hanya menggunakan rumus yang pendek dan tanpa menggunakan kolom/data bantu.

Perhatikan penggunaan fungsi SUM dalam formula array. Dalam contoh tersebut, fungsi SUM berguna untuk menjumlahkan hasil perkalian masing-masing baris dalam rumus array.

Dengan kata lain fungsi ini dapat menjadikan formula array untuk mendapatkan hasil kalkulasi tunggal.

Jika anda sudah memahami fungsi SUMPRODUCT, contoh formula array {=SUM(B2:B6*C2:C6)} menghasilkan nilai yang sama dengan formula =SUMPRODUCT(B2:B6,C2:C6)

Apakah sulit untuk mempelajari rumus array?

Ya pada awalnya mungkin kita harus agak mengkerutkan dahi ketika mempelajari rumus array.

Namun dengan seringnya mencoba dan latihan kita akan merasakan bagaimana formula array dapat menjadi solusi terbaik atas beberapa kasus excel yang tidak dapat diselesaikan dengan formula biasa.

Formula array juga dapat dijadikan sebagai jalan pintas formula dimana jika menggunakan rumus biasa memerlukan beberapa formula transisi pada kolom / baris bantu, sedangkan dengan formula array kita cukup menuliskan satu rumus saja.

Penting Diperhatilan Seputar Formula array.

• Kita tidak dapat mengedit dan menghapus satu atau sebagian cell dalam range rumus array. Karena merupakan satu kesatuan, maka edit atau hapus hanya bisa berlaku bagi semua cell dalam lingkup sebuah array.
• Formula array dapat diedit dengan cara menyeleksi semua sel dalam lingkup formula kemudian tekan F2 dilanjutkan dengan modifikasi atau editing formula.
• Setiap kali melakukan edit rumus array, jangan lupa untuk menekan CTR+SHIFT+ENTER. Jika tidak , maka akan terbaca sebagai formula biasa, bukan array.

Demikian penjelasan ringkas rumus array bagian pertama,  Pada beberapa postingan berikutnya, kita akan melanjutkan membahas lebih lanjut formula array yaitu:

• Pengertian Konstanta Array
• Logika AND dan OR dalam Formula Array
• Double Urinary dan fungsi SIGN serta Penggunaanya dalam Formula Array
• Kelemahan dan Kelebihan Formula Array

Demikian semoga bermanfaat, stay tuned terus ya... :-)

Belajar Excel..!  Excellent..!


Baca Juga Tips dan Tutorial Belajar Excel Lainnya:

• Cara Menggunakan Rumus array TRANSPOSE Excel
• Rumus SUMPRODUCT - Satu Fungsi Ragam Guna
• Cara Mudah Memahami Fungsi dan Formula Excel

Transpose Data Menggunakan Fungsi INDIRECT ADRESS

Pada 4 postingan terdahulu, kita sudah belajar mengenai cara transpose data excel dengan berbagai cara yang berbeda. Ya, semakin lama belajar excel memang semakin menantang untuk mencoba cara baru untuk tugas yang sama.

Cara ke-5 untuk  transpose data excel adalah menggunakan gabungan rumus fungsi INDIRECT, ADDRESS, COLUMN dan ROW. Jangan terlalu dipusingkan dengan penggunaan 4 fungsi dalam satu formula. Jika kita memahami konsepnya, hal ini tidak terlalu rumit.

Fungsi INDIRECT berguna untuk mendapatkan nilai secara tidak langsung dari string address, misalnya jika kita mengetikan formula =INDIRECT(“A1”) maka kita akan mendapatkan nilai dari sel A1. Perhatikan “A1” dalam tanda kutip menandakan A1 adalah string, bukan referensi A1, sehingga jika dalam sel B1 berisi text “A1” kemudian kita menuliskan rumus berikut =INDIRECT(B1) maka akan diperoleh nilai dari A1, karena B1 tanpa tanda kutip menandakan referensi, sehingga fungsi INDIRECT akan mencari nilai tidak langsung dari text address yang berada di dalam sel B1.



Fungsi ADDRESS berguna untuk mendapatkan alamat sel dengan mengacu pada nomor row dan nomor column.  Berikut contoh penulisan fungsi Address dan hasilnya:

=ADRESS(1,1)    --->  hasil =  A1   (baris ke-1,  kolom ke-1)
=ADRESS(2,1)    --->  hasil =  A2   (baris ke-2, kolom ke-1)
=ADRESS(1,2)    --->  hasil =  B1   (baris ke-1, kolom ke-2)

Pada saat mengetikan fungsi address, mungkin anda akan melihat hint parameter selain row_num dan column_num, hamun dalam hal ini jangan terlalu dihiraukan karena untuk transpose data hanya diperlukan 2 parameter seperti yang dicontohkan. Prinsip kerjanya hampir sama dengan transpose data menggunakan fungsi INDEX dan OFFSET, yaitu membalik row dan column.

Fungsi COLUMN dan ROW berguna untuk mendapatkan index kolom dan index baris dari referensi terpilih.

Secara sederhana, rumus untuk tranpose data dapat dituliskan sebagai berikut:

=INDIRECT(ADDRESS(COLUMN,ROW))

Selanjutnya mari kita praktekan dalam lembar kerja excel.

• Masih menggunakan data yang sama pada cara ke-1 s.d ke-4, ketikan rumus berikut pada sel D1

=INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

• Lalu copy sel D1 ke range D1:I2 sehingga kita sudah mendapatkan data transpose pada range D1:I2
• Kelemahan dari formula tersebut adalah hanya berlaku bila sel pertama pada range data asli memiliki nomor kolom dan nomor baris yang sama, misalnya A1, B2, C3, D4, E5 dan seterusnya. Coba test dengan melakukan insert colom pada kolom A atau insert Row pada baris 1, maka data transpose akan berubah dan akan kembali seperti semula jika sel pertama data asli berada pada sel A1, B2, C3 dan seterusnya.
• Lakukan modifikasi pada formula diatas sehingga bisa lebih fleksibel dengan mengetikan formula berikut pada sel D1

=INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))

• Lalu copy formula dari sel D1 ke range D1:I2, lakukan test dengan insert kolom pada kolom A dan insert baris pada baris 1. Disini kita akan mendapatkan data transpose yang konsisten.

• Sampai pada tahap ini kita sudah berhasil melakukan transpose data menggunakan INDIRECT, ADDRESS, COLUMN dan ROW. 

Demikian bagian terakhir mengenai Lima cara untuk melakukan transpose atau merubah orientasi baris menjadi kolom dan sebaliknya pada data excel.  Jika pembaca memiliki metode lain silahkan berbagi dengan komentar pada postingan ini.

Semoga bermanfaat.
Belajar Excel..! Excellent..!

Artikel Terkait:





5 Cara Transpose Data Excel
Transpose Menggunakan Fungsi Array TRANSPOSE
Transpose Menggunakan Fungsi Index
Transpose Menggunakan fungsi OFFSET

Penggunaan Rumus OFFSET untuk Transpose Data Excel

Cara ke-4 untuk melakuan transpose / merubah orientasi kolom dan baris data excel adalah menggunakan rumus kombinasi fungsi OFFSET serta ROW dan COLUMN. Fungsi OFFSET ini berguna untuk mendapatkan nilai berdasarkan posisi relative sebuah range terhadap range acuan.

Adapun syntax dari fungsi OFFSET adalah sebagai berikut:

  =OFFSET(reference, rows, cols, [height], [width])

Tulisan ini tidak akan menjelaskan secara detail fungsi OFFSET dan parameter fungsinya, namun hanya akan menjelaskan kaitan fungsi OFFSET dengan proses transpose data. Untuk transpose data, parameter fungsi OFFSET yang diperlukan adalah reference, rows dan cols.





Reference = range acuan, bisa berupa sebuah sel atau beberapa sel. Dalam kasus transpose data, maka range acuan yang diperlukan adalah sebuah sel.
Rows=posisi relatif sel yang dicari nilainya terhadap sel acuan
Cols = posisi relatif sel yang dicari nilainya terhadap sel acuan
Selanjutnya mari kita praktekan kembali pada lembar kerja excel menggunakan tabel data asli seperti cara pertama.

• Menggunakan contoh kasus seperti pembahasan Transpose bagian pertama (Copy Paste Transpose), Bagian Kedua (TRANSPOSE) dan bagian ketiga (INDEX), Pada bagian ke-4 ini menggunakan fungsi OFFSET, Masukan rumus pada sel D1 sebagai berikut: 

=OFFSET($A$1,COLUMN(A1)-COLUMN($A$1),ROW(A1)-ROW($A$1))

• Copy sel D1 tersebut ke range D1:I12 Sehingga mendapatkan data transpose pada range D1:I2.
• Jika anda menginginkan format data transpose identik dengan format data asli, lakukan copy paste special format transpose dari data asli ke range D1:I10 dengan cara sebagai berikut: seleksi range data asli --> CTR+C  --> klik kanan cel D1 --> paste special --> pada dialog box tick formats dan ticks transpose --> Ok

• Dengan memperhatikan formula di atas, prinsip kerja formula OFFSET hampir sama dengan INDEX yaitu membalik parameter kolom dengan baris.

Demikian semoga bermanfaat

Belajar Excel..! Excellent

Artikel Terkait

5 Cara Transpose Data Excel

Penggunaan Formula Array TRANSPOSE

Transpose Data Menggunakan Rumus INDEX