Excel VLOOKUP ile Birden Fazla Veri Alanını Bul

Excel'in VLOOKUP işlevini COLUMN işleviyle birleştirerek, tek bir veritabanından veya veri tablosundan birden çok değer döndürmenize olanak veren bir arama formülü oluşturabiliriz.

Yukarıdaki resimde gösterilen örnekte, arama formülü, çeşitli donanım parçalarıyla ilgili tüm değerleri (fiyat, parça numarası ve tedarikçi gibi) geri döndürmeyi kolaylaştırır.

10'dan 10

Excel VLOOKUP ile Çoklu Değerleri İade Et

Aşağıda listelenen adımları izleyerek, yukarıdaki görüntüde görülen ve tek bir veri kaydından birden çok değer döndüren arama formülü oluşturur.

Arama formülü, COLUMN işlevinin VLOOKUP'un içine yerleştirilmesini gerektirir.

Bir fonksiyonun yerleştirilmesi, ikinci fonksiyonun ilk fonksiyon için argümanlardan biri olarak girilmesini içerir.

Bu öğreticide, COLUMN işlevi VLOOKUP için sütun dizin numarası argümanı olarak girilecektir.

Öğreticinin son adımı, seçilen bölüm için ek değerler almak için arama formülünü ek sütunlara kopyalamayı içerir.

Eğitim İçeriği

• Öğretici Verilerini Girme
• Veri Tablosu için bir Adlandırılmış Aralık Oluşturma
• VLOOKUP İşlevini Başlatma
• Mutlak Hücre Referanslarını kullanarak Arama Değeri Argümanını Girme
• Tablo Dizisi Bağımsız Değişkeni Girme
• İç içe geçmiş COLUMN işlevini girme
• VLOOKUP İşlevini Tamamlama
• Arama Formülünün Doldurma Koluna Kopyalanması
• Arama Formülü ile Veri Alma

02/10

Öğretici Verileri Girin

Öğreticideki ilk adım, verileri bir Excel çalışma sayfasına girmektir.

Öğreticideki adımları takip etmek için, yukarıdaki resimde gösterilen verileri aşağıdaki hücrelere girin .

• Üst veri aralığını D1 ila G1 hücrelerine girin
• İkinci aralığı D4 ila G10 hücrelerine girin

Bu eğitim sırasında oluşturulan arama kriterleri ve arama formülü çalışma sayfasının 2. satırına girilecektir.

Öğretici, görüntüde görülen formatlamayı içermez, ancak bu, arama formülünün nasıl çalıştığını etkilemez.

Yukarıda görülenlere benzer formatlama seçenekleri ile ilgili bilgiler, bu Temel Excel Formatting Eğitimi'nde mevcuttur .

Öğretici Adımlar

1. Yukarıdaki resimde görüldüğü gibi verileri D1 ila G10 hücrelerine girin.

10/10

Veri Tablosu için bir Adlandırılmış Aralık Oluşturma

Adlandırılmış aralık , bir formüldeki bir veri aralığına başvurmanın kolay bir yoludur. Veriler için hücre referanslarını yazmak yerine, aralığın adını yazabilirsiniz.

Adlandırılmış bir aralığı kullanmanın ikinci bir avantajı, formülün çalışma sayfasındaki diğer hücrelere kopyalandığında bile, bu aralık için hücre referanslarının hiçbir zaman değişmeyeceğidir.

Aralık adları, bu nedenle, formül kopyalanırken hataların önlenmesi için mutlak hücre referanslarının kullanılmasına bir alternatiftir.

Not: Aralık adı, veriler için başlıklar veya alan adlarını (satır 4) içermez, yalnızca veriyi kendisi içerir.

Öğretici Adımlar

1. Seçmek için çalışma sayfasında D5 ila G10 hücrelerini vurgulayın
2. A sütununun üstünde bulunan Ad kutusuna tıklayın.
3. Ad kutusuna "Tablo" (tırnak işareti yok) yazın
4. Klavyede ENTER tuşuna basın
5. D5 ile G10 arasındaki hücreler artık "Tablo" aralığına sahip. VLOOKUP tablo dizisi argümanının adını öğreticinin daha sonra kullanacağız

04/10

VLOOKUP İletişim Kutusunu Açma

Arama formumuzu doğrudan çalışma sayfasındaki bir hücreye yazmak mümkün olsa da, birçok kişi sözdizimini düz tutmayı zor buluyor - özellikle de bu derste kullandığımız gibi karmaşık bir formül için.

Bu durumda bir alternatif VLOOKUP iletişim kutusunu kullanmaktır . Excel'in işlevlerinin hemen hemen hepsi, işlevin argümanlarının her birini ayrı bir satıra girmenizi sağlayan bir iletişim kutusuna sahiptir.

Öğretici Adımlar

1. Çalışma sayfasının E2 hücresine tıklayın - iki boyutlu arama formülü sonuçlarının görüntüleneceği yer
2. Kurdelenin Formüller sekmesini tıklayın.
3. İşlev açılır menüsünü açmak için şeritteki Arama ve Referans seçeneğine tıklayın.
4. İşlev iletişim kutusunu açmak için listede VLOOKUP'a tıklayın.

05/10

Mutlak Hücre Referanslarını kullanarak Arama Değeri Argümanını Girme

Normalde, arama değeri , veri tablosunun ilk sütunundaki bir veri alanıyla eşleşir.

Örneğimizde, arama değeri , bilgi bulmak istediğimiz donanım bölümünün adını ifade eder.

Arama değeri için izin verilen veri türleri şunlardır:

• metin verileri
• mantıksal bir değer (yalnızca TRUE veya FALSE)
• bir sayı
• çalışma sayfasındaki bir değere bir hücre başvurusu

Bu örnekte, hücre ismini, bölüm isminin bulunacağı yere gireceğiz - hücre D2.

Mutlak Hücre Referansları

Eğiticinin sonraki adımlarında, E2 hücresindeki arama formülünü F2 ve G2 hücrelerine kopyalayacağız.

Normalde, formüller Excel'de kopyalandığında, hücre referansları yeni konumlarını yansıtacak şekilde değişir.

Bu olursa, D2 - arama değeri için hücre referansı - formül F2 ve G2 hücrelerinde hatalar oluşturmada kopyalandığı için değişecektir.

Hataları önlemek için, hücre referansı D2'yi mutlak bir hücre referansına dönüştüreceğiz .

Formüller kopyalandığında mutlak hücre referansları değişmez.

Mutlak hücre referansları, klavyedeki F4 tuşuna basılarak oluşturulur. Bunu yapmak, $ D $ 2 gibi hücre referansı etrafında dolar işaretleri ekler

Öğretici Adımlar

1. İletişim kutusundaki lookup_value satırına tıklayın
2. Bu hücre referansını lookup_value satırına eklemek için D2 hücresini tıklayın. Bu bilgiyi aradığımız bölüm ismini yazacağımız hücre
3. Ekleme noktasını hareket ettirmeden, D2'yi mutlak hücre referansına dönüştürmek için klavyedeki F4 tuşuna basın. $ D $ 2
4. Öğreticide sonraki adım için VLOOKUP işlevi iletişim kutusunu açık bırakın

06/10

Tablo Dizisi Bağımsız Değişkeni Girme

Tablo dizisi, arama formülü, istediğimiz bilgileri bulmak için arama verileridir.

Tablo dizisi en az iki sütun sütunu içermelidir.

• İlk sütun arama değeri argümanını içerir (öğreticide önceki adım)
• İkinci ve ek sütunlar, belirttiğimiz bilgileri bulmak için arama formülü tarafından aranacaktır.

Tablo dizisi argümanı, veri tablosu için hücre referanslarını veya aralık adı olarak bir aralık olarak girilmelidir.

Bu örnekte, öğreticinin 3. adımında oluşturulan aralık adını kullanacağız.

Öğretici Adımlar

1. İletişim kutusundaki table_array satırına tıklayın
2. Bu bağımsız değişken için aralık adını girmek için "Tablo" (tırnak işareti yok) yazın
3. Öğreticide sonraki adım için VLOOKUP işlevi iletişim kutusunu açık bırakın

07/10

COLUMN İşlevinin Yerleştirilmesi

Normalde VLOOKUP sadece bir veri tablosunun bir sütunundan veri döndürür ve bu sütun sütun indeks numarası argümanı tarafından ayarlanır.

Bununla birlikte, bu örnekte, verileri döndürmek istediğimiz üç sütuna sahibiz. Bu nedenle, arama formumuzu düzenlemeden sütun dizin numarasını kolayca değiştirmek için bir yola ihtiyacımız var.

Bu, COLUMN işlevinin girdiği yerdir. Sütun dizin numarası bağımsız değişkeni olarak girildiğinde, arama formülü, öğreticide D2 hücresinden E2 ve F2 hücrelerine kopyalandıktan sonra değişecektir.

Yuvalama İşlevleri

Bu nedenle, COLUMN işlevi VLOOKUP'un sütun dizin numarası bağımsız değişkeni olarak işlev görür.

Bu, iletişim kutusunun Col_index_num satırında VLOOKUP içindeki COLUMN işlevinin yerleştirilmesiyle gerçekleştirilir.

COLUMN İşlevini Manuel Olarak Girme

Yuvalama işlevleri olduğunda, Excel, argümanlarını girmek için ikinci fonksiyonun iletişim kutusunu açmamıza izin vermez.

Bu nedenle, COLUMN işlevi Col_index_num satırında manuel olarak girilmelidir.

COLUMN işlevinin yalnızca bir bağımsız değişkeni vardır: bir hücre başvurusu olan Reference argümanı.

COLUMN İşlevinin Referans Argümanı Seçme

COLUMN işlevinin görevi, Reference argümanı olarak verilen sütunun numarasını döndürmektir.

Başka bir deyişle, sütun harfini, A sütununun ilk sütunu, ikinci sütunu B ve benzeri ile bir sayıya dönüştürür.

İade edilmesini istediğimiz ilk veri alanı, öğenin fiyatının - yani, veri tablosunun iki sütununda - olduğundan dolayı, 2 numaralı sayıyı elde etmek için Referans Argümanı olarak B sütununda herhangi bir hücre için hücre referansı seçebiliriz. Col_index_num bağımsız değişkeni.

Öğretici Adımlar

1. VLOOKUP işlevi iletişim kutusunda Col_index_num satırını tıklayın.
2. İşlev adı sütununu ve ardından açık bir yuvarlak köşeli ayraç yazın " ( "
3. Referans argümanı olarak bu hücre referansını girmek için çalışma sayfasındaki B1 hücresini tıklayın.
4. COLUMN işlevini tamamlamak için kapanış yuvarlak köşeli ayraç " ) " yazın
5. Öğreticide sonraki adım için VLOOKUP işlevi iletişim kutusunu açık bırakın

08/10

VLOOKUP Range Arama Argümanı Girme

VLOOKUP'ın Range_lookup argümanı, VLOOKUP'un Lookup_value ile tam veya yaklaşık bir eşleşme bulmasını isteyip istemediğinizi gösteren bir mantıksal değerdir (yalnızca DOĞRU veya YANLIŞ).

• TRUE veya bu argüman atlanırsa, VLOOKUP, Lookup_value öğesinin tam eşleşmesini döndürür veya tam eşleşme bulunamazsa, VLOOKUP bir sonraki en büyük değeri döndürür. Formülün bunu yapması için, Table_array öğesinin ilk sütunundaki veriler , artan sırada sıralanmalıdır .
• FALSE ise, VLOOKUP sadece Lookup_value ile tam bir eşleşme kullanır. Tablo_dizisinin ilk sütununda arama değeriyle eşleşen iki veya daha fazla değer varsa, bulunan ilk değer kullanılır. Tam eşleşme bulunamazsa, # N / A hatası döndürülür.

Bu eğiticide, belirli bir donanım öğesi hakkında özel bilgi aradığımız için, Range_lookup öğesini False değerine eşit olarak ayarlayacağız.

Öğretici Adımlar

1. İletişim kutusundaki Range_lookup satırına tıklayın
2. VLOOKUP'un aradığımız veriler için tam bir eşleşme döndürmesini istediğimizi belirtmek için bu satırdaki Yanlış sözcüğü yazın.
3. Arama formülü tamamlamak ve iletişim kutusunu kapatmak için Tamam'ı tıklatın.
4. Arama ölçütlerini henüz D2 hücresine girmediğimizden, E2 hücresinde # N / A hatası mevcut olacak
5. Bu hata, öğreticinin son adımında arama ölçütlerini ekleyeceğimiz zaman düzeltilecektir.

09/10

Arama Formülünün Doldurma Koluna Kopyalanması

Arama formülü, bir defada veri tablosunun çoklu sütunlarından veri almayı amaçlamaktadır.

Bunu yapmak için, arama formülü bilgi istediğimiz tüm alanlarda bulunmalıdır.

Bu öğreticide, veri tablosunun 2, 3 ve 4 sütunlarından verileri almasını istiyoruz - bu, Lookup_value olarak bir parça adı girdiğimizde fiyat, parça numarası ve tedarikçinin adıdır.

Veriler çalışma sayfasında düzenli bir düzende düzenlendiğinden, E2 hücresindeki arama formülünü F2 ve G2 hücrelerine kopyalayabiliriz.

Formül kopyalandığında, Excel, formülün yeni konumunu yansıtmak için COLUMN işlevindeki (B1) göreli hücre başvurusunu güncelleştirecektir.

Ayrıca, Excel mutlak hücre referansı $ D $ 2 değişmez ve formülün kopyalandığı adı verilen aralık Tablo kopyalanır.

Excel'de verileri kopyalamanın birden fazla yolu vardır, ancak muhtemelen en kolay yol, Doldurma İşareti kullanılarak gerçekleştirilir.

Öğretici Adımlar

1. E2 hücresine tıklayın - arama formülünün bulunduğu yer - etkin hücre yapmak için
2. Fare işaretçisini sağ alt köşedeki siyah karenin üzerine yerleştirin. İşaretçi bir artı işaretine dönüşecektir " + " - bu dolgu tutamacı
3. Sol fare düğmesini tıklayın ve doldurma tutamacını G2 hücresine sürükleyin
4. Fare düğmesini serbest bırakın ve F3 hücresi iki boyutlu arama formülü içermelidir
5. Doğru şekilde yapılırsa, artık F2 ve G2 hücreleri de E2 hücresinde bulunan # N / A hatasını içermelidir.

10/10

Arama Kriterleri Girme

Arama formülü gerekli hücrelere kopyalandıktan sonra, veri tablosundan bilgi almak için kullanılabilir.

Bunu yapmak için, Lookup_value hücresine (D2) almak istediğiniz öğenin adını yazın ve klavyede ENTER tuşuna basın.

Tamamlandığında, arama formülünü içeren her hücre, aradığınız donanım öğesiyle ilgili farklı bir veri parçası içermelidir.

Öğretici Adımlar

1. Çalışma sayfasındaki D2 hücresine tıklayın
2. Widget hücresine D2 yazın ve klavyede ENTER tuşuna basın
3. Aşağıdaki bilgiler E2 ila G2 hücrelerinde gösterilmelidir:
   • E2 - $ 14.76 - bir widget fiyatı
   • F2 - PN-98769 - bir widget için parça numarası
   • G2 - Widgets Inc. - widget'lar için tedarikçinin adı
4. Diğer parçaların adını D2 hücresine yazarak ve sonuçları E2 ile G2 hücrelerine gözlemleyerek VLOOKUP dizi formülü test edin.

#REF! Gibi bir hata mesajı E2, F2 veya G2 hücrelerinde görünür, VLOOKUP hata mesajlarının bu listesi sorunun nerede yattığını belirlemenize yardımcı olabilir.