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 üç tanesi 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

Diyelim ki veri içeren iki tablomuz var - kredi tablosu ve teminat tablosu :

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

Çözüm

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

Öyleyse fonksiyonu kullanalım VLOOKUP . Girileceği hücreyi seçin ( E2 ) ve formül sihirbazını açın (menü Insert - Function ). Kategoride (Arama ve Referans) işlevi bul VLOOKUP ve bas TAMAM . İşlev bağımsız değişkenlerini girmek için bir pencere açılacaktır:

Bunları tek tek doldurun:

• Lookup Value - fonksiyonun ön hazırlık tablosunun en sol sütununda arayacağı anlaşma numarası. Bizim durumumuzda - anlaşma numarası "KF568" hücreden C2 .
• Table Array -gerekli değerleri alacağımız tablo tedarik tablomuzdur. Referans için bir aralık kullanacağız $G$2:$H$11 (aralık bir dolar işaretiyle sabitlenmiştir ($) , böylece formül kopyalanırken aşağıya doğru hareket etmez).
• Column index number - 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 Lookup - bu alana yalnızca iki değer girebiliriz: TRUE veya FALSE:
  
  1) Bir değer girersek 0 veya FALSE , bu aslında yalnızca aramaya izin verildiği anlamına gelir tam bir eşleşme ile yani işlev, ön hazırlık tablosunda sözleşme numarasını bulamazsa (mevcut değilse), bir hata görüntülenecektir #N/A (veri yok).
  
  2) Bir değer girersek 1 veya TRUE , bu yalnızca kesin olarak değil aynı zamanda arama yapılmasına da izin verildiği anlamına gelir. yaklaşık tesadüf . Çoğu durumda, bu tür yaklaşık bir değişiklik yanlış sonuca neden olabilir. VLOOKUP diğer anlaşmaların değerlerinin yerine geçecektir. 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).

Basmak kalıyor TAMAM ve girilen işlevi tüm sütuna kopyalayın.

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

İşlev VLOOKUP bir hata döndürür (#Yok) eğer:

1. Tam aramaya izin verilir (argüman Range Lookup=0) ve aranan değer tabloda yok (Table) .
2. İzin verilen yaklaşık aramaya (Range Lookup=1), ancak tabloda (Table) Değer aradığımız dosyada isimler artan şekilde sıralanmaz.
3. İşlem numarasını aldığımız hücrenin formatı (örneğin, C2 bizim durumumuzda) ve ilk sütunun hücre formatı (G2:G11) tablolar 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 işlevleri kullanabilirsiniz. VALUE Ve TEXT veri formatlarını dönüştürmek için. Şuna benziyor:
   =VLOOKUP(TEXT(C2);$G$2:$H$11;0) .
4. 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 metin işlevini kullanabilirsiniz. (TRIM) Ve (CLEAN) bunları kaldırmak için:
   =VLOOKUP(TRIM(CLEAN(C2));$G$2:$H$11;0) .

Hata bastırma

Hata mesajını bastırmak için (#Yok) , işlev tam bir eşleşme bulamadığında işlevi kullanabilirsiniz. IFERROR . Bu fonksiyon bir hata olup olmadığını kontrol eder (#Yok) işin sonucudur VLOOKUP ve eğer öyleyse, boş bir bant çıktısı alır ( "" ) veya sıfır değilse - işin sonucu VLOOKUP .

Konuyla ilgili makaleler:

• Geliştirilmiş işlev VLOOKUP (VLOOKUP2)
• VLOOKUP iki duruma göre kombinasyon için (VLOOKUP3)
• Büyük tabloların hızlı birleştirilmesi (VLOOKUP2D)