Как открыть тяжелый файл excel

Как очистить рабочую книгу 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. На домашней ленте нажмите Найти и выделить, а затем нажмите Область выделения.
2. Нажмите Фигуры на этом листе. Фигуры отображаются в списке.
3. Удалите ненужные фигуры. (Значок в виде глаза указывает, видна ли фигура).
4. Повторите шаги от 1 до 3 для каждого листа.

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

Метод 4. Удаление условного форматирования

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

1. Сохраните резервную копию файла.
2. На домашней ленте щелкните Условное форматирование.
3. Удалите правила со всего листа.
4. Выполните шаги 2 и 3 для каждого листа в книге.
5. Сохраните книгу под другим именем.
6. Убедитесь в том, что проблема устранена.

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

Проблема не устранена?

Если ни один из этих методов не помогает, вы можете перейти на 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 или разбить проблемную рабочую книгу на разные рабочие книги.

Файл Excel не открывается при двойном нажатии на него

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

Решение

1. Откройте Excel из меню Пуск Windows
2. Откройте параметры Excel: Файл → Параметры
3. Перейдите в раздел меню Дополнительно → общие сведения
4. Убедитесь, что сброшен флажок Игнорировать другие приложения, использующие платформу динамических данных (DDE) .

Поделиться

Продукт

• Почему think-cell?
• Все функции
• Непрерывное улучшение
• Рекомендации клиентов
• Ситуационный анализ

Заказать

• Новый клиент
• Добавить пользователей
• Возобновить лицензии
• Найти реселлера
• Академическая программа
• Стартап программа

Скачать

• Существующий клиент
• Бесплатная пробная версия

Ресурсы

• Поддержка
• Видеоучебники
• Советы и подсказки
• Руководство пользователя
• База знаний
• Webinars
• Content hub

Вакансии

• Разработчик C++
• Тест-инженер программного обеспечения
• Все вакансии
• Доклады и публикации
• Мероприятия
• Блог разработчиков

Уменьшение размера файла Excel электронных таблиц

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

Сохранение таблицы в двоичном формате (XSLB)

Чтобы уменьшить размер файла, можно сохранить таблицу в виде двоичной книги (XSLB), а не формата по умолчанию (XSLX). По умолчанию используется формат XML, что важно при использовании данных в сторонних программах, так как XML является открытым стандартом. Однако в большинстве целей двоичного формата достаточно и он сохраняется в файле меньшего формата.

1. Перейдите в меню >параметры >Сохранить.
2. В списке Сохранениефайлов в этом формате в списке Сохранить книги выберите Excel Двоичная книга.

Этот параметр задает двоичный формат по умолчанию. Если вы хотите сохранить по умолчанию Excel книгу (.xlsx), но сохранить текущий файл как двоичный, выберите параметр в диалоговом окке Сохранить как.

Примечание: Если файл находится в SharePoint, возможно, вы не видите параметр Сохранить как. В этом случае вы можете сохранить копию на локальном диске, сохранить ее как двоичное, а затем заменить исходный файл новым двоичным.

1. Перейдите в > сохранить как и, если файл сохраняется впервые, выберите расположение.
2. В списке типов файлов выберите Excel Двоичная книга (XLSB).

Сохранение таблицы в двоичном формате (XSLB)

Чтобы уменьшить размер файла, можно сохранить таблицу в виде двоичной книги (XSLB), а не формата по умолчанию (XSLX). По умолчанию используется формат XML, что важно при использовании данных в сторонних программах, так как XML является открытым стандартом. Однако в большинстве целей двоичного формата достаточно и он сохраняется в файле меньшего формата.

1. Перейдите в меню >параметры >Сохранить.
2. В списке Сохранениефайлов в этом формате в списке Сохранить книги выберите Excel Двоичная книга.

Этот параметр задает двоичный формат по умолчанию.

Если вы хотите сохранить по умолчанию Excel книгу (.xlsx), но сохранить текущий файл как двоичный, выберите параметр в диалоговом окне Сохранить как:

1. Выберите Файл >Сохранить как.
2. В списке Тип файла выберите Excel двоичной книги (XLSB).

Уменьшение количества таблиц

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

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

Сохранение изображений с более низким разрешением

1. Откройте меню Файл, выберите раздел Параметры, а затем — Дополнительно.
2. В области Размер и качество изображениясделайте следующее:
   • Выберите Отменить редактирование данных. Этот параметр удаляет хранимые данные, которые используются для восстановления исходного состояния изображения после его изменения. Обратите внимание, что если удалить данные редактирования, восстановить изображение будет нельзя.
   • Убедитесь, что не выбрано сжатие изображений в файле.
   • В списке Разрешение по умолчанию выберите разрешение 150ppi или более низкое. В большинстве случаев разрешение не должно быть выше.

Сжатие рисунков

1. Выберите рисунок в документе. На ленте появится вкладка Формат рисунка.
2. На вкладке Формат рисунка в группе Настройка выберите Сжать рисунки.
3. В области Параметры сжатиясделайте следующее:
   • Чтобы сжать все рисунки в файле, снимайте снимок Применить только к этому рисунку. Если этот параметр выбран, изменения, внесенные здесь, будут влиять только на выбранный рисунок.
   • Выберите Удалить обрезанные области рисунков. Этот параметр удаляет обрезанные данные рисунка, но вы не сможете их восстановить.
4. В области Разрешениесделайте следующее:
   • Выберите Использовать разрешение по умолчанию.

Не сохранения кэша данных с файлом

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

1. Выберите любую ячейку в таблице.
2. На вкладке Анализ таблицы в группе Таблица выберите Параметры.
3. В диалоговом окне Параметры таблицы выберите вкладку Данные и сделайте следующее:
4. Чтобы сохранить исходные данные с файлом, с помощью сохранения исходных данных с помощью сохранения.
5. Выберите Обновить данные при открытии файла.

Как открыть тяжелый файл excel

Погуглите как читать кодом текст построчно (или на сайте EducatedFool примеры кода посмотрите, или вот: http://www.firststeps.ru/vba/excel/r.php?16 )
Далее считаете строки — пока порог не достигнут, пишите их в одно место, как перевалили — считаете заново и пишите в другое.
Думаю, вполне приемлемо из одного csv нагенерить десяток других (имя брать родное и дописывать «подномер» — по ссылке firststeps всё в общем есть).

Пользователь
Сообщений: 23779 Регистрация: 22.12.2012
21.03.2012 15:29:12

Вот, на основе от firststeps (пути поставьте свои, вместо 5 напишите 65000):

Sub Test()
Dim s As String, i As Long, ii As Long

Open «D:\csv\razbitj\пример.csv» For Input As #1
ii = 1
Open «c:\» & ii & «.txt» For Output As #2
While Not EOF(1)
i = i + 1
Input #1, s
If i > 5 Then
ii = ii + 1
Close #2
Open «c:\» & ii & «.txt» For Output As #2
i = 0
End If
Print #2, s
Wend

Close #1
Close #2
End Sub

21.03.2012 15:41:18
Hugo, спасибо большое. буду пробовать.
Пользователь
Сообщений: 23779 Регистрация: 22.12.2012
21.03.2012 15:51:39

Ну и если идти дальше — то можно кодом сразу из большого файла читать нужную информацию, его не разбивая. Смотря что нужно получить — допустим подсчитать общие суммы по позициям, или количество повторов уникальных (раз там один столбец) можно сразу сделать, читая построчно.
Ну и хороший вариант — подключить для анализа csv к Access, как в той теме упоминали.

21.03.2012 18:13:39

Hugo, все работает.
спасибо большое.
только такой вопрос.
в моем файле данные записанны, в таком виде в одну ячейку
01.03.2012 00:00:00.144,12,13,1.20,2.25 первая ячейка
01.03.2012 00:00:00.287,13,15,1.30,2.35 вторая ячейка
разделитель — запятая.
после Вашего макроса оно так и записывает.
01.03.2012 00:00:00.144
12
13
1.20
2.25
я вычитал, чтобы он не считал запятую разделителем нужно вставить Line Inpute.
но пока не получилось.

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

p.s. есть ли возможность с Вами связаться через скайп или айсикью?

Пользователь
Сообщений: 23779 Регистрация: 22.12.2012
21.03.2012 18:22:22

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

Пользователь
Сообщений: 23779 Регистрация: 22.12.2012
21.03.2012 18:27:29

Про Line Input это Вы хорошо прочитали (добавил в код слово Line):

Sub Test2()
Dim s As String, i As Long, ii As Long

Open «D:\csv\razbitj\пример.csv» For Input As #1
ii = 1
Open «c:\» & ii & «.txt» For Output As #2
While Not EOF(1)
i = i + 1
Line Input #1, s
If i > 5 Then
ii = ii + 1
Close #2
Open «c:\» & ii & «.txt» For Output As #2
i = 0
End If
Print #2, s
Wend

Close #1
Close #2
End Sub

Пользователь
Сообщений: 8196 Регистрация: 21.12.2012
21.03.2012 21:17:13
как можно разбить такой большой файл?
В Access его.
«..Сладку ягоду рвали вместе, горьку ягоду я одна.»
22.03.2012 12:52:47

Hugo, Вы не получили вчера письмо от меня?

Line вставлял там же, где и Вы в коде.
но с ним не получается.
мне кажется, зацикливается и после создания первого фала стоит.

В Access пробовал, спасибо за совет.
сначала даже обрадовался, но почему-то не получается работать с полученными данными, говорит, что нельзя вносить изменения(

Пользователь
Сообщений: 23779 Регистрация: 22.12.2012
22.03.2012 13:21:16

Я письма не получал.
На счёт зацикливания — попробуйте тестово резать файл например по 20 строк и погоняйте код по F8.
Так сразу будет видно — появляются новые файлы или нет.
Может быть просто долго процесс идёт.

22.03.2012 18:33:34

Hugo, не получается понять вашу почту.
Вам не сложно будет добавить в скайп??
англ. буквами слово снеговик_

Пользователь
Сообщений: 23779 Регистрация: 22.12.2012
22.03.2012 19:01:31
Последнее получил, только там отвечать не на что было 🙂
Вечером из дома в скайп добавлю.
22.03.2012 19:10:59

Hugo, возникла такая идея
если учитывать запятые сложно.
есть ли возможность вставить в ваш код простой счетчик символов?
01.03.2012 00:00:00.287,13,15,1.30,2.35
если у нас такой формат. чтобы счетчик посчитал из исходного файла 39 символов, т.е. разделитель четко через 39 символов.
записал их в одну ячейку и пошел дальше.
мне кажется, тогда должно получится.
с ув. спасибо.

Пользователь
Сообщений: 23779 Регистрация: 22.12.2012
22.03.2012 19:24:21

Вариант Sub Test2() у меня корректно запятые отработал.
Покажите уже наконец кусок файла строк на 20 — поэксперементируем.

22.03.2012 19:32:29

EUR/USD,20110901 00:00:00.320,1.43634,1.43649
EUR/USD,20110901 00:00:00.434,1.43634,1.43648
EUR/USD,20110901 00:00:01.406,1.43635,1.43647
EUR/USD,20110901 00:00:02.118,1.43635,1.43646
EUR/USD,20110901 00:00:02.651,1.43635,1.43646
EUR/USD,20110901 00:00:03.208,1.43635,1.43645
EUR/USD,20110901 00:00:03.268,1.43635,1.43646
EUR/USD,20110901 00:00:03.418,1.43635,1.43645
EUR/USD,20110901 00:00:09.406,1.43634,1.43645
EUR/USD,20110901 00:00:09.408,1.43637,1.43645
EUR/USD,20110901 00:00:09.419,1.43637,1.43647
EUR/USD,20110901 00:00:09.439,1.43637,1.43648
EUR/USD,20110901 00:00:09.501,1.43639,1.43647
EUR/USD,20110901 00:00:09.508,1.43637,1.43648
EUR/USD,20110901 00:00:09.509,1.43639,1.43648
EUR/USD,20110901 00:00:09.511,1.43639,1.43647
EUR/USD,20110901 00:00:09.514,1.43639,1.43648
EUR/USD,20110901 00:00:09.751,1.4364,1.43648
EUR/USD,20110901 00:00:09.958,1.43638,1.43648
EUR/USD,20110901 00:00:09.959,1.43643,1.43648