1Aug
Yakın tarihli bir makalede, VLOOKUP adlı Excel işlevini tanıttık ve bir veritabanından yerel bir çalışma sayfasındaki bir hücrenin içine bilgi almak için nasıl kullanılabileceğini açıkladık. Bu makalede, VLOOKUP için iki kullanýmýn bulunduğundan bahsettik ve bunlardan sadece biri veritabanlarını sorgulamakla ilgilendi. Bu makalede VLOOKUP serisinin ikinci ve son bölümünde, VLOOKUP işlevi için daha az bilinen bu kullanım incelenecektir.
Daha önce yapmadıysanız, lütfen ilk VLOOKUP makalesini okuyun - bu makale, bu makalede açıklanan kavramların çoğunun okuyucu tarafından zaten bilindiğini varsayacaktır.
Veritabanları ile çalışırken VLOOKUP, veritabanında hangi veri kaydını bulmak istediğimizi belirten bir "benzersiz tanımlayıcı"( örn. Bir ürün kodu veya müşteri kimliği) geçirilir. Bu benzersiz tanımlayıcı , 'nin veritabanında bulunması gerekir, aksi takdirde VLOOKUP bize bir hata döndürür. Bu yazıda, tanımlayıcıyı veritabanında hiç bulunması gerekmeyen VLOOKUP'u kullanmanın bir yolunu inceleyeceğiz. Neredeyse sanki VLOOKUP, aradığımız verileri geri getirmek için "yeteri kadar yeteri kadar iyi" yaklaşımı benimseyebilir. Bazı durumlarda, bu
tam olarak ihtiyacımız olanı 'dir.Bu makaleyi, bir dizi satış rakamında üretilen komisyonların hesaplanmasıyla gerçek dünyadaki bir örnekle açıklayacağız.Çok basit bir senaryo ile başlayacağız ve daha sonra problemin tek rasyonel çözümü VLOOKUP kullanmak olacak hale gelene kadar kademeli olarak daha karmaşık hale getireceğiz. Hayali şirketimizdeki ilk senaryo şöyledir: Bir satış elemanı belirli bir yılda 30.000 dolardan fazla satış oluşturursa, bu satışlarda kazanılan komisyon% 30'dur. Aksi takdirde komisyonları sadece% 20'dir.Şimdiye kadar bu oldukça basit bir çalışma sayfasıdır:
Bu çalışma sayfasını kullanmak için satış görevlisi, satış rakamlarını B1 hücresine girer ve B2 hücresindeki formül, B3 hücresinde kullanılan doğru alınıyor komisyon oranını hesaplarsatış görevlisinin ödediği toplam komisyon( B1 ve B2'nin basit bir çarpımıdır).
B2 hücresi, bu çalışma sayfasının ilginç kısmını içeriyor - hangi komisyon oranının kullanılacağına karar vermek için kullanılan formül: 'nin altındaki bir değeri 30.000 ABD Doları veya eşiğin 'nin üstündeki bir eşik değeri. Bu formül, IF adlı Excel işlevini kullanır. IF ile aşina olmayan okuyucular için şu şekilde çalışır:
IF( durumu, doğruysa değer, yanlış değeri)
Burada durumu true olarak değerlendirilen bir deyim veya yanlış .Yukarıdaki örnekte, durumu , "B1, B5'den küçük mü?" Olarak okunabilen B1
Gördüğünüz gibi, satış toplamı 20.000 USD kullanmak B2 hücresinde komisyon oranı% 20'dir.40.000 Dolar değerini girersek farklı bir komisyon oranı elde ederiz:
Dolayısıyla e-tablonuz çalışıyor.
Bunu daha karmaşık hale getirelim.İkinci bir eşik getirelim: Satış elemanı 40.000 dolardan fazla kazanıyorsa komisyon oranı% 40'a yükselir:
Gerçek dünyada anlaşılması kolay, ancak B2 hücresinde formülümüz daha da karmaşıklaşıyor. Formüle dikkatlice bakarsanız, orijinal IF işlevinin üçüncü parametresinin( yanlış olduğu takdirde değeri) kendi başına bir bütün IF fonksiyonu olduğunu göreceksiniz. Buna iç içe fonksiyon denir( bir fonksiyon içinde bir fonksiyon).Excel'de mükemmel geçerlidir( hatta işe yarıyor!), Ancak okumak ve anlamak daha zordur.
Bunun ve niçin çalıştığımızın somutlarına ve cıvatalarına girmeyeceğiz, aynı zamanda iç içe geçmiş fonksiyonların nüanslarını incelemeyeceğiz. Bu, genel olarak Excel'de değil VLOOKUP'da bir öğretici.
Her neyse, daha da kötüye gidiyor!50.000 dolardan fazla kazandıklarında% 50 komisyon alma hakkına sahipler ve 60.000 dolardan fazla kazanırsanız% 60 komisyon almaya karar verirlerse ne olur?
Şimdi B2 hücresindeki formül doğruyken hemen hemen okunamaz hale geldi. Fonksiyonların dört seviyeli derinlikte iç içe geçmiş olduğu yerde formüller yazmak zorunda kimse yoktur! Elbette daha basit bir yol olmalıdır mı?
Kesinlikle var. Kurtarmaya VLOOKUP!
Çalışma sayfasını biraz yeniden tasarlayalım. Tüm rakamları aynı tutacağız, ancak yeni bir şekilde organize edeceğiz, daha fazla tabular yolu:
Yeni bir Oran Tablosu 'nin, yukarıdaki eşikler dizisi ile tam olarak aynı şekilde çalıştığını kendiniz doğrulayacağız.
Kavramsal olarak, yapmak istediğimiz, ücret tablosundaki satış elemanının satış toplamını( B1'den) aramak ve ilgili komisyon oranını bize geri döndürmek için VLOOKUP'u kullanmaktır. Satış elemanının, aslında, ücret tablosundaki beş değerden birine( $ 0, $ 30,000, $ 40,000, $ 50,000 veya $ 60,000) olmayan olan satışları oluşturmuş olabileceğini unutmayın.34.998 dolarlık satışlar yaratmış olabilirler. Oran tablosunda 34,988 doların değil görüneceğini unutmamak gerekir. Bakalım VLOOKUP yine de problemimizi çözebilir mi. ..
B2 hücresini( formülümüzü koymak istediğimiz yer) seçtikten sonra Formülleri sekmesinden VLOOKUP işlevini ekledik:
VASÖRÜNE İşlev Argümanları kutusu görünür. Bağımsız değişkenleri( parametreleri) birer birer doldururuz, Lookup_value 'den başlayarak, bu durumda, B1 hücresindeki satış toplamıdır.İmleci, Lookup_value alanına yerleştirip, sonra da bir kez B1 hücresine tıkla:
Sonra, VLOOKUP'a bu verileri aramak için hangi tabloyu belirteceğimizi belirtmeliyiz. Bu örnekte tabii ki oran tablosu.İmleci Table_array alanına yerleştirdikten sonra, tüm oran tablosunu - başlıklarını başlıkları hariç tutarak vurguluyoruz:
Sonra, tablodaki sütunların formülümüzün bize geri dönmesini istediğimiz bilgileri içerdiğini belirtmeliyiz. Bu durumda tablonun ikinci sütununda bulunan komisyon oranını istiyoruz, bu nedenle bir 2 'yi Col_index_num alanına giriyoruz:
Son olarak, Range_lookup alanına bir değer giriyoruz.
Önemli: VLOOKUP'u kullanmanın iki yolunu ayıran bu alanı kullanmaktır. VLOOKUP'u bir veritabanı ile kullanmak için, bu son parametre olan Range_lookup , daima FALSE olarak ayarlanmalıdır, ancak bu VLOOKUP'un diğer kullanımı ile boş bırakmalı veya TRUE değerini girmeliyiz. VLOOKUP kullanırken, bu son parametre için doğru seçeneği seçmeniz çok önemlidir.
Açıkça belirtmek gerekirse, Range_lookup alanına true değerini gireceğiz. Varsayılan değer olduğu için boş bırakmak da iyi olur:
Tüm parametreleri tamamladık.Şimdi OK düğmesine tıklayın ve Excel, VLOOKUP formülümüzü bizim için oluşturur:
Birkaç farklı toplam satış tutarı denersek, formülü çalıştığından emin olabiliriz.
Sonuç
Range_lookup parametresi YANLIŞ olduğu ilk satılık VLOOKUP "veritabanı" sürümünde, ilk parametrede( Lookup_value ) geçirilen değer, veritabanında bulunmalıdır. Başka bir deyişle, tam bir eşleşme arıyoruz.
Ancak VLOOKUP'un bu diğer kullanımında mutlaka tam bir eşleşme arıyoruz. Bu durumda, "yeterince yakın yeter".Ama "yeterince yakın" tarafından ne demek istiyoruz? Bir örnek verelim: 34.998 dolarlık bir satış ücreti ararken, VLOOKUP formülümüz bize% 30'luk bir değer getirecektir, bu da doğru cevabıdır. Tablonun% 30'unu içeren sırayı neden seçti? Aslında bu durumda "yeterince yakın" anlamına mı gelir? Hassas olalım:
Range_lookup TRUE ( veya atlandı) olarak ayarlandığında, VLOOKUP 1 sütununa bakar ve ile Lookup_value parametresinden 'den büyük olmayan en yüksek değeri eşleştirir.
Ayrıca, bu sistemin çalışması için 'nin tablo sütun 1 'de artan düzende sıralanması gerektiğini unutmayın!
VLOOKUP ile uygulama yapmak isterseniz, bu makalede örneklenen dosya buradan indirilebilir.