Работата с електронни таблици може да бъде хаотично преживяване, ако въведените данни не следват логически ред или не преминават правилна проверка. Excel ни предлага много мощен инструмент за поддържане на ред и точност. В тази публикация ще разгледаме... Как да валидирам въведените данни в Excel и по този начин да контролират точно какъв тип информация може да бъде въведена във всяка клетка, с голямо разнообразие от критерии и условия.
Валидирането на данни обхваща много аспекти: от ограничаване на числови стойности до свързване на клетки за обуславяне на тяхното съдържание, до персонализирани съобщения и формули, ще научите всичко необходимо, за да овладеете този важен инструмент.
За какво се използва валидирането на данни в Excel?
Валидиране на билети в Excel позволява задайте правила за типа стойности, които могат да бъдат въведени в клеткаТова гарантира съгласуваност на данните, предотвратява грешки от момента на въвеждане и подобрява целостта на всеки отчет, списък или база данни.
Някои ситуации, в които този инструмент е особено полезен, са:
- Ограничаване на стойностите до предварително дефиниран списъкНапример, избор на отдел между счетоводство, човешки ресурси или логистика.
- Задайте валидни диапазони за числа, дати или часове. Това предотвратява нереалистични суми или печатни грешки.
- Контролирайте дължината на текста така че описанието да не надвишава определен брой знаци.
- Входни условия въз основа на други клетки, като максимална стойност, която зависи от бюджета, дефиниран в друга клетка.
Типове валидиране: Как да ограничим входните данни по тип данни
Excel позволява различни критерии за прилагане на ограничения. Това са най-често използваните:
Валидация на цяло число
За да контролирате въвеждането само на цели числа в определен диапазон:
- Изберете клетката или клетките.
- Отидете на Данни → Валидиране на данни.
- En позволиизбирам Цяло число.
- Определя дали трябва да бъде между, по-малък от, по-голям отИ др
- Въведете ограниченията. Можете също да използвате формула. Например, ако минимумът трябва да бъде два пъти броя на децата в клетка F1, използвайте: =2*F1
Валидиране на десетични числа
Същата процедура, както при целите числа, но в позволи избирам десетиченИдеално за проценти, заплати, комисионни:
Пример: За да ограничите комисионните до максимум 6% от заплатата на търговския представител, намиращ се в E1, задайте максимален като =E1*6%
Валидиране на дата
Позволява ви да ограничите влизането до конкретни дати или времеви диапазони:
- Изберете клетката.
- En позволиизбирам дата.
- En Данни, изберете критерия (между, по-голямо от, по-малко от…)
- Посочете датите. Можете също да използвате формули като =ДНЕС() o =ДНЕС()+3.
Това е много полезно за заявки за отпуск, дати на доставка, крайни срокове и др.
Валидиране на часовете
Подобно на датите, това ви позволява да ограничите стойностите за време във формат чч:мм:
Пример: За да разрешите срещи само между 8:00 и 17:00, можете да го свържете с две клетки (E2 и F2) и да използвате =E2 y =F2 като граници на валидиране.
Валидиране по дължина на текста
Когато е необходимо дадено поле да не надвишава определен брой знаци:
- избирам Разрешаване → Дължина на текста.
- En Данни Избирам по-малко или равно на.
- Въведете максималния разрешен брой. Пример: 25.
Валидиране, обусловено от други клетки
Една от най-мощните функции на Excel е, че можете да използвате формули, които зависят от други клетки. Това позволява динамична проверка въз основа на други стойности.
Например, позволете данни в клетка само ако не надвишават бюджета, посочен в E1. Конфигурирайте:
- Позволява: Цяло число
- факти: По-малко или равно на
- Максимум: =E1
Полезни функции за автоматично валидиране на данни
Ако искате автоматично да допълните друга стойност, когато изберете една стойност без програмиране, можете да използвате тези функции:
Функция COINCIDIR
Връща относителната позиция на стойност в диапазон:
=MATCH(търсена_стойност; диапазон_от_клетки; 0)
Третият аргумент трябва да е 0, ако се желае точно съвпадение.
Функция INDEX
Връща стойността в пресечната точка на ред и колона в матрица:
=INDEX(масив, ред, колона)
Ако имате списък с тръби с номинален диаметър и вътрешен диаметър, можете да използвате и двете функции, така че когато изберете стойност, свързаната с нея стойност да се връща автоматично.
Персонализирано въвеждане и съобщения за грешки
Excel ви позволява да показвате информационни съобщения, когато потребителят избере клетка:
- Входящо съобщение: появява се при избиране на клетката, полезно за даване на инструкции.
- Съобщение за грешка: Показва се, когато е въведена невалидна стойност. Можете да избирате от:
- Задържане: предотвратява въвеждането на неправилни данни.
- предупреждение: позволява въвеждането им след предупреждение.
- Информация: съобщава за грешката, но ви позволява да продължите.
Практически съвети за работа с валидации
- Ширината на падащия списък зависи от ширината на клетката. Настройте дали опциите да бъдат отрязани.
- Моля, първо настройте валидациите, преди да проверите файла.
- Ако споделяте файла, направете го след като добре дефинирате валидациите.
- Ако приложите валидиране към клетки с данни, Excel няма автоматично да маркира грешки. Използвайте „Кръг“, за да ги намерите.
- За лесно премахване на валидация, отидете на Данни → Валидиране → Изчистване на всички.
- Употреба Търсене и избор → Валидиране на данни за да намерите всички клетки с приложени правила.
- Ако създадете разширен падащ списък, можете да дефинирате имена за диапазоните и да скриете листа, съдържащ опциите.
- Ако дадена клетка има същите настройки за валидиране като другите, можете да приложите глобални промени към всички тях.
- Ако валидирането не работи, проверете дали не пишете едновременно или дали няма грешки във формулите.
Овладяването на валидирането на данни в Excel не само внася ред и прецизност във вашите електронни таблици, но и подобрява работата на другите потребители, които взаимодействат с вашите работни книги. Независимо дали става въпрос за ограничаване на грешките, автоматизиране на процеси или установяване на интелигентни контроли, тази функция е от съществено значение, когато е добре разбрана и прилагана внимателно.