Персонализирани формули за валидиране на данни

Този урок ще ви покаже как да създавате персонализирани формули при валидиране на данни в Excel и Google Sheets

Проверка на данни - трябва да започне с - Excel

Можем да напишем персонализирана формула, за да гарантираме, че данните в клетка започват с определен текст.

1. Маркирайте необходимия диапазон, например: B3: B8.
2. В лентата изберете Данни> Инструменти за данни> Валидиране на данни.

3. Изберете Персонализиран от падащото меню Разрешаване и след това въведете следната формула:

= ТОЧНО (НАЛЯВО (B3,4), „FRU-“)

Формулата използва 2 функции ТОЧНО и НАЛЯВО за да се определи дали първите 4 знака, въведени в клетката, са идентични с „FRU-“

4. Ако искате да добавите входно съобщение за вашия потребител, щракнете върху Въведено съобщение.

5. Уверете се, че квадратчето за отметка „Показва входно съобщение, когато е избрана клетка”Е отметнато и след това въведете заглавието и съобщението, от които се нуждаете.
6. Ако искате да добавите предупреждение за грешка, щракнете върху Предупреждение за грешка.

7. Можете да промените стила от Спри се на двете Внимание или Информация ако имате нужда и след това въведете заглавието и съобщението за грешка.
8. Щракнете Добре.
9. Ако сте използвали Вход Опция за съобщение, коментар ще се появи на екрана, информиращ потребителя за правилото.

10. Въведете „FRI-124“ в клетка В3. Ако сте избрали Спри се, ще се появи следното поле за съобщение.

11. Ако сте избрали Внимание, след това ще се появи това поле за съобщение. Това ви позволява да продължите, ако установите, че данните са верни.

12. Ако сте избрали Информация, след това ще се появи това поле за съобщение.

13. Ако щракнете Добре, ще имате право да продължите с неправилните данни, въведени в клетката.
14. Щракнете Отказ за да излезете от съобщението или Добре за да въведете текста в клетката.

Валидирането на данни позволява главни букви само в Excel

Можем да напишем персонализирана формула, за да гарантираме, че данните в клетка позволяват главни букви само ако текстът е въведен в клетката.

1. Маркирайте необходимия диапазон, например: B3: B8.
2. В лентата изберете Данни> Инструменти за данни> Валидиране на данни.
3. Изберете Персонализиран от падащото меню Разрешаване и след това въведете следната формула:

= ТОЧНО (B3, ГОРЕ (B3))

Формулата използва 2 функции ТОЧНО и ГОРЕН за да се определи дали въведеният в клетката текст е с главни букви. Клетките със смес от числа и текст се считат за текст и числата се игнорират в правилото.

4. Щракнете Добре.
5. Въведете „fru-124“ в клетка В3.
6. Ако сте използвали опцията Предупреждение за грешка, ще се появи вашето персонално предупреждение и съобщение за грешка. Ако не сте използвали тази опция, ще се появи стандартното предупреждение.

7. Щракнете върху Отказ, за ​​да излезете от съобщението, или Повторен опит, за да въведете отново правилния текст в клетката.
8. Въведете „123456“ в клетка В3.
9. Това ще бъде разрешено, тъй като е число, а не текст.

В следващия ни пример ще гарантираме, че в клетката може да се въвежда само главен текст.

Валидирането на данни позволява главен текст само в Excel

Можем да напишем персонализирана формула в Data Validation, която може да се използва, за да се гарантира, че данните в клетка позволяват само главен текст

ЗАБЕЛЕЖКА: ако въведете информация в клетка, която започва с текст, но съдържа числа, Excel ще вземе предвид информационния текст.

1. Маркирайте необходимия диапазон, например: B3: B8.
2. В лентата изберете Данни> Инструменти за данни> Валидиране на данни.
3. Изберете Персонализиран от падащото меню Разрешаване и след това въведете следната формула:

= И (ТОЧНО (B3, ГОРНА (B3)), ISTEXT (B3))

Формулата използва 4 функции И, ТОЧНО, ГОРЕ и ТЕКСТ за да се определи дали въведеният в клетките текст е с главни букви И за да се определи дали въведената информация всъщност е текст, а не чисто число.

4. Щракнете Добре.
5. Въведете „fru-124“ в клетка В3.
6. Ако сте използвали опцията Предупреждение за грешка, ще се появи вашето персонално предупреждение и съобщение за грешка. Ако не сте използвали тази опция, ще се появи стандартното предупреждение.

7. Щракнете върху Отказ, за ​​да излезете от съобщението, или Повторен опит, за да въведете отново правилния текст в клетката.
8. Въведете „123456“ в клетка В3.
9. Ще получите отново съобщение за грешка.
10. Щракнете върху Отказ, за ​​да излезете от съобщението, или Повторен опит, за да въведете отново правилния текст в клетката.

Валидирането на данни предотвратява пространства в Excel

Можем да напишем персонализирана формула, за да гарантираме, че в данните, въведени в диапазон от клетки, не се въвеждат интервали.

1. Маркирайте необходимия диапазон, например: B3: B8.
2. В лентата изберете Данни> Инструменти за данни> Валидиране на данни.
3. Изберете Персонализиран от падащото меню Разрешаване и след това въведете следната формула:

= B3 = ЗАМЕСТНИК (B3, ””, „”)

Формулата използва функцията SUBSTITUTE, за да провери дали не съществуват интервали.

4. Щракнете Добре.
5. Въведете „FRU - 124“ в клетка В4.
6. Ако сте използвали опцията Предупреждение за грешка, ще се появи вашето персонално предупреждение и съобщение за грешка. Ако не сте използвали тази опция, ще се появи стандартното предупреждение.

7. Щракнете върху Отказ, за ​​да излезете от съобщението, или Повторен опит, за да въведете отново правилния текст в клетката.

Валидирането на данни предотвратява дублирането в Excel

Можем да напишем персонализирана формула, която да ни попречи да въвеждаме дублирана информация в диапазон от клетки.

1. Маркирайте необходимия диапазон, например: B3: B8.
2. В лентата изберете Данни> Инструменти за данни> Валидиране на данни.
3. Изберете Клиент от падащото меню Разрешаване и след това въведете следната формула:

= COUNTIF ($ B $ 3: $ B $ 8, B3) <2

Формулата използва COUNTIF функция и употреба АБСОЛЮТНИ в диапазона B3: B8, за да сте сигурни, че това е списъкът, който COUNTIF функцията гледа, когато проверява дали има дублирани стойности.

4. Щракнете Добре.
5. Въведете „FRU-123“ в клетка D4.
6. Ако сте използвали опцията Предупреждение за грешка, ще се появи вашето персонално предупреждение и съобщение за грешка. Ако не сте използвали тази опция, ще се появи стандартното предупреждение.

7. Щракнете върху Отказ, за ​​да излезете от съобщението, или Повторен опит, за да въведете отново правилния текст в клетката.

Валидирането на данни съществува в списък в Excel

Можем да напишем персонализирана формула, за да гарантираме, че само определен текст е въведен в клетка.

1. Маркирайте необходимия диапазон, например: D3: D8.
2. В лентата изберете Данни> Инструменти за данни> Валидиране на данни.
3. Изберете Персонализиран от падащото меню Разрешаване и след това въведете следната формула:

= COUNTIF ($ F $ 6: $ F $ 8, D3)> 0

Формулата използва COUNTIF функция и употреба АБСОЛЮТНИ в диапазона F3: F8, за да се уверите, че това е списъкът, който COUNTIF функцията гледа, когато проверява дали е въведен правилния текст.

4. Щракнете Добре.
5. Въведете „Single“ в клетка D4.
6. Ако сте използвали опцията Предупреждение за грешка, ще се появи вашето персонално предупреждение и съобщение за грешка. Ако не сте използвали тази опция, ще се появи стандартното предупреждение.

7. Щракнете върху Отказ, за ​​да излезете от съобщението, или Повторен опит, за да въведете отново правилния текст в клетката.

Валидирането на данни не съществува в списък в Excel

Можем да използваме персонализирана формула, за да гарантираме, че конкретен текст не е въведен в клетка.

1. Маркирайте необходимия диапазон, например: C3: C8.
2. В лентата изберете Данни> Инструменти за данни> Валидиране на данни.
3. Изберете Клиент от падащото меню Разрешаване и след това въведете следната формула:

= COUNTIF ($ F $ 6: $ F $ 8, C3) = 0

Формулата използва COUNTIF функция и употреба АБСОЛЮТНИ в диапазона F3: F8, за да се уверите, че това е списъкът, който COUNTIF функцията гледа, когато проверява дали правилния текст е въведен.

4. Щракнете Добре.
5. Въведете „говеждо месо“ в клетка С4.
6. Ако сте използвали опцията Предупреждение за грешка, ще се появи вашето персонално предупреждение и съобщение за грешка. Ако не сте използвали тази опция, ще се появи стандартното предупреждение.

7. Щракнете върху Отказ, за ​​да излезете от съобщението, или Повторен опит, за да въведете отново правилния текст в клетката.

В Excel се въвеждат само числа за валидиране на данни

Можем да използваме персонализирана формула, за да гарантираме, че в клетка е въведено само число.

1. Маркирайте необходимия диапазон, напр .: E3: E8.
2. В лентата изберете Данни> Инструменти за данни> Валидиране на данни.
3. Изберете Клиент от падащото меню Разрешаване и след това въведете следната формула:

= ISNUMBER (F3: F8)

Формулата използва функцията ISNUMBER, за да гарантира, че число е въведено в клетките в диапазона.

4. Щракнете Добре.
5. Въведете „девет“ в клетка F4.
6. Ако сте използвали опцията Предупреждение за грешка, ще се появи вашето персонално предупреждение и съобщение за грешка. Ако не сте използвали тази опция, ще се появи стандартното предупреждение.

7. Щракнете върху Отказ, за ​​да излезете от съобщението, или Повторен опит, за да въведете отново правилния текст в клетката.

Проверката на данните не надвишава стойността в Excel

Можем да използваме персонализирана формула, за да гарантираме, че въведените стойности в клетка не надвишават определена стойност.

1. Маркирайте необходимия диапазон, напр .: E3: E8.
2. В лентата изберете Данни> Инструменти за данни> Валидиране на данни.
3. Изберете Клиент от падащото меню Разрешаване и след това въведете следната формула:

= E3 <= $ G $ 6

Формулата an АБСОЛЮТНО в диапазона G6, за да се гарантира, че това е стойността, която правилото проверява при въвеждане на данни в E3.

4. Щракнете Добре.
5. Въведете „9“ в клетка E4.
6. Ако сте използвали опцията Предупреждение за грешка, ще се появи вашето персонално предупреждение и съобщение за грешка. Ако не сте използвали тази опция, ще се появи стандартното предупреждение.

7. Щракнете върху Отказ, за ​​да излезете от съобщението, или Повторен опит, за да въведете отново правилния текст в клетката.

Проверката на данните не надвишава общата стойност в Excel

Можем да използваме персонализирана формула, за да гарантираме, че стойностите, въведени в диапазон от клетки, не надвишават определена обща стойност за диапазона

1. Маркирайте необходимия диапазон, например: F3: F8.
2. В лентата изберете Данни> Инструменти за данни> Валидиране на данни.
3. Изберете Клиент от падащото меню Разрешаване и след това въведете следната формула:

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

Формулата използва SUM функция и употреба АБСОЛЮТНИ в диапазона F3: F8, за да сте сигурни, че това е списъкът, който SUM функцията гледа, когато проверява дали общата стойност на диапазона не е по -голяма от стойността, въведена в H6.

4. Щракнете Добре.
5. Въведете „40“ в клетка F4.
6. Ако сте използвали опцията Предупреждение за грешка, ще се появи вашето персонално предупреждение и съобщение за грешка. Ако не сте използвали тази опция, ще се появи стандартното предупреждение.

7. Щракнете върху Отказ, за ​​да излезете от съобщението, или Повторен опит, за да въведете отново правилния текст в клетката.

Дати за делнични дни за валидиране на данни Само в Excel.

Можем да използваме персонализирана формула, за да гарантираме, че само делничните дни се въвеждат, когато датите се използват в Excel.

1. Маркирайте необходимия диапазон, например: G3: G8.
2. В лентата изберете Данни> Инструменти за данни> Валидиране на данни.
3. Изберете Клиент от падащото меню Разрешаване и след това въведете следната формула:

= СЕДМИЦА (F3,2) <6

Функцията за делничен ден проверява дали денят, съдържащ се в датата, не е събота или неделя.

4. Променете датата в G5, за да се покаже събота (напр. 9^th Май 2022 г.).
5. Ако сте използвали опцията Предупреждение за грешка, ще се появи вашето персонално предупреждение и съобщение за грешка. Ако не сте използвали тази опция, ще се появи стандартното предупреждение.

7. Щракнете върху Отказ или Повторен опит, за да въведете алтернативна дата.

Бъдеща дата за валидиране на данни Само в Excel

Можем да създадем персонализирана формула, която да позволява само на потребителя да въведе дата, която е в бъдеще.

1. Маркирайте необходимия диапазон, например: G3: G8.
2. В лентата изберете Данни> Инструменти за данни> Валидиране на данни.
3. Изберете Клиент от падащото меню Разрешаване и след това въведете следната формула:

= G3> ДНЕС ()

Формулата използва ДНЕС функция за проверка дали датата, въведена в клетката, е по -голяма от днешната.

4. Променете датата в G5 на вчера.
5. Ако сте използвали опцията Предупреждение за грешка, ще се появи вашето персонално предупреждение и съобщение за грешка. Ако не сте използвали тази опция, ще се появи стандартното предупреждение.

6. Щракнете върху Отказ или Повторен опит, за да въведете алтернативна дата.

Валидирането на данните трябва да започне с в Google Таблици

1. Маркирайте необходимия диапазон, например: B3: B8.
2. В менюто изберете Данни> Валидиране на данни.

3. Диапазонът от клетки вече ще бъде попълнен.

4. Изберете Персонализирана формула е от падащия списък Критерии.

5. Въведете формулата.

= ТОЧНО (НАЛЯВО (B3,4), „FRU-“)

6. Изберете едно от двете Показване на предупреждение или Отхвърляне на въвеждане ако данните са невалидни.

7. Можете да въведете някакъв помощен текст за валидиране, ако имате нужда.

8. Щракнете върху Запазване.

9. Щракнете върху B3, за да видите помощния текст за валидиране

10. Тип FRI-123
11. Ако сте избрали Показване на предупреждение, ще се появи следното съобщение.

12. Като алтернатива, ако сте избрали Отхвърляне на въвеждане на невалидни данни, ще бъдете възпрепятствани от въвеждането на данните и на екрана се появява следното съобщение.

Останалите примери за персонализирани формули в Google Sheets работят по абсолютно същия начин.