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

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

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

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) это выручает.
24.03.2017 13:33:54
А что делать для 2007?
26.03.2017 20:34:30
Там не было свойства DisplayFormat в VBA в принципе :(
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 нарушает структуры легенды. До применения макроса так:



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


24.03.2017 12:40:25
Попытался выполнить данный макрос в  excel,  дольки на круговой гистограмме стали прозрачными. В чем может быть причина?
В ячейках применяется условное форматирование.

26.03.2017 20:35:46
Цвет условного форматирования в Excel 2007 и более древних версиях через VBA считать нельзя, к сожалению.
19.05.2017 08:52:15
Здравствуйте, я сделала сводный график, у меня не получается, выделяю сам график и запускаю макрос, но не получается, измененй нет.

Николай, подскажите можно ли это исправить и сделать так что бы это работало?

А так же что бы не на значениях (цифры), а на название компаний например.

ООООчень надо )))
спасибо.
19.05.2017 10:02:13
И мне нужно в точечном графике
09.10.2017 12:57:15
Николая, добрый день. Скажите пожалуйста, а можно изменить макрос, чтобы цвет брался не из цвета ячейки, а цвета текста? И например, если это график , а не гистограмм, то чтобы линия графика меняла цвет (сейчас цвет меняет маркер )
26.08.2018 16:31:00
благодарю! получилось вставить, все красиво!))
30.04.2019 14:25:12
Я вам очень благодарна! Спасибо
05.11.2019 11:33:42
Добрый день. На линейном графике не работает.
В то же время, если поменять линейный график на 3D линейный - начинает работать.

Но нужен именно линейный.
В общем, не понятно "куда копать"(
11.12.2019 16:21:16
Интересно, а есть ли возможность сделать заливку ячеек excel автоматически с изменением тона на один шаг? Ведь если посмотреть приведённый пример с уровнем смертности, залить разным цветом все ячейки тоже будет ой как не просто...
20.01.2021 22:30:14
Спасибо!

Ваш макрос вернул мне программисткие навыки и несказанно помог.

Увидел что возник вопрос как сделать так чтобы легенда раскрашивалась в те же самые цвета цвета и решил зарегистрироваться чтобы поделиться лайф хаком. Мне помогло покрасить заголовки таблицы откуда берутся данные для легенды в эти же цвета и вуаля, легенда соответствует значениям
14.05.2021 11:13:25
Excel 2016. Method `Range` of object `_Global` failed.
03.01.2023 15:46:01
Добрый день, а как поменять название диаграммы, чтобы потом можно было сразу в один клик по макросу применить окрашивание? Значит нужно и в коде имя прописать то же самое... Как это сделать?
05.01.2023 14:28:57
Наверх