1.1.3　使用数据透视表替代公式和函数_Excel数据透视表从新手到高手-QQ阅读女频短篇网

上QQ阅读APP看书，第一时间看更新

下面通过一个示例来对比在汇总和统计大量数据时，使用公式／函数与使用数据透视表的便捷性之间的差异，从而更好地体现出数据透视表在数据汇总和统计方面所具有的优势。

如图1-1所示为不同商品在各个地区的销量明细，图1-1中仅列出了部分数据，实际上包含标题行在内共有51行数据。现在需要分别统计各个地区所有商品的总销量。

本例中的数据只有4列，并不复杂，但是如果使用公式和函数来实现本例的统计需求，则需要分两步完成。首先需要从B列提取出不重复的销售地区的名称，然后根据提取结果，按地区名称对相关商品的销量进行求和。操作步骤如下：

（1）在F2单元格中输入下面的公式，然后按Ctrl+Shift+Enter快捷键，提取出第一个销售地区的名称，如图1-2所示。

     {=INDEX($C$2:$C$51,MATCH(0,COUNTIF(F$1:F1,C$2:C$51),0))}

图1-1　要统计的数据

图1-2　提取第一个销售地区的名称

注意：通过按Ctrl+Shift+Enter快捷键输入的公式是数组公式，Excel会自动在这类公式的两侧添加大括号。如果用户手动输入大括号，则会导致公式出错。书中在公式两侧印上大括号，是为了便于读者从外观上区分数组公式与普通公式。

（2）将F2单元格中的公式向下复制，直到单元格显示#N/A为止，此时将提取出所有销售地区的名称且是不重复的，如图1-3所示。

图1-3　提取出所有不重复的销售地区的名称

（3）提取出不重复的销售地区的名称后，在G2单元格中输入下面的公式，然后按Enter键，计算出第一个销售地区的所有商品的总销量，如图1-4所示。

     =SUMIF(C:C,F2,D:D)

图1-4　计算第一个销售地区的所有商品的总销量

（4）将G2单元格中的公式向下复制，计算出其他销售地区的所有商品的总销量，如图1-5所示。

图1-5　统计各个销售地区的所有商品的总销量

通过上面的示例可以看出，要使用公式和函数的方法来完成本例中的计算，需要掌握INDEX、MATCH、COUNTIF、SUMIF等函数的用法，还要掌握单元格引用和数组公式等概念和技术，在短时间内同时掌握这些内容并非易事。

如果使用数据透视表来完成本例中的计算，可显著降低操作难度，操作步骤如下：

（1）单击数据区域（本例为A1:D51）中的任意一个单元格，然后在功能区的“插入”选项卡中单击“数据透视表”按钮，如图1-6所示。打开“创建数据透视表”对话框，不做任何更改，直接单击“确定”按钮，如图1-7所示。

（2）Excel自动新建一个工作表，在其中创建一个空白的数据透视表，并显示“数据透视表字段”窗格，如图1-8所示。

图1-6　单击“数据透视表”按钮

图1-7　“创建数据透视表”对话框

图1-8　默认创建一个空白的数据透视表

（3）使用鼠标将窗格中的“销售地区”字段拖动到“行”区域，将“日销量”字段拖动到“值”区域，即可统计出各个销售地区的所有商品的总销量，如图1-9所示。

提示：将“日销量”字段拖动到“值”区域之后，该字段在“值”区域中会显示为“求和项:日销量”。

通过对比以上两种方法可以看出，第一种方法需要用户熟练掌握多个函数的综合运用及数组公式等相关技术。对于一般用户而言，学习这些内容需要耗费较长的时间。第二种方法不需要使用公式和函数，只需通过单击和拖动，即可快速完成数据的汇总和统计，对于用户是否掌握公式和函数没有任何要求，因此适合所有的Excel用户。

图1-9　统计各个销售地区的所有商品的总销量