Перейти к содержимому

Где находится проверка данных в экселе

  • автор:

В excel проверка данных список

Выпадающий список в MS EXCEL на основе Проверки данных

​Смотрите также​Ссылка (Reference)​ задаваемые формулами типа​ это можно сделать​ сумма расходов.​ инструмент: «Данные»-«Проверка данных»-«Обвести​ из других ячеек,​ рабочий лист.​ числами.​ и лист не​ и предотвращения ввода​ о том, что​Остановка​ Кроме всего прочего,​ ячейки, для которых​А1:А10​Преимущество​

​При заполнении ячеек данными,​в нижней части​СМЕЩ (OFFSET)​

​ в меню​Дописывая каждую новую позицию,​ неверные данные».​ то при вставке​Пример прайс-листа с введенными​Нажимаем кнопку «ОК». Все​

​ защищен паролем, вы​ недопустимых данных только​ в существующих ячейках​есть два параметра:​ проверку данных можно​ проводится проверка допустимости​. Однако, в этом​

​: наглядность перечня элементов​ часто необходимо ограничить​

А. Простейший выпадающий список — ввод элементов списка непосредственно в поле Источник

​ окна введите руками​. Для первичного (независимого)​Вставка — Имя -​ пользователь должен иметь​Если значения в столбце​ этот инструмент удалиться​

​ ошибками в процентных​​ проверку данных установили.​​ можете изменить или​ в том случае,​ имеются недопустимые данные.​Повторить​​ использовать в следующих​​ данных (заданная с​ случае Выпадающий список​

​ и простота его​ возможность ввода определенным​ следующую формулу:​ списка их использовать​ Присвоить (Insert -​ возможность следить за​

​ B должны соответствовать​ из этих ячеек​​ ставках НДС:​​ Проверяем.​ удалить проверку. Если​ если пользователь вводит​ В этом случае​и​ целях:​

​ помощью команды Данные/​​ может содержать пустые​ модификации. Подход годится​ списком значений. Например,​=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)​ можно, а вот​​ Name — Define).​​ общей суммой расходов.​ определенным условиям, но​ и не сработает.​Чтобы в Excel сделать​В ячейку А34 мы​
​ лист защищен и​
​ данные непосредственно в​

Б. Ввод элементов списка в диапазон (на том же листе, что и выпадающий список)

​ можно выделить недопустимые​Отмена​Ограничение данных предопределенными элементами​ Работа с данными/​ строки.​ для редко изменяющихся​ имеется ячейка, куда​=OFFSET($A$1;MATCH($G$7;$A:$A;0)-1;1;COUNTIF($A:$A;$G$7);1)​

​ вторичный список должен​В Excel 2007​ Решение:​​ содержит ошибки, то​​Иногда возникает необходимость перестраховаться​ проверку вводимых данных​

​ написали первую букву​​ вы не знаете​ ячейку. Если данные​ данные на листе​.​ списка​
​ Проверка данных). При​​Избавиться от пустых строк​ списков.​ пользователь должен внести​Ссылки должны быть абсолютными​ быть определен жестко,​ и новее -​Создайте книгу с листами,​ все они будут​​ от ошибок перед​​ в ячейки следует​ «а». Вышло предупреждающее​ пароль, обратитесь к​ копируются или вставляются,​ с помощью кругов.​Предупреждение​: например, можно ограничить​ выборе переключателя Всех​ и учесть новые​

​Недостатки​ название департамента, указав​ (со знаками $).​ без формул. Однако,​ на вкладке​ так как указано​ обведены красным овалом.​ вводом в ячейки​ выполнить ряд последовательных​ об ошибке окно.​ его прежнему владельцу,​

​ сообщения не отображаются.​ Когда недопустимые данные​Предупреждает пользователей о том,​ выбор отдела значениями​ будут выделены все​

B. Ввод элементов списка в диапазон (на любом листе)

​ элементы перечня позволяет​: если добавляются новые​ где он работает.​ После нажатия Enter​ это ограничение можно​Формулы (Formulas)​

​ выше на рисунке​ Этот инструмент очень​ данных несоответствующим определенным​ действий:​

​Если мы введем​ так как в​ Чтобы предотвратить копирование​ найдены, круги можно​ что введенные данные​

​ «Бухгалтерия», «Финансовый отдел»,​ такие ячейки. При​ Динамический диапазон. Для​ элементы, то приходится​ Логично, предварительно создать​ к формуле будут​ обойти, создав отсортированный​с помощью​

​ и на каждом​
​ удобно использовать, когда​ условиям. Например, в​Выделите диапазон ячеек где​​ число меньше или​​ Excel невозможно восстановить​
​ и заполнение данных​

  • ​ скрыть. При исправлении​​ недопустимы, но не​​ «Отдел кадров» и​
  • ​ выборе опции Этих​ этого при создании​
  • ​ вручную изменять ссылку​ список департаментов организации​ автоматически добавлены имена​

​ список соответствий марка-модель​Диспетчера имен (Name Manager)​ листе добавьте по​ нужно проверить уже​

  • ​ номенклатуре магазина товаров​
  • ​ вводиться формула с​ больше 3 знаков,​ потерянные пароли. Вы​

​ путем перетаскивания ячеек,​
​ недопустимых данных они​ препятствует их вводу.​ т. д.​ же выделяются только​ Имени Список_элементов в​​ на диапазон. Правда,​​ и позволить пользователю​ листов — не​ (см. Способ 2).​. Затем повторим то​

​ несколько позиций разным​ введенные или скопированные​ и цен недолжно​ процентной ставкой для​ то снова выйдет​ также можете скопировать​ выберите​ исчезают автоматически.​

​Когда появляется​Ограничение использования чисел за​ те ячейки, для​​ поле Диапазон необходимо​​ в качестве источника​ лишь выбирать значения​ пугайтесь :)​Имена вторичных диапазонов должны​ же самое со​

​ количеством.​

​ данные.​ быть возможности ввода​ цен с НДС​ окно, указывающее на​ данные на другой​Файл​Чтобы обвести данные, выделите​предупреждение​ пределами указанного диапазона​ которых установлены те​ записать формулу =СМЕЩ(Список!$A$1;;;СЧЁТЗ(Список!$A:$A))​

​ можно определить сразу​​ из этого списка.​Функция​ совпадать с элементами​ списками Форд и​На листе: «ИТОГО» поставьте​Конечно, можно выполнить проверку​

​ отрицательных чисел и​ и выберите инструмент:​ ошибку.​

​ лист и удалить​>​ нужные ячейки и​, пользователь может нажать​: например, можно указать​ же правила проверки​

  • ​Использование функции СЧЁТЗ() предполагает,​ более широкий диапазон,​
  • ​ Этот подход поможет​СМЕЩ (OFFSET)​ первичного выпадающего списка.​ Ниссан, задав соответственно​
  • ​ формулы как указано​ данных в столбце​ нулей. Ведь магазин​
  • ​ «ДАННЫЕ»-«Работа с данными»-«Проверка​Внимание!​ проверку данных.​Параметры​

​ выберите​ кнопку​ максимальный процент годового​ данных, что и​ что заполнение диапазона​ например,​ ускорить процесс ввода​умеет выдавать ссылку​ Т.е. если в​ имена диапазонам​ выше на рисунке​ с помощью логической​ не раздает товар​ данных».​

​Можно написать сообщение​Если при попытке ввести​>​Данные​Да​

​ роста зарплаты сотрудника​
​ для активной ячейки.​ ячеек (​A1:A100​ и уменьшить количество​ на диапазон нужного​ нем есть текст​Ford​ и перейдите в​ функции Excel –​ с доплатой или​В появившемся окне «Проверка​ – указать в​ данные в ячейку​Дополнительно​>​, чтобы принять недопустимый​ (например, 3 %) или​Примечание​A:A​. Но, тогда выпадающий​ опечаток.​ размера, сдвинутый относительно​ с пробелами, то​и​ ячейку B4.​ «ЕСЛИ». Или условное​

​ за бесплатно.​​ вводимых значений» на​
​ чем ошибка, или​ или изменить их​>​Работа с данными​ ввод, кнопку​ разрешить только целое​:​), который содержит элементы,​ список может содержать​Выпадающий список можно создать​ исходной ячейки на​

​ придется их заменять​Nissan​Перейдите на закладку «Формулы»​ форматирование. Но применение​Для того, чтобы предотвратить​ вкладке «Параметры» из​ написать подсказку, как​ выводится предупреждение и​Параметры редактирования​>​Нет​

​ число от 1​Если выпадающий список​ ведется без пропусков​ пустые строки (если,​ с помощью Проверки​ заданное количество строк​ на подчеркивания с​.​ и выберите инструмент​ инструмента «Проверка данных»​ ошибки оператора компьютерного​ выпадающего списка «Тип​ правильно написать код.​ вы не знаете​, снимите флажок​Проверка данных​, чтобы изменить​ до 100.​

Дополнительные сведения о проверке данных

​ содержит более 25-30​​ строк (см. файл​ например, часть элементов​ данных ​ и столбцов. В​ помощью функции​При задании имен помните​ «Окно контрольного значения»​ – более эффективно,​ набору в Excel,​ данных:» выберите опцию​ Для этого нужно​

​ точно, что вы​Разрешить маркеры заполнения и​>​ введенные данные, или​Ограничение дат, находящихся за​ значений, то работать​ примера, лист Динамический​ была удалена или​или с помощью элемента​ более понятном варианте​ПОДСТАВИТЬ (SUBSTITUTE)​ о том, что​В появившемся окне нажмите​ удобно и продуктивно​ будем использовать проверку​ «Список».​

Пример сообщения о недопустимом вводе

При выборе пользователем ячейки отображается сообщение для ввода

Ситуации, в которых полезно использовать проверку данных

​ перейти на закладку​ можете вводить, обратитесь​ перетаскивание ячеек​Обвести неверные данные​ кнопку​ пределами определенного промежутка​ с ним становится​ диапазон).​ список только что​ управления формы Поле​ синтаксис этой функции​

​, т.е. формула будет​ имена диапазонов в​​ кнопку «Добавить контрольное​ для данной задачи.​ данных ввода. Тем​В полю ввода «Источник:»​ «Сообщение об ошибке»​

​ к владельцу книги.​, а затем защитите​​.​Отмена​ времени​ неудобно. Выпадающий список​Используем функцию ДВССЫЛ()​ был создан). Чтобы​ со списком (см.​

​ таков:​ выглядеть как =ДВССЫЛ(ПОДСТАВИТЬ(F3;»​ Excel не должны​​ значение» и в​ Особенно если нам​ самым создадим ему​ введите значения разных​

​ или на закладку​Задать вопрос на форуме​ лист.​​Чтобы быстро отменить проверку​, чтобы удалить недопустимую​: например, можно предотвратить​ одновременно отображает только​Альтернативным способом ссылки на​

​ пустые строки исчезли​​ статью Выпадающий (раскрывающийся)​=СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; размер_диапазона_в_строках;​ «;»_»))​ содержать пробелов, знаков​

​ нем указываем адрес​ нужно одновременно выполнить​ комфортные условия для​​ процентных ставок: 0%;​ «Сообщение для ввода».​ сообщества, посвященном Excel​Выключен пересчет вручную.​ данных в ячейке,​ запись.​ выбор даты, которая​ 8 элементов, а​ перечень элементов, расположенных​ необходимо сохранить файл.​ список на основе​

Подсказки и сообщения об ошибках

​ размер_диапазона_в_столбцах)​Надо руками создавать много​ препинания и начинаться​ ячейки, за значением​ проверку по нескольким​ рутинной работы, где​ 20%; освобождается.​Как установить сообщения​У вас есть предложения​ Если включен пересчет​ выделите ее и​Информационное сообщение​ предшествует текущей, при​ чтобы увидеть остальные,​ на другом листе,​Второй недостаток: диапазон источника​ элемента управления формы).​

Подсказка для ячейки

​Таким образом:​ именованных диапазонов (если​ обязательно с буквы.​

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

​ которого мы будем​ столбцам. В таком​ сложно не допустить​​Перейдите на закладку «Сообщение​ об ошибке или​​ по улучшению следующей​

​ вручную, невычисляемые ячейки​​ выберите​​Информирует пользователей о том,​ запросе отгула.​

Предупреждение о недопустимых данных

​ нужно пользоваться полосой​ является использование функции​

​ должен располагаться на​

​В этой статье создадим​

​начальная ячейка — берем​

​ Поэтому если бы​ следить: =ИТОГО!$B$4. Жмите​

​ случаи более заметна​​ ошибки.​​ об ошибке» и​​ подсказки для заполнения​​ версии Excel? Если​​ могут повлиять на​​Данные​

​ что введенные данные​

​Ограничение времени, которое находится​ прокрутки, что не​ ДВССЫЛ(). На листе​ том же листе,​

​ Выпадающий список с​​ первую ячейку нашего​​ марок автомобилей).​ в одной из​​ добавить.​​ рациональность его использования.​У нас имеется лист​​ заполните текстовое поле​​ ячейки, читайте в​ да, ознакомьтесь с​ правильность проверки данных.​​>​​ недопустимы, но не​ за пределами определенного​

​ Пример, выделяем диапазон​ что и выпадающий​ помощью Проверки данных​ списка, т.е. А1​Этот способ требует наличия​ марок автомобилей присутствовал​

​Теперь у Вас есть​​Программа Excel обладает эффективными​​ номенклатуры товаров магазина:​ «Сообщение:» текстом который​​ статье «Проверка данных​​ темами на портале​ Чтобы отключить пересчет​Работа с данными​​ препятствует их вводу.​​ промежутка времени​

Советы по использованию проверки данных

​В EXCEL не предусмотрена​ ячеек, которые будут​ список, т.к. для​ (Данные/ Работа с​

​сдвиг_вниз — нам считает​​ отсортированного списка соответствий​ бы пробел (например​ возможность избежать бесконтрольных​ инструментами для удобного​Выделите столбец B и​ будет содержать сообщение​ в Excel».​ пользовательских предложений для​

​ вручную, откройте вкладку​>​ Это самый гибкий​: например, вы можете​ регулировка размера шрифта​ содержать выпадающий список,​ правил Проверки данных нельзя​ данными/ Проверка данных)​ функция​

​ марка-модель вот такого​ Ssang Yong), то​ расходов при заполнении​ контроля ввода больших​ выберите инструмент «Данные»-«Проверка​ при вводе других​Второй вариант.​ Excel.​Формулы​Проверка данных​ тип сообщения.​ указать, что собрания​ Выпадающего списка. При​

​ вызываем Проверку данных,​ использовать ссылки на​ с типом данных​ПОИСКПОЗ (MATCH)​ вида:​ его пришлось бы​ листов новыми товарами.​ объемов данных. Так​ данных».​ значений, которые отличаются​

​Проверка вводимых данных в​Как установить простую​, в группе​>​При появлении​ должны проводиться в​ большом количестве элементов​ в Источнике указываем​ другие листы или​ Список.​, которая, попросту говоря,​Для создания первичного выпадающего​ заменить в ячейке​Не важно, на каком​ же присутствуют средства​Заполняем значениями поля на​ от указанных значений​Excel.​

​ функцию​Вычисление​Параметры​​информационного сообщения​​ период между 8:00​​ имеет смысл сортировать​​ =ДВССЫЛ(«список!A1:A4»).​​ книги (это справедливо​​Выпадающий список можно сформировать​​ выдает порядковый номер​​ списка можно марок​

​ и в имени​ листе Вы находитесь.​ для одновременного экспонирования​ вкладке «Параметры» как​​ в списке.​​В формуле можно​​проверки данных, вводимых в​​выберите​​>​​пользователь может нажать​​ и 17:00.​​ список элементов и​​Недостаток​​ для EXCEL 2007​

​ по разному.​ ячейки с выбранной​ можно воспользоваться обычным​ диапазона на нижнее​​ Поверх всех окон​​ нескольких значений ячеек,​​ показано на рисунке:​​​​ написать любое количество​​ ячейки​Параметры вычислений​​Очистить все​​ кнопку​Ограничение количества текстовых знаков​ использовать дополнительную классификацию​: при переименовании листа​ и более ранних).​Самым простым способом создания​

​ маркой (G7) в​ способом, описанным выше,​​ подчеркивание (т.е. Ssang_Yong).​​ отображено окно контрольного​​ которые не соответствуют​​На вкладке «Сообщение для​​Закладка «Сообщение об ошибке»​​ знаков, любые буквы,​Excel​, а затем —​.​ОК​: например, можно разрешить​ элементов (т.е. один​ – формула перестает​Избавимся сначала от второго​ Выпадающего списка является​ заданном диапазоне (столбце​

​ т.е.​Теперь создадим первый выпадающий​ значения, а в​ определенным требованиям.​ ввода» создаем особое​ предоставляет пользователю возможность​ несколько букв, т.д.​, смотрите в статье​​Выполнять автоматически​​Чтобы найти ячейки на​​, чтобы принять недопустимое​ ввод не более​ выпадающий список разбить​​ работать. Как это​

​ недостатка – разместим​ ввод элементов списка​ А)​

​дать имя диапазону D1:D3​ список для выбора​​ нем видно общую​Приготовьте лист доходов и​ примечание, в котором​ оформить стиль сообщения​ Например, такую формулу.​ «Защита ячейки Excel​.​ листе, для которых​ значение, либо нажать​ 10 знаков в​ на 2 и​ можно частично обойти​ перечень элементов выпадающего​ непосредственно в поле​​сдвиг_вправо = 1, т.к.​​ (например​​ марки автомобиля. Выделите​​ сумму расходов. Несложно​​ расходов как показано​​ при помещения курсора​​ об ошибочных вводах​​=И(ЛЕВСИМВ(C33)=»Фрукт/»;ДЛСТР(C32)=8;ЕЧИСЛО(ЗНАЧЕН(ПРАВСИМВ(C32;2))))​​ от неверно вводимых​Отсутствуют ошибки в формулах.​​ включена проверка данных,​ кнопку​

​ ячейку.​​ более).​ см. в статье​ списка на другом​ Источник инструмента Проверка​ мы хотим сослаться​Марки​​ пустую ячейку и​​ догадаться, как сделать​​ на рисунке:​​ на ячейку будет​​ значений. Если пользователь​​Получился такой код.​​ данных». Здесь рассмотрим​​ Убедитесь, что формулы​

​ на вкладке​​Отмена​Проверка данных на основе​Например, чтобы эффективно работать​ Определяем имя листа.​ листе.​ данных.​ на модели в​) с помощью​ откройте меню​ так, чтобы в​

​Прибыль – это естественно​ отображаться условия для​​ вводить в ячейку​Функция проверки данных​ другой вариант -​ в ячейках, для​Главная​, чтобы отменить ввод.​ формул или значений​ со списком сотрудников​Ввод элементов списка в​

​В правилах Проверки данных (также​Предположим, в ячейке​​ соседнем столбце (В)​Диспетчера имен (Name Manager)​Данные — Проверка (Data​ этом окне отображались​ доход минус расход.​ ее заполнения. И​ неправильное значение тогда​ можно использовать для​ как проверять в​

​ которых включена проверка,​в группе элементов​​Обратите внимание на советы​ в других ячейках​ насчитывающем более 300​ диапазон ячеек, находящегося​ как и Условного​B1​размер_диапазона_в_строках — вычисляем с​с вкладки​

​ — Validation)​ и значения общей​​ Допустим нам нужно​ жмем ОК.​ будет выполнен один​ запрета исправления данных​ Excel правильность написания​ не являются причиной​Правка​

Обновление или удаление проверки данных в книге, созданной другим пользователем

​ и рекомендации по​: например, проверку данных можно​ сотрудников, то его​ в другой книге​ форматирования) нельзя впрямую​необходимо создать выпадающий​ помощью функции​Формулы (Formulas)​или нажмите кнопку​ суммы каждой группы​ проверить, в какие​Теперь проверим. В ячейку​ из 3-х параметров:​ в таблице задним​ кода с буквами​ возникновения ошибок, таких​выберите пункт​ использованию проверки данных​

​ использовать для указания​ следует сначала отсортировать​Если необходимо перенести диапазон​ указать ссылку на​ список для ввода​СЧЕТЕСЛИ (COUNTIF)​или в старых​Проверка данных (Data Validation)​

У вас есть вопрос об определенной функции?

​ по отдельности.​ дни прибыль падала​

Помогите нам улучшить Excel

​ B2 введите натуральное​Останов – данный параметр​ числом. Подробнее о​ и числами.​ как #ССЫЛКА! или​Найти и выделить​ в Excel.​

Проверка вводимых данных в Excel.

​ максимально допустимого предела​ в алфавитном порядке.​​ с элементами выпадающего​ диапазоны другого листа​ ​ единиц измерений. Выделим​​, которая умеет подсчитать​ версиях Excel -​на вкладке​Полезный совет. Если взять​ ниже 40$. Решение​ число, а в​ разрешает вводить только​ таком способе, читайте​Первый вариант.​
​ #ДЕЛ/0!. Пока ошибки​
​, а затем нажмите​ ​Примечание:​
​ для комиссионных и​ Затем создать выпадающий​ списка в другую​ (см. Файл примера):​ ячейку​
​ количество встретившихся в​ через меню​Данные (Data)​ курсором мышки за​ следующее:​
​ ячейку B3 отрицательное.​ правильное значение, выбранного​ в статье «Чтобы​Проверка ввода данных в​ не будут устранены,​ кнопку​ Если вы хотите использовать​
​ премий на основе​ список, содержащий буквы​ книгу (например, в​Пусть ячейки, которые должны​B1​ списке (столбце А)​
​Вставка — Имя -​если у вас​
​ заголовок «Окно контрольного​
​Выделите данные в диапазоне​
​ Как видно в​ из выпадающего списка​ не исправляли данные​Excel.​ приложение Excel будет​Проверка данных​ проверку данных в​ общего планируемого значения​ алфавита. Второй выпадающий​
​ книгу Источник.xlsx), то​ содержать Выпадающий список,​и вызовем Проверку​ нужных нам значений​ Присвоить (Insert -​
​ Excel 2007 или​ значения» и навести​ D2:D6 и выберите​ ячейке B3 действие​ или отменить ввод​​ в таблице Excel​У нас такая​ пропускать проверку данных.​​. После того как​ книгах в службы​ заработной платы. Если​ список должен содержать​
​ нужно сделать следующее:​ размещены на листе​ данных.​ — марок авто​ Name — Define)​ новее. Затем из​
​ на строку состояния​
​ инструмент: «Данные»-«Работа с​ оператора набора –​ оставив пустую ячейку.​ задним числом» здесь.​ таблица. В столбце​Ссылки на ячейки в​ найдены ячейки, для​ Excel или Excel​ пользователь в этой​ только те фамилии,​
​в книге Источник.xlsx создайте​ Пример,​Если в поле Источник​ (G7)​выбрать на вкладке​ выпадающего списка​
​ окна программы (в​
​ данными»-«Проверка данных».​ ​ заблокировано. Отображается сообщение​
​Предупреждение – этот параметр​Можно запретить вводить​ A будем писать​ формулах являются правильными.​ которых включена проверка​
​ Web App, сначала​
​ ячейке введет значение,​
​ которые начинаются с​ необходимый перечень элементов;​а диапазон с перечнем​ указать через точку​размер_диапазона_в_столбцах = 1, т.к.​Данные (Data)​Тип данных (Allow)​ область ярлычков с​В появившемся окне: «Проверка​ об ошибке: «Введенное​
​ предупреждает об вводе​ повторяющиеся данные. Это​ коды с буквами​ Если ссылка на​ данных, можно изменить,​ потребуется создать ее​
​ превышающее ограничение, появится​ буквы, выбранной первым​в книге Источник.xlsx диапазону​ элементов разместим на​ с запятой единицы​ нам нужен один​команду​выберите вариант​ названиями листов), то​ вводимых значений» установите​

Проверка вводимых данных в ячейки Excel

​ значение неверно».​ ошибочного значения и​ способ смотрите в​ и числами.​ ячейку будет изменена​ скопировать или удалить​ в классическом приложении​ сообщение об ошибке.​ списком. Для решения​ ячеек содержащему перечень​ другом листе (на​ измерения шт;кг;кв.м;куб.м, то​ столбец с моделями​Проверка данных (Data validation)​Список (List)​ оно удобно зафиксируется​ такие же настройки​Примечание. При желании можно​ предоставляет попытку отредактировать​ статье «Запретить вводить​В этих ячейках установим​ и формула в​ параметры проверки данных.​ Excel.​Вы можете сделать так,​ такой задачи может​ элементов присвойте Имя,​ листе Список в​

Как в Excel сделать проверку данных в ячейках

​ выбор будет ограничен​В итоге должно получиться​выбрать из выпадающего списка​

Прибавить процент в Excel.

​и в поле​ в нижней части​ как показано на​ написать собственный текст​ или выбрать из​

  1. ​ повторяющиеся значения в​ проверку данных, в​ ячейке, для которой​Для создания раскрывающегося списка​Ширина раскрывающегося списка определяется​ чтобы при выборе​ быть использована структура​Проверка данных в Excel.
  2. ​ например СписокВнеш;​ файле примера).​ этими четырьмя значениями.​ что-то вроде этого:​ вариант проверки​Источник (Source)​Проверка вводимых значений.
  3. ​ главного окна.​ рисунке. И нажмите​ для ошибки на​ выпадающего списка.​Останов.
  4. ​ Excel» тут.​ соответствии с нашими​ включена проверка, будет​ можно воспользоваться командой​ шириной ячейки, для​ ячейки выводилась подсказка.​ Связанный список или​откройте книгу, в которой​Для создания выпадающего списка,​

​Осталось добавить выпадающий список​Список (List)​выделите ячейки с​Этот фокус основан на​ ОК.​ третей закладке настроек​Сообщение – параметр сообщает​В Excel можно​ условиями. Выделяем ячейки​

  1. ​ вычислять недопустимый результат,​Присвоить имя​ которой применяется проверка​ В ней указывается,​ Вложенный связанный список.​ предполагается разместить ячейки​
  2. ​ элементы которого расположены​ Выделим ячейку​ на основе созданной​и указать в​ названиями марок (желтые​ применении функции​
  3. ​Теперь выберите инструмент из​ инструмента «Сообщение об​ о неправильном значении​ сделать саму простую​

​ столбца А (А31:А36).​ для этой ячейки​(на вкладке​ данных. Возможно, вам​ данные какого типа​Примечание:​ с выпадающим списком;​ на другом листе,​B1​ формулы к ячейке​ качестве​ ячейки в нашем​ДВССЫЛ (INDIRECT)​ выпадающего списка: «Данные»-«Проверка​ ошибке».​ и позволяет проигнорировать​ таблицу, можно сделать​На закладке «Данные»​

Сообщение для ввода.

​ не появится сообщение​Формулы​ потребуется увеличить ее,​ нужно ввести в​

Контроль ввода в ячейку.

​ Это расширенная статья о​выделите нужный диапазон ячеек,​ можно использовать два​. При выделении ячейки​ G8. Для этого:​Источника (Source)​

​ примере). После нажатия​, которая умеет делать​ данных»-«Обвести неверные данные».​Чтобы удалить проверку данных​ ошибку.​ сложную таблицу, с​ в разделе «Работа​ о проверке.​в группе​ чтобы не обрезать​

Проверка ввода данных в Excel и ее особенности

​ ячейку. Такое сообщение​ проверке данных. Общие​ вызовите инструмент Проверка​ подхода. Один основан​ справа от ячейки​выделяем ячейку G8​=Марки​ на​ одну простую вещь​ И обратите внимание​ в Excel нужно:​Закладка «Сообщение для ввода»​ фильтрами, т.д. Какими​

Проверка вводимых данных в Excel

​ с данными» нажимаем​Возможно, таблица Excel связана​Определенные имена​ допустимые записи, ширина​ выводится рядом с​ сведения о проверке​ данных, в поле​ на использовании Именованного​ появляется квадратная кнопка​выбираем на вкладке​

​или просто выделить​ОК​

  1. ​ — преобразовывать содержимое​ на результат:​ выделить соответствующий диапазон​Параметры.
  2. ​ содержит поля ввода​ способами сделать таблицу​ на кнопку «Проверка​Сообщение.
  3. ​ с сайтом SharePoint.​), которая позволяет определить​ которых больше ширины​ ячейкой. Вы можете​ данных, а также​ Источник укажите =ДВССЫЛ(«[Источник.xlsx]лист1!СписокВнеш»);​ диапазона, другой –​ со стрелкой для​

​Данные (Data)​ ячейки D1:D3 (если​первый выпадающий список​ любой указанной ячейки​При необходимости можете удалить​ ячеек, выбрать инструмент​ для заголовка и​ в Excel, смотрите​ данных». Появится диалоговое​ Невозможно добавить проверку​

​ имя для диапазона,​ раскрывающегося списка.​ переместить его, но​ сведения о проверке​При работе с перечнем​ функции ДВССЫЛ().​

​ выбора элементов из​команду​ они на том​ готов:​ в адрес диапазона,​ красные обводки, выбрав​ и нажать на​ текстовое поле для​

Особенности проверки данных

​ окно «Проверка вводимых​ данных в таблицу​ содержащего список. После​Если необходимо защитить лист​ оно останется на​ данных в ячейке​ элементов, расположенным в​Используем именованный диапазон​ выпадающего списка.​Проверка данных (Data validation)​ же листе, где​Теперь создадим второй выпадающий​ который понимает Excel.​ инструмент «Удалить обводку​

​ кнопку «Очистить все»​ сообщения в примечании,​ сделать таблицу в​ значений».​ Excel, которая связана​

​ создания списка на​ или книгу, выполните​ экране до тех​ или диапазоне см.​ другой книге, файл​Создадим Именованный диапазон Список_элементов,​Недостатки​или в меню​

Обводка.

​ список).​ список, в котором​ То есть, если​ неверных данных».​ (указано на втором​ которое будет появляться​ Excel».​В этом окне​ с сайтом SharePoint.​ другом листе можно​ это действие после​

​ пор, пока вы​ в статье Добавление​ Источник.xlsx должен быть​ содержащий перечень элементов​этого подхода: элементы​Данные — Проверка (Data​А вот для зависимого​ будут отображаться модели​ в ячейке лежит​Примечание. Обведение несоответствующих критериям​ рисунке).​ перед вводом данных​Допустим, что в таблице​ на закладке «Параметры»​ Чтобы добавить проверку​ скрыть лист, который​

Проверка вводимых значений в Excel

​ настройки параметров проверки.​ не перейдете к​ проверки данных в​ открыт и находиться​ выпадающего списка (ячейки​ списка легко потерять​ — Validation)​ списка моделей придется​ выбранной в первом​

Проверка данных в Excel

​ текст «А1», то​ данных работают только​​

Прибыль.

​ в ячейку. А​ прайс-листа с розничными​ в разделе «Условия​ данных, необходимо удалить​ содержит список, а​ Перед тем как​ другой ячейке или​

  1. ​ ячейку или диапазон​ в той же​A1:A4​ (например, удалив строку​
  2. ​из выпадающего списка выбираем​ создать именованный диапазон​ списке марки. Также​ функция выдаст в​ в тех диапазонах,​Данным способом проверяются данные​Прибыль.
  3. ​ точнее как только​ ценами на товары,​ проверки» — «Тип​ связь таблицы Excel​ затем защитить книгу,​Обвести неверные данные.
  4. ​ защитить лист, убедитесь,​ не нажмете клавишу​ ячеек.​ папке, иначе необходимо​

​на листе Список).​ или столбец, содержащие​ вариант проверки​ с функцией​ как в предыдущем​ результате ссылку на​

​ в которых установлены​

Окно контрольного значения в Excel

​ только в процессе​ ячейка будет активной​ в одном из​ данных» выбираем функцию​ или преобразовать ее​ чтобы пользователи не​ что разблокированы любые​ ESC.​Проверка данных используется для​ указывать полный путь​Для этого:​

​ случае, откройте окно​ ячейку А1. Если​ эти же критерии​ ввода. Если данные​ сразу высветлиться соответствующее​ столбцов указана процентная​ «Другой».​

ИТОГО.

​ в диапазон.​ смогли получить доступ​ ячейки, которые необходимо​Вы можете настроить сообщение​ ограничения типа данных​

  1. ​ к файлу. Вообще​выделяем​B1​и вводим в​(OFFSET)​Проверки данных​ в ячейке лежит​
  2. ​ инструментом «Проверка данных».​ уже введенные они​ примечание. Данное примечание​ ставка НДС. Как​В строке «Формула»​
  3. ​Возможно, в настоящее время​ к этому списку.​ проверить. В противном​Окно контрольного значения.
  4. ​ на второй вкладке​ или значений, которые​ ссылок на другие​А1:А4​); не удобно вводить​ качестве​, который будет динамически​, но в поле​

​ слово «Маша», то​​ будут не проверенные.​ повышает контроль над​

Пример.

​ часто бывает в​ пишем такую формулу.​ вводятся данные.​Если вы измените параметры​ случае пользователям не​ «Проверка данных».​ можно вводить в​ листы лучше избегать​,​ большое количество элементов.​Источника (Source)​ ссылаться только на​Источник​

​ функция выдаст ссылку​Смоделируем следующую ситуацию. Нужно​ Например, в столбце​ ошибками связанных с​ рутинной работе срабатывает​=И(ЛЕВСИМВ(A31)=»Ф»;ДЛСТР(A31)=3;ЕЧИСЛО(ЗНАЧЕН(ПРАВСИМВ(A31;2))))​ Во время ввода​ проверки одной ячейки,​ удастся ввести данные​Когда пользователи привыкнут к​ ячейку. Например, ее​

Связанные (зависимые) выпадающие списки

Способ 1. Функция ДВССЫЛ (INDIRECT)

​ или использовать Личную​нажимаем Формулы/ Определенные имена/​​ Подход годится для​​знак равно и​ ячейки моделей определенной​нужно будет ввести​ на именованный диапазон​ закупить расходные материалы​ B нельзя ввести​ вводом значений. В​ человеческий фактор и​Пояснения к формуле.​ данных в ячейку​ вы можете автоматически​ в ячейки. См.​ вводу данных, вы​ можно применять для​ книгу макросов Personal.xlsx​ Присвоить имя​ маленьких (3-5 значений)​​ имя нашего диапазона,​​ марки. Для этого:​ вот такую формулу:​ с именем​

​ для небольшой фирмы.​ текст после установки​ нем можно указать​ по ошибке для​

​Этой формулой мы​ команда «Проверка данных»​ применить изменения ко​ статью Защита листа.​ можете снять флажок​ расчета максимального разрешенного​​ или Надстройки.​​в поле Имя вводим​ неизменных списков.​ т.е.​Нажмите​​=ДВССЫЛ(F3)​Маша​ Требуется приобрести канцелярские​​ условий заполнения в​ правильное значение для​ одной из категорий​​ говорим Excel, что​​ недоступна. Нажмите клавишу​​ всем остальным ячейкам,​​Если требуется открыть общий​Отображать подсказку, если ячейка​ значения в ячейке​Если нет желания присваивать​ Список_элементов, в поле​​Преимущество​​=Модели​​Ctrl+F3​​или =INDIRECT(F3)​

​и т.д. Такой,​ принадлежности, потребительские принадлежности​ нем ячеек. Но​ данных ячеек.​ товаров, вместо ставки​ в ячейках столбца​ ВВОД, чтобы завершить​ имеющим такие же​ доступ к книге,​ является текущей​ на основе значения​ имя диапазону в​ Область выбираем Книга;​: быстрота создания списка.​Вуаля!​или воспользуйтесь кнопкой​где F3 — адрес​

​ своего рода, «перевод​ и моющие средства.​ заголовок в ячейке​После заполнения всех параметров​ НДС 20% была​​ А (с ячейки​ ввод данных, или​​ параметры. Для этого​​ можно сделать это​​.​​ в другой ячейке​​ файле Источник.xlsx, то​Теперь на листе Пример,​Элементы для выпадающего списка​4 способа создать выпадающий​​Диспетчер имен (Name manager)​​ ячейки с первым​​ стрелок» ;)​​ Списки закупаемых позиций​​ B1 «Цена» остался​​ в окне «Проверка​ введена старая ставка​ А31) данные должны​ ESC, чтобы отменить​ на вкладке​​ после настройки проверки​​Также можно выводить​ книги. В приведенном​

​ формулу нужно изменить​ выделим диапазон ячеек,​ можно разместить в​ список в ячейках​на вкладке​ выпадающим списком (замените​Возьмем, например, вот такой​​ записываем отдельно на​​ без предупреждения об​​ вводимых значений» нажмите​​ 19%. Эта небольшая​ начинаться на букву​

​ данных. Если книга​сообщение об ошибке​ ниже примере пользователь​ на =ДВССЫЛ(«[Источник.xlsx]лист1!$A$1:$A$4»)​

​ которые будут содержать​​ диапазоне на листе​​ листа​Формулы (Formulas)​ на свой).​ список моделей автомобилей​

​ каждом листе, разделяя​​ ошибке.​

  • ​ на кнопку ОК.​ разница в данных​ «ф» — это​Возможно, лист защищен или​​установите флажок​​ открыта для общего​при вводе пользователем​ ввел в ячейку​СОВЕТ:​ Выпадающий список.​ EXCEL, а затем​Автоматическое создание выпадающих списков​. В версиях до​Все. После нажатия на​ Toyota, Ford и​
  • ​ их по типам​Внимание! Если ячейки будут​В результате возле каждой​ – 1% может​ часть формулы «ЛЕВСИМВ(А31)=»Ф»;​ является общим.​Распространить изменения на другие​ доступа, изменение параметров​ неверных данных.​​ значение «абв», которое​​Если на листе​вызываем Проверку данных;​ в поле Источник​
  • ​ при помощи инструментов​ 2003 это была​ОК​ Nissan:​
Способ 2. Список соответствий и функции СМЕЩ (OFFSET) и ПОИСКПОЗ (MATCH)

​ товаров.​ скопированы, а не​ заполняемой ячейки справа​ создать большие проблемы​

​ .​ Если книга защищена или​ ячейки с тем​ проверки данных невозможно,​Существует три типа сообщений​

  • ​ является для нее​ много ячеек с​​в поле Источник вводим​​ инструмента Проверки данных​​ надстройки PLEX​​ команда меню​​содержимое второго списка​​Выделим весь список моделей​Наперед не известно, сколько​ введены то их​​ появляется стрелка выпадающего​ для фирмы с​Всего в ячейке​
  • ​ используется совместно, изменить​​ же условием​​ пока не будет​​ об ошибке.​
  • ​ недопустимым.​ правилами Проверки данных,​​ ссылку на созданное​​ указать ссылку на​Выбор фото из выпадающего​​Вставка — Имя -​​ будет выбираться по​​ Тойоты (с ячейки​ позиций будет в​ значения так же​ списка, в которой​ всевозможными последствиями. Чтобы​

​ должно быть написано​ параметры проверки данных​.​ отменен общий доступ.​​Значок​ ​Проверка данных незаменима в​​ то можно использовать​ имя: =Список_элементов.​ этот диапазон.​ списка​

  • ​ Присвоить (Insert -​​ имени диапазона, выбранного​​ А2 и вниз​​ каждой группе товаров.​​ не будут проверены.​​ указана правильная процентная​​ исключить ошибки созданных​ 3 знака –​ невозможно. Необходимо сначала​​Если проверка данных не​Можно применить поверку данных​Тип​
  • ​ том случае, если​ инструмент Выделение группы​Примечание​​Предположим, что элементы списка​​Выпадающий список с автоматическим​​ Name — Define)​​ в первом списке.​ до конца списка)​На листе ИТОГО должно​

​Чтобы проверить соответствуют ли​

​ по причине человеческого​ это функция в​ отменить общий доступ​ работает, проверьте указанные​ к ячейкам, в​Описание​ необходимо предоставить общий​

​ ячеек (Главная/ Найти​​Если предполагается, что​​ шт;кг;кв.м;куб.м введены в​ удалением уже использованных​Создайте новый именованный диапазон​Минусы​ и дадим этому​ быть посчитано, сколько​ все введенные данные,​Внимание! Инструмент «Проверка данных»​ фактора, воспользуемся встроенным​

​ формуле – ДЛСТР(А31)=3.​ или снять защиту​

  • ​ которые уже введены​Остановка​ доступ к книге​
  • ​ и выделить/ Выделение​ перечень элементов будет​​ ячейки диапазона​​ элементов​ с любым именем​такого способа:​ диапазону имя​ денег нужно для​ определенным условиям в​
  • ​ срабатывает только при​ инструментом Excel для​А функция ЕЧИСЛО(ЗНАЧЕН(ПРАВСИМВ(A31;2)))​ книги.​
  • ​Пользователи не копируют и​ данные. Однако в​​Предотвращает ввод недопустимых данных​​ другим пользователям и​ группы ячеек). Опция​ дополняться, то можно​A1:A4​Динамическая выборка данных для​ (например​
  • ​В качестве вторичных (зависимых)​Toyota​ приобретения каждой группы​

​ столбце и нет​ вводе значений в​

​ проверки данных, который​ говорит, что в​Если вы получили книгу​ не вставляют данные.​

Где находится проверка данных в экселе

Используйте проверку данных, чтобы ограничить тип данных или значения, которые пользователи вводит в ячейку, например раскрывающийся список.

Браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

Проверьте, как это работает!

Выделите ячейки, для которых необходимо создать правило.

Выберите Data >Data Validation (Проверка данных >).

Проверка данных

На вкладке Параметры в разделе Разрешить выберите параметр:

Целое число — ограничивает ячейку только целыми числами.

Decimal — ограничивает ячейку только десятичными числами.

Список — для выбора данных из раскрывающегося списка.

Date — ограничивает ячейку только датой.

Time — ограничивает ячейку только временем.

Длина текста — для ограничения длины текста.

Custom — для настраиваемой формулы.

В разделе Данные выберите условие.

Задайте другие обязательные значения в зависимости от того, что вы выбрали для параметра Разрешить и Данные.

Перейдите на вкладку Входное сообщение и настройте сообщение, которое пользователи увидят при вводе данных.

Установите флажок Показывать входное сообщение при выборе ячейки , чтобы отобразить сообщение, когда пользователь выбирает выбранные ячейки или наносит указатель мыши на нее.

Перейдите на вкладку Оповещение об ошибке , чтобы настроить сообщение об ошибке и выбрать стиль.

Нажмите кнопку ОК.

Теперь, если пользователь попытается ввести недопустимое значение, появится предупреждение об ошибке с настроенным сообщением.

Скачивание примеров

Если вы создаете лист, на котором пользователи должны вводить данные, может потребоваться ограничить ввод определенным диапазоном дат или чисел либо разрешить ввод только положительных целых чисел. Excel может ограничить ввод данных определенными ячейками с помощью проверка данных, предложить пользователям ввести допустимые данные при выборе ячейки и отобразить сообщение об ошибке, когда пользователь вводит недопустимые данные.

Ограничение ввода данных

Выделите ячейки, для которых нужно ограничить ввод данных.

На вкладке Данные выберите Проверка данных > Проверка данных.

Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.

В поле Разрешить выберите тип данных, которые нужно разрешить, и заполните ограничивающие условия и значения.

Примечание: Поля, в которые вы вводите ограничивающие значения, помечаются на основе выбранных данных и условий ограничения. Например, если в качестве типа данных выбрана дата, можно ввести ограничивающие значения в полях с минимальными и максимальными значениями с метками «Дата начала » и «Дата окончания«.

Запрос для пользователей на ввод допустимых значений

Когда пользователи выбирают ячейку с требованиями к вводу данных, можно отобразить сообщение с описанием допустимых данных.

Выделите ячейки, в которых для пользователей нужно отображать запрос на ввод допустимых данных.

На вкладке Данные выберите Проверка данных > Проверка данных.

Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.

На вкладке Входное сообщение выберите поле Показывать входное сообщение при выборе ячейки проверка.

В поле Заголовок введите заголовок сообщения.

В поле Входное сообщение введите сообщение, которое нужно отобразить.

Отображение сообщения об ошибке при вводе недопустимых данных

При наличии ограничений для данных и при вводе пользователем недопустимых данных в ячейку можно отображать сообщение об ошибке.

Выберите ячейки, в которых нужно отображать сообщение об ошибке.

На вкладке Данные щелкните Проверка данных > Проверка данных .

Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в разделе Защита книги .

На вкладке Предупреждение об ошибке в поле Заголовок введите заголовок сообщения.

В поле Сообщение об ошибке введите сообщение, которое будет отображаться при вводе недопустимых данных.

Выполните одно из следующих действий:

Во всплывающем меню Стиль выберите

Требовать от пользователей исправления ошибки перед продолжением

Предупреждение пользователей о том, что данные недопустимы, и требовать, чтобы они выбрали Да или Нет , чтобы указать, хотят ли они продолжить

Предупреждение

Предупреждать пользователей о том, что данные недопустимы, но разрешить продолжать после закрытия предупреждающего сообщения

Добавление проверки данных в ячейку или диапазон ячеек

Примечание: Первые два действия, указанные в этом разделе, можно использовать для добавления любого типа проверки данных. Действия 3–7 относятся к созданию раскрывающегося списка.

Выделите одну или несколько ячеек, к которым нужно применить проверку.

На вкладке Данные в группе Средства обработки данных выберите Проверка данных.

На вкладке Параметры в поле Разрешить выберите Список.

В поле Источник введите значения списка, разделенные запятыми. Например, введите Low, Average, High.

Убедитесь, что выбран раскрывающийся список В ячейке проверка. В противном случае вы не увидите стрелку раскрывающегося списка рядом с ячейкой.

Чтобы указать способ обработки пустых (NULL) значений, установите или снимите флажок Игнорировать пустой проверка.

Протестируйте проверку данных, чтобы убедиться, что они работают правильно. Попробуйте ввести в ячейку сначала допустимые, а потом недопустимые данные и убедитесь, что параметры проверки применяются, как вы хотите, а ваши сообщения появляются в нужный момент.

После создания раскрывающегося списка убедитесь, что он работает так, как нужно. Например, можно проверить, достаточно ли ширины ячеек для отображения всех ваших записей.

Удалить проверку данных . Выберите ячейку или ячейки, содержащие проверку, которую требуется удалить, перейдите в раздел Проверка данных > данные и в диалоговом окне проверки данных нажмите кнопку Очистить все, а затем нажмите кнопку ОК.

В таблице перечислены другие типы проверки данных и указано, как применить их к данным на листе.

Разрешить вводить только целые числа из определенного диапазона

Выполните действия 1–2, указанные выше.

В списке Разрешено выберите Целое число.

В поле Данные выберите нужный тип ограничения. Например, чтобы задать верхний и нижний пределы, выберите между.

Введите минимальное, максимальное или определенное разрешенное значение.

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

Например, допустим, что вы проверяете значения в ячейке F1. Чтобы установить минимальное ограничение на количество дочерних элементов в ячейке в два раза, выберите больше или равно в поле Данные и введите формулу =2*F1 в поле Минимум .

Разрешить вводить только десятичные числа из определенного диапазона

Выполните действия 1–2, указанные выше.

В поле Разрешить выберите Десятичный.

В поле Данные выберите нужный тип ограничения. Например, чтобы задать верхний и нижний пределы, выберите между.

Введите минимальное, максимальное или определенное разрешенное значение.

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

Примечание: Чтобы разрешить пользователю вводить процентные значения, например 20 %, выберите Десятичный в поле Разрешить , выберите тип ограничения, которое требуется в поле Данные , введите минимальное, максимальное или определенное значение в виде десятичного разряда, например .2 , а затем отобразите ячейку проверки данных в виде процента, выбрав ячейку и щелкнув процента в группе Число на вкладке Главная .

Разрешить вводить только даты в заданном интервале времени

Выполните действия 1–2, указанные выше.

В поле Разрешить выберите Дата .

В поле Данные выберите нужный тип ограничения. Например, чтобы разрешить даты после определенного дня, выберите больше.

Введите начальную, конечную или определенную разрешенную дату.

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

Разрешить вводить только время в заданном интервале

Выполните действия 1–2, указанные выше.

В поле Разрешить выберите Время.

В поле Данные выберите нужный тип ограничения. Например, чтобы разрешить время до определенного времени суток, выберите меньше.

Укажите время начала, окончания или определенное время, которое необходимо разрешить. Если вы хотите ввести точное время, используйте формат чч:мм.

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

Разрешить вводить только текст определенной длины

Выполните действия 1–2, указанные выше.

В поле Разрешить выберите Длина текста.

В поле Данные выберите нужный тип ограничения. Например, чтобы разрешить до определенного количества символов, выберите значение меньше или равно.

В этом случае нам нужно ограничить ввод до 25 символов, поэтому выберите значение меньше или равно в поле Данные и введите значение 25 в поле Максимум .

Вычислять допустимое значение на основе содержимого другой ячейки

Выполните действия 1–2, указанные выше.

В поле Разрешить выберите нужный тип данных.

В поле Данные выберите нужный тип ограничения.

В поле или полях под полем Данные выберите ячейку, которую нужно использовать, чтобы указать, что разрешено.

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

В примерах ниже при создании формул с условиями используется настраиваемый вариант. В этом случае содержимое поля «Данные» не играет роли.

Снимки экрана в этой статье были сделаны в Excel 2016; но функции одинаковы в Excel в Интернете.

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

Значение в ячейке, содержащей код продукта (C2), всегда начинается со стандартного префикса «ID-» и имеет длину не менее 10 (более 9) знаков.

Ячейка с наименованием продукта (D2) содержала только текст.

Значение в ячейке, содержащей чью-то дату рождения (B6), было больше числа лет, указанного в ячейке B4.

=ЕСЛИ(B6<=(СЕГОДНЯ()-(365*B4));TRUE,FALSE)

Все данные в диапазоне ячеек A2:A10 содержали уникальные значения.

=СЧЁТЕСЛИ($A$2:$A$10;A2)=1

Пример 4. Формулы для проверки данных

Примечание: Необходимо сначала ввести формулу проверки данных в ячейку A2, а затем скопировать эту ячейку в ячейки A3:A10 так, чтобы второй аргумент СЧЁТЕСЛИ соответствовал текущей ячейке. То есть часть A2)=1 изменится на A3)=1, A4)=1 и т. д.

Адрес электронной почты в ячейке B4 содержал символ @.

Совет: Если вы владелец малого бизнеса и хотите получить дополнительные сведения о настройке Microsoft 365, посетите раздел Справка и обучение для малого бизнеса.

Проверка данных в EXCEL

Инструмент Проверка данных ( Данные/ Работа с данными/ Проверка данных ) не был бы столь популярным, если бы его функциональность ограничивалась бы только собственно проверкой. Ниже кратко перечислены основные приемы работы с этим инструментом.

A. Проверка введенных значений

Как видно на рисунке снизу, условия проверки вводимого значения можно настроить очень гибко.

Можно разрешить ввод значений в ячейку только определенного типа, выбрав необходимое условие из выпадающего (раскрывающегося) списка:

  • Целое число . В ячейку разрешен ввод только целых чисел, причем принадлежащих определенному диапазону;
  • Действительное . В ячейку разрешен ввод только чисел, в том числе с десятичной частью (нельзя ввести текст, дату ввести можно);
  • Дата. Предполагается, что в ячейку будут вводиться даты начиная от 01.01.1900 до 31.12.9999. Подробнее о формате Дата — в статье Как Excel хранит дату и время
  • Время . Предполагается, что в ячейку с Проверкой данный этого типа будет вводиться время. Например, на рисунке ниже приведено условие, когда в ячейку разрешено вводить время принадлежащее только второй половине дня, т.е. от 12:00:00 до 23:59:59. Вместо утомительного ввода значения 12:00:00 можно использовать его числовой эквивалент 0,5. Возможность ввода чисел вместо времени следует из того, что любой дате в EXCEL сопоставлено положительное целое число, а следовательно времени (т.к. это часть суток), соответствует дробная часть числа (например, 0,5 – это полдень). Числовым эквивалентом для 23:59:59 будет 0,99999.

  • Длина текста . В ячейку разрешен ввод только определенного количества символов. При этом ограничении можно вводить и числа и даты, главное, чтобы количество введенных символов не противоречило ограничению по длине текста. Например, при ограничении количества символов менее 5, нельзя ввести дату позднее 13/10/2173, т.к. ей соответствует число 99999, а 14/10/2173 — это уже 100000, т.е. 6 символов. Интересно, что при ограничении, например, менее 5 символов, вы не сможете ввести в ячейку формулу =КОРЕНЬ(2) , т.к. результат =1,4142135623731 (в зависимости от заданной в EXCEL точности), а вот =КОРЕНЬ(4) – сможете, ведь результат =2, а это только 1 символ.
  • Список . Наверное, самый интересный тип данных. В этом случае ввод значений в ячейку можно ограничить ранее определенным списком. Например, если в качестве источника указать через точку с запятой единицы измерения товара шт;кг;кв.м;куб.м , то ничего другого, кроме этих 4-х значений из списка вам выбрать не удастся. В источнике можно указать диапазон ячеек, содержащий заранее сформированный список или ссылку на Именованную формулу . Пример приведен в статье Выпадающий (раскрывающийся) список
  • Другой . В ячейку разрешен ввод значений удовлетворяющих более сложным критериям. Для задания критериев необходимо использовать формулу. Рассмотрим это условие подробнее.

При выбранном типе Другой, в поле Формула нужно ввести формулу для расчета логического значения. Если оно принимает значение ИСТИНА, то такое значение разрешено вводить в ячейку, если ЛОЖЬ, то ваше значение ввести не получится. В качестве аргумента формулы нужно использовать ссылку на саму ячейку, к которой применяется Проверка данных или ссылку на зависящую от нее ячейку. Например,

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

Пояснение

Ячейка B2 содержала только текст

В Типе данных нет возможности выбрать тип Текст , поэтому приходится этого добиваться косвенно. Вы можете Проверку данных применить прямо к ячейке B2

Допустить ввод значения в ячейку B1 только в случае, если после ввода значение в ячейке D1 будет больше 100, в D2 меньше, чем 400

Проверку данных применяем к ячейке B1 . При этом в ячейке D1 введена формула =B1*2 , а в D2 – формула =B1*3 . Хотя эта формула эквивалентна ограничению Действительное с диапазоном от 50 до 133,33, но при более сложных связях ячеек, этот прием может быть полезен

Значение в ячейке, содержащей возраст работника ( С1 ), всегда должно быть больше числа полных лет работы ( D1 ) плюс 18 (минимальный возраст приема на работу)

При заполнении таблицы данными о возрасте и стаже работы можно поставить эту проверку для обеих ячеек ( C1 и D1 ). Для этого нужно выделить сразу 2 ячейки, вызвать Проверку данных и немного модифицировать формулу =ЕСЛИ($C1>$D1+18;ИСТИНА;ЛОЖЬ)

Все данные в диапазоне ячеек A1:A20 содержали уникальные значения

Необходимо выделить ячейки А1:А20 , вызвать инструмент Проверка данных и ввести формулу. Вторую формулу можно использовать для всего столбца А , для этого необходимо выделить не диапазон, а весь столбец А

Значение в ячейке, содержащей имя кода продукта ( B5 ), всегда начиналось со стандартного префикса «ID-» и имело длину не менее 10 знаков.

Проверку данных вводим для ячейки B5

При выделении нескольких ячеек, там где нужно, не забывайте указывать абсолютную ссылку на ячейки (например, $A$1:$A$20 ).

При использовании инструмента Проверка данных , предполагается, что в ячейку будут вводиться константы ( 123, товар1, 01.05.2010 и пр.), хотя никто не запрещает вводить и формулы. В этом случае проверяться все равно будет результат вычисления формулы. Вообще вводить формулы в ячейки с проверкой данных не советую – легко запутаться. В этом случае советую использовать Условное форматирование .

В. Отображение комментария, если ячейка является текущей.

Используйте вкладку Сообщение для вывода , чтобы отображать комментарий.

В отличие от обычного примечания ( Рецензирование/ Примечание/ Создать примечание ), которое пропадает после того, как курсор мыши уходит с ячейки (если не активна опция Показать все примечания ), этот комментарий отображается всегда, когда ячейка выделена.

С. Вывод подробного сообщения об ошибке.

После ввода ошибочного значения Проверка данных может отобразить подробное сообщение о том, что было сделано не так. Это некий аналог Msgbox() из VBA .

D. Создание связанных диапазонов (списков)

Если в качестве Типа данных на вкладке Параметры мы выберем Список , то сможем сформировать связанные диапазоны. Тема так обширна, что она выделена в отдельную статью Связанный список .

Е. Использование в правилах ссылок на другие листы

В EXCEL 2007 в Проверке данных , как и в Условном форматировании нельзя впрямую указать ссылку на диапазоны другого листа, например, так =Лист2!$A$1 . Позволяют обойти это ограничение использование Имен .

Если в Проверке данных нужно сделать, например, ссылку на ячейку А1 другого листа, то нужно сначала определить Имя для этой ячейки, а затем сослаться на это имя в правиле Проверке данных .

В Excel 2010, напротив, можно использовать правила проверки данных, ссылающиеся на значения на других листах. В Excel 2007 и Excel 97-2003 проверка данных этого типа не поддерживается и не отображается на листе. Однако все правила проверки данных остаются доступными в книге и применяются при повторном открытии книги в Excel 2010, если они не были изменены в Excel 2007 или Excel 97-2003.

F. Как срабатывает Проверка данных

Проверка данных явно срабатывает при вводе в ячейку значений с клавиатуры с последующим нажатием клавиши ENTER . В этом случае появляется окно с описанием ошибки.

Если значения вставляются через Буфер обмена ( Вставить значения ) или с использованием сочетания клавиш CTRL + D (копирование значения из ячейки сверху) или копируются Маркером заполнения сверху вниз, то проверка в явном виде не осуществляется. Кроме того, при копировании значений можно вообще случайно удалить правила Проверки данных , например если в ячейке источнике не определена Проверка данных , а данные из нее вставляются через Буфер обмен а с использованием комбинации клавиш CTRL+V .

Поясним на примере. Предположим, к ячейке А1 применена Проверка данных с условием проверки Другой , где в поле формула введено =СТРОКА(A1)=1 , т.е. для всех ячеек из первой строки условие Проверки данных будет принимать значение ИСТИНА, для других строк — ЛОЖЬ вне зависимости от содержания ячейки.

Теперь выделим ячейку А2 и нажмем CTRL+D . Значение из А1 скопируется в А2 вместе с условием Проверки данных . Несмотря на то, что теперь условие Проверки данных будет принимать значение ЛОЖЬ, никакого предупреждающего сообщения выведено не будет. Чтобы убедиться, что данные в ячейках соответствуют условиям определенным в Проверке данных , нужно вызвать команду меню Обвести неверные данные ( Данные/ Работа с данными/ Проверка данных/ Обвести неверные данные ). Ячейки с неверными данными будут обведены красными овалами. Теперь опять выделим ячеку А2 и нажмем клавишу F2 (войдем в режим Правки), затем нажмем ENTER — появится окно с сообщением, что введенное значение неверно.

Есть еще один способ обхода проверки данных. Предположим, ввод в ячейку ограничен значениями от 1 до 3. Теперь в любую другую ячейку без Проверки данных введем значение 4. Выделим эту ячейку, в Строке формул выделим значение 4 и скопируем его в Буфер обмена . Теперь выделим ячейку с Проверкой данных и нажмем CTRL+V . Значение вставилось в ячейку! Кроме того, Проверка данных осталась нетронутой в отличие от случая, когда через Буфер обмена , например, вставляется значение из WORD. Чтобы убедиться, что данные в ячейке не соответствуют условиям определенным в Проверке данных , нужно вызвать команду меню Обвести неверные данные ( Данные/ Работа с данными/ Проверка данных/ Обвести неверные данные ).

G. Поиск ячеек с Проверкой данных

Если на листе много ячеек с Проверкой данных , то можно использовать инструмент Выделение группы ячеек ( Главная/ Найти и выделить/ Выделение группы ячеек ).

Опция Проверка данных этого инструмента позволяет выделить ячейки, для которых проводится проверка допустимости данных (заданная с помощью команды Данные/ Работа с данными/ Проверка данных ). При выборе переключателя Всех будут выделены все такие ячейки. При выборе опции Этих же выделяются только те ячейки, для которых установлены те же правила проверки данных, что и для активной ячейки.

Проверка данных Excel

Во время работы с большими объемами информации, особенно когда ее обработка осуществляется формулами или макросами, важно, чтобы данные не имели ошибок, которые способны повлиять на итог. Сложности в получении корректных исходников появляются, когда они поступают из неструктурированных источников (плохо спроектированные базы данных, ручное заполнение таблиц и форм незаинтересованными в результате людьми). Особенно трудно добиться правильного оформления по определенной маске (шаблону). Например, дату в виде строки можно записать несколькими способами:

  • 01.01.2001;
  • 01/01/2001;
  • 1 января 2001 года и т.д.

В статье описывается функционал, позволяющий упростить процесс сбора и проверки данных в Excel.

Где находится?

Для настройки параметров проверки вводимых значений необходимо на вкладке «Данные» в области «Работа с данными» кликнуть по иконке «Проверка данных» либо выбрать аналогичный пункт из раскрывающегося меню:

Расположение функции проверка данных на ленте Excel

На экране появиться окно с настройками по умолчанию, где в качестве типа данных может быть использовано любое значение:

Окно проверки данных с настройками по умолчанию

Настройка условия проверки

Изначально требуется выбрать тип проверяемых данных, что будет являться первым условием. Всего предоставлено 8 вариантов:

  • Целое число;
  • Действительное число;
  • Список;
  • Дата;
  • Время;
  • Длина текста;
  • Другой.

В соответствии с выбранным пунктом, появляется возможность выбора дополнительных условий. Для чисел, дат, времени и длины текста возможно указать ограничения: меньше, больше, диапазон и т.п. К списку обязательно привязывается источник, а пункт «другой» предлагает ввести формулу, возвращающую логическое значение.

Самым необычным видом является выпадающий список.

Пример выпадающего списка в ячейке excel

Он позволяет выбирать значения, указанные в качестве источника и ограничивать допустимые значения им же. Также возможно создавать динамические выпадающие списки.

Всплывающая подсказка ячейки Excel

Функционал проверки данных в Excel позволяет настраивать всплывающие подсказки для ячеек листа. Для этого следует перейти на вторую вкладку окна проверки вводимых значений – «Сообщение для ввода».

Пример настройки всплывающей подсказки для ячейки

На изображении показан пример возможного сообщения для ячейки, в которую вносятся целые числа от 1 до 31 (настройки вкладки «Параметры»). Заголовок и сообщения указываются по Вашему усмотрению, каких-либо правил к их оформлению нет. Не забудьте установить галочку на поле «Отображать подсказку, если ячейка является текущей», иначе сообщение будет деактивировано.

Пример всплывающей подсказки в Excel:

Отображение вслывающей подсказки при выделении ячейки

Вывод сообщения об ошибке

Последняя вкладка окна проверки данных позволяет настроить поведение и вывод сообщений при обнаружении ошибочного значения.

Существует три варианта сообщений, отличающихся по поведению:

  • Останов;
  • Предупреждение;
  • Сообщение.

Настройки вывода сообщения об ошибке проверки данных в Excel

Останов является сообщением об ошибке и позволяет произвести только 2 действия: отменить ввод и повторить ввод. В случае отмены новое значение будет изменено на предыдущее. Повтор ввода дает возможность скорректировать новое значение.

Предупреждение более лояльно в сравнении с остановом, так как позволяет оставлять значение, не соответствующее условиям проверки, после подтверждения ввода пользователем.

Сообщение выводить ошибку в виде простой информации и дает возможность отменить последнее действие.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *