如何在Excel中报告和分析可变长度数据库-百度知道-Excel学习网

Andrew Bates在“ 设置范围名称以将报告连接到Excel数据库”的第1部分中发表了评论，询问如何报告和分析可变长度数据。他描述的问题确实很普遍。在处理作为文本文件导出并导入到Excel的数据时，我经常使用它。但是，一旦我制定了解决方案，它就节省了很多时间。

 

Andrew提到他在Excel 2007中使用表。但是以下解决方案适用于所有版本的Excel：

 

这是一个数据库，可以大致说明安德鲁的要求。他希望建立可以轻松地从该数据库检索数据的公式。

 

简单的Excel数据库

 

第4行包含我在第5行中分配为范围名称的文本。

 

第5行非常重要。这是一个边界行，其中包含键范围名称，并且标记了数据行开始之前的最后一行。

 

要将第4行中的文本分配为第5行中的名称，请选择范围A4：C5，然后…

 

 

接下来，添加几行数据，如图所示。

 

在单元格A2中输入NumRows文本，并将名称NumRows分配给单元格B2。然后为显示的单元格输入以下公式：

 

 

您可能需要根据数据来稍微改变此公式。您需要确保该公式不引用其自己的列。公式需要对一列中的数据进行计数，该列中的数据行中永远不会有空格。请记住，COUNT只计算包括日期在内的数字数据，而COUNTA则计算文本和数字。

 

现在魔术来了：

 

定义范围名称，该范围名称使用到目前为止所做的操作来仅引用三列中每列中包含数据的行。这是您定义的名称：

 

 

 

 

我最近开始用“ aa”开头指定类似的动态范围名称。这会将动态名称分组在名称的排序列表中，这使它们更易于管理。如果还可以帮助我在公式中使用它们。为了说明，我可以快速检查名称“ Customer”是否已分配给单元格A5；因此，该单元格下面的数据列的名称为“ aaCustomer”。

 

在每个名称的定义中，OFFSET函数使用以下参数返回引用：

 

=偏移（参考，行，列，高度，宽度）

 

为了说明，aaCustomer名称返回从客户名称偏移参考基准由一个行和零列。它具有NumRows指定的高度。因为我们没有指定width，所以引用具有Customer的宽度，即一栏。（尽管在这种情况下不是必需的，但是为了安全起见，我通常会指定宽度为1。在这种情况下，我并没有这样做，只是为了说明height和width参数是可选的。）

 

要测试这些名称中的任何一个，例如aaDate，请按F5，键入aaDate作为参考，然后按Enter。如果您有三行数据，则Excel应该选择名为日期的灰色单元格下面的前三行。

 

现在，您可以使用简单的公式来学习有关此数据的信息：

 

客户数量等于NumRows的值。

 

年初至今的销售总额为= SUM（aaSales）。

 

过去12个月内的客户数量为：

 

 

如果您有New Excel，则使用COUNTIFS和SUMIFS函数可以使用多个条件。如果您有Classic Excel，则SUMPRODUCT可提供类似的功能。

 

我的文章使用Excel的SUMPRODUCT汇总工作表数据说明了该过程。因为COUNTIFS和SUMIFS仅支持AND条件，所以如果您具有New Excel，则需要使用SUMPRODUCT来指定OR条件。