Tutorial pokazuje, jak porównywać ciągi tekstowe w Excelu dla rozróżniania wielkości liter i dokładnego dopasowania. Poznasz kilka formuł do porównywania dwóch komórek według ich wartości, długości ciągu lub liczby wystąpień określonego znaku, a także dowiesz się, jak porównywać wiele komórek.
Gdy używasz Excela do analizy danych, dokładność jest najważniejsza. Błędne informacje prowadzą do niedotrzymania terminów, błędnej oceny trendów, błędnych decyzji i utraty przychodów.
Pomimo że formuły Excela są zawsze idealnie prawdziwe, ich wyniki mogą być błędne, ponieważ do systemu przedostały się błędne dane. W takim przypadku jedynym środkiem zaradczym jest sprawdzenie danych pod kątem dokładności. Ręczne porównanie dwóch komórek to nic trudnego, ale dostrzeżenie różnic między setkami i tysiącami ciągów tekstowych jest prawie niemożliwe.
Z tego poradnika dowiesz się, jak zautomatyzować żmudne i podatne na błędy zadanie porównywania komórek oraz jakich formuł najlepiej użyć w każdym konkretnym przypadku.
- Jak porównać 2 komórki w Excelu
- Jak porównać wiele komórek
- Porównanie zakresu komórek z komórką przykładową
- Porównanie komórek według długości ciągu
- Porównanie ciągów według wystąpień określonego znaku
Jak porównać dwie komórki w Excelu
Są dwa różne sposoby porównywania ciągów w Excelu, w zależności od tego, czy chcesz porównywać wielkość liter, czy też nie.czy też nie uwzględniasz wielkości liter.
Formuła niewrażliwa na wielkość liter do porównania 2 komórek
Aby porównać dwie komórki w Excelu, ignorując wielkość liter, należy użyć prostej formuły, takiej jak ta:
=A1=B1
Gdzie A1 i B1 są komórkami, które porównujesz. Wynikiem formuły są wartości logiczne TRUE i FALSE.
Jeśli chcesz wypisać własne teksty dla dopasowań i różnic, umieść powyższe stwierdzenie w teście logicznym funkcji JEŻELI. Na przykład:
=IF(A1=B1, "Equal", "Not equal")
Jak widać na poniższym zrzucie ekranu, obie formuły równie dobrze porównują ciągi tekstowe, daty i liczby:
Formuła wrażliwa na wielkość liter do porównania ciągów w Excelu
W niektórych sytuacjach ważne może być nie tylko porównanie tekstu w dwóch komórkach, ale także porównanie wielkości liter. Porównywanie tekstu z uwzględnieniem wielkości liter można przeprowadzić za pomocą funkcji EXACT programu Excel:
Tekst1 i tekst2 to dwie porównywane komórki.
Zakładając, że twoje ciągi znajdują się w komórkach A2 i B2, formuła wygląda następująco:
=EXACT(A2, B2)
Jako wynik otrzymasz TRUE dla ciągów tekstowych pasujących dokładnie z uwzględnieniem wielkości liter każdego znaku, FALSE w przeciwnym razie.
Jeśli chcesz, aby funkcja EXACT dawała inne wyniki, umieść ją w formule JEŻELI i wpisz własny tekst dla argumentów wartość_jeżeli_true i wartość_jeżeli_false:
=IF(EXACT(A2 ,B2), "Exactly equal", "Not equal")
Następujący zrzut ekranu przedstawia wyniki porównania łańcuchów z uwzględnieniem wielkości liter w programie Excel:
Jak porównać wiele komórek w Excelu
Aby porównać więcej niż 2 komórki w wierszu, należy użyć formuł omówionych w powyższych przykładach w połączeniu z operatorem AND. Poniżej przedstawiono szczegółowe informacje.
Formuła niewrażliwa na wielkość liter do porównywania więcej niż 2 komórek
W zależności od sposobu wyświetlania wyników należy użyć jednej z następujących formuł:
=AND(A2=B2, A2=C2)
lub
=IF(AND(A2=B2, A2=C2), "Equal", "Not equal")
Formuła ORAZ zwraca wartość TRUE, jeśli wszystkie komórki zawierają tę samą wartość, a wartość FALSE, jeśli jakakolwiek wartość jest inna. Formuła JEŻELI wyprowadza etykiety, które w niej wpisujemy, w tym przykładzie „Równe” i „Nie równe”.
Jak pokazano na poniższym zrzucie ekranu, formuła działa doskonale z dowolnymi typami danych – tekstem, datami i wartościami liczbowymi:
Wrażliwa na wielkość liter formuła do porównywania tekstu w kilku komórkach
Aby porównać ze sobą wiele ciągów znaków w celu sprawdzenia, czy dokładnie do siebie pasują, należy użyć następujących formuł:
=AND(EXACT(A2,B2), EXACT(A2, C2))
Or
=IF(AND(EXACT(A2,B2), EXACT(A2, C2)),"Exactly equal", "Not equal")
Tak jak w poprzednim przykładzie, pierwsza formuła dostarcza wartości TRUE i FALSE, natomiast druga wyświetla własne teksty w poszukiwaniu dopasowań i różnic:
Porównanie zakresu komórek z komórką przykładową
Następujące przykłady pokazują, jak można sprawdzić, czy wszystkie komórki w danym zakresie zawierają taki sam tekst jak w komórce przykładowej.
Formuła niewrażliwa na wielkość liter do porównywania komórek z przykładowym tekstem
Jeśli wielkość liter nie ma znaczenia, możesz użyć następującej formuły do porównania komórek z próbką:
W teście logicznym funkcji JEŻELI porównujesz dwie liczby:
- całkowitą liczbę komórek w podanym zakresie (liczba wierszy pomnożona przez liczbę kolumn) oraz
- liczbę komórek zawierających taką samą wartość jak w przykładowej komórce (zwróconą przez funkcję COUNTIF).
Zakładając, że przykładowy tekst znajduje się w komórce C2, a porównywane ciągi znajdują się w zakresie A2:B6, formuła wygląda następująco:
=ROWS(A2:B6)*COLUMNS(A2:B6)=COUNTIF(A2:B6,C2)
Aby wyniki były bardziej przyjazne dla użytkownika, tj. zamiast TRUE i FALSE, użyć funkcji JEŻELI, tak jak w poprzednich przykładach:
=IF(ROWS(A2:B6)*COLUMNS(A2:B6)=COUNTIF(A2:B6,C2),"All match", "Not all match")
Jak widać na powyższym zrzucie ekranu, formuła doskonale radzi sobie z zakresem ciągów tekstowych, ale może być również użyta do porównywania liczb i dat.
Wrażliwa na wielkość liter formuła do porównywania ciągów znaków z przykładowym tekstem
Jeśli wielkość liter robi różnicę, można porównywać ciągi znaków z przykładowym tekstem za pomocą następujących formuł tablicowych.
Z zakresem źródłowym znajdującym się w A2:B6, a przykładowy tekst w C2, formuła przybiera następujący kształt:
=IF(ROWS(A2:B6)*COLUMNS(A2:B6)=SUM(--EXACT(C2, A2:B6)), "All match", "Not all match")
W przeciwieństwie do zwykłych formuł Excela, formuły tablicowe uzupełnia się, naciskając kombinację klawiszy Ctrl + Shift + Enter. Jeśli zostanie wprowadzona poprawnie, Excel zamyka formułę tablicową w nawiasach klamrowych {curly braces}, jak pokazano na zrzucie ekranu:
Jak porównać dwie komórki według długości ciągu
Czasami możesz chcieć sprawdzić, czy ciągi tekstowe w każdym wierszu zawierają równą liczbę znaków. Formuła do tego zadania jest bardzo prosta. Najpierw za pomocą funkcji LEN otrzymujemy długość łańcucha w dwóch komórkach, a następnie porównujemy te liczby.
Przy założeniu, że porównywane ciągi znajdują się w komórkach A2 i B2, użyj jednej z poniższych formuł:
=LEN(A2)=LEN(B2)
Or
=IF(LEN(A2)=LEN(B2), "Equal", "Not equal")
Jak już wiesz, pierwsza formuła zwraca wartości logiczne TRUE lub FALSE, natomiast druga formuła wyprowadza własne wyniki:
Jak pokazano na powyższym zrzucie ekranu, formuły działają zarówno dla ciągów tekstowych, jak i liczb.
Compare two cells by occurrences of a specific character
To już ostatni przykład w naszym samouczku Excel Compare Strings, a pokazuje on rozwiązanie dość specyficznego zadania. Załóżmy, że masz 2 kolumny ciągów tekstowych, które zawierają ważny dla Ciebie znak. Twoim celem jest sprawdzenie, czy dwie komórki w każdym wierszu zawierają taką samą liczbę wystąpień danego znaku.
Aby wszystko było bardziej zrozumiałe, rozważ następujący przykład. Załóżmy, że masz dwie listy zamówień wysłanych (kolumna B) i otrzymanych (kolumna C). Każdy wiersz zawiera zamówienia dla konkretnego elementu, którego unikalny identyfikator jest zawarty we wszystkich identyfikatorach zamówień i jest wymieniony w tym samym wierszu w kolumnie A (proszę zobaczyć zrzut ekranu poniżej). Chcesz się upewnić, że każdy wiersz zawiera równą liczbę wysłanych i otrzymanych pozycji o tym konkretnym identyfikatorze.
Aby rozwiązać ten problem, napisz formułę z następującą logiką.
- Po pierwsze, zastąp unikalny identyfikator niczym za pomocą funkcji SUBSTITUTE:
SUBSTITUTE(A1, character_to_count,"") - Następnie oblicz, ile razy unikalny identyfikator pojawia się w każdej komórce. W tym celu należy uzyskać długość łańcucha bez unikalnego identyfikatora i odjąć ją od całkowitej długości łańcucha. Tę część należy zapisać dla komórki 1 i komórki 2 osobno, na przykład:
LEN(cell 1) - LEN(SUBSTITUTE(cell 1, character_to_count, ""))
iLEN(cell 2) - LEN(SUBSTITUTE(cell 2, character_to_count, "")) - Na koniec porównujemy te 2 liczby, umieszczając znak równości (=) między powyższymi częściami.
LEN(komórka 2) – LEN(SUBSTITUTE(komórka 2, character_to_count, „”))
W naszym przykładzie unikalny identyfikator znajduje się w komórce A2, a ciągi znaków do porównania w komórkach B2 i C2. Zatem pełna formuła wygląda następująco:
=LEN(B2)-LEN(SUBSTITUTE(B2,$A2,""))=LEN(C2)-LEN(SUBSTITUTE(C2,$A2,""))
Formuła zwraca TRUE, jeśli komórki B2 i C2 zawierają taką samą liczbę wystąpień znaku w A2, FALSE w przeciwnym razie. Aby wyniki były bardziej znaczące dla Twoich użytkowników, możesz osadzić formułę w funkcji JEŻELI:
=IF(LEN(B2)-LEN(SUBSTITUTE(B2, $A2,""))=LEN(C2)-LEN(SUBSTITUTE(C2, $A2,"")), "Equal", "Not equal")
Jak widać na powyższym zrzucie ekranu, formuła działa doskonale pomimo kilku dodatkowych komplikacji:
- Znak do policzenia (unikalny identyfikator) może pojawić się w dowolnym miejscu ciągu tekstowego.
- Ciągi zawierają zmienną liczbę znaków i różne separatory, takie jak średnik, przecinek czy spacja.
Tak właśnie porównuje się ciągi w Excelu. Aby przyjrzeć się bliżej formułom omawianym w tym tutorialu, zapraszam do pobrania arkusza Excel Compare Strings Worksheet. Dziękuję za przeczytanie i mam nadzieję, że zobaczymy się na naszym blogu w przyszłym tygodniu.
Może Cię również zainteresować
- Porównanie dwóch kolumn w Excelu w celu uzyskania dopasowań i różnic
- Jak porównać dwa arkusze Excela w celu uzyskania różnic
- Jak używać operatora logicznego do porównywania liczb
- 4 sposoby na zrobienie vlookup z uwzględnieniem wielkości liter w Excelu
.