Дон Гриффитс | Excel. Сборник рецептов (2025) [PDF]

Дон Гриффитс. Excel. Сборник рецептов. Рецепты для изучения Microsoft Excel
Оглавление

Предисловие
Условные обозначения
Использование примеров кода
Платформа онлайн-обучения O'Reilly
Как с нами связаться?
Благодарности

Глава 1. Рабочие книги, рабочие листы и ячейки
1.1. Применяем темы
1.2. Применяем стили ячеек
1.3. Форматируем ячейки
1.4. Форматируем значение ячейки
1.5. Задаем пользовательский числовой формат
1.6. Объединяем ячейки
1.7. Создаем шаблоны
1.8. Защищаем файлы, рабочие книги, рабочие листы и ячейки Excel
1.9. Используем условное форматирование
1.1 О. Применяем формат по образцу
1.11. Используем специальную вставку
1.12. Используем автозаполнение
1.13. Используем пользовательские списки
1.14. Используем мгновенное заполнение
1.15. Настраиваем автозамену
1.16. Используем заметки и примечания
1.17. Находим и выделяем ячейки и перемещаемся по ним
1.18. Создаем представление
1.19. Настраиваем ленту и ее вкладки
1.20. Используем панель быстрого доступа
1.21. Проверяем читаемость

Глава 2. Ссылки и структурированные данные
2.1. Используем относительные и абсолютные ссылки
2.2. Используем относительные и абсолютные ссылки в условном форматировании
2.3. Используем стиль ссылок RICI
2.4. Ссылаемся на другой рабочий лист или книгу
2.5. Используем 3D-ссылки
2.6. Именуем ячейки, диапазоны, константы и формулы
2.7. Создаем динамические именованные диапазоны
2.8. Проверяем данные
2.9. Создаем правило пользовательской проверки данных 2.1 О. Вводим данные с помощью раскрывающегося списка
2.11. Определяем зависимые, или каскадные, раскрывающиеся списки
2.12. Используем форму ввода данных
2.13. Сортируем данные по значению, формату или пользовательскому списку
2.14. Фильтруем данные
2.15. Закрепляем области
2.16. Используем автосумму
2.17. Используем структуру для добавления промежуточных итогов и групп
2.18. Используем таблицы
2.19. Используем структурированные ссылки

Глава 3. Используем формулы
3.1. Используем операторы и порядок очередности
3.2. Используем Excel в различных регионах и на разных языках
3. 3. Используем константы массивов
3.4. Используем динамические и устаревшие формулы массивов
3.5. Используем ссылки на диапазон разлива
3. 6. Предотвращаем поведение динамического массива
3.7. Используем Вставку функции или инструмент Построитель формул
3.8. Добавляем заметки в числовые формулы
3.9. Отображаем формулы
3. 1 О. Используем Окно контрольного значения
3. 11. Отображаем взаимозависимости ячеек
3. 12. Выполняем фоновую проверку ошибок
3.13. Проверяем ошибки в таблице
3.14. Ищем источники ошибок
3.15. Исправляем ошибки в значениях
3. 16. Вычисляем формулы
3. 17. Меняем режим вычисления
3. 18. Настраиваем точность округления
3. 19. Работаем с циклическими ссылками

Глава 4. Математические операции
4.1. Генерируем числа
4.2. Преобразуем текст или булево выражение в число
4.3. Узнаем знак и абсолютное значение числа
4.4. Подсчитываем, суммируем и усредняем значения ячеек
4.5. Используем критерии для подсчета, суммирования и среднего значения
4.6. Складываем и вычитаем значения в квадрате
4. 7. Используем умножение и кратные значения
4.8. Находим частные, остатки и делители
4.9. Округляем до десятичных разрядов и целых чисел
4.1 О. Округляем до значащих цифр и кратных значений
4.11. Используем степени, экспоненты, квадратные корни и логарифмы
4.12. Суммируем степенной ряд
4.13. Используем факториалы, перестановки и комбинации
4.14. Используем тригонометрию
4.15. Работаем с матрицами
4.16. Преобразуем одну систему счисления в другую
4.17. Выполняем побитовые операции
4.18. Работаем с комплексными числами

Глава 5. Работа с текстом
5.1. Объединяем текст
5.2. Используем символьный код
5.3. Генерируем последовательность символов
5.4. Генерируем случайные буквы
5.5. Определяем длину текстовой строки
5.6. Определяем местоположение текста в текстовой строке
5.7. Извлекаем текст фиксированной ширины из текстовой строки
5.8. Извлекаем текст из текстовой строки с помощью разделителя
5.9. Разбиваем текст в текстовой строке на цифровой и нецифровой
5.10. Заменяем, вставляем и удаляем текст
5.11. Удаляем лишние символы
5.12. Подсчитываем слова или конкретные символы
5. 13. Меняем регистр текста
5.14. Повторяем символы
5. 15. Преобразуем массив в текст
5.16. Форматируем текст в формат валюты
5.17. Включаем числовые значения в текстовую строку
5.18. Включаем дату/время в текстовую строку

Глава 6. Дата и время
6.1. Возвращаем текущие дату и время
6.2. Получаем часть значения даты/времени
6.3. Получаем день недели и неделю года
6.4. Получаем календарный или финансовый квартал
6.5. Создаем даты с помощью дня, месяца и года
6.6. Создаем время с помощью часов, минут и секунд
6. 7. Преобразуем текстовое значение в порядковый номер даты/времени
6.8. Извлекаем дату и время из порядкового номера
6.9. Добавляем к дате дни, месяцы и годы
6.1 О. Добавляем ко времени часы, минуты и секунды
6.11. Получаем последний день месяца
6.12. Вычисляем доmо года
6.13. Вычисляем разницу между значениями дат и времени
6.14. Обрабатываем рабочие дни
6.15. Получаем последовательность дат

Глава 7. Массивы, логика и функции поиска
7. 1. Получаем уникальные значения
7.2. Сортируем массив
7.3. Фильтруем массив
7.4. Преобразовываем массивы
7.5. Используем логические критерии Истина и Ложь
7.6. Оцениваем условия И и ИЛИ в формулах массивов
7. 7. Работаем с типами и ошибками в значениях
7.8. Выбираем возвращаемые значения
7.9. Ищем точные и ближайшие значения
7.10. Ищем индекс сопоставимого значения
7. 11. Используем индекс для возврата значения
7. 12. Создаем косвенные ссылки на ячейки и диапазоны 7 .13. Извлекаем адрес ячейки
7 .14. Используем смещенные ссылки

Глава 8. Статистический анализ
8.1. Создаем таблицу частот
8.2. Отображаем накопленные и процентные частоты
8.3. Используем гистограмму или диаграмму Парето
8.4. Вычисляем среднее
8.5. Ранжируем числовые данные
8.6. Находим k-e наибольшее или наименьшее значение
8.7. Разделяем данные на квартили и процентили
8.8. Рассчитываем диапазоны и дисперсии
8.9. Поиск выбросов
8.1 О. Используем диаграмму «ящик с усами»
8.11. Рассчитываем асимметрию
8.12. Рассчитываем вероятности с помощью таблицы вероятностей
8.13. Рассчитываем математическое ожидание и дисперсию
8.14. Используем биномиальное распределение
8.15. Используем отрицательное биномиальное распределение
8.16. Используем гипергеометрическое распределение
8.17. Используем распределение Пуассона
8.18. Используем экспоненциальное распределение
8.19. Используем нормальное распределение
8.20. Используем Z-значения
8.21. Вычисляем доверительный интервал для среднего генеральной совокупности
8.22. Используем критерий независимости хн-квадрат (Х,2)
8.23. Ищем линию наилучшего соответствия
8.24. Получаем уравнение линии наилучшего соответствия

Глава 9. Надстройка Пакет анализа
9.1. Устанавливаем Пакет анализа
9.2. Генерируем описательную статистику
9.3. Генерируем порядковые номера и процентные ранги
9 .4. Генерируем частотное распределение
9.5. Генерируем скользящее среднее
9.6. Используем экспоненциальное сглаживание
9.7. Генерируем случайную выборку
9.8. Генерируем периодическую выборку
9.9. Извлекаем случайные числа из распределения
9.10. Генерируем корреляционную матрицу
9.11. Получаем матрицу ковариаций
9.12. Проводим линейный регрессионный анализ
9.13. Используем двухвыборочный t-критерий Стьюдента
9.14. Проводим двухвыборочный z-тест
9.15. Проводим парный двухвыборочный t-тест
9.16. Проводим двухвыборочный F-тест для дисперсии
9.17. Проводим однофакторный дисперсионный анализ
9 .18. Проводим двух факторный дисперсионный анализ
9.19. Проводим анализ Фурье
Глава 1 О. Финансовый анализ
10.1. Рассчитываем платежи по кредитам с фиксированной процентной ставкой
10.2. Рассчитываем выплаты процентов и основной суммы кредита
10.3. Составляем график амортизации кредита с плавающей ставкой
10.4. Рассчитываем срок для кредита с фиксированной ставкой
10.5. Рассчитываем номинальную или текущую стоимость
10.6. Конвертируем номинальную процентную ставку в реальную
10.7. Рассчитываем будущую стоимость единовременной инвестиции с фиксированной ставкой
10.8. Рассчитываем будущую стоимость единовременной инвестиции с плавающей ставкой
10.9. Рассчитываем будущую стоимость инвестиции с регулярными вложениями
10.1 О. Достигаем инвестиционных целей
10.11. Рассчитываем чистую приведенную стоимость
10.12. Рассчитываем внутреннюю ставку доходности
10.13. Рассчитываем амортизацию
10.14. Получаем данные акций и валют
10.15. Получаем исторические данные акций и валют
1О . 16. Используем биржевые диаграммы
10.17. Рассчитываем бета-коэффициент акции
10.18. Прогнозируем линейный и экспоненциальный рост
10.19. Прогнозируем сезонный рост

Глава 11. Сводные таблицы
11.1. Организуем данные для сводных таблиц
11.2. Вставляем сводную таблицу
11.3. Добавляем строки, столбцы и значения
11.4. Используем дополнительные строки
11.5. Обновляем данные сводной таблицы
11.6. Перемещаем сводную таблицу
11.7. Изменяем внешний вид сводной таблицы
11.8. Изменяем макет по умолчанию
11.9. Изменяем агрегацию значений
11.1 О. Отображаем различные вычисления значений
11.11. Создаем пользовательские промежуточные итоги
11.12. Сортируем данные
11. 13. Перемещаем элементы вручную
11.14. Фильтруем данные
11.15. Используем фильтр для создания нескольких сводных таблиц
11.16. Группируем по дате/времени
11.17. Группируем по числу
11.18. Группируем вручную по текстовым значениям
11.19. Включаем группы с отсутствующими данными
11.20. Изменяем формат пустых ячеек
11.21. Используем вычисляемые поля
11.22. Используем вычисляемые поля для подсчета элементов
11.23. Используем вычисляемые элементы
11.24. Ссылаемся на местоположение в формуле вычисляемого элемента
1 1.25. Изменяем порядок расчета вычисляемого элемента
11.26. Формируем список пользовательских формул
11.27. Изменяем источник данных сводной таблицы
11.28. Используем кеш сводной таблицы
11.29. Фильтруем несколько сводных таблиц, совместно использующих один кеш
11.30. Уменьшаем размер файла рабочей книги
11.31. Восстанавливаем исходные данные сводной таблицы
11.32. Ссылаемся на значения сводной таблицы

Глава 12. Диаграммы
12.1. Используем различные типы диаграмм
12.2. Вставляем диаграмму
12.3. Фильтруем диаграмму
12.4. Настраиваем внешний вид диаграммы
12.5. Добавляем и удаляем элементы диаграммы
12.6. Форматируем элементы диаграммы
12.7. Создаем динамические заголовки и метки
12.8. Настраиваем текст меток данных
12.9. Управляем осями и линиями сетки диаграммы
12.10. Отображаем отрицательные значения
12.11. Используем в гистограммах рисунки
12.12. Форматируем вторичную круговую и линейчатую диаграмму
12 .13. Форматируем гистограммы
12.14. Задаем типы диаграмм для комбинированной диаграммы
12.15. Управляем пустыми ячейками
12.16. Основываем диаграмму на независимых данных
12.17. Изменяем имена рядов данных и элементы легенды
12.18. Добавляем ряд или изменяем источник данных
12.19. Основываем диаграмму на динамическом именованном диапазоне
12.20. Вставляем сводную диаграмму
12.21. Создаем диаграмму Ганта
12.22. Создаем и используем шаблоны диаграмм

Глава 13. Графика, спарклайны и 3D-карты
13 .1 . Вставляем символы
13.2. Вставляем уравнения
13.3. Вставляем фигуры
13.4. Используем инструмент Рисование
13.5. Используем SmartArt
13.6. Вставляем рисунки
13.7. Группируем объекты
13.8. Перемещаем и изменяем размеры объектов вместе с ячейками
13.9. Вставляем связанный рисунок
13.10. Используем спарклайны
13 .11. Используем группы спарклайнов
13.12. Используем 3D-карты
13.13. Создаем видео с помощью 3D-карт

Глава 14. Анализ «что если»
14.1. Создаем таблицу данных с одной переменной
14.2. Создаем ориентированную на строку таблицу данных с одной переменной
14.3. Создаем таблицу данных с двумя переменными
14.4. Редактируем таблицы данных
14.5. Используем диспетчер сценариев
14.6. Объединяем сценарии
14.7. Генерируем сводки сценариев
14.8. Используем инструмент Подбор параметра
14.9. Находим несколько решений с помощью подбора параметра
14.10. Находим решение формулы, описывающей функцию, имеющую разрывы, с помощью подбора параметра
14.11. Устанавливаем надстройку Поискрешения
14.12. Решаем с помощью надстройки Поиск решения проблемы оптимизации
14.13. Используем в надстройке Поиск решения ограничение «только целые числа»
14.14. Используем в в надстройке Поиск решения ограничение «только двоичные»
14.15. Создаем с помощью надстройки Поиск решения изменяемые ячейки с разными значениями
14.16. Решаем с помощью надстройки Поиск решения проблему разрывов функции
14.17. Находим с помощью надстройки Поиск решения несколько решений
14.18. Находим с помощью надстройки Поиск решения глобальный минимум или максимум функции
14.19. Настаиваем параметры надстройки Поиск решения
14.20. Сохраняем и загружаем параметры надстройки Поиск решения
14.21. Сохраняем сценарии, сгенерированные надстройкой Поиск решения
14.22. Отображаем отчеты надстройки Поиск решения

Глава 15. Power Query
15.1. Получаем и загружаем данные
15.2. Получаем и загружаем данные из файлов в папке
15.3. Указываем, куда загрузить данные
15.4. Редактируем настройки и безопасность источника данных
15.5. Обновляем данные запроса
15.6. Управляем запросами
15.7. Редактируем запрос
15.8. Управляем шагами запроса
15.9. Управляем столбцами
15.1 О. Используем типы данных
15.11. Сортируем и фильтруем данные
15.12. Фильтруем файлы при загрузке данных из папки
15.13. Удаляем дубликаты, пустые строки и ошибки
15.14. Преобразуем данные в столбцах
15.15. Разделяем и объединяем столбцы
15.16. Выполняем сведение столбцов
15.17. Отменяем сведение столбцов
15 .18. Преобразуем структурированные столбцы
15.19. Возвращаем значение или список
15.20. Добавляем новые столбцы
15 .21. Добавляем столбцы, основанные на примерах
15.22. Добавляем условный столбец
15.23. Добавляем настраиваемый столбец
15.24. Используем параметры
15.25. Создаем пользовательскую функцию
15.26. Добавляем столбец, вызывая пользовательскую функцию
15.27. Дублируем отчет
15.28. Ссылаемся на запрос
15.29. Добавляем данные из нескольких запросов
15.30. Объединяем данные из нескольких запросов
15.31. Редактируем М-код запроса

Глава 16. Роwег Pivot и модель данных
16.1. Устанавливаем Power Pivot
16.2. Добавляем данные в модель данных
16.3. Управляем соединениями данных Power Pivot
16.4. Просматриваем таблицы модели данных и управляем ими
16.5. Обновляем данные модели данных
16.6. Работаем со столбцами таблицы
16.7. Создаем и изменяем взаимосвязи
16.8. Добавляем вычисляемый столбец
16.9. Создаем сводную таблицу или сводную диаграмму на основе модели данных
16.1 О. Вставляем меры
16.11. Используем КПЭ
16.12. Создаем иерархии
16.13. Создаем таблицу данных
16.14. Используем именованные наборы
16.15. Преобразуем сводную таблицу в формулы
16.16. Используем формулы кубов
16.17. Фильтруем формулы кубов с помощью срезов и временных шкал

Глава 17. LET, UMBDA и вспомогательные лямбда-функции
17 .1. Повышаем эффективность формулы
17.2. Пишем и тестируем лямбда-функции
17.3. Делаем лямбда-аргументы необязательными
17.4. Определяем пользовательскую лямбда-функцию
17.5. Пишем рекурсивные лямбда-формулы
17.6. Копируем пользовательскую лямбда-функцию в другую рабочую книгу
17. 7. Применяем лямбда-формулу в каждом столбце
17.8. Применяем лямбда-формулу в каждой строке
17.9. Создаем массив вычисляемых значений
17 .1 О. Преобразуем значения в массивах
17.11. Вычисляем кумулятивные значения
17.12. Возвращаем конечное значение кумулятивного вычисления

Глава 18. Инструменты разработчика: макросы, VBA, элементы управления и XML
18.1. Отображаем вкладку Разработчик
18.2. Записываем макросы
18.3. Используем личную книгу макросов
18.4. Редактируем настройки макроса
18.5. Запускаем макрос
18.6. Просматриваем и редактируем VВА-код макроса
18.7. Используем абсолютные и относительные ссылки
18.8. Создаем макрос путем написания VВА-кода.
18.9. Создаем пользовательскую функцию VBA
18.10. Используем события рабочих листов и рабочих книг
18.11. Переопределяем комбинацию клавиш с помощью ОпКеу
18.12. Планируем выполнение кода с помощью ОпТiте.
18.13. Удаляем макрос или функцию
18.14. Копируем код в другой VВА-проект
18.15. Исправляем ошибки VВА-кода
18.16. Используем встроенные диалоговые окна
18.17. Используем элементы управления формы
18.18. Используем элементы управления ActiveX
18.19. Создаем пользовательскую форму
18.20. Создаем пользовательскую надстройку Excel
18.21. Настраиваем параметры безопасности и конфиденциальности
18.22. Импортируем и экспортируем ХМL-файлы

Дальнейшие шаги

Предметный указатель
Об авторе
Об изображении на обложке