0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как добавить пробел в качестве первого варианта в список проверки данных Excel?

Как сделать выпадающий список в Excel

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

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

Сегодня мы научимся создавать раскрывающиеся списки в Excel

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

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

1. Выпадающий список состоит только из элементов, которые вы уже ввели

2. Если вам нужно ввести значения не в следующую ячейку, а, например, в одну ячейку (то есть одна ячейка остается пустой), то у вас ничего не получится.

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

spisok-dannye

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

Excel 2007/2010 dataviewer (см. рисунок ниже): Работа с данными.

spisok-panel

Чтобы проверить данные в Excel 2003, нужно щелкнуть Данные > Проверка (см. рисунок ниже).

spisok-panel_2003

Откроется окно проверки билетов. Выберите Список из выпадающего списка,

spisok-proverka

Затем у вас есть возможность ввести источник списка, где вы должны указать исходный список значений. В нашем случае это синяя область G5:G11, заключенная в доллары, так как у нас фиксированная область и она не должна двигаться. После нажатия кнопки OK появится выпадающий список, из которого, как и в формуле, можно выбрать другие ячейки.

spisok-istochnik

Чтобы проделать то же самое с городами, теперь укажем источник в виде именованного диапазона. Выберите исходный диапазон H5:H9, а затем в поле имени (см. рисунок) введите имя: города (имя должно начинаться с буквы и не содержать пробелов), после чего обязательно нажмите Enter.

spisok-istochnik2

Позже вы можете использовать диспетчер имен для редактирования именованного диапазона. Его можно найти в разделе Формулы | Определенные имена | Менеджер имен

Читайте так же:
Как автоматически удалить дубликаты из списка в Excel?

dispetcher-imen

Для этого перейдите в желтую ячейку C4, выберите Данные | Работа с данными | Проверка данных, выберите список из выпадающего списка и укажите =city в качестве источника. После нажатия кнопки OK вы получите выпадающий список с вариантом использования именованного диапазона.

Динамические выпадающие списки Excel

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

Существует два варианта динамических выпадающих списков:

  • Увеличивается;
  • Зависимость.

Растущий выпадающий список

Если вы добавляете элементы в список без изменения источника, говорят, что список растет.

Посмотрите этот пример создания такого списка.

На каждом листе имеется список будущих элементов. Затем правило проверки данных настраивается следующим образом: «Вкладка «Данные» -> область «Работа с данными» -> кнопка «Проверить данные» -> тип данных «Список». Для источника должна быть создана следующая формула:

=Ассоциация($A$1;;;Счет($A:$A)), где:

  • $A$1 — ячейка со значением первого элемента в списке;
  • $A:$A — столбец со списком всех элементов в списке.

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

Параметры динамических выпадающих списков:

Параметры ростущего выпадающего списка

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

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

Подумайте о создании выпадающих списков продуктов с использованием зависимых выпадающих списков. Сначала определим категории доступных продуктов (это будет основной список):

  • Бытовое_оборудование (пробел в названии намеренно заменен знаком подчеркивания, так как менеджер имен, который будет использоваться ниже, не принимает символ пробела);
  • Электроника;
  • Мебель.

Значения каждой категории теперь создаются в Менеджере имен (вкладка «Формулы» -> «Определенные имена»), а имена, связанные с ней, назначаются в соответствии с этими значениями.

Присвоенные имена для связанных выпадающих списков excel

В качестве последнего шага мы применили связанные выпадающие списки к ячейкам с помощью проверки данных (вкладка «Данные» -> «Работа с данными»).

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

=ДВССИЛ($A$1), где :

  • $A$1 — основная ячейка списка.

Параметры зависимых списков в excel

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

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

  • < Назад
  • Вперёд >
  • Проверка данных в программе Excel
  • Критерий Манна-Уитни
  • Соединение MySQL с Excel
  • Соединение Excel с SQL-сервером
Читайте так же:
Как автоматически пронумеровать столбец в Excel?

Если вы нашли материалы с сайта office-menu.ru полезными, пожалуйста, поддержите проект, чтобы я мог развивать его дальше.

Комментарии

Добрый день, Александр Владимирович.

Это можно сделать с помощью макросов. Других способов я не знаю.

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

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

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 символов, например, вы не сможете ввести =CORN(2) в ячейку, так как результат =1.4142135623731 (в зависимости от точности, которую вы установили в EXCEL). Однако если вы введете =CORN(4), вы сможете это сделать, так как результат =2, что составляет всего один символ.
  • Список. Вероятно, самый интересный тип данных. В этом случае ввод значений в ячейку может быть ограничен ранее определенным списком. Например, если вы укажете в качестве источника единицы измерения товаров, разделенные точкой с запятой: шт;кг;м кв;м куб, вы не сможете выбрать из списка ничего, кроме этих 4 значений. В источнике можно указать диапазон ячеек, содержащий предварительно сгенерированный список или ссылку на именованную формулу. Пример см. в статье Выпадающий список
  • Другое . В ячейке допускаются значения, соответствующие более сложным критериям. Для задания критериев необходимо использовать формулу. Давайте рассмотрим это условие более подробно.
Читайте так же:
Как автоматически обновить сводную таблицу в Excel?

Если выбран тип Другой, то для расчета логического значения необходимо ввести формулу в поле Формула. Если это значение TRUE, вы можете ввести это значение в ячейку, а если это FALSE, вы не можете ввести значение. Используйте в качестве аргумента формулы ссылку на саму ячейку, на которую влияет валидация, или ссылку на ячейку, которая зависит от нее. Например,

Ввод формулы

Объяснение.

В ячейке B2 содержится только текст

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

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

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

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

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

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

Выберите ячейки A1:A20, вызовите инструмент проверки данных и введите формулу. Вторую формулу можно использовать для всего столбца A, выбрав весь столбец A вместо диапазона

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

Введите данные контроля для ячейки B5

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

Читайте так же:
Как автоматически вставить строку на основе значения ячейки в Excel?

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

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

Вы можете отобразить комментарий на вкладке Output Message (Вывод сообщения).

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

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

После ввода ошибочного значения в Data Checker можно выводить сообщения об ошибках. Msgbox() из VBA аналогичен этому.

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

Связные диапазоны могут быть созданы, если мы выберем Список в качестве типа данных на вкладке Параметры. Поскольку эта тема настолько обширна, что она выделена в отдельную статью, Linked List.

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

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

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

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

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

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

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

Читайте так же:
Как в Excel создать диаграмму с датой и временем по оси X?

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

Нажмите CTRL+D на ячейке A2. Значения из A1 и условие будут скопированы в A2. Хотя условие проверки данных теперь установлено на FALSE, предупреждающее сообщение не появится. Чтобы определить, соответствуют ли данные в ячейках параметрам, заданным в программе проверки данных, щелкните пункт меню Заполнить неверные данные (Данные / Работа с данными / Проверка данных / Заполнить неверные данные). В таблице ячейки с неверными данными будут обведены красными овалами. Снова выберите A2 и нажмите F2 (войти в режим редактирования), затем нажмите ENTER — появится окно с сообщением «Invalid value entered. «.

Есть и другой способ избежать проверки данных. Предположим, что запись в ячейке ограничена значениями от 1 до 3. Теперь введите значение 4 в любую другую ячейку без проверки данных. Теперь выделите эту ячейку и с помощью панели формул выделите значение 4 и скопируйте его в буфер обмена. Теперь выделите ячейку проверки фактов и нажмите CTRL+V. Значение было вставлено в ячейку. Кроме того, проверка фактов остается нетронутой, в отличие от вставки значения WORD, например, в буфер обмена. Чтобы убедиться, что данные в ячейке не соответствуют условиям, определенным в Контроллере данных, вызовите команду меню Fill Invalid Data (Data / Data Handling / Data Controller / Fill Invalid Data).

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

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

Опция Data Validation этого инструмента позволяет выбрать ячейки, для которых выполняется проверка данных (задается командой Data/Working with Data/Data Validation). Если выбран параметр Все, все эти ячейки будут выделены. При выборе «Эти» также выбираются только те ячейки, для которых определены те же правила проверки данных, что и для активной ячейки.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector