Excel Трюки 100 профессиональных примеров

Содержание книги Microsoft Excel:

Кто создавал эту книгу

Об авторах … 12

Помощники … 12

Благодарности … 13

Предисловие … 13

Зачем нужны трюки Excel? … 14

Правило 80/20 … 14

Конструктивные советы … 15

Советы по форматированию … 16

Советы по применению формул Excel … 16

Где взять и как применить эти трюки … 18

Как работать с этой книгой … 18

Как организована книга … 18

Пользователи Windows и Macintosh … 20

Сокращения, принятые в этой книге … 20

Использование примеров кода … 21

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

ГЛАВА 1. Упрощение рабочих книг и листов

Трюк № 1. Создание персональных представлений рабочих книг … 22

Трюк № 2. Одновременный ввод данных в несколько рабочих листов … 25

Трюк № 3. Запрещение пользователям выполнять определенные действия … 28

Трюк № 4. Запрещение ненужных сообщений Excel … 31

Трюк № 5. Как скрыть лист, чтобы его невозможно было отобразить … 35

Трюк № 6. Настройка диалогового окна шаблонов и рабочей книги по умолчанию … 36

Трюк № 7. Создание указателя листов в книге … 38

Трюк № 8. Ограничение диапазона прокрутки листа … 41

Трюк № 9. Блокировка и защита ячеек, содержащих формулы … 44

Трюк № 10. Поиск повторяющихся данных при помощи условного форматирования … 47

Трюк № И. Пользовательские панели инструментов для определенной книги … 49

Трюк № 12. Как перехитрить обработчик относительных ссылок Excel … 51

Трюк № 13. Удаление несуществующих ссылок на книги Excel … 52

Трюк № 14. Предотвращение распухания рабочей книги … 55

Трюк № 15. Получение данных из поврежденной книги … 57

ГЛАВА 2. Трюки со встроенными возможностями Excel

Трюк № 16. Проверка данных на основе списка на другом листе … 60

Трюк № 17. Управление условным форматированием при помощи флажков … 62

Трюк № 18. Поиск формул Excel при помощи условного форматирования … 65

Трюк № 19. Подсчет и суммирование ячеек, отвечающих критерию условного форматирования … 67

Трюк № 20. Выделение строк или столбцов через один … 69

Трюк № 21. Трехмерные эффекты в таблицах и ячейках … 71

Трюк № 22. Включение и выключение условного форматирования и проверки данных при помощи флажка … 72

Трюк № 23. Поддержка нескольких списков в поле со списком … 74

Трюк № 24. Создание списков проверки, меняющихся на основе выбранного значения в другом списке … 76

Трюк № 25. Как при проверке данных заставить Excel использовать список на другом рабочем листе … 78

Трюк № 26. Удаление ненужных символов при помощи замены … 80

Трюк № 27. Преобразование текстовых чисел в настоящие числа … 80

Трюк № 28. Настройка примечаний ячеек Excel … 83

Трюк № 29. Сортировка более чем по трем столбцам … 85

Трюк № 30. Случайная сортировка … 86

Трюк № 31. Манипулирование данными при помощи расширенного фильтра … 87

Трюк № 32. Создание пользовательских числовых форматов … 90

Трюк № 33. Увеличение количества уровней отмены действий в Excel для Windows … 95

Трюк № 34. Создание пользовательских списков Excel … 96

Трюк № 35. Промежуточные итоги жирным шрифтом … 96

Трюк № 36. Преобразование формул и функций Excel в значения … 99

Трюк № 37. Автоматическое добавление данных в список проверки … 101

Трюк № 38. Трюки с возможностями даты и времени в Excel … 104

ГЛАВА 3. Трюки с именованием

Трюк № 39. Адресация данных по имени … 109

Трюк № 40. Использование одного имени для диапазонов на разных рабочих листах Excel … 110

Трюк № 41. Создание пользовательских функций при помощи имен … 112

Трюк № 42. Создание увеличивающихся и уменьшающихся диапазонов … 115

Трюк № 43. Вложение динамических диапазонов для максимальной гибкости … 121

Трюк № 44. Поиск именованных диапазонов на рабочем листе … 124

ГЛАВА 4. Трюки со сводными таблицами

Трюк № 45. Сводные таблицы: сами по себе — трюк … 126

Трюк № 46. Совместное использование сводных таблиц, но не их данных … 130

Трюк № 47. Автоматизация создания сводных таблиц … 132

Трюк № 48. Перемещение общих итогов сводных таблиц … 135

Трюк № 49. Эффективная вставка данных из другой рабочей книги … 137

ГЛАВА 5. Трюки с диаграммами Excel

Трюк № 50. Выделение одного сектора из круговой диаграммы … 139

Трюк № 51. Создание двух наборов секторов в одной круговой диаграмме … 141

Трюк № 52. Создание диаграмм, изменяющихся вместе с данными … 143

Трюк № 53. Взаимодействие с диаграммами при помощи пользовательских элементов управления … 146

Трюк № 54. Три быстрых способа обновления диаграмм … 149

Трюк № 55. Трюк с созданием простой диаграммы термометра … 153

Трюк № 56. Создание гистограмм с переменной шириной и высотой … 156

Трюк № 57. Создание диаграммы спидометра … 159

Трюк № 58. Привязка текстовых элементов диаграмм к ячейкам Excel … 166

Трюк № 59. Трюки с данными диаграммы, чтобы пустые ячейки не учитывались при построении графика … 167

ГЛАВА 6. Трюки с формулами и функциями

Трюк № 60. Добавление описаний к формулам … 169

Трюк № 61. Перемещение относительных формул без изменения ссылок … 170

Трюк № 62. Сравнение двух диапазонов Excel … 171

Трюк № 63. Заполнение всех пустых ячеек в списке … 173

Трюк № 64. Как сделать, чтобы формула ссылалась на следующие строки при копировании по столбцам … 175

Трюк № 65. Преобразование дат в отформатированные даты Excel … 177

Трюк № 66. Суммирование и подсчет ячеек … 178

Трюк № 67. Как уменьшить влияние переменных функций на скорость пересчета … 180

Трюк № 68. Подсчет только по одному вхождению каждой записи в списке … 181

Трюк № 69. Суммирование каждой второй, третьей или n-й строки или ячейки … 182

Трюк № 70. Поиск n-го вхождения значения Excel … 184

Трюк № 71. Превращение функции Excel ПРОМЕЖУТОЧНЫЕ ИТОГИ (SUBTOTAL) в динамическую … 187

Трюк № 72. Добавление расширений данных … 189

Трюк № 73. Преобразование чисел со знаком минус справа … 190

Трюк № 74. Отображение отрицательных значений времени … 192

Трюк № 75. Использование функции ВПР (VLOOKUP) в нескольких таблицах … 194

Трюк № 76. Отображение общего количества времени в днях, часах и минутах … 196

Трюк № 77. Определение количества указанных дней в любом месяце … 197

Трюк №78. Построение мегаформул … 199

Трюк № 79. Трюки с мегаформулами, которые ссылаются на другие рабочие книги … 201

Трюк № 80. Трюк с одной функцией баз данных Excel, чтобы она работала вместо нескольких функций … 203

ГЛАВА 7. Трюки с макросами Excel

Трюк № 81. Ускорение выполнения кода и исключение мерцания экрана … 209

Трюк № 82. Запуск макроса в определенное время … 210

Трюк № 83. Использование кодовых имен для ссылки на листы в рабочих книгах Excel … 212

Трюк № 84. Простое подключение кнопок к макросам … 213

Трюк № 85. Создание всплывающего экрана для рабочей книги … 214

Трюк № 86. Отображение сообщения ≪Please Wait≫ … 216

Трюк № 87. Как помечать и сбрасывать пометку ячеек при выделении … 217

Трюк № 88. Подсчет или суммирование ячеек с определенным цветом заливки … 219

Трюк № 89. Добавление элемента управления Календарь в любую рабочую книгу Excel … 220

Трюк № 90. Включение и снятие защиты паролем для всех рабочих листов Excel одновременно … 222

Трюк № 91. Получение имени и пути рабочей книги … 225

Трюк № 92. Как обойти ограничение Excel на три критерия условного форматирования … 226

Трюк № 93. Выполнение процедур на защищенных рабочих листах … 228

Трюк №94. Распространение макросов … 229

ГЛАВА 8. Соединение Excel с остальным миром

Трюк № 95. Загрузка документа XML в Excel … 235

Трюк № 96. Сохранение в виде SpreadsheetML и выделение данных … 245

Трюк № 97. Создание электронных таблиц при помощи SpreadsheetML … 254

Трюк № 98. Импорт данных напрямую в Excel … 258

Трюк № 99. Доступ к веб-службам SOAP из Excel … 266

Трюк № 100. Создание электронных таблиц Excel в других средах разработки … 271

Словарь терминов … 277

Алфавитный указатель … 281