Как создать динамические каскадные списки в Excel?

Как строить динамические графики в Excel по именованным диапазонам

Научитесь пользоваться функциональными возможностями MS Excel.

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

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

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

Определяем ячейку, переходим на вкладку «Данные», затем на данные «Управление», в поле «Тип данных» выбираем «Список» и в качестве источника выбираем диапазон (столбец) с команды 1 по команду 5.

Другими словами, скажите Excel, какие обозначения изменятся в нашем перечне.

На всякий случай проверьте список.

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

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

Не думайте о том, где это написать, просто зафиксируйте мысль. Вот формула:

Первый аргумент (ссылка) — $B$2:$M$2. Это заголовок (заголовочная часть) нашей таблицы, та часть, которая всегда будет в движении. Здесь мы задаем желаемый диапазон, из которого будут извлекаться данные.

2-й аргумент (Offset_by_lines) — на сколько строк будет смещение. Можно указать номер строки, но нам нужно автоматизировать и динамически выбирать этот номер строки по команде. Поэтому, когда нужно найти количество строк, мы обращаемся к функции ПОИСКПОЗ, которая должна принять команду на поиск из ячейки с выпадающим списком ($P$7 в нашем случае) и затем перемещаться по строкам в диапазоне $A$3:$A$7 (по командам поиска) и самой точной (3-й аргумент — 0) выдать строку (ее мы записали в 1-й аргумент функции СМЕЩ).

Вся хитрость кроется в названии.

Нажмите Ctrl+F3 (Диспетчер имен — Новый) или перейдите на вкладку «Формулы» и нажмите «Имя».

Имя — Команды (можно выбрать любую, которую вы помните), сразу же скопируйте ее.

А в купе мы просто пишем самую страшную из всех формул:

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

Перейдем к самой интересной части: вставке графика. Выделите весь наш график с помощью Ctrl+A или обводки, затем перейдите к: «Вставка» — «Диаграмма» — выберите рекомендуемый тип, почему нет.

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

Вы можете очистить все подписи легенды (кнопка «Удалить»), нажав кнопку «Создать» в разделе «Элементы легенды», расположенном слева:

Имя строки» — это ячейка с раскрывающимся списком, просто выберите его.

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

Команды (это может быть указанное вами имя) — это одноименная область видимости с главной функцией HUMMES.

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

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

Автор: Роман Павлов, эксперт по образованию SF

Научитесь использовать все прикладные средства функций MS Excel.

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

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

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

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

Шаг 1: Перейдите на вкладку «Данные» в верхней панели и выберите инструмент проверки данных в блоке «Работа с данными» (иконка показана на скриншоте).

Шаг 2. Перейдите на первую вкладку «Параметры», затем выберите «Список» в качестве типа данных.

Шаг 3. Теперь вы можете указать значения для выпадающего списка в поле ввода данных «Источник». Это можно сделать следующими способами:

вручную. Для этого просто введите значения нужных ячеек, разделив их точкой с запятой;

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

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

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

Видео — Создание выпадающих списков в Excel

Раскрывающийся список с подстановкой данных

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

Выделите левой кнопкой мышки диапазон для списка (в данном примере это будет перечень деревьев), затем откройте вкладку «Главная» и выберите меню «Форматировать как таблицу».

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

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

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

Удаленная ячейка (в данном случае мы удалили «березу») также появится в раскрывающемся списке.

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

Зависимые раскрывающиеся списки

Для создания зависимых выпадающих списков используйте следующие инструкции:

Для начала вам нужно создать именованный диапазон. Перейдите во вкладку «Формулы», затем выберите «Диспетчер имён» и «Создать».

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

Видео — Связанные выпадающие списки: легко и быстро

Группировка данных в Excel – придаем таблицам стройности

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

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

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

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

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

Как сгруппировать данные в Excel

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

Автоматическое создание структуры в Excel

Программа может попробовать создать структуру автоматически. Для этого щелкните на ленте: Структура — Группа — Создать структуру.

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

Ручное создание структуры

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

1. Форматирование и заполнение таблицы, создание итоговых строк и столбцов
2. Нажмите на значок маленькой стрелки в правом нижнем углу группы Данные — Структура на ленте. В открывшемся окне настройте расположение строк и столбцов сводки

1. Щелкните на ленте Структура — Группа (или нажмите Alt+Shift+стрелка вправо). Создается группировка

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

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

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

Удалите любую структуру, которая вам больше не нужна. Вам необходимо разгруппировать структуру после нажатия на Данные — Структура — Удалить структуру.

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

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

Я напишу статью о подключении внешних данных и их консолидации. Пользователи Excel сильно зависят от этой функции. Присоединяйтесь к чтению!

Добавить комментарий Отменить ответ

12 комментариев

Добрый день!
Как в книге создать 2 группировки друг за другом, чтобы когда они свернуты не отображались страницы.
Например, в таблице 15 строк. 3 всегда отображаются. Затем 4 строки в группировке и следующие 4 строки в группировке. Надо чтобы,когда обе группировки собраны из них ничего не отображалось.

Здравствуйте, Кир.
Для каждой группировки нужно определять итоговый столбец, который располагается либо над группируемым участком, либо под ним. Поскольку у Вас две группы строк примыкают друг к другу, нужно для верхнего участка установить итоги над данными, для нижнего — под данными. Как это делается — описано вначале пункта «Ручное создание структуры».

Добрый день!
Как сделать чтобы группировки были разного цвета( например 1 группа где знак (+) красного. 2 группа где знак (+) зеленого итд)

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

Здравствуйте! Почему при создании структуры с первого столбца в документе не отображается плюсик? Только точки над структурируемыми столбцами и цифры уровня структуры… Что делать, если структура нужна именно с 1го столбца? Excel 2016. Спасибо.

Здравствуйте, Елена. Не отображается плюсик, потому что он проставляется над столбцом промежуточных итогов. Например, в столбцах у Вас данные по месяцам, и они сворачиваются в итоговые цифры года. Плюсик будет над столбцом соответствующего года.
Итоговый столбец может быть слева или справа от группируемого массива. У Вас в настройках выставлено «слева», однако столбца слева не существует, Вы группируете начиная с первого. Потому, плюсика и нет. Вы можете пойти двумя путями:
1. Скрывать и раскрывать группировку кнопками уровней в верхнем левом углу окна
2. Выбрать расположение столбца итогов справа от группируемого массива. Тогда плюсик будет отображаться в следующем столбце после группируемых. Как это сделать — я рассказал в пункте «Ручное создание структуры»