Stosowanie sprawdzania poprawności danych do komórek

Za pomocą funkcji sprawdzania poprawności danych można ograniczyć typy danych lub wartości wprowadzane w komórce przez użytkowników. Jednym z najczęstszych zastosowań sprawdzania poprawności danych jest tworzenie listy rozwijanej. Obejrzyj ten klip wideo autorstwa Douga z zespołu pakietu Office, zawierający krótkie omówienie funkcji sprawdzania poprawności danych.

Przeglądarka nie obsługuje klipu wideo. Instalowanie dodatku Microsoft Silverlight, Adobe Flash Player lub programu Internet Explorer 9.

Pobierz nasze przykłady

Możesz pobrać przykładowy skoroszyt ze wszystkimi przykładami sprawdzania poprawności danych przedstawionymi w tym artykule. Możesz wykonywać opisane czynności lub utworzyć własne scenariusze sprawdzania poprawności danych.

Pobierz przykłady sprawdzania poprawności danych programu Excel

Dodawanie sprawdzania poprawności danych do komórki lub zakresu

Uwaga : Pierwsze trzy kroki w tej sekcji służą do dodawania dowolnego typu sprawdzania poprawności danych. Kroki 4–8 są przeznaczone specjalnie do tworzenia listy rozwijanej.

  1. Zaznacz jedną lub więcej komórek, w których ma być sprawdzana poprawność.

  2. Na karcie Dane w grupie Narzędzia danych kliknij przycisk Poprawność danych.

    Funkcja sprawdzania poprawności danych znajduje się na karcie Dane, w grupie Narzędzia danych
  3. Na karcie Ustawienia w polu Zezwalaj wybierz pozycję Lista.

    Karta Ustawienia w oknie dialogowym Sprawdzanie poprawności danych
  4. W pole Źródło wpisz swoją listę wartości rozdzielonych przecinkami. Na przykład:

    1. Aby ograniczyć odpowiedź na pytanie typu „Czy masz dzieci?” do dwóch możliwości, wpisz Tak,Nie.

    2. Aby ograniczyć ocenę dostawcy do trzech poziomów, wpisz Niska,Średnia,Wysoka.

      Uwaga : Te kroki zwykle są zalecane tylko dla elementów list, które najprawdopodobniej nigdy nie ulegną zmianie. Jeśli masz listę, która może ulec zmianie, albo chcesz z czasem dodać lub usunąć elementy, lepiej postępować zgodnie z krokiem „Najlepsze rozwiązanie” poniżej.

      Najlepsze rozwiązanie: Wpisy na liście można też utworzyć, odwołując się do zakresu komórek w innym miejscu skoroszytu. Najskuteczniejszą metodą jest utworzenie listy, a następnie sformatowanie jej jako tabeli programu Excel (na karcie Narzędzia główne wybierz pozycję Style > Formatuj jako tabelę, a następnie wybierz styl tabeli, który najlepiej Ci odpowiada). Następnie wybierz zakres treści danych tabeli, który jest częścią tabeli obejmującą tylko Twoją listę, bez nagłówka tabeli (w tym przypadku „Dział”), i nadaj mu opisową nazwę w polu Nazwa nad kolumną A.

      W polu nazwy wprowadź opisową nazwę listy

    Teraz zamiast wpisywać wartości listy w polu sprawdzania poprawności danych Źródło, możesz dodać zdefiniowaną właśnie nazwę poprzedzoną znakiem równości (=).

    Nazwę tabeli należy poprzedzić znakiem =

    Największą zaletą używania tabeli jest to, że w miarę dodawania lub usuwania elementów z listy lista sprawdzania poprawności danych będzie automatycznie aktualizowana.

    Uwaga : Warto umieścić listy w osobnych arkuszach (ukrytych, jeśli to konieczne), aby nikt nie mógł ich edytować.

  5. Upewnij się, że pole wyboru Rozwinięcia w komórce jest zaznaczone. W przeciwnym wypadku nie będzie można zobaczyć strzałki rozwijania obok komórki.

    Lista rozwijana w komórce wyświetlana obok tej komórki
  6. Aby określić sposób traktowania wartości pustych (null), zaznacz lub wyczyść pole wyboru Ignoruj puste.

    Uwaga : Jeśli dozwolone wartości są oparte na zakresie komórek o zdefiniowanej nazwie, a w tym zakresie jest pusta komórka, zaznaczenie pola wyboru Ignoruj puste zezwala na wprowadzanie dowolnej wartości w sprawdzanej komórce. Jest tak też w przypadku wszelkich komórek, do których odwołują się formuły sprawdzania poprawności: jeśli którakolwiek z komórek, do których następuje odwołanie, jest pusta, wówczas zaznaczenie pola wyboru Ignoruj puste zezwala na wprowadzanie dowolnych wartości w sprawdzanej komórce.

  7. Przetestuj sprawdzanie poprawności danych, aby upewnić się, że działa prawidłowo. Spróbuj wprowadzić zarówno prawidłowe, jak i nieprawidłowe dane w komórkach, aby mieć pewność, że użyte ustawienia działają zgodnie z oczekiwaniami i że komunikaty są wyświetlane w oczekiwanych sytuacjach.

Uwagi : 

  • Po utworzeniu listy rozwijanej upewnij się, że działa odpowiednio. Na przykład warto sprawdzić, czy komórka jest dostatecznie szeroka, aby wyświetlić wszystkie pozycje.

  • Jeśli lista pozycji listy rozwijanej znajduje się w innym arkuszu, a nie chcesz, aby inni użytkownicy ją widzieli lub wprowadzali w niej zmiany, możesz ukryć i zabezpieczyć ten arkusz. Aby uzyskać więcej informacji na temat ochrony arkusza, zobacz temat Blokowanie komórek w celu ich ochrony.

  • Wyłączanie sprawdzania poprawności danych — Zaznacz komórki zawierające reguły sprawdzania poprawności danych, które chcesz usunąć, a następnie przejdź do pozycji Dane > Sprawdzanie poprawności danych i w oknie dialogowym Sprawdzanie poprawności danych naciśnij przycisk WyczyśćWszystko, a następnie przycisk OK.

W poniższej tabeli przedstawiono inne typy sprawdzania poprawności danych oraz sposoby dodawania ich do arkuszy.

Aby to zrobić:

Wykonaj następujące czynności:

Ograniczanie wprowadzania danych do liczb całkowitych z określonego przedziału.

  1. Wykonaj kroki 1–3 z sekcji Dodawanie sprawdzania poprawności danych do komórki lub zakresu powyżej.

  2. Na liście Zezwalaj wybierz pozycję Pełna liczba.

  3. W polu Dane wybierz typ ograniczenia. Aby na przykład wyznaczyć górną i dolną granicę, wybierz pozycję między.

  4. Wprowadź minimalną, maksymalną lub określoną wartość, aby zezwolić.

    Okno dialogowe Kryteria sprawdzania poprawności

    Możliwe jest również wprowadzenie formuły, która zwraca liczbę.

    Na przykład załóżmy, że sprawdzana jest poprawność danych w komórce F1. Aby ustawić minimalny limit odliczeń jako dwukrotność liczby dzieci w komórce F1, wybierz pozycję większa lub równa w polu Dane i wprowadź formułę =2*F1 w polu Minimum.

Ograniczanie wprowadzania danych do liczby dziesiętnej z określonego przedziału.

  1. Wykonaj kroki 1–3 z sekcji Dodawanie sprawdzania poprawności danych do komórki lub zakresu powyżej.

  2. W polu Dozwolone wybierz pozycję Dziesiętne.

  3. W polu Dane wybierz typ ograniczenia. Aby na przykład wyznaczyć górną i dolną granicę, wybierz pozycję między.

  4. Wprowadź minimalną, maksymalną lub określoną wartość, aby zezwolić.

    Można też wprowadzić formułę zwracającą liczbę. Aby na przykład ustawić maksymalny limit prowizji i premii jako 6% płacy sprzedawcy w komórce E1, wybierz pozycję mniejsze lub równe w polu Dane i wprowadź formułę =E1*6% w polu Maksimum.

    Uwaga : Aby umożliwić użytkownikowi wprowadzanie wartości procentowych, na przykład 20%, wybierz pozycję Dziesiętne w polu Dozwolone, wybierz typ ograniczenia w polu Dane, wprowadź minimalną, maksymalną lub określoną wartość w postaci dziesiętnej, na przykład 0,2, a następnie wyświetl komórkę sprawdzania poprawności danych jako wartość procentową przez wybranie tej komórki i kliknięcie przycisku Zapis procentowy Obraz przycisku w grupie Liczba na karcie Narzędzia główne.

Ograniczanie wprowadzania danych do daty z określonego zakresu dat.

  1. Wykonaj kroki 1–3 z sekcji Dodawanie sprawdzania poprawności danych do komórki lub zakresu powyżej.

  2. W polu Dozwolone wybierz pozycję Data.

  3. W polu Dane wybierz typ ograniczenia. Aby na przykład zezwolić na stosowanie dat następujących po określonym dniu, wybierz pozycję większa niż.

  4. Wprowadź początkową, końcową lub określoną datę, aby zezwolić.

    Można też wprowadzić formułę zwracającą datę. Aby na przykład ustawić ramy czasowe od daty bieżącej do 3 dni po dacie bieżącej, wybierz pozycję między w polu Dane, wprowadź formułę =DZIŚ() w polu Data rozpoczęcia oraz wprowadź formułę =DZIŚ()+3 w polu Data zakończenia.

    Ustawienia kryteriów sprawdzania poprawności ograniczające wprowadzanie dat do określonego przedziału czasu

Ograniczanie wprowadzania danych do godziny z określonego przedziału czasu.

  1. Wykonaj kroki 1–3 z sekcji Dodawanie sprawdzania poprawności danych do komórki lub zakresu powyżej.

  2. W polu Dozwolone wybierz pozycję Godzina.

  3. W polu Dane wybierz typ ograniczenia. Aby na przykład zezwolić na stosowanie godzin następujących przed określoną godziną, wybierz pozycję mniejsza niż.

  4. Wprowadź początkową, końcową lub konkretną godzinę, jaka będzie dozwolona. Jeśli chcesz wprowadzić konkretną godzinę, wprowadź ją w formacie gg:mm.

    Na przykład załóżmy, że masz komórkę E2 z ustawioną godziną rozpoczęcia (8:00), a w komórce F2 jest godzina zakończenia (17:00) i chcesz ograniczyć czas spotkania tak, aby mieścił się w tych granicach. Wybierz pozycję między w polu Dane, wprowadź = E2 w polu Godzina rozpoczęcia, a następnie wprowadź = F2 w polu Godzina zakończenia.

    Ustawienia sprawdzania poprawności ograniczające wprowadzanie godzin do określonego przedziału czasu

Ograniczanie wprowadzania danych do tekstu o określonej długości.

  1. Wykonaj kroki 1–3 z sekcji Dodawanie sprawdzania poprawności danych do komórki lub zakresu powyżej.

  2. W polu Dozwolone wybierz pozycję Długość tekstu.

  3. W polu Dane wybierz typ ograniczenia. Aby na przykład zezwolić na stosowanie pewnej maksymalnej liczby znaków, wybierz pozycję mniejsza niż lub równa.

  4. W tym przypadku chcemy ograniczyć długość wpisu do 25 znaków, dlatego należy wybrać opcję mniejsze niż lub równe w polu Dane, a następnie wprowadzić wartość 25 w polu Maksimum.

    Przykład sprawdzania poprawności danych z ograniczeniem długości tekstu

Obliczanie dozwolonych wartości na podstawie zawartości innej komórki.

  1. Wykonaj kroki 1–3 z sekcji Dodawanie sprawdzania poprawności danych do komórki lub zakresu powyżej. W polu Dozwolone wybierz odpowiedni typ danych.

  2. W polu Dane wybierz typ ograniczenia.

  3. W polu lub polach poniżej pola Dane kliknij komórkę, za pomocą której chcesz określić dozwolone wartości.

    Aby na przykład zezwolić na wpisy na koncie tylko pod warunkiem, że wynik nie przekroczy budżetu w komórce E1, wybierz pozycję Dozwolone > Liczba całkowita, Dane, mniejsze lub równe, a następnie formułę Maksimum >= =E1.

    Ustawienia sprawdzania poprawności dla obliczeń na podstawie zawartości innej komórki

Uwaga : W poniższych przykładach użyto opcji Niestandardowe, umożliwiającej określanie warunków przez wpisanie formuły. Nie musisz martwić się o wartości w polu Dane, ponieważ w przypadku wybrania opcji Niestandardowe jest ono wyłączone.

Aby mieć pewność, że

Wprowadź tę formułę

Komórka zawierająca identyfikator produktu (C2) zawsze rozpoczyna się od standardowego prefiksu „ID-” i ma długość co najmniej 10 (większą niż 9) znaków.

=ORAZ(LEWY(C2;3)="ID-";DŁ(C2)>9)

Przykład 6. Formuły sprawdzania poprawności danych

Komórka zawierająca nazwę produktu (D2) zawiera tylko tekst.

=CZY.TEKST(D2)

Przykład 2. Formuły sprawdzania poprawności danych

Komórka zawierająca czyjąś datę urodzenia (B6) musi mieć wartość większą niż liczba lat określona w komórce B4.

= JEŻELI(B6<=(DZIŚ()-(365*B4));PRAWDA;FAŁSZ)

Przykład sprawdzania poprawności danych w celu określenia minimalnego wieku we wpisie

Wszystkie dane w zakresie komórek A2:A10 zawierają unikatowe wartości.

= LICZ.JEŻELI($A$2:$A$10;A2)=1

Przykład 4. Formuły sprawdzania poprawności danych

Uwaga : Należy najpierw wprowadzić formułę sprawdzania poprawności danych dla komórki A2, a następnie skopiować A2 do A3:A10, dzięki czemu drugi argument funkcji LICZ.JEŻELI będzie zgodny z bieżącą komórką. Czyli część A2) = 1 zmieni się w A3) = 1; A4) = 1 i tak dalej.

Informacje dodatkowe

Sprawdzanie, czy wpis adresu e-mail w komórce B4 zawiera znak @.

=CZY.LICZBA(ZNAJDŹ("@";B4)

Przykład sprawdzania poprawności danych w celu sprawdzenia, czy adres e-mail zawiera znak @

  • Dlaczego polecenie Sprawdzanie poprawności danych nie jest włączone na wstążce? Możliwe przyczyny braku dostępności tego polecenia:

    • Tabelę programu Microsoft Excel można połączyć z witryną programu SharePoint Sprawdzania poprawności danych nie można dodać do tabeli programu Excel, która jest połączona z witryną programu SharePoint. Aby dodać sprawdzanie poprawności danych, należy rozłączyć tabelę programu Excel lub przekonwertować ją na zakres.

    • Być może właśnie są wprowadzane dane Polecenie Sprawdzanie poprawności danych nie jest włączone na karcie Dane podczas wprowadzania danych w komórce. Aby zakończyć wprowadzanie danych, naciśnij klawisz ENTER lub ESC.

    • Być może arkusz jest chroniony lub udostępniony Ustawień sprawdzania poprawności danych nie można zmienić, jeśli skoroszyt jest udostępniony lub chroniony. Aby się dowiedzieć, jak zatrzymać udostępnianie lub chronienie skoroszytu, zobacz Chronienie skoroszytu.

  • Czy można zmienić rozmiar czcionki? Nie, rozmiar czcionki jest stały. Jedynym sposobem zmiany rozmiaru wyświetlanych elementów jest dopasowywanie powiększenia ekranu w prawym dolnym rogu okna programu Excel. Możesz jednak użyć pola kombi ActiveX. Zobacz Dodawanie pola listy lub pola kombi do arkusza.

  • Czy istnieje sposób umożliwiający automatyczne wypełnianie lub automatyczne wybieranie w ramach sprawdzania poprawności danych podczas pisania? Nie, ale jeśli korzystasz z pola kombi ActiveX, masz dostęp do tej funkcjonalności.

  • Czy można dokonać wielokrotnego wyboru na liście sprawdzania poprawności danych? Nie, chyba że używasz pola kombi ActiveX lub pola listy.

  • Czy można wybrać element na liście sprawdzania poprawności danych i wypełnić nim inną listę? Tak! Jest to nazywane zależnym sprawdzaniem poprawności danych. Aby uzyskać więcej informacji, zobacz Tworzenie zależnych list rozwijanych.

  • Jak usunąć wszystkie opcje sprawdzania poprawności danych w arkuszu? Możesz użyć okna dialogowego Przejdź do > Specjalnie. Przejdź na kartę Narzędzia główne > Edytowanie > Znajdź i zaznacz (lub naciśnij klawisz F5 albo klawisze Ctrl+G na klawiaturze), a następnie wybierz pozycję Specjalnie > Sprawdzanie poprawności danych i wybierz opcję Wszystkich (aby znaleźć wszystkie komórki z regułami sprawdzania poprawności danych) albo Takich samych (aby znaleźć komórki spełniające konkretne ustawienia sprawdzania poprawności danych).

    Okno dialogowe Przechodzenie do — specjalnie

    Następnie wywołaj okno dialogowe sprawdzania poprawności danych (karta Dane > Sprawdzanie poprawności danych), naciśnij przycisk Wyczyść wszystko i przycisk OK.

  • Czy można zmusić inną osobę do wprowadzania danych w komórkach ze sprawdzaniem poprawności danych? Nie, ale możesz użyć języka VBA (Visual Basic for Applications), aby sprawdzić, czy ktoś inny wprowadził dane w określonych warunkach, na przykład przed zapisaniem lub zamknięciem skoroszytu. Jeśli jeszcze nie został dokonany wybór, możesz anulować zdarzenie i nie pozwolić na kontynuację do chwili dokonania wyboru.

  • Jak kolorować komórki na podstawie wyboru z listy sprawdzania poprawności danych? Możesz użyć formatowania warunkowego. W takim przypadku możesz chcieć użyć opcji Formatuj tylko komórki zawierające.

    Formatowanie tylko komórek zawierających opcje
  • Jak sprawdzić poprawność adresu e-mail? Możesz użyć metody Niestandardowe > Formuła i sprawdzić, czy we wpisie istnieje symbol @. W tym przypadku jest używana formuła =CZY.LICZBA(ZNAJDŹ("@";D2)). Funkcja ZNAJDŹ szuka symbolu @, a jeśli go znajdzie, zwraca numer jego pozycji w ciągu tekstowym i pozwala na wpis. Jeśli nie zostanie on znaleziony, funkcja ZNAJDŹ zwraca błąd i uniemożliwia wpis.

Masz pytanie dotyczące konkretnej funkcji?

Zadaj pytanie na forum społeczności programu Excel

Pomoc w ulepszaniu programu Excel

Masz sugestie dotyczące możliwości ulepszenia następnej wersji programu Excel? Jeśli tak, zapoznaj się z tematami w witrynie UserVoice dotyczącej programu Excel.

Zobacz też

Więcej informacji o sprawdzaniu poprawności danych

Klip wideo: tworzenie list rozwijanych i zarządzanie nimi

Dodawanie lub usuwanie elementów z listy rozwijanej

Usuwanie listy rozwijanej

Udostępnij Facebook Facebook Twitter Twitter E-mail E-mail

Czy te informacje były pomocne?

Doskonale! Czy chcesz przesłać inne opinie?

Jak możemy to poprawić?

Dziękujemy za opinię!

×