Dość często użytkownicy Excel mają zadanie porównywania dwóch tabel lub list do identyfikacji różnic w nich lub brakujących elementów. Każdy użytkownik radzi sobie z tym zadaniem na swój sposób, ale najczęściej raczej duża ilość czasu przeznaczona jest na rozwiązanie określonego pytania, ponieważ nie wszystkie podejścia do tego problemu są racjonalne. Jednocześnie istnieje kilka udowodnionych algorytmów działań, które pozwolą porównać listy lub tablice tabeli w dość krótkim czasie przy minimalnym znacznym wysiłku. Rozważmy szczegóły tych opcji.
W ten sam sposób można porównać dane w tabelach znajdujących się na różnych arkuszach. Ale w tym przypadku pożądane jest, aby linie w nich są ponumerowane. Reszta procedury porównania jest prawie taka sama jak opisana powyżej, oprócz tego, że przy składaniu formuły konieczne jest przełączenie między arkuszami. W naszym przypadku wyrażenie będzie miała następującą formę:
= B2 = lista2! B2
Oznacza to, że widzimy przed współrzędnymi danymi, które znajdują się na innych arkuszach innych niż w przypadku wyświetlania wyniku porównania, numer arkuszy i wykrzyknik jest wskazany.
Metoda 2: Wybór grup komórek
Porównanie można wykonać za pomocą narzędzia do oddzielenia grup komórek. Dzięki nim możesz również porównać tylko zsynchronizowane i zamówione listy. Ponadto w tym przypadku listy muszą znajdować się obok siebie na jednym arkuszu.
- Wybierz porównywane tablice. Przejdź do zakładki "Home". Następnie kliknij ikonę "Znajdź i wybierz", który znajduje się na taśmie w pasku narzędzi Edycja. Istnieje lista, w której należy wybrać pozycję "Wybór grupy komórek ...".
Ponadto, wybór grupy komórek można osiągnąć w inny sposób. Ta opcja będzie szczególnie przydatna dla tych użytkowników, którzy mają wersję programu wcześniej niż Excel 2007, ponieważ metoda za pomocą przycisku "Znajdź i wybierana" nie obsługuje tych aplikacji. Wybierz tablice, które chcą porównać i kliknąć klawisz F5.
- Aktywowane jest małe okno przejściowe. Kliknij przycisk "Wyróżnij ..." w lewym dolnym rogu.
- Po tym, niezależnie od wybranych powyżej opcji, zostanie uruchomione okno wyboru grup komórkowych. Zainstaluj przełącznik do pozycji "Highlight on Line". Kliknij przycisk "OK".
- Jak widać, po tym, że nie niespójne wartości linii zostaną podświetlone z innym odcieniem. Ponadto, jak można ocenić od zawartości linii formuły, program dokonuje aktywnej jednej z komórek w wskazanych liniach bez zbiegłych.
Metoda 3: Formatowanie warunkowe
Możesz dokonać porównania, stosując metodę formatowania warunkowego. Podobnie jak w poprzedniej metodzie, porównywane obszary powinny znajdować się na pojedynczym arkusz pracy i zsynchronizować ze sobą.
- Po pierwsze, wybieramy, jaki region tabelaryczny zostanie uznany za główny, a co szukać różnicy. Zróbmy w drugiej stole. Dlatego przydzielimy w nim listę pracowników. Przeprowadzka do zakładki "Home", kliknij przycisk "Warunkowane formatowanie", który ma lokalizację na taśmie w bloku "Styl". Z listy rozwijanej przejdź przez element "Zarządzanie regułami".
- Okno Dyspozytora Reguły jest aktywowane. Kliknij go do przycisku "Utwórz regułę".
- W Uruchomionym oknie wybieramy pozycję "Użyj formuły". W polu "Format Cell" wpisz formułę zawierającą adresy pierwszych komórek zakresów porównanych kolumn, oddzielonych znakiem "nie równy" (). Tuż przed tym wyrażeniem tym razem stanie się znak "=". Ponadto do wszystkich współrzędnych kolumn w tej formule musisz zastosować bezwzględny adresowanie. Aby to zrobić, przydzielamy formułę z kursorem i kliknij klawisz F4 trzy razy. Jak widać, znak dolara pojawił się w pobliżu wszystkich adresów kolumn, co oznacza obracanie linków do absolutnego. Dla naszego konkretnego przypadku formuła zajmie następującą formę:
= $ A2 $ D2
Nagrywamy to wyrażenie w powyższym polu. Następnie klikamy przycisk "Formatuj ...".
- Okno "Format komórkowy" jest aktywowany. Idziemy do karty "Wypełnić". Tutaj na liście kolorów zatrzymaj wybór koloru, który chcemy malować te elementy, w których dane nie będą się zgadzać. Kliknij przycisk "OK".
- Wracając do okna reguły formatowania, kliknij przycisk "OK".
- Po automatycznym przejście do okna "Menedżer reguł" kliknij przycisk "OK" iw nim.
- Teraz w drugiej tabeli elementy, które mają dane niezgodne z odpowiednimi wartościami pierwszej tabeli, zostaną podświetlone w wybranym kolorze.
Istnieje inny sposób zastosowania formatowania warunkowego, aby wykonać zadanie. Podobnie jak poprzednie opcje, wymaga lokalizacji obu porównywanych obszarów na jednym arkuszu, ale w przeciwieństwie do poprzednio opisanych metod, stan synchronizacji lub danych sortowania nie będą obowiązkowe, które odróżniają tę opcję z opisanych wcześniej.
- Produkujemy wybór obszarów, których potrzebujesz, aby porównać.
- Przeprowadzamy przejście do karty "Home". Klikamy przycisku "Formatowanie warunkowe". Na liście aktywowanej wybierz zasady pozycji "dla alokacji komórek". W następnym menu wykonaj pozycję "powtarzające się wartości".
- Uruchomione są duplikaty wartości. Jeśli wszyscy wykonujesz poprawnie, w tym oknie pozostaje tylko do kliknięcia przycisku "OK". Chociaż, jeśli chcesz, w odpowiednim polu tego okna możesz wybrać inny kolor wyboru.
- Po wyprodukowaniu określonej działalności wszystkie powtarzalne elementy zostaną podświetlone w wybranym kolorze. Te elementy, które nie pokrywają pozostaną malowane w oryginalnym kolorze (domyślnie biały). W ten sposób można natychmiast zobaczyć wizualnie, aby zobaczyć, jaka różnica między tablicami.
Jeśli chcesz, wręcz przeciwnie, pomaluj niespójne elementy, a wskaźniki pasujące, pozostawiają wypełnienie poprzedniego koloru. Jednocześnie algorytm akcji jest prawie taki sam, ale w ustawieniach zduplikowanych wartości w pierwszym polu, zamiast parametru "powtarzanego", wybierz parametr "Unikalny". Po tym kliknięciu przycisku "OK".
W ten sposób jest to właśnie te wskaźniki, które nie pokrywają się.
Lekcja: Formatowanie warunkowe w Excelu
Metoda 4: Kompleksowa formuła
Porównaj również dane za pomocą złożonej formuły, której podstawą jest funkcja miernika. Korzystając z tego narzędzia, możesz obliczyć, ile każdy element z wybranej kolumny jest powtarzany w pierwszej kolejności.
Rady operatora odnosi się do grupy statystycznej funkcji. Jego zadaniem jest policzenie liczby komórek, wartości, w których spełniają określony stan. Składnia tego operatora ma ten rodzaj:
= Harmonogramy (zakres; kryterium)
Argument "Zakres" to adres tablicy, który oblicza wartości zbieżności.
Argument "kryteria" określa stan zbiegu okoliczności. W naszym przypadku będzie współrzędnymi konkretnych komórek pierwszego obszaru tabelarycznego.
- Przydzielymy pierwszy element dodatkowej kolumny, w której zostanie obliczona liczba meczów. Następnie kliknij ikonę "Wstaw funkcję".
- Rozpoczyna się kreator funkcji. Idź do kategorii "Statystyczny". Znajdź na liście nazwa "Coverles". Po jego wyborze kliknij przycisk "OK".
- Okno argumenty operatora rozpoczęło działanie. Jak widzimy, nazwy pól w tym oknie odpowiadają nazwom argumentów.
Zainstaluj kursor w polu "Zakres". Po tym, przytrzymując lewy przycisk myszy, przydziel wszystkie wartości kolumny z nazwami drugiej tabeli. Jak widać, współrzędne natychmiast należą do określonego pola. Ale dla naszych celów adres ten powinien być wykonany przez absolutny. Aby to zrobić, zaznacz współrzędne w polu i kliknij klawisz F4.
Jak widać, odniesienie wzięło formularz bezwzględny, który charakteryzuje się obecnością znaków dolara.
Następnie przejdź do pola "Kryterium", instalując kursor. Kliknij pierwszy element z nazwiskami w pierwszym zakresie tabelarycznym. W tym przypadku pozostaw względną link. Po wyświetleniu w polu możesz kliknąć przycisk "OK".
- Wynik jest wytwarzany do elementu arkusza. Jest równy numerowi "1". Oznacza to, że na liście nazw drugiego stołu nazwa "Grinev V. P.", która jest pierwsza na liście pierwszej tablicy tablicy, wystąpi raz.
- Teraz musimy stworzyć podobny wyraz i dla wszystkich innych elementów pierwszej tabeli. Aby to zrobić, wykonam kopiowanie, używając markera napełniającego, jak już wcześniej zrobiliśmy. Umieściliśmy kursor do prawej dolnej części elementu liściowego, który zawiera funkcję miernika, a po przekształceniu go do markera napełniającego, zacisnąć lewy przycisk myszy i pociągnij kursor w dół.
- Jak widać, program dokonał obliczeń zbiegów, porównujących każdą komórkę pierwszej tabeli z danymi, które znajdują się w drugim zakresie tabeli. W czterech przypadkach wynik był "1", aw dwóch przypadkach - "0". Oznacza to, że program nie mógł znaleźć dwóch wartości w drugiej tabeli dostępnej w pierwszej tablicy tabeli.
Oczywiście, wyrażenie w celu porównania wskaźników tabeli, możliwe jest zastosowanie w istniejącej formie, ale można go poprawić.
Dokonamy, że wartości istniejące w drugiej tabeli, ale nie są dostępne w pierwszej kolejności, były wyświetlane jako osobna lista.
- Przede wszystkim recykling naszą formułę, ale uczynimy go jednym z argumentów operatora, jeśli. Aby to zrobić, podkreślamy pierwszą komórkę, w której operator jest operatorem. W strumieniu formuły przed nim dodamy wyrażenie "Jeśli" bez cytatów i otwórz wspornik. Ponadto, że łatwiej było nam pracować, przydzielić formuły w wierszu Formuły i kliknij ikonę "Wstaw funkcję".
- Okno argumentów funkcji otwiera się, jeśli. Jak widać, pierwsze okno okna zostało już wypełnione wyceną Rady Operatora. Ale musimy dodać coś innego w tej dziedzinie. Ustawiamy tam kursor i już istniejące wyrażenie dodać "= 0" bez cytatów.
Po tym przejdź do "znaczenia, jeśli prawda". Tutaj użyjemy kolejnej zagnieżdżonej funkcji - linia. Wprowadź słowo "ciąg" bez cytaty, a następnie otwórz wsporniki i wskazuj współrzędne pierwszej komórki z nazwiskiem w drugiej tabeli, po czym zamkniemy wsporniki. W szczególności w naszym przypadku w polu "Znaczenie, jeśli prawda" wydarzyło się następujące wyrażenie:
Wiersz (D2)
Teraz ciąg operatora będzie raportować funkcje, jeśli numer linii, w którym znajduje się określony nazwisko, aw przypadku, gdy zostanie wykonany stan określony w pierwszym polu, funkcja, jeśli zostanie wyświetlona liczba. Kliknij przycisk "OK".
- Jak widać, pierwszy wynik jest wyświetlany jako "kłamstwa". Oznacza to, że wartość nie spełnia warunków operatora, jeśli. Oznacza to, że pierwsze nazwisko jest obecne na obu listach.
- Korzystanie z markera napełniającego, który jest już znany do skopiowania ekspresji operatora, jeśli w całej kolumnie. Jak widzimy, w dwóch pozycjach, które są obecne w drugiej tabeli, ale nie są pierwsze, formuła wydaje numery.
- Wycofamy się od obszaru tabelowego po prawej stronie i wypełnij kolumnę liczbą w kolejności, począwszy od 1. Liczba liczb musi pasować do liczby wierszy w drugim skomponowanym stole. Aby przyspieszyć procedurę numeracji, możesz również użyć markera wypełnienia.
- Następnie zaznaczamy pierwszą komórkę po prawej stronie mówcy z numerami i kliknij ikonę "Wstaw funkcję".
- Kreator otwiera się. Przejdź do kategorii "Statystyczny" i wytwarzaj nazwę "najmniejszej" nazwy. Kliknij przycisk "OK".
- Funkcja jest najmniejsza, których ujawniono argumenty argumentów, ma wycofać podaną najmniejszą wartość.
W polu "Array" należy określić współrzędne zakresu opcjonalnej kolumny "Liczba dopasowań", które wcześniej przekształciliśmy za pomocą funkcji, jeśli. Tworzymy wszystkie linki bezwzględne.
W polu "K" wskazano, co na koncie należy wyświetlić najmniejszą wartość. Tutaj wskazujemy współrzędne pierwszej komórki kolumny z numerowaniem, które ostatnio dodaliśmy. Względny urlop adresowy. Kliknij przycisk "OK".
- Operator wyświetla wynik - numer 3. Jest to właśnie najmniejsza z numeracji niespójnych linii tablic tabel. Korzystanie z markera napełniającego, skopiuj formułę do samego nosa.
- Teraz, znając liczbę rzędów niezależnych elementów, możemy wstawić do komórki i ich wartości za pomocą funkcji indeksu. Wybierz pierwszy element arkusza zawierający formułę jest najmniejszy. Następnie przejdź do formuł linii i przed nazwą "najmniejszego" dodawania nazwy "indeks" bez cytatów, natychmiast otwórz wspornik i umieścić punktem z przecinkiem (;). Następnie przydzielamy nazwę formuły "indeks" w linii i kliknij ikonę "Funkcja Wklej".
- Następnie otwiera się małe okno, w którym konieczne jest określenie, czy widok referencyjny powinien mieć funkcję indeksu lub zaprojektowany do pracy z tablicami. Potrzebujemy drugiej opcji. Jest domyślnie instalowany, więc w tym oknie po prostu kliknij przycisk "OK".
- Okno argumentu działa funkcję indeksu. Ten operator jest przeznaczony do wyjścia wartości, która znajduje się w określonej tablicy w określonej linii.
Jak widać, pole "Numer wiersza" jest już wypełniony wartościami znaczenia najmniejszej funkcji. Z istniejącej wartości, różnica między numerowaniem arkusza Excel a wewnętrzną numerowanie regionu tabelarycznego. Jak widzimy, mamy tylko kapelusz na wartościach tabeli. Oznacza to, że różnica jest jedną linią. Dlatego dodaj "-1" bez cytatów w polu "Numer wiersza".
W polu "Array" określ adres wartości drugiej wartości tabeli. Jednocześnie wszystkie współrzędne są absolutne, czyli, umieściliśmy znak dolara przed opisanym przez nas metodą.
Kliknij przycisk "OK".
- Po wycofaniu wynik na ekranie rozciąga funkcję za pomocą markera wypełnienia do końca kolumny w dół. Jak widzimy, oba nazwy, które są obecne w drugiej tabeli, ale nie są dostępne w pierwszej kolejności, są usuwane w oddzielnym zakresie.
Metoda 5: Porównanie tablic w różnych książkach
Porównując zakresy w różnych książkach, można użyć powyższych metod, z wyłączeniem tych opcji, w których wymagane jest umieszczenie obu obszarów tabelarycznych na jednym arkuszu. Głównym warunkiem procedury porównania w tym przypadku jest otwarcie okien obu plików w tym samym czasie. W przypadku wersji programu Excel 2013, a później, a także dla wersji do Excel 2007, nie ma problemów z realizacją tego stanu. Ale w Excel 2007 i Excel 2010 w celu otwarcia obu okien w tym samym czasie, wymagane są dodatkowe manipulacje. Jak to zrobić, mówi w oddzielnej lekcji.
Lekcja: Jak otworzyć Excel w różnych oknach
Jak widać, istnieje wiele możliwości porównania stołów ze sobą. Jakiego rodzaju opcji używać zależy od tego, gdzie znajdują się dane tabeli względem siebie (na jednym arkuszu, w różnych książkach, na różnych arkuszach), a także od użytkownika chce, aby to porównanie było wyświetlane na ekranie.