同步自动提取不重复项、自动检错、自动分类汇总的模板

云空飞鹰

<p><font color="#0000ff"><font color="#4b0082"><font color="#000000"><font face="Times New Roman">本模板同步自动提取不重复项、自动分类汇总、自动检错，分类汇总就这么简单有趣快速！</font></font></font></font></p><p><font color="#0000ff"><font color="#4b0082"><font color="#000000"><font face="Times New Roman">请先下载下行的表例附件，</font><font face="宋体 "><font face="宋体 ">然后打开附件中的<span class="t_tag" href="tag.php?name=%B1%ED%B8%F1">表格</span><font face="宋体 "><font face="宋体 "><font face="宋体 "><font face="宋体 ">，</font></font></font></font></font></font><font face="宋体 "><font face="宋体 ">试试下述效果，想按什么<span class="t_tag" href="tag.php?name=%BB%E3%D7%DC">汇总</span>全由您作主：</font></font><br/><font face="宋体 "><font face="宋体 "><font face="宋体 "><font color="indigo"><font face="Times New Roman "><font face="宋体 "><font color="#000000"></font></font></font></font></font></font></font><br/>表例附件：</p><p><font face="Times New Roman">　　我在论坛找了个网上交流的将不重复项目找出的“应用例”，作了计算速度的实测比较，结果出乎意料：相同清单行数和相同一组项目名称，实测计算速度如下：<br/>实测案例&nbsp; &nbsp;&nbsp;&nbsp;“应用例”&nbsp; &nbsp;&nbsp;&nbsp;本模板单一<span class="t_tag" href="tag.php?name=%CC%F5%BC%FE">条件</span>汇总&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;不重复项数量的&nbsp; &nbsp; 　　 　　　　&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; 本模板无重复项<br/>清单行数&nbsp; &nbsp;&nbsp; &nbsp;计算时间&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;＝“应用例”条件&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;对应计算时间&nbsp; &nbsp;&nbsp; &nbsp; 　　　　　　　&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; 最长计算时间　　<br/>500&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; 27 秒&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;1 秒&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;300项&nbsp; &nbsp;2 秒&nbsp; &nbsp; 500项&nbsp; &nbsp; 4 秒&nbsp; &nbsp; 　　　　　　　　　&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;4 秒&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; <br/>1000&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;200 秒&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;1 秒&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;300项&nbsp; &nbsp;3 秒&nbsp; &nbsp; 500项&nbsp; &nbsp; 4 秒　 1000项&nbsp; &nbsp;7 秒　&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; 7 秒&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;<br/>2000&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;600 秒&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;1 秒&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;300项&nbsp; &nbsp;4 秒&nbsp; &nbsp; 500项&nbsp; &nbsp; 5 秒　 1000项 10 秒　&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;21 秒&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; <br/>4000&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; 未实测&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;2 秒&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;300项&nbsp; &nbsp;8 秒&nbsp; &nbsp; 500项&nbsp; &nbsp;13 秒　 1000项 25 秒　&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; 65 秒&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; 　<br/>10000&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;未实测&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;3 秒&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;300项 18 秒&nbsp; &nbsp; 500项&nbsp; &nbsp;33 秒　　　　　　　　　　　见说明<br/>15000&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;未实测&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;3 秒&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;300项 19 秒&nbsp; &nbsp; 500项 115 秒　&nbsp;&nbsp;　　　　 　&nbsp; &nbsp;&nbsp; &nbsp;　　见说明 <br/>25000&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;未实测&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;5 秒&nbsp; &nbsp; 180项 17 秒&nbsp; &nbsp;300项&nbsp; &nbsp;30 秒&nbsp; &nbsp;400项 275 秒&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;　见说明</font><br/><font face="Times New Roman ">　　说明：在上表计算时间内，“应用例”仅将不重复项目找出，本模板不仅将不重复项目找出，还<span class="t_tag" href="tag.php?name=%CD%B3%BC%C6">统计</span>出全部不重复项目的汇总结果。上表前二列计算时间都是按附件“1、<font face="Arial ">表例附件”中的数据重复复制后测试的，不重复项目只有28至32项，</font><font face="Times New Roman ">是在一个重复项<font color="#000000"><font color="#190f00">目非常多的同等条件下的比较测试，仅为对比参考。</font></font></font>我也只用了网上的一种方法来测试，也许还有更快的我没用到，所以，发表出来交流并请大家指教。<font color="#000000"><font color="#190f00">经本人测试，计算速度与表格汇总项目名称的重复程度密切相关：相同行数情况下，</font><font color="red">相同汇</font></font></font><font color="#000000"><font face="宋体 "><font face="宋体 "><font face="Times New Roman "><font color="#190f00"><font color="red">总名称重复的项目越多，计算时间越短；相同汇总名称重复的项目越少，计算时间越长，如</font></font><font color="red">上表中2000行和4000行不重复项32项的计算时间是1秒和2秒（相比“应用例”而言要快600倍），而不重复项2000项和4000项的计算时间（也就是最长时</font></font></font></font></font><font color="#000000"><font face="宋体 "><font face="宋体 "><font face="Times New Roman "><font color="#190f00"><font color="red">间）就为21秒和64秒，不过最长时<font face="宋体 "><font face="宋体 "><font face="Times New Roman ">间</font></font></font>相比“应用例”而言还是要快28倍。</font>由此可见，重复项目多用本模板速度之快自不用说，重复项目少的也可一试作个比较。09-12-24晚已测试1万行单一条件汇总计算300项不重复项目18 秒、500项不重复项目33秒、600项不重复项目出现了“可用资源不足，excel无法完成此任务”的提示；1.5万行单一条件汇总计算300项不重复项目19 秒、500项不重复项目115秒；2.5万行单一条件汇总计算180项不重复项目17 秒、300项不重复项目30秒、400项不重复项目275秒。但本人在测</font></font></font></font></font><font color="#000000"><font face="宋体 "><font face="宋体 "><font face="Times New Roman "><font color="#190f00">试1万行无重复项双条件组合汇总的最长计算时间时，出现了“可用资源不足，excel无法完成此任务”的提示，无法继续进行，因此，对1万行以上大表格，重复项目多用本模板不仅可行而且越多越有很大的优势，但最好是用单一汇总条件（即只在B列放置汇总项目名称，C列空，A列空或放置不重复项很少的大类项目），慎用双条件组合汇总（即A、B、C列都放置有汇总项目名称）；如果不重复项大多，可能会遇到这个问题而无法</font></font></font></font></font><font color="#000000"><font face="宋体 "><font face="宋体 "><font face="Times New Roman "><font color="#190f00">使用。（上表计算时间指表格装入后，改变表格中的数据之后重新计算结果所需的时间。）</font></font></font></font></font><br/><br/></p>

[此贴子已经被作者于2010-1-21 9:28:58编辑过]

一剑惊心

学习试用一下

zhuwei520

<p>能不能告知：流水账的本日合计、本月累计、本年累计，是怎样实现自动合计的？</p><p></p><p>这个表下载看了&nbsp; 谢谢啦！</p>

云空飞鹰

您将日、月、年各开设一列，将这三列作为汇总类项目就可以啦，呵呵

云空飞鹰

<p><strong><font color="#da2549" face="Verdana">回复2楼 一剑惊心 :</font></strong></p><p><strong><font color="#da2549" face="Verdana">请多指教！谢谢！</font></strong></p>

云空飞鹰

<strong><font color="#4b0082">　　<span class="t_tag" id="tag_0.9616686913544891" href="tag.php?name=%B1%ED%B8%F1" unselectable="unselectable" h="17" w="30" y="681" x="241" overfunc="null" outfunc="null" initialized="initialized">表格</span>的设计是二难的：记流水帐吧，表格设计简单、<span class="t_tag" href="tag.php?name=%C5%C5%D0%F2">排序</span>好进行，但分类<span class="t_tag" href="tag.php?name=%BB%E3%D7%DC">汇总</span>就麻烦了；设计分类表格吧，分类汇总简单，但表格设计麻烦了点、排序就更没法直接进行了。<br/>　　本<span class="t_tag" href="tag.php?name=%C4%A3%B0%E5">模板</span>很好解决了上述矛盾，采用记流水帐方式做清单，<span class="t_tag" href="tag.php?name=%D7%D4%B6%AF">自动</span>得到分类汇总的结果，自我感觉良好，请大家也试试如何？</font></strong><br/><font color="#000000"><font face="宋体 "><font face="宋体 "><font color="blue"><font face="宋体 "></font></font></font></font></font>

洁然

学习了，谢谢！

ujtxv

<div style="MARGIN-TOP: 10px; FONT-SIZE: 10pt; MIN-HEIGHT: 200px; WORD-BREAK: break-all; TEXT-INDENT: 24px; LINE-HEIGHT: normal; WORD-WRAP: break-word;">学习了，谢谢！<br/></div>

ujtxv

<div style="MARGIN-TOP: 10px; FONT-SIZE: 10pt; MIN-HEIGHT: 200px; WORD-BREAK: break-all; TEXT-INDENT: 24px; LINE-HEIGHT: normal; WORD-WRAP: break-word;">学习了，谢谢！<br/></div>

云空飞鹰

<p><strong><font color="#61b713" face="Verdana">回复&nbsp; 洁然　：</font></strong></p><p><strong><font color="#61b713" face="Verdana">请多提宝贵意见！谢谢！</font></strong></p>