Производительность Excel: советы по оптимизации производительности

Статья
04/07/2023

Применимо к: Excel | Excel 2013 | Office 2016 | VBA

Следуйте этим советам по оптимизации многих часто возникающих препятствий производительности в Excel.

Оптимизация ссылок и ссылок

Узнайте, как повысить производительность, связанную с типами ссылок и ссылок.

Не используйте прямую ссылку и обратную ссылку

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

Минимизация использования циклических ссылок с помощью итерации

Вычисление циклических ссылок с итерациями выполняется медленно, так как требуется несколько вычислений, и эти вычисления являются однопоточными. Часто можно "раскрутить" циклические ссылки с помощью алгебры, чтобы итеративное вычисление больше не требовалось. Например, при расчете денежного потока и процентов попробуйте вычислить денежный поток до процента, вычислить процент, а затем вычислить денежный поток, включая проценты.

Excel вычисляет циклические ссылки по листу без учета зависимостей. Таким образом, если циклические ссылки охватывают несколько листов, скорость вычислений обычно снижается. Попробуйте переместить все циклические ссылки на один лист или оптимизировать последовательность вычислений на листе, чтобы избежать ненужных вычислений.

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

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

Важно уменьшить количество ячеек в циклических вычислениях и время вычисления, затраченного этими ячейками.

Избегайте связей между книгами

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

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

Попробуйте использовать простые прямые ссылки на ячейки в закрытых книгах. Это позволяет избежать пересчета всех связанных книг при пересчете любых книг. Кроме того, вы можете увидеть значения, считанные Excel из закрытой книги, что часто важно для отладки и аудита книги.

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

Свертывание связей между листами

Разбиение книги на множество листов обычно делает ее более простой в использовании, однако снижает производительность вычислений ссылок на другие листы относительно ссылок в пределах одного листа.

Свернуть используемый диапазон

Чтобы сохранить память и уменьшить размер файла, Excel пытается сохранить сведения только о той области листа, которая использовалась. Эта область называется используемый диапазон. Некоторые операции редактирования и форматирования охватывают область, значительно превышающую диапазон, который на данный момент считается используемым. Это может привести к проблемам, связанным с производительностью и размером файла.

Вы можете проверка видимый используемый диапазон на листе с помощью клавиш CTRL+END. Если это слишком много, рекомендуется удалить все строки и столбцы ниже и справа от последней использованной ячейки, а затем сохранить книгу. Перед этим следует создать резервную копию. Если некоторые формулы содержат диапазоны, указывающие или ссылающиеся на удаленные области, эти диапазоны будут уменьшены в размере или получат значение #Н/Д.

Разрешить дополнительные данные

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

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

Использование ссылок на структурированные таблицы (рекомендуется)

Начиная с Excel 2007, можно использовать структурированные ссылки на таблицы, которые автоматически расширяются и сжимаются по мере увеличения или уменьшения размера указанной таблицы.

Такой подход имеет ряд преимуществ:

Существует меньше недостатков производительности, чем альтернативные варианты ссылок на целые столбцы и динамические диапазоны.
На одном листе легко создать несколько таблиц данных.
Возможность расширения и сокращения внедренных в таблицу формул в соответствии с изменением размера области данных.

Кроме того, можно использовать ссылки на целые столбцы и строки.

Альтернативный подход заключается в использовании ссылки на столбец целиком, например, $A:$A. Эта ссылка возвращает все строки в столбце A. В этом случае все добавляемые данные всегда будут включаться в ссылку.

Такой подход имеет свои преимущества и недостатки:

Многие встроенные функции Excel (SUM, SUMIF) эффективно вычисляют ссылки на столбцы целиком, поскольку они автоматически распознают последнюю использованную строку в столбце. Тем не менее, функции вычисления массивов, такие как SUMPRODUCT, либо не могут обрабатывать ссылки на столбцы целиком, либо вычисляют все ячейки в столбце.
Определяемые пользователем функции не распознают последнюю используемую строку в столбце автоматически и, следовательно, часто вычисляют ссылки на целые столбцы неэффективно. Тем не менее, запрограммировать пользовательские функции на распознавание последней использованной строки достаточно легко.
При наличии нескольких таблиц данных на одном листе трудно использовать ссылки на целые столбцы.
В Excel 2007 и более поздних версиях формулы массива могут обрабатывать ссылки на целые столбцы, но это приводит к принудительному вычислению для всех ячеек в столбце, включая пустые. Для крупных таблиц, содержащих миллионы строк, это может привести к существенному снижению производительности.

Кроме того, используйте динамические диапазоны.

С помощью функций OFFSET или INDEX и COUNTA в определении именованного диапазона можно динамически расширять и сжимать область, на которую ссылается именованный диапазон. Например, создайте определенное имя, используя одну из следующих формул:

  =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
  =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)+ROW(Sheet1!$A$1) - 1,1)

При использовании имени динамического диапазона в формуле он автоматически расширяется для включения новых записей.

Использование формулы INDEX для динамического диапазона, как правило, предпочтительнее формулы OFFSET , так как недостаток OFFSET заключается в том, что она является изменяющейся функцией, которая будет вычисляться при каждом пересчете.

Производительность снижается, так как функция COUNTA в формуле динамического диапазона должна проверять много строк. Это снижение производительности можно свести к минимуму, сохранив часть формулы COUNTA в отдельной ячейке или заданном имени, а затем сославшись на ячейку или имя в динамическом диапазоне:

 Counts!z1=COUNTA(Sheet1!$A:$A)
 OffsetDynamicRange=OFFSET(Sheet1!$A$1,0,0,Counts!$Z$1,1)
 IndexDynamicRange=Sheet1!$A$1:INDEX(Sheet1!$A:$A,Counts!$Z$1+ROW(Sheet1!$A$1) - 1,1)

Вы также можете использовать такие функции, как INDIRECT , для создания динамических диапазонов, но INDIRECT является изменчивым и всегда вычисляет однопотоковые.

Применение динамических диапазонов имеет свои преимущества и недостатки:

Динамические диапазоны эффективно использовать для ограничения числа вычислений, выполняемых формулами массива.
Для использования нескольких динамических диапазонов в одном столбце требуются специальные функции подсчета.
Использование большого числа динамических диапазонов может привести к снижению производительности.

Повышение времени вычисления подстановки

В Office 365 версии 1809 и более поздних функции Excel ВПР, ГПР и ПОИСКПОЗ для поиска точного совпадения в несортированных данных выполняются как никогда быстро при поиске в нескольких столбцах (или строках с помощью ГПР) из одного диапазона таблицы.

При этом для более ранних версий Excel поиск по-прежнему является часто значительным препятствием для вычислений. Тем не менее, существует много способов оптимизации вычисления поиска. Если используется параметр точного совпадения, время вычисления функции пропорционально числу ячеек, проверяемому до нахождения совпадения. Для поиска в больших диапазонах это время может быть достаточно велико.

Время поиска с использованием параметров приблизительного совпадения VLOOKUP, HLOOKUP и MATCH для отсортированных данных достаточно мало и не увеличивается существенно с изменением длины диапазона, для которого выполняется поиск. В этом случае характеристики аналогичны характеристикам двоичного поиска.

Общие сведения о параметрах подстановки

Убедитесь, что вы понимаете параметры сопоставления типов и диапазонов поиска в MATCH, VLOOKUP и HLOOKUP.

В следующем примере кода показан синтаксис функции MATCH. Дополнительные сведения см. в описании метода Match объекта WorksheetFunction.

  MATCH(lookup value, lookup array, matchtype)

Matchtype=1 возвращает наибольшее совпадение, меньшее или равное значению подстановки, если массив подстановки отсортирован по возрастанию (приблизительное совпадение). Если массив подстановки не отсортирован по возрастанию, функция MATCH вернет неправильный ответ. Параметр по умолчанию — приблизительное соответствие по возрастанию.
Matchtype=0 запрашивает точное совпадение. При этом предполагается, что данные не отсортированы.
Matchtype=-1 возвращает наименьшее совпадение, значение которого не меньше значения поиска, при сортировке массива поиска по убыванию (приблизительное совпадение).

В следующем примере кода показан синтаксис функций VLOOKUP и HLOOKUP. Дополнительные сведения см. в описании методов ВПР и ГПР объекта WorksheetFunction.

  VLOOKUP(lookup value, table array, col index num, range-lookup)
  HLOOKUP(lookup value, table array, row index num, range-lookup)

Range-lookup=TRUE возвращает наибольшее совпадение, значение которого не превышает значение поиска (приблизительное совпадение). Это параметр по умолчанию. Табличные массивы должны быть отсортированы по возрастанию.
Range-lookup=FALSE запрашивает точное совпадение. При этом предполагается, что данные не отсортированы.

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

Используйте INDEX и MATCH или OFFSET вместо ВПР

Попробуйте использовать функции INDEX и MATCH вместо VLOOKUP. Хотя функция ВПР выполняется немного быстрее (примерно на 5 процентов быстрее), проще и использует меньше памяти, чем сочетание MATCH и INDEX или OFFSET, дополнительная гибкость, которую часто предлагают MATCH и INDEX , позволяет значительно сэкономить время. Например, можно сохранить результат точного совпадения MATCH в ячейке и затем повторно использовать его в нескольких выражениях INDEX.

Функция INDEX является быстрой и является энергонезависимой функцией, которая ускоряет пересчет. Функция OFFSET также работает быстро; однако это изменчивая функция, которая иногда значительно увеличивает время, затрачиваемое на обработку цепочки вычислений.

Преобразовать VLOOKUP в INDEX и MATCH легко. Следующие два оператора возвращают один и тот же ответ:

 VLOOKUP(A1, Data!$A$2:$F$1000,3,False)

 INDEX(Data!$A$2:$F$1000,MATCH(A1,$A$1:$A$1000,0),3)

Ускорение поиска

Поскольку поиск с точным совпадением может выполняться достаточно медленно, можно рассмотреть следующие способы повышения производительности:

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

Использование двух подстановок для отсортированных данных с отсутствующими значениями

Два приблизительных совпадения значительно быстрее, чем одно точное совпадение для поиска более нескольких строк. (Точка безубыточного останова составляет около 10–20 строк.)

Если вы можете отсортировать данные, но по-прежнему не можете использовать приблизительное совпадение, так как не можете быть уверены, что искомое значение существует в диапазоне подстановки, можно использовать следующую формулу:

  IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val, _
      VLOOKUP(lookup_val, lookup_array, column, True), "notexist")

В первой части формулы осуществляется поиск приблизительного совпадения для самого столбца поиска.

  VLOOKUP(lookup_val ,lookup_array,1,True)

Вы можете проверка, если ответ из столбца подстановки совпадает со значением подстановки (в этом случае имеется точное совпадение), используя следующую формулу:

  IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val,

Если эта формула возвращает значение True, вы нашли точное совпадение, поэтому вы можете выполнить приблизительный поиск еще раз, но на этот раз верните ответ из нужного столбца.

  VLOOKUP(lookup_val, lookup_array, column, True)

Если ответ из столбца подстановки не совпадает со значением подстановки, у вас отсутствует значение, и формула возвращает значение notexist.

Обратите внимание, что если выполняется поиск значения, которое меньше, чем минимальное значение в списке, отображается сообщение об ошибке. Можно реализовать обработку этой ошибки с помощью функции IFERROR или добавить в список минимальное тестовое значение.

Использование функции IFERROR для несортированных данных с отсутствующими значениями

Если необходимо использовать точный поиск соответствия для несортированных данных и вы не можете быть уверены, существует ли значение подстановки, часто необходимо обработать возвращаемое #N/A, если совпадение не найдено. Начиная с Excel 2007, вы можете использовать функцию IFERROR , которая является простой и быстрой.

  IF IFERROR(VLOOKUP(lookupval, table, 2 FALSE),0)

В более ранних версиях можно применять простой, но более медленный способ с использованием функции IF, подразумевающий выполнение двух операций поиска.

  IF(ISNA(VLOOKUP(lookupval,table,2,FALSE)),0,_
      VLOOKUP(lookupval,table,2,FALSE))

Чтобы избежать двойного поиска точного совпадения, можно один раз выполнить поиск MATCH с точным совпадением, сохранить результат в ячейке, а затем проверять этот результат перед выполнением функции INDEX.

  In A1 =MATCH(lookupvalue,lookuparray,0)
  In B1 =IF(ISNA(A1),0,INDEX(tablearray,A1,column))

Если нельзя использовать две ячейки, воспользуйтесь функцией COUNTIF. Как правило, это быстрее, чем поиск точного соответствия.

  IF (COUNTIF(lookuparray,lookupvalue)=0, 0, _
      VLOOKUP(lookupval, table, 2 FALSE))

Использование match и INDEX для поиска точного соответствия в нескольких столбцах

Во многих случаях можно многократно использовать сохраненное точное совпадение MATCH. Например, при выполнении точного поиска в нескольких столбцах результатов можно сэкономить время, используя одно выражение MATCH и несколько выражений INDEX вместо множества выражений VLOOKUP.

Добавьте дополнительный столбец для match , чтобы сохранить результат (stored_row), и для каждого столбца результата используйте следующее:

  INDEX(Lookup_Range,stored_row,column_number)

Также можно использовать функцию VLOOKUP в формуле массива. (Формулы массива необходимо вводить с помощью клавиш CTRL+-SHIFT+ВВОД. Excel добавит { и }, чтобы показать, что это формула массива).

  {VLOOKUP(lookupvalue,{4,2},FALSE)}

Использование INDEX для набора смежных строк или столбцов

Одна операция поиска может возвращать несколько ячеек. Для поиска в непрерывной последовательности из нескольких столбцов можно использовать функцию INDEX в формуле массива, что позволит возвращать несколько столбцов за один раз (используйте 0 в качестве номера столбца). Также можно использовать INDEX для возврата нескольких строк за один раз.

  {INDEX($A$1:$J$1000,stored_row,0)}

Это выражение возвращает столбцы с A по J из сохраненной строки, созданной предыдущим выражением MATCH.

Использование MATCH для возврата прямоугольного блока ячеек

Используйте функции MATCH и OFFSET для возврата прямоугольного блока ячеек.

Использование MATCH и INDEX для двухмерного поиска

Вы можете эффективно выполнять подстановку двумерной таблицы, используя отдельные подстановки для строк и столбцов таблицы с помощью функции INDEX с двумя встроенными функциями MATCH : одна для строки и одна для столбца.

Использование диапазона подмножества для подстановки с несколькими индексами

На больших листах часто может потребоваться выполнить поиск с помощью нескольких индексов, таких как поиск объемов продуктов в стране или регионе. Для этого можно объединить индексы и выполнить поиск с использованием объединенных значений поиска. Тем не менее, такой подход неэффективен по двум причинам:

Операция объединения строк требует больших затрат вычислительных ресурсов.
Поиск охватывает большой диапазон.

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

Рассмотрите варианты трехмерного подстановки

Чтобы выполнять поиск в таблице в дополнение к строке и столбцу, можно использовать следующие способы, в которых основной акцент делается на способ поиска или выбора таблицы в Excel.

Если каждая таблица, которую требуется найти (третье измерение), хранится в виде набора именованных структурированных таблиц, имен диапазонов или таблицы текстовых строк, представляющих диапазоны, вы можете использовать функции CHOOSE или INDIRECT .

В некоторых случаях более эффективно будет использовать функцию CHOOSE и имена диапазонов. Функция CHOOSE не является переменной, однако оптимально подходит только при наличии небольшого числа таблиц. В этом примере динамически используется TableLookup_Value для выбора имени диапазона (TableName1, TableName2, ...) для таблицы подстановки.
```
  INDEX(CHOOSE(TableLookup_Value,TableName1,TableName2,TableName3), _
  MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
```
В следующем примере функция INDIRECT используется для TableLookup_Value динамического создания имени листа, используемого для таблицы подстановки. Этот метод достаточно прост и эффективен при работе с большим числом таблиц. Так как INDIRECT является изменяющейся однопоточной функцией, поиск вычисляется по одному потоку при каждом вычислении, даже если данные не изменились. Использование этого метода выполняется медленно.
```
  INDEX(INDIRECT("Sheet" &amp; TableLookup_Value &amp; "!$B$2:$Z$1000"), _
  MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
```
Также можно использовать функцию VLOOKUP для поиска имени листа или текстовой строки, которые будут использоваться для таблицы, а затем с помощью функции INDIRECT преобразовать полученный текст в диапазон.
```
  INDEX(INDIRECT(VLOOKUP(TableLookup_Value,TableOfTAbles,1)),MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
```

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

Использование поиска с подстановочными знаками

Функции MATCH, VLOOKUP и HLOOKUP позволяют использовать подстановочные знаки ? (любой один символ) и * (без символов или любое количество символов) в алфавитном точном совпадении. В некоторых случаях такой метод может использоваться для поиска уникального совпадения.

Оптимизация формул массива и SUMPRODUCT

Применение формул массива и функций SUMPRODUCT дает широкие возможности, однако при этом следует соблюдать осторожность. Для одной формулы массива может потребоваться много вычислений.

Чем меньшее число ячеек и выражений вычисляется в формуле массива, тем выше будет скорость ее вычисления. Помните, что формула массива немного похожа на изменяющуюся формулу: если какая-либо из ячеек, на которую она ссылается, изменилась, является изменчивой или была пересчитана, формула массива вычисляет все ячейки в формуле и вычисляет все виртуальные ячейки, необходимые для вычисления.

Оптимизация скорости вычисления формул массива:

Выведите ссылки на выражения и диапазоны за пределы формул массива в отдельные вспомогательные столбцы и строки. Это позволит более эффективно использовать процесс интеллектуального пересчета в Excel.
Не задавайте ссылки на строки целиком или большее, чем требуется, число строк и столбцов. Формулы массива принудительно вычисляют все ссылки на ячейки в формуле (в том числе и ссылки на пустые и неиспользуемые ячейки). Поскольку начиная с версии Excel 2007 поддерживается более миллиона строк, формула массива, ссылающаяся на столбец целиком, будет вычисляться очень медленно.
Начиная с версии Excel 2007, по возможности используйте структурированные ссылки, чтобы свести к минимуму число ячеек, которые вычисляются в формуле массива.
В версиях, предшествующих Excel 2007, по возможности используйте имена динамических диапазонов. Несмотря на то, что при таком подходе используется переменная функция, это позволит свести к минимуму размеры диапазонов.
Обращайте внимание на формулы массива, ссылающиеся одновременно на строку и столбец: в этом случае вычисляется прямоугольный диапазон.
По возможности, используйте функцию SUMPRODUCT. Она работает несколько быстрее по сравнению с эквивалентной формулой массива.

Рассмотрите варианты использования СУММ для формул массива с несколькими условиями

Вместо формул массива всегда следует использовать функции SUMIFS, COUNTIFS и AVERAGEIFS , так как они вычисляются гораздо быстрее. Excel 2016 представлены быстрые функции MAXIFS и MINIFS.

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

Если все же требуется использовать формулы массива, можно применять следующие эффективные методы для повышения их скорости:

Используйте имена динамических диапазонов или ссылки на структурированные таблицы, чтобы свести к минимуму количество ячеек.
Разделите несколько условий на столбец вспомогательных формул, возвращающих значение True или False для каждой строки, а затем сослаться на вспомогательный столбец в формуле СУММЕСЛИ или массива. Может показаться, что это не приведет к сокращению числа вычислений для одной формулы массива; однако большую часть времени он позволяет интеллектуальному пересчету пересчитывать только формулы во вспомогательном столбце, которые необходимо пересчитать.
Рассмотрите возможность объединения всех условий в одно, для которого будет использоваться функция SUMIF.
Если данные можно отсортировать, подсчитайте группы строк и ограничьте формулы массива просмотром групп подмножества.

Определение приоритета нескольких условий СУММЕСЛИ, СЧЁТЕСЛИ И других функций семейства IFS

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

Рассмотрите варианты использования SUMPRODUCT для формул массива с несколькими условиями

Начиная с Excel 2007, всегда следует использовать функции SUMIFS, СЧЁТЕСЛИ И AVERAGEIFS, а в Excel 2016 функции MAXIFS и MINIFS вместо формул SUMPRODUCT, где это возможно.

В более ранних версиях применение функции SUMPRODUCT вместо формул массива SUM давало ряд преимуществ:

SUMPRODUCT не обязательно вводить массив с помощью клавиш CTRL+SHIFT+ВВОД.
Функция SUMPRODUCT обычно выполняется несколько быстрее (от 5 до 10 процентов).

Используйте SUMPRODUCT для формул массива с несколькими условиями следующим образом:

  SUMPRODUCT(--(Condition1),--(Condition2),RangetoSum)

В этом примере и Condition2 являются условными выражениями, Condition1 такими как $A$1:$A$10000<=$Z4. Поскольку условные выражения вместо числовых данных возвращают значения True или False, их необходимо привести к числовым значениям в функции SUMPRODUCT. Это можно сделать, используя два знака минус (--), добавив 0 (+0) или умножив на 1 (x1). Использование -- выполняется немного быстрее, чем +0 или x1.

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

Кроме того, можно напрямую умножить термины внутри SUMPRODUCT, а не разделять их запятыми:

  SUMPRODUCT((Condition1)*(Condition2)*RangetoSum)

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

Использование SUMPRODUCT для умножения и добавления диапазонов и массивов

В некоторых случаях, например, при вычислении средневзвешенных значений, когда требуется перемножение диапазонов чисел и суммирование результатов, применение синтаксиса функции SUMPRODUCT с разделением запятыми может быть на 20–25 процентов эффективнее, чем использование функции SUM с вводом массива.

  {=SUM($D$2:$D$10301*$E$2:$E$10301)}
  =SUMPRODUCT($D$2:$D$10301*$E$2:$E$10301)
  =SUMPRODUCT($D$2:$D$10301,$E$2:$E$10301)

Все эти три формулы дают одинаковый результат, но третья формула, использующая синтаксис запятых для SUMPRODUCT, занимает только около 77 процентов времени вычисления, необходимого для двух других формул.

Помните о потенциальных препятствиях для вычислений массивов и функций

Ядро вычислений Excel оптимизировано для работы с формулами массива и функциями, ссылающимися на диапазоны. Тем не менее, при нестандартном использовании таких формул и функций в некоторых случаях возможно существенное увеличение времени вычисления.

При обнаружении проблем при вычислениях с применением формул и функций диапазонов следует обратить внимание на наличие следующих препятствий:

Частично перекрывающиеся ссылки.
Формулы массива и функции диапазонов, ссылающиеся на блок ячеек, которые вычисляются в другой формуле массива или функции диапазона. Такие ситуации достаточно часто происходят во время анализа временных последовательностей.
Наличие различных наборов формул, один из которых ссылается по строке, а второй ссылается на первый набор по столбцу.
Крупный набор одностроковых формул массива, охватывающих блок столбцов с функцией SUM в нижней части каждого столбца.

Эффективное использование функций

Функции значительно расширяют возможности Excel, но способ их использования часто может повлиять на время вычисления.

Избегайте однопоточных функций

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

Определяемые пользователем функции VBA и службы автоматизации, но определяемые пользователем функции на основе XLL могут быть многопотоковые.
PHONETIC
ЯЧЕЙКА, когда используется аргумент "format" или "address"
СМЕЩ
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
КУБЭЛЕМЕНТ
КУБЗНАЧЕНИЕ
КУБСВОЙСТВОЭЛЕМЕНТА
КУБМНОЖ
КУБПОРЭЛЕМЕНТ
КУБЭЛЕМЕНТКИП
КУБЧИСЛОЭЛМНОЖ
АДРЕС, где указан пятый параметр ()sheet_name
Любая функция базы данных (БДСУММ, ДСРЗНАЧ, и т. д.), которая ссылается на сводную таблицу
ТИП.ОШИБКИ
ГИПЕРССЫЛКА

Использование таблиц для функций, обрабатывающих диапазоны

Для таких функций, как SUM, SUMIF и SUMIFS , обрабатывающих диапазоны, время вычисления пропорционально количеству используемых ячеек, которые вы суммируете или подсчитываете. Неиспользуемые ячейки не проверяются, поэтому ссылки на целые столбцы относительно эффективны, но лучше убедиться, что вы не включаете больше используемых ячеек, чем требуется. Используйте таблицы или вычисляйте диапазоны подмножества или динамические диапазоны.

Сокращение изменяющихся функций

Применение переменных функций может уменьшить скорость пересчета, поскольку в таком случае увеличивается число пересчитываемых при каждом вычислении формул.

Зачастую можно уменьшить число переменных функций, используя функцию INDEX вместо OFFSET и функцию CHOOSE вместо INDIRECT. Тем не менее , OFFSET — это быстрая функция, которую часто можно использовать в творческих способах, позволяющих быстро вычислять.

Использование определяемых пользователем функций C или C++

Определяемые пользователем функции, запрограммированные на C или C++ и использующие API C (функции надстроек XLL), обычно выполняются быстрее, чем определяемые пользователем функции, разработанные с помощью VBA или автоматизации (надстройки XLA или автоматизации). Дополнительные сведения см. в статьеDeveloping Excel 2010 XLLs.

Производительность определяемых пользователем функций VBA определяется тем, как вы программировать и вызывать их.

Использование более быстрых пользовательских функций VBA

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

Убедитесь, что все ссылки на ячейки листа в пользовательской функции помещены во входные параметры, а не в тело самой функции, что позволяет избежать добавления ненужных элементов Application.Volatile.

Если требуется много формул, использующих определяемые пользователем функции, убедитесь, что вы находитесь в режиме вычисления вручную и что вычисление инициируется из VBA. Пользовательские функции VBA вычисляются значительно медленнее, если вычисление вызывается не из VBA (например, в автоматическом режиме или с помощью клавиши F9 в ручном режиме). Это особенно актуально, когда редактор Visual Basic (ALT+F11) открыт или был открыт в текущем сеансе Excel.

В этом случае можно перехватывать нажатие клавиши F9 и выполнять перенаправление в подпрограмму вычисления VBA, как показано ниже. Добавьте эту подпрограмму в модуль Thisworkbook .

  Private Sub Workbook_Open()
      Application.OnKey "{F9}", "Recalc"
  End Sub

Добавьте в стандартный модуль следующую подпрограмму.

  Sub Recalc()
      Application.Calculate
      MsgBox "hello"
  End Sub

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

Если пользовательская функция обрабатывает каждую ячейку диапазона, следует объявить входной параметр как диапазон, назначить его переменной, содержащей массив, и реализовать его циклический перебор. Для эффективной обработки ссылок на столбцы целиком необходимо использовать подмножество входного диапазона, разбив его на пересечении с используемым диапазоном, как показано в этом примере.

  Public Function DemoUDF(theInputRange as Range)
      Dim vArr as Variant
      Dim vCell as Variant
      Dim oRange as Range
      Set oRange=Union(theInputRange, theRange.Parent.UsedRange)
      vArr=oRange
      For Each vCell in vArr
          If IsNumeric(vCell) then DemoUDF=DemoUDF+vCell
      Next vCell
  End Function

Если для обработки диапазона в пользовательской функции используются функции листа или методы объектной модели Excel, зачастую более эффективно хранить диапазон в виде объектной переменной, чем передавать все данные из Excel в такую функцию.

  Function uLOOKUP(lookup_value As Variant, lookup_array As Range, _
                   col_num As Variant, sorted As Variant, _
                   NotFound As Variant)
      Dim vAnsa As Variant
      vAnsa = Application.VLookup(lookup_value, lookup_array, _
                                  col_num, sorted)
      If Not IsError(vAnsa) Then
          uLOOKUP = vAnsa
      Else
          uLOOKUP = NotFound
      End If
  End Function

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

  If ISEMPTY(Cell.Value) AND Len(Cell.formula)>0 then

Для каждого вызова определяемой пользователем функции и для каждой передачи данных из Excel в VBA существуют временные затраты. Иногда определяемая пользователем функция с формулой массива с несколькими ячейками может помочь свести к минимуму эти издержки, объединяя несколько вызовов функций в одну функцию с диапазоном входных данных с несколькими ячейками, который возвращает диапазон ответов.

Сведите к минимуму диапазон ячеек, на которые ссылались СУММ и СУММЕСЛИ

Функции Excel SUM и SUMIF часто используются при работе с большим числом ячеек. Время вычисления этих функций пропорционально числу охватываемых ячеек, поэтому рекомендуется свести к минимуму диапазон ячеек, на которые ссылаются функции.

Использование подстановочных знаков SUMIF, COUNTIF, SUMIFS, COUNTIFS и других функций IFS

Используйте подстановочные знаки ? (любой один символ) и * (без символов или любого количества символов) в критериях для алфавитных диапазонов в составе функций SUMIF, COUNTIFS, СЧЁТЕСЛИ и других функций IFS.

Выбор метода для период-к-дате и накопительных SUM

Существует два метода выполнения период-к-дата или кумулятивные suM. Предположим, что числа, которые требуется суммировать суммой суммы , находятся в столбце A, а столбец B будет содержать совокупную сумму; вы можете выполнить одно из следующих действий:

Вы можете создать формулу в столбце B, например, =SUM($A$1:$A2) и перетащить ее вниз по мере необходимости. В качестве начальной ячейки для функции СУММ зафиксирована ячейка A1, однако, поскольку конечная ячейка содержит относительную ссылку на строку, ее номер автоматически увеличивается для каждой строки.
Вы можете создать формулу, например =$A1 в ячейках B1 и =$B1+$A2 B2, и перетащить ее вниз по мере необходимости. В этом случае накопительная сумма в ячейке будет вычисляться посредством сложения значения в текущей строке с накопительной суммой в предыдущей строке (аналогично применению функции SUM).

Для набора из 1000 строк при первом способе приложение Excel выполняет около 500 000 вычислений. Для этого же набора второй способ требует всего 2000 вычислений.

Вычисление сумм подмножества

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

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

Подсчитайте число строк для каждого подблока.
Чтобы определить начальную строку каждого блока, суммируйте число строк накопительно.
Используйте функцию OFFSET, указав в качестве параметра начальную строку и число строк, чтобы вернуть в функцию SUM или SUMIF поддиапазон, который охватывает только определенный блок строк.

Использование ПРОМЕЖУТОЧНЫХ ИТОГОВ для отфильтрованных списков

С помощью функции SUBTOTAL можно суммировать (функция SUM) отфильтрованные списки. Функция SUBTOTAL эффективна в использовании, поскольку, в отличие от функции SUM, она пропускает следующие элементы:

Скрытые в результате фильтрации списка строки. Начиная с версии Excel 2003, можно также выполнять функцию SUBTOTAL, пропуская все скрытые, а не только отфильтрованные строки.
Другие функции SUBTOTAL.

Использование функции AGGREGATE

Функция AGGREGATE — это эффективный и эффективный способ вычисления 19 различных методов агрегирования данных (таких как SUM, MEDIAN, PERCENTILE и LARGE). ФУНКЦИЯ AGGREGATE позволяет игнорировать скрытые или отфильтрованные строки, значения ошибок и вложенные функции SUBTOTAL и AGGREGATE .

Избегайте использования DFunctions

Функции DSUM, DCOUNT, DAVERAGE и аналогичные им работают намного быстрее по сравнению с эквивалентными формулами массива. Недостатком таких функций является то, что условие должно находиться в отдельном диапазоне, что делает их использование непрактичным во многих случаях. Начиная с версии Excel 2007, вместо этих функций следует использовать функции SUMIFS, COUNTIFS и AVERAGEIFS.

Создание более быстрых макросов VBA

Ниже приведены советы по созданию более быстрых макросов VBA.

Отключить все, кроме основных компонентов во время выполнения кода

Чтобы повысить производительность макросов VBA, явно отключите функциональность, которая не требуется во время выполнения кода. Часто один пересчет или один перерасчет после выполнения кода — это все, что необходимо и может повысить производительность. После выполнения кода восстановите функциональность в исходное состояние.

При выполнении макросов VBA обычно можно отключать следующие функции:

Application.ScreenUpdating Отключить обновление экрана. Если параметр Application.ScreenUpdating имеет значение False, Excel не перерисовывает экран. Во время выполнения кода экран быстро обновляется, и пользователю обычно не нужно видеть каждое обновление. Обновление экрана один раз после выполнения кода повышает производительность.
Application.DisplayStatusBar Отключите строку состояния. Если параметр Application.DisplayStatusBar имеет значение False, excel не отображает строку состояния. Параметр строки состояния отделен от параметра обновления экрана, поэтому вы по-прежнему можете отображать состояние текущей операции, даже если экран не обновляется. Однако если вам не нужно отображать состояние каждой операции, отключение строки состояния во время выполнения кода также повышает производительность.
Application.Calculation Переключитесь на ручное вычисление. Если параметр Application.Calculation имеет значение xlCalculationManual, Excel вычисляет книгу только тогда, когда пользователь явно инициирует вычисление. В режиме автоматического вычисления Excel определяет время вычисления. Например, при каждом изменении значения ячейки, связанного с формулой, Excel пересчитывает формулу. При переключении режима вычисления на ручной можно подождать, пока не будут обновлены все ячейки, связанные с формулой, перед пересчетом книги. Пересчитывая книгу только при необходимости во время выполнения кода, можно повысить производительность.
Application.EnableEvents Отключить события. Если параметр Application.EnableEvents имеет значение False, Excel не вызывает событий. Если надстройки прослушивают события Excel, эти надстройки потребляют ресурсы на компьютере при записи событий. Если надстройке не требуется записывать события, возникающие во время выполнения кода, отключение событий повышает производительность.
ActiveSheet.DisplayPageBreaks Отключить разрывы страниц. Если параметр ActiveSheet.DisplayPageBreaks имеет значение False, Excel не отображает разрывы страниц. Не нужно пересчитывать разрывы страниц во время выполнения кода, а вычисление разрывов страниц после выполнения кода повышает производительность.

Важно!

Не забудьте восстановить исходное состояние этих функций после выполнения кода.

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

  ' Save the current state of Excel settings.
  screenUpdateState = Application.ScreenUpdating
  statusBarState = Application.DisplayStatusBar
  calcState = Application.Calculation
  eventsState = Application.EnableEvents
  ' Note: this is a sheet-level setting.
  displayPageBreakState = ActiveSheet.DisplayPageBreaks 

  ' Turn off Excel functionality to improve performance.
  Application.ScreenUpdating = False
  Application.DisplayStatusBar = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  ' Note: this is a sheet-level setting.
  ActiveSheet.DisplayPageBreaks = False

  ' Insert your code here.

  ' Restore Excel settings to original state.
  Application.ScreenUpdating = screenUpdateState
  Application.DisplayStatusBar = statusBarState
  Application.Calculation = calcState
  Application.EnableEvents = eventsState
  ' Note: this is a sheet-level setting
  ActiveSheet.DisplayPageBreaks = displayPageBreaksState

Чтение и запись больших блоков данных в одной операции

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

Ниже приведен неоптимизированный пример кода, в котором осуществляется перебор ячеек по одной для получения и установки значений ячеек в диапазоне A1:C10000. Эти ячейки не содержат формул.

  Dim DataRange as Range
  Dim Irow as Long
  Dim Icol as Integer 
  Dim MyVar as Double 
  Set DataRange=Range("A1:C10000") 

  For Irow=1 to 10000 
      For icol=1 to 3
          ' Read the values from the Excel grid 30,000 times.
          MyVar=DataRange(Irow,Icol) 
          If MyVar > 0 then 
              ' Change the value.
              MyVar=MyVar*Myvar 
              ' Write the values back into the Excel grid 30,000 times.
              DataRange(Irow,Icol)=MyVar
          End If 
      Next Icol 
  Next Irow

Ниже показан оптимизированный пример кода, в котором для получения и установки значений одновременно всех ячеек в диапазоне A1:C10000 используется массив. Эти ячейки не содержат формул.

  Dim DataRange As Variant
  Dim Irow As Long 
  Dim Icol As Integer 
  Dim MyVar As Double 
  ' Read all the values at once from the Excel grid and put them into an array.
  DataRange = Range("A1:C10000").Value2 

  For Irow = 1 To 10000 
      For Icol = 1 To 3 
          MyVar = DataRange(Irow, Icol) 
          If MyVar > 0 Then 
              ' Change the values in the array.
              MyVar=MyVar*Myvar 
              DataRange(Irow, Icol) = MyVar 
          End If 
      Next Icol 
  Next Irow 
  ' Write all the values back into the range at once.
  Range("A1:C10000").Value2 = DataRange

Использовать. Значение2, а не . Значение или . Текст при чтении данных из диапазона Excel

. Текст возвращает форматируемое значение ячейки. Это происходит медленно, может вернуть ### при увеличении масштаба пользователем и потерять точность.
. Значение возвращает валюту VBA или переменную даты VBA, если диапазон был отформатирован как Дата или Валюта. Это происходит медленно, может потерять точность и вызвать ошибки при вызове функций листа.
. Значение 2 работает быстро и не изменяет данные, извлекаемые из Excel.

Избегайте выбора и активации объектов

Процесс выбора и активации объектов сопряжен со значительно большими затратами вычислительных ресурсов, чем указание прямых ссылок на объекты. Путем прямой ссылки на объект , например Range или Shape , можно повысить производительность. В следующем примере кода показано сравнение этих двух методов.

В следующем примере кода показан неоптимированный код, который выделяет каждую фигуру на активном листе и изменяет текст на "Hello".

  For i = 0 To ActiveSheet.Shapes.Count
      ActiveSheet.Shapes(i).Select
      Selection.Text = "Hello"
  Next i

В следующем примере кода показан оптимизированный код, который ссылается на каждую фигуру напрямую и изменяет текст на "Hello".

  For i = 0 To ActiveSheet.Shapes.Count
      ActiveSheet.Shapes(i).TextEffect.Text = "Hello"
  Next i

Использование этих дополнительных оптимизаций производительности VBA

Ниже приведен список дополнительных параметров, влияющих на производительность, которые можно оптимизировать в коде VBA:

Возвращайте результаты посредством присвоения массива напрямую переменной Range.
Объявляйте переменные с явными типами. Это позволит избежать затрат ресурсов на определение типа данных при выполнении кода (многократное выполнение таких операций в цикле существенно скажется на производительности).
Для простых, часто используемых в коде функций можно самостоятельно реализовать функции в VBA вместо использования объекта WorksheetFunction. Дополнительные сведения см. в статье Использование более быстрых пользовательских функций VBA.
Воспользуйтесь методом Range.SpecialCells для уменьшения числа ячеек, с которыми взаимодействует код.
Учитывайте повышение производительности, если вы реализовали свою функциональность с помощью API C в пакете SDK для XLL. Дополнительные сведения см. в документации по пакету Excel 2010 XLL SDK.

Рассмотрите производительность и размер форматов файлов Excel

Начиная с версии Excel 2007, в приложении Excel поддерживается более широкий набор форматов файлов по сравнению с более ранними версиями. Если не учитывать варианты формата макросов, шаблонов, надстройки, PDF и XPS, три main формата— XLS, XLSB и XLSX.

Формат XLS

Формат XLS является аналогом такого же формата в более ранних версиях. При использовании этого формата размер книги ограничен 256 столбцами и 65 536 строками. При сохранении книги Excel 2007 или Excel 2010 в формате XLS выполняется проверка совместимости. Размер файла практически всегда совпадает с файлами предыдущих версий (иногда сохраняется некоторая дополнительная информация), а производительность может быть несколько ниже, чем в более ранних версиях. Функции многопоточной оптимизации порядка вычислений, реализованные в Excel, в формате XLS не сохраняются. В связи с этим, после сохранения книги в формате XLS, ее закрытия и повторного открытия скорость вычислений может снизиться.
Формат XLSB

Двоичный формат XLSB впервые представлен в Excel 2007. Он структурирован в виде сжатой папки, содержащей много двоичных файлов. Он гораздо компактнее, чем формат XLS, но объем сжатия зависит от содержимого книги. Например, для десяти разных книг степень сжатия может находиться в диапазоне от 2 до 8 (в среднем обычно степень сжатия находится в районе 4). Начиная с версии Excel 2007, производительность при открытии и сохранении в этом формате лишь незначительно ухудшается по сравнению с форматом XLS.
Формат XLSX

XLSX — это XML-формат, который был представлен и используется по умолчанию в приложении Excel 2007. Формат XLSX — это сжатая папка, содержащая много XML-файлов (если изменить расширение имени файла на .zip, можно открыть сжатую папку и проверить ее содержимое). В большинстве случаев размер XLSX-файла превышает размер файла в формате XLSB (в среднем около 1,5 раз), однако его размер по-прежнему будет значительно меньше размера аналогичного XLS-файла. Открытие и сохранение файлов в этом формате занимает несколько больше времени по сравнению с XLSB-файлами.

Открытие, закрытие и сохранение книг

Иногда открытие, закрытие и сохранение книг может занимать значительно больше времени, чем их вычисление. В некоторых случаях это связано с большим размером книги, однако могут быть и другие причины такого поведения.

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

Временные файлы.

Временные файлы могут накапливаться в каталоге \Windows\Temp (в Windows 95, Windows 98 и Windows ME) или в каталоге \Documents and Settings\User Name\Local Settings\Temp (в Windows 2000 и Windows XP). Excel создает эти файлы для книги и элементов управления, используемых в открытых книгах. Кроме того, временные файлы могут создаваться программами установки ПО. Если приложение Excel перестало отвечать, может потребоваться удаление этих файлов вручную.

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

Простой способ открыть временный каталог — в меню "Пуск" Windows: нажмите кнопку Пуск, а затем нажмите кнопку Выполнить. В текстовом поле введите %temp%, а затем нажмите кнопку ОК.

Отслеживание изменений в общей книге

Отслеживание изменений в общей книге влечет за собой значительное увеличение размера файла книги.
Фрагментарный файл подкачки

Убедитесь, что файл подкачки Windows располагается на диске, на котором достаточно свободного места и регулярно выполняется дефрагментация.
Книга со структурой, защищенной паролем

Книга, структура которого защищена паролем (меню> Средства Защита>)> книга открывается и закрывается гораздо медленнее, чем книга, которая защищена без дополнительного пароля.
Проблемы с используемым диапазоном

Слишком большой размер используемого диапазона обычно влечет за собой снижение скорости открытия и увеличение размера файла, особенно в том случае, когда такой диапазон содержит скрытые строки или столбцы нестандартной высоты или ширины. Дополнительные сведения о проблемах с используемым диапазоном см. в разделе Минимизация используемого диапазона.
Большое количество элементов управления на листах

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

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

Некоторые параметры антивирусной программы могут вызывать проблемы или снижение скорости при открытии, закрытии или сохранении книг, особенно на сервере. Если есть основания предполагать наличие такого рода проблем, попробуйте на время отключить антивирусную программу.
Медленное вычисление приводит к медленному открытию и сохранению

В некоторых случаях Excel пересчитывает книгу при ее открытии или сохранении. Если время вычисления для книги является длительным и вызывает проблему, убедитесь, что для вычисления задано значение вручную, и рассмотрите возможность отключения параметра "Вычислить перед сохранением" (Tools>Options>Calculation).
Файлы панели инструментов (XLB)

Проверьте размер файла панели инструментов. Типичный файл панели инструментов составляет от 10 ДО 20 КБ. Вы можете найти файлы XLB, выполнив поиск *.xlb с помощью поиска Windows. У каждого пользователя есть уникальный XLB-файл. Добавление, изменение или настройка панелей инструментов увеличивает размер файла toolbar.xlb. При удалении файла удаляются все настройки панели инструментов (переименование ее "toolbar. OLD" безопаснее). При следующем открытии Excel создается новый XLB-файл.

Выполнение дополнительных оптимизаций производительности

Вы можете повысить производительность в следующих областях.

Сводные таблицы

Сводные таблицы эффективно подходят для обобщения крупных объемов данных.
- Итоги в качестве окончательных результатов. Если требуется формировать итоги и промежуточные итоги в составе конечных результатов для книги, рекомендуется использовать сводные таблицы.
- Итоги в качестве промежуточных результатов. С помощью сводных таблиц можно создавать эффективные сводные отчета. Тем не менее, не рекомендуется создавать формулы, в которых результаты сводной таблицы используются в качестве промежуточных итогов в цепочке вычислений, если не соблюдаются следующие условия:
- Сводные таблицы надлежащим образом обновляются в процессе вычисления.
- Сводные таблицы не изменялись, и данные по-прежнему видимы.
Если все же требуется использовать данные сводных таблиц в качестве промежуточных результатов, используйте функцию GETPIVOTDATA.
Условные форматы и проверка данных

Применение условного форматирования и проверки данных бывает крайне полезным, однако слишком активное использование этих функций может повлечь за собой существенное снижение скорости вычислений. При отображении ячейки каждая формула условного формата вычисляется при каждом вычислении и при обновлении отображения ячейки, содержащей условный формат. В объектной модели Excel представлено свойство Worksheet.EnableFormatConditionsCalculation, с помощью которого можно включать и отключать вычисление условных форматов.
Определенные имена

Определенные имена — это одна из самых полезных возможностей приложения Excel, однако их применение может увеличить время вычисления. Использование имен, ссылающихся на другие листы, существенно усложняет процесс вычисления. Кроме того, настоятельно не рекомендуется использовать вложенные имена, которые ссылаются на другие имена.

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

Многие книги содержат значительное количество формул и подстановок, которые связаны с получением входных данных в соответствующую форму для вычислений или используются в качестве защитных мер против изменения размера или формы данных. Если у вас есть блоки формул, которые используются только иногда, можно скопировать и вставить специальные значения, чтобы временно исключить формулы, или поместить их в отдельную, редко открываемую книгу. Так как ошибки на листе часто возникают из-за того, что формулы были преобразованы в значения, предпочтительнее использовать метод отдельной книги.
Использование достаточного объема памяти

32-разрядная версия Excel может использовать до 2 ГБ ОЗУ или до 4 ГБ ОЗУ для 32-разрядных версий Excel 2013 и 2016 с поддержкой больших адресов. Тем не менее, компьютер, на котором работает приложение Excel, также потребляет определенный объем ресурсов памяти. Соответственно, если на компьютере установлено ровно 2 ГБ ОЗУ, приложение Excel не сможет полностью использовать допустимый объем памяти в 2 ГБ, поскольку часть ресурсов будет зарезервирована для операционной системы и других выполняемых программ. Чтобы оптимизировать производительность Excel на 32-разрядном компьютере, рекомендуется иметь на компьютере не менее 3 ГБ ОЗУ.

64-разрядная версия Excel не имеет ограничения в 2 ГБ или до 4 ГБ. Дополнительные сведения см. в разделе "Большие наборы данных и 64-разрядная версия Excel" статьи Производительность Excel: улучшения производительности и ограничений.

Заключение

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

См. также

Поддержка и обратная связь

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