Самый быстрый ВПР
Если в ваших таблицах всего лишь несколько десятков строк, то, скорее всего, эта статья не будет для вас актуальной. На таких небольших объемах данных любой способ будет работать достаточно шустро, чтобы вы этого не замечали. Если же число строк в ваших списках измеряется тысячами, да и самих таблиц не одна-две, то время мучительного ожидания на пересчете формул в 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, который тоже может решить нашу задачу. Кто-то скажет, что некорректно сравнивать пересчёт формул с механизмом обновления запроса, но мне, откровенно говоря, просто самому было интересно — кто быстрее?
- Превращаем обе наши таблицы в «умные» с помощью команды Форматировать как таблицу на вкладке Главная (Home — Format as Table) или с помощью сочетания клавиш Ctrl + T .
- По очереди загружаем таблицы в Power Query с помощью команды Данные — Из таблицы / диапазона (Data — From Table/Range) .
- После загрузки в Power Query возвращаемся обратно в Excel, оставляя загруженные данные как подключение. Для этого в окне Power Query выбираем Главная — Закрыть и загрузить — Закрыть и загрузить в. — Только создать подключение (Home — Close&Load — Close&Load to. — Only create connection) .
- После того, как обе исходные таблицы будут загружены как подключения, создадим ещё один, третий запрос, который будет объединять их между собой, подставляя цены из прайса в отгрузки. Для этого на вкладке Данные выберем Получить данные / Создать запрос — Объединить запросы — Объединить (Get Data / New Query — Merge queries — Merge) :
В отличие от формул, запросы Power Query не обновляются автоматически «на лету», а требуют щелчка правой кнопкой мыши по таблице (или запросу в правой панели) и выбору команды Обновить (Refresh) . Также можно воспользоваться командой Обновить все (Refresh All) на вкладке Данные (Data) .
Время обновления = 8,2 сек.
Итоговая таблица и выводы
Если вы честно дочитали до этого места, то какие-то выводы, наверное, уже сделали самостоятельно. Если же пропустили все детали и сразу перешли к итогам, то вот вам общая результирующая таблица по скорости всех методов:
Само-собой, у каждого из нас свои предпочтения, задачи и тараканы, но для себя я сформулировал выводы после этого тестирования так:
- ВПР всё ещё главная рабочая лошадка. После прошлогодних обновлений, ускоряющих ВПР, и осенних обновлений вычислительного движка, эта функция заиграла новыми красками и даёт жару по-полной.
- Не нужно лениться и выделять столбцы целиком — для всех способов без исключения это ухудшает результаты почти в 3 раза.
- Экзотические способы из прошлого типа СУММПРОИЗВ и СУММЕСЛИ — в топку. Они работают очень медленно и, вдобавок, не поддерживают динамические массивы.
- Динамические массивы и умные таблицы — это будущее.
Ссылки по теме
- Как использовать функцию ВПР для подстановки значений в Excel
- Функция ПРОСМОТРХ как наследник ВПР
- 5 вариантов использованияфункцииИНДЕКС
Самые полезные формулы Excel
MS Excel — универсальный инструмент, для работы с таблицами, который позволяет быстро осуществлять различные экономико-статистические расчеты, использовать графические инструменты и много-много других функций.
Наверняка, 99% (если не все 100%) из вас знакомы с ним. Поэтому, давай пробежимся по основным функциям MS Excel. Для удобства работы будем использовать Google Sheets, аналог Excel, который позволяет работать с таблицами в браузере, но имеет почти идентичный функционал.
Операции с ячейками
Объединение значений ячеек
Бывает такое, что нам необходимо объединить значения двух ячеек в одну. Когда строк в таблице очень много — вручную физически это сделать очень затратно. Да что уж там, даже при наличии 10 строк в таблице.
Для сцепки значений ячеек используется амперсанд & либо формула СЦЕПИТЬ , то есть итоговая формула ячейки может быть такой:
Получение значений из других файлов Google Sheets
Иногда требуется и такое. Данные находятся в разных файлах и одни из них нужно использовать в файле с другими. Для этого достаточно воспользоваться формулой IMPORTRANGE , где нужно указать URL адрес таблицы, лист и ячейку, из которой берутся данные:
Поиск наибольшего и наименьшего значения
Функция МИН возвращает минимальное значение из указанного диапазона. А функция МАКС — наибольшее значение. Форма записи этих функций достаточно проста.
Сразу рассмотрим на примере. У нас есть количество сеансов и количество транзакций за месяц. Нам нужно найти наименьшее и наибольшее количество сеансов в день соответственно. В функции достаточно выбрать диапазон, из которого это значение мы будем искать.
Простейшие функции
Суммирование
Чтобы узнать, сколько всего сеансов было за период, можно воспользоваться функцией СУММ .
Среднее значение
Чтобы узнать среднее количество транзакций в день, воспользуемся формулой СРЗНАЧ .
Функции с условиями
Сравнение значений
Чтобы выполнить логические сравнения значений, используют функцию ЕСЛИ . Например, план продаж в день составляет 100 транзакций, и необходимо в дополнительном столбце указать был ли выполнен план продаж или нет. По ходу проверки ставим «нет», если не выполнен и «да» — при успешном выполнении плана.
Сравнение по нескольким условиям
К сожалению отдельной формулы, которая поможет быстренько перечислить сразу несколько условий подряд в Excel нет, но это не мешает нам использовать формулу ЕСЛИ немного по другому. Например, нам нужно учитывать, был ли выполнен план транзакций из предыдущего пункта, но при условии, что количество сеансов было не менее 10000 в день.
Для этого в третью переменную функции ЕСЛИ мы прописываем еще одну функцию ЕСЛИ . То есть, если первое условие не выполняется (в данном случае план продаж ниже указанного), то Excel поставит «нет», если план выполняется, Excel перейдет к следующему условию.
Сравнение и суммирование
Далее, нам хочется посчитать, сколько всего транзакций в декабре было в дни с выполненным планом, но дополнительный столбец использовать не хочется. Воспользуемся функцией СУММЕСЛИ . Здесь мы суммируем количество транзакций при условии, что выполнение плана = «да».
Сравнение по нескольким условиям и суммирование
Если нам необходимо добавить еще одно условие при суммировании, например, сеансов не менее 10000, используют функцию СУММЕСЛИМН .
Сравнение и подсчет
Теперь можем посчитать, сколько же всего дней план выполнялся и для этого воспользуемся функцией СЧЁТЕСЛИ .
Сопоставление данных
ВПР
Этой формулой мы можем воспользоваться, если необходимо данные из одной таблицы сопоставить с данными в другой. Например, с помощью ВПР перенесем данные о новых пользователях в исходную таблицу.
Дополнительная таблица выглядит следующим образом:
Далее в нашей таблице создаем столбец «Новые пользователи», заносим в него формулу ВПР , которая содержит:
- Искомую ячейку;
- Таблицу с данными которые нужно перенести (первый столбец в таблице должен начинаться со столбца с искомыми значениями);
- Номер столбца в этой таблице, значения которого нужно перенести;
- Тип совпадений значений, нам нужно точное совпадение, поэтому ставим «0» или «Ложь».
Домашнее задание
- Посмотри полный список доступных функций.
- Потренируйся применять функции описанные в данном уроке.
Производительность 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 можно выделить четкие этапы вычисления:
- Построение исходной цепочки вычислений и определение места начала вычисления. Этот этап выполняется, когда книга загружается в память.
- Отслеживание зависимостей, пометка ячеек как нерассчитанных и обновление цепочки вычислений. Этот этап выполняется при каждой записи или каждом изменении даже в режиме вычисления вручную. Обычно этот этап выполняется настолько быстро, что пользователь не замечает его, но в сложных случаях отклик может быть медленным.
- Расчет всех формул. В ходе процесса вычисления в Excel переупорядочивается и переструктурируется цепочка вычислений в целях оптимизации будущих пересчетов.
- Обновление видимых частей окон 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
Многие параметры вычисления (автоматически, автоматически, кроме таблиц данных, вручную, Пересчитывать книгу перед сохранением) и настройки итераций (Включить итеративные вычисления, Предельное число итераций, Относительная погрешность) работают на уровне приложения, а не на уровне книги (они одинаковы для всех открытых книг).
Чтобы просмотреть дополнительные параметры вычисления, на вкладке Файл выберите пункт Параметры. В диалоговом окне Параметры Excel выберите пункт Дополнительно. В разделе Формулы задайте параметры вычисления.
Рисунок 3. Дополнительные параметры вычислений
Когда запускается 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
Увеличение скорости вычислений в книгах
Выполните указанные ниже действия и способы, чтобы рабочие книги рассчитывались быстрее.
Скорость процессора и использование нескольких ядер
Для большинство версий 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 с таймерами вычислений
Поиск и определение приоритетов помех при вычислениях
На большинстве листов, на которых вычисления осуществляются медленно, имеется только несколько проблемных областей или помех, обработка которых занимает большую часть времени вычисления. Если неизвестно, где находятся эти помехи, воспользуйтесь для их поиска детализированным подходом, описанным в этом разделе. Если известно, где они находятся, необходимо измерить время вычисления, которое тратится на каждую помеху, с тем чтобы задать приоритеты в своей работе и удалить их.
Детализированный подход для поиска помех
Детализированный подход начинается с определения времени вычисления книги, вычисления каждого листа и блоков формул на листах, на которых вычисления осуществляются медленно. Выполняйте каждый шаг по порядку и записывайте время вычислений.
Поиск помех с помощью детализированного подхода
- Убедитесь, что открыта только одна книга и никакие другие задачи не выполняются.
- Задайте режим вычисления вручную.
- Сделайте резервную копию книги.
- Откройте книгу, которая содержит макросы таймеров вычисления, или добавьте их в книгу.
- Проверьте используемый диапазон, нажав клавиши CTRL+END в каждом листе по очереди. Это действие покажет ячейку, которая была использована последней. Если ячейка находится за пределами ожидаемого диапазона, подумайте об удалении лишних столбцов и строк и сохранении книги. Дополнительные сведения см. в разделе «Минимизация используемого диапазона» статьи Производительность Excel: советы по оптимизации производительности.
- Запустите макрос FullCalcTimer. Обычно больше всего времени занимает вычисление всех формул в книге.
- Запустите макрос RecalcTimer. Пересчет сразу же после полного вычисления обычно занимает меньше всего времени.
- Рассчитайте изменчивость книги как отношение времени пересчета к времени полного вычисления. Это измерит степень, в которой переменные формулы и расчет цепочки вычислений являются помехами.
- Активируйте каждый лист и по очереди запустите макрос SheetTimer. Поскольку вы только что выполнили пересчет книги, вы получите время пересчета для каждого листа. Это должно позволить вам определить, с какими из листов связана проблема.
- Запустите макрос RangeTimer на выбранных блоках формул.
- Для каждого проблемного листа поделите столбцы или строки на небольшое количество блоков.
- Выбирайте каждый блок по очереди и запускайте макрос RangeTimer по этому блоку.
- При необходимости выполните дальнейшую детализацию, подразделив каждый блок на еще меньшие блоки.
- Определите приоритеты для помех.
Ускорение выполнения вычислений и уменьшение количества помех
Затраты времени на выполнение вычислений обусловливаются не количеством формул или размером книги. Основными факторами здесь являются количество ссылок на ячейки и операций вычисления, а также эффективность используемых функций.
Большинство листов формируются посредством копирования формул, содержащих смесь абсолютных и относительных ссылок. Поэтому на них обычно имеется большое количество формул, содержащих повторяющиеся или дублируемые вычисления и ссылки.
Избегайте использования сложных мегаформул и формул массивов. В общем случае лучше иметь больше строк и столбцов и меньше сложных вычислений. Это позволяет и более эффективно проводить пересчет формул, и выполнять многопоточные вычисления в Excel, что представляет благоприятные возможности для оптимизации вычислений. Этот подход также проще для понимания и отладки. Ниже приводятся несколько правил, помогающих ускорить проведение вычислений в книге.
Первое правило: удаляйте дублируемые, повторяющиеся и не являющиеся обязательными вычисления
Выявите дублируемые, повторяющиеся и не являющиеся обязательными вычисления и прикиньте, сколько примерно ссылок на ячейки и вычислений будет задействовано в Excel, чтобы рассчитать результат при наличии этой помехи. Подумайте, как можно получить тот же результат при меньшем количестве ссылок и вычислений.
Обычно этот подход включает два или несколько из указанных ниже шагов:
- Уменьшите количество ссылок в каждой ячейке.
- Переместите повторяющиеся вычисления в одну или несколько вспомогательных ячеек, а затем задайте ссылки на вспомогательные ячейки из основных формул.
- Используйте дополнительные строки и столбцы для вычисления и сохранения промежуточных результатов, чтобы их можно было повторно использовать в других формулах.
Второе правило: используйте по возможности наиболее эффективные функции
Когда найдена помеха, затрагивающая функцию или формулы массивов, определите, есть ли более эффективный способ для достижения такого же результата. Пример:
- Поиск, проводимый по отсортированным данным, может оказаться в десятки и сотни раз более эффективным, чем поиск по не отсортированным данным.
- Пользовательские функции VBA обычно работают более медленно, чем встроенные функции в Excel (хотя грамотно написанные функции VBA могут работать достаточно быстро).
- Минимизируйте количество используемых ячеек в таких функциях, как СУММ и СУММЕСЛИ. Время вычисления пропорционально числу используемых ячеек (неиспользуемые ячейки игнорируются).
- Рассмотрите возможность замены медленных формул массивов пользовательскими функциями.
Третье правило: часто используйте интеллектуальный пересчет и многопоточные вычисления
Чем больше вы будете пользоваться функцией интеллектуального пересчета в Excel, тем меньше процедур обработки нужно будет выполнять каждый раз, когда в Excel выполняется пересчет, поэтому:
- Старайтесь по возможности не использовать такие функции, как ДВССЫЛ и СМЕЩ, если они не обеспечивают значительно большую эффективность, чем альтернативные функции. (При умелом использовании функция СМЕЩ часто обеспечивает быстрый результат).
- Минимизируйте размер диапазонов, используемых в формулах массивов и функциях.
- Разбейте формулы массивов и мегаформулы на отдельные вспомогательные столбцы и строки.
- Избегайте использования однопотоковых функций:
- PHONETIC
- ЯЧЕЙКА, когда используется аргумент «format» или «address»
- СМЕЩ
- ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
- КУБЭЛЕМЕНТ
- КУБЗНАЧЕНИЕ
- КУБСВОЙСТВОЭЛЕМЕНТА
- КУБМНОЖ
- КУБПОРЭЛЕМЕНТ
- КУБЭЛЕМЕНТКИП
- КУБЧИСЛОЭЛМНОЖ
- АДРЕС, когда указывается пятый параметр (sheet_name)
- Любая функция базы данных (БДСУММ, ДСРЗНАЧ, и т. д.), которая ссылается на сводную таблицу
- ТИП.ОШИБКИ
- ГИПЕРССЫЛКА
- Пользовательские функции для VBA и надстройки COM
Четвертое правило: рассчитывайте время и проверяйте результат для каждого изменения
Внося некоторые изменения, вы можете с удивлением заметить, что получили не тот результат, который ожидали, или что вычисления выполняются более медленно. Поэтому рекомендуется рассчитывать время и проверять результат для каждого изменения, следуя приведенной ниже инструкции.
- Рассчитайте время для формулы, которую нужно изменить, используя макрос RangeTimer.
- Внесите изменение.
- Рассчитайте время для измененной формулы, используя макрос RangeTimer.
- Убедитесь, что измененная формула дает правильный ответ.
Примеры использования правил
В следующих разделах приводятся примеры использования указанных правил для ускорения вычислений.
Суммы с начала периода
Например, требуется рассчитать суммы с начала периода для столбца, содержащего 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 и обратная связь.
Обратная связь
Были ли сведения на этой странице полезными?
Как работать с формулами в Excel
Формулы в таблицах Excel делятся на простые, сложные и комбинированные.
Анастасия Хамидулина
Автор статьи
13 апреля 2022 в 17:48Формулы в таблицах Excel используют, когда данных очень много. Например, чтобы посчитать сумму нескольких чисел быстрее, чем на калькуляторе. Преимуществ много, поэтому работодатели часто указывают эту программу в требованиях. В конце ноября 2023 года больше 110 000 вакансий на хедхантере содержали формулировки вроде «уверенный пользователь Excel», «работа с формулами в Excel».
Кому важно знать Excel и где выучить основы
Excel нужен бухгалтерам, чтобы вести учет в таблицах. Экономистам, чтобы делать перерасчет цен, анализировать показатели компании. Менеджерам — вести базу клиентов. Аналитикам — строить и проверять гипотезы.
Программу можно освоить самостоятельно, например по статьям в интернете. Но это поможет понять только основные формулы. Если нужны глубокие знания — как строить сложные прогнозы, собирать калькулятор юнит-экономики, — пройдите курсы.
Аналитик данных: новая работа через 5 месяцев
Получится, даже если у вас нет опыта в IT
На онлайн-курсе Skypro «Аналитик данных» научитесь владеть базовыми формулами Excel, работать с нестандартными данными, статистикой. Кроме Excel вы изучите Metabase, SQL, Power BI, язык программирования Python. Программа подойдет даже тем, у кого совсем нет опыта в анализе и кто не любит математику. Вас ждут живые вебинары, мастер-классы, домашки с разбором, помощь наставников.
Урок из курса «Аналитик данных» в Skypro
Из чего состоит формула в Excel
= с него начинают любую формулу;
( ) заключают формулу и ее части;
; применяют, чтобы указать очередность ячеек или действий;
: ставят, чтобы обозначить диапазон ячеек, а не выбирать всё подряд вручную.
В таблицах Excel работают с простыми математическими действиями:
сложением +
вычитанием —
умножением *
делением /
возведением в степень ^
Еще в таблицах Excel используют символы сравнения:
равенство =
меньше
больше >
меньше либо равно
больше либо равно >=
не равно <>
Основные виды
Все формулы в таблицах Excel делятся на простые, сложные и комбинированные. Их можно написать самостоятельно или воспользоваться встроенными.
Многие помнят их с уроков информатики в школе. Но даже если вы совсем не знакомы с таблицами Excel, на курсе Skypro «Аналитик данных» обучение начинают с основ. Не нужны глубокие знания математики и навыки работы с программами для анализа данных.
Простые
Применяют, когда нужно совершить одно простое действие, например сложить или умножить.
✅ СУММ. Складывает несколько чисел. Сумму можно посчитать для нескольких ячеек или целого диапазона в таблице.
=СУММ(А1;В1) — для соседних ячеек;
=СУММ(А1;С1;H1) — для определенных ячеек;
=СУММ(А1:Е1) — для диапазона.
Сумма всех чисел в ячейках от А1 до Е1
✅ ПРОИЗВЕД. Умножает числа в соседних, выбранных вручную ячейках или диапазоне таблицы.
Произведение всех чисел в ячейках от А1 до Е1
✅ ОКРУГЛ. Округляет дробное число до целого в большую или меньшую сторону. Укажите ячейку таблицы с нужным числом, в качестве второго значения — 0.
=ОКРУГЛВВЕРХ(А1;0) — к большему целому числу;
=ОКРУГЛВНИЗ(А1;0) — к меньшему.
Округление в меньшую сторону
✅ ВПР. Находит данные в таблице или определенном диапазоне.
=ВПР(С1;А1:В6;2)- С1 — ячейка, в которую выписывают известные данные. В примере это код цвета.
- А1 по В6 — диапазон ячеек в таблице. Ищем название цвета по коду.
- 2 — порядковый номер столбца таблицы для поиска. В нём указаны названия цвета.
Формула вычислила, какой цвет соответствует коду
✅ СЦЕПИТЬ. Объединяет данные диапазона ячеек таблицы, например текст или цифры. Между содержимым ячеек можно добавить пробел, если объединяете слова в предложения.
=СЦЕПИТЬ(А1;В1;С1) — текст без пробелов;
=СЦЕПИТЬ(А1;» «;В1;» «С1) — с пробелами.
Формула объединила три слова в одно предложение
✅ КОРЕНЬ. Вычисляет квадратный корень числа в ячейке таблицы.
=КОРЕНЬ(А1)
Квадратный корень числа в ячейке А1
✅ ПРОПИСН. Преобразует текст в верхний регистр, то есть делает буквы заглавными.
=ПРОПИСН(А1:С1)
Формула преобразовала строчные буквы в прописные
✅ СТРОЧН. Переводит текст в нижний регистр, то есть делает из больших букв маленькие.
=СТРОЧН(А2)✅ СЧЕТ. Считает количество ячеек таблицы с числами.
=СЧЕТ(А1:В5)
Формула вычислила, что в диапазоне А1:В5 четыре ячейки с числами
✅ СЖПРОБЕЛЫ. Убирает лишние пробелы. Например, когда переносите текст из другого документа и сомневаетесь, правильно ли там стоят пробелы.
=СЖПРОБЕЛЫ(А1)
Формула удалила двойные и тройные пробелы
Сложные
✅ ПСТР. Выделяет определенное количество знаков в тексте, например одно слово.
=ПСТР(А1;9;5)- Введите =ПСТР.
- Кликните на ячейку таблицы, где нужно выделить знаки.
- Укажите номер начального знака: например, с какого символа начинается слово. Пробелы тоже считайте.
- Поставьте количество знаков, которые нужно выделить из текста. Например, если слово состоит из пяти букв, впишите цифру 5.
В ячейке А1 формула выделила 5 символов, начиная с 9-го
✅ ЕСЛИ. Анализирует данные по условию. Например, когда в таблице нужно сравнить одно с другим.
=ЕСЛИ(A1>25;"больше 25";"меньше или равно 25")В формуле указали:
- А1 — ячейку с данными;
- >25 — логическое выражение;
- больше 25, меньше или равно 25 — истинное и ложное значения.
Первый результат возвращается, если сравнение истинно. Второй — если ложно.
Число в А1 больше 25. Поэтому формула показывает первый результат — больше 25.
✅ СУММЕСЛИ. Складывает числа, которые соответствуют критерию. Обычно критерий — числовой промежуток или предел.
=СУММЕСЛИ(В2:В5;">10")В формуле указали:
- В2:В5 — диапазон ячеек таблицы;
- >10 — критерий, то есть числа меньше 10 не будут суммироваться.
Число 8 меньше указанного в условии, то есть 10. Поэтому оно не вошло в сумму.
✅ СУММЕСЛИМН. Складывает числа, когда условий несколько. В формуле указывают диапазоны — ячейки таблицы, которые нужно учитывать. И условия — содержание подходящих ячеек. Например:
=СУММЕСЛИМН(D2:D6;C2:C6;"сувениры";B2:B6;"ООО ХY")- D2:D6 — диапазон, из которого суммируем числа;
- C2:C6 — диапазон ячеек таблицы для категории;
- сувениры — условие, то есть числа другой категории учитываться не будут;
- B2:B6 — диапазон ячеек таблицы для компании;
- ООО XY — условие, то есть числа другой компании учитываться не будут.
Под условия подошли только ячейки D3 и D6: их сумму и вывела формула
Комбинированные
В таблицах Excel можно комбинировать несколько функций: сложение, умножение, сравнение и другие. Например, вам нужно найти сумму двух чисел. Если значение больше 65, сумму нужно умножить на 1,5. Если меньше — на 2.
=ЕСЛИ(СУММ(A1;B1)<65;СУММ(A1;B1)*1,5;(СУММ(A1;B1)*2))То есть если сумма двух чисел в А1 и В1 окажется меньше 65, программа посчитает первое условие — СУММ(А1;В1)*1,5. Больше 65 — Excel задействует второе условие — СУММ(А1;В1)*2.
Сумма в А1 и В1 больше 65, поэтому формула посчитала по второму условию: умножила на 2
Встроенные
Используйте их, если удобнее пользоваться готовыми формулами, а не вписывать вручную.
- Поместите курсор в нужную ячейку таблицы.
- Откройте диалоговое окно мастера: нажмите клавиши Shift + F3. Откроется список функций.
- Выберите нужную формулу. Нажмите на нее, затем на «ОК». Откроется окно «Аргументы функций».
- Внесите нужные данные. Например, числа, которые нужно сложить.
Ищите формулу по алфавиту или тематике, выбирайте любую из тех, что использовали недавно
Если вы хотите освоить Excel с нуля и эффективно работать с данными, пройдите в онлайн-университете Skypro специальный курс. Он идет всего два с половиной месяца — этого будет достаточно, чтобы узнать всё о таблицах и ускорить работу с ними.
В конце курса сможете создать калькулятор юнит-экономики и показать работодателю, что умеете считать прибыльность бизнес-модели.Как скопировать
Если для разных ячеек таблицы нужны однотипные действия, например сложить числа не в одной, а в нескольких строках, скопируйте формулу.
- Впишите функцию в ячейку таблицы и кликните на нее.
- Наведите курсор на правый нижний угол — курсор примет форму креста.
- Нажмите левую кнопку мыши, удерживайте ее и тяните до нужной ячейки таблицы.
- Отпустите кнопку. Появится итог.
Посчитали сумму ячеек в трех строках
Как обозначить постоянную ячейку
Это нужно, чтобы, когда вы протягивали формулу, ссылка на ячейку таблицы не смещалась.
- Нажмите на ячейку таблицы с формулой.
- Поместите курсор в нужную ячейку таблицы и нажмите F4.
- В формуле фрагмент с описанием ячейки таблицы приобретет вид $A$1. Если вы протянете формулу, то ссылка на ячейку $A$1 останется на месте.
Как поставить «плюс», «равно» без формулы
Когда нужна не формула, а данные, например +10 °С:
- Кликните правой кнопкой по ячейке таблицы.
- Выберите «Формат ячеек».
- Отметьте «Текстовый», нажмите «ОК».
- Поставьте = или +, затем нужное число.
- Нажмите Enter.
27 января 18:00 МСК
Лучшие IT-профессии 2024 года: тренды, зарплаты, перспективы
Как сделать фильтр по столбцам в Google Sheets
Фильтрация данных — это процесс выделения из набора строк и столбцов только тех, которые подходят определенным, заранее установленным условиям. Благодаря этому пользователям проще анализировать конкретные части данных и работать с ними.
Представим, что перед нами задача — собрать данные о клиентах: возраст до тридцати лет, из Москвы и Казани. Для этого нужно будет поработать с фильтром.
Таблица Excel. Работа с фильтром
Перейдите на любую ячейку таблицы, кликните «Создать фильтр».
Щелкните по выпадающему списку столбца «Город» и отметьте галочками только Москву и Казань. Нажмите «ОК».Дальше точно так же кликните по выпадающему списку столбца «Возраст» и выберите опцию «Фильтровать по условию». Выберите в списке «Меньше или равно», а затем выставьте значение «30». Нажмите «ОК».
Если нужно вернуть исходный вид данных, можно сбросить настройки таблицы. Для этого перейдите по значку фильтра.
В онлайн-университете Skypro вы можете за пару месяцев пройти курс по Excel и получить востребованный навык для аналитики. Программа курса состоит из важнейших блоков: первичная обработка информации, работа со сложными данными и прогнозирование. Еще вы создадите проект по анализу бизнес-метрик.Как создать сводную таблицу в Google Sheets
С помощью сводной таблицы можно объединять данные из разных таблиц и листов, чтобы посчитать общее значение.
Представим, что вам нужно посчитать, сколько продаж было в каждом отделе сети мебельных магазинов.
Сводная таблица в Google Sheets
Выделяем ячейку А1. Так программа поймет, c какими значениями нужно работать.
В верхнем меню «Вставка» выбираем «Создать сводную таблицу».
Откроется меню сводной таблицы. Выберите нужный диапазон и место. Из-за того что курсор стоит в ячейке с данными, поле диапазона заполнится автоматически. Если курсор — в пустой ячейке, нужно прописать диапазон вручную. Вы можете сделать сводную таблицу на этом же листе или на другом.
Сводная таблица в Google Sheets
На новом листе сформируем таблицу, которая покажет данные продаж по каждому отделу. В списке полей сводной таблицы нужно выбрать столбцы «Отдел» и «Сумма». Получаем итоги по каждому отделу.
Главное о формулах в Excel
- Формула состоит из математических знаков. Чтобы ее вписать, используют символы = ( ) ; : .
- С помощью простых формул числа складывают, умножают, округляют, извлекают из них квадратный корень. Чтобы отредактировать текст, используют формулы поиска, изменения регистра, удаления лишних пробелов.
- Сложные и комбинированные формулы помогают делать объемные вычисления, когда нужно соблюдать несколько условий.
- Значительно упростит жизнь аналитика возможность создавать сводную таблицу для расчетов и фильтровать данные по столбцам