Прилагане на проверка на данни към клетки

Можете да използвате проверката на данни за ограничаване на типа на данните или стойностите, които потребителите въвеждат в дадена клетка. Едно от най-често срещаните приложения на проверката на данни е за създаване на падащ списък. Гледайте това видео от Дъг от Office, тъй като той прави бърз общ преглед на проверката на данни.

Нова задача

Изтеглете нашите примери

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

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

Добавяне на проверка на данни към клетка или диапазон

Забележка : Първите три стъпки в този раздел са за добавяне на всякакъв тип проверка на данни. Стъпки 4 – 8 са специално за създаване на падащ списък.

  1. Изберете една или няколко клетки, които да се проверяват.

  2. В раздела Данни, в групата Инструменти за данни щракнете върху Проверка на данни.

    "Проверка на данни" се намира в раздела "Данни", групата "Инструменти за данни"
  3. В раздела Настройки, в полето Позволи изберете Списък.

    Раздел "Настройки" в диалоговия прозорец ''Проверка на данни''
  4. В полето Източник въведете стойностите на списъка, разделени с точка и запетая. Например:

    1. За да сведете отговора до два възможни избора ("Имате ли деца?" например), въведете Да;Не.

    2. За да ограничите репутацията за качеството на търговец до три категории, въведете Ниско;Средно;Високо.

      Забележка : Тези стъпки обикновено се препоръчват само за елементи от списъци, които не е вероятно да се променят. Ако имате списък, който може да се промени, или ако трябва да добавяте или премахвате елементи с течение на времето, тогава ще е по-удобно да следвате стъпката от най-добри практики по-долу.

      Най-добра практика: Можете също създадете елементи на списък чрез препратка към диапазон от клетки на друго място в работната книга. Най-ефикасният начин е да създадете списъка и след това да го форматирате като Таблица на Excel (в раздела Начало изберете Стилове > Форматиране като таблица > изберете стила на таблица, който е най-подходящ за вас). След това изберете тялото на диапазона от данни в таблицата, което е частта от таблицата, където се намира само списъкът, а не заглавката на таблицата ( в този случай "Отдел", и й дайте смислено име в полето "Име" над колона A.

      Въведете смислено име за списъка в полето за име

    Сега, вместо да въвеждате стойностите на списъка в полето Източник за проверка на данни, можете да добавите името, което току-що сте дефинирали, със знак за равенство (=) отпред.

    Поставете пред името на таблицата знак =

    Най-голямото удобство при използването на таблица е, че когато добавяте или премахвате елементи от списъка, вашият списък за проверка на данни се актуализира автоматично.

    Забележка : Най-добре да сложите вашите списъци в отделен работен лист (скрит, ако е нужно) така, че никой да не може да ги редактира.

  5. Проверете дали е отметнато квадратчето Списък на допустимите стойности. В противен случай няма да може да виждате стрелката за падащия списък до клетката.

    Падащ списък в клетка, който се показва до клетката
  6. За да определите как искате да се обработват празни (null) стойности, отметнете или изчистете квадратчето Игнорирай празните.

    Забележка : Ако допустимите за вас стойности са на базата на диапазон от клетки с дефинирано име и в този диапазон има празна клетка, избирането на квадратчето Игнорирай празните позволява в проверяваната клетка да бъдат въвеждани произволни стойности. Това е вярно и за всички клетки, към които има препратки от формулите за проверка: ако някоя от тези клетки е празна, отметката в квадратчето Игнорирай празните позволява в проверяваната клетка да се въвеждат произволни стойности.

  7. Изпробвайте проверката на данни, за да се уверите, че работи правилно. Опитайте да въведете както валидни, така и невалидни данни, за да се уверите, че настройките ви работят, както се очаква, и съобщенията ви се появяват, когато ги очаквате.

Забележки : 

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

  • Ако списъкът със записи за падащия списък е в друг работен лист и желаете да попречите на потребителите да го виждат или да го променят, помислете за скриване и защита на този работен лист. За повече информация относно начините за защита на работен лист вижте Заключване на клетки с цел защитата им.

  • Премахване на проверката на данни – изберете клетката или клетките, които съдържат проверката, която искате да изтриете, след което отидете на Данни > Проверка на данни и в диалоговия прозорец за проверка на данни натиснете бутона Изчисти всички, след което OK.

Следващата таблица описва други типове проверка на данни и показва начини за добавянето им към работните листове.

За да направите това:

Изпълнете тези стъпки:

Ограничаване на въвеждането на данни до цели числа в определени граници.

  1. Изпълнете стъпки от 1 до 3 в Добавяне на проверка на данни към клетка или диапазон по-горе.

  2. Изберете Цяло число от списъка Позволи.

  3. В полето Данни изберете типа на желаното ограничение. Например за да зададете горни и долни и граници, изберете между.

  4. Въведете минимална, максимална, или конкретна позволена стойност.

    Диалогов прозорец с критерии за проверка

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

    Да речем например, че проверявате данните в клетка F1. За да зададете минималната граница на умозаключенията да бъде два пъти по-голяма от броя на децата в тази клетка, изберете по-голямо или равно на в полето Данни и въведете формулата =2*F1 в полето Минимум.

Ограничаване на въведените данни до десетични числа в определени граници.

  1. Изпълнете стъпки от 1 до 3 в Добавяне на проверка на данни към клетка или диапазон по-горе.

  2. В полето Позволи изберете Десетично.

  3. В полето Данни изберете типа на желаното ограничение. Например за да зададете горни и долни и граници, изберете между.

  4. Въведете минимална, максимална, или конкретна позволена стойност.

    Може също да въведете формула, която връща числова стойност. Например за да зададете максималната граница за комисиони и премиални да бъде 6% от заплатата на продавача в клетка E1, изберете по-малко или равно на в полето Данни и въведете формулата =E1*6% в полето Максимум.

    Забележка : За да позволите на потребител да въвежда проценти, например 20%, изберете Десетично в полето Позволи, изберете типа на желаното от вас ограничение в полето Данни, въведете минимума, максимума или определена стойност като десетично число, например 0,2, а след това покажете клетката за проверка на данни като процент, като изберете клетката и щракнете върху Стил за процент Изображение на бутон в групата Число на раздела Начало.

Ограничаване на въведените данни до дата в определен диапазон от дати.

  1. Изпълнете стъпки от 1 до 3 в Добавяне на проверка на данни към клетка или диапазон по-горе.

  2. В полето Позволи изберете Дата.

  3. В полето Данни изберете желания тип ограничение. Например за да позволите въвеждане на дати след определен ден, изберете по-голямо от.

  4. Въведете начална, крайна или конкретна позволена дата.

    Може също да въведете формула, която връща дата. Например за да зададете времева рамка между днешната дата и три дни след днешната дата, изберете между в полето Данни, въведете =TODAY() в полето Начална дата и въведете =TODAY()+3 в полето Крайна дата.

    Настройки за критериите за проверка за ограничаване на въведените дати до определен времеви интервал

Ограничаване на въведените данни до час в определен интервал от време.

  1. Изпълнете стъпки от 1 до 3 в Добавяне на проверка на данни към клетка или диапазон по-горе.

  2. В полето Позволи изберете Час.

  3. В полето Данни изберете желания тип ограничение. Например за да позволите въвеждане на часове преди определен час на деня, изберете по-малко от.

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

    Например ако сте въвели начален час в клетка E2 (8:00 ч.) и краен час в клетка F2 (17:00 ч.) и искате да ограничите часовете за събрания между тези два часа, трябва да изберете между в полето Данни, да въведете = E2 в полето Начален час и след това да въведете = F2 в полето Краен час.

    Настройки за проверка, за да ограничите въвеждането на време до определен интервал от време

Ограничаване на въведените данни до текст с определена дължина.

  1. Изпълнете стъпки от 1 до 3 в Добавяне на проверка на данни към клетка или диапазон по-горе.

  2. В полето Позволи изберете Дължината на текста.

  3. В полето Данни изберете типа на желаното ограничение. Например за да позволите до определен брой знаци, изберете по-малко или равно на.

  4. В този случай искаме да ограничим въвеждането до 25 знака, затова избираме По-малко или равно на в полето Данни и въвеждаме 25 в полето Максимум.

    Пример за проверка на данни с ограничена дължина на текста

Изчисляване на ограничения на базата на съдържанието на друга клетка.

  1. Изпълнете стъпките 1 – 3 в Добавяне на проверка на данни към клетка или диапазон по-горе. В полето Позволи изберете желания от вас тип на данните.

  2. В полето Данни изберете типа на желаното ограничение.

  3. В полето или полетата под полето Данни щракнете върху клетката, която искате да използвате за задаване на ограниченията.

    Например за да позволите въвеждане в една сметка само ако резултатът няма да превиши бюджета в клетка E1, изберете Разреши >Цяло число, Данни, по-малко или равно на, и Максимум >= =E1.

    Настройки за проверка за изчисляване въз основа на съдържанието на друга клетка

Забележка : Следващият пример използва опцията "По избор", при която пишете формули, за да зададете своите условия. Не е нужно да се притеснявате за това, което се показва в полето за данни, тъй като то е забранено с опцията "По избор".

За да гарантирате, че

Въведете тази формула

Клетката, съдържаща ИД на продукт (C2), винаги започва със стандартния префикс "ID" и е с дължина от поне 10 знака (над 9).

=AND(LEFT(C2; 3) ="ID-";LEN(C2) > 9)

Пример 6: Формули при проверка на данни

Клетката, съдържаща име на продукт (D2), съдържа само текст.

=ISTEXT(D2)

Пример 2: Формули при проверка на данни

Клетката, съдържаща нечий рожден ден – (B6) – е с по-голяма стойност от броя на годините, зададени в клетка B4.

=IF(B6<=(TODAY()-(365*B4));TRUE;FALSE)

Пример за проверка на данни за ограничаване на въвежданите данни до минимална възраст

Всички данни в диапазона от клетки A2:A10 съдържат уникални стойности.

=COUNTIF($A$2:$A$10;A2)=1

Пример 4: Формули при проверка на данни

Забележка : Трябва първо да въведете формула за проверка на данните за клетка A2, след това да копирате A2 в A3:A10 така, че вторият аргумент за функцията COUNTIF да съвпада с текущата клетка. Тоест частта A2)=1 да се промени на A3)=1; A4)=1 и така нататък.

За повече информация

Проверка, че записът на имейл адрес в клетка B4 съдържа символа @.

=ISUMBER(FIND("@";B4)

Пример за проверка на данни, който гарантира, че имейл адрес съдържа символа @

  • Защо командата за проверка на данни не е разрешена на лентата? Командата може да е недостъпна, защото:

    • Таблица на Microsoft Excel може да бъде свързана към SharePoint сайт  Не може да добавяте проверка на данните към таблица на Excel, която е свързана към SharePoint сайт. За да добавите проверка на данните, трябва да прекъснете връзката към таблицата на Excel или да конвертирате таблицата на Excel в диапазон.

    • Може в момента да въвеждате данни Командата за проверка на данни не е достъпна в раздела Данни, докато въвеждате данни в клетка. Може да завършите въвеждането на данни, като натиснете клавиша ENTER или ESC.

    • Работният лист може да е защитен или споделен Не можете да променяте настройките за проверка на данните, ако вашата работна книга е споделена или защитена. За да научите как да спрете споделянето или защитата на работна книга, вижте Защита на работна книга.

  • Мога ли да променям размера на шрифта? Не, размерът на шрифта е фиксиран. Единственият начин да промените показвания размер е да настроите мащабирането на екрана в долния десен ъгъл на прозореца на Excel. Но можете да използвате ActiveX разгъващ се списък. Вижте Добавяне на списъчно поле или разгъващ се списък в работен лист.

  • Има ли начин проверката на данни да се извършва с автозапълване или автоматично избиране, докато пиша? Не, но ако използвате ActiveX разгъващ се списък има такава функционалност.

  • Мога да направя няколко селекции в списък за проверка на данни? Не, освен ако не използвате ActiveX разгъващ се списък или списъчно поле.

  • Мога ли да избирам елемент в списък за проверка на данни и с него да попълвам друг списък? Да! Това се нарича проверка на зависими данни. За повече информация вижте Създаване на зависими падащи списъци.

  • Как мога да премахна всички проверки на данни в работен лист? Можете да използвате диалоговия прозорец Отиди на > Специални. В раздела Начало > Редактиране > Намиране и избор (можете също да натиснете F5 или Ctrl+G на клавиатурата), след това Специални > Проверка на данни и изберете Всички (за да намерите всички клетки с проверка на данните) или Същите.(за да намерите клетките, които съответстват на определени настройки за проверка на данните).

    Диалоговият прозорец "Специален избор на клетки"

    След това отворете диалоговия прозорец за проверка на данни (раздел Данни > Проверка на данни) и натиснете бутона Изчисти всички, а след това – OK.

  • Мога ли да принудя някого да направи запис в клетки с проверка на данните? Не, но можете да използвате VBA (Visual Basic for Applications), за да проверите дали някой е направил запис при определени условия, като например преди да запише или затвори работната книга. Ако не са направили избор, можете да отмените събитието и да не позволите да продължат, докато не изберат.

  • Как мога да оцветявам клетки въз основа на избор от списък за проверка на данни? Можете да използвате условно форматиране. В този случай трябва да използвате опцията Форматиране само на клетки, които съдържат.

    Опция за форматиране на клетки, които съдържат
  • Как мога да проверя имейл адрес? Можете да използвате метода По избор > Формула и да проверете дали символът @ съществува в записа. В този случай използваната формула е =ISNUMBER(FIND("@";D2)). Функцията FIND търси символа @ и ако го намери, връща числовата му позиция в текстовия низ и позволява записа. Ако не го намери, FIND връща съобщение за грешка и не позволява записа.

Имате въпрос за конкретна функция?

Публикувайте въпрос във форума на общността на Excel

Помогнете ни да подобрим на Excel

Имате ли предложения как можем да подобрим следващата версия на Excel? Ако е така, вижте темите в Гласът на потребителите на Excel.

Вж. също

Повече информация за проверката на данни

Видео: Създаване и управление на падащи списъци

Добавяне и премахване на елементи от падащ списък

Премахване на падащ списък

Споделяне Facebook Facebook Twitter Twitter Имейл Имейл

Беше ли полезна тази информация?

Чудесно! Друга обратна връзка?

Как можем да подобрим това?

Благодарим ви за обратната връзка!

×