İçerik
Bu küçük öğretici, bir işlevin nasıl oluşturulacağını açıklar VPR (DÜŞEYARA) büyük/küçük harf duyarlıdır, Excel'in büyük/küçük harfe duyarlı bir şekilde arayabileceği diğer birkaç formülü gösterir ve her işlevin güçlü ve zayıf yönlerini belirtir.
Sanırım her Excel kullanıcısı, hangi işlevin dikey arama yaptığını biliyor. Bu doğru, bu bir işlev VPR. Ancak, çok az kişi bunu biliyor VPR büyük/küçük harf duyarlı değildir, yani küçük ve büyük harf karakterleri onun için aynıdır.
İşte yetersizliği gösteren hızlı bir örnek VPR kaydı tanır. Diyelim ki bir hücrede A1 "fatura" değerini ve hücreyi içerir A2 – “Fatura”, formül:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… bu değer listede ilk sırada yer aldığı için “fatura” aramasını durduracak ve değeri hücreden çıkaracaktır. B1.
Bu makalenin ilerleyen bölümlerinde size nasıl yapılacağını göstereceğim VPR harfe duyarlı. Ek olarak, Excel'de büyük/küçük harfe duyarlı aramalar yapabilen birkaç işlev daha öğreneceğiz.
En basitinden başlayacağız – VIEW (ARAMA) ve SUMPRODUCT (SUMPRODUCT), ne yazık ki birkaç önemli sınırlamaya sahiptir. Ardından, biraz daha karmaşık formüle daha yakından bakacağız. İNDEKS+KAÇINCI (INDEX+MATCH) her durumda ve her veri kümesiyle kusursuz çalışır.
DÜŞEYARA işlevi büyük/küçük harfe duyarlıdır
Bildiğiniz gibi, olağan işlev VPR büyük/küçük harfe duyarsızdır. Ancak, büyük/küçük harfe duyarlı hale getirmenin bir yolu var. Bunu yapmak için aşağıdaki örnekte gösterildiği gibi tabloya yardımcı bir sütun eklemeniz gerekir.
Bir sütunda varsayalım B ürün tanımlayıcıları (Öğe) var ve ürünün fiyatını ve ilgili yorumu sütunlardan çıkarmak istiyorsunuz C и D. Sorun, tanımlayıcıların hem küçük hem de büyük harf karakterleri içermesidir. Örneğin, hücre değerleri B4 (001Tvci3u) ve B5 (001Tvci3U) yalnızca son karakter durumunda farklılık gösterir, u и U respectivamente.
Tahmin edebileceğiniz gibi, olağan arama formülü
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
geri dönücek $90, değer olduğundan 001Tvci3u daha önce arama aralığında 001Tvci3U. Ama ihtiyacımız olan bu değil, değil mi?
Bir fonksiyonla arama yapmak için VPR Excel büyük/küçük harf duyarlılığında, bir yardımcı sütun eklemeniz ve hücrelerini aşağıdaki formülle doldurmanız gerekir (burada B, arama sütunudur):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Bu formül, istenen değeri ayrı karakterlere ayırır, her karakteri kendi koduyla değiştirir (örneğin, A bunun yerine 65 yaşında a kod 97) ve ardından bu kodları benzersiz bir sayı dizisi halinde birleştirir.
Bundan sonra basit bir fonksiyon kullanıyoruz VPR büyük/küçük harfe duyarlı arama için:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Fonksiyonun doğru çalışması VPR büyük/küçük harfe duyarlı iki faktöre bağlıdır:
- Yardımcı sütun, görüntülenebilir aralıktaki en soldaki sütun olmalıdır.
- Aradığınız değer, gerçek değer yerine bir karakter kodu içermelidir.
KOD işlevi nasıl doğru şekilde kullanılır?
Yardımcı sütunun hücrelerine eklenen formül, tüm arama değerlerinizin aynı sayıda karaktere sahip olduğunu varsayar. Değilse, en küçük ve en büyük sayıları bilmeniz ve mümkün olduğunca çok özellik eklemeniz gerekir. EĞERHATA (EĞERHATA) En kısa ve en uzun aranan değer arasındaki fark kaç karakterdir.
Örneğin, en kısa arama değeri 3 karakter ve en uzunu 5 karakter ise şu formülü kullanın:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
fonksiyon için PSTR (MID) Aşağıdaki argümanları sağlarsınız:
- 1. argüman – metin (metin) ayıklanacak karakterleri içeren metin veya hücre başvurusudur (bizim durumumuzda B2'dir).
- 2. argüman – başlangıç_sayısı (start_position) ayıklanacak bu karakterlerden ilkinin konumudur. giriş yaptın 1 ilk fonksiyonda PSTR, 2 – ikinci fonksiyonda PSTR vb.
- 3. argüman – sayı_karakterler (number_of_characters) – Metinden çıkarılacak karakter sayısını belirtir. Her zaman sadece 1 karaktere ihtiyacımız olduğu için yazdığımız tüm fonksiyonlarda 1.
Sınırlamalar: işlev VPR Excel'de büyük/küçük harfe duyarlı aramalar için en iyi çözüm değildir. İlk olarak, bir yardımcı sütunun eklenmesi gerekir. İkincisi, formül yalnızca veriler homojense veya aranan değerlerdeki tam karakter sayısı biliniyorsa iyi bir iş çıkarır. Durumunuz bu değilse, aşağıda gösterdiğimiz çözümlerden birini kullanmak daha iyidir.
Büyük/küçük harf duyarlı arama için ARA işlevi
işlev VIEW (ARA) ilgili VPR, ancak sözdizimi, yardımcı sütun eklemeden büyük/küçük harfe duyarlı aramalara izin verir. Bunu yapmak için kullanın VIEW fonksiyon ile birleştirilmiş TAM (BİRE BİR AYNI).
Verileri önceki örnekten alırsak (yardımcı sütun olmadan), aşağıdaki formül görevle başa çıkacaktır:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Aralıkta formül aramaları A2: A7 hücre değeriyle tam eşleşme F2 büyük/küçük harfe duyarlıdır ve aynı satırın B sütunundaki değeri döndürür.
Facebok sayfasını beğenin : VPRişlev VIEW aşağıdaki ekran görüntüsünde görebileceğiniz gibi, metin ve sayısal değerlerle eşit şekilde çalışır:
Önemli! Fonksiyon için VIEW doğru çalıştı, arama sütunundaki değerler artan düzende, yani küçükten büyüğe sıralanmalıdır.
Fonksiyonun nasıl çalıştığını kısaca anlatayım TAM kilit nokta olduğu için yukarıda gösterilen formülde.
işlev TAM 1. ve 2. bağımsız değişkenlerdeki iki metin değerini karşılaştırır ve tam olarak aynıysa DOĞRU, değilse YANLIŞ döndürür. fonksiyonun olması bizim için önemlidir. TAM harfe duyarlı.
Formülümüzün nasıl çalıştığını görelim GÖRÜNTÜLE+TAM:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- işlev TAM hücre değerini karşılaştırır F2 bir sütundaki tüm öğelerle A (A2:A7). Tam bir eşleşme bulunursa DOĞRU, aksi takdirde YANLIŞ döndürür.
- İlk fonksiyon argümanını verdiğinizden beri VIEW değer DOĞRU, belirtilen sütundan (bizim durumumuzda, B sütunu) karşılık gelen değeri yalnızca tam eşleşme bulunursa, büyük/küçük harfe duyarlı olarak çıkarır.
Umarım bu açıklama açık olmuştur ve şimdi ana fikri anlamışsınızdır. Eğer öyleyse, daha sonra analiz edeceğimiz diğer fonksiyonlarla ilgili herhangi bir zorluk yaşamayacaksınız, çünkü. hepsi aynı prensipte çalışır.
Sınırlamalar: Arama sütunundaki veriler artan düzende sıralanmalıdır.
SUMPRODUCT – büyük/küçük harf duyarlı metin değerlerini bulur, ancak yalnızca sayıları döndürür
Başlıktan da anladığınız gibi, SUMPRODUCT (SUMPRODUCT), büyük/küçük harf duyarlı bir arama yapmanıza yardımcı olacak, ancak yalnızca sayısal değerler döndürecek başka bir Excel işlevidir. Bu seçenek size uymuyorsa, hemen pakete geçebilirsiniz. İNDEKS+KAÇINCI, herhangi bir durum ve herhangi bir veri türü için bir çözüm sunar.
Öncelikle, bu işlevin sözdizimini kısaca açıklamama izin verin, bu, aşağıdaki büyük/küçük harfe duyarlı formülü daha iyi anlamanıza yardımcı olacaktır.
işlev SUMPRODUCT verilen dizilerin öğelerini çarpar ve sonuçların toplamını döndürür. Sözdizimi şöyle görünür:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Büyük/küçük harfe duyarlı bir aramaya ihtiyacımız olduğundan, işlevi kullanıyoruz TAM (TAM) çarpanlardan biri olarak önceki örnekten:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Hatırladığın gibi, TAM hücre değerini karşılaştırır F2 bir sütundaki tüm öğelerle A. Tam bir eşleşme bulunursa DOĞRU, aksi takdirde YANLIŞ döndürür. Matematiksel işlemlerde Excel, TRUE değerini şu şekilde alır: 1, ve için YANLIŞ 0Daha fazla SUMPRODUCT bu sayıları çarpar ve sonuçları toplar.
Sıfırlar sayılmaz çünkü çarpıldıklarında her zaman verirler. 0. Bir sütunda tam bir eşleşme olduğunda ne olduğuna daha yakından bakalım A bulundu ve geri döndü 1… İşlev SUMPRODUCT sütundaki sayıyı çarpar B on 1 ve sonucu döndürür - tam olarak aynı sayı! Bunun nedeni, diğer ürünlerin sonuçlarının sıfır olması ve elde edilen toplamı etkilememesidir.
ne yazık ki işlev SUMPRODUCT metin değerleri ve tarihler çarpılamadığı için çalışamaz. Bu durumda, bir hata mesajı alacaksınız # DEĞER! (#DEĞER!) bir hücrede olduğu gibi F4 aşağıdaki resimde:
Sınırlamalar: Yalnızca sayısal değerler döndürür.
INDEX + MATCH – herhangi bir veri türü için büyük/küçük harfe duyarlı arama
Son olarak, herhangi bir veri seti ile çalışan sınırsız ve büyük/küçük harfe duyarlı bir arama formülüne çok yakınız.
Bu örnek en sondadır, çünkü tatlıya en iyisi bırakıldığı için değil, önceki örneklerden edinilen bilgiler büyük/küçük harfe duyarlı formülü daha iyi ve daha hızlı anlamanıza yardımcı olacağı için. İNDEKS+KAÇINCI (INDEX+KAÇINCI).
Muhtemelen tahmin ettiğiniz gibi, fonksiyonların kombinasyonu DAHA MARUZ KALMIŞ и INDEX için daha esnek ve güçlü bir alternatif olarak Excel'de kullanılır. VPR. DÜŞEYARA yerine INDEX ve MATCH kullanma makalesi, bu işlevlerin birlikte nasıl çalıştığını mükemmel bir şekilde açıklayacaktır.
Sadece kilit noktaları tekrarlayacağım:
- işlev DAHA MARUZ KALMIŞ (MATCH) belirli bir aralıktaki bir değeri arar ve göreli konumunu, yani satır ve/veya sütun numarasını döndürür;
- Daha sonra, fonksiyon INDEX (INDEX) belirtilen bir sütundan ve/veya satırdan bir değer döndürür.
formüle İNDEKS+KAÇINCI büyük/küçük harfe duyarlı olarak arama yapabilir, buna yalnızca bir işlev eklemeniz gerekir. Yine ne olduğunu tahmin etmek zor değil TAM (BİRE BİR AYNI):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
Bu formülde TAM işlevle bağlantılı olarak aynı şekilde çalışır VIEW, ve aynı sonucu verir:
Formüle dikkat edin İNDEKS+KAÇINCI kaşlı ayraçlar içine alınmış bir dizi formülüdür ve tuşuna basarak tamamlamanız gerekir. Ctrl + Üst Karakter + Enter.
INDEX+MATCH neden büyük/küçük harfe duyarlı arama için en iyi çözümdür?
Paketin ana avantajları INDEX и DAHA MARUZ KALMIŞ:
- Farklı olarak yardımcı sütun eklenmesini gerektirmez VPR.
- Farklı olarak arama sütununun sıralanmasını gerektirmez VIEW.
- Her tür veriyle çalışır – sayılar, metinler ve tarihler.
Bu formül mükemmel görünüyor, değil mi? Aslında öyle değil. Ve bu yüzden.
Arama değeriyle ilişkili dönüş değeri sütunundaki hücrenin boş olduğunu varsayın. Formül hangi sonucu getirecek? Numara? Formülün gerçekte ne döndürdüğünü görelim:
Hata, formül sıfıra dönüyor! Saf metin değerleriyle çalışıyorsanız bu büyük bir sorun olmayabilir. Ancak tablo "gerçek" sıfırlar dahil sayılar içeriyorsa, bu bir sorun haline gelir.
Aslında, daha önce tartıştığımız diğer tüm arama formülleri (DÜŞEYARA, ARA ve TOPLA) aynı şekilde davranır. Ama mükemmel formülü istiyorsun, değil mi?
Bir formülü büyük/küçük harfe duyarlı hale getirmek için İNDEKS+KAÇINCI mükemmel, bir işleve koy IF (IF) bir hücreyi dönüş değeriyle test edecek ve boşsa boş bir sonuç döndürecek:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
Bu formülde:
- B dönüş değerlerine sahip bir sütundur
- 1+ fonksiyon tarafından döndürülen hücrenin göreli konumunu döndüren bir sayıdır DAHA MARUZ KALMIŞ, hücrenin gerçek adresine. Örneğin, fonksiyonumuzda DAHA MARUZ KALMIŞ verilen arama dizisi A2: A7, yani hücrenin göreli konumu A2 irade 1, çünkü dizideki ilk kişidir. Ancak hücrenin gerçek konumu A2 sütunda 2, böylece ekliyoruz 1farkı yaratmak ve işleve sahip olmak DOLAYLI (DOLAYLI) değeri istenen hücreden aldı.
Aşağıdaki resimler düzeltilmiş büyük/küçük harfe duyarlı formülü göstermektedir İNDEKS+KAÇINCI Eylemde. Döndürülen hücre boşsa boş bir sonuç döndürür.
Formülü sütunlara yeniden yazdım B:Dformül çubuğunu ekran görüntüsüne sığdırmak için.
Formül döndürür 0döndürülen hücre sıfır içeriyorsa.
link istersen INDEX и DAHA MARUZ KALMIŞ dönüş değeri boş olduğunda bir mesaj görüntülendi, bunu formülün son tırnak ("") içine yazabilirsiniz, örneğin şöyle:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
