VLOOKUP, Excel’in en kullanışlı işlevlerinden biridir ve aynı zamanda en az anlaşılanlardan biridir. Bu yazıda VLOOKUP'ı gerçek hayattan bir örnek olarak açığa çıkarıyoruz. Bir kullanılabilir yaratacağız Fatura şablonu hayali bir şirket için.
VLOOKUP bir Excel fonksiyon. Bu makale, okuyucunun zaten Excel işlevlerini anlamadığını ve SUM, ORTALAMA ve BUGÜN gibi temel işlevleri kullanabileceğini varsayar. En yaygın kullanımında, VLOOKUP bir veri tabanı işlev, veritabanı tablolarıyla çalıştığı anlamına gelir – veya daha basit bir şekilde, listeleri Excel çalışma sayfasındaki şeylerin listesi. Ne tür şeyler? İyi, hiç bir şey. CD koleksiyonunuzdaki çalışanların, ürünlerin veya müşterilerin veya CD'lerin veya gece gökyüzündeki yıldızların bir listesini içeren bir çalışma sayfanız olabilir. Gerçekten önemli değil.
İşte bir liste veya veritabanı örneği. Bu durumda, hayali şirketimizin sattığı ürünlerin bir listesi:
Genellikle böyle listeler, listedeki her öğe için bir tür benzersiz tanımlayıcıya sahiptir. Bu durumda, benzersiz tanımlayıcı “Öğe Kodu” sütunundadır. Not: VLOOKUP işlevinin bir veritabanı / liste ile çalışması, bu listenin zorunluluk benzersiz tanımlayıcıyı (veya “anahtar” veya “Kimlik”) içeren bir sütuna sahip olmak ve bu sütun, tablodaki ilk sütun olmalıdır. Yukarıdaki örnek veri tabanımız bu kriteri karşılar.
VLOOKUP'ı kullanmanın en zor kısmı tam olarak ne için olduğunu anlamaktır. Öyleyse bakalım önce şunu netleştirebilecek miyiz:
VLOOKUP, benzersiz tanımlayıcının verilmiş bir örneğini temel alarak bir veritabanından / listeden bilgi alır.
Yukarıdaki örnekte, VLOOKUP işlevini bir öğe koduyla başka bir e-tabloya eklersiniz ve ilgili öğenin açıklamasını, fiyatını veya orijinalinde açıklandığı şekilde kullanılabilirliğini (“Stok” miktarı) verir. liste. Bu bilgi parçalarından hangisi size geri dönecek? Peki, formülü oluştururken buna karar vermelisin.
İhtiyacınız olan tek şey, veritabanındaki bir bilgi parçasıysa, içinde VLOOKUP işlevine sahip bir formül oluşturmak çok zor olacaktır. Genelde, bu tür işlevselliği, şablon gibi yeniden kullanılabilir bir elektronik tabloda kullanırsınız. Birisi geçerli bir öğe kodu girdiğinde, sistem ilgili öğe hakkında gerekli tüm bilgileri alır.
Bunun bir örneğini oluşturalım: Fatura şablonu hayali şirketimizde tekrar tekrar kullanabileceğimizi.
Önce Excel'i başlatırız ve kendimize boş bir fatura oluştururuz:
İşe yarayacak şekilde: Fatura şablonunu kullanan kişi “A” sütununda bir dizi madde kodunu dolduracak ve sistem her bir ürünün tanımını ve fiyatını ürün veri tabanımızdan alacaktır. Bu bilgi, her bir madde için toplam satırı hesaplamak için kullanılacaktır (geçerli bir miktar girdiğimizi varsayarak).
Bu örneği basit tutmak amacıyla, ürün veritabanını aynı çalışma kitabındaki ayrı bir sayfada bulacağız:
Gerçekte, ü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 yer alması pek de önemli olmayan VLOOKUP işlevinde çok az fark yaratır.
Böylece, şu şekilde görünen ürün veritabanımızı oluşturduk:
Yazmak üzere olduğumuz VLOOKUP formülünü test etmek için önce boş faturanızın A11 hücresine geçerli bir öğe kodu giriyoruz:
Daha sonra aktif hücreyi, VLOOKUP tarafından veri tabanından bilgilerin alınmasını istediğimiz hücreye taşırız. İlginçtir ki, çoğu insanın yanlış anladığı adım budur. Daha fazla açıklamak için: A11 hücresindeki ürün koduna karşılık gelen açıklamayı alacak bir VLOOKUP formülü oluşturmak üzereyiz. Bu tanımlamayı ne zaman aldığımızda nereye koymak istiyoruz? Tabii ki B11 hücresinde. İşte VLOOKUP formülünü yazdığımız yer: B11 hücresinde. Şimdi B11 hücresini seçin.
VLOOKUP'ı seçebilmemiz ve formülü tamamlamada bazı yardımlar alabilmemiz için Excel'in sunduğu tüm kullanılabilir işlevlerin listesini bulmamız gerekiyor. Bu ilk tıkladığınızda bulunur. Formüller sekmesini ve ardından İşlev ekle:
Excel'de bulunan işlevlerden herhangi birini seçmemizi sağlayan bir kutu görünür.
Aradığımızı bulmak için, “arama” gibi bir arama terimi yazabiliriz (çünkü ilgilendiğimiz işlev bir yukarı Bak fonksiyonu). Sistem bize Excel'deki arama ile ilgili tüm fonksiyonların bir listesini verirdi. VLOOKUP listedeki ikincisi. Bir tıklama seçin tamam.
İşlev Argümanları hepimizi soran bir kutu görünür. argümanlar (veya parametreler) VLOOKUP işlevini tamamlamak için gerekli. Bu kutuyu, bize aşağıdaki soruları soran işlev olarak düşünebilirsiniz:
- Veritabanında hangi benzersiz tanımlayıcıyı arıyorsunuz?
- Veri tabanı nerede?
- Benzersiz tanımlayıcıyla ilişkilendirilmiş veri tabanından hangi bilgileri almak istersiniz?
İlk üç argüman gösterilir kalın harflerle, olduklarını belirten zorunlu bağımsız değişkenler (VLOOKUP işlevi onlarsız tamamlanmadı ve geçerli bir değer döndürmeyecek). Dördüncü argüman koyu değil, yani isteğe bağlı:
Argümanları sırasıyla yukarıdan aşağıya doğru tamamlayacağız.
Tamamlamamız gereken ilk argüman lookup_value argüman. İşlev, benzersiz tanımlayıcıyı nerede bulacağımızı söylememizi ister ( ürün Kodu Bu durumda) bunun açıklamasını döndürüyor olması gerekir. Daha önce girdiğimiz ürün kodunu seçmeliyiz (A11'de).
İlk argümanın sağındaki seçici simgesine tıklayın:
Ardından, öğe kodunu içeren hücreye (A11) bir kez tıklayın ve Giriş:
İlk argümana “A11” değeri eklenir.
Şimdi bunun için bir değer girmemiz gerekiyor Masa dizisi argüman. Başka bir deyişle, VLOOKUP'a veri tabanını / listeyi nerede bulacağını söylemeliyiz. İkinci bağımsız değişkenin yanındaki seçici simgesine tıklayın:
Şimdi veri tabanını / listeyi bulun ve tüm listeyi seçin – başlık satırı dahil değil. Örneğimizde, veritabanı ayrı bir çalışma sayfasında bulunur, bu yüzden ilk önce bu çalışma sekmesini tıklayın:
Sonra başlık satırı dahil değil tüm veritabanını seçiyoruz:
…ve bas Giriş. Veritabanını temsil eden hücre aralığı (bu durumda “Ürün Veritabanı”! A2: D7 ”) bizim için otomatik olarak ikinci argümana girilir.
Şimdi üçüncü argümana girmeliyiz, sütun_indis_sayısı. Bu argümanı VLOOKUP'a veri tabanından hangi bilgilerin parçalandığını, A11'deki madde kodumuzla ilişkilendirdiğimizi belirtmek için kullanıyoruz. Bu özel örnekte, öğenin açıklama bize geri döndü. Veritabanı çalışma sayfasına bakarsanız, “Açıklama” sütununun ikinci veritabanındaki sütun. Bu, içine “2” değerini girmemiz gerektiği anlamına gelir. sütun_indis_sayısı Kutu:
Buraya “2” girmediğimizi not etmek önemlidir, çünkü “Açıklama” sütunu B bu çalışma sayfasındaki sütun. Veritabanının sütun başlaması halinde K çalışma sayfasından yine de bu alana “2” girecektik çünkü “Açıklama” sütunu “Table_array” belirtilirken seçtiğimiz hücre setindeki ikinci sütun.
Son olarak, son VLOOKUP argümanına bir değer girip girmemeye karar vermemiz gerekir, Menzil araması. Bu argüman ya bir doğru veya yanlış değeri yoksa boş bırakılmalıdır. VLOOKUP'ı veritabanlarıyla kullanırken (zamanın% 90'ı gibi), bu argümana ne koyacağına karar vermenin yolu şu şekilde düşünülebilir:
Veritabanının ilk sütunu (benzersiz tanımlayıcıları içeren sütun) artan sırada alfabetik / sayısal olarak sıralanırsa, değerinin girilmesi mümkündür. doğru bu argümana ya da boş bırakın.
Veritabanının ilk sütunu ise değil sıralanırsa veya azalan sırada sıralanırsa, o zaman siz zorunluluk değerini girin yanlış bu argümana
Veritabanımızın ilk sütunu olarak değil sıralanmış yanlış bu argümana:
Bu kadar! VLOOKUP'ın ihtiyaç duyduğumuz değeri döndürmesi için gereken tüm bilgileri girdik. Tıkla tamam düğmesine basın ve “R99245” ürün koduna karşılık gelen tanımlamanın B11 hücresine doğru girildiğini unutmayın:
Bizim için oluşturulan formül şuna benzer:
Eğer girersek farklı Öğe kodunu A11 hücresine 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:
Öğeyi almak için benzer adımlar uygulayabiliriz fiyat E11 hücresine geri döndü. Yeni formülün E11 hücresinde oluşturulması gerektiğini unutmayın. Sonuç şöyle görünecek:
… Ve formül şöyle görünecek:
İki formül arasındaki tek farkın üçüncü argüman olduğuna dikkat edin.sütun_indis_sayısı) “2” den “3” e değiştirildi (çünkü verilerin veritabanındaki 3. sütundan alınmasını istiyoruz).
Bu eşyalardan 2'sini almaya karar vermiş olsaydık, D11 hücresine “2” girerdik. Sonra toplamı elde etmek için F11 hücresine basit bir formül gireriz:
= D11 * E11
… Ki buna benziyor…
Fatura Şablonunu Tamamlama
Şimdiye kadar VLOOKUP hakkında çok şey öğrendik. Aslında, bu makalede öğreneceğimiz her şeyi öğrendik. VLOOKUP'ın veritabanlarının yanı sıra başka durumlarda da kullanılabileceğini not etmek önemlidir. Bu daha az yaygındır ve gelecekteki Nasıl Yapılır Geek makalelerinde ele alınabilir.
Fatura şablonumuz henüz tamamlanmadı. Bunu tamamlamak için aşağıdakileri yapardık:
- Örnek madde kodunu A11 hücresinden ve “2” hücresini D11 hücresinden kaldırırdık. Bu, yeni oluşturulan VLOOKUP formüllerinin hata mesajlarını göstermesine neden olacaktır:
Excel’in makul kullanımıyla bunu düzeltebiliriz EĞER() ve ISBLANK () fonksiyonlar. Formülümüzü bundan değiştiriyoruz… = VLOOKUP (A11, Product Ürün Veritabanı ’! A2: D7,2, FALSE)…buna…= IF (ISBLANK (A11), ””, VLOOKUP (A11,, Ürün Veritabanı ’! A2: D7,2, FALSE))
- B11, E11 ve F11 hücrelerinde bulunan formülleri, faturanın kalem satırlarının kalanına kopyalayacağız. Bunu yaparsak, ortaya çıkan formüllerin artık doğru şekilde veritabanı tablosuna başvurmayacağını unutmayın. Veritabanının hücre referanslarını değiştirerek bunu düzeltebiliriz. kesin hücre referansları. Alternatif olarak – ve hatta daha iyisi – aralık adı Ürün veritabanının tamamı için (“Ürünler” gibi) ve hücre aralığı yerine bu aralık adını kullanın. Formül bundan değişecekti… = IF (ISBLANK (A11), ””, VLOOKUP (A11,, Ürün Veritabanı ’! A2: D7,2, FALSE))…buna… = EĞER (ISBLANK (A11) “,” VLOOKUP (A11, Ürünler, 2, YANLIŞ))…ve sonra formülleri, fatura kalemi satırlarının geri kalanına kopyalayın.
- Muhtemelen formüllerimizi içeren hücreleri “kilitleriz” (veya daha doğrusu). Kilidini aç diğer Hücreler), ve dikkatli bir şekilde oluşturulmuş formüllerimizin birileri faturayı doldurmaya geldiğinde yanlışlıkla üzerine yazılmamasını sağlamak için çalışma sayfasını koruyun.
- Dosyayı bir olarak kaydederiz şablon, şirketimizdeki herkes tarafından tekrar kullanılabilir.
Eğer hissediyor olsaydık Gerçekten mi Zekice, başka bir çalışma sayfasında tüm müşterilerimizin veritabanını oluşturacağız ve müşterinin adını ve adresini B6, B7 ve B8 hücrelerine otomatik olarak doldurmak için F5 hücresine girilen müşteri kimliğini kullanacağız.
VLOOKUP ile pratik yapmak ya da sadece sonuçta elde edilen Fatura Şablonumuzu görmek istiyorsanız, buradan indirebilirsiniz.
