За последние десять лет Excel фундаментально изменил способы работы с числовыми данными, но большинство его возможностей все еще остается в тени. Книга «Excel. Трюки» помогает читателю сразу же перейти к практическим инструментам и методам анализа, обработки и представления данных. В издании рассматриваются методы наиболее эффективной обработки данных, которые помогают воспользоваться всей мощью Excel и быстро добиться необходимых результатов. Книга предназначена для студентов, аналитиков и всех пользователей компьютеров, желающих узнать о возможностях Excel, связанных с обработкой данных.

Содержание

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

Об авторах

Помощники

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

Предисловие

Зачем нужны

Трюки Excel?

Правило 80/20

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

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

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

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

Трюки

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Глава 3. Трюки с именованием

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

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

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

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

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

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

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

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

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

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

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

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

Глава 5. Трюки с диаграммами

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Глава 7. Трюки с макросами

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

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

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

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

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

Трюк № 86. Отображение сообщения «Please Wait»

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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