В Excel есть множество полезных встроенных функций, но это не значит, что их следует использовать бессистемно. Что делать, если Excel работает медленно Необходимо проверить некоторые формулы, чтобы убедиться в эффективности использования этих функций. Даже кажущиеся простыми формулы могут существенно влиять на производительность и вызывать множество проблем.
Хотя существует множество возможных причин, наиболее значимыми являются формулы, использующие функции переменных, функции массивов и функции поиска. Я объясню, почему они могут замедлять работу ваших рабочих листов, а также дам несколько советов о том, как их исправить для повышения производительности, а также о том, когда их следует полностью избегать.
Быстрые ссылки
Формулы, содержащие переменные функции
Недаром профессионалы используют его экономно.
Если вы заметили, что ваш лист Excel пересчитывается каждый раз при внесении изменений, и это влияет на производительность, вероятно, проблема в функциях переменных. Функции переменных вызывают пересчет для поддержания актуальности результатов. Они делают это, даже если изменение не затрагивает ни одну из указанных ячеек или их выходные данные.
Одна из распространённых функций для работы с переменными — NOW, которая возвращает текущую дату и время и полезна для таких сценариев, как расчёт сроков, добавление временных меток и обновление информационных панелей в режиме реального времени. Другая функция: Функция КОСВЕННАЯ, который использует текстовые строки для создания ссылок на ячейки и полезен, среди прочего, для ссылки на динамические диапазоны.
Хотя они хорошо справляются с поддержанием динамики, их следует использовать только при необходимости. Это особенно актуально для больших форм, содержащих десятки листов или тысячи строк. Также по возможности лучше использовать энергонезависимые альтернативы (например, вручную вводить дату вместо использования функции NOW).
Если вы не можете избежать переменных функций и они замедляют работу книги, вы можете отключить автоматический пересчёт. Таким образом, вы сможете запускать пересчёт вручную, а не при каждом небольшом изменении, но недостаток в том, что об этом легко забыть. F9 Пересчитывает все открытые рабочие листы и Shift + F9 Пересчитывает только активный лист.
Старые матричные формулы
Обработка матричных формул не всегда так проста.
Чтобы создать матричные формулы, введите формулу и нажмите Shift + Ctrl + Enter Вместо того, чтобы просто EnterВы поймете, что формула успешно создана, если она автоматически будет заключена в фигурные скобки.
Формулы массива особенно полезны, когда в вычислениях задействовано несколько значений, избавляя вас от необходимости создавать вспомогательные столбцы. Они также могут возвращать несколько результатов.
Матричные формулы — устаревшая функция, особенно после того, как Microsoft представила более эффективные динамические матричные функции в Excel для Microsoft 365. Тем не менее, они всё ещё актуальны, особенно если требуется совместимость со старыми версиями Excel (2019 и более ранними). Поэтому они не исчезнут в ближайшее время.
Хотя при использовании матричных формул всё аккуратно располагается на одной строке, Excel обрабатывает формулу несколько раз для получения результата. Это может быть очень затратно с точки зрения вычислений, особенно в больших таблицах Excel.
Чтобы избежать матричных формул, можно использовать вспомогательные столбцы, которые распределяют вычисления по нескольким ячейкам. Если это невозможно, попробуйте не использовать их с переменными функциями. Если совместимость не важна, можно просто использовать динамические матричные функции везде, где это возможно.
Формулы поиска
Это удобно, но может быть дорого.
Если вам нужно найти определённое значение в таблице Excel, самый быстрый способ — использовать формулу поиска. Наиболее распространённые функции, используемые в этих формулах, — это ВПР и ГПР, которые выполняют поиск по столбцам и строкам соответственно. Например, легко найти цену товара в вертикальном списке, используя его название. ВПР.
Проблема в том, что эти формулы поиска могут привести к снижению производительности при поиске по большим наборам данных, особенно если искомые данные распределены по нескольким листам. Это становится трудоёмким процессом и может привести к нагрузке на Excel.
Если вы не слишком активно используете функции поиска, это может не быть большой проблемой для версии Excel для Microsoft 365, поскольку она создаёт временный индекс, ускоряющий повторяющиеся поиски. Кроме того, в ней есть более эффективная (и универсальная) альтернативная функция поиска XLOOKUP, которую вы можете использовать вместо неё.
Если вам необходимо использовать формулы поиска в старых версиях Excel, эффективнее использовать формулу на том же листе, что и матрица поиска. Также следует ограничить область поиска только необходимыми ячейками (например, B2: B100) — Избегайте ссылок на весь столбец (например, B: B).
). Кроме того, используйте приблизительное совпадение вместо точного, поскольку в первом случае не требуется выполнять поиск по каждой ячейке, чтобы найти нужное значение.
Раскройте потенциал молниеносных файлов Excel
По моему опыту, именно эти три проблемы являются наиболее серьёзными для пользователей. Также следует обращать внимание на вложенные формулы, особенно содержащие сложную условную логику. Опять же, это может не быть серьёзной проблемой в Excel для Microsoft 365, где для ускорения рекурсивного поиска используются скрытые индексы.
Помимо проверки формул, убедитесь, что Excel актуален, удалите ненужные данные и неиспользуемые надстройки. Всё, что вы можете сделать для ускорения работы с файлом Excel, избавит вас от одной проблемы. Вы сможете сосредоточиться на работе, а не тратить время на оптимизацию.
