Ref-sgain.ru

Программы и сервисы
3 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как создать динамические каскадные списки в 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 (Диспетчер имен — Новый) или перейдите на вкладку «Формулы» и нажмите «Имя».

Читайте так же:
Как создать папки и подпапки из значений ячеек в Excel?

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

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

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

строим график

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте так же:
Как создать гиперссылочный указатель листов в книге?

Переходим во вкладку «Данные», которая расположена на верхней панели, затем в блоке «Работа с данными» выбираем инструмент проверки данных

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

В первой вкладке «Параметры», в разделе «Тип данных» выставляем «Список»

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

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

В поле «Источник» вводим значения нужных ячеек

Вбиваем в поле ввода адрес первой и последней ячейки через двоеточие, нажимаем «ОК»

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

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

Результат сделанного выпадающего списка

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

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

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

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

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

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

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

Выбираем любой понравившийся стиль

Устанавливаем галочку возле «Таблица с заголовками», нажимаем «ОК»

Результат отформатированной таблицы

Выделяем левым кликом мыши ту ячейку, в которой будет расположен выпадающий список, и переходим во вкладку «Данные»

В поле «Источник» печатаем функцию «=ДВССЫЛ(“Имя таблицы[Заголовок]”)», подставляя свои данные, как на примере

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

Готовый список

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

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

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

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

Удаляем значение из таблицы, оно автоматически удалится из выпадающего списка

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

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

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

Читайте так же:
Как скопировать лист в конец книги в Excel?

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

Выделяем диапазон ячеек со значением, открываем вкладку «Формулы», нажимаем «Диспетчер имен»

Нажимаем «Создать»

Пишем имя «Деревья», нажимаем «ОК»

Создаем таким же способом остальные диапазоны

В поле «Источник» указываем ячейки с названием диапазонов, нажимаем «ОК»

Выпадающий список с названием диапазона ячеек

В поле ввода «Источник» вводим функцию «=ДВССЫЛ(E1)», нажимаем «ОК»

Результат выпадающего связанного списка

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

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

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

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

Таблица без структуры

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

Минимизированная таблица

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

Таблица со структурой

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

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

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

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

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

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

Читайте так же:
Как скрыть или отобразить ячейки с нулевым значением в выбранных диапазонах в Excel

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

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

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

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

Настройка группировки

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

    Создана первая группа

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

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

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

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

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

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

    Читайте так же:
    Как создать динамическую диаграмму между двумя датами (на основе дат) в Excel?

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

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

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

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

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

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

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

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

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

    голоса
    Рейтинг статьи
    Ссылка на основную публикацию
    Adblock
    detector