Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Уменьшение объёма файла *.xls с большим числом строк - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 212»
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Уменьшение объёма файла *.xls с большим числом строк (Формулы/Formulas)
Уменьшение объёма файла *.xls с большим числом строк
SergeyNN Дата: Понедельник, 30.10.2017, 18:18 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте!

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

Итак, в файле примерно 500 000 (полмиллиона) строк. Примерно 100 столбцов. Объём файла примерно 100 Мб. Данные или числа или текст. Картинок, графиков, макросов или т.п. ничего нет.

Ноут очень сильно тормозит. Сохраняет файл минут по 15, на каждой сложной операции примерно по столько же приходится ждать и т.п.

Денег на другой комп нет.

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

Посоветйуте, пожалуйста, как уменьшить объём файла или каким-то, может быть, другим чудодейственным способом увеличить быстродействие.

Заранее благодарю!
 
Ответить
СообщениеЗдравствуйте!

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

Итак, в файле примерно 500 000 (полмиллиона) строк. Примерно 100 столбцов. Объём файла примерно 100 Мб. Данные или числа или текст. Картинок, графиков, макросов или т.п. ничего нет.

Ноут очень сильно тормозит. Сохраняет файл минут по 15, на каждой сложной операции примерно по столько же приходится ждать и т.п.

Денег на другой комп нет.

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

Посоветйуте, пожалуйста, как уменьшить объём файла или каким-то, может быть, другим чудодейственным способом увеличить быстродействие.

Заранее благодарю!

Автор - SergeyNN
Дата добавления - 30.10.2017 в 18:18
Nic70y Дата: Понедельник, 30.10.2017, 18:36 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4184
Репутация: 903 ±
Замечаний: 0% ±

Excel 2013
разбейте файл на несколько


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщениеразбейте файл на несколько

Автор - Nic70y
Дата добавления - 30.10.2017 в 18:36
SergeyNN Дата: Понедельник, 30.10.2017, 18:49 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Nic70y,

не, к сожалению, так нельзя-надо смотреть статистику и динамику по всему файлу одновременно. Если разбить, то будут ошибки дискретизации...
 
Ответить
СообщениеNic70y,

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

Автор - SergeyNN
Дата добавления - 30.10.2017 в 18:49
buchlotnik Дата: Понедельник, 30.10.2017, 19:02 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3325
Репутация: 886 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
Надо, конечно, файл смотреть - если не конф. информация - выложите на яндекс диск - пересохраню в xlsb (самое разумное на мой вкус), а там можете ещё что всплывёт


каждому For - Next!
платная помощь:
ЯД: 410012595572239
buchlotnik@mail.ru
 
Ответить
СообщениеНадо, конечно, файл смотреть - если не конф. информация - выложите на яндекс диск - пересохраню в xlsb (самое разумное на мой вкус), а там можете ещё что всплывёт

Автор - buchlotnik
Дата добавления - 30.10.2017 в 19:02
SergeyNN Дата: Понедельник, 30.10.2017, 19:11 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
buchlotnik,

пробовал *.xlsb, но во-первых объём сам по себе меняется крайне незначительно, а во-вторых, как только его открываешь, то Excel сразу же выплёвывает ошибку о недостаточности ресурсов. То есть при *.xlsx очень медленно, но работает, а вот при *.xlsb даже и не работает (((
 
Ответить
Сообщениеbuchlotnik,

пробовал *.xlsb, но во-первых объём сам по себе меняется крайне незначительно, а во-вторых, как только его открываешь, то Excel сразу же выплёвывает ошибку о недостаточности ресурсов. То есть при *.xlsx очень медленно, но работает, а вот при *.xlsb даже и не работает (((

Автор - SergeyNN
Дата добавления - 30.10.2017 в 19:11
Pelena Дата: Понедельник, 30.10.2017, 19:13 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11755
Репутация: 2596 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
SergeyNN, эту тему смотрели?


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеSergeyNN, эту тему смотрели?

Автор - Pelena
Дата добавления - 30.10.2017 в 19:13
buchlotnik Дата: Понедельник, 30.10.2017, 19:13 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3325
Репутация: 886 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
SergeyNN, а форматирование есть? Условное форматирование? Формулы?


каждому For - Next!
платная помощь:
ЯД: 410012595572239
buchlotnik@mail.ru
 
Ответить
СообщениеSergeyNN, а форматирование есть? Условное форматирование? Формулы?

Автор - buchlotnik
Дата добавления - 30.10.2017 в 19:13
RAN Дата: Понедельник, 30.10.2017, 19:29 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4602
Репутация: 925 ±
Замечаний: 0% ±

2010
Макрос
[vba]
Код
Sub q()
Dim i&, ii&
Dim ar(1 To 500000, 1 To 100)
For i = 1 To 500000
For ii = 1 To 100
ar(i, ii) = Rnd
Next
Next
End Sub
[/vba]
напрочь выбивает Excel на стадии объявления массива.
%)


Быть или не быть, вот в чем загвоздка!
 
Ответить
СообщениеМакрос
[vba]
Код
Sub q()
Dim i&, ii&
Dim ar(1 To 500000, 1 To 100)
For i = 1 To 500000
For ii = 1 To 100
ar(i, ii) = Rnd
Next
Next
End Sub
[/vba]
напрочь выбивает Excel на стадии объявления массива.
%)

Автор - RAN
Дата добавления - 30.10.2017 в 19:29
Nic70y Дата: Понедельник, 30.10.2017, 20:01 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4184
Репутация: 903 ±
Замечаний: 0% ±

Excel 2013
надо смотреть статистику и динамику по всему файлу одновременно

500 000 (полмиллиона) строк
не верю %)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение
надо смотреть статистику и динамику по всему файлу одновременно

500 000 (полмиллиона) строк
не верю %)

Автор - Nic70y
Дата добавления - 30.10.2017 в 20:01
_Boroda_ Дата: Понедельник, 30.10.2017, 20:14 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11646
Репутация: 4828 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
У меня на работе есть файл (достался в наследство) на 600 Мб (тому, кто не верит - примерно 700000 строк и 200 столбцов). ШЕСТЬСОТ Мб!!! В Excel! И, Вы не поверите, он работает довольно-таки нормально. Правда, сначала, когда он только ко мне пришел, он сохранялся больше часа.
Что я с ним сделал - встал в ячейку А1, нажал Контрл Шифт Енд (все выделилось) поставил стиль Обычный, на всякий случай (по идее этого не нужно уже, установкой стиля все должно измениться) сделал заливку Нет заливки, цвет шрифта - Авто.
И, самое главное, что действительно очень серьезно тормозит - поставил все границы ячеек в "Нет границ". Ну и, конечно же, посмотрел Контрл F3 - бывают файлы с кучей имен, все их поудалял нафиг. Посмотрел на Стили (вкладка Главная) - если их там много - все поудалял, оставил только те, что есть в новой, пустой книге. Пошел Главная - Найти и выделить - Выделение группы ячеек - Объекты. Если что-то выделилось - удалить делитом

И, кстати, в Готовых решениях есть тема http://www.excelworld.ru/forum/3-57-1


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеУ меня на работе есть файл (достался в наследство) на 600 Мб (тому, кто не верит - примерно 700000 строк и 200 столбцов). ШЕСТЬСОТ Мб!!! В Excel! И, Вы не поверите, он работает довольно-таки нормально. Правда, сначала, когда он только ко мне пришел, он сохранялся больше часа.
Что я с ним сделал - встал в ячейку А1, нажал Контрл Шифт Енд (все выделилось) поставил стиль Обычный, на всякий случай (по идее этого не нужно уже, установкой стиля все должно измениться) сделал заливку Нет заливки, цвет шрифта - Авто.
И, самое главное, что действительно очень серьезно тормозит - поставил все границы ячеек в "Нет границ". Ну и, конечно же, посмотрел Контрл F3 - бывают файлы с кучей имен, все их поудалял нафиг. Посмотрел на Стили (вкладка Главная) - если их там много - все поудалял, оставил только те, что есть в новой, пустой книге. Пошел Главная - Найти и выделить - Выделение группы ячеек - Объекты. Если что-то выделилось - удалить делитом

И, кстати, в Готовых решениях есть тема http://www.excelworld.ru/forum/3-57-1

Автор - _Boroda_
Дата добавления - 30.10.2017 в 20:14
Nic70y Дата: Понедельник, 30.10.2017, 20:39 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4184
Репутация: 903 ±
Замечаний: 0% ±

Excel 2013
ШЕСТЬСОТ Мб
в это верю
и больше файлы видел (тогда я узнал, что у эксель есть конец),
но
смотреть статистику
можно и другим способом


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение
ШЕСТЬСОТ Мб
в это верю
и больше файлы видел (тогда я узнал, что у эксель есть конец),
но
смотреть статистику
можно и другим способом

Автор - Nic70y
Дата добавления - 30.10.2017 в 20:39
pabchek Дата: Вторник, 31.10.2017, 10:55 | Сообщение № 12
Группа: Проверенные
Ранг: Ветеран
Сообщений: 779
Репутация: 173 ±
Замечаний: 0% ±

Excel 2007
Добавлю и свои пять капель..
Чаще всего использую 3 вещи:
1) аналогично способу _Boroda_, только я, выделив весь лист, делаю: "Очистить все форматы";
2) сохраняю в формате .xlsb (хуже восстанавливается, но имею привычку часто сохранять);
3) если данные в виде релятивной таблицы, делаю сводную таблицу в отдельном файле, и сохраняю без сохранения данных. При необходимости обновляю. Процесс обновления, конечно, не быстрый, зато сам файл со сводной маленький и шустрый.
Добавил
=======
Когда формирую сводную, достаю по максимуму поля и пересчитываю таблицу. Смотрю на объем сохраненного файла. Когда размер уменьшится в 3-4 раза делаю окончательную копию и лишние поля уже можно убирать. Таким образом даже файл с сохранением данных можно уменьшить в 3-4 раза.


"Учиться, учиться и еще раз учиться!"
WM: R399923528092


Сообщение отредактировал pabchek - Вторник, 31.10.2017, 11:01
 
Ответить
СообщениеДобавлю и свои пять капель..
Чаще всего использую 3 вещи:
1) аналогично способу _Boroda_, только я, выделив весь лист, делаю: "Очистить все форматы";
2) сохраняю в формате .xlsb (хуже восстанавливается, но имею привычку часто сохранять);
3) если данные в виде релятивной таблицы, делаю сводную таблицу в отдельном файле, и сохраняю без сохранения данных. При необходимости обновляю. Процесс обновления, конечно, не быстрый, зато сам файл со сводной маленький и шустрый.
Добавил
=======
Когда формирую сводную, достаю по максимуму поля и пересчитываю таблицу. Смотрю на объем сохраненного файла. Когда размер уменьшится в 3-4 раза делаю окончательную копию и лишние поля уже можно убирать. Таким образом даже файл с сохранением данных можно уменьшить в 3-4 раза.

Автор - pabchek
Дата добавления - 31.10.2017 в 10:55
SergeyNN Дата: Вторник, 31.10.2017, 15:15 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_boroda_, pabchek, Спасибо Вам!)

Всё проверил, но "лишнего" ничего не нашёл, а имена есть только от ВПРов и они неудаляемые.

Что же касается "заготовки" по адресу: http://www.excelworld.ru/forum/3-57-1 -то я пока ещё не научился дружить с макросами. На данный момент-это планы на ближайшее будущее.

В итоге почистил задваивавшиеся данные, удалил границы ячеек и файл стал в среднем на 10-12% легче.

Пока планирую работать как есть, тем более, что считать-то он нормально считает.

По сути своей проблем две:
а) таблица считает, просто медленно;
б) меня грызут сомнения, что я чего-то не знаю что с ним можно сделать чтобы ускорить работу и сделать поменьше файл)))


Сообщение отредактировал SergeyNN - Вторник, 31.10.2017, 15:16
 
Ответить
Сообщение_boroda_, pabchek, Спасибо Вам!)

Всё проверил, но "лишнего" ничего не нашёл, а имена есть только от ВПРов и они неудаляемые.

Что же касается "заготовки" по адресу: http://www.excelworld.ru/forum/3-57-1 -то я пока ещё не научился дружить с макросами. На данный момент-это планы на ближайшее будущее.

В итоге почистил задваивавшиеся данные, удалил границы ячеек и файл стал в среднем на 10-12% легче.

Пока планирую работать как есть, тем более, что считать-то он нормально считает.

По сути своей проблем две:
а) таблица считает, просто медленно;
б) меня грызут сомнения, что я чего-то не знаю что с ним можно сделать чтобы ускорить работу и сделать поменьше файл)))

Автор - SergeyNN
Дата добавления - 31.10.2017 в 15:15
buchlotnik Дата: Вторник, 31.10.2017, 15:24 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3325
Репутация: 886 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
Цитата
таблица считает, просто медленно
покажите фрагмент в несколько строк и с именами - может формулы можно оптимизировать


каждому For - Next!
платная помощь:
ЯД: 410012595572239
buchlotnik@mail.ru
 
Ответить
Сообщение
Цитата
таблица считает, просто медленно
покажите фрагмент в несколько строк и с именами - может формулы можно оптимизировать

Автор - buchlotnik
Дата добавления - 31.10.2017 в 15:24
SergeyNN Дата: Вторник, 31.10.2017, 15:41 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 0% ±

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

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

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

Автор - SergeyNN
Дата добавления - 31.10.2017 в 15:41
Pelena Дата: Вторник, 31.10.2017, 15:43 | Сообщение № 16
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11755
Репутация: 2596 ±
Замечаний: 0% ±

Excel 2010, 2016 & Mac Excel
Можно попробовать заменить ВПР() на ИНДЕКС()+ПОИСКПОЗ().
Должно быть немного быстрее

И надо проверить, нет ли в формулах обращения к столбцам целиком


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеМожно попробовать заменить ВПР() на ИНДЕКС()+ПОИСКПОЗ().
Должно быть немного быстрее

И надо проверить, нет ли в формулах обращения к столбцам целиком

Автор - Pelena
Дата добавления - 31.10.2017 в 15:43
SergeyNN Дата: Вторник, 31.10.2017, 15:48 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Pelena, да, было дело)))
Если принять общий объём работы с файлом за 100%, то при использовании ИНДЕКС()+ПОИСКПОЗ() комп вис примерно на 30% )))
Ну то есть я копировал формулы, а он просто выходил в астрал и там где-то пребывал до момента снятия задачи Диспетчером задач.)))
 
Ответить
СообщениеPelena, да, было дело)))
Если принять общий объём работы с файлом за 100%, то при использовании ИНДЕКС()+ПОИСКПОЗ() комп вис примерно на 30% )))
Ну то есть я копировал формулы, а он просто выходил в астрал и там где-то пребывал до момента снятия задачи Диспетчером задач.)))

Автор - SergeyNN
Дата добавления - 31.10.2017 в 15:48
pabchek Дата: Вторник, 31.10.2017, 15:52 | Сообщение № 18
Группа: Проверенные
Ранг: Ветеран
Сообщений: 779
Репутация: 173 ±
Замечаний: 0% ±

Excel 2007
как ни вуалируй, всё понятно

возмите сотню строк. Замените контрагентов на "К1", 2 и т.д. товары - Т1 Т2


"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
Сообщение
как ни вуалируй, всё понятно

возмите сотню строк. Замените контрагентов на "К1", 2 и т.д. товары - Т1 Т2

Автор - pabchek
Дата добавления - 31.10.2017 в 15:52
SergeyNN Дата: Вторник, 31.10.2017, 17:43 | Сообщение № 19
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
И надо проверить, нет ли в формулах обращения к столбцам целиком
- это делается посредством "Влияющие/Зависимые ячейки"? )

возмите сотню строк. Замените контрагентов на "К1", 2 и т.д. товары - Т1 Т2
- да там когда начинаешь переименовывать, то все формулы сыпятся((( Я уже хотел составить схематический файл так, как делал раньше когда сюда писал по другим вопросам. Спасибо большое!)
 
Ответить
Сообщение
И надо проверить, нет ли в формулах обращения к столбцам целиком
- это делается посредством "Влияющие/Зависимые ячейки"? )

возмите сотню строк. Замените контрагентов на "К1", 2 и т.д. товары - Т1 Т2
- да там когда начинаешь переименовывать, то все формулы сыпятся((( Я уже хотел составить схематический файл так, как делал раньше когда сюда писал по другим вопросам. Спасибо большое!)

Автор - SergeyNN
Дата добавления - 31.10.2017 в 17:43
_Boroda_ Дата: Вторник, 31.10.2017, 17:48 | Сообщение № 20
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11646
Репутация: 4828 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
нет ли в формулах обращения к столбцам целиком
- это делается посредством "Влияющие/Зависимые ячейки"?

Нууу, можно и так, конечно, но можно и попроще. Смотрите формулу. Если она выглядит вот так
Код
=ВПР(A1;C:R;3;0)
то так нельзя
А вот так
Код
=ВПР(A1;C$1:R$55555;3;0)
льзя


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
нет ли в формулах обращения к столбцам целиком
- это делается посредством "Влияющие/Зависимые ячейки"?

Нууу, можно и так, конечно, но можно и попроще. Смотрите формулу. Если она выглядит вот так
Код
=ВПР(A1;C:R;3;0)
то так нельзя
А вот так
Код
=ВПР(A1;C$1:R$55555;3;0)
льзя

Автор - _Boroda_
Дата добавления - 31.10.2017 в 17:48
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Уменьшение объёма файла *.xls с большим числом строк (Формулы/Formulas)
Страница 1 из 212»
Поиск:

Яндекс цитирования
© 2010-2017 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!