Excel'de VLOOKUP ve HLOOKUP ile Tek Bir Değer Arar - mankenler

Excel 2013'teki arama işlevlerinin en popülerleri HLOOKUP (Yatay Arama için) ve VLOOKUP (Dikey Arama için) işlevleridir. Bu işlevler Şablonun Formüller sekmesindeki Arama ve Başvuru açılır menüsünün yanı sıra İşlev Ekle iletişim kutusundaki Arama ve Başvuru kategorisinde bulunur.

Bunlar, veri tablolarında onları arayarak değerleri döndürebilecek güçlü bir fonksiyon grubunun bir parçasıdır.

VLOOKUP işlevi, bir Lookup tablosunun en soldaki sütununu, program aramakta olduğunuz bir karakterle eşleşen veya aşan bir değeri bulana kadar dikey olarak (alttan üste) arar. HLOOKUP işlevi, aradıklarınızı eşleştiren veya aşan bir değeri bulana kadar, Bir Arama tablosunun en üst satırını yatay olarak (soldan sağa) arar.

VLOOKUP işlevi aşağıdaki sözdizimini kullanır:

HLOOKUP işlevi hemen hemen aynı sözdizimini izler:

HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup]) > Her iki işlevde lookup_value bağımsız değişkeni, Arama tablosunda aramak istediğiniz değerdir ve table_array

, her ikisini de içeren Arama tablosunun hücre aralığı veya adıdır. bakmak için değer ve geri dönmek için ilgili değer.

VLOOKUP işlevindeki col_index_num bağımsız değişkeni, değerleri dikey bir tablodaki lookup_value argümanıyla karşılaştırılan sütunun numarasıdır. HLOOKUP işlevindeki row_index_num bağımsız değişkeni değerleri, yatay bir tabloda lookup_value

ile karşılaştırılan satırın numarasıdır. VLOOKUP ve HLOOKUP işlevlerinde col_index_num veya row_index_num

bağımsız değişkenlerini girerken, sütunların toplam satır sayısını veya satır sayısını aşmayan sıfırdan büyük bir değer girmelisiniz. Arama tablosu. Hem VLOOKUP hem de HLOOKUP işlevlerindeki isteğe bağlı range_lookup bağımsız değişkeni, Excel'in lookup_value

için tam veya yaklaşık eşleşme bulmasını isteyip istemediğinizi belirten mantıksal TRUE veya FALSE'dir. table_array. VUROOKUP veya HLOOKUP işlevinde TRUE belirttiğinizde veya range_lookup bağımsız değişkenini atlarsanız, Excel yaklaşık bir eşleşme bulur. range_lookup

bağımsız değişkeni olarak FALSE belirttiğinizde Excel, yalnızca tam eşlemeleri bulur. Yaklaşık eşlemelerin bulunması yalnızca, dikey veya yatay Arama Tablosunun ilk sütununda veya satırında sayısal girdileri ararken (metin yerine) arandığında geçerlidir.Excel, bu Arama sütununda veya satırda tam bir eşleşme bulamazsa, arama-değeri bağımsız değişkenini aşmayan ve bir sonraki en yüksek değeri bulur ve değeri, < sütun_indis_sayısı veya satır_indis_sayısı argümanlar.

VLOOKUP ve HLOOKUP işlevlerini kullanırken, Arama sütunundaki veya satırdaki (yani, bir Dikey Arama tablosunun en sol sütununda veya yatay Bir Arama tablosunun üst sırasındaki) metin veya sayısal girdiler benzersiz olmalıdır. Bu kayıtlar ayrıca artan düzende düzenlenmeli veya sıralanmalıdır; yani, metin girişleri için alfabetik sıralama ve sayısal girdiler için en düşükten en üst düzeye.

Şekil, çekin vergi öncesi toplamına bağlı olarak VLOOKUP işlevini bir uç tablosundan% 15 veya% 20 ipucu döndürmek için kullanmanın bir örneğini göstermektedir. Hücre F3, VLOOKUP işlevini içerir:

= VLOOKUP (Pretax_Total, Tip_Table, IF (Tip_Percentage = 0. 15, 2, 3))

Bu formül, F1 hücresindeki uç yüzdesine dayalı uç miktarı ve Hücrenin F2'sindeki kontrolün ön vergi tutarı.

Bu ipucu tablosunu kullanmak için F1 hücresine (Tip_Percentage olarak adlandırılır) ipucunun yüzdesini (% 15 veya% 20) ve F2 hücresindeki vergi öncesi tutarı (Pretax_Total olarak adlandırılır) girin. Excel, daha sonra A2: C101 hücre aralığını içeren ve Tip_Table adlı Lookup tablosunun ilk sütunundaki Pretax_Total hücresine girdiğiniz değeri arar.

Excel, ardından bir eşleşme bulana kadar İpucu Tablosunun ilk sütunundaki değerleri aşağıya taşır; bunun üzerine program, VLOOKUP işlevinde col_index_num argümanını kullanır ve bu satırın tablonun o satırından F3 hücresine geri dön.

Excel, Pretax_Total hücresine girilen değerin (bu örnekte 16,50 dolar) Tip_Table'ın ilk sütunundaki değerlerden biriyle tam olarak eşleşmediğini tespit ederse, program karşılaştıkça karşılaştırma aralığını aramaya devam eder Bu oran, vergi öncesi toplamı aşan ilk değerdir (bu örnekte, A19 hücresinde 00.00).

Excel daha sonra tablodaki önceki satıra geri taşır ve VLOOKUP işlevinin col_index_num bağımsız değişkeniyle eşleşen değeri döndürür. (Bunun nedeni, isteğe bağlı range_lookup bağımsız değişkeni işlevden çıkarılmış olmasıdır.)

İpucu tablosundaki örnek, VLOOKUP işlevinin col_index_num bağımsız değişkenini belirlemek için bir IF işlevi kullandığını unutmayın F3 hücresinde.

IF işlevi Tip_Percentage'a (F1 hücresi) girilen yüzdeyi 0 ile eşleştirerek uç tablosunda kullanılacak sütun numarasını belirler. Eşleşirlerse, col_index_num olarak 2'yi döndürür, bağımsız değişkeni ve VLOOKUP işlevi Tip_Table aralığındaki ikinci sütundan (% 15 sütun B) bir değer döndürür.

Aksi halde IF işlevi, col_index_num bağımsız değişkeni olarak 3 döndürür ve VLOOKUP işlevi Tip_Table aralığındaki üçüncü sütundan (% 20 sütun C) bir değer döndürür.

Aşağıdaki resim, ayrı bir Fiyat Arama tablosunda saklanan her fırın öğesinin fiyatını aramak için HLOOKUP işlevini kullanan bir örneği ve daha sonra bu fiyatı Günlük Satışlar listesinin Fiyat / Doz sütununa geri döndürmek için gösterir.Hücre F3, daha sonra sütun F'ye kopyalanan HLOOKUP işleviyle orijinal formülü içerir:

= HLOOKUP (madde, Fiyat_tablosu, 2, YANLIŞ)

Bu HLOOKUP işlevinde, Öğe sütununa verilen aralık adı Öğe C3: C62 aralığında lookup_value bağımsız değişkeni ve hücre aralığı I1: M2 için verilen fiyat aralığı tablosu table_array bağımsız değişkeni olarak tanımlanır.

row_index_num bağımsız değişkeni 2'dir; çünkü Excel'in Fiyatlar Arama tablosunun ikinci satırındaki fiyatları döndürmesini istiyorsunuz ve isteğe bağlı range_lookup bağımsız değişkeni FALSE. Öğe adı Günlük Satışlar listesi, Fiyatlar Arama tablosundaki öğe adıyla tam olarak eşleşmelidir.

HLOOKUP fonksiyonunun Günlük Tablolar listesindeki her bir fırın mamulü kalemi için fiyatı girmek için Fiyat tablosu aralığını kullanması sayesinde listeden herhangi bir satış güncellenmesi çok kolay olur.

Yapmanız gereken tek şey, fiyat / Doz maliyetini bu aralıkta değiştirmek ve HLOOKUP fonksiyonu, günlük satış listesinde, satılan her yerde yeni fiyatı günceller.