Büyük tabloların hızlı birleştirilmesi (VLOOKUP2D)

Tanım

İki büyük tabloyu sütunlar ve satırlarla hızlı bir şekilde nasıl birleştireceğimizi düşünelim, yani tek bir parametreye göre (VLOOKUP veya HLOOKUP işlevleri olarak) değil, aynı anda iki parametreye göre (INDEX ve MATCH işlevlerini kullanarak) seçim yapın.

VLOOKUP işlevine veya onun yatay analogu HLOOKUP işlevine aşina iseniz, bu harika işlevlerin bilgileri yalnızca bir parametreye göre, yani tek boyutlu bir dizide satır veya sütuna göre aradığını hatırlamanız gerekir. Ve iki boyutlu bir tablodan verileri aynı anda iki parametrenin çakışmasıyla - hem satır hem de sütun - aynı anda seçmemiz gerekirse? Tabloları birleştirmek için çeşitli seçenekleri ele alalım.

1. INDEX ve MATCH kullanarak tabloları birleştirmek

Kredi portföyünü ve teminat portföyünü gösteren iki tabloyu birleştirmemiz gerektiğini varsayalım:

Her bir sütunu birleştirmek için VLOOKUP işlevini kullanmaya başlayabiliriz, ancak tablolarımızda aşırı sayıda satır ve sütun varsa, bu alıştırma gerçek bir sıkıntıya dönüşebilir. Ancak bu durumdan oldukça basit bir çıkış yolu var, çünkü Excel iki mükemmel fonksiyona sahip, kategoriden INDEX ve MATCH Referanslar ve diziler (Lookup and Reference), bir çift halinde 2D VLOOKUP olarak çalışır.

Peki iki tabloyu hızlı bir şekilde birleştirmek için ne yapmamız gerekiyor? İkinci tablonun (katılacağımız) başlığını kopyalayıp ilk tablonun başlığının yanına yapıştırarak başlayalım. Başlığın adına göre MATCH işlevi bize sütunun seri numarasını, anlaşma numarasına göre ise ihtiyacımız olan satırın seri numarasını verecektir.

Aslında bir tablodaki belirli bir satır ve sütunun kesişiminden bir hücrenin değerini bulmak istiyoruz. Açıklık sağlamak için görevi üç aşamaya ayıralım:

Öncelikle seçilen kredi sözleşmesine karşılık gelen satır numarasını belirlememiz gerekiyor. Bu, kategoriden MATCH işlevinin yapılmasına yardımcı olacaktır. Referanslar ve diziler (Lookup and Reference). Özellikle, formül MATCH(A14; K2:K18; 0) bize istenen sonucu verecektir (işlem için "№5398" sıra numarası 13 olacaktır). Bu fonksiyonun ilk argümanı aranan değerdir (anlaşma "№5398" A14 hücresinden), ikincisi anlaşma aradığımız hücre aralığıdır (tablodaki anlaşma numaralarını içeren sütun - K2:K18), üçüncü argüman arama türünü belirtir (0 - tam ad eşleşmesi, yaklaşık arama yasaktır).
İkinci olarak, tamamen benzer şekilde, ihtiyacımız olan içeriğin bulunduğu tablodaki sütunun seri numarasını belirlememiz gerekir. İşlev MATCH(F1; J1:M1; 0) bunu yapacak ve örneğin kullanıcı tarafından F1 hücresinde seçilen Teminat adı için 3 değerini yayınlayacaktır.
Ve son olarak, üçüncü olarak, bir hücrenin içeriğini bir tablodan satır ve sütun numarasına göre çıkarabilen bir işleve ihtiyacımız var - aynı kategoriden INDEX işlevi Referanslar ve diziler (Lookup and Reference). Bu fonksiyonun ilk argümanı hücre aralığıdır (bizim durumumuzda tablonun tamamıdır, yani J2:M18), ikincisi satır numarasıdır, üçüncüsü sütun numarasıdır (ve bunları MATCH kullanarak belirleyeceğiz) işlevler).

Yukarıdakilerin tümünü tek bir formülde birleştirerek F14 hücresi için aşağıdaki formülü elde ederiz:

=INDEX(J2:M18; MATCH(A14; K2:K18; 0); MATCH(F1; J1:M1; 0))

Bu şekilde formülümüzü tüm aralığa yayarak ekstra alanları doğru şekilde ekleyeceğiz. Ayrıca formülü genişletirken aralıkları dolar işaretiyle ($) sabitlememiz gerektiğine (sabitlemek için F4 tuşunu kullanabilirsiniz), başlığa göre arama yapmak için sadece satırı sabitlediğimize (1$ ), işlem numaralarına göre arama yapmak için - yalnızca sütun ($A14 ).

2. VLOOKUP kullanarak tabloları birleştirmek

Önceki örneği biraz değiştirelim ve benzer tablolarımız olduğunu ancak bunların başlıklarının birleştirildiğini varsayalım, dolayısıyla MATCH işlevi sütunun seri numarasını doğru bir şekilde belirlememize yardımcı olmayacaktır.

Bu durumda tablonun üzerinde teknik alan oluşturup sütun numaralarını manuel olarak girebiliyoruz. Daha sonra zaten aşina olduğumuz VLOOKUP fonksiyonunu kullanabiliriz.

Yani ikinci seçeneğimizin formülü şu şekilde olacaktır:

=VLOOKUP($A3; $J$3:$M$19; E$1; 0)

Yine formülün doğru çalışması ve aralıkları kaydırırken hata vermemesi için aralığı ve karşılık gelen arama alanlarını $ işaretiyle sabitlemeyi unutmayın.

Konuyla ilgili makaleler: