Цвет диаграммы из ячеек с ее данными

Постановка задачи

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

chart-colors-from-cells1.png

Предвосхищая удивленно-возмущенные крики отдельных товарищей, надо отметить, что, конечно же, цвет заливки на диаграмме можно менять и вручную (правой кнопкой по столбцу - Формат точки/ряда данных (Format data point/series) и т.д. - никто не спорит. Но на практике случается куча ситуаций, когда проще и удобнее сделать это непосредственно в ячейках с данными, а диаграмма потом должна перекраситься уже автоматически. Попробуйте, например, задать заливку по регионам для столбцов на этой диаграмме:

chart-colors-from-cells3.png

Думаю, вы поняли идею, да?

Решение

Ничем, кроме как макросом, такое реализовать не получится. Поэтому открываем Редактор Visual Basic с вкладки Разработчик (Developer - Visual Basic Editor) или нажимаем сочетание клавиш Alt+F11, вставляем новый пустой модуль через меню Insert - Module и копируем туда текст вот такого макроса, который и будет делать всю работу:

Sub SetChartColorsFromDataCells()

    If TypeName(Selection) <> "ChartArea" Then
        MsgBox "Сначала выделите диаграмму!"
        Exit Sub
    End If
    Set c = ActiveChart
    For j = 1 To c.SeriesCollection.Count
        f = c.SeriesCollection(j).Formula
        m = Split(f, ",")
        Set r = Range(m(2))

        For i = 1 To r.Cells.Count
            c.SeriesCollection(j).Points(i).Format.Fill.ForeColor.RGB = _
                r.Cells(i).Interior.Color
        Next i
    Next j
End Sub

Теперь можно закрыть Visual Basic и вернуться в Excel. Использовать созданный макрос очень просто. Выделите диаграмму (область диаграммы, а не область построения, сетку или столбцы!):

chart-colors-from-cells2.png

и запустите наш макрос с помощью кнопки Макросы на вкладке Разработчик (Developer - Macros) или с помощью сочетания клавиш Alt+F8. В том же окне можно, в случае частого использования, назначить макросу сочетание клавиш с помощью кнопки Параметры (Options).

P.S.

Единственной ложкой дегтя остается невозможность применения подобной функции для случаев, когда цвет ячейкам исходных данных назначается с помощью правил условного форматирования. К сожалению, Visual Basic не имеет встроенных средств для считывания таких цветов. Есть, конечно, определенные "костыли", но работают они не для все случаев и не во всех версиях.

Ссылки по теме


30.09.2013 09:46:19
Сохранил макрос в общей книге макросов, вывел кнопку на ленту. Супер!
03.10.2013 17:11:31
Николай, большое спасибо за статью и макрос! То, что делал за 20 минут, теперь делаю за 1! :D А удовлетворенность Вашим курсом "визуализация в excel" теперь перевалила за 100% :D
03.10.2013 21:44:23
Давно надо было что-то подобное написать, все руки не доходили... :)
17.10.2013 11:30:20
Вот только у меня легенда получается не в цвет.
28.10.2013 14:35:13
Боюсь, что это не исправить, т.к. легенда отображает только общий цвет всех элементов ряда, а мы их в разные цвета макросом как раз и перекрашиваем.
17.12.2013 11:57:16
Николай, здравствуйте!
В моем случае - градиентная заливка и при выполнении макроса сегменты круговой диаграммы перекрашиваются несовсем в те цвета, которые необходимо. Не могли бы Вы посмотреть в чем здесь дело?
04.01.2014 11:05:30
Лучше не использовать градиентные заливки для подобного трюка. Они совершенно по-другому задаются в VBA.
03.01.2014 09:08:08
Единственной ложкой дегтя остается невозможность применения подобной функции для случаев, когда цвет ячейкам исходных данных назначается с помощью правил условного форматирования.
Если 2010 и выше, то можно.Строку -
r.Cells(i).Interior.Color
заменить на
r.Cells(i).DisplayFormat.Interior.Color
04.01.2014 11:04:36
Да, спасибо за уточнение - в новых версиях (2010, 2013) это выручает.
03.04.2014 12:44:13
Добрый день!

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

Это можно настроить?
19.04.2014 11:04:55
Надо вешать макрос на изменение ячейки фильтра. Щелкнуть правой по ярлычку листа со сводной таблицей и диаграммой - Исходный текст и вставить туда примерно такое:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "B1" Then Call SetChartColorsFromDataCells
End Sub 
Где B1 - адрес ячейки с фильтром сводной
13.05.2014 13:54:45
Не вышло с макросом. Пишет "Run-time error '1004' Недопустимый параметр.
Ругается на эти строки
c.SeriesCollection(j).Points(i).Format.Fill.ForeColor.RGB = _
r.Cells(i).DisplayFormat.Interior.Color

В чём может быть причина?
14.05.2014 09:42:29
А версия Excel какая?
14.05.2014 21:23:43
Потыкался, перезапустил, пошло, не для всех диаграмм выходит.
2010 Excel
27.04.2015 12:37:51
Не для всех и должно. На сложных типах может не работать - надо переписывать код.
30.06.2015 08:09:02
Николай, добрый день! Спасибо за отличный макрос! и, хочу сказать, что тоже столкнулась с проблемой , что легенда не меняет цвета.
Уточнить хочу, проблема так и не возможно решить?((
20.10.2015 17:19:11
Николай, добрый день! Спасибо Вам за статью.
У меня вопрос не совсем по теме, но по вашему примеру :)
я мучаюсь с эффектом постепенного выцветания, которая у вас представлена в колонках 2013-2011. Я билась с цветом, но сразу все 4 цвета изменить не получается. Подскажите пожалуйста как добиться такого перехода?
Спасибо большое
Мария
07.03.2016 20:51:19
Можно сделать так, чтобы макрос раскрашивания точек запускался автоматически при изменении данных диаграммы. Недавно делал такое:
http://www.cyberforum.ru/vba/thread1674164.html
11.03.2016 16:48:19
Добрый день! Макрос Ваш хорош (на простых диаграммах работает), правда у меня эксель выбивает ошибку (Run-time error "1004";) - Method "Range" of object"_Global" failed. Ругается на строчку - Set r = Range(m(2)). Буду очень благодарен за помощь? Excel 2013, данные для диаграммы с фильтром) Спасибо
14.07.2016 16:36:17
Николай, добрый день!

Подскажите, есть ли возможность сделать шаблон чтобы определенному значению соответствовал определенный цвет.
Например, яблоки - зелёный, апельсин - оранжевый и т.д. Мне приходится делать очень много диаграмм, где значения (яблоки, апельсины) повторяются и цвет каждый раз ввожу вручную. Помогите, пожалуйста! Интересует именно круговая диаграмма.
12.02.2017 21:10:35
В excel 2010 нарушает структуры легенды. До применения макроса так:



После почему-то вот так: