Wiele osób próbuje używać programu Excel jako bazy danych. Chociaż może działać jako baza danych, niektóre zadania, które byłyby bardzo łatwe w programie bazodanowym, są dość złożone w programie Excel. Jednym z tych zadań jest dopasowywanie dwóch list na podstawie wspólnego pola; można to łatwo zrobić za pomocą funkcji WYSZUKAJ.PIONOWO w programie Excel. Przekonasz się, że funkcja WYSZUKAJ.PIONOWO jest niezwykle przydatna, więc poniżej znajdziesz przykład, kiedy i jak z niej korzystać.
Powiedz, że Twoje biuro podróży przesyła Ci raport na koniec miesiąca ze wszystkich miejsc, w których podróżowali Twoi pracownicy. W raporcie zamiast nazw miast używane są kody lotnisk. Byłoby pomocne, gdybyś mógł łatwo wpisać prawdziwą nazwę miasta zamiast samego kodu.
W Internecie można znaleźć i zaimportować listę zawierającą nazwę miasta dla każdego kodu lotniska.
Ale w jaki sposób uzyskujesz te informacje o każdym rekordzie w raporcie?
- Użyj funkcji WYSZUKAJ.PIONOWO. VLOOKUP to skrót od „Vertical Lookup”. Można go używać w dowolnym momencie, gdy masz listę danych z polem klucza w lewej kolumnie.
- Zacznij wpisywać funkcji
=VLOOKUP(. Naciśnij klawisze Ctrl + A, aby uzyskać pomoc dotyczącą funkcji. - WYSZUKAJ.PIONOWO wymaga czterech parametrów. Pierwszy to kod miasta w oryginalnym raporcie. W tym przykładzie byłaby to komórka D4
- Następnym parametrem jest zakres z tabelą przeglądową. Podświetl zakres. Pamiętaj, aby użyć F4, aby zakres był bezwzględny. (Odniesienie bezwzględne ma znak dolara przed numerem kolumny i numerem wiersza. Po skopiowaniu formuły odniesienie będzie nadal wskazywało na I3: J351.
- Trzeci parametr informuje program Excel, w której kolumnie znajduje się nazwa miasta. W zakresie I3: J351 nazwa miasta znajduje się w kolumnie 2. Wprowadź 2 dla tego parametru.
- Czwarty parametr informuje program Excel, czy „bliskie” dopasowanie jest prawidłowe. W tym przypadku tak nie jest, więc wpisz False.
- Kliknij OK, aby uzupełnić formułę. Przeciągnij uchwyt wypełniania, aby skopiować formułę w dół.
- Ponieważ starannie wprowadziłeś formuły bezwzględne, możesz skopiować kolumnę E do kolumny D, aby uzyskać miasto docelowe. W tym przypadku wszystkie odloty odbywają się z międzynarodowego lotniska Pearson w Toronto.
Chociaż ten przykład zadziałał doskonale, gdy widzowie używają funkcji WYSZUKAJ.PIONOWO, zwykle oznacza to, że dopasowują listy pochodzące z różnych źródeł. Jeśli listy pochodzą z różnych źródeł, zawsze mogą występować subtelne różnice, które utrudniają dopasowanie list. Oto trzy przykłady tego, co może się nie udać i jak je naprawić.
-
Jedna lista zawiera myślniki, a druga nie. Użyj
=SUBSTITUTE()funkcji, aby usunąć kreski. Przy pierwszej próbie WYSZUKAJ.PIONOWO wystąpią błędy „Nie dotyczy”.Aby usunąć kreski za pomocą formuły, użyj formuły PODSTAW. Użyj 3 argumentów. Pierwszym argumentem jest komórka zawierająca wartość. Następnym argumentem jest tekst, który chcesz zmienić. Ostatnim argumentem jest tekst zastępczy. W tym przypadku chcesz zmienić myślniki na nic, więc formuła jest taka
=SUBSTITUTE(A4,"-","").Możesz zawinąć tę funkcję w WYSZUKAJ.PIONOWO, aby uzyskać opis.
-
Ten jest subtelny, ale bardzo powszechny. Na jednej liście na końcu wpisu znajduje się spacja. Użyj = TRIM (), aby usunąć nadmiar spacji. Po wstępnym wprowadzeniu wzoru wszystkie odpowiedzi są błędami „Nie dotyczy”. Wiesz na pewno, że wartości są na liście i wszystko wygląda dobrze z formułą.
Standardową rzeczą do sprawdzenia jest przejście do komórki z wartością wyszukiwania. Naciśnij klawisz F2, aby umieścić komórkę w trybie edycji. W trybie edycji możesz zobaczyć, że kursor znajduje się o jedną spację od ostatniej litery. Oznacza to, że we wpisie znajduje się spacja na końcu.
Aby rozwiązać problem, użyj funkcji TRIM.
=TRIM(D4)usunie spacje wiodące i końcowe oraz zamieni wszelkie wewnętrzne spacje podwójne pojedynczą spacją. W tym przypadku TRIM działa idealnie, aby usunąć końcową spację.=VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)jest formułą. -
Wspomniałem o dodatkowej wskazówce w uwagach do programu: jak zamienić wynik # N / D dla brakujących wartości pustą. Jeśli wartości wyszukiwania nie ma w tabeli przeglądowej, funkcja WYSZUKAJ.PIONOWO zwróci błąd N / D.
Ta formuła używa
=ISNA()funkcji do wykrywania, czy wynik formuły jest błędem N / A. Jeśli pojawi się błąd, drugi argument funkcji JEŻELI powie programowi Excel, aby wstawił dowolny tekst.=IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))
WYSZUKAJ.PIONOWO pozwala zaoszczędzić czas podczas dopasowywania list danych. Poświęć trochę czasu na nauczenie się podstawowych zastosowań, a będziesz w stanie wykonywać znacznie bardziej zaawansowane zadania w programie Excel.
