Джон Уокенбах. Excel 2013. Трюки и советы

Когда я был начинающим пользователем Excel, то читал толстые книги типа «Библия пользователя». Пожалуй, я был не типичным начинающим, так как мало кто так поступает. 🙂 Я понимал, что не запомню всё, и даже не ставил такой цели. Я просто набирался знаний, и кое-что откладывалось в голове. А когда возникала практическая задача, я хотя бы мог вспомнить, что где-то про это уже читал, и довольно быстро находил, где. Теперь, когда я знаком с основами, мне интересны нюансы. Поэтому и читаю более специальные книги по Excel. Например, недавно перевел небольшую книгу Билл Джелен. Всё о ВПР: от первого применения до экспертного уровня.

Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014 – 336 с.

Купить книгу в Ozon или Лабиринте

Книга Джона Уокенбаха – это 100 более крупных и 30 относительно небольших советов из всевозможных областей использования Excel: книги и файлы, форматирование, формулы, работа с данными, таблицы и сводные таблицы, диаграммы и графика. Настоящая заметка включает лишь перечень этих советов. Некоторые советы обозначены, как гиперссылки. Значит я уже опубликовал их отдельно. Некоторые пункты представлены подразделами с гиперссылками (набраны с отступом). Это означает, что по этой теме я писал ранее, и совет Джона (на мой взгляд) мало, что добавляет. Эти пункты я не буду раскрывать повторно. Большинство пунктов просто перечислены. Возможно, я напишу по ним заметки в будущем.

Часть I. Книги и файлы

Совет 1. Изменение оформления Excel: Косметические изменения, Скрытие ленты, Работа с параметрами вкладки Вид, Скрытие других элементов, Скрытие статусной строки

Совет 2. Настройка панели быстрого доступа: О панели быстрого доступа, Добавление новых команд, Выполнение других операций

Совет 3. Настройка ленты

Совет 4. Понятие о защищенном просмотре: В каких случаях используется защищенный просмотр, Печать и копирование, Принудительное открытие файла в обычном режиме

Совет 5. Понятие об AutoRecover: Восстановление версий актуальной книги, Восстановление несохраненной работы

Совет б. Работа с книгой в браузере

Совет 7. Сохранение в формате «только для чтения»: Отправка печатной копии, Отправка электронной копии в виде PDF-файла, Отправка MHTML-файла

Совет 8. Генерирование списка имен файлов

Совет 9. Генерирование списка названий листов

См. Создание указателя листов в книге

Совет 10. Применение тем документа: Применение темы, Настройка темы

Совет 11. Понятие о проблемах совместимости в Excel: Форматы файлов в Excel 2013, Пакет обеспечения совместимости Office, Проверка совместимости

О формате файлов Excel см. Excel «тормозит». Что делать? Дубль 2

Совет 12. Об изменении настроек принтера

Часть II. Форматирование

Совет 13. Работа с объединенными ячейками: Другие операции, связанные с объединением, Потенциальные проблемы, которые могут возникнуть с объединенными ячейками, Нахождение всех объединенных ячеек, Разбивка всех объединенных ячеек, Альтернативы для объединения ячеек

Совет 14. Создание отступов для содержимого ячейки

Совет 15. Использование именованных стилей: Работа с меню Стили ячеек, Изменение существующего стиля, Создание новых стилей, Объединение стилей из других книг

Совет 16. Создание пользовательских числовых форматов: Фрагменты строки числового формата, Коды пользовательских числовых форматов

Совет 17. Применение пользовательских числовых форматов при масштабировании значений

Идеи, содержащиеся в советах 16 и 17, см. Пользовательский формат числа в Excel

Совет 18. Создание маркированного списка: Использование маркера, Использование объектов SmartArt

Совет 19. Заливка чередующихся строк с помощью условного форматирования: Отображение чередующейся заливки, Заливка ячеек в шахматном порядке, Заливка групп строк

Совет 20. Форматирование отдельных символов в ячейке

Совет 21. Использование инструмента Формат по образцу: Основы форматирования по образцу, Варианты применения форматирования по образцу

Совет 22. Вставка водяного знака

Совет 23. Вывод текста и значения в ячейке: Применение конкатенации, Применение функции ТЕКСТ, Применение пользовательского числового формата

Совет 24. Как обойтись без подстановки шрифтов при применении малых кеглей

Совет 25. Обновление старых шрифтов

Часть III. Формулы

Совет 26. Изменение размеров строки формул

Совет 27. Отслеживание формул с ячейками из любого места, окно контрольного значения

Совет 28. Некоторые приемы работы с автосуммой

Совет 29. Определение случаев, в которых используются абсолютные и смешанные ссылки

См. Относительные, абсолютные и смешанные ссылки на ячейки в Excel

Совет 30. Как избежать отображения ошибок в формулах: Использование функции ЕСЛИОШИБКА, Использование функции ЕОШИБКА

Совет 31. Создание названий на уровне рабочего листа

Совет 32. Использование именованных констант

Совет 33. Отправка электронной почты из Excel; о функции ГИПЕРССЫЛКА

Совет 34. Поиск точного значения

Совет 35. Двухкоординатный поиск: Применение формулы, Использование неявного пересечения

См. Использование функции Excel ВПР для извлечения данных из таблицы с двумя параметрами

Совет 36. Поиск по двум столбцам

Совет 37. Расчет американских праздничных дней

Совет 38. Вычисление возраста

Совет 39. Работа с датами до 1900 года

Совет 40. Отображение «живого» календаря в диапазоне ячеек

Совет 41. Возврат последней непустой ячейки в столбце или строке

Совет 42. Различные методы округления чисел

Совет 43. Преобразование единиц между различными системами измерения

Совет 44. Подсчет неповторяющихся записей в диапазоне

См. Формулы счета уникальных значений

Совет 45. Использование функции АГРЕГАТ

См. Функция АГРЕГАТ

Совет 46. Точное копирование диапазона формул

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

Совет 47. Использование возможностей фонового контроля ошибок

См. Проверка формул в Excel, или что означает зеленый треугольник

Совет 48. Использование надстройки Inquire (Запрос электронной таблицы): Анализ книги, Схематические инструменты, Сравнение файлов, Другие возможности

Совет 49. Скрытие и защита формул: Скрытие и защита ячеек с формулами, Разблокирование ячеек, не содержащих формул, Защита листа

Совет 50. Использование функции ДВССЫЛ: Косвенное указание строк, Косвенное указание имен рабочих листов, Создание неизменяемой ссылки на ячейку

См. Excel. Примеры использования функции ДВССЫЛ (INDIRECT)

Совет 51. Редактирование формул в диалоговых окнах

Совет 52. Преобразование вертикального диапазона в таблицу

См. также Преобразование массива в столбец или строку

Часть IV. Работа с данными

Совет 53. Эффективное выделение ячеек

Совет 54. Автоматическое заполнение диапазона серией значений

См. также Использование функции СТРОКА для создания заполнителя

Совет 55. Удаление знаков «минус» в конце отрицательных чисел

Совет 56. Как разрешить движение курсора только в ячейках ввода

Совет 57. Преобразование данных без использования формул

Совет 58. Создание раскрывающегося списка в ячейке

См. также Как обойти ограничение Excel и сделать выпадающий список зависимым

Совет 59. Сравнение двух диапазонов с помощью условного форматирования

Совет 60. Нахождение повторяющихся значений с помощью условного форматирования

Совет 61. Работа с номерами кредитных карт

Совет 62. Обнаружение лишних пробелов

Совет 63. Транспонирование диапазона

См. Транспонирование строк в столбцы (или столбцов в строки) с помощью формулы массива и Использование ДВССЫЛ для транспонирования строк в столбцы с сохранением формул

Совет 64. Использование Мгновенного заполнения для извлечения данных

Совет 65. Использование Мгновенного заполнения для объединения данных

См. Excel. Мгновенное заполнение

Совет 66. Получение информации с веб-страницы

Совет 67. Импорт текстового файла в диапазон на рабочем листе

Совет 68. Использование возможности экспресс-анализа

См. Excel 2013. Основы работы с рекомендуемыми сводными таблицами

Совет 69. Заполнение пробелов в отчете

Совет 70. Выполнение нестрогого поиска

Совет 71. Подкрепление данных с помощью аудио

Совет 72. Получение данных из PDF-файла: Копирование и вставка, Использование Word 2013 в качестве программы-посредника

Часть V. Таблицы и сводные таблицы

Совет 73. Понятие о таблицах:

Совет 74. Использование формул в таблице

Совет 75. Автоматическая нумерация строк таблицы

См. Таблицы в Excel

Совет 76. Определение данных, подходящих для использования в сводной таблице

Не всякое табличное представление данных удобно для последующей обработки путем создания сводной таблицы. Как преобразовать «неправильную» таблицу см. совет № 11

Совет 77. Использование сводной таблицы вместо формул

Например, для формирования уникального списка — см. Формулы сортировки и извлечения уникальных списков

Совет 78. Управление ссылками на ячейки внутри сводной таблицы

См. Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Совет 79. Быстрый поиск частоты вхождений

См. раздел Группировка числовых полей в заметке

Совет 80. Группирование элементов по дате в сводной таблице

См. Группировка данных сводной таблицы

Совет 81. Создание сводных таблиц с двумя видами группирования дат

Совет 82. Использование в сводных таблицах срезов и временной шкалы

См. Excel 2013. Срезы сводных таблиц; создание временной шкалы

Часть VI. Диаграммы и графика

Совет 83. Рекомендуемые диаграммы

Совет 84. Настройка диаграмм

Совет 85. Как сделать две диаграммы одинакового размера

Советы 84 и 85 объединены в заметке Настройка диаграмм в Excel

Совет 86. Вставка шаблона диаграммы

Совет 87. Создание комбинированной диаграммы

Советы 86 и 87 объединены в заметке Шаблон комбинированной диаграммы

Совет 88. Работа с недостающей информацией в диаграмме

См. Отражение пропущенных данных на графиках Excel

Совет 89. Использование коридоров колебаний в диаграмме

См. Биржевая диаграмма, она же блочная, она же ящичная

Совет 90. Использование многоуровневых подписей категорий

Совет 91. Связывание текста диаграммы с ячейками

Совет 92. Фиксация диаграммы: преобразование диаграммы в изображение

Совет 93. Создание диаграммы непосредственно в диапазоне

Совет 94. Создание минималистичных диаграмм: Простые гистограммы, Простые круговые диаграммы, Простые линейные диаграммы, Диаграмма-спидометр

Совет 95. Применение взятых из диапазона подписей для данных диаграммы

Совет 96. Группировка диаграмм и других объектов

Совет 97. Создание снимков диапазонов: Создание статического снимка диапазона, Создание живого изображения диапазона, Сохранение диапазона в виде графического файла

Совет 98. Изменение внешнего вида примечаний в ячейке: Настройка панели быстрого доступа, Форматирование примечания, Изменение контура примечания, Добавление изображения к примечанию

Совет 99. Улучшение изображений

Совет 100. Сохранение фигур, диаграмм и диапазонов в виде рисунков

Бонусные советы

Бонусный совет 1. О некоторых полезных сочетаниях клавиш

Бонусный совет 2. Переопределение использованной области рабочего листа

Бонусный совет 3. Скрытие столбцов или строк

Бонусный совет 4. Скрытие содержимого ячеек

Бонусный совет 5. Ограничение используемой области рабочего листа

Бонусный совет 7. Максимальное скрытие листа

Бонусный совет 8. Перемещение указателя ячейки после ввода данных

Бонусный совет 9. Как оставлять заголовки на виду

Бонусный совет 10. Работа с обыкновенными дробями

Бонусный совет 11. Управление автоматическими гиперссылками

Бонусный совет 12. Управление буфером обмена в Office

Бонусный совет 13. Отображение показателей времени, превышающих 24 часа

Бонусный совет 14. Обрамление диапазона

Бонусный совет 15. Работа с линиями сетки, границами и нижними подчеркиваниями

Бонусный совет 16. Просмотр всех символов шрифта

Бонусный совет 17. Аннотирование формулы без использования примечаний

См. Добавление примечания в формулу с использованием функции Ч

Бонусный совет 18. Отображение и печать формул

Бонусный совет 19. Создание списка имен

Бонусный совет 20. Округление временных значений

Бонусный совет 21. Преобразование температур

Бонусный совет 22. Вычисление корней

Бонусный совет 23. Вычисление остатка

Бонусный совет 24. Создание графиков-спарклайнов

См. Спарклайны или микрографики в ячейках Excel.

Бонусный совет 25. Применение рисунков в качестве маркеров на линейной диаграмме

Бонусный совет 26. Рандомизация списка

Бонусный совет 27. Отображение полного пути к рабочей книге

Бонусный совет 28. Закрытие всех рабочих книг

Бонусный совет 29. Копирование настроек страницы между несколькими листами

Бонусный совет 30. Печать примечаний к ячейке