DÜŞEYARA işlevi, Excel'deki en popüler işlevlerden biridir. Bu öğretici, Excel'de DÜŞEYARA işlevinin nasıl kullanılacağını düzinelerce temel ve ileri düzey örnekle adım adım tanıtacaktır.

DÜŞEYARA işlevine giriş - Sözdizimi ve Bağımsız Değişkenler

Excel'de, DÜŞEYARA işlevi, Excel kullanıcılarının çoğu için güçlü bir işlevdir, veri aralığının en solundaki bir değeri aramanıza ve aşağıdaki ekran görüntüsü gösterildiği gibi belirttiğiniz bir sütundan aynı satırda eşleşen bir değer döndürmenize olanak tanır. .

DÜŞEYARA işlevinin sözdizimi:

argümanlar:

İşlev Notları:

Temel DÜŞEYARA örnekleri

Bu bölümde sık kullandığınız bazı Vlookup formüllerinden bahsedeceğiz.

2.1 Tam eşleşme ve yaklaşık eşleşme DÜŞEYARA

 2.1.1 Tam bir eşleşme yapın DÜŞEYARA

Normalde, DÜŞEYARA işleviyle tam bir eşleşme arıyorsanız, son bağımsız değişken olarak YANLIŞ kullanmanız yeterlidir.

Örneğin, belirli kimlik numaralarına göre karşılık gelen Matematik puanlarını almak için lütfen şunu yapın:

Lütfen aşağıdaki formülü kopyalayıp boş bir hücreye yapıştırın (burada G2'yi seçiyorum) ve tuşuna basın Keşfet sonucu almanın anahtarı:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

DÜŞEYARA formülü nasıl çalışır?

İlk olarak, tablonun en soldaki sütununda ID - C1005'i arar. Yukarıdan aşağıya doğru gider ve A6 hücresindeki değeri bulur.

Değeri bulur bulmaz üçüncü sütunda sağa gider ve içindeki değeri çıkarır.

Böylece, sonucu aşağıda gösterilen ekran görüntüsü gibi alacaksınız:

 2.1.2 Yaklaşık bir eşleştirme yapın DÜŞEYARA

Yaklaşık eşleşme, veri aralıkları arasında değer aramak için kullanışlıdır. Tam eşleşme bulunamazsa, yaklaşık DÜŞEYARA, aranan değerden küçük olan en büyük değeri döndürür.

Örneğin, aşağıdaki veri aralığına sahipseniz ve belirtilen siparişler Siparişler sütununda yoksa, B sütununda en yakın İndirim nasıl alınır?

Adım 1: DÜŞEYARA formülünü uygulayın ve diğer hücrelere doldurun

Aşağıdaki formülü kopyalayıp sonucu koymak istediğiniz bir hücreye yapıştırın ve ardından bu formülü diğer hücrelere uygulamak için doldurma tutamacını aşağı doğru sürükleyin.

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

Sonuç:

Şimdi, verilen değerlere göre yaklaşık eşleşmeleri alacaksınız, ekran görüntüsüne bakın:

2.2 Excel'de büyük/küçük harfe duyarlı bir DÜŞEYARA yapın

Varsayılan olarak, DÜŞEYARA işlevi büyük/küçük harfe duyarlı olmayan bir arama gerçekleştirir; bu, küçük ve büyük karakterleri aynı olarak ele aldığı anlamına gelir. Bazen, Excel'de büyük/küçük harfe duyarlı bir arama yapmanız gerekebilir, normal DÜŞEYARA işlevi bunu çözmeyebilir. Bu durumda, TAM işleviyle İNDİS ve KAÇINCI gibi alternatif işlevleri veya ARA ve TAM işlevleri kullanabilirsiniz.

Örneğin, kimlik sütununun büyük veya küçük harf içeren metin dizesini içeren aşağıdaki veri aralığına sahibim, şimdi, verilen kimlik numarasına karşılık gelen Matematik puanını döndürmek istiyorum.

Adım 1: Herhangi bir formülü uygulayın ve diğer hücrelere doldurun

Lütfen aşağıdaki formüllerden herhangi birini kopyalayıp sonucu almak istediğiniz boş bir hücreye yapıştırın. Ardından, formül hücresini seçin, doldurma tutamacını bu formülü doldurmak istediğiniz hücrelere doğru sürükleyin.

Formula 1: Formülü yapıştırdıktan sonra lütfen Ctrl + Üst Karakter + Enter anahtarlar.

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

Formula 2: Formülü yapıştırdıktan sonra lütfen Keşfet tuşuna basın.

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

Sonuç:

O zaman ihtiyacınız olan doğru sonuçları alacaksınız. Ekran görüntüsüne bakın:

2.3 Excel'de sağdan sola DÜŞEYARA değerleri

DÜŞEYARA işlevi her zaman bir veri aralığının en soldaki sütununda bir değer arar ve bir sütundan sağa karşılık gelen değeri döndürür. Sağ sütunda belirli bir değer aramak ve sol sütunda karşılık gelen değeri aşağıda gösterilen ekran görüntüsü gibi döndürmek anlamına gelen bir ters DÜŞEYARA gerçekleştirmek istiyorsanız:

Bu görevle ilgili ayrıntıları adım adım öğrenmek için tıklayın…

2.4 Excel'de ikinci, n. veya son eşleşen değer DÜŞEYARA

Normalde, Vlookup işlevi kullanılırken birden çok eşleşen değer bulunursa, yalnızca ilk eşleşen kayıt döndürülür. Bu bölümde bir veri aralığında ikinci, n. veya son eşleşen değerin nasıl alınacağından bahsedeceğim.

 2.4.1 DÜŞEYARA ve ikinci veya n'inci eşleşen değeri döndürür

Diyelim ki A sütununda, B sütununda satın aldıkları eğitim kursunun adlarının bir listesi var. Şimdi, söz konusu müşteri tarafından satın alınan 2. veya n. eğitim kursunu arıyorsunuz. Ekran görüntüsüne bakın:

Burada DÜŞEYARA işlevi bu görevi doğrudan çözmeyebilir. Ancak, alternatif olarak INDEX işlevini kullanabilirsiniz.

1. Adım: Formülü diğer hücrelere uygulayın ve doldurun

Örneğin, verilen kritere göre ikinci eşleşen değeri elde etmek için lütfen aşağıdaki formülü boş bir hücreye uygulayın ve tuşuna basın. Ctrl + Üst Karakter + Enter İlk sonucu elde etmek için tuşları bir araya getirin. Ardından, formül hücresini seçin, doldurma tutamacını bu formülü doldurmak istediğiniz hücrelere sürükleyin.

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

Sonuç:

Şimdi, verilen adlara dayalı olarak eşleşen tüm ikinci değerler aynı anda görüntülendi.

 2.4.2 DÜŞEYARA ve son eşleşen değeri döndürür

Vlookup yapmak ve son eşleşen değeri aşağıda gösterilen ekran görüntüsü gibi döndürmek istiyorsanız, bu DÜŞEYARA ve Son Eşleşen Değeri Döndür öğretici, son eşleşen değeri ayrıntılı olarak almanıza yardımcı olabilir.

2.5 DÜŞEYARA verilen iki değer veya tarih arasındaki eşleşen değerler

Bazen, iki değer veya tarih arasındaki değerleri aramak ve aşağıdaki ekran görüntüsünde gösterildiği gibi ilgili sonuçları döndürmek isteyebilirsiniz. Böyle bir durumda, sıralanmış bir tablo ile DÜŞEYARA işlevi yerine ARA işlevini kullanabilirsiniz.

 2.5.1 DÜŞEYARA verilen iki değer veya tarih arasındaki değerleri formülle eşleştirme

Adım 1: Verileri düzenleyin ve aşağıdaki formülü uygulayın

Orijinal tablonuz sıralanmış bir veri aralığı olmalıdır. Ardından, aşağıdaki formülü boş bir hücreye kopyalayın veya girin. Ardından, bu formülü ihtiyacınız olan diğer hücrelere doldurmak için doldurma tutamacını sürükleyin.

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

Sonuç:

Ve şimdi, verilen değere göre eşleşen tüm kayıtları alacaksınız, ekran görüntüsüne bakın:

 2.5.2 DÜŞEYARA kullanışlı bir özellik ile verilen iki değer veya tarih arasındaki değerleri eşleştirme

Yukarıdaki formülü hatırlamakta ve anlamakta zorlanıyorsanız, burada kolay bir araç tanıtacağım – Kutools for Excel, Onun ile İki Değer Arasında ARA özelliği, iki değer veya tarih arasında belirli bir değere veya tarihe göre karşılık gelen öğeyi kolaylıkla iade edebilirsiniz.

1. Tıkla Kutools > Süper ARAMA > İki Değer Arasında ARA Bu özelliği etkinleştirmek için.
2. Ardından, verilerinize dayalı olarak iletişim kutusundan işlemleri belirtin.

2.6 DÜŞEYARA işlevinde kısmi eşleşmeler için joker karakterler kullanma

Excel'de, joker karakterler, bir arama değerinde kısmi bir eşleşme gerçekleştirmenize izin veren DÜŞEYARA işlevinde kullanılabilir. Örneğin, bir arama değerinin bir kısmına dayalı olarak bir tablodan eşleşen değeri döndürmek için DÜŞEYARA'yı kullanabilirsiniz.

Diyelim ki, aşağıda gösterilen ekran görüntüsü gibi bir dizi veriye sahibim, şimdi puanı ilk ada göre çıkarmak istiyorum (tam adı değil). Bu görevi Excel'de nasıl çözebilirim?

1. Adım: Formülü diğer hücrelere uygulayın ve doldurun

Lütfen aşağıdaki formülü kopyalayın veya boş bir hücreye girin ve ardından bu formülü ihtiyacınız olan diğer hücrelere doldurmak için doldurma tutamacını sürükleyin:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

Sonuç:

Ve tüm eşleşen puanlar aşağıda gösterilen ekran görüntüsü gibi döndürüldü:

2.7 Başka bir çalışma sayfasındaki DÜŞEYARA değerleri

Genellikle, birden fazla çalışma sayfasıyla çalışmanız gerekebilir, DÜŞEYARA işlevi, bir çalışma sayfasında olduğu gibi başka bir sayfadan veri aramak için kullanılabilir.

Örneğin, aşağıda gösterilen ekran görüntüsü gibi iki çalışma sayfanız var, belirttiğiniz çalışma sayfasından ilgili verileri aramak ve geri döndürmek için lütfen aşağıdaki adımları uygulayın:

1. Adım: Formülü diğer hücrelere uygulayın ve doldurun

Lütfen aşağıdaki formülü eşleşen öğeleri almak istediğiniz boş bir hücreye girin veya kopyalayın. Ardından, doldurma tutamacını bu formülü uygulamak istediğiniz hücrelere sürükleyin.

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

Sonuç:

Karşılık gelen sonuçları istediğiniz gibi alacaksınız, ekran görüntüsüne bakın:

2.8 Başka bir çalışma kitabından DÜŞEYARA değerleri

Bu bölüm arama hakkında konuşacak ve DÜŞEYARA işlevini kullanarak farklı bir çalışma kitabından eşleşen değerleri döndürecektir.

Örneğin, iki çalışma kitabınız olduğunu varsayalım. İlk çalışma kitabı, ürünlerin bir listesini ve ilgili maliyetlerini içerir. İkinci çalışma kitabında, aşağıda gösterilen ekran görüntüsü gibi her bir ürün kalemi için ilgili maliyeti çıkarmak istiyorsunuz.

Adım 1: Formülü uygulayın ve doldurun

Kullanmak istediğiniz her iki çalışma kitabını da açın, ardından ikinci çalışma kitabında sonucu koymak istediğiniz bir hücreye aşağıdaki formülü uygulayın. Ardından, bu formülü ihtiyacınız olan diğer hücrelere sürükleyip kopyalayın

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

Sonuç:

2.9 0 veya #YOK hatası yerine boş veya belirli bir metin döndürün

Genellikle karşılık gelen bir değer döndürmek için DÜŞEYARA işlevini kullandığınızda, eşleşen hücre boşsa 0 döndürür. Eşleşen değer bulunamazsa, şekilde gösterildiği gibi #YOK hata değeri alırsınız. aşağıdaki ekran görüntüsü. 0 veya #YOK yerine boş bir hücre veya belirli bir değer görüntülemek istiyorsanız, bu DÜŞEYARA 0 Veya Yok Yerine Boş veya Belirli Bir Değer Döndürmek İçin öğretici size bir iyilik yapabilir.

Gelişmiş DÜŞEYARA örnekleri

3.1 İki yönlü arama (satır ve sütunda DÜŞEYARA)

Bazen, aynı anda hem satırda hem de sütunda bir değer aramak anlamına gelen 2 boyutlu bir arama yapmanız gerekebilir. Örneğin, aşağıdaki veri aralığına sahipseniz ve belirli bir ürünün değerini belirli bir çeyrekte almanız gerekebilir. Bu bölüm, Excel'de bu işle başa çıkmak için bir formül sunacaktır.

Excel'de, iki yönlü arama yapmak için DÜŞEYARA ve KAÇINCI işlevlerinin bir birleşimini kullanabilirsiniz.

Lütfen aşağıdaki formülü boş bir hücreye uygulayın ve ardından Keşfet sonucu almak için anahtar.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

3.2 DÜŞEYARA iki veya daha fazla kritere dayalı eşleşen değer

Eşleşen değeri tek bir ölçüte göre aramak sizin için kolaydır, ancak iki veya daha fazla ölçütünüz varsa ne yapabilirsiniz?

 3.2.1 Formüllerle iki veya daha fazla ölçüte dayalı DÜŞEYARA eşleştirme değeri

Bu durumda, Excel'deki ARA veya KAÇINCI ve DİZİN işlevleri, bu işi hızlı ve kolay bir şekilde çözmenize yardımcı olabilir.

Örneğin, aşağıdaki veri tablosuna sahibim, eşleşen fiyatı belirli ürüne ve boyuta göre döndürmek için aşağıdaki formüller size yardımcı olabilir.

1. Adım: Herhangi bir formülü uygulayın

Formula 1: Formülü yapıştırdıktan sonra lütfen Keşfet tuşuna basın.

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Formula 2: Formülü yapıştırdıktan sonra lütfen Ctrl + Üst Karakter + Enter anahtarlar.

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Sonuç:

 3.2.2 Akıllı bir özellikle iki veya daha fazla kritere dayalı DÜŞEYARA eşleştirme değeri

Tekrar tekrar uygulanması gereken, iş verimliliğinizi yavaşlatabilecek yukarıdaki karmaşık formülleri hatırlamak zor olabilir. Fakat, Kutools for Excel Bir sunuyor Çok Durumlu Arama sadece birkaç tıklamayla bir veya daha fazla koşula dayalı olarak karşılık gelen sonucu döndürmenizi sağlayan özellik.

1. Tıkla Kutools > Süper ARAMA > Çok Durumlu Arama Bu özelliği etkinleştirmek için.
2. Ardından, verilerinize dayalı olarak iletişim kutusundan işlemleri belirtin.

3.3 DÜŞEYARA, bir veya daha fazla ölçüte sahip birden çok değer döndürmek için

Excel'de DÜŞEYARA işlevi bir değer arar ve yalnızca karşılık gelen birden çok değer bulunursa ilk eşleşen değeri döndürür. Bazen, karşılık gelen tüm değerleri bir satırda, bir sütunda veya tek bir hücrede döndürmek isteyebilirsiniz. Bu bölüm, bir çalışma kitabında bir veya daha fazla koşulla eşleşen birden çok değerin nasıl döndürüleceği hakkında konuşacaktır.

 3.3.1 Yatay olarak bir veya daha fazla koşula dayalı olarak eşleşen tüm değerleri DÜŞEYARA

A1:C14 aralığında ülke, şehir ve adları içeren bir veri tablonuz olduğunu ve şimdi, aşağıda gösterilen ekran görüntüsü gibi "ABD" den olan tüm adları yatay olarak döndürmek istediğinizi varsayarsak. Bu görevi çözmek için lütfen sonucu adım adım almak için buraya tıklayın.

 3.3.2 Dikey olarak bir veya daha fazla koşula dayalı tüm eşleşen değerleri DÜŞEYARA

Vlookup'a ihtiyacınız varsa ve aşağıda gösterilen ekran görüntüsü gibi belirli ölçütlere göre eşleşen tüm değerleri dikey olarak döndürmeniz gerekiyorsa, Çözümü ayrıntılı olarak almak için lütfen buraya tıklayın.

 3.3.3 DÜŞEYARA bir veya daha fazla koşula dayalı tüm eşleşen değerleri tek bir hücrede

Vlookup yapmak ve birden çok eşleşen değeri belirtilen ayırıcıyla tek bir hücreye döndürmek istiyorsanız, TEXTJOIN'in yeni işlevi, bu işi hızlı ve kolay bir şekilde çözmenize yardımcı olabilir.

3.4 DÜŞEYARA, eşleşen bir hücrenin tüm satırını döndürmek için

Bu bölümde DÜŞEYARA fonksiyonu kullanılarak eşleşen bir değerin tüm satırının nasıl alınacağından bahsedeceğim.

Adım 1: Aşağıdaki formülü uygulayın ve doldurun

Lütfen aşağıdaki formülü sonucun çıktısını almak istediğiniz boş bir hücreye kopyalayın veya yazın ve tuşuna basın. Keşfet İlk değeri almak için tuş. Ardından, tüm satırın verileri görüntülenene kadar formül hücresini sağa sürükleyin.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

Sonuç:

Artık tüm satır verilerinin döndürüldüğünü görebilirsiniz. Ekran görüntüsüne bakın:

3.5 Excel'de İç İçe DÜŞEYARA

Bazen birden çok tablo arasında birbirine bağlı değerlere bakmanız gerekebilir. Bu durumda, son değeri elde etmek için birden çok DÜŞEYARA işlevini iç içe geçirebilirsiniz.

Örneğin, iki ayrı tablo içeren bir çalışma sayfam var. İlk tablo, ilgili satıcıyla birlikte tüm ürün adlarını listeler. İkinci tablo, her satıcının toplam satışlarını listeler. Şimdi, aşağıdaki ekran görüntüsünde gösterildiği gibi her bir ürünün satışını bulmak istiyorsanız, bu görevi gerçekleştirmek için DÜŞEYARA işlevini iç içe yerleştirebilirsiniz.

Yuvalanmış DÜŞEYARA işlevi için genel formül şöyledir:

Adım 1: Aşağıdaki formülü uygulayın ve doldurun

Lütfen aşağıdaki formülü boş bir hücreye uygulayın ve ardından doldurma tutamacını bu formülü uygulamak istediğiniz hücrelere sürükleyin.

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Sonuç:

Şimdi, sonucu aşağıdaki ekran görüntüsünde gösterildiği gibi alacaksınız:

3.6 Başka bir sütundaki liste verilerine göre değer olup olmadığını kontrol edin

DÜŞEYARA işlevi, başka bir sütundaki veri listesine göre değerlerin var olup olmadığını kontrol etmenize de yardımcı olabilir. Örneğin, C sütunundaki adları aramak istiyorsanız ve aşağıda gösterilen ekran görüntüsü gibi ad A sütununda bulunursa veya bulunmazsa sadece Evet veya Hayır döndürün.

Adım 1: Aşağıdaki formülü uygulayın ve doldurun

Lütfen aşağıdaki formülü boş bir hücreye uygulayın, ardından doldurma tutamacını bu formülü doldurmak istediğiniz hücrelere sürükleyin.

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

Sonuç:

Ve sonucu istediğiniz gibi alacaksınız, ekran görüntüsüne bakın:

3.7 DÜŞEYARA ve eşleşen tüm değerleri satırlarda veya sütunlarda toplayın

Sayısal verilerle çalışırken, eşleşen değerleri bir tablodan çıkarmanız ve sayıları birkaç sütun veya satırda toplamanız gerekebilir. Bu bölümde, bu görevi gerçekleştirmenize yardımcı olabilecek bazı formüller tanıtılacaktır.

 3.7.1 DÜŞEYARA ve eşleşen tüm değerleri bir satırda veya birden çok satırda toplayın

Aşağıdaki ekran görüntüsünde gösterildiği gibi birkaç ay boyunca satışı olan bir ürün listeniz olduğunu varsayalım. Şimdi, verilen ürünlere göre tüm aylardaki tüm siparişleri toplamanız gerekiyor.

Adım 1: Aşağıdaki formülü uygulayın ve doldurun

Lütfen aşağıdaki formülü kopyalayın veya boş bir hücreye girin ve ardından tuşuna basın. Ctrl + Üst Karakter + Enter İlk sonucu elde etmek için tuşları bir araya getirin. Ardından, bu formülü ihtiyacınız olan diğer hücrelere kopyalamak için doldurma tutamacını aşağı sürükleyin.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Sonuç:

İlk eşleşen değerin satırındaki tüm değerler bir araya toplanmıştır, ekran görüntüsüne bakın:

 3.7.2 DÜŞEYARA ve eşleşen tüm değerleri bir sütunda veya birden çok sütunda toplayın

Aşağıdaki ekran görüntüsünde gösterildiği gibi belirli aylar için toplam değeri toplamak istiyorsanız. Normal DÜŞEYARA işlevi size yardımcı olmayabilir, burada TOPLA, DİZİN ve KAÇINCI işlevlerini bir formül oluşturmak için birlikte uygulamalısınız.

Adım 1: Aşağıdaki formülü uygulayın

Aşağıdaki formülü boş bir hücreye uygulayın ve ardından bu formülü diğer hücrelere kopyalamak için doldurma tutamacını aşağı doğru sürükleyin.

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Sonuç:

Şimdi, bir sütundaki belirli aya göre ilk eşleşen değerler bir araya toplandı, ekran görüntüsüne bakın:

 3.7.3 DÜŞEYARA ve ilk eşleşen veya tüm eşleşen değerleri güçlü bir özellikle toplayın

Belki yukarıdaki formülleri hatırlamanız zor olabilir, bu durumda güçlü bir özellik önereceğim - Arama ve Toplam of Kutools for Excel, bu özellikle, satır veya sütunlardaki ilk eşleşen veya tüm eşleşen değerleri Vlookup ve toplayabilirsiniz.

1. Tıkla Kutools > Süper ARAMA > LOOKUP ve Sum Bu özelliği etkinleştirmek için.
2. Ardından, ihtiyacınıza göre iletişim kutusundan işlemleri belirtin.

 3.7.4 DÜŞEYARA ve eşleşen tüm değerleri hem satırlarda hem de sütunlarda toplayın

Örneğin, Sweater ürününün toplam değerini Mar ayındaki aşağıda gösterilen ekran görüntüsü gibi almak için hem sütun hem de satırı eşleştirmeniz gerektiğinde değerleri toplamak istiyorsanız.

Burada, bu görevi gerçekleştirmek için SUMPRODCT işlevini kullanabilirsiniz.

Lütfen aşağıdaki formülü bir hücreye uygulayın ve ardından Keşfet sonucu almak için anahtar, ekran görüntüsüne bakın:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

3.8 DÜŞEYARA, anahtar sütunlara dayalı olarak iki tabloyu birleştirmek için

Günlük işlerinizde, verileri analiz ederken, gerekli tüm bilgileri bir veya daha fazla anahtar sütuna dayalı tek bir tabloda toplamanız gerekebilir. Bu görevi gerçekleştirmek için DÜŞEYARA işlevi yerine DİZİN ve KAÇINCI işlevlerini kullanabilirsiniz.

 3.8.1 DÜŞEYARA iki tabloyu bir anahtar sütuna göre birleştirmek için

Örneğin, iki tablonuz var, ilk tablo ürün ve ad verilerini içeriyor ve ikinci tablo ürün ve sipariş verilerini içeriyor, şimdi bu iki tabloyu ortak ürün sütununu tek bir tabloyla eşleştirerek birleştirmek istiyorsunuz.

Adım 1: Aşağıdaki formülü uygulayın ve doldurun

Lütfen aşağıdaki formülü boş bir hücreye uygulayın. Ardından, doldurma tutamacını bu formülü uygulamak istediğiniz hücrelere sürükleyin.

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

Sonuç:

Şimdi, anahtar sütun verilerine dayalı olarak ilk tabloya katılan sipariş sütunuyla birleştirilmiş bir tablo alacaksınız.

 3.8.2 DÜŞEYARA, birden çok anahtar sütuna dayalı olarak iki tabloyu birleştirmek için

Birleştirmek istediğiniz iki tablonun birden çok anahtar sütunu varsa, tabloları bu ortak sütunlara göre birleştirmek için lütfen aşağıdaki adımları izleyin.

Genel formül:

Adım 1: Aşağıdaki formülü uygulayın

Lütfen aşağıdaki formülü, sonucu koymak istediğiniz boş bir hücreye uygulayın ve ardından tuşuna basın. Ctrl + Üst Karakter + Enter ilk eşleşen değeri elde etmek için anahtarları bir araya getirin, ekran görüntüsüne bakın:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Adım 2: Formülü diğer hücrelere doldurun

Ardından, ilk formül hücresini seçin ve bu formülü istediğiniz gibi diğer hücrelere kopyalamak için doldurma tutamacını sürükleyin:

3.9 DÜŞEYARA birden çok çalışma sayfasında değerleri eşleştirme

Hiç Excel'de birden çok çalışma sayfasında DÜŞEYARA gerçekleştirmeniz gerekti mi? Örneğin, veri aralıklarına sahip üç çalışma sayfanız varsa ve bu sayfalardan ölçütlere dayalı olarak belirli değerleri almak istiyorsanız, adım adım öğreticiyi takip edebilirsiniz. Birden Fazla Çalışma Sayfasındaki DÜŞEYARA Değerleri Bu görevi başarmak için.

DÜŞEYARA eşleşen değerler hücre biçimlendirmesini korur

Eşleşen değerler aranırken yazı tipi rengi, arka plan rengi, veri biçimi vb. gibi orijinal hücre biçimlendirmesi korunmaz. Hücre veya veri biçimlendirmesini korumak için, bu bölümde işleri çözmek için bazı püf noktaları tanıtılacaktır.

4.1 DÜŞEYARA eşleştirme değeri ve hücre rengini, yazı tipi biçimlendirmesini koru

Hepimizin bildiği gibi, normal DÜŞEYARA işlevi yalnızca eşleşen değeri başka bir veri aralığından alabilir. Ancak, dolgu rengi, yazı tipi rengi ve yazı tipi stili gibi hücre biçimlendirmesiyle birlikte karşılık gelen değeri almak istediğiniz durumlar olabilir. Bu bölümde, Excel'de kaynak biçimlendirmesini korurken eşleşen değerlerin nasıl alınacağını tartışacağız.

Hücre biçimlendirmesiyle birlikte karşılık gelen değerini aramak ve döndürmek için lütfen aşağıdaki adımları uygulayın:

Adım 1: Kod 1'i Sayfa Kodu Modülüne kopyalayın

1. Çalışma sayfasında DÜŞEYARA yapmak istediğiniz verileri içerir, sayfa sekmesine sağ tıklayın ve seçin Kodu Görüntüle bağlam menüsünden. Ekran görüntüsüne bakın:
   
2. Açılan Uygulamalar için Microsoft Visual Basic penceresi, lütfen aşağıdaki VBA kodunu Kod penceresine kopyalayın.
3. VBA kodu 1: Arama değeriyle birlikte hücre biçimlendirmesini almak için DÜŞEYARA

4. Sub Worksheet_Change(ByVal Target As Range)
   'Updateby Extendoffice
       Dim I As Long
       Dim xKeys As Long
       Dim xDicStr As String
       On Error Resume Next
       Application.ScreenUpdating = False
       xKeys = UBound(xDic.Keys)
       If xKeys >= 0 Then
           For I = 0 To UBound(xDic.Keys)
               xDicStr = xDic.Items(I)
               If xDicStr <> "" Then
                   Range(xDic.Keys(I)).Interior.Color = _
                   Range(xDic.Items(I)).Interior.Color
                   Range(xDic.Keys(I)).Font.FontStyle = _
                   Range(xDic.Items(I)).Font.FontStyle
                   Range(xDic.Keys(I)).Font.Size = _
                   Range(xDic.Items(I)).Font.Size
                   Range(xDic.Keys(I)).Font.Color = _
                   Range(xDic.Items(I)).Font.Color
                   Range(xDic.Keys(I)).Font.Name = _
                   Range(xDic.Items(I)).Font.Name
                   Range(xDic.Keys(I)).Font.Underline = _
                   Range(xDic.Items(I)).Font.Underline
               Else
                   Range(xDic.Keys(I)).Interior.Color = xlNone
               End If
           Next
           Set xDic = Nothing
       End If
       Application.ScreenUpdating = True
   End Sub
   

5. 

2. Adım: 2. kodu Modül penceresine kopyalayın

1. Yine de Uygulamalar için Microsoft Visual Basic Pencere, tıklayın Ekle > modülve ardından aşağıdaki VBA kodu 2'yi Modül penceresine kopyalayın.
2. VBA kodu 2: Arama değeriyle birlikte hücre biçimlendirmesini almak için DÜŞEYARA

3. Public xDic As New Dictionary
   Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
       Dim xFindCell As Range
       On Error Resume Next
       Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
       If xFindCell Is Nothing Then
           LookupKeepFormat = ""
           xDic.Add Application.Caller.Address, ""
       Else
           LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
           xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
       End If
   End Function
   

4. 

3. Adım: VBA projesi için seçeneği seçin

1. Yukarıdaki kodları girdikten sonra tıklayın. Tools > Referanslar içinde Uygulamalar için Microsoft Visual Basic pencere. Sonra kontrol edin Microsoft Komut Dosyası Çalışma Zamanı onay kutusu Referanslar - VBAProject iletişim kutusu. Ekran görüntülerine bakın:
2. Ardından, tıklayın OK iletişim kutusunu kapatmak için ve ardından kod penceresini kaydedip kapatın.

Adım 4: Sonucu almak için formülü yazın

1. Şimdi çalışma sayfasına geri dönün, aşağıdaki formülü uygulayın. Ardından, biçimlendirmeleriyle birlikte tüm sonuçları almak için doldurma tutamacını aşağı sürükleyin. Ekran görüntüsüne bakın:
   

   =LookupKeepFormat(E2,$A$1:$C$10,3)

   
   

4.2 DÜŞEYARA döndürülen değerden tarih biçimini koruyun

Tarih biçiminde bir değer aramak ve döndürmek için DÜŞEYARA işlevini kullanırken, döndürülen sonuç bir sayı olarak görüntülenebilir. Tarih biçimini döndürülen sonuçta tutmak için DÜŞEYARA işlevini METİN işlevinin içine almalısınız.

Adım 1: Aşağıdaki formülü uygulayın ve doldurun

Lütfen aşağıdaki formülü boş bir hücreye uygulayınız. Ardından, bu formülü diğer hücrelere kopyalamak için doldurma tutamacını sürükleyin.

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Sonuç:

Eşleşen tüm tarihler, aşağıda gösterilen ekran görüntüsü gibi döndürüldü:

4.3 DÜŞEYARA'dan hücre yorumunu döndür

Aşağıdaki ekran görüntüsünde gösterildiği gibi, Excel'de DÜŞEYARA'yı kullanarak hem eşleşen hücre verilerini hem de ilişkili açıklamayı almanız hiç gerekti mi? Öyleyse, aşağıda sağlanan Kullanıcı Tanımlı İşlev, bu görevi gerçekleştirmenize yardımcı olabilir.

1. Adım: Kodu bir Modüle kopyalayın

1. basılı tutun ALT + F11 tuşlarını açmak için Uygulamalar için Microsoft Visual Basic pencere.
2. Tıkla Ekle > modül, ardından aşağıdaki kodu Modül Penceresine kopyalayıp yapıştırın.
   VBA kodu: Vlookup ve hücre yorumu ile eşleşen değeri döndür:

   Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
   'Updateby Extendoffice
       Application.Volatile
       Dim xRet As Variant 'could be an error
       Dim xCell As Range
       xRet = Application.Match(LookVal, FTable.Columns(1), FType)
       If IsError(xRet) Then
           VlookupComment = "Not Found"
       Else
           Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
           VlookupComment = xCell.Value
           With Application.Caller
               If Not .Comment Is Nothing Then
                   .Comment.Delete
               End If
               If Not xCell.Comment Is Nothing Then
                   .AddComment xCell.Comment.Text
               End If
           End With
       End If
   End Function

3. Ardından kod penceresini kaydedin ve kapatın.

2. Adım: Sonucu almak için formülü yazın

1. Şimdi aşağıdaki formülü girin ve bu formülü diğer hücrelere kopyalamak için doldurma tutamacını sürükleyin. Eşleşen değerleri ve yorumları aynı anda döndürür, ekran görüntüsüne bakın:
   

   =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

   
   

4.4 Metin olarak saklanan DÜŞEYARA numaraları

Örneğin, orijinal tablodaki kimlik numarasının sayı biçiminde olduğu ve arama hücrelerindeki kimlik numarasının metin olarak depolandığı bir veri aralığım var, normal DÜŞEYARA işlevini kullanırken #YOK hatasıyla karşılaşabilirsiniz. Bu durumda, doğru bilgiyi almak için DÜŞEYARA işlevi içinde METİN ve DEĞER işlevlerini kaydırabilirsiniz. Bunu başarmak için formül aşağıdadır:

Adım 1: Aşağıdaki formülü uygulayın ve doldurun

Lütfen aşağıdaki formülü boş bir hücreye uygulayın ve ardından bu formülü kopyalamak için doldurma tutamacını aşağı doğru sürükleyin.

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Sonuç:

Şimdi, aşağıda gösterilen ekran görüntüsü gibi doğru sonuçları alacaksınız: