Spis treści
Ten mały samouczek wyjaśnia, jak stworzyć funkcję VPR (WYSZUKAJ.PIONOWO) z uwzględnieniem wielkości liter, pokazuje kilka innych formuł, które program Excel może wyszukiwać z uwzględnieniem wielkości liter, oraz wskazuje mocne i słabe strony każdej funkcji.
Chyba każdy użytkownik Excela wie, jaką funkcję wykonuje wyszukiwanie pionowe. Zgadza się, to funkcja VPR. Jednak niewiele osób o tym wie VPR nie rozróżnia wielkości liter, tzn. małe i duże litery są dla niego identyczne.
Oto krótki przykład demonstrujący niemożność VPR rozpoznać rejestr. Załóżmy w komórce A1 zawiera wartość „rachunek” i komórkę A2 – „Rachunek”, wzór:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… zatrzyma wyszukiwanie „rachunku”, ponieważ ta wartość znajduje się na pierwszym miejscu na liście, i wyodrębni wartość z komórki B1.
W dalszej części tego artykułu pokażę Ci, jak to zrobić VPR wielkość liter ma znaczenie. Ponadto poznamy kilka dodatkowych funkcji, które mogą wykonywać wyszukiwania z rozróżnianiem wielkości liter w Excelu.
Zaczniemy od najprostszego – ZOBACZ (WYSZUKAJ) i SUMPRODUCT (SUMPRODUCT), które niestety mają kilka istotnych ograniczeń. Następnie przyjrzymy się nieco bardziej złożonej formule INDEKS+DOPASOWANIE (INDEX+MATCH), który działa bezbłędnie w każdej sytuacji iz dowolnym zestawem danych.
Funkcja WYSZUKAJ.PIONOWO rozróżnia wielkość liter
Jak już wiesz, zwykła funkcja VPR wielkość liter nie jest rozróżniana. Istnieje jednak sposób, aby rozróżniać wielkość liter. Aby to zrobić, musisz dodać do tabeli kolumnę pomocniczą, jak pokazano w poniższym przykładzie.
Załóżmy w kolumnie B istnieją identyfikatory produktu (pozycja) i chcesz wyodrębnić cenę produktu i odpowiedni komentarz z kolumn C и D. Problem polega na tym, że identyfikatory zawierają zarówno małe, jak i wielkie litery. Na przykład wartości komórek B4 (001Tvci3u) i B5 (001Tvci3U) różnią się tylko ostatnim znakiem, u и U odpowiednio.
Jak możesz sobie wyobrazić, zwykła formuła wyszukiwania
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
wróci $ 90, ponieważ wartość 001Tvci3u znajduje się w zakresie wyszukiwania wcześniej niż 001Tvci3U. Ale nie tego potrzebujemy, prawda?
Wyszukiwanie za pomocą funkcji VPR w programie Excel rozróżniana jest wielkość liter, będziesz musiał dodać kolumnę pomocniczą i wypełnić jej komórki następującą formułą (gdzie B jest kolumną wyszukiwania):
=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));"")
Ta formuła dzieli żądaną wartość na oddzielne znaki, zastępuje każdy znak swoim kodem (na przykład zamiast A w wieku 65 lat, zamiast tego a kod 97), a następnie łączy te kody w unikalny ciąg liczb.
Następnie używamy prostej funkcji VPR dla wyszukiwania z rozróżnianiem wielkości liter:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Prawidłowe działanie funkcji VPR wielkość liter zależy od dwóch czynników:
- Kolumna pomocnicza musi znajdować się najdalej od lewej w widocznym zakresie.
- Poszukiwana wartość musi zawierać kod znaku zamiast wartości rzeczywistej.
Jak poprawnie korzystać z funkcji KOD
Formuła wstawiona do komórek kolumny pomocniczej zakłada, że wszystkie wartości wyszukiwania mają taką samą liczbę znaków. Jeśli nie, musisz znać najmniejsze i największe liczby i dodać tyle funkcji JEŻELI BŁĄD (JEŻELIBŁĄD) liczba znaków to różnica między najkrótszą i najdłuższą wyszukiwaną wartością.
Na przykład, jeśli najkrótsza wartość wyszukiwania to 3 znaki, a najdłuższa to 5 znaków, użyj tej formuły:
=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));"")
Dla funkcji PSTR (MID) Podajesz następujące argumenty:
- 1. argument – XNUMX (tekst) to odwołanie do tekstu lub komórki zawierające znaki do wyodrębnienia (w naszym przypadku jest to B2)
- 2. argument – numer_początkowy (pozycja_początkowa) to pozycja pierwszego z tych znaków do wyodrębnienia. wchodzisz 1 w pierwszej funkcji PSTR, 2 – w drugiej funkcji PSTR itd.
- 3. argument – liczba_znaków (liczba_znaków) — określa liczbę znaków do wyodrębnienia z tekstu. Ponieważ cały czas potrzebujemy tylko 1 znaku, we wszystkich funkcjach piszemy 1.
OGRANICZENIA: Funkcjonować VPR nie jest najlepszym rozwiązaniem dla wyszukiwań z rozróżnianiem wielkości liter w programie Excel. Po pierwsze, wymagane jest dodanie kolumny pomocniczej. Po drugie, formuła sprawdza się tylko wtedy, gdy dane są jednorodne lub znana jest dokładna liczba znaków w wyszukiwanych wartościach. Jeśli tak nie jest, lepiej skorzystać z jednego z rozwiązań, które pokazujemy poniżej.
Funkcja LOOKUP do wyszukiwania z uwzględnieniem wielkości liter
Funkcjonować ZOBACZ (WYSZUKAJ) powiązane VPR, jednak jego składnia umożliwia wyszukiwanie z uwzględnieniem wielkości liter bez dodawania kolumny pomocniczej. Aby to zrobić, użyj ZOBACZ w połączeniu z funkcją DOKŁADNY (DOKŁADNY).
Jeśli weźmiemy dane z poprzedniego przykładu (bez kolumny pomocniczej), to z zadaniem poradzi sobie następująca formuła:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Wyszukiwanie formuł w zakresie A2: A7 dokładne dopasowanie do wartości komórki F2 wielkość liter ma znaczenie i zwraca wartość z kolumny B tego samego wiersza.
Jak VPRfunkcjonować ZOBACZ działa tak samo z wartościami tekstowymi i liczbowymi, jak widać na poniższym zrzucie ekranu:
Ważne! W celu funkcji ZOBACZ działał poprawnie, wartości w kolumnie wyszukiwania powinny być posortowane w kolejności rosnącej, czyli od najmniejszej do największej.
Pokrótce wyjaśnię, jak działa funkcja DOKŁADNY we wzorze pokazanym powyżej, ponieważ jest to kluczowy punkt.
Funkcjonować DOKŁADNY porównuje dwie wartości tekstowe w 1. i 2. argumencie i zwraca TRUE, jeśli są dokładnie takie same, lub FALSE, jeśli nie są. Ważne jest dla nas, aby funkcja DOKŁADNY wielkość liter ma znaczenie.
Zobaczmy, jak działa nasza formuła WYŚWIETL+DOKŁADNIE:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Funkcjonować DOKŁADNY porównuje wartość komórki F2 ze wszystkimi elementami w kolumnie A (A2:A7). Zwraca TRUE, jeśli zostanie znalezione dokładne dopasowanie, w przeciwnym razie FALSE.
- Ponieważ podajesz pierwszy argument funkcji ZOBACZ wartość TRUE, wyodrębnia odpowiednią wartość z określonej kolumny (w naszym przypadku kolumny B) tylko wtedy, gdy zostanie znalezione dokładne dopasowanie, z uwzględnieniem wielkości liter.
Mam nadzieję, że to wyjaśnienie było jasne i teraz rozumiesz główną ideę. Jeśli tak, to nie będziesz miał żadnych trudności z innymi funkcjami, które będziemy dalej analizować, ponieważ. wszystkie działają na tej samej zasadzie.
OGRANICZENIA: Dane w kolumnie odnośnika muszą być posortowane w porządku rosnącym.
SUMPRODUCT – wyszukuje wartości tekstowe, rozróżnia wielkość liter, ale zwraca tylko liczby
Jak już zrozumiałeś z tytułu, SUMPRODUCT (SUMPRODUCT) to kolejna funkcja programu Excel, która ułatwia wyszukiwanie z uwzględnieniem wielkości liter, ale zwraca tylko wartości liczbowe. Jeśli ta opcja Ci nie odpowiada, możesz od razu przejść do pakietu INDEKS+DOPASOWANIE, co daje rozwiązanie dla każdego przypadku i dla dowolnych typów danych.
Najpierw pokrótce wyjaśnię składnię tej funkcji, co pomoże ci lepiej zrozumieć następującą formułę uwzględniającą wielkość liter.
Funkcjonować SUMPRODUCT mnoży elementy podanych tablic i zwraca sumę wyników. Składnia wygląda tak:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Ponieważ potrzebujemy wyszukiwania z uwzględnieniem wielkości liter, używamy funkcji DOKŁADNY (DOKŁADNIE) z poprzedniego przykładu jako jeden z mnożników:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Jak pamiętacie DOKŁADNY porównuje wartość komórki F2 ze wszystkimi elementami w kolumnie A. Zwraca TRUE, jeśli zostanie znalezione dokładne dopasowanie, w przeciwnym razie FALSE. W operacjach matematycznych Excel przyjmuje TRUE jako 1i FALSE dla 0Dalej SUMPRODUCT mnoży te liczby i sumuje wyniki.
Zera nie są liczone, ponieważ pomnożone zawsze dają 0. Przyjrzyjmy się bliżej, co się dzieje, gdy w kolumnie występuje dokładne dopasowanie A znaleziony i zwrócony 1… Funkcja SUMPRODUCT mnoży liczbę w kolumnie B on 1 i zwraca wynik – dokładnie taką samą liczbę! Dzieje się tak, ponieważ wyniki innych produktów są zerowe i nie wpływają na wynikową sumę.
Niestety funkcja SUMPRODUCT nie może pracować z wartościami tekstowymi i datami, ponieważ nie można ich mnożyć. W takim przypadku otrzymasz komunikat o błędzie #WARTOŚĆ! (#VALUE!) jak w komórce F4 na zdjęciu poniżej:
OGRANICZENIA: Zwraca tylko wartości liczbowe.
INDEX + MATCH – wyszukiwanie z rozróżnianiem wielkości liter dla dowolnego typu danych
Wreszcie jesteśmy blisko nieograniczonej formuły wyszukiwania z rozróżnianiem wielkości liter, która działa z dowolnym zestawem danych.
Ten przykład jest ostatni, nie dlatego, że najlepsze zostaje na deser, ale dlatego, że wiedza zdobyta w poprzednich przykładach pomoże ci lepiej i szybciej zrozumieć formułę uwzględniającą wielkość liter. INDEKS+DOPASOWANIE (INDEKS+DOPASOWANIE).
Jak się zapewne domyślasz, kombinacja funkcji WIĘCEJ EKSPOZYCYJNE и INDEKS używany w programie Excel jako bardziej elastyczna i wydajna alternatywa dla VPR. Artykuł Używanie indeksu i dopasowania zamiast funkcji WYSZUKAJ.PIONOWO doskonale wyjaśni, jak te funkcje współpracują ze sobą.
Podsumuję tylko kluczowe punkty:
- Funkcjonować WIĘCEJ EKSPOZYCYJNE (MATCH) wyszukuje wartość w podanym zakresie i zwraca jej pozycję względną, czyli numer wiersza i/lub kolumny;
- Następnie funkcja INDEKS (INDEKS) zwraca wartość z określonej kolumny i/lub wiersza.
Do formuły INDEKS+DOPASOWANIE może wyszukiwać z uwzględnieniem wielkości liter, wystarczy dodać do niego tylko jedną funkcję. Nietrudno zgadnąć, co to jest ponownie DOKŁADNY (DOKŁADNY):
=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))
W tej formule DOKŁADNY działa tak samo, jak w połączeniu z funkcją ZOBACZi daje ten sam wynik:
Zauważ, że formuła INDEKS+DOPASOWANIE ujęta w nawiasy klamrowe jest formułą tablicową i należy ją uzupełnić, naciskając Ctrl + Shift + Enter.
Dlaczego INDEX+MATCH jest najlepszym rozwiązaniem do wyszukiwania z rozróżnianiem wielkości liter?
Główne zalety pakietu INDEKS и WIĘCEJ EKSPOZYCYJNE:
- Nie wymaga dodawania kolumny pomocniczej, w przeciwieństwie do VPR.
- Nie wymaga sortowania kolumny wyszukiwania, w przeciwieństwie do ZOBACZ.
- Działa ze wszystkimi typami danych – liczbami, tekstem i datami.
Ta formuła wydaje się idealna, prawda? Właściwie tak nie jest. I własnie dlatego.
Załóżmy, że komórka w kolumnie zwracanej wartości skojarzonej z wartością wyszukiwania jest pusta. Jaki wynik zwróci formuła? Nie? Zobaczmy, co faktycznie zwraca formuła:
Ups, formuła zwraca zero! Może to nie być duży problem, jeśli pracujesz z czystymi wartościami tekstowymi. Jeśli jednak tabela zawiera liczby, w tym „prawdziwe” zera, staje się to problemem.
W rzeczywistości wszystkie inne formuły wyszukiwania (WYSZUKAJ.PIONOWO, WYSZUKAJ i SUMPRODUCT), które omówiliśmy wcześniej, zachowują się w ten sam sposób. Ale chcesz idealnej formuły, prawda?
Aby w formule rozróżniać wielkość liter INDEKS+DOPASOWANIE idealnie, umieść to w funkcji IF (IF), który przetestuje komórkę ze zwracaną wartością i zwróci pusty wynik, jeśli jest pusta:
=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));"")
W tej formule:
- B to kolumna ze zwracanymi wartościami
- 1+ to liczba, która zmienia względną pozycję komórki zwróconej przez funkcję WIĘCEJ EKSPOZYCYJNE, na prawdziwy adres komórki. Na przykład w naszej funkcji WIĘCEJ EKSPOZYCYJNE podano tablicę wyszukiwania A2: A7, czyli względne położenie komórki A2 będzie 1, ponieważ jest to pierwsza w tablicy. Ale rzeczywista pozycja komórki A2 w kolumnie jest 2, więc dodajemy 1aby nadrobić różnicę i mieć funkcję POŚREDNI (POŚREDNIA) pobrała wartość z żądanej komórki.
Poniższe zdjęcia pokazują poprawioną formułę uwzględniającą wielkość liter INDEKS+DOPASOWANIE W akcji. Zwraca pusty wynik, jeśli zwrócona komórka jest pusta.
Przepisałem formułę na kolumny B: Daby dopasować pasek formuły na zrzucie ekranu.
Formuła zwraca 0jeśli zwrócona komórka zawiera zero.
Jeśli chcesz link INDEKS и WIĘCEJ EKSPOZYCYJNE wyświetlił komunikat, gdy zwracana wartość jest pusta, możesz napisać ją w ostatnich cudzysłowach („”) formuły, na przykład w ten sposób:
=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.")
