Изменение формата подписей данных в диаграмме. Вставить в письмо подпись из Outlook через VBA

О чём мы думаем в первую очередь после создания диаграммы в Excel? О том, как придать диаграмме именно такой облик, какой мы себе представляли, когда брались за дело!

В современных версиях Excel 2013 и 2016 настройка диаграмм – это просто и удобно. Корпорация Microsoft приложила немало усилий, чтобы сделать процесс настройки простым, а необходимые параметры легко доступными. Далее в этой статье мы покажем несколько простых способов, как добавить и настроить все основные элементы диаграммы в Excel.

3 способа настраивать параметры диаграмм в Excel

  1. Выделить диаграмму и использовать вкладки из группы Работа с диаграммами (Chart Tools) – Конструктор (Design) и Формат (Format).
  2. Кликнуть правой кнопкой мыши по элементу диаграммы, который необходимо настроить, и выбрать нужную команду из контекстного меню.
  3. Использовать специальные иконки, которые появляются возле правого верхнего угла диаграммы, если кликнуть по ней мышью.

Ещё больше параметров находится в панели Формат области диаграммы (Format Chart), которая появляется в правой части рабочего листа, если нажать пункт Дополнительные параметры (More options) в контекстном меню диаграммы или на вкладках группы Работа с диаграммами (Chart Tools).

Совет: Чтобы сразу же открыть нужный раздел панели для настройки параметров диаграммы, дважды кликните по соответствующему элементу на диаграмме.

Вооружившись этими базовыми знаниями, давайте посмотрим, как можно изменять различные элементы диаграммы в Excel, чтобы придать ей именно такой облик, какой мы хотим видеть.

Как добавить название к диаграмме Excel

В этом разделе мы расскажем о том, как добавить название к диаграмме в различных версиях Excel, и покажем, где находятся основные инструменты для работы с диаграммами. В остальной части статьи мы будем рассматривать примеры работы только в самых новых версиях Excel 2013 и 2016.

Добавляем название к диаграмме в Excel 2013 и Excel 2016

В Excel 2013 и Excel 2016 при создании диаграммы в её названии автоматически вставляется текст “Название диаграммы “. Чтобы изменить этот текст, просто выделите его и введите свой вариант названия:

Можно также связать название диаграммы с какой-либо ячейкой на листе при помощи ссылки, чтобы название автоматически обновлялось каждый раз при изменении содержимого связанной ячейки. О том, как это сделать, рассказано.

Если название по каким-то причинам не было добавлено автоматически, то кликните в любом месте диаграммы, чтобы появилась группа вкладок Работа с диаграммами (Chart Tools). Откройте вкладку Конструктор (Design) и нажмите Добавить элемент диаграммы (Add Chart Element) > Название диаграммы (Chart Title) > Над диаграммой (Above Chart) или По центру (наложение) (Centered Overlay).

Либо нажмите иконку Элементы диаграммы (Chart Elements) возле правого верхнего угла диаграммы и отметьте галочкой параметр Название диаграммы (Chart Title).

Рядом с параметром Название диаграммы (Chart Title) можно нажать стрелку, направленную вправо (см. рисунок выше), и выбрать один из предложенных вариантов:

  • Над диаграммой (Above Chart) – название размещается над областью построения диаграммы, размер графика при этом уменьшается; этот вариант используется по умолчанию.
  • По центру (наложение) (Centered Overlay) – выровненное по центру название накладывается поверх области построения, размер графика при этом не изменяется.

Чтобы найти больше параметров, откройте вкладку Конструктор (Design) и нажмите Добавить элемент диаграммы (Add Chart Element) > Название диаграммы (Chart Title) > Дополнительные параметры заголовка (More Options). Или нажмите иконку Элементы диаграммы (Chart Elements), затем Название диаграммы (Chart Title) > Дополнительные параметры (More Options).

Нажатие кнопки Дополнительные параметры (More Options), как в одном, так и в другом случае, открывает панель Формат названия диаграммы (Format Chart Title) в правой части рабочего листа, где можно найти нужные параметры.

Добавляем название к диаграмме в Excel 2010 и Excel 2007

Чтобы добавить название к диаграмме в Excel 2010 и более ранних версиях, выполните следующие действия:


Связываем название диаграммы с ячейкой рабочего листа

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

Чтобы связать название диаграммы с ячейкой рабочего листа:

  1. Выделите название диаграммы.
  2. В строке формул введите знак равенства (= ), кликните по ячейке, содержащей нужный текст, и нажмите Enter .

В данном примере мы связываем название диаграммы Excel с ячейкой A1 . Можно выбрать две или более ячеек (например, несколько заголовков столбцов), и в результате в названии диаграммы будет показано содержимое всех выбранных ячеек.

Перемещаем название в диаграмме

Если нужно переместить название диаграммы в другое место, выделите его и перетащите при помощи мыши:

Удаляем название диаграммы

Если для диаграммы Excel не нужно название, то его можно удалить двумя способами:

Изменяем шрифт и оформление названия диаграммы

Чтобы изменить шрифт названия диаграммы в Excel, кликните по нему правой кнопкой мыши и нажмите Шрифт (Font) в контекстном меню. Откроется одноименное диалоговое окно, в котором можно настраивать различные параметры шрифта.

Если нужны более детальные настройки, выделите название диаграммы, откройте вкладку Формат (Format) и поиграйте различными параметрами. Вот как, например, можно преобразить название диаграммы при помощи Ленты меню:

Таким же образом можно изменить оформление других элементов диаграммы, таких как , подписи на осях и .

Настраиваем оси диаграммы в Excel

Для большинства типов диаграмм в Excel вертикальная ось (она же – ось значений или ось Y) и горизонтальная ось (она же – ось категорий или ось Х) добавляются автоматически при создании диаграммы.

Чтобы скрыть или показать оси диаграммы, кликните по иконке Элементы диаграммы (Chart Elements), затем нажмите стрелку в строке Оси (Axes) и отметьте галочкой те оси, которые нужно показать, или уберите галочку рядом с теми, которые нужно скрыть.

Для некоторых типов диаграмм, например, для комбинированных диаграмм , может быть показана вспомогательная ось.

При создании объёмных диаграмм, можно отобразить ось глубины :

Для каждого элемента осей диаграммы в Excel можно настраивать различные параметры (далее поговорим об этом подробнее):

Добавляем названия осей в диаграмме

Создавая диаграмму в Excel, можно добавить названия вертикальной и горизонтальной осей, чтобы пользователям было проще понять, что за данные показаны на диаграмме. Чтобы добавить названия осей, нужно сделать вот что:


Чтобы настроить оформление названия оси, кликните по нему правой кнопкой мыши и в контекстном меню нажмите Формат названия оси (Format Axis Title). При этом откроется одноимённая панель с большим выбором настраиваемых параметров оформления. Можно также использовать параметры, предложенные на вкладке Формат (Format) Ленты меню, как мы делали при .

Связываем названия осей с заданными ячейками рабочего листа

Сегодня Вы узнали о том, как можно настраивать диаграммы в Excel. Конечно, эта статья позволяет только поверхностно коснуться темы настроек и форматирования диаграмм в Excel, хотя об этом можно рассказать намного больше. В следующей статье мы построим диаграмму из данных, которые находятся на различных рабочих листах. А тем временем рекомендую Вам попрактиковаться, чтобы закрепить полученные сегодня знания.


Работа с VB проектом (12)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

Вставить в письмо подпись из Outlook через VBA

Последнее время стал часто встречать вопрос о том, как в письмо, созданное кодом VBA в Excel, добавить стандартную подпись Outlook.
Немного теории для тех, кто не знает как создать и использовать подписи в Outlook . Дело в том, что в Outlook можно создавать несколько подписей и какую-то использовать по умолчанию, а остальные можно вставлять в письмо по необходимости.
Чтобы создать подпись в Excel 2007 и выше необходимо перейти в меню:

  • для 2007 : Сервис -Параметры -Сообщение -Подписи
  • для 2010 и выше : Файл (File) -Параметры (Options) -Почта (Mail) -Подписи (Signatures)

В появившемся окне на вкладке Электронная подпись (E-mail Signature) необходимо нажать кнопку Создать (New) . Будет предложено ввести имя новой подписи. После чего необходимо подтвердить создание нажатием кнопки ОК и подпись будет добавлена. После добавления необходимо выбрать созданную подпись и добавить текст. Все созданные подписи отображаются в поле Выберите подпись для изменения (Select signature to edit) . При выборе любой подписи в нижнем окне Изменить подпись (Edit signature) можно изменить как сам текст подписи и другие параметры: добавить/изменить текст, отформатировать его, вставить гиперссылки, картинки(логотип компании, например) и т.д. Для сохранения изменений необходимо нажать кнопку Сохранить(под верхним окном). Не очень интуитивненько, но как есть.
В этой же вкладке можно настроить использование подписей по умолчанию для выбранной учетной записи(для каждой учетной записи можно назначить свою подпись по умолчанию) - в правой части окна.
Чтобы после создания письма вставить любую из созданных ранее подписей необходимо из окна созданного сообщения перейти на вкладку Вставка (Insert) -Подписи (Signatures) и выбрать нужную подпись.

Как я уже упоминал вначале статьи - из VBA так же можно создавать письма в Outlook. Подробнее об этом можно прочитать в этой статье: Как отправить письмо из Excel? . И вот там как раз есть небольшой недостаток - при создании письма подпись не добавляется автоматом, даже если она создана и настроена для вставки в новые сообщения. Это и побудило меня написать данную статью. Дело в том, что все созданные подписи хранятся на ПК в определенном месте на диске(%AppData%\Microsoft\Signatures\) и до них можно достучаться. Правда, сэмулировать выбор подписи непосредственно из меню не представляется возможным, а вот определить наличие подписей и сделать их вставку можно. Перебрать все созданные подписи можно кодом:

Sub SeeSigns() Dim sSignPath As String Dim sF sSignPath = Environ("appdata" ) & "\Microsoft\Signatures\" "" sF = Dir(sSignPath & "*.txt" ) Do While sF <> "" MsgBox sF, vbInformation, "www.сайт" sF = Dir Loop End Sub

Sub SeeSigns() Dim sSignPath As String Dim sF sSignPath = Environ("appdata") & "\Microsoft\Signatures\" "" sF = Dir(sSignPath & "*.txt") Do While sF <> "" MsgBox sF, vbInformation, "www.сайт" sF = Dir Loop End Sub

Этот код просматривает только текстовые подписи - т.е. без оформления в виде картинок и гиперссылок. Однако в самой папке помимо текстовых файлов есть файлы с форматированием в формате.htm. Они добавляются в письма, которые пишутся с применением форматирования, т.е. письма формата.HTML.

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

(66,5 KiB, 1 729 скачиваний)


После скачивания файла прежде чем нажать кнопку Создать письмо в Outlook с подписью надо будет настроить параметры письма в ячейках:
  • в В11 - адрес получателя(кому отправить письмо)
  • в В12 - тема письма
  • в В13 - текст письма


Если планируется отправить письмо с форматированием текста(жирный шрифт, различный цвет шрифта и т.п.) - то надо будет к письму применить теги HTML(ячейка В13 ). В файле я составил именно такой текст, чтобы был пример подобных писем. При этом для форматированного письма в форме следует выбрать пункт Форматированный текст (.htm) . Иначе текст письма будет обычный, но со всеми тегами(ровно так, как он выглядит в самой ячейке на листе). Если форматирование не нужно - то просто записываем текст в ячейку и в форме выбираем Обычный текст (.txt) .
После нажатия Ок появится запрос:


Если нажать Нет (No) , то письмо будет создано, подпись добавлена, но письмо не будет отправлено, а просто будет выведено на экран, чтобы можно было проверить правильно ли все заполнено и создано. Если нажать Да (Yes) , то письмо будет создано и сразу отправлено, без вывода на экран.
Рекомендую сначала пользоваться предпросмотром, чтобы понять как правильно создавать и форматировать письма.
Для тех, кто уже имеет опыт программирования в VBA, полагаю, не составит труда адаптировать код под какие-то свои коды или коды на этом сайте.

Если совсем не охота вдумываться в макросы и нужно готовое решение по рассылке с вложениями и подписями - есть готовое решение: Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

Для того, чтобы в определённом рабочем листе, заменить все диаграммы на их скриншоты(рисунки), можно воспользоваться нижеопубликованным макросом:

  • Ответ: Актуально для MS Excel 2000(и старше)

    Для того, чтобы в определённом рабочем листе, найти все круговые и разрезанные круговые диаграммы и сохранить все их составляющие в виде графических файлов, можно воспользоваться нижеопубликованным макросом. A после того, как временная рабочая книга, будет сохранена в виде HTML файла, Вы сможете увидеть все графические файлы в папке "C:\Temp_files"

    Private Sub SavePieChartToPictures() Dim iList1 As Worksheet, iList2 As Worksheet Dim iCharts As ChartObjects, iChart As Chart Dim iGroup As Picture, iOffset&, iCount% Const iFileName = "C:\Temp.html" "укажите временный файл Application.ScreenUpdating = False Application.DisplayAlerts = False Set iList1 = Workbooks.Add(xlWBATWorksheet).Worksheets(1) Set iList2 = ThisWorkbook.Worksheets(1) "укажите нужный рабочий лист Set iCharts = iList2.ChartObjects For iCount = 1 To iCharts.Count Set iChart = iCharts(iCount).Chart Select Case iChart.ChartType Case xlPie, xlPieExploded iChart.CopyPicture Size:=xlScreen Set iGroup = iList1.Pictures.Paste ShapeList iGroup.ShapeRange.Ungroup, iOffset End Select Next With iList1.Parent .SaveAs FileName:=iFileName, FileFormat:=xlHtml .FollowHyperlink Address:=Replace(iFileName, ".html", ".files") .Close saveChanges:=False End With Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Private Sub ShapeList(iGroup As ShapeRange, iOffset&) Dim iShape As Shape For Each iShape In iGroup If iShape.Type = msoGroup Then ShapeList iShape.Ungroup, iOffset Else iShape.Top = iOffset iOffset = iOffset + iShape.Height + 10 End If Next End Sub Комментарий: Если Вы являетесь обладателем Microsoft Excel 2007 и при программной попытке разгруппировать рисунок, получаете ошибку 1004 , то это означает, что Вам необходимо установить соответствующий Service Pack.

    Для того, чтобы раскрасить все точки определённой диаграммы случайными цветами можно использовать нижеопубликованный макрос, только не забудьте указать нужную диаграмму (в первом макросе предполагается, что диаграмма расположена в отдельном листе Диаграммы, а во втором, что диаграмма находится на рабочем листе с кодовым именем Лист1) :

  • Ответ: Актуально для MS Excel 97, 2000, XP

    Если Вам необходимо автоматически менять цвет заливки точки, в зависимости от данных исходной ячейки, например, после ввода числа > 1000 окрасить точку в зелёный цвет, а после ввода числа необходимо разместить в модуле рабочего листа (см. пример)

    В этом примере используется диаграмма с одним рядом, построенная на основании данных диапазона . Если диаграмма должна находиться в отдельном листе Диаграммы, или в другом рабочем листе, то в таком случае - Вам придётся заменить ключевое слово Me ссылкой на нужный лист. Кроме того, в данном примере допускается применение функции RGB, вместо используемых констант, например, RGB(50, 0, 100)
  • Ответ: Актуально для MS Excel 97-2003

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

    Примечание: Разумеется, в обоих примерах, синхронизация цвета будет происходить, только после выполнения события рабочего листа Worksheet_Activate . Если же активация листа Вас, по каким-то причинам, не устраивает, то вместо него можно использовать событие рабочей книги Worksheet_Open (вкупе с соответствующими изменениями)

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

  • Ответ:

    Не секрет, что после создания диаграммы можно разорвать связь этой диаграммы с исходными ячейками, для этого достаточно последовательно выделять каждый ряд и нажимать клавиши F2, F9 и ENTER. Если тоже самое необходимо проделать, но уже с помощью VBA, то в случае, если диаграмма расположена на рабочем листе с кодовым именем Лист1 , можно использовать следующий макрос:

    Примечание: Допускается замена кодового(программного) имени рабочего листа и листа диаграммы, так, к примеру, вместо Лист1 можно использовать Worksheets(Индекс_листа), Worksheets("Имя_листа") , а вместо Диаграмма1 возможно применение Charts(Индекс_листа) или Charts("Имя_листа") , Sheets("Имя_листа")

    Комментарий: К сожалению, программный способ замены приведёт к возникновению ошибки, если в результате замены, формула =РЯД() теоретически будет содержать более 481 символов (не считая длины имени ряда)
    А вот замена вручную , при этом же количестве символов, возможна , правда если их количество всё же превысит стандартное ограничение формулы в 1024 символов, то Вы получите сообщение типа, Слишком сложная формула.

    Вариант II. (Microsoft Excel 2000 или старше)

    Комментарий: Второй способ также приведёт к возникновению ошибки, если формула = iFormula$ будет содержать более 255 символов.

    Microsoft Excel 2007
    В этой версии, разработчики несколько увеличили лимит на количество символов в формуле (второй вариант). Однако, высчитывать их количество, сейчас нет особого смысла, т.к. есть альтернатива - первый вариант, где разработчики, возможно, "сняли" ограничение. Во всяком случае, мне удалось заменить ссылку на диапазон, где количество символов превышало 10000 шт.

  • Ответ:

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

    Комментарий: Важно, чтобы выбранный цвет заливки (в данных примерах это красный и зелёный) не совпадал с изначальной заливкой точек, в противном случае, Вы получите диаграмму, где нужным цветом будут выделены не только точки с минимумом/максимумом. Впрочем, подобного совпадения цветов можно избежать, если предварительно установить цвет заливки у всех точек ряда, отличный от красного и зелёного, или же проверять цвет каждой точки и в случае совпадения, менять его. Кроме того, возможен вариант, при котором минимальное значение либо вообще не будет отображаться (к примеру, нулевое значение), либо визуально точка будет практически незаметна.
  • Ответ:

    Если в уже существующей диаграмме Вам необходимо найти, например, пять точек с максимальными значениями и добавить им подпись и/или изменить цвет текста подписи, то в случае, когда диаграмма расположена на рабочем листе с кодовым именем Лист1 , Вы можете использовать следующий макрос:

    Комментарий: Если предполагается установить единый цвет текста для всех максимальных точек, то лучше не менять цвет шрифта в цикле, а установить его сразу для всей серии (см.пример)
  • Ответ: Актуально для MS Excel 2007(и старше)

    Если в уже существующей диаграмме Вам необходимо найти все точки, значения которых больше указанного в программе, и изменить их "яркость" (цвет заливки) , то Вы можете использовать следующий макрос. Только не забудьте, заменить температуру 36,6 на своё значение, превышение которого и должно выделять точку необходимым оттенком красного цвета.

    Вариант I.

  • Ответ: Актуально только для MS Excel 97-2003

    Если Вам необходимо программно добавить существующую нестандартную диаграмму - в список пользовательских типов диаграмм, то для этого можно использовать нижеопубликованный вариант, где третий именованный аргумент Description является необязательным, т.е. если краткое описание графика, для Вас, не представляет интереса, то этот аргумент можно оставить в покое. Обратите внимание на то, что первые два примера предназначены для диаграммы, которая размещена в отдельном листе, а следующие, для диаграммы, которая находится на рабочем листе.

  • Ответ:

    Если Вам необходимо определить над какой из точек находится курсор мышки, причём не прибегая к выделению этой точки, то в таком случае можно использовать следующее событие диаграммы. Обратите внимание на то, что вывод информации в статус баре не носит обязательного характера и применяется только в качестве примера.

    Примечание: В данном примере предполагается, что диаграмма расположена в отдельном листе , а весь вышеприведённый код находится в модуле этой диаграммы .

    Если же речь идёт о диаграмме, которая находится в рабочем листе, то здесь также возможно применение события Chart_MouseMove, но только в случае, если диаграмма активна(выделена). В противном случае, указанное событие выполняться не будет, однако, это ограничение можно обойти, если пойти на маленькую хитрость, а именно:
    создать ActiveX элемент управления Надпись (Label) и используя свойства созданного элемента управления:
    - удалить текст, который отображается напротив поля Caption
    - напротив поля BackStyle выбрать 0 - fmBackStyleTransparent
    - и главное, расположить и изменить его размеры, так, чтобы подогнать надпись под месторасположение и размеры нужной диаграммы.
    После чего, использовать аналогичное событие, но уже не диаграммы, а созданного элемента управления, естественно, учитывая их особенности. Готовый пример, где демонстрируется этот трюк, можно скачать .

  • Ответ:

    Если Вам необходимо привести все диаграммы в рабочем листе к одинаковому размеру, т.е. установить одну и туже высоту и ширину для всех диаграмм листа, причём осуществить это необходимо (или желательно) без цикла , то Вы можете воспользоваться нижеприведённым вариантом, разумеется, указав свою высоту(height) и ширину(width) и, при необходимости, заменив ActiveSheet на конкретный рабочий лист.

    • Объект Excel.Chart, программная работа с диаграммами средствами VBA, выбор типа диаграммы, добавление рядов

    Одно из основных применений Excel - это анализ данных. А для анализа данных часто удобно использовать диаграммы с их специальными возможностями, такими, как тренды. На практике задачи по автоматизации создания множества похожих друг на друга диаграмм (обычно на основе информации, полученной из базы данных) возникают очень часто.

    С диаграммами в Excel существует некоторая терминологическая путаница. То, что на графическом интерфейсе русского Excel называется диаграммой (меню Вставка -> Диаграмма ), по английски называется графиком (Chart) и ему соответствует объект Chart. В объектной модели Excel предусмотрен также и объект Diagram, но он представляет скорее схему отношений (то, что при помощи графического интерфейса русского Excel можно добавить при помощи меню Вставка -> Схематическая диаграмма ). Под диаграммой в этом разделе будет пониматься то же, что и у создателей русского Excel - график.

    Диаграммы в Excel создаются при помощи объекта Chart.

    Лучше всего вначале этот объект объявить:

    Set oChart = ActiveWorkbook.Charts.Add(, ActiveSheet)

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

    Первое (и единственное обязательное действие) - определить источник данных для диаграммы, для чего предназначен метод SetSourceData(). В качестве источника может выступать только объект Range (он передается в качестве первого и единственного обязательного параметра этого метода). Второй параметр (необязательный) определяет, в каком порядке считывать данные - сначала по столбцам или сначала по строкам. Например, в нашем случае это может выглядеть так:

    oChart.SetSourceData(Sheets("Лист1").Range("A1:A10"))

    В принципе, если запустить созданный код на выполнение, то диаграмма уже будет создана. Для всех остальных параметров будут приняты значения по умолчанию. Однако на практике нужно определить еще хотя бы тип диаграммы (по умолчанию она будет выглядеть как "обычная гистограмма", то есть ряд из столбиков разной длины). Для этой цели используется свойство ChartType, для которой разработчиками предусмотрено целых 73 значения. Например, чтобы преобразовать диаграмму в обычный график, можно использовать код вида:

    oChart.ChartType = xlLineMarkers

    Еще одна очень распространенная задача – добавить дополнительные ряды на диаграмму. Для этой цели необходимо создать и получить ссылку на объект Series – ряд, а потом для ряда определить свойство Values (ему передается в качестве значения объект Range):

    Dim oSeries As Series

    Set oSeries = oChart.SeriesCollection.NewSeries

    Пользователи часто говорят, что им необходимо создавать диаграммы не на отдельном листе, а на том же листе, на котором расположены данные. По умолчанию диаграмма создается в оперативной памяти и помещается на отдельный лист. Если нам необходимо поместить ее на уже существующий лист, то в этом случае ее вначале надо создать на отдельном листе, а затем переместить при помощи метода Location. Отдельный лист, созданный для диаграммы, при этом автоматически исчезнет:

    oChart.Location xlLocationAsObject, "Лист1"

    Обратите внимание, что метод Location принимает в качестве первого параметра одну из констант (xlLocationAsNewSheet – переместить на специально создаваемый новый лист, xlLocationAsObject – переместить на объект, то есть лист), а в качестве второго – не объект листа, как можно было бы подумать, а обязательно его имя. Если код предполагается использовать и в русской, и в английской версии Excel, то предпочтительнее получить имя листа программным образом.

    Большая неприятность, связанная с методом Location, заключается в том, что после перемещения диаграммы внутрь листа объектная ссылка на эту диаграмму теряется, и надо находить объект этой диаграммы заново. При попытке повторного обращения к объекту Chart выдается сообщение "Automation Error". Лучше всего поэтому вызов метода Location помещать в самый конец кода, посвященного диаграмме. В противном случае нам придется разыскивать созданную нами диаграмму и заново получать на нее объектную ссылку, например так:

    Dim oSeries As Series

    Set oSeries = Worksheets(1).ChartObjects(1).Chart.SeriesCollection.NewSeries

    oSeries.Values = Worksheets(1).Range("B1:B10")

    Так работать, конечно, намного менее удобно.

    Остальные многочисленные параметры диаграммы настраиваются при помощи свойств и методов объектов Chart:

    • свойство ChartArea - это свойство возвращает одноименный объект ChartArea, который представляет собой область, занимаемую диаграммой и используется для настройки внешнего вида диаграммы (свойства Font, Interior и т.п.). Если необходимо настроить внешний вид не всей диаграммы, а той ее части, которая используется непосредственно для вывода графика, используется схожее свойство PlotArea . По умолчанию диаграмма размещается прямо по центру листа. Если необходимо ее переместить в точно определенное место листа, используются знакомые на свойства Top, Height, Left и Width для объекта ChartArea.
    • свойство ChartTitle возвращает одноименный объект, при помощи которого можно настроить заголовок диаграммы (с такими свойствами, как Text, Font, Border и т.п.);
    • ChartType - важнейшее свойство, про которое мы уже говорили. Определяет тип диаграммы;
    • HasDataTable - если установить это свойство в True, то в нижней части диаграммы (по умолчанию) появится таблица с числами, на основе которых была создана диаграмма. Одновременно будет создан программный объект DataTable, при помощи которого можно настроить представление этой таблицы. Схожим образом действуют свойства HasLegend, HasPivotFields и HasTitle.
    • Name - это свойство позволяет настроить имя диаграммы (как название вкладки в Excel). По умолчанию диаграммы называются последовательно "Диаграмма1", "Диаграмма2" и т.п.
    • SizeWithWindow - если поставить значение этого свойства в True (по умолчанию False), то размер диаграммы будет подогнан таким образом, чтобы точно соответствовать размеру листа.
    • Tab - свойство, о котором мало кто подозревает. Оно позволяет настроить при помощи одноименного объекта внешний вид вкладки в книге Excel для диаграммы (или просто листа). Например, чтобы пометить вкладку зеленым, можно воспользоваться кодом

    oChart.Tab.Color = RGB(0, 255, 0)

    • Visible - возможность спрятать диаграмму без ее удаления.

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

    Теперь - о самых главных методах объекта Chart:

    • метод Activate() используется очень часто. Он позволяет сделать диаграмму активной (то есть просто перейти на нее);
    • метод ApplyCustomType() позволяет поместить создать диаграмму своего собственно пользовательского типа (для этого необходимо вначале создать шаблон для этого типа и поместить его в галерею);
    • метод ApplyDataLabels() позволяет поместить на диаграмму метки для размещенных на ней данных. Этот метод принимает множество параметров, которые позволяют настроить отображение данных меток (показывать или не показывать значения и т.п.);
    • метод Axes() возвращает объект, представляющий оси диаграммы. Затем этот объект можно использовать для настройки данных осей;
    • ChartWizard() - этот метод позволяет быстро переформатировать диаграмму, как будто бы прошли на графическом экране при помощи мастера построения диаграмм и передали ему значения. Позволяет при помощи одной строки кода добиться того, что другими способами потребовало бы минимум несколько строк;
    • Copy() - позволяет скопировать диаграмму в другое место книги (например, чтобы создать новую диаграмму, использовав в качестве основы существующую). Для переноса существующей диаграммы в другое место можно воспользоваться методами Location() или Move() .
    • CopyPicture() - замечательный метод, который позволяет поместить диаграмму в буфер обмена как изображение. Затем это изображение можно вставить, например, в документ Word или в любое другое место. Другой вариант - воспользоваться методом Export() , который позволяет создать рисунок, представляющий диаграмму, в виде файла на диске.
    • Delete() - просто удаляет диаграмму;
    • Evaluate() - как обычно, этот метод позволяет найти нужную диаграмму в книге по ее имени;
    • PrintOut() - возможность отправить диаграмму на печать. Этот метод принимает множество параметров, которые позволяют настроить такой вывод;
    • Refresh() - возможность обновить диаграмму, если изменились данные, на основе которых она строилась;
    • Select() - возможность выделить диаграмму (равносильно щелчку по ней мышью). Deselect() - снятие выделения (равносильно нажатию на );
    • SetBackgroundPicture() - возможность "подложить" под диаграмму фоновый рисунок. Конечно, он должен быть не очень ярким;
    • SetSourceData() - важнейший метод, который позволяет определить данные, на основе которых строится диаграмма. Про него мы уже говорили.

    Для объекта Chart предусмотрено также события "на все случаи жизни" - реакция на щелчки мышью, на выделение/снятие выделения, активизацию, пересчет данных, изменение размера и т.п., однако используются такие события на практике нечасто.

    Заливка ячейки цветом в VBA Excel. Фон ячейки. Свойства.Interior.Color и.Interior.ColorIndex. Цветовая модель RGB. Стандартная палитра из 56 цветов. Предопределенные константы.

    Свойство.Interior.Color объекта Range

    Начиная с Excel 2007 основным способом заливки диапазона или отдельной ячейки цветом (зарисовки, добавления, изменения фона) является использование свойства.Interior.Color объекта Range путем присваивания ему значения цвета в виде десятичного числа от 0 до 16777215 (всего 16777216 цветов).

    Заливка ячейки цветом в VBA Excel

    Пример кода 1:

    Sub ColorTest1() Range("A1").Interior.Color = 31569 Range("A4:D8").Interior.Color = 4569325 Range("C12:D17").Cells(4).Interior.Color = 568569 Cells(3, 6).Interior.Color = 12659 End Sub

    Поместите пример кода в свой программный модуль и нажмите кнопку на панели инструментов «Run Sub» или на клавиатуре «F5», курсор должен быть внутри выполняемой программы. На активном листе Excel ячейки и диапазон, выбранные в коде, окрасятся в соответствующие цвета.

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

    Sub ColorTest11() Cells(1, 1).Interior.Color = -12207890 Cells(2, 1).Interior.Color = 16777215 + (-12207890) Cells(3, 1).Interior.Color = 4569325 End Sub

    Проверено в Excel 2016.

    Вывод сообщений о числовых значениях цветов

    Числовые значения цветов запомнить невозможно, поэтому часто возникает вопрос о том, как узнать числовое значение фона ячейки. Следующий код VBA Excel выводит сообщения о числовых значениях присвоенных ранее цветов.

    Пример кода 2:

    Sub ColorTest2() MsgBox Range("A1").Interior.Color MsgBox Range("A4:D8").Interior.Color MsgBox Range("C12:D17").Cells(4).Interior.Color MsgBox Cells(3, 6).Interior.Color End Sub

    Вместо вывода сообщений можно присвоить числовые значения цветов переменным, объявив их как Long.

    Использование предопределенных констант

    В VBA Excel есть предопределенные константы часто используемых цветов для заливки ячеек:

    Присваивается цвет ячейке предопределенной константой в VBA Excel точно так же, как и числовым значением:

    Пример кода 3:

    Range("A1").Interior.Color = vbGreen

    Цветовая модель RGB

    Цветовая система RGB представляет собой комбинацию различных по интенсивности основных трех цветов: красного, зеленого и синего. Они могут принимать значения от 0 до 255. Если все значения равны 0 - это черный цвет, если все значения равны 255 - это белый цвет.

    Выбрать цвет и узнать его значения RGB можно с помощью палитры Excel:

    Чтобы можно было присвоить ячейке или диапазону цвет с помощью значений RGB, их необходимо перевести в десятичное число, обозначающее цвет. Для этого существует функция VBA Excel, которая так и называется - RGB.

    Пример кода 4:

    Range("A1").Interior.Color = RGB(100, 150, 200)

    Свойство.Interior.ColorIndex объекта Range

    До появления Excel 2007 существовала только ограниченная палитра для заливки ячеек фоном, состоявшая из 56 цветов, которая сохранилась и в настоящее время. Каждому цвету в этой палитре присвоен индекс от 1 до 56. Присвоить цвет ячейке по индексу или вывести сообщение о нем можно с помощью свойства.Interior.ColorIndex:

    Пример кода 5:

    Range("A1").Interior.ColorIndex = 8 MsgBox Range("A1").Interior.ColorIndex

    Просмотреть ограниченную палитру для заливки ячеек фоном можно, запустив в VBA Excel простейший макрос:

    Пример кода 6:

    Sub ColorIndex() Dim i As Byte For i = 1 To 56 Cells(i, 1).Interior.ColorIndex = i Next End Sub

    Номера строк активного листа от 1 до 56 будут соответствовать индексу цвета, а ячейка в первом столбце будет залита соответствующим индексу фоном.

    Готовую стандартную палитру из 56 цветов можете посмотреть .