Как использовать формулы в диаграммах Excel: примеры

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

Рассмотрим, как применять формулы и условное форматирование в диаграммах Excel.

Примеры формул в диаграммах

Построим на основе ряда данных простой график с маркерами:

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

Аргументы функции РЯД:

1. Имя (название ряда данных, отображается в легенде; не обязательный аргумент);
2. Подписи категорий (метки, появляющиеся на оси категорий; не обязательный аргумент);
3. Значения (которые применяются для построения графика; обязательный параметр);
4. Порядок (порядок значений в ряду данных; обязательный параметр).

Аргументы функции РЯД можно найти и изменить в диалоговом окне «Выбрать данные»:

Выделим элемент легенды «y» и щелкнем по кнопке изменить. В поле «Имя ряда» содержится аргумент функции «Имя»:

Название ряда данных – «y». Его можно менять.

В поле «Значения» - аргумент значений ряда данных.

Подписи горизонтальной оси – это аргумент функции РЯД «Подписи категорий»:

Так как наш график построен на основе одного ряда данных, то порядок равняется единице. Данный аргумент отражается в списке «Элементы легенды».

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

Присмотримся поближе к применению именованных динамических диапазонов при построении диаграмм.



Создание динамических диаграмм

Для имеющейся исходной таблицы с данными создадим именованные диапазоны: для первого столбца – категорий – «х»; для второго – точек данных – «у».

Открываем вкладку «Формулы» - нажимаем кнопку «Диспетчер имен».

В диалоговом окне жмем «Создать». Откроется окно «Создание имени». В поле «Имя» вводим имя диапазона. В поле «Диапазон» - формулу для ссылки на данные в первом столбце (=СМЕЩ(Лист1!$A$1;1;0;СЧЁТЗ(Лист1!$A$1:$A$20)-1;1)).

Чтобы заголовок ряда данных не включался в именованный диапазон, за аргументами функции СЧЕТЗ ставим «-1». В качестве диапазона можно указывать весь столбец А – Excel быстро определяет пустые ячейки. В примере мы поставили лишь первые 20 ячеек.

Создаем именованный диапазон для второго столбца. По такому же принципу.

Теперь поменяем ссылки на ряд данных в графике именами динамических диапазонов. Вызываем диалоговое окно «Выбор источника данных». Выделяем элемент легенды и нажимаем «Изменить». Меняем ссылки в поле «Значения» на имя диапазона.

Далее жмем «Изменить подписи горизонтальной оси». Задаем для диапазона назначенной имя.

График остается прежним. Но если мы добавим в имеющуюся таблицу новые данные, они тут же попадут на диаграмму.

При работе с огромным массивом данных иногда нужно создать диаграмму только на основе некоторого количества последних значений в ряду. Чтобы формула выбирала только их, при формировании динамического именованного диапазона прописываем следующее: =СМЕЩ(Лист1!$A$1;СЧЁТЗ(Лист1!$A$1:$A$1000)-40;0;40;1). По такому же принципу – для столбца В.

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

Условное форматирование в диаграмме

Данный инструмент достаточно просто используется для обычных данных. Для диаграмм в Excel применить встроенное условное форматирование невозможно. Нужно идти другим путем.

Зачем это? Для улучшения восприятия информации. При изменении значений в исходных ячейках автоматически будет меняться цветовое исполнение диаграммы.

Выполнить условное форматирование в диаграммах можно с помощью макросов и формул. Рассмотрим второй способ.

На основании тех же исходных данных составим гистограмму:

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

Для условного форматирования требуется формула, которая определяет отформатированные ячейки.

Для каждого условия создадим отдельный ряд данных. Значения в исходной таблице находятся в диапазоне от 0,06 до 5,7. Создадим ряд для периодов 0-0,6; 0,6-1,6; 1,6-3; 3-4,6; 4,6-6.

Сформируем данные для гистограммы с условным форматированием. Диапазон условий внесем в строки 1 и 2. Заголовки – в строку 3. Формулы для заголовков:

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

Источник данных для гистограммы – столбцы А и В. Нужно исключить колонку В и добавить вновь созданный диапазон С:F.

Теперь столбики диаграммы окрашены в разные цвета в зависимости от значения.