Sprawdzanie poprawności danych w MS Excel

Wprowadzenie

Narzędzie „Poprawność danych” dostępne w programie Microsoft Excel stanowi kluczowy element kontroli jakości danych w arkuszach kalkulacyjnych. Umożliwia ono precyzyjne określenie, jakie dane mogą być wprowadzane do wybranych komórek, co znacząco podnosi spójność i dokładność wprowadzanych informacji. Załóżmy, że istnieje potrzeba zebrania danych od różnych użytkowników w jednym pliku – narzędzie „Poprawność danych” pozwala upewnić się, że wszystkie dane będą spełniać określone kryteria, co minimalizuje ryzyko błędów.

Dynamiczne formuły tablicowe i zakresy (funkcje) rozlewające się (Excelu 2021 i nowszych wersjach) to nowoczesne rozwiązania umożliwiające tworzenie dynamicznych list rozwijanych. Dzięki nim możliwe jest automatyczne aktualizowanie się list wyboru w zależności od wprowadzanych danych (zobacz również: Dynamiczne formuły tablicowe (formuły rozlewające się) w Excelu). Użycie tych funkcji w narzędziu „Poprawność danych” umożliwia jeszcze większą elastyczność i dokładność.

Najczęściej używanym wariantem tej funkcjonalności jest lista rozwijana, o której będziemy w tym artykule o niej mówić. Szerzej na temat samej listy rozwijanej przeczytasz i dowiesz się stąd: (zobacz również: Lista Rozwijana w MS Excel)

Przegląd funkcjonalności

Funkcjonalności narzędzia „Poprawność danych” obejmują:

• Dowolna wartość – brak ograniczeń.
• Pełna Liczba – ogranicza wpisy do liczb całkowitych.
• Dziesiętne – ogranicza wpisy do liczb dziesiętnych.
• Lista – pozwala na wybór danych z listy rozwijanej.
• Data – ogranicza wpisy do dat.
• Godzina – ogranicza wpisy do godzin.
• Długość tekstu – ogranicza długość wpisywanego tekstu.
• Niestandardowe – umożliwia wprowadzenie niestandardowych formuł weryfikujących poprawność danych.

Przykłady użycia

Jak akceptować tylko liczby całkowite?

Załóżmy, że istnieje potrzeba wprowadzenia w komórkach tylko liczb całkowitych z przedziału od 1 do 100.

1. Zaznacz komórki, które mają być objęte ograniczeniem (np. A5:A10).
2. Wybierz „Dane” > „Poprawność danych”.
3. W karcie „Ustawienia” wybierz „Liczba całkowita”.
4. Określ minimalną i maksymalną wartość (np. od 1 do 100).

5. Opcjonalnie, zaznacz opcję „Ignoruj puste”, jeśli puste komórki mają być ignorowane.

6. Zaznacz opcję „Rozwinięcia w komórce”, aby umożliwić rozwijanie listy wyboru w komórce (Jeśli wybierzemy listę rozwijaną). W przeciwnym wypadku będzie można wpisać wartość z listy, ale bez podglądu co jest na liście.

7. Przejdź do zakładki „Komunikat wejściowy”, gdzie można wprowadzić własne komunikaty. Wpisz tytuł oraz treść komunikatu, który ma się wyświetlać przy zaznaczeniu komórki.

8. Przejdź do zakładki „Alert o błędzie”, gdzie można skonfigurować sposób reakcji na nieprawidłowe dane. Wybierz styl: „Stop”, „Ostrzeżenie” lub „Informacje”. Wpisz tytuł oraz treść komunikatu o błędzie, który ma się wyświetlać przy wprowadzeniu nieprawidłowych danych.

W kolejnych przykładach nie będziemy powtarzać konfiguracji opcji „Ignoruj puste” i „Rozwinięcia w komórce” ani ustawień komunikatów i alertów.

Jak akceptować tylko liczby dziesiętne?

W sytuacji, gdy wymagane jest wprowadzenie liczb dziesiętnych z określonego zakresu, na przykład od 0,01 do 0,99:

1. Zaznacz odpowiednie komórki (np. A5:A10).
2. Przejdź do „Dane” > „Poprawność danych”.
3. Wybierz „Dziesiętne” i wprowadź zakres (0,01 – 0,99).

Uwaga! Pełne liczby też są akceptowane, jeśli podamy szerszy zakres.

Jak utworzyć listę rozwijaną (sztywne źródło)?

Załóżmy, że lista rozwijana ma zawierać nazwy działów: Księgowość, Kadry, IT.

1. Wybierz komórki, w których ma być dostępna lista (np. A5:A10).
2. W „Poprawności danych” wybierz „Lista”.
3. W polu „Źródło” wpisz wartości oddzielone przecinkami: Księgowość, Kadry, IT.

Jak ograniczyć wprowadzanie dat?

Aby wprowadzać tylko daty z bieżącego miesiąca:

1. Zaznacz komórki (np. A5:A10).
2. Wybierz „Data” w „Poprawności danych”.
3. Określ zakres dat, np. od pierwszego do ostatniego dnia miesiąca.

Jak ograniczyć wprowadzanie godzin?

Jeśli godziny mają być wprowadzane tylko w przedziale od 8:00 do 17:00:

1. Zaznacz komórki (np. A5:A10).
2. Wybierz „Godzina” w „Poprawności danych”.
3. Określ zakres godzin (8:00 – 17:00).

Jak ograniczyć długość tekstu?

Aby ograniczyć długość tekstu do maksymalnie 10 znaków np. dla NIP:

1. Zaznacz komórki (np. A5:A10).
2. W „Poprawności danych” wybierz „Długość tekstu”.
3. Określ maksymalną długość (10 znaków).

Jak użyć niestandardowej formuły?

Załóżmy, że w komórkach mają być wprowadzane tylko wartości większe od wartości w innej komórce:

1. Zaznacz komórki (np. A5:A10).
2. Wybierz „Niestandardowe” w „Poprawności danych”.
3. Wprowadź formułę, np. A5>B5, gdzie A5 to wartość bieżącej komórki, a B5 to wartość odniesienia.

Po zaznaczeniu całego zakresu i odwołania do komórki porównywanej to adresy zachowują się tak samo jak w arkuszu. To znaczy, że odwołania względne będą się zmieniać wraz z wybranym zakresem. Więcej na temat adresowania zapraszamy do naszego bloga – Adresowanie komórek.

Jak używać funkcji rozlewających się w narzędziu Poprawność danych (Excel 2021>)?

Funkcje rozlewające się w Excelu 2021 i nowszych wersjach umożliwiają dynamiczne wprowadzanie danych do wielu komórek jednocześnie. Aby użyć funkcji rozlewających się w kontekście Poprawności danych:

1. Tworzenie tabeli wyborów:
   • Najpierw należy utworzyć tabelę przechowującą wszystkie opcje wyboru. Kolumna pierwsza będzie zawierać opcje dla rozwijanego menu. Należy upewnić się, że tabela jest zapisana jako tabela Excel (Wstaw > Tabela) i warto nadać jej opisową nazwę na karcie kontekstowej tabeli. W tym przykładzie nazwiemy tabelę „Wybory”.
2. Tworzenie dynamicznych list wyboru: Aby utworzyć listę rozwijaną, która automatycznie aktualizuje się w zależności od wprowadzonych danych, można użyć funkcji dynamicznych tablic, takich jak UNIKATOWE. Więcej na jej temat znajdziesz na naszym blogu.
   • Aby uzyskać unikalną listę wartości z kolumny „Regiony” z tabeli „Wybory”, należy użyć funkcji UNIKATOWE np. komórce C5:

= UNIKATOWE(Wybory[Regiony])

• Wynik tej formuły automatycznie rozleje się na komórki poniżej formuły.

3. Tworzenie list rozwijanych: Aby utworzyć listę rozwijaną dla wyboru regionu:
   • Zaznacz komórkę, w której ma się znajdować lista rozwijana (np. E5).
   • Przejdź do karty „Dane” -> „Poprawność danych”.
   • Wybierz „Lista” i w polu „Źródło” wpisz odwołanie do komórki zawierającej formułę, dodając symbol # na końcu, aby objąć cały zakres rozlewający się:

=A5#

Jak używać listy rozwijanej na podstawie Tabeli Excel?

1. Zamiana zakresu danych na tabelę Excel: Załóżmy, że dane znajdują się w zakresie A4:A10. Aby zamienić ten zakres na tabelę Excel:
   • Zaznacz zakres A1:A10.
   • Przejdź do „Wstaw” -> „Tabela”.
   • Nadaj tabeli nazwę, np. „Dane”.
2. Użycie narzędzia Poprawność danych: Aby utworzyć listę rozwijaną na podstawie tabeli Excel:
   • Wybierz komórki, w których ma być dostępna lista rozwijana (np. A5:A10).
   • Przejdź do „Dane” > „Poprawność danych”.
   • Wybierz „Lista” i w polu „Źródło” wpisz zakres kolumny tabeli bez nagłówków tak aby nie pojawił się adres strukturalny, np.:

=$A$5:$A$25

• Upewnij się, że odwołanie jest bezwzględnie zablokowane.

3. Zatwierdzenie listy: Po zatwierdzeniu, lista rozwijana będzie zawierać dane z kolumny tabeli Excel. Każde nowe dane dodane do tabeli automatycznie powiększą zakres listy rozwijanej.

Uwaga: Narzędzie Poprawność danych nie współpracuje bezpośrednio z tabelami Excel, a dokładniej mówiąc, z odwołaniami strukturalnymi. Jednak wpisanie zakresu w ten sposób spowoduje, że Poprawność danych będzie traktować go jako zakres dynamiczny. Nowe dane dodane do tabeli będą automatycznie rozszerzać ten zakres.

Najnowsze wersje Excela 365 posiadają wbudowane usuwanie duplikatów w listach rozwijanych. W starszych wersjach od 2021 należy używać funkcji UNIKATOWE. W jeszcze starszych wersjach dane należy przygotować przed zbudowaniem listy za pomocą narzędzia Usuń Duplikaty na karcie Dane.

Czy można zrobić listę z danych poziomo z wiersza?

Tak, można znaleźć nieprawidłowe wpisy do komórek, korzystając z narzędzia „Zakreśl nieprawidłowe dane”. Aby to zrobić, wykonaj następujące kroki:

1. Zaznacz komórki z ograniczeniami: Załóżmy, że istnieje potrzeba wprowadzenia w komórkach tylko liczb całkowitych z przedziału od 1 do 100.
   • Zaznacz komórki, które mają być objęte ograniczeniem (np. A5:A10).
   • Wybierz „Dane” > „Poprawność danych”.
   • W karcie „Ustawienia” wybierz „Liczba całkowita”.
   • Określ minimalną i maksymalną wartość (np. od 1 do 100).
     • Opcjonalnie, zaznacz opcję „Ignoruj puste”, jeśli puste komórki mają być ignorowane.
     • Przejdź do zakładki „Alert o błędzie”, gdzie można skonfigurować sposób reakcji na nieprawidłowe dane. Wybierz styl: „Ostrzeżenie” lub „Informacje”, aby pozwolić na wpisanie nieprawidłowej wartości, ale ostrzec użytkownika. Wpisz tytuł oraz treść komunikatu o błędzie, który ma się wyświetlać przy wprowadzeniu nieprawidłowych danych.

2. Wprowadzenie danych: Wprowadź dane do komórek A5:A10, w tym wartości, które nie mieszczą się w zakresie od 1 do 100.
3. Zakreślenie nieprawidłowych danych:
   • Przejdź do „Dane”- > „Zakreśl nieprawidłowe dane”.
   • Excel automatycznie obramuje komórki zawierające nieprawidłowe dane czerwonym obramowaniem, co ułatwia ich zlokalizowanie.

Podsumowanie

Narzędzie „Poprawność danych” w Excelu to potężne narzędzie zapewniające spójność i dokładność danych wprowadzanych do arkuszy kalkulacyjnych. Poprzez odpowiednie ustawienia można ograniczyć rodzaj, zakres i format wprowadzanych danych, co jest niezwykle pomocne w pracy zespołowej oraz przy zarządzaniu dużymi zbiorami danych. Narzędzia „Zakreśl nieprawidłowe dane” i „Wyczyść zakreślenia nieprawidłowych danych” dodatkowo wspierają proces kontroli jakości, ułatwiając identyfikację i korektę błędów.

Dynamiczne formuły tablicowe oraz zakresy rozlewające się umożliwiają tworzenie elastycznych, dynamicznych list rozwijanych, które automatycznie aktualizują się w zależności od wprowadzonych danych. Zastosowanie tych funkcji w narzędziu „Poprawność danych” otwiera nowe możliwości w zarządzaniu danymi i zwiększa efektywność.

Nauka Excela sprawia Ci trudności? Nie wiesz od czego zacząć i jak się uczyć? Potrzebujesz informacji na temat jak usuwać duplikaty Excel? Sprawdź nasze kursy informatyczne.

Zobacz również:

Szkolenia: