Znajdowanie rozbieżności w programie Excel — wyjaśniono 5 łatwych technik

Podczas pracy z dużymi zbiorami danych w programie Microsoft Excel napotkanie rozbieżności w danych jest nieuniknione. Identyfikacja i naprawa tych niespójności ma kluczowe znaczenie dla zapewnienia dokładnych raportów i analiz.

Dobre wieści? Program Excel zawiera wiele narzędzi i technik ułatwiających znajdowanie i rozwiązywanie tych problemów. W tym artykule poznasz niektóre z najskuteczniejszych metod. Obejmuje to filtrowanie, formatowanie warunkowe i używanie zaawansowanych formuł.

Włączając te techniki do swojego codziennego przepływu pracy, staniesz się bardziej wydajny w zarządzaniu danymi i analizowaniu ich. Ostatecznie prowadzi to do lepszego podejmowania decyzji i lepszych wyników Twoich projektów.

Spis treści

• Zrozumienie rozbieżności w programie Excel
• 5 sposobów na znalezienie rozbieżności w Excelu
• #1 – Znajdowanie rozbieżności za pomocą ręcznego przeglądu w programie Excel
• #2 – Używanie filtra w Excelu do wykrywania i naprawiania niespójnych danych
• 3# – Podkreślanie rozbieżności z formatowaniem warunkowym
• #4 – Znajdowanie rozbieżności za pomocą zaawansowanych funkcji programu Excel
  • Funkcje JEŻELI i JEST
  • WYSZUKAJ.PIONOWO, WYSZUKAJ.POZIOMO I WYSZUKAJ X
  • MECZ
• #5 – Korzystanie z dodatków programu Excel do wykrywania rozbieżności
• Jak można tworzyć reguły sprawdzania poprawności danych, aby zapobiegać rozbieżnościom?
• Nasze ostatnie słowo

Zrozumienie rozbieżności w programie Excel

Rozbieżności w wierszach i kolumnach mogą wynikać z błędów wprowadzonych przez ludzi, zduplikowanych wpisów lub nieprawidłowości w formatowaniu danych.

Przyjrzyj się poniższym danym. Zauważysz, że płeć Waltera White'a i Wayne'a Cena jest błędnie napisana - Mael zamiast Male. Rzeczywiście, łatwo jest zauważyć i naprawić błędy w tym scenariuszu. Inaczej jest w przypadku arkuszy kalkulacyjnych zawierających tysiące wierszy i kolumn.

Aby znaleźć te rozbieżności i zarządzać nimi, możesz użyć następujących funkcji programu Excel :

• Filtry: Zastosuj filtry do określonych kolumn, aby wyświetlić unikalne wartości i szybko wykryć niespójne dane.
• Formatowanie warunkowe: Użyj reguł formatowania, aby wyróżnić komórki spełniające określone kryteria lub niezgodne dane między kolumnami.
• Zaawansowane funkcje programu Excel: Korzystaj z funkcji, takich jak PODAJ.POZYCJĘ i WYSZUKAJ.PIONOWO , aby porównywać zestawy danych i identyfikować rozbieżności między nimi.

Wiedząc, jak korzystać z tych funkcji w programie Excel, możesz skutecznie wykrywać i korygować rozbieżności w swoich zestawach danych.

5 sposobów na znalezienie rozbieżności w Excelu

Istnieje 5 głównych sposobów radzenia sobie z rozbieżnościami w programie Excel:

1. Przegląd ręczny
2. Filtracja
3. Formatowanie warunkowe
4. Zaawansowane funkcje Excela
5. Dodatki Excela

Przeanalizujmy każdy z nich, abyś mógł wypracować najlepszy sposób, który Ci odpowiada.

#1 – Znajdowanie rozbieżności za pomocą ręcznego przeglądu w programie Excel

Podczas pracy z programem Excel ręczna recenzja może być pomocną metodą identyfikowania rozbieżności w danych. Ten proces wymaga starannego zbadania zbiorów danych w celu wykrycia wszelkich niespójności lub błędów.

Oto kilka kroków, które możesz wykonać, aby przeprowadzić ręczną weryfikację:

• Zacznij od jasnego zrozumienia zestawów danych, z którymi pracujesz. Zapoznaj się z wierszami, kolumnami i formatowaniem.
• Przewijaj dane i zwracaj szczególną uwagę na informacje prezentowane w każdej komórce. Możesz użyć narzędzi, takich jak wyróżnianie komórek za pomocą koloru wypełnienia, formatowanie tekstu lub formatowanie warunkowe, aby ułatwić odczytywanie danych.
• Porównaj jeden wiersz z drugim lub jedną kolumnę z inną. Szukaj niezgodnych informacji, zduplikowanych wpisów itp., które mogą powodować nieścisłości w arkuszach programu Excel.
• W miarę identyfikowania rozbieżności stwórz listę lub użyj komentarzy, aby je udokumentować. Później mogą one służyć jako ważne odniesienia do rozwiązywania problemów.

Podczas ręcznego przeglądania danych pod kątem rozbieżności w arkuszu kalkulacyjnym programu Excel najważniejsza jest cierpliwość. Ten proces jest czasochłonny. Jest to jednak dobry punkt wyjścia do zidentyfikowania potencjalnych błędów.

#2 – Używanie filtra w Excelu do wykrywania i naprawiania niespójnych danych

Jednym z najłatwiejszych sposobów wykrywania niespójnych danych jest użycie Filtra programu Excel .

Za pomocą Filtra wyizolujemy nieprawidłowe dane w kolumnie Płeć w następującym zbiorze danych:

1. Wybierz opcję Sortuj i filtruj na karcie Strona główna .

2. Wybierz opcję Filtruj – upewnij się, że kursor znajduje się w zakresie danych.

3. Kliknij strzałkę listy rozwijanej na kolumnie z niespójnymi danymi.

4. Teraz odznacz wszystkie pasujące dane. W naszym przypadku jest to Male . Następnie kliknij OK, aby zapisać zmiany.

5. Arkusz programu Excel będzie teraz zawierał tylko wiersze z niepoprawnymi wartościami. Aby je naprawić, kliknij Znajdź i wybierz na karcie Narzędzia główne .

6. Kliknij Zastąp , gdy pojawi się lista rozwijana.

7. Wpisz niepoprawną wartość w Znajdź co i poprawną w Zamień na . Następnie kliknij opcję Zamień wszystko .

8. Pojawi się okno dialogowe potwierdzające zmiany. Kliknij OK, aby kontynuować.

3# – Podkreślanie rozbieżności z formatowaniem warunkowym

Innym sposobem identyfikowania różnic w kolumnach i wierszach jest użycie formatowania warunkowego.

Możesz uzyskać dostęp do reguł, takich jak podświetlanie komórek, góra/dół, paski danych, skale kolorów i zestawy żelaza. Ponadto możesz utworzyć nową regułę i zarządzać lub usuwać poprzednie reguły.

Użyjemy przykładowej tabeli, aby pokazać moc formatowania warunkowego:

1. Wybierz tabelę lub preferowane wiersze i kolumny. Wybieramy Kolumnę A (Firma) i Kolumnę B (Kraj). Następnie wybierz Formatowanie warunkowe na karcie Narzędzia główne .

2. Wybierz typ reguły, a następnie regułę. Wybieramy reguły wyróżniania komórek i zduplikowane wartości…

3. Teraz musimy wybrać, czy podświetlić Duplicate lub Unique wartości oraz Fill Color & Font , aby sformatować komórki. Po zakończeniu kliknij OK .

4. Wszystkie zduplikowane wartości są teraz podświetlone Jasnoczerwonym wypełnieniem z ciemnoczerwonym tekstem.

#4 – Znajdowanie rozbieżności za pomocą zaawansowanych funkcji programu Excel

Funkcje JEŻELI i JEST

Aby porównać komórki i zidentyfikować różnice, możesz użyć funkcji JEŻELI i CZY.

Na przykład użyliśmy formuły =IF(A2=B2,"MATCH","NOT MATCHED")do ​​porównania komórek A2 i B2. Przeciągnęliśmy formułę, aby zastosować ją do innych komórek. Tak więc identyczne komórki zwracają MATCH inaczej NOT MATCHED .

Możesz łączyć funkcje JEŻELI z funkcjami CZY, takimi jak CZY.LICZBA lub CZY.TEKST, aby sprawdzać określone typy danych. Na przykład formuła =IF(ISNUMBER(B2), "Number", "NaN")zwróci liczbę w kolumnie c, jeśli B2 jest wartością liczbową, aw przeciwnym razie NaN .

WYSZUKAJ.PIONOWO, WYSZUKAJ.POZIOMO I WYSZUKAJ X

Funkcje WYSZUKAJ.PIONOWO (pionowo) i WYSZUKAJ.POZIOMO (poziomo) są przydatne podczas porównywania wartości w różnych kolumnach lub wierszach dwóch tabel. Aby to zrobić, użyj następującej formuły: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) lub =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

Zamień szukaną_wartość na wartość, którą chcesz znaleźć w drugiej tabeli, tablicę_tablicy na zakres komórek w drugiej tabeli, numer_indeksu_kolumny lub numer_indeksu wiersza na numer indeksu kolumny lub wiersza, z którego chcesz zwrócić wartość, a [wyszukanie_zakresu] na FAŁSZ dla dokładnych dopasowań lub PRAWDA dla przybliżonego dopasowania.

W poniższym samouczku używamy funkcji WYSZUKAJ.PIONOWO w kolumnie E, aby porównać nazwiska pracowników w kolumnie A i kolumnie D, aby pobrać ich wynagrodzenia z kolumny B, jeśli zostanie znalezione dopasowanie.

XLOOKUP to odświeżona wersja WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO dostępna w programach Excel 2021 i Excel 365. Formuła XLOOKUP to: =XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found]).

Zastąp szukaną_wartość wartością, którą chcesz znaleźć w drugiej tabeli, szukaną_tablicę zakresem komórek do przeszukania w drugiej tabeli, zwracaną_tablicę tablicą, z której chcesz zwrócić wartość, a [if_nout_found] wartością tekstową, jeśli nie pasuje znajdują się wartości.

Oto krótka próbka XLOOKUP z tymi samymi tabelami:

MECZ

Funkcji PODAJ.POZYCJĘ można również użyć do porównania dwóch list pod kątem rozbieżności.

Funkcja PODAJ.POZYCJĘ =MATCH(lookup_value, lookup_array, [match_type])wyszukuje określony element w zakresie i zwraca względną pozycję elementu w tym zakresie.

Tutaj znajdujemy pozycję arbuzów w tablicy A2:A8 za pomocą PODAJ.POZYCJĘ. Używamy match_type jako 0 , aby znaleźć pierwszą wartość, która jest dokładnie równa szukanej_wartości .

#5 – Korzystanie z dodatków programu Excel do wykrywania rozbieżności

Program Excel oferuje różne dodatki i wbudowane narzędzia, które mogą pomóc w skuteczniejszym wykrywaniu i analizowaniu rozbieżności. W tej sekcji omówimy niektóre z tych przydatnych dodatków.

Najpierw rozważ użycie dodatku Spreadsheet Inquire . Może to pomóc w porównaniu dwóch skoroszytów i wyróżnieniu różnic komórka po komórce. Aby włączyć dodatek Spreadsheet Inquire:

1. Kliknij kartę Plik .

2. Wybierz Opcje , a następnie kliknij kategorię Dodatki .

3. W polu Zarządzaj wybierz Dodatki COM i kliknij Przejdź .

4. Zaznacz pole Zapytanie dotyczące arkusza kalkulacyjnego i kliknij OK .

5. Po włączeniu przejdź do karty Zapytanie , aby użyć polecenia Porównaj pliki .

Innym przydatnym narzędziem do wykrywania rozbieżności jest Analysis ToolPak . Ten dodatek zapewnia zaawansowane funkcje statystyczne i narzędzia do analizy danych, które mogą być przydatne przy analizie rozbieżności.

Aby włączyć dodatek Analysis ToolPak, wykonaj te same czynności, co przy włączaniu dodatku Spreadsheet Inquire. Ale wybierz Dodatki programu Excel w polu Zarządzaj i zaznacz pole Analysis ToolPak .

Podsumowując, program Excel oferuje różne dodatki, które pomagają wykrywać i obsługiwać rozbieżności w danych. Możesz poprawić dokładność danych, zapoznając się z tymi narzędziami i formułami.

Jak można tworzyć reguły sprawdzania poprawności danych, aby zapobiegać rozbieżnościom?

Aby utworzyć reguły sprawdzania poprawności danych w programie Excel, które mogą pomóc w zapobieganiu rozbieżnościom, wykonaj następujące kroki:

1. Wybierz komórki, które chcesz ograniczyć. Może to być pojedyncza komórka, zakres komórek lub cała kolumna.

2. Przejdź do zakładki Dane na pasku narzędzi i kliknij przycisk Sprawdzanie poprawności danych (oznaczony dwoma poziomymi prostokątami, zielonym haczykiem i czerwonym przekreślonym kółkiem).

3. W oknie Sprawdzanie poprawności danych upewnij się, że jesteś na karcie Ustawienia . Tutaj będziesz mieć możliwość zdefiniowania kryteriów walidacji w oparciu o swoje potrzeby.

W programie Excel dostępne są różne typy kryteriów sprawdzania poprawności, z których niektóre obejmują:

• Liczba całkowita — dopuszcza tylko liczby całkowite z określonego zakresu.
• Dziesiętny — dopuszcza tylko liczby dziesiętne z określonego zakresu.
• Lista – ogranicza wprowadzanie do predefiniowanej listy dopuszczalnych wartości.
• Data – wymaga dat w określonym zakresie dat.
• Czas – akceptuje tylko czasy w określonym przedziale czasowym.
• Długość tekstu – wymusza ograniczenie długości wprowadzanego tekstu.
• Niestandardowe — umożliwia tworzenie niestandardowych reguł sprawdzania poprawności przy użyciu formuł programu Excel.

Po wybraniu odpowiednich kryteriów walidacji określ wymagane parametry. Na przykład, jeśli wybierzesz liczbę całkowitą, będziesz musiał ustawić minimum i maksimum za pomocą walidatora zakresu danych , takiego jak pomiędzy, równe, mniejsze niż itd.

Oprócz kryteriów sprawdzania poprawności możesz także skonfigurować niestandardowe komunikaty o błędach i wskazówki dotyczące wprowadzania danych, aby pomóc użytkownikom zrozumieć reguły sprawdzania poprawności i ich przestrzegać. Aby to zrobić, przejdź do zakładek Input Message i Error Alert , aby wprowadzić żądane komunikaty.

Wdrażając reguły sprawdzania poprawności danych, znacznie zmniejszasz prawdopodobieństwo wystąpienia niespójności danych i poprawiasz ogólną dokładność arkuszy programu Excel.

Nasze ostatnie słowo

W tym artykule poznałeś różne metody znajdowania i rozwiązywania rozbieżności w danych programu Excel. Podsumujmy te kluczowe strategie:

• Używanie list rozwijanych filtrów w kolumnach do identyfikowania unikalnych wartości i niespójnych danych.
• Stosowanie formatowania warunkowego w wybranych komórkach w celu podkreślenia rozbieżności przy użyciu zestawu szablonów reguł.
• Stosowanie zaawansowanych funkcji programu Excel, takich jak PODAJ.POZYCJĘ , WYSZUKAJ.PIONOWO , XWYSZUKAJ itp. do sprawdzania krzyżowego danych w dwóch listach lub tabelach.
• Używanie dodatków Excel do wykrywania rozbieżności.
• Konfigurowanie reguł sprawdzania poprawności danych w celu zapobiegania błędom w danych

Dzięki tym technikom w Twoim zestawie umiejętności Excel Twoje dane będą dokładniejsze i godne zaufania.

Pamiętaj, aby regularnie ćwiczyć te metody, aby skuteczniej wykrywać rozbieżności w arkuszach kalkulacyjnych.

Kontynuuj doskonalenie swoich umiejętności, zapisując się na nasze i kontynuuj odkrywanie nowych funkcji, aby jeszcze bardziej biegle posługiwać się programem Excel.