Jak korzystać z VLOOKUP Excel? Funkcja Excel w programie Excel dla manekinów i nie tylko

Każda osoba zajmująca się liczbami, czy to w celach zawodowych, czy po prostu dla zamiłowania do sztuki, oczywiście, pierwszą rzeczą jest opanowanie kalkulatora. Co dalej? Kolejny z kolei to genialny program do pracy z liczbami - Excel. Prawdopodobnie nie można znać tego programu doskonale, a także doskonale znać matematykę. Istnieją jednak podstawowe funkcje programu, funkcja Excel, wiedząc, że z 90% pewnością możemy mówić o zdolności do pracy z danymi na poziomie pewnego użytkownika. Jedną z najważniejszych funkcji w Excelu jest VLOOKUP, który jest bardzo wszechstronny. Sposób korzystania z funkcji WYSZUKAJ.PIONOWO w programie Excel (dla manekinów) omówiono w artykule. Oczywiście, percepcja teorii nie przyniesie pełnego zrozumienia procesu korzystania z funkcji. Trzeba najpierw ćwiczyć na małych tablicach danych, a następnie na tabelach o dowolnej wielkości.

Istota i cel funkcji CDF w Excelu

Funkcja CDF oszczędza dużo czasu takim specjalistom, jak ekonomiści, finansiści, księgowi, technolodzy. Opis funkcji WYSZUKAJ.PIONOWO w programie Excel i przykład jej zastosowania należy rozpocząć od rozważenia sytuacji, w której często występują eksperci różnych rodzajów. Jeśli istnieją dwie tabele, w których pojawiają się te same definiowalne dane (na przykład nazwy towarów), ale definicje dla nich w różnych tabelach różnią się, na przykład w jednym znajduje się ilość, aw innej cenie. W tym przypadku zadaniem jest połączenie wszystkich tych danych razem i wykonanie odpowiednich obliczeń - w takich sytuacjach na ratunek przychodzi funkcja CDF. Aby zrozumieć, jak działa funkcja WYSZUKAJ.PIONOWO w programie Excel, poniżej przedstawiono dwie tabele. W każdym z nich znajduje się kod produktu. W jednej jest odpowiednia ilość, w drugiej - cena. Do zastąpienia cen w pierwszej tabeli i wykorzystanego CDF.

Ręczna zamiana danych między tabelami to ogromna strata czasu i wysiłku pracownika, błędów nie da się uniknąć. Za pomocą CDF możesz szybko i łatwo znaleźć odpowiednie wartości. Aby poznać sposób korzystania z funkcji WYSZUKAJ.PIONOWO w programie Excel, należy przeanalizować wszystkie argumenty funkcji.

Argumenty funkcji

Aby skorzystać z tej funkcji, należy stać w komórce wyników, wybierz kartę Formuły - "Łącza i tablice" - "CDF" na wstążce. W komórce pojawił się napis "= VLR" Teraz musisz poprawnie wprowadzić argumenty funkcji, możesz to zrobić za pomocą średnika bezpośrednio na pasku formuły, jednak wygodniej jest dla nowicjuszy zrobić to za pomocą okna dialogowego funkcji argumentów.

Funkcja CDF ma cztery argumenty - wartość, której szukasz, tabelę, numer kolumny, wyszukiwanie przedziałów. Trzy pierwsze, zaznaczone pogrubioną czcionką w oknie dialogowym, są wymagane. Wyjaśniamy, czym są argumenty funkcji:

• poszukiwana wartość jest tym, czego należy szukać;
• stół - gdzie szukać;
• numer kolumny - w której kolumnie szukać;
• widok interwału - posortowany.

Argument "poszukiwana wartość"

W pierwszym polu musisz wpisać to, czego szukasz. Może istnieć liczba, tekst, data. Możesz wprowadzić wartość bezwzględną, możesz przypisać odwołanie do komórki z żądaną wartością. Oczekiwana wartość powinna teoretycznie być obecna w obu tabelach. W powyższym przykładzie taka wartość może być kodem produktu. Aby uniknąć błędów, lepiej skorzystać z linku.

Gdy używasz tekstu jako pożądanej wartości, musi on być cytowany. Odwołując się do komórki z tekstem, cytaty nie są potrzebne. Zarejestruj się przy wpisywaniu tekstu nie ma znaczenia. Można użyć wyszukiwania rozmytego, opartego na fragmencie tekstu. Aby to zrobić, w cudzysłowach należy umieścić kawałek tekstu w * znakach.

Najczęstszym problemem podczas korzystania z funkcji jest inny format komórek w zakresach. Jeśli w polu pierwszego argumentu umieszcza się łącze do komórki, w której dane są w formacie tekstowym, a w zakresie wyszukiwania dane będą miały format liczbowy, nie znajdzie dopasowania do zewnętrznej tożsamości wartości CDF.

Konwersja do formatu liczbowego jest łatwa - musisz pomnożyć tablicę przez jeden.

Argument tabeli

Tutaj musisz wprowadzić zakres, w którym funkcja będzie szukać pierwszej wprowadzonej wartości. Zakres dla znalezienia wartości będzie pierwszą kolumną wybranego obszaru. Wartość, którą chcesz znaleźć i umieścić jako wynik, musi znajdować się w kolumnie po prawej stronie niż w kolumnie wyszukiwania meczu. Jest to jedna z głównych wad używania CDF: aby móc z nią pracować, często trzeba przebudować tabelę, aby wymagane dane były po lewej stronie. Zakres można wprowadzić zarówno ręcznie, jak i przez odniesienie. W przykładzie wygląda to tak:

Zakres tutaj jest właściwą tabelą. Wartość poszukiwana w kolumnie po prawej stronie, w której Excel będzie szukać wartości 3187849428, wartość, którą należy znaleźć i zastąpić w wyniku formuły, znajduje się po lewej stronie. Aby uzyskać dokładny wynik, lepiej jest ustalić zakres, wybierając go i naciskając klawisz F4, link do tablicy stanie się absolutny.

Argument "numer kolumny"

W tym miejscu należy umieścić cyfrę, w której kolumna, od lewej, będzie potrzebna do przyjęcia wartości do zastąpienia w wyniku obliczenia. W powyższym przykładzie jest to druga kolumna, w łańcuchu argumentów należy podać liczbę 2. Jeśli pomiędzy kolumnami Kod i Cena pojawi się kolejna kolumna, należy podać liczbę 3 i tak dalej.

Argument podglądu interwału

Wypełnienie tego pola jest opcjonalne, ale może być bardzo ważne. Może występować jedna z dwóch wartości - 1 (prawda) lub 0 (fałsz). Większość użytkowników uważa, że ​​funkcją tego argumentu jest określenie dokładności dopasowania pożądanych wartości. To nie jest całkowicie poprawne. Podczas określania jednostki, jeśli w zakresie tabeli występują powtórzenia, funkcja zwróci ostatnią znalezioną wartość. W takim przypadku funkcja VLR będzie uwzględniać wszystkie wartości mniejsze lub równe wartościom wprowadzonym w polu "Wymagana wartość". Jeśli funkcja znajdzie większą wartość, ale nie znajdzie mniejszej lub równej wartości, wygeneruje błąd N / A.

Widać, że numer 3187849425 wprowadzono w polu "Wartość", nie ma takiej wartości w pożądanym zakresie, a program, znajdując wszystkie wartości mniejsze lub równe żądanemu, zwrócił wartość odpowiadającą ostatniemu odpowiedniemu kodowi na liście - 3187848593, którego cena wynosi 2 479,46 rubli. Jeśli pole widzenia Interwał pozostanie puste, funkcja będzie działać w taki sam sposób, jak wartość jednostki.

Po wprowadzeniu wartości 0 w polu argumentu funkcja zwróci tylko wartość odpowiadającą wymaganemu. Jeśli w zakresie wyszukiwania występuje powtórzenie, funkcja zajmie pierwsze dopasowanie. Przy użyciu formuły z argumentem 0 funkcja działa znacznie dłużej, ale znacznie dokładniej.

Cechy zastosowania CDF pod kilkoma warunkami

Korzystanie z funkcji CDF jest często procesem twórczym, który wymaga myślenia matematycznego od użytkownika. Często istnieje potrzeba znalezienia dopasowania nie dla jednej kolumny, ale dla dwóch lub nawet więcej. Za pomocą dodatkowych akcji można również zastosować funkcję VLOOKUP. Konieczne jest utworzenie dodatkowych kolumn w obu tabelach, w których można łączyć dane z rozważanych kolumn. Można to zrobić za pomocą funkcji "CLUTCH" lub ikony "&".

Jak korzystać z funkcji CDF, jeśli dane znajdują się na różnych arkuszach

Jak korzystać z VLOOKUP w Excelu na różnych arkuszach, w różnych plikach? Często użytkownicy tworzą podręczniki, z którymi należy porównywać i znajdować korespondencje. Różnice w pracy w takich warunkach polegają na nieco innej formie pola "Tabela" w oknie argumentów funkcji. Przed wprowadzeniem zakresu konieczne jest podanie numeru arkusza (jeśli dane znajdują się w jednej książce) lub nazwy pliku (jeśli dane nie znajdują się w jednej książce). Jeśli w powyższym przykładzie cennik zostanie skopiowany do osobnego pliku, nazwij go "Cena", a używając funkcji CDF, poszukaj tam cen, będzie wyglądał tak:

Wszystkie powyższe czynności można wykonać za pomocą linku. Musisz umieścić kursor w polu "Tabela", przejść do żądanego pliku i wybrać zakres za pomocą myszy. Czasami zdarza się, że tabela nie jest wstawiana jako łącze w oknie. Następnie należy wykonać następujące czynności: otworzyć oryginalną tabelę, w której należy wykonać obliczenia, a następnie za pomocą menu "Plik" - "Otwórz" znaleźć drugą tabelę. Otwarte w ten sposób pliki działają sprawnie.

Wszystkie powyższe czynności można wykonać za pomocą linku. Musisz umieścić kursor w polu "Tabela", przejść do żądanego pliku i wybrać zakres za pomocą myszy. Czasami zdarza się, że tabela nie jest wstawiana jako łącze w oknie. Następnie należy wykonać następujące czynności: otworzyć oryginalną tabelę, w której należy wykonać obliczenia, a następnie za pomocą menu "Plik" - "Otwórz" znaleźć drugą tabelę. Otwarte w ten sposób pliki działają sprawnie.

Aby zważyć plik, w którym istnieją formuły z CDF, w odniesieniu do innych plików będzie znacznie więcej niż bez nich. Może to być problematyczne, na przykład przesyłanie plików. Aby uniknąć tych problemów, musisz przekonwertować formułę na wartości. Na wstążce wybierz podmenu "Dane" i polecenie "Zmień komunikację". Nawet jeśli dane nie są pobierane z innego pliku, zawsze warto zastąpić formuły wartościami - to czyni obliczenia bardziej wiarygodnymi.

Lista rozwijana ułatwiająca pracę z CDF

Często funkcja VLR nie działa, jeśli występują drobne rozbieżności danych. To dodatkowe miejsce w tekście, dane wprowadzone z błędami. Aby uniknąć wszystkich tych problemów, użyj listy rozwijanej w programie Excel, aby wprowadzić wartości. Sensowne jest rozpoczynanie pracy z ciągle powtarzającymi się danymi. Jeśli istnieje pewna książka referencyjna, która jest używana jako tabela do porównania, wówczas zakres porównania może zostać przyjęty jako dane dla rozwijanej listy i wykorzystany do utworzenia tabeli, do której dane zostaną następnie wstawione do funkcji CDF. Kursor zostanie umieszczony w komórce, w której należy wprowadzić wartość. Dalej na taśmie znajduje się podsekcja "Dane", wybrana jest komenda "Weryfikacja danych". W oknie dialogowym, w polu "Typ danych" wprowadź wartość "Lista". W polu "Źródło" rejestrowany jest zakres tabeli referencyjnej. Zostanie utworzona lista rozwijana. Teraz przy wypełnianiu tabeli gwarantowana jest pełna zgodność wartości.

Przykład użycia funkcji WYSZUKAJ.PIONOWO w programie Excel

Jak korzystać z VLOOKUP w Excelu, każdy użytkownik rozwiązuje się sam, w oparciu o praktyczne zadania w swojej pracy. Biorąc pod uwagę wszystkie niuanse podane powyżej, można przywrócić poprzedni przykład do logicznego końca i skonstruować wygodny i czytelny algorytm do zastosowania funkcji CDF w praktyce. Tak więc istnieje kosztorys części zamiennych do naprawy sprzętu. Dostępny jest również cennik części zamiennych. Ekonomista firmy musi znaleźć koszt części zamiennych do naprawy.

Najpierw musisz poprawnie napisać formułę.

Tak więc, przy użyciu funkcji WYSZUKAJ.PIONOWO, pojawi się cena odpowiadająca kodowi w pierwszym wierszu. Konieczne jest pomnożenie formuły w dół, aby to zrobić, wybierz komórkę z formułą i pociągnij w dół kwadrat w prawym rogu komórki. W kolumnie D funkcja zwróciła ceny odpowiadające kodom. Konieczne jest zastąpienie formuł wartościami. Aby to zrobić, zaznacz wypełnione komórki w kolumnie D, skopiuj je i wklej jako wartości. Następnie musisz utworzyć kolumnę "Suma", w której podajesz formułę iloczynu ilości i ceny, a następnie użyj podsumowania, aby podsumować koszty.

Jest to przykład działania usługi VLOOKUP w programie Excel.

Błędy podczas korzystania z funkcji CDF

Na początkowym etapie użytkowania, zamiast pożądanych wartości, funkcja często wskazuje różne rodzaje błędów. Wiedzieć, co oznacza błąd, to właściwa droga, aby szybko to naprawić. Najczęściej zgłaszane błędy:

• "N / A" - najczęstszy rodzaj błędu. Może się zdarzyć z kilku powodów.

1. Kolumna, w której funkcja szuka dopasowania, jest niepoprawnie zlokalizowana (musi znajdować się najdalej). Jeśli pojawi się sytuacja, w której pożądana wartość znajduje się po lewej stronie obszaru dopasowania, tabela musi zostać przekonwertowana. Na przykład skopiuj wybraną kolumnę i wklej ją po prawej stronie obszaru wyszukiwania.
2. Błąd "N / A" może zostać zwrócony, jeśli zakres wyszukiwania nie jest ustalony, podczas wyciągania formuły WFD.
3. Jeśli dokładne wyszukiwanie zostanie określone przy użyciu argumentu Interval View (zostanie ustawiona cyfra 0), błąd "N / A" zostanie zwrócony, jeśli nie ma dokładnego dopasowania w dwóch tabelach.
4. Argument Interval Viewing jest ustawiony na najbliższą wartość (1 oznacza, lub pole nie jest wypełnione), a zakres, w którym odbywa się wyszukiwanie, nie jest sortowany. W przypadku niedokładnego wyszukiwania należy posortować kolumnę po lewej stronie zakresu wyszukiwania.
5. Porównywane dane mają dodatkowe spacje (w celu ich usunięcia można użyć funkcji "SIFPROBELS", stosując ją do tabeli i do żądanej wartości), innego formatu, dodatkowych cudzysłowów. Aby zapisać te same wartości w obu tabelach, warto skorzystać z rozwijanej listy.

• "LINK" - ten błąd występuje często, gdy numer kolumny został określony niepoprawnie, jeśli tak wiele kolumn nie znajduje się w wybranym zakresie. W takich przypadkach należy pamiętać, że numer jest odłożony, licząc od lewej kolumny wybranego zakresu, a nie od stołu jako całości.
• "NAME" - błąd jest często zwracany po wpisaniu tekstu do "Wymaganej wartości". Tekst musi być napisany w cudzysłowie.

Wiedza na temat korzystania z funkcji WYSZUKAJ.PIONOWO w programie Excel, przyjdź, podobnie jak wszystko inne w tym programie, z praktyką. Samo studiowanie teorii zamiast jasności prawdopodobnie doprowadzi do chaosu w zrozumieniu konkretnej operacji w programie. Podczas badania dowolnej funkcji programu Excel użytkownik zawsze może polegać na konkretnym, choć niewielkim, przykładowym tablecie. Pozwala to lepiej zrozumieć istotę analizy, utrwalić zdobytą wiedzę. Tak więc z funkcją CDF w Excelu. Przy dużej ilości teorii praktyczne zastosowanie pokazuje, że nie jest tak skomplikowana, jak jest użyteczna.