Как ускорить работу Excel с большими электронными таблицами

Обновлено: 24.11.2024

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

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

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

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

Как повысить эффективность электронных таблиц Excel?

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

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

1. Предотвращение неустойчивых формул

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

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

2. Используйте вспомогательные столбцы

Вспомогательные столбцы — самые недооцененные элементы дизайна в Excel. Как правило, пользователь не будет использовать вспомогательные столбцы.

НЕ ДЕЛАЙТЕ ЭТОГО.

Использование «вспомогательных столбцов» поможет вам избежать формул массива.

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

3. Отключите все надстройки Excel

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

Вот как это сделать:

  • Сначала выберите «Файл» > «Параметры» > «Надстройки».
  • Проверьте включенные надстройки и, выбрав их, отключите их, например "Надстройки Com".
  • Далее нажмите "Перейти".
  • Снимите флажки со всех надстроек, которые не являются ненужными. Если надстройки необходимы, вы можете включить их снова

4. Используйте условное форматирование с осторожностью

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

Также рекомендуется использовать его с осторожностью для оптимизации файла Excel.

5. Используйте именованные диапазоны и таблицы Excel

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

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

6. Преобразование неиспользуемых формул в статические значения

Говорят: «Если тебе это не нужно, не оставляй». Большое количество формул приводит к медленной работе книги Excel. Поэтому, если у вас есть ненужные формулы, лучше преобразовать их в статическое значение, просто вставив в качестве значений. В конечном итоге это поможет вам повысить производительность Excel.

7. Храните все ссылочные данные на одном листе

Ну, это возможно не всегда, но если вы сможете это сделать, постепенно ваш лист Excel станет быстрее.

Логика этого проста; формулы на листах не требуют далекого перемещения данных, когда они помещаются рядом на том же листе.

8. Избегайте использования всей строки/столбца в качестве ссылки

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

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

9. Используйте ручной режим расчета

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

  • Перейдите на вкладку "Формула" > "Параметры расчета" > "Вручную" (нажмите клавишу F9, чтобы пересчитать)

10. Используйте более быстрые методы формул

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

  • Используйте ЕСЛИОШИБКА вместо комбинации ЕСЛИ и ЕОШИБКА (если вы используете Excel 2003 или более раннюю версию, в которой нет ЕСЛИОШИБКА).
  • Использовать — (двойное отрицание) для преобразования ИСТИНА и ЛОЖЬ в 1 и 0 (вместо умножения на 1 или добавления к нему 0). Повышение скорости заметно в больших наборах данных.

11. Разделить книгу на несколько файлов

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

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

Дополнительные советы по ускорению работы с файлом Excel:

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

Иногда эта проблема возникает из-за того, что служба ОС, известная как "Супервыборка", отключена. Это необходимо для кэширования данных в ОЗУ, чтобы, как следует из названия, при необходимости их можно было извлечь сверхбыстро.

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

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

Для этого выполните несколько инструкций:

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

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

Окончательный вердикт:

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

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

Начните внедрять их прямо сейчас…

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

Маргрет

Маргрет Артур – предприниматель и эксперт по контент-маркетингу. Она ведет технические блоги и делится знаниями по MS Office, Excel и другим техническим дисциплинам. Ее самобытное искусство представления технической информации простым для понимания языком очень впечатляет. Когда она не пишет, она любит незапланированные путешествия.

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

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

В 9 из 10 случаев пользователь Excel жаловался на медленную работу электронных таблиц Excel.И вы вряд ли что-то можете с этим поделать.

Ну, это НЕ совсем так.

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

10 советов по работе с медленными электронными таблицами Excel

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

  1. Избегайте нестабильных функций (вы должны).
  2. Использовать вспомогательные столбцы.
  3. Избегайте формул массива (если можете).
  4. Осторожно используйте условное форматирование.
  5. Используйте таблицы Excel и именованные диапазоны.
  6. Преобразование неиспользуемых формул в значения.
  7. Хранить все ссылочные данные на одном листе.
  8. Избегайте использования всей строки/столбца в ссылках.
  9. Использовать режим ручного расчета.
  10. Используйте более быстрые методы формул.

1. Избегайте неустойчивых формул

Неслучайно летучие формулы называются так. Такие функции, как NOW, TODAY, INDIRECT, RAND, OFFSET и т. д., пересчитываются каждый раз, когда в книге происходят изменения.

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

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

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

2. Использовать вспомогательные столбцы

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

НЕ ДЕЛАЙТЕ ЭТОГО.

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

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

Вот несколько примеров использования вспомогательных столбцов:

3. Избегайте формул массива

У формул массива есть свои преимущества, но скорость не входит в их число.

Как объяснялось выше, формулы массива могут обрабатывать много данных (ссылки на ячейки), анализировать их и давать вам результат. Но это требует времени.

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

4. Используйте условное форматирование с осторожностью

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

Но… вот в чем проблема.

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

Совет — используйте его осторожно.

5. Используйте таблицы Excel и именованные диапазоны

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

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

Кроме того, это делает ваши формулы более понятными.

Например, что легче понять?

=Цена продажи-себестоимость ИЛИ =СУММ(A1:A10)-СУММ(G1:G10)

6. Преобразование неиспользуемых формул в статические значения

Это не проблема. Если он вам не нужен, не храните его.

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

7. Храните все ссылочные данные на одном листе

Это не всегда возможно, но если вы сможете это сделать, я гарантирую, что ваш лист Excel станет быстрее.

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

8. Избегайте использования всей строки/столбца в качестве ссылки (A:A)

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

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

9. Использовать режим ручного расчета

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

  • Чтобы переключиться в ручной режим, перейдите на вкладку "Формула" > "Параметры расчета" > "Вручную" (для пересчета нажмите клавишу F9).

10. Используйте более быстрые методы формул

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

Вот несколько примеров:

  • Используйте ЕСЛИОШИБКА вместо комбинации ЕСЛИ и ЕОШИБКА (если вы не используете Excel 2003 или более раннюю версию, в которой нет ЕСЛИОШИБКА).
  • Используйте MAX(A1,0) вместо IF(A1>0,A1,0). Это классный совет, который я узнал от мистера Эксель, также известного как Билл Джелен. Его исследование показывает, что опция MAX на 40 % быстрее, чем опция IF (и я готов принять эту статистику за чистую монету).
  • Используйте комбинацию ИНДЕКС/ПОИСКПОЗ вместо ВПР. Это может вызвать недоумение, но правда в том, что ВПР не может быть быстрее, если у вас есть сотни столбцов данных. Мир движется к ИНДЕКС/ПОИСКПОЗ, и вы тоже должны измениться.
    [Если вы все еще не знаете, что использовать, вот прямое сравнение VLOOKUP и VLOOKUP. ИНДЕКС/ПОИСКПОЗ].
  • Используйте — (двойное отрицание) для преобразования значений ИСТИНА и ЛОЖЬ в 1 и 0 (вместо умножения на 1 или прибавления к нему 0). Повышение скорости заметно в больших наборах данных.

Это исчерпывающий список? Точно нет. Вот несколько хороших, которыми, я думаю, стоит поделиться в качестве отправной точки. Если вы хотите освоить методы Excel-Speed-Up, многие эксперты по Excel проделали хорошую работу.

Вот несколько источников, которые могут оказаться полезными:

    (умело сделано с помощью краудсорсинга). . (исследуйте это, и вы найдете массу советов).

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

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

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

БЕСПЛАТНАЯ КНИГА EXCEL

Получите электронную книгу «51 совет по Excel», чтобы резко повысить свою продуктивность и быстрее выполнять работу

30 мыслей на тему «10 способов исправить медленные таблицы Excel (ускорить работу Excel)»

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

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

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

Привет! Это очень помогает, спасибо, что собрали все в одном месте! 🙂

Это был очень полезный контент, но я кое-что к нему добавлю.
если вы используете ЕСЛИОШИБКА вместо ЕСЛИ, проблема, которую это вызовет, состоит в том, что Iferror сначала оценивает значение, которое является формулой, и если это вызывает ошибку, то он оценивает ложный аргумент, который является пустой тратой ресурсов и времени.

Большое спасибо за вашу поддержку

Иными словами, не используйте Excel! Вы только что уничтожили большинство самых удобных инструментов. Я люблю Excel, но они не продумали это, я говорю это как инженер-электронщик (и s/w, и h/w).

Рад, что советы оказались полезными, Шон!

как вы нашли лист и столбец?/

Приведенная выше формула очень помогла мне в изучении Excel. Everest предлагает учебные пособия по Microsoft Excel: наши услуги: обучение Microsoft Excel, онлайн-обучение Excel, курсы Excel, классы Excel, изучение Excel.

Мне нравится этот блог Excel Everest предлагает учебники по Microsoft Excel: наша администрация занимается обучением Microsoft Excel, онлайн-обучением Excel, курсами Excel.

№ 8 имеет огромное значение и, к счастью, остановил все большие листы моей компании, которые неоднократно зависали, а затем аварийно завершали работу Excel.

Мой дорогой друг. Спасибо.
Виновником была условная формула.
С уважением.

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

Что такое вспомогательная колонка? Не могли бы вы описать…. ?

Полезно, но похоже, что это нацелено на «вину пользователей», что может быть правдой для людей, использующих определенные рабочие листы. Это не нацелено на проблему, когда КАЖДЫЙ рабочий лист имеет медленный ввод, что обычно связано с плохой или враждебной разработкой программного обеспечения Microsoft и требует манипулирования настройками ядра и отключения таких функций, как настройки сети и справки, активация продукта и т. д., и вообще другой мусор M $ и использует АНБ для своих различных протекционистских схем и для незаконного слежки за своими пользователями.

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

Спасибо. Да, в основном процессоры i7 и скорости ГГц, программное обеспечение наших компьютеров должно иметь молниеносную скорость ввода данных, сохранения, передачи папок и т. д. Сегодня компьютеры имеют реальную вычислительную мощность суперкомпьютеров прошлых лет… но почему-то они этого не делают. Почему? Ну, причина проста… M$ максимизирует эту дополнительную мощность для своих собственных целей, а не для пользователей, что в масштабах, в которых она это делает, в основном воровство в масштабах, намного больших, чем так называемые пираты программного обеспечения, которые все, что вы услышать в СМИ. Настоящие пираты (наших процессорных циклов и зомбированных компьютеров) — это сама M$ и любая третья сторона, с которой они соглашаются (RIAA, АНБ и т. д.). Практика должна считаться незаконной по любому разумному определению закона, однако M$ просто трусливо прикрывается проприетарным кодом ядра и зашифрованными передачами данных в Редмонд и куда-либо еще. Недавно я обнаружил, что M$ загружает 3 ГБ программного обеспечения Winn10 на свой персональный компьютер. Они заплатили за пропускную способность… нет… они дали мне знать, что занимаются этим незаконным делом… нет. Они даже помещают его в скрытый защищенный файл. Это огромная потеря экономической производительности, которая сегодня так сильно зависит от компьютеров. Дела в M$ настолько гнилые, что правительству действительно следует подумать о том, чтобы их разбить. Все эти талантливые инженеры-программисты просто производят один мусорный продукт за другим, потому что все, что волнует начальство, это деньги и разрушение конкуренции, почивая на лаврах с патентными зарослями и протекционизмом, вместо того, чтобы сосредоточиться на создании лучшего продукта.

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

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

Цифры от 1 до 6 должны быть вашей отправной точкой, поскольку их легко применять и они обычно оказывают большое влияние. Тогда вам следует попробовать числа от 7 до 13. Эти шаги немного сложнее, так как большинство из них требуют изменений в формулах или структуре книги.

Методы с 14 по 15 (методы с легким применением и низким воздействием) обычно также стоит попробовать, но в большинстве случаев они не слишком успешны. Что касается последних двух методов (16 и 17): я рекомендую использовать их только тогда, когда все остальные способы уже применены.

1. Ускорение за счет использования всех процессоров для расчета

По умолчанию Excel выполняет расчеты с использованием всех доступных ядер. Но иногда он использует только один процессор. Как проверить, сколько ядер используется?При расчете рабочей книги в правом нижнем углу будет указано количество используемых процессоров: «Расчет» (=только один процессор) или «Расчет (4 процессора)», например, см. номер 1 на рисунке справа.

Вы можете включить расчет со всеми доступными процессорами, выбрав «Файл» (№ 2), «Параметры», «Дополнительно» (№ 3). Прокрутите вниз, отметьте «Включить многопоточные вычисления» и выберите «Использовать все процессоры на этом компьютере» (№ 4). Это должно немедленно ускорить ваш расчет.

2. Закройте другие программы

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

3. Переключите региональные настройки компьютера на «Английский (США)»

Наше исследование показывает, что выбор региона «Английский (США)» оказывает безумно большое влияние на время вычислений в Excel: например, если вы используете немецкую систему счисления (используйте запятую в качестве десятичного разделителя), вы можете сократить время расчета до 81%.

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

4. Используйте 64-разрядную версию Excel для ускорения расчетов

Это довольно просто: установите 64-разрядную версию Office. Наш тест показывает, что вы можете сохранить приложение. 9% времени вычисления с 64-битной версией Excel. Условие: Ваш компьютер должен поддерживать 64-битное программное обеспечение. Кроме того, убедитесь, что сторонние надстройки поддерживают 64-разрядную версию Microsoft Office.

5. Переключить Excel на ручной расчет

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

Чтобы избежать повторного расчета каждый раз, когда вы что-либо меняете, перейдите в «Формулы», «Параметры расчета» и выберите «Вручную». Теперь ваша книга будет пересчитана только тогда, когда вы сохраните ее или нажмете F9. При нажатии Shift + F9 будет рассчитан только текущий рабочий лист.

Дополнительную информацию о различных параметрах расчета в Excel см. в этой статье.

6. Отключить все надстройки Excel

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

  • Перейдите в «Файл», затем в «Параметры», а затем в «Надстройки».
  • Просмотрите включенные надстройки и отключите их, выбрав, например, «Надстройки Com».
  • Затем нажмите "Перейти".
  • Отключите все надстройки, которые вам не нужны. Вы все еще можете включить их позже, если они вам понадобятся снова.

7. Используйте более быстрые формулы

Если вы используете СУММЕСЛИМН в качестве формулы поиска (что на самом деле очень удобно), вы можете пересмотреть свое решение. Мы обнаружили, что ВПР и комбинация ИНДЕКС-ПОИСКПОЗ намного быстрее, чем СУММЕСЛИМН. Таким образом, замена СУММЕСЛИМН на ВПР или ИНДЕКС-ПОИСКПОЗ (где это возможно) может сократить время расчета приложением. 53%.

Еще один пример: =MAX(A1,0) — приложение. На 6 % быстрее, чем =IF(A1>0,A1,0).

8. Те же диапазоны ячеек в формулах

Предположим, у вас есть функция, которую вы применяете ко всему столбцу. Диапазон ячеек в формуле отличается или меняется с каждой строкой, например =СУММ($A$1:A3) (одна часть фиксируется со знаком $, а другая — нет). Такая формула делает вашу книгу очень медленной. Вместо этого попробуйте применять формулы с одним и тем же диапазоном (=СУММ($A$1:$A$3)). Если вы действительно не можете этого избежать, вам следует искать обходной путь (например, следующий метод № 6).

9. Заменить формулы значениями

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

Обычно рекомендуется настроить одну или две пустые строки поверх данных или расчетов. Вы можете сохранить формулы здесь, и данные ниже не содержат никаких формул, а только значения. Поэтому скопируйте данные (еще с формулами) и вставьте их специальным образом (Ctrl+Alt+v), выбрав «Значения» в специальном окне вставки.

Хотите повысить производительность в Excel?

Получите ленту профессора Excel!

Добавьте в Excel более 120 замечательных функций!

10. Избегайте условного форматирования

Для вычислений форматирование не требуется. Это просто форматирование. Конечно, ваш файл должен выглядеть хорошо, но если вы страдаете от длительного времени вычислений, вам следует избегать условного форматирования. Особенно, если вы используете формулы в условном форматировании. Если вы действительно хотите использовать условные форматы, вам следует подумать о разделении вашего файла на несколько небольших файлов, например, один только для ваших окончательных результатов (см. номер 10). В одном из окончательных результатов — очевидно, без больших вычислений — вы, конечно, можете использовать условное форматирование.

11. Избегайте изменчивых формул

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

  • СЕЙЧАС
  • СЕГОДНЯ
  • РАНД
  • СМЕЩЕНИЕ
  • НЕПРЯМОЙ
  • INFO (в зависимости от аргументов)
  • ЯЧЕЙКА (в зависимости от ее аргументов)

Поскольку эти формулы, особенно ДВССЫЛ и СМЕЩ, очень эффективны, вам следует хорошенько подумать, можно ли их заменить или опустить.

12. Избегайте таблиц данных

Вы не слышали о таблицах данных? Не волнуйтесь, в этом случае вы можете перейти к шагу №. 10. Если вы большой поклонник таблиц данных, вот плохая новость: им нужна высокая производительность. Старайтесь избегать их или, по крайней мере, не используйте их слишком много. Конечно, это отличный метод для имитации различных входных значений, но каждый из них увеличивает время вычисления. Если вы действительно хотите иметь таблицы данных, вы можете переключить режим расчета на «Автоматический, кроме таблиц данных».

13. Разделить книгу на несколько файлов

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

14. Используйте несколько «маленьких» формул

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

  • Намного проще понять этапы расчета, особенно если вы открываете свой файл через некоторое время или кто-то другой пытается работать с вашей книгой.
  • Кроме того, вычисление выполняется немного быстрее.
  • Вы можете заменить формулы некоторых «маленьких» шагов значениями, как описано в пункте 6.

15. Удалить рабочие листы и (форматы) неиспользуемых ячеек

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

Кроме того, поскольку мы уже говорим о неиспользуемых ячейках: Рабочая книга — это предположительно небольшой файл, но он занимает несколько МБ на вашем жестком диске? Это может быть подсказкой, что есть отформатированные ячейки, которые не используются. Просто удалите все строки и столбцы после последних использованных ячеек и посмотрите, уменьшится ли размер файла после сохранения. В то же время это сокращает время расчета.

16. Оптимизировать компьютер

Аналогично пункту 2. Ускорьте работу всего компьютера. Существуют разные методы, самый простой из которых — настроить ваш (Windows-) компьютер на «лучшую производительность» вместо «наилучшего внешнего вида». По общему признанию, особенно в Windows 7, рабочий стол, строки меню и так далее будут выглядеть на 15 лет старше. Выполните шаги, как показано на скриншоте справа:

  1. Откройте панель управления и перейдите в раздел «Система». Оказавшись там, нажмите «Дополнительные параметры системы».
  2. На вкладке "Дополнительно" нажмите "Настройки" в группе "Производительность".
  3. Выберите "Настроить для лучшей производительности".

Производительность и скорость должны возрасти мгновенно.

16. Избегайте больших диапазонов в формулах

Старайтесь устанавливать как можно меньшие диапазоны в формулах и позволяйте Excel использовать только те диапазоны, которые действительно содержат необходимую информацию. Например, если у вас есть поиск (например, «= ВПР»), сведите к минимуму диапазон ячеек, в которых Excel выполняет поиск. Если можете, отсортируйте данные.

17. Переместить данные на один лист

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

Кроме того, изменение порядка листов оказывает небольшое влияние на производительность: Excel выполняет вычисления от начала до конца вашей книги. «Переход» между рабочими листами и рабочими книгами может сократить время вычислений. Наше исследование показывает, что вы можете сократить время расчета на -1%.

Заключение

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

Эти 15 методов должны быть первой ссылкой. Предлагаем вам просмотреть этот список и решить, какой совет подходит именно вам.

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

Дополнительная литература:

Microsoft предоставляет несколько подробных статей о том, как работают вычисления в Excel:

Утром мы приходим в офис с кофе в руке, но мы вялые. Нам кажется, что мир движется в 10 раз медленнее!

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

Фантомные данные

Первое, что вам нужно сделать, чтобы оптимизировать производительность вашего листа, это проверить, есть ли у вас данные в ячейках, которые вы считаете пустыми. Дело в том, что иногда, когда вы заняты этим сверхважным анализом, вы непреднамеренно форматируете ячейки, которые не собирались делать (например, выделяете всю строку), или Excel считывает данные в ячейках, которые раньше содержали данные. Я называю это «фантомными данными»…

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

  • Найти последнюю строку данных
  • Выделите строку под последней строкой данных.
  • Нажмите CTRL + SHIFT + Стрелка вниз.
  • Нажмите CTRL + – (минус)
  • Найти последний столбец данных
  • Выделите столбец после последнего столбца данных.
  • Нажмите CTRL + SHIFT + Стрелка вправо.
  • Нажмите CTRL + – (минус)

Не связывайте формулы последовательно

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

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

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

В следующий раз, когда будете настраивать электронную таблицу, помните об этом. Если вы считаете, что необходимо последовательно связать формулы, рассмотрите возможность использования сводной таблицы для расчета необходимых значений и использования функции ПОЛУЧИТЬ СВОД для доступа к этим значениям.

Избегайте использования изменчивых функций

Слово «изменчивые функции» вызывает в воображении образ песочных часов, которые уходят в небытие.

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

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

  • Удаление/вставка строк или столбцов.
  • Фильтрация/отмена фильтрации.
  • Добавление, редактирование или удаление определенного имени.
  • Переименование листа.
  • Изменение положения листа по отношению к другим листам.
  • Скрытие или отображение строк

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

Итак, каковы примеры изменчивых функций Excel? Вот список:

  • СЛУЧАЙ()
  • СЕЙЧАС()
  • СЕГОДНЯ()
  • СМЕЩ()
  • ЯЧЕЙКА()
  • ДВССЫЛ()
  • ИНФО()
  • СЛУЧМЕЖДУ()

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

Например: вместо использования СЕГОДНЯ() рассмотрите возможность использования ярлыка Excel для ввода статических даты и времени:

Подумайте об обновлении Excel до 64-разрядной версии

Excel в настоящее время (версии после 2010 г.) поставляется в двух разных версиях; 32-битные и 64-битные. Одно из основных различий между ними заключается в том, что 64-разрядная версия в два раза больше способна обрабатывать данные, чем 32-разрядная версия. Обычно это означает, что ваши электронные таблицы будут загружаться и работать быстрее, особенно если вы привыкли работать с большими файлами данных. По моему опыту, 32-разрядная версия Excel обычно начинает испытывать проблемы с производительностью, когда размер файла превышает 20 МБ. Обновление может существенно повысить производительность.

Однако есть одна загвоздка…

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

Как узнать, какую версию Excel вы используете?

  1. Открыть Excel
  2. Выберите «Файл» в верхнем левом углу, а затем выберите «Учетная запись» или «Справка» в списке слева.
  3. Нажмите "О Excel".
  4. Здесь вы сможете увидеть, какую версию Excel вы используете.

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

Постарайтесь избегать формул массива

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

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

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

Использование таблиц Excel

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

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

Итак, в следующий раз, когда вы будете сидеть и смотреть на этот экран…

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

Читайте также: