为什么我的Excel 10兆大吗？

问题描述：

我的Excel 2010工作簿包含为什么我的Excel 10兆大吗？

4数据表与表（即的ListObject）

交付，使用范围$ A $ 1：$ T $ 7647
股票，使用范围$ A $ 1：$ V $ 22962
销售，使用的范围$ A $ 1：$ U $ 2348
总之，使用范围$ A $ 1：$ AA $ 9509

01（每件的生命周期，在VBA创建的摘要）

7张带有数据透视表的隐藏表，使用范围最大的$ A $ 1：$ CS $ 37，参考上表

1张使用范围为$ A $ 1的演示文稿：$ X $ 108，其中包含7个表（ListObjects），其中的任何公式都指向数据透视表，或者从VBA中复制的值。

为什么我的档案如此庞大，我该怎么办？

编辑响应来自其他用户的评论：

为了确保有我过去曾经感动，但不使用任何更没有太多的细胞，我检索到的上述范围与

For Each sht In ThisWorkbook.Sheets 
    Print sht.Name, sht.UsedRange.Address 
Next

因此，它将无助于将数据和代码复制到新的干净工作簿。

没有涉及整列，样式=match("anything", A:A)的公式，也没有涉及任何超出使用范围的公式的公式。

由于我是VBA开发人员，任何解决方案都应该对用户透明。这个人不应该知道Excel的内部。

答

当您在工作簿上插入数据透视表时，Excel将缓存来自PivotTable.dataSource的数据。 （这就是为什么你需要更新您的数据透视表中包含源数据改变成透视表。）

数据缓存不足一半大的作为工作表区域它的缓存，但显著。 （在我的情况又增加了2.6MB到6.5MB存在，显然一旦超过10MB，Excel启动交换数据到磁盘）。

如果源数据是在一个单独的表，你可以删除源将您的数据仅保存在缓存中，并节省空间，而不是使用数据透视表破坏它。 （小心你did_not_uncheck Save data with the file，虽然）

如果您稍后需要编辑数据源，你可以通过双击在数据透视表总计从缓存中的数据“通过钻”。

阅读Massive Excel Spreadsheets – How to Reduce the Size of Pivot Table Workbooks了解更多详情。

作为一个VBA程序员，我必须隐藏这一切都为我的客户，所以我写了

代码提取数据并删除数据透视表

Public Sub unpackPivots() 
    On Error GoTo goneWrong 

    Dim PSheet As Worksheet, PTable As PivotTable, PBody As Range 
    For Each PSheet In ThisWorkbook.Sheets 
     If PSheet.Name Like "*Pivot" Then 
      Set PTable = PSheet.PivotTables(1) 
      PTable.ClearAllFilters 
      Set PBody = PTable.DataBodyRange 
      Set PBody = PBody.Cells(PBody.Rows.Count, PBody.Columns.Count) 
      PSheet.Activate 
      PBody.Select 
      PBody.ShowDetail = True 
      Application.ActiveSheet.Name = Replace(PSheet.Name, "Pivot", "_Data") 
      Application.DisplayAlerts = False 
      PSheet.Delete 
     End If 
    Next 
    Application.DisplayAlerts = True 

    Exit Sub

goneWrong： Application.DisplayAlerts = True Debug.Print Err.Number，Err.Description Debug.Assert False Err.Clear Resume End Sub

代码来创建从数据表中的数据透视表和删除表

Public Sub recreatePivots() 
    On Error GoTo goneWrong 

    Dim DSheet As Worksheet, DTable As ListObject 
    Dim PSheet As Worksheet, PCach As PivotCache 
    Dim PTable As PivotTable, PBody As Range 

    For Each DSheet In ThisWorkbook.Sheets 
     If DSheet.Name Like "*_Data" Then 
      Set PCache = ActiveWorkbook.PivotCaches.Create(_ 
       SourceType:=xlDatabase, _ 
       sourceData:=DSheet.ListObjects(1).Name, _ 
       Version:=xlPivotTableVersion14 _ 
      ) 
      ThisWorkbook.Sheets.Add Before:=DSheet 
      Set PSheet = Application.ActiveSheet 
      PSheet.Name = Replace(DSheet.Name, "_Data", "_Pivot") 

      Set PTable = PCache.CreatePivotTable(_ 
       TableDestination:=PSheet.Name + "!R3C1", _ 
       DefaultVersion:=xlPivotTableVersion14 _ 
      )

这里说到您的业务相关的东西

  Select Case DSheet.Name 
       Case "Stock_Data" 
        PTable.PivotFields("Month").Orientation = xlPageField 
        PTable.PivotFields("Manufacturer").Orientation = xlRowField 
        PTable.PivotFields("Warehouse").Orientation = xlColumnField 
        PTable.AddDataField PTable.PivotFields("Key"), _ 
         "Nr of products", xlSum 
       Case Else 
        ' Some other configuration of a pivote table 
      End Select 
      DSheet.Delete 
     End If 
    Next 

    Exit Sub 
goneWrong: 
    Application.DisplayAlerts = True 
    Debug.Print Err.Number, Err.Description 
    Debug.Assert False 
    Err.Clear 
    Resume 
End Sub

为什么我的Excel 10兆大吗？

相关推荐