Excel'de VLOOKUP, bölüm 2 VLOOKUP'ı veritabanı olmadan kullanma

Son bir makalede, denilen Excel işlevini tanıttık VLOOKUP ve yerel bir çalışma sayfasındaki bir veri tabanından bir hücreye bilgi almak için nasıl kullanılabileceğini açıkladı. Bu yazıda VLOOKUP'ın iki kullanımı olduğunu ve bunlardan sadece birinin veri tabanlarını sorguladığını belirtti. Bu yazıda, VLOOKUP serisinin ikincisi ve finali olarak, VLOOKUP işlevi için bu daha az bilinen kullanımı inceliyoruz..

Henüz yapmadıysanız, lütfen ilk VLOOKUP makalesini okuyun - bu makale, bu makalede açıklanan kavramların çoğunun zaten okuyucu tarafından bilindiğini varsayar.

Veritabanlarıyla çalışırken, VLOOKUP, veritabanında hangi veri kaydını bulmak istediğimizi belirlemeye yarayan “benzersiz bir tanımlayıcı” 'dan geçirilir (örneğin bir ürün kodu veya müşteri kimliği). Bu benzersiz tanımlayıcı şart veritabanında var, aksi takdirde VLOOKUP bize bir hata döndürür. Bu yazıda, tanımlayıcının veritabanında bulunması gerekmeyen VLOOKUP'ı kullanmanın bir yolunu inceleyeceğiz. Neredeyse VLOOKUP aradığımız veriyi geri getirmek için “yeterince yakın, yeterince iyi” bir yaklaşım benimseyebilir. Bazı durumlarda bu kesinlikle ihtiyacımız olan.

Bu makaleyi gerçek dünyadan bir örnekle - bir dizi satış rakamıyla oluşturulan komisyonları hesaplamakla - açıklayacağız. Çok basit bir senaryo ile başlayacağız ve daha sonra problemin tek rasyonel çözümü VLOOKUP'ı kullanana kadar aşamalı olarak daha karmaşık hale getireceğiz. Hayali şirketimizdeki ilk senaryo şöyle çalışır: Eğer bir satışçı belirli bir yılda 30.000 $ değerinde satış yaratırsa, bu satışlardan elde ettikleri komisyon% 30'dur. Aksi halde komisyonları sadece% 20'dir. Şimdiye kadar bu oldukça basit bir çalışma sayfası:

Bu çalışma sayfasını kullanmak için, satış temsilcisi satış rakamlarını B1 hücresine girer ve B2 hücresindeki formül, satış görevlisinin sahip olduğu toplam komisyonu hesaplamak için B3 hücresinde kullanılan hak sahibi komisyon oranını hesaplar. basit bir B1 ve B2 çarpımıdır).

B2 hücresi bu çalışma sayfasının tek ilginç bölümünü içerir - hangi komisyon oranının kullanılacağına karar verme formülü: altında 30.000 ABD Doları eşi veya yukarıdaki eşik. Bu formül denilen Excel işlevini kullanır EĞER. IF'ye aşina olmayan okuyucular için şöyle çalışır:

EĞER(koşul, doğru ise değer, yanlış ise değer)

Nerede şart her ikisine de değerlendirilen bir ifadedir doğru veya yanlış. Yukarıdaki örnekte şart ifade B1, “B1, B5'ten daha mı az?” olarak okunabilir veya başka bir ifadeyle, “Toplam satış eşikten daha mı düşük”. Bu sorunun cevabı “evet” ise (doğru), sonra true ise değer işlevin parametresi, yani B6 Bu durumda - eğer satışlar toplamsa komisyon oranı altında eşik. Eğer sorunun cevabı “hayır” ise (yanlış), o zaman false değeri işlevin parametresi, yani B7 Bu durumda - eğer satışlar toplamsa komisyon oranı yukarıdaki eşik.

Görebildiğiniz gibi, toplam 20.000 ABD Doları satış kullanmak bize B2 hücresinde% 20 komisyon oranı verir. 40.000 dolarlık bir değer girersek, farklı bir komisyon oranı alırız:

Yani elektronik tablomuz çalışıyor.

Daha karmaşık hale getirelim. İkinci bir eşik verelim: Satış elemanı 40.000 ABD Dolarından fazla kazanırsa, komisyon oranları% 40'a yükselir:

Gerçek dünyada anlaşılması yeterince kolay, ancak B2 hücresindeki formülümüz daha karmaşık hale geliyor. Formüle yakından bakarsanız, orijinal IF işlevinin üçüncü parametresinin ( false değeri) şimdi kendi başına bir IF işlevidir. Bu bir denir iç içe geçmiş işlev (bir işlev içindeki bir işlev). Excel'de mükemmel şekilde geçerlidir (hatta çalışır!), Ancak okunması ve anlaşılması zordur.

Bunun nasıl ve neden işe yaradığının somun ve cıvatalarına girmeyeceğiz, iç içe geçmiş fonksiyonların nüanslarını da inceleyemeyiz. Bu, genel olarak Excel’de değil, VLOOKUP’ta bir eğitim.

Neyse, daha da kötüleşiyor! Peki ya 50.000 $ 'dan fazla para kazanırlarsa, o zaman% 50 komisyon alma hakkına sahip olduklarına, ve 60.000 $' dan fazla para kazanmalarına rağmen% 60 komisyon alma hakkına sahip olduğumuzda?

Şimdi B2 hücresindeki formül doğru olsa da, neredeyse okunaksız hale geldi. Hiç kimse, fonksiyonların dört seviyeli derinliklere yerleştirildiği formüller yazmak zorunda olmamalıdır! Elbette daha basit bir yol olmalı?

Kesinlikle var. VLOOKUP kurtarmaya!

Biraz çalışma sayfasını yeniden tasarlayalım. Hepimiz aynı rakamları tutacağız, ancak yeni bir şekilde düzenleyelim, daha yassı yol:

Bir dakikanızı ayırın ve kendiniz için doğrulayın yeni Puan Tablosu tam olarak yukarıdaki eşik serileriyle aynı şekilde çalışır.

Kavramsal olarak, yapmak üzere olduğumuz şey, satış görevlisinin satış tablosundaki (B1'den) satış tablosuna bakmak ve ilgili komisyon oranını bize geri vermek için VLOOKUP'ı kullanmaktır. Satış görevlisinin, gerçekten satış amaçlı satışlar yapmış olabileceğini unutmayın. değil Oran tablosundaki beş değerden biri (0, 30,000, 40,000, 50,000 veya 60,000 ABD Doları). 34,988 $ satış yapmış olabilirler. 34,988 ABD Dolarının değil Ücret tablosunda belirir. Bakalım VLOOKUP yine de sorunumuzu çözebilecek mi…

B2 hücresini seçtik (formülümüzü koymak istediğimiz konum) ve ardından VLOOKUP işlevini Formüller sekmesi:

İşlev Argümanları VLOOKUP kutusu görünür. Argümanları (parametreleri) birer birer doldururuz, lookup_value, ki bu durumda B1 hücresinden yapılan satışların toplamıdır. İmleci lookup_value alanını ve ardından B1 hücresini bir kez tıklayın:

Daha sonra VLOOKUP'a bu verilere bakmak için hangi tabloyu belirtmemiz gerekir. Bu örnekte, bu tabiki oran tablosu. İmleci Masa dizisi alanını işaretleyin ve ardından tüm ücret tablosunu vurgulayın - başlıklar hariç:

Daha sonra, tablodaki hangi sütunun formülümüzün bize geri dönmesini istediğimiz bilgileri içerdiğini belirtmeliyiz. Bu durumda, tablodaki ikinci sütunda bulunan komisyon oranını istiyoruz; 2 içine sütun_indis_sayısı alan:

Sonunda biz bir değer girin Menzil araması alan.

Önemli: VLOOKUP'ı kullanmanın iki yolunu farklılaştıran bu alanın kullanılmasıdır. VLOOKUP'ı veritabanında kullanmak için bu son parametre, Menzil araması, daima olarak ayarlanmalı YANLIŞ, ancak VLOOKUP’ın bu diğer kullanımında ya boş bırakmalı ya da değerini girmeliyiz DOĞRU. VLOOKUP kullanırken, bu son parametre için doğru seçimi yapmanız hayati önem taşır..

Açık olması için, değerini gireceğiz doğru içinde Menzil araması alan. Varsayılan değer olduğu için boş bırakmanız da iyi olur:

Tüm parametreleri tamamladık. Şimdi tıklayın tamam düğmesi ve Excel bizim için VLOOKUP formülünü oluşturuyor:

Birkaç farklı satış toplam tutarı denersek, formülün çalıştığını kendimiz tatmin edebiliriz.

Sonuç

VLOOKUP’ın “veritabanı” sürümünde, Menzil araması parametre YANLIŞ, ilk parametrede geçen değer (lookup_value) şart Veritabanında mevcut olmak. Başka bir deyişle, tam bir eşleşme arıyoruz.

Ancak VLOOKUP'ın bu diğer kullanımında, mutlaka tam bir eşleşme aramıyoruz. Bu durumda, “yeterince yakın yeterli”. Fakat “yeterince yakın” ile ne kastediyoruz? Bir örnek kullanalım: Toplam 34.988 ABD Doları tutarındaki satışlarda komisyon oranı ararken, VLOOKUP formülümüz bize% 30'luk bir değer döndürecektir, bu doğru cevaptır. Tablodaki% 30'u içeren satırı neden seçti? Aslında, bu durumda “yeterince yakın” ne anlama geliyor? Kesin olalım:

Ne zaman Menzil araması ayarlandı DOĞRU (veya atlandı), VLOOKUP sütun 1’de görünecek ve eşleşecektir daha büyük olmayan en yüksek değer lookup_value parametre.

Bu sistemin çalışması için not etmek de önemlidir., Tablo, sütun 1'de artan düzende sıralanmalıdır!

VLOOKUP ile pratik yapmak istiyorsanız, bu makalede gösterilen örnek dosya buradan indirilebilir..