Check-moscow.ru

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

Как выделить строки на основе раскрывающегося списка в Excel?

Выпадающий список в EXCEL

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

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

В этой статье мы создадим выпадающий список, используя проверку данных (Data / Data Management / Validation) с типом данных List (Список).

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

Как выделить строки на основе раскрывающегося списка в Excel?

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

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

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

Добавьте новые пункты, перейдя в конец списка.

Чтобы удалить элемент, нажмите кнопку Delete.

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

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

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

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

Если необходимо удалить элемент, нажмите Удалить.

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

В разделе Менеджер имен на вкладке Формулы нажмите кнопку .

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

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

В появившемся диалоговом окне нажмите Да и нажмите Закрыть для сохранения изменений.

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

Название именованного диапазона в поле имени

Откройте лист, содержащий данные в выпадающем списке.

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

Чтобы добавить элемент, перейдите к нижней части списка и введите новый элемент.

Чтобы удалить элемент, нажмите кнопку Delete.

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

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

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

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

Диапазон в качестве источника раскрывающегося списка

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

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

Во вкладке «Данные» нажмите на кнопку «Проверить данные».

На вкладке Параметры щелкните поле Источник и измените нужные элементы списка. Элементы должны быть разделены точкой с запятой без пробелов следующим образом: Да;Нет;Возможно

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

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

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

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

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

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

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

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

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

Для удаления элемента нажмите кнопку Delete.

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

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

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

Для добавления элемента перейдите в конец списка и введите новый элемент.

Чтобы удалить элемент, нажмите кнопку Delete (Удалить).

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

На вкладке Формулы нажмите кнопку Диспетчер имен.

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

Щелкните в поле Диапазон, а затем выберите все ячейки электронной таблицы, которые содержат записи для раскрывающегося списка.

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

Определите именованный диапазон, выделив его и введя его имя в поле Имя. Описание того, как найти именованные диапазоны, см. в разделе Поиск именованных диапазонов.

Название именованного диапазона в поле имени

Откройте лист с данными раскрывающегося списка.

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

В конце списка можно добавить новый элемент.

Для удаления элемента нажмите кнопку Delete.

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

В листе раскрывающегося списка выделите ячейку, в которой содержится список.

Чтобы проверить данные, нажмите Проверка данных.

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

Диапазон в качестве источника раскрывающегося списка

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

В раскрывающемся списке выберите ячейку, содержащую список.

Нажмите кнопку Проверить данные на вкладке Данные.

Щелкните поле Источник на вкладке Параметры и измените нужные элементы списка. Элементы должны быть разделены точкой с запятой, без пробелов между ними, как показано ниже: Да;Нет;Возможно

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

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

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

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

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

Чтобы посмотреть видео о создании и управлении выпадающими списками, щелкните здесь.

В Excel on the Web можно изменить только выпадающий список, в который вручную вводятся исходные данные.

Выделите ячейки, в которых расположен выпадающий список.

Выберите «Данные» > «Проверка данных».

Выберите Источник на вкладке Параметры. Затем выполните одно из следующих действий

Введите новые записи или удалите ненужные, если поле Источник содержит выпадающие списки. После завершения записи должны быть разделены запятыми без пробелов. Например: Фрукты, Овощи, Мясо, Закуски.

Если поле Источник содержит ссылку на диапазон ячеек (например, =$A$2:$A$5), нажмите кнопку Отменить, а затем добавьте или удалите записи из этих ячеек. Здесь вы можете добавить или удалить записи в ячейках A2-A5. В случае если итоговый список записей больше или меньше исходного диапазона, вернитесь на вкладку Параметры и удалите содержимое поля Источник. Затем можно щелкнуть и перетащить мышью, чтобы выбрать новый диапазон, содержащий записи.

Если поле «Источник» содержит именованный диапазон, например, «Отделы», сам диапазон должен быть изменен с помощью классической версии Excel.

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

Работа с «Умной таблицей»

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

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

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

Вызов окна «Настроить панель быстрого доступа»

Выберите «Другие команды…». в этом меню для доступа к окну настроек панели инструментов быстрого доступа:

Окно «Настроить панель быстрого доступа»

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

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

Автоформа для «Умной таблицы»

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

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

Самым простым способом было бы вставить формулу =[предыдущая ячейка]+1, но она не будет автоматически скопирована в новую запись. В ячейках с этой формулой Excel напечатает примечание: «Несовместимая формула в номинальной колонке».

В нашем примере подойдет следующая формула: =STROCK([@Name])-1 , которая вычисляет номер текущей строки в таблице Excel и уменьшает его на единицу, поскольку первая запись в нашей «Умной таблице» начинается со второй строки в таблице Excel.

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

Читайте так же:
Как вывести список всех открытых книг и легко переключаться между ними в Excel?

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

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

Примеры для редактирования выпадающего списка в Excel

Следующие примеры помогут вам отредактировать выпадающий список в Excel

Вы можете скачать этот Как редактировать шаблон Excel из выпадающего списка здесь — Как изменить шаблон Excel из выпадающего списка

Пример № 1 — Редактирование списка через запятую

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

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

Настройки обозначены как на рисунке ниже:

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

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

  • На ленте Excel выберите вкладку «Данные» > «Проверка данных».

  • Открывается окно управления данными.

  • Добавление или удаление значений списка под источником. Вы можете удалить их все и задать новый набор значений. В этом случае замените значение списка «Возможно» на «Не уверен» и нажмите «OK». Это сохраняет изменения в списке значений и закрывает окно проверки данных.

  • Обновленные изменения можно увидеть в списке под изображением.

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

Пример №2 — Редактирование выпадающего списка именованного диапазона

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

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

Следуйте инструкциям ниже:

  • Перейдите на рабочий лист, содержащий именованные диапазоны. Обновление данных в диапазонах (удаление некоторых записей или добавление записей вместо предыдущих).

  • Перейдите на вкладку Формулы > Работа с именами на ленте Excel (или нажмите одновременно Ctrl + F3).

  • Откроется окно Менеджер имен. В окне Диспетчер имен выберите диапазон имен, который необходимо обновить. Отредактируйте ссылку в поле Relates to, выбрав все записи в выпадающем списке с помощью значка свертывания в диалоговом окне. После завершения необходимых обновлений вы можете закрыть Менеджер имен, нажав кнопку Закрыть.
Читайте так же:
Как вставить случайные (целые) числа между двумя числами без повторов в Excel?

  • Вы можете видеть, что выпадающий список, связанный с именованной областью (столбец Имя сотрудника), был обновлен (Кофи был заменен на Лалит, а Шарлет на Сухану соответственно).

Пример № 3 — Редактирование выпадающего списка на основе диапазона таблицы

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

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

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

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

См. пример ниже:

В колонке A в качестве исходных данных указаны имена сотрудников.

Вот выпадающий список, который мы создали, когда предоставили таблицу «Имена сотрудников» в качестве источника. Когда вы добавляете запись в конец таблицы (например, Mohan), выпадающий список автоматически обновляется, и вам не нужно обновлять его вручную (как вы это делали с разделителями-запятыми и именованным диапазоном). Выпадающий список, созданный на основе таблицы как источника, обеспечивает это преимущество.

См. заключение ниже:

Это было в этой статье.

Что нужно помнить о выпадающем списке в Excel

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

Рекомендуемые статьи

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

Как выделить строки на основе раскрывающегося списка в Excel?

раскрывающиеся списки в Microsoft Excel

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

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

1. Создание таблицы данных и списка параметров

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

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

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

01-создание данных таблицы -Лист2-Сохранить

Каждый параметр должен быть введен в отдельную колонку (или строку). После того как вы внесли данные в рабочий лист, вернитесь к нему.

02-создание списка-опции-Сохранить

2. Включение проверки данных для выбранных ячеек

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

Читайте так же:
Как выделить строки на основе нескольких значений ячеек в Excel?

Затем перейдите на вкладку Data (Данные).

03-выберите клетки и нажмите сведения вкладки-Сохранить

Нажмите «Проверка данных» в разделе «Инструменты данных. «.

04-нажмите кнопку проверка данные в инструментах-Сохранить

3. Добавление раскрывающегося списка в выбранные ячейки

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

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

05-выберите список в разрешениях на вкладке настройки-Сохранить

4. Выбор источника для раскрывающихся списков

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

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

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

06-нажмите кнопку выбор источника-Сохранить

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

07-клик-Лист2 - проверка данных-Сохранить

Затем выделите ячейки, содержащие параметры. Имя рабочего листа и диапазон ячеек с параметрами добавляются в поле Source диалогового окна Validate Data.

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

08-выбрать список-данные-проверка-Сохранить

Добавление входного сообщения

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

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

09-Добавить-Ввод-Сообщение-Сохранить

Добавление предупреждения об ошибке

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

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

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

10-Добавить-Ошибка-Предупреждение-Сохранить

Использование раскрывающегося списка

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

11-выпадающий список в ячейке-Сохранить

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

12-входной сигнал-сообщение-отобразить-Сохранить

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

13-ошибка-предупреждение-отображение-Сохранить

Как вы использовали выпадающие списки в Excel? Сообщите нам об этом в комментариях. См. также другие советы по Microsoft Office и мое руководство по созданию выпадающих списков в Google Spreadsheets.

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