Производительность Excel: улучшения производительности и ограничений
Область применения: Excel | Excel M365| Excel 2016 | Excel 2013 | Excel 2010 | Office 2016 | SharePoint Server 2010 | VBA
В Excel M365 представлены новые функции, которые можно использовать для улучшения производительности при работе с большими или сложными книгами Excel.
Улучшение функций СУММЕСЛИМН, СРЗНАЧЕСЛИМН, СЧЁТЕСЛИМН, МАКСЕСЛИМН и МИНЕСЛИМН
В Office 365 версии 2005 канала Monthly Channel или в более поздних версиях функции СУММЕСЛИМН, СРЗНАЧЕСЛИМН, СЧЁТЕСЛИМН, МАКСЕСЛИМН и МИНЕСЛИМН, а также их отдельные элементы СУММЕСЛИ, СРЗНАЧЕСЛИ и СЧЁТЕСЛИ намного быстрее агрегируют строковые данные в электронной таблице, чем в Excel 2010. Теперь эти функции создают внутренний кэшированный индекс для диапазона, в котором выполняется поиск для каждого выражения. Этот кэшированный индекс используется повторно во всех последующих агрегатах, извлекающих данные из того же диапазона.
Эффект просто поразителен: например, расчет 1200 формул СУММЕСЛИМН, СРЗНАЧЕСЛИМН и СЧЁТЕСЛИМН, агрегирующих данные из 1 миллиона ячеек, на 4-ядерном ЦП с тактовой частотой 2 ГГц, который занимал 20 секунд в Excel 2010, теперь занимает только 8 секунд в Excel M365 2006.
Функция RealTimeData (ДРВ)
В ежемесячном канале Excel M365 версии 2002 или более поздней функция Excel RealTimeData (RTD) выполняется гораздо быстрее, чем excel 2010, вычисляющая данные в электронной таблице. Мы удалили узкие места в базовой памяти и структурах данных, а также обеспечили потокобезопасность, чтобы разрешить вычисление во всех доступных потоках многопоточного пересчета (MTR).
Например, моделирование 125000 обновлений ДРВ для тем, связанных с акциями, таких как "Последняя цена", "Заявка", "Предложение", для вычисления значений, таких как "Торговый объем", "Рыночная стоимость", "Торговые прибыли/убытки" и т. д., в 5000000 ячейках заняло 47 секунд при использовании Excel 2010 и лишь 7 секунд при использовании Excel M365 версии 2002 на том же оборудовании.
Другой положительный эффект превращения функции ДРВ в потокобезопасную заключается в том, что больше не требуется приостанавливать многопоточный перерасчет (MTR), чтобы запустить функцию ДРВ. Это заметно повышает производительность при выполнении функции ДРВ с большим количеством других вычислений.
Например, мы запускали книгу с 10 000 функций ДРВ и 10 000 функций ВПР, при этом каждая функция ВПР зависела от результата функции ДРВ. Без потоковобезопасного полного пересчета RTD потребовалось 10,20 секунды, а с потокобезопасной RTD — 5,84 секунды.
Улучшения функций ВПР, ГПР и ПОИСКПОЗ
В Office 365 версии 1809 и более поздних функции Excel ВПР, ГПР и ПОИСКПОЗ для поиска точного совпадения в несортированных данных выполняются как никогда быстро при поиске в нескольких столбцах (или строках с помощью ГПР) из одного диапазона таблицы.
Теперь эти функции поиска создают внутренний кэшированный индекс для диапазона столбцов, в котором выполняется поиск. Этот кэшированный индекс повторно используется во всех последующих подстановках, которые извлекаются из той же строки (ВПР и ПОИСКПОЗ) или столбца (ГПР). Эффект просто поразителен: подстановка по 5 разным столбцам в одном диапазоне таблицы может выполняться до 4 раз быстрее, чем та же подстановка с помощью Excel 2010 или Excel 2016, а улучшение еще заметнее, если просматривается еще больше столбцов.
Например, вычисление 100 строк для этих 5 формул ВПР (VLOOKUP) заняло 37 секунд на вычисление с помощью Excel 2010 и только 12 секунд с помощью Excel 2016.
=VLOOKUP($A900000,$A$2:$E$1000000,1,FALSE)
=VLOOKUP($A900000,$A$2:$E$1000000,2,FALSE)
=VLOOKUP($A900000,$A$2:$E$1000000,3,FALSE)
=VLOOKUP($A900000,$A$2:$E$1000000,4,FALSE)
=VLOOKUP($A900000,$A$2:$E$1000000,5,FALSE)
Улучшение памяти LAA для 32-разрядной версии Excel
Хотя в 64-разрядной версии Excel применяются большие значения пределов виртуальной памяти, в 32-разрядной версии применяется только 2 ГБ виртуальной памяти. Некоторые клиенты используют 32-разрядную версию, так как некоторые сторонние надстройки и элементы управления недоступны в 64-разрядной версии.
32-разрядные версии Excel 2013 и Excel 2016 теперь поддерживают обработку больших адресов (LAA). Это сводит к минимуму появление сообщений об ошибках нехватки памяти.
LAA удваивает доступную виртуальную память с 2 до 4 ГБ в 64-разрядных версиях Windows и увеличивает объем доступной виртуальной памяти с 2 до 3 ГБ в 32-разрядных версиях Windows.
Дополнительные сведения см. в статье Изменение функции обработки больших адресов для Excel.
Чтобы скачать средство, демонстрирующее объем доступной виртуальной памяти и степень ее использования, перейдите на страницу средства проверки памяти Excel
Ссылки на полные столбцы
В более ранних версиях Excel книги, использующие большое количество ссылок на полные столбцы и несколько листов (например, =COUNTIF(Sheet2!A:A,Sheet3!A1)), могут использовать большой объем памяти и ЦП при открытии или удалении строк.
Excel 2016 сборки 16.0.8212.1000 сокращает объем памяти и ЦП, используемый в таких обстоятельствах.
В примере тестирования для книги с 6 миллионами формул применение ссылок на полные столбцы завершилось ошибкой с сообщением о недостатке памяти при использовании 4 ГБ виртуальной памяти в Excel 2013 LAA и Excel 2010, но в Excel 2016 использовалось только 2 ГБ виртуальной памяти.
Структурированные ссылки
В Excel 2013 и более ранних версиях редактирование таблиц в книге с формулами, использующими структурированные ссылки на таблицу, выполнялось медленно. Из-за этого складывалось впечатление о нежелательности использования таблиц с большим числом строк. Эта проблема больше не возникает в Excel 2016.
Например, операция редактирования, занимавшая 1,9 секунды в Excel 2013 и Excel 2010, заняла около 2 миллисекунд в Excel 2016.
Фильтрация, сортировка, копирование и вставка
Внесен ряд улучшений для уменьшения времени отклика при фильтрации, сортировке, копировании и вставке в больших книгах.
После фильтрации, сортировки или копирования и вставки большого числа строк приложение Excel 2013 могло медленно реагировать или зависать. Производительность зависела от количества всех строк между верхней видимой строкой и нижней видимой строкой. Эти операции выполняются значительно быстрее после улучшения внутреннего вычисления вертикальных положений пользовательского интерфейса в сборке 16.0.8431.2058.
Открытие книги с большим количеством отфильтрованных или скрытых строк, объединенных ячеек или структур могло приводить к высокой нагрузке ЦП. В сборке 16.0.8229.1000 добавлено исправление этой проблемы.
После вставки скопированного столбца ячеек из таблицы с отфильтрованными строками, в которых фильтр приводил к созданию большого числа отдельных блоков строк, отклик был очень медленным. Это было улучшено в сборке 16.0.8327.1000.
Пример тестирования при копировании и вставке 22 000 строк, отфильтрованных из 44 000 строк, показал значительное улучшение:
- Для таблицы время изменилось с 39 секунд в Excel 2013 и 18 секунд в Excel 2010 до 2 секунд в Excel 2016.
- Для диапазона время изменилось с 30 секунд в Excel 2013 и 13 секунд в Excel 2010 до мгновенного выполнения в Excel 2016.
Копирование условного форматирования
В Excel 2013 копирование и вставка ячеек, содержащих условное форматирование, могло выполняться медленно. Это было значительно улучшено в Excel 2016 сборки 16.0.8229.0.
Пример тестирования копирования 44 000 ячеек с общим количеством правил условного форматирования 386 000 показал значительное улучшение:
- Excel 2010: 70 секунд
- Excel 2013: 68 секунд
- Excel 2016: 7 секунд
Добавление и удаление листов
При добавлении и удалении большого числа листов пример тестирования в Excel 2016 сборки 16.0.8431.2058 показывает 15–20 % повышение скорости по сравнению с Excel 2013, но 5–10 % снижение по сравнению с Excel 2010.
Новые функции
В Excel 2016 сборки 16.0.7920.1000 представлено несколько полезных функций листа:
- Функции МАКСЕСЛИ и МИНЕСЛИ расширяют семейство функций СЧЁТЕСЛИМН и СУММЕСЛИМН. У этих функций хорошие характеристики производительности. Используйте их для замены эквивалентных формул массива.
- TEXTJOIN и CONCAT позволяют легко объединять текстовые строки из диапазонов ячеек. Используйте их для замены эквивалентных пользовательских функций VBA.
Другие обновления в Excel 2016 для Windows
Дополнительные сведения об улучшениях по месяцам, внесенных в Excel 2016, см. в статье Новые возможности Excel 2016 для Windows.
Улучшения производительности в Excel 2010
На основе отзывов и предложений по Excel 2007 в Excel 2010 были реализованы улучшения некоторых возможностей.
| Функция | Улучшение |
|---|---|
| Представление принтера и разметки страницы |
Для повышения производительности базовых пользовательских операций в режиме разметки страницы, таких как ввод данных, работа с формулами и настройка полей, в Excel 2010 реализовано кэширование параметров принтера и оптимизирован процесс вычисления параметров отображения. Благодаря кэшированию параметров принтера удается сократить число сетевых вызовов и степень зависимости от медленных или неотвечающих принтеров. Кроме того, поддерживается отмена подключения к принтеру, что позволяет не дожидаться реакции от медленного или неотвечающего принтера. |
| Диаграммы |
Начиная с версии Excel 2010, была увеличена скорость отображения диаграмм, особенно при работе с крупными наборами данных, и текста. Кроме того, в Excel 2010 реализовано кэширование изображения диаграммы и по-возможности используется кэшированная версия, что позволяет избежать ненужных вычислений. |
| Решения VBA |
Улучшения объектной модели и способов взаимодействия с Excel позволили повысить производительность многих решений VBA при работе в Excel 2010 по сравнению с Excel 2007. |
Большие наборы данных и 64-разрядная версия Excel
64-разрядная версия Excel 2010 не ограничена 2 ГБ ОЗУ, как в приложениях 32-разрядной версии, или до 4 ГБ ОЗУ, как в 32-разрядной версии приложений с поддержкой больших адресов. Таким образом, пользователи 64-разрядной версии Excel 2010 могут создавать книги большего размера. 64-разрядная версия Windows поддерживает больший объем адресуемой памяти, что в полной мере используется приложением Excel. Например, пользователи могут заполнять сетку большим объемом данных по сравнению с предыдущими версиями Excel. При увеличении объемов ОЗУ компьютера приложение Excel может использовать эту дополнительную память для работы со значительно более крупными книгами и масштабирования доступного объема ОЗУ.
Кроме того, поскольку 64-разрядная версия Excel поддерживает более крупные наборы данных, в 32-и 64-разрядных версиях Excel 2010 представлены улучшения при выполнении задач с крупными наборами данных, таких как ввод и заполнение данных, сортировка, фильтрация, копирование и вставка данных. Также в 32- и 64-разрядных версиях Excel оптимизировано использование памяти.
Дополнительные сведения о 64-разрядной версии Office 2010 см. в статье Совместимость 32- и 64-разрядных версий Office 2010, а для выбора между 64- и 32-разрядной версиями см. статью Выбор 64- или 32-разрядной версии Office.
Формы
В Excel 2010 представлены значительные улучшения в области производительности графики в Excel. На более высоком уровне эти улучшения относятся к двум областям: масштабируемость и отображение.
Улучшения масштабируемости играют важную роль, поскольку в электронных таблицах Excel содержится большое число графических элементов. Зачастую возможно создание большого числа фигур при копировании и вставке данных с веб-сайта, а также при выполнении автоматических процедур, в ходе которых фигуры создаются, но не удаляются. Из-за большого числа графических элементов и их связей с таблицей данных в Excel возникают уникальные проблемы, связанные с производительностью. В Excel 2010 увеличена производительность электронных таблиц, в которых содержится много фигур.
Кроме того, начиная с версии Excel 2010 улучшена производительность функций отображения за счет поддержки аппаратного ускорения. Также в Excel 2010 представлены улучшения производительности метода Select объекта Shape в объектной модели VBA.
| Функция | Улучшение |
|---|---|
| Общие задачи |
Первая часть улучшений в Excel 2010 относится к сценариям общих задач. К этим сценариям относятся операции и функции сортировки, фильтрации, вставки и изменения размера строк или столбцов, а также объединения ячеек. При выполнении таких операций может потребоваться обновление позиции графического объекта в сетке. В худшем случае может потребоваться обновление всех объектов в электронной таблице. В Excel 2010 улучшена производительность общих задач даже в сценариях с тысячами объектов в таблице. Следует обратить внимание, что эти улучшения достигнуты не за счет усовершенствования или исправления одной функции, а благодаря комплексной структурной переработке, в том числе изменению механизма поиска фигур, применению файлов для тестирования нагрузки и исследованию ограничений. |
| Текстовые ссылки |
Текстовая ссылка на фигуру создается, когда пользователь указывает формулу, например, "=A1", которая задает текст для фигуры. Использование таких фигур с большой долей вероятности могло повлечь за собой проблемы с производительностью на листах с большим числом объектов, а также при изменении контента ячеек. Начиная с версии Excel 2010, оптимизирован способ отслеживания и обновления таких фигур при изменении контента ячеек. Это позволило повысить производительность в таких сценариях, как ввод нового значения в ячейку или выполнение сложных операций объектной модели. |
| Большая сетка |
Начиная с версии Excel 2007, размер сетки увеличен с 65 000 до более чем одного миллиона строк. Это увеличение повлекло за собой ряд проблем с производительностью и отображением при работе с графическими объектами в новых областях расширенной сетки. В Excel 2010 были оптимизированы функции, в которых в качестве источника используются данные левой верхней части сетки, для повышения производительности при работе с графическими элементами в новых областях сетки. По сравнению с Excel 2007 удалось повысить качество и производительность функций отображения. |
| Отображение: аппаратное ускорение |
Начиная с версии Excel 2010, была усовершенствована графическая платформа, которая теперь поддерживает аппаратное ускорение при отображении трехмерных объектов. Несмотря на то, что по сравнению с ЦП графический процессор обладает большей производительностью при отображении таких объектов, общая производительность Excel 2010 зависит от контента электронной таблицы. Если в таблице содержится множество трехмерных фигур, поддержка аппаратного ускорения даст значительно большее повышение эффективности по сравнению с таблицами, содержащими только двухмерные фигуры (поскольку для отображения таких фигур не задействуется графический процессор). |
Улучшения вычислений
Начиная с версии Excel 2007, улучшена производительность многопоточных вычислений.
Начиная с версии Excel 2010, представлены дополнительные улучшения, позволяющие повысить скорость вычислений. В Excel 2010 поддерживается асинхронный вызов пользовательских функций. Это позволяет повысить производительность за счет одновременного выполнения нескольких вычислений. При выполнении пользовательских функций в вычислительном кластере асинхронный вызов функций позволяет использовать в вычислениях несколько компьютеров. Дополнительные сведения см. в статье Асинхронные пользовательские функции.
Многоядерная обработка
В Excel 2010 внесены дополнительные улучшения для эффективного использования возможностей многоядерных процессоров и повышения производительности при выполнении стандартных задач. Начиная с версии Excel 2010, следующие функции могут использовать многоядерные процессоры: сохранение и открытие файлов, обновление сводной таблицы (для внешних источников данных, за исключением OLAP и SharePoint), сортировка обычной или сводной таблицы, а также автоподбор размеров столбца.
Для операций чтения, загрузки или записи данных, таких как открытие и сохранение файлов или обновление данных, разбиение операции на два процесса позволяет повысить скорость ее выполнения. При этом первый процесс получает данные, а второй загружает их в соответствующую структуру в памяти или записывает их в файл. Таким образом, как только первый процесс начинает чтение фрагмента данных, второй может немедленно начать загрузку или запись этих данных. При этом первый может по-прежнему продолжать считывание очередного фрагмента данных. Ранее первый процесс должен был сначала считать все данные в конкретном разделе, после чего второй мог начать загрузку данных этого раздела в память или их запись в файл.
PowerPivot
PowerPivot — это набор приложений и служб, с помощью которого реализуется комплексный подход к созданию управляемых пользователями решений бизнес-аналитики на основе данных в книгах Excel. PowerPivot для Excel — это инструмент анализа данных, который обладает непревзойденной вычислительной эффективностью и может использоваться непосредственно в Excel. Благодаря применению знакомых функций Excel пользователи могут с высокой скоростью преобразовывать большие объемы данных практически из любого источника в наборы значащих данных, с помощью которых можно за несколько секунд получать ответы практически на любые вопросы.
PowerPivot также поддерживает интеграцию с SharePoint. PowerPivot для SharePoint размещается в ферме SharePoint и представляет собой набор серверных приложений и компонентов, обеспечивающих эффективную совместную работу группы с данными бизнес-аналитики. SharePoint используется в качестве платформы для совместной работы и обмена данными бизнес-аналитики между группами в крупных организациях. На сайтах SharePoint публикуются данные бизнес-аналитики, создаваемые авторами и владельцами книг.
Дополнительные сведения о PowerPivot см. в статье Общие сведения о PowerPivot.
Службы высокопроизводительных вычислительных систем для Excel 2010
Приложение Excel 2010 предлагает широчайший выбор функций статистического анализа, поддержку построения сложных анализов, а также огромные возможности для расширения, что делает его оптимальным выбором для анализа бизнес-данных. По мере увеличения размеров моделей и сложности книг возрастает ценность создаваемой информации. Естественно, выполнение вычислений в сложных книгах занимает больше времени. На выполнение сложного анализа сложных книг пользователи зачастую могут тратить часы, дни и даже недели.
Одним из возможных решений является использование приложения Windows HPC Server 2008 для параллельного масштабирования вычислений Excel между несколькими узлами в кластере высокопроизводительных вычислительных систем Windows (HPC). Существует три способа выполнения вычислений Excel 2010 в кластере на базе Windows HPC Server 2008: запуск книг Excel в кластере, выполнение пользовательских функций Excel в кластере, а также использование Excel в качестве клиента кластерной ориентированной на службы архитектуры (SOA).
Дополнительные сведения о службах высокопроизводительных вычислительных систем для Excel 2010 см. на странице Ускорение Excel 2010 с помощью Windows HPC Server 2008 R2.
Заключение
В Excel 2016 реализованы улучшения производительности и ограничений, предназначенные для повышения эффективности работы с большими и сложными книгами в Excel. Эти улучшения позволяют Excel масштабироваться вместе с оборудованием, что повышает производительность, так как мощность ЦП и ОЗУ компьютеров увеличивается.
См. также
- Производительность Excel: повышение производительности вычислений
- Производительность Excel: советы по оптимизации производительности
- Портал разработчиков Excel
- Изменения названий уровней с поздним и ранним доступом в программе предварительной оценки Office для Windows
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.