Note: The other languages of the website are Google-translated. Back to English
Oturum aç  \/ 
x
or
x
Kaydol  \/ 
x

or

Excel'deki bazı temel ve gelişmiş örneklerle DÜŞEYARA işlevi

Excel'de, DÜŞEYARA işlevi, Excel kullanıcılarının çoğu için güçlü bir işlevdir ve veri aralığının en solunda bir değer aramak ve aşağıda gösterilen ekran görüntüsü gibi belirttiğiniz bir sütundan aynı satırda eşleşen bir değer döndürmek için kullanılır. . Bu eğitim, Excel'deki bazı temel ve gelişmiş örneklerle DÜŞEYARA işlevinin nasıl kullanılacağından bahsediyor.

İçindekiler:

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

2. Temel DÜŞEYARA örnekleri

3. Gelişmiş DÜŞEYARA örnekleri

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

5. DÜŞEYARA örnek dosyalarını indirin


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

DÜŞEYARA işlevinin sözdizimi:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

argümanlar:

aranan_değer: Aramak istediğiniz değer. Table_array aralığının ilk sütununda olmalıdır.

Masa dizisi: Arama değeri sütununun ve sonuç değeri sütununun bulunduğu veri aralığı veya tablo.

Süt_indis_sayısı: Eşleşen değerin döndürüleceği sütun sayısı. Tablo dizisindeki en soldaki sütundan 1 ile başlar.

Menzil araması: Bu DÜŞEYARA işlevinin tam eşleşme mi yoksa yaklaşık eşleşme mi döndüreceğini belirleyen mantıksal bir değer.

  • Yaklaşık eşleşme - 1 / DOĞRU: Tam bir eşleşme bulunmazsa formül, en yakın eşleşmeyi (arama değerinden daha küçük olan en büyük değeri) arar. Bu durumda, arama sütununu artan sırada sıralamanız gerekir.
    = DÜŞEYARA (aranan_değer, tablo_dizisi, sütun_indisi, DOĞRU)
    = DÜŞEYARA (aranan_değer, tablo_dizisi, sütun_indisi, 1)
  • Tam eşleme - 0 / YANLIŞ: Bu, tam olarak arama değerine eşit bir değer aramak için kullanılır. Tam bir eşleşme bulunmazsa, # N / A hata değeri döndürülür.
    = DÜŞEYARA (aranan_değer, tablo_dizisi, sütun_indisi, YANLIŞ)
    = DÜŞEYARA (aranan_değer, tablo_dizisi, sütun_indisi, 0)

Notlar:

  • 1. Vlookup işlevi yalnızca soldan sağa değer arar.
  • 2. Arama değerine göre birden fazla eşleşen değer varsa, Vlookup işlevi kullanılarak yalnızca ilk eşleşen döndürülür.
  • 3. Arama değeri bulunamazsa, # N / A hata değerini döndürecektir.

Temel DÜŞEYARA örnekleri

1. Tam eşleşme Vlookup ve yaklaşık eşleşme Vlookup yapın

Excel'de tam bir Vlookup eşleme yapın

Normalde, Vlookup işlevi ile tam bir eşleşme arıyorsanız, son bağımsız değişkende FALSE 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:

1. Aşağıdaki formülü, sonucu almak istediğiniz boş bir hücreye uygulayın:

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

2. Ardından, doldurma tutamacını bu formülü doldurmak istediğiniz hücrelere sürükleyin ve istediğiniz sonuçları alacaksınız. Ekran görüntüsüne bakın:

Notlar:

  • 1. Yukarıdaki formülde, F2 eşleşen değerini döndürmek istediğiniz değerdir, A2: D7 tablo dizisi, sayı 3 eşleşen değerinizin döndürüldüğü sütun numarası ve YANLIŞ tam eşleşmeyi ifade eder.
  • 2. Ölçüt değeriniz veri aralığında bulunmazsa, # N / A hata değeri görüntülenecektir.

Excel'de yaklaşık bir Vlookup eşleşmesi yapın

Yaklaşık eşleşme, veri aralıkları arasındaki değerleri aramak için kullanışlıdır. Tam eşleşme bulunamazsa, yaklaşık Vlookup, arama değerinden daha küçük olan en büyük değeri döndürecektir.

Örneğin, aşağıdaki aralık verilerine sahipseniz, belirtilen siparişler Siparişler sütununda değilse, en yakın İndirimi B sütununda nasıl elde edilir?

1. Aşağıdaki formülü sonucu koymak istediğiniz hücreye girin:

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

2. Ardından, bu formülü uygulamak için dolgu tutamacını hücrelere sürükleyin; verilen değerlere göre yaklaşık eşleşmeleri elde edersiniz, ekran görüntüsüne bakın:

Notlar:

  • 1. Yukarıdaki formülde, D2 göreli bilgilerini döndürmek istediğiniz değerdir, A2: B9 veri aralığı, sayı 2 eşleşen değerinizin döndürüldüğü sütun numarasını ve DOĞRU yaklaşık eşleşmeyi ifade eder.
  • 2. Yaklaşık eşleşme, belirli arama değerinizden daha küçük olan en büyük değeri döndürecektir.
  • 3. Yaklaşık bir eşleşme değeri elde etmek üzere Vlookup işlevini kullanmak için, veri aralığının en soldaki sütununu artan sırada sıralamanız gerekir, aksi takdirde yanlış bir sonuç döndürür.

2. Excel'de büyük / küçük harfe duyarlı bir Vlookup yapın

Varsayılan olarak, Vlookup işlevi büyük / küçük harfe duyarlı olmayan bir arama gerçekleştirir; bu, küçük ve büyük harf karakterlerini aynı kabul ettiği anlamına gelir. Bazen, Excel'de büyük / küçük harfe duyarlı bir arama yapmanız gerekebilir, Dizin, Eşleştirme ve Tam işlevler veya Arama ve Tam işlevler size bir iyilik yapabilir.

Ö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.

Formül 1: TAM, İNDİS, KAÇINCI işlevlerini kullanma

1. Lütfen aşağıdaki dizi formülünü sonucu almak istediğiniz boş bir hücreye girin veya kopyalayın:

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

2. Daha sonra, tuşuna basın. Ctrl + Üst Karakter + Enter ilk sonucu almak için aynı anda tuşlarını kullanın ve ardından formül hücresini seçin, doldurma tutamacını bu formülü doldurmak istediğiniz hücrelere sürükleyin, ardından ihtiyacınız olan doğru sonuçları alacaksınız. Ekran görüntüsüne bakın:

Notlar:

  • 1. Yukarıdaki formülde, A2: A10 aramak istediğiniz belirli değerleri içeren sütundur, F2 arama değeridir, C2: C10 sonucun döndürüleceği sütundur.
  • 2. Birden fazla eşleşme bulunursa, bu formül her zaman ilk eşleşmeyi döndürür.

Formül 2: Arama ve Tam işlevlerini kullanma

1. Lütfen aşağıdaki formülü sonucu almak istediğiniz boş bir hücreye uygulayın:

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

2. Ardından, doldurma tutamacını bu formülü kopyalamak istediğiniz hücrelere sürükleyin ve aşağıda gösterilen ekran görüntüsü gibi büyük / küçük harfe duyarlı eşleşen değerleri alacaksınız:

Notlar:

  • 1. Yukarıdaki formülde, A2: A10 aramak istediğiniz belirli değerleri içeren sütundur, F2 arama değeridir, C2: C10 sonucun döndürüleceği sütundur.
  • 2. Birden fazla eşleşme bulunursa, bu formül her zaman son eşleşmeyi döndürür.

3. Excel'de sağdan sola Vlookup değerleri

Vlookup işlevi her zaman bir veri aralığının en sol sütununda bir değer arar ve ilgili değeri bir sütundan sağa döndürür. Ters Vlookup yapmak istiyorsanız, bu, sağdaki belirli bir değeri aramak ve karşılık gelen değeri aşağıda gösterilen ekran görüntüsü gibi sol sütunda döndürmek anlamına gelir:

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


4. Vlookup Excel'de ikinci, n'inci veya son eşleşen değeri

Normalde, Vlookup işlevi kullanılırken birden fazla eşleşen değer bulunursa, yalnızca ilk eşleşen kayıt döndürülür. Bu bölümde, Vlookup fonksiyonu ile ikinci, n'inci veya son eşleşen değerin nasıl elde edileceğinden bahsedeceğim.

Vlookup ve ikinci veya n. Eşleşen değeri döndür

A sütununda, B sütununda satın aldıkları eğitim kursunda bir isim listesine sahip olduğunuzu ve şimdi, söz konusu müşteri tarafından satın alınan 2. veya n. Eğitim kursunu bulmaya çalıştığınızı varsayalım. Ekran görüntüsüne bakın:

1. Verilen kriterlere göre ikinci veya n'inci eşleşen değeri elde etmek için, lütfen aşağıdaki dizi formülünü boş bir hücreye uygulayın:

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

2. Daha sonra tuşuna basın. Ctrl + Üst Karakter + Enter ilk sonucu almak için tuşları bir araya getirin ve ardından formül hücresini seçin, doldurma tutamacını bu formülü doldurmak istediğiniz hücrelere sürükleyin ve verilen adlara göre eşleşen tüm ikinci değerler aynı anda görüntülendi, ekran görüntüsüne bakın:

Not:

  • Bu formülde, A2: A14 arama için tüm değerleri içeren aralıktır, B2: B14 geri dönmek istediğiniz eşleşen değerlerin aralığıdır, E2 arama değeri ve son sayı 2 elde etmek istediğiniz ikinci eşleşen değeri gösterir, üçüncü eşleşen değeri döndürmek istiyorsanız, ihtiyacınız olduğu kadar 3 olarak değiştirmeniz yeterlidir.

Vlookup ve son eşleşen değeri döndü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 Vlookup Ve Son Eşleşen Değeri Döndür öğretici, ayrıntılarda son eşleşen değeri elde etmenize yardımcı olabilir.


5. Verilen iki değer veya tarih arasındaki Vlookup eşleşen değerler

Bazen, iki değer veya tarih arasındaki değerleri aramak ve karşılık gelen sonuçları aşağıda gösterilen ekran görüntüsü gibi döndürmek isteyebilirsiniz, bu durumda ARA işlevini ve sıralı bir tabloyu kullanabilirsiniz.

Vlookup, verilen iki değer veya tarih arasındaki değerleri formülle eşleştirme

1. İlk olarak, orijinal tablonuz sıralı bir veri aralığı olmalıdır. Ardından, aşağıdaki formülü boş bir hücreye kopyalayın veya girin:

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

2. Ardından, bu formülü ihtiyacınız olan diğer hücrelere doldurmak için doldurma tutamacını sürükleyin 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:

Notlar:

  • 1. Yukarıdaki formülde, A2: A6 daha küçük değerlerin aralığıdır ve B2: B6 veri aralığınızdaki daha büyük sayıların aralığıdır, E2 karşılık gelen değerini almak istediğiniz verilen değerdir, C2: C6 ayıklamak istediğiniz sütun verileridir.
  • 2. Bu formül, aşağıda gösterilen ekran görüntüsü gibi iki tarih arasında eşleşen değerleri çıkarmak için de kullanılabilir:

Vlookup yararlı bir özellik ile verilen iki değer veya tarih arasındaki değerleri eşleştirme

Yukarıdaki formülle acı çekiyorsanız, burada size kolay bir araç sunacağım - Kutools for Excel, Onun ile İki Değer Arasında ARA özelliği, herhangi bir formülü hatırlamadan iki değer veya tarih arasındaki belirli değere veya tarihe göre karşılık gelen öğeyi döndürebilirsiniz.   Kutools for Excel'i şimdi indirmek için tıklayın!


6. Vlookup 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ştirmeyi sağlayan Vlookup işlevi içinde 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 Vlookup'ı 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. Normal Vlookup işlevi düzgün çalışmıyor, metin veya hücre referansını bir joker karakterle birleştirmeniz gerekiyor, lütfen aşağıdaki formülü boş bir hücreye kopyalayın veya girin:

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

2. Ardından, bu formülü ihtiyacınız olan diğer hücrelere doldurmak için doldurma tutamacını sürükleyin ve tüm eşleşen puanlar aşağıda gösterilen ekran görüntüsü gibi döndürülür:

Notlar:

  • 1. Yukarıdaki formülde, E2 ve ”*” arama değeri, içindeki değer E2 ve * joker karakter ("*" herhangi bir karakteri veya herhangi bir karakteri belirtir), A2: C11 arama aralığı, sayı 3 döndürülecek değeri içeren sütun.
  • 2. Joker karakter kullanırken Vlookup, Vlookup işlevindeki son bağımsız değişken için FALSE veya 0 ile tam eşleşme modunu ayarlamalısınız.

İpuçları:

1. Belirli bir değerle biten eşleşen değerleri bulun ve döndürün, lütfen şu formülü uygulayın: =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2. Belirtilen metin metin dizesinin önünde, arkasında veya ortasında olsun, metin dizesinin bir kısmına göre eşleşen değeri aramak ve döndürmek için, hücre başvurusu veya metnin etrafında iki * karakteri birleştirmeniz yeterlidir. Lütfen bu formülü uygulayın: =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


7. Başka bir çalışma sayfasından Vlookup değerleri

Genellikle, birden fazla çalışma sayfasıyla çalışmanız gerekebilir, Vlookup işlevi, bir çalışma sayfasındaki gibi başka bir sayfadaki verileri 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. Lütfen aşağıdaki formülü, eşleşen öğeleri almak istediğiniz boş bir hücreye girin veya kopyalayın:

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

2. Ardından, doldurma tutamacını bu formülü uygulamak istediğiniz hücrelere sürükleyin ve ihtiyacınız olan ilgili sonuçları alacaksınız, ekran görüntüsüne bakın:

Not: Yukarıdaki formülde:

  • A2 arama değerini temsil eder;
  • Veri Sayfası veri aramak istediğiniz çalışma sayfasının adıdır, (Sayfa adı boşluk veya noktalama karakterleri içeriyorsa, sayfa adının etrafına tek tırnak işareti koymalısınız, aksi takdirde sayfa adını doğrudan kullanabilirsiniz = DÜŞEYARA (A2, Veri sayfası! $ A $ 2: $ C $ 15,3,0));
  • A2: C15 veri aradığımız Veri Sayfasındaki veri aralığıdır;
  • sayısını 3 geri dönmek istediğiniz eşleşen verileri içeren sütun numarasıdır.

8. Başka bir çalışma kitabındaki Vlookup değerleri

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

Örneğin, ilk çalışma kitabı ürün ve maliyet listelerini içerir, şimdi, ikinci çalışma kitabındaki ilgili maliyeti, aşağıda gösterilen ekran görüntüsü gibi ürün öğesine dayalı olarak çıkarmak istiyorsunuz.

1. Göreli maliyeti başka bir çalışma kitabından almak için, önce kullanmak istediğiniz her iki çalışma kitabını da açın, ardından aşağıdaki formülü sonucu koymak istediğiniz hücreye uygulayın:

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

2. Ardından, bu formülü ihtiyacınız olan diğer hücrelere sürükleyip kopyalayın, ekran görüntüsüne bakın:

Notlar:

  • 1. Yukarıdaki formülde:
    B2 arama değerini temsil eder;
    [Ürün listesi.xlsx] Sayfa1 veri aramak istediğiniz çalışma kitabının ve çalışma sayfasının adıdır, (Çalışma kitabına yapılan başvuru köşeli parantez içine alınır ve tüm çalışma kitabı + sayfası tek tırnak içine alınır);
    A2: B6 veri aradığımız başka bir çalışma kitabının çalışma sayfasındaki veri aralığıdır;
    sayısını 2 geri dönmek istediğiniz eşleşen verileri içeren sütun numarasıdır.
  • 2. Arama çalışma kitabı kapatılırsa, arama çalışma kitabının tam dosya yolu, aşağıdaki ekran görüntüsü gibi formülde gösterilir:

9. Vlookup ve 0 veya # N / A hata değeri yerine boş veya belirli bir metin döndür

Normalde, karşılık gelen değeri döndürmek için vlookup işlevini uyguladığınızda, eşleşen hücreniz boşsa, 0 döndürür ve eşleşen değeriniz bulunmazsa, aşağıda gösterilen ekran görüntüsü gibi bir hata #YOK değeri alırsınız. 0 veya # N / A değerini boş hücre veya istediğiniz başka bir değerle görüntülemek yerine, bu Vlookup 0 Veya Yok Yerine Boş Veya Belirli Bir Değer Döndürmek İçin öğretici size adım adım bir iyilik yapabilir.


Gelişmiş DÜŞEYARA örnekleri

1. Vlookup işlevi ile iki yönlü arama (satır ve sütunda Vlookup)

Bazen, aynı anda hem satır hem de sütunda Vlookup anlamına gelen 2 boyutlu bir arama yapmanız gerekebilir. Diyelim ki, aşağıdaki veri aralığına sahipseniz ve şimdi, belirli bir ürün için değeri belirli bir çeyrekte almanız gerekebilir. Bu bölüm, bu işi Excel'de halletmek için bazı formülleri tanıtacaktır.

Formül 1: DÜŞEYARA ve KAÇINCI işlevlerini kullanma

Excel'de, iki yönlü arama yapmak için DÜŞEYARA ve KAÇINCI işlevlerinin bir kombinasyonunu kullanabilirsiniz, lütfen aşağıdaki formülü boş bir hücreye uygulayın ve ardından tuşuna basın Keşfet sonucu almak için anahtar.

=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

Not: Yukarıdaki formülde:

  • H1: ilgili değeri temel alarak almak istediğiniz sütundaki arama değeri;
  • A2: E6: satır başlıklarını içeren veri aralığı;
  • H2: karşılık gelen değeri temel alarak almak istediğiniz satırdaki arama değeri;
  • A1: E1: sütun başlıklarının hücreleri.

Formül 2: INDEX ve MATCH işlevlerini kullanma

İşte başka bir formül de 2 boyutlu bir arama yapmanıza yardımcı olabilir, lütfen aşağıdaki formülü uygulayın ve ardından Keşfet ihtiyacınız olan sonucu elde etmek için anahtar.

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

Not: Yukarıdaki formülde:

  • B2: E6: eşleşen öğenin döndürüleceği veri aralığı;
  • H1: ilgili değeri temel alarak almak istediğiniz sütundaki arama değeri;
  • A2: A6: satır başlıkları, aramak istediğiniz ürünü içerir.
  • H2: karşılık gelen değeri temel alarak almak istediğiniz satırdaki arama değeri;
  • B1: E1: sütun başlıkları, aramak istediğiniz çeyreği içerir.

2. İki veya daha fazla kritere göre Vlookup eşleştirme değeri

Eşleşen değeri bir kritere göre aramak sizin için kolaydır, ancak iki veya daha fazla kriteriniz varsa ne yapabilirsiniz? Excel'deki ARA veya MATCH ve INDEX 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.

Formül 1: ARA işlevini kullanma

Lütfen aşağıdaki formülü sonucu almak istediğiniz hücreye uygulayın ve ardından Enter tuşuna basın, ekran görüntüsüne bakın:

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

Notlar:

  • 1. Yukarıdaki formülde:
    A2: A12 = G1: A1: A2 aralığında G12 kriterlerini aramak anlamına gelir;
    B2: B12 = G2: B2: B2 aralığında G12 kriterlerini aramak anlamına gelir;
    D2: D12: karşılık gelen değeri döndürmek istediğiniz aralık.
  • 2. İkiden fazla kriteriniz varsa, diğer kriterleri formülde birleştirmeniz yeterlidir, örneğin: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

Formül 2: INDEXT ve MATCH işlevlerini kullanma

Dizin ve Eşleşme işlevinin kombinasyonu, birden çok ölçüte dayalı olarak eşleşen değeri döndürmek için de kullanılabilir. Lütfen aşağıdaki formülü kopyalayın veya girin:

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

Ardından, ihtiyacınız olan göreli değeri elde etmek için Ctrl + Shift + Enter tuşlarına birlikte basın. Ekran görüntüsüne bakın:

Notlar:

  • 1. Yukarıdaki formülde:
    A2: A12 = G1: A1: A2 aralığında G12 kriterlerini aramak anlamına gelir;
    B2: B12 = G2: B2: B2 aralığında G12 kriterlerini aramak anlamına gelir;
    D2: D12: karşılık gelen değeri döndürmek istediğiniz aralık.
  • 2. İkiden fazla kriteriniz varsa, yeni kriterleri formüle dahil etmeniz yeterlidir, örneğin: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

3. Bir veya daha fazla koşulla birden çok eşleşen değer döndürmek için Vlookup

Excel'de, Vlookup işlevi bir değer arar ve yalnızca birden çok karşılık gelen 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, sütunda veya tek bir hücrede döndürmek isteyebilirsiniz. Bu bölümde, bir çalışma kitabındaki bir veya daha fazla koşulla birden çok eşleşen değerin nasıl döndürüleceği hakkında konuşulacaktır.

Yatay olarak bir veya daha fazla koşula dayalı olarak tüm eşleşen değerleri görüntüleyin

Yatay olarak bir koşula dayalı tüm eşleşen değerleri Vlookup:

Vlookup'a ve tüm eşleşen değerleri yatay olarak belirli bir değere dayalı olarak döndürmek için genel formül şu şekildedir:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
not: m dönüş aralığındaki ilk hücrenin satır numarası eksi 1'dir.
      n ilk formül hücresinin sütun numarası eksi 1'dir.

1. Lütfen aşağıdaki formülü boş bir hücreye uygulayın ve ardından 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:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Ve sonra, ilk formül hücresini seçin ve doldurma tutamacını boş hücre görüntülenene kadar sağ hücrelere sürükleyin ve karşılık gelen tüm öğeler çıkarıldı, ekran görüntüsüne bakın:

İpuçları:

Döndürülen listede yinelenen eşleşen değerler varsa, yinelemeleri yok saymak için lütfen bu formülleri kullanın ve ardından Keşfet ilk sonucu almak için: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

Bu formülü girmeye devam edin: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") ilk sonucun yanındaki bir hücreye girin ve ardından Ctrl + Üst Karakter + Enter ikinci sonucu almak için anahtarları bir araya getirin, ardından boş hücre görüntülenene kadar diğer tüm eşleşen değerleri almak için bu formülü sağ hücrelere sürükleyin, ekran görüntüsüne bakın:


Yatay olarak iki veya daha fazla koşula dayalı olarak tüm eşleşen değerleri görüntüleyin:

Vlookup'a ve tüm eşleşen değerleri yatay olarak daha spesifik değerlere göre döndürmek için genel formül şu şekildedir:

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
not: m dönüş aralığındaki ilk hücrenin satır numarası eksi 1'dir.
      n ilk formül hücresinin sütun numarası eksi 1'dir.

1. Aşağıdaki formülü, sonucun çıktısını almak istediğiniz boş bir hücreye uygulayın:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Ardından, formül hücresini seçin ve doldurma tutamacını boş hücre görüntülenene kadar sağ hücrelere sürükleyin ve belirli kriterlere göre eşleşen tüm değerler döndürüldü, ekran görüntüsüne bakın:

not: Daha fazla kriter için, aranan_değer ve aranan_aralığı formüle dahil etmeniz yeterlidir, örneğin: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


Dikey olarak bir veya daha fazla koşula dayalı olarak tüm eşleşen değerleri görüntüleyin

Dikey olarak bir koşula göre tüm eşleşen değerleri Vlookup:

Vlookup'a ve tüm eşleşen değerleri dikey olarak belirli bir değere göre döndürmek için genel formül şu şekildedir:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
not: m dönüş aralığındaki ilk hücrenin satır numarası eksi 1'dir.
      n ilk formül hücresinin satır numarası eksi 1'dir.

1. Aşağıdaki formülü, sonucu almak istediğiniz hücreye kopyalayın veya yazın ve ardından 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:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Ardından, ilk formül hücresini seçin ve boş hücre görüntülenene ve ilgili tüm öğeler bir sütunda listelenene kadar doldurma tutamacını diğer hücrelere sürükleyin, ekran görüntüsüne bakın:

İpuçları:

Döndürülen eşleşen değerlerdeki kopyaları yok saymak için lütfen şu formülleri kullanın: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

Daha sonra tuşuna basın. Ctrl + Üst Karakter + Enter ilk eşleşen değeri elde etmek için anahtarları bir araya getirin ve ardından bu formül hücresini boş hücre görüntülenene kadar diğer hücrelere sürükleyin; istediğiniz gibi sonucu alırsınız:


Dikey olarak iki veya daha fazla koşula göre eşleşen tüm değerleri Vlookup:

Vlookup'a ve daha spesifik değerlere dayalı olarak tüm eşleşen değerleri dikey olarak döndürmek için genel formül şu şekildedir:

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
not: m dönüş aralığındaki ilk hücrenin satır numarası eksi 1'dir.
      n ilk formül hücresinin satır numarası eksi 1'dir.

1. Aşağıdaki formülü boş bir hücreye kopyalayın ve ardından Ctrl + Üst Karakter + Enter ilk eşleşen öğeyi almak için anahtarları bir araya getirin.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20)) * (--($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Ardından, formül hücresini boş hücre görüntülenene kadar diğer hücrelere sürükleyin, ekran görüntüsüne bakın:

not: Daha fazla kriter için, aranan_değer ve aranan_aralığı formüle dahil etmeniz yeterlidir, örneğin: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


İki veya daha fazla koşula dayalı tüm eşleşen değerleri tek hücreye bak

Vlookup'ı kullanmak 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.

Tek bir koşula dayalı tüm eşleşen değerleri tek hücreye bak:

Lütfen aşağıdaki basit formülü boş bir hücreye uygulayın ve ardından Ctrl + Üst Karakter + Enter sonucu almak için anahtarları bir araya getirin:

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

İpuçları:

Döndürülen eşleşen değerlerdeki kopyaları yok saymak için lütfen şu formülleri kullanın: =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


İki veya daha fazla koşula dayalı tüm eşleşen değerleri tek hücreye bak:

Tüm eşleşen değerleri tek bir hücreye döndürürken birden fazla koşulla uğraşmak için lütfen aşağıdaki formülü uygulayın ve ardından tuşuna basın Ctrl + Üst Karakter + Enter sonucu almak için anahtarları bir araya getirin:

=TEXTJOIN(",",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

Notlar:

1. METİN BİRLEŞTİR işlevi yalnızca Excel 2019 ve Office 365'te kullanılabilir.

2. Excel 2016 ve önceki sürümleri kullanıyorsanız, lütfen aşağıdaki makalelerin Kullanıcı Tanımlı İşlevini kullanın:


4. Vlookup eşleşen bir hücrenin tamamını veya tamamını döndürmek için

Bu bölümde, Vlookup işlevini kullanarak eşleşen bir değerin tüm satırının nasıl alınacağından bahsedeceğim.

1. Lütfen aşağıdaki formülü, sonucun çıktısını almak istediğiniz boş bir hücreye kopyalayın veya yazın ve Keşfet ilk değeri almak için anahtar.

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

2. Ardından, formül hücresini tüm satırın verileri görüntülenene kadar sağa sürükleyin, ekran görüntüsüne bakın:

not: Yukarıdaki formülde, F2 tüm satırı temel alarak döndürmek istediğiniz arama değeridir, A1: D12 kullanmak istediğiniz veri aralığı, A1 veri aralığınızdaki ilk sütun numarasını gösterir.

İpuçları:

Eşleşen değere göre birden fazla satır bulunursa, karşılık gelen tüm satırları döndürmek için lütfen şu formülü uygulayın: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),"")tuşuna basın ve ardından tuşuna basın. Ctrl + Üst Karakter + Enter ilk sonucu almak için tuşları bir araya getirin, ardından doldurma tutamacını hücrelere doğru sürükleyin, ekran görüntüsüne bakın:

Ardından, tüm eşleşen satırları aşağıda gösterilen ekran görüntüsü gibi almak için doldurma tutamacını hücreler boyunca aşağı doğru sürükleyin:


5. Excel'de birden çok Vlookup işlevi (iç içe geçmiş Vlookup) yapın

Bazen, birden çok tablodaki değerleri aramak isteyebilirsiniz, eğer tablolardan herhangi biri aşağıda gösterilen ekran görüntüsü gibi verilen arama değerini içeriyorsa, bu durumda, birden fazla arama gerçekleştirmek için bir veya daha fazla Vlookup işlevini EĞERHATA işlevi ile birleştirebilirsiniz.

İç içe geçmiş Vlookup işlevi için genel formül şudur:

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

Not:

  • lookup_value: aradığınız değer;
  • Table1, Table2, Table3, ...: arama değerinin ve dönüş değerinin bulunduğu tablolar;
  • col: eşleşen değeri döndürmek istediğiniz tablodaki sütun numarası.
  • 0: Bu, tam eşleşme için kullanılır.

1. Lütfen aşağıdaki formülü sonucu koymak istediğiniz boş bir hücreye uygulayın:

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

2. Ardından, doldurma tutamacını bu formülü uygulamak istediğiniz hücrelere sürükleyin ve eşleşen tüm değerler aşağıda gösterilen ekran görüntüsü gibi döndürülür:

Notlar:

  • 1. Yukarıdaki formülde, J3 aradığınız değer; A3: B7, D3: E7, G3: H7 arama değerinin ve dönüş değerinin bulunduğu tablo aralıklarıdır; Numara 2 aralıktaki eşleşen değerin döndürüleceği sütun numarasıdır.
  • 2. Arama değeri bulunamazsa, bir hata değeri görüntülenir, hatayı okunabilir bir metinle değiştirmek için lütfen şu formülü kullanın: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

6. Vlookup, başka bir sütundaki bir liste verilerine göre değer olup olmadığını kontrol etmek için

Vlookup işlevi ayrıca, değerlerin başka bir listeye dayalı olup olmadığını kontrol etmenize yardımcı olabilir, örneğin, C sütunundaki isimleri aramak ve aşağıdaki ekran görüntüsü gibi A sütununda ad bulunursa veya yoksa Evet veya Hayır'ı döndürmek isterseniz gösterilen.

1. Lütfen aşağıdaki formülü boş bir hücreye uygulayın:

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

2. Ardından, doldurma tutamacını bu formülü doldurmak istediğiniz hücrelere sürükleyin ve istediğiniz sonucu elde edersiniz, ekran görüntüsüne bakın:

not: Yukarıdaki formülde, C2 kontrol etmek istediğiniz arama değeridir; A2: A10 arama değerlerinin bulunacağı yerin listesidir; numara 1 , aralığınızdaki değeri almak istediğiniz yerden sütun numarasıdır.


7. Vlookup ve tüm eşleşen değerleri satırlar veya sütunlar halinde toplayın

Sayısal verilerle çalışıyorsanız, bazen tablodan eşleşen değerleri çıkarırken, sayıları birkaç sütun veya satırda toplamanız da gerekebilir. Bu bölüm, bu işi Excel'de bitirmek için bazı formülleri tanıtacaktır.

Vlookup ve tüm eşleşen değerleri bir satırda veya birden çok satırda topla

Diyelim ki, aşağıda gösterilen ekran görüntüsü gibi birkaç aylık satışları olan bir ürün listeniz var, şimdi, tüm aylardaki tüm siparişleri verilen ürünlere göre toplamanız gerekiyor.

Vlookup ve arka arkaya ilk eşleşen değerleri toplayın:

1. Lütfen aşağıdaki formülü boş bir hücreye kopyalayın veya girin ve ardından Ctrl + Üst Karakter + Enter ilk sonucu almak için anahtarları bir araya getirin.

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

2. Ardından, bu formülü ihtiyacınız olan diğer hücrelere kopyalamak için doldurma tutamacını aşağı sürükleyin ve ilk eşleşen değerin bir satırındaki tüm değerler bir araya toplandı, ekran görüntüsüne bakın:

not: Yukarıdaki formülde: H2 aradığınız değeri içeren hücredir; A2: F9 arama değerini ve eşleşen değerleri içeren veri aralığıdır (sütun başlıkları olmadan); Numara 2,3,4,5,6 {} aralığın toplamını hesaplamak için kullanılan sütun numaralarıdır.


Vlookup ve tüm eşleşen değerleri birden çok satırda topla:

Yukarıdaki formül, yalnızca ilk eşleşen değer için bir satırdaki değerleri toplayabilir. Tüm eşleşmeleri birden çok satırda toplamak istiyorsanız, lütfen aşağıdaki formülü kullanın ve ardından doldurma tutamacını bu formülü uygulamak istediğiniz hücrelere sürükleyin ve ihtiyacınız olan istenen sonucu alacaksınız, ekran görüntüsüne bakın:

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

not: Yukarıdaki formülde: H2 aradığınız arama değeridir; A2: A9 arama değerini içeren satır başlıklarıdır; B2: F9 toplamak istediğiniz sayısal değerlerin veri aralığı.


Vlookup ve eşleşen tüm değerleri bir sütunda veya birden çok sütunda topla

Vlookup ve bir sütundaki ilk eşleşen değerleri 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.

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; ş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:

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

not: Yukarıdaki formülde: H2 aradığınız arama değeridir; B1: F1 arama değerini içeren sütun başlıklarıdır; B2: F9 toplamak istediğiniz sayısal değerlerin veri aralığı.


Vlookup ve eşleşen tüm değerleri birden çok sütunda topla:

Vlookup ve tüm eşleşen değerleri birden çok sütunda toplamak için aşağıdaki formülü kullanmalısınız:

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

not: Yukarıdaki formülde: H2 aradığınız arama değeridir; B1: F1 arama değerini içeren sütun başlıklarıdır; B2: F9 toplamak istediğiniz sayısal değerlerin veri aralığı.


Vlookup 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 zordur, bu durumda kullanışlı bir özellik önereceğim - Arama ve Toplam of Kutools for Excel, bu özellik ile sonucu olabildiğince kolay alabilirsiniz.    Kutools for Excel'i şimdi indirmek için tıklayın!


Vlookup ve tüm eşleşen değerleri hem satırlarda hem de sütunlarda topla

Ö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.

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

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

not: Yukarıdaki formülde: B2: F9 toplamak istediğiniz sayısal değerlerin veri aralığıdır; B1: F1 is sütun başlıkları, temel alarak toplamak istediğiniz arama değerini içerir; I2 aradığınız sütun başlıkları içindeki arama değeridir; A2: A9 satır üstbilgileri, temel alarak toplamak istediğiniz arama değerini içerir; H2 aradığınız satır başlıkları içindeki arama değeridir.


8. Bir veya daha fazla anahtar sütuna göre iki tabloyu birleştirmek için Vlookup

Günlük çalışmalarınızda verileri analiz ederken gerekli tüm bilgileri bir veya daha fazla anahtar sütuna göre tek bir tabloda toplamanız gerekebilir. Bu işi çözmek için Vlookup işlevi de size bir iyilik yapabilir.

Vlookup, bir anahtar sütuna göre iki tabloyu birleştirmek için

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

Formül 1: DÜŞEYARA işlevini kullanma

İki tabloyu bir anahtar sütuna dayalı olarak birleştirmek için, lütfen aşağıdaki formülü sonucu almak istediğiniz boş bir hücreye uygulayın ve ardından doldurma tutamacını bu formülü uygulamak istediğiniz hücrelere sürükleyin. Anahtar sütun verilerine dayalı olarak ilk tablo verilerine katılan sıra sütunu ile birleştirilmiş bir tablo elde edin.

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

not: Yukarıdaki formülde, A2 aradığınız değer E2: F8 aranacak tablodur, numara 2 değerin alınacağı tablodaki sütun numarasıdır.

Formül 2: INDEX ve MATCH işlevlerini kullanma

Sıra sütununu birleştirmek için sağ taraftaki ortak verileriniz ve ikinci tabloda sol sütunda döndürülen veriler varsa, Vlookup işlevi işi yapamaz. Sağdan sola doğru bakmak için, Vlookup işlevini değiştirmek için DİZİN ve KAÇINCI işlevlerini kullanabilirsiniz.

Lütfen aşağıdaki formülü boş bir hücreye kopyalayın veya girin, ardından formülü sütunun altına kopyalayın ve sıra sütunu ilk tabloya birleştirildi, ekran görüntüsüne bakın:

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

not: Yukarıdaki formülde, A2 aradığınız arama değeri, E2: E8 döndürmek istediğiniz veri aralığıdır, F2: F8 arama değerini içeren arama aralığıdır.


Vlookup, birden çok anahtar sütuna dayalı iki tabloyu birleştirmek için

Birleştirmek istediğiniz iki tablonun birden fazla anahtar sütunu varsa, tabloları bu ortak sütunlara göre birleştirmek için INDEX ve MATCH işlevleri size yardımcı olabilir.

Birden çok anahtar sütuna göre iki tabloyu birleştirmek için genel formül şudur:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

1. 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)

not: Yukarıdaki formülde, aşağıda gösterilen ekran görüntüsü gibi hücre referansları neyi temsil eder:

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

İpuçları: Excel 2016 ve sonraki sürümlerde, ayrıca Güç Sorgu anahtar sütunlara göre iki veya daha fazla tabloyu bir araya getirme özelliği. Detayları adım adım öğrenmek için lütfen tıklayınız..

9. Birden çok çalışma sayfasında Vlookup eşleşen değerler

Hiç birden çok çalışma sayfasında değerleri Vlookup yapmayı denediniz mi? Aşağıdaki veri aralığına sahip aşağıdaki üç çalışma sayfasına sahip olduğumu varsayarsak ve şimdi, aşağıda gösterilen ekran görüntüsü gibi sonucu almak için bu üç çalışma sayfasındaki kriterlere göre karşılık gelen değerlerin bir kısmını almak istiyorum. Bu durumda, Birden Çok Çalışma Sayfasında Vlookup Değerleri öğretici size adım adım bir iyilik yapabilir.


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

1. Arama değeriyle birlikte hücre biçimlendirmesini (hücre rengi, yazı tipi rengi) almak için Vlookup

Hepimizin bildiği gibi, normal Vlookup işlevi yalnızca eşleşen değeri başka bir veri aralığından döndürmemize yardımcı olabilir, ancak bazen dolgu rengi, yazı tipi rengi, yazı tipi stili gibi hücre biçimlendirmesiyle birlikte karşılık gelen değeri döndürmek isteyebilirsiniz. aşağıda gösterilen ekran görüntüsü gibi. Bu bölümde, Excel'de döndürülen değerle hücre biçimlendirmesinin nasıl elde edileceği anlatılacaktır.

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:

1. Çalışma sayfasında Vlookup 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.

VBA kodu 1: Arama değeriyle birlikte hücre biçimlendirmesini almak için Vlookup

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

3. Hala içinde 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.

VBA kodu 2: Arama değeriyle birlikte hücre biçimlendirmesini almak için Vlookup

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. Yukarıdaki kodları ekledikten sonra, Araçlar > 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:

5. Daha sonra, OK iletişim kutusunu kapatmak ve ardından kod penceresini kaydedip kapatmak için, şimdi çalışma sayfasına geri dönün ve ardından şu formülü uygulayın: =LookupKeepFormat(E2,$A$1:$C$10,3) sonucun çıktısını almak istediğiniz boş bir hücreye girin ve ardından Enter tuşuna basın. Ekran görüntüsüne bakın:

not: Yukarıdaki formülde, E2 bakacağın değer A1: C10 tablo aralığı ve numarasıdır 3 eşleşen değerin döndürülmesini istediğiniz tablonun sütun numarasıdır.

6. Ardından, ilk sonuç hücresini seçin ve tüm sonuçları biçimlendirmeleriyle birlikte almak için dolgu tutamacını aşağı sürükleyin. Ekran görüntüsüne bakın.


2. Tarih biçimini Vlookup tarafından döndürülen bir değerden koruyun

Normalde, eşleşen tarih biçimi değerini aramak ve döndürmek için Vloook işlevini kullanırken, aşağıda gösterilen ekran görüntüsü gibi bazı sayı biçimleri görüntülenecektir. Tarih biçimini döndürülen bir sonuçtan korumak için, METİN işlevini Vlookup işlevine eklemelisiniz.

Lütfen 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ı sürükleyin ve tüm eşleşen tarihler aşağıda gösterilen ekran görüntüsü gibi döndürülmüştür:

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

not: Yukarıdaki formülde, E2 görünüş değeridir A2: C9 arama aralığı, sayı 3 değerin döndürülmesini istediğiniz sütun numarasıdır, mm/dd/yyy saklamak istediğiniz tarih biçimidir.


3. Vlookup ve hücre yorumuyla eşleşen değeri döndür

Hiç Vlookup'u yalnızca eşleşen hücre verilerini değil, aynı zamanda hücre açıklamasını ve Excel'de gösterilen aşağıdaki ekran görüntüsü gibi döndürmeyi denediniz mi? Bu görevi çözmek için aşağıdaki Kullanıcı Tanımlı İşlev size bir iyilik yapabilir.

1. Basılı tutun ALT + F11 tuşlarını açmak için Uygulamalar için Microsoft Visual Basic pencere.

2. tıklayın 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, şu formülü girin: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) Sonucu bulmak için boş bir hücreye yerleştirin ve ardından bu formülü diğer hücrelere kopyalamak için doldurma tutamacını sürükleyin; şimdi, eşleşen değerler ve yorumlar aynı anda döndürülür, ekran görüntüsüne bakın:

not: Yukarıdaki formülde, D2 karşılık gelen değerini döndürmek istediğiniz arama değeridir, A2: B9 kullanmak istediğiniz veri tablosu, numara 2 döndürmek istediğiniz eşleşen değeri içeren sütun numarasıdır.


4. Vlookup'ta metin ve gerçek sayılarla ilgilenin

Örneğin, bir veri aralığım var, orijinal tablodaki kimlik numarası sayı biçimidir, metin olarak saklanan arama hücresinde, normal Vlookup işlevi uygulanırken, aşağıdaki ekran görüntüsü gibi bir #YOK hata sonucu görüntüleniyor gösterilen. Bu durumda tablodaki arama numarası ve orijinal numara farklı veri formatına sahipse doğru bilgiyi nasıl elde edebilirsiniz?

Vlookup işlevindeki metin ve gerçek sayılarla uğraşmak için 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 ve aşağıdaki ekran görüntüsü gibi doğru sonuçları alacaksınız:

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

Notlar:

  • 1. Yukarıdaki formülde, D2 karşılık gelen değerini döndürmek istediğiniz arama değeridir, A2: B8 kullanmak istediğiniz veri tablosu, numara 2 döndürmek istediğiniz eşleşen değeri içeren sütun numarasıdır.
  • 2. Bu formül, nerede sayılarınız ve nerede metinleriniz olduğundan emin değilseniz de işe yarar.

DÜŞEYARA örnek dosyalarını indirin

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • Süper Formül Çubuğu (birden çok metin ve formül satırını kolayca düzenleyin); Okuma Düzeni (çok sayıda hücreyi kolayca okuyun ve düzenleyin); Filtrelenmiş Aralığa Yapıştır...
  • Hücreleri / Satırları / Sütunları Birleştirme ve Verilerin Saklanması; Bölünmüş Hücre İçeriği; Yinelenen Satırları ve Toplam / Ortalamayı Birleştirme... Yinelenen Hücreleri Önleyin; Aralıkları Karşılaştır...
  • Yinelenen veya Benzersiz'i seçin Satırlar; Boş Satırları Seçin (tüm hücreler boştur); Süper Bul ve Bulanık Bul Birçok Çalışma Kitabında; Rastgele Seçim ...
  • Tam kopya Formül referansını değiştirmeden Birden Çok Hücre; Otomatik Referans Oluştur Birden Çok Sayfaya; Madde İşaretleri Ekle, Onay Kutuları ve daha fazlası ...
  • Sık Kullanılan ve Hızlı Eklenen Formüller, Aralıklar, Grafikler ve Resimler; Hücreleri Şifrele şifre ile; Posta Listesi Oluşturun ve e-posta gönder ...
  • Metni Çıkar, Metin Ekle, Konuma Göre Kaldır, Alanı Kaldır; Sayfalama Alt Toplamları Oluşturma ve Yazdırma; Hücre İçeriği ve Yorumları Arasında Dönüştür...
  • Süper Filtre (filtre şemalarını kaydedin ve diğer sayfalara uygulayın); Gelişmiş Sıralama ay / hafta / gün, sıklık ve daha fazlasına göre; Özel Filtre kalın, italik ...
  • Çalışma Kitaplarını ve Çalışma Sayfalarını Birleştirin; Tabloları anahtar sütunlara göre birleştirin; Verileri Birden Çok Sayfaya Bölme; Toplu dönüştürme xls, xlsx ve PDF...
  • Pivot Tablo Gruplaması hafta numarası, haftanın günü ve daha fazlası ... Kilidi Açılmış, Kilitli Hücreleri Göster farklı renklerle; Formülü / Adı Olan Hücreleri Vurgulayın...
kte sekmesi 201905
  • Word, Excel, PowerPoint'te sekmeli düzenlemeyi ve okumayı etkinleştirin, Publisher, Access, Visio ve Project.
  • Yeni pencereler yerine aynı pencerenin yeni sekmelerinde birden çok belge açın ve oluşturun.
  • Üretkenliğinizi% 50 artırır ve her gün sizin için yüzlerce fare tıklamasını azaltır!
ofis tabanı
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.