Ta wikiHow uczy, jak używać formatowania warunkowego do porównywania wszystkich komórek na dwóch oddzielnych listach w arkuszu kalkulacyjnym Excel i oznaczania komórek, które pojawiają się na obu listach. Ta funkcja jest dostępna tylko w klasycznej wersji programu Excel, a aplikacja mobilna jej nie obsługuje.
Kroki
Metoda 1 z 2: Korzystanie z formuły CountIf
Krok 1. Otwórz arkusz kalkulacyjny Excel, który chcesz edytować
Znajdź plik arkusza kalkulacyjnego z listami, które chcesz porównać, i kliknij dwukrotnie plik, aby otworzyć go w programie Microsoft Excel.
Krok 2. Wybierz swoją pierwszą listę
Kliknij pierwszą komórkę na pierwszej liście i przeciągnij myszą do ostatniej komórki na liście, aby zaznaczyć cały zakres danych.
Krok 3. Kliknij kartę Formuły na wstążce paska narzędzi
Tę kartę znajdziesz nad paskiem narzędzi u góry arkusza kalkulacyjnego. Otworzy narzędzia do formuł na wstążce paska narzędzi.
Krok 4. Kliknij Zdefiniuj nazwę na pasku narzędzi
Tę opcję znajdziesz na środku wstążki „Formuły”. Otworzy się nowe wyskakujące okienko i pozwoli ci nazwać swoją listę.
Krok 5. Wpisz List1 w polu Nazwa
Kliknij pole tekstowe u góry wyskakującego okienka i wprowadź tutaj nazwę listy.
- Możesz później użyć tej nazwy, aby wstawić swoją listę do formuły porównania.
- Alternatywnie możesz tutaj nadać swojej liście inną nazwę. Na przykład, jeśli jest to lista lokalizacji, możesz nazwać ją „locations1” lub „locationList”.
Krok 6. Kliknij OK w wyskakującym oknie
To potwierdzi twoje działanie i nazwie twoją listę.
Krok 7. Nazwij swoją drugą listę jako List2
Wykonaj te same czynności, co w przypadku pierwszej listy i nadaj drugiej liście nazwę. Umożliwi to późniejsze szybkie użycie tej drugiej listy w formule porównania.
Możesz nadać liście dowolną nazwę. Pamiętaj, aby zapamiętać lub zanotować nazwę, którą nadajesz każdej ze swoich list tutaj
Krok 8. Wybierz swoją pierwszą listę
Kliknij pierwszą komórkę na pierwszej liście i przeciągnij w dół, aby zaznaczyć cały zakres danych.
Upewnij się, że Twoja pierwsza lista jest wybrana, zanim zaczniesz konfigurować formatowanie warunkowe
Krok 9. Kliknij kartę Strona główna na pasku narzędzi
To jest pierwsza karta w lewym górnym rogu wstążki paska narzędzi. Otworzy podstawowe narzędzia arkusza kalkulacyjnego na pasku narzędzi.
Krok 10. Kliknij Formatowanie warunkowe na pasku narzędzi
Ta opcja wygląda jak mała ikona arkusza kalkulacyjnego z niektórymi komórkami wyróżnionymi na czerwono i niebiesko. Otworzy się rozwijane menu wszystkich opcji formatowania.
Krok 11. Kliknij Nowa reguła w menu rozwijanym
Otworzy się nowe wyskakujące okno i pozwoli ręcznie skonfigurować nową regułę formatowania dla wybranego zakresu.
Krok 12. Wybierz opcję „Użyj formuły, aby określić, które komórki do sformatowania”
Ta opcja pozwoli Ci ręcznie wpisać formułę formatowania w celu porównania dwóch list.
- Na Okna, znajdziesz go na dole listy reguł, w polu „Wybierz typ reguły”.
- Na Prochowiec, Wybierz Klasyczny w menu rozwijanym „Styl” u góry wyskakującego okienka. Następnie znajdź tę opcję w drugim menu rozwijanym poniżej menu Styl.
Krok 13. Kliknij pole formuły w wyskakującym oknie
W tym miejscu możesz wprowadzić dowolną prawidłową formułę programu Excel, aby skonfigurować regułę formatowania warunkowego.
Krok 14. Wpisz =countif(List2, A1)=1 w pasku formuły
Ta formuła przeskanuje twoje dwie listy i zaznaczy wszystkie komórki na pierwszej liście, które pojawiają się również na drugiej liście.
- Zastąp A1 w formule numerem pierwszej komórki na pierwszej liście.
- Na przykład, jeśli pierwszą komórką pierwszej listy jest komórka D5, formuła będzie wyglądać tak: =countif(List2, D5)=1.
- Jeśli nadałeś drugiej liście inną nazwę, pamiętaj, aby zastąpić List2 w formule rzeczywistą nazwą własnej listy.
- Alternatywnie zmień formułę na = countif (List2, A1) = 0, jeśli chcesz oznaczyć komórki, które nie rób pojawiają się na drugiej liście.
Krok 15. Wpisz =countif(List1, B1)=1 w pasku formuły (opcjonalnie)
Jeśli chcesz znaleźć i zaznaczyć komórki na swoim druga listy, która pojawia się również na pierwszej liście, użyj tej formuły zamiast pierwszej.
Zastąp List1 nazwą pierwszej listy, a B1 pierwszą komórką drugiej listy
Krok 16. Wybierz niestandardowy format, aby oznaczyć komórki (opcjonalnie)
Możesz wybrać niestandardowy kolor wypełnienia tła i różne style czcionek, aby oznaczyć komórki znalezione przez formułę.
- Na Okna, Kliknij Format w prawym dolnym rogu wyskakującego okna. Możesz wybrać kolor tła w zakładce "Wypełnienie", a style czcionki w zakładce "Czcionka".
- Na Prochowiec, wybierz gotowe ustawienie formatu w menu rozwijanym „Formatuj za pomocą” u dołu. Możesz także wybrać niestandardowy format tutaj, aby ręcznie wybrać wypełnienie tła i style czcionek.
Krok 17. Kliknij OK w wyskakującym oknie
To potwierdzi i zastosuje twoją formułę porównawczą. Wszystkie komórki na pierwszej liście, które pojawiają się również na drugiej liście, zostaną oznaczone wybranym kolorem i czcionką.
- Na przykład, jeśli wybierzesz jasnoczerwone wypełnienie z ciemnoczerwonym tekstem, wszystkie powtarzające się komórki zmienią kolor na ten na pierwszej liście.
- Jeśli użyjesz drugiej formuły powyżej, formatowanie warunkowe oznaczy powtarzające się komórki na drugiej liście zamiast na pierwszej.
Metoda 2 z 2: Korzystanie z formuły VLookup
Krok 1. Otwórz arkusz kalkulacyjny Excel
Znajdź plik Excel z listami, które chcesz porównać, i kliknij dwukrotnie nazwę pliku lub ikonę, aby otworzyć arkusz kalkulacyjny w programie Microsoft Excel.
Krok 2. Kliknij pustą komórkę obok pierwszego elementu na drugiej liście
Znajdź drugą listę w arkuszu kalkulacyjnym i kliknij pustą komórkę obok pierwszego elementu listy u góry.
- Tutaj możesz wstawić formułę VLookup.
- Możesz też zaznaczyć dowolną pustą komórkę w arkuszu kalkulacyjnym. Ta komórka tylko ułatwi Ci przeglądanie porównania obok drugiej listy.
Krok 3. Wpisz =vlookup(w pustej komórce
Formuła VLookup pozwoli Ci porównać wszystkie elementy na dwóch oddzielnych listach i sprawdzić, czy wartość jest powtarzającą się, czy nową wartością.
Nie zamykaj nawiasów formuły, dopóki formuła nie będzie kompletna
Krok 4. Wybierz pierwszy element z drugiej listy
Bez zamykania nawiasu formuły kliknij pierwszy element na drugiej liście. Spowoduje to wstawienie do formuły pierwszej komórki z drugiej listy.
Krok 5. Wpisz w formule przecinek
Po wybraniu pierwszej komórki z drugiej listy wpisz przecinek w formule. Następnie będziesz mógł wybrać zakres porównania.
Krok 6. Przytrzymaj i wybierz całą pierwszą listę
Spowoduje to wstawienie zakresu komórek z pierwszej listy do drugiej części formuły VLookup.
Umożliwi to przeszukanie pierwszej listy dla wybranego elementu z drugiej listy (pierwszy element na górze drugiej listy) i powrót, jeśli jest to powtarzająca się lub nowa wartość
Krok 7. Wpisz w formule przecinek
Spowoduje to zablokowanie zakresu porównania w Twojej formule.
Krok 8. Wpisz 1 w formule po przecinku
Ta liczba reprezentuje numer indeksu Twojej kolumny. Poprosi formułę VLookup o przeszukanie rzeczywistej kolumny listy zamiast innej kolumny obok niej.
Jeśli chcesz, aby formuła zwracała wartość z kolumny tuż obok pierwszej listy, wpisz 2 tutaj
Krok 9. Wpisz w formule przecinek
Spowoduje to zablokowanie numeru indeksu kolumny (1) w formule VLookup.
Krok 10. Wpisz FALSE w formule
Spowoduje to przeszukanie listy pod kątem dokładnego dopasowania wybranego elementu wyszukiwania (pierwszy element na górze drugiej listy) zamiast dopasowań przybliżonych.
- Zamiast FALSE możesz użyć 0, jest dokładnie tak samo.
- Możesz też wpisać TRUE lub 1, jeśli chcesz wyszukać przybliżone dopasowanie.
Krok 11. Wpisz) na końcu, aby zamknąć formułę
Możesz teraz uruchomić formułę i sprawdzić, czy wybrany element wyszukiwania na drugiej liście jest powtarzającą się lub nową wartością.
Na przykład, jeśli Twoja druga lista zaczyna się od B1, a Twoja pierwsza lista przechodzi z komórek A1 do A5, Twoja formuła będzie wyglądać tak =vlookup(B1, $A$1:$A$5, 1, fałsz).
Krok 12. Naciśnij ↵ Enter lub ⏎ Wróć na klawiaturze.
Spowoduje to uruchomienie formuły i przeszukanie pierwszej listy w poszukiwaniu pierwszego elementu z drugiej listy.
- Jeśli jest to wartość powtarzalna, w komórce z formułą zostanie ponownie wydrukowana ta sama wartość.
- Jeśli jest to nowa wartość, zobaczysz „ #Nie dotyczy ” wydrukowane tutaj.
- Na przykład, jeśli szukasz słowa „Jan” na pierwszej liście, a teraz w komórce z formułą widzisz słowo „Jan”, jest to wartość powtarzalna, która pojawia się na obu listach. Jeśli widzisz „#N/A”, jest to nowa wartość na drugiej liście.
Krok 13. Wybierz komórkę z formułą
Po uruchomieniu formuły i wyświetleniu wyników dla pierwszego elementu listy kliknij komórkę z formułą, aby ją wybrać.
Krok 14. Kliknij i przeciągnij w dół zieloną kropkę w prawym dolnym rogu komórki
Spowoduje to rozwinięcie komórki z formułą na liście i zastosowanie formuły do każdego elementu listy na drugiej liście.
- W ten sposób możesz porównać każdą pozycję na drugiej liście z całą pierwszą listą.
- Spowoduje to przeszukanie pierwszej listy indywidualnie dla każdego elementu na drugiej liście i wyświetlenie wyniku obok każdej komórki osobno.
- Jeśli chcesz zobaczyć inny znacznik dla nowych wartości zamiast „#N/A”, użyj tej formuły: =iferror(vlookup(B1, $A$1:$A$5, 1, false), „Nowa wartość”). Spowoduje to wydrukowanie „Nowej wartości” dla nowych wartości zamiast „#N/A”.
