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. Запрещение ненужных сообщений … 31

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

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

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

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

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

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

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

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

№ 13. Удаление несуществующих ссылок на книги … 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. Настройка примечаний ячеек … 83

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

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

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

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

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

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

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

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

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

№ 38. Функциональные возможности даты и времени в Excel … 104

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

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

№ 40. Использование одного имени для диапазонов на разных рабочих листах … 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