Pemakaian Name Manager dan Fungsi VLOOKUP pada Microsoft Excel

Umumnya pada perhitungan Rencana Anggaran Biaya (RAB) proyek, untuk membuat tautan (link) cell antara sheet harga material, upah dan analisa harga satuan pekerjaan adalah dengan menggunakan operator "=" (sama dengan). Namun cara ini memerlukan waktu yang cukup lama dan rentan terhadap kesalahan dalam menautkan cell. Pada tutorial ini, tautan cell antar sheet menggunakan name manager dan fungsi VLOOKUP. Dengan cara ini, secara otomatis, excel akan mencarikan dan mengisi semua nilai cell (harga satuan) pada sheet Analisa Harga Satuan sesuai dengan deskripsi uraian harga material dan harga upah. File untuk latihan dan hasil akhirnya dapat diunduh pada link ini.

Berikut adalah uraian langkah-langkahnya :

A. Membuat data dynamic pada sheet harga satuan material

1. Klik pada sheet analisa harga satuan material.

2. Pada toolbar, klik : Formula >> Name Manager >> New

Gambar 1. New Name (Name Manager)

3. Beri nama Data_Material  dan pilih scope > Workbook

4. Pada bagian Refer to, masukan formula: 

=OFFSET('HARGA SATUAN MATERIAL'!$C$4;1;0;COUNTA('HARGA SATUAN MATERIAL'!$C:$C)-1;3)

5. Klik "OK"

Gambar 2. Data Material

Lingkup data akan bertambah secara otomatis setiap ada penambahan item material, ditandai dengan garis putus-putus ketika isian Refer to di klik.

B. Membuat data dynamic pada sheet harga upah pekerja (sama seperti langkah A)

1. Klik pada sheet analisa harga upah pekerja.

2. Pada toolbar, klik : Formula >> Name Manager >> New

3. Beri nama Data_Upah  dan pilih scope > Workbook

4. Pada bagian Refer to, masukan formula:

=OFFSET('HARGA UPAH PEKERJA'!$C$4;1;0;COUNTA('HARGA UPAH PEKERJA'!$C:$C)-1;3)

5. Klik "OK"

C. Pemberian nama pada kolom uraian (kolom C) pada sheet analisa harga satuan

1. Blok kolom uraian pada sheet analisa harga satuan

2. Pada Name Box, ubah / ketik nama : Material_Upah. Lalu tekan tombol "ENTER".

Gambar 3. Pemberian Nama Kolom Uraian pada

Sheet Analisa Harga Satuan

D. Pemanggilan data upah dan material pada sheet analisa harga satuan

1. Untuk data upah ketik rumus : =VLOOKUP(Material_Upah;Data_Upah;3;0) , lalu tekan tombol "ENTER".

2. Copy-paste untuk rumus ke baris bagian bawahnya.

3. Untuk data material ketik rumus : =VLOOKUP(Material_Upah;Data_Material;3;0) , lalu tekan tombol "ENTER".

4. Copy-paste untuk rumus ke baris bagian bawahnya.

5. Untuk data upah dan material pada analisa harga satuan pekerjaan yang lainnya cukup dengan copy - paste rumus yang telah dibuat sebelumnya.

Gambar 4. Pemanggilan Data Upah

Gambar 5. Pemanggilan Data Material

Video 1. Langkah A - D

E. Pemberian kode uraian material 

Untuk mempermudah pencarian data di sheet analisa harga satuan. Uraian item material ditandai dengan kode pengenal (sebagai contoh) : hm_ di bagian depannya. (Catatan : kode pengenal dapat menggunakan kode berbeda sesuai dengan keinginan pemakai)

1. Insert kolom baru (blok kolom C, klik kanan lalu pilih insert) untuk kolom KODE MATERIAL.

2. Pada cell C5, ketik formula : =CONCATENATE($A$5;D5) , lalu tekan tombol "ENTER". Keterangan : klik tombol F4 setelah klik cell A5 untuk mengunci cell tersebut pada formula.

Gambar 6. Kode Uraian Material

3.Copy-paste rumus ke baris bagian bawahnya atau klik kiri dan drag pada pojok kanan bawah cell.

4. Blok cell yang berisi kode (kolom baru) dan uraian material. Pada toolbar, klik : Formula >> Create From Selection (Name Manager). Pada Create Name from Selection, pilih "Left column". Kemudian klik "OK"

Gambar 7. Create Name from Selection

F. Pemberian kode uraian upah
Lakukan langkah yang sama (langkah E) untuk KODE UPAH pada sheet harga upah pekerja dengan kode depan uraian pekerjaan : hu_.

G. Pemakaian kode item uraian 
Pemakaian kode item uraian sesuai dengan kode yang telah dideklarasikan pada langkah E dan F.

1. Pada cell uraian sheet analisa satuan pekerjaan, untuk upah ketik : =hu , maka akan muncul list upah.

2. Lalu pilih item upah dengan cara double klik kiri pada item yang diinginkan. Lalu tekan "ENTER"

Gambar 8. Pemakaian Kode Uraian Upah / Material

3. Untuk material ketik : =hm , maka akan muncul list material.

4. Pilih item material dengan cara double klik kiri pada item yang diinginkan. Lalu tekan "ENTER"

Video 2. Langkah E - G

H. Penambahan item uraian baru
Lakukan langkah E dan F ketika ada penambahan item uraian yang baru pada sheet harga satuan material maupun upah pekerja. Kolom kode pengenal (hm_ dan hu_) dapat disembunyikan jika semua item uraian pada sheet harga satuan material dan sheet harga upah pekerja seluruhnya telah terisi lengkap.