求助---提高工作表运行速度

Simon_Zhu

26楼的附件看过了吗，公式符合你的要求吗？觉得速度提升了吗？

Fae

O(∩_∩)O谢谢！

我下下来了，依大侠的公式放在现有我用的资料库里，还是很慢。

我想慢的原因主要还是在那两个“计时工资表”与“计件工资表”里。因为我的“数据源”有一万多差不多两万行

Simon_Zhu

原帖由 Fae 于 2009-5-27 23:13 发表
O(∩_∩)O谢谢！

我下下来了，依大侠的公式放在现有我用的资料库里，还是很慢。

我想慢的原因主要还是在那两个“计时工资表”与“计件工资表”里。因为我的“数据源”有一万多差不多两万行

一项一项的说吧
一、还是先说一下瓶颈。
1. 测试你看过了吗？
订单实际人工工资统计表 D列公式是最卡的，
计件工资总表&计时工资总表的工号人名部分的公式次之…
我知道你的心结是在MMULT部分，觉得那里是最影响速度的。
其实不尽然，
计件工资总表&计时工资总表 数据区MMULT公式测速 100000次运算 耗时 才3.02秒
虽然在源数据里数据增大后，即MMULT取值范围变大时，不可否认的要对整体速度有影响，但因为其返回的是数值，按你表中的公式关系树来看，其卡慢速度的影响要比订单实际人工工资统计表 D列公式和计件工资总表&计时工资总表的工号人名部分的公式的影响要小得多，明白否？
2. MMULT部分
如按你所说，“数据源”有一万多差不多两万行的数据并还要逐渐增多，
那么MMULT根本就无法满足你的多条件求和的需求，
czzqb版主做过测试，由于EXCEL本身的限制。MMULT()公式结果的单元格数量不能超过5461个，超过则失效。
参见3楼说明 http://club.excelhome.net/viewth ... amp;highlight=mmult
所以，我不知道数据在一万多差不多两万行的时候，MMULT公式怎么满足你需求的...
3. 个人体会
在你1楼和3楼的帮公式减肥，调整公式求助角度出发的话，
26楼的附件是在你提供的数据中做出的公式优化调整，在与你原文件做速度比对时，客观的说，速度是有大幅度提升的，已经达到了公式优化的初衷，注意：这是在提供的现有数据基础上的速度比对，而不是在继续增加数据量的基础上。
但你“数据源”有一万多差不多两万行时，公式再怎么优化，卡慢也是必然的。也就是说，要达到你心目中的速度要求，不是优化公式能解决的…
二. 解决方法的个人建议
1. 公式：
如果还是想用公式解决的话，那么心态上就要先作一下调整，降低一点要求（前面说过，“数据源”有一万多差不多两万行时，公式再怎么优化，卡慢也是必然的）。用添加大量辅助列的方法，尽量不要用数组的方法返回数值，用空间换时间，这样速度还可大幅提升，我估计数据一万多差不多两万行时也基本能满足你需求，会有点卡，基本能接受，至少不会象数组那样奇慢无比甚至当机。这是基于与你原公式做比对的基础上的，数据一万多差不多两万行时，MMULT()公式已经超出了5461个的范围，无法算出结果了，sumproduct的话就更不谈了，那么多sumproduct公式在表中，且取值范围那么大，重新计算一下的话，没一二分钟是搞不定的…（不想大动干戈的话，懒人推荐）
2. 数据透视表：
不想用VBA的话，数据透视表是不二选择。
计时工资总表、计件工资总表、订单实际人工工资统计表的结构都不复杂，中规中矩，用数据透视表做出还是比较容易的，速度也是非常快的。（强烈推荐）
3. VBA
(1). 高级筛选+公式（辅助）
(2). SQL+公式（辅助）
(3). SQL+ACCESS

一家之言，仅供参考。

[ 本帖最后由 Simon_Zhu 于 2009-5-28 14:20 编辑 ]

Simon_Zhu

表"订单实际人工工资统计表" 做了个SQL+公式（辅助）的实例供参考，见附件。

[ 本帖最后由 Simon_Zhu 于 2009-5-29 19:23 编辑 ]

Fae

谢谢Simon_Zhu详细的解说及帮助。O(∩_∩)O谢

附档的速度真的非常快了，但我不是很理解，（对vba不大懂）
能否在这给我上一课？

衷心感谢！

me322

建议把各种数据分类成几个工作表,这样,应该会快点

梦里啥都有

Simon_Zhu 发表于 2009-5-25 15:17
先说说关于提速方面通用的一些要点：
1. 公式方面
一般来说

你是咋测试的，教教呗，我也测测我的。

wyzdddl

Simon_Zhu 发表于 2009-5-28 14:12
一项一项的说吧
一、还是先说一下瓶颈。
1. 测试你看过了吗？

新年快乐，让我知道了我的工作簿为什么很慢的原因。试着优化一下吧