İçerik
- Excel'de DÜŞEYARA işlevi – genel açıklama ve sözdizimi
- DÜŞEYARA Örnekleri
- DÜŞEYARA işlevinde tam veya yaklaşık eşleşme
- Excel'de DÜŞEYARA – bunu hatırlamanız gerekir!
Bugün, Excel'in en kullanışlı özelliklerinden birini açıklayan bir dizi makaleye başlıyoruz - VPR (DÜŞEYARA). Bu işlev, aynı zamanda, en karmaşık ve en az anlaşılanlardan biridir.
Bu öğreticide VPR Öğrenme sürecini deneyimsiz kullanıcılar için olabildiğince anlaşılır kılmak için temel bilgileri olabildiğince basit bir şekilde ortaya koymaya çalışacağım. Ek olarak, işlev için en yaygın kullanım durumlarını gösterecek Excel formülleriyle birkaç örnek inceleyeceğiz. VPR.
Excel'de DÜŞEYARA işlevi – genel açıklama ve sözdizimi
peki nedir VPR? Her şeyden önce, bu bir Excel işlevidir. Yaptığı şey? Belirttiğiniz değeri arar ve diğer sütundan karşılık gelen değeri döndürür. Teknik olarak konuşursak, VPR verilen aralığın ilk sütunundaki değeri arar ve aynı satırdaki başka bir sütundan sonucu döndürür.
En yaygın uygulamada, işlev VPR veritabanında belirli bir benzersiz tanımlayıcı arar ve bununla ilgili bazı bilgileri veritabanından çıkarır.
İşlev adındaki ilk harf VPR (DÜŞEYARA) şu anlama gelir Вdikey (Vdikey). Bununla ayırt edebilirsiniz VPR itibaren GPR (HLOOKUP), bir aralığın en üst satırında bir değer arar - Гyatay (Hyatay olarak).
işlev VPR Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP ve Excel 2000'de mevcuttur.
DÜŞEYARA işlevinin sözdizimi
işlev VPR (DÜŞEYARA) aşağıdaki sözdizimine sahiptir:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
Gördüğünüz gibi bir fonksiyon VPR Microsoft Excel'de 4 seçenek (veya bağımsız değişken) vardır. İlk üçü zorunludur, sonuncusu isteğe bağlıdır.
- lookup_value (arama_değeri) – Aranacak değer. Bu, bir değer (sayı, tarih, metin) veya hücre başvurusu (arama değerini içeren) veya başka bir Excel işlevi tarafından döndürülen bir değer olabilir. Örneğin, bu formül değeri arayacaktır. 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
Aranan değer, aranan aralığın ilk sütunundaki en küçük değerden küçükse, işlev VPR bir hata bildirecek #NS (#Yok).
- masa dizisi (tablo) – iki veya daha fazla veri sütunu. Unutma, fonksiyon VPR her zaman bağımsız değişkende verilen aralığın ilk sütunundaki değeri arar masa dizisi (masa). Görüntülenebilir aralık, metin, tarihler, sayılar, boole değerleri gibi çeşitli verileri içerebilir. İşlev büyük/küçük harfe duyarsızdır, yani büyük ve küçük harf karakterleri aynı kabul edilir. Yani formülümüz değeri arayacak 40 hücrelerde A2 için A15, çünkü A, bağımsız değişkende verilen A2:B15 aralığının ilk sütunu masa dizisi (masa):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
- sütun_indis_sayısı (sütun_sayısı), verilen aralıktaki bulunan satırdaki değerin döndürüleceği sütunun numarasıdır. Verilen aralıktaki en soldaki sütun 1, ikinci sütun 2, üçüncü sütun 3 ve benzeri. Şimdi tüm formülü okuyabilirsiniz:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)Değer arayan formül 40 aralıkta A2: A15 ve B sütunundan karşılık gelen değeri döndürür (çünkü B, A2:B15 aralığındaki ikinci sütundur).
Argümanın değeri ise sütun_indis_sayısı (column_number) daha az 1sonra VPR bir hata bildirecek # DEĞER! (#DEĞER!). Ve aralıktaki sütun sayısından fazlaysa masa dizisi (tablo), işlev bir hata döndürür # BAŞV! (#BAĞLANTI!).
- Menzil araması (range_lookup) – neyin aranacağını belirler:
- tam eşleşme, bağımsız değişken eşit olmalıdır YANLIŞ (YANLIŞ);
- yaklaşık eşleşme, argüman eşittir DOĞRU KOD (DOĞRU) veya hiç belirtilmemiş.
Bu parametre isteğe bağlıdır, ancak çok önemlidir. Bu eğitimde daha sonra VPR Tam ve yaklaşık eşleşmeleri bulmak için formüllerin nasıl yazılacağını açıklayan bazı örnekler göstereceğim.
DÜŞEYARA Örnekleri
umarım işlevi VPR senin için biraz daha net ol. Şimdi bazı kullanım durumlarına bakalım VPR gerçek veriler içeren formüllerde.
Başka bir Excel sayfasında arama yapmak için DÜŞEYARA nasıl kullanılır?
Pratikte, işlevi olan formüller VPR nadiren aynı çalışma sayfasındaki verileri aramak için kullanılır. Çoğu zaman, başka bir sayfadan karşılık gelen değerleri arayacak ve alacaksınız.
kullanmak için VPR, başka bir Microsoft Excel sayfasında arama yapın, Argümanda olmalısınız masa dizisi (tablo) bir ünlem işareti ve ardından bir hücre aralığı ile sayfa adını belirtin. Örneğin, aşağıdaki formül, aralığın A2: B15 adlı bir sayfada Sheet2.
=VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)
Elbette, sayfa adının manuel olarak girilmesi gerekmez. Sadece formülü yazmaya başlayın ve argüman söz konusu olduğunda masa dizisi (tablo), istediğiniz sayfaya geçin ve fare ile istediğiniz hücre aralığını seçin.
Aşağıdaki ekran görüntüsünde gösterilen formül, bir çalışma sayfasında A sütununda (A1:B1 aralığının 2. sütunudur) "Ürün 9" metnini arar. Fiyatlar.
=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)
Lütfen bir metin değeri ararken, genellikle Excel formüllerinde yapıldığı gibi, onu tırnak işaretleri (“”) içine almanız gerektiğini unutmayın.
argüman için masa dizisi (tablo) her zaman mutlak referansların kullanılması arzu edilir ($ işaretiyle). Bu durumda, formül diğer hücrelere kopyalanırken arama aralığı değişmeden kalacaktır.
DÜŞEYARA ile başka bir çalışma kitabında arama yapın
çalışmak VPR iki Excel çalışma kitabı arasında çalıştıysanız, çalışma kitabı adını sayfa adından önce köşeli parantez içinde belirtmeniz gerekir.
Örneğin, aşağıda değeri arayan bir formül verilmiştir. 40 sayfada Sheet2 kitapta Sayılar.xlsx:
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)
Excel'de formül oluşturmanın en kolay yolu VPRbaşka bir çalışma kitabına bağlanan:
- Her iki kitabı da açın. Bu gerekli değildir, ancak bu şekilde bir formül oluşturmak daha kolaydır. Çalışma kitabı adını manuel olarak girmek istemezsiniz, değil mi? Ayrıca, sizi yanlışlıkla yapılan yazım hatalarından koruyacaktır.
- Bir işlev yazmaya başlayın VPRve tartışmaya gelince masa dizisi (tablo), başka bir çalışma kitabına geçin ve içindeki gerekli arama aralığını seçin.
Aşağıdaki ekran görüntüsü, çalışma kitabındaki bir aralığa ayarlanmış arama ile formülü gösterir. Fiyat Listesi.xlsx sayfada Fiyatlar.
işlev VPR aranan çalışma kitabını kapattığınızda ve çalışma kitabı dosyasının tam yolu aşağıda gösterildiği gibi formül çubuğunda göründüğünde bile çalışır:
Çalışma kitabının veya sayfanın adı boşluk içeriyorsa, kesme işareti içine alınmalıdır:
=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)
DÜŞEYARA ile formüllerde adlandırılmış bir aralık veya tablo nasıl kullanılır?
Aynı arama aralığını birden çok işlevde kullanmayı planlıyorsanız VPR, adlandırılmış bir aralık oluşturabilir ve adını argüman olarak formüle girebilirsiniz. masa dizisi (masa).
Adlandırılmış bir aralık oluşturmak için hücreleri seçmeniz ve alana uygun bir ad girmeniz yeterlidir. İsim, formül çubuğunun solunda.
Artık bir ürünün fiyatını bulmak için aşağıdaki formülü yazabilirsiniz. Ürün 1:
=VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)
Aralık adlarının çoğu tüm Excel çalışma kitabı için çalışır, bu nedenle bağımsız değişken için sayfa adını belirtmeye gerek yoktur. masa dizisi (tablo), formül ve arama aralığı farklı çalışma sayfalarında olsa bile. Farklı çalışma kitaplarındalarsa, aralığın adından önce çalışma kitabının adını belirtmeniz gerekir, örneğin şöyle:
=VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)
Yani formül çok daha net görünüyor, katılıyor musunuz? Ayrıca, formülü diğer hücrelere kopyaladığınızda adlandırılmış aralık değişmediğinden, adlandırılmış aralıkları kullanmak mutlak başvurulara iyi bir alternatiftir. Bu, formüldeki arama aralığının her zaman doğru kalacağından emin olabileceğiniz anlamına gelir.
Komutu kullanarak bir hücre aralığını tam teşekküllü bir Excel elektronik tablosuna dönüştürürseniz tablo (Tablo) sekmesi sokma (Ekle), ardından fareyle bir aralık seçtiğinizde, Microsoft Excel sütun adlarını (veya tüm tabloyu seçerseniz tablo adını) formüle otomatik olarak ekler.
Bitmiş formül şöyle görünecektir:
=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
=ВПР("Product 1";Table46[[Product]:[Price]];2)
Ya da belki böyle bile:
=VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)
Adlandırılmış aralıkları kullanırken, işlevi nereye kopyalarsanız kopyalayın, bağlantılar aynı hücrelere işaret eder. VPR çalışma kitabının içinde.
DÜŞEYARA Formüllerinde Joker Karakterleri Kullanma
Diğer birçok işlevde olduğu gibi, VPR Aşağıdaki joker karakterleri kullanabilirsiniz:
- Soru işareti (?) – herhangi bir tek karakterin yerini alır.
- Yıldız işareti (*) – herhangi bir karakter dizisini değiştirir.
İşlevlerde Joker Karakterleri Kullanma VPR birçok durumda faydalı olabilir, örneğin:
- Bulmanız gereken metni tam olarak hatırlamadığınızda.
- Bir hücrenin içeriğinin parçası olan bir kelime bulmak istediğinizde. Biliyorum VPR seçenek etkinmiş gibi hücrenin içeriğine göre bir bütün olarak arama yapar Tüm hücre içeriğini eşleştir (Tüm hücre) standart Excel aramasında.
- Bir hücre, içeriğin başında veya sonunda fazladan boşluk içerdiğinde. Böyle bir durumda, formülün neden çalışmadığını anlamaya çalışarak beyninizi uzun süre rafa kaldırabilirsiniz.
Örnek 1: Belirli karakterlerle başlayan veya biten metni aramak
Aşağıda gösterilen veritabanında belirli bir müşteriyi aramak istediğinizi varsayalım. Soyadını hatırlamıyorsun ama “ack” ile başladığını biliyorsun. İşte işi gayet iyi yapacak bir formül:
=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)
Artık doğru adı bulduğunuzdan emin olduğunuza göre, bu müşterinin ödediği tutarı bulmak için aynı formülü kullanabilirsiniz. Bunu yapmak için, işlevin üçüncü argümanını değiştirmeniz yeterlidir. VPR istenilen sütun numarasına Bizim durumumuzda bu, C sütunudur (aralıkta 3.):
=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)
İşte joker karakterlere sahip birkaç örnek daha:
~ "Adam" ile biten bir isim bulun:
=VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)
~ "Reklam" ile başlayan ve "oğul" ile biten bir isim bulun:
=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)
~ İlk adı 5 karakterden oluşan listede buluyoruz:
=VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)
çalışmak VPR joker karakterlerle her zaman kullanmanız gereken dördüncü argüman olarak doğru çalıştı YANLIŞ (YANLIŞ). Arama aralığı, arama terimleriyle joker karakterlerle eşleşen birden fazla değer içeriyorsa, bulunan ilk değer döndürülür.
Örnek 2: DÜŞEYARA formüllerinde joker karakterleri ve hücre referanslarını birleştirin
Şimdi işlevi kullanarak nasıl arama yapılacağına dair biraz daha karmaşık bir örneğe bakalım. VPR bir hücredeki değere göre. A sütununun lisans anahtarlarının bir listesi olduğunu ve B sütununun bir lisansa sahip olan adların listesi olduğunu hayal edin. Ek olarak, C1 hücresinde bir tür lisans anahtarının bir parçasına (birkaç karakter) sahipsiniz ve sahibinin adını bulmak istiyorsunuz.
Bu, aşağıdaki formül kullanılarak yapılabilir:
=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)
Bu formül, verilen aralıktaki C1 hücresindeki değeri arar ve B sütunundan karşılık gelen değeri döndürür. İlk bağımsız değişkende, metin dizesini bağlamak için hücre başvurusundan önce ve sonra bir ve işareti (&) karakteri kullandığımızı unutmayın.
Aşağıdaki resimde de görebileceğiniz gibi, fonksiyon VPR lisans anahtarı C1 hücresindeki karakter dizisini içerdiğinden "Jeremy Hill" değerini döndürür.
argüman olduğunu unutmayın masa dizisi (tablo) yukarıdaki ekran görüntüsünde bir hücre aralığı belirtmek yerine tablonun adını (Tablo7) içerir. Önceki örnekte yaptığımız şey buydu.
DÜŞEYARA işlevinde tam veya yaklaşık eşleşme
Ve son olarak, fonksiyon için belirtilen son argümana daha yakından bakalım. VPR - Menzil araması (aralık_görünüm). Dersin başında belirtildiği gibi, bu argüman çok önemlidir. Değeri ile aynı formülde tamamen farklı sonuçlar elde edebilirsiniz. DOĞRU KOD (DOĞRU) veya YANLIŞ (YANLIŞ).
İlk olarak, Microsoft Excel'in tam ve yaklaşık eşleşmelerle ne anlama geldiğini öğrenelim.
- eğer argüman Menzil araması (aralık_bak) eşittir YANLIŞ (YANLIŞ), formül tam bir eşleşme arar, yani bağımsız değişkende verilenle tam olarak aynı değer lookup_value (arama_değeri). t aralığının ilk sütununda isemümkün_dizi (tablo) argümanla eşleşen iki veya daha fazla değerle karşılaşır lookup_value (arama_değeri), ardından ilki seçilecektir. Hiçbir eşleşme bulunamazsa, işlev bir hata bildirir #NS (#Yok). Örneğin, aşağıdaki formül bir hata bildirir #NS (#N/A) A2:A15 aralığında değer yoksa 4:
=VLOOKUP(4,A2:B15,2,FALSE)=ВПР(4;A2:B15;2;ЛОЖЬ) - eğer argüman Menzil araması (aralık_bak) eşittir DOĞRU KOD (DOĞRU), formül yaklaşık bir eşleşme arar. Daha doğrusu, önce fonksiyon VPR tam bir eşleşme arar ve bulunamazsa yaklaşık bir eşleşme seçer. Yaklaşık eşleşme, bağımsız değişkende belirtilen değeri aşmayan en büyük değerdir. lookup_value (arama_değeri).
eğer argüman Menzil araması (aralık_bak) eşittir DOĞRU KOD (TRUE) veya belirtilmemişse, aralığın ilk sütunundaki değerler artan düzende, yani küçükten büyüğe sıralanmalıdır. Aksi takdirde, fonksiyon VPR hatalı bir sonuç döndürebilir.
Seçimin önemini daha iyi anlamak için DOĞRU KOD (GERÇEK) veya YANLIŞ (YANLIŞ), işlevle birlikte birkaç formüle daha bakalım VPR ve sonuçlara bakın.
Örnek 1: DÜŞEYARA ile Tam Bir Eşleşme Bulma
Hatırladığınız gibi, tam bir eşleşme aramak için, fonksiyonun dördüncü argümanı VPR önemli olmalı YANLIŞ (YANLIŞ).
İlk örnekten tabloya geri dönelim ve hangi hayvanın hızlı hareket edebildiğini bulalım. 50 saatte mil. Bu formülün size zorluk çıkarmayacağına inanıyorum:
=VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
Arama aralığımızın (sütun A) iki değer içerdiğini unutmayın 50 – hücrelerde A5 и A6. Formül hücreden değer döndürür B5. Neden? Niye? Çünkü tam bir eşleşme ararken, fonksiyon VPR aranan ile eşleşen ilk bulunan değeri kullanır.
Örnek 2: Yaklaşık Bir Eşleşme Bulmak için DÜŞEYARA'yı Kullanma
Fonksiyonu kullandığınızda VPR yaklaşık bir eşleşme aramak için, yani argüman ne zaman Menzil araması (aralık_bak) eşittir DOĞRU KOD (DOĞRU) veya atlanmışsa, yapmanız gereken ilk şey, aralığı artan düzende ilk sütuna göre sıralamaktır.
Bu çok önemlidir çünkü işlev VPR verilenden sonraki en büyük değeri döndürür ve ardından arama durur. Doğru sıralamayı ihmal ederseniz, çok garip sonuçlar veya bir hata mesajı alırsınız. #NS (#Yok).
Artık aşağıdaki formüllerden birini kullanabilirsiniz:
=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)
Gördüğünüz gibi, hayvanlardan hangisinin en yakın hıza sahip olduğunu bulmak istiyorum. 69 saatte mil. Ve işte fonksiyonun bana verdiği sonuç VPR:
Gördüğünüz gibi formül bir sonuç döndürdü. Antilop (antilop), kimin hızı 61 Listede ayrıca yer almasına rağmen, saatte mil Cheetah (Çita) hızla koşan 70 saatte mil ve 70, 69'a 61'den daha yakın, değil mi? Bu neden oluyor? çünkü işlev VPR yaklaşık bir eşleşme ararken, aranandan büyük olmayan en büyük değeri döndürür.
Umarım bu örnekler işlevle çalışmaya biraz ışık tutar VPR Excel'de ve artık ona bir yabancı olarak bakmıyorsunuz. Şimdi, incelediğimiz materyalin kilit noktalarını hafızada daha iyi düzeltmek için kısaca tekrarlamaktan zarar gelmez.
Excel'de DÜŞEYARA – bunu hatırlamanız gerekir!
- işlev VPR Excel sola bakamaz. Her zaman argüman tarafından verilen aralığın en soldaki sütunundaki değeri arar. masa dizisi (masa).
- İşlevde VPR tüm değerler büyük/küçük harfe duyarsızdır, yani küçük ve büyük harfler eşdeğerdir.
- Aradığınız değer, aranan aralığın ilk sütunundaki minimum değerden küçükse, fonksiyon VPR bir hata bildirecek #NS (#Yok).
- 3. argüman ise sütun_indis_sayısı (column_number) daha az 1işlev VPR bir hata bildirecek # DEĞER! (#DEĞER!). Aralıktaki sütun sayısından büyükse masa dizisi (tablo), işlev bir hata bildirir # BAŞV! (#BAĞLANTI!).
- Bağımsız değişkende mutlak hücre referanslarını kullan masa dizisi (tablo) böylece formül kopyalanırken doğru arama aralığı korunur. Alternatif olarak Excel'de adlandırılmış aralıkları veya tabloları kullanmayı deneyin.
- Yaklaşık eşleşme araması yaparken, aradığınız aralıktaki ilk sütunun artan düzende sıralanması gerektiğini unutmayın.
- Son olarak, dördüncü argümanın önemini hatırlayın. Değerleri kullan DOĞRU KOD (GERÇEK) veya YANLIŞ (YANLIŞ) kasten ve birçok baş ağrısından kurtulacaksınız.
İşlev eğitimimizin aşağıdaki makalelerinde VPR Excel'de, kullanarak çeşitli hesaplamalar yapmak gibi daha gelişmiş örnekleri öğreneceğiz. VPR, değerleri birden çok sütundan çıkarma ve daha fazlası. Bu öğreticiyi okuduğunuz için teşekkür ederiz ve gelecek hafta tekrar görüşmek dileğiyle!