Mungkin salah satu yang paling unggul adalah umumnya diperlukan Fungsi VLOOKUP. Hal ini juga kemungkinan merupakan fungsi yang kebanyakan orang memiliki masalah pemahaman. Fungsi VLOOKUP Excel digunakan untuk mencari data tertentu dalam kolom pertama tabel data. Setelah ditemukan ia akan mengembalikan hasilnya, pada baris yang sama, sejumlah tertentu kolom dari kolom pertama. Sintaks untuk VLOOKUP adalah:
=vlookup(lookup_value,table_array,col_index_num,range_lookup)
Sintaks fungsi VLOOKUP memiliki argumen berikut:
=vlookup(lookup_value,table_array,col_index_num,range_lookup)
Sintaks fungsi VLOOKUP memiliki argumen berikut:
- lookup_value. Nilai untuk mencari dalam kolom pertama dari tabel atau jangkauan. Argumen lookup_value dapat menjadi nilai atau referensi. Jika nilai yang Anda masukan untuk argumen lookup_value lebih kecil dari nilai terkecil pada kolom pertama dari argumen table_array, VLOOKUP akan mengembalikan # N / A nilai error.
- table_array . Kisaran sel yang berisi data. Anda dapat menggunakan referensi untuk rentang (misalnya, A2: D8), atau nama jangkauan. Nilai-nilai dalam kolom pertama dari table_array adalah nilai-nilai dicari oleh lookup_value. Nilai-nilai ini dapat berupa teks, angka, atau nilai-nilai logis. Huruf besar huruf kecil dan teks yang setara.
- col_index_num . Jumlah kolom dalam argumen table_array dari mana nilai yang sesuai harus dikembalikan. Argumen col_index_num dari 1 mengembalikan nilai dalam kolom pertama dalam table_array, sebuah col_index_num dari 2 mengembalikan nilai pada kolom kedua di table_array, dan sebagainya.
CONTOH :
Contoh ini mencari kolom Kepadatan dari tabel sifat atmosfer untuk menemukan nilai-nilai yang sesuai dalam kolom Viskositas dan Suhu. (Nilai adalah untuk udara pada 0 derajat Celsius di permukaan laut, atau 1 atmosfer.)
| A | B | C |
|---|---|---|
| Density | Viscosity | Temperature |
| 0.457 | 3.55 | 500 |
| 0.525 | 3.25 | 400 |
| 0.606 | 2.93 | 300 |
| 0.675 | 2.75 | 250 |
| 0.746 | 2.57 | 200 |
| 0.835 | 2.38 | 150 |
| 0.946 | 2.17 | 100 |
| 1.09 | 1.95 | 50 |
| 1.29 | 1.71 | 0 |
| Formula | Description | Result |
| =VLOOKUP(1,A2:C10,2) | Menggunakan perkiraan kecocokan, mencari nilai 1 di kolom A, menemukan nilai terbesar kurang dari atau sama dengan 1 pada kolom A yang nilainta 0,946, dan kemudian mengembalikan nilai dari B kolom dalam baris yang sama. | 2.17 |
| =VLOOKUP(1,A2:C10,3,TRUE) | Menggunakan perkiraan kecocokan, mencari nilai 1 di kolom A, menemukan nilai terbesar kurang dari atau sama dengan 1 di kolom A, yang 0,946, dan kemudian mengembalikan nilai dari kolom C di baris yang sama. | 100 |
| =VLOOKUP(0.7,A2:C10,3,FALSE) | Menggunakan pencocokan sama persis, mencari nilai 0,7 dalam kolom A. Karena tidak ada sama persis di kolom A, Nilai error akan muncul ( N/A). | #N/A |
| =VLOOKUP(0.1,A2:C10,2,TRUE) | Menggunakan perkiraan kecocokan, mencari nilai 0,1 dalam kolom A. Karena 0,1 kurang dari nilai terkecil pada kolom A, Nilai error akan muncul ( N/A). | #N/A |
| =VLOOKUP(2,A2:C10,2,TRUE) | Menggunakan perkiraan kecocokan, pencarian untuk 2 nilai dalam kolom A, menemukan nilai terbesar kurang dari atau sama dengan 2 di kolom A, yang nilainya 1,29, dan kemudian mengembalikan nilai dari B kolom dalam baris yang sama. | 1.71 |
CONTOH 2 :
Hal ini digunakan dengan cara berikut:
= VLOOKUP ("Kucing", $ A $ 1: $ E $ 1000,3, False)
Perhatikan penggunaan False sebagai Argumen range_lookup opsional. Ini memberitahu VLOOKUP untuk menemukan yang sama persis dan yang paling sering dibutuhkan ketika mencari kecocokan teks. Jika ini diabaikan, atau TRUE, Anda akan sering mendapatkan hasil yang tidak diinginkan ketika mencari teks yang berada dalam kolom data tak terurut. Ini berarti bahwa ketika Benar digunakan, atau Argumen range_lookup dihilangkan, data tersebut harus Anda urutkan (dengan kolom pertama) dalam urutan yang sifatnya "naik".
Penggunaan Argumen TRUE, atau range_lookup dihilangkan, yang paling sering digunakan ketika melihat data angka yang berada pada kolom pertama tabel data Anda.
| A | B | C | |
| 1 | Amount | Name | Age |
| 2 | $256.95 | Bill | 56 |
| 3 | $125.63 | Joe | 22 |
| 4 | $25.66 | Mary | 59 |
| 5 | $548.00 | Dave | 21 |
| 6 | $489.32 | Frank | 48 |
| 7 | $500.25 | Sue | 19 |
| 8 | $368.59 | Hillary | 15 |
| 9 | $901.56 | Kate | 25 |
| 10 | $99.95 | Aleisha | 33 |
Jika kita menggunakan:
=VLOOKUP(368.59,$A$1:$C$10,2)
pada data di atas, kita akan mendapatkan hasil dari "Maria" dan BUKAN "Hillary" seperti yang telah kita menghilangkan Argumen AND untuk argumen range_lookup pada data, dan kita tidak mengurutkannya berdasarkan Jumlah. Hal ini karena VLOOKUP terlihat di Kolom A, Mulai dari A1, dan segera setelah menemukan jumlah $ 548,00 dan kembali ke sel di atas karena $ 548,00 lebih besar daripada lookup_value dari 368,59
Jika kita urutkan data kami dengan Jumlah kita akan mendapatkan hasil yang diinginkan. Kita juga bisa mendapatkan hasil yang diinginkan dengan menggunakan:
=VLOOKUP(368.59,$A$1:$C$10,2,False)
Penggunaan False sebagai Argumen range_lookup pada formula VLOOKUP untuk mencari semua nilai dan hanya berhenti ketika menemukan yang sama persis. Jika sama persis tidak ada kita mendapatkan # N / A, kesalahan.
Metode pemilahan yang terbaik sebagai VLOOKUP yang mencari dalam rentang urutkan JAUH lebih cepat. Efek dari ini dapat signifikan jika Anda memiliki dan mengerti banyak fungsi VLOOKUP.
Referensi Relatif
Ketika formula disalin, referensi Relatif digunakan. Sebuah Relatif referensi adalah jarak, dalam baris dan kolom, antara referensi dan sel yang berisi formula.
contoh:
Cara mengkopinya :
- Dalam contoh di atas, Row 8 berisi Referensi Relatif. Excel secara otomatis menyesuaikan formula disalin berdasarkan lokasi mereka. Misalnya, dengan menyalin rumus = SUM (B3: B7) dari sel B8 ke sel C8, Excel secara otomatis disesuaikan untuk membaca SUM = (C3: C7). Formula menjadi = SUM (D3: D7) setelah disalin ke sel D8.
- Pilih sel yang berisi rumus yang ingin Anda salin.
- Arahkan kursor ke sudut kanan bawah sel sampai perubahan kursor dari panah ke sebuah tanda plus (+).
- Tekan dan tahan tombol kiri mouse sambil menyeret mouse ke bawah kolom atau di baris.
- Sel-sel yang Anda menyeret mouse melalui sekarang akan berisi rumus berulang, yang akan telah disesuaikan relatif terhadap setiap lokasi sel baru.
Referensi Mutlak
Misalnya , Anda pilih sel B1 dari contoh sebelumnya. Dalam Formula Bar, pilih A1, dan kemudian tekan <F4>. Hasilnya adalah = $ A $ 1.
Menyalin isi sel ke B1 B10 sel. Perhatikan dan lihat bahwa rumus tidak berubah - referensi rumus tetap konstan sebagai = $ A $ 1.
Contoh lain dalam gambar : Dalam contoh di atas, Row 9 berisi Referensi konstan atau Mutlak. Bagian pertama dari formula tidak disesuaikan, meskipun lokasinya baru. Ketika rumus = $ B $ 10-B8 disalin dari sel ke sel C9 B9, B10 referensi tidak berubah, meskipun referensi B8 itu. Formula menjadi = $ B $ 10-C8. Referensi konstan atau mutlak untuk B10 sel dibuat dengan menempatkan $ di depan kolom dan baris penunjukan, dalam hal ini $ B $ 10. Jadi, tidak peduli di mana formula disalin, $ B $ 10 tetap sama. Formula menjadi = $ B $ 10-D8 setelah disalin ke sel D9. Referensi 10 $ B $ adalah konstan, atau absolut.
Cara mengkopinya/memperbanyaknya :
Cara mengkopinya/memperbanyaknya :
- Pilih sel yang berisi rumus yang ingin Anda salin.
- Buat referensi mutlak dalam rumus dengan memilih referensi sel dalam Formula bar dan menekan tombol F4. (Anda juga dapat mengetikkan $ sekitar referensi sel, misalnya, $ B $ 1.)
- Arahkan kursor ke sudut kanan bawah sel sampai perubahan kursor dari panah ke sebuah salib yang solid (+).
- Tekan dan tahan tombol kiri mouse sambil menyeret mouse ke bawah kolom atau di baris.
- Sel-sel yang Anda menyeret mouse melalui sekarang akan berisi rumus berulang, yang pada gilirannya akan berisi referensi absolut.
Tombol <F4>
Cara pintas keyboard <F4> memiliki empat pernyataan:
Pernyataan 1: referensi mutlak untuk kolom dan baris, = $ A $ 1
Pernyataan 2: referensi relatif (kolom) dan referensi Mutlak (baris), = A $ 1
Pernyataan 3: referensi absolut (kolom) dan referensi Relatif (baris), = $ A1
Pernyataan 4: referensi Sehubungan dengan kolom dan baris, = A1
Pahami dan terapkan dalam spredsheet excel yang anda miliki untuk lebih memahaminya !