İçerik
Kısa süre önce bir makaleyi en kullanışlı Excel işlevlerinden birine adadık: VPR ve gerekli bilgileri bir veritabanından bir çalışma sayfası hücresine çıkarmak için nasıl kullanılabileceğini gösterdi. Ayrıca fonksiyon için iki kullanım durumu olduğundan bahsetmiştik. VPR ve bunlardan yalnızca biri veritabanı sorgularıyla ilgilenir. Bu makalede, işlevi kullanmanın daha az bilinen başka bir yolunu öğreneceksiniz. VPR Excel'de.
Bunu henüz yapmadıysanız, işlevle ilgili son makaleyi okuduğunuzdan emin olun. VPR, çünkü aşağıdaki tüm bilgiler, ilk makalede açıklanan ilkelere zaten aşina olduğunuzu varsaymaktadır.
Veritabanlarıyla çalışırken, işlevler VPR Bulmak istediğimiz bilgileri (örneğin, bir ürün kodu veya müşteri kimlik numarası) tanımlamak için kullanılan benzersiz bir tanımlayıcı iletilir. Bu benzersiz kod veritabanında bulunmalıdır, aksi takdirde VPR bir hata bildirecektir. Bu yazıda, işlevin bu şekilde kullanılmasına bakacağız. VPRid veritabanında hiç bulunmadığında. sanki işlev VPR yaklaşık moda geçer ve bir şey bulmak istediğimizde bize hangi verilerin sağlanacağını seçer. Bazı durumlarda, tam olarak ihtiyaç duyulan şey budur.
Hayattan bir örnek. Görevi belirledik
Bu makaleyi gerçek hayattan bir örnekle açıklayalım – çok çeşitli satış ölçütlerine dayalı komisyon hesaplama. Çok basit bir seçenekle başlayacağız ve sonra sorunun tek rasyonel çözümü işlevi kullanmak olana kadar yavaş yavaş karmaşıklaştıracağız. VPR. Hayali görevimizin ilk senaryosu şu şekildedir: Bir satış elemanı bir yılda 30000 dolardan fazla satış yaparsa, komisyonu %30'dur. Aksi takdirde, komisyon sadece %20'dir. Bunu bir tablo şeklinde koyalım:
Satıcı, satış verilerini B1 hücresine girer ve B2 hücresindeki formül, satıcının bekleyebileceği doğru komisyon oranını belirler. Buna karşılık, elde edilen oran, satıcının alması gereken toplam komisyonu hesaplamak için B3 hücresinde kullanılır (sadece B1 ve B2 hücrelerini çarparak).
Tablonun en ilginç kısmı B2 hücresinde bulunur - bu, komisyon oranını belirleme formülüdür. Bu formül, adında bir Excel işlevi içerir. IF (EĞER). Bu fonksiyona aşina olmayan okuyucular için nasıl çalıştığını açıklayacağım:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Şart birinin değerini alan bir işlev argümanıdır DOĞRU KOD (DOĞRU) veya YANLIŞ (YANLIŞ). Yukarıdaki örnekte, B1 ifadesi
B1'in B5'ten küçük olduğu doğru mu?
Ya da farklı şekilde söyleyebilirsiniz:
Yıl için toplam satış tutarının eşik değerinden az olduğu doğru mu?
Bu soruyu cevaplarsak EVET (DOĞRU), ardından işlev döner değer doğruysa (eğer DOĞRU ise değer). Bizim durumumuzda bu, B6 hücresinin değeri, yani toplam satışlar eşiğin altında olduğunda komisyon oranı olacaktır. soruyu cevaplarsak YOK HAYIR (YANLIŞ) sonra döner yanlış ise değer (YANLIŞ ise değer). Bizim durumumuzda bu, B7 hücresinin değeridir, yani toplam satışlar eşiğin üzerinde olduğunda komisyon oranıdır.
Gördüğünüz gibi, 20000$'lık toplam satışları alırsak, B2 hücresinde %20 komisyon oranı elde ederiz. 40000$'lık bir değer girersek, komisyon oranı %30 değişecektir:
Tablomuz bu şekilde çalışıyor.
Görevi karmaşıklaştırıyoruz
İşleri biraz daha zorlaştıralım. Başka bir eşik belirleyelim: Satıcı 40000 dolardan fazla kazanırsa komisyon oranı %40'a yükselir:
Her şey basit ve açık görünüyor, ancak B2 hücresindeki formülümüz gözle görülür şekilde daha karmaşık hale geliyor. Formüle yakından bakarsanız, işlevin üçüncü argümanının olduğunu göreceksiniz. IF (IF) başka bir tam teşekküllü işleve dönüştü IF (EĞER). Bu yapıya fonksiyonların iç içe yerleştirilmesi denir. Excel bu yapılara memnuniyetle izin verir ve hatta çalışırlar, ancak bunları okumak ve anlamak çok daha zordur.
Neden ve nasıl çalıştığının teknik ayrıntılarına girmeyeceğiz ve iç içe işlevler yazmanın nüanslarına girmeyeceğiz. Sonuçta, bu işleve adanmış bir makaledir. VPR, tam bir Excel rehberi değil.
Durum ne olursa olsun, formül daha da karmaşıklaşıyor! 50 dolardan fazla satış yapan satıcılar için %50000 komisyon oranı için başka bir seçenek sunsak ne olur? Ve eğer biri 60000 dolardan fazla sattıysa, %60 komisyon ödeyecek mi?
Artık B2 hücresindeki formül, hatasız yazılmış olsa bile tamamen okunamaz hale geldi. Projelerinde 4 seviyeli iç içe formüller kullanmak isteyen çok az kişi olduğunu düşünüyorum. Daha kolay bir yolu olmalı?!
Ve böyle bir yol var! İşlev bize yardımcı olacak VPR.
Sorunu çözmek için DÜŞEYARA işlevini uyguluyoruz
Masamızın tasarımını biraz değiştirelim. Tüm alanları ve verileri aynı tutacağız, ancak bunları yeni ve daha kompakt bir şekilde düzenleyeceğiz:
Bir dakikanızı ayırın ve yeni tablonun Oran Tablosu önceki eşik tablosuyla aynı verileri içerir.
Ana fikir, işlevi kullanmaktır. VPR tabloya göre istenilen tarife oranını belirlemek Oran Tablosu satış hacmine göre değişir. Satıcının, tablodaki beş eşikten birine eşit olmayan bir miktarda mal satabileceğini lütfen unutmayın. Örneğin, 34988 dolara satabilir, ancak böyle bir miktar yoktur. Bakalım işlevi nasıl VPR böyle bir durumla başa çıkabilir.
DÜŞEYARA işlevi ekleme
B2 hücresini seçin (formülümüzü eklemek istediğimiz yer) ve bulun VLOOKUP (DÜŞEYARA) Excel İşlevleri Kitaplığında: Formüller (formüller) > İşlev Kütüphanesi (İşlev Kitaplığı) > Arama ve Referans (Referanslar ve diziler).
Bir iletişim kutusu belirir İşlev Argümanları (İşlev argümanları). Argümanların değerlerini tek tek dolduruyoruz. aranan_değer (Arama_değeri). Bu örnekte, bu, B1 hücresinden yapılan toplam satış tutarıdır. İmleci alana getirin aranan_değer (Arama_değeri) ve B1 hücresini seçin.
Ardından, işlevleri belirtmeniz gerekir VPRveri nerede aranır. Örneğimizde bu bir tablodur. Oran Tablosu. İmleci alana getirin Masa dizisi (Tablo) ve tüm tabloyu seçin Oran Tablosubaşlıklar hariç.
Ardından, formülümüzü kullanarak verileri hangi sütundan çıkaracağımızı belirlememiz gerekiyor. Tablonun ikinci sütununda yer alan komisyon oranı ile ilgileniyoruz. Bu nedenle, argüman için Süt_indis_sayısı (Column_number) 2 değerini girin.
Ve son olarak, son argümanı sunuyoruz - Menzil araması (Aralık_bak).
Önemli: işlevi uygulamanın iki yolu arasındaki farkı yaratan bu argümanın kullanımıdır. VPR. Veritabanlarıyla çalışırken, argüman Menzil araması (range_lookup) her zaman bir değere sahip olmalıdır YANLIŞ (YANLIŞ) tam bir eşleşme aramak için. Fonksiyonu kullanmamızda VPR, bu alanı boş bırakmalı veya bir değer girmeliyiz DOĞRU KOD (DOĞRU). Bu seçeneği doğru seçmek son derece önemlidir.
Daha açık hale getirmek için tanıtacağız DOĞRU KOD (DOĞRU) alanda Menzil araması (Aralık_bak). Alanı boş bırakırsanız, bu bir hata olmayacaktır, çünkü DOĞRU KOD varsayılan değeridir:
Tüm parametreleri doldurduk. şimdi basıyoruz OK, ve Excel bizim için bir işlevle bir formül oluşturur VPR.
Toplam satış tutarı için birkaç farklı değerle deneme yaparsak, formülün doğru çalıştığından emin oluruz.
Sonuç
fonksiyon ne zaman VPR veritabanları ile çalışır, argüman Menzil araması (range_lookup) kabul etmeli YANLIŞ (YANLIŞ). Ve girilen değer aranan_değer (Arama_değeri) veritabanında bulunmalıdır. Başka bir deyişle, tam bir eşleşme arıyor.
Bu makalede incelediğimiz örnekte, tam bir eşleşme elde etmeye gerek yoktur. Bu durum, fonksiyon VPR istenen sonucu döndürmek için yaklaşık moda geçmelidir.
Örneğin: Satış hacmi 34988$ olan bir satış elemanı için komisyon hesaplamasında hangi oranın kullanılacağını belirlemek istiyoruz. İşlev VPR bize kesinlikle doğru olan %30 değerini döndürür. Fakat formül neden %30 veya %20 değil de tam olarak %40 içeren satırı seçti? Yaklaşık arama ile ne kastedilmektedir? Açık olalım.
Argüman ne zaman Menzil araması (interval_lookup) bir değere sahip DOĞRU KOD (DOĞRU) veya atlanmış, işlev VPR ilk sütunu yineler ve arama değerini aşmayan en büyük değeri seçer.
Önemli nokta: Bu şemanın çalışması için tablonun ilk sütununun artan düzende sıralanması gerekir.