Z pewnością wielu aktywnych użytkowników edytora arkuszy kalkulacyjnych Excel czasami musiało radzić sobie z sytuacjami, w których konieczne było zastępowanie wartości z jednej tabeli na drugą. Wyobraź sobie, że do twojego magazynu trafił pewien przedmiot. Mamy do dyspozycji dwa pliki: jeden z listą nazw otrzymanych towarów, drugi - cennikiem tego produktu. Po otwarciu cennika okazało się, że jest w nim więcej pozycji i nie ma ich w sekwencji, która znajduje się w pliku z listą nazw. Rzadko ktokolwiek z nas spodoba się pomysłowi ręcznego sprawdzenia zarówno plików, jak i cen transferowych z jednego dokumentu do drugiego. Oczywiście w przypadku, gdy chodzi o 5-10 pozycji, mechaniczne wprowadzanie danych jest całkiem możliwe, ale co jeśli liczba nazw przekracza 1000? W tym przypadku Excel i jego magiczna funkcja CDF (lub przeglądanie, jeśli mówimy o angielskiej wersji programu) pomogą nam poradzić sobie z monotonną pracą.
VPR pozwala pracować z dużymi tabelami, przesyłając dane z jednego do drugiego
Treść
- 1 Co to jest CDF i jak z niego korzystać?
- 2 Ważna uwaga
- 3 Jak porównać dwie tabele w programie Excel?
- 4 Umiejętność pracy z kilkoma warunkami
Co to jest CDF i jak z niego korzystać?
Tak więc, na początku naszej pracy nad konwersją danych z jednej tabeli na drugą, należy dokonać niewielkiego przeglądu funkcji CDF. Jak już zapewne zrozumiałeś, vlookup umożliwia przesyłanie danych z jednej tabeli do drugiej, dzięki czemu automatycznie wypełniasz potrzebne komórki. Aby funkcja CDF działała poprawnie, zwróć uwagę na obecność scalonych komórek w nagłówkach twojego stołu. Jeśli tak, będziesz musiał je złamać.
Załóżmy, że musimy wypełnić "Tabele zamówień" danymi z "Cennika"
Tak więc, stoimy przed zadaniem - przenieść ceny istniejących towarów do tabeli z ich nazwami i obliczyć całkowity koszt każdego produktu. Aby to zrobić, musimy wykonać następujący algorytm:
- Najpierw przenieś arkusz kalkulacyjny Excel do żądanej postaci. Dodaj do zebranej matrycy danych dwie kolumny o nazwach "Cena" i "Koszt". Wybierz dla komórek w zakresie nowo utworzonych kolumn, format pieniężny.
- Teraz aktywuj pierwszą komórkę w bloku "Cena" i wywołaj "Kreator funkcji". Można to zrobić, naciskając przycisk "fx" znajdujący się przed linią formuły lub przytrzymując kombinację klawiszy "Shift + F3". W otwartym oknie dialogowym znajdź kategorię "Łącza i tablice".Tutaj nie interesuje nas nic poza funkcją CDF. Wybierz i kliknij "OK". Nawiasem mówiąc, należy stwierdzić, że funkcję VLOOKUP można wywołać za pomocą zakładki "Formulas", z rozwijanej listy, w której znajduje się także kategoria "Łącza i tablice".
- Po aktywacji CDF zobaczysz okno z listą argumentów wybranej funkcji. W polu "Wymagana wartość" należy wprowadzić zakres danych zawartych w pierwszej kolumnie tabeli wraz z listą otrzymanych towarów i ich ilością. Oznacza to, że musisz powiedzieć programowi Excel, co dokładnie powinien znaleźć w drugiej tabeli i przenieść do pierwszego.
- Po zaznaczeniu pierwszego argumentu możesz przejść do drugiego. W naszym przypadku drugim argumentem jest tabela z cennikiem. Umieść kursor myszy w polu argumentu i przejdź do listy cen. Ręcznie wybierz zakres z komórkami znajdującymi się w obszarze kolumn z nazwami produktów komercyjnych i ich ceną. Określ Excel, które wartości chcesz mapować funkcje VLOOKUP.
- Aby program Excel nie był zdezorientowany i odnosił się do potrzebnych danych, ważne jest, aby naprawić podany link. Aby to zrobić, podświetl wymagane wartości w polu Tabela i naciśnij klawisz F4. Jeśli wszystko zostanie wykonane poprawnie, znak $ powinien pojawić się na ekranie.
- Teraz przechodzimy do pola argumentu Numer strony i ustawiamy go na wartość "2". W tym bloku znajdują się wszystkie dane, które chcesz przesłać do naszej tabeli roboczej, dlatego ważne jest przypisanie fałszywej wartości do widoku przedziału (ustaw wartość FALSE). Jest to konieczne, aby funkcja CDF działała tylko z dokładnymi wartościami i nie zaokrąglała ich.
Dodaj formułę do pierwszej kolumny.
Po wykonaniu wszystkich niezbędnych czynności, możemy je potwierdzić, naciskając przycisk "OK". Jak tylko dane w pierwszej komórce ulegną zmianie, będziemy musieli zastosować funkcję CDF do całego dokumentu Excel. Aby to zrobić, po prostu propaguj VLOOKUP wokół całej kolumny "Cena". Można to zrobić, przeciągając dolny prawy róg komórki ze zmodyfikowaną wartością na sam dół kolumny. Jeśli wszystko się ułoży, a dane ulegną zmianie w miarę potrzeb, możemy przystąpić do obliczenia całkowitej wartości naszych towarów. Aby wykonać to działanie, musimy znaleźć produkt dwóch kolumn - "Ilości" i "Ceny". Ponieważ program Excel zawiera wszystkie formuły matematyczne, obliczenia można wprowadzić w "Pasku formuły" za pomocą znanej ikony "fx".
Ważna uwaga
Wydaje się, że wszystko jest gotowe, a VLOOKUP poradził sobie z naszym zadaniem, ale go nie było.Faktem jest, że w kolumnie "Cena" funkcja CDF jest nadal aktywna, dowodem tego faktu jest jej wyświetlenie na pasku formuły. Oznacza to, że oba nasze stoły pozostają ze sobą połączone. Taki tandem może prowadzić do tego, że gdy dane w tabeli z ceną ulegną zmianie, informacje zawarte w naszej roboczej dokumentacji z listą towarów również ulegną zmianie.
Tej sytuacji najlepiej uniknąć, dzieląc dwie tabele. Aby to zrobić, musimy wybrać komórki znajdujące się w zakresie kolumny "Cena" i kliknąć je prawym przyciskiem myszy. W oknie, które zostanie otwarte, wybierz i aktywuj opcję "Kopiuj". Następnie, bez usuwania zaznaczenia z zaznaczonego obszaru komórki, ponownie naciśnij prawy przycisk myszy i wybierz opcję "Wklej specjalnie".
Aktywacja tej opcji spowoduje otwarcie okna dialogowego, w którym należy zaznaczyć pole obok kategorii "Wartość". Potwierdź swoje działania, klikając przycisk "OK".
Powracamy do naszego paska formuły i sprawdzamy obecność w kolumnie Cena aktywnej funkcji VLOOKUP. Jeśli zamiast formuły widzisz tylko wartości liczbowe, wszystko się wyłączyło, a funkcja CDF jest wyłączona.Oznacza to, że połączenie między dwoma plikami Excela jest zepsute i nie ma zagrożenia nieplanowanymi zmianami lub usunięciem danych dołączonych do tabeli z cennikiem. Teraz możesz bezpiecznie korzystać z dokumentu tabelarycznego i nie martwić się o to, co stanie się, gdy Cennik zostanie zamknięty lub przeniesiony do innej lokalizacji.
Przeciągnij znacznik w prawym dolnym rogu komórki, aby zastosować formułę do całej tabeli.
Jak porównać dwie tabele w programie Excel?
Za pomocą funkcji CDF możesz porównać kilka różnych wartości w ciągu kilku sekund, na przykład, aby porównać zmiany cen istniejących produktów. Aby to zrobić, musisz zarejestrować VLOOKUP w pustej kolumnie i połączyć funkcję ze zmienionymi wartościami, które znajdują się w innej tabeli. Najlepszy ze wszystkich, jeśli kolumna "Nowa cena" zostanie zlokalizowana zaraz po kolumnie "Cena". Taka decyzja pozwoli ci uwidocznić zmiany cen dla porównania.
Umiejętność pracy z kilkoma warunkami
Kolejną niewątpliwą zaletą funkcji VLOOKUP jest możliwość pracy z kilkoma parametrami związanymi z twoim produktem. Aby znaleźć produkt według dwóch lub więcej cech, musisz:
- Utwórz dwa (lub, jeśli to konieczne, więcej) warunki wyszukiwania.
- Dodaj nową kolumnę, do której w trakcie tej funkcji zostaną dodane wszystkie pozostałe kolumny, za pomocą których zostanie przeszukany produkt.
- W wynikowej kolumnie, zgodnie z algorytmem opisanym powyżej, wprowadzamy formułę funkcji VLOOKUP, która jest już nam znana.
Podsumowując, należy stwierdzić, że utrzymanie Excela w takiej funkcji jak CDF znacznie upraszcza pracę z informacjami tabelarycznymi. Nie bój się używać VLOOKUP w pracy z ogromną ilością danych, ponieważ niezależnie od tego, jak są wykonywane, zasada działania jest zawsze taka sama. Wszystko, co musisz zrobić, to poprawnie zdefiniować jego argumenty.
