在Excel中处理任意多个项目的任意大集合的方法

对于报告,如果您需要总结数据的某些方面,那么数据透视表(在“数据”菜单上)是合乎逻辑的选择。可以将数据集列用作透视表中的页面字段,以便每个数据集都可以显示在单独的报表中

有一些风险因素:

• 数据集的大小可能超过工作表上的最大行数(Excel 2007中为1048576;Excel 2003中为65536)
• 给定字段中不同值的数量可能超过数据透视表中可汇总的数量(Excel 2007中为1048576;Excel 2003中为32500)

如果您的数据可能超过任何最大大小,则Excel不太可能是用于该数据的合适程序(作为某种数据库的前端除外)

因为您已经有了命名范围,所以可以使用一个queryTable/listObject,它实际上指向同一工作簿中的另一个工作表。这使用了Excel文件DSN(通过ODBC,它应该在所有使用Excel的工作站上),我已经多次使用这个技巧来处理我需要报告的可变数据。如何执行此操作取决于正在运行的Excel版本。我现在正好在运行2007,所以操作说明是“数据选项卡/来自其他来源/来自Microsoft Query”,选择“Excel文件”,浏览到您的文件(您可能需要先保存,因为这与您所在的文件相同),然后选择要查找的命名范围。如果您熟悉MS Query,那么应该直接从那里开始。

现在,新查询表的DSN将在连接字符串中包含对工作簿的硬编码引用。要更新此内容,您需要在工作簿\u打开事件中使用少量VBA代码。它应该看起来像:

Sub Workbook_Open()
   Dim MyLocation as String
   MyLocation = ThisWorkbook.Path & "\" ThisWorkbook.Name
   Sheet1.ListObjects(1).queryTable.ConnectionString = _
         "ODBC;DSN=Excel Files;DBQ=" & _
         MyLocation & ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
End Sub

显然,对于多个查询,您需要为教学做一些工作,但这就是要点。让我知道这是否是您想要的,我可以给您更深入地解释创建查询的好处、方法、代码示例(而不是通过MSQuery手工创建)、其他版本的不同方向,等等。

注意:这种方法也适用于数据透视表,但我发现对于像您描述的数据这样的简单数据集,这种方法很麻烦。

这可能很有用:

http://www.exceluser.com/solutions/variablelists_xl12.htm