Вопрос или проблема
При работе в таблицах с множеством формул, если пользователи вводят данные в ячейку, а затем покидают её, Excel зависает на несколько секунд или до пары минут. Время варьируется в зависимости от того, с какой таблицей они работают. Что я обнаружил с помощью ресурса мониторинга, так это то, что во время этих зависаний процессор достигает жёсткого лимита использования в 25%. В наших мощных рабочих книгах используется 100% двух из восьми ядер, но на моём ноутбуке, который менее мощный, нагрузка распределяется на все четыре ядра, но всё равно фиксируется на 25%. Согласно форуму MSDN по Excel, это нормально для некоторых пользовательских формул во всех версиях Excel использовать максимум 25%, однако я тестировал это в Excel 2010, и процесс был мгновенным, и не было никакого заметного использования процессора на той же модели ноутбука с той же конфигурацией памяти.
Я проверил распределение процессора для формул в Excel, и он настроен использовать всю доступную мощность процессора.
Если отключить автоматические вычисления, проблема остаётся.
Я попробовал скопировать несколько строк из одного листа в новую книгу, и это заняло всего десятую долю секунды, но как только я добавил остальные строки, время зависания увеличилось до половины секунды. Чем больше данных добавляется, тем длиннее становятся зависания с удивительной скоростью. Полусекундное зависание было в одном листе, который содержал всего 50 строк, каждая строка имела формулы в пяти или шести столбцах.
Я пытался отключить ссылки на другие рабочие книги и изолировать эту, но это не повлияло на длительность зависания.
Я могу понять, что Excel ограничивает себя 25% процессора, но что не могу понять, так это почему этот процесс использует 25% процессора, когда в 2010 году точно такая же таблица не вызывала заметного использования процессора.
Кажется, что каждый раз при вводе данных в ячейку Excel пересчитывает каждую формулу во всей книге. Это может быть нормальной практикой, а может и нет. Согласно нескольким форумам, которые я посещал в ходе исследования этой проблемы, именно так работает Excel, и всегда работал. Однако очевидно, что что-то изменилось. Если в 2010 году пересчитывалась каждая формула каждый раз, это происходило эффективно, в то время как в 2016 году это вешает всё приложение. Я нахожу другие сообщения о подобном в 2013 году, но в них никто не предложил решение, которое я бы ещё не попробовал.
Любые советы, которые у вас могут быть, были бы полезны, поскольку пользователи из финансового отдела имеют несколько таких таблиц, которые требуют сотни записей каждый месяц. Они не могут работать эффективно или разумно, если им приходится ждать, пока обработается каждая запись. Пользователи уже заявили, что если мы не найдём решение, потребуется откатиться на Office 2010, так как это делает невозможным выполнение их работы в срок.
Здесь вы можете увидеть пример строки, показывающей формулы в одном из документов. Я скрыл столбцы, в которых нет формул или данных. Вы можете нажать на изображение, чтобы развернуть его и сделать читаемым.
Быстрое (хотя и некрасивое, но быстрое) решение этой проблемы заключается в том, чтобы щелкнуть на другой вкладке в рабочей книге, а затем вернуться и получить доступ к ячейке. Однако это происходит так часто, что работать в Excel 2016 ужасно.
Отмечу, что у меня возникают наибольшие проблемы при вводе данных в любую ячейку, содержащую “@” – например, при введении электронных писем или паролей с “@”.
Я тоже столкнулся с этой проблемой. Программа зависает, и загрузка процессора увеличивается на длительное время. Иногда это может длиться несколько минут, в других случаях — более получаса!
Обычно это происходило у меня на работе при использовании среднесложных формул в таблицах со средними по длине листами. Некоторые листы содержат более 400 строк, и мои формулы используют такие функции, как INDEX, MATCH, EXACT, VLOOKUP, TRANSPOSE, OFFSET, наряду с несколькими другими, которые, вероятно, менее нагружают программу. Я также использую некоторое количество именованных массивов.
Это явный баг, и разработчики должны его изучить.
Ответ или решение
Excel 2016 может вызывать фризы при вводе данных в электронные таблицы с множеством формул, и эта проблема требует внимательного анализа и решения. В указанной ситуации проблема проявляется тогда, когда пользователи вводят данные в ячейки и выходят из них, после чего Excel зависает на несколько секунд или даже минут. В случае значительных нагрузок он использует 100% двух из восьми ядер процессора. Даже если автоматические вычисления отключены, зависания продолжаются.
Теория (Т)
Основная проблема, вероятно, связана с перераспределением вычислительного процесса в версии Excel 2016 по сравнению с версией 2010. Возможно, изменения в алгоритмах обработки данных и формул или более строгие условия безопасности могли значительно повлиять на производительность. Когда данные вводятся в ячейку, Excel, как правило, пересчитывает все формулы в книге, что в данном случае вызывает чрезмерные задержки.
Некоторые пользователи также отметили, что зависания могут быть связаны с использованием определенных символов, таких как "@", что может вызвать дополнительные проверки или обработки в программе.
Пример (Э)
Рассмотрим пример насыщенной формулами электронной таблицы, где каждая строка содержит формулы в нескольких столбцах. Допустим, мы используем формулы INDEX, MATCH, EXACT, VLOOKUP, TRANSPOSE и OFFSET, и у нас есть именные массивы. Такая структура данных требует значительных ресурсов для пересчета, особенно при изменении данных.
Если в Excel 2010 та же самая таблица не вызывала существенной нагрузки на процессор, изменение в версии 2016 может указывать на ухудшение производительности в новых версиях программы из-за дополнительных вычислительных процессов или изменений в реализации формул.
Применение (П)
Для решения этой проблемы можно предложить несколько подходов:
-
Оптимизация формул и структуры данных:
- Убедитесь, что все формулы оптимальны и не содержат избыточных вычислений. Функции, такие как OFFSET и INDIRECT, могут быть ресурсоемкими, и их можно заменить на более производительные альтернативы.
- Пересмотрите использование именных массивов и попытайтесь минимизировать их применение, если это возможно.
-
Настройка параметров вычислений:
- Проверьте настройки многоядерной обработки в Excel и, если возможно, измените параметры, чтобы приложение могло использовать большее количество ядер более эффективно.
- Включите режим ручного пересчета (через ‘Formulas’ -> ‘Calculation Options’ -> ‘Manual’), и запускайте пересчеты вручную с помощью F9 после ввода значительных объемов данных.
-
Обновление и исправление:
- Проверьте наличие обновлений для Office и Excel, которые могли бы исправить известные ошибки в версии 2016.
- Убедитесь, что все драйвера, особенно графические, вовремя обновлены, так как они могут влиять на производительность Excel.
-
Переменная загрузка данных:
- Разделите файлы на несколько частей, чтобы уменьшить нагрузку на каждый файл отдельно. Это может помочь в обработке данных и пересчете формул более управляемыми порциями.
Пользователи выражают необходимость в ином приложении таких проблем, утверждая, что они испытывают сложные и продолжительные задержки, иногда превышающие полчаса, что недопустимо в рабочей среде.
Если ни одно из решений не помогает, следует рассмотреть возможность использования более старой стабильной версии Excel 2010, которая, по опыту пользователя, более стабильно обрабатывает такие задачи. Конечно, любой переход на более раннюю версию может потребовать тщательного анализа возможных потерь в функционале и обновлениях безопасности.
Это профессиональное решение требует системного подхода и внимательного анализа как со стороны ИТ-специалистов, так и пользователей, чтобы наилучшим образом адаптировать использование Excel в существующей инфраструктуре компании.