Excel всплывающее окно при условии

Обновлено: 03.07.2024

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

Например, нужно по имени человека выводить его номер телефона.

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

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

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

Файлы с примерами можно скачать здесь .

Первый вариант выпадающего списка с поиском (динамические массивы)

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

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

Поможет в этом функция ПОИСК ( SEARCH ), которая ищет заданный набор символов и выдает либо порядковый номер первого символа в тексте, либо ошибку, если символы обнаружены не были.

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

Что мы получили в итоге?

Цифра "1" указывает нам позицию введенных символов в выбранном нами тексте (строка начинается с этих символов).

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

Если же введем « ива », то получим ошибку ЗНАЧ! , которая указывает на то, что такой комбинации символов в тексте не найдено.

Так работает функция ПОИСК и нам лишь остается указать весь диапазон, на который она должна распространяться. Для этого откорректируем формулу и подставим сюда весь диапазон - выбираем первую его ячейку (А2), а затем нажимаем сочетание клавиш Ctrl + Shift + стрелка вниз . Будет выбран весь неразрывный диапазон значений. В моем случае А2:А30. Нажимаем Enter и получаем диапазон значений, который представляет собой динамический массив . На это также указывает синий контур, обрамляющий значения.

Динамические массивы в Excel появились несколько лет назад, но до сих пор они доступны лишь пользователям Microsoft Office 365 и в онлайн-версии офисного пакета. В других версиях и редакциях Excel динамических массивов пока нет и вряд ли они там появится. Скорее всего в коробочной редакции Excel их стоит ждать лишь в следующей версии офисного пакета в 22-ом году.

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

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

Фактически нас интересуют только ячейки с числами, при этом сами значения неважны. Поэтому можем переконвертировать полученные значения в формат ИСТИНА/ЛОЖЬ .

Для этого воспользуемся функцией ЕЧИСЛО ( ISNUMBER ) и обернем ей созданную ранее формулу. В результате если в ячейке будет находиться число, то функция выдаст ИСТИНУ , иначе ЛОЖЬ .

Осталось лишь отфильтровать значения, ведь нас интересует только ИСТИНА . Воспользуемся функцией ФИЛЬТР ( FILTER ), которая позволяет работать с динамическими массивами.

Обернем созданную ранее формулу функцией ФИЛЬТР .

Первый аргумент функции - массив . Укажем диапазон с именами А2:А30. Далее нужно указать то, что нужно включить в новый диапазон, а это вычисляет ранее созданная формула. В качестве третьего аргумента зададим значение, которое будет выводиться в случае отсутствия искомых символов в тексте. Например, напишем « не найдено ».

Все, формула готова!

Вот так просто мы получили список из имен, которые удовлетворяют условиям поиска. Осталось лишь создать выпадающий список. Для этого откорректируем формулу и подставим в функцию ПОИСК ссылку на ячейку, в которой будет выпадающий список. В моем примере эти ячейки находятся на втором листе.

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

На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel. Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав клавиши CTRL+T.

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

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

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

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

На вкладке Параметры в поле Тип данных выберите пункт Список.

Щелкните поле Источник и выделите диапазон списка. В примере данные находятся на листе "Города" в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.

Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.

Установите флажок Список допустимых значений

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

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

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

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

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

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

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

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

Выделите ячейки, для которых нужно ограничить ввод данных.

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

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

Откройте вкладку Параметры и во всплывающем меню Разрешить выберите пункт Список.

Щелкните поле Источник и выделите на листе список допустимых элементов.

Диалоговое окно свернется, чтобы было видно весь лист.

Нажмите клавишу ВВОД или кнопку Развернуть , чтобы развернуть диалоговое окно, а затем нажмите кнопку ОК.

Значения также можно ввести непосредственно в поле Источник через запятую.

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

См. также

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

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

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

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

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

Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы

Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.

Установите флажок Список допустимых значений

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Вы можете использовать кнопку управления формы или кнопку (ActiveX) для запуска макроса, который выполняет действие, когда пользователь щелкает его. Обе эти кнопки также называются push-кнопками, которые можно настроить для автоматизации печати, фильтрации данных или вычисления чисел. Как правило, кнопки управления формы и ActiveX похожи по внешнему виду и функции. Однако между ними есть несколько отличий, которые объясняются в следующих разделах.

Кнопка (управление формы)

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

Примечание: ActiveX не поддерживаются на компьютере Mac.

Макрос и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта.

Прежде всего нужно включить его. Дополнительные сведения см. в статье Показ вкладки "Разработчик".

Добавление кнопки (управление формы)

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

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

Назначьте кнопке макрос и нажмите кнопку ОК.

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

Добавление кнопки (ActiveX управления)

На вкладке Разработчик в группе Элементы управления нажмите кнопку Вставить, а затем вгруппе Элементы управления ActiveX нажмите кнопку .

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

В группе Элементы управления нажмите кнопку Код представления. При этом Visual Basic редактора. Убедитесь, что в списке справа выбрано нажатие кнопки. При нажатии кнопки CommandButton1_Click процедуры (см. рисунок ниже) запускаются эти два макроса: SelectC15 и HelloMessage.

В подпроцеду для кнопки сделайте следующее:

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

При необходимости добавьте собственный код VBA.

Закроем Visual Basic редактора и нажмите кнопку Режим конструктора режим конструктора, чтобы отключить режим конструктора.

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

Чтобы изменить элемент ActiveX, убедитесь, что выбран режим конструктора. На вкладке Разработчик в группе Элементы управления нажмите кнопку Режим конструктора.

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

Примечание: Перед тем как нажать кнопку Свойства, выделите объект, свойства которого требуется просмотреть или изменить.

Появится поле Свойства. Для получения дополнительных сведений о каждом свойстве выберите свойство и нажмите клавишу F1, чтобы отобразить соответствующий раздел справка по Visual Basic. Можно также ввести имя свойства в поле Поиск справки Visual Basic. В следующей таблице 2010 2010 2016 2016 2016 2016 201

Описание параметра

Необходимое свойство

Загружает ли его при открытом книге. (Игнорируется для ActiveX элементов управления.)

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

Можно ли изменять элемент управления

Имя элемента управления

Способ привязки элемента управления к расположенным под ним ячейкам (не закреплен, можно перемещать, но нельзя изменять размер, можно перемещать и изменять размер)

Можно ли выводить элемент управления на печать

Является ли элемент управления видимым или скрытым

Атрибуты шрифта (полужирный, курсив, размер, зачеркивание, подчеркивание и насыщенность)

Bold, Italic, Size, StrikeThrough, Underline, Weight (форма)

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

Будет ли содержимое элемента управления переноситься по словам в конце строки

Размер и положение

Будет ли размер элемента управления изменяться автоматически для отображения всего содержимого

Высота или ширина в пунктах

Height, Width (форма)

Расстояние от элемента управления до левого или верхнего края листа

Форматирование

Стиль фона (прозрачный или непрозрачный)

Цвет переднего плана

Наличие тени элемента управления

Изображение

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

Положение рисунка относительно его заголовка (слева, сверху, справа и так далее)

Клавиатура и мышь

Сочетание клавиш для элемента управления

Настраиваемый значок мыши

Тип указателя, отображаемого при наведите указатель мыши на определенный объект (стандартный, стрелка, I-указатель и так далее).

Будет ли фокус при щелчке.

Макрос и средства VBA находятся на вкладке "Разработчик", которая по умолчанию скрыта, поэтому сначала нужно включить ее.

Перейдите в Excel > параметры. > ленты & панель инструментов.

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

Добавление кнопки (управление формы)

На вкладке Разработчик нажмите кнопку .

Примечание: Если кнопка уже вставлена, щелкните ее правой кнопкой мыши и выберите назначить макрос.

Назначьте кнопке макрос и нажмите кнопку ОК.

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

Добавление кнопки (Visual Basic управления)

На вкладке Разработчик нажмите кнопку .

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

Примечание: Если кнопка уже вставлена, щелкните ее правой кнопкой мыши и выберите назначить макрос.

В диалоговом окне Назначение макроса выберите Новый, который откроет редактор Visual Basic(VBE)в области со следующим кодом:

В подпроцеду для кнопки между строками Sub и End Sub сделайте следующее:

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

Добавьте собственный код VBA.

Чтобы изменить кнопку, щелкните ее правой кнопкой мыши ивыберите Visual Basic .

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

Контекстная экранная строка — это сводная информация о значении, строке или столбце поля. Например, при надвижении указателя на ячейку в области значений контекстная экранная строка отображает текущее значение, а также расположение строки и столбца. Эта контекстная экранная советка особенно полезна при работе с большим отчетом и не нужно менять макет или прокручивать отчет для поиска или проверки этих сведений.

Щелкните отчет сводной таблицы.

На вкладке Анализ в группе Таблица нажмите кнопку Параметры.

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

Если отчет pivotttable подключен к кубу OLAP служб Microsoft SQL Server 2005 Analysis Services и администратор куба включил отображение сведений о свойстве участника, значения свойств можно отобразить во во время перемещения указателя по полю во экранной технической части. Например, продукт может содержать дополнительные сведения о свойстве, такие как ключ продукта, цена за цену за акции и уровень акций. Кроме того, вы можете управлять тем, какие сведения о свойствах будут отображаться во во ветвях.

Примечание: Для этой процедуры требуется, чтобы для поля были определены и включены свойства куба OLAP. Чтобы узнать, доступны ли свойства, щелкните поле правой кнопкой мыши и в ярлыке выберите пункт Показать свойства в отчете. Если в shortcut menu (Без properties Defined)(Свойства не определены) отображаются никакие свойства.

Убедитесь в том, что свойства членов отображаются для отчета в отчете. Для этого сделайте следующее:

На вкладке Анализ в группе Таблица нажмите кнопку Параметры.

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

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

Отображение свойств участника в качестве данных в отчете pivotTable

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

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

Примечание: Для этой процедуры необходимо, чтобы для поля были определены и включены свойства куба OLAP. Чтобы узнать, доступны ли свойства, щелкните поле правой кнопкой мыши и выберите в ярлыке пункт Показать свойства в отчете. Если в shortcut menu (Без properties Defined)(Свойства не определены) отображаются никакие свойства.

Щелкните отчет сводной таблицы.

Убедитесь в том, что свойства членов отображаются для отчета в отчете. Для этого сделайте следующее:

На вкладке Анализ в группе Таблица нажмите кнопку Параметры.

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

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

На вкладке Анализ в группе Инструменты нажмите кнопку Средства OLAPи выберите свойства Поля.

Появится диалоговое окно Выбор полей свойств для измерения.

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

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

Убедитесь, что выбран параметр Показывать поля для этого измерения в форме структуры и нажмите кнопку ОК.

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

Совет: Вы также можете щелкнуть поле метки правой кнопкой мыши, навести указатель мыши на пункт Показать свойства в отчете, а затем щелкнуть Показать все свойства ,Скрыть все свойства илиотдельное свойство.

Щелкните отчет сводной таблицы.

На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.

Убедитесь в том, что свойства членов отображаются для отчета в отчете. Для этого сделайте следующее:

На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.

Отображение свойств участника в качестве данных в отчете pivotTable

Щелкните отчет сводной таблицы.

Убедитесь в том, что свойства членов отображаются для отчета в отчете. Для этого сделайте следующее:

На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.

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

На вкладке Параметры в группе Инструменты нажмите кнопку Средства OLAPи выберите свойства Поля.

Появится диалоговое окно Выбор полей свойств для измерения.

Убедитесь, что выбран параметр Показывать поля для этого измерения в форме структуры и нажмите кнопку ОК.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Читайте также: