Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.
Задача: объект DataLabel (метка подписи данных на диаграмме) не имеет, ни собственной высоты, ни ширины, и Excel не позволяет пользователю изменять его размер. В некоторых ситуациях вам может потребоваться определить эти свойства. Например, когда подписи наезжают друг на друга (рис. 1).
Рис. 1. Вы хотите узнать высоту и ширину объекта DataLabel для точки (210; 414)
Скачать заметку в формате Word или pdf, примеры в формате Excel (с кодом VBA)
Справочная информация: решение этой проблемы основано на том, что невозможно переместить подписи данных (а также любой иной элемент диаграммы, такой как легенда, название диаграммы, название оси и т.д.) даже частично за область диаграммы. Чтобы убедиться в этом, выберите любой такой элемент (двойным кликом) и попробуйте перетащить его за нижний правый угол. У вас не получится (рис. 2). Начало координат (0,0) для диаграммы – верхний левый угол. Используются свойства диаграммы в VBA: Wd =ChartArea.Width, Ht = ChartArea.Height. Значения X и Y можно вычислить на основе свойств объекта DataLabel: h = Ht – y, w = Wd – x.
Рис. 2. Ситуация, когда метка подписи данных перемещается в нижний правый угол области диаграммы
Решение: выделите диаграмму и используйте следующий код (см. также соответствующий модуль VBA в прикрепленном Excel-файле):
Sub FindLblSize()
Dim Lbl As DataLabel
Set Cht = ActiveChart
Set Lbl = Cht.SeriesCollection(1).Points(3).DataLabel
‘Get height and width of the chart area
ChartWd = Cht.ChartArea.Width
ChartHt = Cht.ChartArea.Height
‘Store old position of data label
OldTop = Lbl.Top
OldLeft = Lbl.Left
‘Attempt to move data label so that top left corner
‘coincides with bottom right corner of chart area
Lbl.Top = ChartHt
Lbl.Left = ChartWd
‘Above move makes bottom right corner of data label
‘to coincide with bottom right corner of chart area
‘as it cannot be moved any further
‘Calculate and display the label dimensions
LblWd = ChartWd — Lbl.Left
LblHt = ChartHt — Lbl.Top
MsgBox "
Label dimensions: Width = "
& LblWd & "
pts Height = "
& LblHt & "
pts"
‘Move label to a slightly staggered position to remove overlap
Lbl.Left = OldLeft
Lbl.Top = Cht.SeriesCollection(1).Points(2).DataLabel.Top — LblHt
End Sub
После выполнения кода подписи данных займут более корректное положение (рис. 3).
Рис. 3. Расположение меток подписей данных улучшено
Резюме: Excel ограничивает положение элементов диаграммы ее границами. Сам по себе предложенный код, по-видимому, не очень полезен, но проиллюстрированная техника могли бы лечь в основу более широкого метода анализа перекрывающихся меток данных и исправления ситуации.
Добрый день! Очень интересный метод. Я попытался на его основе построить область (прямоугольник), привязанную к меткам оси Y. Но, к сожалению, не удалось точно получить реальные координаты меток (они меняются в зависимости от графика). Может у вас есть какие-то идеи на эту тему? Было бы здорово! Спасибо!