Excel VLOOKUP ile Birden Fazla Veri Alanını Bul

Excel'in birleştirilmesiyleVLOOKUP işleviyleSÜTUN işlev, veritabanının veya veri tablosunun tek bir satırından birden çok değer döndürmenizi sağlayan bir arama formülü oluşturabiliriz.

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ü şunları gerektirir: SÜTUN işlev iç içe VLOOKUP. Bir fonksiyonun yerleştirilmesi, ikinci fonksiyonun ilk fonksiyon için argümanlardan biri olarak girilmesini içerir.

Bu eğitimde SÜTUN işlev olarak girilecektir sütun dizini numara için argüman VLOOKUP. Öğ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.

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ı girin. hücreler D1 için G1.
• İkinci aralığı giriniz. hücreler D4 için G10.

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

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

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.

Aralık adı, veriler için başlıkları veya alan adlarını içermez (satır 4) ancak sadece verilerin kendisi.

1. Vurgulamak hücreler D5 için G10 bunları seçmek için çalışma sayfasında.
2. Tıkla İsim kutusu yukarıda yer alan sütun A.
3. tip tablo içine İsim kutusu.
4. Basın Girmek klavyede tuşuna basın.
5. Hücreler D5 için G10 şimdi aralığın adı tablo. Adını kullanacağız VLOOKUP masa dizisi argüman sonra öğretici.

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 kullanmaktır VLOOKUP iletişim kutusu. 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. Tıklamak hücre E2 Çalışma sayfasının - iki boyutlu arama formülü sonuçlarının görüntüleneceği yer.
2. Tıkla Formüller sekmesi arasında kurdele.
3. Tıkla Arama ve Referans seçeneğinde kurdele işlevi açmak için açılır.
4. Tıklamak VLOOKUP Listede işlev iletişim kutusunu açmak için.

05/10

Arama Değeri Argümanı Girme

Normalde, yukarı Bak değer Veri tablosunun ilk sütunundaki bir veri alanıyla eşleşir. Örneğimizde yukarı Bak değer bilgi bulmak istediğimiz donanım bölümünün adını ifade eder. İçin izin verilen veri türleri yukarı Bak değer metin verileri, mantıksal değerler, sayılar ve hücre referanslarıdır.

Mutlak Hücre Referansları

Normalde, formüller Excel'de kopyalandığında, hücre referansları yeni konumlarını yansıtacak şekilde değişir. Eğer bu olursa, D2için hücre referansı yukarı Bak değer, formülün içinde hatalar oluştururken kopyalanır hücreler F2 ve G2.

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

Hataları önlemek için hücre referansını değiştireceğiz D2 mutlak bir hücre referansı içine. Mutlak hücre referansları basılarak oluşturulur F4 klavyede tuşuna basın. Bunu yapmak, hücre referansı gibi dolar işaretleri ekler. $ D $ 2.

1. Tıkla lookup_value iletişim kutusundaki satırı.
2. Tıklamak hücre D2 bu hücre referansı eklemek lookup_value satır - bu bilgi aradığımız bölüm adını yazacağımız hücre
3. Ekleme noktasını taşımadan, F4 dönüştürmek için klavyede D2 mutlak hücre referansına $ D $ 2.
4. Bırak VLOOKUP öğretici bir sonraki adım için işlev iletişim kutusu açı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ı (öğreticide önceki adım) içerirken, ikinci sütun 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.

1. Tıkla masa dizisi iletişim kutusundaki satırı.
2. tip tablo Bu argüman için aralık ismini girmek için
3. Bırak VLOOKUP öğretici bir sonraki adım için işlev iletişim kutusu açın.

07/10

COLUMN İşlevinin Yerleştirilmesi

Normalde VLOOKUP yalnızca bir veri tablosunun bir sütunundan veri döndürür ve bu sütun sütun dizin numarası argüman.

Ancak bu örnekte, verileri döndürmek istediğimiz üç sütunumuz var. sütun dizin numarası arama formumuzu düzenlemeden - burası SÜTUN işlevi geliyor.

Olarak girerek sütun dizin numarası argüman, arama formülü kopyalandığında değişecektir hücre D2 için E2 hücreleri ve F2 daha sonra eğitimde.

Yuvalama İşlevleri

SÜTUN işlev, bu nedenle, gibi davranır VLOOKUP'ler sütun dizin numarası argüman; Bu yuvalama tarafından gerçekleştirilir SÜTUN içinde işlev VLOOKUP içinde sütun_indis_sayısı iletişim kutusunun satırı.

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. SÜTUN Bu nedenle, işlev el ile girilmelidir sütun_indis_sayısı hat. SÜTUN fonksiyonun sadece bir argümanı vardır - Referans Bir hücre referansı olan argüman.

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

SÜTUN fonksiyonun görevi, verilen sütun sayısını Referans argüman. Başka bir deyişle, sütun harfini bir sayıya dönüştürür. sütun A ilk sütun olmak, sütun B ikinci ve daha fazlası.

İlk veri alanı olduğundan, geri dönmek istediğimiz şey, veri tablosunun iki sütununda bulunan öğenin fiyatıdır. Herhangi bir hücre için hücre referansı seçebiliriz sütun B olarak Referans Sayıyı almak için argüman 2 için sütun_indis_sayısı argüman.

1. İçinde VLOOKUP işlev iletişim kutusuna tıklayın sütun_indis_sayısı hat.
2. İşlev adını yazın kolon takip eden açık yuvarlak braket.
3. Tıklamak hücre B1 Bu hücre referansı girmek için çalışma sayfasında Referans argüman.
4. Yazın yuvarlak braketi kapatma tamamlamak için SÜTUN fonksiyon
5. Bırak VLOOKUP öğretici bir sonraki adım için işlev iletişim kutusu açın.

08/10

VLOOKUP Range Arama Argümanı Girme

VLOOKUP'ler Menzil araması argüman mantıksal bir değerdir (DOĞRU veya YANLIŞ) İstediğinizi belirten VLOOKUP ile tam veya yaklaşık bir eşleşme bulmak lookup_value.

• Eğer DOĞRU veya bu argüman atlanırsa: VLOOKUP ya tam bir eşleşme döndürür lookup_valueVeya tam bir eşleşme bulunamazsa, VLOOKUP bir sonraki en büyük değeri döndürür. Bunu yapabilmek için formülün ilk sütunundaki veriler Masa dizisi artan sırada sıralanmalıdır.
• Eğer YANLIŞ: VLOOKUP sadece tam bir eşleşme kullanır lookup_value. İlk sütununda iki veya daha fazla değer varsa Masa dizisi Arama değeriyle eşleşen, bulunan ilk değer kullanılır. Tam eşleşme bulunamazsa, # N / A hata döndü

Bu eğiticide, belirli bir donanım öğesi hakkında özel bilgi aradığımızdan, Menzil araması eşittir Yanlış.

1. Tıkla Menzil araması iletişim kutusundaki satırı.
2. Sözcüğü yazın Yanlış bu satırda istediğimizi belirtmek için VLOOKUP aradığımız veriler için tam bir eşleşme döndürmek için.
3. Click tamam arama formülü tamamlamak ve iletişim kutusunu kapatmak için.
4. Arama kriterlerini henüz girmediğimiz için hücre D2 bir # N / A hatası içinde mevcut olacak hücre E2.
5. Bu hata, arama kriterini öğreticinin son adımına ekleyeceğimiz zaman düzeltilecektir.

09/10

Arama Formülünü Kopyalama

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 eğiticide, verilerin sütunlar 2, 3, ve 4 veri tablosunun - yani kısmi bir isim olarak girdiğimizde fiyat, parça numarası ve tedarikçinin adıdır. lookup_value.

Veriler çalışma sayfasında düzenli bir düzende düzenlendiğinden, arama formunu hücre E2 için hücreler F2 ve G2.

Formül kopyalandığında, Excel ilgili hücresel referansı SÜTUN işlev (hücre B1) formülün yeni yerini yansıtmak.

Ayrıca, Excel mutlak hücre referansını değiştirmez $ D $ 2 ve adlandırılmış aralık tablo formül kopyalandığında.

Excel'de veri kopyalamak için birden fazla yol var, ama muhtemelen en kolay yolu kullanarak Doldurma Kolu.

1. Tıklamak hücre E2Arama formülünün bulunduğu yer, onu aktif hücrenin yapmak.
2. Yerleştirin fare imleci üzerinde siyah kare Sağ alt köşede - işaretçi, doldurma tutamacını belirtmek için artı işaretine dönüşür.
3. Farenin sol düğmesine tıklayın ve dolgu tutamacını sürükleyin karşısında hücre G2.
4. Serbest bırakmak fare düğmesi ve hücre F3 iki boyutlu arama formülü içermelidir.
5. Doğru yapıldığında, hücreler F2 ve G2 şimdi de içermelidir # N / A içinde bulunan hata hücre E2.

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 öğenin adı içine almak istiyorsan lookup_value hücre (D2) ve basın Girmek klavyede 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.

1. Tıklamak hücre D2 çalışma sayfasında.
2. tip Widget içine hücre D2 ve basın Girmek klavyede tuşuna basın.
3. Aşağıdaki bilgiler görüntülenmelidir E2 hücreleri için G2.
   1. E2: $ 14.76 - bir widget fiyatı
   2. F2: PN-98769 - bir widget için parça numarası
   3. G2: Widgets Inc. - widget'lar için tedarikçinin adı
4. Test edin VLOOKUP diğer formüllerin adını yazarak dizi formülü hücre D2 ve sonuçların gözlemlenmesi E2 hücreleri için G2