Алексей Гладкий - Excel. Трюки и эффекты

• Название: Excel. Трюки и эффекты
• Автор: Алексей Гладкий
• Жанр: Программы

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

---

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

• Пользовательский интерфейс – средство взаимодействия пользователя с программой. В состав пользовательского интерфейса входят лента с вкладками, группы, диалоговые окна и др. В Excel применяется стандартный пользовательский интерфейс Windows.

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

• Рабочая книга – файл, который создается, редактируется и сохраняется средствами Microsoft Excel. В большинстве случаев рабочая книга имеет расширение XLSX. Основной структурной единицей рабочей книги является рабочий лист (см. ниже).

• Рабочий лист – основной элемент рабочей книги, предназначенный для ввода, редактирования и хранения данных, а также для выполнения вычислений. По умолчанию в состав рабочей книги включены три рабочих листа. Основной структурной единицей рабочего листа является ячейка (см. ниже).

• Редактор VBA – интегрированная среда разработки, в которой осуществляется написание кодов (программирование) на языке VBA. Чтобы перейти в данный режим, необходимо нажать сочетание клавиш Alt+F11.

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

• Строка формул – предназначена для ввода формул и редактирования содержимого ячеек.

• Форматирование – изменение отображения ячейки (ее «внешнего вида») либо представления данных, содержащихся в ячейке. Параметры форматирования ячейки не зависят от ее содержимого, и наоборот. Не стоит забывать, что после применения форматирования отображенное в ячейке значение может не совпадать с ее фактическим значением (наиболее характерный пример – округление: в ячейке хранится значение 0,24, но в соответствии с параметрами форматирования на экране может отображаться значение 0,2).

Совет

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

• Формула – специальный инструмент Excel, предназначенный для расчетов, вычислений и анализа данных. Формула может включать в себя константу, оператор, ссылку, имя ячейки (диапазона) и функцию (см. ниже).

Операторы бывают трех видов.

– Арифметический оператор – предназначен для выполнения арифметических действий и выдающий в качестве результата числовое значение.

– Оператор сравнения – используется для сравнения данных и выдает в качестве результата логическое значение ИСТИНА или ЛОЖЬ.

– Текстовый оператор – применяется для объединения данных.

• Функция – готовая формула Microsoft Excel для расчетов, вычислений и анализа данных. Каждая функция может включать в себя константу, оператор, ссылку, имя ячейки (диапазона) и формулу. Пользовательская функция – это функция, написанная пользователем на языке VBA.

• Электронная таблица – интерактивная программа, состоящая из набора строк и столбцов, которые выводятся на экран в отдельном окне.

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

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

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

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

Тексты программ на сайте издательства

Все приведенные в книге листинги (коды программ) можно загрузить с сайта издательства «Питер» по адресу http://www.piter.com/downLoad/978591180547/.

От издательства

Ваши замечания, предложения и вопросы отправляйте по адресу электронной почты [email protected] (издательство «Питер», компьютерная редакция). Мы будем рады узнать ваше мнение!

На сайте издательства http://www.piter.com вы найдете подробную информацию о наших книгах.

Глава 1 Краткое руководство по VBA

Цель данной главы – ознакомить читателя с основами программирования на языке Visual Basic for Applications (VBA), который встроен в пакет Microsoft Office.

Эта глава по форме изложения скорее похожа на справочник. Она рассчитана на пользователей, имеющих некоторый опыт создания программ на других объектно-ориентированных языках программирования, то есть представляющих, что такое переменная, константа, оператор, цикл, массив, класс, объект и т. д. Главный упор в данной главе сделан на описание синтаксиса и особенностей использования основных конструкций VBA, которые нужно знать для понимания приведенных в тексте листингов.

Знакомство с VBA

VBA – это язык программирования, поддерживаемый большинством приложений пакета Microsoft Office. Для запуска среды программирования VBA можно использовать сочетание клавиш Alt+F11.

Возможности VBA

Благодаря высокой степени интеграции в приложения язык VBA позволяет программисту легко применять существующие возможности Microsoft Office для решения специфических задач. Не менее легко VBA позволяет добавлять в приложения этого пакета новые возможности, увеличивая функциональность приложений и удобство работы с ними.

Применительно к Excel программирование на VBA позволяет реализовывать следующие возможности (естественно, это далеко не полный список):

• добавление функций для специфических расчетов;

• ускорение ввода данных в таблицу;

• автоматизацию типичных, часто выполняемых пользователем действий;

• создание команд меню, панелей инструментов как на основе уже имеющихся, так и выполняющих совершенно новые действия;

• повышение наглядности данных (например, с помощью различной окраски ячеек);

• автоматизацию обработки больших объемов данных;

• автоматизацию создания разнообразных отчетов, бланков и прочих действий, связанных с выбором заданной информации из большого объема исходных данных.

Реализация всех этих возможностей при использовании VBA очень часто достигается написанием небольшого количества достаточно простого программного кода.

Структура проекта VBA

Внешний вид редактора VBА с открытым проектом представлен на рис. 1.1.

Рис. 1.1. Проект VBA

В редакторе открыты три окна: слева вверху – окно структуры проекта (Project), слева внизу – окно свойств (Properties) и справа – окно с текстом программы модуля. С помощью окна Project (Проект) можно просматривать структуру проекта, добавлять и удалять элементы проекта, открывать для редактирования содержимое модуля (двойным щелчком на значке соответствующего модуля). Окно Properties (Свойства) используется для задания свойств выделенных элементов проекта.

VBA-проект в Microsoft Excel может содержать следующие элементы:

• модули (стандартные модули VBA);

• модули класса;

• модули рабочей книги;

• модули рабочих листов;

• модули диаграмм;

• формы.

Стандартный модуль VBA

Стандартный модуль VBA – это элемент проекта, который содержит программный код, непосредственно используемый остальными элементами проекта (глобальные функции, переменные, константы и т. д.). В окне структуры проекта стандартные модули группируются в папку Modules.

Обычно в стандартном модуле записываются программы, которые не привязаны к конкретным объектам, таким как рабочий лист и рабочая книга. Именно в этом модуле записывается большинство примеров (трюков), рассматриваемых в последующих главах книги.

Модуль класса

Модуль класса – это модуль, в котором записывается программный код, реализующий работу пользовательских (созданных программистом) классов. В окне структуры проекта такие модули группируются в папку Class Modules.