Медленный подробный статус документа Excel

Question 1

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

Мои главные советы по оптимизации скорости работы вашей книги Excel:

Сравните размер данных рабочей книги(столбцы, строки, листы) и размер файла. Непропорционально большой файл может содержать некоторые скрытые диаграммы или рисунки, которые занимают большие куски памяти. Вы можете переименовать .xlsx в .zip, затем распаковать в папку и найти самый большой файл, который даст вам подсказку.
Сохранить как разные типы файлов, например, старый .xls сохранить в новый .xlsx, новый .xlsx в двоичный .xlsb. Это особенно полезно при длительной загрузке и сохранении больших наборов данных.
Проверьте использование ЦП и памяти. Высокое использование памяти без большого движения ЦП будет означать, что в основном это просто много данных, но не очень интенсивной обработки. Двоичный формат (.xlsb) в сочетании с RAM (удвойте RAM) и обновлением диска (SSD) поможет. Также проверьте настройки автосохранения/резервного копирования, чтобы уменьшить количество раз, когда файлы сохраняются/резервируются во время работы с ними.
ЕслиИспользование ЦП и памятивысоки, скорее всего, вам придется лезть в формулы, чтобы оптимизировать. Другим признаком интенсивного расчета формул будет внезапное и значительное замедление после, скажем, редактирования одной из ранних входных ячеек, тогда как относительно быстро только во время просмотра (за исключением начального времени загрузки).
ИЗМЕНЧИВЫЕ функции(OFFSET, INDIRECT, NOW, RAND и т. д.) — это смерть любой большой электронной таблицы. Вы можете проверить их, отредактировав пустую, не имеющую ссылок, одинокую ячейку внизу. Если это редактирование инициирует несколько задержек, то, скорее всего, виноваты изменчивые функции. О, и не забудьтеУСЛОВНОЕ ФОРМАТИРОВАНИЕТАКЖЕ ИЗМЕНЧИВ.
ИЗМЕНЧИВЫЕ триггерывключают действия, которые вызывают пересчет по всей (электронной) таблице, такие как изменение автофильтров, настройка ширины и высоты ячеек, поиск цели, скрытие/отображение/вставка/удаление/перемещение/переименование (ячеек и листов), обновление подключений, например, к файлам CSV или базам данных, изменение именованных ячеек и диапазонов. Если что-либо из этого также выполняется в макросах VBA, вы поняли.
НАСТРОЙКИ ПЕРЕСЧЕТАможет существенно повлиять на производительность. Если возможно, отключите автоматический пересчет и пересчитайте вручную после обновлений/редактирования. Избегайте итеративных вычислений.
Отслеживание иждивенцев/прецедентовс помощью опций на ленте Формулы или с помощью CTRL+[ и CTRL+]. Последнее весьма полезно, так как вы можете нажать CTRL+[ несколько раз и определить зависимые элементы зависимых элементов зависимых элементов. Они фактически выбираются по мере продвижения (в отличие от стрелок, добавляемых кнопками на ленте) и могут быть закодированы цветом/помечены по вашему желанию.
Покажите или найдите проблемные формулы.Вы можете переключить ленту формул >Показать формулы(CTRL+~), чтобы легко обнаружить проблемные формулы. Вы также можете сделать рабочую книгу широкойнаходитьдля изменчивых функций, например, найдите OFFSET.
Не забывайте про именованные диапазоны.Проверьте диспетчер имен на наличие именованных диапазонов, содержащих изменчивые функции.
ИдтиНЕЛЕТУЧНЫЙ. Замените INDIRECT на INDEX(..MATCH(..)) и OFFSET на относительные именованные диапазоны (например, именованный диапазон "CellAbove")
Оптимизация памяти данных.УменьшитьИСПОЛЬЗОВАННЫЙ ДИАПАЗОН(нет пустых строк и столбцов внутри используемого диапазона). Избегайте перекрестных ссылок на другие рабочие книги или даже другие листы. Избегайте хранения данных в виде текста. Убедитесь, что это пронумерованные данные. Избегайте мнимых чисел, поскольку они на самом деле хранятся в виде текста. Если у вас есть Office 365, используйте LET, чтобы избежать повторного вычисления одной и той же части формулы снова и снова. Например, =IF(LOG(A1)>B1,LOG(A1),B1)может быть =LET(x, LOG(A1), IF(x > B1, x, B1)). Кроме того, если разные ячейки выполняют одну и ту же часть уравнения снова и снова, лучше вычисляйте ее в отдельной ячейке или определите именованную.
Оптимизируйте свои макросы/код VBA/javascript. Отключить пересчет/обновления экрана/события в начале каждого вызова подпрограммы или функции. Запустить оставшуюся часть кода и включить их снова.
Использоватьблокирует чтение и запись ячеек. Это особенно актуально в коде VBA/macro/javascript. Доступ к отдельным ячейкам медленный, особенно если включены обновления экрана. Попробуйте накопить свои ответы в массиве и блочно записать его обратно в ячейки. В связи с этим вы можете использовать формулы массива (и проливание в Excel 365), чтобы сделать эквивалент блочных операций чтения/записи в формулах Excel. Я не проверял, действительно ли это быстрее, но моя интуиция говорит да.
Проверьте соединения для передачи данных.Некоторые соединения настроены на автоматическое обновление при открытии книги, что может существенно увеличить общее время загрузки, особенно при обновлении онлайн-/сетевых данных.

Answer

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

Мои главные советы по оптимизации скорости работы вашей книги Excel:

Сравните размер данных рабочей книги(столбцы, строки, листы) и размер файла. Непропорционально большой файл может содержать некоторые скрытые диаграммы или рисунки, которые занимают большие куски памяти. Вы можете переименовать .xlsx в .zip, затем распаковать в папку и найти самый большой файл, который даст вам подсказку.
Сохранить как разные типы файлов, например, старый .xls сохранить в новый .xlsx, новый .xlsx в двоичный .xlsb. Это особенно полезно при длительной загрузке и сохранении больших наборов данных.
Проверьте использование ЦП и памяти. Высокое использование памяти без большого движения ЦП будет означать, что в основном это просто много данных, но не очень интенсивной обработки. Двоичный формат (.xlsb) в сочетании с RAM (удвойте RAM) и обновлением диска (SSD) поможет. Также проверьте настройки автосохранения/резервного копирования, чтобы уменьшить количество раз, когда файлы сохраняются/резервируются во время работы с ними.
ЕслиИспользование ЦП и памятивысоки, скорее всего, вам придется лезть в формулы, чтобы оптимизировать. Другим признаком интенсивного расчета формул будет внезапное и значительное замедление после, скажем, редактирования одной из ранних входных ячеек, тогда как относительно быстро только во время просмотра (за исключением начального времени загрузки).
ИЗМЕНЧИВЫЕ функции(OFFSET, INDIRECT, NOW, RAND и т. д.) — это смерть любой большой электронной таблицы. Вы можете проверить их, отредактировав пустую, не имеющую ссылок, одинокую ячейку внизу. Если это редактирование инициирует несколько задержек, то, скорее всего, виноваты изменчивые функции. О, и не забудьтеУСЛОВНОЕ ФОРМАТИРОВАНИЕТАКЖЕ ИЗМЕНЧИВ.
ИЗМЕНЧИВЫЕ триггерывключают действия, которые вызывают пересчет по всей (электронной) таблице, такие как изменение автофильтров, настройка ширины и высоты ячеек, поиск цели, скрытие/отображение/вставка/удаление/перемещение/переименование (ячеек и листов), обновление подключений, например, к файлам CSV или базам данных, изменение именованных ячеек и диапазонов. Если что-либо из этого также выполняется в макросах VBA, вы поняли.
НАСТРОЙКИ ПЕРЕСЧЕТАможет существенно повлиять на производительность. Если возможно, отключите автоматический пересчет и пересчитайте вручную после обновлений/редактирования. Избегайте итеративных вычислений.
Отслеживание иждивенцев/прецедентовс помощью опций на ленте Формулы или с помощью CTRL+[ и CTRL+]. Последнее весьма полезно, так как вы можете нажать CTRL+[ несколько раз и определить зависимые элементы зависимых элементов зависимых элементов. Они фактически выбираются по мере продвижения (в отличие от стрелок, добавляемых кнопками на ленте) и могут быть закодированы цветом/помечены по вашему желанию.
Покажите или найдите проблемные формулы.Вы можете переключить ленту формул >Показать формулы(CTRL+~), чтобы легко обнаружить проблемные формулы. Вы также можете сделать рабочую книгу широкойнаходитьдля изменчивых функций, например, найдите OFFSET.
Не забывайте про именованные диапазоны.Проверьте диспетчер имен на наличие именованных диапазонов, содержащих изменчивые функции.
ИдтиНЕЛЕТУЧНЫЙ. Замените INDIRECT на INDEX(..MATCH(..)) и OFFSET на относительные именованные диапазоны (например, именованный диапазон "CellAbove")
Оптимизация памяти данных.УменьшитьИСПОЛЬЗОВАННЫЙ ДИАПАЗОН(нет пустых строк и столбцов внутри используемого диапазона). Избегайте перекрестных ссылок на другие рабочие книги или даже другие листы. Избегайте хранения данных в виде текста. Убедитесь, что это пронумерованные данные. Избегайте мнимых чисел, поскольку они на самом деле хранятся в виде текста. Если у вас есть Office 365, используйте LET, чтобы избежать повторного вычисления одной и той же части формулы снова и снова. Например, =IF(LOG(A1)>B1,LOG(A1),B1)может быть =LET(x, LOG(A1), IF(x > B1, x, B1)). Кроме того, если разные ячейки выполняют одну и ту же часть уравнения снова и снова, лучше вычисляйте ее в отдельной ячейке или определите именованную.
Оптимизируйте свои макросы/код VBA/javascript. Отключить пересчет/обновления экрана/события в начале каждого вызова подпрограммы или функции. Запустить оставшуюся часть кода и включить их снова.
Использоватьблокирует чтение и запись ячеек. Это особенно актуально в коде VBA/macro/javascript. Доступ к отдельным ячейкам медленный, особенно если включены обновления экрана. Попробуйте накопить свои ответы в массиве и блочно записать его обратно в ячейки. В связи с этим вы можете использовать формулы массива (и проливание в Excel 365), чтобы сделать эквивалент блочных операций чтения/записи в формулах Excel. Я не проверял, действительно ли это быстрее, но моя интуиция говорит да.
Проверьте соединения для передачи данных.Некоторые соединения настроены на автоматическое обновление при открытии книги, что может существенно увеличить общее время загрузки, особенно при обновлении онлайн-/сетевых данных.

Question 2

Я предполагаю, что в рабочей книге используется VBA, и опубликую ответ, основанный на этом предположении.

Большая часть затрат будет связана с обновлением экрана и/или записью файла на диск/сетевой ресурс.

Для обновления экрана оберните модуль, который выполняет тяжелую работу, в

Application.ScreenUpdating = True
    'Do Something
Application.ScreenUpdating = False

Эти настройки создают видимость того, что ничего не происходит, но код-behind все равно будет выполняться. Вы можете добавить несколько debug.print' и следить за своим непосредственным окном, чтобы убедиться, что вы делаете.

Вы также можете рассмотреть возможность отключения Application.EnableEventsи использования только Application.Calculation = xlAutomaticв случае крайней необходимости.

Что касается стоимости диска. Я бы рекомендовал вам (если вы еще этого не сделали) разместить файл на локальной машине, а также, если возможно, исходные данные MatLab. Чтение/запись на/из сетевых ресурсов изначально медленны, по сравнению с локальным диском.

Answer