01/03
Excel'in VLOOKUP ile Verilere Yaklaşan Eşleşmeleri Bul
VLOOKUP İşlevi Nasıl Çalışır?
Excel'in VLOOKUP fonksiyonu , dikey arama için , veri veya veri tabanı tablosunda bulunan belirli bilgileri aramak için kullanılabilir.
VLOOKUP normalde çıktı olarak tek bir veri alanı döndürür. Bu nasıl:
- VLOOKUP'a , veri tablosunun istenen verileri aramasını sağlayan satır veya kayda bildiren bir ad veya lookup_value sağlarsınız.
- Aradığınız verilerin col_index_num olarak bilinen sütun numarasını sağladınız
- İşlev, veri tablosunun ilk sütununda lookup_value değerini arar.
- Ardından, VLOOKUP, aradığınız bilgileri, verilen kayıt numarasını kullanarak aynı kaydın başka bir alanından bulur ve geri gönderir.
Verileri Önce Sıralama
Her zaman gerekli olmamasına rağmen, VLOOKUP'un sıralama anahtarı için aralığın ilk sütunu kullanılarak artan sırada arama yaptığı veri aralığını sıralamak genellikle en iyisidir.
Veriler sıralanmazsa, VLOOKUP yanlış bir sonuç verebilir.
VLOOKUP İşlevinin Sözdizimi ve Bağımsız Değişkenleri
Bir fonksiyonun sözdizimi , fonksiyonun düzenini ifade eder ve fonksiyonun adını, parantezlerini ve argümanlarını içerir .
VLOOKUP işlevinin sözdizimi şöyledir:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
arama _value - (gerekli) aranacak değer - yukarıdaki resimde satılan miktar gibi
table_array - (gerekli) bu VLOOKUP'un, sonra bulunduğunuz bilgileri bulmak için aradığı verilerin tablosu.
- Table_array en az iki sütun sütunu içermelidir
- İlk sütun normalde lookup_value değerini içerir
col_index_num - İstediğiniz değerin sütun numarası.
- Numaralandırma, 1. sütun olarak search_key sütunuyla başlar
- Col_index_num , table_array argümanında seçilen sütun sayısından büyük bir sayıya ayarlanırsa, #REF ! hata işlev tarafından döndürüldü
range_lookup - (isteğe bağlı), aralığın artan sırada sıralanıp sıralanmadığını belirtir.
- İlk sütundaki veriler, sıralama anahtarı olarak kullanılır
- Bir Boole değeri - DOĞRU veya YANLIŞ, kabul edilebilir değerlerdir
- Gerekirse, değer varsayılan olarak TRUE olarak ayarlanır
- TRUE olarak ayarlanırsa veya atlanırsa ve aralığın ilk sütunu artan sırada sıralanmazsa, yanlış bir sonuç ortaya çıkabilir
- TRUE olarak ayarlandıysa veya atlandıysa ve arama için tam bir eşleşme bulunamazsa, boyut veya değerden küçük olan en yakın eşleşme, search_key olarak kullanılır
- FALSE olarak ayarlanırsa, VLOOKUP yalnızca arama için tam bir eşleşmeyi kabul eder. Birden çok eşleşen değer varsa, ilk eşleşen değer döndürülür
- FALSE olarak ayarlandıysa ve search_key için eşleşme değeri bulunamadıysa, işlev tarafından # N / A hatası döndürülür
Örnek: Satın alınan miktar için indirim oranını bulun
Yukarıdaki görüntüdeki örnek, satın alınan öğelerin miktarına bağlı olarak değişen indirim oranını bulmak için VLOOKUP işlevini kullanır.
Örnek, 19 maddenin satın alınması için indirimin% 2 olduğunu göstermektedir. Bunun nedeni, Miktar sütununun değer aralıklarını içermesidir. Sonuç olarak, VLOOKUP tam bir eşleşme bulamıyor. Bunun yerine, doğru iskonto oranını döndürmek için yaklaşık bir eşleşme bulunmalıdır.
Yaklaşık eşleşmeleri bulmak için:
- tablo_dizisi içindeki verileri artan düzende sıralayın;
- range_lookup argümanını TRUE olarak ayarlayın
Örnekte, satın alınan mal miktarları için indirimi bulmak amacıyla VLOOKUP işlevini içeren aşağıdaki formül kullanılır.
= DÜŞEYARA (C2 $ c $ 5: $ D $ 8,2, TRUE)
Bu formül sadece bir çalışma sayfası hücresine yazılabilir olsa da, aşağıda listelenen adımlarda kullanıldığı gibi başka bir seçenek de argümanlarını girmek için işlevin iletişim kutusunu kullanmaktır.
- İletişim kutusunu kullanmak genellikle bir işlevin argümanlarını doğru şekilde girmeyi kolaylaştırır.
VLOOKUP İletişim Kutusunu Açma
Yukarıdaki resimde görüntülenen VLOOKUP işlevini hücre B2'ye girmek için kullanılan adımlar şunlardır:
- Etkin hücre yapmak için B2 hücresine tıklayın - VLOOKUP işlevinin sonuçlarının görüntülendiği yer
- Formüller sekmesine tıklayın.
- İşlev açılır listesini açmak için şeritten Arama ve Referans'ı seçin
- İşlev iletişim kutusunu açmak için listede VLOOKUP'a tıklayın.
02/03
Excel'in VLOOKUP İşlevinin Argümanlarını Girme
Hücre Referanslarına Yönlendirme
VLOOKUP işlevi için argümanlar, yukarıdaki resimde gösterildiği gibi iletişim kutusunun ayrı satırlarına girilir.
Argümanlar olarak kullanılacak hücre referansları doğru çizgiye yazılabilir veya aşağıdaki adımlarda yapıldığı gibi, fare imleciyle istenen hücre aralığını vurgulamayı içeren işaret, bunları iletişim kutusuna girmek için kullanılabilir. .
İşaretleme kullanmanın avantajları şunlardır:
- Yazmaktan daha hızlıdır;
- Doğru hücre referanslarına daha az hata yapılır.
Argümanlar ile Bağıl ve Mutlak Hücre Referanslarını Kullanma
Aynı veri tablosundan farklı bilgiler vermek için birden çok VLOOKUP kopyasının kullanılması nadir değildir. Bunu daha kolay hale getirmek için, VLOOKUP genellikle bir hücreden diğerine kopyalanabilir. Fonksiyonlar diğer hücrelere kopyalandığında, fonksiyonun yeni konumuna göre sonuçtaki hücre referanslarının doğru olmasına dikkat edilmelidir.
Yukarıdaki resimde, dolar işaretleri ( $ ), mutlak hücre referansları olduklarını belirten table_array argümanı için hücre referanslarını çevreler; bu, işlev başka bir hücreye kopyalandığında değişmeyeceği anlamına gelir. Bu, VLOOKUP'un çoklu kopyalarının hepsinin, bilgi kaynağıyla aynı veri tablosunu referans gösterdiği için istenir.
Diğer yandan , lookup_value için kullanılan hücre referansı , göreli bir hücre başvurusu yapan dolar işaretleri ile çevrelenmez. Göreceli hücre referansları, yeni konumlarını başvurdukları verilere göre yansıtacak şekilde kopyalandığında değişir.
İşlev Argümanlarına Girme
- VLOOKUP iletişim kutusundaki Lookup _value satırına tıklayın.
- Bu hücre referansını search_key argümanı olarak girmek için çalışma sayfasındaki C2 hücresini tıklayın.
- İletişim kutusunun Table_array satırını tıklayın.
- Bu aralığı Tablo_dizisi argümanı olarak girmek için çalışma sayfasındaki C5 ila D8 hücrelerini vurgulayın - tablo başlıkları dahil değildir
- Aralığı mutlak hücre referanslarına değiştirmek için klavyedeki F4 tuşuna basın
- İletişim kutusunun Col_index_num satırına tıklayın
- Bu satırdaki 2 değerini Col_index_num argümanı olarak yazın, çünkü iskonto oranları Table_array argümanının 2. sütununda bulunur.
- İletişim kutusunun Range_lookup satırına tıklayın
- Range_lookup argümanı olarak True kelimesini yazın
- İletişim kutusunu kapatmak ve çalışma sayfasına geri dönmek için klavyede Enter tuşuna basın
- Yanıtın% 2'si (satın alınan miktarın iskonto oranı) çalışma sayfasının D2 hücresinde görünmelidir.
- D2 hücresine tıkladığınızda, çalışma sayfasının üstündeki formül çubuğunda tam işlev = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) görünür.
Neden VLOOKUP% 2 Sonuç olarak geri döndü
- Örnekte, Miktar sütunu, 19'un search_key değeri için tam bir eşleşme içermiyor.
- Is_sorted argümanı DOĞRU olarak ayarlandığından, VLOOKUP, search_key değerine yaklaşık bir eşleşme bulacaktır .
- Halen, search_key değerinden 19 daha küçük olan en yakın değer 11'dir.
- Bu nedenle, VLOOKUP, 11 içeren satırdaki indirim yüzdesini arar ve sonuç olarak% 2'lik bir indirim oranı döndürür.
03/03
Excel VLOOKUP Çalışmıyor: # N / A ve #REF Hataları
VLOOKUP Hata İletileri
Aşağıdaki hata iletileri VLOOKUP ile ilişkilidir.
A # N / A ("değer mevcut değil") Hata Görüntüleniyorsa:
- Arama değeri , aralık argümanının ilk sütununda bulunamadı
- Table_array argümanı yanlış. Örneğin, argüman, aralığın sol tarafında boş sütun içerebilir
- Range_lookup argümanı FALSE olarak ayarlandı ve search_key argümanı için tam bir eşleşme, aralığın ilk sütununda bulunamadı
- Range_lookup argümanı TRUE olarak ayarlandı ve aralığın ilk sütunundaki tüm değerler search_key'den daha büyük.
Bir #REF! ("aralık dışı referans") Hata Görüntüleniyorsa:
- Col_index_num bağımsız değişkeni, Table_array'daki sütunların sayısından büyüktür.