СОЗДАНИЕ РАСКРЫВАЮЩИХСЯ СПИСКОВ В EXCEL5

5. СОЗДАНИЕ РАСКРЫВАЮЩИХСЯ СПИСКОВ В EXCEL

Применяется к: Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel 2016 для Mac. Чтобы сделать лист удобнее, можно добавить в ячейки списки, в которых пользователь может выбрать определённое значение.

1.

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

2.

3.

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

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

4.

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

5. 6.

Советы: Не удаётся нажать кнопку Проверка данных. Приведем возможные причины. o Раскрывающиеся списки невозможно добавлять в таблицы, которые связаны с сайтом SharePoint. Удалите связь таблицы с сайтом или удалите форматирование таблицы, а затем повторите попытку. o Возможно, лист защищён или находится в режиме совместного использования. Снимите защиту или закройте совместный доступ к листу, а затем повторите попытку. На вкладке Параметры в поле Разрешить нажмите кнопку Список. В поле Источник введите знак равенства (=), а сразу за ним — имя, присвоенное списку. Например: =Отделы.

7. Установите флажок Список допустимых значений 8. Если можно оставить ячейку пустой, установите выключатель

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

подсказку, если ячейка

является текущей, введите заголовок и сообщение в соответствующие поля (до 225 знаков).

11.Откройте вкладку Сообщение об ошибке. 12.Установите флажок Выводить сообщение об ошибке, выберите параметр из поля Стиль и введите заголовок и сообщение. Если вы не хотите, чтобы сообщение отображалось, отключите выключатель.

Выбор параметра в поле Стиль. o Чтобы отобразить сообщение, не препятствующее пользователям вводить данные, которые не содержатся в раскрывающемся списке, щелкните параметр Уведомление или "Предупреждение". Параметр "Уведомление" отобразит

o сообщение с помощью значка , а "Предупреждение" — с помощью значка . Чтобы заблокировать пользователям ввод данных, которые не содержатся в раскрывающемся списке, щелкните параметр Остановить. Примечание: Если не были добавлены заголовок и текст, по умолчанию вводится заголовок "Microsoft Excel" и сообщение: "Введенное значение неверно. Набор значений, которые могут быть введены в ячейку, ограничен".

Работа с раскрывающимся списком

После создания раскрывающегося списка убедитесь, что он работает определенным образом. Например, можно проверить, достаточно ли ширины ячеек для отображения всех ваших записей. Если список записей для раскрывающегося списка находится на другом листе и вы хотите запретить пользователям его просмотр и изменение, скройте и защитите этот лист. Применяется к: Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel 2016 для Mac. После того, как создан раскрывающийся список, может понадобиться добавлять в него дополнительные элементы или удалять имеющиеся.

Изменение раскрывающегося списка, основанного на таблице Excel

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

• Чтобы добавить элемент, перейдите в конец списка и введите новый элемент. • Чтобы удалить элемент, выделите его, нажмите кнопку Удалить.

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

Изменение раскрывающегося списка, основанного на именованном диапазоне:

1. Откройте лист, содержащий именованный диапазон для раскрывающегося списка. 2. Выполните одно из указанных действий. o Чтобы добавить элемент, перейдите в конец списка и введите новый элемент. o Чтобы удалить элемент, нажмите кнопку Удалить.

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

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

5.

6.

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

Совет: Чтобы определить именованный диапазон, выделите его и найдите его имя в поле Имя.

Поиск именованных диапазонов.

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

Применяется к: Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel Starter 2010. Проверка данных позволяет ограничить тип данных или значений, которые можно ввести в ячейку. Чаще всего она используется для создания раскрывающихся списков.

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

Примечание: Первые три действия, указанные в этом разделе, можно использовать для любого типа проверки данных. Шаги 4–8 относятся к созданию раскрывающегося списка. 1. Выделите одну или несколько ячеек, к которым нужно применить проверку. 2. На вкладке Данные в группе Работа с данными нажмите кнопку Проверка данных.

3.

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

4.

В поле Источник введите значения, разделенные точкой с запятой. Например: a. Для ограничения ответа на вопрос (например, "Есть ли у вас дети?") двумя вариантами введите Да;Нет.

b. Для ограничения рейтинга качества производителя тремя позициями введите Низкое;Среднее;Высокое. Примечание: Эти инструкции обычно применимы только в том случае, если элементы списка, скорее всего, не будут изменяться. Если список может измениться или вам нужно добавлять или удалять элементы, следуйте рекомендации. Рекомендация: можно также создать список значений с помощью ссылки на диапазон ячеек в любой части книги. Удобнее всего создать список, а затем отформатировать его как таблицу Excel (на вкладке Главная щелкните Стили→Форматировать как таблицу и выберите нужный стиль таблицы). Затем выберите диапазон данных таблицы, то есть часть таблицы, содержащую список без заголовка (в данном случае — "Отдел"), и присвойте ему имя в поле "Имя".

5.

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

6.

7.

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

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

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

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

Примечания:

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

Бизнес-информатика: дистанционный курс

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

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

Чтобы: Сделайте следующее:

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

3.

4.

Выполнить действия 1–3, описанные в разделе Добавление проверки данных в ячейку или диапазон ячеек. В списке Разрешить выберите значение Целое число. В поле Данные выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Диапазон. Введите минимальное, максимальное или точное значение. Можно ввести формулу, которая возвращает числовое значение. Например, проверку значения в ячейке F1. Чтобы задать минимальный объем вычетов, равный значению этой ячейки, умноженному на 2, выберите пункт Больше или равно в поле Данные и введите формулу =2*F1 в поле Минимальное значение.

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

4. Выполнить действия 1–3, описанные в разделе Добавление проверки данных в ячейку или диапазон ячеек. В поле Разрешить выберите значение Десятичный. В поле Данные выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Диапазон. Введите минимальное, максимальное или точное значение. Можно ввести формулу, которая возвращает числовое значение. Например, для задания максимального значения комиссионных и премиальных в размере 6 % от заработной платы продавца в ячейке E1 выберите пункт Меньше или равно в поле Данные и введите формулу =E1*6% в поле Максимальное значение. Примечание: Чтобы пользователи могли вводить проценты, например "20 %", в поле Разрешить выберите значение Десятичное число, в поле Данные задайте необходимый тип ограничения, введите минимальное, максимальное или определенное значение в виде десятичного числа, например 0,2, а затем отобразите ячейку проверки данных в виде процентного значения, выделив ее и нажав кнопку Процентный формат на вкладке Главная в группе Число.

Бизнес-информатика: дистанционный курс

Чтобы:

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

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

2. 3.

4.

1.

2. 3.

4.

Сделайте следующее:

Выполнить действия 1–3, описанные в разделе Добавление проверки данных в ячейку или диапазон ячеек выше. В поле Разрешить выберите значение Дата. В поле Данные выбрать необходимый тип ограничения. Например, для разрешения даты после определенного дня выберите ограничение Больше. Ввести дату начала, окончания или определенную дату. Можно ввести формулу, которая возвращает дату. Например, чтобы задать интервал времени между текущей датой и датой через 3 дня после текущей, выберите пункт Между в поле Данные, потом введите =СЕГОДНЯ() в поле Дата начала и затем введите =СЕГОДНЯ()+3 в поле Дата завершения.

Выполнить действия 1–3, описанные в разделе

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

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

Бизнес-информатика: дистанционный курс

Чтобы:

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

Сделайте следующее:

1. Выполните действия 1–3, описанные в разделе Добавление проверки данных в ячейку или диапазон ячеек выше. 2. В поле Разрешить выберите значение Длина текста. 3. В поле Данные выберите необходимый тип ограничения. Например, для установки определенного количества знаков выберите ограничение Меньше или равно. 4. В данном случае нам нужно ограничить длину вводимого текста 25 символами, поэтому выберем меньше или равно в поле Данные и введем 25 в поле Максимальное значение.

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

2. 3. Выполните действия 1–3, описанные в разделе Добавление проверки данных в ячейку или диапазон ячеек. В поле Разрешить выберите нужный тип данных. В поле Данные выберите необходимый тип ограничения. В поле или полях, расположенных под полем Данные, выберите ячейку, которую необходимо использовать для определения допустимых значений. Например, чтобы допустить ввод сведений для счета только тогда, когда итог не превышает бюджет в ячейке E1, выберите значение Число десятичных знаков в списке Разрешить, ограничение "Меньше или равно" в списке "Данные", а в поле Максимальное значение введите >=E1.

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

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

Чтобы

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

Значение в ячейке, содержащей код = И(ЛЕВСИМВ(C2;3)="ID-";ДЛСТР(C2)>9) продукта (C2), всегда начинается со стандартного префикса "ID-" и имеет длину не менее 10 (более 9) знаков.

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

Значение в ячейке, содержащей чью- =ЕСЛИ(B6<=(СЕГОДНЯ()-(365*B4));TRUE,FALSE) то дату рождения (B6), было больше числа лет, указанного в ячейке B4.

=ЕЧИСЛО(НАЙТИ("@";B4)

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