Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Долгий пересчет файла, в файле формулы, 30 листов,15 сводных, данные пополняются
 
#1
13.07.2015 12:55:17
Уважаемые господа, добрый день!

Вопрос следующий.
Я создал файл excel, в нем около 30 листов данных и 15 сводных таблиц и около 10 макросов.  
На каждом листе есть одна таблица. Размеры таблиц от 20 до 250 столбцов и от 100 до 5  000 строк.
В каждой таблице примерно каждый 5-й столбец имеет формулы на всю длину таблицы, и в большинстве случаев ссылки на другие листы. Основная формула ЕСЛИ(IF) и ВПР (VLOOKUP), но чаще она входит в состав других формул. Также есть много формул для работы с текстом. TRIM, TEXT, и с датами - преобразование даты в год неделю месяц и т. д. Есть немного выпадающих списков.
Все это я сделал в основном на основании видеоуроков с этого сайта и Вашего форума. Спасибо огромное Николаю, и всем участникам!
Единственный макрос который я очень люблю и нашел на другом сайте - это СЦЕПИТЬЕСЛИ. То есть по условию сцепляет текст. Очень нужная функция. Простыми инструментами эксель я не нашел как сделать...

Но вопрос в другом.
Данные в мой файл только добавляются! Сейчас он весит уже около 30 мб. Я уже давно отключил автоматический пересчет. Но и сейчас с пересчетом вручную он нереально тормозит!!! У меня процессор Intel Core i7-4790K и 16 Гб оперативы. Куда мощнее я не знаю. Но с файлом невозможно работать. Бывает когда я внесу данные мой файл пересчитывается около 30 минут. Так работать невозможно!

ЕСТЬ ЛИ ВЫХОД ИЗ ЭТОЙ СИТУАЦИИ? Или просто возможности эксель ограничены и он дальше не вывозит.

P/s/ Пробовал разбить листы на разные файлы, но только хуже. Вернул обратно.
Мне кажется что причина в бесконечных и постоянно активных формулах.
Если бы на всех листах были только значения, наверное эту проблему можно решить.

Но я попробовал написать макрос (с помощью кнопки "запись"), по другому к сожалению не умею, в макросе была вставлена формула - пересчет - и в конце вставить значения. Вроде вставить только значения получилось, но этот макрос работает около 1 часа. Работать также невозможно...
 
 
 
#2
13.07.2015 13:00:38
Переведите все на 1С. Но кто нибудь посоветует Вам Access  :) .
Excel + SQL = Activetables
 
 
 
#3
13.07.2015 13:03:06
А может SQL. Я не очень силен в этом. Прочитал про него в википедии. Ни разу не работал.

А что значить "переведите все на 1С." не понял. Прошу прокомментировать.
 
 
 
#4
13.07.2015 13:05:51
Цитата
Oleg777 написал: А что значить "переведите все на 1С." не понял
Платформа для быстрой разработки бизнес приложений фирмы 1С. http://v8.1c.ru/overview/Platform.htm. Я считаю, что легче и быстрее сделать на нем чем на access.
Excel + SQL = Activetables
 
 
 
#5
13.07.2015 13:06:49
15 сводных таблиц - это 15 дополнительных областей оперативки, где содержатся данные для каждой конкретной сводной. М.Б. как-то обойтись одной, но изменять ее источник для получения нужных отчетных данных?
Еще можно попробовать поудалять не занятые данными строки/столбцы на каждом листе (чтобы исключить их из рабочей области листа, т.к. если вы даже случайно перескакивали в последнюю строку/столбец листа при занесении данных, чего-то туда вбивали, потом сразу удаляли - все эти строки включены в состав рабочей области). Надо тупо выделить строки от последней строки с данными до последней строки на листе и через ПКМ - удалить. Тоже самое со столбцами.
Еще вариант - если данные поступали из разных файлов, то возможно накапливание излишнего количества стилей ячеек в файле (на эту тему есть статья в разделе приемы, и способы устранения в ней описаны).
По поводу оптимизации формул - не знаю, ибо не видя их не посоветуешь.

Сейчас наши корифеи более точно подскажут, а пока - чем могу, так сказать... :)
Кому решение нужно - тот пример и рисует.
 
 
 
#6
13.07.2015 13:08:27
ВПР по неотсортированным данным с точным поиском - медленный, конечно.
много-много таких ВПР-ов способны делать чудеса.

я не знаю общих рекомендаций, способных кардинально улучшить ситуацию.
как правило - смотреть надо конкретно.
менять структуру данных, упрощать формулы, минимизировать (или совсем убирать) УФ, использовать сортировку, формулы заменять на макросы, медленные макросы заменять на правильные...
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#7
13.07.2015 13:09:25
Если хотите оставить в Excel.
1.Каждый лист в отдельную книгу.
2.Каждый отчет в отдельную книгу.
3.Никаких формул, все данные собирать макросами.(SQL запросами)
Excel + SQL = Activetables
 
 
 
#8
13.07.2015 13:17:25
Цитата
PowerBoy написал: 3.Никаких формул, все данные собирать макросами.(SQL запросами)
Спасибо всем огромное.
Это получиться как аналог запросов в Access? Вообще без формул? Запрос запустил и в таблице появляются значения. Это очень круто! Мне кажется это может сильно улучшить ситуацию.

А можно один пример такого SQL запроса на макросах.
 
 
 
#9
13.07.2015 13:26:15
Цитата
Пытливый написал:  М.Б. как-то обойтись одной [сводной], но изменять ее источник для получения нужных отчетных данных?
Я ищу от ПК, будь то excel (как сейчас или что то другое), максимального решения своих задач. Каждый раз менять представление отчета из сводной таблицы - это гемор. А так я зашел на нужный лист, обновил, и сразу печатаю. Это намного продуктивней.
Цитата
[USER=92]Еще можно попробовать поудалять не занятые данными строки/столбцы на каждом листе... выделить строки от последней строки с данными до последней строки на листе и через ПКМ - удалить. Тоже самое со столбцами.
Я работаю с форматом таблицы. И в "белые" поля, куда не распространяется область таблицы значения не вношу. Только в формате таблицы у меня заполненных около 5 000 строк... Или я что то неправильно понял?
Цитата
[USER=92]По поводу оптимизации формул - не знаю, ибо не видя их не посоветуешь.
Обычная моя формула = IF(A7=0;VLOOKUP(...);VLOOKUP(...)) - и это одна из самых простых. Они мне такие нужны, для моей задачи. Но конечно оптимизация процесс бесконечный. Буду работать над этим. Спасибо!
 
 
 
#10
13.07.2015 13:39:20
Цитата
ikki написал: много-много таких ВПР-ов способны делать чудеса.
Это точно!!! И еще ЕСЛИ (IF). И чудеса это мягко сказано. Они могут помочь максимально исключить человеческий фактор. Простейшая форма ИИ, если так можно сказать.)))

Цитата
я не знаю общих рекомендаций, способных кардинально улучшить ситуацию.
Жаль конечно! А может можно в другой какой-нибудь программе работать? А excel использовать как средство выхода данных??? Как это во многих современных программах устроено? Но как этого добиться, я не знаю. Трудиться готов!!! Не знаю дорогу, которой нужно идти!!! Поэтому к Вам за советом и пришел.

Може какой нибудь SQL server express?
Цитата
менять структуру данных, упрощать формулы, минимизировать (или совсем убирать) УФ, использовать сортировку, формулы заменять на макросы, медленные макросы заменять на правильные...
Мне кажется это все "таблетки", а не решение причины болезни, т.к. это сейчас 5 000 строк, а через год будет 20 000...

Вот формулы заменить на макросы - хорошая идея. Но я этого не умею делать к сожалению...
 
 
 
#11
13.07.2015 13:52:33
Цитата
Oleg777 написал: Мне кажется это все "таблетки"
простой пример.
для того, чтобы найти значение в неотсортированном массиве из 1000 записей, нужно в худшем случае просмотреть все 1000, в среднем - 500.
в отсортированном - в худшем случае логарифм по основанию 2 от 1000 = 10 записей.
при увеличении до 100000 - соответственно 100000, 50000 и 17.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#12
13.07.2015 13:55:16
Тогда я Вас не понял...
А что значит вся эта фраза?)))
Цитата
ikki написал: в отсортированном - в худшем случае логарифм по основанию 2 от 1000 = 10 записей.
 
 
 
#13
13.07.2015 13:57:55
Цитата
Oleg777 написал: что значит вся эта фраза?
гм. честно говоря, только то, что в ней написано.
аллюзий и скрытых смыслов в ней нет.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#14
13.07.2015 14:03:59
Я не понял как так сделать чтобы ВПР при поиске просматривал не 1000 записей, а 10???)))
 
 
 
#15
13.07.2015 14:08:24
массив должен быть отсортирован по возрастанию того столбца, в котором ВПР ищет значение.
последним параметом ВПР должно быть ЛОЖЬ
один нюанс - если у Вас нет уверенности, что искомое значение обязательно есть в таблице, то нужна дополнительная проверка с ВПР по первому столбцу (т.е. два ВПР-а)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#16
13.07.2015 14:14:45
в файле.

хотя...
я согласен - для 30Мб+ конечно, нужно искать более комплексные решения
Изменено: ikki - 13.07.2015 16:11:10
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#17
13.07.2015 14:21:16
Спасибо, за ответ!
Подскажите про SQL server express и SQl запрос для Экселя? Стоит ли этим пользоваться для решения моей задачи и если да, то как???
Изменено: Oleg777 - 13.07.2015 16:10:13 (Изменение сообщения собеседником.)
 
 
 
#18
13.07.2015 14:30:48
Вот пример SQL запроса из той же книги:

Код
Public Sub RefreshData()'Created using add-in ActiveTables
Dim strConnection As String
Dim strSQL As String
strConnection = IIf(Val(Application.Version) < 12, "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 8.0;HDR=YES;IMEX=3';", "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=YES;IMEX=3';")
strSQL = "select 1 as Mes,* from [Jan$] union all select 2 as Mes,* from [Feb$] union all select 3 as Mes,* from [Mar$]"
With ThisWorkbook.Sheets("Itog")
 .UsedRange.Clear
 With .QueryTables.Add(strConnection, .Range("A1"), strSQL)
 .Refresh False
 .Delete
 End With
End With
End Sub
Excel + SQL = Activetables
 
 
 
Страницы: 1
Читают тему
Наверх