FUNGSI LOOKUP DAN REFERENSI

Postingan kali ini saya ingin berbagi tentang fungsi Microsoft Excel yakni Fungsi Lookup dan Referensi. Dalam kelompok fungsi ini terdapat beberapa fungsi lagi diantaranya adalah sebagai berikut :

1. FUNGSI AREAS

Fungsi ini berfungsi untuk menampilkan jumlah area  yang berupa sel, range atau nama range.
Bentuk rumusannya adalah seperti dibawah ini :

=AREAS(reference)

• Reference diisi dengan sel, nama sel, atau range.

Lebih jelasnya lihat gambar dibawah ini :

Pada kasus 1 menghitung jumlah range dan jumlah sel. Sedangkan kasus 2 menghitung jumlah sel yang diberi nama sel.

2. FUNGSI ADDRESS
Anda dapat menggunakan fungsi ADDRESS untuk mendapatkan alamat dari sel dalam worksheet, dengan menentukan baris dan kolom nomor.
Bentuk penulisan rumusnya adalah :

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

• row_num ; diisi nomor baris yang akan dicari
• column_num ; diisi nomor kolom yang akan dicari
• abs_num ; referensi alamat sel yakni sebagai berikut  :

           1. atau diabaikan ; absolut kolom, absolut baris
           2.                         ; relatif kolomm absolut baris
           3.                         ; abslut kolom, relatif baris
           4.                         ; relatif kolom, relatif baris

• sheet_text ; teks untuk menentukan nama worksheet yang akan digunakan sebagai referensi eksternal. Jika argumen sheet_text dihilangkan, tidak ada nama sheet yang digunakan, dan alamat dikembalikan oleh fungsi mengacu pada sel pada lembar saat ini.

Agar lebih jelas kita lihat tabel dibawah ini:

Untuk kasus 1 adalah fungsi mencari alamat sel, sedangkan pada kasus 2 mencari alamat sheet.

3. FUNGSI COLUMN
Fungsi ini digunakan untuk menunjukkan urutan kolom yang dimulai dari ururtan sebelah kiri.
Syntaxnya adalah sebagai berikut :

=COLUMN([reference])

• reference adalah sel atau range yang dipilih untuk diketahui nomor kolomnya.

Fungsi ini cukup mudah dengan melihat kasus diatas.

4. FUNGSI CHOOSE

Penerapan fungsi ini digunakan untuk menampilkan isi suatu sel berdasarkan posisi urutan dalam kelompok referensi. Dalam referensi sendiri bisa terdiri dari beberapa tipe, diantaranya angka, teks atau sejenisnya.

Rumusannya adalah sebagai berikut :

=CHOOSE(index_num, value1, [value2], ...)

• Index_num ; diisi dengan nilai argumen yang dipilih. Index_num harus angka antara 1 sampai dengan 254, ( formula / referensi  sel  berisi angka antara 1 dan 254 )

• Value ; nilai opsionalnya adalah 1-254.  Argumen ini digunakan untuk memilih nilai atau tindakan untuk berkinerja berdasarkan index_num. Argumen dapat berupa nomor, referensi sel, formula, fungsi, atau teks.

 Lihat contoh berikut ini :

Pada contoh diatas anda dapat mengganti / memilih sesuai keinginan pada alamat sel E19.

5. FUNGSI COLUMNS 

Fungsi yang digunakan untuk menampilkan angka yang menunjukkan jumlah kolom dalam suatu array atau referensi.

Fungsi rumusannnya adalah sebagai berikut :

=COLUMNS(array)

Lebih jelasnya lihat contoh dibawah ini :

6. FUNGSI HYPERLINK

Fungsi ini menciptakan jalan pintas untuk membuka dokumen yang tersimpan pada server jaringan, intranet, internet atau kesebuah file. Ketika Anda mengklik sel yang berisi fungsi HYPERLINK, Microsoft Excel akan membuka file yang disimpan di link_location.

Syntax-nya adalah seperti ini :

=HYPERLINK(link_location, [friendly_name])

• Link_location ; diisi nama Path dan nama file dari sebuah dokumen yang akan dibuka. Link_location dapat merujuk ke suatu tempat di dokumen - seperti sel tertentu atau kisaran pada  lembar kerja Excel atau workbook, atau ke bookmark dalam dokumen Microsoft Word. juga bisa ke file yang disimpan pada hard disk drive.
• Friendly_name ; Opsionalnya adalah teks atau nilai numerik yang ditampilkan dalam sel. Friendly_name ditampilkan dalam warna biru dan digarisbawahi.

Dibawah ini adalah contoh fungsi tersebut :

Pada contoh diatas tulisan pada sel yang harusnya berwarna biru dan digarisbawahi telah dimodifikasi menjadi warna merah dan dihilangkan garis bawahnnya.

7. FUNGSI HLOOKUP
Fungsi ini dipakai untuk menampilkan data dari tabel yang bentu tabel tersebut bisa horizontal maupun vertikal. Sedangkan syarat dalam menyusun tabel harus urutan menaik. misalnya saja dari angka 1, 2, 3, dst ayau A, B, C, dst.
Sedangkan rumusnya adalah :

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• Lookup_value ; adalah nilai yang akan diisikan pada baris pertama tabel. Lookup_value dapat  menjadi nilai, referensi, atau string teks.
• Table_array ; range data ( vertikal atau horizontal ) sebagai tabel pembantu.
• Row_index_num ; nomor urut baris yang digunakan untuk membaca tabel. 

Contohnya adalah sebagai berikut :

    
8. FUNGSI INDEX
Fungsi Index adalah fungsi untuk menampilkan data pada sebuah array dengan didasarkan pada nomor baris dan nomor kolom.
Bentuk syntax-nya adalah sebagai berikut :

=INDEX(array, row_num, [column_num])

• array ; tabel isi yang akan dicari.
• row_num ; posisi nomor baris yang dicari pada tabel.
• column_num ; posisi nomor kolom yang akan dicari pada tabel.

Penjelasan  untuk contoh fungsi ini adalah :

9. FUNGSI LOOKUP
Fungsi ini dipakai untuk menampilkan data yang terdapat pada lebih dari satu tabel.
Penulisan rumus fungsinya adalah :

=LOOKUP(lookup_value, lookup_vector, [result_vector])

• lookup_value ; adalah nilai yang menjadi dasar  pembacaan tabel.
• lookup_vector ;  adalah range data tempat nilai yang menjadi dasar pembacaan tabel.
• result_vector ; adalah range data yang isinya data yang akan dicari.

Contohnya adalah sebagai berikut :

10. FUNGSI INDIRECT

Fungsi INDIRECT dipakai untuk menampilkan data pada suatu sel, alamat sel yang dituju isinya berupa teks atau nama suatu range.

Bentuk fungsinya adalah sebagai berikut :

=INDIRECT(ref_text, [a1])

• ref-text ; sel sebagai referensi style A1 atau referensi style R1C1.
• a1 ; nilai logika referensi yang menunjukkan penggunaan style a1 atau R1C1.

Contohnya sebagai berikut :

11. FUNGSI OFFSET

Fungsi OFFSET digunakan untuk menyalin atau mengcopy data pada suatu range. Dasarnya adalah posisi awal dengan menetapkan jarak kolom dan baris posisi dari data yang disalin.

Bentuk rumusnya adalah sebagai berikut :

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

• reference ; alamat sel posisi data yang akan disalin
• rows ; posisi penempatan hasil data yang disalin.
• cols ; nomor kolom, ke kiri atau ke kanan sesuai keinginan. Pada prinsipnya sama dengan argumen row.
•  height ; tinggi range ( dalam jumlah baris (angka positif)) dan tidak boleh melebihi jumlah baris dalam referensi.
• width ; lebar range ( dalam jumlah kolom ( angka positif)) dan tidak boleh melebihi jumlah kolom referensi.

Contohnya adalah sebagai berikut :

12.  FUNGSI ROW
Fungsi ini digunakan untuk menunjukkan posisi urutan baris dari sebuah sel referensi.
Syntax-nya adalah sebagai berikut :

=ROW([reference])

• reference ; alamat sel atau range yang akan dicari posisi / letak nomor barisnya.

 Contoh fungsinya adalah sebagai berikut :

Khusus untuk alamat sel C11 dan C12, setelah menuliskan fungsi =ROW(A2:A6) diakhiri dengan menekan tombol Shift + Ctrl + Enter.

13. FUNGSI TRANSPOSE

Fungsi ini dipakai untuk mengubah posisi tabel pada sebuah array dari posisi kolom menjadi baris.

Bentuk rumusannya adalah sebagai berikut :

 =TRANSPOSE(array)

• array ; isi dengan array, sel atau range pada lembar kerja yang ingin anda refleksikan.
  

Contohnya adalah sebagai berikut :

Cara membuat fungsi ini lain dengan pembuatan fungsi-fungsi yang sudah diposting sebelumnya :
Ikuti prosedur berikut ini :

• Arahkan petunjuk sel untuk contoh diatas di alamat sel B15.
• Blok alamat sel ( B15:G20 ) kemudian ketikan fungsi ini =TRANSPOSE(
• Selanjutnya arahkan krusor pada alamat sel B5 dan blok sampai dengan G20.
• Tekan Shift + Ctrl + Enter

 14. FUNGSI ROWS
Fungsi ini menghasilkan jumlah baris dalam referensi atau array.
Bentuk fungsinya adalah sebagai berikut :

=ROWS(array)

• Diisi dengan array yang akan dihitung jumlah barisnya.

Contonya adalah sebagai berikut :

 

15. FUNGSI VLOOKUP
Fungsi ini digunakan untuk menampilkan data dari tabel berbentuk vertikal, dengan persyaratan data tabel tersebut harus menaik ( misal dari A sd Z  ) atau dari yang terkecil ke besar ( 1 dst... )
Fungsinya adalah sebagai berikut :

=VLOOKUP(lookup_value,table_array;col_index_num)

• lookup_value ; nilai kunci pembaca tabel
• table_array ; tabel yang difungsikan sebagai tabel pembaca.
• col_index_num ; nomor urut kolom ( penghutingan dimulai dari tabel sebelah kiri )

Contohnya adalah sebagai berikut :

Inilah fungsi-fungsi yang berada pada kelompok Fungsi LOOKUP dan REFERENSI. Untuk file xlxs-nya bisa diunduh pada link dibawah ini.

Semoga bermanfaat.

DOWNLOAD