Главная › Телефон › Проверка введенных данных в excel. Проверка вводимых данных в Excel

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

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

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

Подсказки в MS Excel

Первый способ вставки подсказки

Первым делом разберемся c подсказками. Взгляните на мою табличку. Вроде бы все просто, однако даже при такой простоте можно нагородить солидный огород. К примеру, поле «номер документ» — это №1, 1, или 22.03-1?

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

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

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

Обратите внимание: нажатие кнопки «Delete» в ячейке не удалит примечание. Избавиться от него (или изменить его текст) можно повторно щелкнув в ячейке правой кнопкой мыши и выбрав пункт «Удалить примечание» или «Изменить примечание».

Второй способ вставки подсказки

Ещё один способ ввести подсказку интересен нам только с точки зрения того, о чем пойдет разговор дальше (фильтрации ввода), тем не менее знать о нем полезно.

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

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

Проверка по числу введенных символов

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

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

Первое, что может оказаться полезным, это проверка по длине введенного текста . Выбираем пункт «Длина текста » и указываем любой диапазон ограничений. К примеру, я хочу чтобы текст в поле «Сообщение» был как можно более кратким, но в то же время ячейка не могла оставаться пустой. Значения от 3-х до 25 введенных символов, этого вполне хватит, чтоб вписать что-то вроде «Напоминание об оплате» (21 символ), а при попытке ввести более длинный текст, MS Excel выдаст окно-предупреждение и не даст завершить ввод.

Проверка по числу

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

Выбор из имеющегося списка значений

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

А теперь, сделаем проверку по выпадающему списку в MS Excel. Данные введем вручную, через точку с запятой.

Выбираем в «Проверке данных » вкладку «Параметры «, и указываем «Тип данных» → «Список» . Ввести «предустановленные» значения можно двумя способами:

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

…или выберем из диапазона

Второй способ более интересен, так как данные «спрятаны» от пользователя, зато набить их больше десятка подряд — проблематично. Первый способ позволяет без проблем задать намного больший список для выбора данных, однако прежде придется этот список разместить где-то в документе (каждое значение на отдельной строке), например на отдельном «служебном» листе в той же книге MS Excel.

Выбор из списка на листе MS Excel. Других данных ввести не удастся.

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

Кстати, можно изменить и её текст — для этого перейдите на вкладку «Сообщение для вывода » в «Проверке данных», как мы уже делали при «втором способе» вставке подсказок, измените текст на требуемый.

Условное форматирование (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}}

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

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

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

У нас имеется лист номенклатуры товаров магазина:

Теперь проверим. В ячейку B2 введите натуральное число, а в ячейку B3 отрицательное. Как видно в ячейке B3 действие оператора набора – заблокировано. Отображается сообщение об ошибке: «Введенное значение неверно».

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

Чтобы удалить проверку данных в Excel нужно: выделить соответствующий диапазон ячеек, выбрать инструмент и нажать на кнопку «Очистить все» (указано на втором рисунке).

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

Данным способом проверяются данные только в процессе ввода. Если данные уже введенные они будут не проверенные. Например, в столбце B нельзя ввести текст после установки условий заполнения в нем ячеек. Но заголовок в ячейке B1 «Цена» остался без предупреждения об ошибке.

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

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

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

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

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

1. Выделить ячейку или группу ячеек, для которых нужно определить процедуру контроля.
2. На вкладке Данные -> Работа с данными сделать щелчок на кнопке Проверка данных.
3. На вкладке Параметры окна Проверка вводимых значений задать условие проверки данных.

Условие проверки задается путем выбора в раскрывающихся списках Тип данных и Значение соответственно типа данных (целое число, действительное число, дата и др.), которые считаются правильными, и правила сравнения (между, больше, меньше и т.д.), а также границ диапазона допустимых значений (поля Минимум и Максимум). Например, чтобы задать условие, означающее, что в ячейку можно ввести только целое число от 1 до 99, в списке Тип данных надо выбрать Целое число, в списке Значение - вариант между, а в поля Минимум и Максимум ввести, соответственно, 1 и 99.

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

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

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

Вид сообщения	Значок	Командные кнопки
Остановка		Повторить, Отмена
Предупреждение		Да, Нет, Отмена
Сообщение		OK, Отмена

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

Предупреждающее сообщение заканчивается вопросом "Продолжить?". В результате щелчка на кнопке Да неверные данные записываются в ячейку. Щелчок на кнопке Нет активизирует режим редактирования.

Щелчок на кнопке OK в окне информационного сообщения оставляет неверные данные в ячейке таблицы.

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

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

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

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

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

Предположим, в ячейке 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 сотрудников, то его следует сначала отсортировать в алфавитном порядке. Затем создать выпадающий список , содержащий буквы алфавита. Второй выпадающий список должен содержать только те фамилии, которые начинаются с буквы, выбранной первым списком. Для решения такой задачи может быть использована структура или .