Przejdź do głównej zawartości

Walidacja danych w Excelu: dodawaj, używaj, kopiuj i usuwaj walidację danych w Excelu

W programie Excel sprawdzanie poprawności danych jest przydatną funkcją, której można użyć do ograniczenia tego, co użytkownik może wprowadzić do komórki. Na przykład funkcja sprawdzania poprawności danych może pomóc w ograniczeniu długości ciągów tekstowych lub tekstu rozpoczynającego/kończącego się określonymi znakami lub wprowadzanych unikalnych wartości i tak dalej.

W tym samouczku omówimy, jak dodawać, używać i usuwać walidację danych w programie Excel, a także szczegółowo zademonstrowane zostaną niektóre podstawowe i zaawansowane operacje tej funkcji.

Spis treści:

1. Co to jest sprawdzanie poprawności danych w programie Excel?

2. Jak dodać walidację danych w programie Excel?

3. Podstawowe przykłady walidacji danych

4. Zaawansowane niestandardowe reguły walidacji danych

5. Jak edytować walidację danych w Excelu?

6. Jak znaleźć i wybrać komórki z walidacją danych w programie Excel?

7. Jak skopiować regułę walidacji danych do innych komórek?

8. Jak korzystać z weryfikacji danych, aby zakreślić nieprawidłowe wpisy w programie Excel?

9. Jak usunąć walidację danych w programie Excel?


1. Co to jest sprawdzanie poprawności danych w programie Excel?

Połączenia Walidacja danych Funkcja może pomóc w ograniczeniu zawartości wejściowej w arkuszu. Zwykle można utworzyć pewne reguły sprawdzania poprawności, aby zapobiegać lub zezwalać na wprowadzanie tylko niektórych danych na liście wybranych komórek.

Niektóre podstawowe zastosowania funkcji sprawdzania poprawności danych:

  • 1. Dowolna wartość: nie jest wykonywana walidacja, możesz wprowadzić cokolwiek do określonych komórek.
  • 2. Cała wartość: dozwolone są tylko liczby całkowite.
  • 3. Dziesiętny: umożliwia wprowadzanie liczb całkowitych oraz dziesiętnych.
  • 4. Lista: można wprowadzać lub wybierać tylko wartości z predefiniowanej listy. Wartości są wyświetlane na liście rozwijanej.
  • 5. Data: dozwolone są tylko daty.
  • 6. Czas: dozwolone są tylko czasy.
  • 7. Długość tekstu: zezwalaj tylko na wprowadzenie określonej długości tekstu.
  • 8. Zwyczaj: tworzenie niestandardowych reguł formuł do walidacji danych wejściowych użytkowników.

2. Jak dodać walidację danych w programie Excel?

W arkuszu programu Excel możesz dodać walidację danych, wykonując następujące czynności:

1. Wybierz listę komórek, w których chcesz ustawić sprawdzanie poprawności danych, a następnie kliknij Dane > Walidacja danych > Walidacja danychzobacz zrzut ekranu:

2, w Walidacja danych okno dialogowe w obszarze Ustawienia stwórz własne reguły walidacji. w polach kryteriów możesz podać dowolny z następujących typów:

  • Wartości: Wpisz liczby bezpośrednio w polach kryteriów;
  • Odwołanie do komórki: Odwołanie do komórki w arkuszu lub innym arkuszu;
  • Wzory: Utwórz bardziej złożone formuły jako warunki.

Jako przykład utworzę regułę, która pozwala na wprowadzanie tylko liczb całkowitych od 100 do 1000, tutaj ustaw kryteria jak pokazano na zrzucie ekranu:

3. Po skonfigurowaniu warunków możesz przejść do Komunikat wejściowy or Alert o błędzie , aby ustawić komunikat wejściowy lub alert o błędzie dla komórek sprawdzania poprawności, jak chcesz. (Jeśli nie chcesz ustawiać alertu, kliknij OK zakończyć bezpośrednio.)

3.1) Dodaj wiadomość wejściową (opcjonalnie):

Możesz utworzyć komunikat, który pojawi się po wybraniu komórki zawierającej sprawdzanie poprawności danych. Ta wiadomość pomaga przypomnieć użytkownikowi, co może wprowadzić do komórki.

Idź do Komunikat wejściowy i wykonaj następujące czynności:

  • Sprawdź Pokaż komunikat wejściowy, gdy komórka jest zaznaczona opcja;
  • Wprowadź tytuł i przypomnienie, które chcesz w odpowiednich polach;
  • Kliknij OK aby zamknąć to okno dialogowe.

Teraz, po wybraniu zweryfikowanej komórki, pojawi się okno komunikatu w następujący sposób:

3.2) Utwórz znaczące komunikaty o błędach (opcjonalnie):

Oprócz tworzenia komunikatu wejściowego możesz również wyświetlać alerty o błędach, gdy nieprawidłowe dane zostaną wprowadzone do komórki z walidacją danych.

Idź do Alert o błędzie zakładka Walidacja danych okno dialogowe, wykonaj następujące czynności:

  • Sprawdź Pokaż alert o błędzie po wprowadzeniu nieprawidłowych danych opcja;
  • W Styl rozwijaną listę, wybierz jeden żądany typ alertu, którego potrzebujesz:
    • Zatrzymaj (domyślnie): Ten typ alertu uniemożliwia użytkownikom wprowadzanie nieprawidłowych danych.
    • ostrzeżenie: Ostrzega użytkowników, że dane są nieprawidłowe, ale nie uniemożliwia ich wprowadzenia.
    • Informacja: Informuje użytkowników tylko o nieprawidłowym wprowadzeniu danych.
  • Wprowadź tytuł i komunikat ostrzegawczy, który chcesz w odpowiednich polach;
  • Kliknij OK aby zamknąć okno dialogowe.

A teraz, po wprowadzeniu nieprawidłowej wartości, pojawi się okno ostrzeżenia o wiadomości, jak pokazano na poniższym zrzucie ekranu:

Stop opcja: Możesz kliknąć Spróbować ponownie aby wpisać inną wartość lub Anuluj aby usunąć wpis.

ostrzeżenie opcja: Kliknij Tak wprowadzić błędny wpis, Nie aby go zmodyfikować, lub Anuluj aby usunąć wpis.

Informacja opcja: Kliknij OK aby wprowadzić błędny wpis lub Anuluj aby usunąć wpis.

Note: Jeśli nie ustawisz własnej niestandardowej wiadomości w Alert o błędzie pudełko, domyślnie Stop Zostanie wyświetlone okno z monitem o alert, jak pokazano poniżej:


3. Podstawowe przykłady walidacji danych

Podczas korzystania z tej funkcji sprawdzania poprawności danych dostępnych jest 8 wbudowanych opcji, które umożliwiają ustawienie sprawdzania poprawności danych. Takie jak: dowolna wartość, liczby całkowite i dziesiętne, data i godzina, lista, długość tekstu i niestandardowa formuła. W tej sekcji omówimy, jak korzystać z niektórych wbudowanych opcji w programie Excel?

3.1 Walidacja danych dla liczb całkowitych i dziesiętnych

1. Wybierz listę komórek, w których chcesz zezwolić tylko na liczby całkowite lub dziesiętne, a następnie kliknij Dane > Walidacja danych > Walidacja danych.

2, w Walidacja danych okno dialogowe w obszarze Ustawienia wykonaj następujące czynności:

  • Wybierz odpowiednią pozycję Cały numer or Dziesiętny Dopuszczać upuścić pudło.
  • A następnie wybierz jedno z kryteriów, których potrzebujesz w Dane pudełko (w tym przykładzie wybieram pomiędzy opcja).
  • Tips: Kryteria zawierają: pomiędzy, nie pomiędzy, równe, nie równe, większe niż, mniejsze niż, większe lub równe, mniejsze lub równe.
  • Następnie wprowadź Minimum i Maksymalny wartości, których potrzebujesz (chcę liczby od 0 do 1 00).
  • Nareszcie kliknij OK przycisk.

3. Teraz w wybranych komórkach można wprowadzać tylko liczby całkowite od 0 do 100.


3.2 Walidacja danych dla daty i godziny

Aby zatwierdzić konkretną datę lub godzinę, którą należy wprowadzić, jest to łatwe za pomocą tego Walidacja danychwykonaj następujące czynności:

1. Wybierz listę komórek, w których chcesz zezwolić tylko na określone daty lub godziny, a następnie kliknij Dane > Walidacja danych > Walidacja danych.

2, w Walidacja danych okno dialogowe w obszarze Ustawienia wykonaj następujące czynności:

  • Wybierz odpowiednią pozycję Data or Czas Dopuszczać upuścić pudło.
  • A następnie wybierz jedno z kryteriów, których potrzebujesz w Dane pudełko (Tu wybieram większa niż opcja).
  • Tips: Kryteria zawierają: pomiędzy, nie pomiędzy, równe, nie równe, większe niż, mniejsze niż, większe lub równe, mniejsze lub równe.
  • Następnie wprowadź Data rozpoczęcia potrzebujesz (chcę, aby daty były większe niż 8).
  • Nareszcie kliknij OK przycisk.

3. Teraz w wybranych komórkach można wprowadzać tylko daty większe niż 8.


3.3 Walidacja danych pod kątem długości tekstu

Jeśli chcesz ograniczyć liczbę znaków, które można wpisać do komórki. Na przykład, aby ograniczyć treść do nie więcej niż 10 znaków dla określonego zakresu, to Walidacja danych również może zrobić ci przysługę.

1. Wybierz listę komórek, w których chcesz ograniczyć długość tekstu, a następnie kliknij Dane > Walidacja danych > Walidacja danych.

2, w Walidacja danych okno dialogowe w obszarze Ustawienia wykonaj następujące czynności:

  • Wybierz Długość tekstu z Dopuszczać upuścić pudło.
  • A następnie wybierz jedno z kryteriów, których potrzebujesz w Dane pudełko (w tym przykładzie wybieram mniej niż opcja).
  • Tips: Kryteria zawierają: pomiędzy, nie pomiędzy, równe, nie równe, większe niż, mniejsze niż, większe lub równe, mniejsze lub równe.
  • Następnie wprowadź Maksymalny numer, który musisz ograniczyć (chcę, aby długość tekstu nie przekraczała 10 znaków).
  • Nareszcie kliknij OK przycisk.

3. Teraz wybrane komórki pozwalają tylko wpisać ciąg tekstowy krótszy niż 10 znaków.


3.4 Lista walidacji danych (lista rozwijana)

Z tym potężnym Walidacja danych funkcja, możesz również szybko i łatwo tworzyć listę rozwijaną w komórkach. Proszę zrób tak:

1. Wybierz komórki docelowe, w których chcesz wstawić listę rozwijaną, a następnie kliknij Dane > Walidacja danych > Walidacja danych.

2, w Walidacja danych okno dialogowe w obszarze Ustawienia wykonaj następujące czynności:

  • Wybierz Lista z Dopuszczać Lista rozwijana.
  • W Źródło w polu tekstowym wpisz elementy listy bezpośrednio oddzielone przecinkami. Na przykład, aby ograniczyć wprowadzanie danych przez użytkownika do trzech opcji, wpisz Nie rozpoczęto, W toku, Ukończono lub możesz wybrać listę komórek zawierających wartości, na podstawie których chcesz wstawić listę rozwijaną.
  • Nareszcie kliknij OK przycisk.

3. Teraz lista rozwijana została utworzona w komórkach, jak pokazano na poniższym zrzucie ekranu:

Kliknij, aby poznać bardziej szczegółowe informacje o liście rozwijanej…


4. Zaawansowane niestandardowe reguły walidacji danych

W tej sekcji przedstawię, jak tworzyć zaawansowane niestandardowe reguły sprawdzania poprawności danych, aby rozwiązać problemy, takie jak: tworzenie formuł sprawdzania poprawności, które zezwalają tylko na liczby lub ciągi tekstowe, tylko unikalne wartości, tylko określone numery telefonów, adresy e-mail i tak dalej .

4.1 Walidacja danych dopuszcza tylko liczby lub teksty

 Zezwalaj na wprowadzanie tylko liczb z funkcją sprawdzania poprawności danych

Aby zezwolić tylko na liczby z zakresu komórek, wykonaj następujące czynności:

1. Wybierz zakres komórek, w których chcesz wprowadzać tylko liczby.

2. Kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia proszę wykonać następujące operacje:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • A następnie wprowadź tę formułę: =CZY.LICZBA(A2) do Formuła pole tekstowe. (A2 to pierwsza komórka z wybranego zakresu, który chcesz ograniczyć)
  • Kliknij OK aby zamknąć to okno dialogowe.

3. Od teraz do wybranych komórek można wprowadzać tylko liczby.

Note: Ten CZY.NUMER funkcja zezwala na dowolne wartości liczbowe w zwalidowanych komórkach, w tym liczby całkowite, ułamki dziesiętne, ułamki, daty i godziny.


 Zezwalaj na wprowadzanie tylko ciągów tekstowych za pomocą funkcji sprawdzania poprawności danych

Aby ograniczyć wpisy w komórkach tylko do tekstu, możesz użyć Walidacja danych funkcja z niestandardową formułą opartą na CZYTEKST funkcja, wykonaj następujące czynności:

1. Wybierz zakres komórek, do których chcesz wprowadzić tylko ciągi tekstowe.

2. Kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia proszę wykonać następujące operacje:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • A następnie wprowadź tę formułę: =CZYTEKST(A2) do Formuła pole tekstowe. (A2 to pierwsza komórka z wybranego zakresu, który chcesz ograniczyć)
  • Kliknij OK aby zamknąć to okno dialogowe.

3. Teraz, podczas wprowadzania danych do określonych komórek, dozwolone są tylko dane w formacie tekstowym.


4.2 Walidacja danych dopuszcza tylko wartości alfanumeryczne

W niektórych celach chcesz tylko zezwolić na wprowadzanie alfabetów i wartości liczbowych, ale ograniczyć znaki specjalne, takie jak ~,%, $, spacja itp., W tej sekcji przedstawimy kilka sztuczek.

 Zezwalaj tylko na wartości alfanumeryczne z funkcją sprawdzania poprawności danych

Aby uniknąć znaków specjalnych, ale zezwalać tylko na wartości alfanumeryczne, należy utworzyć niestandardową formułę w Walidacja danych funkcji, wykonaj następujące czynności:

1. Wybierz zakres komórek, do których mają być wprowadzane tylko wartości alfanumeryczne.

2. Kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia proszę wykonać następujące operacje:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • A następnie wprowadź poniższą formułę do Formuła pole tekstowe.
  • =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))
  • Kliknij OK aby zamknąć to okno dialogowe.

Note: W powyższych wzorach A2 to pierwsza komórka z wybranego zakresu, który chcesz ograniczyć.

3. Teraz można wprowadzać tylko alfabety i wartości liczbowe, a znaki specjalne będą ograniczone podczas pisania, jak pokazano na poniższym zrzucie ekranu:


 Zezwalaj tylko na wartości alfanumeryczne z niesamowitą funkcją

Być może powyższa formuła jest dla nas skomplikowana do zrozumienia i zapamiętania, tutaj przedstawię przydatną funkcję – Zapobiegaj pisaniu of Kutools dla programu Excel, dzięki tej funkcji możesz szybko i łatwo rozwiązać to zadanie.

Po zainstalowaniu Kutools dla programu Excel, zrób tak:

1. Wybierz zakres komórek, do których mają być wprowadzane tylko wartości alfanumeryczne.

2. Następnie kliknij Kutools > Zapobiegaj pisaniu > Zapobiegaj pisaniuzobacz zrzut ekranu:

3. W wyskoczył Zapobiegaj pisaniu okno dialogowe, wybierz Zapobiegaj wpisywaniu znaków specjalnych opcja, patrz zrzut ekranu:

4. Następnie kliknij Ok i w kolejnych okienkach kliknij Tak > OK aby zakończyć operację. Teraz w wybranych komórkach dozwolone są tylko alfabety i wartości liczbowe, patrz zrzut ekranu:


4.3 Walidacja danych umożliwia tekst rozpoczynający się lub kończący określonymi znakami

Jeśli wszystkie wartości w określonym zakresie powinny zaczynać się lub kończyć określonym znakiem lub podciągiem, możesz użyć sprawdzania poprawności danych za pomocą niestandardowej formuły opartej na funkcji DOKŁADNIE, W LEWO, W PRAWO lub LICZ.JEŻELI.

 Zezwalaj na rozpoczynanie lub kończenie tekstów określonymi znakami z tylko jednym warunkiem

Na przykład chcę, aby teksty musiały zaczynać się lub kończyć na „CN” podczas wprowadzania ciągów tekstowych w określonych komórkach, wykonaj następujące czynności:

1. Wybierz zakres komórek, w których dozwolone są tylko teksty, które zaczynają się lub kończą określonymi znakami.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia proszę wykonać następujące operacje:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • A następnie wprowadź poniższą formułę do Formuła pole tekstowe.
  • Begin with: =EXACT(LEFT(A2,2),"CN")
    End with: =EXACT(RIGHT(A2,2),"CN")
  • Kliknij OK aby zamknąć to okno dialogowe.

Note: W powyższych wzorach A2 to pierwsza komórka z wybranego zakresu, liczba 2 to liczba znaków, które podałeś, CN to tekst, od którego chcesz zacząć lub zakończyć.

3. Odtąd do wybranych komórek można wprowadzać tylko ciąg tekstowy rozpoczynający się lub kończący określonymi znakami. W przeciwnym razie pojawi się ostrzeżenie, aby przypomnieć, jak pokazano na poniższym zrzucie ekranu:

Porady: W powyższych formułach rozróżniana jest wielkość liter, jeśli nie potrzebujesz rozróżniania wielkości liter, zastosuj poniższe formuły CONTIF:

Begin with (non case sensitive): =COUNTIF(A2,"CN*")
End with (non case sensitive): =COUNTIF(A2,"*CN")

Note: Gwiazdka * to symbol wieloznaczny, który odpowiada co najmniej jednemu znakowi.


 Zezwalaj na rozpoczynanie lub kończenie tekstów określonymi znakami z wieloma kryteriami (logika OR)

Na przykład, jeśli chcesz, aby teksty zaczynały się lub kończyły na „CN” lub „UK”, jak pokazano na poniższym zrzucie ekranu, musisz dodać kolejne wystąpienie EXACT, używając znaku plus (+). Wykonaj następujące czynności:

1. Wybierz zakres komórek, który zezwala tylko na teksty, które zaczynają się lub kończą wieloma kryteriami.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia proszę wykonać następujące operacje:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • A następnie wprowadź poniższą formułę do Formuła pole tekstowe.
  • Begin with: =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")
    End with: =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")
  • Kliknij OK aby zamknąć to okno dialogowe.

Note: W powyższych wzorach A2 to pierwsza komórka z wybranego zakresu, liczba 2 to liczba znaków, które podałeś, CN i UK to konkretne teksty, od których chcesz zacząć lub zakończyć.

3. Teraz do wybranych komórek można wprowadzić tylko ciąg tekstowy rozpoczynający się lub kończący określonymi znakami.

Porady: Aby zignorować wielkość liter, zastosuj poniższe formuły CONTIF:

Begin with (non case sensitive): =COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")
End with (non case sensitive): =COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")

Note: Gwiazdka * to symbol wieloznaczny, który odpowiada co najmniej jednemu znakowi.


4.4 Walidacja danych pozwala na wpisy muszą zawierać / nie mogą zawierać określonego tekstu

W tej sekcji omówię, jak zastosować Walidację danych, aby zezwolić, aby wartości zawierały lub nie mogły zawierać jednego określonego podciągu lub jednego z wielu podciągów w programie Excel.

 Zezwól na wpisy muszą zawierać jeden lub jeden z wielu konkretnych tekstów

Zezwalaj na wpisy muszą zawierać jeden konkretny tekst

Aby zezwolić na wpisy zawierające określony ciąg tekstowy, na przykład wszystkie wprowadzone wartości powinny zawierać tekst „KTE”, jak pokazano na poniższym zrzucie ekranu, możesz zastosować weryfikację danych za pomocą niestandardowej formuły opartej na funkcjach ZNAJDŹ i CZY. Proszę zrób tak:

1. Wybierz zakres komórek, który zezwala tylko na teksty zawierające określony tekst.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia proszę wykonać następujące operacje:

  • Wybierz Zamówienia Indywidualne z listy rozwijanej Zezwól.
  • A następnie wprowadź jedną z poniższych formuł do Formuła pole tekstowe.
  • =ISNUMBER(FIND("KTE",A2))             (Case sensitive)
    =ISNUMBER(SEARCH("KTE",A2))         (Non case sensitive)
  • Kliknij OK aby zamknąć to okno dialogowe.

Note: W powyższych wzorach A2 to pierwsza komórka z wybranego zakresu, tekst KTE to ciąg tekstowy, który muszą zawierać wpisy.

3. Teraz, gdy wprowadzona wartość nie zawiera zaprojektowanego tekstu, wyskoczy okienko z ostrzeżeniem.


Zezwalaj na wpisy muszą zawierać jeden z wielu konkretnych tekstów

Powyższa formuła działa tylko dla jednego ciągu tekstowego, jeśli potrzebujesz jednego z wielu ciągów tekstowych, które mają być dozwolone w komórkach, jak pokazano na zrzucie ekranu, powinieneś użyć funkcji SUMPRODUCT, FIND i ISNUMBER razem, aby utworzyć formułę.

1. Wybierz zakres komórek, w którym dozwolone są tylko teksty zawierające jeden z wielu elementów.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia proszę wykonać następujące operacje:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • Następnie wprowadź jedną z poniższych formuł, zgodnie z potrzebami, w Formuła pole tekstowe.
  • =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0                        (Case sensitive)
    =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0                   (Non case sensitive)
  • A następnie kliknij OK aby zamknąć okno dialogowe.

Note: W powyższych wzorach A2 to pierwsza komórka z wybranego zakresu, C2: C4 to lista wartości, dla których chcesz, aby wpisy zawierały jedną z nich.

3. Teraz można wprowadzić tylko wpisy zawierające dowolną wartość z określonej listy.


 Zezwalaj na wpisy nie mogą zawierać jednego lub jednego z wielu konkretnych tekstów

Zezwalaj na wpisy nie mogą zawierać jednego konkretnego tekstu

Aby sprawdzić poprawność wpisów nie mogą zawierać określonego tekstu, na przykład, aby zezwolić na wartości, które nie mogą zawierać tekstu „KTE” w komórce, możesz użyć funkcji ISERROR i FIND, aby utworzyć regułę sprawdzania poprawności danych. Proszę zrób tak:

1. Wybierz zakres komórek, który zezwala tylko na teksty, które nie zawierają określonego tekstu.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia proszę wykonać następujące operacje:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • A następnie wprowadź jedną z poniższych formuł do Formuła pole tekstowe.
  • =ISERROR(FIND("KTE",A2))                  (Case sensitive)
    =ISERROR(SEARCH("KTE",A2))                  (Non case sensitive)
  • Kliknij OK aby zamknąć to okno dialogowe.

Note: W powyższych wzorach A2 to pierwsza komórka z wybranego zakresu, tekst KTE to ciąg tekstowy, którego wpisy nie mogą zawierać.

3. Teraz wpisy zawierające określony tekst nie będą mogły zostać wprowadzone.


Zezwalaj na wpisy nie mogą zawierać jednego z wielu konkretnych tekstów

Aby zapobiec wprowadzeniu jednego z wielu ciągów tekstowych na liście, jak pokazano na poniższym zrzucie ekranu, należy wykonać następujące czynności:

1. Wybierz zakres komórek, dla których chcesz zapobiec niektórym tekstom.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia proszę wykonać następujące operacje:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • Następnie wprowadź poniższą formułę do Formuła pole tekstowe.
  • =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0                     (Case sensitive)
    =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0                 (Non case sensitive)
  • A następnie kliknij OK aby zamknąć okno dialogowe.

Note: W powyższych wzorach A2 to pierwsza komórka z wybranego zakresu, C2: C4 to lista wartości, których chcesz zapobiec, jeśli wpisy zawierają jedną z nich.

3. Od tej chwili wpisy zawierające którykolwiek z określonych tekstów nie będą mogły być wprowadzane.


4.5 Walidacja danych dopuszcza tylko unikalne wartości

Jeśli chcesz zapobiec wprowadzaniu zduplikowanych danych do zakresu komórek, w tej sekcji przedstawimy kilka szybkich metod rozwiązania tego zadania w programie Excel.

 Zezwalaj tylko na unikalne wartości z funkcją sprawdzania poprawności danych

Zwykle funkcja sprawdzania poprawności danych z niestandardową formułą opartą na funkcji LICZ.JEŻELI może ci pomóc, wykonaj następujące czynności:

1. Zaznacz komórki lub kolumny, do których chcesz wprowadzić tylko unikatowe wartości.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia wykonaj następujące czynności:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • A następnie wprowadź poniższą formułę do Formuła pole tekstowe.
  • =COUNTIF($A$2:$A$9,A2)=1
  • Kliknij OK aby zamknąć to okno dialogowe.

Note: W powyższym wzorze, A2: A9 to zakres komórek, w których chcesz zezwolić tylko na unikalne wartości, oraz A2 to pierwsza komórka z wybranego zakresu.

3. Teraz można wprowadzać tylko unikalne wartości, a po wprowadzeniu zduplikowanych danych pojawi się komunikat ostrzegawczy, patrz zrzut ekranu:


 Zezwalaj tylko na unikalne wartości z kodem VBA

Poniższy kod VBA może również pomóc w zapobieganiu wprowadzaniu zduplikowanych wartości, wykonaj następujące czynności:

1. Kliknij prawym przyciskiem myszy kartę arkusza, na której chcesz zezwolić tylko na unikatowe wartości, i wybierz Wyświetl kod z menu kontekstowego w wyskakującym pliku Microsoft Visual Basic for Applications w oknie, skopiuj i wklej następujący kod do pustego modułu:

Kod VBA: zezwalaj tylko na unikalne wartości w zakresie komórek:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
  Dim xRg As Range, iLong, fLong As Long
  If Not Intersect(Target, Me.[A1:A100]) Is Nothing Then
     Application.EnableEvents = False
     For Each xRg In Target
     With xRg
         If (.Value <> "") Then
          If WorksheetFunction.CountIf(Me.[A:A], .Value) > 1 Then
            iLong = .Interior.ColorIndex
            fLong = .Font.ColorIndex
            .Interior.ColorIndex = 3
            .Font.ColorIndex = 6
            MsgBox "Duplicate Entry !", vbCritical, "Kutools for Excel"
            .ClearContents
            .Interior.ColorIndex = iLong
            .Font.ColorIndex = fLong
          End If
       End If
     End With
     Next
     Application.EnableEvents = True
  End If
End Sub

Note: W powyższym kodzie A1: A100 i Odp .: A to komórki w kolumnie, których chcesz zapobiec duplikowaniu, zmień je zgodnie z potrzebami.

2. Następnie zapisz i zamknij ten kod, teraz, podczas wprowadzania zduplikowanej wartości w komórce A1: A100, pojawi się okno ostrzeżenia, jak pokazano na poniższym zrzucie ekranu:


 Zezwalaj tylko na unikalne wartości z przydatną funkcją

Jeśli Kutools dla programu Excel, Z siedzibą w Zapobiegaj duplikowaniu funkcja, możesz ustawić sprawdzanie poprawności danych, aby zapobiec duplikatom dla zakresu komórek za pomocą zaledwie kilku kliknięć.

Po zainstalowaniu Kutools dla programu Excel, zrób tak:

1. Wybierz zakres komórek, w których chcesz zapobiec duplikowaniu wartości, ale zezwalaj tylko na unikatowe dane.

2. Następnie kliknij Kutools > Zapobiegaj pisaniu > Zapobiegaj duplikowaniuzobacz zrzut ekranu:

3. Pojawi się komunikat ostrzegawczy przypominający, że sprawdzanie poprawności danych zostanie usunięte, jeśli zastosujesz tę funkcję, kliknij Tak i w następującym okienku kliknij OKzobacz zrzuty ekranu:

4. Teraz, gdy wprowadzisz zduplikowane dane w określonych komórkach, zostanie wyświetlone okno z przypomnieniem, że zduplikowane dane nie są prawidłowe, patrz zrzut ekranu:


4.6 Walidacja danych dopuszcza tylko wielkie/małe/prawidłowe litery

Ta walidacja danych jest potężną funkcją, która może również pomóc użytkownikowi wpisać tylko wielkie, małe lub prawidłowe wpisy w zakresie komórek. Wykonaj następujące czynności:

1. Wybierz zakres komórek, w których chcesz wprowadzać tylko wielkie litery, małe litery lub poprawną literę.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia wykonaj następujące czynności:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • A następnie wprowadź jedną z poniższych formuł, których potrzebujesz do Formuła pole tekstowe.
  • =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2))                   (only allow uppercase text)
    =AND(EXACT(A2,LOWER(A2)),ISTEXT(A2))                 (only allow lowercase text)
    =AND(EXACT(A2,PROPER(A2)),ISTEXT(A2))               (only allow proper case text)
  • Kliknij OK aby zamknąć to okno dialogowe.

Note: W powyższym wzorze, A2 to pierwsza komórka kolumny, której chcesz użyć.

3. Teraz tylko wpisy zgodne z utworzoną regułą będą akceptowane.


4.7 Walidacja danych pozwala na wartości, które istnieją / nie istnieją na innej liście

Zezwolenie, aby wartości istniały lub nie istniały na innej liście, aby były wprowadzane do zakresu komórek, może być bolesnym problemem dla większości z nas. W rzeczywistości możesz użyć funkcji sprawdzania poprawności danych z prostą formułą opartą na funkcji LICZ.JEŻELI, aby sobie z tym poradzić.

Na przykład chcę, aby tylko wartości z zakresu C2: C4 były wprowadzane do zakresu komórek, jak pokazano na poniższym zrzucie ekranu, aby rozwiązać to zadanie, wykonaj następujące czynności:

1. Wybierz zakres komórek, do których chcesz zastosować sprawdzanie poprawności danych.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia wykonaj następujące czynności:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • A następnie wprowadź jedną z poniższych formuł, których potrzebujesz do Formuła pole tekstowe.
  • =COUNTIF($C$2:$C$4,A2)>0                (only allow values exist in another column)
    =COUNTIF($C$2:$C$4,A2)=0                (prevent values exist in another column)
  • Kliknij OK aby zamknąć to okno dialogowe.

Note: W powyższym wzorze, A2 to pierwsza komórka kolumny, której chcesz użyć, C2: C4 to lista wartości, które chcesz zablokować lub zezwolić, jeśli wpisy są jedną z nich.

3. Teraz wpisy są zgodne tylko z regułą, którą utworzyłeś, można wprowadzić, inne będą zablokowane.


4.8 Walidacja danych wymusza wprowadzenie tylko formatu numeru telefonu

Podczas wprowadzania informacji o pracownikach firmy, w jednej kolumnie należy wpisać numer telefonu, aby zapewnić szybkie i dokładne wprowadzanie numerów telefonów, w tym przypadku można ustawić walidację danych dla numerów telefonów. Na przykład chcę, aby numer telefonu w tym formacie (123) 456-7890 mógł być wprowadzany do arkusza roboczego, ta sekcja przedstawi dwie szybkie sztuczki do rozwiązania tego zadania.

 Wymuś tylko format numeru telefonu z funkcją sprawdzania poprawności danych

Aby zezwolić na wprowadzanie tylko określonego formatu numeru telefonu, wykonaj następujące czynności:

1. Wybierz listę komórek, do których chcesz wprowadzić określony format numeru telefonu, a następnie kliknij prawym przyciskiem myszy, wybierz Formatowanie komórek z menu kontekstowego, zobacz zrzut ekranu:

2, w Formatowanie komórek okno dialogowe w obszarze Numer kartę, wybierz Zamówienia Indywidualne po lewej Kategoria pole listy, a następnie wprowadź potrzebny format numeru telefonu w polu tekstowym Wpisz, na przykład użyję tego (###) ### - #### format, patrz zrzut ekranu:

3. Następnie kliknij OK aby zamknąć okno dialogowe.

4. Po sformatowaniu komórek kontynuuj wybieranie komórek, a następnie otwórz Walidacja danych okno dialogowe po kliknięciu Dane > Walidacja danych > Walidacja danych, w wyskakującym oknie dialogowym, pod Ustawienia wykonaj następujące czynności:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • A następnie wprowadź tę formułę = AND (ISNUMBER (A2); LEN (A2) = 10) w polu tekstowym Formuła.
  • Kliknij OK aby zamknąć to okno dialogowe.

Note: W powyższym wzorze, A2 to pierwsza komórka w kolumnie, w której chcesz sprawdzić poprawność numeru telefonu.

5. Teraz, po wprowadzeniu 10-cyfrowego numeru, zostanie on automatycznie przekonwertowany na określony format numeru telefonu, zgodnie z potrzebami, zobacz zrzuty ekranu:

Note: Jeśli wprowadzony numer nie składa się z 10 cyfr, pojawi się okno z ostrzeżeniem, aby przypomnieć, patrz zrzut ekranu:


 Wymuś tylko format numeru telefonu z przydatną funkcją

Kutools dla programu Excel'S Potwierdź numer telefonu Funkcja może również pomóc wymusić wprowadzenie tylko formatu numeru telefonu za pomocą zaledwie kilku kliknięć.

Po zainstalowaniu Kutools dla programu Excel, zrób tak:

1. Wybierz listę komórek, które zezwalają tylko na określony numer telefonu, a następnie kliknij Kutools > Zapobiegaj pisaniu > Potwierdź numer telefonuzobacz zrzut ekranu:

2, w Numer telefonu w oknie dialogowym wybierz żądany format numeru telefonu lub możesz utworzyć własne formatowanie, klikając przycisk Dodaj przycisk, zobacz zrzut ekranu:

3. Po wybraniu lub ustawieniu formatowania numeru telefonu kliknij OK, teraz można wprowadzić tylko numer telefonu z określonym formatowaniem, w przeciwnym razie pojawi się komunikat ostrzegawczy, aby przypomnieć, patrz zrzut ekranu:


4.9 Walidacja danych wymusza tylko wprowadzenie adresów e-mail

Przypuśćmy, że musisz wpisać wiele adresów e-mail w kolumnie arkusza roboczego, aby zapobiec wprowadzeniu nieprawidłowego formatowania adresów e-mail, zwykle możesz ustawić regułę sprawdzania poprawności danych, która zezwala tylko na formatowanie adresów e-mail.

 Wymuś tylko format adresów e-mail z funkcją sprawdzania poprawności danych

Korzystając z funkcji sprawdzania poprawności danych z niestandardową formułą, możesz utworzyć regułę zapobiegającą szybkiemu wprowadzaniu nieprawidłowych adresów e-mail, wykonaj następujące czynności:

1. Wybierz komórki, do których chcesz wprowadzić tylko adresy e-mail, a następnie kliknij Dane > Walidacja danych > Walidacja danych.

2. W wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia wykonaj następujące czynności:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • A następnie wprowadź tę formułę = ISNUMBER (MATCH ("*@*.?*", A2,0)) do Formuła pole tekstowe.
  • Kliknij OK aby zamknąć to okno dialogowe.

Note: W powyższym wzorze, A2 to pierwsza komórka kolumny, której chcesz użyć.

3. Teraz, jeśli wprowadzony tekst nie jest formatem adresu e-mail, pojawi się okno z ostrzeżeniem, aby przypomnieć, patrz zrzut ekranu:


 Wymuś tylko format adresów e-mail z przydatną funkcją

Kutools dla programu Excel obsługuje niesamowitą funkcję – Sprawdź poprawność adresu e-mail, dzięki temu narzędziu możesz zapobiec nieprawidłowym adresom e-mail za pomocą jednego kliknięcia.

Po zainstalowaniu Kutools dla programu Excelwykonaj następujące czynności:

1. Wybierz komórki, w których chcesz wprowadzać tylko adresy e-mail, a następnie kliknij Kutools > Zapobiegaj pisaniu > Sprawdź poprawność adresu e-mail. Zobacz zrzut ekranu:

2. Następnie można wprowadzić tylko formatowanie adresu e-mail, w przeciwnym razie pojawi się okno z ostrzeżeniem, aby przypomnieć, patrz zrzut ekranu:


4.10 Walidacja danych wymusza wprowadzenie tylko adresów IP

W tej sekcji przedstawię kilka szybkich sztuczek, aby ustawić sprawdzanie poprawności danych, aby akceptować tylko adresy IP w zakresie komórek.

 Wymuś formatowanie tylko adresów IP z funkcją sprawdzania poprawności danych

Zezwalaj tylko na wpisywanie adresów IP do określonego zakresu komórek, wykonaj następujące czynności:

1. Wybierz komórki, do których chcesz wprowadzić tylko adres IP, a następnie kliknij Dane > Walidacja danych > Walidacja danych.

2. W wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia wykonaj następujące czynności:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • A następnie wprowadź poniższą formułę do Formuła pole tekstowe.
  • =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))
  • Kliknij OK aby zamknąć to okno dialogowe.

Uwaga: W powyższym wzorze A2 to pierwsza komórka kolumny, której chcesz użyć.

3. Teraz, jeśli wprowadzisz nieprawidłowy adres IP do komórki, pojawi się komunikat ostrzegawczy, aby przypomnieć, jak pokazano na poniższym zrzucie ekranu:


 Wymuś tylko format adresów IP z kodem VBA

W tym przypadku poniższy kod VBA może również pomóc w umożliwieniu wprowadzania tylko adresów IP i ograniczeniu innego wprowadzania, wykonaj następujące czynności:

1. Kliknij prawym przyciskiem myszy kartę arkusza i kliknij Wyświetl kod z menu kontekstowego, w otworze Microsoft Visual Basic for Applications skopiuj do niego poniższy kod VBA.

Kod VBA: weryfikuj komórki, aby akceptować tylko adres IP

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by ExtendOffice
Dim xArrIp() As String
Dim xIntIP1, xIntIP2, xIntIP3, xIntIP4 As Integer
If Intersect(Target, Range("A2:A10")) Is Nothing Then
    Exit Sub
Else
    If Target = "" Then
        Exit Sub
    End If
    xArrIp = Split(Target.Text, ".")
    If UBound(xArrIp) <> 3 Then
        GoTo EIP
    Else
    xIntIP1 = CInt(xArrIp(0))
    xIntIP2 = CInt(xArrIp(1))
    xIntIP3 = CInt(xArrIp(2))
    xIntIP4 = CInt(xArrIp(3))
    If (xIntIP1 < 1) Or (xIntIP1 > 255) _
    Or (xIntIP2 < 1) Or (xIntIP2 > 255) _
    Or (xIntIP3 < 1) Or (xIntIP3 > 255) _
    Or (xIntIP4 < 1) Or (xIntIP4 > 255) Then
    GoTo EIP
     End If
    End If
End If
Exit Sub
EIP:
    MsgBox "Please enter correct IP address"
    Target = ""
End Sub

Note: W powyższym kodzie, A2: A10 to zakres komórek, w którym chcesz akceptować tylko adresy IP.

2. Następnie zapisz i zamknij ten kod, teraz tylko prawidłowe adresy IP pozwalają na wprowadzenie do określonych komórek.


 Wymuś formatowanie tylko adresów IP z łatwą funkcją

Jeśli Kutools dla programu Excel zainstalowany w twoim skoroszycie, jego Sprawdź poprawność adresu IP funkcja może również pomóc w rozwiązaniu tego zadania.

Po zainstalowaniu Kutools dla programu Excel, zrób tak:

1. Wybierz komórki, w których zezwalasz tylko na wprowadzanie adresów IP, a następnie kliknij Kutools > Zapobiegaj pisaniu > Sprawdź poprawność adresu IP. Zobacz zrzut ekranu:

2. Po zastosowaniu tej funkcji, teraz można wprowadzić tylko adres IP, w przeciwnym razie pojawi się okno z ostrzeżeniem, aby przypomnieć, patrz zrzut ekranu:


4.11 Wartości graniczne walidacji danych, które przekraczają wartość całkowitą

Przypuśćmy, że masz miesięczny raport z wydatków, a całkowity budżet wynosi 18000 USD, teraz potrzebujesz całkowitej kwoty na liście wydatków nie przekraczającej wstępnie ustawionej sumy 18000 USD, jak pokazano na zrzucie ekranu. W takim przypadku można utworzyć regułę sprawdzania poprawności danych za pomocą funkcji SUMA, aby zapobiec przekroczeniu sumy wartości wstępnie ustawionej sumy.

1. Wybierz listę komórek, w których chcesz ograniczyć wartości.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia wykonaj następujące czynności:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • A następnie wprowadź poniższą formułę do Formuła pole tekstowe.
  • =SUM($B$2:$B$7)<=18000
  • Kliknij OK aby zamknąć to okno dialogowe.

Note: W powyższym wzorze, B2: B7 to zakres komórek, w których chcesz ograniczyć wpisy.

3. Teraz, podczas wprowadzania wartości z zakresu B2:B7, jeśli suma wartości jest mniejsza niż 18000 USD, walidacja przechodzi pomyślnie. Jeśli jakakolwiek wartość spowoduje, że suma przekroczy 18000 USD, pojawi się okno z ostrzeżeniem, aby przypomnieć.


4.12 Sprawdzanie poprawności danych ogranicza wprowadzanie do komórki na podstawie innej komórki

Jeśli chcesz ograniczyć wpisy danych na liście komórek na podstawie wartości w innej komórce, funkcja sprawdzania poprawności danych może również pomóc w rozwiązaniu tego zadania. Na przykład, jeśli komórka C1 jest tekstem „Tak”, zakres A2: A9 może wpisywać cokolwiek, ale jeśli komórka C1 jest innym tekstem, nic nie pozwala na wprowadzenie w zakresie A2: A9, jak pokazano poniżej zrzuty ekranu :

Aby rozwiązać to rozwiązanie, wykonaj następujące czynności:

1. Wybierz listę komórek, w których chcesz ograniczyć wartości.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia wykonaj następujące czynności:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • A następnie wprowadź poniższą formułę do Formuła pole tekstowe.
  • =$C$1="Yes"
  • Kliknij OK aby zamknąć to okno dialogowe.

Note: W powyższym wzorze, C1 czy komórka zawiera konkretny tekst, którego chcesz użyć, a tekst „Tak” to tekst, na podstawie którego chcesz ograniczyć komórki, zmień je zgodnie z potrzebami.

3. Teraz, jeśli komórka C1 ma tekst „Tak”, wszystko można wprowadzić do zakresu A2: A9, jeśli komórka C1 ma inny tekst, nie będziesz mógł wprowadzić żadnej wartości, patrz poniżej demo:


4.13 Walidacja danych pozwala na wprowadzenie tylko dni tygodnia lub weekendów

Jeśli potrzebujesz wpisać tylko dni tygodnia (od poniedziałku do piątku) lub weekendy (sobota i niedziela) na liście komórek, Walidacja danych również może Ci pomóc, wykonaj następujące czynności:

1. Wybierz listę komórek, w których chcesz wprowadzić dni tygodnia lub dni tygodnia.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia wykonaj następujące czynności:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • A następnie wprowadź jedną z poniższych formuł do Formuła pole tekstowe, jak potrzebujesz.
  • =WEEKDAY(A2,2)<6                      (allow only weekdays)
    =WEEKDAY(A2,2)>5                      (allow only weekends)
  • Kliknij OK aby zamknąć to okno dialogowe.

Note: W powyższym wzorze, A2 to pierwsza komórka kolumny, której chcesz użyć.

3. Teraz możesz wpisać tylko datę tygodnia lub weekendu w określonych komórkach w zależności od potrzeb.


4.14 Walidacja danych pozwala na wprowadzoną datę na podstawie dzisiejszej daty

Czasami może być konieczne zezwolenie na wprowadzanie na liście komórek tylko dat większych lub mniejszych niż dzisiaj. ten Walidacja danych funkcja z DZIŚ funkcja może zrobić ci przysługę. Proszę zrób tak:

1. Wybierz listę komórek, w której chcesz wprowadzić tylko przyszłą datę (datę większą niż dzisiejsza).

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia wykonaj następujące czynności:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • A następnie wprowadź poniższą formułę do Formuła pole tekstowe.
  • =A2>Today()
  • Kliknij OK aby zamknąć to okno dialogowe.

Note: W powyższym wzorze, A2 to pierwsza komórka kolumny, której chcesz użyć.

3. Teraz do komórek można wprowadzić tylko daty większe niż dzisiejsza data, w przeciwnym razie pojawi się okno z ostrzeżeniem, aby przypomnieć, patrz zrzut ekranu:

Porady:

1. Aby umożliwić wprowadzenie daty przeszłej (daty krótszej niż dzisiejsza), zastosuj poniższy wzór w Walidacji danych:

=A2<Today()

2. Zezwól na wprowadzenie daty z określonego zakresu dat, na przykład dat w ciągu najbliższych 30 dni, wprowadź poniższą formułę do Walidacji danych:

=AND(A2>TODAY(),A2<=(TODAY()+30))


4.15 Walidacja danych pozwala wprowadzić czas na podstawie aktualnego czasu

Jeśli chcesz sprawdzić poprawność danych na przykład na podstawie bieżącego czasu, w komórkach można wpisać tylko czasy przed lub po bieżącej godzinie. Możesz stworzyć własną formułę walidacji danych, wykonaj następujące czynności:

1. Wybierz listę komórek, w których chcesz wprowadzić tylko czasy przed lub po bieżącym czasie.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia wykonaj następujące czynności:

  • Wybierz Czas z Dopuszczać Lista rozwijana.
  • Następnie wybierz mniej niż zezwolić tylko na czasy przed bieżącym czasem, lub większa niż aby zezwolić na czasy po bieżącym czasie, zgodnie z potrzebami od Dane upuścić.
  • A potem w Czas zakończenia or Czas zacząć wpisz poniższą formułę:
  • =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
  • Kliknij OK aby zamknąć to okno dialogowe.

Note: W powyższym wzorze, A2 to pierwsza komórka kolumny, której chcesz użyć.

3. Teraz do konkretnych komórek można wpisać tylko czasy przed lub po bieżącej godzinie.


4.16 Walidacja danych data konkretnego lub bieżącego roku

Aby zezwolić na wprowadzanie tylko dat z określonego roku lub bieżącego roku, możesz użyć sprawdzania poprawności danych za pomocą niestandardowej formuły opartej na funkcji ROK.

1. Wybierz listę komórek, w której chcesz wprowadzić tylko daty z określonego roku.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia wykonaj następujące czynności:

  • Wybierz Zamówienia Indywidualne z Dopuszczać Lista rozwijana.
  • A następnie wprowadź poniższą formułę do Formuła pole tekstowe.
  • =YEAR(A2)=2020
  • Kliknij OK aby zamknąć to okno dialogowe.

Note: W powyższym wzorze, A2 to pierwsza komórka kolumny, której chcesz użyć, 2020 to numer roku, który chcesz ograniczyć.

3. Następnie można wprowadzić tylko daty w roku 2020, jeśli nie, pojawi się okno komunikatu ostrzegawczego, jak pokazano na poniższym zrzucie ekranu:

Porady:

Aby zezwolić tylko na daty w bieżącym roku, możesz zastosować poniższą formułę do walidacji danych:

=YEAR(A2)=YEAR(TODAY())


4.17 Walidacja danych data w bieżącym tygodniu lub miesiącu

Jeśli chcesz zezwolić użytkownikowi na wprowadzanie dat bieżącego tygodnia lub miesiąca w określonych komórkach, ta sekcja wprowadzi kilka formuł do radzenia sobie z tym zadaniem w programie Excel.

 Zezwól na wpisanie daty bieżącego tygodnia

1. Wybierz listę komórek, w której chcesz wprowadzić tylko daty z bieżącego tygodnia.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia wykonaj następujące czynności:

  • Wybierz Data z Dopuszczać Lista rozwijana.
  • A potem wybierz pomiędzy z Dane upuścić.
  • W Data rozpoczęcia pole tekstowe wprowadź następującą formułę: =DZIŚ()-DZIEŃ.TYG(DZIŚ(),3)
  • W Data zakończenia pole tekstowe wprowadź następującą formułę: =DZIŚ()-DZIEŃ.TYG(DZIŚ();3)+6
  • Nareszcie kliknij OK przycisk.

3. Następnie można wprowadzić tylko daty w bieżącym tygodniu, inne daty zostaną uniemożliwione, jak pokazano na poniższym zrzucie ekranu:


 Zezwól na wpisanie daty bieżącego miesiąca

Aby zezwolić na wprowadzanie tylko dat z bieżącego miesiąca, wykonaj następujące czynności:

1. Wybierz listę komórek, w której chcesz wprowadzić tylko daty z bieżącego miesiąca.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, w wyskoczył Walidacja danych okno dialogowe w obszarze Ustawienia wykonaj następujące czynności:

  • Wybierz Data z Dopuszczać Lista rozwijana.
  • A następnie wybierz spośród Dane upuścić.
  • W Data rozpoczęcia pole tekstowe wprowadź następującą formułę: =DATA(ROK(DZIŚ()),MIESIĄC(DZIŚ()),1)
  • W Data zakończenia pole tekstowe wprowadź następującą formułę: =DATA(ROK(DZIŚ()),MIESIĄC(DZIŚ()),DZIEŃ(DATA(ROK(DZIŚ()),MIESIĄC(DZIŚ())+1,1)-1))
  • Nareszcie kliknij OK przycisk.

3. Od teraz do wybranych komórek można wpisywać tylko daty bieżącego miesiąca.


5. Jak edytować walidację danych w Excelu?

Aby edytować lub zmienić istniejącą regułę walidacji danych, wykonaj poniższe czynności:

1. Wybierz dowolną komórkę z regułą sprawdzania poprawności danych.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych , aby przejść do Walidacja danych w oknie dialogowym, edytuj lub zmień reguły zgodnie z potrzebami, a następnie zaznacz Zastosuj te zmiany do wszystkich innych komórek z tymi samymi ustawieniami możliwość zastosowania tej nowej reguły do ​​wszystkich innych komórek z oryginalnymi kryteriami walidacji. Zobacz zrzut ekranu:

3. Kliknij OK zapisać zmiany.


6. Jak znaleźć i wybrać komórki z walidacją danych w programie Excel?

Jeśli utworzyłeś wiele reguł sprawdzania poprawności danych w arkuszu, teraz musisz znaleźć i wybrać komórki, które zastosowały reguły sprawdzania poprawności danych, Przejdź do specjalnego Polecenie może pomóc w wyborze wszystkich rodzajów sprawdzania poprawności danych lub określonego typu sprawdzania poprawności danych.

1. Aktywuj arkusz, który chcesz znaleźć, i zaznacz komórki z walidacją danych.

2. Następnie kliknij Strona główna > Znajdź i wybierz > Przejdź do specjalnegozobacz zrzut ekranu:

3, w Przejdź do specjalnego okno dialogowe, wybierz Walidacji danych > Wszystkie kategoriezobacz zrzut ekranu:

4. Wszystkie komórki z walidacją danych zostały wybrane jednocześnie w bieżącym arkuszu.

Tips: Jeśli chcesz tylko wybrać jeden określony typ sprawdzania poprawności danych, najpierw wybierz jedną komórkę zawierającą określoną walidację danych, którą chcesz sprawdzić, a następnie przejdź do Przejdź do specjalnego okno dialogowe i wybierz Walidacji danych > Podobnie.


7. Jak skopiować regułę walidacji danych do innych komórek?

Przypuśćmy, że utworzyłeś regułę sprawdzania poprawności danych dla listy komórek, a teraz musisz zastosować tę samą regułę sprawdzania poprawności danych do innych komórek. Zamiast tworzyć regułę ponownie, możesz szybko i łatwo skopiować i wkleić istniejącą regułę do innych komórek.

1. Kliknij, aby wybrać jedną komórkę z regułą walidacji, której chcesz użyć, a następnie naciśnij Ctrl + C skopiować.

2. Następnie wybierz komórki, które chcesz zweryfikować, aby wybrać wiele niesąsiadujących komórek, naciśnij i przytrzymaj Ctrl podczas zaznaczania komórek.

3. A następnie kliknij prawym przyciskiem myszy zaznaczenie, wybierz wklej specjalnie opcja, patrz zrzut ekranu:

4, w wklej specjalnie okno dialogowe, wybierz Walidacja opcja, patrz zrzut ekranu:

5. Kliknij OK przycisk, teraz reguła walidacji jest kopiowana do nowych komórek.


8. Jak korzystać z weryfikacji danych, aby zakreślić nieprawidłowe wpisy w programie Excel?

Czasami może być konieczne utworzenie reguł sprawdzania poprawności danych dla istniejących danych, w takim przypadku niektóre nieprawidłowe dane mogą pojawić się w zakresie komórek. Jak sprawdzić nieprawidłowe dane i je zmodyfikować? W programie Excel możesz użyć Okrąg niepoprawne dane funkcja podświetlania nieprawidłowych danych czerwonym kółkiem.

Aby zakreślić nieprawidłowe dane, których potrzebujesz, zastosuj Walidacja danych funkcja, aby ustawić regułę dla zakresu danych. Proszę wykonać następujące czynności:

1. Wybierz zakres danych, w którym chcesz zakreślić nieprawidłowe dane.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, W Walidacja danych w oknie dialogowym ustaw regułę walidacji zgodnie z potrzebami, na przykład tutaj sprawdzę wartości większe niż 500, patrz zrzut ekranu:

3. Następnie kliknij OK aby zamknąć okno dialogowe. Po ustawieniu reguły sprawdzania poprawności danych kliknij Dane > Walidacja danych > Okrąg niepoprawne dane, wszystkie nieprawidłowe wartości, które są mniejsze niż 500, zostały zaznaczone czerwonym owalem. Zobacz zrzuty ekranu:

Uwagi:

  • 1. Gdy tylko poprawisz nieprawidłowe dane, czerwone kółko zniknie automatycznie.
  • 2. To Okrąg niepoprawne dane tylko funkcja może okrążyć maksymalnie 255 komórek. Po zapisaniu bieżącego skoroszytu wszystkie czerwone kółka zostaną usunięte.
  • 3. Te kręgi są niedrukowalne.
  • 4. Możesz także usunąć czerwone kółka, klikając Dane > Walidacja danych > Wyczyść koła walidacyjne.

9. Jak usunąć walidację danych w programie Excel?

Aby usunąć reguły sprawdzania poprawności danych z zakresu komórek, bieżącego arkusza lub całego skoroszytu, możesz skorzystać z następujących metod.

 Usuń walidację danych w wybranym zakresie za pomocą funkcji walidacji danych

1. Wybierz komórki z weryfikacją danych, które chcesz usunąć.

2. Następnie kliknij Dane > Walidacja danych > Walidacja danych, w wyskakującym oknie dialogowym, pod Ustawienia tab, kliknij Wyczyść wszystko przycisk, zobacz zrzut ekranu:

3. Następnie kliknij OK przycisk, aby zamknąć to okno dialogowe. A reguła sprawdzania poprawności danych zastosowana do wybranego zakresu została natychmiast usunięta.

Tips: Aby usunąć weryfikację danych z bieżącego arkusza, najpierw wybierz cały arkusz, a następnie zastosuj powyższe kroki.


 Usuń sprawdzanie poprawności danych w wybranym zakresie za pomocą przydatnej funkcji

Jeśli Kutools dla programu Excel, jego Wyczyść ograniczenia dotyczące walidacji danych funkcja może również pomóc w usunięciu reguł walidacji danych z wybranego zakresu lub całego arkusza roboczego.

Po zainstalowaniu Kutools dla programu Excel, zrób tak:

1. Wybierz zakres komórek lub cały arkusz zawiera sprawdzanie poprawności danych, które chcesz usunąć.

2. Następnie kliknij Kutools > Zapobiegaj pisaniu > Wyczyść ograniczenia dotyczące walidacji danychzobacz zrzut ekranu:

3. W wyskakującym okienku kliknij OK, a reguła sprawdzania poprawności danych została usunięta zgodnie z potrzebami.


 Usuń sprawdzanie poprawności danych ze wszystkich arkuszy roboczych za pomocą kodu VBA

Aby usunąć reguły walidacji danych z całego skoroszytu, powyższe metody będą czasochłonne, jeśli istnieje wiele arkuszy roboczych, tutaj poniższy kod może pomóc szybko poradzić sobie z tym zadaniem.

1. Przytrzymaj ALT + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.

2. Następnie kliknij wstawka > Modułi wklej następujące makro w Moduł okno.

Kod VBA: Usuń reguły sprawdzania poprawności danych we wszystkich arkuszach roboczych:

Sub RemoveDataValidation()
'Updateby Extendoffice
  Dim xwsh As Worksheet
  For Each xwsh In ActiveWorkbook.Worksheets
    xwsh.Cells.Validation.Delete
  Next xwsh
End Sub

3. Następnie naciśnij F5 klucz, aby uruchomić ten kod, a wszystkie reguły sprawdzania poprawności danych zostały natychmiast usunięte z całego skoroszytu.

 


  • Pasek Super Formula (łatwo edytować wiele wierszy tekstu i formuły); Układ do czytania (łatwe odczytywanie i edytowanie dużej liczby komórek); Wklej do filtrowanego zakresu...
  • Scal komórki / wiersze / kolumny i przechowywanie danych; Podziel zawartość komórek; Połącz zduplikowane wiersze i sumę / średnią... Zapobiegaj zduplikowanym komórkom; Porównaj zakresy...
  • Wybierz Duplikat lub Unikalny Wydziwianie; Wybierz puste wiersze (wszystkie komórki są puste); Super Find i Fuzzy Find w wielu zeszytach ćwiczeń; Losowy wybór ...
  • Dokładna kopia Wiele komórek bez zmiany odwołania do formuły; Automatyczne tworzenie odniesień do wielu arkuszy; Wstaw punktory, Pola wyboru i nie tylko ...
  • Ulubione i szybkie wstawianie formuł, Zakresy, wykresy i obrazy; Szyfruj komórki z hasłem; Utwórz listę mailingową i wysyłaj e-maile ...
  • Wyodrębnij tekst, Dodaj tekst, Usuń według pozycji, Usuń przestrzeń; Tworzenie i drukowanie podsumowań stronicowania; Konwertuj zawartość komórek i komentarze...
  • Super filtr (zapisz i zastosuj schematy filtrów do innych arkuszy); Zaawansowane sortowanie według miesiąca / tygodnia / dnia, częstotliwości i innych; Specjalny filtr pogrubieniem, kursywą ...
  • Połącz skoroszyty i arkusze robocze; Scal tabele na podstawie kluczowych kolumn; Podziel dane na wiele arkuszy; Konwersja wsadowa xls, xlsx i PDF...
  • Grupowanie tabel przestawnych według numer tygodnia, dzień tygodnia i więcej ... Pokaż odblokowane, zablokowane komórki w różnych kolorach; Podświetl komórki, które mają formułę / nazwę...
karta kte 201905
  • Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
  • Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
  • Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
officetab dół

 

Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thanks For Sharing this Great Information. I loved it.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations