2011г.
не нуждается в дополнительном представлении или рекламе. Количество пользователей программы чрезвычайно велико, поскольку она позволяет решать множество прикладных задач. Однако далеко не все используют возможности Excel на все 100 %. Многие даже не догадываются о разнообразии ее функций. Ведь эта программа — не просто большой калькулятор. Она содержит многочисленные графические инструменты, предоставляет возможность экономико-статистических расчетов, включает в себя язык программирования VBA. Данная книга — не банальное руководство для начинающих. Она, скорее, предназначена для пользователей, которые уже имеют начальные навыки работы в Excel, но понимают, что им еще нужно многому научиться, чтобы применять возможности этого многофункционального приложения по максимуму. Новая книга
Джона Уокенбаха, одного из самых известных и авторитетных специалистов Microsoft Excel, состоит из более 200 профессиональных трюков и советов по свежей версии программы. Данное руководство
написано в легкой и непринужденной форме. Вы не найдете в нем сухой теории — только реально применяемый на практике материал. Благодаря этой книге вы научитесь делать в Microsoft Excel 2010 то, о чем раньше даже и не подозревали!
Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21
Что вы должны знать . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21
Что у вас должно быть . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21
Обозначения, используемые в этой книге . . . . . . . . . . . . . . . . . . . .22
Примеры формул . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .22
Примеры кода VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .22
Названия клавиш . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .22
Лента . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .22
Функции, процедуры и именованные диапазоны . . . . . . . . . .23
Соглашения по использованию мыши . . . . . . . . . . . . . . . . . .23
Что обозначают врезки . . . . . . . . . . . . . . . . . . . . . . . . . . . .23
Ввод кода VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24
Как построена книга . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24
Как пользоваться книгой . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .25
Обратная связь . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .25
Об авторе . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .26
От издательства . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .26
Часть I. Основы использования Excel . . . . . . . . . . . . . . . . . . . . . . .27
Совет 1. Различия версий Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . .29
Совет 2. Максимально эффективное использование ленты . . . . . . .32
Совет 3. Описание режима защищенного просмотра . . . . . . . . . . . .35
Совет 4. Эффективно выбираем ячейки . . . . . . . . . . . . . . . . . . . . .37
Выбор диапазона ячеек с помощью клавиши Shift
и стрелок . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .37
Выбор текущей области . . . . . . . . . . . . . . . . . . . . . . . . . . . .37
Выбор диапазона с помощью клавиши Shift и мыши . . . . . . .38
Выбор несмежных диапазонов . . . . . . . . . . . . . . . . . . . . . . .38
Выбор строк . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .38
Выбор столбцов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .39
Выбор диапазонов, расположенных на разных листах
книги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .39
Совет 5. Выбор «особых» диапазонов . . . . . . . . . . . . . . . . . . . . . . .41
Совет 6. Отмена, возврат и повтор . . . . . . . . . . . . . . . . . . . . . . . . .43
Отмена . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43
Возврат . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .44
Повтор . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .44
Совет 7. Знакомство с полезными сочетаниями клавиш . . . . . . . . . .46
Совет 8. Навигация по листам в книге . . . . . . . . . . . . . . . . . . . . . .47
Совет 9. Переопределение используемой области листа . . . . . . . . .48
Совет 10. Различие понятий «книга» и «окно» . . . . . . . . . . . . . . . .49
Совет 11. Настройка панели быстрого доступа . . . . . . . . . . . . . . . .51
О панели быстрого доступа . . . . . . . . . . . . . . . . . . . . . . . . .51
Добавление новых команд на панель быстрого доступа . . . .52
Другие действия с панелью быстрого доступа . . . . . . . . . . . .54
Совет 12. Настройка ленты . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56
Совет 13. Доступ к ленте с клавиатуры . . . . . . . . . . . . . . . . . . . . . .59
Совет 14. Восстановление документа . . . . . . . . . . . . . . . . . . . . . . .60
Восстановление версий текущей книги . . . . . . . . . . . . . . . . .60
Восстановление несохраненной работы . . . . . . . . . . . . . . . .61
Совет 15. Настройка книги, используемой по умолчанию . . . . . . . .62
Изменение заданных по умолчанию значений
в диалоговом окне Параметры Excel . . . . . . . . . . . . . . . . . . .62
Создание шаблона книга.xltx . . . . . . . . . . . . . . . . . . . . . . . .63
Совет 16 . Использование тем документов . . . . . . . . . . . . . . . . . . . .65
Применение темы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65
Настройка темы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .67
Совет 17. Скрытие элементов пользовательского интерфейса . . . . .70
Скрытие элементов с помощью команд ленты . . . . . . . . . . . .70
Скрытие элементов главного окна с помощью клавиатуры . . .70
Скрытие элементов с помощью диалогового
окна Параметры Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .70
Настройка, которая требует использования макроса . . . . . . .72
Совет 18. Скрытие столбцов или строк . . . . . . . . . . . . . . . . . . . . . .73
Скрытие . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .73
Возвращение на экран . . . . . . . . . . . . . . . . . . . . . . . . . . . . .73
Совет 19. Скрытие содержимого ячейки . . . . . . . . . . . . . . . . . . . . .75
Совет 20. Создание снимков диапазонов ячеек . . . . . . . . . . . . . . . .76
Создание статичного изображения диапазона ячеек . . . . . . .76
Создание динамичного снимка диапазона . . . . . . . . . . . . . . .77
Совет 21. Неточный поиск . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .78
Совет 22. Изменение форматирования содержимого ячеек . . . . . . .80
Совет 23. Изменение цветовой схемы Excel . . . . . . . . . . . . . . . . . . .83
Совет 24. Ограничение полезной площади в листах . . . . . . . . . . . .84
Установка свойства ScrollArea . . . . . . . . . . . . . . . . . . . . . . . .84
Использование защиты листа . . . . . . . . . . . . . . . . . . . . . . . .85
Совет 25. Альтернатива примечаниям к ячейкам . . . . . . . . . . . . . . .87
Совет 26. Использование справки Excel . . . . . . . . . . . . . . . . . . . . .88
Совет 27. Создание «очень скрытого» листа . . . . . . . . . . . . . . . . . .91
Совет 28. Использование представления Backstage . . . . . . . . . . . . .93
Часть II. Ввод данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .95
Совет 29. Понимание типов данных . . . . . . . . . . . . . . . . . . . . . . . .97
Ввод значений . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .97
Ввод даты и времени . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .97
Ввод текста . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .99
Ввод формул . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .99
Совет 30. Перемещения указателя ячейки после ввода данных . . . 100
Совет 31. Выбор диапазона ячеек перед вводом данных . . . . . . . . 101
Совет 32. Использование автоматического завершения
для автоматизации ввода данных . . . . . . . . . . . . . . . . . . . . . . . . . 102
Совет 33. Удаление повторяющихся строк . . . . . . . . . . . . . . . . . . 104
Совет 34. Не упускаем заголовки из виду . . . . . . . . . . . . . . . . . . . 106
Совет 35. Автоматическое заполнение диапазона набором
значений . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Совет 36. Работа с дробями . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Совет 37. Изменение размеров строки формул . . . . . . . . . . . . . . . 113
Совет 38. Проверка данных с помощью звука . . . . . . . . . . . . . . . . 115
Добавление речевых команд на ленту . . . . . . . . . . . . . . . . 115
Использование голосовых команд . . . . . . . . . . . . . . . . . . . . 115
Совет 39. Управление автоматическими гиперссылками . . . . . . . . 117
Отмена автоматической гиперссылки . . . . . . . . . . . . . . . . . 117
Отключение автоматических гиперссылок . . . . . . . . . . . . . 117
Удаление существующих гиперссылок . . . . . . . . . . . . . . . . 118
Совет 40. Ввод номеров кредитных карт . . . . . . . . . . . . . . . . . . . . 119
Совет 41. Использование встроенной в Excel формы
для ввода данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Совет 42. Настройка и совместное использование автозамены . . . 122
Совет 43. Ограничение передвижения курсора ячейками
для ввода . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
Совет 44. Управление буфером обмена Office . . . . . . . . . . . . . . . . 126
Совет 45. Создание в ячейке раскрывающегося списка . . . . . . . . . 128
Часть III. Форматирование . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Совет 46. Использование мини-панели инструментов . . . . . . . . . . 133
Совет 47. Задание отступов для содержимого ячеек . . . . . . . . . . . 135
Совет 48. Быстрое форматирование чисел . . . . . . . . . . . . . . . . . . 137
Совет 49. Создание пользовательских форматов чисел . . . . . . . . . 138
Части строки формата числа . . . . . . . . . . . . . . . . . . . . . . . 139
Коды пользовательских форматов чисел . . . . . . . . . . . . . . . 140
Совет 50. Пользовательские числовые форматы
для масштабирования значений . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Совет 51. Использование пользовательского форматирования
даты и времени . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Совет 52. Изучение некоторых полезных пользовательских
числовых форматов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Скрытие нулей . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Отображение ведущих нулей . . . . . . . . . . . . . . . . . . . . . . . 145
Форматирование процентов . . . . . . . . . . . . . . . . . . . . . . . . 145
Отображение дробей . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
Повторение текста . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
Отображение знака «минус» справа . . . . . . . . . . . . . . . . . . 146
Скрытие определенных типов записей . . . . . . . . . . . . . . . . 147
Совет 53. Обновление старых шрифтов . . . . . . . . . . . . . . . . . . . . 148
Совет 54. Визуализация при условном форматировании . . . . . . . . 150
Гистограммы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
Палитра цветов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
Наборы ячеек . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
Совет 55. Отображение текста и значения в ячейке . . . . . . . . . . . 153
Конкатенация . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Функция ТЕКСТ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Пользовательские числовые форматы . . . . . . . . . . . . . . . . 153
Совет 56. Объединение ячеек . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Совет 57. Форматирование отдельных символов в ячейке . . . . . . . 156
Совет 58. Отображение времени, превышающего 24 часа . . . . . . . 157
Совет 59. Решение проблемы «нечисловых» чисел . . . . . . . . . . . . 159
Совет 60. Добавление рамки к диапазону ячеек . . . . . . . . . . . . . . 160
Совет 61. Работа с сеткой, границами и подчеркиванием . . . . . . . 161
Совет 62. Вставка водяных знаков . . . . . . . . . . . . . . . . . . . . . . . . 163
Совет 63. Добавление фонового рисунка на лист . . . . . . . . . . . . . 165
Совет 64. Перенос текста в ячейке . . . . . . . . . . . . . . . . . . . . . . . . 167
Совет 65. Просмотр всех символов в определенном шрифте . . . . . 169
Совет 66. Ввод специальных символов . . . . . . . . . . . . . . . . . . . . . 171
Совет 67. Использование именованных стилей . . . . . . . . . . . . . . . 173
Использование галереи стилей . . . . . . . . . . . . . . . . . . . . . . 173
Изменение существующего стиля . . . . . . . . . . . . . . . . . . . . 174
Создание новых стилей . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Слияние стилей из разных книг . . . . . . . . . . . . . . . . . . . . . 176
Часть IV. Основные формулы и функции . . . . . . . . . . . . . . . . . . . 177
Совет 68. Использование формулы автозавершения . . . . . . . . . . . 179
Совет 69. Узнаем, когда использовать абсолютные ссылки . . . . . . 181
Совет 70. Узнаем, когда использовать смешанные ссылки . . . . . . . 183
Совет 71. Изменение типа ссылки на ячейку . . . . . . . . . . . . . . . . . 184
Совет 72. Преобразование вертикально расположенного
диапазона данных в таблицу . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
Совет 73. Хитрости использования автосуммирования . . . . . . . . . 187
Совет 74. Использование компонентов строки состояния . . . . . . . 189
Совет 75. Преобразование формул в значения . . . . . . . . . . . . . . . 191
Совет 76. Преобразование данных без использования формул . . . 192
Совет 77. Преобразование данных с помощью временных
формул . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Совет 78. Удаление значений с сохранением формул . . . . . . . . . . 195
Совет 79. Суммирование для разных листов . . . . . . . . . . . . . . . . . 196
Совет 80. Работа с аргументами функции . . . . . . . . . . . . . . . . . . . 198
Совет 81. Аннотирование формулы без использования
примечаний . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
Совет 82. Создание точной копии диапазона формул . . . . . . . . . . 201
Совет 83. Мониторинг формулы ячейки из любого
местоположения . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203
Совет 84. Отображение и печать формул . . . . . . . . . . . . . . . . . . . 204
Совет 85. Как избежать отображения ошибок в формулах . . . . . . . 206
Использование функции ЕОШИБКА . . . . . . . . . . . . . . . . . . . 206
Использование функции ЕСЛИОШИБКА . . . . . . . . . . . . . . . 207
Совет 86. Использование функции Подбор параметра . . . . . . . . . . 208
Совет 87. Раскроем секрет об именах . . . . . . . . . . . . . . . . . . . . . . 210
Совет 88. Использование именованных констант . . . . . . . . . . . . . 211
Совет 89. Использование функций в именах . . . . . . . . . . . . . . . . . 213
Совет 90. Создание списка имен . . . . . . . . . . . . . . . . . . . . . . . . . . 214
Совет 91. Использование динамических имен . . . . . . . . . . . . . . . . 216
Совет 92. Создание имен уровня листа . . . . . . . . . . . . . . . . . . . . . 219
Совет 93. Работа с датами, предшествующими 1900 году . . . . . . . 221
Совет 94. Работа с отрицательными значениями времени . . . . . . . 223
Часть V. Полезные примеры формул . . . . . . . . . . . . . . . . . . . . . . . 225
Совет 95. Расчет даты праздников . . . . . . . . . . . . . . . . . . . . . . . . 227
Новый год . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
День Мартина Лютера Кинга . . . . . . . . . . . . . . . . . . . . . . . 227
День президента . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
Пасха . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
День памяти . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
День независимости . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
День труда . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
День Колумба . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
День ветеранов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
День благодарения . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Рождество . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Совет 96. Расчет средневзвешенного . . . . . . . . . . . . . . . . . . . . . . 230
Совет 97. Расчет возраста человека . . . . . . . . . . . . . . . . . . . . . . . 231
Метод 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
Метод 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
Метод 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
Совет 98. Ранжируем значения . . . . . . . . . . . . . . . . . . . . . . . . . . . 232
Совет 99. Преобразование дюймов в футы и дюймы . . . . . . . . . . . 234
Совет 100. Использование функции РАЗНДАТ . . . . . . . . . . . . . . . . 235
Совет 101. Подсчет символов в ячейке . . . . . . . . . . . . . . . . . . . . . 236
Подсчет всех символов в ячейке . . . . . . . . . . . . . . . . . . . . . 236
Подсчет конкретных символов в ячейке . . . . . . . . . . . . . . . 236
Подсчет вхождений подстроки в ячейке . . . . . . . . . . . . . . . 236
Совет 102. Нумерация недель . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Совет 103. Использование сводной таблицы вместо формул . . . . . 238
Вставка промежуточных итогов . . . . . . . . . . . . . . . . . . . . . 238
Использование формул . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
Использование сводной таблицы . . . . . . . . . . . . . . . . . . . . 239
Совет 104. Выражение числа в качестве порядкового . . . . . . . . . . 242
Совет 105. Извлечение слов из строки . . . . . . . . . . . . . . . . . . . . . 243
Извлечение первого слова из строки . . . . . . . . . . . . . . . . . 243
Извлечение последнего слова строки . . . . . . . . . . . . . . . . . 243
Извлечение всего, кроме первого слова строки . . . . . . . . . . 244
Совет 106. Анализ имен . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
Совет 107. Удаление обращений из имен . . . . . . . . . . . . . . . . . . . 247
Совет 108. Генерирование нескольких дат . . . . . . . . . . . . . . . . . . 248
Использование автозаполнения . . . . . . . . . . . . . . . . . . . . . 248
Использование формул . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
Совет 109. Определение конкретных дат . . . . . . . . . . . . . . . . . . . 250
Определение дня в году . . . . . . . . . . . . . . . . . . . . . . . . . . . 250
Определение дня недели . . . . . . . . . . . . . . . . . . . . . . . . . . 250
Определение даты последнего воскресенья . . . . . . . . . . . . 250
Определение первого дня недели после указанной даты . . . 251
Нахождение n-го определенного дня недели в месяце . . . . 251
Определение последнего дня месяца . . . . . . . . . . . . . . . . . 251
Определение квартала даты . . . . . . . . . . . . . . . . . . . . . . . . 252
Совет 110. Отображение календаря в диапазоне ячеек . . . . . . . . . 253
Совет 111. Различные методы округления чисел . . . . . . . . . . . . . . 254
Округление до ближайшего кратного числа . . . . . . . . . . . . 254
Округление денежных значений . . . . . . . . . . . . . . . . . . . . . 255
Использование функций ЦЕЛОЕ и ОТБР . . . . . . . . . . . . . . . 255
Округление до n значащих цифр . . . . . . . . . . . . . . . . . . . . 256
Совет 112. Округление значений времени . . . . . . . . . . . . . . . . . . . 257
Совет 113. Использование новой функции АГРЕГАТ . . . . . . . . . . . 258
Совет 114. Возвращение последней непустой ячейки в столбец
или строку . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
Совет 115. Использование функции СЧЁТЕСЛИ . . . . . . . . . . . . . . . 262
Совет 116. Подсчет ячеек, которые соответствуют
нескольким критериям . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
Использование критерия И . . . . . . . . . . . . . . . . . . . . . . . . . 263
Использование критерия ИЛИ . . . . . . . . . . . . . . . . . . . . . . . 264
Объединение критериев И и ИЛИ . . . . . . . . . . . . . . . . . . . . 265
Совет 117. Подсчет недублированных записей в диапазоне . . . . . . 266
Совет 118. Расчет условных сумм с одним критерием . . . . . . . . . . 267
Суммирование только отрицательных значений . . . . . . . . . 267
Суммирование значений на основе разных диапазонов . . . . 267
Суммирование значений на основе сравнения текста . . . . . 268
Суммирование значений на основе сравнения дат . . . . . . . 268
Совет 119. Расчет условных сумм с несколькими критериями . . . . 269
Использование критерия И . . . . . . . . . . . . . . . . . . . . . . . . . 269
Использование критерия ИЛИ . . . . . . . . . . . . . . . . . . . . . . . 269
Использование критерия И или ИЛИ . . . . . . . . . . . . . . . . . . 270
Совет 120. Поиск точного значения . . . . . . . . . . . . . . . . . . . . . . . 271
Совет 121. Выполнение двустороннего поиска . . . . . . . . . . . . . . . 273
Использование формулы . . . . . . . . . . . . . . . . . . . . . . . . . . 273
Использование неявного пересечения . . . . . . . . . . . . . . . . 274
Совет 122. Поиск по двум столбцам . . . . . . . . . . . . . . . . . . . . . . . 275
Совет 123. Выполнение поиска с помощью массива . . . . . . . . . . . 277
Использование таблицы поиска . . . . . . . . . . . . . . . . . . . . . 277
Применение массива . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
Совет 124. Использование функции ДВССЫЛ . . . . . . . . . . . . . . . . 279
Косвенное указание строк . . . . . . . . . . . . . . . . . . . . . . . . . 279
Косвенное указание названий листов . . . . . . . . . . . . . . . . . 280
Создание неизменной ссылки на ячейку . . . . . . . . . . . . . . . 280
Совет 125. Создание мегаформул . . . . . . . . . . . . . . . . . . . . . . . . . 282
Часть VI. Преобразования и математические расчеты . . . . . . . . . 285
Совет 126. Перевод из одной системы мер в другую . . . . . . . . . . . 287
Совет 127. Преобразование температур . . . . . . . . . . . . . . . . . . . . 289
Совет 128. Решение систем уравнений . . . . . . . . . . . . . . . . . . . . . 290
Совет 129. Решение рекурсивных уравнений . . . . . . . . . . . . . . . . . 292
Совет 130. Генерация случайных чисел . . . . . . . . . . . . . . . . . . . . 294
Использование функции СЛЧИС . . . . . . . . . . . . . . . . . . . . . 294
Использование функции СЛУЧМЕЖДУ . . . . . . . . . . . . . . . . .294
Использование надстройки Analysis ToolPak . . . . . . . . . . . . 294
Совет 131. Расчет корней . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Совет 132. Расчет остатка . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
Часть VII. Диаграммы и графики . . . . . . . . . . . . . . . . . . . . . . . . . . 299
Совет 133. Создание текстовой диаграммы непосредственно
в диапазоне ячеек . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Совет 134. Выбор элементов в диаграмме . . . . . . . . . . . . . . . . . . . 304
Выбор с помощью мыши . . . . . . . . . . . . . . . . . . . . . . . . . . . 304
Выбор с помощью клавиатуры . . . . . . . . . . . . . . . . . . . . . . 304
Выбор с помощью списка Элементы диаграммы . . . . . . . . . 305
Совет 135. Создание самостоятельно расширяющихся диаграмм . . . 307
Совет 136. Создание смешанных диаграмм . . . . . . . . . . . . . . . . . . 308
Совет 137. Создание диаграммы Ганта . . . . . . . . . . . . . . . . . . . . . 311
Совет 138. Создание калибровочных диаграмм . . . . . . . . . . . . . . . 313
Совет 139. Использование рисунков в диаграммах . . . . . . . . . . . . 315
Совет 140. Построение графиков математических функций . . . . . . 317
Построение графиков математических функций
с одной переменной . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
Построение графиков математических функций
с двумя переменными . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
Совет 141. Использование биржевых диаграмм . . . . . . . . . . . . . . . 320
Совет 142. Связывание текста диаграмм с ячейками . . . . . . . . . . . 321
Совет 143. Создание шаблона диаграммы . . . . . . . . . . . . . . . . . . . 322
Совет 144. Сохранение диаграммы в виде графического файла . . . 323
Метод 1. Вставка диаграммы в графическую программу . . . 323
Метод 2. Сохранение в качестве файла HTML . . . . . . . . . . . 323
Метод 3. Использование макросов VBA . . . . . . . . . . . . . . . . 324
Совет 145. Сохранение диапазона в качестве графического
изображения . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325
Совет 146. Создание диаграмм того же размера . . . . . . . . . . . . . . 326
Совет 147. Сброс всех настроек форматирования диаграммы . . . . 328
Совет 148. Закрепление диаграммы . . . . . . . . . . . . . . . . . . . . . . . 329
Преобразование диаграммы в рисунок . . . . . . . . . . . . . . . . 329
Преобразование диапазона ссылок в массивы . . . . . . . . . . . 329
Совет 149. Создание эффектов изображений на диаграмме . . . . . 331
Совет 150. Создание спарклайн-графики . . . . . . . . . . . . . . . . . . . 332
Совет 151. Выбор объектов на листе . . . . . . . . . . . . . . . . . . . . . . . 335
Ctrl+щелчок . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335
Панель Выделение и видимость . . . . . . . . . . . . . . . . . . . . . 335
Диалоговое окно Выделение группы ячеек . . . . . . . . . . . . . 336
Инструмент выбора объектов . . . . . . . . . . . . . . . . . . . . . . . 336
Совет 152. Создание поздравительных открыток . . . . . . . . . . . . . . 337
Совет 153. Улучшение форматирования текста в фигурах . . . . . . . 339
Совет 154. Использование изображений в качестве маркеров
для графиков . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341
Совет 155. Изменение формы примечаний к ячейкам . . . . . . . . . . 343
Совет 156. Добавление изображений в примечания к ячейкам . . . 344
Совет 157. Улучшение изображений . . . . . . . . . . . . . . . . . . . . . . . 345
Часть VIII. Анализ данных и списки . . . . . . . . . . . . . . . . . . . . . . . 347
Совет 158. Использование возможностей таблиц . . . . . . . . . . . . . 349
Понимание того, что такое таблица . . . . . . . . . . . . . . . . . . 349
Диапазон ячеек или таблица . . . . . . . . . . . . . . . . . . . . . . . 349
Создание таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350
Совет 159. Работа с таблицами . . . . . . . . . . . . . . . . . . . . . . . . . . . 352
Навигация в таблице . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352
Выбор частей таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352
Добавление новых строк или столбцов . . . . . . . . . . . . . . . . 352
Удаление строк или столбцов . . . . . . . . . . . . . . . . . . . . . . . 353
Перемещение таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353
Сортировка и фильтрация таблицы . . . . . . . . . . . . . . . . . . .353
Совет 160. Использование формул в таблице . . . . . . . . . . . . . . . . 355
Работа с итоговой строкой . . . . . . . . . . . . . . . . . . . . . . . . . 355
Использование формул внутри таблицы . . . . . . . . . . . . . . . 356
Ссылки на данные в таблице . . . . . . . . . . . . . . . . . . . . . . . 358
Совет 161. Нумерация строк в таблице . . . . . . . . . . . . . . . . . . . . . 360
Совет 162. Использование настраиваемых представлений
с фильтрацией . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 362
Совет 163. Размещение расширенных результатов фильтра
на другом листе . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364
Совет 164. Сравнение двух диапазонов с помощью условного
форматирования . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
Совет 165. Перемешивание списка . . . . . . . . . . . . . . . . . . . . . . . . 368
Совет 166. Восполнение пробелов в отчете . . . . . . . . . . . . . . . . . 369
Совет 167. Создание списка из сводной таблицы . . . . . . . . . . . . . . 371
Совет 168. Поиск дубликатов с помощью условного
форматирования . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374
Совет 169. Быстрый поиск частоты вхождений . . . . . . . . . . . . . . . 376
Совет 170. Управление ссылками на ячейки в сводных таблицах . . . 378
Совет 171. Группирование элементов по дате в сводной таблице . . . 379
Совет 172. Отсоединение сводной таблицы от источника данных . . . 382
Совет 173. Срезы сводных таблиц . . . . . . . . . . . . . . . . . . . . . . . . . 384
Часть IX. Работа с файлами . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387
Совет 174. Понимание новых файловых форматов Excel . . . . . . . . 389
Распознаем новые форматы файлов Excel . . . . . . . . . . . . . . 389
Пакет обеспечения совместимости Office . . . . . . . . . . . . . . 389
Сохранение файла для использования
в более старой версии Excel . . . . . . . . . . . . . . . . . . . . . . . . 390
Совет 175. Импортирование текстового файла в диапазон
ячеек листа . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391
Совет 176. Получение данных из веб-страниц . . . . . . . . . . . . . . . . 393
Вставка статической информации . . . . . . . . . . . . . . . . . . . . 393
Вставка обновляемой информации . . . . . . . . . . . . . . . . . . . 393
Открытие веб-страницы напрямую . . . . . . . . . . . . . . . . . . . 394
Совет 177. Отображение полного пути файла книги . . . . . . . . . . . 396
Переход к представлению Backstage . . . . . . . . . . . . . . . . . . 396
Использование формулы . . . . . . . . . . . . . . . . . . . . . . . . . . 396
Добавление элемента управления на панель быстрого
доступа . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396
Отображение панели свойств документа . . . . . . . . . . . . . . 397
Использование макросов . . . . . . . . . . . . . . . . . . . . . . . . . . 397
Совет 178. Использование свойств документа . . . . . . . . . . . . . . . . 398
Совет 179. Проверка книги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400
Совет 180. Поиск отсутствующей кнопки Нет для всех
при закрытии файлов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401
Совет 181. Получение списка имен файлов . . . . . . . . . . . . . . . . . . 402
Совет 182. Использование файлов рабочей области . . . . . . . . . . . 404
Часть X. Печать . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 405
Совет 183. Управление печатью . . . . . . . . . . . . . . . . . . . . . . . . . . 407
Отображение кнопки Быстрая печать . . . . . . . . . . . . . . . . . 407
Настройка общих параметров страницы . . . . . . . . . . . . . . . 407
Совет 184. Отображение повторяющихся строк или столбцов
на распечатке . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
Совет 185. Печать несмежных диапазонов на одной странице . . . . 410
Использование инструмента Камера . . . . . . . . . . . . . . . . . . 410
Съемка камерой . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411
Совет 186. Предотвращение печати объектов . . . . . . . . . . . . . . . . 413
Совет 187. Нумерация страниц . . . . . . . . . . . . . . . . . . . . . . . . . . . 414
Использование базовой нумерации страниц . . . . . . . . . . . . 414
Изменение номера первой страницы . . . . . . . . . . . . . . . . . . 414
Совет 188. Добавление и удаление разрывов страниц . . . . . . . . . . 416
Принудительный разрыв страницы в нужном вам месте . . . 416
Удаление добавленных разрывов страниц . . . . . . . . . . . . . 416
Совет 189. Сохранение в PDF-файл . . . . . . . . . . . . . . . . . . . . . . . . 417
Совет 190. Создание распечатки, которая поместится
на одной странице . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 418
Совет 191. Печать содержимого ячейки в верхнем
или нижнем колонтитуле . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420
Совет 192. Копирование параметров страницы
между листами . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 422
Совет 193. Печать примечаний к ячейкам . . . . . . . . . . . . . . . . . . . 423
Совет 194. Печать гигантского баннера . . . . . . . . . . . . . . . . . . . . 424
Часть XI. Определение, фиксация и предупреждение ошибок . . . 427
Совет 195. Использование возможностей проверки ошибок . . . . . 429
Совет 196. Выявление ячеек с формулами . . . . . . . . . . . . . . . . . . 431
Использование окна Выделение группы ячеек . . . . . . . . . . 431
Использование условного форматирования . . . . . . . . . . . . 431
Совет 197. Решение проблем чисел с плавающей запятой . . . . . . . 433
Совет 198. Удаление избыточных пробелов . . . . . . . . . . . . . . . . . 435
Совет 199. Наглядный просмотр названий . . . . . . . . . . . . . . . . . . 437
Совет 200. Определение фантомных ссылок . . . . . . . . . . . . . . . . . 438
Совет 201. Понимание разницы между отображаемым
и фактическим значением . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439
Совет 202. Отслеживание отношений ячеек . . . . . . . . . . . . . . . . . 440
Идентификация влияющих ячеек . . . . . . . . . . . . . . . . . . . . 440
Идентификация зависимых ячеек . . . . . . . . . . . . . . . . . . . . 440
Часть XII. Основы использования VBA и макросов . . . . . . . . . . . 443
Совет 203. Знакомство с макросами и VBA . . . . . . . . . . . . . . . . . . 445
Что такое макрос? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445
Что может сделать макрос? . . . . . . . . . . . . . . . . . . . . . . . . 445
Совет 204. Запись макроса . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
Создание макроса . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
Просмотр кода макроса . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
Тестирование макроса . . . . . . . . . . . . . . . . . . . . . . . . . . . . 448
Совет 205. Выполнение макросов . . . . . . . . . . . . . . . . . . . . . . . . . 449
Использование окна Макрос . . . . . . . . . . . . . . . . . . . . . . . . 449
Использование окна Visual Basic Editor . . . . . . . . . . . . . . . . 449
Использование сочетаний клавиш . . . . . . . . . . . . . . . . . . . 449
Назначение макроса кнопке . . . . . . . . . . . . . . . . . . . . . . . . 449
Назначение макроса фигурам . . . . . . . . . . . . . . . . . . . . . . . 450
Добавление кнопки на панель быстрого доступа . . . . . . . . . 450
Добавление кнопки на ленту . . . . . . . . . . . . . . . . . . . . . . . 450
Совет 206. Понимание разницы между функциями Function
и процедурами Sub . . . . . . . . . . . . . . . . . . . . . . .
