Typowym zadaniem, które okresowo pojawia się przed każdym użytkownikiem Excela, jest porównanie dwóch zakresów z danymi i znalezienie różnic między nimi. Sposób rozwiązania w tym przypadku zależy od rodzaju danych początkowych.
Opcja 1. Listy synchroniczne
Jeśli listy są zsynchronizowane (posortowane), wszystko odbywa się bardzo prosto, ponieważ w rzeczywistości konieczne jest porównanie wartości w sąsiednich komórkach każdego wiersza. Jako najprostszą opcję używamy formuły do porównywania wartości, która na wyjściu daje wartości logiczne TRUE (PRAWDZIWE) or KŁAMLIWY (FAŁSZYWE):
Liczbę niezgodności można obliczyć według wzoru:
=SUMA ILOCZYNÓW(—(A2:A20<>B2:B20))
lub w języku angielskim =SUMPRODUCT(—(A2:A20<>B2:B20))
Jeśli wynik wynosi zero, listy są identyczne. W przeciwnym razie mają różnice. Formułę należy wpisać jako formułę tablicową, tzn. po wpisaniu formuły w komórce nie należy naciskać Wchodzę, I Ctrl + Shift + Enter.
Jeśli musisz zrobić coś z różnymi komórkami, zrobi to inna szybka metoda: wybierz obie kolumny i naciśnij klawisz F5, następnie w otwartym oknie przycisk Atrakcja (Specjalny) - Różnice linii (Różnice w wierszach). W najnowszych wersjach programu Excel 2007/2010 możesz również użyć przycisku Znajdź i wybierz (Znajdź i wybierz) – Wybór grupy komórek (Przejdź do Specjalne) Zakładka Strona główna (Dom)
Excel podświetli komórki, które różnią się zawartością (według wiersza). Mogą być następnie przetwarzane, na przykład:
- wypełnić kolorem lub jakoś wizualnie sformatować
- wyczyść klawiszem Usuń
- wypełnij wszystko na raz tą samą wartością wpisując ją i naciskając Ctrl + Enter
- usuń wszystkie wiersze z zaznaczonymi komórkami za pomocą polecenia Strona główna — Usuń — Usuń wiersze z arkusza (Strona główna — Usuń — Usuń wiersze)
- itd.
Opcja 2: Potasowane listy
Jeśli listy mają różne rozmiary i nie są posortowane (elementy są w innej kolejności), to musisz iść w drugą stronę.
Najprostszym i najszybszym rozwiązaniem jest umożliwienie kolorowego wyróżniania różnic za pomocą formatowania warunkowego. Wybierz oba zakresy z danymi i wybierz na karcie home – Formatowanie warunkowe – Reguły podświetlania komórek – Zduplikowane wartości:
Jeśli wybierzesz opcję Nawracający, wtedy Excel podświetli dopasowania na naszych listach, jeśli opcja Wyjątkowy – różnice.
Podkreślanie kolorów nie zawsze jest jednak wygodne, zwłaszcza przy dużych stołach. Ponadto, jeśli elementy mogą się powtarzać wewnątrz samych list, ta metoda nie zadziała.
Alternatywnie możesz użyć funkcji COUNTIF (LICZ.JEŻELI) z kategorii Statystyczny, który zlicza ile razy każdy element z drugiej listy występuje w pierwszej:
Otrzymane zero wskazuje na różnice.
I na koniec „akrobacje” – różnice możesz wyświetlić na osobnej liście. Aby to zrobić, będziesz musiał użyć formuły tablicowej:
Wygląda przerażająco, ale wykonuje swoją pracę doskonale 😉
- Zaznacz duplikaty na liście kolorem
- Porównanie dwóch serii z dodatkiem PLEX
- Zakaz wprowadzania zduplikowanych wartości