Эксель формулы которые работают быстрее
Перейти к содержимому

Эксель формулы которые работают быстрее

  • автор:

Производительность Excel: повышение производительности вычислений

«Большая сетка» (Big Grid), состоящая из 1 миллиона строк и 16 000 столбцов, в Office Excel 2016 вместе с увеличением многих других предельных значений значительно увеличивает максимальный размер создаваемых листов по сравнению с более ранними версиями Excel. Отдельный лист в Excel может содержать в 1 000 раз больше ячеек, чем было в более ранних версиях.

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

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

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

Важность скорости вычисления

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

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

  • Автоматическое вычисление — формулы пересчитываются автоматически при внесении изменения.
  • Вычисление вручную — формулы пересчитываются только в случае запроса пользователя (например, при нажатии пользователем клавиши F9).

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

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

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

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

Общие сведения о методах вычисления в Excel

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

Полное вычисление и зависимости пересчета

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

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

В Excel продолжается вычисление ячеек, которые зависят от ранее вычисленных ячеек, даже если значение ранее вычисленной ячейки не меняется при вычислении.

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

В режиме вычисления вручную вы можете запустить этот модуль пересчета, нажав клавишу F9. Можно вызвать полное вычисление всех формул, нажав клавиши CTRL+ALT+F9, или полностью перестроить зависимости и выполнить полное вычисление, нажав клавиши SHIFT+CTRL+ALT+F9.

Процесс вычисления

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

В Excel можно выделить четкие этапы вычисления:

  1. Построение исходной цепочки вычислений и определение места начала вычисления. Этот этап выполняется, когда книга загружается в память.
  2. Отслеживание зависимостей, пометка ячеек как нерассчитанных и обновление цепочки вычислений. Этот этап выполняется при каждой записи или каждом изменении даже в режиме вычисления вручную. Обычно этот этап выполняется настолько быстро, что пользователь не замечает его, но в сложных случаях отклик может быть медленным.
  3. Расчет всех формул. В ходе процесса вычисления в Excel переупорядочивается и переструктурируется цепочка вычислений в целях оптимизации будущих пересчетов.
  4. Обновление видимых частей окон Excel.

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

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

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

Выполнение вычислений в рабочих книгах, листах и диапазонах

Управлять тем, что именно рассчитывается, можно с помощью различных методов вычислений в Excel.

Пересчет всех открытых книг

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

Расчет выбранных листов

Можно пересчитать только выбранные листы, используя сочетания клавиш SHIFT+F9. Это не решает зависимости между листами и не сбрасывает грязные ячейки как рассчитанные.

Расчет диапазона ячеек

В Excel для расчета диапазона ячеек можно использовать методы Visual Basic для приложений (VBA) Range.CalculateRowMajorOrder и Range.Calculate:

  • Range.CalculateRowMajorOrder выполняет расчет диапазона слева направо и сверху вниз, при этом все зависимости игнорируются.
  • Range.Calculate выполняет расчет диапазона, решая все зависимости в диапазоне.

Так как метод CalculateRowMajorOrder не решает никакие зависимости в рассчитываемом диапазоне, он обычно значительно быстрее, чем Range.Calculate. Однако его следует применять осторожно, так как результаты могут отличаться от результатов, полученных с помощью Range.Calculate.

Range.Calculate является одним из самых полезных инструментов в Excel для оптимизации производительности, поскольку позволяет сравнить скорость вычисления различных формул.

Переменные функции

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

Некоторые встроенные функции в Excel являются явно переменными: СЛЧИС(), ТДАТА(), СЕГОДНЯ(). Для других функций менее очевидно, что они переменные: СМЕЩ(), ЯЧЕЙКА(), ДВВСЫЛ(), ИНФОРМ().

Некоторые функции, которые ранее были указаны в документации как переменные, фактически не являются переменными: ИНДЕКС(), ЧСТРОК(), ЧИСЛСТОЛБ(), ОБЛАСТИ().

Действия для пересчета

Действия для пересчета вызывают повторное выполнение вычисления. К ним относятся следующие действия:

  • Щелчок разделителя строки или столбца при работе в автоматическом режиме.
  • Вставка или удаление строк, столбцов или ячеек в листе.
  • Добавление, изменение или удаление определенных имен.
  • Переименование листов или изменение положения листов при работе в автоматическом режиме.
  • Фильтрация, скрытие или отмена скрытия строк.
  • Открытие книги при работе в автоматическом режиме. Если в прошлый раз расчет книги выполнялся в другой версии Excel, открытие книги обычно приводит к полному вычислению.
  • Сохранение книги в ручном режиме, если выбран параметр Пересчитывать книгу перед сохранением.

Условия расчета формулы и имени

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

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

Формула помечается как невычисляемая, когда она ссылается на ячейку или формулу (зависит от ячейки или формулы), для которой выполняется одно из следующих условий:

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

Формула, помеченная как невычисляемая, рассчитывается при расчете или перерасчете листа, книги или экземпляра Excel, содержащего эту формулу.

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

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

Таблицы данных

Таблицы данных Excel (вкладка «Данные» > Группа >«Что, если>таблица анализов«) не следует путать с таблицей (группа «Стили» вкладки «Главная» > в формате >таблицы или группа «Вставкатабличных> таблиц>«). В таблицах данных Excel выполняются множественные пересчеты книги, каждый управляется различными значениями в таблице. В Excel книга сначала рассчитывается обычным образом. Затем для каждой пары значений строки и столбца подставляются значения, выполняется однопотоковый пересчет и результаты сохраняются в таблице данных.

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

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

Параметры управления вычислением

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

Рисунок 1. Группа «Вычисление» на вкладке «Формулы»

Параметры вычислений на вкладке

Чтобы просмотреть дополнительные параметры вычислений Excel, на вкладке Файл выберите пункт Параметры. В диалоговом окне Параметры Excel выберите вкладку Формулы.

Рисунок 2. Параметры вычислений на вкладке «Формулы» в параметрах Excel

Параметры вычислений в представлении Backstage

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

Чтобы просмотреть дополнительные параметры вычисления, на вкладке Файл выберите пункт Параметры. В диалоговом окне Параметры Excel выберите пункт Дополнительно. В разделе Формулы задайте параметры вычисления.

Рисунок 3. Дополнительные параметры вычислений

Дополнительные параметры вычислений в представлении Backstage

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

Автоматический расчет

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

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

Расчет вручную

Режим расчета вручную означает, что в Excel выполняется пересчет всех открытых книг только при нажатии пользователем клавиш F9 или CTRL+ALT+F9 или при сохранении книги. При работе с книгами, пересчет которых занимает больше доли секунды, необходимо задать вычисление в режиме расчета вручную, чтобы избежать задержки во время внесения изменений.

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

Настройки итераций

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

Свойство книги ForceFullCalculation

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

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

В строке состояния будет отображаться команда Вычислить, если для свойства книги ForceFullCalculation задано значение True.

Вы можете управлять этим параметром с помощью VBE (ALT+F11), выбрав ЭтаКнига в обозревателе проектов (CTRL+R) и отображая окно свойств (F4).

Рисунок 4. Настройка свойства Workbook.ForceFullCalculation

Настройка свойства ForceFullCalculation

Увеличение скорости вычислений в книгах

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

Скорость процессора и использование нескольких ядер

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

Для большинства больших книг повышение производительности вычислений с разных процессоров осуществляется почти линейно с числом физических процессоров. Однако технология Hyper-Threading физических процессоров обеспечивает лишь небольшое увеличение производительности.

ОЗУ

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

Как уже упоминалось, последние версии Excel могут эффективно использовать большие объемы памяти, а в 32-разрядной версии Excel 2007 и Excel 2010 можно обрабатывать одну книгу или комбинацию книг, используя до 2 ГБ памяти.

32-разрядные версии Excel 2013 и Excel 2016, использующие функцию с поддержкой больших адресов (LAA), могут использовать до 3 или 4 ГБ памяти в зависимости от установленной версии Windows. 64-разрядная версия Excel может обрабатывать книги еще большего размера. Дополнительные сведения см. в разделе, посвященном большим наборам данных, LAA и 64-разрядной версии Excel статьи Производительность Excel: улучшения производительности и ограничений.

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

Измерение времени вычисления

Чтобы расчет книг выполнялся быстрее, необходимо иметь возможность точно измерить время вычисления. Требуется таймер, более быстрый и более точный, чем функция Time в VBA. Функция MICROTIMER(), приведенная в следующем примере кода, использует API Windows для обращения к системному таймеру с высоким разрешением. Он может измерять временные интервалы, длительность которых составляет небольшое количество микросекунд. Обратите внимание, что поскольку Windows является многозадачной операционной системой и повторный расчет чего-либо может выполняться быстрее, чем в первый раз, полученные значения времени обычно не совпадают. Для получения более точного значения замеряйте время выполнения задач вычислений несколько раз и выбирайте среднее значение из полученных результатов.

Дополнительные сведения о том, как редактор Visual Basic может значительно повлиять на производительность пользовательских функций на языке VBA, см. в разделе «Более быстрые пользовательские функции VBA» статьи Производительность Excel: советы по оптимизации производительности.

#If VBA7 Then Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias _ "QueryPerformanceFrequency" (cyFrequency As Currency) As Long Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias _ "QueryPerformanceCounter" (cyTickCount As Currency) As Long #Else Private Declare Function getFrequency Lib "kernel32" Alias _ "QueryPerformanceFrequency" (cyFrequency As Currency) As Long Private Declare Function getTickCount Lib "kernel32" Alias _ "QueryPerformanceCounter" (cyTickCount As Currency) As Long #End If Function MicroTimer() As Double ' ' Returns seconds. Dim cyTicks1 As Currency Static cyFrequency As Currency ' MicroTimer = 0 ' Get frequency. If cyFrequency = 0 Then getFrequency cyFrequency ' Get ticks. getTickCount cyTicks1 ' Seconds If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency End Function 

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

Скопируйте все эти подпрограммы и функции в стандартный модуль VBA. Чтобы открыть редактор VBA, нажмите клавиши ALT+F11. В меню Вставка выберите пункт Модуль и скопируйте код в модуль.

Sub RangeTimer() DoCalcTimer 1 End Sub Sub SheetTimer() DoCalcTimer 2 End Sub Sub RecalcTimer() DoCalcTimer 3 End Sub Sub FullcalcTimer() DoCalcTimer 4 End Sub Sub DoCalcTimer(jMethod As Long) Dim dTime As Double Dim dOvhd As Double Dim oRng As Range Dim oCell As Range Dim oArrRange As Range Dim sCalcType As String Dim lCalcSave As Long Dim bIterSave As Boolean ' On Error GoTo Errhandl ' Initialize dTime = MicroTimer ' Save calculation settings. lCalcSave = Application.Calculation bIterSave = Application.Iteration If Application.Calculation <> xlCalculationManual Then Application.Calculation = xlCalculationManual End If Select Case jMethod Case 1 ' Switch off iteration. If Application.Iteration <> False Then Application.Iteration = False End if ' Max is used range. If Selection.Count > 1000 Then Set oRng = Intersect(Selection, Selection.Parent.UsedRange) Else Set oRng = Selection End If ' Include array cells outside selection. For Each oCell In oRng If oCell.HasArray Then If oArrRange Is Nothing Then Set oArrRange = oCell.CurrentArray End If If Intersect(oCell, oArrRange) Is Nothing Then Set oArrRange = oCell.CurrentArray Set oRng = Union(oRng, oArrRange) End If End If Next oCell sCalcType = "Calculate " & CStr(oRng.Count) & _ " Cell(s) in Selected Range: " Case 2 sCalcType = "Recalculate Sheet " & ActiveSheet.Name & ": " Case 3 sCalcType = "Recalculate open workbooks: " Case 4 sCalcType = "Full Calculate open workbooks: " End Select ' Get start time. dTime = MicroTimer Select Case jMethod Case 1 If Val(Application.Version) >= 12 Then oRng.CalculateRowMajorOrder Else oRng.Calculate End If Case 2 ActiveSheet.Calculate Case 3 Application.Calculate Case 4 Application.CalculateFull End Select ' Calculate duration. dTime = MicroTimer - dTime On Error GoTo 0 dTime = Round(dTime, 5) MsgBox sCalcType & " " & CStr(dTime) & " Seconds", _ vbOKOnly + vbInformation, "CalcTimer" Finish: ' Restore calculation settings. If Application.Calculation <> lCalcSave Then Application.Calculation = lCalcSave End If If Application.Iteration <> bIterSave Then Application.Iteration = bIterSave End If Exit Sub Errhandl: On Error GoTo 0 MsgBox "Unable to Calculate " & sCalcType, _ vbOKOnly + vbCritical, "CalcTimer" GoTo Finish End Sub 

Чтобы выполнить подпрограммы в Excel, нажмите клавиши ALT+F8. Выберите нужную подпрограмму и щелкните Выполнить.

Рисунок 5 Окно макросов Excel с таймерами вычислений

Окно макроса Excel

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

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

Детализированный подход для поиска помех

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

Поиск помех с помощью детализированного подхода
  1. Убедитесь, что открыта только одна книга и никакие другие задачи не выполняются.
  2. Задайте режим вычисления вручную.
  3. Сделайте резервную копию книги.
  4. Откройте книгу, которая содержит макросы таймеров вычисления, или добавьте их в книгу.
  5. Проверьте используемый диапазон, нажав клавиши CTRL+END в каждом листе по очереди. Это действие покажет ячейку, которая была использована последней. Если ячейка находится за пределами ожидаемого диапазона, подумайте об удалении лишних столбцов и строк и сохранении книги. Дополнительные сведения см. в разделе «Минимизация используемого диапазона» статьи Производительность Excel: советы по оптимизации производительности.
  6. Запустите макрос FullCalcTimer. Обычно больше всего времени занимает вычисление всех формул в книге.
  7. Запустите макрос RecalcTimer. Пересчет сразу же после полного вычисления обычно занимает меньше всего времени.
  8. Рассчитайте изменчивость книги как отношение времени пересчета к времени полного вычисления. Это измерит степень, в которой переменные формулы и расчет цепочки вычислений являются помехами.
  9. Активируйте каждый лист и по очереди запустите макрос SheetTimer. Поскольку вы только что выполнили пересчет книги, вы получите время пересчета для каждого листа. Это должно позволить вам определить, с какими из листов связана проблема.
  10. Запустите макрос RangeTimer на выбранных блоках формул.
  11. Для каждого проблемного листа поделите столбцы или строки на небольшое количество блоков.
  12. Выбирайте каждый блок по очереди и запускайте макрос RangeTimer по этому блоку.
  13. При необходимости выполните дальнейшую детализацию, подразделив каждый блок на еще меньшие блоки.
  14. Определите приоритеты для помех.

Ускорение выполнения вычислений и уменьшение количества помех

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

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

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

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

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

Обычно этот подход включает два или несколько из указанных ниже шагов:

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

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

  • Поиск, проводимый по отсортированным данным, может оказаться в десятки и сотни раз более эффективным, чем поиск по не отсортированным данным.
  • Пользовательские функции VBA обычно работают более медленно, чем встроенные функции в Excel (хотя грамотно написанные функции VBA могут работать достаточно быстро).
  • Минимизируйте количество используемых ячеек в таких функциях, как СУММ и СУММЕСЛИ. Время вычисления пропорционально числу используемых ячеек (неиспользуемые ячейки игнорируются).
  • Рассмотрите возможность замены медленных формул массивов пользовательскими функциями.
Третье правило: часто используйте интеллектуальный пересчет и многопоточные вычисления

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

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

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

    1. Рассчитайте время для формулы, которую нужно изменить, используя макрос RangeTimer.
    2. Внесите изменение.
    3. Рассчитайте время для измененной формулы, используя макрос RangeTimer.
    4. Убедитесь, что измененная формула дает правильный ответ.

    Примеры использования правил

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

    Суммы с начала периода

    Например, требуется рассчитать суммы с начала периода для столбца, содержащего 2 000 чисел. Предположим, что в столбце A содержатся числа, а в столбцах B и C должны располагаться суммарные значения с начала периода.

    Соответствующую формулу можно записать с помощью эффективной функции SUM (СУММ).

     B1=SUM($A$1:$A1) B2=SUM($A$1:$A2) 

    Рисунок 6. Пример формул СУММ за период

    Пример формулы СУММ за период

    Скопируйте формулу вниз до ячейки B2000.

    Каково теперь общее число ссылок на ячейки, складываемые функцией СУММ? Ячейка B1 ссылается на одну ячейку, а ячейка B2000 — на 2 000 ячеек. Среднее значение составляет 1 000 ссылок на ячейку, что в сумме дает общее число ссылок, равное 2 миллионам. Выбор 2 000 формул и использование макроса RangeTimer показывает, что 2 000 формул в столбце B вычисляются за 80 миллисекунд. Большая часть этих вычислений дублируется много раз: СУММ добавляет A1 к A2 в каждой формуле из диапазона B2:B2000.

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

     C1=A1 C2=C1+A1 

    Скопируйте эту формулу вниз до ячейки C2000.

    Каково теперь общее число ссылок на складываемые ячейки? В каждой формуле, за исключением первой формулы, используется две ссылки на ячейки. Таким образом, общее их число равняется 1999*2+1=3999. Это в 500 раз меньше ссылок на ячейки.

    RangeTimer показывает, что 2 000 формул в столбце C вычисляются за 3,7 миллисекунды (напомним, что формулы в столбце B вычисляются за 80 миллисекунд). Этому изменению соответствует коэффициент улучшения, равный лишь 80/3,7=22, а не 500, поскольку имеются лишь небольшие дополнительные временные затраты в расчете на одну формулу.

    Обработка ошибок

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

    • Его можно записать с помощью одной формулы, вычисления по которой осуществляются медленно: B1=IF(ISERROR(time expensive formula),0,time expensive formula)
    • Его можно записать с помощью двух формул, вычисления по которым осуществляются быстро: A1=time expensive formula B1=IF(ISERROR(A1),0,A1)
    • Можно также использовать функцию IFERROR (ЕСЛИОШИБКА), которая разрабатывалась как простая и быстрая, включающая одну формулу: B1=IFERROR(time expensive formula,0)
    Динамический подсчет уникальных элементов

    Рисунок 7. Пример списка данных для подсчета уникальных значений

    Пример подсчета уникальных данных

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

      Формулы массива (используйте клавиши CTRL+SHIFT+ВВОД); RangeTimer показывает, что это занимает 13,8 секунды.

    <=SUM(IF(LEN(A2:A11000)>0,1/COUNTIF(A2:A11000,A2:A11000)))> 
    =SUMPRODUCT((A2:A11000<>"")/COUNTIF(A2:A11000,A2:A11000&"")) 
    Public Function COUNTU(theRange As Range) As Variant Dim colUniques As New Collection Dim vArr As Variant Dim vCell As Variant Dim vLcell As Variant Dim oRng As Range Set oRng = Intersect(theRange, theRange.Parent.UsedRange) vArr = oRng On Error Resume Next For Each vCell In vArr If vCell <> vLcell Then If Len(CStr(vCell)) > 0 Then colUniques.Add vCell, CStr(vCell) End If End If vLcell = vCell Next vCell COUNTU = colUniques.Count End Function 
     =IF(AND(A2<>"",A2<>A1),1,0) 

    Скопируйте формулу, а затем добавьте формулу сложения значений в столбце B.

     =SUM(B2:B11000) 

    Заключение

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

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

    См. также

    • Производительность Excel: улучшения производительности и ограничений
    • Производительность Excel: советы по оптимизации производительности
    • Портал разработчиков Excel

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

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

    Обратная связь

    Были ли сведения на этой странице полезными?

    7 полезных формул для тех, кто считает деньги в эксель-таблице

    Аватар автора

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

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

    Как упростить жизнь с помощью «Экселя»
    Курс, о том, как таблицы помогут планировать бюджет, считать расходы и упорядочить бытовые дела

    Баннер

    Начать учиться

    Соединить текст из разных ячеек

    Иногда надо быстро собрать данные из разных ячеек в одной. Поочередно копировать долго и неудобно, поэтому лучше использовать формулу с амперсандом — знаком «&».

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

    Соединение текста экономит скорее время, чем деньги, но при правильном подходе это легко конвертировать

    Соединение текста экономит скорее время, чем деньги, но при правильном подходе это легко конвертировать

    Подобрать значения для нужного результата

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

    Для этого на вкладке «Данные» надо выбрать «Анализ „Что если“», с помощью функции «Подбор параметра» задать целевое значение и выбрать ячейку, которую нужно изменить для получения желаемой цифры.

    Обложка статьи

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

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

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

    Обновить курс валют

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

    Чтобы использовать эту функцию, на вкладке «Данные» выберите кнопку «Из интернета» и вставьте адрес надежного источника, например cbr.ru. Эксель предложит выбрать, какую именно таблицу нужно загрузить с сайта — отметьте нужную галочкой.

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

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

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

    Планировать действия

    Теперь, когда курсы валют и стоимость акций подгружаются автоматически, эксель становится динамичным рабочим инструментом. Но можно пойти дальше и требовать от него реакций — и даже советов! — при определенных изменениях. Для этого понадобится функция «Если». Она заполняет ячейки заданными значениями в зависимости от того, что происходит в остальной таблице.

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

    Обложка статьи

    Вот так: =ЕСЛИ (ячейка с ценой акции ≥ цена выгодной продажи; «продавать»; ЕСЛИ (ячейка с ценой акции ≤ цена выгодной покупки; «покупать»; «ничего»)).

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

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

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

    Выделить цветом нужные данные

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

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

    Также можно выделить значения, которые находятся в определенном интервале (в условиях форматирования — «между»), содержат нужный текст («текст содержит»), или задать сразу несколько условий

    Также можно выделить значения, которые находятся в определенном интервале (в условиях форматирования — «между»), содержат нужный текст («текст содержит»), или задать сразу несколько условий

    Суммировать только нужное

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

    Мы попробуем узнать, сколько Аня тратит на еду в офисе. Для этого в таблице создаем формулу =СУММ((А2:А16=F2)*(B2:B16=F3)*C2:С16) и получаем 915 рублей. Теперь постепенно.

    В первой скобке программа ищет значение из ячейки F2 («Аня») в столбце с именами. Во второй скобке — значение из ячейки F3 («Еда на работе») из столбца с категориями расходов. А после считает сумму ячеек из третьего столбца, которые выполнили эти условия.

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

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

    Расставить по порядку

    В экселе можно быстро узнать максимальное, минимальное и среднее значение для любого массива ячеек. Для этого в скобках формул =МАКС(), =МИН() и =СРЗНАЧ() нужно указать диапазон ячеек, в которых будет искать программа. Это пригодится для таблицы, в которую вы записываете все расходы: вы увидите, на что потратили больше денег, а на что — меньше. Еще этим тратам можно присвоить «места» — и отдать почетное первое место максимальной или минимальной сумме.

    Например, вы считаете зарплаты сотрудников и хотите узнать, кто заработал больше за определенный срок. Для этого в скобках формулы =РАНГ() через точку с запятой укажите ячейку, порядок которой хотите узнать; все ячейки с числами; 1, если нужен номер по возрастанию, или 0, если нужен номер по убыванию.

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

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

    Уверены, теперь вы сможете прокачать наши таблицы до максимального уровня:

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

    Самый быстрый ВПР

    Если в ваших таблицах всего лишь несколько десятков строк, то, скорее всего, эта статья не будет для вас актуальной. На таких небольших объемах данных любой способ будет работать достаточно шустро, чтобы вы этого не замечали. Если же число строк в ваших списках измеряется тысячами, да и самих таблиц не одна-две, то время мучительного ожидания на пересчете формул в Excel может доходить до нескольких минут. В этом случае, правильный выбор функции, применяемой для связывания таблиц, играет решающую роль — разница в производительности между ними, как мы увидим далее, может составлять более 20 раз!
    Когда я писал свою первую книжку пять лет назад, то уже делал сравнительный скоростной тест различных способов поиска и подстановки данных функциями ВПР, ИНДЕКС+ПОИСКПОЗ, СУММЕСЛИ и др. С тех пор сменилось три версии Office, появились надстройки Power Query и Power Pivot, кардинально изменившие весь процесс работы с данными. А в прошлом году ещё и обновился вычислительный движок Excel, получив поддержку динамических массивов и новые функции ПРОСМОТРХ, ФИЛЬТР и т.п.
    Так что пришла пора снова взяться за секундомер и выяснить — кто же самый быстрый. Ну и, заодно, проверить — какие способы поиска и подстановки данных в Excel вы знаете 🙂

    Подопытный кролик

    Исходный пример

    Тест будем проводить на следующем примере:
    Это книга Excel с одним листом, где расположены две таблицы: отгрузки (500 000 строк) и прайс-лист (600 строк). Наша задача — подставить цены из прайс-листа в таблицу отгрузок. Для каждого способа будем вводить формулу в ячейку С2 и копировать вниз на весь столбец, замеряя время, которое потребуется Excel, чтобы просчитать весь столбец из полумиллиона ячеек. Полученные значения, безусловно, зависят от множества факторов (поколение процессора, объем оперативной памяти, текущая загрузка системы, версия Office и т.д.), но нам важны не конкретные цифры, а, скорее, их сравнение друг с другом. Важно понимать прожорливость каждого способа и их ограничения.

    Способ 1. ВПР

    ВПР

    Сначала — классика 🙂 Легендарная функция вертикального просмотра — ВПР (VLOOKUP) , которая приходит в голову первой в подобных ситуациях:
    Здесь участвуют следующие аргументы:

    • B2 — искомое значение, т.е. название товара, который мы хотим найти в прайс-листе
    • $G$2:$H$600 — закреплённая знаками доллара (чтобы не сползала при копировании формулы вниз) абсолютная ссылка на прайс
    • 2 — номер столбца в прайс-листе, откуда мы хотим взять цену
    • 0 или ЛОЖЬ — переключение в режим поиска точного соответствия, когда любое некорректное название товара (например, ФОНЕРА) в столбце B в таблице отгрузок приведёт к появлению ошибки #Н/Д как результата работы функции.

    Время вычисления = 4,3 сек.

    Способ 2. ВПР с выделением столбцов целиком

    Многие пользователи, применяя ВПР, во втором аргументе этой функции, где нужно задать поисковую таблицу (прайс), выделяют не ограниченный диапазон ( $G$2:$H$600 ), а сразу столбцы G:H целиком. Это проще, быстрее, позволяет не думать про F4 и то, что завтра прайс-лист может быть на несколько строк больше. Формула в этом случае выглядит тоже компактнее:

    ВПР с выделением столбцов целиком

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

    Время вычисления = 14,5 сек.

    Способ 3. ИНДЕКС и ПОИСКПОЗ

    Следующей после ВПР ступенью эволюции для многих пользователей Microsoft Excel обычно является переход на использование связки функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) . Выглядит эта формула так:

    ИНДЕКС и ПОИСКПОЗ

    Функция ИНДЕКС извлекает из заданного в первом аргументе диапазона (столбца $H$2:$H$600 с ценами в прайс-листе) содержимое ячейки с заданным номером. А номер этот, в свою очередь, определяется функцией ПОИСКПОЗ, у которой три аргумента:

    • Что нужно найти — название товара из B2
    • Где мы это ищем — столбец с названиями товаров в прайсе ( $G$2:$G$600 )
    • Режим поиска: 0 — точный, 1 или -1 — приблизительный с округлением в меньшую или большую сторону, соответственно.

    Формула выходит чуть сложнее, но, при этом имеет несколько ощутимых преимуществ перед классической ВПР, а именно:

    • Не нужно отсчитывать номер столбца (как в третьем аргументе ВПР).
    • Можно извлекать данные, которые находятся левее столбца, где просходит поиск.

    По скорости, однако же, этот способ проигрывает ВПР почти в два раза:

    Время вычисления = 7,8 сек.

    Если же, вдобавок, полениться и выделять не ограниченные диапазоны, а столбцы целиком:

    ИНДЕКС и ПОИСКПОЗ с выделением столбцов целиком

    . то результат получается совсем печальный:

    Время вычисления = 28,5 сек.

    28 секунд, Карл! В 6 раз медленнее ВПР!

    Способ 4. СУММЕСЛИ

    Если нужно найти не текстовые, а именно числовые данные (как в нашем случае — цену), то вместо ВПР вполне можно использовать функцию СУММЕСЛИ (SUMIF) . Изначально она задумывалась как инструмент для выборочного суммирования данных по условию (найди и сложи мне все продажи кабелей, например), но можно заставить её искать нужный нам товар и в прайс-листе. Если грузы в нём не повторяются, то суммировать будет не с чем и эта функция просто выведет искомое значение:

    СУММЕСЛИ

    • Первый аргумент СУММЕСЛИ — это диапазон проверяемых ячеек, т.е. названия товаров в прайсе ( $G$2:$G$600 ).
    • Второй аргумент ( B2 ) — что мы ищем.
    • Третий аргумент — диапазон ячеек с ценами $H$2:$H$600 , числа из которых мы хотим просуммировать, если в соседних ячейках проверяемого диапазона есть искомое значение.

    Очевидным минусом такого подхода является то, что он работает только с числами. Также этот способ не удобен, если прайс-лист находится в отдельном файле — придется всё время держать его открытым, т.к. функция СУММЕСЛИ не умеет брать данные из закрытых книг, в отличие от ВПР, для которой это не проблема.

    В плюсы же можно записать удобство при поиске сразу по нескольким столбцам — для этого идеально подходит более продвинутая версия этой функции — СУММЕСЛИМН (SUMIFS) . Скорость вычислений же, при этом, весьма посредственная:

    Время вычисления = 12,8 сек.

    При выделении столбцов целиком, т.е. использовании формулы вида =СУММЕСЛИ( G:G ; B2 ; H:H ) всё ещё хуже:

    Время вычисления = 41,7 сек.

    Это самый плохой результат в нашем тесте.

    Способ 5. СУММПРОИЗВ

    Этот подход сейчас встречается не часто, но всё ещё достаточно регулярно. Обычно так любят извращаться пользователи старой школы, ещё хорошо помнящие те времена, когда в Excel было всего 255 столбцов и 56 цветов 🙂

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

    СУММПРОИЗВ

    Выражение ($G$2:$G$600=B2) , по сути, проверяет каждое название груза в прайс-листе на предмет соответствия искомому значению (ФАНЕРА ПР). Результатом каждого сравнения будет логическое значение ИСТИНА (TRUE ) или ЛОЖЬ (FALSE) , что в Excel интерпретируется как 1 и 0, соответственно. Последующее умножение этих нулей и единиц на цены оставит в живых цену только того товара, который нам, в данном случае, и нужен.

    Эта формула является, по сути, формулой массива, но не требует нажатия обычного для них сочетания клавиш Ctrl + Shift + Enter , т.к. функция СУММПРОИЗВ поддерживает массивы уже сама по себе. Возможно, по этой же причине (формулы массива всегда медленнее, чем обычные) такой скорость пересчёта такой формулы — не очень:

    Время вычисления = 11,8 сек.

    К плюсам же такого подхода можно отнести:

    • Совместимость с любыми, самыми древними версиями Excel.
    • Возможность задавать сложные условия (и несколько)
    • Способность этой формулы работать с данными из закрытых файлов, если добавить перед ней двойное бинарное отрицание (два подряд знака «минус»). СУММЕСЛИМН таким похвастаться не может.

    Способ 6. ПРОСМОТР

    Ещё один относительно экзотический способ поиска и подстановки данных, наравне с ВПР — это использование функции ПРОСМОТР (LOOKUP) . Только не перепутайте её с новой, буквально, на днях появившейся функцией ПРОСМОТРХ (XLOOKUP) — про неё мы поговорим дальше особо. Функция ПРОСМОТР существовала в Excel начиная с самых ранних версий и тоже вполне может решить нашу задачу:

    ПРОСМОТР

    • B2 — название груза, которое мы ищем
    • $G$2:$G$600 — одномерный диапазон-вектор (столбец или строка), где мы ищем совпадение
    • $H$2:$H$600 — такого же размера диапазон, откуда нужно вернуть найденный результат (цену)

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

    Ошибка с ПРОСМОТР

    • Эта функция требует обязательной сортировки прайс-листа по возрастанию (алфавиту) и без этого не работает.
    • Если в таблице отгрузок искомое значение будет написано с опечаткой (например, АГ Е ДОЛ вместо АГИДОЛ), то функция ПРОСМОТР выдаст не ошибку #Н/Д, а цену для ближайшего предыдущего товара:

    При работе с неидеальными данными в реальном мире это гарантированно создаст проблемы, как вы понимаете.

    Скорость же вычислений у функции ПРОСМОТР (LOOKUP) весьма приличная:

    Время вычисления = 7,6 сек.

    Способ 7. Новая функция ПРОСМОТРХ

    Эта функция пришла с одним из недавних обновлений пока только пользователям Office 365 и пока отсутствует во всех остальных версиях (Excel 2010, 2013, 2016, 2019). По сравнению с классической ВПР у этой функции есть масса преимуществ (упрощенный синтаксис, возможность искать не только сверху-вниз, возможность сразу задать значение вместо #Н/Д и т.д.) Формула для решения нашей задачи будет выглядеть в этом случае так:

    ПРОСМОТРХ

    Если не брать в расчёт необязательные 4,5,6 аргументы, то синтаксис этой функции полностью совпадает с её предшественником — функцией ПРОСМОТР (LOOKUP) . Скорость вычислений при тестировании на наши 500000 строк тоже оказалась аналогичной:

    Время вычисления = 7,6 сек.

    Почти в два раза медленнее, чем у ВПР, вместо которой Microsoft предлагает теперь использовать ПРОСМОТРХ. Жаль.

    И, опять же, если полениться и выделить диапазоны в прайс-листе целыми столбцами:

    ПРОСМОТРХ и выделение столбцов целиком

    . то скорость падает до совершенно неприличных уже значений:

    Время вычисления = 28,3 сек.

    А если на динамических массивах?

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

    ВПР на динамических массивах

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

    Скорость пересчета в таком варианте меня, откровенно говоря, ошеломила — пауза между нажатием на Enter после ввода формулы и получением результатов почти отсутствовала.

    Время вычисления = 1 сек.

    Что интересно, и новая ПРОСМОТРХ, и старая ПРОСМОТР, и связка ИНДЕКС+ПОИСКПОЗ в таком режиме тоже были очень быстрыми — время вычислений не больше 1 секунды! Фантастика.

    А вот олдскульные подходы на основе СУММПРОИЗВ и СУММЕСЛИ(МН) с динамическими массивами работать отказались 🙁

    Что с умными таблицами?

    Обрадовавшись фантастическим результатам, полученным на динамических массивах, я решил вдогон попробовать протестировать разницу в скорости при работе с обычными и «умными» таблицами. Я имею ввиду те самые «красивые таблицы», в которые вы можете преобразовать ваш диапазон с помощью команды Форматировать как таблицу на вкладке Главная (Home — Format as Table) или с помощью сочетания клавиш Ctrl + T .

    Если предварительно превратить наши отгрузки и прайс в «умные» (по умолчанию они получат имена Таблица1 и Таблица2, соответственно), то формула с той же ВПР будет выглядеть как:

    ВПР на умных таблицах

    • [@Груз] — ссылка на ячейку B2, означающая, в данном случае, что нужно взять значение из той же строки из столбца Груз текущей умной таблицы.
    • Таблица2 — ссылка на прайс-лист

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

    Скорость же, как выяснилось, тоже вырастает очень значительно и примерно равна скорости работы на динамических массивах:

    Время вычисления = 1 сек.

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

    Бонус. Запрос Power Query

    Замерять, так замерять! Давайте, для полноты картины, сравним наши перечисленные способы еще и с запросом Power Query, который тоже может решить нашу задачу. Кто-то скажет, что некорректно сравнивать пересчёт формул с механизмом обновления запроса, но мне, откровенно говоря, просто самому было интересно — кто быстрее?

    1. Превращаем обе наши таблицы в «умные» с помощью команды Форматировать как таблицу на вкладке Главная (Home — Format as Table) или с помощью сочетания клавиш Ctrl + T .
    2. По очереди загружаем таблицы в Power Query с помощью команды Данные — Из таблицы / диапазона (Data — From Table/Range) .
    3. После загрузки в Power Query возвращаемся обратно в Excel, оставляя загруженные данные как подключение. Для этого в окне Power Query выбираем Главная — Закрыть и загрузить — Закрыть и загрузить в. — Только создать подключение (Home — Close&Load — Close&Load to. — Only create connection) .
    4. После того, как обе исходные таблицы будут загружены как подключения, создадим ещё один, третий запрос, который будет объединять их между собой, подставляя цены из прайса в отгрузки. Для этого на вкладке Данные выберем Получить данные / Создать запрос — Объединить запросы — Объединить (Get Data / New Query — Merge queries — Merge) :

    Объединяем запросы

    Настройки объединения

    Разворачиваем вложенные таблицы после объединения

    В отличие от формул, запросы Power Query не обновляются автоматически «на лету», а требуют щелчка правой кнопкой мыши по таблице (или запросу в правой панели) и выбору команды Обновить (Refresh) . Также можно воспользоваться командой Обновить все (Refresh All) на вкладке Данные (Data) .

    Время обновления = 8,2 сек.

    Итоговая таблица и выводы

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

    Итоговая таблица результатов

    Само-собой, у каждого из нас свои предпочтения, задачи и тараканы, но для себя я сформулировал выводы после этого тестирования так:

    • ВПР всё ещё главная рабочая лошадка. После прошлогодних обновлений, ускоряющих ВПР, и осенних обновлений вычислительного движка, эта функция заиграла новыми красками и даёт жару по-полной.
    • Не нужно лениться и выделять столбцы целиком — для всех способов без исключения это ухудшает результаты почти в 3 раза.
    • Экзотические способы из прошлого типа СУММПРОИЗВ и СУММЕСЛИ — в топку. Они работают очень медленно и, вдобавок, не поддерживают динамические массивы.
    • Динамические массивы и умные таблицы — это будущее.

    Ссылки по теме

    • Как использовать функцию ВПР для подстановки значений в Excel
    • Функция ПРОСМОТРХ как наследник ВПР
    • 5 вариантов использованияфункцииИНДЕКС

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

    Следуйте этим советам по оптимизации многих часто возникающих препятствий производительности в 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 добавит < и >, чтобы показать, что это формула массива).

     ,FALSE)> 

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

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

    Это выражение возвращает столбцы с 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)) 
     INDEX(INDIRECT("Sheet" & TableLookup_Value & "!$B$2:$Z$1000"), _ MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1)) 
     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) 

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

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

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

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

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

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

      =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 "", "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 .

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

    1. Подсчитайте число строк для каждого подблока.
    2. Чтобы определить начальную строку каждого блока, суммируйте число строк накопительно.
    3. Используйте функцию 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, таких как ссылки, подстановки, формулы, функции и код VBA, чтобы избежать распространенных препятствий и повысить производительность.

      См. также

      • Производительность Excel: повышение производительности вычислений
      • Производительность Excel: улучшения производительности и ограничений
      • Портал разработчиков Excel

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

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

      Обратная связь

      Были ли сведения на этой странице полезными?

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *