Страницы: 1
RSS
Как установить точную причину подвисания файла
 
Добрый вечер.
Есть файл, 5 тыс. строк, 25 столбцов. Вес 3,5 мб.
При фильтрации ужасно висит, долго сохраняется. Пытаюсь установить точную причину такого поведения.
В файле есть ВПР формулы с ссылками на внешние книги.
Если разрываю внешние связи, то это ни на секунду не ускоряет файл.
Также пробовал выделить весь лист и нажать очистить форматы - также ни на секунду не ускоряет файл.
Но если сделать и то и другое сразу, то файл начинает работать молниеносно.

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

Можно ли на основании этого предположить что  именно тормозит файл? Если такое протягивание убирает лаги полностью.
 
Условной форматирование на листе есть?
По вопросам из тем форума, личку не читаю.
 
БМВ, есть, но я так понимаю дело не в нем, потому что, удалив все правила, прироста быстродействия не замечено.
И вот это заполнение таблицы просто цифрами решает проблему с тормозами, но ведь не убирает форматирование.

Файл то не показать никак( Удалял из книги все листы, кроме одного с таблицей. Все макросы удалил. Разрывал внешние связи и сокращал таблицу раз в 5 по количеству строк.
И вот только заполнив ее цифрами или очистив от форматов, лаги уходят. Причем лаги уходят не постепенно, а резко. Как-будто в одном месте где-то что-то есть только. Объекты проверял - их нет. Файл открывал как архив, самые большие там файлы это SharedString и CalcChain xml.
 
Цитата
whateverlover написал:
вбил цифру 1, в другую ячейку 2
значит ищите связь с этим. в слепую тут можно гадать долго.
Изменено: БМВ - 10.04.2020 09:35:50
По вопросам из тем форума, личку не читаю.
 
Цитата
whateverlover написал:
Хотел выложить файл, может кто-то посмотрел бы, но: данные конфидениальные
Что там конфиденциального? Впр тянет конфиденциальные данные? Так настройте впр временно на обезличенную таблицу в том же файле. Остальное секретное замените и выложите файл в облако для нас.
Изменено: Михаил Л - 10.04.2020 09:42:32
 
https://yadi.sk/i/CcO9ZuaAbsntSQ
в файле уже разорваны внешние связи
надо заметить, что даже просто заменяя данные на цифры, файл ускорился, изначально он работает намного дольше
но даже сейчас, попробуйте в каком-то столбце фильтром выбрать позиции, а потом снять фильтр, такой небольшой файл без внешних связей не должен столько думать
изначально файл - это выгрузка из 1с
также некоторые строки залиты у меня до конца таблицы вправо)
 
Цитата
whateverlover написал:
даже просто заменяя данные на цифры, файл ускорился
А эти данные были просто цифрами или формулами тянулись?
Цитата
whateverlover написал:
попробуйте в каком-то столбце фильтром выбрать позиции, а потом снять фильтр
Задержка не более секунды
Видать при подготовке нам данные вы удалили то, что задерживало вычисление
Вообще формулы простые. Непонятно почему у вас виснет. Попробуйте этот файл открыть на другом компьютере. Будет ли виснуть?  
 
Я на отдельном листе сделал столбец с числами от 1 до 3300 и оттуда скопировал вместо столбцов с данными, которые не желательны к показу.
Открывал на двух компьютерах, на обоих виснет.
Вот поэтому изначально и не мог фал-пример выложить, потому что готовя его, проблема как бы уходит.

А попробуйте выделить лист и нажать очистить форматы. Станет вообще моментально работать. У меня раз в 10 быстрей сразу.


upd. на Excel 2016 лагов меньше, изначально все делал на 2010
Изменено: whateverlover - 10.04.2020 10:38:29
 
Что должна делать эта формула?
=IF(MID(TDSheet!$C$2:$C$3957;1;9)="Приходный";"ПКО";IF(MID(TDSheet!$F$2:$F$3957;1;31)="//ПРИЛОЖЕНИЕ//Страховые платежи";"ИПТ СБЕРБАНК";IF(MID(TDSheet!$F$2:$F$3957;22;4)="4723";"POS-терминал";IF(MID(TDSheet!$F$2:$F$3957;58;11)="N810-7-0052";"ИПТ ГПБ";"Прочие"))))
Зачем указан диапазон а не c2,f2
По вопросам из тем форума, личку не читаю.
 
Форумала по значению в других ячейках определяет тип платежа.
Изначально это была умная таблица и в формуле были просто заголовки, потом пробуя все варианты, я, наверное, сделал таблицу неумной и заголовки поменялись на диапазоны.
Изменено: whateverlover - 10.04.2020 10:41:45
 
там были не заголовки а ссылки на ячейки той же строки в нотации умной таблицы. Замените, протяните и смотрите на  оригинальных данных. Это касается не толкьо этой формулы. Проверяйте все столбцы, бегло прошел еще 3-4 таких есть.
Изменено: БМВ - 10.04.2020 10:45:03
По вопросам из тем форума, личку не читаю.
 
вот так в исходном файле это выглядит
Код
=ЕСЛИ(ПСТР([Документ оплаты];1;9)="Приходный";"ПКО";ЕСЛИ(ПСТР([Назначение платежа];1;31)="//ПРИЛОЖЕНИЕ//Страховые платежи";"ИПТ СБЕРБАНК";ЕСЛИ(ПСТР([Назначение платежа];22;4)="4723";"POS-терминал";ЕСЛИ(ПСТР([Назначение платежа];58;11)="N810-7-0052";"ИПТ ГПБ";"Прочие"))))

это разве неправильно?
я когда при написании формулы нажимаю на ячейки, он сам мне имя столбца ставит
 
так может и правильно , если  вы хотите со всем столбцом работать. но мне кажется вы строку обрабатываете а тогда [@Документ оплаты]
Изменено: БМВ - 10.04.2020 11:37:49
По вопросам из тем форума, личку не читаю.
 
БМВ, добавил "собак", быстрей не стало, но это косяк) формулы вставлял через VBA и не учел этот момент, спасибо!
Кажется, проще файл собрать заново( Что в файле не меняй, все равно чистый пошустрей будет, как свежепереустановленный windows)
Просто было интересно, что конкртено так тормозит, чтобы на те же грабли не наступить впоследствии.
 
Здесь описывал возможные причины и методы борьбы с зависаниями при фильтрации:
Файл долго фильтрует строки/тормозит при фильтрации
и на всякий случай:
Excel тормозит/зависает при копировании ячейки/диапазона
Изменено: Дмитрий(The_Prist) Щербаков - 10.04.2020 12:26:27
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Как варинт запустить вот этот макрос в корявом файле, мене помогает в 90% случаев.
Код
Sub ПочиститьФаилZVI()
' ZVI:2009-08-08 Active workbook excess formatting clearing
' Idea & original code of Just_Jon: http://www.mrexcel.com/forum/showthread.php?t=120831
' First attempt of modification: http://www.mrexcel.com/forum/showthread.php?t=339144
' Bugs tracking:
' 1. Range("A1") fixed to .Range("A1")
' 2. Exchanging:
'   .EntireRow.RowHeight to ws.StandardHeight
'   .EntireColumn.ColumnWidth to ws.StandardWidth
' Revised:2010-06-16
' 3.(ZVI:2010-06-16) Code for StandardHeight corrected. Comments shapes are skipped
'   https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&TID=73659&MID=617994#message617994
' 4.(ZVI:2019-03-08) Deleting of entire columns & rows is used instead of Clear method
'   https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&TID=114886&MID=954470#message954470
  Const Title = "MrExcelDiet: Just_Jon's code modified by ZVI"
  Const vbTab2 = vbTab & vbTab
  Dim Wb As Workbook, Ws As Worksheet, LastCell As Range, Shp As Shape, Chrt As Chart
  Dim Prot As Boolean, ProtWarning As Boolean, DoCharts As Boolean
  Dim LastRow&, LastCol&, ShpLastRow&, ShpLastCol&, i&, ac, x
  Dim SheetsTotal&, SheetsCleared&, ChartsCleared&, SheetsProtSkipped&
  Dim FileNameTmp$, BytesInFileOld&, BytesInFileNew&
  ' Choose the clearing mode
  Set Wb = ActiveWorkbook
  x = MsgBox("Excess formatting clearing of " & Wb.Name & vbCr & vbCr & _
             "Apply full clearing?" & vbCr & vbCr & _
             "Yes" & vbTab & "- Full mode, including chart's AutoScaleFont=False" & vbCr & _
             "No" & vbTab & "- Medium mode, without charts processing" & vbCr & _
             "Cancel" & vbTab & "- Stop clearing & Exit", _
             vbInformation + vbYesNoCancel, _
             Title)
  If x = vbCancel Then Exit Sub
  DoCharts = (x = vbYes)
  ' Freeze on
  With Application
    .ScreenUpdating = False
    .EnableEvents = False
    ac = .Calculation: .Calculation = xlCalculationManual
  End With
  ' Calculate the old file size
  On Error Resume Next
  With CreateObject("Scripting.FileSystemObject")
    FileNameTmp = .GetSpecialFolder(2) & "\" & Wb.Name & ".TMP"
    Wb.SaveCopyAs FileNameTmp
    BytesInFileOld = .GetFile(FileNameTmp).Size
  End With
  ' Processing in each worksheet
  ProtWarning = True
  SheetsTotal = Wb.Sheets.Count
  For Each Ws In Wb.Worksheets
    With Ws
      ' Clear an error flag
      Err.Clear
      ' Inform on processing status
      Application.StatusBar = "MrExcelDiet: processing of sheet " & Ws.Name
      ' Check protection
      Prot = .ProtectContents
      ' Try to unprotect without password
      If Prot Then .Unprotect ""
      If (Err <> 0 Or .ProtectContents) And ProtWarning Then
        SheetsProtSkipped = SheetsProtSkipped + 1
        x = MsgBox(Ws.Name & " is protected and will be skipped" & vbCr & vbCr & _
                   "Skip warning on protected sheets?" & vbCr & vbCr & _
                   "Yes" & vbTab & "- Skip warning, clear sheets silently" & vbCr & _
                   "No" & vbTab & "- Warning on each protected sheets" & vbCr & _
                   "Cancel" & vbTab & "- Stop clearing & Exit", _
                   vbExclamation + vbYesNoCancel, _
                   Title)
        ProtWarning = (x = vbNo)
        If x = vbCancel Then GoTo exit_
      Else
        ' Count processed worksheets
        SheetsCleared = SheetsCleared + 1
        ' Determine the last used cell with a formula or value or comment in Ws
        Set LastCell = GetLastCell(Ws)
        ' Determine the last column and last row
        If Not LastCell Is Nothing Then
          LastCol = LastCell.Column
          LastRow = LastCell.Row
        End If
        ' Determine if any merged cells are beyond the last row
        For Each x In Range(.Cells(LastRow, 1), .Cells(LastRow, LastCol))
          If x.MergeCells Then
            With x.MergeArea
              LastRow = Max(LastRow, .Rows(.Rows.Count).Row)
            End With
          End If
        Next
        ' Determine if any merged cells are beyond the last column
        For Each x In Range(.Cells(1, LastCol), .Cells(LastRow, LastCol))
          If x.MergeCells Then
            With x.MergeArea
              LastCol = Max(LastCol, .Columns(.Columns.Count).Column)
            End With
          End If
        Next
        ' Determine if any shapes are beyond the last row and last column
        ShpLastCol = LastCol
        ShpLastRow = LastRow
        For Each Shp In .Shapes
          If Shp.Type <> msoComment Then  ' ZVI:2010-06-16
            ShpLastCol = Max(ShpLastCol, Shp.BottomRightCell.Column)
            ShpLastRow = Max(ShpLastRow, Shp.BottomRightCell.Row)
          End If
        Next
        ' Clear cells beyond the last column
        If LastCol < .Columns.Count Then
          With .Range(.Columns(LastCol + 1), .Columns(.Columns.Count))
            .EntireColumn.Delete ' rev4.
            If LastCol >= ShpLastCol Then
              ' Set StandardWidth to columns which are beyond the last col
              .EntireColumn.ColumnWidth = IIf(Ws.StandardWidth, Ws.StandardWidth, 8.43)  'Ws.StandardWidth
            End If
          End With
          If ShpLastCol < .Columns.Count Then
            ' Set StandardWidth to columns which are beyond the Shapes
            With .Range(.Columns(ShpLastCol + 1), .Columns(.Columns.Count))
              .EntireColumn.ColumnWidth = IIf(.StandardWidth, .StandardWidth, 8.43)  'Ws.StandardWidth
            End With
          End If
        End If
        ' Clear cells beyond the last row
        If LastRow < .Rows.Count Then
          With .Range(.Rows(LastRow + 1), .Rows(.Rows.Count))
            .EntireRow.Delete ' rev.4
            If LastRow >= ShpLastRow Then
              ' Set StandardWidth to rows which are beyond the last row
              .EntireRow.RowHeight = IIf(Ws.StandardHeight, Ws.StandardHeight, 12.75)
            End If
          End With
          If ShpLastRow < .Rows.Count Then
            ' Set StandardHeight to rows which are beyond the Shapes
            With .Range(.Rows(ShpLastRow + 1), .Rows(.Rows.Count))
              .EntireRow.RowHeight = IIf(.StandardHeight, .StandardHeight, 12.75)
            End With
          End If
        End If
        ' Reset last cell position of the sheet
        With .UsedRange: End With
        ' Protect the sheet if it was unprotected
        If Prot Then .Protect
      End If
      ' Apply setting to worksheet's charts: ChartArea.AutoScaleFont = False
      If DoCharts Then
        For i = 1 To .ChartObjects.Count
          Application.StatusBar = "MrExcelDiet: processing of chart " & .ChartObjects(i).Name
          .ChartObjects(i).Chart.ChartArea.AutoScaleFont = False
          ChartsCleared = ChartsCleared + 1
        Next
      End If
    End With
  Next
  ' Apply setting to workbook's charts: ChartArea.AutoScaleFont = False
  If DoCharts Then
    With Wb
      For i = 1 To .Charts.Count
        ' Clear an error flag
        Err.Clear
        ' Inform on processing status
        Application.StatusBar = "MrExcelDiet: processing of chart " & .Charts(i).Name
        ' Check chart sheet protection
        Prot = .Charts(i).ProtectContents
        ' Try to unprotect chart sheet without password
        If Prot Then .Charts(i).Unprotect ""
        If (Err <> 0 Or .Charts(i).ProtectContents) And ProtWarning Then
          SheetsProtSkipped = SheetsProtSkipped + 1
          x = MsgBox(Ws.Name & " is protected and will be skipped" & vbCr & vbCr & _
                     "Skip warning on protected sheets?" & vbCr & vbCr & _
                     "Yes" & vbTab & "- Skip warning, clear sheets silently" & vbCr & _
                     "No" & vbTab & "- Warning on each protected sheets" & vbCr & _
                     "Cancel" & vbTab & "- Stop clearing & Exit", _
                     vbExclamation + vbYesNoCancel, _
                     Title)
          ProtWarning = (x = vbNo)
          If x = vbCancel Then GoTo exit_
        Else
          ' Set AutoScaleFont = False for chart sheet
          .Charts(i).ChartArea.AutoScaleFont = False
          SheetsCleared = SheetsCleared + 1
          ChartsCleared = ChartsCleared + 1
          ' Protect the chart sheet if it was unprotected
          If Prot Then .Charts(i).Protect
        End If
      Next
    End With
  End If
exit_:
  ' Calculate the new file size
  Wb.SaveCopyAs FileNameTmp
  BytesInFileNew = CreateObject("Scripting.FileSystemObject").GetFile(FileNameTmp).Size
  Kill FileNameTmp
  ' Freeze off
  With Application
    .Calculation = ac
    .ScreenUpdating = True
    .EnableEvents = True
  End With
  ' Report on results
  Application.StatusBar = False
  x = MsgBox("Statistics of excess formatting clearing" & vbLf & vbLf & _
             "Workbook:" & vbTab & ActiveWorkbook.Name & "'" & vbLf & _
             "Sheets total:" & vbTab2 & SheetsTotal & vbLf & _
             "Sheets cleared:" & vbTab2 & SheetsCleared & vbLf & _
             "Protected sheets skipped: " & vbTab & SheetsProtSkipped & vbLf & _
             "Other sheets skipped:" & vbTab & SheetsTotal - SheetsCleared - SheetsProtSkipped & vbLf & _
             "Charts cleared:" & vbTab2 & ChartsCleared & vbLf & _
             "File size old:" & vbTab & Format(BytesInFileOld, "# ### ##0") & " Bytes" & vbLf & _
             "File size new:" & vbTab & Format(BytesInFileNew, "# ### ##0") & " Bytes" & vbLf & _
             vbLf & _
             "Save the cleared workbook to keep the changes?" & vbLf & _
             "Yes" & vbTab & "- Save & Exit" & vbLf & _
             "No" & vbTab & "- Exit without saving, cleared", _
             vbInformation + vbYesNo + IIf(BytesInFileNew < BytesInFileOld, vbDefaultButton1, vbDefaultButton2), _
             Title)
  If x = vbYes Then Wb.Save
End Sub
  
' ZVI:2009-02-02 Get last cell within values/formulas/comments of sheet Sh
' Auto-filtered & hidden rows/columns are also calculated without ShowAllData
' ActiveSheet is used if optional Sh is missing
' If VisibleOnly=True then only visible cells are searched
Function GetLastCell(Optional Sh As Worksheet, Optional VisibleOnly As Boolean) As Range
  Dim SpecCells(), Rng As Range, r&, c&, x, a
  SpecCells = Array(xlCellTypeConstants, xlCellTypeFormulas, xlCellTypeComments)
  On Error Resume Next
  If Sh Is Nothing Then Set Sh = ActiveSheet
  Set Rng = Sh.UsedRange
  If VisibleOnly Then Set Rng = Rng.SpecialCells(xlCellTypeVisible)
  For Each x In SpecCells
    For Each a In Rng.SpecialCells(x).Areas
      With a.Cells(a.Rows.Count, a.Columns.Count)
        c = Max(c, .Column)
        r = Max(r, .Row)
      End With
    Next
  Next
  If r * c <> 0 Then Set GetLastCell = Sh.Cells(r, c)
End Function
  
' Aux function: max value of arguments
Private Function Max(ParamArray Values())
  Dim x
  For Each x In Values
    If x > Max Then Max = x
  Next
End Function

 
Попробую, спасибо)
 
Цитата
whateverlover написал:
Попробую, спасибо)
не поможет
у вас текст там и текстовые операции. заменяя на числа - вы сильно упрощаете расчет. Уберите строки, оставьте 5, Там замените конф данные на абракодабру но оставьте нужны значения. и это сюда. выкладывайте
По вопросам из тем форума, личку не читаю.
 
А что означает формула в T2 (такая же и в других ячейках столбца T):
Код
=TDSheet!$M$2:$M$3957=TDSheet!$R$2:$R$3957
Изменено: sokol92 - 10.04.2020 14:01:40
Владимир
 
sokol92, Приветствую. #9 и далее.
По вопросам из тем форума, личку не читаю.
 
Здравствуйте, Михаил! Так я вообще этой формулы не понимаю. :)  Странно, что она не выдает ошибку. Насколько я осознаю, будут сравниваться только первые ячейки диапазонов, при этом диапазоны могут иметь и разные размеры.
Изменено: sokol92 - 10.04.2020 14:02:29
Владимир
Страницы: 1
Наверх