Как очистить рабочую книгу Excel, чтобы она использовала меньше памяти
После обновления до Office 2013/2016/Microsoft 365 появляется один или несколько следующих симптомов:
- Компьютер использует больше памяти, когда вы открываете несколько рабочих книг Microsoft Excel 2013, сохраняете их или выполняете вычисления в них.
- Вы больше не можете открыть столько рабочих книг Excel в одном экземпляре, сколько было до обновления до Excel 2013/2016.
- При вставке столбцов в рабочую книгу Excel возникает ошибка касательно доступной памяти.
- При работе с электронной таблицей Excel появляется следующее сообщение об ошибке:
There isn't enough memory to complete this action. Try using less data or closing other applications. To increase memory availability, consider: - Using a 64-bit version of Microsoft Excel. - Adding memory to your device. 
Причина
Начиная с Excel 2013, были внесены улучшения, которые требуют больше системных ресурсов, чем требовалось в предыдущих версиях. В этой статье определяются области в рабочих книгах Excel, которые используют много памяти, и описывается, как можно повысить эффективность работы с файлами рабочих книг.
Дополнительные сведения об изменениях, которые мы внесли в Excel 2013, см. в разделе Использование памяти в 32-разрядной версии Excel 2013.
Решение
Чтобы решить эту проблему, воспользуйтесь приведенными ниже способами в указанном порядке. Если один из этих способов не помогает, переходите к следующему способу.
Многие рабочие книги Excel имеют ряд проблем, которые могут вызвать затруднения. После устранения этих проблем ваша рабочая книга будет работать более эффективно.
Рекомендации по форматированию
Форматирование может привести к тому, что рабочие книги Excel станут настолько большими, что будут работать некорректно. Часто Excel зависает или аварийно завершает работу из-за проблем с форматированием.
Способ 1: устранение чрезмерного форматирования
Чрезмерное форматирование в рабочей книге Excel может привести к расширению файла и ухудшению производительности. Форматирование будет считаться чрезмерным если отформатировать целые столбцы или строки с помощью цвета или границ. Эта проблема также возникает, когда форматирование требует копирования или импорта данных с веб-страниц или баз данных. Чтобы устранить избыточное форматирование, используйте надстройку для очистки формата, которая доступна в разделе Очистка избыточного форматирования ячеек в рабочем листе.
Если после устранения избыточного форматирования у вас по-прежнему возникают проблемы, перейдите к способу 2.
Способ 2: удалите неиспользуемые стили
Вы можете использовать стили для стандартизации форматов, которые вы используете во всех рабочих книгах. Когда ячейки копируются из одной рабочей книги в другую, их стили также копируются. Эти стили продолжают увеличивать файл и в конечном итоге могут привести к появлению сообщения об ошибке «Слишком много различных форматов ячеек» в Excel при сохранении в старых версиях файла.
Существует множество утилит, которые удаляют неиспользуемые стили. Если вы используете книгу Excel на основе XML (то есть .xlsx или XLSM-файл), вы можете использовать средство очистки стиля. Вы можете найти этот инструмент здесь.
Если после удаления неиспользуемых стилей у вас по-прежнему возникают проблемы, перейдите к способу 3.
Способ 3: удаление фигур
Добавление большого количества фигур в электронную таблицу также требует много памяти. Фигура определяется как любой объект, который располагается в таблице Excel. Ниже приведены некоторые примеры:
- Диаграммы
- Рисование фигур
- Comments
- Клипарт
- SmartArt
- Изображения
- WordArt
Часто эти объекты копируются с веб-страниц или других рабочих листов, скрываются или располагаются друг на друге. Зачастую пользователь не подозревает об их присутствии.
Для проверки наличия фигур выполните указанные ниже действия:
- На домашней ленте нажмите Найти и выделить, а затем нажмите Область выделения.
- Нажмите Фигуры на этом листе. Фигуры отображаются в списке.
- Удалите ненужные фигуры. (Значок в виде глаза указывает, видна ли фигура).
- Повторите шаги от 1 до 3 для каждого листа.
Если проблема продолжает возникать после удаления фигур, следует изучить рекомендации, не связанные с форматированием.
Метод 4. Удаление условного форматирования
Условное форматирование может привести к увеличению размера файла. Это происходит в том случае, если условное форматирование в файле повреждено. Вы можете удалить условное форматирование, чтобы проверить, не связана ли проблема с повреждением в форматировании. Чтобы удалить условное форматирование, выполните следующие действия:
- Сохраните резервную копию файла.
- На домашней ленте щелкните Условное форматирование.
- Удалите правила со всего листа.
- Выполните шаги 2 и 3 для каждого листа в книге.
- Сохраните книгу под другим именем.
- Убедитесь в том, что проблема устранена.
Если удаление условного форматирования позволило устранить проблему, откройте исходную книгу, удалите условное форматирование и повторно примените его.
Проблема не устранена?
Если ни один из этих методов не помогает, вы можете перейти на 64-битную версию Excel, разбить проблемную книгу на различные книги или обратиться в службу поддержки для получения дополнительных сведений по устранению неполадок.
Рекомендации, связанные с вычислениями
Помимо форматирования сбой и зависание в Excel также могут вызывать вычисления.
Метод 1. Открытие книги в последней версии Excel
Первое открытие книги Excel в новой версии Excel может занять много времени, если книга содержит множество вычислений. При первом открытии книги Excel должен пересчитать книгу и проверить значения в книге. Дополнительные сведения см. в следующих статьях:
- Книга загружается медленно при первом открытии в Excel
- Внешние ссылки могут обновляться при открытии книги, которая была сохранена в предыдущей версии Excel
Если файл продолжает медленно открываться после того, как Excel полностью пересчитает файл и вы сохраните его, перейдите к методу 2.
Метод 2. Формулы
Просмотрите книгу и изучите типы формул, которые вы используете. Для некоторых формул требуется много памяти. К ним относятся следующие формулы с массивами:
Их можно использовать. Однако следует помнить о диапазонах, на которые вы ссылаетесь.
Формулы, которые ссылаются на целые столбцы, могут привести к низкой производительности файлов .xlsx. Размер сетки увеличился с 65 536 до 1 048 576 строк и с 256 (IV) до 16 384 столбцов (XFD). Популярный (однако не самый лучший) способ создания формул заключался в том, чтобы ссылаться на целые столбцы. Если вы ссылались только на один столбец в старой версии, то были включены только 65 536 ячеек. В новой версии вы ссылаетесь более чем на 1 миллион столбцов.
Предположим, что вы используете следующую функцию ВПР:
=VLOOKUP(A1,$D:$M,2,FALSE) В Excel 2003 и более ранних версиях данная функция ВПР ссылалась на целую строку, которая включала только 655 560 ячеек (10 столбцов x 65 536 строк). Однако с новой, более крупной сеткой эта же формула ссылается почти на 10,5 миллиона ячеек (10 столбцов x 1 048 576 строк = 10 485 760).
Это было исправлено в Office 2016/365 версии 1708 16.0.8431.2079 и более поздних. Информацию о том, как обновить Office, см. в разделе Установка обновлений Office.
В более ранних версиях Office вам может потребоваться перестроить формулы, чтобы они ссылались только на те ячейки, которые необходимы для них.
Проверьте определенные имена, чтобы убедиться, что у вас нет других формул, которые ссылаются на целые столбцы или строки.
Этот сценарий также возникнет при использовании целых строк.
Если после изменения формул вы продолжаете испытывать проблемы, ссылаясь только на используемые ячейки, переходите к способу 3.
Способ 3: вычисление по всем рабочим книгам
Ограничьте формулы, выполняющие вычисления в разных рабочих книгах. Это важно по двум причинам:
- Вы пытаетесь открыть файл по сети.
- Excel пытается вычислить большие объемы данных.
Вместо того чтобы выполнять вычисления в разных сетях, поместите формулу в одну рабочую книгу, а затем создайте простую ссылку из одной рабочей книги в другую.
Если вы продолжаете испытывать проблему после того, как изменили формулы, чтобы они ссылались только на ячейки, а не вычислялись по всей рабочей книге, перейдите к способу 4.
Способ 4: переменные функции
Ограничьте использование переменных функций в рабочей книге. Вам не нужно иметь сотни ячеек, использующих функцию TODAY или NOW. Если вам необходимо указать текущую дату и время в электронной таблице, используйте функцию один раз, а затем ссылайтесь на нее через определенное имя ссылки.
Если вы продолжаете испытывать проблему после ограничения переменных формул, переходите к способу 5.
Способ 5: формулы массива
Формулы массивов очень мощные. Но их нужно использовать правильно. Важно не добавлять в массив больше ячеек, чем необходимо. Когда ячейка в массиве содержит формулу, требующую вычисления, вычисление происходит для всех ячеек, на которые ссылается эта формула.
Более подробную информацию о том, как работают массивы, можно найти в разделе Производительность Excel 2010: советы по оптимизации препятствий производительности.
Если после обновления формул массива проблема не будет устранена, перейдите к способу 6.
Способ 6: определенные имена
Определенные имена используются для ссылок на ячейки и формулы во всей рабочей книге, чтобы добавить «дружественное имя» к формулам. Необходимо проверить наличие определенных имен, которые ссылаются на другие рабочие книги или временные файлы Интернета. Как правило, эти ссылки излишни и замедляют открытие рабочей книги Excel.
Вы можете использовать инструмент Диспетчер имен для просмотра скрытых определенных имен, которые нельзя увидеть в интерфейсе Excel. Этот инструмент позволяет просматривать и удалять ненужные имена.
Если после удаления ненужных определенных имен Excel продолжает аварийно завершать работу, перейдите к способу 7.
Способ 7: ссылки и гиперссылки
Преимущество Excel заключается в его способности получать данные из других электронных таблиц в реальном времени. Проведите инвентаризацию файла и внешних файлов, на которые он ссылается. В Excel нет ограничений на количество рабочих книг, которые можно связать, хотя существует несколько проблем, с которыми вы можете столкнуться. Протестируйте файл без ссылок, чтобы определить, в чем проблема – в этом файле или в одном из связанных файлов.
Продолжаем
Это наиболее распространенные проблемы, которые вызывают зависание и сбои в Excel. Если вы по-прежнему сталкиваетесь со сбоями и зависаниями в Excel, вам следует рассмотреть возможность открытия запроса в службу поддержки Microsoft.
Дополнительная информация
Если ни один из этих методов не помог, вам следует рассмотреть возможность перехода на 64-разрядную версию Excel или разбить проблемную рабочую книгу на разные рабочие книги.
Удаление (скрытие) строк по условию
макрос удалит на листе все строки, в которых содержится искомый текст:
Sub УдалениеСтрокПоУсловию() Dim ra As Range, delra As Range, ТекстДляПоиска As String Application.ScreenUpdating = False ' отключаем обновление экрана ТекстДляПоиска = "Наименование ценности" ' удаляем строки с таким текстом ' перебираем все строки в используемом диапазоне листа For Each ra In ActiveSheet.UsedRange.Rows ' если в строке найден искомый текст If Not ra.Find(ТекстДляПоиска, , xlValues, xlPart) Is Nothing Then ' добавляем строку в диапазон для удаления If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra) End If Next ' если подходящие строки найдены - удаляем их If Not delra Is Nothing Then delra.EntireRow.Delete End Sub
Чтобы вместо удаления просто скрыть такие строки, замените строку
If Not delra Is Nothing Then delra.EntireRow.Delete
If Not delra Is Nothing Then delra.EntireRow.Hidden=TRUE
Расширенная версия этого макроса — с использованием UserForm для ввода искомого значения
Function ПоискСтрокПоУсловию(ByVal ТекстДляПоиска As String, Optional HideOnly As Boolean) As Long ' функция получает в качестве параметра ТекстДляПоиска (можно использовать символы * и ?) ' Если HideOnly = TRUE, то строки, содержащие в ячейках ТекстДляПоиска, скрываются, ' иначе (HideOnly = FALSE - по умолчанию) - удаляются ' Функция возвращает количество удалённых строк Dim ra As Range, delra As Range Application.ScreenUpdating = False ' отключаем обновление экрана ' перебираем все строки в используемом диапазоне листа For Each ra In ActiveSheet.UsedRange.Rows ' если в строке найден искомый текст If Not ra.Find(ТекстДляПоиска, , xlValues, xlPart) Is Nothing Then ' добавляем строку в диапазон для удаления If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra) End If Next On Error Resume Next: ПоискСтрокПоУсловию = delra.Areas.Count ' количество найденных строк If Not delra Is Nothing Then ' если подходящие строки найдены - скрываем или удаляем их If HideOnly Then delra.EntireRow.Hidden = True Else delra.EntireRow.Delete End If End Function
Ещё один вариант кода, позволяющего выполнять поиск (с последующим удалением или скрытием строк) сразу по нескольким условиям:
Sub УдалениеСтрокПоНесколькимУсловиям() Dim ra As Range, delra As Range Application.ScreenUpdating = False ' отключаем обновление экрана ' ищем и удаляем строки, содержащие заданный текст ' (можно указать сколько угодно значений, и использовать подстановочные знаки) УдалятьСтрокиСТекстом = Array("Наименование *", "Количество", _ "текст?", "цен*сти", "*78*") ' перебираем все строки в используемом диапазоне листа For Each ra In ActiveSheet.UsedRange.Rows ' перебираем все фразы в массиве For Each word In УдалятьСтрокиСТекстом ' если в очередной строке листа найден искомый текст If Not ra.Find(word, , xlValues, xlPart) Is Nothing Then ' добавляем строку в диапазон для удаления If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra) End If Next word Next ' если подходящие строки найдены, то: (оставьте одну из 2 следующих строк) If Not delra Is Nothing Then delra.EntireRow.Hidden = True ' скрываем их If Not delra Is Nothing Then delra.EntireRow.Delete ' удаляем их End Sub
| ConditionalRowsDeleting.xls | 24 КБ |
| ConditionalRowsDeletingUsingUserform.xls | 45.5 КБ |
Удаление пустых строк и столбцов в данных
Пустые строки и столбцы могут быть головной болью в таблицах во многих случаях. Стандартные функции сортировки, фильтрации, подведения итогов, создания сводных таблиц и т.д. воспринимают пустые строки и столбцы как разрыв таблицы, не подхватывая данные, расположенные за ними далее. Если таких разрывов много, то удалять их вручную может оказаться весьма затратно, а удалить сразу всех «оптом», используя фильтрацию не получится, т.к. фильтр тоже будет «спотыкаться» на разрывах. Давайте рассмотрим несколько способов решения этой задачи.
Способ 1. Поиск пустых ячеек
Это, может, и не самый удобный, но точно самый простой способ вполне достойный упоминания. Предположим, что мы имеем дело вот с такой таблицей, содержащей внутри множество пустых строк и столбцов (для наглядности выделены цветом):
Допустим, мы уверены, что в первом столбце нашей таблицы (колонка B) всегда обязательно присутствует название какого-либо города. Тогда пустые ячейки в этой колонке будут признаком ненужных пустых строк. Чтобы быстро их все удалить делаем следующее:
- Выделяем диапазон с городами (B2:B26)
- Нажимаем клавишу F5 и затем кнопку Выделить (Go to Special) или выбираем на вкладке Главная — Найти и выделить — Выделить группу ячеек (Home — Find&Select — Go to special) .
- В открывшемся окне выбираем опцию Пустые ячейки (Blanks) и жмём ОК – должны выделиться все пустые ячейки в первом столбце нашей таблицы.
- Теперь выбираем на вкладке Главная команду Удалить — Удалить строки с листа (Delete — Delete rows) или жмём сочетание клавиш Ctrl + минус — и наша задача решена.
Само-собой, от пустых столбцов можно избавиться совершенно аналогично, взяв за основу шапку таблицы.
Способ 2. Поиск незаполненных строк
Как вы, возможно, уже сообразили, предыдущий способ сработает только в том случае, если в наших данных обязательно присутствую полностью заполненные строки и столбцы, за которые можно зацепиться при поиске пустых ячеек. Но что, если такой уверенности нет, и в данных могут содержаться и пустые ячейки в том числе?
Взгляните, например, на следующую таблицу — как раз такой случай:
Здесь подход будет чуть похитрее:
-
Введём в ячейку A2 функцию СЧЁТЗ (COUNTA) , которая вычислит количество заполненных ячеек в строке правее и скопируем эту формулу вниз на всю таблицу:
К сожалению, со столбцами такой трюк уже не проделать – фильтровать по столбцам Excel пока не научился.
Способ 3. Макрос удаления всех пустых строк и столбцов на листе
Для автоматизации подобной задачи можно использовать и простой макрос. Нажмите сочетание клавиш Alt + F11 или выберите на вкладке Разработчик — Visual Basic (Developer — Visual Basic Editor) . Если вкладки Разработчик не видно, то можно включить ее через Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon) .
В открывшемся окне редактора Visual Basic выберите команду меню Insert — Module и в появившийся пустой модуль скопируйте и вставьте следующие строки:
Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Then If rng Is Nothing Then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r)) End If Next r If Not rng Is Nothing Then rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Then If rng Is Nothing Then Set rng = Columns(r) Else Set rng = Union(rng, Columns(r)) End If Next r If Not rng Is Nothing Then rng.Delete End Sub
Закройте редактор и вернитесь в Excel.
Теперь нажмите сочетание Alt + F8 или кнопку Макросы на вкладке Разработчик. В открывшемся окне будут перечислены все доступные вам в данный момент для запуска макросы, в том числе только что созданный макрос DeleteEmpty. Выберите его и нажмите кнопку Выполнить (Run) — все пустые строки и столбцы на листе будут мгновенно удалены.
Способ 4. Запрос Power Query
Ещё один способ решить нашу задачу и весьма частый сценарий — это удаление пустых строк и столбцов в Power Query.
Сначала давайте загрузим нашу таблицу в редактор запросов Power Query. Можно конвертировать её в динамическую «умную» сочетанием клавиш Ctrl+T или же просто выделить наш диапазон данных и дать ему имя (например Данные) в строке формул, преобразовав в именованный:
Теперь используем команду Данные — Получить данные — Из таблицы/диапазона (Data — Get Data — From table/range) и грузим всё в Power Query:
Дальше всё просто:
- Удаляем пустые строки командой Главная — Сократить строки — Удалить строки — Удалить пустые строки (Home — Remove Rows — Remove empty rows).
- Щёлкаем правой кнопкой мыши по заголовку первого столбца Город и выбираем в контекстном меню команду Отменить свёртывание других столбцов (Unpivot Other Columns). Наша таблица будет, как это технически правильно называется, нормализована — преобразована в три столбца: город, месяц и значение с пересечения города и месяца из исходной таблицы. Особенность этой операции в Power Query в том, что она пропускает в исходных данных пустые ячейки, что нам и требуется:
Ссылки по теме
- Что такое макрос, как он работает, куда копировать текст макроса, как запустить макрос?
- Заполнение всех пустых ячеек в списке значениями вышестоящих ячеек
- Удаление всех пустых ячеек из заданного диапазона
- Удаление всех пустых строк на листе с помощью надстройки PLEX
Как удалить миллион пустых строк в Excel, смотри в закрепленном комментарии
Как удалить миллион пустых строк в Excel? Предположим в вашей табличке пропущено много строк, где указан менеджер и дата его рождения. 1) выбираем любую ячейку и нажимаем сочетание клавиш «Ctrl+A» на клавиатуре, чтобы выделить весь диапазон. 2) Далее нажимаем F5 и команду «Выделить». 3) Нас интересует кнопка «Пустые ячейки». 4) Правой кнопкой мыши нажимаем команду «Удалить ячейки со сдвигом вверх» ✅ Сохраняй ролик и подписывайся на мою страницу с подсказками по Excel ================================================== Канал в телеграм https://t.me/+6tJPJehqD1NkZTdi Группа ВК: https://vk.com/akademia_excel Сайт: http://akademia-excel.ru/
Показать больше
Войдите , чтобы оставлять комментарии