Tabloları Birleştir (VLOOKUP)

VLOOKUP nedir?

Yerleşik VLOOKUP işlevi, Excel'deki en güçlü işlevlerden biridir. Excel'de SUM ve AVERAGE'den sonra en popüler üçü arasında yer alıyor. Görevi, veri tablosunda istenen değeri bulmak ve belirtilen hücrede görüntülemektir.

İşlev sözdizimi, bir arama değeri (ne aradığınızı), bir veya daha fazla sütun içeren bir tablo (nereye bakılacağını), bir sütun dizin numarası (hangi sütundan veri döndürüleceği) ve bir arama türünü (ek bir bağımsız değişken) içerir. yaklaşık veya kesin bir sonuç seçmenizi sağlar).

VLOOKUP işlevi genellikle finansal analiz, veri yönetimi ve veritabanlarıyla çalışmak için kullanılır. Bu özelliği kullanarak kullanıcılar büyük bir veri kümesindeki bilgileri hızlı ve kolay bir şekilde bulabilir, bu da zamandan tasarruf sağlayabilir ve doğruluğu artırabilir.

VLOOKUP işlevi değerleri dikey olarak, yani satırlar arasında arar (bu, ilk V - Dikey harfiyle gösterilir). Excel'de, benzer bir işlevselliğe sahip olan ancak sütunlara göre yatay (H - Yatay) arama yapan benzer bir HLOOKUP işlevi de vardır.

Bir sorun örneği

Verileri içeren iki tablomuz olduğunu varsayalım: bir kredi tablosu ve bir teminat tablosu:

Daha fazla raporlama için teminatı sözleşme numarasına göre kredi tablosuna otomatik olarak eklememiz gerekiyor.

Çözüm

Arama ve referans kategorisindeki standart işlevler kümesindeki Excel'de bir VLOOKUP işlevi vardır. Bu fonksiyon, yukarıdan aşağıya doğru belirtilen tablonun (teminat tablosu) en sol sütununda belirtilen değeri (örneğimizde sözleşme numarası) arar ve bularak bitişik hücrenin değerini (kredi teminat türü) görüntüler.). Şematik olarak fonksiyonun işleyişi şuna benzer:

O halde VLOOKUP fonksiyonunu kullanalım. Girileceği hücreyi seçin (E2) ve formül sihirbazını açın (menü Insert - Function). (Arama ve Referans) kategorisinde VLOOKUP işlevini bulun ve Tamam'a tıklayın. İşlev bağımsız değişkenlerini girmek için bir pencere açılacaktır:

Bunları tek tek doldurun:

Arama Value- işlevin temel hazırlık tablosunun en sol sütununda arayacağı anlaşmanın numarası. Bizim durumumuzda - C2 hücresinden "KF568" anlaşma numarası.
Table Array-gerekli değerleri alacağımız tablo tedarik tablomuzdur. Referans olarak $G$2:$H$11 aralığını kullanacağız (bu aralık bir dolar işaretiyle ($) sabitlenmiştir, böylece formül kopyalanırken en alta gitmez).
Column dizin numarası- provizyonun adını alacağımız provizyon tablosundaki sütunun seri numarası (harf değil!). Provizyon tablosunun ilk sütununda sıra numarası 1 olduğundan provizyon tipini ikinci sütundan (2 numara) almamız gerekiyor.
Range Arama- bu alana yalnızca iki değer girebiliriz: TRUE veya FALSE:

1) 0 veya FALSE değerini girersek bu aslında aramaya yalnızca izin verildiği anlamına gelir tam bir eşleşme ileyani işlev, ön hazırlık tablosunda anlaşma numarasını bulamazsa (mevcut değilse), #N/A (veri yok) hatası görüntülenecektir.

2) 1 veya TRUE değerini girersek, bu sadece tam aramaya izin verildiği anlamına gelmez, aynı zamanda yaklaşık tesadüf. Çoğu durumda, bu yaklaşık ikame, VLOOKUP diğer işlemlerin değerlerini değiştirdiğinde yanlış sonuçlara neden olabilir. Bu nedenle, yaklaşık aramayı yalnızca sayısal değerler için kullanmalısınız (önceden artan sırada sıralanmıştır).

Tamam'a basmak ve girilen işlevi tüm sütuna kopyalamak kalır.

VLOOKUP işlevinin dezavantajları

Ana dezavantaj, istenen değerin aranmasının yalnızca verilen aralığın ilk sütununda gerçekleştirilebilmesi ve fonksiyonun istenen değeri yalnızca sağdaki sütunlardan döndürebilmesidir.

VLOOKUP'nin ikinci dezavantajı, arama tablosuna bir sütun eklerseniz veya kaldırırsanız işlevin çalışmayı durdurmasıdır. Eklenen veya silinen bir öğe formülün sonucunu değiştirir çünkü işlev sözdizimi, veri çıkarmak istediğiniz aralığın tamamını ve belirli sütun numarasını belirtmenizi gerektirir.

Ayrıca, VLOOKUP işlevinin arama uzunluğu sınırı 255 karakterdir, aksi takdirde #VALUE hatası döndürülür!

İşlevle çalışırken sorunlar

VLOOKUP işlevi aşağıdaki durumlarda bir hata (#N/A) döndürür:

Tam aramaya izin verilir (argüman Range Arama=0) ve aranan değer tabloda bulunmuyor (Table).
İzin verilen yaklaşık aramaya (Range Arama=1), ancak değerleri aradığımız tabloda (Table) adlar artan sırada sıralanmıyor.
İşlem numarasını aldığımız hücrenin formatı (örneğin bizim durumumuzda C2) ile tablonun ilk sütunundaki hücrenin formatı (G2:G11) farklıdır (örneğin sayısal ve metin). Bu durum özellikle metinsel adlar (hesap numaraları, kimlik kodları, tarihler vb.) yerine sayısal kodlar kullanıldığında tipiktir. Bu durumda veri formatlarını dönüştürmek için VALUE ve TEXT işlevlerini kullanabilirsiniz. Şuna benziyor:
=VLOOKUP(TEXT(C2);$G$2:$H$11;0).
Kod boşluklar ve yazdırılmayan görünmez karakterler (bant beslemeleri vb.) içerdiğinden işlev bir değer bulamıyor. Bu durumda bunları kaldırmak için (TRIM) ve (CLEAN) metin işlevini kullanabilirsiniz:
=VLOOKUP(TRIM(CLEAN(C2));$G$2:$H$11;0).

Hata bastırma

İşlev tam bir eşleşme bulamadığında hata iletisini (#N/A) gizlemek için IFERROR işlevini kullanabilirsiniz. Bu işlev, hatanın (#N/A) VLOOKUP sonucu olup olmadığını kontrol eder ve eğer öyleyse, boş bir dize ("") veya null döndürür, aksi takdirde VLOOKUP sonucunu döndürür.

Konuyla ilgili makaleler: