Как сделать разные цвета в диаграмме excel
Обновлено: 02.07.2024
Большая доля людей легче и проще воспринимают информацию, когда она представлена наглядно. Сделать это самостоятельно можно с помощью презентаций и отчётов с использованием диаграмм и графиков. Это незаменимая составляющая практически любой аналитической деятельности.
Но потребуется освоить некоторые нюансы, разобраться с принципами построения, внимательнее изучить функционал программы.
Графики и диаграммы требуют отдельного рассмотрения.
Построение графиков
Начать стоит с того, как можно построить графики функции и данных в Excel.
График отображает изменения данных. Чаще всего изменения связаны со временем. К примеру, компания получила за определённый временной промежуток ту или иную прибыль. Вариантов много. Но суть везде одинаковая.
Чаще всего осуществляется построение графиков, руководствуясь данными из таблицы программы Excel. Ведь Excel и является главным инструментом для работы с таблицами.
Чтобы создать график, нужно взглянуть на то, какие возможности предлагает Excel, и как ими правильно воспользоваться.
Самый простой график
Поскольку графики отображают изменения тех или иных данных, в них должна быть заложена определённая информация.
Есть исходная информация. И по ней нужно разобраться, как правильно построить график в Excel по этим данным. Он будет отображать события, происходящие на разных временных этапах.
К примеру, каждый год в вуз поступает определённое число абитуриентов. Теперь поэтапно о том, как добавить простейших график, и что вообще для этого предлагает программа Excel.
Здесь последовательность действий, следующая:
· открыть сначала вкладку «Вставка», где на выбор будет представлено несколько диаграмм;
· в всплывающем окне выбрать вид графика;
· при наведении курсора выплывает подсказка о том, где тот или иной тип лучше использовать;
· теперь нужно выбрать подходящий вариант;
· также в область диаграммы скопировать и вставить таблицу с исходной информацией.
Подтвердите действие, и дождитесь результата. Синяя горизонтально расположенная линия не нужна. Для её удаления достаточно выделить, и удалить.
Тут будет использоваться только одна кривая. Потому смело удаляйте информацию справа от графика. Она называется «легенда». Для уточнений сделайте подписи к маркерам. Для этого нужно открыть вкладку «Макет», и выбрать «Подписи данных». Здесь выберите место, где будут располагаться цифры.
Ещё больше усовершенствовать график можно через меню «Макет», и раздел «Название осей».
Заголовок перемещайте по своему усмотрению. Можно поменять стиль шрифта, выполнить заливку, изменить размеры. Всё это делается через вкладку «Название диаграммы».
Две и более кривых
Также можно вставить и видоизменить график через программу Excel, в котором будет от 2 и более кривых.
К примеру, нужно показать не только число поступивших в вуз, но ещё и количество выпускников за тот же период. То есть появляется уже больше исходной информации в таблице. Хотя принцип создания графика не меняется. Зато есть смысл не удалять легенду.
Добавить при необходимости дополнительную ось при одинаковых используемых единицах измерения можно по инструкции, которая уже была представлена выше. Если представленные данные разного типа, потребуется вспомогательная ось.
В этом случае рисовать соответствующие графики через приложение Excel нужно по такому алгоритму:
· постройте график так, словно используются одинаковые единицы измерения;
· выделите ось, куда нужно добавить ещё одну, вспомогательную;
· выберите вариант «Формат ряда данных», а затем перейдите в раздел «Параметры ряда»;
· тут нужно нажать на «По вспомогательной оси»;
· закрывайте окно, после чего должна появиться ещё одна ось.
У этого варианта есть альтернативное решение. Другой способ предусматривает, что можно изменить тип используемой диаграммы.
· кликнуть сначала правой кнопкой мышки по той линии, которой требуется дополнительная ось;
· затем нажать на «Изменить тип диаграммы для ряда»;
· выбрать тип второго ряда информации.
Подтвердите изменения, и наслаждайтесь видоизменённым графиком.
Как видите, здесь достаточно выполнить несколько кликов, и дополнительная ось сделана.
График функций
Есть и более сложный вариант, когда есть функция, и по ней нужно нарисовать или начертить соответствующий график.
Действуя пошагово, всю процедуру можно разделить на 2 этапа:
Сначала нужно составить таблицу. Есть условно 2 столбца. Это X и Y.
Есть условная формула Y=X(X-2) с шагом 0,5.
В первом столбце будет X. А значение для первой ячейки составит 1. Каждая последующая ячейка на 0,5 больше предыдущей. Нужно вставить формулу = (имя ячейки) + 0,5. Выделите теперь нижний правый угол ячейки, где формула, и тяните вниз. При этом ячейки будут автоматически заполняться значениями.
Второй столбец Y. Тут прописывается расчётная формула, которая используется для формирования графика функции. Поэтому нужно кликнуть на Enter. Программа посчитает все заданные значения. Формулу можно размножить по столбцу, потянув вниз угол ячейки. Всё, таблица есть.
Дальнейший алгоритм такой:
· используйте тот тип, который вам больше нравится;
· кликните по области вашей диаграммы с помощью правой кнопки мышки;
· теперь выделите значения из первого столбца, то есть Х, после чего нажмите на «Добавить»;
· при появлении окна с названием «Изменение ряда» задайте ему имя, а также для значения X первый столбец, а для Y второй;
· нажмите на ОК и посмотрите, что из всего этого получится.
Комбинирование и наложение
Нет ничего сложного в том, чтобы сделать сразу пару графиков в Excel. Для этого требуется на одном поле совместить 2 разных графика функций через приложение для работы с таблицами. Добавьте новую формулу к предыдущей. Допустим, это будет формула Z=X(X-3).
Выделите эти данные и вставьте в поле имеющейся диаграммы. Через соответствующее меню «Выбрать данные» можно отредактировать информацию, если есть какие-то неточности или несовпадения.
Всё, теперь пара графиков функций гармонично оказались в рамках одного поля.
Как сделать график зависимости
В этом случае информация из одной строки или столбца будет зависеть от исходных данных из другого столбца или строки.
Для начала выберите подходящий тип диаграммы. Хорошо смотрится точечный вариант с маркерами и гладкими кривыми. Но выбирать можете то, что вам больше нравится или подходит для конкретной задачи.
Теперь выполняется выбор данных, нажмите на кнопку «Добавить». Здесь можно придерживаться такого принципа:
· теперь имя ряда будет В, а значение Х это данные из столбца В;
По соответствующему принципу строится вся остальная таблица.
Насколько это легко или сложно, каждый пользователь определяет самостоятельно. Стоит попробовать, попрактиковаться, рассмотреть несколько наглядных примеров. Чем больше опыта и знаний, тем проще работать с графиками через программу Excel.
Построение диаграмм
Помимо графиков, существуют ещё и диаграммы, также упрощающие восприятие той или иной информации. Особенно это касается данных с числовыми значениями. Их можно сравнивать между собой, сопоставлять.
Есть несколько вариантов того, как можно создать диаграммы в Excel. Они могут отображать разные значения в том виде, в котором их удобно воспринимать пользователям. Потому применяются всевозможные варианты. Включая точечные и динамические графические диаграммы в программе для работы с таблицами Excel.
Диаграмма по исходной таблице
Для начала о том, как сделать наглядную диаграмму, используя данные из таблицы Excel.
Здесь нужно выполнить несколько пошаговых процедур:
· для начала создать таблицу, где будут представлены нужные данные, на своё усмотрение;
· далее выделить область со значениями, на основе которой сформируется диаграмма;
· выбрать вкладку «Вставка» и нажать на один из понравившихся типов диаграммы;
· как вариант, выберите гистограмму, а затем один из её вариантов;
· после выбрать автоматически появляется презентация.
Вообще сделать самостоятельно такую диаграмму в Excel, работая по таблице, не сложно. Но автоматически созданная презентация может не устраивать в полной мере. Нужно внести изменения.
· кликните дважды по названию, и пропишите нужное вам имя для гистограммы;
· теперь поменяйте подпись вертикальной оси;
· тут следует зайти во вкладку «Макет», далее в «Подписи», и выбрать «Название осей»;
· кликните по вертикальной оси, и выберите её название;
· если в таблице данных немного, запись справа, то есть легенду, можно смело убрать, нажав на неё и кликнув Delete;
· ещё можно заменить стандартные стили и цвета через вкладку «Конструктор» и меню «Стили диаграмм».
Диаграмма полностью редактируется, что позволяет придать ей тот вид, который нужен пользователю. При желании можно сделать круговую диаграмму, точечную, линейчатую и пр. В этом плане программа Excel довольно мощная и функциональная.
Добавление данных в диаграмму
Бывает такая ситуация, когда есть готовая диаграмма, но в неё через программу Excel нужно добавить новые данные.
Это не сложно. Инструкция выглядит следующим образом:
· к исходной таблице добавьте новое значение, пропишите соответствующие цифры, придумайте название;
· теперь выделите диапазон с новыми данными, захватив при этом и название;
· выделите созданную ранее диаграмму;
· вставьте выделенный на предыдущем этапе фрагмент из обновлённой таблицы.
Поскольку новые данные в графике не совсем понятные, тут на помощь приходит легенда. Для этого нужно открыть вкладку «Макет», нажать на меню «Легенда», и здесь добавить её справа, снизу, сверху или использовать иной подходящий вариант.
Легенда в сочетании с разными цветами гистограммы даёт наглядно понять, где какие данные, как они связаны друг с другом. Всё понятно и визуально легко воспринимается.
Хотя есть и альтернативный способ, чтобы добавить данные в уже созданную диаграмму. Он более сложный. Тут используется меню «Выбор источника данных». Чтобы открыть его, нужно кликнуть правой кнопкой мышки, и нажать на «Выбрать данные». Нажимая на кнопку «Добавить», появится строка, через которую можно будет выбрать диапазон данных.
Как оси поменять местами
Бывают и такие ситуации, когда требуется поменять местами разные оси в созданной диаграмме через программу Excel.
Сделать это не сложно. Просто нужно отталкиваться от следующей инструкции:
· выберите диаграмму, и кликните по ней правой кнопкой;
· появится новое меню, где кликните по варианту «Строка/столбец».
Теперь параметры для категорий и рядов автоматически поменяются между собой местами.
Элементы управления
Также Excel позволяет закрепить элементы управления, которые будут располагаться на созданной диаграмме.
Актуально в тех случаях, когда в гистограмму регулярно добавляются новые сведения. И всякий раз менять диапазон не особо удобно.
Самым оптимальным решением в такой ситуации станет динамическая диаграмма. То есть она сможет автоматически обновляться. Чтобы за гистограммой были закреплены элементы управления, нужно преобразовать область данных в так называемую умную таблицу.
Тут от пользователя потребуется сделать следующее:
· в главном меню нажать на «Форматировать как таблицу»;
· появится новое окно, где можно выбрать любой стиль на свой вкус;
· согласиться с предложением программы выбрать диапазон;
· при вводе новых значений параллельно начнёт меняться и сама диаграмма, то есть станет динамической.
Так используются готовые данные, преобразуемые в умную таблицу. Либо можно сделать всё с нуля, выбрав меню «Вставка», и далее через раздел «Таблица» создать новые параметры.
Диаграмма в процентах
Некоторая информация легче и удобнее воспринимается, если она представлена не просто в цифрах, а в процентах.
Для таких задач лучше использовать круговой тип диаграммы.
Взяв определённые исходные данные, необходимо:
· далее нажать на «Круговая» и выбрать объёмный тип;
· во вкладке «Конструктор» кликнуть на «Макеты диаграммы»;
· тут среди вариантов предусмотрено несколько стилей с процентами;
Если проценты маленькие, эти сектора могут плохо просматриваться. Для их выделения через конструктор нужно поменять тип диаграммы, и выбрать круговую со вторичной.
Когда процентная диаграмма создаётся автоматически, зачастую она в полной мере не устраивает пользователя. Потому тут можно кликнуть по любому из секторов правой кнопкой, после чего появляются точки-границы. Через контекстное меню выберите «Формат ряда данных». Теперь для ряда задаются нужные параметры, и изменения сохраняются.
Диаграмма Ганта
Отдельного внимания заслуживает диаграмма, с помощью которой можно отобразить в виде столбцов данные многоэтапного события. Красиво и совсем не сложно.
Допустим, что диаграмма для учителя, который следит за сроками сдачи отчётов.
Сделать нужно следующее:
· добавить для диаграммы столбец, отображающий количество дней;
· выделите диапазон, в котором будет располагаться создаваемая диаграмма Ганта;
· ячейки здесь будут иметь заливку разного цвета между датой начала и окончанием сроков сдачи отчётов;
· нажмите на «Создать правило» и затем кликните на пункт «Использовать формулу для определения ячеек»;
· тут диаграмма, как оператор, будет сравнивать дату для конкретной ячейки с датами начала и завершения мероприятия;
· теперь нажмите на «Формат» и выберите подходящие цвета для заливки.
Проверяйте, устраивает ли вас результат в полной мере.
Выполнить такую задачу не сложно. Но свои нюансы в работе с графиками и диаграммами через программу Excel есть.
Приходилось ли вам работать с графиком или диаграммой? Для чего они нужны были? Какие трудности возникли? Что можете посоветовать новичкам в этом деле?
Делитесь опытом и оставляйте свои комментарии. Подписывайтесь, задавайте вопросы и рассказывайте о нашем проекте своим друзьям!
Изменение цвета столбца/гистограммы в зависимости от значения
Метод 1. Измените цвет столбца/гистограммы в зависимости от значения с помощью формул и встроенной функции диаграммы
Метод 2: изменение цвета столбца диаграммы в зависимости от значения с помощью удобного инструмента
Изменение цвета линейной диаграммы в зависимости от значения
Загрузить файл образца
Изменить цвет столбца/гистограммы в зависимости от значения
Метод 1. Измените цвет столбца диаграммы в зависимости от значения с помощью формул и встроенной функции диаграммы.
Во-первых, вам необходимо создать данные, как показано на скриншоте ниже. как показано, перечислите каждый диапазон значений, а затем рядом с данными вставьте диапазон значений в качестве заголовков столбцов.
р>
1. В ячейке C5 введите эту формулу
Затем перетащите маркер заполнения вниз, чтобы заполнить ячейки, затем продолжайте перетаскивать маркер вправо.
2. Затем выберите имя столбца, удерживая клавишу Ctrl , выберите ячейки формулы, включая заголовки диапазона значений.
3. нажмите Вставить > Вставить столбец или гистограмму , выберите Clustered Column или Cluster Bar по мере необходимости.
Затем диаграмма была вставлена, и диаграмма цвета различаются в зависимости от значения.
Иногда использование формулы для создания диаграммы может вызвать некоторые ошибки, поскольку формулы неверны или удалены. Теперь вам может помочь инструмент Изменить цвет диаграммы по значению из Kutools for Excel .
Бесплатная загрузкаБесплатная полнофункциональная версия через 30 дней
После бесплатной установки Kutools for Excel, сделайте следующее:
1. Нажмите Kutools > Диаграммы > Изменить цвет диаграммы по значению . См. Снимок экрана:
2. В открывшемся диалоговом окне выполните следующие действия:
1) Выберите нужный тип диаграммы, затем выберите метки осей и значения серий отдельно, кроме заголовков столбцов.
2) Затем нажмите кнопку Добавить , чтобы добавить нужный диапазон значений.
3) Повторите шаг выше, чтобы добавить все диапазоны значений в список Группа . Затем нажмите Ok .
Совет :
1. Вы можете дважды щелкнуть столбец или полосу, чтобы отобразить панель Форматировать точку данных , чтобы изменить цвет.
Выберите гистограмму или столбчатую диаграмму, затем нажмите Kutools > Диаграммы > Цветовая диаграмма по значению . Затем в появившемся диалоговом окне установите необходимый диапазон значений и относительный цвет. Нажмите, чтобы бесплатно скачать сейчас!
Изменить цвет линейной диаграммы в зависимости от значения
Если вы хотите вставить линейную диаграмму с разными цветами на основе значений, вам понадобится другая формула.
Во-первых, вам нужно создать данные, как показано на скриншоте ниже, перечислить каждый диапазон значений, а затем рядом с данными вставить диапазон значений в качестве заголовков столбцов.
Примечание : значение серии должно быть отсортировано от A до Z.
1 . В ячейке C5 введите эту формулу
Затем перетащите маркер заполнения вниз, чтобы заполнить ячейки, затем продолжайте перетаскивать маркер вправо.
3. Выберите диапазон данных, включая заголовки диапазона значений и ячейки формулы, см. Снимок экрана:
4. нажмите Insert > Insert Line или Area Chart , выберите тип Line .
Теперь линейная диаграмма создана с разными цветовыми линиями по значениям.
Загрузить файл образца
Нажмите, чтобы загрузить образец файла.
Другие операции (статьи), связанные с диаграммой
Динамическое выделение точки данных на диаграмме Excel
Если диаграмма с несколькими сериями и большим количеством данных, нанесенных на нее, будет трудно читать или находить только релевантные данные в одной серии, которую вы используете.
Создание интерактивной диаграммы с флажком выбора серии в Excel
В Excel мы обычно вставляем диаграмма для лучшего отображения данных, иногда диаграмма с выбором нескольких серий. В этом случае вы можете захотеть показать серию, установив флажки.
Гистограмма с условным форматированием в Excel
В этом руководстве показано, как создать столбчатую диаграмму с накоплением условного форматирования, как показано на снимке экрана ниже, шаг за шагом в Excel.
Пошаговое создание диаграммы фактического и бюджета в Excel.
В этом руководстве рассказывается, как создать столбчатую диаграмму с накоплением условного форматирования, как показано на скриншоте ниже, шаг за шагом в Excel.
Предположим, что мы построили простую гистограмму для наглядного отображения значений по нескольким категориям - например прибыли по городам-филиалам:
Задача: выделить цветом нужный столбец(ы) по заданному условию, например город с максимальными продажами или город выбранный в выпадающем списке и т.д. Причем сделать это не вручную поменяв цвет заливки столбца, а "на автопилоте", т.к. завтра данные поменяются и подсветка должна также обновиться. Другими словами, нужно реализовать что-то типа условного форматирования, но не для ячеек, а для столбцов в диаграмме.
Суть способа
Идея, на самом деле, простая. Добавим к нашей таблице с данными еще один вспомогательный столбец:
Обратите внимание, что:
Если теперь построить гистограмму по такой таблице или добавить вспомогательный столбец как новый ряд в имеющуюся диаграмму, то мы увидим прежнюю картинку, но нужный нам столбец будет задублирован в новом ряду данных:
Теперь главное: щелкаем правой кнопкой мыши по любому столбцу, выбираем команду Формат ряда данных (Format Data Series) и в появившемся окне двигаем ползунок Перекрытие рядов (Overlap) на 100%:
 |  |
Дубликат столбца наедет на исходный, полностью его перекрывая, но, по факту, выглядеть это будет как подсветка заданной колонки. А ячейки, в которых есть любые ошибки на диаграммах не отображаются. Вместо НД также можно выводить пустую строку "" - эффект примерно тот же.
Ну, а если использовать в дополнительном столбце формулы, то можно реализовать подсветку по любым критериям - простор для фантазии тут большой. Давайте разберем несколько примеров для затравки.
Подсветка столбца в диаграмме с выбором из выпадающего списка
Предположим, что мы сделали выпадающий список в ячейке G2 для выбора нужного города с помощью классической команды Данные - Проверка данных (Data - Data Validation) и указали в качестве источника наш список городов A2:A15:
Подсветка самого большого или маленького столбца
Очень похоже на предыдущий способ, но функцией ЕСЛИ по очереди проверяем- равно ли значение продаж для каждого города максимальному по столбцу:
Само-собой, можно функцию МАКС заменить на МИН.
Подсветка столбцов выше/ниже среднего
А если заменить в предыдущей формуле знак равно на больше или меньше, а функцию МАКС на СРЗНАЧ (AVERAGE) , то можно подсвечивать все города, где продажи были выше или ниже среднего по стране:
Подсветка Top-3
Если вместо функции МАКС, которая находит только одно самое большое значение, использовать функцию НАИБОЛЬШИЙ (LARGE) , то можно легко подсветить Top-3:
Изучая статистику по коронавирусу (актуальному на момент написания статьи), я зашел на информационную страницу Яндекса с данными по заболеваниям и выздоровлениям и обнаружил там довольно интересную диаграмму:
Условное форматирование с помощью дополнительных столбцов.
На словах, возможно, звучит немного запутанно, но сейчас все покажу на примере.
- Значения менее 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 ;НД () )
Далее, если Вам необходимо, изменяете ширину столбцов и цвет каждой группы данных (цвет каждого столбца изменять не нужно). Такое условное форматирование динамично: не нужно каждый раз перестраивать дополнительную таблицу, достаточно просто заменить значения в исходной и столбцы все перестроятся и перекрасятся.
Условное форматирование диаграммы с помощью VBA.
Есть несколько алгоритмов для закрашивания столбцов диаграммы с помощью макросов на VBA , но ниже я приведу пример того, который я считаю наиболее оптимальным и удобным. В нем мы напрямую обращаемся к столбцам диаграммы и соответствующим значениям, не затрагивая сам источник данных (то есть нам не важно, где расположена сама таблица со значениями):
При запуске данного макроса мы получим следующий результат:
Если очень хочется, условное форматирование диаграммы через макрос можно усовершенствовать:
- Добавить еще несколько условий
- Добавить под новые условия новые цвета
- Создать форму VBA, на которой можно будет самостоятельно выбирать цвета через палитру и задавать диапазоны условий, не изменяя код
Также, на лист с диаграммой можно добавить обработку события (автоматическое выполнение кода при определенном действии пользователя), которое будет автоматически запускать закрашивание диаграммы, при изменений значений в источнике данных. В таком случае, условное форматирование становится динамичным и отпадает необходимость каждый раз запускать макрос, достаточно просто изменить значение в исходной таблице. В примере, который можно скачать в конце статьи, оно реализовано следующим образом:
Надстройка SHTEM для Excel.
Условное форматирование для диаграммы реализовано пока что только в тестовой версии надстройки SHTEM для Excel: с формой для ввода ограничений и несколькими заданными наборами цветов (в том числе и с градиентом). Сейчас функция тестируется на работоспособность в различных условиях и спустя некоторое время будет добавлена в основную версию надстройки.
Предварительный вариант инструмента условного форматирования диаграмм в тестовой версии надстройки выглядит следующим образом:
Диапазоны для градиента могут заполняться автоматически (числовой ряд будет делиться на 5 равных отрезков). Возможно, чуть изменю цвета и добавлю еще несколько вариантов градиента, а также добавлю возможность выбирать пользовательские цвета из палитры.
Напомню, что скачать надстройку может абсолютно любой желающий: изначально дается тестовый период на 30 дней, а затем, если необходимо, я могу выдать вам код для активации неограниченного доступа.
Условное форматирование: заключение.
Читайте также: