Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Зависает на громоздких таблицах
 
#1
20.06.2017 10:11:30
WIN7 64 bit   проц i7 32 GB памяти
офис правда 32 битный

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

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

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

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

открывал таблицу в архиваторе - пробовал найти drawing - но эта папка мизерного размера.
также удалял пустые строки и столбцы - это ничего не дало - их там нет

размер 1-го файла 24 мега
Изменено: Алексей - 20.06.2017 12:56:27
 
 
 
#2
20.06.2017 10:15:26
Вы пробовали открыть аналогичный по размерам файл и там произвести аналогичные действия?
24 мега для 150 тыс строк что-то много.
Изменено: CrazyRabbit - 20.06.2017 10:17:05
 
 
 
#3
20.06.2017 10:18:09
не совсем понимаю ваш вопрос.
большие (больше 100 000 строк) файлы и раньше вызывали проблему у меня

но как-то же с ними можно работать?
 
 
 
#4
20.06.2017 10:26:24
Попробуйте пересохранить файл в "Двоичная книга Excel" (Это .xlsb) - это должно немного помочь.
Аналогичная проблема у меня на работе где сервер слабый и на нем мы пытались большие объемы данных крутить.
 
 
 
#5
20.06.2017 10:36:59
это пробовал.
не помогает.
проблема ведь не в объеме сохраняемого файла, а количестве копируемых, заменяемых данных.

и вот еще странно с импортом проблем не было - быстро и без проблем все импортируется
замена точек на запятые и копирование вызывает паралич экселя
Изменено: Алексей - 20.06.2017 10:38:45
 
 
 
#6
20.06.2017 10:47:26
Попробуйте создать новый файл и туда импортировать данные.
Цитата
Алексей написал:
решаю через индекс-поиск позиции
такие объемы лучше макросами решать (мое мнение) с отключением обновления экрана и пересчет формул.
На форуме были примеры импорта из текстового фала нужных данных.
 
 
 
#7
20.06.2017 11:06:56
Дмитрий спасибо
проблема не в этом.
при замене точек на запятые нет никаких формул - чистый лист с импортированными данными и эксель не может заменить сразу все, частями в 10 проходов только

при этом тут
https://support.office.com/ru-RU/article/%D0%A2%D0%B5%D1%85%D0%BD%D0%B8%D1%87%D0%B5%D1%81%­D0%BA%D0%B...
пишут о значительно больших возможностях
пробовал на двух машинах с разными офисами (2016 и 2013, WIN 7 и WIN8) одинаково плачевно
Изменено: Алексей - 20.06.2017 11:11:06
 
 
 
#8
20.06.2017 11:11:45
Алексей, посмотрите в разделе http://www.planetaexcel.ru/video/, 3 строка сверху запись "Как уменьшить размер файла и ускорить его в Excel"
Изменено: Сергей П - 20.06.2017 11:12:17
 
 
 
#9
20.06.2017 11:15:01
Алексей, А в текстовом файле поменять точку на запятую есть возможность? (таким же поиском-заменой)
 
 
 
#10
20.06.2017 11:15:02
Цитата
Дмитрий Тарковский написал:
такие объемы лучше макросами решать
Абсолютно согласен.
Алексей, сделайте макросом.
Изменено: Vetermsk - 20.06.2017 11:15:23
 
 
 
#11
20.06.2017 11:38:40
спасибо за советы, видео дома посмотрю.
все ваши предложения наводят на мысль что проблема работы Экселя с большими таблицами существует.
так как предложения по сути - это способы как обойти проблему, но не решить почему же эксель не способен сразу заменить много точек на запятых.
я посчитал общее количество ячеек 5 000 000 примерно, точки даже не в каждой есть.
менять в исходном файле попробую вечером, но похоже будут проблемы с датами потом
 
 
 
#12
20.06.2017 11:46:34
Цитата
Алексей написал:
проблема работы Экселя с большими таблицами существует.
Существует. Может попробуете Аксесс? Если вам действительно нужны такие объёмные таблицы, лучше переходить в базы данных.
 
 
 
#13
20.06.2017 13:13:17
Алексей, скиньте обезличенный небольшой пример текстового файла, отражающий реальную картину. Может что то придумаю...
 
 
 
#14
20.06.2017 14:01:39
Алексей, точку на точку менять довольно долго. Для каждой ячейки производится пересчет. По идее действительно, может, в текстовике предварительно поменять? Еще вариант попробовать так - сходить в настройки Винды и там поставить разделитель целой и дробной части точку. В файле нажать F9 - пересчет. Потом файл закрыть, настройки поменять обратно, файл открыть. Добавлено. Думаю, что лучше сначала поменять настройки, а потом загружать данные в Excel

По поводу оперативки - 32 бита по определению не может кушать больше 4-х гигов (фактически не больше 3,5). Переходите на 64 бита.

Обратите внимание на форматы в таблицах. Они очень прожорливые. В особенности это относится к цветным границам ячеек.
Проверьте, нет ли в файлах кучи ненужных стилей (при выгрузках бывает). Также проверьте, нет ли кучи непонятно зачем нужных именованных диапазонов (Контрл F3). Если есть, то поубивайте всё нафиг.

Отключите автопересчет формул (Формулы - Параметры вычислений). И неважно, что формул в файле нет, все равно отключите.

При копировании вставляйте не через Контрл+V, а значениями

Мне на работе достались в наследство неподъемные таблицы размером примерно 600 000 строк на 150 столбцов и весом под 350 мегов. Это они там так базу вели. Таблицы, конечно же, практически не работали, зависали после каждого телодвижения. Я поубирал в них все раскраски, границы, везде, кроме дат, поставил стиль Общий, убил все имена - стало более-менее возможно работать. Сейчас-то, конечно, такого безобразия у нас уже нет, но тогда нужно было как-то реанимировать этих монстров и указанные способы неплохо помогли.
 
Изменено: _Boroda_ - 20.06.2017 20:37:41
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#15
20.06.2017 14:13:21
Цитата
_Boroda_ написал:
И неважно, что формул в файле нет
Автор упоминал, что в файле имеются формулы (ИНДЕКС и ПОИСКПОЗ), но отредактировал своё сообщение.
 
 
 
#16
20.06.2017 14:29:08
Тогда тем более - 150 000 ПОИСКПОЗов по 150 000 строк каждый конечно вешает всё.
Кстати, Алексей, если есть автофильтры и Вы ими пользуетесь, то не забывайте, что при их использовании формулы пересчитываются.
Да, забыл еще про Условное форматирование - оно тоже постоянно пересчитывается
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#17
20.06.2017 19:17:54
Цитата
_Boroda_ написал:
По поводу оперативки - 32 бита по определению не может кушать больше 4-х гигов (фактически не больше 3,5). Переходите на 64 бита.
да я давно хотел, потом вычитал что не все надстройки работают, хотя уже не актуально была у меня на прошлой работе нужна я примочка для диаграмм, теперь это не нужно.
какие еще недостатки 64 битной?

советы производителя тут https://support.office.com/en-us/article/Choose-between-the-64-bit-or-32-bit-version-of-Office-2dee7...
но может с практической стороны еще есть ньюансы, типа глюков и т.д.
Изменено: Алексей - 20.06.2017 20:09:40
 
 
 
#18
20.06.2017 19:20:45
Цитата
Юрий М написал:
(решаю через индекс-поиск позиции)
не редактировал и сейчас это есть в описании
но формулы я пишу после всех копирований и с самими формулами проблем нет они считаются нормально по скорости
проблема ступора проявляется при
1. замены точек идут на вообще чистом листе после импорта
форматы надо попробовать ощий - я ставил числовой и множил на 1 (множит кстати быстро)
2. копирование с аналогичного чистого листа на такой же.
3. сохранение файла после копирования также долго и сам файл минут 10 в проводнике не обновляется - т.е. эксель чето там внутри себя долго думает перед тем как на диск пересохранить. диск SSD

попробую через Power pivot сделать
Изменено: Алексей - 20.06.2017 19:25:50
 
 
 
#19
20.06.2017 19:29:54
Сейчас увидел - в стартовом сообщении. А на момент написания сообщения не нашёл )
 
 
 
#20
20.06.2017 19:32:35
ребят как быстро удалить то, что не нужно, например фильтрую только положительное и отрицательное могу выкинуть. может есть какая комбинация клавиш сразу снести все что не попало в фильтр?
пока делаю все наоборот - фильтрую отрицательное - удаляю строки, потом удаляю фильтр. но с учетом тормозов таблицы это архидолго - полчаса и после отфильтрованного уже начинает шевелиться наконец таблица
Изменено: Алексей - 20.06.2017 19:53:01
 
 
 
#21
20.06.2017 19:41:38
Цитата
Алексей написал: как бысть удадить
На каком языке написано?
 
 
 
#22
20.06.2017 19:53:33
Цитата
vikttur написал:
На каком языке написано?
прошу прощения, поправил
 
 
 
#23
20.06.2017 20:13:30
Провёл эксперимент на своей старенькой машине (ХР, 2010 [32], 2М оперативной, дохлый процессор 1,9 ГГц и 2 ядра):
Заполнил на листе таблицу 100 000 строк на 10 столбцов. В каждой ячейке текст с точкой.
Ctrl+H точка на запятую. Потребовалось около 500 секунд, но Excel не упал - и всё поменялось )  
 
 
 
#24
20.06.2017 20:52:36
Цитата
Дмитрий Тарковский написал:
Алексей , скиньте обезличенный небольшой пример текстового файла, отражающий реальную картину.
Может при импорте текстового файла менять запятую на точку в нужной колонке? Приложите пример текстового файла только поменяйте данные чтоб не раскрывать конфиденциальную информацию.  
 
 
 
#25
20.06.2017 22:12:16
укороченный пример (исходник и  сразу после импорта)
только у меня 150 000 строк
пробую power pivot  тоже не быстро и вообще вылетает
 
 
 
#26
20.06.2017 22:21:00
Цитата
Юрий М написал:
но Excel не упал - и всё поменялось )  
возможно все же из-за размера выборки, где-то до миллиона значений нормально
а потом вот так
смотрите скрин

после этого правда курсор встает в недоделанном месте и можно дальше выделять и менять и так несколько раз
21 век
 
 
 
#27
20.06.2017 22:39:57
21 век - это просто обработать выборку с помощью Power (Query, Pivot, etc)
20 век - обработать входные данные, используя dynamic-SQL и ODBC/ADO/DAO/COM
Ззаниматься обработкой кучи (десятков-сотен тысяч) записей  хоть средствами фейса XL, хоть формулами - это моветон...
 
 
 
#28
21.06.2017 00:05:19
начал импортировать через - создать запрос
там замена миллионов точек на запятые происходит мгновенно
 
 
 
#29
21.06.2017 01:09:52
Ну, не "мгновенно" - просто это воспринимать надо как "числа в источнике данных имеют разделителем десятичных разрядов точку". Результат - запрос читает исходник в потоке, не занимаясь преобразованиями типов в результирующей таблице.

А "продолжить" можно тем, что вообще и весь анализ (по поводу "построчный поиск соответствий"), и получение результата ("далее при попытке копирования всей таблицы из одного файла в другой") можно возложить на Power. Будет быстро, качественно и совсем не сердито  :)  
 
 
 
#30
21.06.2017 02:01:42
Цитата
AndreTM написал:
можно возложить на Power
пока не привело к нужному результату
опыта нет + вылеты экселя

пока только импорт и уже все быстрее пошло
 
 
 
Страницы: 1
Читают тему
Наверх