教程 | Excel性能优化方法（九）

01

数据透视表

数据透视表提供汇总大量数据的有效方式。

如果仍想使用数据透视表作为中间结果，请使用 GETPIVOTDATA 函数。

02

条件格式和数据验证

条件格式和数据验证非常好，但是大量使用会显著降低计算速度。如果显示单元格，则在每次计算时以及刷新包含该条件格式的单元格的显示时计算每个条件格式公式。Excel 对象模型具有 WorksheetEnableFormatConditionsCalculation 属性，因此可以启用或禁用条件格式计算。

03

已定义名称

已定义名称是 Excel 中最强大的功能之一，但它们确实需要额外的计算时间。使用引用其他工作表的名称会增加计算过程的复杂程度。此外，应尽量避免嵌套名称（引用其他名称的名称）。

因为每次计算引用名称的公式时都会计算名称，所以应避免将计算密集型公式或函数放在定义名称中。在这些情况下，将计算密集型公式或函数放在空闲单元格中，并直接或通过名称引用该单元格，速度会快得多。

04

仅偶尔使用的公式

许多工作簿包含大量公式和查找，它们涉及将输入数据转换成适当的计算形状，或者被用作防止数据大小或形状变化的措施。如果拥有仅偶尔使用的一组公式，可以复制和粘贴特殊值来临时消除公式，也可以将它们放在一个单独的、很少打开的工作簿中。由于工作表错误通常是由于没有注意到公式已转换为值而引起的，因此最好使用单独的工作簿方法。

05

使用足够的内存

32 位版本的 Excel 可以使用最多 2 GB 的 RAM，也可以使用最多 4 GB 的 RAM 来支持 Large Address Aware 32 位版本的 Excel 2013 和 2016。然而，运行 Excel 的计算机同样需要内存资源。因此，如果计算机上只有 2 GB 的 RAM，Excel 就不能充分利用这 2 GB 的内存，因为一部分内存分配给了 *** 作系统和其他正在运行的程序。要在 32 位计算机上优化 Excel 的性能，建议计算机至少具有 3 GB RAM。

64 位版本的 Excel 没有 2 GB 或最多 4 GB 的限制。

Solver就是中文版的规划求解，如下图所示： 具体打开步骤，文件选项卡→选项→加载项下，选择Excel加载项，点击转到（如红框所示位置）： 然后你就可以看到规划求解： 把前面的复选框勾选即可。 然后你就可以在数据选项卡下，找到规划求解了。

你说的是Excel提供的规划求解吧，这里有个例子能够帮到你。

>

01 关闭自动计算

有部分同学的Excel每天都往里面添加数据，并且大部分数据都是不用重新进行计算的，这个时候我们就要为文件关闭自动计算。

在Excel中，每次打开/刷新文件，都会对所有公式进行自动计算，如果表格中的公式非常多的话，你的表格就会非常的卡，这是因为Excel在计算公式，需要消耗计算资源导致的。

这个时候，我们就可以关掉「自动计算」，选择「公式选项卡」，点击「计算选项」，设置「手动」即可。

当设置了「手动计算」后，发现输入公式并不会自动计算。这个时候就需要按「F9」进行手动计算。

02 拒绝引用整列/行数据

这个问题常常出现在VLOOKUP公式中，大部分同学都使用引用一整列，例如“A:D”这种格式，这样使用的话，VLOOKUP会去遍历Excel总共一百多万行数据，非常消耗计算机资源，所以表格会非常的卡顿。

建议使用多少数据引用多少数据，并且加上绝对定位，例如上图将“A:B”转换成“$A$1:$B$100”这种格式，只用到了前100行数据，这样Excel整体就会变得很轻快了。

这种情况在使用「数组公式」的时候计算量被快速放大，当选中两列进行数组运算的时候，Excel会变得非常卡顿。

03 尽量不使用可变函数

在非必要的情况下，不使用「可变函数」（易失性函数），例如：RAND、NOW、TODAY、INDIRECT等，这种函数有一个非常明显的特点，当数值发生变化的时候，每次都会进行重新计算，极大影响Excel的性能。

例如在Excel中大量使用了TODAY函数，每当文件保存一次的时候，都会重新调用系统当前时间进行更新。所以尽量使用其他功能性函数或者方案来代替可变函数。

04 大文件保存为xlsb格式

经常碰到几十M甚至上百M的Excel文件，其实对于Excel来说，这些文件已经不太适合使用Excel了，如果没办法使用其他更专业的数据分析软件来代替的话。可以尝试将Excel另存为「xlsb格式」的文件。

xlsb格式是Excel二进制文件，兼容所有的Excel版本，跟普通的xls和xlsx文件不一样的是，xlsb文件电脑可以直接识别。正常我们打开xlsx文件，电脑需要将其转换成二进制文件才能读出内容，直接保存为xlsb就省略了转换的这一步，会让速度更加流畅。

05 公式只保留第一行

对于不经常变动的Excel表格，我们可以将「公式粘贴成数值」，并且只保留第一个单元格为公式，例如某一列是由「公式」计算得到的，这一列的话，除了第一个单元格保留原始公式之外，其他的单元格全部复制粘贴成数值。

下次需要修改的时候直接双击「第一个公式」，就可以快速填充了。在Excel中，公式越少计算速度越快。

06 复杂公式使用VBA/数据透视表代替

在Excel中，有一个不成文的3行原则，公式一定不超过3行，如果超过了3行，请考虑使用VBA或者其他方式来实现。

对于超过3行的Excel公式，不仅阅读起来及其困难，而且消耗的计算资源也会成倍添加，对于经常需要处理Excel的同学来说，维护公式就已经是一件非常吃力的事情了。

今天的分享就到这里，优化好Excel文件，能让我们的Excel使用起来更加舒服，减少崩溃出现的情况，这几个小技巧大家可以尝试使用下哦。

个Excel文件，数据内容不多，但文件打开却需要20多秒。通过资源管理器查看文件属性，发现体积竟有十几兆。而类似情况下，这种Excel通常不过几十KB。这种情况很多网友都曾遇过，一般都是Excel中暗含了某些特殊元素。那么如何快速地让“巨婴Excel”苗条下来呢？今天小编就给大家提供几条思路。

1 清理无用对象

一般来说，遇到这种体积巨大，表面又看不出什么异常的Excel文件。首先要考虑的，就是里面是否夹杂了一些不可见的自选图形。为了避免手动清除清理不干净的情况，遇到这种问题，我们一般通过Excel的“定位”功能实现。

搜索文档中图形元素

具体方法是，点击“开始”→“查找和选择”，在下拉菜单中选择“定位条件”，然后在d出对话框中勾选“对象”选框。一般来说，Excel的搜索结果常常是些体积极小的透明元素（Excel会用一个个圆形小白点表示），这往往是用户在进行批量复制粘贴时误打出来的，保持选中状态下直接按下Delete键，这些不相关元素便迅速被删除了。

2 清理冗余公式

除了暗藏的对象以外，还有一种情况也会导致Excel莫名变大，这就是隐藏公式。一般来说公式仅需要对有数据的行列生效，而没有数据的行列则可以直接删除。

快速清理冗余公式

具体方法是，首先选中空白区域的首行，然后按下Ctrl+Shift+↓跳转到表格区最后一行，接下来通过快捷键Ctrl+-清除行内所有内容（其实就是删除选中行）。如果确实是公式导致的，存盘退出后Excel会马上恢复正常。

3 隐藏Sheet

类似的情况还有隐藏Sheet，也就是说除了你能看到的表格之外，当前文档中还有一些你看不到的表格。解决方法同样简单，在底部标签处右击鼠标，如果“取消隐藏”是点亮状态的话，就意味着文件中确实包含了隐藏Sheet。接下来的 *** 作顺理成章，如果确认被隐藏的Sheet没有用的话，就直接将它们删除。如果表格被加密无法删除，就直接将当前Sheet复制到一个新Excel文件中即可。

如果“取消隐藏”可选的话，就意味着表格中确实存在隐藏了的Sheet

以上就是小编为大家带来的Excel文件的优化技巧，其实数组函数的滥用、大规模使用条件格式、文档中重复调用其他文档，也都可能导致Excel文档体积过大。如同程序编写一样，Excel在编制时也需要讲究一定策略，函数如何选择，功能如何搭配，也都是颇有讲究的

一、工具：excel2010

二、 *** 作步骤：

A添加求解器solver

excel里面通常不会默认添加求解器的，要进行简单的 *** 作来实现。选择”文件“，点击”选项“；在d出的excel选项框中点击”加载项“，选择”excel加载项“，点击”转到“。

2在d出的加载宏对话框中勾选"规划求解”，点击“确定”；于是在数据选项卡中就添加了求解器solver工具。

B求解最优化解

按照题目的限制条件在excel中输入如下的表格和公式。

2A和B,的数量可以留空，但是为了能够更好地了解输入的公式，暂时分别填写50,50。

3点击“数据”选项卡中的“规划求解”。

4在d出的对话框中填入相应的信息；设置目标：单元格C20,即我们的目标函数的位置；勾选“最大值”；通过更改可变单元格：填入A，B产品数量的单元格（C5：C6）下面添加限制公式，点击“添加”。

5输入第一条限制公式：A<=400;点击确定。

6如图遵守约束中出现刚才填写的限制条件，再点击“添加”加入另外两条公式；点击“求解”。

7如图，自动求解出最优解：A=0，B=400;点解“确定”就可以了。

Excel表格是个多面手。用Excel来求型材、线材下料优化有三种情况。

1、材料需求数不多：用Excel表格的数据和的公式，然后配合手工来完成配料。

2、材料较多对Excel精通：用Excel表格中的规划求解来完成配料。难就难在计算模型的设计。

3、材料需求数较多：下载既有的Excel表格+vba

下载地址：>

示例：

以上就是关于教程 | Excel性能优化方法（九）全部的内容，包括:教程 | Excel性能优化方法（九）、如何用excel求解器solver求出最优化解、请问，用EXCEL能进行最优化求解吗怎么做，请高手指点等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！