Niestandardowe formuły sprawdzania poprawności danych

Ten samouczek pokaże Ci, jak tworzyć niestandardowe formuły w walidacji danych w programie Excel i Arkuszach Google

Walidacja danych - musi zaczynać się od - Excel

Możemy napisać niestandardową formułę, aby upewnić się, że dane w komórce zaczynają się od określonego tekstu.

1. Zaznacz wymagany zakres, np.: B3:B8.
2. Na Wstążce wybierz Dane > Narzędzia danych > Walidacja danych.

3. Wybierz Zwyczaj z listy rozwijanej Zezwalaj, a następnie wpisz następującą formułę:

=DOKŁADNIE(LEWO(B3,4);”FRU-”)

Formuła wykorzystuje 2 funkcje DOKŁADNY oraz LEWO aby określić, czy pierwsze 4 znaki wprowadzone do komórki są identyczne z „FRU-”

4. Jeśli chcesz dodać wiadomość wejściową dla swojego użytkownika, kliknij Wprowadź wiadomość.

5. Upewnij się, że pole wyboru „Pokaż komunikat wejściowy, gdy komórka jest zaznaczona”, a następnie wpisz żądany tytuł i wiadomość.
6. Jeśli chcesz dodać alert o błędzie, kliknij Ostrzeżenie o błędzie.

7. Możesz zmienić styl z Zatrzymać albo Ostrzeżenie lub Informacja jeśli potrzebujesz, a następnie wpisz tytuł i komunikat o błędzie.
8. Kliknij ok.
9. Jeśli korzystałeś z Wejście Opcja wiadomości, na ekranie pojawi się komentarz informujący użytkownika o regule.

10. Wpisz „FRI-124” w komórce B3. Jeśli wybrałeś Zatrzymać, pojawi się następujący komunikat.

11. Jeśli wybrałeś Ostrzeżenie, pojawi się to okno komunikatu. Dzięki temu możesz kontynuować, jeśli stwierdzisz, że dane są prawidłowe.

12. Jeśli wybrałeś Informacja, pojawi się to okno komunikatu.

13. Jeśli klikniesz ok, będziesz mógł kontynuować z błędnymi danymi wprowadzonymi do komórki.
14. Kliknij Anulować aby wyjść z wiadomości lub ok aby wprowadzić tekst do komórki.

Walidacja danych Zezwalaj tylko na wielkie litery w programie Excel

Możemy napisać niestandardową formułę, aby upewnić się, że dane w komórce dopuszczają tylko wielkie litery, jeśli tekst jest wprowadzany do komórki.

1. Zaznacz wymagany zakres, np.: B3:B8.
2. Na Wstążce wybierz Dane > Narzędzia danych > Walidacja danych.
3. Wybierz Zwyczaj z listy rozwijanej Zezwalaj, a następnie wpisz następującą formułę:

=DOKŁADNE(B3;GÓRNE(B3))

Formuła wykorzystuje 2 funkcje DOKŁADNY oraz GÓRNY aby określić, czy tekst wprowadzony do komórki jest pisany wielkimi literami. Komórki zawierające kombinację liczby i tekstu są traktowane jako tekst, a liczby są ignorowane w regule.

4. Kliknij ok.
5. Wpisz „fru-124” w komórce B3.
6. Jeśli użyłeś opcji Alert o błędzie, pojawi się niestandardowe ostrzeżenie i komunikat o błędzie. Jeśli nie skorzystałeś z tej opcji, pojawi się standardowe ostrzeżenie.

7. Kliknij Anuluj, aby wyjść z wiadomości, lub Ponów, aby ponownie wprowadzić poprawny tekst do komórki.
8. Wpisz „123456” w komórce B3.
9. Będzie to dozwolone, ponieważ jest to liczba, a nie tekst.

W naszym następnym przykładzie upewnimy się, że do komórki można wprowadzić tylko tekst pisany wielkimi literami.

Walidacja danych Zezwalaj tylko na tekst pisany wielkimi literami w programie Excel

Możemy napisać niestandardową formułę w Walidacji danych, aby upewnić się, że dane w komórce zezwalają tylko na tekst pisany wielkimi literami

UWAGA: jeśli wprowadzisz informacje do komórki, która zaczyna się od tekstu, ale zawiera liczby, program Excel weźmie pod uwagę tekst informacyjny.

1. Zaznacz wymagany zakres, np.: B3:B8.
2. Na Wstążce wybierz Dane > Narzędzia danych > Walidacja danych.
3. Wybierz Zwyczaj z listy rozwijanej Zezwalaj, a następnie wpisz następującą formułę:

=I(DOKŁADNE(B3;GÓRNE(B3));ITEKST(B3))

Formuła wykorzystuje 4 funkcje ORAZ, DOKŁADNY, GÓRNY oraz TEKST aby określić, czy tekst wprowadzony do komórek jest pisany wielkimi literami ORAZ aby określić, czy wprowadzone informacje są w rzeczywistości tekstem, a nie czystą liczbą.

4. Kliknij ok.
5. Wpisz „fru-124” w komórce B3.
6. Jeśli użyłeś opcji Alert o błędzie, pojawi się niestandardowe ostrzeżenie i komunikat o błędzie. Jeśli nie skorzystałeś z tej opcji, pojawi się standardowe ostrzeżenie.

7. Kliknij Anuluj, aby wyjść z wiadomości, lub Ponów, aby ponownie wprowadzić poprawny tekst do komórki.
8. Wpisz „123456” w komórce B3.
9. Ponownie otrzymasz komunikat o błędzie.
10. Kliknij Anuluj, aby wyjść z wiadomości, lub Ponów, aby ponownie wprowadzić poprawny tekst do komórki.

Walidacja danych Zapobiegaj spacji w programie Excel

Możemy napisać niestandardową formułę, aby zapewnić, że w danych wprowadzanych w zakresie komórek nie zostaną wprowadzone spacje.

1. Zaznacz wymagany zakres, np.: B3:B8.
2. Na Wstążce wybierz Dane > Narzędzia danych > Walidacja danych.
3. Wybierz Zwyczaj z listy rozwijanej Zezwalaj, a następnie wpisz następującą formułę:

=B3=ZAMIENNIK(B3; ” ” , „”)

Formuła używa funkcji SUBSTITUTE w celu sprawdzenia, czy spacje nie istnieją.

4. Kliknij ok.
5. Wpisz „FRU - 124” w komórce B4.
6. Jeśli użyłeś opcji Alert o błędzie, pojawi się niestandardowe ostrzeżenie i komunikat o błędzie. Jeśli nie skorzystałeś z tej opcji, pojawi się standardowe ostrzeżenie.

7. Kliknij Anuluj, aby wyjść z wiadomości, lub Ponów, aby ponownie wprowadzić poprawny tekst do komórki.

Sprawdzanie poprawności danych Zapobieganie duplikatom w programie Excel

Możemy napisać niestandardową formułę, aby uniemożliwić nam wprowadzanie zduplikowanych informacji do zakresu komórek.

1. Zaznacz wymagany zakres, np.: B3:B8.
2. Na Wstążce wybierz Dane > Narzędzia danych > Walidacja danych.
3. Wybierz pozycję Klient z listy rozwijanej Zezwalaj, a następnie wpisz następującą formułę:

=LICZ.JEŻELI(3 złB3:8 złB8;B3)<2

Formuła wykorzystuje LICZ.JEŻELI funkcja i zastosowania ABSOLUTÓW w zakresie B3:B8, aby upewnić się, że jest to lista, którą LICZ.JEŻELI Funkcja sprawdza, kiedy sprawdza, czy istnieją zduplikowane wartości.

4. Kliknij ok.
5. Wpisz „FRU-123” w komórce D4.
6. Jeśli użyłeś opcji Alert o błędzie, pojawi się niestandardowe ostrzeżenie i komunikat o błędzie. Jeśli nie skorzystałeś z tej opcji, pojawi się standardowe ostrzeżenie.

7. Kliknij Anuluj, aby wyjść z wiadomości, lub Ponów, aby ponownie wprowadzić poprawny tekst do komórki.

Walidacja danych istnieje na liście w programie Excel

Możemy napisać niestandardową formułę zapewniającą, że do komórki zostanie wprowadzony tylko określony tekst.

1. Zaznacz wymagany zakres, np.: D3:D8.
2. Na Wstążce wybierz Dane > Narzędzia danych > Walidacja danych.
3. Wybierz opcję Niestandardowe z listy rozwijanej Zezwalaj, a następnie wpisz następującą formułę:

=LICZ.JEŻELI(6$F$:8$F;D3)>0

Formuła wykorzystuje LICZ.JEŻELI funkcja i zastosowania ABSOLUTÓW w zakresie F3:F8, aby upewnić się, że jest to lista, którą LICZ.JEŻELI Funkcja sprawdza, kiedy sprawdza, czy wprowadzany jest poprawny tekst.

4. Kliknij ok.
5. Wpisz „Single” w komórce D4.
6. Jeśli użyłeś opcji Alert o błędzie, pojawi się niestandardowe ostrzeżenie i komunikat o błędzie. Jeśli nie skorzystałeś z tej opcji, pojawi się standardowe ostrzeżenie.

7. Kliknij Anuluj, aby wyjść z wiadomości, lub Ponów, aby ponownie wprowadzić poprawny tekst do komórki.

Sprawdzanie poprawności danych nie istnieje na liście w programie Excel

Możemy użyć niestandardowej formuły, aby upewnić się, że określony tekst nie zostanie wprowadzony do komórki.

1. Zaznacz wymagany zakres, np.: C3:C8.
2. Na Wstążce wybierz Dane > Narzędzia danych > Walidacja danych.
3. Wybierz pozycję Klient z listy rozwijanej Zezwalaj, a następnie wpisz następującą formułę:

=LICZ.JEŻELI($F$6:$F$8;C3)=0

Formuła wykorzystuje LICZ.JEŻELI funkcja i zastosowania ABSOLUTÓW w zakresie F3:F8, aby upewnić się, że jest to lista, którą LICZ.JEŻELI Funkcja sprawdza, kiedy sprawdza, czy wprowadzany jest poprawny tekst.

4. Kliknij ok.
5. Wpisz „Wołowina” w komórce C4.
6. Jeśli użyłeś opcji Alert o błędzie, pojawi się niestandardowe ostrzeżenie i komunikat o błędzie. Jeśli nie skorzystałeś z tej opcji, pojawi się standardowe ostrzeżenie.

7. Kliknij Anuluj, aby wyjść z wiadomości, lub Ponów, aby ponownie wprowadzić poprawny tekst do komórki.

W Excelu wprowadzane są tylko liczby do sprawdzania poprawności danych

Możemy użyć niestandardowej formuły, aby zapewnić, że do komórki zostanie wprowadzona tylko liczba.

1. Zaznacz wymagany zakres, np.: E3:E8.
2. Na Wstążce wybierz Dane > Narzędzia danych > Walidacja danych.
3. Wybierz pozycję Klient z listy rozwijanej Zezwalaj, a następnie wpisz następującą formułę:

=CZY.LICZBA(F3:F8)

Formuła używa funkcji CZY.LICZBA, aby zapewnić wprowadzenie liczby do komórek w zakresie.

4. Kliknij ok.
5. Wpisz „dziewięć” w komórce F4.
6. Jeśli użyłeś opcji Alert o błędzie, pojawi się niestandardowe ostrzeżenie i komunikat o błędzie. Jeśli nie skorzystałeś z tej opcji, pojawi się standardowe ostrzeżenie.

7. Kliknij Anuluj, aby wyjść z wiadomości, lub Ponów, aby ponownie wprowadzić poprawny tekst do komórki.

Walidacja danych nie przekracza wartości w programie Excel

Możemy użyć niestandardowej formuły, aby wartości wprowadzone do komórki nie przekraczały określonej wartości.

1. Zaznacz wymagany zakres, np.: E3:E8.
2. Na Wstążce wybierz Dane > Narzędzia danych > Walidacja danych.
3. Wybierz pozycję Klient z listy rozwijanej Zezwalaj, a następnie wpisz następującą formułę:

=E3<=$6G$

Formuła an ABSOLUTNY w zakresie G6, aby upewnić się, że jest to wartość, którą reguła sprawdza, gdy dane są wprowadzane do E3.

4. Kliknij ok.
5. Wpisz „9” w komórce E4.
6. Jeśli użyłeś opcji Alert o błędzie, pojawi się niestandardowe ostrzeżenie i komunikat o błędzie. Jeśli nie skorzystałeś z tej opcji, pojawi się standardowe ostrzeżenie.

7. Kliknij Anuluj, aby wyjść z wiadomości, lub Ponów, aby ponownie wprowadzić poprawny tekst do komórki.

Walidacja danych nie przekracza sumy w programie Excel

Możemy użyć niestandardowej formuły, aby upewnić się, że wartości wprowadzone do zakresu komórek nie przekraczają określonej łącznej wartości dla zakresu

1. Zaznacz wymagany zakres, np.: F3:F8.
2. Na Wstążce wybierz Dane > Narzędzia danych > Walidacja danych.
3. Wybierz pozycję Klient z listy rozwijanej Zezwalaj, a następnie wpisz następującą formułę:

=SUMA($F$3:$F$8)<=$H$6

Formuła wykorzystuje SUMA funkcja i zastosowania ABSOLUTÓW w zakresie F3:F8, aby upewnić się, że jest to lista, którą SUMA funkcja patrzy, kiedy sprawdza, czy suma zakresu nie jest większa niż wartość wprowadzona w H6.

4. Kliknij ok.
5. Wpisz „40” w komórce F4.
6. Jeśli użyłeś opcji Alert o błędzie, pojawi się niestandardowe ostrzeżenie i komunikat o błędzie. Jeśli nie skorzystałeś z tej opcji, pojawi się standardowe ostrzeżenie.

7. Kliknij Anuluj, aby wyjść z wiadomości, lub Ponów, aby ponownie wprowadzić poprawny tekst do komórki.

Daty dni tygodnia sprawdzania poprawności danych Tylko w programie Excel.

Możemy użyć niestandardowej formuły, aby upewnić się, że w programie Excel są wprowadzane tylko dni tygodnia.

1. Zaznacz wymagany zakres, np.: G3:G8.
2. Na Wstążce wybierz Dane > Narzędzia danych > Walidacja danych.
3. Wybierz pozycję Klient z listy rozwijanej Zezwalaj, a następnie wpisz następującą formułę:

=DZIEŃ.TYG(F3,2)<6

Funkcja dni tygodnia sprawdza, czy dzień zawarty w dacie nie jest sobotą ani niedzielą.

4. Zmień datę w G5, aby pokazać sobotę (np. 9^NS maj 2022).
5. Jeśli użyłeś opcji Alert o błędzie, pojawi się niestandardowe ostrzeżenie i komunikat o błędzie. Jeśli nie skorzystałeś z tej opcji, pojawi się standardowe ostrzeżenie.

7. Kliknij Anuluj lub Ponów, aby wprowadzić alternatywną datę.

Walidacja danych Przyszła data tylko w Excelu

Możemy stworzyć niestandardową formułę, aby użytkownik mógł wprowadzić tylko datę, która jest w przyszłości.

1. Zaznacz wymagany zakres, np.: G3:G8.
2. Na Wstążce wybierz Dane > Narzędzia danych > Walidacja danych.
3. Wybierz pozycję Klient z listy rozwijanej Zezwalaj, a następnie wpisz następującą formułę:

=G3>DZIŚ()

Formuła wykorzystuje DZIŚ funkcja sprawdzająca, czy data wpisana do komórki jest większa niż dzisiejsza.

4. Zmień datę w G5 na wczoraj.
5. Jeśli użyłeś opcji Alert o błędzie, pojawi się niestandardowe ostrzeżenie i komunikat o błędzie. Jeśli nie skorzystałeś z tej opcji, pojawi się standardowe ostrzeżenie.

6. Kliknij Anuluj lub Ponów, aby wprowadzić alternatywną datę.

Sprawdzanie poprawności danych musi rozpocząć się w Arkuszach Google

1. Zaznacz wymagany zakres, np.: B3:B8.
2. W menu wybierz Dane > Walidacja danych.

3. Zakres komórek zostanie już wypełniony.

4. Wybierz Formuła niestandardowa jest z listy rozwijanej Kryteria.

5. Wpisz formułę.

=DOKŁADNIE(LEWO(B3,4);”FRU-”)

6. Wybierz albo Pokaż ostrzeżenie lub Odrzuć dane wejściowe jeśli dane są nieprawidłowe.

7. W razie potrzeby możesz wpisać tekst pomocy dotyczący walidacji.

8. Kliknij Zapisz.

9. Kliknij B3, aby zobaczyć tekst pomocy Walidacji

10. Wpisz FRI-123
11. Jeśli wybrałeś Pokaż ostrzeżenie, pojawi się następujący komunikat.

12. Alternatywnie, jeśli wybrałeś Odrzuć dane wejściowe w przypadku nieprawidłowych danych, nie będziesz mógł wprowadzać danych, a na ekranie pojawi się następujący komunikat.

Pozostałe przykłady niestandardowych formuł w Arkuszach Google działają dokładnie w ten sam sposób.