Эта статья о том, как из выпадающих списков выбрать данные для простых таблиц и диаграмм, построенных на основе этих таблиц, и как скрыть отдельные ряды в диаграммах.
Оба примера, приведенные ниже, используют исходные данные из таблицы Excel (форматированной, умной) Таблица1.
Допустим, в трех подразделениях имеется по 100 комплектов каждого из трех видов оборудования. В полях с 1 кв., исп. по 4 кв., неисп. указано среднее количество исправного и неисправного оборудования в день за четыре квартала.
Как из выпадающих списков выбрать данные для простых таблиц и диаграмм, построенных на основе этих таблиц
Пример 1 показывает возможность выбора трех видов условий для таблицы 2. Можно выбрать одно из трех или все подразделения, одно из трех типов или все типы оборудования, два состояния оборудования (исправное и неисправное). Это похоже на сводную таблицу с фильтром, показанную на рисунке 1.
В ячейках C17:C19 (с бирюзовой заливкой) созданы выпадающие списки, значения из которых после преобразования в ячейках E17:E19 используются в качестве аргументов функций в формулах, расположенных в ячейках таблицы D23:G23.
В ячейке C17 выпадающий список содержит значения: Подразделение1;Подразделение2;Подразделение3;Все подразделения.
В ячейкеC18 выпадающий список содержит значения: Тип1;Тип2;Тип3;Все типы.
В ячейке C19 выпадающий список содержит значения: Исправное;Неисправное.
В ячейках E17:E19 содержатся формулы, преобразующие значения из выпадающих списков в значения, которые будут использоваться в качестве аргументов функций в формулах таблицы 2.
В ячейке E17 содержится формула ЕСЛИ(C17="Все подразделения";"*";C17), которая проверяет выбранное в C17 значение и, если оно равно "Все подразделения", возвращает значение «*», а другие значения в C17 оставляет без изменений.
В ячейке E18 содержится формула ЕСЛИ(C18="Все типы";"*";C18), которая проверяет выбранное в C18 значение и, если оно равно "Все типы", возвращает значение «*», а другие значения в C17 оставляет без изменений.
В ячейке E19 содержится формула ЕСЛИ(C19="Исправное";"исп.";"неисп."), которая преобразует выбранное в C18 значение «Исправное» в «исп.», а значение «Неисправное» преобразует в «неисп.».
Таблица 2
Название таблицы формируется формулой C17&". "&C18&". "&C19 из ячеек C17:C19.
Значение ячейки B23 в поле «Подразделение» таблицы берется из C17, а значение ячейки C23 в поле «Тип оборудования» берется из C18.
В ячейке D23 поля «1 квартал» содержится формула
=СУММЕСЛИМН(ДВССЫЛ("Таблица1["&ЛЕВСИМВ(D22;1)&" кв., "&$E$19&"]");ДВССЫЛ("Таблица1[Подразделение]");$E$17;ДВССЫЛ("Таблица1[Тип оборудования]");$E$18)
Она составлена таким образом, чтобы ее можно было протянуть (скопировать) в ячейки E23:G23.
Формула обращается к столбцам таблицы Excel Таблица1. Адресация столбцов выглядит так: Имя_таблицы[Имя_столбца]. Например, Таблица1[1 кв., исп.].
Аргументы функции СУММЕСЛИМН
СУММЕСЛИМН(диапазон_суммирования;диапазон_условия1;условие1; …)
Диапазон суммирования
ДВССЫЛ("Таблица1["&ЛЕВСИМВ(D22;1)&" кв., "&$E$19&"]")
К строке "Таблица1[" из заголовков D22:G22 таблицы 2 добавляется левый символ (1, 2, 3 или 4), добавляется строка " кв., " и из ячейки E19 добавляется "исп." или "неисп.". Для столбцов первого квартала в таблице Excel Таблица1 получаются строки «Таблица1[1 кв., исп.]» или «Таблица1[1 кв., неисп.]». Функция ДВССЫЛ преобразует текстовую строку в ссылку.
Диапазон_условия1
ДВССЫЛ("Таблица1[Подразделение]"
Здесь функция ДВССЫЛ используется в качестве знака абсолютной ссылки $ и предотвращает сдвиг адресации столбцов таблицы Excel Таблица1 при протягивании (копировании) формулы в ячейки E23:G23.
Условие1
Формируется в ячейке $E$17 и может принимать значения: "Подразделение1", "Подразделение2", "Подразделение3", "*". Значение "*" в условии означает любое значение.
Диапазон_условия2
ДВССЫЛ("Таблица1[Тип оборудования]")
Здесь функция ДВССЫЛ также используется в качестве знака абсолютной ссылки $ и предотвращает сдвиг адресации столбцов таблицы Excel Таблица1 при протягивании (копировании) формулы в ячейки E23:G23.
Условие2
Формируется в ячейке $E$18 и может принимать значения: "Тип1", "Тип2", "Тип3", "*". Значение "*" в условии означает любое значение.
Диаграмма
Название диаграммы берется из ячейки B21 с названием таблицы, которое, в свою очередь, формируется из значений ячеек C17:C19 с выпадающими списками. Чтобы привязать название диаграммы к ячейке B21 нужно выбрать (активировать) название диаграммы, в окно формул вставить знак равно и выбрать ячейку B21. В окне формул появится =ИмяЛиста!$B$21. Диапазоном данных для диаграммы является диапазон D23:G23 таблицы 2, изменение значений в этом диапазоне приводит к изменению диаграммы.
Как скрыть отдельные ряды в диаграммах
Пример 2 демонстрирует возможность скрытия отдельных рядов в диаграммах.
В ячейках C39 и C40 (с бирюзовой заливкой) созданы выпадающие списки, значения из которых после преобразования в ячейках E39 и E40 используются в качестве аргументов функций в формулах, расположенных в ячейках таблицы E44:H46.
В ячейке C39 выпадающий список содержит значения: Тип1;Тип2;Тип3;Все типы.
В ячейке C19 выпадающий список содержит значения: Исправное;Неисправное.
В ячейках E39 и E40 содержатся формулы, преобразующие значения из выпадающих списков в значения, которые будут использоваться в качестве аргументов функций в формулах таблицы 3.
В ячейке E39 содержится формула ЕСЛИ(C39="Все типы";"*";C39), которая проверяет выбранное в C39 значение и, если оно равно "Все типы", возвращает значение «*», а другие значения в C39 оставляет без изменений.
В ячейке E40 содержится формула ЕСЛИ(C40="Исправное";"исп.";"неисп."), которая преобразует выбранное в C40 значение «Исправное» в «исп.», а значение «Неисправное» преобразует в «неисп.».
Таблица 3
Название таблицы формируется формулой =C39&". "&C40 из ячеек C39 и C40.
Значения в поле «Тип оборудования» берутся из $C$39. В поле «Показать на графике» созданы выпадающие списки со значениями «Показать» и «Не показывать».
В ячейке E44 в поле «1 квартал» содержится формула
=ЕСЛИ($D$44="Не показывать";# Н/Д; СУММЕСЛИМН(ДВССЫЛ("Таблица1["&ЛЕВСИМВ(E43;1)&" кв., "&$E$40&"]");ДВССЫЛ("Таблица1[Подразделение]");"Подразделение1";ДВССЫЛ("Таблица1[Тип оборудования]");$E$39))
Формула такая же, как в таблице 2, но к ней добавлено условие: если $D$44="Не показывать", то формула возвращает значение # Н/Д – нет данных.
Диаграмма
Название диаграммы берется из ячейки B42 с названием таблицы, которое, в свою очередь, формируется из значений ячеек C39 и C40 с выпадающими списками. Чтобы привязать название диаграммы к ячейке B42 нужно выбрать (активировать) название диаграммы, в окно формул вставить знак равно и выбрать ячейку B42. В окне формул появится =ИмяЛиста!$B$42. Диапазоном данных для диаграммы является диапазон E44:H46 таблицы 3.
Скрытие отдельных рядов диаграммы
Самый простой способ скрыть ряд диаграммы – в таблице 3 автофильтром скрыть строку с исходными данными для этого ряда, как показано на рисунке 5.
Если на листе несколько неформатированных таблиц, то включить автофильтр можно только в одной. В этом случае можно использовать способ подстановки значения # Н/Д в исходные данные ряда диаграммы, предназначенного для скрытия.
На рисунке 6 в ячейке D44 из выпадающего списка выбрано значение «не показывать». Условие ЕСЛИ($D$44="Не показывать";# Н/Д;[значение_если_ложь]) возвращает всем формулам в строке значение # Н/Д. На диаграмме ряд Подразделение1 скрывается. Для того, чтобы в ячейках не отображалось значение # Н/Д применено условное форматирование. Правило =ЕОШИБКА(E44), формат – шрифт белого цвета применяется к диапазону =$E$44:$H$46.
После применения условного форматирования значения # Н/Д становятся невидимыми в ячейках без заливки или с белым фоном.
Треугольники в левых верхних углах ячеек, показывающие наличие ошибки в ячейке, убраны, как показано на рисунке 9.
В условии ЕСЛИ($D$44="Не показывать";# Н/Д;[значение_если_ложь]) значение # Н/Д можно заменить на "" или на 0. При этом ряд будет отображаться на нулевом значении оси Y.
Для того, чтобы скрыть ряд на нулевом значении оси Y можно в формате оси, в поле Минимум заменить 0 на большее значение, при котором ряд не будет видно, например, на 5. Такой вариант возможен, если только предполагается, что значение рядов не может быть меньше 5.
Нули в ячейках таблицы можно убрать условным форматированием или выбрать Файл → Параметры → Дополнительно и выключить чек-бокс «Показывать нули в ячейках, которые содержат нулевые значения».
Это все, о чем я хотел рассказать в этой статье.
Книга с примерами выложена на Яндекс.Диск.
Если статья вам понравилась, то лайки и подписка – лучшая благодарность. Если у вас есть замечания и дополнения, расскажите, пожалуйста, о них в комментариях. Отвечу на все заданные в комментариях вопросы.
