İçerik
- Excel'de birden çok kritere göre arama yapın
- DÜŞEYARA kullanarak 2., 3. vb değerleri çıkarıyoruz
- İstenen değerin tüm tekrarlarını alın
- Bilinen satır ve sütuna göre XNUMXD arama
- Tek formülde birden çok DÜŞEYARA kullanma
- DÜŞEYARA ve DOLAYLI kullanarak farklı tablolardan verilerin dinamik olarak değiştirilmesi
İşlev hakkındaki eğitimimizin ikinci bölümünde VPR (DÜŞEYARA) Excel'de, tüm gücü yönlendirmenize yardımcı olacak birkaç örneği analiz edeceğiz VPR en iddialı Excel görevlerini çözmek için. Örnekler, bu özelliğin nasıl çalıştığına dair temel bilgilere sahip olduğunuzu varsaymaktadır. Değilse, sözdizimi ve temel kullanımı açıklayan bu öğreticinin ilk bölümünden başlamak isteyebilirsiniz. VPR. Pekala, başlayalım.
Excel'de birden çok kritere göre arama yapın
işlev VPR Excel'de, bir veritabanında belirli bir değer için arama yapmak için gerçekten güçlü bir araçtır. Ancak, önemli bir sınırlama vardır – sözdizimi yalnızca tek bir değer aramanıza izin verir. Birden çok koşula göre arama yapmak isterseniz ne olur? Çözümü aşağıda bulacaksınız.
Örnek 1: 2 farklı kritere göre arama yapın
Diyelim ki bir sipariş listemiz var ve bulmak istiyoruz. Mal miktarı (Adet), iki kritere göre – Müşteri Adı (Müşteri) è ürün adı (Ürün). Konu, aşağıdaki tablodan da görülebileceği gibi, alıcıların her birinin birkaç tür mal sipariş etmesi gerçeğiyle karmaşıktır:
normal fonksiyon VPR verilen arama değeriyle eşleşen bulduğu ilk değeri döndüreceğinden bu senaryoda çalışmayacaktır. Örneğin, bir öğenin miktarını bilmek istiyorsanız tatlılaralıcı tarafından sipariş edildi Jeremy Tepesi, aşağıdaki formülü yazın:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
- bu formül sonucu döndürür 15ürüne karşılık gelen Elma, çünkü eşleşen ilk değer budur.
Basit bir geçici çözüm var - istenen tüm kriterleri birleştirebileceğiniz ek bir sütun oluşturun. Örneğimizde, bunlar sütunlardır. Müşteri Adı (Müşteri) è ürün adı (Ürün). Birleştirilmiş sütunun her zaman arama aralığındaki en soldaki sütun olması gerektiğini unutmayın, çünkü işlev en soldaki sütundur. VPR bir değer ararken yukarı bakar.
Böylece tabloya bir yardımcı sütun eklersiniz ve aşağıdaki formülü tüm hücrelerine kopyalarsınız: =B2&C2. Dizenin daha okunaklı olmasını istiyorsanız, birleştirilen değerleri bir boşlukla ayırabilirsiniz: =B2&» «&C2. Bundan sonra, aşağıdaki formülü kullanabilirsiniz:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
hücre nerede B1 bağımsız değişkenin birleştirilmiş değerini içerir lookup_value (arama_değeri) ve 4 - Argüman sütun_indis_sayısı (sütun_numarası), yani alınacak verileri içeren sütunun numarası.
Örnek 2: Tablonun başka bir sayfada görüntülendiği iki kritere göre DÜŞEYARA
Başka bir sayfada veya başka bir Excel çalışma kitabında bulunan ikinci tablodan (Arama tablosu) veri ekleyerek ana tabloyu (Ana tablo) güncellemeniz gerekiyorsa, istediğiniz değeri doğrudan eklediğiniz formülde toplayabilirsiniz. ana masaya.
Önceki örnekte olduğu gibi, Arama tablosunda birleştirilmiş değerlere sahip bir yardımcı sütuna ihtiyacınız olacak. Bu sütun, arama aralığındaki en soldaki sütun olmalıdır.
Yani formül ile VPR şöyle olabilir:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Burada B ve C sütunları sırasıyla müşteri adlarını ve ürün adlarını içerir ve bağlantı Siparişler!$A&$2:$D$2 başka bir sayfada aranacak bir tablo tanımlar.
Formülü daha okunabilir hale getirmek için görünüm aralığına bir ad verebilirsiniz ve ardından formül çok daha basit görünecektir:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Formülün işe yaraması için baktığınız tablonun en sol sütunundaki değerlerin tam olarak arama kriterlerinde olduğu gibi birleştirilmesi gerekir. Yukarıdaki şekilde, fonksiyonun ilk argümanında (B2& “” & C2) yapmanız gereken şekilde, uXNUMXbuXNUMXband değerlerini birleştirdik, aralarına boşluk koyduk.
Hatırlamak! işlev VPR 255 karakterle sınırlı, 255 karakterden uzun bir değeri arayamaz. Bunu aklınızda bulundurun ve istenen değerin uzunluğunun bu sınırı aşmadığından emin olun.
Yardımcı sütun eklemenin en zarif ve her zaman kabul edilebilir bir çözüm olmadığına katılıyorum. Aynısını yardımcı sütun olmadan da yapabilirsiniz, ancak bu, bir işlev kombinasyonuyla çok daha karmaşık bir formül gerektirir. INDEX (INDEX) ve MAÇ (DAHA FAZLA AÇILMIŞTIR).
DÜŞEYARA kullanarak 2., 3. vb değerleri çıkarıyoruz
Sen bunu zaten biliyorsun VPR yalnızca bir eşleşen değeri, daha doğrusu bulunan ilk değeri döndürebilir. Peki ya bu değer görüntülenen dizide birkaç kez tekrarlanırsa ve siz bunların 2. veya 3.'ünü çıkarmak istiyorsanız? Peki ya tüm değerler? Sorun karmaşık görünüyor, ancak çözüm var!
Tablonun bir sütununun müşterilerin adlarını (Müşteri Adı) ve diğer sütunun satın aldıkları ürünleri (Ürün) içerdiğini varsayalım. Belirli bir müşteri tarafından satın alınan 2., 3. ve 4. ürünleri bulmaya çalışalım.
En kolay yol, sütundan önce bir yardımcı sütun eklemektir. müşteri adı ve her adın tekrar numarasıyla müşteri adlarıyla doldurun, örneğin, John Doe1, John Doe2 vb. Fonksiyonu kullanarak numaralandırma ile hile yapacağız. COUNTIF (EĞERSAY), müşteri adlarının B sütununda olması koşuluyla:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Bundan sonra normal işlevi kullanabilirsiniz. VPRgerekli sırayı bulmak için Örneğin:
- bulmak 2-inci müşteri tarafından sipariş edilen ürün Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - bulmak 3-inci müşteri tarafından sipariş edilen ürün Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Aslında, aşağıdaki şekilde gösterildiği gibi, metin yerine arama değeri olarak bir hücre referansı girebilirsiniz:
Eğer sadece arıyorsanız 2-e tekrarlama, daha karmaşık bir formül oluşturarak yardımcı sütun olmadan yapabilirsiniz:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
Bu formülde:
- 2 F $ - alıcının adını içeren bir hücre (değişmez, lütfen unutmayın - bağlantı mutlaktır);
- $B $ - kolon müşteri adı;
- Table4 – Masanız (burası normal bir aralık da olabilir);
- C16 $ – tablonuzun veya aralığınızın son hücresi.
Bu formül yalnızca ikinci eşleşen değeri bulur. Kalan tekrarları çıkarmanız gerekirse, önceki çözümü kullanın.
Tüm eşleşmelerin bir listesine ihtiyacınız varsa – işlev VPR bu bir yardımcı değildir, çünkü her seferinde yalnızca bir değer döndürür – periyot. Ama Excel'in bir işlevi var INDEX (INDEX), bu görevle kolayca başa çıkabilir. Böyle bir formülün nasıl görüneceğini aşağıdaki örnekte öğreneceksiniz.
İstenen değerin tüm tekrarlarını alın
Yukarıda da belirtildiği gibi VPR taranan aralıktan tüm yinelenen değerleri çıkaramaz. Bunu yapmak için, birkaç Excel işlevinden oluşan biraz daha karmaşık bir formüle ihtiyacınız var. INDEX (İNDEKS), KÜÇÜK (KÜÇÜK) ve SIRA (ASTAR)
Örneğin, aşağıdaki formül, B2:B2 aralığındaki F16 hücresindeki değerin tüm tekrarlarını bulur ve C sütunundaki aynı satırların sonucunu döndürür.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Bu dizi formülünü hücreler gibi birden çok bitişik hücreye girin F4: F8aşağıdaki şekilde gösterildiği gibi. Hücre sayısı, aranan değerin mümkün olan maksimum tekrar sayısına eşit veya daha fazla olmalıdır. tıklamayı unutmayın Ctrl + Üst Karakter + Enterdizi formülünü doğru girmek için.
Nasıl çalıştığını anlamakla ilgileniyorsanız, formülün ayrıntılarına biraz dalalım:
Bölüm 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – F2 hücresindeki değeri, B2:B16 aralığındaki her bir değerle karşılaştırın. Bir eşleşme bulunursa, ifade DİZİ(C2:C16)-1 karşılık gelen satırın numarasını döndürür (değer -1 başlık satırını dahil etmemenizi sağlar). Eşleşme yoksa, işlev IF (IF) boş bir dize döndürür.
fonksiyon sonucu IF (IF) böyle bir yatay dizi olacak: {1,"",3,"",5,"","","","","","",12,"","",""}
Bölüm 2:
ROW()-3
СТРОКА()-3
Buradaki fonksiyon SIRA (LINE) ek bir sayaç görevi görür. Formül F4:F9 hücrelerine kopyalandığından, sayıyı çıkarırız 3 değer almak için fonksiyon sonucundan 1 hücrede F4 (satır 4, çıkarma 3) almak için 2 hücrede F5 (satır 5, çıkarma 3) vb.
Bölüm 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
işlev KÜÇÜK (KÜÇÜK) döner yok veri dizisindeki en küçük değer. Bizim durumumuzda, hangi pozisyonun (en küçüğünden) döndürüleceği fonksiyon tarafından belirlenir. SIRA (LINE) (bkz. Bölüm 2). Yani bir hücre için F4 işlev KÜÇÜK({dizi},1) İade 1-inci (en küçük) dizi öğesi, yani 1. hücre için F5 İade 2-inci dizideki en küçük eleman, yani 3, vb.
Bölüm 4:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
işlev INDEX (INDEX) sadece bir dizideki belirli bir hücrenin değerini döndürür C2:C16. hücre için F4 işlev İNDEKS($C$2:$C$16) geri dönücek Elmaİçin F5 işlev İNDEKS($C$2:$C$16) geri dönücek tatlılar ve benzerleri.
Bölüm 5:
IFERROR()
ЕСЛИОШИБКА()
Son olarak, formülü fonksiyonun içine koyuyoruz. EĞERHATA (IFERROR), çünkü hata mesajından memnun kalmayacaksınız #NS (#N/A) formülün içine kopyalandığı hücre sayısı, görüntülenen aralıktaki yinelenen değerlerin sayısından azsa.
Bilinen satır ve sütuna göre XNUMXD arama
Excel'de XNUMXD arama yapmak, bilinen bir satır ve sütun numarasına göre bir değer aramayı içerir. Başka bir deyişle, belirli bir satır ve sütunun kesişim noktasındaki hücre değerini çıkarıyorsunuz.
O halde tablomuza dönelim ve fonksiyonu olan bir formül yazalım. VPR, Mart ayında satılan limonların maliyeti hakkında bilgi bulacaksınız.
XNUMXD arama yapmanın birkaç yolu vardır. Seçeneklere göz atın ve size en uygun olanı seçin.
DÜŞEYARA ve KAÇINCI işlevleri
Bir sürü işlevi kullanabilirsiniz VPR (DÜŞEYARA) ve DAHA MARUZ KALMIŞ (MATCH) alanların kesişimindeki değeri bulmak için ürün adı (dize) ve Ay (sütun) söz konusu dizinin:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
Yukarıdaki formül normal bir fonksiyondur VPRA2 ile A9 arasındaki hücrelerde "Limonlar" değerinin tam eşleşmesini arayan . Ancak Mart satışlarının hangi sütunda olduğunu bilmediğiniz için üçüncü işlev argümanı için sütun numarasını belirleyemezsiniz. VPR. Bunun yerine, işlev kullanılır DAHA MARUZ KALMIŞBu sütunu tanımlamak için
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
İnsan diline çevrilen bu formül şu anlama gelir:
- “Mar” karakterlerini arıyoruz – argüman lookup_value (arama_değeri);
- A1'den I1'e kadar olan hücrelere bakma – argüman arama_dizisi (arama_dizisi);
- Tam eşleşme döndürme – bağımsız değişken eşleşme türü (eşleşme türü).
kullanma 0 üçüncü argümanda, işlevler diyorsunuz DAHA MARUZ KALMIŞ aradığınız değerle tam olarak eşleşen ilk değeri arayın. Bu değere eşdeğerdir YANLIŞ (YANLIŞ) dördüncü argüman için VPR.
Excel'de iki boyutlu arama veya çift yönlü arama olarak da bilinen iki yönlü bir arama formülü bu şekilde oluşturabilirsiniz.
SUMPRODUCT işlevi
işlev SUMPRODUCT (SUMPRODUCT), seçili dizilerin çarpımlarının toplamını döndürür:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
İNDEKS ve KAÇINCI işlevleri
Bir sonraki makalede bu işlevleri ayrıntılı olarak açıklayacağım, şimdilik bu formülü kopyalayabilirsiniz:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Adlandırılmış aralıklar ve kesişim operatörü
Tüm bu karmaşık Excel formülleriyle ilgilenmiyorsanız, bu görsel ve akılda kalıcı yolu beğenebilirsiniz:
- Tabloyu seçin, sekmeyi açın Formüller (Formüller) ve tıklayın Seçimden Oluştur (Seçimden oluştur).
- Kutuları kontrol et Üst sıra (yukarıdaki satırda) ve Sol sütun (soldaki sütunda). Microsoft Excel, hesap tablonuzun üst satırındaki ve sol sütunundaki değerlerden aralıklara adlar atayacaktır. Artık formül oluşturmadan doğrudan bu adları kullanarak arama yapabilirsiniz.
- Herhangi bir boş hücreye yazın =satır_adı sütun_adı, örneğin şöyle:
=Limon Mar
… ya da tam tersi:
=Mar Limonlar
Satır ve sütun adlarının, bu durumda kesişim operatörü gibi çalışan bir boşlukla ayrılması gerektiğini unutmayın.
Bir ad girdiğinizde, Microsoft Excel, tıpkı bir formül girerken olduğu gibi, eşleşen adların listesini içeren bir araç ipucu gösterecektir.
- Basın Keşfet ve sonucu kontrol et
Genel olarak, yukarıdaki yöntemlerden hangisini seçerseniz seçin, iki boyutlu bir aramanın sonucu aynı olacaktır:
Tek formülde birden çok DÜŞEYARA kullanma
Ana tablo ve arama tablosunun ortak tek bir sütunu olmadığı ve bu, normal işlevi kullanmanızı engeller. VPR. Ancak ilgilendiğimiz bilgileri içermeyen ancak ana tablo ve arama tablosu ile ortak bir sütunu olan başka bir tablo daha var.
Aşağıdaki örneğe bir göz atalım. Sütunlu bir Ana tablomuz var SKU (yeni), başka bir tablodan karşılık gelen fiyatları içeren bir sütun eklemek istediğiniz yere. Ayrıca 2 tane arama tablomuz var. İlki (Arama tablosu 1) güncellenmiş numaraları içerir SKU (yeni) ve ürün adları ve ikincisi (Arama tablosu 2) – ürün adları ve eski numaralar SKU (eski).
İkinci arama tablosundan ana tabloya fiyat eklemek için double olarak bilinen bir işlem gerçekleştirmelisiniz. VPR veya iç içe VPR.
- Bir fonksiyon yaz VPR, ürün adını tabloda bulan Arama tablosu 1kullanma SKU, istenen değer olarak:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)İşte Yeni_SKU - adlandırılmış aralık $A:$B masada Arama tablosu 1, 2 - bu, malların adlarını içeren B sütunudur (yukarıdaki resme bakın)
- Tablodan fiyat eklemek için bir formül yazın Arama tablosu 2 iyi bilinen ürün adlarına dayanmaktadır. Bunu yapmak için, daha önce oluşturduğunuz formülü yeni işlevin arama değeri olarak yapıştırın. VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)İşte Ücret - adlandırılmış aralık A $: C $ masada Arama tablosu 2, 3 fiyatları içeren sütun C'dir.
Aşağıdaki şekil, oluşturduğumuz formülün döndürdüğü sonucu göstermektedir:
DÜŞEYARA ve DOLAYLI kullanarak farklı tablolardan verilerin dinamik olarak değiştirilmesi
Öncelikle, birbirimizi doğru anladığımızdan emin olmak için “Farklı tablolardan verilerin dinamik olarak değiştirilmesi” ifadesiyle ne demek istediğimizi açıklayalım.
Aynı formatta verilere sahip birkaç sayfanın olduğu durumlar vardır ve belirli bir hücreye girilen değere bağlı olarak gerekli bilgileri belirli bir sayfadan çıkarmak gerekir. Bunu bir örnekle açıklamak daha kolay sanırım.
Aynı ürünlerle ve aynı formatta birkaç bölge için satış raporlarınız olduğunu düşünün. Belirli bir bölge için satış rakamlarını bulmak istiyorsunuz:
Bu tür yalnızca iki raporunuz varsa, işlevlerle utanç verici derecede basit bir formül kullanabilirsiniz. VPR и IF (IF) aranacak istenen raporu seçmek için:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Nerede:
- $ G $ 2 ürünün adını içeren bir hücredir. Formülü diğer hücrelere kopyalarken arama değerini değiştirmekten kaçınmak için burada mutlak referanslar kullandığımızı unutmayın.
- $ D3 bölgenin adını taşıyan bir hücredir. Formülü aynı sütundaki diğer hücrelere kopyalamayı planladığımız için mutlak sütun başvurusu ve göreli satır başvurusu kullanıyoruz.
- FL_Sales и CA_Satış – ilgili satış raporlarını içeren tabloların (veya adlandırılmış aralıkların) adları. Elbette normal sayfa adlarını ve hücre aralığı referanslarını kullanabilirsiniz, örneğin 'FL Sayfası'!$A$3:$B$10, ancak adlandırılmış aralıklar çok daha uygundur.
Ancak, bu tür birçok tablo olduğunda, işlev IF en iyi çözüm değildir. Bunun yerine, işlevi kullanabilirsiniz. DOLAYLI (DOLAYLI) istenen arama aralığını döndürmek için.
Muhtemelen bildiğiniz gibi, fonksiyon DOLAYLI bir metin dizesi tarafından verilen bir bağlantıyı döndürmek için kullanılır, şu anda tam olarak ihtiyacımız olan şey budur. Bu nedenle, yukarıdaki formülde ifadeyi cesurca işlevle değiştirin IF fonksiyon ile bağlantı kurmak DOLAYLI. İşte bir kombinasyon VPR и DOLAYLI ile harika çalışıyor:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Nerede:
- $ G $ 2 – bu, ürünün adına sahip bir hücredir, mutlak bağlantı nedeniyle değişmez.
- $ D3 bölge adının ilk bölümünü içeren hücredir. Örneğimizde bu FL.
- _Satış – tüm adlandırılmış aralıkların veya tabloların adının ortak kısmı. D3 hücresindeki değerle birleştirildiğinde, gerekli aralığın tam adını oluşturur. Fonksiyona yeni başlayanlar için bazı detaylar aşağıdadır. DOLAYLI.
DOLAYLI ve DÜŞEYARA nasıl çalışır?
Öncelikle fonksiyonun söz dizimini hatırlatayım. DOLAYLI (DOLAYLI):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
İlk argüman bir hücre başvurusu (A1 veya R1C1 stili), bir aralık adı veya bir metin dizesi olabilir. İkinci argüman, ilk argümanda hangi bağlantı stilinin bulunduğunu belirler:
- A1argüman ise DOĞRU KOD (DOĞRU) veya belirtilmemiş;
- R1C1, Eğer FE OLARAK (YANLIŞ).
Bizim durumumuzda, bağlantının stili var A1, böylece ikinci argümanı dışarıda bırakıp birincisine odaklanabilirsiniz.
O halde satış raporlarımıza geri dönelim. Hatırlarsanız, her rapor ayrı bir sayfada bulunan ayrı bir tablodur. Formülün doğru çalışması için tablolarınızı (veya aralıklarınızı) adlandırmanız ve tüm adların ortak bir parçası olması gerekir. Örneğin, bunun gibi: CA_Satış, FL_Satış, TX_Satış ve benzeri. Gördüğünüz gibi, tüm isimlerde “_Sales” var.
işlev DOLAYLI D sütunundaki değeri ve “_Sales” metin dizesini birbirine bağlar, böylece VPR hangi tabloda aranacak. D3 hücresi "FL" değerini içeriyorsa, formül tabloyu arayacaktır. FL_Satış, eğer “CA” ise – tabloda CA_Satış ve benzerleri.
Fonksiyonların sonucu VPR и DOLAYLI aşağıdakiler olacaktır:
Veriler farklı Excel kitaplarında bulunuyorsa, kitabın adını adlandırılmış aralıktan önce eklemeniz gerekir, örneğin:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Eğer işlev DOLAYLI başka bir çalışma kitabına atıfta bulunur, bu çalışma kitabının açık olması gerekir. Kapatılırsa, işlev bir hata bildirir. # BAŞV! (#SSYL!).