如何在Excel中报告和分析可变长度数据库-百度知道--英雄云拓展知识分享

Andrew Bates在“ 设置范围名称以将报告连接到Excel数据库”的第1部份中发表了评论，询问如何报告和分析可变长度数据。他描写的问题确切很普遍。在处理作为文本文件导出并导入到Excel的数据时，我常常使用它。但是，一旦我制定了解决方案，它就节省了很多时间。Andrew提到他在Excel 2007中使用表。但是以下解决方案适用于所有版本的Excel：这是一个数据库，可以大致说明安德鲁的要求。他希望建立可以轻松地从该数据库检索数据的公式。

简单的Excel数据库第4行包括我在第5行中分配为范围名称的文本。第5行非常重要。这是一个边界行，其中包括键范围名称，并且标记了数据行开始之前的最后一行。要将第4行中的文本分配为第5行中的名称，请选择范围A4：C5，然后…在“新Excel”中，选择“公式”，“定义内涵的名称”，“从选择中创建”。在Classic Excel中，选择“插入”，“名称”，“创建”。然后，在对话框中，确保仅选中“顶行”，然后选择“肯定”。接下来，添加几行数据，如图所示。在单元格A2中输入NumRows文本，并将名称NumRows分配给单元格B2。然后为显示的单元格输入以下公式：B2：= COUNT（C：C）您可能需要针对数据来略微改变此公式。您需要确保该公式不援用其自己的列。公式需要对一列中的数据进行计数，该列中的数据行中永久不会有空格。请记住，COUNT只计算包括日期在内的数字数据，而COUNTA则计算文本和数字。现在魔术来了：定义内涵范围名称，该范围名称使用到至今为止为止所做的操作来仅援用3列中每列中包括数据的行。这是您定义内涵的名称：aaCustomer= OFFSET（Customer，1,0，NumRows）aaSales= OFFSET（Sales，1,0，NumRows）aaDate= OFFSET（Date，1,0，NumRows）我最近开始用“ aa”开头指定类似的动态范围名称。这会将动态名称分组在名称的排序列表中，这使它们更容易于管理。如果还可以帮助我在公式中使用它们。为了说明，我可以快捷检查名称“ Customer”是不是已分配给单元格A5；因此，这种情况下，该单元格下面的数据列的名称为“ aaCustomer”。在每一个名称的定义内涵中，OFFSET函数使用以下参数返回援用：=偏移（参考，行，列，高度，宽度）为了说明，aaCustomer名称返回从客户名称偏移参考基准由一个行和零列。它具有NumRows指定的高度。由于我们没有指定width，所以援用具有Customer的宽度，即一栏。（虽然在这类情况下不是必须的，但是为了安全起见，我通常会指定宽度为1。在这类情况下，我并没有这样做，只是为了说明height和width参数是可选的。）要测试这些名称中的任何一个，例如aaDate，请按F5，键入aaDate作为参考，然后按Enter。如果您有3行数据，则Excel应当选择名为日期的灰色单元格下面的前3行。现在，您可使用简单的公式来学习有关此数据的信息：客户数量等于NumRows的值。年初至今的销售总额为= SUM（aaSales）。过去12个月内的客户数量为：= COUNTIF（aaDate，”>”＆DATE（YEAR（NOW（））⑴，MONTH（NOW（）），DAY（NOW（））））如果您有New Excel，则使用COUNTIFS和SUMIFS函数可使用多个条件。如果您有Classic Excel，则SUMPRODUCT可提供类似的功能。我的文章使用Excel的SUMPRODUCT汇总工作表数听说明了该进程。由于COUNTIFS和SUMIFS仅支持AND条件，所以如果您具有New Excel，则需要使用SUMPRODUCT来指定OR条件。