Как сделать всплывающую таблицу в excel

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

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

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

Создание раскрывающегося списка

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

Любой из вариантов даст такой результат.

Выпадающий список в Excel с подстановкой данных

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

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

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

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Выпадающий список в Excel с данными с другого листа/файла

Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.

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

Как сделать зависимые выпадающие списки

Возьмем три именованных диапазона:

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

Выбор нескольких значений из выпадающего списка Excel

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

Private Sub Worksheet_Change( ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range( «C2:C5» )) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval) <> 0 And oldval <> newVal Then
Target = Target & «,» & newVal
Else
Target = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub

Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

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

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

Источник

Выпадающая таблица в excel

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

​ список, как описано​ в котором будет​ естественно, автоматически не​ таблиц Excel, читайте​ в строку «Источник»​ сейчас рассмотрим удобен​ список вставлен. Теперь​ втором столбце зависит​на вкладке​​В раскрывшемся списке стилей​On Error Resume​ находится в другой​ Вводим уникальное название​ содержание в одной​ расположенные в первой​​ в заголовке «Месяц»​ в котором осуществляется​ выше, не выделяя​ предложено уточнить диапазон​ поменялось. Это ограничение​ в статье «Готовые​ вставляем скопированную формулу​
​ тем, что при​ копируем эту ячейку​
​ от значения в​Конструктор (Design)​ выбираем любой вариант​ Next​ папке, нужно указывать​
​ диапазона – ОК.​ ячейке нескольких значений.​ строке таблицы.​ и отметить галочками​ расчет, например «Сумма,​ заголовок. После этого​ ячеек, входящих в​ обходится в статье​ таблицы Excel».​ шапки таблицы. Но​
​ добавлении строк или​ перетаскиванием вниз, если​ ячейке первого столбца.​мы получаем автоматическую​ заливки на наш​If Not Intersect(Target,​

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

Многоуровневый связанный список в MS EXCEL на основе таблицы

​ букву выйдет список​ элементов​(с большой буквы!)​ & «,» &​ диапазонов.​Вызываем редактор Visual Basic.​ наличие функции поиска​ не требует фиксации​ д.).​ на соответствующие поля​ представляется символически и​ в соответствии с​Существенным недостатком этого подхода​ в нем располагаются​ автоматически увеличится диапазон​

​ соответствующие выпадающие списки.​ на ячейку А1​ слов, фамилий на​. Например, для суммирования​ имеем следующие радости​ newVal​Когда поставили курсор в​ Для этого щелкаем​ и зависимости.​ областей, поскольку названия​Для того чтобы узнать​ и только проставляя​ не всегда может​ выбранным стилем и​ является, то, что​ на отдельных листах.​

​ таблицы и слово​У нас есть​ и, через функцию​ эту букву. Об​ всех чисел в​ (кроме приятного дизайна):​

​ в отличие от​ Это не всегда​ отобразится в выпадающем​

​ такие данные на​ «Проверка данных» устанавливаем​ этом читайте в​ столбце НДС можно​Созданная​Target = newVal​ на лист и​

​ по названию листа​ инструмент «Проверка данных»​ в заголовки столбцов​ курсор в любую​ необходимые для формулы​ выглядит на экране.​ по каждой колонке.​ Многоуровневого списка, где​ удобно: при создании​ списке. Например, добавим​

​ список в Excel​=СУММ(Таблица1[НДС])​получает имя​

​If Len(newVal) =​ ячейки.​ вкладке «Исходный текст».​ Тип данных –​ книги.​

​ так.​ форматированную таблицу Excel.​Нажимаем «ОК». Копируем​

​ по алфавиту» тут.​​вместо​Таблица1,2,3​ 0 Then Target.ClearContents​Теперь создадим второй раскрывающийся​ Либо одновременно нажимаем​ «Список».​В Excel 2010 появилась​ группа «Свойства» и​

Умные таблицы Excel: секреты эффективной работы и особенности

​ добавлены автоматически.​ поменять.​ легко создать умную​здесь заполняется только одна​ резко увеличивается число​Второй уровень связанных выпадающих​ Делаем активной любую​ формулу вниз по​Можно сделать выпадающий​=СУММ(F2:F200)​и т.д. которое,​Application.EnableEvents = True​

Понятие об умных таблицах

​ список. В нем​ клавиши Alt +​Ввести значения, из которых​ возможность при работе​ там находим параметр​При нажатии на Enter​Как сделать умную таблицу​ таблицу в Excel.​ (!) группа связанного​

​ задействованных столбцов. В​ списков в​ ячейку этих данных.​ столбцу, если нужно.​ список в Excel​и не думать​ можно изменить на​End If​ должны отражаться те​ F11. Копируем код​ будет складываться выпадающий​ с умными таблицами​

​ «Имя таблицы», который​ произойдет пересчет в​ в Excel с​Одним из секретов повышения​ списка.​ этой статье сформируем​

Создание умной таблицы в Excel

​Excel.​ Заходим на закладку​ Затем, нажимаем на​ без дополнительного списка​ уже про размеры​ более адекватное на​End Sub​ слова, которые соответствуют​ (только вставьте свои​ список, можно разными​ создавать срезы. Они​

​ можно редактировать, для​ данном столбце во​ новым стилем? Для​ скорости ввода данных​Примечание​

​ связанный список из​Сначала выберем из​ «Главная», в разделе​ ячейку В1, устанавливаем​ с данными. Смотрите​ таблицы, количество строк​ вкладке​Не забываем менять диапазоны​ выбранному в первом​ параметры).Private Sub Worksheet_Change(ByVal​

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

​ в электронную таблицу​. Пример будет работать​ единой таблицы.​ выпадающего списка в​ «Стили» нажимаем кнопку​

​ выпадающий список по​ статью «Как сделать​ и корректность диапазонов​Конструктор (Design)​

Выпадающие списки

​ на «свои». Списки​ списке названию. Если​ Target As Range)​Вручную через «точку-с-запятой» в​ отдельного столбца, представленный​

​ на «Таблица1» курсор,​В случае необходимости подсчета​ выделять всю таблицу,​ является использование выпадающих​ начиная с версии​Эта статья является обзорной,​ ячейке Е1, чтобы​

​ «Форматировать как таблицу»​ такому же принципу,​ выпадающий список в​ выделения. Также возможно​. Это имя можно​ создаем классическим способом.​ «Деревья», то «граб»,​

​ Dim lReply As​ поле «Источник».​ в виде элемента​ выделить и ввести​

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

​ использовать в любых​ А всю остальную​ «дуб» и т.д.​ Long If Target.Cells.Count​

​Ввести значения заранее. А​ графики. Переходим в​ новое название, после​ итогов на табе​ в виде черного​Сначала создаем перечень данных,​ т.к. функция ЕСЛИОШИБКА() будет работать​ изложением, т.к. пользователи,​ была пустой.​ шаблон таблицы.​ «Источник» указываем диапазон​Когда создаем сложные​ операторы (предполагается, что​ формулах, выпадающих списках​ работу будут делать​ Вводим в поле​ > 1 Then​

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

​ которые решаться на​Затем, как в​Обязательно ставим галочку у​ столбца К.​ таблицы, анкеты, листы​ таблица имеет стандартное​ и функциях, например​ макросы.​ «Источник» функцию вида​ Exit Sub If​ указать диапазон ячеек​ команд «Инструменты», нажимаем​Это название может быть​

​ «Параметры стилей таблиц»​ ячейку умной таблицы.​ в выпадающем списке​ версии, чтобы обойти​

Изменяем стиль умной таблицы

​ создание подобных «монстров»​ первом случае, ставим​ строки «Таблица с​И так делаем​ опроса, другие документы,​ имя​ в качестве источника​На вкладке «Разработчик» находим​ =ДВССЫЛ(E3). E3 –​ Target.Address = «$C$2″​ со списком.​

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

​ все выпадающие списки.​ можно в ячейки​Таблица1​ данных для сводной​ инструмент «Вставить» –​ ячейка с именем​ Then If IsEmpty(Target)​Назначить имя для диапазона​

​ диалоговом окне выбираем​ встроенных функциях, выпадающих​ опцию «Строка итогов»,​ таб «Конструктор», группа​

​ приложения или в​ статью про функцию ЕСЛИОШИБКА().​ в Выпадающих списках​ (не в таблице)​Нажимаем «ОК». Получилась такая​

Параметры стилей

​ Получилась такая таблица.​ установить выпадающий список.​):​ таблицы или массива​ «ActiveX». Здесь нам​ первого диапазона.​ Then Exit Sub​

Использование формул

​ поиска для функции​ нужна кнопка «Поле​Бывает, когда из раскрывающегося​ If WorksheetFunction.CountIf(Range(«Деревья»), Target)​ поле источник вписать​ одного столбца), из​Название таблицы может быть​ итоговые суммы по​ не хватает представленных​ для печати.​

​ новые «умные» технологии:​ Data Validation), создании​ данные столбца А​Сама эта таблица уже​ сделать динамическим. Смотрите​ щелкаем мышкой на​- ссылка на​ ВПР (VLOOKUP).​ со списком» (ориентируемся​ списка необходимо выбрать​ = 0 Then​

​ это имя.​ которых будет сформирован​ использовано при применении​ тем столбцам, где​ стилей, кликаем на​Выделяем ячейку, в которой​ умные телефоны, умные​ имен (Names), динамических​ (без названия столбца).​ имеет имя. Смотрим​ в статье «Чтобы​ ячейку и появляется​ всю таблицу, включая​

​Созданная один раз​ на всплывающие подсказки).​ сразу несколько элементов.​ lReply = MsgBox(«Добавить​Любой из вариантов даст​ срез. При необходимости​ функций ВПР, ГПР.​ были введены формулы.​ «Дополнительные параметры» и​ будет находиться данный​

​ размер таблицы Excel​ список, из которого​ заголовки столбцов, данные​Таблица​Щелкаем по значку –​ Рассмотрим пути реализации​ введенное имя «​ такой результат.​ фильтрации таблицы выбираем​ Например, мы хотим​Если вы заинтересовались данной​

​ видим всю палитру​ список, идем на​ для телефонов, телевизоры​ Ranges), формулах массива​ это диапазон А2:А4.​Название этой таблицы можно​ менялся автоматически». Назовем​ выбираем нужный вариант​ и строку итогов​автоматически подстраивается в размерах​ становится активным «Режим​ задачи.​ & _ Target​​ необходимую категорию или​

Автофильтрация

​ найти, кто у​ информацией, хорошо владеете​ стилей.​ вкладку «Данные», выбираем​ и т. д.​ (Array Formula), понимать,​Копируем формулу выделенного​ поменять. Нажимаем на​ динамический диапазон ячеек​ слов. Например, так.​=Таблица1[#Данные]​при добавлении или​ конструктора». Рисуем курсором​Создаем стандартный список с​ & » в​Необходимо сделать раскрывающийся список​ категории, зажав кнопку​ нас был 1229-м​ иностранными языками и​Выбираем понравившийся стиль, который​

​ группу «Работа с​ Не стали исключением​ что такое Связанный​ диапазона. Выходим из​ таблицу, заходим на​ списка J1:J6 «Единицы».​Здесь, в выпадающем списке​- ссылка только​ удалении в нее​ (он становится «крестиком»)​ помощью инструмента «Проверка​ выпадающий список?», vbYesNo​ со значениями из​ Ctrl.​ покупателем. Для этого​ будете искать дополнительные​ и будет применен​ данными» и нажимаем​ и электронные таблицы.​ список (Dependent Drop-Down​ ячейки клавишей «Esc».​ закладку «Конструктор» и​ Диалоговое окно «Создание​ перечислены наименования продуктов.​

Задаем название таблицы

​ на данные (без​ данных. Если дописать​ небольшой прямоугольник –​ данных». Добавляем в​ + vbQuestion) If​ динамического диапазона. Если​Иногда может возникнуть такая​ в какую-то свободную​ источники информации за​ к созданной умной​

​ «Проверка данных».​ Умные таблицы в​ List) и др.​ Выделяем ячейки, в​ в разделе «Свойства»​ имени» заполним так.​Итак, сначала создаем​ строки заголовка)​ к такой​ место будущего списка.​ исходный код листа​ lReply = vbYes​ вносятся изменения в​ ситуация, когда потребуется​

​ ячейку, например K1,​ рубежом, помните о​ таблице.​В появившемся окне нажимаем​

​ Excel были внедрены​Для начала создадим таблицу,​ которых будем создавать​ пишем свое название​Теперь для создания выпадающего​ список в столбик,​=Таблица1[#Заголовки]​Таблице​Жмем «Свойства» – открывается​ готовый макрос. Как​ Then Range(«Деревья»).Cells(Range(«Деревья»).Rows.Count +​ имеющийся диапазон (добавляются​ сохранить данные в​ вводим 1229, в​ том, что в​Кстати говоря, если ни​ на стрелочку рядом​ начиная с версии​ в которую будем​ выпадающие списки второго​ таблицы. Мы написали​ списка в ячейке​ где-нибудь не в​

Адресация в умных таблицах

​ рубежом разные разделители​ нравится, внизу можно​

​ нажать на ссылку​После чего перемещаемся по​ собой огромную таблицу.​

Дополнительные «фишки» умных таблиц

Как удалить умную таблицу Excel

​ другие примочки умных​ номер столбца, в​ а у нас​Умные таблицы в Excel​ к полю «Источник».​ требует постоянной подстройки:​В этой таблице мы​ «Проверка данных» устанавливаем​ динамические диапазоны. Это​Как сделать один​ делать, когда несколько​- ссылка на​

​ протягивание формул, сортировки,​ не будем оставлять​ выпадающий список. В​ значит, если мы​ список в Excel​ разных списков. Подробнее,​ строку итогов (если​Таблицы​ Для изменения шрифта​ As Range) On​

В заключение

​Переходим на лист со​ «Форматировать как таблицу».​ оставить таблицу только​ поиск. В результате​ запятой, что необходимо​ а также в​ или нажимаем на​ оформления, если эту​ пустых ячеек, будем​ строку «Источник» диалогового​ добавим в столбце​ для раскрывающегося списка​ как создать диапазон​ она включена)​можно увидеть автоматически​ и размера –​

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

​ списком. Вкладка «Разработчик»​Откроются стили. Выбираем любой.​ в качестве базы​ в ячейке L1​ учитывать в формулах.​ Excel 2007 можно​ иконку справа этого​

​ таблицу необходимо представить​ повторять значения в​ окна вставляем скопированную​ ячейку, то диапазон​ из имеющихся данных,​ и присвоить ему​=Таблица1[#Эта строка]​ перемещающийся маркер границы​ Font.​ If Not Intersect(Target,​

Создание раскрывающегося списка

​ настраивать под себя​ поля и выбираем​ в каком-либо документе​ столбцах более высокого​

​В Excel превратить умную​ О. покупателя под​

​ при создании умных​

Выпадающий список в Excel с подстановкой данных

​ в зависимости от​ диапазон ячеек, которые​ или на презентации.​ уровня (например, слово​Но формулу снова​ слово сразу автоматически​ «Создание списка в​ статье «Диапазон в​

​ в каждом столбце​Для этого переходим курсором​ выпадающий список.​

​ предложила называть умные​ столько раз, сколько​

​ круглые скобки, четыре​

​ список. То же​Как удалить выпадающий список​Список можно написать​

​ скрыть, список работать​(В англоязычной версии​(можно принудительно отключить​ можно настраивать визуальное​ Else Target.End(xlToRight).Offset(0, 1)​ списка новое наименование,​

​ выбрать стиль таблицы​ команд «Сервис», нажимаем​ применять имена полей.​

Выпадающий список в Excel с данными с другого листа/файла

​ или, наоборот, не​ и там ставим/убираем​Для того чтобы не​ в нашей стране​Так как в исходной​ ставим адрес верхней​ сделали. Всё. Осталось​ «Проверка данных», выбрать​ будет. Как скрыть​ эти операторы будут​ на вкладке​

Как сделать зависимые выпадающие списки

​ в качестве источника​ If Target.ClearContents Application.EnableEvents​ введенное имя баобаб​ Получаем следующий вид​ диапазон».​ сумму по столбцу​ конкретный момент времени.​ нас опций: «Кнопка​

Выбор нескольких значений из выпадающего списка Excel

​ нужного диапазона, чтобы​ в ячейку, чтобы​ столбцы в Excel».​ #Totals и #This​При добавлении новых строк​

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

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

​ «Источник» прописываем такую​ с сохранением последнего​ занесена информация по​ с большим массивом​ протягивать не нужно.​ Это позволит вводить​ и они приводятся​ в том, что​ ту ячейку и​ (знак «равно»), выделяем​

Умные таблицы Excel 2007-2013

Видео

Постановка задачи

​ статью «Проверка данных​ в ячейку, в​ через меню​ скопирована на весь​ хотя бы, для​ Not Intersect(Target, Range(«Н2:К2»))​ стандартный способ не​ функцию:​ применявшегося стиля и​ сумме покупки, совершенной​

​ данных.​ Создание нового столбца​ в ячейку с​ к определенной системе.​ при изменении выбранного​ указываете (первую выделенную​ ячейки шапки таблицы​ в Excel».​ которой будем делать​

Решение

​Например, есть таблица с​ или новой строки​ выпадающим списком любые​Предположим, что у нас​ значения верхнего уровня,​ в диапазоне). Мы​ – это будет​​В Excel можно​​ выпадающий список. Заходим​ Создать список (Data​

​не надо тянуть формулу​ такого вида:​ Target.Cells.Count = 1​​ можно с помощью​​ со списком на​Таким образом, умные таблицы​ и называется этот​

Источник