Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)
Назад: Динамический спарклайн
Дальше: Power Map (3D-карты)
Диаграммы
Файл с примерами: Диаграммы.xlsxНЕСКОЛЬКО ОБЩИХ РЕКОМЕНДАЦИЙ
Data-Ink — меньше визуального мусора
Эдвард Тафти (кстати, термин «спарклайн» принадлежит ему), главный эксперт в мире информационного дизайна и автор классических книг по теме, предложил коэффициент Data-Ink (данные-чернила): соотношение «чернил», затраченных на график в целом, и «чернил», которые действительно отображают данные. Чем он выше, тем лучше (меньше «чернил» потрачено впустую на то, что Тафти называет chartjunk — мусором). Иначе говоря, лишние элементы диаграмм их совсем не украшают, а только усложняют восприятие данных.
Никаких 3D-диаграмм
В подавляющем большинстве книг по Excel и визуализации данных авторы сходятся в том, что объем — главное зло в диаграммах: он уже никого не впечатлит из ваших слушателей и читателей, а вот исказить данные вполне может. Используйте двумерные диаграммы, чтобы доносить информацию точно.
Правило пяти секунд
Стремитесь к тому, чтобы читатель вашего отчета/диаграммы мог сразу понять, что имеется в виду и о чем говорит ваша визуализация. Сложность вводит нас в ступор (и к тому же сложность вокруг нас только возрастает во всем), так что иначе вы рискуете потерять вашего читателя. Здорово, если у каждой диаграммы есть один посыл, одна идея, которую вы хотите донести до слушателя, и надписи / элементы / сам макет диаграммы позволяют ее быстро и однозначно считать.
СОЗДАНИЕ ДИАГРАММ В EXCEL
Диаграмма — визуальное представление числовых данных в Excel. Диаграммы бывают внедренными и расположенными на отдельном листе.
Внедренные диаграммы находятся на графическом слое обычного листа Excel — поверх ячеек. Диаграммы можно передвигать (напомню, что с зажатой клавишей Alt диаграммы и другие объекты графического слоя «приклеиваются» к границам ячеек), можно менять их размеры.
Быстро создать внедренную диаграмму можно с помощью клавиш Alt + F1 (Fn + 
+ F1). Правда, создается с помощью горячих клавиш определенный тип — гистограмма.
+ F1). Правда, создается с помощью горячих клавиш определенный тип — гистограмма.
Поменять тип существующей диаграммы всегда можно на ленте на вкладке «Конструктор диаграмм».
Диаграммы на отдельном листе занимают целый лист, на котором не может быть других объектов, диаграмм, ячеек. Это может быть удобно в некоторых случаях:
• такую диаграмму проще найти (у нее будет свой ярлык, так как она занимает отдельный лист);
• она не закрывает данные, которые тоже могут быть важны;
• на ней самой лучше видно мелкие детали.
Но все же их используют не так часто: нередко на одном листе для построения отчета нужны и сводная/таблица, и отдельные ячейки с показателями (вычисляемыми формулами), и спарклайны / условное форматирование / внедренные диаграммы.
Можно переместить внедренную диаграмму на отдельный лист, нажав соответствующую кнопку на контекстной вкладке «Конструктор диаграмм»; она появляется, когда вы активируете диаграмму.
Диаграммы можно вставить не только горячими клавишами, но и с помощью соответствующей группы команд на ленте на вкладке «Вставка». Там вы можете выбрать нужный тип диаграммы (напомним, что с помощью горячих клавиш строится только один тип — гистограмма, столбики).
Если в вашей версии доступна опция «Рекомендуемые диаграммы» (Recommended Charts) (она появилась в версии 2013), Excel сразу предложит несколько вариантов, наиболее подходящих для ваших данных.
ЭЛЕМЕНТЫ ДИАГРАММ
У диаграмм довольно много элементов (их набор отличается от типа к типу), их можно удалять и изменять (форматировать).
Чтобы выделить объект, можно щелкнуть на него мышкой.
Кроме того, при выделении диаграммы появляется контекстная вкладка на ленте — «Формат», в ней есть выпадающий список элементов.
Чтобы удалить элемент, нужно нажать Delete либо щелкнуть правой кнопкой мыши и нажать «Удалить» в контекстном меню.
Добавить элементы в диаграмму можно несколькими способами. Если в вашей версии есть кнопки настройки диаграммы (появляющиеся справа от нее), то это самый быстрый способ. Первая кнопка с плюсом — «Элементы диаграммы».
Можно добавить элемент с помощью команды на вкладке «Конструктор диаграмм» (Chart Design), она контекстная и появится при выделении диаграммы.
Рядом есть кнопка для выбора одного из готовых макетов (макет — это готовое сочетание элементов диаграммы).
Вы можете навести мышкой на любой макет и посмотреть, как будет выглядеть ваша диаграмма. Но не факт, что для вашей задачи подойдет хотя бы один из готовых макетов; скорее всего, вам нужно будет его донастраивать, добавляя и меняя элементы диаграммы.
Настроить любой элемент можно с помощью контекстного меню и боковой панели «Формат». Чтобы ее вызвать, нужно дважды щелкнуть на элемент диаграммы, либо щелкнуть правой кнопкой на элемент и нажать «Формат…», либо выбрать команду «Формат выделенного» на ленте инструментов во вкладке «Формат», либо воспользоваться сочетанием клавиш Ctrl + 1.
Некоторые настройки можно изменить не только на панели, но и прямо в контекстном меню, которое появляется при щелчке правой кнопкой мыши на элементе. Например, заливку ряда данных (обратите внимание, что тот элемент, к которому применяются настройки, выделен на диаграмме маркерами).
Настраивать можно не только ряды данных, но и отдельные точки данных. Щелкните сначала на ряд данных, а потом еще раз на точку — и сможете настроить ее отдельно.
Подписи данных
Подписи данных — это значения тех или иных точек на диаграмме. Их можно добавить в контекстном меню по правому щелчку на ряде данных — «Добавить подписи данных» (Add Data Labels).
В этом примере мы добавили подписи данных для ряда данных с плановыми значениями.
Это тот случай, когда разместить все подписи не представляется возможным: если мы добавим подписи и для второго ряда данных, они будут перекрывать друг друга, места уже не осталось. Что можно сделать в такой ситуации?
Уменьшить шрифт. Выделите подписи данных, щелкнув на любой из них, и измените шрифт на ленте.
Поменять цену деления у оси. Для этого в формате оси нужно выбрать цену деления, в нашем случае можно выбрать тысячи или десятки тысяч. Теперь подписи будут короче.
У оси появится обозначение «Тысячи», чтобы у тех, кто будет просматривать диаграммы, было понимание, какой масштаб исходных данных.
Еще одно решение — таблица данных.
Таблица данных
Таблица данных позволяет совместить в диаграмме и собственно диаграмму, и таблицу с теми данными, на основе которых она построена.
Особенно полезна таблица данных может быть для диаграмм, расположенных на отдельном листе, ведь в таком случае ячеек рядом с диаграммой быть не может и это единственный вариант совмещения таблицы и диаграммы.
Но таблица данных может быть полезна и для внедренных диаграмм. Она позволяет показать исходные данные под диаграммой, совместить их с легендой, а в метки данных на самой диаграмме вывести еще какой-нибудь показатель.
Заголовки в цвет рядов данных
Есть простое решение, которое может заменить легенду: цветовая индикация в заголовке. Если вынести в заголовок названия ваших рядов данных, то можно выделить каждое слово тем же цветом, какой у соответствующего ряда данных.
Тогда легенду можно удалить: ее функцию теперь выполняет заголовок.
Как это сделать? Достаточно выделить слово (часть заголовка) — например, мышкой — и потом на ленте на вкладке «Главная» поменять цвет шрифта, как если бы вы делали это просто в ячейке. Можно сделать текст полужирным, чтобы цвет считывался лучше.
Ссылка на ячейку из заголовка
Если вы хотите, чтобы в заголовке диаграммы отображалось значение из ячейки, выделите его, введите знак «равно» (=) в строке формул и щелкните на эту ячейку!
Теперь в заголовке будет отображаться то, что в текущий момент отображается в этой ячейке. Если в ней формула, которая пересчитывается (например, в зависимости от других значений или от текущей даты), то и в диаграмме результат вычисления всегда будет актуальный.
Более того, так можно ссылаться не только в заголовке, но и в других объектах диаграммы, например в отдельном ярлыке данных.
Фильтр диаграммы
В Excel 2019 появилась очень полезная опция — фильтр, позволяющий отобразить только часть данных или только некоторые ряды данных на диаграмме. Это третья кнопка рядом с диаграммой — с иконкой фильтра.
В предыдущих версиях можно скрывать строки или столбцы, чтобы данные не отображались на диаграмме.
Если же вам, напротив, нужно отображать данные со скрытых строк и листов (второе может быть особенно актуально, если вы сделали отдельный лист с заготовкой данных для диаграммы, но в остальном этот лист для работы не нужен, и его хочется скрыть), эту опцию можно включить в окне «Выбор источника данных».
ОСНОВНЫЕ ТИПЫ ДИАГРАММ
Выбрать подходящий тип диаграммы — уже половина успеха. К разным данным подходят разные типы диаграмм (круговая диаграмма вряд ли подойдет для анализа динамики какого-то показателя, а график — для отражения структуры).
Основные типы диаграмм, использующиеся чаще всего: круговая, график, линейчатая, гистограмма (столбики).
Для чего подходят эти типы диаграмм? Наиболее простая схема выбора диаграммы — у Джина Желязны, автора книги «Говори на языке диаграмм».
Есть и более сложные схемы для выбора диаграммы в простых и не очень случаях. Например, схема Эндрю Абела (на русском языке она есть в книге «Сделай наглядно» ).
Круговая диаграмма — базовый вариант для визуализации структуры (доли элементов).
Отмечу, что во многих источниках не рекомендуют использовать круговую диаграмму, если в анализируемой структуре больше пяти-семи элементов: диаграмма становится трудночитаемой.
В таких случаях можно использовать другой тип («Дерево», если есть в вашей версии Excel, или линейчатую — о них ниже) либо построить вторичную круговую (Pie of Pie) или вторичную линейчатую диаграмму (Bar of Pie) — в обоих случаях часть значений переносится на вторую (соответственно круговую или линейчатую) диаграмму, а на основной диаграмме они все становятся одним значением «Другой» (это название можно изменить).
Обратите внимание, что в подписях данных можно выбрать несколько параметров: и имя категории, и значения, и доли. Это можно сделать в боковой панели «Формат подписей данных».
Гистограмма (столбики, Column) — тот тип диаграммы, что строится по умолчанию (когда вы строите диаграмму горячими клавишами Alt + F1). Она подходит для визуализации динамики одного показателя, как и график. Обратите внимание, что гистограмма более актуальна для дискретных, а не непрерывных данных: здесь — отдельные точки (столбцы), а у графика — непрерывная линия.
Но еще эта диаграмма подходит для анализа динамики нескольких показателей (составляющих), например объемов продаж нескольких товаров/категорий. То есть случаев, когда есть и структура, и динамика: например, есть три составляющие бизнеса или два продукта и динамика по ним за какой-то период.
Можно построить обычную гистограмму (гистограмма с группировкой, Clustered Column) — и тогда будут отдельные столбики по каждой составляющей.
Этот вариант также используют и для анализа «план — факт», когда сравнивают базисный (плановый) и итоговый показатели.
Но минус такого варианта гистограммы в том, что не видно значения общих продаж (в случае с отдельными составляющими, а не с анализом выполнения плана). Если вы хотите видеть общие продажи одним столбиком и цветом (вклад каждого компонента), то используйте гистограмму с накоплением (Stacked Column).
У нее есть еще один вариант — нормированная гистограмма с накоплением (100% Stacked Column). Здесь все столбцы одинаковой высоты и показывается только распределение по составляющим в каждом месяце, но не абсолютные значения.
Для визуализации динамики также можно использовать график (Line).
В графике может быть и несколько рядов данных. А если нужно показать разницу между рядами, используйте полосы повышения и понижения (Up/Down Bars). Кстати, это может быть еще одним вариантом для анализа «план — факт».
Полосы повышения и понижения можно выделять и форматировать, как другие объекты. Здесь они сделаны красными и зелеными исходя из того, что перевыполнение плана — это хорошо.
Подойдет для динамики и вариант диаграммы «с областями» (Area) — в этом типе закрашивается площадь под графиком.
Кстати, если значений много (нужно показать динамику показателя за длинный период), можно обойтись вообще без диаграммы — подойдет и условное форматирование. Можно сделать столбец, в котором просто будут дублироваться данные, а потом вставить гистограммы условного форматирования. Можно убрать сами числа в ячейках с гистограммами — мы обсуждали, как это делать, выше в .
Позиционное сравнение: когда нам нужно визуализировать несколько значений, но делать акцент не на их долях (как в круговой диаграмме), а на величине каждого относительно других, подходит линейчатая диаграмма (Bar).
Линейчатая диаграмма, как и гистограмма, бывает и с накоплением, и нормированной.
А еще здесь тоже можно использовать гистограммы условного форматирования.
ДРУГИЕ ДИАГРАММЫ
Комбинированная диаграмма (Combo)
Не все данные можно сравнить в рамках одной диаграммы с одной осью. Нередко данные отличаются на один или несколько порядков, и такая разница не позволяет их показать с сохранением масштаба на одном графике.
В следующем примере продажи — это десятки тысяч, а численность — несколько сотен сотрудников. И численность просто «размазывается» на дне диаграммы из-за своего масштаба.
В таких случаях используются диаграммы с двумя осями, когда каждый из рядов данных показывается в своем масштабе со своей осью. Подойдет в случаях, когда вам нужно показать на одной диаграмме динамику какого-то абсолютного показателя (десятки тысяч штук или миллионы рублей выручки, например) и процентного показателя (какой-либо коэффициент, уровень выполнения плана и так далее) или еще одного абсолютного, но не сопоставимого по величине.
В таких случаях выбирайте тип диаграммы «Комбинированная» (Combo), который позволяет в рамках одной диаграммы использовать две оси (для этого нужно выбрать «Вспомогательная ось» для одного из рядов данных, и тогда каждый из них будет отображаться в своем масштабе).
Комбинированная диаграмма позволяет сравнивать десятки тысяч проданных единиц и десятки сотрудников в одном месте в динамике, несмотря на различие в порядке чисел
Можно изменить и тип существующей диаграммы, если щелкнуть правой кнопкой мыши и выбрать в контекстном меню «Изменить тип диаграммы» (Change Chart Type).
Гистограмма и график — это не единственная возможная комбинация. Можно комбинировать разные типы диаграмм, например «С областями» (график с заливкой всей площади под ним) и гистограмму.
Для упрощения восприятия диаграммы с двумя осями можно поменять цвет шрифта каждой оси на цвет, соответствующий ряду данных, который отображается по этой оси.
Каскадная диаграмма (Waterfall)
Эту диаграмму также называют водопадом (waterfall), факторной диаграммой. Ранее ее нужно было собирать с помощью формул и вспомогательных конструкций, а с Excel 2016 есть встроенный тип диаграммы.
Обычно ее используют, чтобы показать вклад каждого показателя или направления бизнеса или другой составляющей в общий результат. Например, если мы продавали три типа штук и у нас был план по каждой, отчетный период закончился и мы анализируем выполнение общего плана, мы можем посмотреть, как на этот результат повлиял каждый продукт.
Для построения каскадной диаграммы нужно подготовить данные: один столбец с подписями и один с данными (в нашем примере исходные данные в диапазоне A1:C5, а данные для диаграммы в E2:F6), в котором будет первоначальный показатель (в нашем случае план), далее влияние каждого показателя (мы вычитаем из факта план по каждой категории, чтобы вычислить, как она повлияла на общий результат, — например, по штуковинам вы недополучили 80 000) и, наконец, итоговый результат (общий факт).
После построения диаграммы будет следующая картина: каждое положительное значение будет считаться увеличением, отрицательное — уменьшением.
Мы можем сделать любое значение итогом — такие значения отсчитываются от нуля на оси, а не от предыдущего значения, и у них другой цвет. В нашем случае итоги — это план и факт (можно делать и предыдущие итоги, если необходимо). Чтобы сделать точку данных итогов, щелкаем по ней правой кнопкой и в контекстном меню выбираем «Установить в качестве итога» (Set as Total).
Теперь плановый и фактический результат — серого цвета, оба отсчитываются от нуля, и мы видим, как шли от запланированной выручки к фактической и как каждая из трех категорий (выполнение плана по ней) повлияла на результат.
Цвета увеличения и уменьшения можно сделать более уместными, например зелеными и красными. Проще всего изменить их цвета, нажав на соответствующий элемент на легенде.
Можно менять и цвет итоговых значений.
После изменений цветов легенду можно и удалить: все-таки при правильных цветах догадаться, что такое «улучшение», а что «уменьшение», несложно (к тому же об этом говорит и положение точек данных).
Эта диаграмма есть и в Google Таблицах.
Воронка (Funnel)
Чтобы анализировать конверсию (то, сколько клиентов остается на каждом этапе, например), пригодится новая (с Excel 2019) диаграмма «Воронка». Что-то вроде линейчатой, но с выравниванием по центру. Можно выделить отдельную точку данных цветом, если вы хотите сделать акцент на каком-то этапе «Воронки».
Дерево (Treemap)
Еще одна относительно новая диаграмма, которая позволяет показать структуру, но несколько иначе, чем стандартная круговая диаграмма, — это «Дерево» (Treemap). Она появилась в Excel 2016.
Здесь нельзя вставить в подписи доли и значения, как в круговой диаграмме, — только значения.
Так что, если вы хотите выводить именно доли, их придется рассчитать в ячейках и построить диаграмму на основе этих ячеек.
Парето (Pareto)
Помните про принцип Парето? «20% усилий дают 80% результата».
Если хотите визуально проанализировать данные и посмотреть, какой процент клиентов / филиалов / каналов продаж / продуктов и т. д. дает какой процент результата, можно использовать эту диаграмму, появившуюся в Excel 2019.
Можно построить эту диаграмму по данным из предыдущего примера.
Каналы будут визуализированы в виде столбиков (гистограммы) и отсортированы по убыванию. Сверху будет оранжевая линия (график), показывающая накопительный вклад.
СВОДНЫЕ ДИАГРАММЫ
Файл с примером: Сводные диаграммы.xlsxСводная диаграмма подходит для случаев, когда значений много и их невозможно визуализировать на нижнем уровне детализации, но есть признак, по которому можно сгруппировать данные, — тогда визуализация уже приобретает смысл.
Например, в следующем случае у нас несколько тысяч строк с данными об остатках.
Любая диаграмма, построенная непосредственно на основе этих данных, будет выглядеть абсурдно.
Но если сгруппировать данные (например, по городу или категориям товаров), это приобретает смысл. Можно сначала вставить сводную таблицу, настроить тот отчет, который вы хотите визуализировать, а потом построить сводную диаграмму. Можно сразу вставить сводную диаграмму. В любом случае будет построена сводная таблица тоже, без нее сводной диаграммы быть не может.
Если уже есть сводная таблица, можно вставить диаграмму (там же на ленте на вкладке «Вставка»).
Ну а самым быстрым способом будет опция «Рекомендуемые сводные таблицы» (она есть в Excel с 2013-й версии).
Если в данных есть что группировать, то Excel сам предложит в списке рекомендуемых сводные диаграммы.
После нажатия ОК вы получите сводную таблицу и диаграмму.
Сводные таблицы и диаграммы жестко связаны друг с другом: так, если вы отфильтруете данные в таблице, это тут же отразится на диаграмме, и наоборот. Если вы будете добавлять поля в строки, столбцы, фильтры или область значений сводной — они появятся и на диаграмме. И наоборот.
В следующем примере отсортировали данные в таблице — это привело к упорядочиванию и на диаграмме.
Обратите внимание, что областям сводных таблиц строго соответствуют области сводной таблицы:
— ось X (горизонтальная в случае графиков, гистограмм) — это область строк сводной таблицы;
— ось Y (вертикальная) — это область значений сводной таблицы;
— у каждого значения в области столбцов сводной таблицы свой график / столбец / сегмент столбца (в случае гистограммы или линейчатой диаграммы с накоплением, как на скриншоте выше).
То есть для следующей таблицы, где в столбцах годы (динамика), мы не получим желаемый результат на диаграмме (где временная ось будет горизонтальной) сразу.
Диаграмма будет выглядеть так:
Придется поменять столбцы и строки местами: либо в настройках полей сводной таблицы, либо на вкладке «Конструктор» (при выделенной диаграмме) — там есть опция «Строка/столбец» (Switch Row / Column), как и у обычных диаграмм.
Кнопки полей сводной диаграммы выводятся на печать. Если вы хотите это исключить (или просто хотите, чтобы они не отображались в Excel) — отключите их на вкладке «Анализ сводной диаграммы».
В Google Таблицах нет сводных диаграмм. Конечно, можно построить сводную, потом выделить ее ячейки и построить диаграмму, но источником данных для такой диаграммы станет выступать конкретный диапазон, связи со сводной таблицей не будет. Если вы поменяете ее структуру или добавятся новые элементы, диаграмма по-прежнему будет строиться на основе тех же ячеек (то есть в нее могут попасть не все значения, картинка будет искажена).Зато в Google Таблицах можно сгруппировать по полю в настройках обычной диаграммы. Допустим, у нас данные по регионам и округам.
Если построить диаграмму по субъектам, то в ней будет слишком много элементов.
Имеет смысл визуализировать данные на уровне округов, а не субъектов. Для этого в боковой панели «Редактор диаграмм» в качестве ярлыка выберите тот уровень, на котором нужно сгруппировать данные (у нас это ФО, федеральный округ), и нажмите «Объединить».
Таблица с примером: Группировка в диаграмме
Интерактивные диаграммы
Файл с примерами: Интерактивные диаграммы.xlsx
Линия уровня
Чтобы добавить к диаграмме какую-то фиксированную линию (например, линию с планом), достаточно добавить столбец, в который нужно вставить одно и то же число, на уровне которого и должна проходить линия.
Если хочется чуть больше интерактивности (а именно возможности менять этот уровень), можно сделать отдельную ячейку, в которую он будет вводиться, а столбец заполнить, ссылаясь на эту ячейку.
Построим график: фактически данные будут, разумеется, меняться по месяцам, а линия с планом окажется прямой — на заданном уровне.
Чтобы избавиться от «пустого» пространства на диаграмме (у нас нет значений меньше 10 000, а ось начинается с нуля), можно изменить минимальное значение по вертикальной оси в формате этой самой оси (чтобы туда попасть, нужно выделить ось мышкой и нажать Ctrl + 1).
Выделение значений
На диаграммах можно изменять цвет заливки и другие параметры форматирования отдельных точек данных. За счет этого можно визуально выделить отдельные параметры/регионы/периоды.
Чтобы поменять цвет отдельной точки, ее сначала необходимо выделить. Для этого сначала нужно выделить весь ряд данных (щелкнуть по любой точке), а затем еще раз щелкнуть уже по нужной точке. Маркеры выделения (в углах) останутся только у этой точки.
После выделения точки данных можно нажать Ctrl + 1, чтобы открыть боковую панель форматирования «Формат точки данных» (Format Data Point), в ней можно настроить все параметры форматирования, например заливку.
Либо щелкнуть правой кнопкой мыши на точку данных и изменить цвет заливки или границы в появившейся панели.
Но такое форматирование применяется к конкретным точкам данных и не зависит от самих данных. А что, если нужно автоматически изменять форматирование в зависимости от значений? Например, выделять значения больше 20 миллионов?
Если провести аналогию с форматированием ячеек, то изменение заливки — это обычное форматирование, а то, что мы хотим сделать, похоже на условное форматирование ячеек (изменение заливки / шрифта / других параметров в зависимости от значений, хранящихся в ячейках).
Для этого нужно добавить столбец к исходным данным, в нем необходимо с помощью формул оставить только те значения, что мы будем выделять, и заменить те значения, что выделять не планируется, значением #Н/Д (#N/A).
Это можно сделать с помощью функции ЕСЛИ / IF, если мы хотим выделять только значения больше или меньше определенного уровня.
После того как готовы два столбца (со всеми данными и только с теми, что нужно выделить), строим диаграмму по обоим столбцам.
И меняем ее тип на «Комбинированную» (Combo). Сделаем оба ряда данных одинакового типа — «Гистограмма» (Clustered Column). Таким образом, они будут накладываться друг на друга. Если во втором ряду нет значений, то будет отображаться первый (синего цвета в нашем примере). Если во втором ряду есть значения (а есть они, напомним, если значение соответствует условию) — они будут отображаться поверх первого ряда своим (оранжевым в нашем примере) цветом. И возникнет ощущение выделения.
Можно удалить легенду и вторую ось (вспомогательная ось здесь не нужна, так как де-факто у нас графики одинакового масштаба и с одинаковыми значениями, поэтому достаточно будет левой оси).
Теперь при изменении уровня в ячейке E1 на диаграмме автоматически будут выделяться цветом значения выше этого уровня.
Выделение периода
Допустим, у нас есть данные по продажам в кафе Штруделя, и мы построили диаграммы (гистограмму) с продажами за несколько месяцев.
Часть этого периода — карантин. И мы хотим выделить этот период фоном определенного цвета.
Для этого нужен дополнительный столбец, в который мы вставим числа, примерно соответствующие верхней планке по оси в нашем графике (в нашем примере это 14 000).
После этого достаточно добавить новый столбец в диаграмму и изменить ее тип на комбинированный (Combo), чтобы сделать новый столбец диаграммой типа «С областями» (Area).
Высота оси автоматически изменится до 16 000, но ее можно всегда изменить обратно на 14 000 в формате оси, чтобы оранжевая область доходила до самого верха диаграммы.
Флажки (отключение рядов)
Если мы хотим включать и отключать определенные ряды данных на диаграмме, чтобы формировать набор для сравнения (например, выбирать, какие два или три региона из четырех сравнивать друг с другом на графике), можно воспользоваться элементами управления — флажками (Check Box).
Будем рассматривать задачу на примере данных по четырем регионам.
Нам необходимы четыре «переключателя» — флажка, нажимая на которые мы будем включать или выключать определенные регионы на диаграмме.
Флажки, как и другие элементы управления, вставляются на вкладке «Разработчик» (Developer). Если в вашем Excel она не отображается на ленте, ее нужно включить в параметрах Excel.
Флажки вставляются в группе «Элементы управления» — кнопка «Вставить» (Controls — Insert).
После вставки флажка его можно переименовать, чтобы надпись на нем соответствовала региону (щелчок правой кнопкой мыши по флажку — «Изменить текст», Edit Text).
И далее необходимо связать флажок с какой-нибудь ячейкой — это позволит отображать его текущее значение (снят/установлен) в виде логических значений ИСТИНА / ЛОЖЬ (TRUE / FALSE).
Для установки связи с ячейкой (Cell link) необходимо щелкнуть правой кнопкой по объекту и выбрать «Формат объекта» (Format Control).
Теперь при изменении флажка будет меняться и значение в ячейке J1.
Остается создать аналогичные флажки для каждого ряда данных (региона) в нашей таблице.
Теперь у нас есть четыре ячейки, в которых отражается выбор регионов.
Остается создать дополнительную таблицу, в которой будут не все данные (как в A1:E13), а только те, которые мы выбрали. Для этого можно, как мы делали в одном из предыдущих примеров, воспользоваться функциями ЕСЛИ / IF и НД / NA.
Теперь можно построить диаграмму по созданному диапазону A15:E27, в котором отображаются только выбранные данные.
При снятии и установке флажков на диаграмме теперь будут отображаться соответствующие ряды данных (регионы).
Значения ИСТИНА / ЛОЖЬ можно скрыть с помощью форматов или белого шрифта: они не нужны и не являются частью интерфейса, для пользователя здесь предназначены флажки, а значения в ячейках нужны для формул.
А сами флажки можно для большей наглядности передвинуть на диаграмму — к легенде. В таком случае текст на флажках можно удалить — достаточно будет подписей легенды.
Назад: Динамический спарклайн
Дальше: Power Map (3D-карты)
