WYSZUKAJ.PIONOWO w programie Excel jest potężne, ale nie będzie działać w określonych sytuacjach. Dziś zobacz, jak rozwiązywać problemy z funkcją WYSZUKAJ.PIONOWO.
WYSZUKAJ.PIONOWO to moja ulubiona funkcja w programie Excel. Jeśli możesz wykonać WYSZUKAJ.PIONOWO, jesteś w stanie rozwiązać wiele problemów w programie Excel. Ale są rzeczy, które mogą spowodować awarię WYSZUKAJ.PIONOWO. W tym temacie omówiono kilka z nich.
Ale najpierw podstawy VLOOKUP w prostym języku angielskim.
Dane w A: C pochodzą z działu IT. Poprosiłeś o sprzedaż według pozycji i daty. Dali ci numer przedmiotu. Potrzebujesz opisu przedmiotu. Zamiast czekać, aż dział IT ponownie uruchomi dane, znajdziesz tabelę pokazaną w kolumnie F: G.
Chcesz, aby funkcja WYSZUKAJ.PIONOWO znalazła element w A2 podczas przeszukiwania pierwszej kolumny tabeli w $ F $ 3: $ G 30 $. Gdy WYSZUKAJ.PIONOWO znajdzie dopasowanie w F7, chcesz, aby WYSZUKAJ.PIONOWO zwróciło opis znajdujący się w drugiej kolumnie tabeli. Każda funkcja WYSZUKAJ.PIONOWO, która szuka dokładnego dopasowania, musi kończyć się fałszem (lub zerem, co jest równoważne fałszowi). Poniższy wzór jest poprawnie skonfigurowany.
Zwróć uwagę, że używasz klawisza F4, aby dodać cztery znaki dolara do adresu tabeli przeglądowej. Podczas kopiowania formuły w dół kolumny D potrzebujesz adresu tabeli przeglądowej, aby pozostał stały. Istnieją dwie typowe alternatywy: Możesz określić całe kolumny F: G jako tabelę przeglądową. Lub możesz nazwać F3: G30 nazwą taką jak ItemTable. Jeśli używasz =VLOOKUP(A2,ItemTable,2,False), nazwany zakres działa jak odniesienie bezwzględne.
Za każdym razem, gdy wykonujesz kilka funkcji WYSZUKAJ.PIONOWO, musisz posortować kolumnę WYSZUKAJ.PIONOWO. Sortuj ZA, a wszelkie błędy # N / A pojawią się na górze. W tym przypadku jest jeden. W tabeli przeglądowej brakuje pozycji BG33-9. Może to literówka. Może to zupełnie nowy przedmiot. Jeśli jest nowy, wstaw nowy wiersz w dowolnym miejscu na środku tabeli przeglądowej i dodaj nowy element.
Wystąpienie kilku błędów # N / D jest całkiem normalne. Ale na poniższym rysunku dokładnie ta sama formuła zwraca tylko # N / A. Kiedy tak się stanie, widzę, czy uda mi się rozwiązać pierwsze WYSZUKAJ.PIONOWO. Szukasz BG33-8 znalezionego w A2. Zacznij przechodzić przez pierwszą kolumnę tabeli przeglądowej. Jak widać, pasująca wartość wyraźnie znajduje się w F10. Dlaczego to widzisz, ale program Excel tego nie widzi?
Przejdź do każdej komórki i naciśnij klawisz F2. Oto F10. Zwróć uwagę, że kursor wstawiania pojawia się zaraz po 8.
Oto komórka A2 w trybie edycji. Kursor wstawiania znajduje się kilka spacji od cyfry 8. To znak, że w pewnym momencie dane te były przechowywane w starym zestawie danych języka COBOL. W języku COBOL, jeśli pole Pozycja zostało zdefiniowane jako 10 znaków, a Ty wpisałeś tylko 6 znaków, COBOL uzupełniłby je 4 dodatkowymi spacjami.
Rozwiązanie? Zamiast szukać A2, spójrz w górę TRIM(A2).
Funkcja TRIM () usuwa spacje wiodące i końcowe. Jeśli masz wiele spacji między słowami, TRIM przekształci je w pojedynczą spację. Na poniższym rysunku znajdują się spacje przed i po obu nazwach w A1. =TRIM(A1)usuwa wszystkie z wyjątkiem jednej spacji w A3.
A propos, co by było, gdyby problem dotyczył końcowych spacji w kolumnie F zamiast w kolumnie A? Dodaj kolumnę funkcji TRIM () do E, wskazując na kolumnę F. Skopiuj je i wklej jako wartości w F, aby wyszukiwanie zaczęło działać ponownie.
Inny bardzo częsty powód, dla którego funkcja WYSZUKAJ.PIONOWO nie działa, jest pokazany tutaj. Kolumna F zawiera liczby rzeczywiste. Kolumna A zawiera tekst, który wygląda jak liczby.
Zaznacz całą kolumnę A. Naciśnij alt = "" + D, E, F. Spowoduje to utworzenie domyślnego tekstu na kolumny i przekonwertuje wszystkie liczby tekstowe na liczby rzeczywiste. Wyszukiwanie ponownie zaczyna działać.
Obejrzyj wideo
- WYSZUKAJ.PIONOWO rozwiązuje wiele problemów
- Typowe problemy z funkcją WYSZUKAJ.PIONOWO:
- Jeśli funkcja WYSZUKAJ.PIONOWO zaczyna działać, ale # N / A staje się bardziej widoczne: zapomniałem znaku $ w tabeli odnośników
- Kilka # N / A: brakuje elementów w tabeli
- Żadna z funkcji WYSZUKAJ.PIONOWO nie działa: sprawdź spacje końcowe
- Usuń końcowe spacje za pomocą TRIM
- Liczby i liczby przechowywane jako tekst
- Wybierz obie kolumny i użyj alt = "" + DEF
- Odcinek zawiera żart, który zarówno księgowi, jak i informatycy uważają za zabawny, ale z różnych powodów
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2027 - Rozwiązywanie problemów z WYSZUKAJ.PIONOWO!
W porządku, podcasting całej tej książki, kliknij „i” w prawym górnym rogu, aby przejść do listy odtwarzania!
WYSZUKAJ.PIONOWO to moja ulubiona funkcja w całym programie Excel i zawsze opowiadam ten żart na jednym z moich seminariów i sprawia mi to śmiech, niezależnie od tego, czy jesteś informatykiem, czy nie. Zawsze mówię: „No cóż, mamy zestaw danych tutaj po lewej stronie i mogę spojrzeć na te dane i stwierdzić, że pochodzą one z działu IT, ponieważ jest to dokładnie to, o co prosiłem, ale nie było to, czego potrzebowałem. Poprosiłem o datę i ilość przedmiotu, a oni podali mi datę i ilość numeru przedmiotu, ale nie zadali sobie trudu, aby podać mi opis, prawda? ” Księgowi zawsze się z tego śmieją, bo to im się zdarza cały czas, a informatycy mówią: „Cóż, dałem ci to, o co prosiłeś, to nie moja wina!” Więc oboje uważają, że to zabawne z różnych powodów, więc wiesz, a informatyk jest zajęty, nie może wrócić do przepisywania zapytania,więc musimy coś zrobić, aby sami to uratować.
A więc ten mały stolik, który skopiowałem z innego miejsca w moim komputerze, w prostym języku angielskim, wyświetla funkcję WYSZUKAJ.PIONOWO: „Hej, mamy numer pozycji W25-6”. Mamy tutaj tabelę, chcę przejść przez pierwszą kolumnę tabeli, aż znajdę numer pozycji, a następnie zwrócić coś z tego wiersza. W porządku, w tym przypadku potrzebuję drugiej kolumny z tego wiersza. A potem na końcu każdego WYSZUKAJ.PIONOWO musimy wstawić FAŁSZ lub 0. Teraz tutaj, po wybraniu zakresu, nacisnę klawisz F4, a następnie chcę, aby druga kolumna, a następnie FAŁSZ, była dokładna mecz. Nigdy nie wybieraj przybliżonego dopasowania, nigdy, nigdy! To nie jest przybliżone dopasowanie, to bardzo wyjątkowa rzecz, nie działa przez cały czas. Jeśli chcesz ponownie przekazać swoje liczby do Komisji Papierów Wartościowych i Giełd, z całą pewnością nie wahaj się skorzystać z:TRUE lub po prostu zostaw, FALSE wyłączone. Ale każde WYSZUKAJ.PIONOWO, które kiedykolwiek utworzysz, powinno kończyć się na FAŁSZ lub 0, 0 jest krótsze niż FAŁSZ, powinieneś umieścić tam FAŁSZ, ale 0 to to samo, co FAŁSZ.
W porządku, teraz rozwiązywanie problemów, po pierwsze, kiedy robisz całą masę WYSZUKAJ.PIONOWO, to bardzo normalne, że kilka # N / A, prawda? I zawsze znajduję # N / A, przechodząc Data, ZA, co przenosi # N / As na górę, właśnie tam, BG33-9, albo to jest literówka, albo jest to zupełnie nowy element, w porządku, i mamy żeby to rozgryźć. Więc tutaj po prawej mam nowe dane, wytnę to, a potem Alt + IED, wstawię te komórki na środku, nie musi to być alfabetyczne, ta tabela nie musi być sortowana. Kiedy używasz wersji FAŁSZ, tabela nie jest - możesz ją po prostu umieścić w dowolnym miejscu, nie umieściłem na końcu, ponieważ nie musiałem przepisywać formuły, po prostu chcę, aby formuła była praca. W porządku, więc kilka # N / A's, skrajnie, skrajnie normalne, ale spójrz na to.
Kiedy zaczynasz od odpowiedzi, które działają, ale potem # N / A zaczynają pojawiać się nieco częściej, a potem w końcu pojawiają się całkowicie w dół, jest to pewny znak, że nie zablokowałeś odwołania do tabeli. W porządku, więc tutaj tabela przesuwa się, gdy kopiuję wzór w dół, tak, więc mam szczęście trafiając kilka, które były na końcu listy. Ale w końcu dochodzę do punktu, w którym patrzy tutaj w całkowicie pustych komórkach i oczywiście nic nie znajduje. W porządku, więc to pierwsza rzecz, dostajesz parę, które działają, kilka # N / A's, kilka innych, które działają, a potem wszystko # N / A to reszta drogi - na pewno znak, że nie umieściłeś $ w.
Po prostu wróć, F2, aby przejść do trybu edycji, wybierz dwukropek, naciśnij F4, co spowoduje umieszczenie wszystkich $, kliknij dwukrotnie, aby to zestrzelić, i wszystko znów zacznie działać, w porządku. Następny, dokładnie ta sama formuła, wzór jest doskonały, BG33-8 widzisz, nic z tego nie robimy. W porządku, więc idę patrzeć, BG33-8, hej, jest, jest tam, jest na czerwono, powinienem to zobaczyć! Więc dochodzę do tego po prawej stronie, wciskam F2 i patrzę na migający punkt wstawiania i widzę, jest zaraz po 8, tak, a potem podchodzę tutaj i wciskam F2, są kilka końcowych spacji. To jest bardzo, bardzo częste w czasach COBOL-a, mówili „Wiesz, spójrz, damy ci 10 miejsc na numer przedmiotu, a jeśli nie wpiszesz wszystkich 10 spacji, wypełnią one te miejsca . ” Dlatego jest to bardzo częste,a świetnym rozwiązaniem jest pozbycie się tych wiodących i końcowych spacji za pomocą funkcji TRIM. Zamiast A2 użyj TRIM (A2), kliknij dwukrotnie, aby zestrzelić, w porządku, nadal BG33-9 jest z powrotem w drugiej tabeli.
W porządku, żebyś zrozumiał, jak działa TRIM, więc wpisałem kilka spacji, John, kilka spacji, Durran i kilka spacji, a następnie połączyłem * przed i po, aby zobaczyć, jak to wygląda . Kiedy proszę o TRIM (P4), pozbywamy się wszystkich spacji wiodących, wszystkich spacji końcowych, a następnie wiele przestrzeni wewnętrznych zostaje zredukowanych do jednej spacji. W porządku, więc możesz zobaczyć, w pewnym sensie wizualizować, że pozbywają się spacji wiodących i końcowych, wszelkie podwojone spacje w środku stają się pojedynczą spacją. Więc TRIM, świetne, świetne narzędzie w twoim arsenale, w porządku, oto kolejne naprawdę powszechne.
Jeśli nie chodzi o końcowe spacje, to najczęstszą rzeczą, jaką widziałem, jest to, gdzie tutaj mamy prawdziwe liczby, a tutaj mamy liczby zapisane jako tekst. WYSZUKAJ.PIONOWO nie zobaczy tego jako dopasowania, mimo że 4399, to jest liczba, to jest tekst, nie działa. Najszybszy sposób na przekonwertowanie kolumny tekstu na liczby, wybierz kolumnę, 3 litery w kolejności, alt = "" DEF i nagle, nasze WYSZUKAJ.PIONOWO znowu zaczynają działać, świetna, świetna wskazówka sprzed około 1500 podcastów. W porządku, więc są to najczęstsze problemy z funkcją WYSZUKAJ.PIONOWO. Albo zapomniałeś $, albo masz spacje na końcu, albo masz liczby i liczby zapisane jako tekst. Wszystkie te wskazówki znajdują się w tej książce „MrExcel XL”, kliknij „i” w prawym górnym rogu, możesz kupić książkę.
W porządku, krótkie podsumowanie: WYSZUKAJ.PIONOWO rozwiązuje wiele problemów, jeśli WYSZUKAJ.PIONOWO zacznie działać, ale # N / A! staje się bardziej widoczny, zapomniałeś umieścić $ w tabeli przeglądowej. Jeśli jest kilka # N / A's, to po prostu brakuje elementów w tabeli. Jeśli żadna z funkcji WYSZUKAJ.PIONOWO nie działa i zawiera tekst, sprawdź spacje końcowe, możesz użyć funkcji PRZYCINANIE. Jeśli masz liczby i liczby zapisane jako tekst, wybierz jedną z kolumn, tę, w której znajduje się tekst, a następnie wykonaj alt = "" DEF mają wszystkie te z powrotem do liczb.
W porządku, hej, chcę ci podziękować za zatrzymanie się, do zobaczenia następnym razem z kolejnym netcastem!
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2027.xlsx
