Excel 2016da XLOOKUP İşlevi Nasıl Kullanılır

Windows ve Mac'teki Office 365 aboneleri için Excel 2016, artık çok popüler (ancak genellikle kötü niyetli) dikey arama işlevi olan DÜŞEYARA (DÜŞEYARA'daki X'in ne olduğunu bilmiyorum) için oldukça basit ve çok yönlü bir yedek olarak lanse edilen yeni bir DÜŞEYARA işlevini desteklemektedir. anlamına gelir; Kapsamlı, belki?).

DÜŞEYARA'ya henüz aşina olmayanlar için (TOPLA ve ORTALAMA'dan hemen sonra en çok kullanılan üçüncü işlev olarak kabul edilir), bu işlev, belirtilen bir arama tablosunun en sol sütununda yukarıdan aşağıya dikey olarak bir satırda bir değer bulana kadar arar. aradığınızla eşleşen veya onu aşan bir ofset numarasıyla belirlenen arama sütunu. Çalışma sayfanızdaki bir veri tablosunun uzun bir listesindeki veya sütunundaki belirli öğeleri bulmak için son derece yararlı olsa da, DÜŞEYARA işlevinin, XLOOKUP olarak bu yeni arama işlevi tarafından paylaşılmayan birkaç sınırlaması vardır:

• Arama aralığındaki arama değeriniz için tam eşleşmeleri bulmak için varsayılanlar
• Bir tabloda hem dikey (satıra göre) hem de yatay (sütun) arama yapabilir, böylece yatay olarak sütuna göre arama yaparken HLOOKUP işlevini kullanma gereksinimini ortadan kaldırabilir
• Fonksiyonun çalışması için, arama tablonuzdaki arama aralığının, dönüş aralığı olarak belirlenmiş olanın solundaki bir sütunda bulunması gerekmeyecek şekilde sola veya sağa arama yapabilir
• Tam eşleşme varsayılanı kullanıldığında, arama aralığındaki değerler belirli bir sırada sıralanmadığında bile çalışır
• İsteğe bağlı bir arama modu bağımsız değişkeni kullanarak arama dizisi aralığında en alt satırdan en üste arama yapabilir

DÜŞEYARA işlevi, aşağıdaki sözdizimini kullanarak ilk üçü gerekli ve son ikisi isteğe bağlı olmak üzere beş olası bağımsız değişkene sahiptir:

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

Gerekli aranan_değer bağımsız değişkeni, aradığınız değeri veya öğeyi belirtir. Gerekli look_up dizi argüman hücre aralığı bu arama değeri aranacak atar ve return_array argüman Excel tam eşleşme bulduğunda iade istediğiniz değeri içeren hücre aralığını belirler.

* XLOOKUP işlevinizde aranan_dizi ve dönüş_dizi 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ünüzde hata. Bu, bu argümanları tanımlarken onları belirtmek veya hücre referanslarını yazmak yerine, belirlenmiş bir veri tablosunun aralık adlarını veya sütun adlarını kullanmanızın nedenidir .

İ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şleşme için 0 (varsayılan, eşleşme_modu bağımsız değişkeni atanmadığındakiyle aynıdır )
• -1 tam eşleşme veya sonraki daha düşük değer için
• 1 tam eşleşme veya sonraki daha büyük değer için
• 2 , aranan_değer bağımsız değişkeninde hücre referansına birleştirilmiş joker karakterler kullanan kısmi eşleşme için

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

• 1 baştan sona, yani yukarıdan aşağıya doğru arama yapmak için (varsayılan, arama_modu bağımsız değişkeni atanmadığındakiyle aynıdır )
• -1 sondan başa, yani aşağıdan yukarıya doğru arama yapmak için
• 2 artan sırada ikili arama için
• -2 azalan sırada ikili arama için

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örmektir. Aşağıdaki şekilde, ülkeye göre düzenlenmiş basit bir 2019 satış verileri 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 DÜŞEYARA'yı kullanmak için şu adımları izleyin:

Hücre imlecini çalışma sayfasının E4 hücresine yerleştirin

Fonksiyon 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 kısmındaki DÜŞEYARA'yı tıklayın.

Hücre referansını Aranan_değer bağımsız değişkeni metin kutusuna girmek için çalışma sayfasındaki D4 hücresine tıklayın.

Lookup_array bağımsız değişkeni metin kutusunu seçmek için Sekme'ye basın, ardından A4 hücresini tıklayı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ığı bir Excel veri tablosu olarak tanımlanır, Metin kutusunda A4:A8 aralığının yerine Tablo1[Ülke] görünür.

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ıklayın ve arama 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 (bu şu şekilde görünür: Metin kutusundaki Tablo1[Toplam Satış]).

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

E4 hücresinde D4 hücresine girilen ülkeye göre satışları döndüren XLOOKUP ile formül oluşturma.

Excel, XLOOKUP formülünü çalışma sayfasının E4 hücresine girer ve sonuç olarak 4900 döndürür çünkü Kosta Rika şu anda D4 arama hücresine girilir 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 ve soldan sağa çalıştığından, belirli bir satış rakamına göre ülkeyi bu satış tablosundan döndürmek için de bu işlevi kullanabilirsiniz. Aşağıdaki şekil, bunu nasıl yapacağınızı gösterir. Bu kez, D4 hücresinde DÜŞEYARA formülünü oluşturursunuz ve E4 hücresine girilen değeri (bu durumda 11.000), aranan_değer bağımsız değişkeni olarak belirlersiniz.

Ek olarak, işlevin tam eşleme varsayılanını geçersiz kılmak için eşleşme_modu bağımsız değişkeni olarak -1 girersiniz, böylece Excel, E4 arama hücresine girilen satış değeriyle veya bir sonraki düşük toplam satışla (Meksika ile Meksika) tam eşleşmeli ülkeyi döndürür. Bu tabloda 10.000 ABD Doları toplam satışı olan bir ülke olmadığı için bu durumda 10.000 ABD Doları). Bu formül için bir eşleşme_modu bağımsız değişkeni atamadan Excel, sonuç olarak #NA döndürür, çünkü bu satış tablosunda 11.000 ABD Doları ile tam eşleşme yoktur.

D4 hücresinde XLOOKUP ile E4 hücresine girilen satışlara göre ülkeyi döndüren bir formül oluşturma

DÜŞEYARA işlevi, satıra göre dikey arama yaptığı gibi sütuna göre yatay aramada da aynı derecede rahat olduğundan, iki yönlü bir arama gerçekleştiren bir formül oluşturmak için kullanabilirsiniz (İNDİS ve KAÇINCI işlevlerini şu şekilde birleştiren bir formül oluşturma ihtiyacının yerini alır). geçmişte). Nisan-Aralık ayları için AB-100'den AB-103'e kadar parça numaraları için 2019 üretim programı tablosunu içeren aşağıdaki şekil, bunun nasıl yapıldığını gösterir.

Belirli bir ayda bir parça için üretilen birim sayısını döndürmek için iç içe XLOOKUP işlevleriyle formül oluşturma

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

=XLOOKUP(part_lookup,$A$3:$A$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 (bu durumda B10 hücresi) yapılan parça girişiyle tam eşleşme için dikey olarak satır bazında arama yapan bir XLOOKUP işlevi tanımlayarak başlar. . Ancak, bu orijinal ARA işlevi için dönüş_dizisi bağımsız değişkeninin kendisinin ikinci bir DÜŞEYARA işlevi olduğuna dikkat edin.

Bu ikinci, iç içe XLOOKUP işlevi, tarih_bak adlı hücrede (bu durumda B11 hücresi) yapılan tarih girişiyle tam eşleşme için $B$2:$J$2 hücre aralığını sütuna göre yatay olarak arar. Bu ikinci, iç içe XLOOKUP işlevi için return_array argümanı, 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 ilk olarak, bu durumda, Haziran-19 sütununun D3: D6 hücre aralığındaki diziyi döndüren yatay bir arama gerçekleştirerek ikinci, iç içe XLOOKUP işlevinin sonucunu hesaplamasıdır (değerlerle: 438, 153, 306 ve 779). Bu sonuç, sırayla, B11 hücresinde (parça_bak adlı) yapılan parça numarası girişiyle tam eşleşme için satıra göre dikey arama gerçekleştiren orijinal XLOOKUP işlevi için dönüş_dizi bağımsız değişkeni olur. Bu örnekte, bu part_lookup hücresi AB-102'yi içerdiğinden, formül ikinci, sonraki XLOOKUP işlevinin sonucundan yalnızca Haziran-19 üretim değeri olan 306'yı döndürür.

İşte aldın! Yalnızca DÜŞEYARA ve YATAYARA işlevleri tarafından gerçekleştirilen tek değerli aramaları değil, aynı zamanda İNDEKS ve KAÇINCI işlevleri de.

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