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

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

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

Выделите точки данных в линейной диаграмме Excel

Вот как вы можете создать этот тип диаграмм:

  • Получите данные на месте. Для этого графика у меня есть данные о росте выручки за каждый квартал 2012-2015 годов.
  • Выделите все данные, перейдите в Вставка -> Графики -> Линия с маркерами. Это позволит вставить линейную диаграмму с тремя разными линиями для каждого года.
  • Перейдите в Вставка -> Иллюстрации -> Фигуры -> Прямоугольник со скругленными углами. Щелкните в любом месте листа, и он вставит прямоугольник с закругленными углами на лист.
  • Вставьте еще 2 прямоугольника со скругленными углами и поместите их на диаграмму. Введите название серии (годы) в формы, как показано ниже:
  • Выберите прямоугольник для 2013 года, перейдите в поле «Имя» и введите 2013. Аналогичным образом сделайте то же самое для полей 2014 и 2015 (поле имени находится слева от строки формул).
  • В ячейке F2 введите 2013 (вы можете ввести любой год из данных).
  • В ячейке F3 введите следующую комбинацию функций INDEX, ROWS и MATCH (и перетащите ее для ячеек F3: F6)
  • =INDEX($B$3:$D$6,ROWS($E$3:E3),MATCH($F$2,$B$2:$D$2,0)) 
  • Выделите ячейки F3: F6 и скопируйте их (нажмите Control + C), выберите диаграмму и вставьте (Ctrl + v). Это создаст две строки для одного и того же года (при копировании обратите внимание, что цвет линии выбранного года изменится).
  • Выберите строку года (для которого вы скопировали данные), щелкните правой кнопкой мыши и выберите «Форматировать данные ряда». В серии данных формата:
    • Измените цвет линии на «Нет линии»
    • В параметрах маркера внесите следующие изменения
      • Встроенный тип: круглая форма
      • Встроенный размер: 15
    • Измените заливку маркера на Без заливки
    • Изменить цвет границы маркера (я использовал красный цвет), ширину и тип тире
    • Щелкните правой кнопкой мыши любой из круглых маркеров и выберите «Добавить метки данных». Отформатируйте его, чтобы показать проценты

Если вы выполнили все вышеперечисленные шаги, у вас будет что-то, как показано ниже:

Теперь, чтобы кнопки работали, мы будем использовать простой код VBA.

Код VBA

Мы будем использовать код VBA для двух вещей:

  • Измените значение года в ячейке F3 при щелчке по фигуре и
  • Изменить цвет выбранной формы

Просто скопируйте следующий код в редактор VB.

Sub SelectYear2013()
Range("F2").Value = 2013
ActiveSheet.Shapes("2013").Fill.ForeColor.RGB = RGB(176, 196, 222)
ActiveSheet.Shapes("2014").Fill.ForeColor.RGB = RGB(255, 255, 255)
ActiveSheet.Shapes("2015").Fill.ForeColor.RGB = RGB(255, 255, 255)
End Sub

Sub SelectYear2014()
Range("F2").Value = 2014
ActiveSheet.Shapes("2013").Fill.ForeColor.RGB = RGB(255, 255, 255)
ActiveSheet.Shapes("2014").Fill.ForeColor.RGB = RGB(176, 196, 222)
ActiveSheet.Shapes("2015").Fill.ForeColor.RGB = RGB(255, 255, 255)
End Sub

Sub SelectYear2015()
Range("F2").Value = 2015
ActiveSheet.Shapes("2013").Fill.ForeColor.RGB = RGB(255, 255, 255)
ActiveSheet.Shapes("2014").Fill.ForeColor.RGB = RGB(255, 255, 255)
ActiveSheet.Shapes("2015").Fill.ForeColor.RGB = RGB(176, 196, 222)
End Sub

Чтобы скопировать этот код:

  • Нажмите Alt + F11. Откроется редактор VBE.
  • Перейдите во вкладку «Вставить» и нажмите «Модуль». Это вставит модуль.
  • Дважды щелкните значок модуля и вставьте код в область кода справа.

Назначьте макросы кнопкам

Когда у вас есть код VBA, вам нужно назначить макросы кнопкам / фигурам. Сделать это:

  • Щелкните фигуру правой кнопкой мыши и выберите «Назначить макрос».
  • В диалоговом окне «Назначить макрос» выберите макрос и нажмите «ОК».

Примечание. Поскольку эта книга содержит макрос, сохраните его как файл формата .xlsm или .xls.

Теперь ваша динамическая диаграмма готова. Теперь одним щелчком мыши можно выделить точки данных для выбранной серии.

Динамическое построение диаграмм — выделение точек данных в Excel одним нажатием кнопки