Страницы: 1 2 След.
RSS
Ускорение работы Excel.
 

Добрый день. Имеется книга эксель, в которой несколько листов, и одним из них является реестр ТТ.В этом листе более 14000 строк с информацией (с 58 столбцами). Информация в формате умной таблицы (делал обычный формат, не помогло). К этому листу через функции обращаются остальные листы. Совсем внезапно начались зависания на листе «Реестр» (идет медленная прокрутка, медленно реагирует на активацию мышкой ячейку(8сек) и т.д), на других листах книга работает стабильно. Как можно исправить данные зависания. Переносил данный лист в отдельную книгу, зависания остались. Книга сохранена в двоичном формате. Спасибо!  

 
Цитата
Xat написал:
начались зависания на листе «Реестр
На этом листе используются "летучие" функции, пересчитывающиеся при каждом чихе, а каждый чих устраивает какой-нибудь макрос, например.
Цитата
Xat написал:
Как можно исправить данные зависания
Купить новый компьютер с 16 ядрами и тактовой частотой 32ГГерц, например.
 
Xat, файл-пример приложить всё же стоит: непонятно какие функции, как используются и т.д.
Попробуйте отключить автовычисление значений (т.к. именно из-за него, как я понимаю, у Вас подвисает всё).
Вообще использовать много функций в больших таблицах - плохая практика, большие объёмы данных лучше обрабатывать макросами.
 
Андрей VG,Летучих формул нету, ушел от макросов уже давно (файл зависал очень сильно).Даже прокрутка мышкой вниз или вверх идет с зависанием.
Цитата
Андрей VG написал:
Купить новый компьютер с 16 ядрами и тактовой частотой 32ГГерц, например.
Неплохой вариант , поговорю с руководством))
Irregular Expression, Файл приложить не могу , так как содержит информацию компании. Принцип формул очень прост:

=СЧЁТЕСЛИ(Реестр!I6778:I7029;"1")
=СУММПРОИЗВ(--((Реестр!I6778:I7029+Реестр!J6778:J7029)>0))
=СУММПРОИЗВ((Реестр!L6778:L7029="+")*(Реестр[[#Заголовки];[Электроустановочная продукция]:[Напольные покрытия ]]=E150)*(Реестр!G6778:J7029))

Функции простые , но их много, так как в реестре много городов , и они обращаются к разным диапазонам.

Если я правильно понимаю, если дело в вычислениях , то зависания должны были пропасть , когда перенес лист в отдельную книгу , так как на листе "Реестр" нету ни одной функции?

PS
Вариант отключения автовычисление значений не помог.
Изменено: Xat - 14.03.2018 12:29:42
 
Цитата
Xat написал:
.Даже прокрутка мышкой вниз или вверх идет с зависанием
Такое может наблюдаться еще в следующих случаях:
1. На листе установлена группировка(Данные -Группировка, Данные -Структура или Промежуточные итоги).
2. Используется избыточное условное форматирование или оно неверно(не оптимально) сформировано.
+3. Плюсом ко всему этому может быть закрепление областей. Самостоятельно оно на такое не способно, но вместе с другим функционалом визуальной обработки данных вполне может дать подобный эффект.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Xat,  Комп физический или виртуалка? А не отключено ли использование аппаратного ускорения графики в настройках?
По вопросам из тем форума, личку не читаю.
 
Цитата
Xat написал:
Файл приложить не могу , так как содержит информацию компании.
Никто ж не мешает обезличить информацию и обрезать табличку до пары десятков строк. Но по своему опыту могу сказать, что использование смарт-таблиц для таких массивов с большим количеством строк-столбцов очень негативно сказывается на производительности. Особенно если в таблице имеются вычисляемые столбцы.
Вот горшок пустой, он предмет простой...
 
Кстати, еще один момент: если установлен флажок "Показывать разбиение на страницы"(Файл -Параметры -Дополнительно, в группе параметров отображения листа), то тоже могут наблюдаться тормоза.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
СУММПРОИЗВ() - старая и довольно медленная функция. Если у Вас несколько сотен (не говоря о тысячах) ячеек с такими формулами, этого уже достаточно для заметного подвисания файла.
 
Цитата
Irregular Expression написал:
СУММПРОИЗВ() - старая и довольно медленная функция
Позволю не согласится, не все старые медленные,  и не все новые быстрые :-) . Cравнивали как-то -  да есть проигрыш в сравнении с встроенным аналогом при одинаговых условиях  (sumif(s) , countif(s) ), но не такой значительный, хотя при многотысячном использовании набежит. Прежде всего влияет область расчета. встроенные сами определяют используемую, а вот SUMPRODUCT нет и для него смерти подобно указать весь столбец.

Онако расчитанный экемпляр листа не должен тормозить при скроллинге, при использовании вышеуказанных формул и это подтверждается тем, что при отключении расчета ничего не меняется., и тут соглашусь с Дмитрий Щербаков, УФ, и прочая визуализация может дать негативный эффект.
По вопросам из тем форума, личку не читаю.
 
Дмитрий Щербаков, Переместил лист в отдельную книгу , и провел манипуляции по Вашим рекомендациям.
1-Убрал всю группировку.
2-Убрал все условное форматирование, удалил все правила.
3-Снял все закрепление(верхняя строка была закреплена  ).
4-Флажок не стоит (Показывать разбиение на страницы)

Файл по прежнему тормозит((

БМВ, Физический.По поводу "использование аппаратного ускорения графики" не могу сказать , надо у айтишников спрашивать ( это процесс не быстрый) Даже выделяет строки и столбцы с задержкой( просто мышкой )


Получается дело не в функциях , так как лист в отдельной книге ,и в самом листе нету функций. Тогда не могу понять в чем еще может быть причина  
Изменено: Xat - 14.03.2018 14:08:50
 
Цитата
Irregular Expression написал:
Если у Вас несколько сотен (не говоря о тысячах) ячеек с такими формулами, этого уже достаточно для заметного подвисания файла.
14000 строк без единой функции , просто текстовая информация.  
 
Цитата
Xat написал:
По поводу "использование аппаратного ускорения графики" не могу сказать , надо у айтишников спрашивать
Зачем? Зайдите в Файл -Параметры -Дополнительно -группа Экран. Там есть галочка: Отключить аппаратное ускорение обработки изображения. Поиграйтесь с ней.

Цитата
Xat написал:
14000 строк без единой функции
а нет ли случаем именованных диапазонов/функций? (Ctrl+F3)
Или может еще изображений и объектов, в том числе скрытых? Можете еще отсюда пару советов испробовать: Как уменьшить размер файла
Изменено: Дмитрий Щербаков - 14.03.2018 14:13:09
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Дмитрий Щербаков,Поигрался, результатов не дало( Проделал советы из ссылки , тоже без результатов , сейчас это голая таблица с данными , но по прежнему виснет
Цитата
Дмитрий Щербаков написал:
а нет ли случаем именованных диапазонов/функций? (Ctrl+F3)
Нету. Так же нету изображений и объектов.Обычная таблица с данными.

Думаю зависания начали происходить , после того как добавил в реестр строки, примерно 40шт , и потом забил в них данные , в ручную.  
Изменено: Xat - 14.03.2018 14:48:27
 
Думаю по симптомам, что проблема в файле, а не в железе. Попробуйте в файле из #11 стереть "конфиденциальные" данные. Есть файл будет по-прежнему тормозить, то выложите ссылку.
Владимир
 
sokol92,Не совсем Вас понял ,откуда и  что именно стереть?  
Изменено: Xat - 14.03.2018 15:07:59
 
Xat, Попробуйте сперва перенести все значения, именно как значения, в новую книгу и на новый лист. Посмотреть что там будет.
По вопросам из тем форума, личку не читаю.
 
БМВ,Скопировал всю таблицу , и вставил в новую книгу , как значения , все работает отлично.Но тогда теряется вся связь с другими листами.Там очень много функций на других листах , настроены на этот лист  
Изменено: Xat - 14.03.2018 15:13:42
 
Цитата
Xat написал:
Там очень много функций настроены на этот лист
Цитата
Xat написал:
14000 строк без единой функции , просто текстовая информация
так может как раз эти все связи и функции, завязанные на лист и тормозят?
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Цитата
Xat написал:
Там очень много функций на других листах , настроены на этот лист  
оооо, пошли скрытые возможности и детали. :-)
По вопросам из тем форума, личку не читаю.
 
Дмитрий Щербаков,Я перенес этот лист в другую книгу , по идеи к нему не должны обращаться функции , пока не открою книгу с остальными листами( где имеются функции). Но тормозить не перестал , и разве функции могут влиять на просто прокрутку листа?  
Изменено: Xat - 14.03.2018 15:20:15
 
БМВ,Вроде не скрывал их , сразу описал что на листе "Реестр" нету функций,просто текстовая информация , а на остальных есть и они обращаются на этот лист , пример функций изложил выше  
 
Xat,
Цитата
Xat написал:
пока не открою книгу с остальными листами( где имеются функции)  
значит разрушен лист. Перенесите данные и назовите и лист и книгу, как в нерабочей. Связи перелинкуются, а вот тормоза могут пропасть.
По вопросам из тем форума, личку не читаю.
 
У Вас сейчас есть "новая" книга, в которой проблемный лист "тормозит". Если кнопка в меню Данные/Изменить связи активна, то для каждой показанной внешней связи нажмите "Разорвать связь". Сообщите, пожалуйста, о результате.
Изменено: sokol92 - 14.03.2018 15:31:24
Владимир
 
БМВ,Лист действительно разрушен , удалил всю информацию с него , он все равно виснет , теперь не получается настроить связь на новый лист.
Как это грамотно сделать? Сначала скопировал все данные на новый лист( вставка значений) , потом удалил старый лист , назвал новый "Реестр" не прокатило((

sokol92, Кнопка в меню Данные/Изменить связи не активна.
 
Как я понял, лист уже в отдельной книге и есть линки из другой книги. Если не так то делаем именно так. (можно конечно потом и все поиском с заменой творить, но зачем)
1. закрываем обе книги.
2. книгу с разрушенным листом (имя запоминаем) переименовываем.
3. Создаем новую книгу с именем, что помним с п.2
4. Создам в ней лист с именем разрушенного. Переносим туда данные с разрушенного листа  копированием только значений.
5. Записываем.
6. Открываем книгу ссылающуюся на нее, проверяем что формулы ссылаются куда надо.

только уже не по теме отвечаю получается, ибо определили, что тормозом послужил разрушенный лист и теперь второй вопрос, как исправить
Изменено: БМВ - 15.03.2018 10:10:51
По вопросам из тем форума, личку не читаю.
 
Причина таких тормозов может быть в том, что на листе много объектов-прямоугольников с нулевой высотой или шириной, поэтому их не видно. Можно сохранить книгу в XLS-формате и удалить их так:
Код
Sub ShapesDelete()
  ActiveSheet.Rectangles.Delete
End Sub

или более цивилизованно:
Код
Sub ShapesDelete1()
  Dim i As Long
  i = ActiveSheet.Rectangles.Count
  If i > 0 Then
    If MsgBox("Удалить объекты-прямоугольники, " & i & " шт ?", vbYesNo) = vbYes Then
      ActiveSheet.Rectangles.Delete
    End If
  Else
    MsgBox "Объектов прямоугольников на листе нет"
  End If
End Sub

В XLS формате удаление мгновенное, а в современном формате книги удаление может выполняться о-о-чень долго. После удаления - сохранить в нужном формате.

Можно и без конвертирования в XLS:
1. Закрыть Excel
2. Переименовать "MyFile.xlsx" to "MyFile.xlsx.zip"
3. Открыть архив "MyFile.xlsx.zip" в проводнике или архиваторе, удалить папку внутри архива:
XL\DRAWINGS и сохранить изменения
4. Переименовать обратно "MyFile.xlsx.zip" в "MyFile.xlsx"
5. Открыть "MyFile.xlsx" в Excel, игнорируя предупреждение об удалении автофигур, сохранить книгу.
После этого все будет по-быстрому.
Изменено: ZVI - 15.03.2018 13:07:01
 
Иногда бывает то, о чем написал Дмитрий в сообщении #8 - при отображении разметки страниц листа задействуется и тормозит обычную навигацию на листе драйвер принтера. В такой режим можно попасть не только из Вид - 'Разметка страниц' или 'Страничный режим', но и после предварительного просмотра или экспорта в PDF. В некоторых случаях (зависит от драйвера принтера), даже без каких-либо действий на листе автофигуры начинают периодически вам подмигивать.
Устраняется таким кодом (публиковал когда-то):
Код
Sub FasterFaster()
  Dim Sh As Worksheet
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  With ActiveSheet
    For Each Sh In Worksheets
      Sh.DisplayAutomaticPageBreaks = False
      Sh.Activate
      ActiveWindow.View = xlNormalView
    Next
    .Activate
  End With
  Application.ScreenUpdating = True
  Application.EnableEvents = True
End Sub
Изменено: ZVI - 15.03.2018 12:13:33
 
БМВ,Большое спасибо , данное руководство помогло , теперь все работает шустро.Функции работают.

ZVI, Спасибо. Попробую проделать данные манипуляции со старым файлом. О результатах отпишусь.
 
Цитата
Irregular Expression написал:
большие объёмы данных лучше обрабатывать макросами.
Цитата
Xat написал:
ушел от макросов уже давно (файл зависал очень сильно)
:)  Как раз с макросами всё будет гораздо быстрее, что до  Вас и пытаются донести.
Страницы: 1 2 След.
Наверх