Remkomplekty.ru

IT Новости из мира ПК
22 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как ускорить работу файла excel

Почему тормозит Excel, 12 способов улучшить работу с файлами!

Добрый день уважаемый читатель!

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

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

Проблемы с «тормознутостью» MS Office это довольно распространённое явление в работе Excel. Проблемная работа Excel может возникать по многим причинам: излишнее условное форматирование, чрезмерное использование функций Excel, созданные из больших массивов данных сводные таблицы, дополнительные и ненужные строки и столбцы в таблицах, и многое другое.

А теперь перейдем от теории к действию и рассмотрим 12 способов, которые позволит вам, избавится от «тормозов» Excel и начать комфортно работать:

1. Уменьшаем размеры картинок

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

Когда вы добавляли картинку через меню на панели управления «Вставка» в блоке «Иллюстрации» нажимали «Рисунок», тогда в файл вшивался рисунок с полным объёмом. А вот теперь необходимо внести изменения, установите курсор на картинку, появится новая вкладка «Работа с рисунками» с под вкладкой «Формат». В блоке «Изменения» кликаете на кнопку «Сжать рисунки». В диалоговом окне «Сжатие рисунков» указываете подходящий вам вариант уменьшения размера и нажимаете «Ок». Не стоит забывать о том, что картинки могут скрываться и в примечаниях.

2. Заменим макросами большие формулы

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

3. Убрать форматирование таблиц Excel

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

Определить как именно отформатированная таблица можно просто, установив курсор на абсолютно любую ячейку, появится дополнительная вкладка в панели управления «Работа с таблицами». Выбираете вкладку «Конструктор», ищете раздел «Сервис», нажимаете кнопку «Преобразовать в диапазон» и подтверждаете, что вам нужно преобразовать вашу таблицу в обычную.

4. Удаляем ненужные столбцы и строки

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

Проверить такой вариант возможно по характерному признаку, это очень маленький ползунок.

5. Заменить формат рабочей книги на *.xlsb

В случаях, когда ваша работа связана с огромными таблицами и их размер достигает больших объёмов, то следует сохранять рабочие книги в формате *.xlsb. Это расширение хранится как двоичный формат, своеобразный специальный формат для создания и хранения ваших «баз данных» имеющих в основе электронные таблицы.

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

6. Убираем чрезмерное условное форматирование

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

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

7. Удаляем ненужные данных внутри структуры файла Excel

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

Для проведения процедуры по уменьшению размера, перестрахуйтесь, и сделайте резервное копирование вашего файла. Затем откройте файл с помощью контекстного меню, открываем файл, нажав на мышке правую кнопку и выбрав пункт «Открыть с помощью» и с доступных программ выбираете программу архиватор. Или, второй вариант, открываете архиватор и в меню «Файл» кликаете на пункт «Открыть внутри». Результатом любого из вариантов будет открытие файла Excel, как архива с файлами и папками.

В архиве, скорее всего в папке «xl», удалите папки «printerSettings» и «drawings». После всех мучений запускаем снова файл и на все системные возмущение соглашаемся кнопкой «Ок», файл восстановиться и запустится.

Обращаю ваше внимание, что если файл содержит рисованные объекты типа кнопок или фигур то удалять всю папку «drawings» не стоит, вместе с ней испарятся и фигурки. Достаточно в папке удалить vmlDrawing.vml который вмещает разнообразную информацию и может достигать больших размеров.

8. Неправильно настроенная печать на принтер

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

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

9. Сменить версию Excel на более позднюю

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

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

Усовершенствуйте версию, переходите на более продуктивные продукты MS Office, желательно от 2013 версии и выше.

10. Сносим файл «Personal.*»

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

Проблемы могут возникать, если начнет, тормозит файл персональных настроек. Для устранения проблемы, воспользуйтесь принципом, нет файла, нет проблем. Найдите С:. Application DataMicrosoftExcelXLSTART или С:. Microsoft OfficeOffice12 и где то в недрах этих папок ищете файл «Personal.*» и сносите его.

11. Производим оптимальную настройку сводных таблиц

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

Читать еще:  Excel rows count

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

12. Убираем ненужные объекты

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

Для удаления таких ненужных объектов необходимо запустить макрос, кликните горячую комбинацию клавиш Alt + F11 для вызова редактора и вставьте код:

Как ускорить работу в Excel в 3-5 раз

Как ускорить работу в Excel в 3-5 раз

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

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

Мазок, ещё мазок. Проходит несколько часов и. Пару небольших табличек и график с стандартным оформлением. Грустно не так ли?

И у меня такое бывало. Работаешь, работаешь. Уже вымотался, а работа только начата. Что же с этим делать?

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

Интересно? Тогда поехали!

Совет по Excel номер РАЗ. Мышка

Первое что нужно сделать — выкинуть мышку!

Именно так вы всё верно прочитали. Чтобы работать быстрее в Excel нужно убрать мышку. ТОЛЬКО не спешите закрывать вкладку, сейчас всё станет понятно.

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

Всё дело в горячих клавишах которые позволяют просто молниеносно переключаться между страницами, выделять, добавлять или удалять стройки/столбцы. В общем знание буквально 5-7 комбинаций ускоряет работу на порядок. Многие навигационные операции с помощью мыши делаются в 3-5, а то и 10 раз дольше.

Приведу пример. Чтобы добавить несколько строк в Excel есть три варианта:

  1. Вариант 1. Взять мышь, навести её на соответствующий столбец или несколько столбцов в зависимости того сколько столбцов нужно вставить. Нажать правой кнопкой, в выпадающем окошке выбрать вставить.
  2. Вариант 2. С помощью кнопок на вкладке Главная в группе ячейки. Опять же выделяем, идем на вкладку Главная. Щелкаем по соответствующим кнопкам для получения результата.
  3. Вариант 3. Для выделения столбца нажать Ctrl + ‘Пробел’, для добавления столбца нажать Ctrl + ‘+’.

Вы можете сказать, что экономия не велика. Одним способом я сделаю за 15-20 секунд, другим за 3-5. И что я буду делать с освободившимися 12-15 секундами? И вообще сколько нужно выучить горячих клавиш чтобы почувствовать разницу?

Согласен! Время, сэкономленное одной горячей клавишей не велико, но вся наша работа в Excel — это монотонное повторение одних и тех же операций по сотне раз в разных последовательностях. Значит если условная экономия на одной операции предположим 10 секунд. При повторении одной операции 20-30 раз в день экономия от знания одной комбинации горячих клавиш — 3-5 минут. Значит знание 10 горячих клавиш экономит 30-50 минут времени. А вот это уже круто.

Совет по Excel номер ДВА. Горячие клавиши

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

«Хорошенький совет!» — возразите вы. — «И как их учить? Становиться на табуретку перед коллегами и рассказывать как стихи?» 🙂

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

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

Ну и конечно же вот вам список полезных быстрых и горячих клавиш:

Вычисления
  • SHIFT+F3 Мастер Функций
  • F4 Переключение между относительными и абсолютными ссылками при вводе формулы
  • CTRL+

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

  • ALT+= Вставить функцию автосуммы
  • F9 Пересчет формул во всей книге
  • SHIFT+F9 Пересчет формул на текущем листе
  • Редактирование
    • F2 Редактировать активную ячейку
    • SHIFT+F2 Редактировать примечание к ячейке
    • CTRL+Delete Удаляет текст от курсора до конца строки (при редактировании содержимого ячейки)
    • CTRL+ALT+V Специальная вставка (после копирования)
    • CTRL+ плюс Добавит выделенное кол-во строк или столбцов
    • CTRL+минус Удалит выделенные строки или столбцы
    • CTRL+D Заполняет вниз выделенный диапазон значениями/формулами из первой выделенной ячейки
    • CTRL+R Заполняет вправо выделенный диапазон значениями/формулами из первой выделенной ячейки
    • CTRL+H Диалог «Найти и заменить»
    • SHIFT+F11 Вставить пустой лист
    • CTRL+Z Отменить последнее действие
    • CTRL+Y Повторить отмененное действие
    • ALT+SHIFT+→ Сгруппировать выделенные строки/столбцы
    • ALT+SHIFT+← Разгруппировать выделенные строки/столбцы
    Форматирование
    • CTRL+1 Диалог «Формат ячеек»
    • CTRL+SHIFT+

    Общий формат

  • CTRL+SHIFT+$ Денежный формат
  • CTRL+SHIFT+% Процентный формат
  • CTRL+SHIFT+# Формат даты
  • CTRL+SHIFT+@ Формат времени
  • CTRL+SHIFT+! Числовой формат
  • CTRL+B Полужирный
  • CTRL+I Курсив
  • CTRL+U Подчеркивание
  • CTRL+5 Зачеркнутый текст
  • CTRL+SHIFT+& Включить внешние границы ячеек
  • CTRL+SHIFT+_ Выключить все границы ячеекF2 Редактировать активную ячейку
  • Ввод данных
    • CTRL+Enter Ввод сразу во все выделенные ячейки
    • ALT+Enter Перенос строки внутри ячейки (при вводе)
    • CTRL+SHIFT+4 Вставить текущую дату
    • CTRL+SHIFT+; Вставить текущее время
    • ALT+↓ Открыть выпадающий список (в пустой ячейке под столбцом с данными)
    Навигация
    • CTRL+Backspace Вернуться к активной ячейке (прокрутка)
    • CTRL+PgUp/PgDown Переключение между листами
    • CTRL+Tab Переключение между открытыми книгами
    • CTRL+пробел Выделить весь столбец
    • SHIFT+пробел Выделить всю строку
    • ALT+; Выделить только видимые ячейки в диапазоне
    • CTRL+A Выделить текущую область (до ближайших пустых строк и столбцов)
    • CTRL+End Перейти к последней ячейке листа
    • CTRL+SHIFT+End Выделить все до последней ячейки листа
    • CTRL+стрелки Перейти до конца/начала строки/столбца
    • CTRL+SHIFT+стрелки Выделить до конца/начала строки/столбца
    Работа с файлами
    • CTRL+N Создать новую пустую книгу
    • CTRL+S Сохранить текущую книгу
    • CTRL+O Открыть файл
    Всякое разное
    • CTRL+L Создание умной таблицы (с фильтрами, автоподстройкой размеров, дизайном, закреплением шапки и т.д.)
    • CTRL+F2 Предварительный просмотр
    • F4 Повтор последнего действия (любого, если вообще возможен)
    • CTRL+K Вставить гиперссылку
    • CTRL+F3 Диспетчер имен
    • ALT+F8 Открыть диалоговое окно макросов

    Горячие клавиши уж точно помогут вам рабоатать быстрее в Excel. Трениуртесь в их использовании и очень быстро скорость вашей работы существенно возрастёт. Но это ещё не всё!

    У меня в запасе есть ещё несколько советов. Готовы? Поехали!

    Совет по Excel номер ТРИ. Функции и их комбинации, которые обязатлеьно освоить

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

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

    =СУММ(Лист1!А1; Лист2!А1; Лист3!А1; Лист4!А1; Лист5!А1; Лист6!А1; Лист7!А1; . ЛистN!A1)

    Формула будет выглядеть как

    =СУММ(Лист1:ЛистN!А1)

    Всё это к чему? Помимо богатого функционала Excel, который есть в его стандартных формулах, существует множество комбинаций, знание которых позволяет решать и не стандартные задачи. Так, у Excel совсем нет функции МИНЕСЛИ. Да, есть СУММЕСЛИ, СЧЁТЕСЛИ, а МИНЕСЛИ не сделали. Также МАКСЕСЛИ, МЕДИАНАЕСЛИ и т.п., но при этом всё это решается путем использования функций областей. Возможно вы видели, когда формула облачается в фигурные скобки.

    Некоторые функции отлично работают только в связке. Это я сейчас говорю о ИНДЕКС и ПОИСКПОЗ. Казалось бы, бестолковые функции по отдельности, но в паре дают отличный функционал.

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

    Что для этого нужно? Например, подписаться на нашу группу в Facebook и ждать выхода новых постов.

    Также посмотрите список формул, на которые я рекомендую вам обратить внимание:

    8 простых способов ускорить работу в Excel

    А вы знаете, как быстро выделить всю строку или столбец, вписать данные внутри ячейки в несколько строк, добавить комментарий, вставить текущую дату? Сегодня мы раскрываем секреты работы в Excel.

    1. Как быстро подогнать размер столбцов под ширину содержимого

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

    2. Как быстро выделить всю строку или столбец

    При работе с большими объёмами данных невозможно обойтись без клавиатурных сокращений, с помощью которых легко перемещаться по листу и выделять отдельные столбцы или строки. Комбинации Ctrl + Spacebar и Shift + Spacebar как раз и отвечают за быстрое выделение текущего столбца или строки.

    3. Как быстро выделить диапазон данных

    Однажды я наблюдал, как четырёхлетний ребёнок использовал горячие клавиши копирования и вставки для ввода ключа к игре. Похоже, неразлучная троица Ctrl + C, Ctrl + V и Ctrl + A впитывается молодым поколением вместе с молоком матери. К слову, команда «Выделить всё» в Excel имеет небольшую особенность: одиночное нажатие Ctrl + A выделяет текущий диапазон, а двойное — таблицу целиком.

    4. Как внести изменения на нескольких листах одновременно

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

    5. Как перенести строку в текущей ячейке

    Хочешь поиздеваться над новичком в Excel — попроси его вписать данные внутри ячейки в несколько строк. Конечно, школьный трюк, но со старшим поколением работает на ура. Комбинация Alt + Enter предотвращает переход к другой ячейке и переводит курсор на новую строку в пределах текущей ячейки.

    6. Как вставить сразу несколько столбцов или строк

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

    7. Как быстро вставить текущую дату

    Судя по всему, горячие клавиши Ctrl + ; и Ctrl + Shift + ; нужно крутить по радио вместо утреннего гимна. Эти комбинации стоит повторять до той поры, пока на планете не останется того трудяги, который вбивает дату и время вручную.

    8. Как быстро добавить комментарий

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

    Как уменьшить размер файла Excel и заставить его работать быстрее

    Каждый, кто часто имеет дело с Excel и созданными в нем документами, рано или поздно сталкивается с проблемой медленной работы файла. «Файл тормозит, что делать?»

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

    1. Уменьшаем размер используемого диапазона листа

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

    Чтобы проверить, есть ли на листе лишние пустые столбцы и строки нужно нажать сочетание клавиш «Ctrl+End» . Вы попадете в последнюю ячейку, которую использует программа. Если она явно за пределами ваших данных, то лишние строки и столбцы стоит удалить. Для этого в столбце А встаем в ячейку ниже последней нужной нам строки и нажимаем «Ctrl+Shift+End» .

    Выделятся все лишние строки. Удаляем их. То же самое повторяем для столбцов. Если их много – файл может зависнуть. В таком случае проще использовать небольшой макрос. Нажмите Alt+F11 или кнопку Visual Basic на вкладке Разработчик (как ее активировать — показывали здесь ), вставьте новый модуль через меню Insert — Module и скопируйте туда код макроса:

    For Each Sht In ActiveWorkbook.Worksheets

    Чтобы запустить его — на той же вкладке (1) нажмите кнопку «Макросы» (2), выберите в списке «УдалениеЛишнихЯчеек» (3) и нажмите кнопку «Выполнить» (4).

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

    2. Пересохраняем файл в другом формате

    Если кто-то еще пользуется файлами в старом формате XLS, но уже сидит на более новом ПО (Excel 2007 и новее), то есть смысл пересохранить файл в один из новых форматов: XLSX, XLSM, XLSB. Они более современные, лучше оптимизированы, весят меньше и работают быстрее. Самый компактный из них – XSLB. При сохранении в этом формате размер файла существенно уменьшится (даже если пересохранить в него «новый» XLSX).

    3. Удаляем лишние объекты из книги

    Часто при копировании в файл данных из сторонних программ (например, 1С) вместе с ними копируются лишние объекты (фигуры, картинки и прочее), которые не всегда можно сразу заметить на листе. Чтобы проверить, есть ли в файле лишние объекты, нужно найти на ленте команду «Найти и выделить» (1) и выбрать «Область выделения» (2). Откроется список объектов листа (3).

    Удалить все объекты можно выделив их и нажав клавишу Delete . Чтобы выделить все объекты снова используем команду «Найти и выделить» (1), выбираем пункт «Выделить группу ячеек» (2) и в открывшемся окне выбираем «Объекты» (3).

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

    For each shp in ActiveSheet.Shapes

    4. Уменьшаем размер сводных таблиц

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

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

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

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

    6. Удаляем лишнее форматирование

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

    Чтобы удалить лишние правила условного форматирования выбираем на вкладке «Главная» инструмент «Условное форматирование», кнопка «Управление правилами». В открывшемся диспетчере выбираем весь лист (1), выделяем лишнее правило (2) и удаляем его (3). Повторяем, пока не удалим всё лишнее.

    И помните — никогда не используйте формат целиком для всего столбца или строки!

    7. Удаляем лишние имена

    Именованные диапазоны используют далеко не все, но обнаружить их в своем файле может каждый (например, они могли попасть туда при импорте или копировании данных). Весь список можно увидеть в диспетчере имен («Формулы» — «Диспетчер имен»). Там же можно и удалить все ненужные имена.

    Иногда в книге могут оказаться скрытые имена. Для их удаления придется воспользоваться небольшим макросом.

    For Each Nm In ActiveWorkbook.Names

    If Nm.Visible = 0 Then

    Заключение

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

    Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram :

    Как ускорить работу файла excel

    Всем привет и хорошего настроения!

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

    10 СОВЕТОВ КАК УЛУЧШИТЬ РАБОТУ ФОРМУЛ В EXCEL

    1). Используйте таблицы для хранения данных.

    Начиная с Excel 2007 можно держать связанные друг с другом данные в таблицах (Преобразование данных: «Главная» — «Стили» — «Форматировать как таблицу»). К данным в таблицах можно обращаться формулами с помощью структурных ссылок. Также данные таких таблиц можно использовать как источник для сводных таблиц. Если Вы меняете количество строк в таблице — формулы по столбцам автоматически становятся динамическими. Например, если у Вас есть таблица «Sales», то можно собрать сумму по столбцу «Turn_Over» просто введя формулу:

    Формула будет работать корректно даже если в таблице уменьшилось или увеличилось количество строк.

    На заметку: в 2003-м Excel есть аналог таблиц (Exel lists).

    2). Используйте именованные диапазоны и формулы.

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

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

    Бывают случаи, когда формулы нам в принципе не нужны, но мы их используем просто потому, что можем. Но разве не проще использовать сводную таблицу, где множество итогов можно получить всего в несколько кликов мышью? После построения сводной таблицы, можно ссылаться на её значения с помощью формулы ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ или простыми ссылками на её ячейки. Это избавит от необходимости выполнять необязательные вычисления. Если данные меняются, достаточно просто перейти на вкладку «Данные» и обновить все сводные в одно действие. Обновление пройдёт лучше, если исходные данные хранятся в формате таблиц.

    Часто причиной медленной работы книги Excel бывает большой объём данных. И сводные таблицы были разработаны именно для того, чтобы обрабатывать большие массивы информации.

    4). Сортируйте данные.

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

    5). Вычисление книги вручную.

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

    6). Старайтесь отказываться от волатильных формул.

    В Excel существует особый класс формул — волатильные формулы. Они пересчитываются при ЛЮБОМ изменении в книге. Примером волатильных формул служат: СЛЧИС, СЕГОДНЯ, ТДАТА, СМЕЩ и т.д. И если лист содержит множество волатильных формул, при малейшем изменении эти формулы должны быть пересчитаны. Что сверх необходимого нагружает лист вычислениями.

    Что делать? Просто откажитесь от волатильных формул. Например, чем использовать СМЕЩ для построения динамического диапазона — задействуйте ИНДЕКС. Как НЕ волатильная формула он должен работать быстрее. А ещё лучше — используйте таблицы.

    7). Держите формулы на отдельном листе.

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

    8). Пишите хорошие формулы.

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

    — Встроенные формулы удобнее Ваших собственных. Например, СУММЕСЛИМН проще заполняется аргументами и такая же быстрая, как СУММПРОИЗВ.
    — Не тратьте силы Excel на работу с целым столбцом, когда требуется всего несколько значений. Не стоит писать СУММ(A:A), когда заведомо известно, что значения будут в диапазоне А1:А10.
    — Используйте ЕСЛИОШИБКА для обработки ошибок вместо усложненных конструкций типа ЕСЛИ(ЕОШИБКА())
    — Уберите или исправьте ошибки формул (деление на ноль, ошибки имени и т.д.).
    — Уберите или сократите использование массивных формул.
    — Удалите совсем или уменьшите количество ссылок на другие книги.
    — Удалите любые именованные диапазоны, которые выдают ошибку или несуществующие ссылки.
    — Старайтесь использовать альтернативные формулы. Это не только отточит ум, но и позволит находить интересные решения.
    — Не рассчитывайте цифры, которые Вам не нужны.
    — Не считайте что-либо дважды, а лучше используйте первый результат во втором вычислении.

    9). Суровые времена требуют суровых мер.

    Иногда, что бы Вы не делали, скорость книги всё равно оставляет желать лучшего. Ниже несколько жестких решений:

    — Замена формул на значения. Сохраните формулы отдельно (или только в первой строке данных), Ctrl+C, Ctrl+Alt+V, только значения и форматы чисел.
    — Сборка книги с нуля. Позволяет выбросить всё лишнее, о чём даже нельзя было догадаться.
    — Замените внешние ссылки на данные собственно актуальными данными. При необходимости импортируйте данные методом копи-пасты.
    — Уменьшите функциональность. Ведь пользователь наверняка как-нибудь проживет без пары-другой фишек, верно?
    — Найдите альтернативное решение. Пытаться сделать в Excel абсолютно всё — глупо. Зачем копать молотком, если есть лопата? Проверьте, вдруг есть какой другой инструмент, который делает то же самое лучше и быстрее.

    10). Узнавайте новые формулы и играйте с ними.

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

    Ссылка на основную публикацию
    Adblock
    detector