Excel XLOOKUP işlevi nasıl kullanılır -

İçindekiler

Özet

Excel XLOOKUP işlevi, DÜŞEYARA, YATAYARA ve ARA işlevi gibi eski işlevlerin yerine kullanılan modern ve esnek bir işlevdir. XLOOKUP, yaklaşık ve tam eşleşmeyi, kısmi eşleşmeler için joker karakterleri (*?) Ve dikey veya yatay aralıklardaki aramaları destekler.

Amaç

Aralık veya dizide arama değerleri

Geri dönüş değeri

Dönüş dizisinden eşleşen değer (ler)

Sözdizimi

= XLOOKUP (arama, arama_dizisi, dönüş_dizisi, (bulunmayan_), (eşleşme_modu), (arama_modu))

Argümanlar

  • arama - Arama değeri.
  • aranan_dizisi - Aranacak dizi veya aralık.
  • dönüş_dizisi - Döndürülecek dizi veya aralık.
  • not_found - (isteğe bağlı) Eşleşme bulunmazsa döndürülecek değer.
  • eşleşme_modu - (isteğe bağlı) 0 = tam eşleşme (varsayılan), -1 = tam eşleşme veya sonraki en küçük, 1 = tam eşleşme veya sonraki daha büyük, 2 = joker eşleşme.
  • search_mode - (isteğe bağlı) 1 = ilkten arama (varsayılan), -1 = sondan arama, 2 = artan ikili arama, -2 = azalan ikili arama.

Sürüm

Excel 365

Kullanım notları

XLOOKUP, DÜŞEYARA işlevinin modern bir alternatifidir. Çok çeşitli durumlarda kullanılabilen esnek ve çok yönlü bir işlevdir.

XLOOKUP, dikey veya yatay aralıklardaki değerleri bulabilir, yaklaşık ve tam eşleşmeleri gerçekleştirebilir ve kısmi eşleşmeler için joker karakterleri (*?) Destekler. Ek olarak, XLOOKUP, ilk değerden veya son değerden başlayarak verileri arayabilir (aşağıdaki eşleme türü ve arama modu ayrıntılarına bakın). VLOOKUP, HLOOKUP ve LOOKUP gibi daha eski işlevlerle karşılaştırıldığında, XLOOKUP birkaç önemli avantaj sunar.

Bulunamadı mesajı

XLOOKUP bir eşleşme bulamadığında, Excel'deki diğer eşleşme işlevleri gibi #YOK hatasını döndürür. Diğer eşleşme işlevlerinden farklı olarak, XLOOKUP, #YOK hatasını başka şekilde göründüğünde geçersiz kılmak için kullanılabilen not_found adlı isteğe bağlı bir bağımsız değişkeni destekler. Not_found için tipik değerler "Bulunamadı", "Eşleşme yok", "Sonuç yok", vb. Olabilir. Not_found için bir değer sağlarken, metni çift tırnak ("") içine alın.

Not: not_found için boş bir dizge ("") sağlarsanız dikkatli olun. Eşleşme bulunmazsa, XLOOKUP # N / A yerine hiçbir şey göstermez. Bir eşleşme bulunamadığında # N / A hatasını görmek istiyorsanız, bağımsız değişkeni tamamen atlayın.

Eşleşme türü

Varsayılan olarak, XLOOKUP tam eşleşme gerçekleştirir. Eşleşme davranışı, aşağıdaki seçeneklere sahip olan eşleştirme_türü adında isteğe bağlı bir bağımsız değişken tarafından kontrol edilir :

Eşleşme türü Davranış
0 (varsayılan) Tam eşleşme. Eşleşme yoksa #YOK döndürür.
-1 Tam eşleme veya sonraki küçük öğe.
1 Tam eşleme veya sonraki daha büyük öğe.
2 Joker karakter eşleşmesi (*,?, ~)

Arama modu

Varsayılan olarak, XLOOKUP ilk veri değerinden eşleşmeye başlar. Arama davranışı, aşağıdaki seçenekleri sağlayan arama_modu adlı isteğe bağlı bir bağımsız değişken tarafından kontrol edilir :

Arama modu Davranış
1 (varsayılan) İlk değerden ara
-1 Son değerden ara (ters)
2 Artan düzende sıralanmış ikili arama değerleri
-2 Azalan düzende sıralanmış ikili arama değerleri

İkili aramalar çok hızlıdır, ancak veriler gerektiği gibi sıralanmalıdır. Veriler doğru sıralanmamışsa, ikili arama tamamen normal görünen geçersiz sonuçlar döndürebilir.

Örnek 1 - temel tam eşleşme

Varsayılan olarak, XLOOKUP tam eşleşme gerçekleştirir. Aşağıdaki örnekte, XLOOKUP, Film'deki tam eşleşmeye göre Satışları almak için kullanılır. H5'teki formül:

=XLOOKUP(H4,B5:B9,E5:E9)

Daha ayrıntılı açıklama burada.

Örnek 2 - temel yaklaşık eşleşme

Yaklaşık bir eşleşmeyi etkinleştirmek için, "eşleşme_modu" bağımsız değişkeni için bir değer sağlayın. Aşağıdaki örnekte, XLOOKUP, yaklaşık bir eşleşme gerektiren miktara dayalı bir indirim hesaplamak için kullanılmıştır. F5'teki formül, "tam eşleşme veya sonraki en küçük" davranışla yaklaşık eşleşmeyi etkinleştirmek için eşleştirme_modu için -1 sağlar:

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Daha ayrıntılı açıklama burada.

Örnek 3 - birden çok değer

XLOOKUP, aynı eşleşme için aynı anda birden fazla değer döndürebilir. Aşağıdaki örnek, XLOOKUP'un tek bir formülle üç eşleşen değeri döndürecek şekilde nasıl yapılandırılabileceğini göstermektedir. C5'teki formül:

=XLOOKUP(B5,B8:B15,C8:E15)

Dönüş dizisinin (C8: E15) 3 sütun içerdiğine dikkat edin: İlk, Son, Bölüm. Üç değerin tümü döndürülür ve C5: E5 aralığına taşınır.

Örnek 4 - iki yönlü arama

XLOOKUP, bir XLOOKUP'ı diğerinin içine yerleştirerek iki yönlü bir arama gerçekleştirmek için kullanılabilir. Aşağıdaki örnekte, "iç" XLOOKUP, dönüş dizisi olarak "dış" XLOOKUP'a devredilen tüm satırı (Glass için tüm değerler) alır. Dış ÇAPRAZARA, uygun grubu (B) bulur ve nihai sonuç olarak karşılık gelen değeri (17.25) döndürür.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Daha fazla ayrıntı burada.

Örnek 5 - bulunamadı mesajı

Diğer arama işlevleri gibi, XLOOKUP bir değer bulamazsa, #YOK hatasını döndürür. # N / A yerine özel bir mesaj görüntülemek için, isteğe bağlı "bulunamadı" bağımsız değişkeni için çift tırnak ("") içine alınmış bir değer sağlayın. Örneğin, eşleşen bir film bulunmadığında, aşağıdaki çalışma sayfasına göre "Bulunamadı" öğesini görüntülemek için şunu kullanın:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Bu mesajı istediğiniz gibi özelleştirebilirsiniz: "Eşleşme yok", "Film bulunamadı" vb.

Örnek 6 - karmaşık ölçütler

Dizileri yerel olarak işleme yeteneği sayesinde, XLOOKUP karmaşık ölçütlerle kullanılabilir. Aşağıdaki örnekte, XLOOKUP ilk kayıtla eşleşiyor: hesap "x" ile başlıyor ve bölge "doğu" ve ay Nisan değil:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Ayrıntılar: (1) basit örnek, (2) daha karmaşık örnek.

XLOOKUP avantajları

XLOOKUP, özellikle DÜŞEYARA ile karşılaştırıldığında birçok önemli avantaj sunar:

  • XLOOKUP, arama değerlerinin sağında veya solunda veri arayabilir
  • XLOOKUP birden çok sonuç döndürebilir (yukarıdaki örnek 3)
  • XLOOKUP varsayılan olarak tam eşleşmeye ayarlıdır (DÜŞEYARA varsayılan olarak yaklaşıktır)
  • XLOOKUP, dikey ve yatay verilerle çalışabilir
  • XLOOKUP, ters arama yapabilir (sondan birinciye)
  • XLOOKUP, yalnızca bir değeri değil, tüm satırları veya sütunları döndürebilir
  • XLOOKUP, karmaşık ölçütleri uygulamak için yerel olarak dizilerle çalışabilir

Notlar

  1. XLOOKUP, hem dikey hem de yatay dizilerle çalışabilir.
  2. Arama değeri bulunamazsa, XLOOKUP # N / A sonucunu döndürür.
  3. Arama_dizisi ile uyumlu bir boyuta sahip olmalıdır return_array aksi XLOOKUP # DEĞER dönecektir, argüman!
  4. Çalışma kitapları arasında XLOOKUP kullanılırsa, her iki çalışma kitabının da açık olması gerekir, aksi takdirde XLOOKUP # BAŞV! Döndürür.
  5. INDEX işlevi gibi, XLOOKUP sonuç olarak bir başvuru döndürür.

İlgili videolar

Temel XLOOKUP örneği Bu videoda, XLOOKUP işlevini temel bir örnekle kuracağız. Şehir adıyla eşleşerek Ülke ve Nüfus bilgisini alacağız. Temel XLOOKUP yaklaşık eşleşmesi Bu videoda, miktara dayalı bir indirim hesaplamak için yaklaşık bir eşleşme gerçekleştirmek üzere XLOOKUP işlevini ayarlayacağız. Boole mantıklı XLOOKUP Bu videoda, XLOOKUP işlevinin birden çok ölçütü uygulamak için Boole mantığıyla nasıl kullanılacağına bakacağız. Birden çok arama değeri içeren XLOOKUP Bu videoda, tek bir arama değeri yerine bir dizi arama değeri sağlayarak, dinamik bir dizide birden çok değeri döndürecek şekilde XLOOKUP ayarını yapacağız.

Ilginç makaleler...