WYSZUKAJ.PIONOWO to niesamowita i moja ulubiona funkcja
Te tabele nie tylko ułatwiają odświeżanie danych, ale także znacznie ułatwiają czytanie formuł! Jedyne, co musisz zrobić, to nacisnąć Ctrl + T przed napisaniem formuły.
Wróćmy do formuły WYSZUKAJ.PIONOWO z góry. Tym razem przekonwertuj swoją tabelę przedmiotów i tabelę zakupów do tabeli Excela za pomocą Ctrl + T od samego początku! Aby to ułatwić, nadaj każdej tabeli przyjazną nazwę za pomocą karty Narzędzia tabel:
Teraz wpisz ponownie WYSZUKAJ.PIONOWO, nie robiąc niczego inaczej niż zwykle, twoja formuła w C2 jest teraz =VLOOKUP((@Item),Items,2,0)zamiast =VLOOKUP(B2,$E$5:$F$10,2,0)!
Nawet jeśli tabela Elementy znajduje się w innym arkuszu, formuła jest taka sama, a nie mniej czytelna =VLOOKUP(B2,Items!$A$2:$B$7,2,0).
(@Item) w formule odnosi się do komórki w kolumnie Pozycja w tej tabeli (w tym samym wierszu co formuła) i dlatego jest taka sama w całej kolumnie. A Pozycja odnosi się do całej tabeli pozycji (bez nagłówków). A co najlepsze, nie musisz tego wpisywać. Gdy będzie to tabela, program Excel umieści te nazwy w formule podczas wybierania komórek / zakresów!
Pójdźmy o krok dalej. Dodaj kolejną kolumnę do tabeli Sales, aby obliczyć przychód za pomocą formuły =(@Price)*(@Qty). Jeśli chcesz teraz obliczyć całkowity dochód, wzór jest następujący =SUM(Sales(Revenue)); co jest naprawdę łatwe do zrozumienia, bez względu na to, gdzie znajdują się dane i ile wierszy obejmują!
Obejrzyj wideo
- WYSZUKAJ.PIONOWO to niesamowita i moja ulubiona funkcja
- Nienawidzący funkcji WYSZUKAJ.PIONOWO narzekają, że jest kruchy ze względu na trzeci argument
- Jeśli kształt tabeli przeglądowej zmieni się, odpowiedzi mogą ulec zmianie
- Jednym obejściem jest zastąpienie trzeciego argumentu parametrem PODAJ.POZYCJĘ
- Ale wyobraź sobie, że wykonujesz MATCH dla 1000 wierszy funkcji WYSZUKAJ.PIONOWO
- Przed wykonaniem funkcji WYSZUKAJ.PIONOWO przekształć swoją tablicę przeglądową w tabelę
- Strukturalne odwołanie do tabeli poradzi sobie w przypadku zmiany kształtu tabeli
- Ponadto nie wymaga ciągłego wykonywania MECZÓW
- Peter Albert przesłał tę wskazówkę
Transkrypcja wideo
Learn Excel for Podcast, Episode 2003 - Readable References
Nie zapomnij zasubskrybować listy odtwarzania XL. Będę podcastował całą tę książkę.
Dobra dzisiejsza wskazówka od Petera Alberta. Peter Albert. Porozmawiajmy teraz o WYSZUKAJ.PIONOWO. Jestem wielkim fanem VLOOKUP. Dla mnie VLOOKUP to linia podziału. Jeśli potrafisz wykonywać WYSZUKAJ.PIONOWO, wszystko inne w Excelu będzie dla Ciebie łatwe. WYSZUKAJ.PIONOWO pozwala nam sprawdzić cenę z tej tabeli, ao WYSZUKAJ.PIONOWO porozmawiamy później.
Więc skopiuj to i wszystko działa dobrze, ale muszę ci powiedzieć. Widziałem ich. Rozmawiałem z nimi. Poznałam ich. Tam są hejterzy VLOOKUP. Ludzie, którzy nienawidzą, jeśli spojrzysz w górę i jakie inne narzekają, że jest tak kruchy, ten trzeci argument, w którym powiedzieliśmy, że chcemy trzecią kolumnę, że jeśli ktoś zdecyduje później, potrzebujemy tutaj nowego pola, może jak, rozmiar . W porządku, po pierwsze, wydaje się, że jest jakiś błąd, że Excel nie przelicza tego wszystkiego. Pozwól mi cofnąć, cofnąć, a następnie powtórzyć. No to jedziemy. To dziwne, musiałem zgłosić to zespołowi Excela, ale widzisz, że tam, gdzie otrzymywaliśmy cenę, teraz nabiera koloru, ponieważ na stałe zakodowano, że chcą trzecią kolumnę. W porządku, a to, co ludzie robią, aby to obejść, to ta szalona rzecz z = MATCH.Poszukaj słowa Cena w pierwszym wierszu tabeli, F4,0, a to powie nam, że cena w tym miejscu jest czwartą kolumną. Więc faktycznie zrobią = WYSZUKAJ.PIONOWO. W tej tabeli wyszukujemy A104. F4, a następnie zamiast na stałe zakodować liczbę cztery, robią MECZ, a MECZ zostanie zablokowany na cenie. Więc F4, dwa razy wstaw $ przed 1 i będzie przeglądać pierwszy wiersz tabeli. Ups, dwukrotnie klawisz F4, przecinek, pominięto przecinek. W porządku, naciśnij F4 tutaj przecinek 0, aby uzyskać dokładne dopasowanie do dopasowania, a następnie przecinek przypada, aby uzyskać dokładne dopasowanie do WYSZUKAJ.PIONOWO. Tak i hej, to działa świetnie, a tutaj mam ich tylko sześć, więc to nic wielkiego.w tej tabeli. F4, a następnie zamiast na stałe zakodować liczbę cztery, robią MECZ, a MECZ zostanie zablokowany na cenie. Więc F4, dwa razy wstaw $ przed 1 i będzie przeglądać pierwszy wiersz tabeli. Ups, dwukrotnie klawisz F4, przecinek, pominięto przecinek. W porządku, naciśnij F4 tutaj przecinek 0, aby uzyskać dokładne dopasowanie do dopasowania, a następnie przecinek przypada, aby uzyskać dokładne dopasowanie do WYSZUKAJ.PIONOWO. Tak i hej, to działa świetnie, a tutaj mam ich tylko sześć, więc to nic wielkiego.w tej tabeli. F4, a następnie zamiast na stałe zakodować liczbę cztery, robią MECZ, a MECZ zostanie zablokowany na cenie. Więc F4, dwa razy wstaw $ przed 1 i będzie przeglądać pierwszy wiersz tabeli. Ups, dwukrotnie klawisz F4, przecinek, pominięto przecinek. W porządku, naciśnij F4 tutaj przecinek 0, aby uzyskać dokładne dopasowanie do dopasowania, a następnie przecinek przypada, aby uzyskać dokładne dopasowanie do WYSZUKAJ.PIONOWO. Tak i hej, to działa świetnie, a tutaj mam ich tylko sześć, więc to nic wielkiego.W porządku, naciśnij F4 tutaj przecinek 0, aby uzyskać dokładne dopasowanie do dopasowania, a następnie przecinek przypada, aby uzyskać dokładne dopasowanie do WYSZUKAJ.PIONOWO. Tak i hej, to działa świetnie, a tutaj mam ich tylko sześć, więc to nic wielkiego.W porządku, naciśnij F4 tutaj przecinek 0, aby uzyskać dokładne dopasowanie do dopasowania, a następnie przecinek przypada, aby uzyskać dokładne dopasowanie do WYSZUKAJ.PIONOWO. Tak i hej, to działa świetnie, a tutaj mam ich tylko sześć, więc to nic wielkiego.
Sprawdź, czy wstawię nowy, automatycznie dostosuje się i będzie nadal pobierał cenę, ale wyobraź sobie, że masz tysiąc WYSZUKAJ.PIONOWO, a każde WYSZUKAJ.PIONOWO powtórzy to dopasowanie, aby obliczyć ceny w piątej lub czwartej kolumnie. To jest straszne. Tabele po prostu rozwiązują ten problem. Oto moja tabela WYSZUKAJ.PIONOWO, zanim cokolwiek zrobię, przejdę tutaj i CTRL T, aby przekształcić ją w prawdziwą tabelę. Nazwą to tabelą 1, ale ja nazwę to ProductTable, wszystko jednym słowem, bez spacji: ProductTable. Więc teraz ma nazwę. W porządku, więc mamy teraz tabelę o nazwie ProductTable. Potem przychodzimy tutaj i mówimy, że zrobimy = INDEKS tych cen. Jaką cenę chcemy? Chcemy wyniku dopasowania A104 do tych pozycji. Dokładne dopasowanie, zamykające nawiasy dla INDEKSU.To tylko jeden mecz. To nie jest dopasowywanie i WYSZUKAJ.PIONOWO. W pewnym sensie będzie dużo, dużo szybciej. Skopiuj to. W porządku, a później, jeśli wstawimy rozmiar, więc wstaw kolumnę, rozmiar wszystko nadal działa, ponieważ szuka kolumny o nazwie Cena i powiedzmy, że jeśli zmienimy to na cenę katalogową, ta formuła zostanie przepisana. Tak, o wiele, dużo bezpieczniejszy i bezpieczniejszy sposób.
W porządku, tyle fajnych sztuczek w tabelach. Przeczytaj tę książkę Kevina Jonesa i Zacha Barresse'a w tabelach programu Excel. Wszelkiego rodzaju sztuczki i wszystko, co podcastujemy w sierpniu i wrześniu, jest w tej wypełnionej jamą książce. Plus dużo zabawy. Dowcipy Excela. Koktajle Excel. Tweety Excela. Przygody Excela. Pakowane dżemem w pełnym kolorze. Sprawdź to, kup tę książkę. Byłbym naprawdę wdzięczny.
W porządku, dzisiejszy odcinek. WYSZUKAJ.PIONOWO jest niesamowita i to moja ulubiona funkcja, ale są tam osoby nienawidzące funkcji WYSZUKAJ.PIONOWO, które narzekają, że jest kruche z powodu tego trzeciego argumentu, jeśli kształt tabeli WYSZUKAJ.PIONOWO zmieni się, odpowiedzi się zmienią. Jednym obejściem jest zastąpienie tego trzeciego argumentu PODAJANIE, ale rany, wyobraź sobie, że wykonujesz PODAJNIKI dla tysiąca wierszy WYSZUKAJ.PIONOWO. Więc przed wykonaniem WYSZUKAJ.PIONOWO utwórz WYSZUKAJ.PIONOWO w tabeli. Odwołania do tabeli struktury będą obsługiwane, jeśli zmieni się kształt tabeli. Poza tym nie wykonujesz funkcji WYSZUKAJ.PIONOWO i dopasowania. Tylko jedno dopasowanie wraz z INDEKSEM i INDEKSEM jest błyskawiczne, błyskawiczne.
Dziękuję Peterowi Robertowi za tę wskazówkę i dziękuję za zatrzymanie się. Do zobaczenia następnym razem, podczas kolejnego netcastu od.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2003.xlsx
