Перейти к содержимому

7. Полезняшки Excel

Работа с процедурами VBA

Процедура — это последовательность операторов VBA, расположенная в модуле VBA, доступ к которому можно получить с помощью VBE. Модуль может включать любое количество процедур.[1] Некоторые процедуры получают аргументы. Аргумент — это информация, используемая процедурой в процессе выполнения. Аргументы процедуры во многом подобны аргументам, используемым функциями Excel.

При объявлении процедуры с использованием ключевого слова Sub применяется следующий синтаксис.

[Private | Public][Static] Sub имя([список_аргументов])
    [инструкции]
    [Exit Sub]
    [инструкции]
End Sub

Рис. 1. Запуск процедуры из Visual Basic Editor

Подробнее »Работа с процедурами VBA

Основы программирования на VBA

Для начала в качестве примера рассмотрим простую процедуру VBA типа Sub. Она хранится в модуле VBA и вычисляет сумму первых ста положительных целых чисел. По окончании вычислений процедура отображает сообщение с результатом.[1]

Sub VBA_Demo()
    'Пример простой процедуры VBA
    Dim Total As Long, i As Long
    Total = 0
    For i = 1 To 100
        Total = Total + i
    Next i
    MsgBox Total
End Sub

Подробнее »Основы программирования на VBA

Джон Уокенбах. Excel 2010. Профессиональное программирование на VBA

Джон Уокенбах, один из крупнейших специалистов по Excel, представляет программирование на VBA (Visual Basic for Applications). Книга будет полезна, как начинающим, так и опытным программистам VBA. На момент публикации заметки вышла следующая версия книги для Excel 2013.

Джон Уокенбах. Excel 2010. Профессиональное программирование на VBA. – М.: Диалектика, 2010 – 944 с.

Подробнее »Джон Уокенбах. Excel 2010. Профессиональное программирование на VBA

Настройка среды Visual Basic Editor

В процессе программирования на VBA в Excel вы будете проводить много времени, работая в окнах VBE. Чтобы сделать редактор более удобным, вам понадобится настройка среды Visual Basic Editor под ваши предпочтения.[1] Находясь в Excel, перейдите в окно VBE, например, нажав Alt+F11. В строке меню окна VBE выберите команду Tools –> Options. Появится диалоговое окно Options (Параметры) с четырьмя вкладками: Editor (Редактор), Editor Format (Формат редактора), General (Общие) и Docking (Прикрепление). Кстати, не путайте это окно с диалоговым окном Параметры Excel программы Excel, которое можно открыть в Excel с помощью команды Файл –> Параметры Excel.

Рис. 1. Вкладка Editor диалогового окна VBE Options (Параметры)

Подробнее »Настройка среды Visual Basic Editor

Формат файлов Excel

Начиная с версии 2007 в Excel используется XML-формат, на основе которого создаются рабочие книги, шаблоны и надстройки. Фактически эти файлы представляют собой ZIP-архивы. При необходимости они могут быть разархивированы и просмотрены.[1] В версиях, предшествующих Excel 2007, применялся двоичный файловый формат. И, хотя спецификации этого формата известны, работать с двоичными файлами совсем непросто. С другой стороны, файловый XML-формат относится к категории так называемых открытых форматов. Подобные файлы могут создаваться и обрабатываться с помощью любых программ, не относящихся к Office 2010.

В качестве примера используется файл с поддержкой макросов Excel (XLSM). Он состоит из одного рабочего листа, одного листа диаграммы и простого макроса на языке VBA. Рабочий лист включает таблицу, кнопку (из группы элементов управления Формы), рисунок Smart Art, а также фотографию цветка (рис. 1).

Рис. 1. Пример листа рабочей книги

Подробнее »Формат файлов Excel

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

Microsoft очень давно не добавлял в Excel новые типы диаграмм, и вот, наконец в версии 2016 появилось целых шесть новых диаграмм! В предыдущих версиях некоторые из них были в принципе недоступны, а для построения других приходилось совершать шаманские действия. Сейчас же всё происходит в несколько щелчков мыши. Итак, представляю вашему вниманию (рис. 1):

  • Дерево
  • Солнечные лучи
  • Гистограмма (два типа)
  • Ящик с усами
  • Каскадная

%d1%80%d0%b8%d1%81-1-%d0%bd%d0%be%d0%b2%d1%8b%d0%b5-%d1%82%d0%b8%d0%bf%d1%8b-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc-%d0%b2%d1%8b%d0%b4%d0%b5%d0%bb%d0%b5%d0%bd%d1%8b

Подробнее »Новые диаграммы в Excel 2016

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

При создании диаграммы может понадобиться, чтобы некоторые текстовые ее элементы были связаны с ячейками, тогда при изменении текста в ячейке соответствующий элемент диаграммы обновляется. Можно связывать текстовые элементы диаграммы даже с ячейками, в которых содержатся формулы, например, связать заголовок диаграммы с ячейкой, в которой содержится формула, возвращающая текущую дату. [1]

%d1%80%d0%b8%d1%81-1-%d0%bf%d0%be%d0%ba%d0%b0-%d0%b7%d0%b0%d0%b3%d0%be%d0%bb%d0%be%d0%b2%d0%be%d0%ba-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc%d1%8b-%d1%81%d0%b0%d0%bc-%d0%bf%d0%be

Рис. 1. Пока заголовок диаграммы «сам по себе»

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

Использование многоуровневых подписей категорий на диаграммах в Excel

Большинство пользователей не знает, что при создании диаграммы в Excel можно отображать многоуровневые подписи категорий. [1] Для этого не нужно делать ничего особенного — просто выделите все данные перед тем, как создавать диаграмму, а обо всех деталях Excel позаботится сам (рис. 1). В этом примере для подписей категорий используются два столбца (А и В). Обратите внимание: подписи в столбце А не повторяются для каждого субъекта. Название каждого округа фигурирует в диаграмме всего по разу.

%d1%80%d0%b8%d1%81-1-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc%d0%b0-%d0%b2-%d0%ba%d0%be%d1%82%d0%be%d1%80%d0%be%d0%b9-%d0%b4%d0%bb%d1%8f-%d0%bf%d0%be%d0%b4%d0%bf%d0%b8%d1%81%d0%b5%d0%b9

Рис. 1. Диаграмма, в которой для подписей категорий используется два столбца

Подробнее »Использование многоуровневых подписей категорий на диаграммах в Excel

Шаблон комбинированной диаграммы

Если вы часто используете одни и те же настройки диаграмм, можно создать шаблон и сэкономить время. Многие пользователи не любят прибегать к этой возможности, так как считают ее слишком сложной. Однако создать шаблон диаграммы достаточно просто. [1] На рис. 1 представлена диаграмма с множеством настроек, которая будет сохранена как шаблон для использования при создании новых диаграмм.

%d1%80%d0%b8%d1%81-1-%d1%8d%d1%82%d1%83-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc%d1%83-%d0%bc%d0%be%d0%b6%d0%bd%d0%be-%d1%81%d0%be%d1%85%d1%80%d0%b0%d0%bd%d0%b8%d1%82%d1%8c-%d0%ba%d0%b0

Рис. 1. Эту диаграмму можно сохранить как шаблон

Подробнее »Шаблон комбинированной диаграммы

Настройка диаграмм в Excel

Начиная с версии 2013 настройка диаграмм в Excel значительно упростилась. Если в Excel 2013 вы щелкаете на диаграмме, справа появятся три кнопки: элементы, фильтры и стили диаграммы. Они очень удобны и помогают быстро и легко настроить диаграмму. [1] Если нажать на кнопку Элементы диаграммы (рис. 1), появится список, который позволяет отобразить дополнительные параметры. Чтобы это сделать, наведите указатель мыши на любой элемент списка и щелкните на появившейся справа стрелке. Наведите указатель на какой-нибудь элемент и посмотрите, как будет выглядеть диаграмма при его выборе.

%d1%80%d0%b8%d1%81-1-%d1%82%d1%80%d0%b8-%d0%ba%d0%bd%d0%be%d0%bf%d0%ba%d0%b8-%d0%bd%d0%b0%d1%81%d1%82%d1%80%d0%be%d0%b9%d0%ba%d0%b8-%d0%b4%d0%b8%d0%b0%d0%b3%d1%80%d0%b0%d0%bc%d0%bc%d1%8b-%d0%b8

Рис. 1. Три кнопки настройки диаграммы, и параметры, доступные для кнопки Элементы диаграммы

Подробнее »Настройка диаграмм в Excel