Страницы: 1
RSS
Облегчение базы данных в Эксель, Как сделать чтобы БД в эксель работала шустрее
 
Добрый день,

Я работаю в базе данных, созданной в Эксель. Состоит из 216 столбцов и 153104 строк. Вес 176 Мб. Есть группировки и простые формулы. Скажите пож-ста возможно ли, чтобы такая база работала быстрее? Быстрее открывалась, сохранялась и т.д.
 
Минимум форматирования. Желательно вообще без условного форматирования.
Тормозить могут пересчеты формул. Надо смотреть, что там накручено. Избавляться от летучих функций. Не указывать полные диапазоны строк/столбцов...

У Вас вопрос общий, никакой конкретики в виде примера. Ответ под стать вопросу.
 
У меня нет никакого усл форматирования. А как эту громаду выложить здесь?
 
Alex77, Не надо вся громадину. Выложите несколько строчек. Сразу станет понятно, что у вас там за формулы и т.д.
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
Структура, примерно, такая. Вроде простая.
 
Alex77, попробуйте пересохранить в формате  двоичная книга (.xlsb)
Не бойтесь совершенства. Вам его не достичь.
 
Сохранял. Уменьшает размер двое, но все равно тяжело работает.
 
Alex77, еще вариант если пользуетесь plex (там можно скачать пробную версию на некоторое время)
https://www.planetaexcel.ru/plex/features/20/275/

может уже смотрели, но на всякий случай https://www.planetaexcel.ru/techniques/12/114/
Изменено: Mershik - 01.07.2020 20:49:57
Не бойтесь совершенства. Вам его не достичь.
 
Спасибо. Ознакомлюсь.
 
Alex77, еще есть добрый товарищ  Владимир (sokol92), он мне когда-то помогал с файлом. Попробуйте написать.
Не бойтесь совершенства. Вам его не достичь.
 
Цитата
Mershik написал:
 https://www.planetaexcel.ru/techniques/12/114/
По этой ссылке проверил всё, что меня может беспокоить. Всё чисто. Не пойму, файл весом 100 Мб в формате .xlsb так и должен тормозить?
 
1. ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Если пользуетесь постоянно фильтром - пересчеты (если не пользуетесь - зачем такая функция?)
2. СУММЕСЛИМН - тормознутая. А у Вас их немало. Чем больше диапазон для функции, тем сильеее замедление пересчетов. Зачем ее применять при проверке одного условия, если есть более шустрая СУММЕСЛИ? И, кстати, СУММЕСЛИ сама умеет определять используемый диапазон, ей можно скармливать диапазоны целых столбцов.
3. избавляться от ошибок при вычислениях. Куча #ДЕЛ/0!. Проверяйте:
ЕСЛИ(b<>0;a/b;0).
Так не только уберете ошибки, но и немного ускорите вычисления.
4. Чем больше строк с формулами. тем больше расчетов. Там, где формулы уже не нужны (например, давние периоды), заменить на значения. Да и вообще - при таких оъемах о формул нжно уходить.
5. 100 Мб... Книги обычно начинают стонать от нескольких десятков Мб, а у Вас сотня... Поэтому не только от формул отказываться, но разносить данные в разные книги..


Создайте копию, которую не жалко поломать. Замените ВСЕ формулы на значения. Проверьте. как работает.
 
Цитата
vikttur написал:
(если не пользуетесь - зачем такая функция?)2
В итоги чтоб не попадало, там тоже она.
По вопросам из тем форума, личку не читаю.
 
Цитата
vikttur написал: 1. ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Пользуемся фильтрами. Кроме меня еще человек 20. Без них будет неудобно.

Цитата
vikttur написал:
2. СУММЕСЛИМН - тормознутая
Заменили на СУММЕСЛИ. Благодарю за напоминание.

Цитата
vikttur написал: Куча #ДЕЛ/0!.
Их нет в полной БД. Они попались в обрезке, что я выложил. Но на всякий случай прописал ЕСЛИ.
Изменено: Alex77 - 02.07.2020 13:07:47
 
Alex77, здравствуйте
Почитайте мою тему Связанные умные таблицы с большим объёмом данных. Как ускорить пересчёт?
Изменено: Jack Famous - 02.07.2020 11:22:44
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Alex77, было предложение: ОПРЕДЕЛИТЬ ПРИЧИНУ! А уже потом думать об ускорении.
Вы пробовали заменить формулы на значения? Что показало?
Если эффекта мало, удалить половину данных. Поверить. Отрезать еще часть...
Если тормозят формулы, на другой копии заменять формулы и смотреть, какие из них приводят к головной боли.

А без проверок, в ожидании волшебной подсказки, так и просидите у разбитого корыта до глубокой старости.
 
Можно ещё данный макрос попробовать в книге запустить.
Код
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

 
Цитата
Alex77 написал: Состоит из 216 столбцов и 153104 строк. Вес 176 Мб. Есть группировки и простые формулы.
Пользуемся фильтрами.
Цитата
О'Генри написал: "Боливар очень устал и двоих ему не выдержать"
Автофильтр и формулы плохо живут вместе. При открытии файла, а также при каждом изменении автофильтра происходит перерасчет практически всех формул. Как вариант, иметь два файла c одной и той же информацией: корректируемый без автофильтра и зеркальный (копию), в котором выставлен автофильтр, а в диапазоне автофильтра все формулы изменены на значения.
Владимир
 
Владимир, приветствую!
Цитата
sokol92: Автофильтр и формулы плохо живут вместе
семейным психологом в таком случае выступает ручной пересчёт (отключение автопересчёта - см. скрин)  :D
Цитата
sokol92: Как вариант, иметь два файла c одной и той же информацией
крайне сомнительная затея - я бы ни за что не стал так делать…
Изменено: Jack Famous - 02.07.2020 14:35:33
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Добрый день, Алексей! Вариант с отключением формул, конечно, проще всего.  А если автору нужны (промежуточные ) итоги после смены фильтра - о них шла речь в этой теме?
Владимир
 
Цитата
sokol92: если автору нужны (промежуточные ) итоги
так нажать F9 и всё (ручной пересчёт)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Jack Famous,как вы оптимистично настроены про 20-то пользователей (что каждый из них не забудет обновить формулы)... В таком случае вместе с отключением автоподсчета формул я бы прописал в файле книги принудительный пересчет через макрос при обновлении автофильтра. Чтоб потом морду лица не били за запоротые отчеты
Программный код, как и яды, лучше тестировать по капельке
Люблю изобретать велосипеды с колесами произвольной формы
 
Цитата
Hypohelix: как вы оптимистично настроены про 20-то пользователей
люди бывают с разным уровнем сообразительности - мои коллеги привыкли руками пересчитывать дня за 3. Разумеется, можно и макросы на события написать и ещё много чего, но на каждый такой костыль можно будет предложить что-то из серии "а если человек вместо цифры "3" букву Зэ напишет - какой макрос это может проверить?". Короче говоря, исходить нужно из необходимости и достаточности, а не пытаться чинить то, что не ломалось
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
vikttur написал:
Не указывать полные диапазоны строк/столбцов
Скажите, что Вы имели ввиду?
 
Цитата
Alex77: Скажите, что Вы имели ввиду?
например, в статье Николая Владимировича Самый быстрый ВПР наглядно показана разница
Изменено: Jack Famous - 03.07.2020 10:38:20
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Страницы: 1
Наверх