Darbe.ru

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

Как сделать график с осью Z в Excel — Вокруг-Дом — 2021

Как сделать график с осью Z в Excel — Вокруг-Дом — 2021

Используете ли вы его для бизнеса в офисе или для отслеживания своих финансов дома, Microsoft Excel может быть отличным организационным инструментом. Это также может привести к путанице, пока вы не освоитесь с этим. Создание графика с осью Z в Excel не должно вводить в заблуждение. Вертикальная ось Y и горизонтальная ось X вместе отображают дату в двух измерениях, а ось Z переносит усвоение и представление данных на один шаг дальше в третье измерение. Чтобы использовать ось Z, вам просто нужно знать, куда вставить вашу информацию в Excel.

Excel может создавать графики с тремя осями.

Шаг 1

Откройте Microsoft Excel и введите свои данные в вертикальные столбцы. Выделите ваши данные. Нажмите вкладку «Вставить», затем нажмите кнопку «Диаграмма» под ней.

Шаг 2

Выберите график, который вы предпочитаете, убедившись, что он находится в 3-D. Нажмите «Нажмите и удерживайте, чтобы просмотреть образец», чтобы просмотреть выбранный график перед его применением. Нажмите «Далее», когда найдете диаграмму, которая лучше всего подходит для ваших целей.

Шаг 3

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

Шаг 4

Нажмите «Далее», затем перейдите на вкладку «Заголовки» в следующем окне. Введите заголовок для вашего графика и заголовки для осей x, y и z.

Шаг 5

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

Шаг 6

Нажмите «Ярлыки данных», чтобы выбрать, какую часть информации о сериале и заголовки вы хотите показать из всего, что вы ввели.

Шаг 7

Нажмите «Таблица данных», чтобы просмотреть все данные серии, которые вы ввели в свой график в виде таблицы.

Шаг 8

Нажмите кнопку "Далее." Выберите местоположение и расположение графика в последнем всплывающем окне. Нажмите «Готово».

Как сделать процентильный график в Excel

Как сделать процентильный график в Excel

График процентиля помогает увидеть, как данное значение ранжируется по отношению к населению в целом. Он делает это, просматривая все доступные данные и генерируя процентили на основе .

Как создать XY-диаграмму с центрированной осью в Word

Как создать XY-диаграмму с центрированной осью в Word

Настройте диаграмму X Y в Word 2013 таким образом, чтобы оси X, Y или обе оси были центрированы в диаграмме.

Как сделать график безубыточности в Excel

Как сделать график безубыточности в Excel

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

Диаграмма рассеяния в EXCEL

Диаграмма рассеяния ( scatter plot ) используется для отображения возможной взаимосвязи между двумя переменными. Диаграмма рассеяния незаменима при проведении корреляционного и регрессионного анализа.

Возьмем 2 переменные Х и Y и, соответственно, выборку состоящую из нескольких пар значений (Х i ; Y i ). Для наглядности зададим различные типы зависимости между переменными: линейную, квадратичную и затухающую синусоидальную. Для этого сгенерируем соответствующие тренды и настроим случайный разброс переменной Y (по нормальному закону ).

Сначала рассмотрим линейный тренд Y = aX + b (см. Файл примера, лист Линейный ). Параметры тренда (прямой линии) a и b зададим в отдельной табличке, там же зададим параметры отвечающие за величину дисперсии переменной Y.

Величину постоянного разброса (отвечающую за гомоскедастичность модели) будем задавать в % от среднего значения Y. Иногда, дисперсия переменной Y не постоянна (имеется неоднородность наблюдений — гетероскедастичность ). Поэтому, при построении формул учтем и такую возможность.

Для построения диаграммы рассеяния в файле примера использована диаграмма График , т.к. шаг по Х у нас задан постоянным. В случае реальных данных (переменная Х является случайной величиной, а не жестко заданной, как в нашем примере) используйте диаграмму типа Точечная. В файле примера реализовано оба варианта.

Читайте так же:
Можно ли играть на ps4 без подписки

Примечание : Подробнее о построении диаграмм см. статьи Основы построения диаграмм и Основные типы диаграмм .

Отображение информации о 3-х переменных на двухмерной диаграмме

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

Нам требуется построить двумерную диаграмму рассеяния (на плоскости), хотя у нас имеется 3 переменных: производительность, температура и режим .

Обратим внимание, что третья переменная Режим является категориальной (принимает только значения из ограниченного набора значений). В нашем случае переменная Режим принимает 2 значения: Режим №1 и Режим №2 (значения 1 и 2 присвоены номинально).

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

Такой же подход можно использовать для дискретных переменных , когда они принимают небольшое количество значений: 2-5.

Категоризованные диаграммы

Если третья переменная – непрерывная величина, то для отображения данных можно использовать так называемые категоризованные диаграммы (coplot = conditioning plot).

Теперь вместо категориальной переменной Режим у нас имеется непрерывная переменная Давление , которая принимает значения от 10 до 20. Предположим, что значение переменной Давление = 15, является неким пороговым и протекание процесса значительно отличается, если оно протекает при давлении от 10 до 15 и от 15 до 20. Используя этот факт строят 2 диаграммы:

  • Пары значений ( производительность; температура ) при давлении от 10 до 15:
  • Пары значений ( производительность; температура ) при давлении от 15 до 20.

Если пороговых значений 2, то понадобится 3 диаграммы и т.д. Эти диаграммы строятся аналогично диаграммам из предыдущего раздела.

Матрица диаграмм рассеивания

Для множественной регрессии, когда имеется 3 или более переменных, часто строят Матрицу диаграмм рассеивания (Matrix Scatter Plot, Scatter Plot Matrix — SPM).

Если имеется 3 переменных (x 1 , x 2 , y), то строятся 3 обычные диаграммы рассеяния отображающие парные взаимосвязи переменных: (x 1 , x 2 ); (x 1 , y); (x 2 , y).

Примечание : Чтобы найти количество диаграмм рассеяния в матрице, необходимо вычислить число сочетаний из n по 2, где n – число переменных. Например, для 4-х переменных число диаграмм равно ЧИСЛКОМБ(4;2) =6.

Иногда строят не только диаграмму (x 1 , x 2 ), но и (x 2 , x 1 ). В этом случае матрица будет содержать в 2 раза больше диаграмм рассеяния (см. файл примера лист Matrix ).

Примечание : Чтобы найти количество диаграмм рассеяния в такой (полной) матрице, необходимо вычислить число перестановок из n по 2, где n – число переменных. Например, для 4-х переменных число диаграмм равно ПЕРЕСТ(4;2) =12.

Новые диаграммы в Excel 2016

Microsoft очень давно не добавлял в Excel новые типы диаграмм, и вот, наконец в версии 2016 появилось целых шесть новых диаграмм! В предыдущих версиях некоторые из них были в принципе недоступны, а для построения других приходилось совершать шаманские действия. Сейчас же всё происходит в несколько щелчков мыши. Итак, представляю вашему вниманию (рис. 1):

  • Дерево
  • Солнечные лучи
  • Гистограмма (два типа)
  • Ящик с усами
  • Каскадная

%d1%80%d0%b8%d1%81-1-%d0%bd%d0%be%d0%b2%d1%8b%d0%b5-%d1%82%d0%b8%d0%bf%d1%8b-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc-%d0%b2%d1%8b%d0%b4%d0%b5%d0%bb%d0%b5%d0%bd%d1%8b

Скачать заметку в формате Word или pdf, примеры в формате Excel

Рис. 1. Новые типы диаграмм (выделены)

Не спрашивайте, почему «дерево», или почему MS дважды использует тип «гистограмма»…

Дерево

Для примера (рис. 2) я выбрал два десятка самых популярных заметок моего блога, указав рубрику, посещаемость (среднюю в день) и сокращенное название страницы (длинные названия будут смотреться плохо).

%d1%80%d0%b8%d1%81-2-%d0%b8%d0%b5%d1%80%d0%b0%d1%80%d1%85%d0%b8%d1%87%d0%b5%d1%81%d0%ba%d0%b0%d1%8f-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc%d0%b0-treemap-chart-%d0%be%d0%bd%d0%b0-%d0%b6

Рис. 2. Иерархическая диаграмма (Treemap Chart), она же дерево в переводе MS на русский язык

Вот что пишет о такого рода диаграммах Найтон Яу в книге Искусство визуализации в бизнесе: «В 1990 году Бен Шнайдерман из Университета Мэриленда придумал тримап. Этот способ визуализации основан на областях, при котором размер каждого прямоугольника выражает собой количественный показатель. Внешние прямоугольники представляют родительские категории, а прямоугольники внутри родительских категорий — это своего рода подкатегории. Вы можете использовать тримап для визуализации прямых пропорций, но, чтобы опробовать технологию в полном объеме, ее лучше применять с иерархическими или, точнее, с древовидно структурированными данными». Вот почему MS назвал этот тип диаграмм деревом

Читайте так же:
Где находится сервис в excel 2010

Исходная область (в нашем примере А2:С20) не требует повторения заголовков первого уровня (можно указать Финансы только в ячейке А3, оставив ячейки А4 и А5 пустыми). Зато обязательно нужно отсортировать данные по столбцу А. Если строки, относящиеся к рубрике Финансы, расположить не подряд, Excel на диаграмме создаст несколько таких областей. К сожалению, этот тип диаграмм не совместим со сводными таблицами. Также в отличие от большинства других диаграмм не поддерживается связывание текста диаграммы с ячейками.

Солнечные лучи

Этот вид диаграмм также визуализирует иерархию элементов, поэтому в меню он сгруппирован вместе с деревом (рис. 3).

%d1%80%d0%b8%d1%81-3-%d0%b4%d0%b2%d0%b0-%d1%82%d0%b8%d0%bf%d0%b0-%d0%b8%d0%b5%d1%80%d0%b0%d1%80%d1%85%d0%b8%d1%87%d0%b5%d1%81%d0%ba%d0%b8%d1%85-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc

Рис. 3. Два типа иерархических диаграмм

В качестве примера я взял данные по населению РФ в разрезе федеральных округов и субъектов федерации (рис. 4).

%d1%80%d0%b8%d1%81-4-%d1%81%d0%b0%d0%bc%d1%8b%d0%b5-%d0%ba%d1%80%d1%83%d0%bf%d0%bd%d1%8b%d0%b5-%d0%bf%d0%be-%d0%bd%d0%b0%d1%81%d0%b5%d0%bb%d0%b5%d0%bd%d0%b8%d1%8e-%d1%81%d1%83%d0%b1%d1%8a%d0%b5

Рис. 4. Самые крупные (по населению) субъекты федерации

Если в таблице данные отсортированы по округам (в алфавитном порядке), а в каждом округе по населению отдельных субъектов (строка Другие всегда указана последней), то на диаграмме солнечные лучи (рис. 5) федеральные округа автоматически сортируются по совокупному населению, начиная с 12 часов, и по часовой стрелке. Строка Другие сортируется вместе с иными строками внутри округов. Я не нашел возможности, как не показывать строки Другие. Поэтому последовательно выделил соответствующие сектора и отформатировал Нет заливки.

%d1%80%d0%b8%d1%81-5-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc%d0%b0-%d1%81%d0%be%d0%bb%d0%bd%d0%b5%d1%87%d0%bd%d1%8b%d0%b5-%d0%bb%d1%83%d1%87%d0%b8

Рис. 5. Диаграмма солнечные лучи; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Частотная гистограмма

В качестве примера я взял ежедневный курс доллара ЦБ РФ (рис. 6) выбрал только ряд В и выполнил команду Вставить –> Гистограмма –> Гистограмма (рис. 7)

%d1%80%d0%b8%d1%81-6-%d0%ba%d1%83%d1%80%d1%81-%d0%b4%d0%be%d0%bb%d0%bb%d0%b0%d1%80%d0%b0-%d1%86%d0%b1-%d1%80%d1%84

Рис. 6. Курс доллара ЦБ РФ

%d1%80%d0%b8%d1%81-7-%d0%b2%d1%81%d1%82%d0%b0%d0%b2%d0%b8%d1%82%d1%8c-%d0%b3%d0%b8%d1%81%d1%82%d0%be%d0%b3%d1%80%d0%b0%d0%bc%d0%bc%d1%83

Рис. 7. Вставить Гистограмму

В предыдущих версиях Excel, чтобы построить частотную гистограмму сначала требовалось обработать исходные данные, например, с помощью функции ЧАСТОТА или сводных таблиц (подробнее см. Функция массива ЧАСТОТА и Группировка данных сводной таблицы в Excel 2013, раздел Группировка числовых полей). Сейчас же данные могут быть расположены в произвольном порядке. Частотная диаграмма по умолчанию, выглядит не самым привлекательным образом (рис. 8).

%d1%80%d0%b8%d1%81-8-%d1%87%d0%b0%d1%81%d1%82%d0%be%d1%82%d0%bd%d0%b0%d1%8f-%d0%b3%d0%b8%d1%81%d1%82%d0%be%d0%b3%d1%80%d0%b0%d0%bc%d0%bc%d0%b0-%d0%bf%d0%be-%d1%83%d0%bc%d0%be%d0%bb%d1%87%d0%b0%d0%bd

Рис. 8. Частотная гистограмма по умолчанию

Я выбрал иной стиль диаграммы, а также «поколдовал» с настройками диапазонов (рис. 9). Получилось более читабельно (рис. 10).

%d1%80%d0%b8%d1%81-9-%d0%bd%d0%b0%d1%81%d1%82%d1%80%d0%be%d0%b9%d0%ba%d0%b8-%d0%bf%d0%b0%d1%80%d0%b0%d0%bc%d0%b5%d1%82%d1%80%d0%be%d0%b2-%d0%be%d1%81%d0%b8-%d0%b4%d0%b8%d0%b0%d0%bf%d0%b0%d0%b7%d0%be

Рис. 9. Настройки параметров оси диапазонов (оси абсцисс)

%d1%80%d0%b8%d1%81-10-%d0%be%d1%82%d1%84%d0%be%d1%80%d0%bc%d0%b0%d1%82%d0%b8%d1%80%d0%be%d0%b2%d0%b0%d0%bd%d0%bd%d0%b0%d1%8f-%d1%87%d0%b0%d1%81%d1%82%d0%be%d1%82%d0%bd%d0%b0%d1%8f-%d0%b3%d0%b8%d1%81

Рис. 10. Отформатированная частотная гистограмма

Диаграмма Парето

Аналог частотной диаграммы. В диаграмме Парето, как правило, используются не числовые диапазоны, а категории. Например, причины возникновения дефектов (рис. 11). Категории могут располагаться в произвольном порядке. Допускается несколько строк на одну и ту же категорию. А вот переместить категорию Прочие в конец списка, мне не удалось. Диаграмма Парето является комбинированной: наряду с частотной диаграммой присутствует кумулятивная кривая накопленной доли категорий (в нашем примере – накопленная доля дефектов). Для этой кривой используется вторая ось ординат – справа. Так же, как и другие диаграммы, представленные ранее, не поддерживается связывание текста диаграммы с ячейками.

%d1%80%d0%b8%d1%81-11-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc%d0%b0-%d0%bf%d0%b0%d1%80%d0%b5%d1%82%d0%be

Рис. 11. Диаграмма Парето

Ящик с усами

Используется для визуализации сводок статистических данных, характеризующих выборку. В предыдущих версиях приходилось исхитрятся и использовать для этих целей биржевые диаграммы, предварительно проводя анализ (подробнее см. Биржевая диаграмма, она же блочная, она же ящичная). Сейчас же исходные данные могут быть расположены в произвольном порядке, и Excel самостоятельно выдаст 6 сводок выборки: среднее арифметическое, медиану, первый и третий квартили, минимум и максимум, а также покажет отдельные выбросы (рис. 12; подробнее см. Диаграмма «ящик с усами» в Excel 2016).

%d1%80%d0%b8%d1%81-12-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc%d0%b0-%d1%8f%d1%89%d0%b8%d0%ba-%d1%81-%d1%83%d1%81%d0%b0%d0%bc%d0%b8

Рис. 12. Диаграмма ящик с усами

Каскадная

Используется для отображения приращений (изменений) параметра. Наиболее любима финансовыми аналитиками для отображения кеш-флоу за период (рис. 13).

Как создать диаграмму в Excel: пошаговая инструкция

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

Читайте так же:
Как в ворде напечатать буквы контуром

Построение диаграммы на основе таблицы

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

Построение диаграммы на основе таблицы

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

  • Гистрограмма
  • График
  • Круговая
  • Иерархическая
  • Статистическая
  • Точечная
  • Каскадная
  • Комбинированная

Построение диаграммы на основе таблицы

Примечание:
В зависимости от версии программы количество разновидностей диаграмм может быть разным.

Также, существуют и другие типы диаграмм, но они не столь распространённые. Ознакомиться с полным списком можно через меню “Вставка” (в строке меню программы в самом верху), далее пункт – “Диаграмма”.

Построение диаграммы на основе таблицы

Диаграмма в виде графика будет отображается следующим образом:

Построение диаграммы на основе таблицы

А вот так выглядит круговая диаграмма:

Построение диаграммы на основе таблицы

Как работать с диаграммами

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

Как работать с диаграммами в Экселе

Например, чтобы поменять типа диаграммы и ее подтип, щелкаем по кнопке “Изменить тип диаграммы” и в открывшемся списке выбираем то, что нам нужно.

Как работать с диаграммами в Экселе

Нажав на кнопку “Добавить элемент диаграммы” можно раскрыть список действий, который поможет детально настроить вашу диаграмму.

Как работать с диаграммами в Экселе

Для быстрой настройки можно также воспользоваться инструментом “Экспресс-макет”. Здесь предложены различные варианты оформления диаграммы, и можно выбрать тот, который больше всего подходит для ваших целей.

Как работать с диаграммами в Экселе

Довольно полезно, наряду со столбиками, иметь также конкретное значение данных для каждого из них. В этом нам поможет функция подписи данных. Открываем список, нажав кнопку “Добавить элемент диаграммы”, здесь выбираем пункт “Подписи данных” и далее – вариант, который нам нравится (в нашем случае – “У края, снаружи”).

Как работать с диаграммами в Экселе

Готово, теперь наша диаграмма не только наглядна, но и информативна.

Как работать с диаграммами в Экселе

Настройка размера шрифтов диаграммы

Если вам кажется, что размер шрифтов в диаграмме маловат, их можно настроить. Для этого правой кнопкой мыши щелкните, например, на подписи данных, выберите в раскрывшемся меню пункт “Шрифт…”.

Настройка размера шрифтов диаграммы

Здесь можно внести требуемые изменения и сохранить их, нажав кнопку “OK”.

Настройка размера шрифтов диаграммы

Диаграмма с процентами

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

  1. По тому же принципу, который был описан выше, создайте таблицу и выделите участок, который необходимо преобразовать в диаграмму. Далее переходим во вкладку «Вставка» и выбираем, соответственно, тип диаграммы “Круговая”.Диаграмма с процентами в Экселе
  2. По завершении предыдущего шага программа вас автоматически направит во вкладку по работе с вашей диаграммой – «Конструктор». Просмотрите предложенные макеты и остановите свой выбор на той диаграмме, где имеются значки процентов.Диаграмма с процентами в Экселе
  3. Вот, собственно говоря, и все. Работа над круговой диаграммой с процентным отображением данных завершена.Диаграмма с процентами в Экселе

Диаграмма Парето — что это такое, и как ее построить в Экселе

Итальянский инженер, экономист и социолог Вильфредо Парето выдвинул очень интересную теорию, согласно которой 20% наиболее эффективных предпринятых действий обеспечивают 80% полученного конечного результата. Из этого следует, что остальные 80% действий обеспечивают всего 20% достигнутого результата.

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

  1. Создаем таблицу, например, с наименованиями товаров. В одном столбце будет указан объем закупки в денежном выражении, в другом – полученная прибыль. Цель данной таблицы вычислить — закупка какой продукции приносит максимальную выгоду при ее реализации.Диаграмма Парето — что это такое, и как ее построить в Экселе
  2. Строим обычную гистограмму. Для этого нужно выделить область таблицы, перейти во вкладку «Вставка» и далее выбирать тип диаграммы.Диаграмма Парето — что это такое, и как ее построить в Экселе
  3. После того как мы это сделали, сформируется диаграмма с 2-мя столбиками разного цвета, каждая из которых соответствует данным разных столбцов таблицы.Диаграмма Парето — что это такое, и как ее построить в Экселе
  4. Следующее, что нужно сделать – это изменить столбик, отвечающий за прибыль, на тип “График”. Для этого выделяем нужный столбик и идем в раздел «Конструктор». Там мы видим кнопку «Изменить тип диаграммы», нажимаем на нее. В открывшемся диалоговом окне переходим в раздел «График» и кликаем по подходящему типу графика.Диаграмма Парето — что это такое, и как ее построить в Экселе
  5. Вот и все, что требовалось сделать. Диаграмма Парето готова.Диаграмма Парето — что это такое, и как ее построить в ЭкселеДалее, ее можно отредактировать точно так же, как мы рассказывали выше, например, добавить значения столбиков и точек со значениями на графике.Диаграмма Парето — что это такое, и как ее построить в Экселе
Читайте так же:
Где в интернет эксплорер свойства браузера

Заключение

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

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

Изучая статистику по коронавирусу (актуальному на момент написания статьи), я зашел на информационную страницу Яндекса с данными по заболеваниям и выздоровлениям и обнаружил там довольно интересную диаграмму:

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

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

Мне захотелось реализовать что-то подобное в Excel, но я с удивлением обнаружил, что стандартные инструменты для реализации условного форматирования диаграммы в нем отсутствуют. Да, можно вручную закрасить нужный нам столбец в определенный цвет, но если у нас много условий закрашивания или много столбцов в диаграмме — это будет довольно проблематично. Именно поэтому, я решил немного углубиться в эту тему и найти возможные способы, с помощью которых можно реализовать хоть какое-то «Условное форматирование» в диаграммах Excel.

Условное форматирование с помощью дополнительных столбцов.

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

На словах, возможно, звучит немного запутанно, но сейчас все покажу на примере.

Условное форматирование: расширение источника данных

Зеленым выделена наша основная таблица с данными. Справа от нее — 4 столбца, по которым распределяются исходные значения, в зависимости от попадания в определенный диапазон:

  • Значения менее 3000. Формула в ячейке C2: =ЕСЛИ( B2 <3000; B2 ;НД () )
  • Значения более 3000, но менее 5000. Формула в ячейке D2: =ЕСЛИ(И( B2 >=3000; B2 <5000);B2;НД () )
  • Значения более 5000, но менее 7000. Формула в ячейке E2: =ЕСЛИ(И( B2 >=5000; B2 <7000);B2;НД())
  • Значения более 7000. Формула в ячейке F2: =ЕСЛИ( B2 >=7000; B2 ;НД () )

Если значение не попадает в какой-либо диапазон, то в соответствующем столбце выводится ошибка #Н/Д. Это нужно для того, чтобы «неправильные» значения не отображались на диаграмме. Если вам мешаются ячейки с #Н/Д, то могу предложить несколько вариантов:

  1. Включить классическое условное форматирование для ячеек, которое будет изменять цвет шрифта ячеек с ошибками на белый. Таблица будет выглядеть опрятнее.
  2. После построения диаграммы, скрыть столбцы с дополнительной частью таблицы. Изначально, диаграмма не будет отображать скрытые данные, но это решается установкой галочки «Показывать данные в скрытых строках и столбцах» в настройках (при выборе источника данных для диаграммы).

После этого, вставляем новую диаграмму и источником выбираем всю нашу большую таблицу, исключая начальный столбец со значениями (чтобы не было задвоения данных на диаграмме). После этого, как я уже говорил, устанавливаем «Перекрытие столбцов» на 100% и получаем уже готовую таблицу, в которой уже реализовано условное форматирование.

Условное форматирование диаграммы с помощью дополнительной таблицы

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

Читайте так же:
Можно ли в ворде посчитать количество слов
Условное форматирование диаграммы с помощью VBA.

Второй вариант — использование макросов VBA. Нравится этот способ мне гораздо больше: не нужно строить лишние таблицы, выбирать новые источники данных в настройках и настраивать «корректный» вывод ошибок с «#Н/Д». Достаточно один раз подготовить код и использовать его по необходимости.

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

Все просто. Данный макрос подсвечивает диаграмму тремя цветами (по типу «Светофор»: красный, желтый и зеленый), в зависимости от принадлежности значения столбца диаграммы к определенному диапазону. Диапазонов, соответственно, тоже три и задаются они с помощью двух переменных: FirstValue и SecondValue (все значения меньше FirstValue, между FirstValue и SecondValue и больше SecondValue). Значения этих переменных задаются в макросе, точно так же, как и цвета.

При запуске данного макроса мы получим следующий результат:

Условное форматирование: результат выполнения макроса VBA

Все столбцы со значением менее 700000 были залиты красным цветом, со значением более 900000 — зеленым, а в диапазоне от 700000 до 900000 — желтым.

Если очень хочется, условное форматирование диаграммы через макрос можно усовершенствовать:

  • Добавить еще несколько условий
  • Добавить под новые условия новые цвета
  • Создать форму VBA, на которой можно будет самостоятельно выбирать цвета через палитру и задавать диапазоны условий, не изменяя код

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

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

Надстройка SHTEM для Excel.

Условное форматирование для диаграммы реализовано пока что только в тестовой версии надстройки SHTEM для Excel: с формой для ввода ограничений и несколькими заданными наборами цветов (в том числе и с градиентом). Сейчас функция тестируется на работоспособность в различных условиях и спустя некоторое время будет добавлена в основную версию надстройки.

Предварительный вариант инструмента условного форматирования диаграмм в тестовой версии надстройки выглядит следующим образом:

Условное форматирование в надстройке для Excel

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

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

Условное форматирование: заключение.

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

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