《Excel财务日常数据与实例讲解》_从理论到实践的一大步_3.1.2 将一维表转换为二维表

【摘要】 本书摘自《Excel财务日常数据与实例讲解》一书中第3章，第1.2节，作者是韩小良、贾春雷。

3.1.2 将一维表转换为二维表

如果是一个一维表格，将一维表格转换为二维表格的最简单方法是使用数据透视表；如 果是一个工作簿里的多个一维表格，可以使用现有连接+SQL 语句，或者使用 Power Query; 如果是多个工作簿里的一维表格，最好使用 Power Query。

另外，还有一种情况是按照规定的格式从一维表格中抓取汇总数据到这个二维表中，此 时，则需要使用函数来创建公式。

1.一个一维表格转换为二维表格

这种情况是最简单的，创建基本的数据透视表，按照二维表的格式要求布局美化即可。

例如，对于图3-2所示的一维表，要做成二维表(费用一列，月份多列),则可以建立 数据透视表进行布局，如图3-33所示。

2. 一个工作簿里的多个一维表格：现有连接+SQL 方法

一个工作簿里的多个一维表格要转换为二维表格，如果使用现有连接+SQL 方法，那么 在“导入数据”对话框这一步需要选择“数据透视表”选项。

例如，对于第2章的案例2-11的示例数据，汇总进行到最后一步“导入数据”时，就 选择“数据透视表”选项，那么就会得到一个数据透视表，而不是表格，如图3-35和图3-36 所示。

3.一个工作簿里的多个一维表格： Power Query 方法

这种方法制作二维表也是很简单的，直接汇总并进行分组和透视即可，无须经过先表再 透视这样的烦琐过程，特别适合数据量大的场合。

例如，对第2章的案例2- 11的示例数据，利用Power Query汇总，先得到图3-39所示 的汇总结果。

选中"店铺名称"列，选择“开始” → "分组依据"命令，打开“分组依据”对话框， 如图3-40所示。

选中“高级”单选按钮，添加一个分组，然后第一个分组依据选择“店铺名称”,第二 个分组依据选择“年份”,新列名为“收入合计”,操作选择“求和”,柱选择“收入”,这就 得到了图3-41所示的分组结果。

再选中“年份”列，选择“转换”→ “透视列”命令，这就得到了图3-42所示的二维表格。

4.多个工作簿的一维表格

这种情况下，最好的方法是使用Power Query 对这些工作簿先进行汇总，然后采用前面 介绍的方法对指定的字段进行分组即可。限于篇幅，这里就不再举例说明。

5.由不规范的基础表，按照特定格式制作二维表格

这种情况下，使用函数是较好的方法，当然也可以使用VBA 来解决。

在使用函数时，由于是按照特定格式对指定项目进行汇总计算，所以使用的函数主要是

SUMIF、SUMIFS 或者 SUMPRODUCT 函数，而SUMPRODUCT 函数更多是用于基础数据不规

范但需要直接从这样不规范的一维表单中制作需要的报表。

另外，也会遇到这样的情况，不需要进行汇总计算，而是从不规范的表单中直接抓取数据， 此时，需要使用有关查找函数，如VLOOKUP、MATCH、INDEX、OFFSET 函数等。

下面介绍两个例子。

案例3-5

图3-43所示是这样一个例子，左侧的基础数据表单“基础数据”中，A 列日期是非法的， 并不是真正的日期，而是位数字的“日期”格式 (yymmdd), 同 时B 列是产品编码，其左边 的两位是产品类别。

现在的任务是，制作一个按照产品类别和月份汇总的二维表，如何设计公式?

这个问题并不复杂，利用MID 函数从日期数据中提取中间的两位数字就是月份，再用 TEXT 函数将两位数字转换为中文月份名称；利用LEFT 函数从产品编码中提取左侧的两位数 就是产品类别，这样，将这两个提取出来的数据进行比较判断，就能直接求和。

如图3-44所示，单元格C3 的公式如下。

=SUMPRODUCT(

(TEXT(MID (基础数据!SAS2:SA$1000,3,2),"0月")=C$2)*1,

(LEFT(基础数据!SBS2:$B$1000,2)=$B3)*1,

基础数据!$C$2:$C$1000

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们 18664393530@aliyun.com 处理，核实后本网站将在24小时内删除侵权内容。