Table Of ContentА. Гладкий, А. Чиртик
е 1 Ш 1 | >/
т р ю ки © э Ф Ф
Москва • Санкт-Петербург • НИЖНИЙ Новгород • Воронеж
Ростов-на-Дону • Екатеринбург • Самара • Новосибирск
Киев • Харьков • Минск
2006
А. А. Гладкий, А. А. Чиртик
Excel. Трюки и эффекты
Серия «Трюки и эффекты»
Заведующий редакцией Д. Гурский
Руководитель проекта М. Моисеева
Научные редакторы М. Моисеева, А. Чиртик
Художник А. Татарко
Корректоры Т. Куръянович, Е, Павлович
Верстка Д. Коршук
ББК 32.973.23-018.2
УДК 004.42
Гладкий А. А., Чиртик А. А.
Г52 Excel. Трюки и эффекты. — СПб.: Питер, 2006. — 368 с: ил. — (Серия «Трюки и
эффекты»).
ISBN 5-469-01223-9
Данная книга предназначена для пользователей Microsoft Excel и содержит описание приемов и ме-
тодов работы, которые ввиду своей специфичности недостаточно представлены (либо вообще не пред-
ставлены) в пользовательской, справочной и иной соответствующей документации. Изучение приведен-
ных в книге примеров позволит читателю открыть для себя неизвестные ранее возможности Excel. Пред-
лагаемый материал легко усваивается благодаря тому, что излагается доступным и понятным языком.
© ЗАО Издательский дом «Питер», 2006
Все права защищены. Никакая часть данной книги не может быть воспроизведена в какой бы то ни было форме без письменного
разрешения владельцев авторских прав.
Информация, содержащаяся в данной книге, получена из источников, рассматриваемых издательством как надежные. Тем не
менее, имея в виду возможные человеческие или технические ошибки, издательство не может гарантировать абсолютную точ-
ность и полноту приводимых сведений и не несет ответственность за возможные ошибки, связанные с использованием книги.
ISBN 5-469-01223-9
ООО «Питер Принт». 194044, Санкт-Петербург. Б. Сампсониевский пр., 29а.
Лицензия ИД № 05784 от 07.09.01.
Налоговая льгота — общероссийский классификатор продукции ОК 005-93, том 2; 953005 — литература учебная.
Подписано в печать 02.12.05. Формат 70Х1007,. Усл. п. л. 29,67. Тираж 3500 экз. Заказ № 6843.
6
Отпечатано с готовых диапозитивов в ФГУП «Печатный двор» им. А. М. Горького
Федерального агентства по печати и массовым коммуникациям.
197110, Санкт-Петербург, Чкаловский пр., 15.
Краткое содержание
Введение 11
От издательства 16
Глава 1. Краткое руководство по VBA 17
Глава 2. Рабочая область Microsoft Excel 80
Глава 3. Создание трюков с помощью макросов 168
Глава 4. Трюки и эффекты с помощником 281
Глава 5. Эксперименты с диаграммами 289
Глава 6. Создание полезных программ 306
Глава 7. Полезные советы 339
Приложение. Основные объекты Excel 351
Алфавитный указатель 367
Оглавление
Введение 11
Структура книги : 11
Общие положения 12
Назначение и функциональные возможности Microsoft Excel 13
Используемая терминология 13
Тексты программ на сайте издательства 16
От издательства 16
Глава 1. Краткое руководство по VBA 17
Знакомство cVBA 18
Возможности VBA 18
Структура проекта VBA 18
Структура модуля VBA 21
Соглашения, применяемые при описании синтаксиса VBA 21
Комментарии в программе 23
Идентификаторы 23
Переменные 24
Встроенные типы данных 24
Ссылки. Тип данных Object 26
Объявление переменных 27
Инициализация переменных 28
Явное и неявное объявление переменных 28
Константы 29
Операторы 29
Операторы для работы счисленными значениями 29
Операторы сравнения 30
Логические операторы 32
Массивы 32
Объявление массива 33
Задание нижней границы по умолчанию 34
Изменение размера массива 34
Определение границ массива 35
Доступ кэлементам массива 35
Использование переменной Variant при работе с массивами 35
Использование функции Array для заполнения массива 36
Оглавление
Коллекции 36
Добавление элементов 37
Количество элементов в коллекции 37
Удаление элементов из коллекции 38
Доступ к элементам коллекций 38
Определяемые пользователем типы данных 38
Структуры 38
Перечисления 41
Управление выполнением программы 42
Циклы 43
Инструкции выбора 46
Инструкции безусловного перехода 49
Процедуры и функции 51
Объявление процедур 51
Вызов процедур 53
Объявление функций. Возврат значения 54
Вызов функций 55
Особенности передачи параметров 56
Определение и преобразование типов переменных 58
Определение типов переменных 58
Преобразование типов 60
Файловый ввод/вывод 61
Открытие файлов 61
Дескрипторы файлов. Функция FreeFile 62
Закрытие файлов 63
Чтение из файлов и запись в файлы 63
Определение конца файла 66
Определение текущей позиции файла 66
Стандартные окна сообщений 67
Обработка ошибок времени выполнения 68
Перехват ошибок 69
Обработка перехваченных ошибок 69
Классы в VBA 71
Создание класса на VBA 71
Свойства класса 72
Методы класса ,. 73
Использование класса в программе 73
Использование API-функций в VBA 74
Объявление API-функций 75
Вызов API-функций 76
Использование объектов Excel 76
Объектная модель Excel 77
Доступ к объектам Excel из программы 78
Оглавление
Глава 2. Рабочая область Microsoft Excel 80
Рабочая книга 81
Автозапуск любимого файла при загрузке Excel 81
Эксперименте кнопкой Закрыть на панели инструментов 81
Общий макрос для всех книг Excel 82
Восстановление важной информации из испорченного файла 83
Быстрое размножение рабочей книги 83
Сохранение рабочей книги с именем, представляющим
собой текущую дату 84
Создание книги с одним листом 85
Установка и снятие защиты рабочей книги 85
Закрытие рабочей книги только при выполнении условия 86
Быстрое удаление из рабочей книги ненужных имен 87
Сортировка листов втекущей рабочей книге 88
Импорт данных, для которых нужно более 256 столбцов 92
Рабочий лист 95
Листы-синонимы 95
Автоматическая вставка URL-адреса 95
Быстрый переход по рабочему листу 96
Разные листы с общими данными 97
Рисование «правильных» фигур 97
Скрытие данных от посторонних 97
Новый лист —через макрос 98
Блокировка использования контекстного меню 98
Вставка колонтитула с именем книги, листа и текущей датой 99
Проверка существования листа 99
Проверка, защищен ли рабочий лист 100
Сколько страниц на всех листах? 101
Автоматический пересчет данных таблицы
при изменении ее значений 101
Ячейка и диапазон 103
Быстрое заполнение ячеек 103
Автоматизация ввода данных в ячейки 103
Ввод дробных чисел 104
Сбор данных из разных ячеек 105
Выделение диапазона над текущей ячейкой 105
Поиск ближайшей пустой ячейки столбца 106
Поиск максимального значения в диапазоне 106
Автоматическая замена значений диапазона 107
Засекречивание содержимого ячейки 107
Всем ячейкам диапазона — одно значение 109
«Фотографирование» ячеек 109
Добавление в ячейку раскрывающегося списка 110
Быстрое заполнение диапазона 110
Гиперссылки — в виде обычного текста 114
Помещение в ячейку электронных часов 115
Оглавление
«Будильник» 115
Поиск данных в диапазоне 116
Создание цветной границы диапазона 118
Автоматическое определение адреса ячейки 118
Автоматизация добавления примечаний в указанном диапазоне 119
Заливка диапазона 120
Ввод строго ограниченных значений в указанный диапазон 120
Последовательный ввод данных 123
Быстрое выделение ячеек с отрицательными значениями 124
Получение информации о выделенном диапазоне 125
Панель инструментов изменения числового формата ячейки 127
Тестирование скорости чтения и записи диапазонов 129
Работа с формулами 131
Складывание и вычитание даты и времени 131
Складывание диапазонов разных листов 131
Накопление итога в ячейке 131
Быстрое размножение формул 132
Маскировка формул от других пользователей 132
Быстрое суммирование всех ячеек столбца или строки 133
Вместо формулы — текущее значение 133
Повышение точности вычисления формул 134
Быстрая проверка правильности формул 134
Скрытие сообщений об ошибках при вычислениях 136
Разработкам применение полезных пользовательских функций 136
Объединение данных диапазона 137
Объединение данных с учетом форматов 138
Эксперименты с датой 139
Выбор из текста всех чисел 139
Прописная буква только в начале текста 140
Перевод чисел в «деньги» 140
Подсчет количества повторов искомого текста 142
Суммирование данных только видимых ячеек 143
При суммировании — курсор внутри диапазона 143
Вывод суммы прописью 144
Начисление процентов в зависимости от суммы 144
Еще о расчете процентов 146
Сводный пример расчета комиссионного вознаграждения 147
Подсчет количества ячеек, содержащих указанные значения 150
Подсчет количества видимых ячеек в диапазоне 151
Поиск ближайшего понедельника 152
Подсчет количества полных лет 153
Проверка, была ли сохранена рабочая книга 153
Расчет средневзвешенного значения 154
Преобразование номера месяца в его название 155
Расчет суммы первых значений диапазона 155
Поиск последней непустой ячейки диапазона 156
Поиск последней непустой ячейки столбца 156
Оглавление
Поиск последней непустой ячейки строки 157
Подсчет количества ячеек в диапазоне,
содержащих указанные значения 157
Англоязычный текст — заглавными буквами 158
Отображение текста «задом наперед» 159
Поиск максимального значения на всех листах книги 160
Использование относительных ссылок 161
Определение типа данных ячейки 162
Выделение из текста произвольного элемента 163
Генератор случайных чисел 165
Случайные числа — на основании диапазона 166
Глава 3. Создание трюков с помощью макросов 168
Подсчет количества открытий файла • 169
Получение «закрытой» информации 170
Произвольный текст в строке состояния 170
Быстрое изменение заголовка окна 171
Ввод данных с помощью диалогового окна 173
Применение функции без ввода ее в ячейку 174
Скрытие строки столбцов от посторонних 174
Быстрое выделение ячеек, расположенных через интервал 177
Определение количества ячеек в диапазоне и суммы их значений 178
Подсчет именованных объектов 180
Быстрый поиск курсора 181
Поиск начала и окончания диапазона, содержащего данные 181
Трюки с примечаниями 182
Подсчет примечаний 182
Вывод на экран всех примечаний рабочего листа 183
Создание списка примечаний рабочего листа 184
Несколько трюков водном примере 186
Дополнение панели инструментов 188
Примеры создания панелей инструментов 189
Формирование пользовательского меню 192
Проверка наличия файла по указанному пути 194
Автоматизация удаления файлов 197
Перечень имен листов в виде гиперссылок 198
Удаление пустых строк на рабочем листе 199
Запись текущих данных в текстовый файл 200
Экспорт и импорт данных 201
Одновременное умножение всех данных диапазона 203
Преобразование таблицы Excel в HTML-формат 205
Оглавление
Поиск данных нештатными средствами 210
Включение автофильтра с помощью макроса 211
Трюки с форматированием 212
Изменение формата представления чисел нештатными средствами ... 212
Помещение последнего символа ячейки над строкой 213
Создание нестандартной рамки 213
Быстрая вставка фамилий должностных лиц в документ 214
Вызов окна настройки шрифта 215
Вывод информации о текущем документе 216
Вывод результата расчетов в отдельном окне 216
Вывод разрешения монитора 217
Что открыто в данный момент? 218
Создание бегущей строки 219
Создание бегущей картинки 219
Мигающая ячейка 221
Вращающиеся автофигуры 222
Вызов таблицы цветов 225
Создание калькулятора 226
Еще о создании пользовательских меню 228
Меню с пользовательскими командами 228
Замена стандартного меню пользовательским 233
Склонение фамилии, имени и отчества 234
Получение информации об используемом принтере 239
Вывод текущей даты и времени 241
Автоматическое создание документов
Word на основе табличных данных Excel 242
Создание списка панелей инструментов и контекстных меню 245
Создание списка пунктов главного меню Excel 246
Создание списка пунктов контекстных меню 247
Отображение панели инструментов при определенном условии 248
Скрытие и отображение панелей инструментов 251
Создание меню на основе данных рабочего листа 252
Создание контекстного меню 256
Просмотр содержимого папки 260
Получение информации о состоянии дисков 265
Расчет среднего арифметического 267
Вывод списка доступных шрифтов 268
Обработка сразу нескольких внешних файлов 269
Запуск таблицы символов из Excel 271
Создание раскрывающегося списка 274
