автордың кітабын онлайн тегін оқу Excel. Трюки и эффекты
Алексей Анатольевич Гладкий, Александр Анатольевич Чиртик
Excel. Трюки и эффекты
Данная книга предназначена для пользователей Microsoft Excel и содержит описание приемов и методов работы, которые из-за своей специфичности недостаточно представлены (либо вообще не представлены) в пользовательской, справочной и иной соответствующей документации. Изучение приведенных в книге примеров позволит читателю открыть для себя не известные ранее возможности Excel. Предлагаемый материал легко усваивается благодаря тому, что излагается доступным и понятным языком.
Table of Contents
Введение
От издательства
Глава 1 Краткое руководство по VBA
Знакомство с VBA
Возможности VBA
Структура проекта VBA
Структура модуля VBA
Соглашения, применяемые при описании синтаксиса VBA
Комментарии в программе
Идентификаторы
Переменные
Встроенные типы данных
Объявление переменных
Инициализация переменных
Явное и неявное объявление переменных
Константы
Операторы
Операторы для работы с численными значениями
Операторы сравнения
Логические операторы
Массивы
Объявление массива
Задание нижней границы по умолчанию
Изменение размера массива
Определение границ массива
Доступ к элементам массива
Использование переменной Variant при работе с массивами
Использование функции Array для заполнения массива
Коллекции
Добавление элементов
Количество элементов в коллекции
Удаление элементов из коллекции
Доступ к элементам коллекций
Определяемые пользователем типы данных
Структуры
Перечисления
Управление выполнением программы
Циклы
Инструкции выбора
Инструкции безусловного перехода
Процедуры и функции
Объявление процедур
Вызов процедур
Объявление функций. Возврат значения
Вызов функций
Особенности передачи параметров
Определение и преобразование типов переменных
Определение типов переменных
Преобразование типов
Файловый ввод/вывод
Открытие файлов
Дескрипторы файлов. Функция FreeFile
Закрытие файлов
Чтение из файлов и запись в файлы
Определение конца файла
Определение текущей позиции файла
Стандартные окна сообщений
Обработка ошибок времени выполнения
Перехват ошибок
Обработка перехваченных ошибок
Классы в VBA
Создание класса на VBA
Свойства класса
Методы класса
Использование класса в программе
Использование API-функций в VBA
Объявление API-функций
Вызов API-функций
Использование объектов Excel
Объектная модель Excel
Доступ к объектам Excel из программы
Глава 2 Рабочая область Microsoft Excel
Рабочая книга
Автозапуск любимого файла при загрузке Excel
Восстановление важной информации из испорченного файла
Быстрое размножение рабочей книги
Сохранение рабочей книги с именем, представляющим собой текущую дату
Создание книги с одним листом
Установка и снятие защиты рабочей книги
Закрытие рабочей книги только при выполнении условия
Быстрое удаление из рабочей книги ненужных имен
Сортировка листов в текущей рабочей книге
Рабочий лист
Листы-синонимы
Автоматическая вставка URL-адреса
Быстрый переход по рабочему листу
Разные листы с общими данными
Рисование «правильных» фигур
Скрытие данных от посторонних
Блокировка использования контекстного меню
Вставка колонтитула с именем книги, листа и текущей датой
Проверка существования листа
Проверка, защищен ли рабочий лист
Сколько страниц на всех листах?
Автоматический пересчет данных таблицы при изменении ее значений
Ячейка и диапазон
Быстрое заполнение ячеек
Автоматизация ввода данных в ячейки
Ввод дробных чисел
Сбор данных из разных ячеек
Выделение диапазона над текущей ячейкой
Поиск ближайшей пустой ячейки столбца
Поиск максимального значения в диапазоне
Автоматическая замена значений диапазона
Засекречивание содержимого ячейки
Всем ячейкам диапазона – одно значение
Добавление в ячейку раскрывающегося списка
Быстрое заполнение диапазона
Гиперссылки – в виде обычного текста
Помещение в ячейку электронных часов
«Будильник»
Поиск данных в диапазоне
Создание цветной границы диапазона
Автоматическое определение адреса ячейки
Автоматизация добавления примечаний в указанном диапазоне
Заливка диапазона
Ввод строго ограниченных значений в указанный диапазон
Последовательный ввод данных
Быстрое выделение ячеек с отрицательными значениями
Получение информации о выделенном диапазоне
Кнопка для изменения числового формата ячейки
Тестирование скорости чтения и записи диапазонов
Работа с формулами
Сложение и вычитание даты и времени
Сложение диапазонов разных листов
Накопление итога в ячейке
Быстрое размножение формул
Маскировка формул от других пользователей
Быстрое суммирование всех ячеек столбца или строки
Вместо формулы – текущее значение
Повышение точности вычисления формул
Скрытие сообщений об ошибках при вычислениях
Разработка и применение полезных пользовательских функций
Объединение данных диапазона
Объединение данных с учетом форматов
Эксперименты с датой
Выбор из текста всех чисел
Прописная буква только в начале текста
Перевод чисел в «деньги»
Подсчет количества повторов искомого текста
Суммирование данных только видимых ячеек
При суммировании – курсор внутри диапазона
Начисление процентов в зависимости от суммы
Еще о расчете процентов
Сводный пример расчета комиссионного вознаграждения
Подсчет количества ячеек, содержащих указанные значения
Подсчет количества видимых ячеек в диапазоне
Поиск ближайшего понедельника
Подсчет количества полных лет
Проверка, была ли сохранена рабочая книга
Расчет средневзвешенного значения
Преобразование номера месяца в его название
Расчет суммы первых значений диапазона
Поиск последней непустой ячейки диапазона
Поиск последней непустой ячейки столбца
Поиск последней непустой ячейки строки
Подсчет количества ячеек в диапазоне, содержащих указанные значения
Англоязычный текст – заглавными буквами
Отображение текста «задом наперед»
Поиск максимального значения на всех листах книги
Использование относительных ссылок
Определение типа данных ячейки
Выделение из текста произвольного элемента
Генератор случайных чисел
Случайные числа – на основании диапазона
Глава 3 Создание трюков с помощью макросов
Подсчет количества открытий файла
Получение «закрытой» информации
Произвольный текст в строке состояния
Быстрое изменение заголовка окна
Ввод данных с помощью диалогового окна
Применение функции без ввода ее в ячейку
Скрытие строк и столбцов от посторонних
Быстрое выделение ячеек, расположенных через интервал
Определение количества ячеек в диапазоне и суммы их значений
Подсчет именованных объектов
Быстрый поиск курсора
Поиск начала и окончания диапазона, содержащего данные
Трюки с примечаниями
Подсчет примечаний
Вывод на экран всех примечаний рабочего листа
Создание списка примечаний рабочего листа
Несколько трюков в одном примере
Дополнение панели инструментов
Примеры создания панелей инструментов
Формирование пользовательского меню
Проверка наличия файла по указанному пути
Автоматизация удаления файлов
Перечень имен листов в виде гиперссылок
Удаление пустых строк на рабочем листе
Запись текущих данных в текстовый файл
Экспорт и импорт данных
Одновременное умножение всех данных диапазона
Преобразование таблицы Excel в HTML-формат
Поиск данных нештатными средствами
Включение автофильтра с помощью макроса
Трюки с форматированием
Изменение формата представления чисел нештатными средствами
Помещение последнего символа ячейки над строкой
Создание нестандартной рамки
Быстрая вставка фамилий должностных лиц в документ
Вызов окна настройки шрифта
Вывод информации о текущем документе
Вывод результата расчетов в отдельном окне
Вывод разрешения монитора
Что открыто в данный момент?
Создание бегущей строки
Мигающая ячейка
Вращающиеся автофигуры
Вызов таблицы цветов
Создание калькулятора
Еще о создании пользовательских меню
Меню с пользовательскими командами
Меню со стандартными командами
Склонение фамилии, имени и отчества
Получение информации об используемом принтере
Вывод текущей даты и времени
Автоматическое создание документов Word на основе табличных данных Excel
Создание списка панелей инструментов и контекстных меню
Создание списка пунктов главного меню Excel
Создание списка пунктов контекстных меню
Отображение панели инструментов при определенном условии
Скрытие и отображение панелей инструментов
Создание меню на основе данных рабочего листа
Создание контекстного меню
Просмотр содержимого папки
Получение информации о состоянии дисков
Расчет среднего арифметического
Вывод списка доступных шрифтов
Создание раскрывающегося списка
Добавление команды на вкладку
Добавление команды «Очистить все, кроме формул»
Добавление команды «Линии сетки»
Глава 4 Эксперименты с диаграммами
Построение диаграммы с помощью макроса
Сохранение диаграммы в отдельном файле
Построение и удаление диаграммы нажатием одной кнопки
Вывод списка диаграмм в отдельном окне
Применение случайной цветовой палитры
Эффект прозрачности диаграммы
Построение диаграммы на основе данных нескольких рабочих листов
Создание подписей к данным диаграммы
Глава 5 Создание полезных программ
Программа для составления кроссвордов
Написание макросов
Создание пользовательских форм
Порядок использования программы
Игра «Минное поле»
Игра «Угадай животное»
Расчет на основании ячеек определенного цвета
Создание программы
Работа с программой
Глава 6 Полезные советы
Заключение
Приложение Основные объекты Excel
Объект Application
Объект Chart
Объект Range
Объект Workbook
Объект Worksheet
Введение
В настоящее время табличный редактор Excel, который является разработкой корпорации Microsoft и входит в состав пакета Microsoft Office, – один из самых популярных программных продуктов. Во многом это обусловлено возможностью применения Excel в самых разных отраслях: данную программу используют математики, IT-разработчики, инженеры, экономисты, бухгалтеры, аналитики, менеджеры и т. д. Такое распространение Excel объясняется широкими функциональными возможностями программы и вместе с этим простотой в использовании (удобный и понятный пользовательский интерфейс, возможность быстрого ввода и обработки данных, наглядность представления информации и др.).
Порядок использования программы подробно описывается в справочной подсистеме (для вызова справки достаточно нажать клавишу F1 на клавиатуре или кнопку Справка: Microsoft Offic Excel на ленте в окне приложения). Однако в процессе эксплуатации программы можно также использовать приемы и методы, которые в стандартной документации не рассматриваются либо рассматриваются поверхностно – как правило, потому, что они становятся известны только в результате активной эксплуатации Excel (то есть открываются опытным путем). Иногда они являются сюрпризом даже для самих разработчиков и открывают новые, порой самые неожиданные возможности программы. Описанию подобных трюков и посвящена эта книга.
Большинство описываемых в книге трюков и эффектов выполняется средствами языка VBA (для перехода к редактору VBA используется комбинация клавиш Alt+F11). Книга также содержит описание приемов, выполняемых «подручными» средствами, без программирования.
Структура книги
В главе 1 рассказывается об основах программирования на встроенном в пакет Microsoft Office языке Visual Basic for Applications (VBA). Главный упор сделан на описание синтаксиса и особенностей использования основных конструкций VBA.
В главе 2 рассматриваются нестандартные, но вместе с этим полезные и практичные решения, которые можно реализовать в рабочей области программы; здесь же рассказывается о приемах работы с файлами Excel (то есть с рабочей книгой). Приводится описание нестандартного использования формул Excel, а также порядок формирования и применения большого количества пользовательских функций. В отдельный раздел вынесено описание трюков и эффектов, которые применяются к ячейкам и диапазонам рабочего листа.
Глава 3 – самая объемная. В ней описывается большое количество самых разнообразных трюков и эффектов, относящихся к различным сферам использования программы. В частности, здесь рассказывается о создании пользовательских меню (как обычных, так и контекстных) и панелей инструментов, о нестандартных приемах форматирования, о работе с примечаниями и др. Большинство приведенных в данной главе трюков имеют практический характер, но есть и такие, реализация которых служит лишь развлекательным либо эстетическим целям.
У пользователей, часто работающих с диаграммами, наверняка вызовет интерес глава 4. В ней рассказывается, как быстро создать диаграмму с помощью макроса, построить диаграмму на основании данных нескольких рабочих листов, быстро изменить тип диаграммы, используя специально созданное контекстное меню, и др.
В главе 5 приведено несколько примеров создания небольших программ – как развлекательных, так и применяемых в работе, а также показаны приемы использования созданных программ.
В главе 6 содержится перечень полезных советов, которые пригодятся и начинающим, и опытным пользователям программы. Для удобства восприятия материал представлен в режиме «вопрос – ответ».
В приложении описаны наиболее часто используемые в приведенных в книге примерах стандартные объекты Excel: Application, Chart, Range, Workbook и Worksheet.
Общие положения
При написании книги использовалась версия Excel 2007. Тем не менее большинство приведенных в книге трюков работает и в других версиях программы.
Перед тем как вплотную приступать к изучению нестандартных приемов работы с Excel, кратко вспомним, для решения каких задач предназначено данное приложение и каковы его функциональные возможности, а также определимся с основными терминами и понятиями, которыми мы будем оперировать в дальнейшем.
Подразумевается, что данную книгу будут изучать пользователи, имеющие как минимум начальное представление об Excel. Тем не менее не будет лишним вспомнить, какие задачи решаются с помощью этой программы, а также ознакомиться с используемой в издании терминологией.
Назначение и функциональные возможности Microsoft Excel
Табличный редактор Microsoft Excel предназначен для решения следующих задач.
• Ввод и обработка табличных данных с использованием встроенных механизмов формул, функций, макросов и др.
• Анализ и управление данными (автоматический расчет итоговых и промежуточных данных, структуризация и консолидация данных, использование сводных таблиц, отчетов и др.).
• Импорт необходимых данных из различных источников (включая базы данных OLAP) и последующая их обработка. Поддержка XML-формата.
• Работа с графическими объектами и диаграммами.
• Взаимодействие и обмен данными с программой Lotus Notes, а также интеграция с другими программными продуктами («Галактика», «1С» и др.).
• Работа в Интернете (изменение данных на веб-странице, размещение данных Microsoft Excel в Сети, поддержка веб-файлов, гиперссылок и др.).
• Доступ к данным совместно с другими программами (Word, PowerPoint, Access и др.).
• Формирование самых разнообразных отчетов: аналитических, сводных, графических, в виде диаграмм и др.
• Выполнение стандартных функций Microsoft Office: печать документа, поиск данных и их замена, проверка наличия ошибок, защита информации и др.
• Создание приложений с применением языка программирования VBA.
С помощью Excel можно решать и другие задачи, возникновение которых обусловлено потребностями конкретного пользователя.
Используемая терминология
В данной книге используются следующие основные термины и понятия.
• Автофигура – готовая к использованию фигура заданной формы, которую можно добавлять на рабочий лист или в диаграмму. В Excel имеется встроенный набор автофигур.
• Диаграмма – визуальный способ представления числовых значений. Программа Excel поддерживает работу с различными видами диаграмм: круговыми, пузырьковыми, гистограммами, графиками и др.
• Имя – идентификатор, который предоставляет возможность ссылаться на какой-либо объект (ячейку, диапазон, формулу и т. д.).
• Комментарий – текст, который следует в программном коде сразу после символа «» вплоть до окончания данной строки и игнорируется при выполнении программы. Комментарий обычно включает в себя произвольную информацию вспомогательного характера, предназначенную для описания и пояснения определенных фрагментов кода либо всего кода.
• Контекстное меню – меню, содержащее список команд, которые предназначены для работы с конкретным объектом. Для вызова контекстного меню нужно щелкнуть на объекте правой кнопкой мыши или нажать комбинацию клавиш Shift+F10.
• Макрос – программа, которая написана на встроенном в Excel языке программирования Visual Basic for Applications (VBA). Переход в режим работы с макросами осуществляется с помощью команды Вид → Макросы.
• Массив – определенное количество ячеек либо значений, с которыми работают как с единым целым. Иначе говоря, массив – это группа элементов одного типа, которые имеют общее имя.
• Модуль – совокупность описаний, инструкций и процедур, сохраненная под общим именем в редакторе VBA.
• Надстройка – программа, внедренная в Excel для расширения функциональных возможностей. Чтобы подключить надстройки, следует в режиме настройки программы в разделе Настройки в поле Управление выбрать значение Настройки Excel и нажать кнопку Перейти, после чего в открывшемся окне установить требуемые флажки.
• Настройка – изменение ныне действующих параметров работы Microsoft Excel стандартными средствами, доступ к которым осуществляется из рабочего интерфейса Excel. Параметры работы программы можно разделить на два основных вида.
– Общие параметры – редактирование этих параметров приведет к соответствующим изменениям во всех рабочих книгах, в том числе и во вновь создаваемых.
– Локальные параметры – редактирование этих параметров вызовет соответствующие изменения только в текущей книге.
Примечание
Некоторые параметры работы Microsoft 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/.
От издательства
Ваши замечания, предложения и вопросы отправляйте по адресу электронной почты dgurski@minsk.piter.com (издательство «Питер», компьютерная редакция). Мы будем рады узнать ваше мнение!
На сайте издательства http://www.piter.com вы найдете подробную информацию о наших книгах.
Глава 1
Краткое руководство по VBA
Цель данной главы – ознакомить читателя с основами программирования на языке Visual Basic for Applications (VBA), который встроен в пакет Microsoft Office.
Эта глава по форме изложения скорее похожа на справочник. Она рассчитана на пользователей, имеющих некоторый опыт создания программ на других объектно-ориентированных языках программирования, то есть представляющих, что такое переменная, константа, оператор, цикл, массив, класс, объект и т. д. Главный упор в данной главе сделан на описание синтаксиса и особенностей использования основных конструкций VBA, которые нужно знать для понимания приведенных в тексте листингов.
Знакомство с VBA
VBA – это язык программирования, поддерживаемый большинством приложений пакета Microsoft Office. Для запуска среды программирования VBA можно использовать сочетание клавиш Alt+F11.
