VLOOKUP Excel'de Nasıl Kullanılır

VLOOKUP Excel'in en kullanışlı işlevlerinden biridir ve aynı zamanda en az anlaşılanlardan biridir. Bu makalede, VLOOKUP'u gerçek hayatta bir örnek vasıtasıyla özetleyebiliriz. Hayali bir şirket için kullanışlı bir Fatura Şablonu oluştururuz.

VLOOKUP bir Excel işlevi 'dir. Bu makale, okuyucunun Excel işlevlerini zaten anlamış olduğunu ve SUM, AVERAGE ve BUODAY gibi temel işlevleri kullanabileceğini varsayacaktır. En yaygın kullanımında, VLOOKUP bir veritabanı işlevi, yani veritabanı tablolarıyla çalıştığı anlamına gelir - veya daha basit bir şekilde , öğelerini bir Excel çalışma sayfasında listeler. Ne çeşit şeyler? Peki, herhangi bir tür şeyler .CD koleksiyonunuzda çalışanların, ürünlerin, müşterilerin veya CD'lerin veya gece gökyüzündeki yıldızların bulunduğu bir çalışma sayfasına sahip olabilirsiniz. Gerçekten önemli değil.

İşte bir liste veya veritabanı örneği. Bu durumda hayali şirketimizin sattığı ürünlerin bir listesi:

Genellikle listelerdeki her öğe için benzersiz tanımlayıcıya sahip olduğu listeler. Bu durumda, benzersiz tanımlayıcı "Öğe Kodu" sütununda bulunur. Not: VLOOKUP işlevi bir veritabanı veya liste ile çalışmak için

listesinin benzersiz tanımlayıcıyı( veya "anahtar" veya "KIMLIK") içeren bir sütun içermesi gerekir ve bu sütun tablodaki ilk sütun olmalıdır.Yukarıdaki örnek veritabanımız bu kriterleri karşılamaktadır.

VLOOKUP'u kullanmanın en zor kısmı tam olarak ne için olduğunu anlamaktır.Öyleyse şunu açıklığa kavuşturup temizleyemiyoruz:

VLOOKUP, benzersiz tanımlayıcının sağlanan örneğine dayalı bir veritabanı / listeden bilgi alır.

Yukarıdaki örnekte, VLOOKUP işlevini bir öğe koduyla birlikte başka bir e-tabloya eklersiniz ve size karşılık gelen öğenin açıklamasını, fiyatını veya stok durumu( "stokta" miktarı) öğeniziorijinal liste. Bu bilgilerden hangisi sizi geri vereceksiniz? Peki, formülü oluştururken buna karar vereceksiniz.

Eğer ihtiyacınız olan tek şey veritabanından bir bilgi parçasıysa, içinde bir VLOOKUP işlevi olan bir formül oluşturmaya gitmek çok zor olur. Genellikle, bu tür işlevleri bir şablon gibi tekrar kullanılabilir bir e-tabloda kullanırsınız. Birisi geçerli bir ürün kodunu girdiği her sefer, sistem ilgili öğe hakkında gerekli tüm bilgileri alır.

Bunun bir örneği oluşturalım: Hayali şirketimizde defalarca tekrar kullanabildiğimiz bir Fatura Şablonu .

Önce Excel'e başlıyoruz ve boş bir fatura oluşturduk:

İşte işler böyle yürür: Fatura şablonunu kullanan kişi "A" sütununda bir dizi madde kodu dolduracak ve sistem her bir öğeninaçıklama ve fiyat dan bizim ürün veritabanından. Bu bilgi, her bir öğenin satır toplamını hesaplamak için kullanılacaktır( geçerli bir miktar girdiğimizi varsayarak).

Bu örneğin basit tutulması amacıyla ürün veritabanını aynı çalışma kitabındaki ayrı bir sayfaya yerleştireceğiz:

Aslında, ürün veritabanının ayrı bir çalışma kitabında bulunması daha olasıdır. Veritabanının aynı sayfada, farklı bir sayfada veya tamamen farklı bir çalışma kitabında bulunması umrumda değil VLOOKUP işlevinde çok az fark yaratır.

Dolayısıyla şu gibi görünen ürün veritabanımızı oluşturduk:

Yazmaya hazır olduğumuz VLOOKUP formülünü test etmek için önce boş faturamızın A11 hücresine geçerli bir madde kodu girelim:

Sonra, bizaktif hücreyi, VLOOKUP tarafından veritabanından alınan bilgilerin depolanacağı hücreye taşıyın.İlginçtir, çoğu insan yanlış anlayan adımdır. Daha ayrıntılı açıklamak için: A11 hücresindeki öğe koduna karşılık gelen açıklama alacak bir VLOOKUP formülünü oluşturmak üzereyiz. Bu açıklama, nereden edineceğimizi nereye koyuyoruz? Elbette B11 hücresinde.İşte bu noktada VLOOKUP formülünü yazıyoruz: B11 hücresinde.Şimdi B11 hücresini seçin.

VLOOKUP'u seçebilmemiz ve formülün tamamlanmasında biraz yardımcı olabilmemiz için, Excel'in sunabileceği tüm işlevlerin listesini bulmalıyız. Bu, önce Formülleri sekmesini tıklatıp ardından Ekle İşlev :

öğesini tıklatarak bulundu

Excel'de kullanılabilen işlevlerden herhangi birini seçmemizi sağlayan bir kutu görüntülenir.

Aradığımız bir şeyi bulmak için "arama" gibi bir arama terimi yazabiliriz( çünkü ilgilendiğimiz fonksiyon bir arama fonksiyonudur).Sistem bize Excel'de arama ile ilgili tüm işlevlerin bir listesini döndürür. VLOOKUP listede ikincisidir. Bir tıklama seçin OK .

VLOOKUP işlevini tamamlamak için gereken bağımsız değişkenleri ( veya parametreleri ) için bize sorarak İşlev Argümanları kutusu görüntülenir. Bu kutuyu bize aşağıdaki soruları soran fonksiyon olarak düşünebilirsiniz:

1. Veritabanında hangi benzersiz tanımlayıcı arıyor?
2. Veritabanı nerde?
3. Veritabanından benzersiz tanımlayıcı ile ilişkili olan hangi bilgi parçası sizin için geri almak istersiniz?

İlk üç bağımsız değişken, kalın 'de olarak gösterilir ve bu da zorunlu bağımsız değişkenlerini( VLOOKUP işlevi onsuz eksik ve geçerli bir değer döndürmez) belirtir. Dördüncü argüman koyu değil, yani isteğe bağlı olduğunu gösterir:

Argümanları üstten alta doğru sırayla tamamlayacağız.

Tamamlamamız gereken ilk argüman Lookup_value argümanıdır. Fonksiyonun açıklamasına dönen eşsiz tanımlayıcıyı( burada madde kodu ) nerede bulacağını söylememiz gerekir. Daha önce girmiş olduğumuz madde kodunu seçmeliyiz( A11'de).

Birinci argümanın sağındaki selektör simgesini tıklayın:

Sonra madde kodunu( A11) içeren hücreyi bir kez tıklatın ve tuşuna basın girin:

"A11" değeri birinci argümana eklenir.

Şimdi Table_array argümanı için bir değer girmeliyiz. Başka bir deyişle, VLOOKUP'a veritabanını / listeyi nerede bulacağını söylemeliyiz.İkinci argümanın yanındaki seçici simgesini tıklayın:

Şimdi veritabanı / listeyi bulun ve listenin tamamını seçin - başlık satırı içermeyen.Örneğimizde veri tabanı ayrı bir çalışma sayfasında yer almaktadır, bu yüzden ilk önce o çalışma sayfası sekmesine tıklıyoruz:

Sonra başlık satırını da içermeyen tüm veritabanını seçelim:

. .. ve tuşuna basın girin. Veritabanını temsil eden hücrelerin aralığı( bu durumda 'Ürün Veritabanı'! A2: D7 ") otomatik olarak ikinci bağımsız değişkene girilir.

Şimdi, üçüncü argüman olan Col_index_num 'yi girmeliyiz. Bu argümanı, VLOOKUP'a veritabanından hangi bilgiyi belirtmek için kullandığımızı, A11'deki öğe koduyla ilişkilendirerek bize geri dönmek istiyoruz. Bu özel örnekte, öğenin açıklaması 'nin bize geri gönderilmesini isteriz. Veritabanı çalışma sayfasına bakarsanız, "Açıklama" sütununun veritabanındaki ikinci sütununda olduğunu fark edeceksiniz. Bu, Col_index_num kutusuna "2" değeri girmeliyiz demektir:

Burada "Açıklama" sütunu B sütununda olduğu için burada "2" girilmediğimizi bilmeniz önemlidir. Bu çalışma sayfası.Veritabanı K sütununda başlamazsa, "Açıklama" sütununda "Tablo_array" belirtildiğinde seçtiğimiz hücreler kümesindeki ikinci sütundur çünkü yine de bu alana "2" gireriz.

Sonunda, son VLOOKUP argümanına, Range_lookup bir değer girip girmeyeceğimize karar vermemiz gerekir. Bu bağımsız değişken, gerçek veya yanlış değerini gerektirir veya boş bırakılmalıdır. VLOOKUP'u veritabanlarıyla( zamanın% 90'ı olduğu gibi) kullanırken, bu argümana ne koyacağınıza karar vermenin yolu şu şekilde düşünülebilir:

Veritabanının ilk sütunu( benzersiz tanımlayıcıları içeren sütun)alfabetik / sayısal olarak artan düzende sıralanırsa, bu argümana gerçek değerini girmek veya boş bırakmak mümkündür.

Eğer veritabanının ilk sütunu değilse sıralanır veya azalan düzende sıralanırsa değerini yanlış değerine bu argümana

girmelidir. Veritabanımızın ilk sütun değil sıralanmıştır,biz bu argümana yanlış giriyoruz:

İşte!İhtiyacınız olan değeri geri VLOOKUP için gerekli olan tüm bilgileri girdik. OK düğmesini tıklayın ve "R99245" kalem koduna karşılık gelen açıklamanın B11 hücresine doğru bir şekilde girildiğine dikkat edin:

Bizim için yaratılan formül şu şekildedir:

Bir farklı madde kodunu hücrenize girersekA11, VLOOKUP işlevinin gücünü görmeye başlayacağız: Açıklama hücresi yeni öğe koduyla eşleşecek şekilde değişir:

Ürünün fiyatını 'yi hücre E11'e döndürmek için benzer bir dizi adım uygulayabiliriz. Yeni formülün E11 hücresinde oluşturulması gerektiğini unutmayın. Sonuç şu şekilde olacaktır:

. .. ve formül şöyle görünecektir:

Üç formül arasındaki tek fark Col_index_num "2" den "3" e değiştiğidir.veritabanındaki 3. sütundan alınan verileri istiyoruz).

Bu maddelerin 2'sini almaya karar verirsek, D11 hücresine "2" girerdik. Ardından, satır toplamını elde etmek için F11 hücresine basit bir formül gireceğiz:

= D11 * E11

. .. şu şekilde görünüyor. ..

Fatura Şablonunun Tamamlanması

Şimdiye kadar VLOOKUP hakkında çok şey öğrendik. Aslında bu makalede öğreneceğimiz her şeyi öğrendik. VLOOKUP'un diğer koşullar altında veri tabanları dışında kullanılabileceğini unutmamak önemlidir. Bu daha az yaygındır ve ileride How-To Geek makalelerinde ele alınabilir.

Fatura şablonumuz henüz tamamlanmamıştır. Bunu tamamlamak için şunları yapacağız:

1. Örnek öğe kodunu A11 hücresinden ve "2" hücresini D11 hücresinden kaldıracağız. Bu, yeni oluşturulan VLOOKUP formülümüzün hata iletileri görüntülemesine neden olur:
   
   Bunu, Excel'in IF() ve ISBLANK() işlevlerini akıllıca kullanarak çözebiliriz. Formülümüzü şu şekilde değiştiriyoruz. .. = VLOOKUP( A11, 'Ürün Veritabanı'! A2: D7,2, YANLIŞ) . .. buna. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, 'Ürün Veritabanı'! A2: D7,2, FALSE))
   
2. B11, E11 ve F11 hücrelerindeki formülleri, faturanın madde satırlarının geri kalanına kopyalayacağız. Bunu yaparsak, elde edilen formüller artık veritabanı tablosuna atıfta bulunmayacağını unutmayın. Bunu, mutlak hücre referanslarına veritabanının hücre başvurularını değiştirerek düzeltebiliriz. Alternatif olarak ve daha da iyisi, ürün yelpazesine ( "Ürünler" gibi) oluşturabilir ve hücre referansları yerine bu aralık adını kullanabilirsiniz. Formül bundan değişecektir. .. = IF( ISBLANK( A11), VLOOKUP( A11, 'Ürün Veritabanı'! A2: D7,2, FALSE)) . .. bu işe. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, Ürünler, 2, YANLIŞ)) . .. ve sonra formülleri fatura kalemi satırlarının geri kalanına kopyalayın.
3. Formüllerimizi içeren hücreleri muhtemelen "kilitler"( daha doğrusu , diğer hücrelerini kilidini açarız) ve daha sonra birisi doldurmaya geldiğinde özenle oluşturulmuş formüllerimizin yanlışlıkla üzerine yazılmamasını sağlamak için çalışma sayfasını koruruzfaturada.
4. Dosyayı bir şablonu olarak kaydedeceğiz, böylece şirketimizdeki herkes tarafından tekrar kullanılabilirdi

gerçekten zekice hissediyorsak, tüm müşterilerimizin bir veritabanı oluşturup başka bir çalışma sayfasında yaratırdık veB6, B7 ve B8 hücrelerindeki müşterinin adını ve adresini otomatik olarak doldurmak için F5 hücresine girilen müşteri kimliği.

VLOOKUP ile pratik yapmak ya da sonuçta çıkan Fatura Şablonumuzu görmek isterseniz, buradan indirebilirsiniz.