Содержание
- Что такое выпадающий список
- Создание выпадающего списка
- Связанные выпадающие списки
- Способ 1. Связанные выпадающие списки из таблицы с группами в заголовках столбцов
- Справка:
Переходим во вкладку «Данные» программы Microsoft Excel. Выделяем область таблицы, где собираемся применять выпадающий список. Жмем на кнопку «Проверка данных», расположенную на Ленте.
Что такое выпадающий список
Выпадающий список в Excel (или раскрывающийся список) — это список в ячейке Excel, из которого можно выбрать одно из нескольких заранее заданных значений. Это удобно для быстрого и правильного заполнения данных: не вбивать руками, а просто выбрать. Тут же можно настроить контроль, чтобы пользователи не могли вносить значения, не предусмотренные в списке.
Выпадающий список работает так: выбираете ячейку и справа от нее появляется кнопка со стрелкой вниз (правда, стрелка больше похожа на треугольник).
После нажатия на кнопку списка появится перечень доступных значений, одно из которых можно выбрать.
Значений в списке может быть много, но в обзор помещается до 8 строк. Если значений в списке больше восьми, справа от них появится полоса прокрутки.
Создание выпадающего списка
Чтобы создать выпадающий список, выделите ячейку, где он должен появиться (или группу ячеек) и перейдите на вкладку Данные -> Проверка данных.
В появившемся окне укажите тип данных – Список, поставьте галочку рядом со строкой «Список допустимых значений».
Источником данных может быть:
- Текст – пишется через точку с запятой «;» и без знака равно «=», например
Материалы;Заработная плата;Амортизация
- Ссылки на ячейки:
=$A$1:$A$7
- Именованный диапазон:
=ИмяДиапазона
Связанные выпадающие списки
Связанные выпадающие списки – это списки, в которых выпадающие значения появляются не «просто так», а в зависимости от уже заполненных данных. Так, для выбранной группы появится только список входящих в неё наименований.
Создадим выпадающие списки несколькими способами – для разных таблиц с исходными данными.
Способ 1. Названия групп в заголовках столбцов, в строках – элементы групп.
Способ 2. Названия групп – в первом столбце, элементы групп – во втором столбце.
Способ 1. Связанные выпадающие списки из таблицы с группами в заголовках столбцов
Исходные данные: таблица с названиями групп в заголовках столбцов.
В этом способе используется всего одна простая формула — ДВССЫЛ (правда, непривычная обычным пользователям) и форматированная smart-таблица Excel (иногда их еще называют «умные» таблицы). Зато вы получите взаимозависимые списки и будете пользоваться ими по принципу «сделал и забыл». Не нужно будет переживать о том, что «слетит» диапазон и переделывать всю архитектуру данных. Просто один раз сделаете и будете пользоваться.
Справка:
Форматированная («умная») таблица Excel
Форматированная таблица – это таблица, у которой есть свое имя, свойства и структура. Такая таблица представляет из себя именованный «саморасширяющийся» диапазон. При добавлении в нее новых данных границы таблицы автоматически «захватят» новое значение.
У форматированной таблицы множество преимуществ по сравнению с обычной. Поэтому на курсах и семинарах я советую использовать такие таблицы везде, где есть такая возможность.
Создать форматированную таблицу просто: выделите диапазон ячеек и перейдите в меню Главная -> Форматировать как таблицу -> выберите понравившийся вид таблицы. Готово – форматированная таблица создана.
Формула ДВССЫЛ
Формула ДВССЫЛ передает значения из ячейки, адрес которой записан в самой формуле в виде текстовой строки.
Например, записываем в ячейке B1 адрес ячейки А1. Формула ДВССЫЛ( B1 ) «увидит», какой адрес записан в ячейке B1, а результатом вычисления формулы будет текст, записанный в ячейке А1. Эту же формулу можно записать, указав адрес ячейки в кавычках – ДВССЫЛ( « А1 » ).
С помощью ДВССЫЛ можно обратиться к ячейке по адресу с помощью других формул, например СЦЕПИТЬ, & или ЕСЛИ и т.д. Так, формула на рисунке ДВССЫЛ( B1 & C1 ) обращается к тексту в ячейке А1. После нажатия Enter в ячейке, где вводилась формула ДВССЫЛ( B1 & C1 ) появится значение из ячейки A1, в нашем случае это «текст».
Пошаговая инструкция по созданию связанных выпадающих списков
Шаг 1. Создайте справочник исходных данных в виде форматированной smart-таблицы.
- Выделите таблицу со статьями и преобразуйте ее в smart-таблицу: выберите в меню Главная -> Форматировать как таблицу.
- В появившемся окне обязательно проверьте галочку рядом с надписью «Таблица с заголовками». Если ее нет – поставьте.
- Присвойте созданной таблице имя: выделите любую ячейку таблицы, перейдите на вкладку Конструктор, введите имя — «Источник».
Таблица «Источник» создана. Теперь можно обращаться к таблице и её элементам по имени. Например, название заголовка таблицы будет выглядеть так: = Источник[#Заголовки]
Столбец таблицы: = Источник[Материалы]
Чтобы появилась такая формула, нажмите равно = и выделите столбец, его имя появится в строке формул.
Шаг 2. Создайте выпадающий список с группами.
- Выделите ячейки в столбце «группа».
- Перейдите в меню Данные -> Проверка данных.
- В появившемся окне выберите тип данных — Список, а в строке Источник введите формулу =ДВССЫЛ(«Источник[#Заголовки]»)
Готово! В столбце «группа» появился выпадающий список.
Шаг 3. Создайте выпадающий список со статьями.
- Выделите столбец «статья» в таблице.
- Перейдите в меню Данные -> Проверка данных.
- В появившемся окне выберите тип данных — Список, а в строке Источник введите формулу: =ДВССЫЛ(«Источник[«&$G3&»]»)
В формуле $G3 – это первая ячейка из столбца «группа». Ссылка на столбец «зафиксирована» с помощью знака доллара $, а строка может изменяться.
Готово! В столбце «статья» появляется только список статей, входящих в группу.
Теперь в форматированную smart-таблицу можно добавлять новые группы и статьи. Добавим, например, новый столбец «Прочее», и такая группа сразу же появится в выпадающем списке.
Способ 2. Связанные выпадающие списки из таблицы с группами в первом столбце и элементами — во втором
Исходные данные: таблица с названиями групп в первом столбце, элементами групп – во втором столбце.
На самом деле в сети можно найти несколько вариантов реализации этого способа. Но у них у всех есть один недостаток: такой список нужно «администрировать». Потому что таблица должна быть всегда отсортирована по названиям групп – нельзя, чтобы группы располагались произвольно. Если группы будут идти «как попало», то формула, с помощью которой это всё сделано (СМЕЩ) не сработает, и список будет создаваться с ошибкой. Т.е. пользователю нужно все время сортировать первый столбец или добавлять туда данные в алфавитном порядке. А еще потребуется записывать где-то отдельно сами названия групп и это тоже нужно будет делать «вручную».
Мы придумали, как обойти эту проблему: с помощью сводных таблиц , которые будут за нас упорядочивать данные. Благодаря этому постоянное «администрирование» будет заменено на простую процедуру «обновить».
Для создания списков используем форматированные (умные) таблицы, сводные таблицы, формулы СМЕЩ + ПОИСКПОЗ + СЧЁТЗ, СЧЁТЕСЛИ и диспетчер имен.
Справка:
Формула СМЕЩ
СМЕЩ выдает ссылку на диапазон ячеек, находящийся в указанном количестве ячеек от исходной. Ссылка определяется с учетом заданного в формуле размера диапазона – числа строк и столбцов. Другими словами, этой формулой вы можете «сказать» Excel-ю на сколько ячеек он должен отступить и какой диапазон «захватить».
Синтаксис формулы СМЕЩ такой:
СМЕЩ(ссылка ; смещ_по_строкам ; смещ_по_столбцам ; [высота] ; [ширина] ), где
- ссылка – ссылка, от которой вычисляется смещение, может быть адресом ячейки или группы ячеек;
- смещ_по_строкам – количество строк, которые требуется отсчитать вверх или вниз от начальной ссылки;
- смещ_по_столбцам – количество столбцов, которые требуется отсчитать влево или вправо от начальной ссылки;
- [высота] – число строк возвращаемой ссылки (необязательный);
- [ширина] – число столбцов возвращаемой ссылки (необязательный).
Формула ПОИСКПОЗ
Ищет нужный нам элемент в диапазоне ячеек и выдает его порядковый номер в диапазоне.
Синтаксис ПОИСКПОЗ такой:
ПОИСКПОЗ( искомое_значение ; просматриваемый_массив ; [тип_сопоставления] )
- искомое_значение – значение, которое ищем. Может быть числом, текстом, логическим значением или ссылкой на ячейку;
- просматриваемый_массив – диапазон ячеек, где будем искать нужное значение;
- [тип_сопоставления] — число -1, 0 или 1, которое показывает, как сравнивать искомое значение с ячейками просматриваемого массива. Не переживайте, если не поняли, когда и что ставить, потому что 90% случаев нужно выбирать ноль.
Подробнее про эту формулу можно посмотреть в видеоинструкции: Какая формула лучше ВПР и работает с несколькими критериями
Формула СЧЁТЗ
СЧЁТЗ просто считает количество непустых ячеек в диапазоне.
Формула СЧЁТЕСЛИ
Почти тот же СУММЕСЛИ, только проще – подсчитывает количество значений, соответствующих определенному условию.
Пошаговая инструкция по созданию списков
Шаг 1. Преобразуйте исходные данные в форматированную smart-таблицу.
- Выделите таблицу со статьями и преобразуйте ее в smart-таблицу: перейдите в меню Главная -> Форматировать как таблицу.
- В появившемся окне обязательно проверьте галочку рядом с надписью «Таблица с заголовками». Если ее нет – поставьте.
- Присвойте таблице имя: на вкладке Конструктор введите имя таблицы — «статьи».
Форматированная таблица «статьи» создана.
Шаг 2. Создайте две сводные таблицы – одну с названиями групп, вторую — со статьями.
Для чего используем сводные таблицы? Во-первых, чтобы вручную не создавать перечень групп, а во-вторых, как уже упоминали выше, чтобы вручную не сортировать справочники статей (что иногда забывают сделать пользователи, а это важно, иначе формула СМЕЩ «срабатывает» с ошибкой). «Ручную» работу сделает вместо нас кнопка «Обновить» в меню Данные — нажимаем ее каждый раз после появления новых статей.
- Создайте первую сводную таблицу с группами статей.
Выделите любую ячейку таблицы с исходными данными, перейдите в меню Вставка -> Сводная таблица. Добавьте сводную таблицу на существующий лист и поместите группы в область строк.
- Создайте вторую сводную таблицу со статьями: меню Вставка -> Сводная таблица. В область строк поместите группы и статьи.
- Форматируем сводную таблицу со статьями и придаем ей вид справочника.
Выделите любую ячейку таблицы, перейдите на вкладку Конструктор -> Макет отчета -> Показать в табличной форме. У нас получится почти та таблица, которая нам нужна, но в ней автоматом появятся промежуточные суммы. Чтобы их отключить, идем: Промежуточные итоги -> Не показывать промежуточные суммы.
- Скройте строку «Общий итог» в обеих таблицах справочников. Перейдите на вкладку Конструктор -> Общие итоги -> Отключить для строк и столбцов.
В итоге получатся два справочника, как на рисунке ниже. Для удобства разместите таблицы рядом на одном листе – с первой строки и в столбцах A, C и D, как на рисунке (это поможет разобраться с формулой СМЕЩ).
Шаг 3. Создайте именованные диапазоны с помощью диспетчера имен.
- Откройте диспетчер имен: в меню Формулы -> Диспетчер имен.
- В появившемся окне нажмите кнопку «Создать».
- Введите имя «ГруппыСписок» и формулу, которая будет определять диапазон:
=СМЕЩ($A$1;1;0;СЧЁТЗ($A:$A)-1;1)
Пояснения к формуле:
СМЕЩ ( $A$1 ; 1 ; 0 ; СЧЁТЗ( $A:$A ) – 1 ; 1 ) – определяет адрес ячеек с названиями групп.
- $A$1 – это первая ячейка в справочнике групп.
- Следующие цифры 1 ; 0 – это отступ от первой ячейки на 1 строку и 0 столбцов (отступ нужен, потому что в первой ячейке название столбца).
- СЧЁТЗ( $A:$A ) – 1 Считаем число непустых ячеек в столбце А. Вычитаем -1, потому что название столбца не должно быть в списке.
- Последнее число 1 в формуле – это количество столбцов.
Нажмите ОК. Названия листов в формуле появятся сами.
- Точно так же создайте в диспетчере имен список статей.
Введите имя ГруппыСтатей, а для диапазона – формулу:
=СМЕЩ($C$1;ПОИСКПОЗ($G2;$C:$C;0)-1;1;СЧЁТЕСЛИ($C:$C;$G2);1)
Пояснения к формуле:
СМЕЩ ( $C$1 ; ПОИСКПОЗ ( $G2 ; $C:$C ; 0 ) – 1 ; 1 ; СЧЁТЕСЛИ( $C:$C ; $G2 ) ; 1 ) – определяет адрес ячеек с названиями статей из группы с помощью ПОИСКПОЗ, которая ищет группы статей.
- $С$1 – это первая ячейка в столбце с группами.
- ПОИСКПОЗ ( $G2 ; $C:$C ; 0 ) – 1 Определяет, на сколько строк нужно отступить от первой ячейки. ПОИСКПОЗ ищет название группы, выбранной в таблице с данными (столбец $G) среди ячеек справочника (столбец $C). В адресе ячейки $G2 не «закрепляем» номер строки с помощью знака $, чтобы формула работала для каждой ячейки в столбце.
- Следующая цифра 1 – это отступ на 1 столбец вправо, т.е. переходим к столбцу «статьи», откуда нужно брать данные.
- СЧЁТЕСЛИ( $C:$C ; $G2 ) – считаем число ячеек в столбце $C, в которых названия групп такие же, как в столбце с данными. Здесь тоже не «закрепляем» номер строки у ячейки $G2 с помощью знака $.
- Последнее число 1 в формуле – это количество столбцов.
Шаг 4. Создайте выпадающие списки.
Выделите ячейки в столбце «группы», перейдите в меню Данные -> Проверка данных. Задайте тип данных Список, источник =ГруппыСписок.
То же самое – для статей. Тип данных – список, источник =ГруппыСтатьи
Выпадающие списки готовы. Форматированные smart-таблицы позволят «захватить» все данные, а сводные таблицы – избежать ошибок, отсортировать справочник и создать список групп.
Microsoft Excel: выпадающие списки
При работе в программе Microsoft Excel в таблицах с повторяющимися данными, очень удобно использовать выпадающий список. С его помощью можно просто выбирать нужные параметры из сформированного меню. Давайте выясним, как сделать раскрывающийся список различными способами.
Выпадающий список в Excel с поиском
Создание дополнительного списка
Самым удобным, и одновременно наиболее функциональным способом создания выпадающего списка, является метод, основанный на построении отдельного списка данных.
Прежде всего, делаем таблицу-заготовку, где собираемся использовать выпадающее меню, а также делаем отдельным списком данные, которые в будущем включим в это меню. Эти данные можно размещать как на этом же листе документа, так и на другом, если вы не хотите, чтобы обе таблице располагались визуально вместе.
Выделяем данные, которые планируем занести в раскрывающийся список. Кликаем правой кнопкой мыши, и в контекстном меню выбираем пункт «Присвоить имя…».
Открывается форма создания имени. В поле «Имя» вписываем любое удобное наименование, по которому будем узнавать данный список. Но, это наименование должно начинаться обязательно с буквы. Можно также вписать примечание, но это не обязательно. Жмем на кнопку «OK».
Переходим во вкладку «Данные» программы Microsoft Excel. Выделяем область таблицы, где собираемся применять выпадающий список. Жмем на кнопку «Проверка данных», расположенную на Ленте.
Как создать выпадающий список в excel. Самый простой способ
Открывается окно проверки вводимых значений. Во вкладке «Параметры» в поле «Тип данных» выбираем параметр «Список». В поле «Источник» ставим знак равно, и сразу без пробелов пишем имя списка, которое присвоили ему выше. Жмем на кнопку «OK».
Выпадающий список готов. Теперь, при нажатии на кнопку у каждой ячейки указанного диапазона будет появляться список параметров, среди которых можно выбрать любой для добавления в ячейку.
Создание выпадающего списка с помощью инструментов разработчика
Второй способ предполагает создание выпадающего списка с помощью инструментов разработчика, а именно с использованием ActiveX. По умолчанию, функции инструментов разработчика отсутствуют, поэтому нам, прежде всего, нужно будет их включить. Для этого, переходим во вкладку «Файл» программы Excel, а затем кликаем по надписи «Параметры».
В открывшемся окне переходим в подраздел «Настройка ленты», и ставим флажок напротив значения «Разработчик». Жмем на кнопку «OK».
После этого, на ленте появляется вкладка с названием «Разработчик», куда мы и перемещаемся. Чертим в Microsoft Excel список, который должен стать выпадающим меню. Затем, кликаем на Ленте на значок «Вставить», и среди появившихся элементов в группе «Элемент ActiveX» выбираем «Поле со списком».
Кликаем по месту, где должна быть ячейка со списком. Как видите, форма списка появилась.
Затем мы перемещаемся в «Режим конструктора». Жмем на кнопку «Свойства элемента управления».
Открывается окно свойств элемента управления. В графе «ListFillRange» вручную через двоеточие прописываем диапазон ячеек таблицы, данные которой будут формировать пункты выпадающего списка.
Далее, кликаем по ячейке, и в контекстном меню последовательно переходим по пунктам «Объект ComboBox» и «Edit».
Выпадающий список в Microsoft Excel готов.
Чтобы сделать и другие ячейки с выпадающим списком, просто становимся на нижний правый край готовой ячейки, нажимаем кнопку мыши, и протягиваем вниз.
Связанные списки
Также, в программе Excel можно создавать связанные выпадающие списки. Это такие списки, когда при выборе одного значения из списка, в другой графе предлагается выбрать соответствующие ему параметры. Например, при выборе в списке продуктов картофеля, предлагается выбрать как меры измерения килограммы и граммы, а при выборе масла растительного – литры и миллилитры.
Прежде всего, подготовим таблицу, где будут располагаться выпадающие списки, и отдельно сделаем списки с наименованием продуктов и мер измерения.
Присваиваем каждому из списков именованный диапазон, как это мы уже делали ранее с обычными выпадающими списками.
В первой ячейке создаём список точно таким же образом, как делали это ранее, через проверку данных.
Во второй ячейке тоже запускаем окно проверки данных, но в графе «Источник» вводим функцию «=ДВССЫЛ» и адрес первой ячейки. Например, =ДВССЫЛ($B3).
Как видим, список создан.
Теперь, чтобы и нижние ячейки приобрели те же свойства, как и в предыдущий раз, выделяем верхние ячейки, и при нажатой клавише мышки «протаскиваем» вниз.
Всё, таблица создана.
Мы разобрались, как сделать выпадающий список в Экселе. В программе можно создавать, как простые выпадающие списки, так и зависимые. При этом, можно использовать различные методы создания. Выбор зависит от конкретного предназначения списка, целей его создания, области применения, и т.д.
Удаление выпадающего списка в Microsoft Excel
Для удаления выпадающего списка в Excel можно обойтись без перехода в соответствующее меню, где осуществляется управление подобными элементами. Вызов подходящего для очистки инструмента выполняется и через контекстное меню, что чаще занимает гораздо меньше времени, нежели стандартный принцип удаления.
- Сначала отыщите в таблице выпадающий список, выделите его, а затем сделайте щелчок правой кнопкой мыши.
- В контекстном меню найдите пункт «Удалить» и активируйте его.
- Появится стандартное окно удаления ячеек, где укажите один из первых вариантов, отметив его маркером.
Если вдруг оказалось, что был удален не тот список или это действие необходимо отменить по другим причинам, сразу же нажмите стандартное сочетание клавиш Ctrl + Z и список снова появится в той же ячейке в своем предыдущем состоянии.
Способ 2: Кнопка «Очистить все» в меню управления списками
Больше методов, не связанных с окном управления списками в Excel и позволяющих удалить выпадающий перечень, нет, поэтому переместимся к меню «Данные» и разберем первую кнопку под названием «Очистить все».
- Выделите выпадающий список и откройте вкладку «Данные».
Способ 3: Изменение типа данных ячейки
Осталось разобрать последний способ, выполняющийся через то же окно «Проверка вводимых значений». Он подразумевает изменение типа данных ячейки без предварительной очистки содержимого. Этот процесс осуществляется самостоятельно, поскольку источник данных в данном случае просто отсутствует.
-
Нажмите по кнопке «Проверка данных» для перехода в то же меню.
Похожие статьи
-
Зависимый выпадающий список позволяет сделать трюк, который очень часто хвалят пользователи шаблонов Excel. Трюк, который делает работу проще и быстрее….
-
Хотите выбирать наименование товара из выпадающего списка и получать в соседней ячейке фотографию выбранного продукта? Или выбирать человека из списка и…
-
Выпадающий список в ячейке эксель
Этот способ представляет собой вставку на лист нового объекта — элемента управления "поле со списком" с последующей привязкой его к диапазонам на листе….