Excel

DÜŞEYARA hakkında bilmeniz gereken 23 şey

23 Things You Should Know About Vlookup

Bir tablodan bilgi almak istediğinizde, Excel DÜŞEYARA işlevi harika bir çözümdür. Bir tablodan dinamik olarak bilgi arama ve bilgi alma özelliği birçok kullanıcı için oyunun kurallarını değiştirir ve her yerde DÜŞEYARA'yı bulursunuz.





Yine de, DÜŞEYARA kullanımı nispeten kolay olsa da, yanlış gidebilecek çok şey var. Bunun bir nedeni, DÜŞEYARA'nın büyük bir tasarım hatasına sahip olmasıdır - varsayılan olarak, yaklaşık bir eşleşmede sorun olmadığını varsayar. Ki muhtemelen değilsin.

Bu sonuçlara neden olabilir tamamen normal görün olmalarına rağmen tamamen yanlış . İnanın bana, bu, hesap tablonuzu yönetime gönderdikten sonra patronunuza açıklamaya çalışmak isteyeceğiniz bir şey DEĞİLDİR :)





Bu zorluğun nasıl yönetileceğini öğrenmek için aşağıyı okuyun ve bu zorluğun üstesinden gelmek için diğer ipuçlarını keşfedin. Excel DÜŞEYARA işlevi .

1. DÜŞEYARA nasıl çalışır?

DÜŞEYARA, bir tablodaki verileri aramak ve almak için kullanılan bir işlevdir. DÜŞEYARA'daki 'V' dikey anlamına gelir; bu, tablodaki verilerin satırlar halinde dikey olarak düzenlenmesi gerektiği anlamına gelir. (Yatay olarak yapılandırılmış veriler için bkz. HLOOKUP ).



Bilgileri dikey olarak düzenlenmiş, iyi yapılandırılmış bir tablonuz ve solda bir satırı eşleştirmek için kullanabileceğiniz bir sütununuz varsa, muhtemelen DÜŞEYARA'yı kullanabilirsiniz.

DÜŞEYARA, tablonun en soldaki sütunda arama değerleri görünecek şekilde yapılandırılmasını gerektirir. Almak istediğiniz veriler (sonuç değerleri) sağdaki herhangi bir sütunda görünebilir. DÜŞEYARA kullandığınızda, tablodaki her sütunun soldan başlayarak numaralandırıldığını hayal edin. Belirli bir sütundan bir değer elde etmek için, 'sütun dizini' olarak uygun sayıyı sağlamanız yeterlidir. Aşağıdaki örnekte, e-posta adresini aramak istiyoruz, bu nedenle sütun dizini için 4 sayısını kullanıyoruz:

DÜŞEYARA'nın nasıl çalıştığına genel bakış

Yukarıdaki tabloda, çalışan kimlikleri soldaki 1. sütunda ve e-posta adresleri sağdaki 4. sütundadır.

DÜŞEYARA'yı kullanmak için 4 parça bilgi veya 'argüman' sağlarsınız:

  1. Aradığınız değer ( aranan_değer )
  2. Tabloyu oluşturan hücre aralığı ( masa dizisi )
  3. Bir sonucun alınacağı sütunun numarası ( sütun_dizini )
  4. maç modu ( Menzil araması , DOĞRU = yaklaşık, YANLIŞ = kesin)

Video: DÜŞEYARA nasıl kullanılır (3 dakika)

DÜŞEYARA'nın temel fikrini hâlâ anlamadıysanız, Excel Kampüsü'ndeki Jon Acampora'nın harika açıklama Starbucks kahve menüsüne göre.

2. DÜŞEYARA yalnızca doğru görünüyor

DÜŞEYARA'nın belki de en büyük sınırlaması, verileri almak için yalnızca sağa bakabilmesidir.

Bu, DÜŞEYARA'nın yalnızca tablodaki ilk sütunun sağındaki sütunlardan veri alabileceği anlamına gelir. İlk (en soldaki) sütunda arama değerleri göründüğünde, diğer tüm sütunlar zaten sağda olduğundan bu sınırlama pek bir anlam ifade etmez. Ancak, arama sütunu tablonun içinde bir yerde görünüyorsa, yalnızca o sütunun sağındaki sütunlardan değerleri arayabilirsiniz. Ayrıca DÜŞEYARA'ya arama sütunuyla başlayan daha küçük bir tablo sağlamanız gerekir.

DÜŞEYARA yalnızca arama değeri sütununun sağına bakabilir

DÜŞEYARA yerine INDEX ve MATCH kullanarak bu sınırlamayı aşabilirsiniz.

3. DÜŞEYARA ilk eşleşmeyi bulur

Tam eşleme modunda, bir arama sütunu yinelenen değerler içeriyorsa, DÜŞEYARA yalnızca ilk değerle eşleşir. Aşağıdaki örnekte, bir ad bulmak için DÜŞEYARA kullanıyoruz ve DÜŞEYARA tam eşleşme yapacak şekilde ayarlandı. Listede iki 'Janet' olmasına rağmen, DÜŞEYARA yalnızca ilkiyle eşleşir:

DÜŞEYARA her zaman ilk eşleşmeyi bulur

Not: DÜŞEYARA yaklaşık eşleşme modunda kullanıldığında davranış değişebilir. Bu makale konuyu ayrıntılı olarak açıklamaktadır.

4. DÜŞEYARA büyük/küçük harf duyarlı değildir

Bir değer ararken DÜŞEYARA, büyük ve küçük harfli metni farklı şekilde işlemez. DÜŞEYARA için 'PQRF' gibi bir ürün kodu 'pqrf' ile aynıdır. Aşağıdaki örnekte, büyük harf 'JANET' arıyoruz, ancak DÜŞEYARA büyük/küçük harf ayrımı yapmadığından, bulduğu ilk eşleşme olduğu için 'Janet' ile eşleşiyor:

DÜŞEYARA büyük/küçük harf duyarlı DEĞİLDİR

Biz de sunuyoruz ücretli eğitim DÜŞEYARA ve INDEX/MATCH için

5. DÜŞEYARA iki eşleşen moda sahiptir

DÜŞEYARA'nın iki çalışma modu vardır: tam eşleşme ve yaklaşık eşleşme. Çoğu durumda DÜŞEYARA'yı tam eşleme modunda kullanmak isteyeceksiniz. Bu, örneğin bir ürün koduna dayalı ürün bilgileri veya bir film başlığına dayalı film verileri gibi benzersiz bir anahtara dayalı bilgileri aramak istediğinizde anlamlıdır:

DÜŞEYARA tam eşleşme örneği - eşleşen filmler

Film başlığının tam eşleşmesine dayalı olarak H6'daki arama yılı formülü şudur:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

Ancak, benzersiz bir kimlik üzerinde eşleşmediğiniz, bunun yerine 'en iyi eşleşme' veya 'en iyi kategori' aradığınız durumlarda yaklaşık modu kullanmak isteyeceksiniz. Örneğin, kiloya göre posta ücreti, gelire göre vergi oranı veya aylık satış numarasına göre komisyon oranı arıyor olabilirsiniz. Bu durumlarda, büyük olasılıkla tam arama değerini tabloda bulamazsınız. Bunun yerine, DÜŞEYARA'nın belirli bir arama değeri için size en iyi eşleşmeyi bulmasını istersiniz.

DÜŞEYARA yaklaşık eşleşme örneği - komisyonlar

D5'teki formül, doğru komisyonu almak için yaklaşık bir eşleşme yapar:

 
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match

6. Dikkat: DÜŞEYARA varsayılan olarak yaklaşık eşleşmeyi kullanır

DÜŞEYARA'daki tam ve yaklaşık eşleşme, 'aralık arama' adı verilen 4. bağımsız değişken tarafından kontrol edilir. Bu isim sezgisel değil, bu yüzden nasıl çalıştığını ezberlemeniz yeterli.

Tam eşleşme için YANLIŞ veya 0 kullanın. Yaklaşık eşleşme için aralık_bak öğesini DOĞRU veya 1 olarak ayarlayın:

 
= VLOOKUP (value,table,column,TRUE) // approximate match = VLOOKUP (value,table,column,FALSE) // exact match

Ne yazık ki, 4. bağımsız değişken olan aralık_bak isteğe bağlıdır ve varsayılan olarak DOĞRU'dur; bu, DÜŞEYARA'nın varsayılan olarak yaklaşık bir eşleşme yapacağı anlamına gelir. Yaklaşık bir eşleşme yaparken DÜŞEYARA, tablonun sıralandığını varsayar ve ikili arama yapar. İkili arama sırasında DÜŞEYARA tam bir eşleşme değeri bulursa, o satırdan bir değer döndürür. Ancak DÜŞEYARA, arama değerinden daha büyük bir değerle karşılaşırsa, önceki satırdan bir değer döndürür.

Bu tehlikeli bir varsayılandır çünkü birçok kişi farkında olmadan DÜŞEYARA'yı varsayılan modunda bırakır; yanlış sonuç tablo sıralanmadığında.

Bu sorunu önlemek için, tam bir eşleşme istediğinizde 4. bağımsız değişken olarak FALSE veya sıfırı kullandığınızdan emin olun.

7. DÜŞEYARA'yı tam bir eşleşme yapmaya zorlayabilirsiniz

DÜŞEYARA'yı tam bir eşleşme bulmaya zorlamak için 4 bağımsız değişkenini (aralık_bak) YANLIŞ veya sıfır olarak ayarladığınızdan emin olun. Bu iki formül eşdeğerdir:

 
= VLOOKUP (value, data, column, FALSE) = VLOOKUP (value, data, column, 0)

Tam eşleme modunda, DÜŞEYARA bir değer bulamadığında #N/A değerini döndürür. Bu, değerin tabloda bulunmadığının açık bir göstergesidir.

8. DÜŞEYARA yaklaşık bir eşleşme yapmasını söyleyebilirsiniz

DÜŞEYARA'yı yaklaşık eşleşme modunda kullanmak için ya 4. bağımsız değişkeni (aralık_bak) atlayın ya da DOĞRU veya 1 olarak sağlayın. Bu 3 formül eşdeğerdir:

 
= VLOOKUP (value, data, column) = VLOOKUP (value, data, column, 1) = VLOOKUP (value, data, column, TRUE)

DÜŞEYARA gerektirmese de, aralık_bak bağımsız değişkenini her zaman açık bir şekilde ayarlamanızı öneririz. Bu şekilde, her zaman beklediğiniz maç modunun görsel bir hatırlatıcısına sahip olursunuz.

Video: Yaklaşık eşleşmeler için DÜŞEYARA nasıl kullanılır?

9. Yaklaşık eşleşmeler için veriler sıralanmalıdır.

Yaklaşık mod eşleştirme kullanıyorsanız, verileriniz sıralanmalıdır arama değerine göre artan sırada. Aksi takdirde, bir yanlış sonuçlar . Ayrıca bazen metin verilerinin bakmak öyle olmasa da sıralanır.

felienne herman bu sorunun harika bir örneği var burada , o gerçek üzerinde yaptığı harika bir analizden Enron elektronik tablolar!

10. DÜŞEYARA farklı tablolardaki verileri birleştirebilir

DÜŞEYARA için yaygın bir kullanım durumu, iki veya daha fazla tablodaki verileri birleştirmektir. Örneğin, bir tabloda sipariş verileriniz ve diğerinde müşteri verileriniz var ve analiz için bazı müşteri verilerini sipariş tablosuna getirmek istiyorsunuz:

DÜŞEYARA tabloları birleştirerek verileri birleştirme -önce

Müşteri kimliği her iki tabloda da bulunduğundan, DÜŞEYARA ile istediğiniz verileri çekmek için bu değeri kullanabilirsiniz. DÜŞEYARA'yı tablo 1'deki id değerini ve tablo 2'deki verileri gerekli sütun dizini ile kullanacak şekilde yapılandırmanız yeterlidir. Aşağıdaki örnekte iki DÜŞEYARA formülü kullanıyoruz. Biri müşteri adını, diğeri müşteri durumunu çekmek için.

DÜŞEYARA tabloları birleştirerek verileri birleştirir -sonra

Bağlantı: DÜŞEYARA ile birleştirme örneği .

Video: Tabloları birleştirmek için DÜŞEYARA nasıl kullanılır? .

11. DÜŞEYARA verileri sınıflandırabilir veya kategorilere ayırabilir

Veri kayıtlarına rastgele kategoriler uygulamanız gerekirse, kategorileri atamak için 'anahtar' görevi gören bir tablo kullanarak DÜŞEYARA ile bunu kolayca yapabilirsiniz.

Klasik bir örnek, bir puana göre bir not vermeniz gereken notlardır:

DÜŞEYARA kategorize etmek için kullanılır - notları atamak

Bu durumda DÜŞEYARA yaklaşık eşleşme için yapılandırılır, bu nedenle tablonun artan düzende sıralanması önemlidir.

Ancak, isteğe bağlı kategoriler atamak için DÜŞEYARA'yı da kullanabilirsiniz. Aşağıdaki örnekte, gruplamayı tanımlayan küçük bir tablo ('anahtar' adlı) kullanarak her departman için bir grup hesaplamak için DÜŞEYARA kullanıyoruz.

DÜŞEYARA kategorize etmek için kullanılır - isteğe bağlı gruplar atama

12. Mutlak referanslar DÜŞEYARA'yı daha taşınabilir hale getirir

Bir tablodaki birden fazla sütundan bilgi almayı planladığınız durumlarda veya DÜŞEYARA kopyalayıp yapıştırmanız gerekiyorsa, arama değeri ve tablo dizisi için mutlak referanslar kullanarak zamandan ve ağırlaştırmadan tasarruf edebilirsiniz. Bu, formülü kopyalamanıza ve ardından farklı bir sütundan değer almak için aynı aramayı kullanmak için yalnızca sütun dizin numarasını değiştirmenize olanak tanır.

Örneğin, arama değeri ve tablo dizisi mutlak olduğundan, formülü sütunlar arasında kopyalayabilir, ardından geri gelip sütun dizinini gerektiği gibi değiştirebiliriz.

Mutlak referanslar DÜŞEYARA formüllerini daha taşınabilir hale getirir

13. Adlandırılmış aralıklar DÜŞEYARA'yı okumayı kolaylaştırır (ve daha taşınabilir)

Mutlak aralıklar oldukça çirkin görünür, bu nedenle mutlak referansları otomatik olarak mutlak olan adlandırılmış aralıklarla değiştirerek DÜŞEYARA formüllerinizi çok daha temiz ve okunması daha kolay hale getirebilir.

Örneğin, yukarıdaki çalışan verileri örneğinde, giriş hücresine 'id' adını verebilir ve ardından tablodaki verileri 'veri' olarak adlandırabilir, formülünüzü aşağıdaki gibi yazabilirsiniz:

Adlandırılmış aralıklar DÜŞEYARA formüllerinin okunmasını kolaylaştırır

Bu formülün okunması daha kolay olmakla kalmaz, aynı zamanda adlandırılmış aralıklar otomatik olarak mutlak olduğundan daha taşınabilirdir.

14. Bir sütun eklemek, mevcut DÜŞEYARA formüllerini bozabilir

Bir çalışma sayfasında DÜŞEYARA formülleriniz varsa, tabloya bir sütun eklerseniz formüller bozulabilir. Bunun nedeni, sabit kodlanmış sütun dizin değerlerinin, sütunlar eklendiğinde veya silindiğinde otomatik olarak değişmemesidir.

Bu örnekte, Yıl ve Sıra arasına yeni bir sütun eklendiğinde Sıra ve Satış aramaları bozuldu. Yıl, eklenen sütunun solunda olduğu için çalışmaya devam eder:

Tabloya bir sütun eklemek DÜŞEYARA'yı bozabilir

Bu sorunu önlemek için sonraki iki ipucunda açıklandığı gibi bir sütun dizini hesaplayabilirsiniz.

15. Bir sütun indeksi hesaplamak için ROW veya COLUMN kullanabilirsiniz.

Bir formülü kopyaladıktan sonra herhangi bir miktarda düzenleme yapmaktan rahatsızsanız, dinamik sütun dizinleri oluşturmak için SATIR veya SÜTUN kullanabilirsiniz. Ardışık sütunlardan veri alıyorsanız, bu numara bir DÜŞEYARA formülü oluşturmanıza ve ardından herhangi bir değişiklik gerekmeden bu formülü kopyalamanıza olanak tanır.

Örneğin, aşağıdaki çalışan verileriyle, dinamik bir sütun dizini oluşturmak için COLUMN işlevini kullanabiliriz. C3 hücresindeki ilk formül için, COLUMN tek başına 3 döndürür (çünkü C sütunu çalışma sayfasında üçüncüdür), bu nedenle yalnızca bir tane çıkarmamız ve formülü aşağıdakilere kopyalamamız gerekir:

DÜŞEYARA sütun dizinini hesaplamak için COLUMN kullanımına örnek

Tüm formüller, sonradan düzenleme gerektirmeden aynıdır.

Kullandığımız formül şudur:

 
= VLOOKUP (id,data, COLUMN ()-1,0)

16. Tamamen dinamik bir sütun dizini için DÜŞEYARA + KAÇINCI kullanın

Yukarıdaki ipucunu bir adım daha ileri götürerek, tablodaki bir sütunun konumunu aramak ve tamamen dinamik bir sütun dizini döndürmek için KAÇINCI'yı kullanabilirsiniz.

Hem satıra hem de sütuna baktığınız için buna bazen iki yönlü arama denir.

Örneğin, belirli bir ayda bir satış elemanının satışlarını aramak veya belirli bir tedarikçiden belirli bir ürünün fiyatını aramak olabilir.

Örneğin, satış görevlisine göre aylık satışlarınız olduğunu varsayalım:

DÜŞEYARA iki yönlü arama - ay nasıl aranır?

DÜŞEYARA, satış elemanını kolayca bulabilir, ancak ay adını otomatik olarak işlemenin bir yolu yoktur. İşin püf noktası, statik bir sütun dizini yerine KAÇINCI işlevini kullanmaktır.

Sütun dizinini almak için MATCH kullanarak DÜŞEYARA iki yönlü arama

DÜŞEYARA tarafından kullanılan sütun numaralarını 'senkronize etmek' için eşleşmeye tablodaki tüm sütunları içeren bir aralık verdiğimize dikkat edin.

 
= VLOOKUP (H2,data, MATCH (H3,months,0),0)

Not: Büyük veri kümelerinde daha fazla esneklik ve daha iyi performans sunan bir yaklaşım olan INDEX ve MATCH ile yapılan iki yönlü aramaları sık sık görürsünüz. Bu hızlı videoda nasıl olduğunu görün: INDEX ve MATCH ile iki yönlü arama nasıl yapılır .

17. DÜŞEYARA, kısmi eşleştirme için joker karakterlere izin verir

DÜŞEYARA'yı tam eşleme modunda her kullandığınızda, arama değerinde joker karakterler kullanma seçeneğiniz vardır. Mantıksız görünebilir, ancak joker karakterler, kısmi eşleşmeye dayalı tam bir eşleşme yapmanıza izin verir :)

Excel iki joker karakter sağlar: yıldız işareti (*) bir veya daha fazla karakterle ve soru işareti (?) bir karakterle eşleşir.

Örneğin, bir hücreye doğrudan bir yıldız işareti yazabilir ve buna DÜŞEYARA ile bir arama değeri olarak başvurabilirsiniz. Aşağıdaki ekranda, 'val' adında bir adlandırılmış aralık olan H3'e 'Pzt*' girdik. Bu, DÜŞEYARA'nın 'Monet' adıyla eşleşmesine neden olur.

Joker karakterlerle DÜŞEYARA - doğrudan yıldız işareti kullanarak

Bu durumda formül basittir:

 
= VLOOKUP (val,data,1,0)

İsterseniz, DÜŞEYARA formülünü, aşağıdaki örnekte olduğu gibi yerleşik bir joker karakter kullanacak şekilde ayarlayabilirsiniz; burada H3'teki değeri bir yıldız işaretiyle birleştiririz.

Joker karakterlerle DÜŞEYARA - yıldız işareti, arama değeriyle birleştirilir

Bu durumda, yıldız işaretini DÜŞEYARA işlevi içindeki arama değeriyle birleştiriyoruz:

 
= VLOOKUP (val&'*',data,1,0)

Not: Joker karakterlere ve DÜŞEYARA dikkatli olun. Size 'tembel eşleşme' oluşturmanın kolay bir yolunu sunarlar, ancak aynı zamanda yanlış eşleşmeyi bulmayı da kolaylaştırırlar.

18. #N/A hatalarını yakalayabilir ve dostça bir mesaj görüntüleyebilirsiniz.

Tam eşleşme modunda DÜŞEYARA, eşleşme bulunmadığında #YOK hatasını görüntüler. Bir bakıma bu yararlıdır çünkü size arama tablosunda kesinlikle eşleşme olmadığını söyler. Ancak, #YOK hatalarına bakmak pek eğlenceli değildir, bu nedenle bu hatayı yakalamanın ve bunun yerine başka bir şey göstermenin birkaç yolu vardır.

DÜŞEYARA'yı kullanmaya başladığınızda, DÜŞEYARA bir eşleşme bulamadığında oluşan #YOK hatasıyla karşılaşmanız gerekir.

pivot tablodaki benzersiz değerleri say

Bu yararlı bir hatadır, çünkü DÜŞEYARA size açıkça arama değerini bulamadığını söylüyor. Bu örnekte, 'Latte' tabloda içecek olarak mevcut değil, bu nedenle DÜŞEYARA #YOK hatası veriyor

DÜŞEYARA #N/A hatası veriyor

Bu durumda formül tamamen standart bir tam eşleşmedir:

 
= VLOOKUP (E6,data,2,0)

Ancak #N/A hatalarına bakmak pek eğlenceli değildir, bu nedenle bu hatayı yakalamak ve daha samimi bir mesaj göstermek isteyebilirsiniz.

DÜŞEYARA ile hataları yakalamanın en kolay yolu DÜŞEYARA işlevini EĞERHATA işlevine kaydırmaktır. EĞERHATA, herhangi bir hatayı 'yakalamanıza' ve seçtiğiniz bir sonucu döndürmenize olanak tanır.

Bu hatayı yakalamak ve hata yerine 'bulunamadı' mesajını görüntülemek için, orijinal formülü EĞERHATA'nın içine sarmanız ve istediğiniz sonucu ayarlamanız yeterlidir:

DÜŞEYARA #YOK hatası EĞERHATA ile yakalandı

Aranan değer bulunursa hata oluşmaz ve DÜŞEYARA işlevi normal bir sonuç döndürür. İşte formül:

 
= IFERROR ( VLOOKUP (E6,data,2,0),'Not found')

19. Metin olarak sayılar eşleşme hatasına neden olabilir

Bazen DÜŞEYARA'da üzerinde çalıştığınız tablo metin olarak girilen sayıları içerebilir. Bir tablodaki bir sütundan sayıları metin olarak alıyorsanız, bunun bir önemi yoktur. Ancak tablonun ilk sütunu metin olarak girilen sayıları içeriyorsa, arama değeri de metin değilse #YOK hatası alırsınız.

Aşağıdaki örnekte, gezegen tablosunun kimlikleri sayılardır. metin olarak girildi , bu da DÜŞEYARA'nın bir hata döndürmesine neden olur, çünkü arama değeri sayı 3:

Metin olarak girilen sayılar DÜŞEYARA hata örneği

Bu sorunu çözmek için, arama değerinin ve tablonun ilk sütununun her ikisinin de aynı veri türünde (her iki sayı veya her iki metin) olduğundan emin olmanız gerekir.

Bunu yapmanın bir yolu, arama sütunundaki değerleri sayılara dönüştürmektir. Bunu yapmanın kolay bir yolu, özel yapıştır kullanarak sıfır eklemektir.

Kaynak tablo üzerinde kolay kontrolünüz yoksa, arama değerini metne dönüştürmek için DÜŞEYARA formülünü de ayarlayabilirsiniz.

 
= VLOOKUP (id&'',planets,2,0)

Metin olarak girilen sayılar DÜŞEYARA hata çözümü

Ne zaman sayılara ve ne zaman metinlere sahip olacağınızdan emin değilseniz, DÜŞEYARA'yı EĞERHATA içine kaydırarak ve her iki durumu da ele almak için bir formül yazarak her iki seçeneği de karşılayabilirsiniz:

 
= IFERROR ( VLOOKUP (id,planets,3,0), VLOOKUP (id&'',planets,3,0))

20. İç içe EĞER deyimlerini değiştirmek için DÜŞEYARA'yı kullanabilirsiniz.

DÜŞEYARA'nın daha ilginç kullanımlarından biri, iç içe EĞER deyimlerini değiştirmektir. Daha önce bir dizi iç içe IF oluşturduysanız, bunların iyi çalıştığını bilirsiniz, ancak biraz parantez tartışması gerektirir. Mantık hatası yapmamak için çalıştığınız sıraya da dikkat etmelisiniz.

Örneğin, iç içe IF'lerin yaygın bir kullanımı, bir tür puana dayalı notlar vermektir. Aşağıdaki örnekte, kılavuz olarak sağdaki not anahtarı kullanılarak tam da bunu yapmak için iç içe IF'lerle bir formülün oluşturulduğunu görebilirsiniz.

Uzun iç içe EĞER formülüyle notları atama

Tam iç içe EĞER formülü şöyle görünür:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

Bu iyi çalışıyor, ancak hem mantığın hem de gerçek puanların doğrudan formüle yerleştirildiğini unutmayın. Puanlama herhangi bir nedenle değişirse, bir formülü dikkatlice güncellemeniz ve ardından tüm tabloya kopyalamanız gerekir.

Buna karşılık DÜŞEYARA aynı notları basit bir formülle atayabilir. Tek yapmanız gereken, not anahtarı tablosunun DÜŞEYARA için ayarlandığından emin olmaktır (yani, çoğu puana göre sıralanır ve tüm puanları işlemek için parantez içerir).

Not anahtarı tablosu için adlandırılmış bir aralık 'anahtar' tanımladıktan sonra, DÜŞEYARA formülü çok basittir ve orijinal iç içe EĞER formülüyle aynı notları oluşturur:

Basit bir DÜŞEYARA formülüyle not atama

'Anahtar' adlı not anahtarı tablosu ile çok basit bir DÜŞEYARA formülümüz var:

 
= VLOOKUP (C5,key,2,TRUE)

Bu yaklaşımın güzel bir avantajı, hem mantığın hem de puanların doğrudan not anahtarı tablosuna yerleştirilmiş olmasıdır. Herhangi bir değişiklik olursa, tabloyu doğrudan güncelleyebilirsiniz ve DÜŞEYARA formülleri otomatik olarak güncellenir - düzenleme gerekmez.

Video: DÜŞEYARA ile iç içe IF'ler nasıl değiştirilir

21. DÜŞEYARA yalnızca tek bir kriteri işleyebilir

Tasarım gereği, DÜŞEYARA yalnızca tablonun ilk sütununda (arama sütunu) bulunacak bir arama değeri olarak sağlanan tek bir ölçüte dayalı değerleri bulabilir.

Bu, 'Muhasebe' bölümünde 'Smith' soyadına sahip bir çalışanı veya ayrı sütunlarda ad ve soyadına göre bir çalışan aramak gibi şeyleri kolayca yapamayacağınız anlamına gelir.

Ancak, bu sınırlamayı aşmanın yolları vardır. Çözümlerden biri, birden çok koşul gibi davranan arama değerleri oluşturmak için farklı sütunlardaki değerleri birleştiren bir yardımcı sütun oluşturmaktır. Örneğin, burada bir çalışanın departmanını ve grubunu bulmak istiyoruz, ancak adı ve soyadı ayrı sütunlarda görünüyor. İkisini aynı anda nasıl arayabiliriz?

DÜŞEYARA çoklu ölçüt sorunu - hem ad hem de soyadı nasıl aranır?

İlk olarak, adları ve soyadlarını basitçe birleştiren bir yardımcı sütun ekleyin:

DÜŞEYARA çoklu ölçüt 2. adım - birden çok ölçütü birleştiren bir yardımcı sütun ekleyin

Ardından DÜŞEYARA'yı bu yeni sütunu içeren bir tablo kullanacak şekilde yapılandırın ve arama değeri için ad ve soyadlarını birleştirin:

DÜŞEYARA çoklu ölçüt 3. adım - arama değeri oluşturmak için ölçütleri birleştirin

Son DÜŞEYARA formülü, yardımcı sütunu anahtar olarak kullanarak ad ve soyadlarını birlikte arar:

 
= VLOOKUP (C3&D3,data,4,0)

22. İki DÜŞEYARA, bir DÜŞEYARA'dan daha hızlıdır

Tamamen çılgınca görünebilir, ancak büyük bir veri setiniz olduğunda ve tam bir eşleşme yapmanız gerektiğinde, formüle başka bir DÜŞEYARA ekleyerek DÜŞEYARA'yı çok hızlandırabilirsiniz!

Arka plan: çok fazla sipariş veriniz olduğunu, örneğin 10.000'den fazla kaydınız olduğunu ve sipariş kimliğine göre sipariş toplamını aramak için DÜŞEYARA kullandığınızı hayal edin. Yani, böyle bir şey kullanıyorsunuz:

 
= VLOOKUP (order_id,order_data, 5, FALSE)

Sondaki YANLIŞ, DÜŞEYARA'yı tam bir eşleşme yapmaya zorlar. Tam bir eşleşme istiyorsunuz çünkü bir sipariş numarasının bulunmama ihtimali var. Bu durumda, tam eşleşme ayarı DÜŞEYARA'nın #YOK hatası vermesine neden olur.

Sorun şu ki, tam eşleşmeler gerçekten yavaştır, çünkü Excel bir eşleşme bulana kadar tüm değerler arasında doğrusal bir şekilde ilerlemelidir.

Tersine, yaklaşık eşleşmeler yıldırım hızındadır çünkü Excel, Ikili arama .

Ancak ikili aramalarla ilgili sorun (yani, yaklaşık eşleşme modunda DÜŞEYARA), bir değer bulunmadığında DÜŞEYARA'nın yanlış sonuç döndürebilmesidir. Daha da kötüsü, sonuç tamamen normal görünebilir, bu nedenle fark edilmesi çok zor olabilir.

Çözüm, DÜŞEYARA'yı yaklaşık eşleşme modunda iki kez, iki kez kullanmaktır. İlk örnek, değerin gerçekten var olup olmadığını kontrol eder. Öyleyse, istediğiniz verileri getirmek için başka bir DÜŞEYARA çalıştırılır (yine yaklaşık eşleşme modunda). Değilse, sonuç bulunamadığını belirtmek istediğiniz herhangi bir değeri döndürebilirsiniz.

Son formül şöyle görünür:

 
= IF ( VLOOKUP (order_id,order_data,1,TRUE)=order_id,  VLOOKUP (order_id,order_data,5,TRUE), 'Missing')

Bu yaklaşımı, burada harika, ayrıntılı bir makalesi olan FastExcel'den Charles Williams'tan öğrendim: 2 DÜŞEYARA neden 1 DÜŞEYARA'dan daha iyidir .

Not: Bu numarayı kullanmak için verilerinizin sıralanması gerekir. Hızlı bir aramayı sürdürürken, kayıp bir arama değerine karşı koruma sağlamanın bir yoludur.

23. INDEX ve MATCH birlikte DÜŞEYARA'nın yapabileceği her şeyi ve daha fazlasını yapabilir

Excel'i çevrimiçi takip ediyorsanız, muhtemelen DÜŞEYARA vs. INDEX/MATCH tartışması. Tartışma şaşırtıcı derecede ısınabilir :)

İşin özü şudur: INDEX + MATCH, DÜŞEYARA'nın (ve YATAYARA'nın) yapabileceği her şeyi, biraz daha fazla karmaşıklık pahasına çok daha fazla esneklikle yapabilir. Bu nedenle, INDEX + MAÇ'tan yana olanlar, sonunda size daha iyi bir araç seti sağladığından, INDEX ve MAÇ'ı öğrenmeye başlamanızın da iyi olacağını (çok mantıklı bir şekilde) tartışacaklar.

INDEX + MATCH'a karşı argüman, bir yerine iki işlev gerektirmesidir, bu nedenle kullanıcıların (özellikle yeni kullanıcıların) öğrenmesi ve ustalaşması doğası gereği daha karmaşıktır.

İki sentim, Excel'i sık kullanıyorsanız, INDEX ve MATCH'ı nasıl kullanacağınızı öğrenmek isteyeceksiniz. Bu çok güçlü bir kombinasyon.

Ama aynı zamanda, genellikle başkalarından devraldığınız çalışma sayfalarında her yerde karşılaşacağınız DÜŞEYARA'yı da öğrenmeniz gerektiğini düşünüyorum. Basit durumlarda, DÜŞEYARA, işi sorunsuz bir şekilde halledecektir.

INDEX ve MATCH hakkında daha fazla bilgi edinmek için, bu makaleye bakın .

Yazar Dave Bruns


^