Excel 2020: XLOOKUP'un On İki Faydası - Excel İpuçları

Yeni XLOOKUP işlevi Kasım 2019'dan itibaren Office 365'te kullanıma sunuluyor. Excel ekibinden Joe McDaid, DÜŞEYARA kullanan kişilerle DİZİN / KAÇINCI kullanan kişileri birleştirmek için XLOOKUP'u tasarladı. Bu bölümde, ÇAPRAZUP'un 12 faydası tartışılacaktır:

  1. Tam eşleme varsayılandır.
  2. DÜŞEYARA'nın tamsayı tabanlı üçüncü argümanı artık uygun bir referanstır.
  3. IFNA, eksik değerleri işlemek için yerleşiktir.
  4. XLOOKUP, sola gitmede sorun yaşamıyor.
  5. Tabloyu sıralamadan sonraki-küçük veya sonraki-büyük eşleşmeyi bulun.
  6. XLOOKUP, HLOOKUP yapabilir.
  7. Alttan arayarak son eşleşmeyi bulun.
  8. Joker karakterler varsayılan olarak "kapalıdır", ancak bunları tekrar açabilirsiniz.
  9. 12 ayın tamamını tek bir formülde döndür.
  10. XLOOKUP, XLOOKUP (); XLOOKUP () gibi iki nokta üst üste işaretinin yanındaysa hücre referansı döndürebilir
  11. INDEX (, MATCH, MATCH) gibi çift yönlü bir eşleştirme yapabilir.
  12. Tüm aramaları, ARA işlevinin yapabileceği gibi tek bir formülde toplayabilir.

Sözdizimi şu şekildedir: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).

XLOOKUP Avantajı 1: Varsayılan Olarak Tam Eşleşme

DÜŞEYARA formüllerimin% 99'u, tam bir eşleşmeyi belirtmek için YANLIŞ veya 0 ile bitiyor. Her zaman DÜŞEYARA'nın tam eşleme sürümünü kullanıyorsanız, ÇAPRAZARA işlevinizin eşleştirme_modunu kapalı bırakmaya başlayabilirsiniz.

Aşağıdaki şekilde, A4 hücresinden W25-6'yı arıyorsunuz. Bu öğeyi L8: L35'te aramak istiyorsunuz. Bulunduğunda, N sütunundan karşılık gelen fiyatı istiyorsunuz. Match_mode olarak False'ı belirtmenize gerek yoktur çünkü XLOOKUP varsayılan olarak tam bir eşleşmeyi kullanır.

A4'teki değeri ÇAPRAZARA. L8: L35'e bakın. N8: N35'den ilgili fiyatı döndürün.

XLOOKUP Fayda 2: Results_Array tam sayı yerine referanstır

XLOOKUP'tan önce kullanacağınız DÜŞEYARA formülünü düşünün. Üçüncü argüman, 3. sütunu döndürmek istediğinizi belirtmek için 3 olurdu. Bilgisiz bir iş arkadaşınızın tablonuza bir sütun eklemesi (veya silmesi) tehlikesi her zaman vardı. Tabloda fazladan bir sütun olduğunda, bir fiyat döndüren DÜŞEYARA bir açıklama döndürmeye başlayacaktır. XLOOKUP bir hücre başvurusuna işaret ettiğinden, formül şu anda O sütununda bulunan fiyatı göstermeye devam edecek şekilde kendini yeniden yazar.

Birisi arama tablosuna yeni bir sütun eklerse eski DÜŞEYARA başarısız olur. XLOOKUP çalışmaya devam ediyor.

ÇAPRAZUP Avantaj 3: IFNA, isteğe bağlı bir bağımsız değişken olarak yerleşiktir

Korkunç #YOK hatası, arama değeriniz tabloda bulunmadığında döndürülür. Geçmişte, # N / A'yı başka bir şeyle değiştirmek için, DÜŞEYARA etrafına sarılmış EĞERHATA veya IFNA'yı kullanmanız gerekirdi.

Bir öğe bulunamadığında, VLOOKUP veya XLOOKUP'tan # N / A döndürür…

Rico'nun YouTube kanalımdaki bir önerisi sayesinde, Excel ekibi if_not_found için isteğe bağlı dördüncü bir argüman ekledi. Bu #YOK hatalarını sıfırla değiştirmek istiyorsanız, dördüncü bağımsız değişken olarak 0 eklemeniz yeterlidir. Veya "Değer bulunamadı" gibi bir metin de kullanabilirsiniz.

XLOOKUP'taki isteğe bağlı dördüncü bağımsız değişken "bulunamazsa" dır. Oraya 0 veya "Bulunamadı" yazın.

XLOOKUP Avantaj 4: Anahtar alanın soluna bakmak sorun değil

DÜŞEYARA, VLOOKUP'a (A4, CHOOSE ((1,2), G7: G34, F7: F34), 2, False) başvurmadan anahtar alanın soluna bakamaz. XLOOKUP ile, Arama_dizisinin solunda Results_array olması sorun olmaz.

XLOOKUP ile, G sütunundaki parça numaralarına bakılırken Kategori F sütunundan döndürülürken sorun yoktur. Bu her zaman DÜŞEYARA'nın zayıf yönüydü: sola bakamıyordu.

XLOOKUP Avantajı 5: Sıralamadan sonraki küçük veya sonraki daha büyük eşleşme

DÜŞEYARA, tam eşleşmeyi veya yalnızca daha küçük bir değeri arama seçeneğine sahipti. Dördüncü argümanı DÜŞEYARA'nın dışında bırakabilir veya Yanlış'ı Doğru olarak değiştirebilirsiniz. Bunun işe yaraması için, arama tablosunun artan sırada sıralanması gerekiyordu.

DÜŞEYARA'nın Yaklaşık Eşleşme sürümüne bir örnek. 10 binden 20 bine kadar yapılan herhangi bir satış 12 $ bonus alır.

Ancak DÜŞEYARA, tam eşleşmeyi veya sonraki daha büyük öğeyi döndürme yeteneğine sahip değildi. Bunun için, MATCH'ı eşleştirme modu olarak -1 ile kullanmaya geçmeniz ve arama tablosunun azalan sırada sıralanmasına dikkat etmeniz gerekiyordu.

XLOOKUP'un isteğe bağlı beşinci bağımsız değişkeni match_mode yalnızca tam eşleşmeyi, eşit veya daha küçük, eşit veya sadece daha büyük olanı arayabilir. XLOOKUP'taki değerlerin MATCH'tan daha anlamlı olduğunu unutmayın:

  • -1, eşit veya daha küçük değeri bulur
  • 0 tam bir eşleşme bul
  • 1, eşit veya daha büyük olan değeri bulur.

Ancak, en şaşırtıcı kısım: arama tablosunun sıralanması gerekmez ve herhangi bir eşleştirme_modu çalışacaktır.

Aşağıda, -1'lik bir match_mode sonraki-küçük öğeyi bulur.

XLOOKUP'un beşinci argümanı Match_Mode'dur. 0 tam eşleşme içindir. Negatif olan, Tam Eşleşme veya Sonraki Daha Küçük Öğe için kullanılır. Pozitif 1, tam eşleşme veya sonraki daha büyük öğe içindir. 2 Joker Karakter Eşleşmesi içindir. Dördüncü bağımsız değişkendeki DÜŞEYARA işlevinin True ile yapacağını aynalamak için, XLOOKUP'a eşleştirme_modu bağımsız değişkeni olarak negatif bir tane koyun.

Burada, 1'lik bir match_mode, partideki kişi sayısına bağlı olarak hangi araca ihtiyaç olduğunu bulur. Arama tablosunun yolculara göre sıralanmadığını ve araç adının anahtarın solunda olduğunu unutmayın.

XLOOKUP, DÜŞEYARA'nın yapamadığı bir şeyi yapabilir: tam eşleşmeyi veya daha büyük olanı bulun. Bu durumda, bir tur şirketinin bir rezervasyon listesi vardır. Yolcu sayısına bağlı olarak, arama tablosu bu insanlar için hangi araca ihtiyacınız olduğunu gösterir.

Tablo diyor ki:

  • 64 kişilik otobüs
  • Araba 4 kişi tutar
  • Motorcyle 1 kişiyi barındırır
  • Tour Van, 12 kişilik
  • Van'da 6 kişi var.

Bonus olarak, veriler Araca göre sıralanır (eski çözümde, MATCH kullanılarak, tablonun Kapasiteye göre azalan sıralanması gerekirdi. Ayrıca: Araç Kapasitenin solundadır.

XLOOKUP Avantajı 6: Yana doğru XLOOKUP, YATAYARA'nın yerini alır

Arama_dizisi ve sonuç_dizisi, XLOOKUP ile yatay olabilir, bu da YATAYARA'yı değiştirmeyi kolaylaştırır.

Burada arama tablosu yataydır. Geçmişte, bu YATAYARA gerektirirdi, ancak XLOOKUP yana doğru giden bir tabloyla başa çıkabilir.

XLOOKUP Faydası 7: En son eşleşmeyi en alttan arayın

YouTube'da bir İngiliz at çiftliğinden gelen bir soruyu yanıtlayan eski bir videom var. Araç filoları vardı. Yakıt veya servis için bir araç her geldiğinde, aracı, tarihi ve kilometreyi bir elektronik tabloya kaydetti. Her araç için bilinen en son kilometreyi bulmak istediler. Excel-2017 dönemi MAXIFS bugün bunu çözebilirken, yıllar önce çözüm, ARA'yı kullanan gizli bir formüldü ve sıfıra bölmeyi içeriyordu.

Bugün, XLOOKUP'un isteğe bağlı altıncı bağımsız değişkeni, aramanın veri kümesinin altından başlaması gerektiğini belirtmenize olanak tanır.

Listedeki son eşleşmeyi bulun.

Not

Bu büyük bir gelişme olsa da, yalnızca ilk veya son eşleşmeyi bulmanızı sağlar. Bazı insanlar bunun ikinci veya üçüncü eşleşmeyi bulmanızı sağlayacağını umdu, ancak arama_modu argümanının amacı bu değil.

Dikkat

Yukarıdaki şekil, eski ikili aramayı kullanan arama modları olduğunu göstermektedir. Joe McDaid bunların kullanılmamasını tavsiye ediyor. İlk olarak, 2018'den itibaren geliştirilmiş arama algoritması yeterince hızlıdır ve önemli bir hız avantajı yoktur. İkincisi, bilgisiz bir iş arkadaşınızın arama tablosunu sıralaması ve yanlış cevaplar vermesi riskini alırsınız.

XLOOKUP Avantajı 8: Joker karakterler varsayılan olarak "kapalıdır"

Çoğu kişi DÜŞEYARA'nın yıldız işaretini, soru işaretini ve yaklaşık işaretini "# 51 DÜŞEYARA'da Joker Karakter Kullanın" sayfa 143 bölümünde açıklandığı gibi joker karakterler olarak ele aldığını fark etmedi. XLOOKUP ile joker karakterler varsayılan olarak kapalıdır. XLOOKUP'un bu karakterleri joker karakter olarak değerlendirmesini istiyorsanız, Eşleştirme_ Modu olarak 2'yi kullanın.

Çok az kişi DÜŞEYARA'nın, arama değerindeki yıldız işaretlerini joker karakter olarak ele aldığını fark etti. Varsayılan olarak, XLOOKUP joker karakterler kullanmaz, ancak 2 Eşleme Modu: Joker Karakter Eşleştirme kullanırsanız DÜŞEYARA gibi davranmaya zorlayabilirsiniz.

XLOOKUP Avantajı 9: 12 Ayın Tümünü Tek Bir Formülde Geri Verin!

Bu gerçekten Dinamik Dizilerin bir avantajı, ancak XLOOKUP'u sevmek için en sevdiğim nedendir. Bir aramada 12 ayın tümünü geri döndürmeniz gerektiğinde, B6'ya dikdörtgen dönüş_dizisi ile girilen tek bir formül birden çok sonuç döndürür. Bu sonuçlar bitişik hücrelere yayılacaktır.

Aşağıdaki şekilde, B7'ye girilen tek bir formül, B7: M7'de gösterilen 12 cevabın tümünü verir.

Ocak sütunundaki tek bir ÇOK SAYFA, Ocak'tan Aralık'a kadar olan sayıları döndürür. Bu, 12 sütunlu bir results_array belirtilerek yapılır.

XLOOKUP Avantajı 10: İki Noktaya Komşuysa Hücre Referansı döndürebilir

Bu karmaşık ama güzel. Geçmişte, işlev iki nokta üst üste dokunuyorsa, bir hücre değeri döndürmekten bir hücre başvurusu döndürmeye değişen yedi işlev vardı. Bir örnek için bkz. Uçucu Olmayan OFSET olarak A2: INDEX () kullanın. XLOOKUP, CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET ve SWITCH'e katılarak bu davranışı sunan sekizli işlevdir.

Aşağıdaki şekli düşünün. Birisi E4'te Cherry ve E5'te Fig'ü seçiyor. B6'dan B9'a kadar her şeyi toplayacak bir formül istiyorsunuz.

Şekilde, iki hücrede iki XLOOKUP formülü gösterilmektedir. İlki B6 hücresinden 15 döndürür. İkincisi, B9'dan 30'u geri alır. Ancak üçüncü bir hücrede, iki XLOOKUP formülünü iki nokta üst üste ile birleştiren ve ardından bunu bir TOPLA işleviyle saran bir formül vardır. Sonuç, B6: B9'un TOPLAMIdır, çünkü XLOOKUP, iki nokta üst üste gibi bir işlecin yanında görünüyorsa bir hücre başvurusu döndürebilir. Bunun işe yaradığını kanıtlamak için, sonraki birkaç şekil bu formülü Formülü Değerlendir iletişim kutusunda gösterecektir.

Yukarıdaki şekilde, E4'ün XLOOKUP'ının B6 hücresinden 15'i döndüreceğini görebilirsiniz. E5'in bir XLOOKUP'ı, B9'dan 30'u döndürecektir. Ancak, D9 ve D10 hücrelerinden iki XLOOKUP işlevini alıp aradaki iki nokta üst üste ile bir araya getirirseniz, XLOOKUP'un davranışı değişir. 15 döndürmek yerine, ilk XLOOKUP B6 hücre adresini döndürür!

Bunu kanıtlamak için D7'yi seçtim ve Formüller, Formülü Değerlendir'i kullandım. Değerlendir'e iki kez bastıktan sonra, burada gösterildiği gibi, hesaplanacak sonraki bölüm XLOOKUP ("Kiraz", A4: A29, B4: B29) olur.

Bu, ilk XLOOKUP'u değerlendirmeden hemen önce Formülü Değerlendir iletişim kutusunu gösterir. Bu XLOOKUP, iki nokta üst üste işaretinden hemen önce görünür.

Tekrar Değerlendir'e basın ve şaşırtıcı bir şekilde, XLOOKUP formülü, B6'da depolanan 15 yerine $ B $ 6 döndürür. Bunun nedeni, bu XLOOKUP formülünün hemen ardından iki nokta üst üste bulunmasıdır.

Değerlendir'i tıklayın ve ilk XLOOKUP 15 yerine 6 $ B $ döndürür.

İki kez daha Değerlendir'e basın ve ara formül = TOPLA (B6: B9) olacaktır.

İkinci XLOOKUP değerlendirildikten sonra, ara formül = TOPLA (B6: B9) olur.

Bu, çoğu insanın bilmediği harika bir davranış. Excel MVP Charles Williams bana, XLOOKUP öğesinin yanındaki şu üç operatörden herhangi biriyle tetiklenebileceğini söylüyor:

  • Kolon
  • Boşluk (Kesişme operatörü)
  • Virgül (Sendika operatörü)

XLOOKUP Avantajı 11: INDEX (, MATCH, MATCH) gibi iki yönlü eşleştirme

DÜŞEYARA arkadaşlarımın tümü için, INDEX / MATCH çalışanları, XLOOKUP'un iki yönlü bir eşleşmeyi kaldırıp kaldıramayacağını görmek için bekliyorlardı. Harika haber: bunu yapabilir. Kötü haber: metodoloji INDEX / MATCH hayranlarının beklediğinden biraz farklı. Kafalarının biraz üzerinde olabilir. Ama bu yönteme gelebileceklerinden eminim.

İki yönlü bir eşleşme için, J3'te gösterilen A621 hesap numarasını içeren satırın hangisi olduğunu bulmak istiyorsunuz. Dolayısıyla, XLOOKUP yeterince kolay başlar: = XLOOKUP (J3, A5: A15. Ancak daha sonra bir sonuç_dizisi sağlamanız gerekir. XLOOKUP Avantaj 9: Yukarıdaki Tek Formülde 12 Ayın Tümünü Döndür, ancak dikey vektör döndürmek için kullanın. İçteki XLOOKUP, B4: G4'teki ay başlıklarında J4 ayı arar. Return_array, B5: G15 olarak belirtilir. Sonuç, içteki XLOOKUP'un I10'da gösterilene benzer bir dizi döndürmesidir. : Aşağıdaki I20. A621, aranan_dizinin beşinci hücresinde bulunduğundan ve sonuç_dizisinin beşinci hücresinde 104 bulunduğundan, formülden doğru yanıtı alırsınız.Aşağıda, J6 eski yolu gösterir.J7 yeni yolu döndürür.

A5: A15'teki hesaplar listesinde XLookup J3. Sonuç Dizisi için, XLOOKUP (J4, B4: G4, B5: G15) kullanın. Bu formülde, B4: G4 ayların listesidir. B5: G15, tüm aylar için tüm hesaplar için dikdörtgen değerler dizisidir. Başka bir hücrede, yalnızca içteki XLOOKUP, Mayıs ayına ait değerler sütununun tamamını nasıl döndürdüğünü gösterir.

ÇAPRAZUPtan Yarar 12: Tüm arama değerlerini tek bir formülde toplayın

Eski ARA işlevi iki garip numara sunuyordu. İlk olarak, tahakkuk edecek toplam prim gideri miktarını bulmaya çalışıyorsanız, ARA işlevinden tüm değerleri tek bir formülde aramasını isteyebilirsiniz. Aşağıdaki resimde, ARA (C4: C14 11 arama yapıyor. Ancak ARA işlevi tam bir eşleşme sunmadı ve arama tablosunun sıralanmasını gerektirdi.

13 değeri arayın ve toplayın. Bu, LOOKUP ile çalışıyordu, ancak XLOOKUP ile de çalışıyordu. Tüm arama değerlerini C4: C14 ilk bağımsız değişken olarak belirtin. ÇAPRAZARA'yı bir TOPLA işlevine sarın.

XLOOKUP ile, aranan_değer ve XLOOKUP tüm yanıtları döndürecek şekilde bir aralık belirtebilirsiniz. Bunun yararı, XLOOKUP'un tam eşleşmeler yapabilmesidir.

Tüm arama sonuçlarını toplamak için ARA'yı kullanma hilesi, yalnızca Arama'nın yaklaşık eşleşme sürümüyle çalıştı. Burada, XLOOKUP, L4: L14'teki tüm adlar üzerinde tam bir eşleşme yapıyor ve tüm sonuçların toplamını alıyor.

Bonus İpucu: Twisted LOOKUP'a ne dersiniz?

Excel MVP Mike Girvin, genellikle Lookup_Vector'ün dikey ve Result_Vector'ın yatay olduğu ARA işlevinin bir hilesini gösterir. XLOOKUP bu numarayı yerel olarak desteklemeyecektir. Ancak, biraz hile yapar ve TRANSPOSE işlevinde results_array'i kaydırırsanız, çarpık bir aramayı yönetebilirsiniz.

Burada arama dizisi dikeydir ve sonuç dizisi yataydır. Eski ARA işlevi bunu halledebilir, ancak bunu XLOOKUP ile yapmak için her iki diziyi de TRANSPOSE'a sarmalısınız.

Ilginç makaleler...