Spis treści
Mamy dwie tabele (np. stara i nowa wersja cennika), które musimy porównać i szybko znaleźć różnice:
Od razu widać, że coś zostało dodane do nowego cennika (daty, czosnek…), coś zniknęło (jeżyny, maliny…), zmieniły się ceny niektórych towarów (figi, melony…). Musisz szybko znaleźć i wyświetlić wszystkie te zmiany.
Dla każdego zadania w Excelu prawie zawsze jest więcej niż jedno rozwiązanie (zwykle 4-5). Dla naszego problemu można zastosować wiele różnych podejść:
- funkcjonować VPR (WYSZUKAJ.PIONOWO) — wyszukaj nazwy produktów z nowego cennika w starym i wyświetl starą cenę obok nowej, a następnie złap różnice
- połącz dwie listy w jedną, a następnie zbuduj na jej podstawie tabelę przestawną, w której różnice będą wyraźnie widoczne
- użyj dodatku Power Query dla programu Excel
Zbierzmy je wszystkie w kolejności.
Metoda 1. Porównanie tabel za pomocą funkcji WYSZUKAJ.PIONOWO
Jeśli zupełnie nie znasz tej wspaniałej funkcji, najpierw zajrzyj tutaj i przeczytaj lub obejrzyj samouczek wideo – oszczędź sobie kilka lat życia.
Zazwyczaj ta funkcja służy do ściągania danych z jednej tabeli do drugiej przez dopasowanie jakiegoś wspólnego parametru. W tym przypadku użyjemy go do przeniesienia starych cen do nowej ceny:
Produkty, przy których wystąpił błąd #N/A, nie znajdują się na starej liście, czyli zostały dodane. Wyraźnie widoczne są również zmiany cen.
ZALETY ta metoda: prosta i przejrzysta, „klasyka gatunku”, jak mówią. Działa w dowolnej wersji programu Excel.
Wady jest też tam. Aby wyszukać produkty dodane do nowego cennika, będziesz musiał wykonać tę samą procedurę w odwrotnym kierunku, tj. podciągnąć nowe ceny do starej ceny za pomocą funkcji WYSZUKAJ.PIONOWO. Jeśli jutro zmienią się rozmiary tabel, trzeba będzie dostosować formuły. No i na naprawdę dużych stołach (>100 tys. wierszy) całe to szczęście przyzwoicie zwolni.
Metoda 2: Porównywanie tabel za pomocą elementu przestawnego
Skopiujmy nasze tabele jedna pod drugą, dodając kolumnę z nazwą cennika, aby później można było zrozumieć z której listy, który wiersz:
Teraz na podstawie utworzonej tabeli stworzymy podsumowanie poprzez Wstaw – tabela przestawna (Wstaw — tabela przestawna). Rzućmy pole Produkt do obszaru linii, pola Cena do obszaru kolumny i pola Цena w zakresie:
Jak widać, tabela przestawna automatycznie wygeneruje ogólną listę wszystkich produktów ze starego i nowego cennika (bez powtórzeń!) i posortuje produkty alfabetycznie. Możesz wyraźnie zobaczyć dodane produkty (nie mają starej ceny), usunięte produkty (nie mają nowej ceny) oraz ewentualne zmiany cen.
Sumy końcowe w takiej tabeli nie mają sensu i można je wyłączyć w zakładce Konstruktor – Sumy całkowite – Wyłącz dla wierszy i kolumn (Projekt — Sumy końcowe).
Jeśli zmienią się ceny (ale nie ilość towaru!), wystarczy po prostu zaktualizować utworzone zestawienie, klikając je prawym przyciskiem myszy – Odśwież kod.
ZALETY: To podejście jest o rząd wielkości szybsze w przypadku dużych tabel niż WYSZUKAJ.PIONOWO.
Wady: trzeba ręcznie skopiować dane pod siebie i dodać kolumnę z nazwą cennika. Jeśli zmienią się rozmiary stołów, musisz zrobić wszystko od nowa.
Metoda 3: porównywanie tabel za pomocą Power Query
Power Query to bezpłatny dodatek do programu Microsoft Excel, który umożliwia ładowanie danych do programu Excel z niemal dowolnego źródła, a następnie przekształcanie tych danych w dowolny sposób. W programie Excel 2016 ten dodatek jest już domyślnie wbudowany na karcie Dane (Dane), a dla Excela 2010-2013 musisz pobrać go oddzielnie ze strony Microsoftu i zainstalować – uzyskaj nową kartę Zapytanie o moc.
Przed załadowaniem naszych cenników do dodatku Power Query należy je najpierw przekonwertować na inteligentne tabele. Aby to zrobić, wybierz zakres z danymi i naciśnij kombinację na klawiaturze Ctrl+T lub wybierz zakładkę na wstążce Strona główna – Formatuj jako tabelę (Strona główna — Formatuj jako tabelę). Nazwy utworzonych tabel można poprawić na zakładce Konstruktor (zostawię standard Tabela 1 и Tabela 2, które są uzyskiwane domyślnie).
Załaduj starą cenę w Power Query za pomocą przycisku Z tabeli/zakresu (z tabeli/zakresu) z zakładki Dane (Data) lub z zakładki Zapytanie o moc (w zależności od wersji Excela). Po załadowaniu wrócimy do Excela z Power Query za pomocą polecenia Zamknij i załaduj – Zamknij i załaduj w… (Zamknij i załaduj — Zamknij i załaduj do…):
… i w wyświetlonym oknie wybierz Po prostu stwórz połączenie (Tylko połączenie).
Powtórz to samo z nowym cennikiem.
Teraz utwórzmy trzecie zapytanie, które połączy i porówna dane z dwóch poprzednich. Aby to zrobić, wybierz w Excelu na karcie Dane – Uzyskaj dane – Połącz żądania – Połącz (Dane — Pobierz dane — Scal zapytania — Scal) lub naciśnij przycisk Połączyć (Łączyć) Zakładka Zapytanie o moc.
W oknie łączenia wybierz nasze tabele z list rozwijanych, wybierz kolumny z nazwami w nich towarów, a na dole ustaw metodę łączenia – Kompletny zewnętrzny (Pełna zewnętrzna):
Po kliknięciu OK powinna pojawić się tabela z trzema kolumnami, gdzie w trzeciej kolumnie należy rozwinąć zawartość zagnieżdżonych tabel za pomocą podwójnej strzałki w nagłówku:
W rezultacie otrzymujemy połączenie danych z obu tabel:
Lepiej oczywiście zmienić nazwy kolumn w nagłówku, klikając dwukrotnie bardziej zrozumiałe:
A teraz najciekawsze. Przejdź do zakładki Dodaj kolumnę (Dodaj kolumnę) i kliknij przycisk Kolumna warunkowa (Kolumna warunkowa). A następnie w oknie, które się otworzy, wprowadź kilka warunków testowych z odpowiadającymi im wartościami wyjściowymi:
Pozostaje kliknąć OK i prześlij wynikowy raport do Excela za pomocą tego samego przycisku zamknij i pobierz (Zamknij i załaduj) Zakładka Strona główna (Dom):
Piękno.
Co więcej, jeśli w przyszłości nastąpią jakiekolwiek zmiany w cennikach (dodanie lub usunięcie wierszy, zmiana cen itp.), to wystarczy tylko zaktualizować nasze zapytania za pomocą skrótu klawiaturowego Ctrl+inny+F5 lub przyciskiem Odśwież wszystko (Odśwież wszystko) Zakładka Dane (Data).
ZALETY: Być może najpiękniejszy i najwygodniejszy sposób ze wszystkich. Działa elegancko z dużymi stołami. Nie wymaga ręcznej edycji podczas zmiany rozmiaru tabel.
Wady: wymaga zainstalowania dodatku Power Query (w programie Excel 2010–2013) lub programu Excel 2016. Nazwy kolumn w danych źródłowych nie mogą być zmieniane, w przeciwnym razie otrzymamy błąd „Nie znaleziono kolumny takiej i takiej!” podczas próby aktualizacji zapytania.
- Jak zbierać dane ze wszystkich plików Excela w danym folderze za pomocą Power Query
- Jak znaleźć dopasowania między dwiema listami w programie Excel?
- Scalanie dwóch list bez duplikatów