✅ 10 хитростей, чтобы исправить медленные таблицы Excel (ускорение Excel)

Содержание

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

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

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

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

Хорошо, это НЕТ абсолютно верно.

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

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

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

Избегайте нестабильных функций (вы должны).
Используйте вспомогательные столбцы.
Избегайте формул массива (если можете).
Осторожно используйте условное форматирование.
Используйте таблицы Excel и именованные диапазоны.
Преобразуйте неиспользуемые формулы в значения.
Храните все ссылочные данные на одном листе.
Избегайте использования всей строки / столбца в ссылках.
Используйте ручной режим расчета.
Используйте более быстрые методы формул.

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

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

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

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

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

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

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

НЕ ДЕЛАЙТЕ этого.

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

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

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

Автоматическая сортировка данных в алфавитном порядке с помощью формул.
Динамический фильтр Excel - извлекайте данные по мере ввода.
Создание нескольких раскрывающихся списков в Excel без повторения.

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

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

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

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

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

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

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

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

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

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

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

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

Это также дает дополнительное преимущество, делая ваши формулы более понятными.

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

= Продажная цена-себестоимость ИЛИ = СУММ (A1: A10) -SUM (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) - это классный совет, который я узнал от мистера Excel, также известного как Билл Джелен. Его исследование показывает, что опция MAX на 40% быстрее, чем опция IF (и я готов принять этот показатель за его номинальную стоимость).
Используйте комбинацию ИНДЕКС / ПОИСКПОЗ вместо ВПР - это может вызвать недоумение, но правда в том, что ВПР не может быть быстрее, если у вас есть 100 столбцов данных. Мир движется в направлении ИНДЕКС / МАТЧ, и вам тоже следует сделать это.
[Если вы все еще не знаете, что использовать, вот прямое сравнение VLOOKUP Vs. ИНДЕКС / МАТЧ].
Используйте - (двойное отрицание), чтобы преобразовать ИСТИНА и ЛОЖЬ в 1 и 0 (вместо умножения на 1 или добавления к нему 0). Улучшение скорости заметно на больших наборах данных.

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

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

75 советов по ускорению от Chandoo (грамотно сделано краудсорсингом).
Веб-сайт моделей решений.
Доска сообщений Mr. Excel (изучите это, и вы найдете массу советов).

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

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

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