职场常用Excel基础04-二维表转换

大家好，今天和大家一起分享一下excel的二维表转换相关内容~

在Excel中，二维表（也称为矩阵或表格）是一种组织数据的方式，其中数据按照行和列的格式进行排列。然而，在实际的数据分析过程中，我们常常需要对这些二维表进行转换以满足不同的需求。比如，从长格式转为宽格式，或者反之亦然；又或者是将多个工作表中的数据合并到一个表格中。

1. 长格式与宽格式之间的转换

1.1 宽格式转长格式（Pivot Table）

宽格式（Wide Format）是指每个观测值占据一行，而变量名则作为列标题出现在第一行。例如，如果我们有一个记录不同年份销售情况的表格，其中每一列代表一年的销售额，那么这个表格就是宽格式的。

示例1： 假设我们有一个宽格式的销售数据表如下：

我们可以使用Excel的“数据透视表”功能将其转换成长格式。具体步骤如下：

• 选中上述数据区域。
• 点击“插入”菜单下的“数据透视表”。
• 在弹出的对话框中选择放置数据透视表的位置。
• 将“地区”拖动到“行标签”，将“2020年销售额”、“2021年销售额”、“2022年销售额”分别拖动到“数值”。

得到的结果将是长格式的数据表示，其中每一年的销售额都单独占据了多行，且每行对应一个特定的地区和年份。

1.2 长格式转宽格式（Unpivot）

长格式（Long Format）指的是每一个观测值都占用一行，所有变量（如时间、类别等）都在同一列中列出。如果要将长格式转换成宽格式，可以使用Excel的“Power Query编辑器”。

示例2： 考虑以下长格式的销售数据：

为了转换成宽格式，可以执行以下步骤：

• 选中数据并点击“数据”选项卡中的“从表格/范围”。

• 在Power Query编辑器中，选择“地区”列，然后点击“展开”旁边的箭头，选择“未聚合”。
• 接下来，点击“高级编辑器”，输入相应的M语言代码来实现转换，或直接使用界面中的“分组依据”功能。

2. 合并多个工作表的数据

当有多个包含类似结构但不同内容的工作表时，我们可能希望将它们合并成一个单一的工作表以便于分析。这可以通过多种方式完成，包括VBA宏编程、Power Query或其他内置函数。

示例3： 假设有三个工作表，每个工作表都记录了不同月份的销售数据，格式相同，但月份不同：

• 表1（1月）：| 产品 | 销售额 |
• 表2（2月）：| 产品 | 销售额 |
• 表3（3月）：| 产品 | 销售额 |

要合并这些表，可以采用以下步骤：

• 使用“Power Query”加载第一个表。
• 然后依次添加其他两个表到查询中，并确保选择了“追加查询”选项。
• 最后，关闭并加载到Excel中，即可获得一个新的合并后的表格，该表格包含了所有三个月的数据。

3. 数据的归一化和标准化

有时候，我们需要对二维表中的数值进行归一化或标准化处理，以便更好地比较不同尺度的数据。例如，当我们想要比较不同地区的经济增长率时，可能会因为各地区基础经济规模差异巨大而难以直观对比。此时，可以使用Excel提供的公式来进行数据的归一化或标准化。

示例4： 给定一个包含不同城市人口数量的表格，我们想对其进行标准化，使其均值为0，标准差为1。可以使用以下公式：

= (A2 - AVERAGE($A$2:$A$100)) / STDEV.P($A$2:$A$100)

这里假设人口数据位于A2到A100之间。此公式会计算每个城市的标准化得分，使得所有城市的平均得分为0，标准差为1。

Excel提供了丰富的工具和技术用于二维表的转换，无论是长宽格式之间的转换，还是多个工作表数据的合并，甚至是数据的归一化和标准化，都能轻松应对，欢迎大家一起讨论~