随着数据的增加自动填充公式并动态排序 [英] Filling formulas automatically as data increase and sort dynamically

查看:410
本文介绍了随着数据的增加自动填充公式并动态排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想对工作表中的数据进行排序,该工作表每天都会在第二个工作表中获取新行.

I want to sort data in a worksheet, which gets new rows daily, in a second worksheet.

问题是,如果我使用SMALL()函数并自动填充单元格直到最后一行(A102482或类似的东西),我的文件将变得非常庞大且缓慢(> 20mb).

The problem is, if I use the SMALL()-function and fill the cells automatically till the last row (A102482 or something like that), my file gets very huge and laggy (>20mb).

添加新行的人当然可以将第二个工作表中的公式扩展到新行,但这根本不方便用户使用!

Of course the person adding a new line could expand the formula in the second worksheet into a new row, but this is not userfriendly at all!

什么是最佳解决方案?我想到了vba代码,该代码会计算第一个工作表中的条目,然后运行这样的代码

What would be the best solution? I thought about vba-code, which counts the entries in the first worksheet, and then runs a code like this

for (i to numberrows; i++) { SMALL(A + i + 2*3, i) }

填充第二个工作表中的前几行,以便excel文件不会太大...

filling the first few rows in the second worksheet so the excel-file doesn't get too big...

提前谢谢!

编辑.

更具体地说:

我要excel做的是复制一个工作表,并在第二个工作表上对行进行排序.随着表的使用,排序表当然会具有更多的值.此过程应自动完成,用户仅在第一个工作表中输入新数据,然后在第二个工作表中查看结果.具有第二个工作表的单元格都已经填充了SMALL()函数是不可行的,因为这在我的情况下是可行的,但这太慢了并且文件变得太大...

What I'm asking excel to do is copy a worksheet and have the rows sorted on the second worksheet. And as the table expends, of course the sorted table has more values. This process should be done automatically, with the user only entering new data in the first worksheet and seeing the results in the second worksheet. Having the second worksheet's cells all already populates witht the SMALL() function is not an option, as this would work in my case, but this is way too slow and the files get too big...

推荐答案

我建议从数据透视表中创建第二个表(从属表).

I suggest making the second table, the dependent one, out of a PivotTable.

每次刷新数据时,它会随着第一张图纸的增加和缩小,可以将其设置为在打开文件时自动刷新.方法如下:

This will grow and shrink with the first sheet every time you refresh the data, which can be set to automatically refresh when you open the file. Here's how:

使用插入>表"格式化第一个列表.然后使用汇总数据透视表"或插入>数据透视表"(两个功能相同的名称)将数据透视表放在下一张纸上.

Use "Insert > Table" to format the first list. Then use "Summarize with PivotTable" or "Insert > PivotTable" (2 names for the same feature) to put your PivotTable on the next sheet.

将出现的所有所需的列都放在第二个表中,在出现的数据透视表向导的行标签"下.

Put all the columns you want in the second sheet under "Row Labels" on the PivotTable wizard thingy that appears.

在数据透视表工具>设计>布局>小计下,选择不显示小计". 在数据透视表工具">设计">布局">总计"下,选择关闭行和列". 在数据透视表工具">设计">布局">报表布局"下,选择以表格形式显示".

Under PivotTable Tools > Design > Layout > Subtotals, choose "Do not show subtotals." Under PivotTable Tools > Design > Layout > Grand Totals, choose "Off for Rows and Columns." Under PivotTable Tools > Design > Layout > Report Layout, choose "Show in Tabular Form."

请注意,如果行标签列表的顶部没有唯一的东西(类似于我的示例中的"ID"(= ROW())),则数据透视表将对您可能使用的数据进行智能处理不想要.

Note that if you don't have something unique at the top of the list in Row Labels, similar to "ID" in my example ( =ROW() ), the PivotTable will do intelligent things with the data that you may not want.

这是打开文件时自动刷新的方法.另外,您可以在表格上点击鼠标右键,然后选择刷新".

Here's how to get the thing to refresh automatically when the file opens. Alternately, you right-click on the table and choose "refresh".

如果您希望每次数据更改时都刷新数据透视表,请放入

If you wanted to refresh the PivotTable every time the data changes, put

Sheets("Sheet2").PivotTables(1).PivotCache.Refresh

(用表所在的工作表名称替换"Sheet2";在左侧激活数据表;在第一个下拉菜单中选择工作表",然后在第二个下拉菜单中选择更改"以访问子表Worksheet_Change代码区域)

(substituting "Sheet2" with the name of the sheet where the table appears; activating the data sheet on the left side; select Worksheet on the first pull-down, and select Change on the second pull-down to access the Sub Worksheet_Change code area)

...这里:

干杯!

这篇关于随着数据的增加自动填充公式并动态排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆