Функция проверка данных дан экспорт. Проверка данных в MS EXCEL

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

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

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

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

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

  • Целое число . В ячейку разрешен ввод только целых чисел, причем принадлежащих определенному диапазону;
  • Действительное . В ячейку разрешен ввод только чисел, в том числе с десятичной частью (нельзя ввести текст, дату ввести можно);
  • Дата. Предполагается, что в ячейку будут вводиться даты начиная от 01.01.1900 до 31.12.9999. Подробнее о формате Дата - в статье
  • Время . Предполагается, что в ячейку с Проверкой данный этого типа будет вводиться время. Например, на рисунке ниже приведено условие, когда в ячейку разрешено вводить время принадлежащее только второй половине дня, т.е. от 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)

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

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

И(D1>100;D2<400)

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

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

=ЕСЛИ(C1>D1+18;ИСТИНА;ЛОЖЬ)

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

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

=СЧЁТЕСЛИ($A$1:$A$20;A1)=1

=ПОИСКПОЗ(A1;$A:$A;0)=СТРОКА(A1)

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

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

=И(ЛЕВСИМВ(B5;3)="ID-"; ДЛСТР(B5)>9)

Проверку данных вводим для ячейки 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. Поиск ячеек с Проверкой данных

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

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


Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

Проверка данных является неплохим инструментом Excel для контроля за вносимыми на листах изменениями, не прибегая к помощи VBA. С её помощью можно ограничить ввод в ячейку, разрешив вводить только даты либо время, либо только числа. Да к тому же еще и задать диапазон дат либо предел чисел(к примеру от 1 до 10).Применений, я думаю, можно придумать массу: для корректной работы многих формул требуются корректные исходные данные. Следовательно, мы можем с помощью Проверки данных разрешить пользователю вводить только тот тип и диапазон данных, который может обработать формула, не возвращая значение ошибки. Разберем поподробней.

Сей чудесный инструмент находится: Данные (Data) -. Должно появиться окно:

Для начала надо выделить ячейку(или диапазон ячеек) для которой необходимо установить проверку. Затем идем в Данные (Data) -Проверка данных (Data Validation) .

Первая вкладка - Параметры (Settings)

В поле Тип данных (Allow) - выбирается непосредственно тип данных, который должен быть записан в ячейке. Всего доступно 8 типов: Любое значение, Целое число, Действительное, Список, Дата, Время, Длина текста, Другой (Any Value, Whole number, Decimal, List, Date, Time, Text lenght, Custom) . Пункт Любое значение (Any Value) установлен по умолчанию, проверка не осуществляется. Подробно про пункт Списки (List) и как их создавать можно посмотреть и почитать в статье Выпадающие списки . Там все подробно и с нюансами расписано про списки в проверке данных, поэтому в данной статье рассмотрим оставшиеся 6 типов. Если кратко, то при выборе пункта Список в ячейке появляется выпадающий список допустимых значений. И ввести можно только то значение, которое присутствует в списке

Остальные типы данных:

  • Целое число (Whole number) - говорит само за себя. После установки такой проверки в ячейку можно будет внести только целое число. Т.е. число, не имеющее дробного остатка(9,1 например уже нельзя будет ввести). Так же нельзя будет ввести произвольный текст. Чаще всего подобная проверка применяется в полях для записи кол-ва штук товара и т.п. Т.е. там, где не может быть дробных значений.
  • Действительное (Decimal) - тоже, что и в предыдущем пункте, но ввести можно любое число - хоть целое, хоть дробное, но невозможно будет ввести текст
  • Дата (Date) - В ячейке может содержаться только значение даты. Дата может быть записана в любом формате, допустимом в текущей локализации Windows. Здесь тот же нюанс, что и проверкой на числа, только в обратную сторону - любая дата это число, поэтому по сути можно будет ввести любое целое число, которое может быть переведено Excel-м в дату.
  • Время (Time) - можно записывать значения в формате времени для текущей локализации и так же как с датой в ячейку можно будет вводить не только время, но и любые числа: целые или дробные
  • Длина текста (Text lenght) - позволяет ограничить количество вводимых в ячейку символов. Данные могут являться и числом и текстом и временем. Чем угодно, только должны соответствовать остальным условиям проверки. Часто применяется для контроля ввода данных по ИНН, КПП, БИК, артикулам и т.п.

    • Если посмотреть на картинку в начале статьи, то можно увидеть еще три поля, помимо тех, которые я перечислил: Значение (Data) , Минимум (Minimum) и Максимум (Maximum) . Поле Значение содержит несколько вариантов проверки: Между, Вне, Равно, Больше, Меньше, Больше или равно, Меньше или равно (between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to) и доступно оно только при выборе Типа данных Целое число, Действительное, Дата, Время, Длина текста. Для других типов поле Значение недоступно. В зависимости от того, какой пункт выбран в поле Значение появляются дополнительные поля: Минимум и Максимум. При этом может быть только одно поле и название может быть иным. Например, для варианта Равно будет показано только одно поле и называться оно будет Значение (Value) . Но здесь нет никаких подводных камней и разобраться с этими полями можно не напрягаясь и без поллитры:)

    По сути все пункты довольно красноречивы и пояснять подробно каждый, думаю, смысла нет. Например, Между (between) - указывается интервал дат или чисел, в который должно входить условие(например целое число от 1 до 12: Минимум = 1, Максимум = 12). Если пользователь попытается ввести число за пределами указанного интервала(скажем число 0 , -3 или 14), то Excel выдаст сообщение об ошибке(см.ниже). Вариант Больше или равно (greater than or equal to) позволит вносить только положительные значения больше нуля, если указать в поле Минимум значение 1.
    Чуть большее внимание следует уделить варианту проверки Вне (not between) . При установленном значении Вне , в ячейку можно будет внести только данные, которые не входят в диапазон, указанный в полях Минимум и Максимум . Т.е. при тех же условиях от 1 до 12 ввести можно будет и 0 и -3 и 14 , но нельзя будет ввести 1 или 10.
    При этом в полях для ввода значений допускается указать ссылку на ячейку. Например, в ячейке A1 записана начальная дата приема заявок, в B1 - конечная дата. И надо установить в ячейках A2:A50 проверку на интервал дат, указанных как раз в A1 и B1 . Для этого выделяем ячейки A2:A50 и создаем в них проверку данных: Тип данных: Дата, Значение: Между, Начальная дата: = A1 , Конечная дата: = B1 . Теперь можно регулировать интервал без изменения самой проверки данных - просто изменяя значения ячеек A1 и B1 .

  • Другой (Custom) - на мой взгляд самый интересный и самый мощный тип проверки. Здесь нам предоставляется возможность более широко контролировать ввод данных. Для заполнения есть только одно поле - Формула (Formula) . В него необходимо записать формулу и при каждом вводе значений в ячейку, Excel проверит введенное выражение на ИСТИНУ. Если быть точнее то Excel сначала вычислит формулу в этом поле ориентируясь на введенное в ячейку с проверкой данных значение, а потом проверит - возвращает ли формула значение ИСТИНА(TRUE). Если результатом будет ИСТИНА (TRUE) , то введенное значение будет сохранено в ячейке, а если ЛОЖЬ (FALSE) , то будет выдано окно с сообщением об ошибке:

    Приведу простой пример. В ячейку А1 введем число 1. Выделяем ячейку В1 - назначаем проверку данных-Другой . В поле Формула вписываем: = B1 = A1 . Теперь в ячейку В1 можно вписать только значение, которое полностью идентично значению в ячейке А1 .

    Важно: если проверка данных в дальнейшем будет скопирована в другие ячейки, то ссылки будут смещены так, будто это простая формула. Поэтому при создании формул в проверке данных необходимо учитывать возможность смещения и при необходимости закреплять ссылки(знак доллара - $ ).

    • Вкладка Сообщение для ввода (Input Message)

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

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

    Вкладка Сообщение об ошибке (Error Alert)
    Здесь указывается отображать сообщение об ошибочном вводе или нет, и сам тип выводимого сообщения об ошибке.

    • Выводить сообщение об ошибке (Show error alert after invalid data is entered) - в большинстве случаев необходимо установить галочку. Если галочка снята, то проверка вводимых в ячейку данных не будет осуществляться.
    • Вид (Style)
      • Останов, Сообщение (Stop, Information) - можно ввести только разрешенные проверкой значения. Различается только вид сообщения(пример сообщения Останов приведен на рисунке выше).
      • Предупреждение (Warning) - в ячейку можно ввести любое значение, но при вводе значения, противоречащего проверке, появиться предупреждающее сообщение с подтверждением ввода данных.
    • Заголовок (Title) - текст, который будет показан в заголовке сообщения об ошибке. Если не указан, то в заголовке будет написано Microsoft Excel .
    • Сообщение (Error message) - непосредственно текст самого сообщения об ошибке. Если не указан, то будет показан текст примерно следующего содержания:
      Это значение не соответствует ограничениям по проверке данных, установленным для этой ячейки
      (This value doesn"t match the data validation restrictions defined for this cell)

    Но так же необходимо помнить, что какое бы условие на проверку Вы не поставили - значение в ячейке можно удалить, нажав кнопку Del . Либо скопировав ячейку из другой книги или листа и вставив на место проверки данных - проверка исчезнет, т.к. вся ячейка будет заменена скопированной. И от этого никак не уйти - такой проверку сделали разработчики...

    Как скопировать проверку данных на другие ячейки
    Все очень просто - копируем ячейку с нужной проверкой данных -выделяем ячейки для создания в них такого же условия -Правая кнопка мыши -Специальная вставка (Paste Special) -в окне выбираем Условия на значения (Validation) -Ок :

    Как удалить проверку данных из ячеек
    Выделяем необходимые ячейки -вкладка Данные (Data) -Проверка данных (Data Validation) . В поле Тип данных (Allow) устанавливаем Любое значение (Any Value) -Ок .

    Маленькая хитрость использования проверки данных
    Если проверка данных на ячейки была установлена уже после того, как данные были внесены, то очень неплохо понять, все ли данные соответствуют условиям проверки. Сделать это несложно. После того, как ячейкам была назначена проверка данных переходим на вкладку Данные (Data) -Проверка данных (Data Validation) -раскрываем меню и выбираем Обвести неверные данные (Circle Invalid Data) . Все ячейки, данные в которых не соответствуют условиям проверки данных будут обведены красной линией:

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

    Сразу после исправления неверных значений на те, которые есть в списке обводка исчезает. Если не все значения надо исправлять, а обводку тем не менее надо удалить, то после всех нужных правок просто переходим на вкладку Данные (Data) -Проверка данных (Data Validation) -раскрываем меню и выбираем Удалить обводку неверных данных (Clear Validation Circles)

    Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

    {"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

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

Выпадающий список можно создать с помощью

В этой статье создадим Выпадающий список с помощью () с типом данных Список .

Выпадающий список можно сформировать по разному.

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

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

Если в поле Источник указать через точку с запятой единицы измерения шт;кг;кв.м;куб.м , то выбор будет ограничен этими четырьмя значениями.

Теперь смотрим, что получилось. Выделим ячейку B 1 . При выделении ячейки справа от ячейки появляется квадратная кнопка со стрелкой для выбора элементов из выпадающего списка .

Недостатки этого подхода: элементы списка легко потерять (например, удалив строку или столбец, содержащие ячейку B 1 ); не удобно вводить большое количество элементов. Подход годится для маленьких (3-5 значений) неизменных списков.
Преимущество : быстрота создания списка.

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

Элементы для выпадающего списка можно разместить в диапазоне на листе EXCEL, а затем в поле Источник инструмента указать ссылку на этот диапазон.

Предположим, что элементы списка шт;кг;кв.м;куб.м введены в ячейки диапазона A 1: A 4 , тогда поле Источник будет содержать =лист1!$A$1:$A$4

Преимущество : наглядность перечня элементов и простота его модификации. Подход годится для редко изменяющихся списков.
Недостатки : если добавляются новые элементы, то приходится вручную изменять ссылку на диапазон. Правда, в качестве источника можно определить сразу более широкий диапазон, например, A 1: A 100 . Но, тогда выпадающий список может содержать пустые строки (если, например, часть элементов была удалена или список только что был создан). Чтобы пустые строки исчезли необходимо сохранить файл.

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

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

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

Ввод элементов списка в диапазон ячеек, находящегося в другой книге

Если необходимо перенести диапазон с элементами выпадающего списка в другую книгу (например, в книгу Источник. xlsx ), то нужно сделать следующее:

  • в книге Источник. xlsx создайте необходимый перечень элементов;
  • в книге Источник.xlsx диапазону ячеек содержащему перечень элементов присвойте , например СписокВнеш;
  • откройте книгу, в которой предполагается разместить ячейки с выпадающим списком;
  • выделите нужный диапазон ячеек, вызовите инструмент , в поле Источник укажите =ДВССЫЛ("[Источник.xlsx]лист1!СписокВнеш") ;

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

Если нет желания присваивать имя диапазону в файле Источник.xlsx , то формулу нужно изменить на =ДВССЫЛ("[Источник.xlsx]лист1!$A$1:$A$4")

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

Примечание :
Если выпадающий список содержит более 25-30 значений, то работать с ним становится неудобно. Выпадающий список одновременно отображает только 8 элементов, а чтобы увидеть остальные, нужно пользоваться полосой прокрутки, что не всегда удобно.

В EXCEL не предусмотрена регулировка размера шрифта Выпадающего списка . При большом количестве элементов имеет смысл список элементов и использовать дополнительную классификацию элементов (т.е. один выпадающий список разбить на 2 и более).

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

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

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

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

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

После выбора типа данных становится возможным выбор условия соответствия вводимого значения в графе «Значение:» . Выберем для примера «Между» .

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

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

Сообщение подсказка.

Вид «Останов» .

Вид «Предупреждение» .

Похожие статьи