 | |||
|
|
Excel's VLOOKUP Function: How to Use It and How to Nest It Excel dari VLOOKUP Fungsi: Cara Menggunakan Penyalahgunaan dan Mau ke sarang Penyalahgunaanby Linda Johnson, MOS oleh Linda Johnson, MOS |
|
The VLOOKUP function is a handy one to know when you want Excel to lookup a value in one place and insert it in another. VLOOKUP dengan fungsi yang berguna untuk mengetahui bila Anda ingin Excel untuk mencari nilai di satu tempat dan dimasukkan dalam lainnya. For example, let's say you have a list of all of your customers on a sheet named “Accounts” and an invoice on another sheet named “Invoice”. Sebagai contoh, mari kita berkata Anda memiliki daftar semua pelanggan Anda pada lembaran yang bernama "Akun" dan faktur pada lembar lain bernama "Faktur". When you type in their account number on the Invoice, you want Excel to fill in the name of the customer and their address (and this information is included for all customers on the Accounts sheet). Bila Anda ketik nomor rekening mereka pada Faktur, Anda ingin Excel untuk mengisi nama dan alamat mereka pelanggan (dan informasi ini termasuk untuk semua pelanggan di Akun lembar). A VLOOKUP will do this for you. J VLOOKUP akan melakukannya untuk Anda. Make a small sample workbook to try this out. Contoh kecil membuat workbook ini mencoba untuk keluar. Name Sheet One “Invoice” and name Sheet Two “Accounts”. Satu nama Sheet "Faktur" dan Lembar Dua nama "Akun". On the Accounts sheet, put three columns of data. Pada Akun lembar, taruh tiga kolom data. Column A would be Account Numbers, Column B would be Customer Name, and Column C would be Address. Kolom A akan Account Number, Kolom B akan Nama Pelanggan, dan Kolom C akan Alamat. Add at least five pretend customers, so you have enough to play with. Tambahkan setidaknya lima pretend pelanggan, sehingga Anda memiliki cukup untuk bermain dengan. On the Invoice sheet, just add these five column headings in cells A1:E1 – Date, Product Ordered, Account #, Customer Name, Address -but don't put any data in there yet. Pada Faktur lembar, hanya lima ini menambahkan kolom judul di sel A1: E1 - Tanggal, Produk memerintahkan, Account #, Nama Pelanggan, Alamat-tetapi tidak menempatkan data apapun di sana belum. (In reality, this sheet would be an actual invoice which included sections for you to add ordering info for any products they buy, etc. But for this example, let's keep it simple.) (Sebenarnya, ini akan menjadi sebuah lembaran Tagihan yang termasuk bagian bagi Anda untuk menambahkan info pemesanan untuk membeli produk mereka, dll Tapi untuk contoh ini, mari kita tetap itu sederhana.) Now, before we try the VLOOKUP, the best thing to do is name the range of data that includes the info you want to pull over from the Accounts sheet. Sekarang, sebelum kita mencoba VLOOKUP, yang terbaik untuk dilakukan adalah nama dari berbagai data yang meliputi informasi yang ingin Anda tarik dari Akun lembar. You can do VLOOKUPs without naming the range, but then you MUST be sure to use absolute cell references. Anda dapat melakukannya tanpa nama VLOOKUPs kisaran, tetapi kemudian anda harus pastikan untuk menggunakan sel referensi absolut. So, I find naming the range a much easier way to do it. Jadi, saya menemukan nama kisaran yang lebih mudah cara untuk melakukannya. Highlight all the data on the Accounts sheet and name it Customers (don't include the column headings in the named range – just the data). Sorot semua data pada lembar Akun Pelanggan dan nama (tidak termasuk kolom judul dalam rentang bernama - hanya data). If you don't know how to name ranges, read this TechTrax article I wrote on how to do that. Jika anda tidak tahu bagaimana nama rentang, TechTrax membaca artikel ini saya menulis tentang cara untuk melakukan itu. http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=281 http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=281 Now that you have your data and have named the range, let's look at building a simple VLOOKUP formula. Sekarang bahwa Anda memiliki data dan memiliki nama yang beragam, mari kita melihat bangunan yang sederhana rumus VLOOKUP. Assume we will be typing account numbers into cell C2 and wanting the customer's name and address to be filled into D2 and E2. Anggaplah kita akan mengetik nomor rekening ke dalam sel C2 dan keinginan pelanggan dengan nama dan alamat yang akan diisi ke D2 dan E2. The best way to learn new formulas is to use the Insert Function button Cara terbaik untuk mempelajari rumus baru ini untuk menggunakan Sisipkan Fungsi tombol
(The picture above shows how this box looks in Excel 97/2000. In 2002/2003 it's slightly different, but I think you can figure it out.) (Gambar di atas menunjukkan bagaimana kotak ini terlihat dalam Excel 97/2000. Dalam 2002/2003 ini sedikit berbeda, tapi saya pikir Anda bisa figure it out.) If you know what type of function you are looking for, you can select the category and all the functions within that category will be listed. However, if you don't know what category you need, you can select "All" in the category list and all of Excel's functions will be listed. Notice as you click on any function name, Excel displays a description of what that function does below the boxes. For this exercise, select the "Lookup & Reference" category and scroll down to select the VLOOKUP function. Note that it tells you that this function “searches for a value in the leftmost column of a table and returns a value from the same row in that table based on what column in that row you specify.” Click OK. Jika anda tahu apa jenis fungsi yang Anda cari, Anda dapat memilih kategori dan semua fungsi dalam kategori yang akan ditampilkan. Namun, jika Anda tidak tahu apa kategori yang Anda butuhkan, Anda dapat memilih "All" di kategori daftar dan Excel dari semua fungsi akan terdaftar. Notice yang Anda klik pada nama fungsi, Excel akan menampilkan keterangan tentang apa yang tidak berfungsi di bawah kotak. Untuk latihan ini, pilih "Lookup & Referensi" kategori dan gulir ke bawah untuk memilih fungsi VLOOKUP. Catatan yang memberitahu Anda bahwa fungsi ini "mencari nilai di kolom sebelah kiri dari meja dan kembali dari nilai yang sama dalam baris tabel berdasarkan kolom dalam baris yang Anda tentukan." Klik OK. Once you click OK, you will get the wizard which helps you with your VLOOKUP formula. Setelah Anda mengklik OK, Anda akan mendapatkan panduan yang akan membantu Anda dengan rumus VLOOKUP. Now, because we will be typing an Account # in cell C2, that is the value we must put in the first box of this wizard which will tell Excel to look for whatever is in C2 in the leftmost column of our lookup table (which we created on the Accounts sheet and named "Customers"). So, enter C2 into the top box. Sekarang, karena kita akan mengetik Account # dalam sel C2, yaitu nilai kita harus dimasukkan ke dalam kotak pertama dari wizard ini yang akan kirim Excel untuk mencari apa yang ada di C2 pada kolom sebelah kiri dari tabel kita (yang kita dibuat pada lembaran Akun dan bernama "Pelanggan"). Jadi, masukkan C2 ke atas kotak. Click into the next box where it says "Table_array". Klik pada kotak di mana ia mengatakan "Table_array". Notice at the bottom of this box, it tells you what each box you click inside needs. This is where we need to identify our table so Excel knows where to look. So, in this box, simply type Customers . Perhatikan di bagian bawah kotak ini, ia memberitahu anda apa yang anda klik setiap kotak di dalam kebutuhan. Di sinilah kita perlu mengidentifikasi tabel sehingga kami Excel untuk mencari tahu di mana. Jadi, dalam kotak ini, cukup ketik Pelanggan. (If you didn't name the range, you will have to put the absolute reference including the sheet name here … this is why it's easier to just name the range.) (Jika Anda tidak memberikan nama jangkauan, Anda harus menempatkan mutlak referensi termasuk lembaran nama disini ... mengapa hal ini akan lebih mudah untuk hanya nama rentang.) Click into the third box. This one wants to know the number of the column we want returned. Remember that what you entered in the first box in this wizard must ALWAYS be in the first column of your lookup table. So, in our table, the Account # is in the first column and the Customer Name is in the second column. Since the customer name is what we want to put here, just type a 2 to let Excel know we want what is in the second column. Klik ke dalam kotak ketiga. Ini satu ingin mengetahui jumlah kolom kita ingin kembali. Ingat bahwa apa yang Anda masukkan dalam kotak pertama dalam wizard ini harus menjadi ALWAYS pada kolom tabel Anda. Jadi, dalam tabel, Account # ada di kolom pertama dan Nama Pelanggan yang ada di kolom kedua. Sejak nama pelanggan adalah apa yang kita ingin meletakkan di sini, cukup ketik 2 untuk memberitahu Excel kita ingin tahu apa yang ada di kolom kedua. Notice the last box is labeled "Range_lookup" and it is the only label that is not bold. Whenever a label in this wizard is not bold, that means this "argument" of the function is not required. However, if you do not enter anything in this box, Excel will apply the default. If you read the instructions at the bottom of this box, you will see that the default for this box is "true" which will find the "closest match", whereas "false" will find an "exact match". Since we want an exact match, type false in this box. Pemberitahuan terakhir kotak berlabel "Range_lookup" dan ia adalah satu-satunya label yang tidak tebal. Kapanpun label dalam wizard ini tidak tebal, yang berarti "argumen" dari fungsi tidak diperlukan. Namun, jika Anda tidak memasukkan sesuatu dalam kotak ini, Excel akan menerapkan standar. Jika Anda membaca petunjuk di bagian bawah kotak ini, Anda akan melihat bahwa standar untuk kotak ini adalah "true" yang akan menemukan "paling cocok", sedangkan "palsu" akan menemukan "sama persis". Karena kita ingin yang sama persis, salah ketik di kotak ini. This is what it should look like if you have entered all the info correctly: Inilah yang harus seperti itu jika Anda telah memasukkan semua informasi dengan benar:
Click OK and you will see that cell D2 now shows #N/A , which simply means that there is no value yet in C2, so the information is "not available". Look in your formula bar and you will see the formula is =VLOOKUP(C2,Customers,2,FALSE) . As you get more used to using functions, you won't have to use the wizard as much if you take the time to look at the formulas and start to understand how they work. Klik OK dan Anda akan melihat bahwa sekarang sel D2 menunjukkan # N / A, yang berarti bahwa tidak ada nilai namun dalam C2, sehingga informasi tersebut "tidak tersedia". Lihatlah dalam formula bar dan Anda akan melihat Rumusnya adalah = VLOOKUP (C2, Pelanggan, 2, FALSE). Seperti yang Anda mendapatkan lebih banyak digunakan untuk menggunakan fungsi tersebut, Anda tidak harus menggunakan wizard sebanyak jika Anda meluangkan waktu untuk melihat rumus dan mulai untuk memahami bagaimana mereka bekerja. Now click into cell E2 and add a VLOOKUP formula which will find the Address in our table. The formula will be exactly the same, except the "Col_index_num" will be 3 instead of 2 because we want to return the address, which is in the third column of our table. Once you have added this, you should see another #N/A in cell E2. The formula will be =VLOOKUP(C2,Customers,3,FALSE) . Sekarang klik ke sel E2 dan menambahkan VLOOKUP formula yang akan menemukan Alamat kami dalam tabel. Rumus akan persis sama, kecuali "Col_index_num" akan 3 bukan 2, karena kami ingin kembali ke alamat yang ada di ketiga kolom dari tabel kita. Setelah Anda menambahkan ini, Anda akan melihat lain # N / A E2 dalam sel. Rumus akan = VLOOKUP (C2, Pelanggan, 3, FALSE). NOTE: All other information in the second VLOOKUP formula will be exactly the same as the first one. We are still looking for the value that will be placed in C2 . We are still looking in the table named Customers . And we still want false for an exact match. The ONLY thing that is different is we are now going to pull the information from Column 3 instead of 2. CATATAN: Semua informasi lainnya yang kedua rumus VLOOKUP akan persis sama seperti yang pertama. Kami masih mencari nilai yang akan ditempatkan di C2. Kami masih mencari dalam tabel bernama Pelanggan. Dan kita masih ingin untuk palsu yang sama persis. The ONLY sesuatu yang berbeda adalah sekarang kami akan tarik informasi dari Kolom 3 bukan 2. Move to cell C2 and type in one of the Account numbers you have in your Accounts sheet and you will see Excel fills in the Customer Name and Address for that account number However, if you type a number that does not exist on your Accounts sheet, Excel will leave the #N/A because the information for that number is "not available Pindah ke sel C2 dan jenis di salah satu nomor akun yang Anda terima di Akun lembar dan Anda akan melihat Excel dalam mengisi Nama dan Alamat Pelanggan untuk nomor rekening Namun, jika Anda ketik nomor yang tidak ada pada Akun lembar, Excel akan meninggalkan # N / A karena informasi bahwa nomor tersebut "tidak tersedia To see a VLOOKUP formula in action, go to this page at my website and view this interactive formula: Untuk melihat rumus VLOOKUP dalam tindakan, pergi ke halaman ini di website saya dan melihat interaktif rumus ini: Now that we've gotten more comfortable with VLOOKUP, let's look at nesting formulas , one inside another. We can use the VLOOKUP formula we just made as a starter. Sekarang kami telah gotten VLOOKUP dengan lebih nyaman, mari kita melihat nesting rumus, satu dalam lainnya. Kita dapat menggunakan rumus VLOOKUP kita hanya dibuat sebagai starter. Let's say we don't want to see that nasty old #N/A every time one of our VLOOKUP formulas refers to an empty cell or value that is not in our lookup table. What we need to do is tell Excel to show us the result of the VLOOKUP only IF it does not return a #NA result and, otherwise, just leave the cell empty. So we need to use three different functions in one formula: VLOOKUP, IF, and ISNA. Misalnya kita tidak ingin melihat bahwa nasty old # N / A setiap kali salah satu rumus VLOOKUP merujuk ke sel yang kosong atau nilai yang tidak ada dalam tabel kami. Apa yang kita butuhkan adalah kirim Excel untuk menunjukkan kepada kita yang hasil VLOOKUP hanya JIKA tidak kembali sebuah # NA dan hasilnya, jika tidak, hanya meninggalkan sel kosong. Jadi kita harus menggunakan tiga fungsi dalam satu rumusan: VLOOKUP, JIKA, dan ISNA. Remember how we made an IF statement. =IF( Intro to IF Statements: Intro ke JIKA Pernyataan: Nesting IF Statements: Nesting JIKA Pernyataan: So, our criteria is IF the VLOOKUP returns #N/A, give me an empty cell, but if it doesn't, give me the result of the VLOOKUP. Jadi, kami adalah kriteria yang VLOOKUP JIKA kembali # N / A, beri saya sel yang kosong, tetapi jika tidak, saya memberikan hasil VLOOKUP. Let's try it. First we need to think about each of the three formulas and what they must include. We already have our VLOOKUP formula and we already know what an IF function must include, so the only one we need to learn is the ISNA function. All the ISNA function does is return a value of true or false. True if the cell has a #N/A error and false if it doesn't. So, since an IF statement wants to know if something is true or false, these two functions work very well together. Mari kita coba. Pertama kita perlu berpikir tentang masing-masing tiga rumus dan apa yang mereka harus menyertakan. Kami telah kami rumus VLOOKUP dan kita sudah tahu apa yang harus menyertakan fungsi IF, jadi satu-satunya kita perlu belajar adalah fungsi ISNA . Semua fungsi ISNA tidak kembali adalah nilai benar atau salah. Benar jika sel memiliki # N / A error palsu dan jika tidak. Jadi, sejak JIKA sebuah pernyataan ingin tahu jika ada sesuatu yang benar atau salah, kedua fungsi bekerja dengan baik bersama-sama. Click into cell D2 in your Invoice sheet where your first VLOOKUP formula is. Klik sel D2 dalam lembar Faktur tempat pertama adalah rumus VLOOKUP. The thing you need to understand is that when you nest formulas within formulas, Excel performs the operation dictated by the deepest nested formula first, then works its way outward. So, enter the following formula into cell D2, then enter an account number in cell C2 that does not exist on your Accounts sheet and you will see that the #N/A error does not show. Hal yang anda perlu memahami bahwa ketika anda adalah sarang dalam rumus rumus, Excel melakukan operasi dictated oleh deepest nested rumus pertama, kemudian bekerja dengan jalan ke luar. Jadi, masukkan ke dalam rumus berikut sel D2, maka memasukkan nomor rekening di sel C2 yang tidak ada pada lembar Account dan Anda akan melihat bahwa # N / A kesalahan tidak muncul. =IF(ISNA(VLOOKUP(C2,Customers,2,FALSE)),"",(VLOOKUP(C2,Customers,2,FALSE))) = IF (ISNA (VLOOKUP (C2, Pelanggan, 2, FALSE )),"",( VLOOKUP (C2, Pelanggan, 2, FALSE))) NOTE: Be sure to include all the proper commas and parentheses. Remember that all opening parentheses must have closing parentheses or Excel will give you an error message. CATATAN: Pastikan untuk memasukkan semua benar koma dan tanda kurung. Ingat bahwa semua tersebut harus ada tanda kurung tutup kurung atau Excel akan memberikan pesan kesalahan. Remember that Excel performs the deepest nested function first, so looking at the formula above, you will see: Ingat bahwa Excel melakukan deepest nested fungsi pertama, begitu melihat rumus di atas, Anda akan melihat:
Getting the hang of it? Mendapatkan memahamkan itu? Excel has other lookup and reference formulas that you can explore using the Insert Function box. Excel lookup lainnya telah rumus dan referensi yang Anda dapat mencari menggunakan Sisipkan Fungsi kotak. For example, HLOOKUP is similar to VLOOKUP, but it looks for H orizontally laid out data, instead of V ertical. Misalnya, HLOOKUP sama dengan VLOOKUP, tetapi mencari orizontally H laid out data, bukan V ertical. More information on different lookup and reference formulas is available in this article from my ezine, ABC ~ All 'Bout Computers: Informasi lebih lanjut tentang berbagai rumus lookup dan referensi yang tersedia di artikel ini dari ezine, ABC ~ Semua 'Bout Komputer: Excel Lookup Functions Explained Excel Lookup Fungsi Dijelaskan Linda F. Johnson Linda F. Johnson |
0 komentar:
Posting Komentar