Советы по улучшению работы формул (часть 1)

1. Используйте таблицы для хранения данных
Начиная с Excel 2007 можно держать связанные друг с другом данные в таблицах (Преобразование данных: Главная ➡️ Стили ➡️ Форматировать как таблицу).
К данным в таблицах можно обращаться формулами с помощью структурных ссылок. Также данные таких таблиц можно использовать как источник для сводных таблиц. Если вы меняете количество строк в таблице — формулы по столбцам автоматически становятся динамическими.

2. Используйте именованные диапазоны и формулы
Используя именованные диапазоны и формулы, вы упростите восприятие вычислений. Более того, есть возможность провести промежуточные расчеты один раз в именованной функции вместо того, чтобы рассчитывать их отдельно. Это снимет часть вычислительной нагрузки и ускорит работу книги Excel.

➖➖➖➖➖➖➖➖➖➖➖➖➖➖
Наш чат | Курс по инвестированию

Советы по улучшению работы формул (часть 2)

3. Используйте сводные таблицы
Бывают случаи, когда вместо формул проще использовать сводную таблицу, где множество итогов можно получить всего в несколько кликов.
После построения сводной таблицы, можно ссылаться на значения простыми ссылками на её ячейки. Это избавит от необходимости выполнять необязательные вычисления.
Если данные меняются, достаточно просто перейти на вкладку Данные и обновить все сводные в одно действие.
Часто причиной медленной работы бывает большой объём данных, и сводные таблицы были разработаны именно для того, чтобы обрабатывать такие массивы информации.

4. Сортируйте данные
Ещё одна причина низкой скорости — попытки что-то найти в куче не отсортированных данных. У Excel нет выбора, и ему приходится искать иголку в стоге сена.
Отсортировав данные, можно значительно ускорить работу книги. Если сортировать что-то приходиться регулярно — можно запустить простейший макрос сразу, как только данные обновлены.

Часть 1

➖➖➖➖➖➖➖➖➖➖➖➖➖➖
Наш чат | Обучение Excel | Word Hacks

Советы по улучшению работы формул (часть 3)

5. Вычисление книги вручную
Еще одна функция, которая влияет на скорость - это моментальное отражение проведённых изменений.
Но для корректной работы нам далеко не всегда нужно, чтобы цепочка вычислений проходила после каждого нажатия. Зачастую вполне достаточно переключить расчёт формул в ручной режим и проводить вычисления, только когда это действительно требуется.
Это можно сделать, например, нажатием клавиши F9. Обратите внимание, что Excel проходит по формулам каждый раз, когда вы сохраняете файл.

6. Откажитесь от волатильных формул
В Excel существует особый класс формул — волатильные формулы. Они пересчитываются при любом изменении в книге. Примером таких формул служат: СЛЧИС, СЕГОДНЯ, ТДАТА, СМЕЩ и т.д.
Часто они нагружают лист вычислениями сверх необходимого.

Часть 1 | Часть 2

➖➖➖➖➖➖➖➖➖➖➖➖➖➖
Наш чат | Обучение Excel | Word Hacks

Советы по улучшению работы формул (часть 4)

7. Держите формулы на отдельном листе
Формулы — движущая сила любой книги или модели в Excel. Собрав их на отдельном листе, вы уменьшите вероятность ошибок, пробелов или повторов.
Работа над ускорением файла станет элементарной задачей, когда все формулы собраны в одном месте. Также это придаст файлу большую структурность и организованность.

8. Пишите хорошие формулы
Вот несколько советов, на которые можно ориентироваться:
• встроенные формулы удобнее ваших собственных;
• не тратьте силы Excel на работу с целым столбцом, когда требуется всего несколько значений;
• уберите или исправьте ошибки формул (деление на ноль, ошибки имени и т.д.);
• уберите или сократите использование массивных формул;
• удалите совсем или уменьшите количество ссылок на другие книги;
• удалите любые именованные диапазоны, которые выдают ошибку или несуществующие ссылки;
• не рассчитывайте цифры, которые вам не нужны;
• не считайте что-либо дважды, а лучше используйте первый результат во втором вычислении.

Часть 1 | Часть 2 | Часть 3

➖➖➖➖➖➖➖➖➖➖➖➖➖➖
Наш чат | Обучение Excel | Word Hacks

Советы по улучшению работы формул (часть 5)

9. Жёсткие решения
Иногда, что бы вы не делали, скорость книги всё равно оставляет желать лучшего, ниже представлено несколько жестких решений:
• замена формул на значения: сохраните формулы отдельно (или только в первой строке данных) ➡️ CTRL + C ➡️ CTRL + ALT +V ➡️только значения и форматы чисел;
• сборка книги с нуля: позволяет выбросить всё лишнее, о чём даже нельзя было догадаться;
• замените внешние ссылки на данные собственно актуальными данными;
• уменьшите функциональность;
• найдите альтернативное решение.

10. Узнавайте новые формулы и пробуйте их использовать
Оптимизация — вещь не определённая, до конца не регламентированная. Это продолжительный и развивающийся процесс: всегда нужно учить новые формулы, новые применения и играть с ними. И через это могут открыться новые способы улучшить тормозящие книги.

Часть 1 | Часть 2 | Часть 3 | Часть 4

➖➖➖➖➖➖➖➖➖➖➖➖➖➖
Наш чат | Обучение Excel | Word Hacks

Подборка постов #1

Способы создания уникальных списков:

• Первый способ (Удалить дубликаты)
— t.me/c/1150636847/152

• Второй способ (Расширенный фильтр)
— t.me/c/1150636847/153

• Третий способ (Power Query)
— t.me/c/1150636847/155

➖➖➖➖➖➖➖➖➖➖➖➖➖➖
Наш чат | Обучение Excel | Word Hacks

​​Выделение группы ячеек

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

Этот инструмент располагается на вкладке Главная ➡️ Найти и выделить ➡️ Выделить группу ячеек. Можно также нажать сочетание клавиш CTRL + G, а затем в появившемся окне щелкнуть Выделить.

В следующих постах рассмотрим примеры применения этой функции.

➖➖➖➖➖➖➖➖➖➖➖➖➖➖
Наш чат | Курс по инвестированию

​​Выделение группы ячеек: Примечания

Выделяются только те ячейки, которые содержат примечания.
Чтобы воспользоваться, достаточно одной выделенной ячейки на листе.

➖➖➖➖➖➖➖➖➖➖➖➖➖➖
Наш чат | Обучение Excel | Word Hacks

Подборка постов #2

Всё об функции автозаполнения:

• Общая информация
— t.me/c/1150636847/158

• Автозаполнение
числами | датами | текстом
на основе прогрессий | форматом

• Команда «Прогрессия»
— t.me/c/1150636847/168

• Другие списки автозаполнения
— t.me/c/1150636847/170

• Свои списки автозаполнения
— t.me/c/1150636847/173

➖➖➖➖➖➖➖➖➖➖➖➖➖➖
Наш чат | Курс по инвестированию

​​Выделение группы ячеек: Константы

Выделяются непустые ячейки, которые не содержат формул или ссылок на другие ячейки. Можно уточнить, ячейки с каким содержимым должны быть выделены: число, текст, логическое значение (ИСТИНА или ЛОЖЬ) или ошибки.

➖➖➖➖➖➖➖➖➖➖➖➖➖➖
Наш чат | Обучение Excel | Word Hacks

​​Выделение группы ячеек: Формулы

Выделяются ячейки, содержащие формулы. Можно также уточнить, какой именно результат формулы интересен: число, текст, логическое значение или ошибки.

➖➖➖➖➖➖➖➖➖➖➖➖➖➖
Наш чат | Курс по инвестированию

​​Выделение группы ячеек: Пустые ячейки

Выделяются пустые ячейки, расположенные в пределах заполненного диапазона. Пустой ячейкой считается ячейка без значений и формул, при этом она может содержать формат (заливку, границы или правило Условного форматирования).

➖➖➖➖➖➖➖➖➖➖➖➖➖➖
Наш чат | Курс по инвестированию

​​Выделение группы ячеек: Текущая область

Выделяется прямоугольный диапазон вокруг активной ячейки. Граница этого диапазона определяется окружающими его пустыми строками и столбцами.
Также текущую область можно выделить сочетанием клавиш CTRL + A.

➖➖➖➖➖➖➖➖➖➖➖➖➖➖
Наш чат | Курс по инвестированию

​​Выделение группы ячеек: Текущий массив

Выделяется диапазон ячеек, содержащий массив, в который входит активная ячейка.
Массив — это диапазон с формулой, завершенный сочетанием CTRL + SHIFT + ENTER. Формула при этом заключается в фигурные скобки, например: {=D3:D11/$D$12}.
Для выделения всего массива сначала нужно выделить одну ячейку, находящуюся в этом массиве.

➖➖➖➖➖➖➖➖➖➖➖➖➖➖
Наш чат | Обучение Excel | Word Hacks

Подборка постов #3

Новые возможности последней версии Excel 2019:

• Функции
— t.me/c/1150636847/115

• Иллюстрационные объекты
— t.me/c/1150636847/118

• Изменения на вкладке Данные
— t.me/c/1150636847/121

• Новые диаграммы
— t.me/c/1150636847/124

• Изменения на вкладке Рецензирование
— t.me/c/1150636847/130

• Новый формат данных География
— t.me/c/1150636847/136

➖➖➖➖➖➖➖➖➖➖➖➖➖➖
Наш чат | Курс по инвестированию

​​Выделение группы ячеек: Объекты

Выбираются все графические объекты рабочего листа, что позволяет обнаружить скрытые надписи, фигуры, диаграммы и т.д.

➖➖➖➖➖➖➖➖➖➖➖➖➖➖
Наш чат | Обучение Excel | Word Hacks