Excel XLOOKUP İşlevi: Bilmeniz Gereken Her Şey (10 Örnek)

Videoyu İzle - Excel XLOOKUP İşlevi (10 XLOOKUP Örneği)

Excel XLOOKUP işlevi nihayet geldi.

DÜŞEYARA veya İNDEX/MATCH kullanıyorsanız, DÜŞEYARA işlevinin sağladığı esnekliği seveceğinize eminim.

Bu eğitimde, XLOOKUP işlevi hakkında bilinmesi gereken her şeyi ve onu en iyi şekilde nasıl kullanacağınızı bilmenize yardımcı olacak bazı örnekleri ele alacağım.

O halde başlayalım!

XLOOKUP nedir?

DÜŞEYARA, Office 365'in yeni bir işlevidir ve DÜŞEYARA/YATAYARA işlevinin yeni ve geliştirilmiş bir sürümüdür.

DÜŞEYARA'nın eskiden yaptığı her şeyi ve çok daha fazlasını yapar.

XLOOKUP, bir veri kümesinde (dikey veya yatay) bir değeri hızlı bir şekilde aramanıza ve başka bir satırda/sütunda karşılık gelen değeri döndürmenize izin veren bir işlevdir.

Örneğin, bir sınavdaki öğrencilerin puanlarını aldıysanız, öğrencinin adını kullanarak öğrencinin ne kadar puan aldığını hızlıca kontrol etmek için DÜŞEYARA'yı kullanabilirsiniz.

Bazı konulara derinlemesine daldıkça bu işlevin gücü daha da netleşecek. XLOOKUP örnekleri Bu eğitimde daha sonra.

Ancak örneklere geçmeden önce büyük bir soru var - XLOOKUP'a nasıl erişebilirim?

XLOOKUP'a Nasıl Erişilir?

Şu an itibariyle XLOOKUP yalnızca Office 365 kullanıcıları tarafından kullanılabilir.

Bu nedenle, Excel'in önceki sürümlerini (2010/2013/2016/2019) kullanıyorsanız bu işlevi kullanamazsınız.

Bunun önceki sürümler için yayınlanıp yayınlanmayacağından da emin değilim (belki Microsoft, Power Query için yaptıkları şekilde bir eklenti oluşturabilir). Ancak şu andan itibaren yalnızca Office 365 kullanıyorsanız kullanabilirsiniz.

Office 365'e yükseltmek için burayı tıklayın

Halihazırda Office 365 (Ev, Kişisel veya Üniversite sürümü) kullanıyorsanız ve buna erişiminiz yoksa Dosya sekmesine gidebilir ve ardından Hesap'a tıklayabilirsiniz.

Bir Office Insider programı olacaktır ve Office Insider Programına tıklayıp katılabilirsiniz. Bu, XLOOKUP işlevine erişmenizi sağlayacaktır.

XLOOKUP'ın yakında tüm Office 365 sürümlerinde kullanıma sunulmasını bekliyorum.

Not: XLOOKUP, Mac için Office 365 ve Web için Excel için de kullanılabilir (Çevrimiçi Excel)

XLOOKUP İşlev Sözdizimi

XLOOKUP işlevinin sözdizimi aşağıdadır:

=XLOOKUP(arama_değeri, arama_dizisi, dönüş_dizisi, [if_not_found], [match_mode], [arama_modu])

DÜŞEYARA'yı kullandıysanız, sözdiziminin elbette bazı harika ek özelliklerle birlikte oldukça benzer olduğunu fark edeceksiniz.

Sözdizimi ve argüman biraz fazla görünüyorsa endişelenmeyin. Bunları, bu eğitimde daha sonra kristal netliğinde hale getirecek bazı kolay XLOOKUP örnekleriyle ele alacağım.

DÜŞEYARA işlevi 6 argüman alabilir (3 zorunlu ve 3 isteğe bağlı):

1. aranan_değer - aradığınız değer
2. arama_dizisi - arama değerini aradığınız dizi
3. dönüş_dizisi - değeri almak ve döndürmek istediğiniz dizi (arama değerinin bulunduğu konuma karşılık gelir)
4. [if_not_found] - aranan değerin bulunamaması durumunda döndürülecek değer. Bu bağımsız değişkeni belirtmemeniz durumunda bir #YOK hatası döndürülür.
5. [match_mode] - Burada istediğiniz eşleşme türünü belirtebilirsiniz:
   • 0 - Aranan_değerin, aranan_dizideki değerle tam olarak eşleşmesi gereken tam eşleşme. Bu varsayılan seçenektir.
   • -1 - Tam eşleşmeyi arar, ancak bulunursa bir sonraki daha küçük öğeyi/değeri döndürür
   • 1 - Tam eşleşmeyi arar, ancak bulunursa bir sonraki daha büyük öğeyi/değeri döndürür
   • 2 - Joker karakterler (* veya ~) kullanarak kısmi eşleştirme yapmak için
6. [Arama modu] - Burada, XLOOKUP işlevinin aranan_diziyi nasıl araması gerektiğini belirtirsiniz.
   • 1 - Bu, fonksiyonun aranan_dizide yukarıdan (ilk öğe) aşağıya (son öğe) kadar aranan_değeri aramaya başladığı varsayılan seçenektir.
   • -1 - Aşağıdan yukarıya doğru arama yapar. aranan_dizideki son eşleşen değeri bulmak istediğinizde kullanışlıdır
   • 2 - Verilerin artan düzende sıralanması gereken bir ikili arama gerçekleştirir. Sıralanmazsa, bu hata veya yanlış sonuçlar verebilir
   • -2 - Verilerin azalan düzende sıralanması gereken bir ikili arama gerçekleştirir. Sıralanmazsa, bu hata veya yanlış sonuçlar verebilir

XLOOKUP İşlev Örnekleri

Şimdi ilginç kısma geçelim - bazı pratik XLOOKUP örnekleri.

Bu örnekler, XLOOKUP'ın nasıl çalıştığını, DÜŞEYARA ve INDEX/MATCH'tan nasıl farklı olduğunu ve bu işlevin bazı geliştirmelerini ve sınırlamalarını daha iyi anlamanıza yardımcı olacaktır.

Örnek dosyayı indirmek ve takip etmek için buraya tıklayın

Örnek 1: Bir Arama Değeri Getirme

Aşağıdaki veri kümesine sahip olduğunuzu ve Greg için matematik puanını (arama değeri) almak istediğinizi varsayalım.

Bunu yapan formül aşağıdadır:

=GÖRÜNTÜLEME(F2,A2:A15,B2:B15)

Yukarıdaki formülde, adı aradığı (yukarıdan aşağıya), tam bir eşleşme bulduğu ve B2:B15'ten karşılık gelen değeri döndürdüğü zorunlu argümanları kullandım.

DÜŞEYARA ve DÜŞEYARA işlevinin bariz bir farkı, arama dizisini işleme biçimleridir. DÜŞEYARA'da, arama değerinin en soldaki sütunda olduğu dizinin tamamına sahipsiniz ve ardından sonucu almak istediğiniz sütun numarasını belirtirsiniz. XLOOKUP ise, lookup_array ve return_array'i ayrı ayrı seçmenize izin verir.

Lookup_array ve return_array öğelerini ayrı bağımsız değişkenler olarak kullanmanın bir anlık avantajı, artık şunları yapabileceğiniz anlamına gelir: sola bak. DÜŞEYARA, yalnızca sağdaki bir değeri arayabileceğiniz ve bulabileceğiniz bu sınırlamaya sahipti. Ancak XLOOKUP ile bu sınırlama ortadan kalktı.

İşte bir örnek. Aynı veri kümesine sahibim, burada adın sağda ve dönüş_aralığı solda.

Matematikte Greg'in puanını almak için kullanabileceğim formül aşağıdadır (bu, aranan_değerin soluna bakmak anlamına gelir)

=DÖNÜŞME(F2,D2:D15,A2:A15)

XLOOKUP, başka bir önemli sorunu çözüyor - Yeni bir sütun eklemeniz veya sütunları hareket ettirmeniz durumunda, elde edilen veriler yine de doğru olacaktır. DÜŞEYARA, çoğu zaman sütun dizin değerinin sabit kodlanmış olduğu durumlarda bozulur veya yanlış sonuç verir.

Örnek 2: Tüm Kaydı Arama ve Alma

Aynı verileri örnek olarak alalım.

Bu durumda, sadece Greg'in Math'daki puanını almak istemiyorum. Bütün derslerden puan almak istiyorum.

Bu durumda aşağıdaki formülü kullanabilirim:

=DÖNÜŞME(F2,A2:A15,B2:D15)

Yukarıdaki formül, bir sütundan daha büyük bir dönüş_dizi aralığı kullanır (B2:D15). Bu nedenle, A2:A15'te arama değeri bulunduğunda, formül, dönüş_dizisinden tüm satırı döndürür.

Ayrıca, yalnızca otomatik olarak doldurulan dizinin parçası olan hücreleri silemezsiniz. Bu örnekte, H2 veya I2'yi silemezsiniz. Eğer denersen, hiçbir şey olmaz. Bu hücreleri seçerseniz, formül çubuğundaki formül grileşir (değiştirilemeyeceğini gösterir)

G2 hücresindeki (başlangıçta girdiğimiz yer) formülü silebilirsiniz, bu tüm sonucu siler.

Bu, DÜŞEYARA ile daha önce olduğu gibi yararlı bir geliştirmedir, her formül için sütun numarasını ayrı ayrı belirtmeniz gerekir.

Örnek 3: XLOOKUP Kullanarak İki Yönlü Arama (Yatay ve Dikey Arama)

Aşağıda, Matematikte Greg'in puanını (G2 hücresindeki konu) bilmek istediğim bir veri kümesi var.

Bu, A sütununda adı ve 1. satırda konu adını aradığım iki yönlü bir arama kullanılarak yapılabilir. Bu iki yönlü aramanın yararı, sonucun konu adının öğrenci adından bağımsız olmasıdır. Konu adını Kimya olarak değiştirirsem, bu iki yönlü XLOOKUP formülü yine çalışır ve bana doğru sonucu verirdi.

İki yönlü aramayı gerçekleştirecek ve doğru sonucu verecek formül aşağıdadır:

=DÜŞEYARA(G1,B1:D1,DÜŞEYARA(F2,A2:A15,B2:D15))

Bu formül, ilk önce F2 hücresindeki öğrencinin tüm notlarını almak için kullandığım bir İç İçe XLOOKUP kullanır.

Dolayısıyla, DÜŞEYARA(F2,A2:A15,B2:D15) sonucunun {21,94,81} olduğu, bu durumda Greg tarafından atılan bir dizi işarettir.

Bu daha sonra dönüş dizisi olarak dış XLOOKUP formülünde tekrar kullanılır. Dış XLOOKUP formülünde, (G1 hücresindeki) konu adını arıyorum ve arama dizisi B1:D1.

Konu adı Math ise, bu dış XLOOKUP formülü, bu örnekte {21,94,81} olan dönüş dizisinden ilk değeri getirir.

Bu, şimdiye kadar INDEX ve MATCH kombinasyonu kullanılarak elde edilenin aynısını yapar.

Örnek dosyayı indirmek ve takip etmek için buraya tıklayın

Örnek 4: Arama Değeri Bulunmadığında (Hata İşleme)

XLOOKUP formülüne hata işleme eklendi.

DÜŞEYARA işlevindeki dördüncü argüman, aramanın bulunamaması durumunda ne istediğinizi belirtebileceğiniz [if_not_found]'dur.

Eşleşme olması durumunda Matematik puanını almak istediğiniz ve adın bulunamaması durumunda geri dönmek istediğiniz aşağıda gösterilen veri setine sahip olduğunuzu varsayalım - 'Görünmedi'

Aşağıdaki formül bunu yapacaktır:

=GÖRÜNTÜLEME(F2,A2:A15,B2:B15,"Görünmedi")

Bu durumda, eşleşme olmaması durumunda elde etmek istediklerimi sabit kodladım. Bir hücreye veya formüle hücre başvurusu da kullanabilirsiniz.

Örnek 5: İç İçe XLOOKUP (Birden Çok Aralıkta Arama)

[if_not_found] argümanına sahip olmanın dehası, kullanmanıza izin vermesidir. iç içe XLOOKUP formülü.

Örneğin, aşağıda gösterildiği gibi iki ayrı listeniz olduğunu varsayalım. Bu iki tabloyu aynı sayfada bulundururken, bunları ayrı sayfalarda veya hatta çalışma kitaplarında tutabilirsiniz.

Aşağıda, her iki tabloda da adı kontrol edecek ve belirtilen sütundan karşılık gelen değeri döndürecek iç içe XLOOKUP formülü verilmiştir.

=DÜŞEYARA(A12,A2:A8,B2:B8,DÖNÜŞME(A12,F2:F8,G2:G8))

Yukarıdaki formülde, başka bir XLOOKUP formülü kullanmak için [if_not_found] argümanını kullandım. Bu, ikinci XLOOKUP'ı aynı formüle eklemenize ve iki tabloyu tek bir formülle taramanıza olanak tanır.

Bir formülde kaç tane iç içe XLOOKUP kullanabileceğinizden emin değilim. 10'a kadar denedim işe yaradı sonra vazgeçtim 🙂

Örnek 6: Son Eşleşen Değeri Bulun

Buna çok ihtiyaç vardı ve XLOOKUP bunu mümkün kıldı. Artık bir aralıktaki son eşleşen değeri elde etmek için karmaşık yollar bulmanız gerekmiyor.

Aşağıda gösterilen veri setine sahip olduğunuzu ve her bir departmanda en son ne zaman işe alındığını ve işe alma tarihinin ne olduğunu kontrol etmek istediğinizi varsayalım.

Aşağıdaki formül, her departman için son değeri arayacak ve son işe alınan kişinin adını verecektir:

=DÖNÜŞME(F1,$B$2:$B$15,$A$2:$A$15,,,-1)

Ve aşağıdaki formül, her departman için son işe alım tarihini verecektir:

=DÖNÜŞME(F1,$B$2:$B$15,$C$2:$C$15,,,-1)

XLOOKUP, aramanın yönünü belirtmek için dahili bir özelliğe sahip olduğundan (ilkten sona veya sondan ilke), bu basit bir formülle yapılır. Dikey verilerle DÜŞEYARA ve İNDEKS/KAÇINCI her zaman yukarıdan aşağıya bakar, ancak XLOOKUP ile yönü aşağıdan yukarıya da belirtebilir.

Örnek 7: XLOOKUP ile Yaklaşık Eşleşme (Vergi Oranını Bul)

XLOOKUP ile ilgili bir diğer kayda değer gelişme, artık dört eşleşme modunun (DÜŞEYARA 2 ve MAÇTA 3'e sahip) olmasıdır.

Arama değerinin nasıl eşleştirileceğine karar vermek için dört bağımsız değişkenden herhangi birini belirtebilirsiniz:

• 0 - Aranan_değerin, aranan_dizideki değerle tam olarak eşleşmesi gereken tam eşleşme. Bu varsayılan seçenektir.
• -1 - Tam eşleşmeyi arar, ancak bulunursa bir sonraki daha küçük öğeyi/değeri döndürür
• 1 - Tam eşleşmeyi arar, ancak bulunursa bir sonraki daha büyük öğeyi/değeri döndürür
• 2 - Joker karakterler (* veya ~) kullanarak kısmi eşleştirme yapmak için

Ancak en iyi yanı, verilerinizin artan düzende mi yoksa azalan düzende mi sıralandığı konusunda endişelenmenize gerek olmamasıdır. Veriler sıralanmamış olsa bile, XLOOKUP bununla ilgilenecektir.

Aşağıda, her bir kişinin komisyonunu bulmak istediğim bir veri setim var - ve komisyonun sağdaki tablo kullanılarak hesaplanması gerekiyor.

Bunu yapacak formül aşağıdadır:

=DÜŞEYARA(B2,$E$2:$E$6,$F$2:$F$6,0,-1)*B2

Bu, arama olarak satış değerini kullanır ve sağdaki arama tablosuna bakar. Bu formülde, beşinci argüman ([match_mode]) olarak -1 kullandım, yani tam bir eşleşme arayacak ve bir eşleşme bulamadığında, arama değerinden biraz daha küçük bir değer döndürecek. .

Ve dediğim gibi, verilerinizin sıralanıp sıralanmadığı konusunda endişelenmenize gerek yok.

Örnek dosyayı indirmek ve takip etmek için buraya tıklayın

Örnek 8: Yatay Arama

XLOOKUP, yatay aramanın yanı sıra dikey arama da yapabilir.

Aşağıda, öğrenci adlarının ve puanlarının satırlar halinde bulunduğu bir veri kümem var ve B7 hücresindeki adın puanını almak istiyorum.

Aşağıdaki formül bunu yapacaktır:

=GÖRÜNTÜLEME(B7,B1:O1,B2:O2)

Bu, basit bir aramadan başka bir şey değildir (Örnek 1'de gördüğümüze benzer), ancak yataydır.

Dikey arama hakkında ele aldığım tüm örnekler, XLOOKUP (DÜŞEYARA ve YATAYARA'ya veda) kullanılarak yatay bir arama ile de yapılabilir.

Örnek 9: Koşullu Arama (Diğer Formüllerle XLOOKUP Kullanma)

Bu biraz gelişmiş bir örnektir ve ayrıca karmaşık aramalar yapmanız gerektiğinde XLOOKUP'ın gücünü gösterir.

Aşağıda, öğrencilerin adlarını ve puanlarını aldığım bir veri seti var ve her konuda en yüksek puanı alan öğrencinin adını ve her konuda 80'den fazla puan alan öğrenci sayısını bilmek istiyorum.

Her dersten en yüksek notu alan öğrencinin adını verecek formül aşağıdadır:

=DÜŞEYARA(MAKS(DÜŞEYARA(G1,$B$1:$D$1,$B$2:$D$15)),DÜŞEYARA(G1,$B$1:$D$1,$B$2:$D$15),$A $2:$A$15)

XLOOKUP tüm bir diziyi döndürmek için kullanılabildiğinden, ilk önce gerekli konu için tüm işaretleri almak için kullandım.

Örneğin, Matematik için, DÜŞEYARA(G1,$B$1:$D$1,$B$2:$D$15) kullandığımda, bana matematikteki tüm puanları verir. Daha sonra bu aralıktaki maksimum puanı bulmak için MAX işlevini kullanabilirim.

Bu maksimum puan daha sonra benim arama değerim olur ve arama aralığı XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15) tarafından döndürülen dizi olur.

Bunu, maksimum puanı alan öğrencinin adını almak için başka bir XLOOKUP formülünde kullanıyorum.

80'den fazla puan alan öğrenci sayısını saymak için aşağıdaki formülü kullanın:

=EĞERSAY(DÜŞEYARA(G1,$B$1:$D$1,$B$2:$D$15),">80")

Bu, verilen konu için tüm değerlerin bir aralığını elde etmek için yalnızca DÜŞEYARA formülünü kullanır. Ardından, 80'den fazla olan puanların sayısını almak için EĞERSAY işlevine sarar.

Örnek 10: XLOOKUP'ta Joker Karakter Kullanma

DÜŞEYARA ve KAÇINCI'da joker karakterleri kullanabildiğiniz gibi, bunu DÜŞEYARA ile de yapabilirsiniz.

Ama bir fark var.

XLOOKUP'ta joker karakterler kullandığınızı belirtmeniz gerekir (beşinci argümanda). Bunu belirtmezseniz, XLOOKUP size bir hata verecektir.

Aşağıda, şirket adlarına ve piyasa değerlerine sahip olduğum bir veri kümesi var.

D sütununda bir şirket adına bakmak ve soldaki tablodan piyasa değerini almak istiyorum. Ve Sütun D'deki isimler tam olarak eşleşmediğinden, joker karakterler kullanmam gerekecek.

Bunu yapacak formül aşağıdadır:

=XLOOKUP("*"&D2&"*",$A$2:$A$11,$B$2:$B$11,,2)

Yukarıdaki formülde, yıldız işareti (*) joker karakterini D2'den önce olduğu gibi kullandım (çift tırnak içinde olması ve ve işareti kullanılarak D2 ile birleştirilmesi gerekiyor).

Bu, formüle tüm hücrelere bakmasını söyler ve D2 hücresindeki (Apple olan) kelimeyi içeriyorsa, onu tam bir eşleşme olarak kabul edin. D2 hücresindeki metinden önce ve sonra kaç ve hangi karakter olursa olsun.

Ve XLOOKUP'ın joker karakterleri kabul ettiğinden emin olmak için beşinci argüman 2'ye (joker karakter eşleşmesi) ayarlanmıştır.

Örnek 11: Sütundaki Son Değeri Bulun

XLOOKUP, aşağıdan yukarıya doğru arama yapmanıza izin verdiği için, bir listedeki son değeri kolayca bulabilir ve ilgili değeri bir sütundan getirebilirsiniz.

Aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve son şirketin ne olduğunu ve bu son şirketin piyasa değerinin ne olduğunu bilmek istediğinizi varsayalım.

Aşağıdaki formül size son şirketin adını verecektir:

=XLOOKUP("*",A2:A11,A2:A11,,2,-1)

Ve aşağıdaki formül, listedeki son şirketin piyasa değerini verecektir:

=DÖNME("*",A2:A11,B2:B11,,2,-1)

Bu formüller yine joker karakterler kullanır. Bunlarda, arama değeri olarak yıldız işareti (*) kullandım; bu, bunun karşılaştığı ilk hücreyi tam bir eşleşme olarak kabul edeceği anlamına gelir (yıldız işareti herhangi bir karakter ve herhangi bir sayıda karakter olabilir).

Ve yön aşağıdan yukarıya doğru olduğu için (dikey olarak düzenlenmiş veriler için), listedeki son değeri döndürür.

Ve ikinci formül, listedeki soyadının piyasa değerini almak için ayrı bir dönüş_aralığı kullanır.

Örnek dosyayı indirmek ve takip etmek için buraya tıklayın

Ya XLOOKUP'ınız yoksa?

XLOOKUP büyük olasılıkla yalnızca Office 365 kullanıcıları tarafından kullanılabilecek olduğundan, bunu elde etmenin bir yolu Office 365'e yükseltmektir.

Zaten Office 365 Ev, Kişisel veya Üniversite sürümünüz varsa, XLOOKUP'a zaten erişiminiz vardır. Tek yapmanız gereken Office Insider programına katılmak.

Bunu yapmak için Dosya sekmesine gidin, Hesap'a tıklayın ve ardından Office içeriden bilgi seçeneğine tıklayın. İçeriden öğrenenlerin programına katılma seçeneği olacaktır.

Başka Office 365 abonelikleriniz (Enterprise gibi) varsa, XLOOKUP ve diğer harika özelliklerin (dinamik diziler, SIRALAMA ve FİLTRE gibi formüller gibi) yakında kullanıma sunulacağına eminim.

Excel 2010/2013/2016/2019 kullanıyorsanız, XLOOKUP'a sahip olmayacaksınız ve arama formüllerinden en iyi şekilde yararlanmak için DÜŞEYARA, YATAYARA ve İNDEKS/KAÇINCI kombinasyonunu kullanmaya devam etmeniz gerekecektir.

XLOOKUP Geriye Dönük Uyumluluk

Bu, dikkatli olmanız gereken bir şeydir - XLOOKUP Geriye dönük uyumlu DEĞİL.

Bu, bir dosya oluşturup DÜŞEYARA formülünü kullanırsanız ve ardından onu DÜŞEYARA özelliği olmayan bir sürümde açarsanız, hataları göstereceği anlamına gelir.

XLOOKUP doğru yönde atılmış büyük bir adım olduğundan, bunun varsayılan arama formülü olacağına inanıyorum, ancak yaygın olarak benimsenmesi kesinlikle birkaç yıl alacaktır. Sonuçta, hala Excel 2003 kullanan bazı insanlar görüyorum.

Bunlar, tüm arama ve referans işlemlerini daha hızlı yapmanıza yardımcı olabilecek ve aynı zamanda kullanımı kolaylaştırabilecek 11 XLOOKUP Örneğidir.

Umarım bu öğreticiyi faydalı bulmuşsunuzdur!