Содержание
- 1 Цветовой статус
- 2 Ограничения условного форматирования
- 3 Фигуры
- 4 Цель
- 5 Пример
-
- 5.0.1 Скачать
-
- 6 Последовательность шагов
- 6.1 Общие принципы управления строкой состояния
- 6.2 Адрес выделенного диапазона в строке состояния
- 6.3 Количество выделенных ячеек в строке состояния
- 6.4 Ссылки по теме
Выпадающий список в Excel это, пожалуй, один из самых удобных способов работы с данными. Использовать их вы можете как при заполнении форм, так и создавая дашборды и объемные таблицы. Выпадающие списки часто используют в приложениях на смартфонах, веб-сайтах. Они интуитивно понятны рядовому пользователю.
Кликните по кнопке ниже для загрузки файла с примерами выпадающих списков в Excel:
Видео-урок Как создать выпадающий список в Экселе на основе данных из перечня
Представим, что у нас есть перечень фруктов:
Для создания выпадающего списка нам потребуется сделать следующие шаги:
- Выбрать ячейку, в которой мы хотим создать выпадающий список;
- Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбираем пункт “Проверка данных“.
- Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список“:
- В поле “Источник” ввести диапазон названий фруктов =$A$2:$A$6 или просто поставить курсор мыши в поле ввода значений “Источник” и затем мышкой выбрать диапазон данных:
Если вы хотите создать выпадающие списки в нескольких ячейках за раз, то выберите все ячейки, в которых вы хотите их создать, а затем выполните указанные выше действия. Важно убедиться, что ссылки на ячейки являются абсолютными (например, $A$2), а не относительными (например, A2 или A$2 или $A2).
Как сделать выпадающий список в Excel используя ручной ввод данных
На примере выше, мы вводили список данных для выпадающего списка путем выделения диапазона ячеек. Помимо этого способа, вы можете вводить данные для создания выпадающего списка вручную (необязательно их хранить в каких-либо ячейках).
Например, представим что в выпадающем меню мы хотим отразить два слова “Да” и “Нет”. Для этого нам потребуется:
- Выбрать ячейку, в которой мы хотим создать выпадающий список;
- Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбрать пункт “Проверка данных“:
- Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список“:
- В поле “Источник” ввести значение “Да; Нет”.
- Нажимаем “ОК“
После этого система создаст раскрывающийся список в выбранной ячейке. Все элементы, перечисленные в поле “Источник“, разделенные точкой с запятой будут отражены в разных строчках выпадающего меню.
Если вы хотите одновременно создать выпадающий список в нескольких ячейках – выделите нужные ячейки и следуйте инструкциям выше.
Как создать раскрывающийся список в Эксель с помощью функции СМЕЩ
Наряду со способами описанными выше, вы также можете использовать формулу СМЕЩ для создания выпадающих списков.
Например, у нас есть список с перечнем фруктов:
Для того чтобы сделать выпадающий список с помощью формулы СМЕЩ необходимо сделать следующее:
- Выбрать ячейку, в которой мы хотим создать выпадающий список;
- Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбрать пункт “Проверка данных“:
- Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список“:
- В поле “Источник” ввести формулу: =СМЕЩ(A$2$;0;0;5)
- Нажать “ОК“
Система создаст выпадающий список с перечнем фруктов.
Как эта формула работает?
На примере выше мы использовали формулу =СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;;).
Эта функция содержит в себе пять аргументов. В аргументе “ссылка” (в примере $A$2) указывается с какой ячейки начинать смещение. В аргументах “смещ_по_строкам” и “смещ_по_столбцам” (в примере указано значение “0”) – на какое количество строк/столбцов нужно смещаться для отображения данных. В аргументе “” указано значение “5”, которое обозначает высоту диапазона ячеек. Аргумент “” мы не указываем, так как в нашем примере диапазон состоит из одной колонки.
Используя эту формулу, система возвращает вам в качестве данных для выпадающего списка диапазон ячеек, начинающийся с ячейки $A$2, состоящий из 5 ячеек.
Как сделать выпадающий список в Excel с подстановкой данных (с использованием функции СМЕЩ)
Если вы используете для создания списка формулу СМЕЩ на примере выше, то вы создаете список данных, зафиксированный в определенном диапазоне ячеек. Если вы захотите добавить какое-либо значение в качестве элемента списка, вам придется корректировать формулу вручную. Ниже вы узнаете, как делать динамический выпадающий список, в который будут автоматически загружаться новые данные для отображения.
Для создания списка потребуется:
- Выбрать ячейку, в которой мы хотим создать выпадающий список;
- Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбрать пункт “Проверка данных“;
- Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список“;
- В поле “Источник” ввести формулу: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;””))
- Нажать “ОК“
В этой формуле, в аргументе “” мы указываем в качестве аргумента, обозначающего высоту списка с данными – формулу СЧЕТЕСЛИ, которая рассчитывает в заданном диапазоне A2:A100 количество не пустых ячеек.
Примечание: для корректной работы формулы, важно, чтобы в списке данных для отображения в выпадающем меню не было пустых строк.
Как создать выпадающий список в Excel с автоматической подстановкой данных
Для того чтобы в созданный вами выпадающий список автоматически подгружались новые данные, нужно проделать следующие действия:
- Создаем список данных для отображения в выпадающем списке. В нашем случае это список цветов. Выделяем перечень левой кнопкой мыши:
- На панели инструментов нажимаем пункт “Форматировать как таблицу“:
- Из раскрывающегося меню выбираем стиль оформления таблицы:
- Нажав клавишу “ОК” во всплывающем окне, подтверждаем выбранный диапазон ячеек:
- Затем, выделим диапазон данных таблицы для выпадающего списка и присвоим ему имя в левом поле над столбцом “А”:
Таблица с данными готова, теперь можем создавать выпадающий список. Для этого необходимо:
- Выбрать ячейку, в которой мы хотим создать список;
- Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбрать пункт “Проверка данных“:
- Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список“:
- В поле источник указываем =”название вашей таблицы”. В нашем случае мы ее назвали “Список“:
- Готово! Выпадающий список создан, в нем отображаются все данные из указанной таблицы:
- Для того чтобы добавить новое значение в выпадающий список – просто добавьте в следующую после таблицы с данными ячейку информацию:
- Таблица автоматически расширит свой диапазон данных. Выпадающий список соответственно пополнится новым значением из таблицы:
Как скопировать выпадающий список в Excel
В Excel есть возможность копировать созданные выпадающие списки. Например, в ячейке А1 у нас есть выпадающий список, который мы хотим скопировать в диапазон ячеек А2:А6.
Для того чтобы скопировать выпадающий список с текущим форматированием:
- нажмите левой клавишей мыши на ячейку с выпадающим списком, которую вы хотите скопировать;
- нажмите сочетание клавиш на клавиатуре CTRL+C;
- выделите ячейки в диапазоне А2:А6, в которые вы хотите вставить выпадающий список;
- нажмите сочетание клавиш на клавиатуре CTRL+V.
Так, вы скопируете выпадающий список, сохранив исходный формат списка (цвет, шрифт и.т.д). Если вы хотите скопировать/вставить выпадающий список без сохранения формата, то:
- нажмите левой клавишей мыши на ячейку с выпадающим списком, который вы хотите скопировать;
- нажмите сочетание клавиш на клавиатуре CTRL+C;
- выберите ячейку, в которую вы хотите вставить выпадающий список;
- нажмите правую кнопку мыши => вызовите выпадающее меню и нажмите “Специальная вставка“;
- В появившемся окне в разделе “Вставить” выберите пункт “условия на значения“:
- Нажмите “ОК“
После этого, Эксель скопирует только данные выпадающего списка, не сохраняя форматирование исходной ячейки.
Как выделить все ячейки, содержащие выпадающий список в Экселе
Иногда, сложно понять, какое количество ячеек в файле Excel содержат выпадающие списки. Есть простой способ отобразить их. Для этого:
- Нажмите на вкладку “Главная” на Панели инструментов;
- Нажмите “Найти и выделить” и выберите пункт “Выделить группу ячеек“:
- В диалоговом окне выберите пункт “Проверка данных“. В этом поле есть возможность выбрать пункты “Всех” и “Этих же“. “Всех” позволит выделить все выпадающие списки на листе. Пункт “этих же” покажет выпадающие списки схожие по содержанию данных в выпадающем меню. В нашем случае мы выбираем “всех“:
- Нажмите “ОК“
Нажав “ОК“, Excel выделит на листе все ячейки с выпадающим списком. Так вы сможете привести за раз все списки к общему формату, выделить границы и.т.д.
Как сделать зависимые выпадающие списки в Excel
Иногда нам требуется создать несколько выпадающих списков, причем, таким образом, чтобы, выбирая значения из первого списка, Excel определял какие данные отобразить во втором выпадающем списке.
Предположим, что у нас есть списки городов двух стран Россия и США:
Для создания зависимого выпадающего списка нам потребуется:
- Создать два именованных диапазона для ячеек “A2:A5” с именем “Россия” и для ячеек “B2:B5” с названием “США”. Для этого нам нужно выделить весь диапазон данных для выпадающих списков:
- Перейти на вкладку “Формулы” => кликнуть в разделе “Определенные имена” на пункт “Создать из выделенного“:
- Во всплывающем окне “Создание имен из выделенного диапазона” поставьте галочку в пункт “в строке выше“. Сделав это, Excel создаст два именованных диапазона “Россия” и “США” со списками городов:
- Нажмите “ОК“
- В ячейке “D2” создайте выпадающий список для выбора стран “Россия” или “США”. Так, мы создадим первый выпадающий список, в котором пользователь сможет выбрать одну из двух стран.
Теперь, для создания зависимого выпадающего списка:
- Выделите ячейку E2 (или любую другую ячейку, в которой вы хотите сделать зависимый выпадающий список);
- Кликните по вкладке “Данные” => “Проверка данных”;
- Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выберите “Список“:
- В разделе “Источник” укажите ссылку: =INDIRECT($D$2) или =ДВССЫЛ($D$2);
- Нажмите “ОК“
Теперь, если вы выберите в первом выпадающем списке страну “Россия”, то во втором выпадающем списке появятся только те города, которые относятся к этой стране. Также и в случае, когда выбираете “США” из первого выпадающего списка.
Цветовой статус
Часто возникает необходимость в отчётах акцентировать статус какого-либо процесса при помощи цвета. При этом довольно популярна метафора светофора. Если всё хорошо — зелёный статус, что-то настораживает — жёлтый, всё плохо — красный. Таким образом, тот, кто смотрит ваши отчёты, имеет возможность по цвету сразу обращаться к проблемным цифрам. Отчёты становятся более наглядными, интуитивно понятными и экономят время просматривающих.
Ограничения условного форматирования
В Excel есть стандартный инструмент, который решает эту задачу, — условное форматирование при помощи набора значков. Инструмент отличный, но в некоторых ситуациях вам его может быть недостаточно. Я, например, вижу следующую проблему: данные значки довольно мелкие и хорошо выглядят только в своём оригинальном размере. Если вам потребуется значок побольше и/или поинтересней, то придётся его делать самому при помощи фигур.
Фигуры
Фигурами в MS Office можно нарисовать всё, что угодно. Серьёзно. Любой сложный рисунок «собирается» из простых элементов. Это вопрос только времени и стараний. В этой статье мы будем управлять вот такими несложными, но достаточно привлекательными светофорами, которые легко делаются из фигур овал (круг — частный случай овала/эллипса) и кольцо.
Цель
Мы хотим визуализировать соотношение фактических и плановых расходов по проектам при помощи наших светофоров. Вот так:
Пример
Скачать
Последовательность шагов
-
Для начала подготовим данные, на основе которых будем вычислять статусы. На нашем примере это столбцы: Проект, Бюджет, Факт.
-
Подготовим вспомогательную таблицу, на основе которой будем присваивать значения статусов. В нашем случае эта таблица располагается на листе Настройки, оформлена в виде умной таблицы с названием Шкала. Статус G означает Green (зеленый), Y — Yellow (жёлтый), R — Red (красный).
-
В ячейку E3 листа Статусы введена формула
=ЕСЛИОШИБКА(ВПР((D3-C3)/C3;Шкала;2);»D»).
Как видите, мы находим разницу между фактом и бюджетом и делим её на бюджет. Минимальное значение этого соотношения -1 (минус единица) достигается при нулевых фактических затратах. Этот факт определяет пороговое значение (-1 = -100%) для статуса G в таблице Шкала. Порог начала жёлтого цвета вы определяете сами — у меня он 0%. То есть зелёный цвет должен быть у всего, что в диапазоне от -100% до 0%. Жёлтый — от 0% до 15%. Красный — 15% и выше. Для выбора значения из Шкалы идеально подходит формула ВПР в своей диапазонной версии, которая ищёт диапазон, в который попадает значение ((D3-C3)/C3) в справочнике (Шкала), и возвращает из справочника содержимое ячейки на пересечении найденной строки и указанного столбца (2). Если вычисление функции ВПР (VLOOKUP) оканчивается ошибкой (например, когда Бюджет=0), то формула ЕСЛИОШИБКА (IFERROR) её перехватывает и возвращает в ячейку значение D, что будет означать, что светофор не горит (серый). Формулу из E3 распространяем на E4:E5. -
Формат данных диапазона E3:E5 устанавливаем в «;;;», что предотвращает появление значений ячеек на экране, чтобы цифры не выглядывали из-за светофоров, которые мы поместим над этими ячейками.
-
Создаём именованный диапазон rngTrafLight для ячеек E3:E5.
-
Создаём из фигур наши светофоры. Круги, цвет которых мы будем менять, называем именами figTL1 для E3, figTL2 для E4 и figTL3 для E5. Располагаем фигуры, там где они должны находиться.
-
В редакторе Visual Basic for Application (Alt+F11) вставляем module с любым именем (у меня TL). Для этого щёлкните правой кнопкой по папке Modules и выберите Insert -> Module. Вставьте в модуль этот код:
-
В редакторе VBA в лист Лист1 (Статусы) поместите код:
-
Проверьте как всё работает.
Попробуйте! Это не сложно, но эффектно.
Кому-как, а мне лично строка состояния бывает нужна только в 2-3 случаях:
- после фильтрации в ней отображается количество оставшихся после отбора значений
- при выделении диапазона в ней отображается сумма, среднее и количество выделенных ячеек
- в случае тяжелых файлов в ней можно увидеть прогресс по пересчету формул в книге.
Не так уж и много для строки, которая занимает почти всю ширину экрана и висит на нем постоянно. Давайте попробуем расширить этот скромный список и прикрутить к ней еще несколько полезных функций 🙂
Общие принципы управления строкой состояния
Управление строкой состояния с помощью Visual Basic очень несложно. Чтобы вывести в нее свой текст, можно использовать простой макрос:
Sub MyStatus() Application.StatusBar = "Привет!" End Sub
После его запуска получим:
Чтобы восстановить исходное состояние строки статуса нужен будет такой же короткий «антимакрос»:
Sub MyStatus_Off() Application.StatusBar = False End Sub
В базовом варианте, как видите, все очень просто. Теперь давайте попробуем развить идею…
Адрес выделенного диапазона в строке состояния
В левом верхнем углу окна Excel в строке формул всегда можно увидеть адрес текущей ячейки. Но если выделен целый диапазон, то адреса выделения мы там, к сожалению, не увидим — отображается все та же одна единственная активная ячейка:
Для решения этой задачи можно использовать простой макрос, который будет выводить адрес выделенной области в строку состояния. Причем запускаться этот макрос должен автоматически, при любом изменении выделения на любом листе — для этого разместим его в обработчике события SelectionChange нашей книги.
Откройте редактор Visual Basic с помощью одноименной кнопки на вкладке Разработчик (Developer) или сочетания клавиш левый Alt+F11. Найдите в левом верхнем углу на панели Project свою книгу и откройте в ней двойным щелчком модуль ЭтаКнига (ThisWorkbook):
В открывшееся окно скопируйте и вставьте следующий код макроса:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.StatusBar = "Выделено: " & Selection.Address(0, 0) End Sub
Теперь при выделении любого диапазона (в том числе и не одного!), в строке состояния будет отображаться его адрес:
Чтобы адреса нескольких выделенных с Ctrl диапазонов не сливались, можно добавить небольшое улучшение — заменить с помощью функции Replace запятую на запятую с пробелом:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.StatusBar = "Выделено: " & Replace(Selection.Address(0, 0), ",", ", ") End Sub
Количество выделенных ячеек в строке состояния
При выделении любого диапазона в правой части строки состояния стандартно отображается количество непустых выделенных ячеек. Иногда же нужно знать количество выделенных. Эту задачу также можно решить с помощью простого макроса обработки события книги SelectionChange, как в предыдущем примере. Нужен будет макрос вида:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Dim CellCount As Variant, rng As Range For Each rng In Selection.Areas 'перебираем все выделенные области RowsCount = rng.Rows.Count 'число строк ColumnsCount = rng.Columns.Count 'число столбцов CellCount = CellCount + RowsCount * ColumnsCount 'накапливаем общее число ячеек Next 'выводим в строку состояния Application.StatusBar = "Выделено: " & CellCount & " ячеек" End Sub
Этот макрос перебирает все выделенные с Ctrl области (если их несколько), сохраняет количество строк и столбцов в каждой области в переменных RowsCount и ColumnsCount и накапливает количество ячеек в переменной CellCount, которая затем и выводится в строку состояния. В работе это будет выглядеть следующим образом:
Само-собой, можно соединить этот и предыдущий макросы, чтобы выводить и адрес выделенного диапазона и количество ячеек одновременно. Понадобится изменить лишь одну предпоследнюю строку на:
Application.StatusBar = "Выделено: " & Replace(Selection.Address(0, 0), ",", ", ") & " - итого " & CellCount & " ячеек"
Тогда картина будет совсем замечательная:
Ну, я думаю, вы поняли идею. Предлагайте в комментариях — что еще полезного можно было бы отобразить в строке состояния?
Ссылки по теме
- Что такое макросы, как они работают, как их использовать и создавать
- Удобное координатное выделение на листе Excel
- Как сделать сложные формулы более наглядными
Внизу окна, под горизонтальной полосой прокрутки, располагается полоса строки состояния, на которой отображается текущие изменения документа (режим разметки, страничный или обычный), а также специальная информация — вычисленное среднее значение, сумма и т. д., символы NUM (включения боковой цифровой клавиатуры), SCRL (включения режима прокрутки), CAPS (включения заглавных букв).
- В окне открытой книги щелкните правой кнопкой на свободном месте строки состояния.
- В контекстном меню активируйте нужные пункты (рис. 1.33):
Рис. 1.33. Контекстное меню строки состояния
- «Режим ячейки» (используется по умолчанию) — для отображения режима правки текущей ячейки. При его активации в левой части строки состояния отображается один из следующих режимов.
- «Готово» – общее состояние ячейки.
- «Ввод» – режим ввода содержимого в ячейку. Включается при выделении ячейки и начале ввода данных, а также при нажатии клавиши F2 два раза.
- «Правка» – режим правки данных внутри ячейки. Включается при двойном щелчке левой кнопкой мыши на ячейке или же после одинарного нажатия клавиши F2.
- «Укажите» – режим создания формулы в ячейке. Включается при начале ввода формулы и выборе очередной ячейки для создания формулы.
- «Подписи» (используется по умолчанию) – сообщает, что активная книга имеет цифровую подпись.
- «Политика управления данными» (используется по умолчанию) – сообщает, что используется управление правами на доступ к данным (IRM) для ограничения доступа к содержимому в выбранной книге.
- «Разрешения» (используется по умолчанию) – для отображения специального значка (рядом с индикатором «Режим ячейки»), с помощью которого можно просматривать разрешения на чтение и правку активного документа. При этом данный значок отображается только при ограничении доступа к документу (об этом подробнее смотрите главу про параметры безопасности Excel).
- «CAPS LOCK», «NUM LOCK» и «SCROLL LOCK» — для отображения соответствующих режимов — ввода символов в верхнем регистре, включения боковой цифровой клавиатуры и прокрутки листов с помощью клавиш со стрелками.
- «Фиксированный десятичный формат» (используется по умолчанию) — для указания при вводе, что все численные значения, которые вводятся на листе, будут отображаться в фиксированном десятичном формате.
ПримечаниеДанный формат используется, если активировать настройку «Автоматическая вставка десятичной запятой».
- «Режим замены» — для отображения режима «Замена», который включается клавишей INS при редактировании содержимого ячейки.
- «Режим перехода в конец» (используется по умолчанию) – для отображения режима перехода в конец документа.
- «Запись макроса» (используется по умолчанию) — для отображения кнопка рядом с индикатором «Режим ячейки», которая запускает начало записи макроса.
- «Режим выделения» (используется по умолчанию) — для отображения различный режимов выделения ячейки.
- «Номер страницы» (используется по умолчанию) — для отображения номера выделенной страницы листа и число страниц листа при работе в режиме «Разметка страницы» или «Предварительный просмотр».
- «Среднее» (используется по умолчанию) – для отображения среднего значения выделенных ячеек, содержащих численные значения.
- «Количество» (используется по умолчанию) — для отображения числа выделенных ячеек.
- «Количество чисел» – для отображения числа выделенных ячеек, содержащих численные значения.
- «Минимальное значение» – для отображения минимального значения в выделенных ячейках.
- «Максимальное значение» — для отображения максимального значения в выделенных ячейках.
- «Сумма» – для отображения суммы численных значений в выделенных ячейках.
- «Ярлыки просмотра» – для отображения значка режимов просмотра табличного поля.
- «Масштаб» – для отображения значения масштаба.
- «Ползунок масштаба» — для отображения ползунка изменения масштаба.
Создание раскрывающегося списка
Создание раскрывающегося списка
Чтобы упростить работу пользователей с листом, добавьте в ячейки раскрывающиеся списки. Раскрывающиеся списки позволяют пользователям выбирать элементы из созданного вами списка.
-
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в
таблице Excel
. Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав
Ctrl+T
.
Примечания:
-
Почему данные следует поместить в таблицу? Когда ваши данные находятся в таблице при
добавлении или удалении элементов из списка
, любые раскрывающиеся списки, основанные на этой таблице, будут автоматически обновляться. Дополнительные действия не требуются. -
Сейчас самое время
отсортировать данные в диапазоне или таблице
в раскрывающемся списке.
-
-
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
-
Перейдите на вкладку
данных
на ленте, затем
в проверку данных
.Примечание:
Если вы не можете щелкнуть
проверку данных
, возможно, рабочий лист защищен или общедоступен.
Разблокируйте определенные области защищенной книги
или прекратите совместное использование листа, а затем повторите шаг 3. -
На вкладке
Параметры
в поле
Разрешить
щелкните
Список
. -
Щелкните поле
Источник
и выделите диапазон списка. В примере данные находятся на листе «Города» в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.
-
Если можно оставить ячейку пустой, установите флажок
Игнорировать пустые
ячейки. -
Установите флажок
Список допустимых значений
-
Откройте вкладку
Сообщение для ввода.
-
Если вы хотите, чтобы при выборе ячейки появлялось всплывающее сообщение, установите флажок
Отображать подсказку, если ячейка является текущей
и введите заголовок и сообщение в соответствующие поля (до 225 символов). Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
-
-
Откройте вкладку
Сообщение об ошибке.
-
Если вы хотите, чтобы при вводе значения, которого нет в списке, появлялось всплывающее сообщение, установите флажок
Выводить сообщение об ошибке,
выберите параметр в поле
Вид
и введите заголовок и сообщение. Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
-
-
Не знаете, какой параметр выбрать в поле
Вид
?-
Чтобы отобразить сообщение, не препятствующее вводу данных, которые не содержатся в раскрывающемся списке, выберите вариант
Сведения
или Предупреждение. Если выбрать вариант «Сообщение», сообщение будет отображаться со значком
, а если «Предупреждение» — со значком
. -
Чтобы заблокировать пользователям ввод данных, которые не содержатся в раскрывающемся списке, выберите вариант
Остановка
.Примечание:
Если вы не добавили заголовок и текст, по умолчанию выводится заголовок «Microsoft Excel» и сообщение «Введенное значение неверно. Набор значений, которые могут быть введены в ячейку, ограничен».
-
Предлагаем скачать образец книги с несколькими примерами проверки данных, аналогичными примеру в этой статье. Вы можете воспользоваться ими или создать собственные сценарии проверки данных.
Скачать примеры проверки данных Excel
.
Ввод данных станет быстрее и точнее, если ограничить значения в ячейке вариантами из раскрывающегося списка.
Сначала создайте на листе список допустимых элементов, а затем выполните сортировку или расположите их в нужном порядке. В дальнейшем эти элементы могут служить источником для раскрывающегося списка данных. Если список небольшой, на него можно легко ссылаться и вводить элементы прямо в средстве проверки данных.
-
Создайте список допустимых элементов для раскрывающегося списка. Для этого введите элементы на листе в одном столбце или строке без пустых ячеек.
-
Выделите ячейки, для которых нужно ограничить ввод данных.
-
На вкладке
Данные
в группе
Инструменты
щелкните
Проверка данных
или
Проверить
.
Примечание:
Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье
Защита книги
. -
Откройте вкладку
Параметры
и во всплывающем меню
Разрешить
щелкните
пункт Список
. -
Щелкните поле
Источник
и выделите на листе список допустимых элементов.Диалоговое окно свернется, чтобы было видно весь лист.
-
Нажмите клавишу ВВОД или кнопку
Развернуть
, чтобы развернуть диалоговое окно, а затем щелкните
ОК
.Советы:
-
Значения также можно ввести непосредственно в поле
Источник
через запятую. -
Чтобы изменить список допустимых элементов, просто измените значения в списке-источнике или диапазон в поле
Источник
-
Можно указать собственное сообщение об ошибке, которое будет отображаться при вводе недопустимых данных. На вкладке
Данные
щелкните
Проверка данных
или
Проверить
, а затем щелкните
Сообщение об ошибке
.
-
См. также
Применение проверки данных к ячейкам
-
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в
таблице Excel
.Примечания:
-
Почему данные следует поместить в таблицу? Когда ваши данные находятся в таблице при
добавлении или удалении элементов из списка
, любые раскрывающиеся списки, основанные на этой таблице, будут автоматически обновляться. Дополнительные действия не требуются. -
Теперь следует
отсортировать данные в том порядке, в котором они должны отображаться
в раскрывающемся списке.
-
-
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
-
Откройте вкладку
Данные
на ленте и щелкните кнопку
Проверка данных
. -
На вкладке
Параметры
в поле
Разрешить
щелкните
Список
. -
Если вы уже создали таблицу с элементами раскрывающегося списка, щелкните поле
Источник
и выделите ячейки, содержащие эти элементы. Однако не включайте в него ячейку заголовка. Добавьте только ячейки, которые должны отображаться в раскрывающемся списке. Список элементов также можно ввести непосредственно в поле
Источник
через запятую. Например:
Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы
-
Если можно оставить ячейку пустой, установите флажок
Игнорировать пустые
ячейки. -
Установите флажок
Список допустимых значений
-
Откройте вкладку
Сообщение для ввода.
-
Если вы хотите, чтобы при выборе ячейки появлялось всплывающее сообщение, установите флажок
Показывать сообщения
и введите заголовок и сообщение в соответствующие поля (до 225 символов). Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
-
-
Откройте вкладку
Сообщение об ошибке.
-
Если вы хотите, чтобы при вводе значения, которого нет в списке, появлялось всплывающее сообщение, установите флажок
Показывать оповещения
, выберите параметр в поле
Тип
, и введите заголовок и сообщение. Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
-
-
Щелкните
ОК
.
После создания раскрывающегося списка убедитесь, что он работает так, как нужно. Например, рекомендуется проверить,
изменяется ли ширина столбцов и высота строк
при отображении всех ваших записей. Если вы решили изменить элементы раскрывающегося списка, см. статью
Добавление и удаление элементов раскрывающегося списка
. Чтобы удалить раскрывающийся список, см. статью
Удаление раскрывающегося списка
.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Добавление и удаление элементов раскрывающегося списка
Видео: создание раскрывающихся списков и управление ими
Общие сведения о таблицах Excel
Применение проверки данных к ячейкам
Блокировка и разблокировка определенных областей защищенного листа
Нужна дополнительная помощь?
Под выпадающим списком понимается содержание в одной ячейке нескольких значений. Когда пользователь щелкает по стрелочке справа, появляется определенный перечень. Можно выбрать конкретное.
Очень удобный инструмент Excel для проверки введенных данных. Повысить комфорт работы с данными позволяют возможности выпадающих списков: подстановка данных, отображение данных другого листа или файла, наличие функции поиска и зависимости.
Создание раскрывающегося списка
Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Параметры». Тип данных – «Список».
Ввести значения, из которых будет складываться выпадающий список, можно разными способами:
- Вручную через «точку-с-запятой» в поле «Источник».
- Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
- Назначить имя для диапазона значений и в поле источник вписать это имя.
Любой из вариантов даст такой результат.
Выпадающий список в Excel с подстановкой данных
Необходимо сделать раскрывающийся список со значениями из динамического диапазона. Если вносятся изменения в имеющийся диапазон (добавляются или удаляются данные), они автоматически отражаются в раскрывающемся списке.
- Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».
- Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:
- Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:
Протестируем. Вот наша таблица со списком на одном листе:
Добавим в таблицу новое значение «елка».
Теперь удалим значение «береза».
Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.
Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.
- Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.
- Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
- Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
- Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
- Сохраняем, установив тип файла «с поддержкой макросов».
- Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».
Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Address = "$C$2" Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range("Деревья"), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & _ Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Range("Деревья").Cells(Range("Деревья").Rows.Count + 1, 1) = Target End If End If End If End Sub
Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».
Нажмем «Да» и добавиться еще одна строка со значением «баобаб».
Выпадающий список в Excel с данными с другого листа/файла
Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.
- Делаем активной ячейку, куда хотим поместить раскрывающийся список.
- Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).
Имя файла, из которого берется информация для списка, заключено в квадратные скобки. Этот файл должен быть открыт. Если книга с нужными значениями находится в другой папке, нужно указывать путь полностью.
Как сделать зависимые выпадающие списки
Возьмем три именованных диапазона:
Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.
- Создадим первый выпадающий список, куда войдут названия диапазонов.
- Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
- Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.
- Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
- Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
- Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.
- На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
- Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
- Жмем «Свойства» – открывается перечень настроек.
- Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.
Выбор нескольких значений из выпадающего списка Excel
Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("Е2:Е9")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(0, 1)) = 0 Then Target.Offset(0, 1) = Target Else Target.End(xlToRight).Offset(0, 1) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("Н2:К2")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
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
Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.
Выпадающий список с поиском
Скачать пример выпадающего списка
При вводе первых букв с клавиатуры высвечиваются подходящие элементы. И это далеко не все приятные моменты данного инструмента. Здесь можно настраивать визуальное представление информации, указывать в качестве источника сразу два столбца.
Skip to content
Одной из наиболее полезных функций при вводе данных является возможность использовать выпадающий список в Excel. Он позволяет выбирать значение из предварительно определенного перечня и разрешает вводить только те данные, которые соответствуют вашим требованиям. Мы предложим вам несколько простых способов, как создавать выпадающие списки в Excel. Более сложные способы, основанные на динамических диапазонах и использовании данных из других таблиц, мы также рассмотрим.
Зачем нужен выпадающий список?
Часто случается так, что в какой-то из колонок вашей таблицы нужно вводить одинаковые повторяющиеся значения. К примеру, фамилии сотрудников, названия товаров или делать выбор в ячейке Excel вида «да – нет». Что может случиться? Конечно, в первую очередь будут ошибки при вводе. Человеческий фактор ведь никто не отменял. Чем нам сие грозит? К примеру, когда мы решим подсчитать, сколько заказов выполнил каждый из менеджеров, то окажется, что фамилий больше, чем сотрудников. Далее придётся искать ошибки, исправлять их и вновь повторять расчет.
Ну и конечно же, все время руками вводить одни и те же слова – просто бессмысленная работа и потеря времени. Вот здесь-то выпадающие списки нам и пригодятся. При нажатии выпадает перечень заранее определённых значений, из которых просто необходимо указать какое-то одно.
Важно то, что вы теперь будете не писать, а выбирать их с помощью мыши или клавиатуры. Это значительно ускоряет работу, а также гарантирует защиту от случайных ошибок. Проверка того, что мы записали в таблицу, теперь уже не нужна.
1 — Самый быстрый способ создать выпадающий список.
Как проще всего добавить выпадающий список в таблицу Excel? Всего один щелчок правой кнопкой мыши по пустой клетке под столбцом с данными, затем команда контекстного меню «Выберите из раскрывающегося списка» (Choose from drop-down list). А можно просто стать в нужное место и нажать сочетание клавиш Alt+стрелка вниз. Появится отсортированный перечень уникальных ранее введенных значений.
Способ не работает, если нашу ячейку и столбец с записями отделяет хотя бы одна пустая строка или вы хотите ввести то, что еще не вводилось выше. На нашем примере это хорошо видно.
2 — Используем меню.
Давайте рассмотрим небольшой пример, в котором нам нужно постоянно вводить в таблицу одни и те же наименования товаров. Выпишите в столбик данные, которые мы будем использовать (например, названия товаров). В нашем примере — в диапазон G2:G7.
Выделите ячейку таблицы (можно сразу несколько), в которых хотите использовать ввод из заранее определенного перечня. Там мы разместим наш выпадающий список.
Далее в главном меню выберите на вкладке Данные – Проверка… (Data – Validation). Затем укажите пункт Тип данных (Allow) и выберите вариант Список (List). Поставьте курсор в поле Источник (Source) и впишите в него адреса с эталонными значениями элементов — в нашем случае G2:G7. Рекомендуется также использовать здесь абсолютные ссылки (для их установки нажмите клавишу F4).
Бонусом здесь идет возможность задать подсказку и сообщение об ошибке, если автоматически вставленное значение вы захотите изменить вручную. Для этого существуют вкладки Подсказка по вводу (Input Message) и Сообщение об ошибке (Error Alert).
В качестве источника значений для выпадающего списка в Excel можно использовать также и именованный диапазон.
К примеру, диапазону I2:I13, содержащему названия месяцев, можно присвоить наименование «месяцы». Затем имя можно ввести в поле «Источник».
Кроме того, и источник и в виде обычного диапазона ячеек, и именованный диапазон могут находиться на других листах вашей рабочей книги.
Но вы можете и не использовать диапазоны или ссылки, а просто определить возможные варианты прямо в поле «Источник». К примеру, чтобы реализовать в таблице Excel простейший выбор «да – нет», вы можете вписать туда –
Да;Нет
Используйте для разделения значений точку с запятой, запятую, либо другой символ, установленный у вас в качестве разделителя элементов. (Смотрите Панель управления — Часы и регион — Форматы — Дополнительно — Числа.)
3 — Создаем элемент управления.
Вставим на лист новый объект – элемент управления «Поле со списком» с последующей привязкой его к данным на листе Excel. Делаем:
- Откройте вкладку Разработчик (Developer). Если её не видно, то в Excel 2007 нужно нажать кнопку Офис – Параметры – флажок Отображать вкладку Разработчик на ленте (Office Button – Options – Show Developer Tab in the Ribbon) или в версии 2010–2013 и выше щелкните правой кнопкой мыши по ленте, выберите команду Настройка ленты (Customize Ribbon) и включите отображение вкладки Разработчик (Developer) с помощью флажка.
- Найдите нужный значок среди элементов управления (см.рисунок ниже).
Вставив элемент управления на рабочий лист, щелкните по нему правой кнопкой мышки и выберите в появившемся меню пункт «Формат объекта». Далее указываем диапазон ячеек, в котором записаны допустимые значения для ввода. В поле «Связь с ячейкой» укажем, куда именно поместить результат. Важно учитывать, что этим результатом будет не само значение из указанного нами диапазона, а только его порядковый номер.
Но нам ведь нужен не этот номер, а соответствующее ему слово. Используем функцию ИНДЕКС (INDEX в английском варианте). Она позволяет найти в списке значений одно из них соответственно его порядковому номеру. В качестве аргументов ИНДЕКС укажите диапазон ячеек (F5:F11) и адрес с полученным порядковым номером (F2).
Формулу в F3 запишем, как показано на рисунке:
=ИНДЕКС(F5:F11;F2)
Как и в предыдущем способе, здесь возможны ссылки на другие листы, на именованные диапазоны.
Обратите также внимание, что здесь мы не привязаны ни к какой конкретному месту таблицы. Таким списком Excel удобно пользоваться, поскольку его можно свободно «перетаскивать» мышкой в любое удобное место. Для этого на вкладке «Разработчик» нужно активизировать режим конструктора.
4 — Элемент ActiveX
Действуем аналогично предыдущему способу, но выбираем иконку чуть ниже — из раздела «Элементы ActiveX».
Определяем перечень допустимых значений (1). Обратите внимание, что здесь для показа можно выбирать сразу несколько колонок. Затем выбираем адрес, по которому будет вставлена нужная позиция из перечня (2).Указываем количество столбцов, которые будут использованы как исходные данные (3), и номер столбца, из которого будет происходить выбор для вставки на лист (4). Если укажете номер столбца 2, то в А5 будет вставлена не фамилия, а должность. Можно также указать количество строк, которое будет выведено в перечне. По умолчанию — 8. Остальные можно прокручивать мышкой (5).
Этот способ сложнее предыдущего, но зато возвращает сразу значение, а не его номер. Поэтому необходимость в промежуточной ячейке и обработке ее при помощи ИНДЕКС — отпадает. Думаю, таким списком пользоваться гораздо удобнее.
5 — Выпадающий список в Excel с автозаполнением
Задача: Создать перечень, в который будут автоматически добавляться значения из заданного динамического диапазона. Если в любую ячейку этого диапазона будут внесены изменения, то сразу же изменится и набор предлагаемых к выбору значений. Никакие формулы и настройки здесь корректировать не нужно.
Вот как автозаполнение может выглядеть на простом примере:
Способ 1. Укажите заведомо большой источник значений для списка.
Самая простая и несложная хитрость. В начале действуем по обычному алгоритму действий: в меню выбираем на вкладке Данные – Проверка … (Data – Validation). Из перечня Тип данных (Allow) выберите вариант Список (List). Поставьте курсор в поле Источник (Source). Зарезервируем в списке набор с большим запасом: например, до 55-й строки, хотя занято у нас только 7. Обязательно не забудьте поставить галочку в чекбоксе «Игнорировать пустые …». Тогда ваш «резерв» из пустых значений не будет вам мешать.
Действительно самый простой способ, но не слишком удобный. Ведь зарезервированное место может и закончиться…
Конечно, в качестве источника можно указать и весь столбец:
=$A:$A
Но обработка такого большого количества ячеек может несколько замедлить вычисления. Особенно в больших таблицах Excel.
Способ 2. Применяем именованный диапазон.
Именованный диапазон отличается от обычного тем, что ему присвоено определенное наименование. С ним гораздо проще работать, так как не нужно вводить ссылку, а достаточно просто указать его имя. Давайте рассмотрим небольшой пример.
В столбце А находятся имена сотрудников, которые мы будем вводить. Перечень может быть сколь угодно длинным. Нам необходимо, чтобы каждая новая запись включалась в раскрывающийся список без всяких дополнительных действий с нашей стороны.
Выделим имеющийся в нашем распоряжении перечень имен A2:A10. Затем присвоим ему название, заполнив поле «Имя», находящееся левее строки формул.
Так вы присвоите какое-то имя этому диапазону Excel.
Создадим в С2 перечень значений. В качестве источника для него укажем выражение
=имя
Недостатком работы с таким выпадающим списком в Excel является то, что новые значения нельзя просто дописывать в конец используемого перечня. Они останутся за пределами именованного диапазона. Если что-то нужно добавить, то их придется вставлять внутрь диапазона, использовав вставку пустой строки.
Перечень ещё можно отсортировать, чтобы удобно было пользоваться.
Главное неудобство пользования таким списком заключается в том, что используемый нами диапазон — статический. Автоматически его размеры измениться не могут. Согласитесь, не слишком удобный и технологичный способ. Слишком много ручных операций.
А теперь давайте пойдем дальше и посмотрим, как можно работать с динамическим диапазоном, который автоматически подстраивается под вводимые значения.
Способ 3. Выпадающий список на основе «умной» таблицы Excel.
Начиная с 2007 года таблица для Excel — уже не просто набор строк и столбцов. Если вы просто расположите показатели с привычном для нас табличном виде, то он не будет считать их таблицей. Существует специальное форматирование, после чего диапазон начинает вести себя как единое целое, приобретая целый ряд интересных свойств. В частности, он начинает сам отслеживать свои размеры, динамически изменяясь при корректировке данных.
Любой набор значений в таблице может быть таким образом преобразован. Например, A1:A8. Выделите их мышкой. Затем преобразуйте в таблицу, используя меню Главная — Форматировать как таблицу (Home — Format as Table). Укажите, что в первой строке у вас находится название столбца. Это будет «шапка» вашей таблицы. Внешний вид может быть любым: это не более чем внешнее оформление и ни на что больше оно не влияет.
Как уже было сказано выше, «умная» таблица хороша для нас тем, что динамически меняет свои размеры при добавлении в нее информации. Если в строку ниже нее вписать что-либо, то она тут же присоединит к себе её. Таким образом, новые значения можно просто дописывать. К примеру, впишите в A9 слово «кокос», и таблица тут же расширится до 9 строк.
Следовательно, автоматическое обновление набора используемой информации в списке можно организовать, если использовать содержимое какого-либо столбца «умной» таблицы.
Осталось только обозначить ее как источник. Проблема заключается в том, что программа в качестве источника в списке не понимает выражение вида
=Таблица1[Столбец1]
и не считает его формулой. Хотя в обычных выражениях на листе вашей рабочей книги это вполне будет работать. Эта конструкция обозначает ссылку на первый столбец. Но в поле «Источник» она почему-то игнорируется.
Чтобы использовать «умную таблицу» как источник, нам придется пойти на небольшую хитрость и воспользоваться функцией ДВССЫЛ (INDIRECT в английском варианте). Эта функция преобразует текстовую переменную в обычную ссылку.
Формула теперь будет выглядеть следующим образом:
=ДВССЫЛ(«Таблица5[Продукт]»)
Таблица5 — имя, автоматически присвоенное «умной таблице». У вас оно может быть другим. На вкладке меню Конструктор (Design) можно изменить стандартное имя на свое (но без пробелов!). По нему мы сможем потом адресоваться к нашей таблице на любом листе книги.
«Продукт» — название нашего первого и единственного столбца, присвоено по его заголовку.
Не забудьте также заключить все выражение в кавычки, чтобы обозначить его как текстовую переменную.
Теперь если в A9 вы допишете еще один фрукт (например, кокос), то он тут же автоматически появится и в нашем перечне. Аналогично будет, если мы что-то удалим. Задача автоматического увеличения выпадающего списка значений решена.
Надеемся, вы сможете теперь с помощью списков без ошибок вводить часто повторяющиеся данные в таблицу Excel при помощи выпадающего списка.
А вот еще полезная для вас информация:
Как сделать зависимый выпадающий список в Excel? — Одной из наиболее полезных функций проверки данных является возможность создания выпадающего списка, который позволяет выбирать значение из предварительно определенного перечня. Но как только вы начнете применять это в своих таблицах,…
The status bar can be a helpful tool in Excel
Updated on February 17, 2021
What to Know
- By default, the status bar shows the page number, number of pages, calculations, magnification, worksheet view, and cell mode.
- Default calculation options include finding the average, count, and sum for selected cells of data in the current worksheet.
- Right-click the status bar to see more options, such as the zoom slider and upload status. Click an option in the menu to toggle it on or off.
This article explains how to work with the status bar in Microsoft Excel. Choose specific options to display information about the current worksheet, spreadsheet data, and on/off status of individual keys on the keyboard, such as Caps Lock, Scroll Lock, and Num Lock. Information covers Excel for Microsoft 365, Microsoft Excel 2019, 2016, 2013, and 2010.
Default Options
Status bar defaults include the page number of the selected worksheet page and the number of pages in the worksheet when you are working in Page Layout or Print Preview. Other details shown by default include:
- Conducting mathematical and statistical calculations
- Changing the magnification of the worksheet
- Changing the worksheet view
- Cell mode
Right-click the status bar to open the status bar context menu. The menu lists available options — those with a checkmark beside them are currently active. Click an option in the menu to toggle it on or off.
Calculation Options
The default calculation options include finding the average, count, and sum for selected cells of data in the current worksheet; these options are linked to the Excel functions by the same name.
If you select two or more cells containing number data in a worksheet the status bar displays:
- The average value of the data in the cells
- The number of cells selected (count)
- The total value of the data in the cells (sum)
Although not active by default, options for finding the Maximum and Minimum values in a selected range of cells are also available using the status bar.
Zoom and Zoom Slider
One of the most-used options of the status bar is the zoom slider in the bottom right corner, which allows users to alter the magnification level of a worksheet. Next to it is zoom, which shows the current level of magnification.
If you choose to display the zoom option but not the zoom slider, you can change the magnification level by clicking on zoom to open the dialog box, which contains options for adjusting magnification.
Worksheet View
Also active by default is the view shortcuts option. Shortcuts are next to the zoom slider, and the three default views are the normal view, page layout view, and page break preview.
Cell Mode
Another well-used option and one also activated by default is Cell Mode, which displays the current status of the active cell in the worksheet. The cell mode is on the left side of the status bar and displays as a single word denoting the current mode of the selected cell.
These modes include:
- Ready: Indicates that the worksheet is ready to accept user input, such as data input, formulas, and formatting.
- Edit: Indicates, as the name states, that Excel is in editing mode. You can activate editing mode by double-clicking on a cell with the mouse pointer or by pressing the F2 key on the keyboard.
If you’re unable to activate edit mode by double-clicking or pressing F2, you need to enable edit mode by going to File > Options > Advanced. Under Editing options, select Allow editing directly in cells.
- Enter: Occurs when a user is entering data into a cell; this state is activated automatically by typing data into a cell or by pressing the F2 key on the keyboard twice in succession.
- Point: This occurs when a formula is being entered using a cell reference by mouse pointing or the arrow keys on the keyboard.
Thanks for letting us know!
Get the Latest Tech News Delivered Every Day
Subscribe
На чтение 3 мин. Просмотров 1.5k. Опубликовано 25.06.2019
Строка состояния , которая проходит горизонтально в нижней части экрана Excel, может быть настроена для отображения ряда параметров, большинство из которых предоставляют пользователю информацию о текущем рабочем листе, данных электронной таблицы и на/отключить статус отдельных клавиш на клавиатуре пользователя, например Caps Lock , Scroll Lock и Num Lock .
Эти инструкции относятся к Microsoft Excel 2019, 2016, 2013, 2010 и Excel для Office 365.
Содержание
- Изменение параметров строки состояния
- Параметры по умолчанию
- Варианты расчета
- Zoom и Zoom Slider
- Просмотр листа
- Сотовый режим
Изменение параметров строки состояния
В строке состояния есть некоторые параметры по умолчанию, такие как номер страницы выбранной страницы рабочего листа и количество страниц в рабочем листе, когда вы работаете в Макет страницы или Предварительный просмотр .
Параметры можно изменить, нажав правой кнопкой мыши на статус-ба r с помощью указателя мыши, чтобы открыть контекстное меню строки состояния . Меню содержит список доступных опций – те, с галочкой у них в данный момент активны. Нажатие на опцию в меню включает или выключает ее.
Параметры по умолчанию
Как уже упоминалось, некоторые параметры предварительно выбираются для отображения по умолчанию в строке состояния. Эти параметры включают в себя:
- Проведение математических и статистических расчетов
- Изменение увеличения листа
- Изменение вида листа
- Сотовый режим
Варианты расчета
Параметры расчета по умолчанию включают в себя поиск среднего значения, количества и суммы для выбранных ячеек данных в текущей рабочей таблице; Эти параметры связаны с функциями Excel под тем же именем.
Если вы выберете две или более ячейки, содержащие числовые данные на рабочем листе, появится строка состояния:
- Среднее значение данных в ячейках
- Количество выбранных клеток (количество)
- Общая стоимость данных в ячейках (сумма)
Хотя по умолчанию эта функция неактивна, параметры поиска максимальных и минимальных значений в выбранном диапазоне ячеек также доступны с помощью строки состояния.
Zoom и Zoom Slider
Одним из наиболее часто используемых параметров строки состояния является ползунок масштабирования в правом нижнем углу, который позволяет пользователям изменять уровень увеличения рабочего листа. Рядом с ним находится зум , который показывает текущий уровень увеличения.
Если вы решите показать параметр зум , но не зум ползунок , вы можете изменить уровень увеличения, нажав на увеличить , чтобы открыть диалоговое окно, в котором содержатся параметры для регулировки увеличения.
Просмотр листа
По умолчанию также активен параметр view ярлыки . Ярлыки находятся рядом с ползунком масштабирования , а три вида по умолчанию – это обычный вид , вид макета страницы и предварительный просмотр разрыва страницы. .
Сотовый режим
Еще одна широко используемая опция, также активируемая по умолчанию, – это Режим ячейки , который отображает текущее состояние активной ячейки на листе. Режим ячейки находится в левой части строки состояния и отображается в виде одного слова, обозначающего текущий режим выбранной ячейки.
Эти режимы включают в себя:
- Готово . Указывает, что рабочий лист готов принять пользовательский ввод, например ввод данных, формулы и форматирование.
- Правка . Указывает, что, как следует из названия, Excel находится в режиме редактирования. Вы можете активировать режим редактирования, дважды щелкнув в ячейке с помощью указателя мыши или нажав клавишу F2 на клавиатуре.
Если вы не можете активировать режим редактирования, дважды щелкнув или нажав клавишу F2, вам необходимо включить режим редактирования, перейдя в раздел Файл > Параметры > Дополнительно . В разделе Параметры редактирования выберите Разрешить редактирование непосредственно в ячейках .
- Enter . Происходит, когда пользователь вводит данные в ячейку; это состояние активируется автоматически, если ввести данные в ячейку или дважды нажать клавишу F2 на клавиатуре.
- Точка . Происходит при вводе формулы с использованием ссылки на ячейку с помощью мыши или клавиш со стрелками на клавиатуре.
Я собираюсь вам рассказать о фантастически полезном и эффектном приёме. Это одна из самых интересных вещей, которую мне доводилось видеть в Excel. Кроме того, она достаточно легко реализуется. Если вы разберётесь, как она работает, то вы сразу узнаете об Excel необычно много. Я постараюсь рассказать всё максимально подробно, последовательно и внятно.
Выпадающий список с контекстным поиском
Итак, речь пойдёт о выпадающем списке (так называемый combo box), в который встроена возможность динамического поиска по подстроке, которую пользователь вводит с клавиатуры. Посмотрите пример, в котором мы имеем топ 300 крупнейших городов России. На анимированной иллюстрации видно, как мы динамически сужаем список выбора, вводя подстроку «кр» или «ниж», экономя огромное количество времени. Более того, список меняется после ввода каждого нового символа! Выглядит чрезвычайно привлекательно и профессионально, не так ли? Давайте разбираться, как это устроено.
Файл примера
Скачать
Пошаговая инструкция
Предварительные замечания
В файле примера выпадающий список с поиском реализован сразу в двух вариантах: для обычного диапазона (лист Range) и для умной таблицы (лист Table). Мы будим эти варианты обсуждать одновременно, отмечая их различия.
Шаг 1. Готовим таблицу для списка
Подготовьте таблицу с четырьмя колонками: Город (или то, что вам нужно), Статус, Индекс, Фильтр. Заполните столбец Город значениями. В остальных трёх колонках будут формулы, которые мы обсудим ниже. Я всем рекомендую использовать умную таблицу, так как это значительно проще.
Шаг 2. Формулы для столбца Статус
На примере ячейки F2 рассмотрим формулу, аналогичную для всего столбца Статус (столбец F). Из F2 формулу можно протягивать вниз до конца, а в случае умной таблицы Excel это сделает за вас. Это также относится ко всем формулам, которые мы будем обсуждать в этой статье.
$B$2 — ячейка, с которой будет связан выпадающий список (добавляется на шаге 6). Что значит связано? Всё, что вы введёте в выпадающий список, тут же отразится в ячейке B2.
Формула ПОИСК вернёт ошибку, если содержимое B2 не найдено в $E2. ЕОШИБКА перехватит ошибку и вернёт ИСТИНА, если действительно была ошибка, и — ЛОЖЬ, если строка таки была найдена. Функция НЕ делает из истины ложь и наоборот (инверсирует результат). Таким образом, мы получим в этом столбце ИСТИНА, если подстрока найдена в текущем городе, и наоборот. Обратите внимание, что пустая подстрока содержится в любой строке, поэтому все ячейки столбца Статус имеют значения ИСТИНА, когда мы не ввели ещё ничего в B2.
| Обычный диапазон | Умная таблица |
=НЕ( ЕОШИБКА( ПОИСК( $B$2; $E2) ) )или =NOT( ISERROR( SEARCH( $B$2; $E2) ) ) |
=НЕ( ЕОШИБКА( ПОИСК( $B$2; [@Город]) ) )или =NOT( ISERROR( SEARCH( $B$2; [@Город]) ) )[@Город] — на языке структурных формул умных таблиц это ссылка на ячейку столбца Город в той же строке, в которой находится сама формула. Поскольку ссылка идёт внутри таблицы, то имя самой таблицы в формуле можно не использовать. В остальном всё — тоже самое. |
Шаг 3. Формула для столбца Индекс
Если B2 содержит подстроку поиска, то в столбце Статус не все ячейки примут значение ИСТИНА. Статус ИСТИНА будет только там, в чьи названия городов входит соответствующая подстрока. А в столбце Индекс мы рассчитываем номер по порядку для всех строк, которые содержат искомую подстроку. Например, на рисунке ниже B2 содержит «ни», что заставляет столбец Статус быть истинным у строк с городами Нижний Новгород, Калининград, Магнитогорск и т.д., а в столбце Индекс мы начинаем считать факты срабатываний в F: Нижний Новгород — первое срабатывание, Калининград — второе и так далее.
Функция ЕСЛИ отсекает все значения в F, которые не равны ИСТИНА. Функция СЧЁТЕСЛИ подсчитывает количество значений ИСТИНА в F.
| Обычный диапазон | Умная таблица |
=ЕСЛИ( $F2; СЧЁТЕСЛИ( $F$2:$F2; ИСТИНА ); «»)или =IF( $F2; COUNTIF( $F$2:$F2; TRUE ); «») |
=ЕСЛИ( [@Статус]; СЧЁТЕСЛИ( $F$2:[@Статус]; ИСТИНА ); «»)или =IF( [@Статус]; COUNTIF( $F$2:[@Статус]; TRUE ); «») |
Обратите внимание, что диапазон условия в СЧЁТЕСЛИ введен скользящий — вторая координата не закреплена — и во время протягивания она растёт пропорционально таблице. За счёт этого трюка мы получаем механизм подсчёта значения ИСТИНА. Например, 6-я строка будет подсчитывать ИСТИНУ по диапазону $F$2:$F6 (там одно значение — от Нижнего Новгорода), а 41-я строка будет подсчитывать ИСТИНУ уже по диапазону $F$2:$F41 (а там уже 2 значения — от Нижнего Новгорода и от Калининграда). Вот суть механизма. Это полезный приём, который стоит запомнить.
Шаг 4. Формула для столбца Фильтр
Теперь наша задача, опираясь на столбец Индекс, сформировать в столбце H отфильтрованный список городов, который необходимо показывать в выпадающем списке.
| Обычный диапазон | Умная таблица |
=ЕСЛИОШИБКА( ИНДЕКС( стлГород; ПОИСКПОЗ( ЧСТРОК($G$2:$G2); стлИндекс; 0) ); «»)или =IFERROR( INDEX( стлГород; MATCH( ROWS($G$2:$G2); стлИндекс; 0) ); «» )Обратите внимание на динамический именованный диапазон стлГород и стлИндекс, которые мы вынуждены создавать для случая диапазона, чтобы придать решению должный уровень универсальности. Техника, по которой созданы эти именованные диапазоны разобрана тут. |
=ЕСЛИОШИБКА( ИНДЕКС( [Город]; ПОИСКПОЗ( ЧСТРОК($G$2:[@Индекс]); [Индекс]; 0) ); «»)или =IFERROR( INDEX( [Город]; MATCH( ROWS($G$2:[@Индекс]); [Индекс]; 0) ); «» )Не путайте: [Индекс] — ссылка на весь столбец, а [@Индекс] — ссылка на ячейку из этого столбца в текущей строке. Никакие дополнительные именованные диапазоны нам создавать нет никакой необходимости, так как мы пользуемся встроенным в умные таблицы сервисом при ссылке на столбцы. |
Формула ЧСТРОК($G$2:$G2) используется для генерации последовательных номеров от 1 (для второй строки) до N (в строке N+1), равному количеству найденных подстрок. Просто генерируется диапазон соответствующего размера, а формула ЧСТРОК возвращает его высоту в строках.
Формула ПОИСКПОЗ ищет номер реальной строки, содержащий соответствующий индекс. Например, в столбце Фильтр мы видим Магнитогорск на третьей позиции, но в реальности он взят из E45, так как в G45 стоит цифра 3, которую мы и нашли через ПОИСКПОЗ. То есть ПОИСКПОЗ сказал нам, что Магнитогорск находится в 45-й строке, а извлекли мы его оттуда уже при помощи формулы ИНДЕКС.
Если же при извлечении возникает ошибка (текущая строка находится ниже строки N+1), то формула возвращает пустую строку. За это отвечает ЕСЛИОШИБКА.
Шаг 5. Создание именованных диапазонов
| Обычный диапазон | Умная таблица |
|
Именованный диапазон стлГород =Range!$E$2:ИНДЕКС( Range!$E:$E; СЧЁТЗ(Range!$E:$E) )или =Range!$E$2:INDEX( Range!$E:$E; COUNTA(Range!$E:$E) )Именованный диапазон стлИндекс =Range!$G$2:ИНДЕКС( Range!$G:$G; СЧЁТЗ(Range!$G:$G) )или =Range!$G$2:INDEX( Range!$G:$G; COUNTA(Range!$G:$G) )Именованный диапазон стлФильтр =Range!$H$2:ИНДЕКС( Range!$H:$H; СЧЁТЗ(Range!$H:$H) )или =Range!$H$2:INDEX( Range!$H:$H; COUNTA(Range!$H:$H) )Именованный диапазон DDL_Range =Range!$H$2:ИНДЕКС( стлФильтр; МАКС(стлИндекс) )или =Range!$H$2:INDEX( стлФильтр; MAX(стлИндекс) ) |
Именованный диапазон DDL_Table =Table!$H$2:ИНДЕКС( tblData[Фильтр]; МАКС(tblData[Индекс]) )или =Table!$H$2:INDEX( tblData[Фильтр]; MAX(tblData[Индекс]) )tblData — имя умной таблицы Именованный диапазон DDL_Fake =DDL_TableИменованный диапазон DDL_Fake, как видите, напрямую ссылается на DDL_Table и нужен для того, чтобы обмануть элемент управления ComboBox21, так как он не умеет работать с ИД, ссылающимися на умную таблицу. |
DDL_Range и DDL_Table это диапазоны, которые формируются на базе значений столбца Фильтр без пустых строк. Эти ИД указываются в свойствах ListFillRange выпадающих списков (DDL_Table — через DDL_Fake).
Шаг 6. Вставляем Combo box на лист
На ленте Разработчик в группе Элементы управления через кнопку Вставить выберите элемент управления ActiveX Поле со списком и вставьте его на лист.
Далее:
-
Нажмите кнопку Режим конструктора (предварительно убедитесь, что вставленный элемент управления активен)
-
Нажмите кнопку Свойства на ленте
-
Отредактируйте свойства в соответствии с рисунком:
- Поле AutoWordSelect должно быть равно False
- Поле LinkedCell сделайте равным B2
- Поле ListFillRange должно быть либо DDL_Range для обычного диапазона (лист Range), либо DDL_Fake для умной таблицы (лист Table).
- Поле MatchEntry = 2 — fmMatchEntryNone
Шаг 7. Модификация события Change для элементов Поле со списком
Ну и изюминка нашего приёма, то, что заставляет Поле со списком показывать нам обновленный список выбора после каждого изменения строки ввода, — событие Change этого элемента управления, которое переприсваивает свойство ListFillRange и вызывает событие раскрытия списка DropDown.
Вот и всё!
P.S. К сожалению, не я придумал такую крутую штуку. Это сделал индийский товарищ Sumit Bansal, решение которого я лишь немного оптимизировал (исправил VBA событие — у него оно почему-то было привязано к GotFocus, что всё портило, и сократил формулы).
Читайте также:
-
Создание выпадающего списка в ячейке
-
Каскадные выпадающие списки
-
Универсальные динамические каскадные выпадающие списки без VBA!
-
Каскадные комбинированные списки
-
Динамические каскадные списки на основе сводных таблиц
|
Помогите проставить статус из 3х имеющихся к суммам реазации |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
