Как уменьшить огромный файл excel

Ответ 1

Я написал файл VBA, чтобы добавить инструмент, очищающий этот ненормально большой файл. Этот script очищает все столбцы и строки после последних ячеек, реально используемых для reset последних ячеек ([Ctrl] + [End]), а также обеспечивает разрешение сжатия изображений.

Я создаю AddIns с автоматической установкой (просто запустите его с включенным макросом), чтобы включить в контекстное меню много новых кнопок:

• Оптимизация
• Оптимизировать и Сохранить
• Отключить оптимизатор

Это основано на KB of Microsoft office 2003 и ответе PP. с улучшением персоналий:

• добавить сжатие изображений
• Исправить ошибку для столбцов
• совместимость с excel 2007 - 2010 -... (более 255 столбцов)

РЕШЕНИЕ > вы можете скачать мой *.xlam файл ToolsKit

основной код

Sub ClearExcessRowsAndColumns()
    Dim ar As Range, r As Double, c As Double, tr As Double, tc As Double
    Dim wksWks As Worksheet, ur As Range, arCount As Integer, i As Integer
    Dim blProtCont As Boolean, blProtScen As Boolean, blProtDO As Boolean
    Dim shp As Shape
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each wksWks In ActiveWorkbook.Worksheets
      Err.Clear
      'Store worksheet protection settings and unprotect if protected.
      blProtCont = wksWks.ProtectContents
      blProtDO = wksWks.ProtectDrawingObjects
      blProtScen = wksWks.ProtectScenarios
      wksWks.Unprotect ""
      If Err.Number = 1004 Then
         Err.Clear
         MsgBox "'" & wksWks.Name & "' is protected with a password and cannot be checked.", vbInformation
      Else
         Application.StatusBar = "Checking " & wksWks.Name & ", Please Wait..."
         r = 0
         c = 0

         'Determine if the sheet contains both formulas and constants
         Set ur = Union(wksWks.UsedRange.SpecialCells(xlCellTypeConstants), wksWks.UsedRange.SpecialCells(xlCellTypeFormulas))
         'If both fails, try constants only
         If Err.Number = 1004 Then
            Err.Clear
            Set ur = wksWks.UsedRange.SpecialCells(xlCellTypeConstants)
         End If
         'If constants fails then set it to formulas
         If Err.Number = 1004 Then
            Err.Clear
            Set ur = wksWks.UsedRange.SpecialCells(xlCellTypeFormulas)
         End If
         'If there is still an error then the worksheet is empty
         If Err.Number <> 0 Then
            Err.Clear
            If wksWks.UsedRange.Address <> "$A$1" Then
               ur.EntireRow.Delete
            Else
               Set ur = Nothing
            End If
         End If
         'On Error GoTo 0
         If Not ur Is Nothing Then
            arCount = ur.Areas.Count
            'determine the last column and row that contains data or formula
            For Each ar In ur.Areas
               i = i + 1
               tr = ar.Range("A1").Row + ar.Rows.Count - 1
               tc = ar.Range("A1").Column + ar.Columns.Count - 1
               If tc > c Then c = tc
               If tr > r Then r = tr
            Next
            'Determine the area covered by shapes
            'so we don't remove shading behind shapes
            For Each shp In wksWks.Shapes
               tr = shp.BottomRightCell.Row
               tc = shp.BottomRightCell.Column
               If tc > c Then c = tc
               If tr > r Then r = tr
            Next
            Application.StatusBar = "Clearing Excess Cells in " & wksWks.Name & ", Please Wait..."
            Set ur = wksWks.Rows(r + 1 & ":" & wksWks.Rows.Count)
                'Reset row height which can also cause the lastcell to be innacurate
                ur.EntireRow.RowHeight = wksWks.StandardHeight
                ur.Clear

            Set ur = wksWks.Columns(ColLetter(c + 1) & ":" & ColLetter(wksWks.Columns.Count))
                'Reset column width which can also cause the lastcell to be innacurate
                ur.EntireColumn.ColumnWidth = wksWks.StandardWidth
                ur.Clear
         End If
      End If
      'Reset protection.
      wksWks.Protect "", blProtDO, blProtCont, blProtScen
      Err.Clear
    Next
    Application.StatusBar = False
    ' prepare les combinaison de touches pour la validation automatique de la fenetre
    ' Application.SendKeys "%(oe)~{TAB}~"

    ' ouvre la fenetre de compression des images
    Application.CommandBars.ExecuteMso "PicturesCompress"
    Application.ScreenUpdating = True
End Sub


Function ColLetter(ColNumber As Integer) As String
    ColLetter = Left(Cells(1, ColNumber).Address(False, False), Len(Cells(1, ColNumber).Address(False, False)) - 1)
End Function

Ответ 2

Я сохраняю файлы в формате .XLSB для уменьшения размера. XLSB также позволяет VBA и макросам оставаться с файлом. Я видел 50 мегабайт файлов до менее 10 с двоичным форматированием.

Ответ 3

Если ваш файл является только текстом, лучшим решением является сохранение каждого листа как .csv, а затем reimport в excel - требуется немного больше работы, но я уменьшил файл размером 20 МБ до 43 КБ.

Ответ 4

i Измените формат файла на *. XLSX. Это изменение сжимает мой файл и уменьшает размер файла 15%

Ответ 5

Я много работал в Excel и нашел следующие 3 точки очень полезными

Найти, если есть ячейки, которые, по-видимому, не содержат никаких данных, но Excel считает, что они имеют данные

Вы можете найти это, используя следующее свойство на листе

ActiveSheet.UsedRange.Rows.Count
ActiveSheet.UsedRange.Columns.Count

Если этот диапазон больше, чем ячейки, на которых у вас есть данные, удалите остальные строки/столбцы

Вы будете удивлены, увидев количество свободного места, которое он может освободить

Преобразование файлов в формат .xlsb

Формат XLSM должен сделать Excel совместимым с Open XML, но очень мало примеров, когда мы фактически используем XML-формат Excel. Это уменьшает размер почти на 50%, если не больше

Оптимальный способ хранения информации

Например, если вам нужно сохранить стоимость акции около 10 лет, и вам нужно сохранить Open, High, Low, Close для акций, это приведет к Используются (252 * 10) * (4) ячейки

Вместо этого, используя отдельные столбцы для Open, High, Low, Close, сохраните их в одном столбце с разделителем полей Часы работы: Высокий: Низкий: Закрыть

Вы можете легко написать функцию для извлечения информации из одного столбца всякий раз, когда захотите, но она освободит почти 2/3 пространства, которое вы сейчас занимаете.

Ответ 6

У меня был файл excel размером 24 МБ в размере, благодаря более чем 100 изображениям внутри. Я уменьшил размер до менее 5 МБ, выполнив следующие шаги:

1. Выделите каждое изображение, вырежьте его (CTRL X) и вставьте его в специальном режиме с помощью опции ALT ES Bitmap
2. Чтобы найти, какой битмап был по-прежнему большой, нужно выбрать один из файлов на листе, а затем сделать CTRL A. Это выберет все изображения.
3. Двойной щелчок по любому изображению, а опция RESET Picture отображается сверху.
4. Нажмите на изображение сброса, и все изображения, которые по-прежнему остаются крупными, отображаются.
5. Сделайте CTRL Z (UNDO) и теперь снова вставьте эти изображения баланса в BITMAP (*.BMP), как шаг 1.

Мне потребовалось 2 дня, чтобы понять это, так как это не было указано ни в одном справочном форуме. Надеюсь, что этот ответ поможет кому-то

БР Гаутам Далал (Индия)

Ответ 7

Посмотрите на сообщения вроде: http://www.officearticles.com/excel/clean_up_your_worksheet_in_microsoft_excel.htm или http://www.contextures.on.ca/xlfaqApp.html#Unused

В принципе: попробуйте Googling?

Ответ 8

Я наткнулся на интересную причину для гигантского файла .xlsx. В оригинальной книге было 20 листов или около того, было 20 МБ Я создал новую книгу с 1 листом, поэтому она будет более управляемой: еще 11,5 МБ Представьте себе мое удивление, обнаружив, что на одном листе в новой книге было пустое число 1 041 776 (подсчет!)! Теперь это 13,5 КБ