1. Microsoft OfficeExcelMicrosoft 2016'da XLOOKUP İşlevini Kullanma

Greg Harvey tarafından

Windows ve Mac'teki Office 365 aboneleri için Excel 2016 artık yeni popüler bir XLOOKUP işlevini destekliyor. anlamına gelir; yoğun, belki?).

Henüz DÜŞEYARA (SUM ve ORTALAMA'dan hemen sonra en çok kullanılan üçüncü işlev olarak kabul edilen) aşina olmayanlarınız için, bu işlev, belirli bir arama tablosunun en soldaki sütununda yukarıdan aşağıya doğru bir satırda bir değer bulana kadar dikey olarak arama yapar. aradığınız sütunla eşleşen veya bu sayıyı aşan bir ofset numarası ile gösterilen arama sütunu. Çalışma sayfanızdaki bir veri tablosunun uzun bir listesinde veya sütununda belirli öğeleri bulmak için çok yararlı olsa da, DÜŞEYARA işlevinin XLOOKUP olarak bu yeni arama işlevi tarafından paylaşılmayan bazı sınırlamaları vardır:

  • Arama aralığındaki arama değeriniz için tam eşleşmeleri bulma varsayılanları Bir tabloda hem dikey (satır olarak) hem de yatay (sütuna göre) arama yapabilir, böylece sütuna göre yatay arama yaparken YÜKSELTME işlevini kullanma gereğini değiştirebilir Arama tablonuzdaki arama aralığının, işlevin çalışması için dönüş aralığı olarak belirtilen sütunun solundaki bir sütunda bulunması gerekmemesi için sola veya sağa arama yapabilir Tam eşleme varsayılanı kullanıldığında, arama aralığındaki değerler belirli bir sırada sıralanmamış olsa bile çalışır İsteğe bağlı bir arama modu bağımsız değişkeni kullanarak, arama dizisi aralığının en alt satırından en üstüne kadar arama yapabilir

XLOOKUP işlevinde, ilk üçü gerekli ve son ikisi isteğe bağlı olarak, aşağıdaki sözdizimi kullanılarak beş olası bağımsız değişken bulunur:

XLOOKUP (lookup_value arama_dizisi, return_array [match_mode], [search_mode])

Gerekli arama_değeri bağımsız değişkeni, aradığınız değeri veya öğeyi belirtir. Gereken arama dizisi bağımsız değişkeni, bu arama değeri için aranacak hücre aralığını belirtir ve return_array bağımsız değişkeni, Excel tam bir eşleşme bulduğunda döndürülmesini istediğiniz değeri içeren hücre aralığını belirtir.

* XLOOKUP işlevinizde lookup_array ve return_array bağımsız değişkenlerini belirlerken her iki aralığın da eşit uzunlukta olması gerektiğini unutmayın, aksi takdirde Excel # DEĞER! formülü hatası. Bu bağımsız değişkenleri tanımlarken veya hücre başvurularına yazmak yerine, tanımlanmış bir veri tablosunun aralık adlarını veya sütun adlarını kullanmanızın nedeni de budur.

İsteğe bağlı match_mode bağımsız değişkeni aşağıdaki dört değerden herhangi birini içerebilir:

  • Tam eşleme için 0 (varsayılan, hiçbir match_mode argümanı belirtilmediğinde olduğu gibi) Tam eşleme veya sonraki küçük değer için -1 Tam eşleşme veya bir sonraki büyük değer için 1 Lookup_value bağımsız değişkeninde hücre başvurusuna katılan joker karakterler kullanılarak kısmi eşleşme için 2

İsteğe bağlı search_mode bağımsız değişkeni aşağıdaki dört değerden herhangi birini içerebilir:

  • 1'den başlayarak sonuncuya, yani yukarıdan aşağıya arama yapmak için (varsayılan, hiçbir arama_modu argümanı belirtilmediğinde olduğu gibi) -1 ilk-son, yani aşağıdan yukarıya 2 artan sırada ikili arama için Azalan düzende ikili arama için -2

Yeni XLOOKUP işlevinin gücünü ve çok yönlülüğünü anlamanın en iyi yolu, onu bir Excel çalışma sayfasında çalışırken görmek. Aşağıdaki şekilde, ülkeye göre düzenlenmiş basit bir 2019 satış veri tablosuna sahip bir çalışma sayfam var. Çalışma sayfasının D4 hücresine girdiğiniz ülkeye göre E4 hücresindeki bu tablodan toplam satışları döndürmek için XLOOKUP'u kullanmak için şu adımları izleyin:

  1. Hücre imlecini çalışma sayfasının E4 hücresine konumlandırma İşlevler Bağımsız Değişkenleri iletişim kutusunu açmak için Formüller sekmesindeki Arama ve Referans seçeneğini ve ardından açılır menünün altındaki XLOOKUP'u tıklayın. Hücre başvurusunu Arama_değeri bağımsız değişkeni metin kutusuna girmek için çalışma sayfasındaki D4 hücresini tıklatın. Arama_dizisi bağımsız değişken metin kutusunu seçmek için Sekme tuşuna basın, ardından A4 hücresini tıklatın ve aranacak aralık olarak A4: A8'i seçmek için Ctrl-aşağı okuna basarken Shift tuşunu basılı tutun (çünkü A3: B8 aralığı Excel veri tablosu olarak tanımlanır, Metin kutusunda A4: A8 aralığı yerine Tablo1 [Ülke] görüntülenir. Return_array bağımsız değişkeni metin kutusunu seçmek için Sekme tuşuna basın, ardından B4 hücresini tıklatın ve aramanın sonuçlarına göre döndürülecek değerleri içeren aralık olarak B4: B8'i seçmek için Ctrl-aşağı okuna basarken Shift tuşunu basılı tutun. Metin kutusunda Tablo1 [Toplam Satışlar]).

E4 hücresine XLOOKUP formülünü girmek için Tamam'ı tıklatın.

XLOOKUP formül oluşturma

Excel, XLOOKUP formülünü çalışma sayfasının E4 hücresine girer ve sonuç olarak 4900 değerini döndürür, çünkü Kosta Rika şu anda D4 arama hücresine girmiştir ve 2019 satış tablosunda görebileceğiniz gibi, bu gerçekten bu ülke için yapılan toplam satıştır.

XLOOKUP, sağdan sola olduğu kadar soldan sağa da çalıştığı için, belirli bir satış rakamına dayanarak ülkeyi bu satış tablosundan döndürmek için de bu işlevi kullanabilirsiniz. Aşağıdaki şekilde bunu nasıl yaptığınız gösterilmektedir. Bu kez, D4 hücresinde XLOOKUP formülü oluşturur ve E4 hücresine (bu durumda 11.000) girilen değeri arama_değeri bağımsız değişkeni olarak belirtirsiniz.

Buna ek olarak, işlevin tam eşleşme varsayılanını geçersiz kılmak için match_mode bağımsız değişkeni olarak -1 girersiniz, böylece Excel ülkeyi E4 arama hücresine girilen satış değerine veya bir sonraki düşük toplam satışa sahip olana (Meksika ile Bu durumda 10.000 $, bu tabloda toplam satışların 11.000 $ olduğu ülke yok). Bu formül için bir match_mode bağımsız değişkeni belirtmeden Excel sonuç olarak #NA değerini döndürür, çünkü bu satış tablosunda 11.000 ABD Doları ile tam bir eşleşme yoktur.

D4'te XLOOKUP formülü

XLOOKUP işlevi, satırlara göre dikey olarak arama yaparken sütuna göre yatay olarak eşit derecede rahat arama yaptığı için, bunu iki yönlü arama gerçekleştiren bir formül oluşturmak için kullanabilirsiniz (INDEX ve MATCH işlevlerini bir araya getiren bir formül oluşturma gereksiniminin yerini alır. geçmişte). Nisan-Aralık ayları için AB-100 ila AB-103 parça numaraları için 2019 üretim çizelgesi tablosunu içeren aşağıdaki şekil, bunun nasıl yapıldığını gösterir.

iç içe XLOOKUP işlevleri

B12 hücresinde aşağıdaki formülü oluşturdum:

= XLOOKUP (part_lookup, $ A $ 3: $ 6 $, XLOOKUP (date_lookup, $ B $ 2: $ J $ 2, $ B $ 3: $ J $ 6))

Bu formül, üretim tablosunun $ A $ 3: $ A $ 6 hücre aralığında part_lookup adlı hücrede yapılan parça girişiyle (bu durumda B10 hücresi) tam olarak eşleşecek şekilde dikey olarak arama yapan bir XLOOKUP işlevi tanımlayarak başlar. . Ancak, bu orijinal LOOKUP işlevi için return_array argümanının kendisinin ikinci bir XLOOKUP işlevi olduğunu unutmayın.

Bu ikinci, iç içe XLOOKUP işlevi $ B $ 2: $ J $ 2 hücre aralığını date_lookup adlı hücrede yapılan tarih girişiyle (bu örnekte B11 hücresi) tam olarak eşleşmek için sütuna göre yatay olarak arar. Bu ikinci, iç içe XLOOKUP işlevi için return_array bağımsız değişkeni, tablodaki tüm üretim değerlerinin hücre aralığı olan $ B $ 3: $ J $ 6'dır.

Bu formülün çalışma şekli, Excel'in ikinci, iç içe XLOOKUP işlevinin sonucunu, bu durumda, Haziran-19 sütununun D3: D6 hücre aralığındaki (değerleri ile) döndüren yatay bir arama yaparak hesaplamasıdır. 438, 153, 306 ve 779). Bu sonuç, B11 hücresinde (part_lookup olarak adlandırılır) yapılan parça numarası girişiyle tam eşleşme için satır başına dikey arama yapan orijinal XLOOKUP işlevi için return_array argümanı olur. Bu örnekte, bu part_lookup hücresi AB-102 içerdiğinden, formül ikinci, sonraki XLOOKUP işlevinin sonucundan sadece 30 Haziran tarihli üretim değerini (306) döndürür.

İşte aldın! Sadece VLOOKUP ve HLOOKUP işlevleri tarafından gerçekleştirilen tek değerli aramaları değil, aynı zamanda birleştirerek gerçekleştirilen iki yönlü değer aramalarını da yapabilen güçlü, çok yönlü ve kullanımı kolay yeni bir arama işlevi olan XLOOKUP'a ilk bakış INDEX ve MATCH işlevleri de.

* Ne yazık ki, XLOOKUP işlevi, yalnızca VLOOKUP ve HLOOKUP işlevlerini destekleyen Microsoft Excel'in önceki sürümleriyle geriye dönük olarak uyumlu değildir veya Excel 2019 ve Excel Online gibi arama işlevlerinden biri olarak henüz içermeyen geçerli sürümlerle uyumlu değildir. Bu, XLOOKUP formüllerini içeren bir çalışma kitabını, bu yeni arama işlevini içermeyen Excel sürümünü kullanan iş arkadaşlarınızla veya istemcilerle paylaşırsanız, tüm bu formüller #NAME döndürür? çalışma sayfasını açtıklarında hata değerleri.