数据透视表是数据分析中高效汇总、多维度分析数据的核心工具，能快速将杂乱数据转化为结构化的汇总报表。在实际分析场景中，我们常需要基于数据透视表中已有的两列数据（如“单价”与“销量”、“数量”与“单价”、“人数”与“人均产值”）相乘，生成新的衍生字段（如“销售额”“总成本”“总产值”），以满足更深层次的分析需求。本文将详细拆解数据透视表中通过两列相乘设置新字段的核心逻辑、不同工具（Excel、Power BI）的具体操作步骤、注意事项及实战案例，帮助读者快速掌握这一实用技能。

一、核心认知：两列相乘设置新字段的适用场景与价值

在数据透视表中通过两列相乘生成新字段，本质是利用“计算字段”功能，基于已有汇总数据进行二次运算，其核心价值在于弥补原始数据的不足，挖掘数据间的关联价值。常见适用场景包括：

• 销售分析：基于“销量”（汇总列1）和“单价”（汇总列2）相乘，生成“销售额”新字段，快速汇总不同维度（如地区、产品、时间）的销售额；

• 成本核算：基于“采购数量”和“单位成本”相乘，生成“总成本”新字段，分析不同供应商、不同批次的采购总成本；

• 人力资源分析：基于“部门人数”和“人均薪酬”相乘，生成“部门薪酬总额”新字段，对比各部门薪酬支出；

• 库存管理：基于“库存数量”和“单位库存成本”相乘，生成“库存总价值”新字段，评估库存资产规模。

需要注意的是，数据透视表中用于相乘的两列必须是“数值型汇总字段”（如求和、平均值后的字段），而非原始数据的文本字段。若原始数据中已存在相乘后的字段（如原始数据有“销售额”），优先使用原始字段汇总；若仅存在基础字段（如仅存“销量”和“单价”），则通过计算字段实现两列相乘。

关键前提：数据透视表的“行/列标签”需能同时关联两列相乘的字段。例如，若“销量”按“产品类别”汇总，“单价”按“地区”汇总，两者无共同关联维度，则无法直接相乘生成有意义的新字段。

二、Excel数据透视表：两列相乘设置新字段实操步骤

Excel是最常用的数据透视表工具，其“计算字段”功能可直接实现两列相乘生成新字段，操作步骤简洁易懂，具体如下：

前提准备：确保数据透视表包含需相乘的两列

假设我们有一份销售原始数据，包含“地区”“产品类别”“销量”“单价”字段，已创建数据透视表，行标签为“地区”，列标签为“产品类别”，值区域已添加“求和项:销量”和“求和项:单价”两列，需基于这两列相乘生成“销售额”新字段。

步骤1：打开“计算字段”对话框

• 点击数据透视表内的任意单元格（激活数据透视表）；

• 在顶部菜单栏中选择【分析】选项卡（Excel 2016及以上版本，旧版本为【选项】选项卡）；

• 在“计算”组中，点击【字段、项目和集】，在下拉菜单中选择【计算字段】，打开计算字段设置对话框。

步骤2：设置新字段名称与相乘公式

• 在“名称”输入框中，输入新字段的名称，如“销售额”；

• 在“公式”输入框中，删除默认的“0”，构建两列相乘的公式。核心逻辑是：选择需相乘的两个汇总字段，用“*”连接。具体操作：

  • 在“字段”列表中，找到“求和项:销量”，点击【插入字段】，将其添加到公式中；

  • 输入“*”（乘法符号）；

  • 在“字段”列表中，找到“求和项:单价”，点击【插入字段】，完成公式构建，最终公式为：求和项:销量 * 求和项:单价；

  • 点击【检查公式】，确认公式无语法错误（若公式有误，会弹出提示）。

步骤3：确认添加新字段并调整格式

• 点击【添加】按钮，将“销售额”字段添加到“字段”列表中，此时数据透视表的“值区域”会自动新增“求和项:销售额”列；

• 点击【关闭】，关闭计算字段对话框；

• （可选）右键点击“求和项:销售额”列的任意单元格，选择【设置单元格格式】，将其设置为“数值”或“货币”格式，保留2位小数，提升可读性。

特殊场景：若需基于“平均值”等非求和字段相乘

若数据透视表中的两列并非“求和”汇总，而是“平均值”“计数”等其他汇总方式，操作逻辑一致：在“计算字段”的公式中，插入对应的汇总字段即可。例如，基于“平均值项:人均产值”和“求和项:人数”相乘生成“总产值”，公式为：平均值项:人均产值 * 求和项:人数。

三、Power BI数据透视表：两列相乘设置新字段实操步骤

Power BI中的“矩阵”视觉对象（即数据透视表），需通过“新建度量值”功能实现两列相乘生成新字段，其核心逻辑是利用DAX语言编写乘法公式，适配Power BI的数据分析模型。具体操作步骤如下：

前提准备：确保模型中包含需相乘的字段

假设已在Power BI中导入销售数据，创建了数据模型，包含“地区”“产品类别”“销量”“单价”字段，已添加“矩阵”视觉对象（行=地区，列=产品类别，值=销量（求和）、单价（求和）），需生成“销售额”新字段。

步骤1：新建度量值，编写乘法DAX公式

• 在右侧“字段”窗格中，右键点击数据所在的表（如“销售数据”表），选择【新建度量值】；

• 在公式栏中，输入新字段的名称和乘法公式。核心逻辑：用SUM函数汇总需相乘的两个字段，再用“*”连接。公式示例： 销售额 = SUM('销售数据'[销量]) * SUM('销售数据'[单价])公式解读：SUM('销售数据'[销量]) 表示汇总“销量”字段，SUM('销售数据'[单价]) 表示汇总“单价”字段，两者相乘得到“销售额”。

• 按下回车键，完成度量值的创建，此时“字段”窗格中会新增“销售额”度量值。

步骤2：将新字段添加到矩阵（数据透视表）中

• 点击矩阵视觉对象，激活编辑状态；

• 在右侧“字段”窗格中，将新建的“销售额”度量值拖拽到“值”区域；

• 此时矩阵中会新增“销售额”列，完成两列相乘新字段的添加。

特殊场景：基于筛选条件的两列相乘

若需在特定筛选条件下实现两列相乘（如仅计算“2024年”的销售额），可在DAX公式中添加筛选函数。示例公式： 2024年销售额 = CALCULATE(SUM('销售数据'[销量]) * SUM('销售数据'[单价]), '销售数据'[年份] = 2024)

四、关键注意事项：避免操作失误的核心要点

在数据透视表中通过两列相乘设置新字段时，容易因字段类型、汇总方式、公式逻辑等问题导致结果错误，需重点关注以下注意事项：

1. 确保相乘的两列均为数值型汇总字段

错误场景：将文本字段（如“产品名称”）与数值字段（如“销量”）相乘，导致公式报错；或用原始数据字段（未汇总）直接相乘，结果不符合预期。

解决方案：仅选择“值区域”中的汇总字段（如求和项、平均值项）进行相乘；若原始字段为文本类型，需先转换为数值类型（如通过“数据”选项卡的“分列”“替换”功能）。

2. 注意汇总方式的一致性与合理性

错误场景：将“求和项:销量”与“平均值项:单价”相乘，导致结果失真（如单价为平均值，销量为总和，相乘后并非真实销售额）。

解决方案：确保两列的汇总方式符合业务逻辑。例如，计算销售额时，“销量”和“单价”均需采用“求和”汇总（若单价为固定值，也可采用“平均值”，但需确保同一产品单价一致）；避免将不同汇总方式的字段随意相乘。

3. 公式中避免使用单元格引用，优先使用字段名称

错误场景：在Excel计算字段公式中，直接引用单元格（如“=B2*C2”），当数据透视表结构调整（如新增行/列）时，公式会失效。

解决方案：始终通过“字段”列表插入字段名称（如“求和项:销量”），而非直接引用单元格。数据透视表会自动关联字段与行/列标签，确保结构调整后公式仍有效。

4. 处理异常值，避免结果偏差

错误场景：原始数据中存在“销量为0”“单价为负数”等异常值，导致相乘后的新字段出现不合理数据（如销售额为0或负数）。

解决方案：先清理原始数据，剔除或修正异常值；也可在公式中添加条件判断，如Excel计算字段公式：IF(求和项:销量>0且求和项:单价>0, 求和项:销量*求和项:单价, 0)；Power BI DAX公式：销售额 = IF(SUM('销售数据'[销量])>0 && SUM('销售数据'[单价])>0, SUM('销售数据'[销量])*SUM('销售数据'[单价]), 0)。

5. 数据透视表刷新后需重新检查新字段

错误场景：修改原始数据后，刷新数据透视表，发现新字段结果未更新；或新增行/列标签后，新字段计算逻辑出错。

解决方案：刷新数据透视表后，重新打开“计算字段”（Excel）或检查DAX度量值（Power BI），确认公式未被篡改；若新增了行/列标签，确保标签与相乘字段的关联逻辑合理。

五、实战案例：用两列相乘新字段完成销售分析

以Excel为例，结合具体业务场景演示两列相乘设置新字段的应用：

案例背景：某零售企业2024年1-6月销售数据，包含“地区”“产品类别”“销量”“单价”字段，需分析各地区、各产品类别的销售额及占比。

操作步骤：

1. 创建基础数据透视表：行标签=地区，列标签=产品类别，值区域=求和项:销量、求和项:单价；

2. 通过“计算字段”添加“销售额”新字段，公式=求和项:销量*求和项:单价；

3. 再次添加计算字段“销售额占比”，公式=销售额/总销售额（总销售额可通过“全部”汇总项计算，或单独创建计算字段=SUM(销售额)）；

4. 设置“销售额”为货币格式，“销售额占比”为百分比格式；

5. 分析结论：通过新字段可快速发现“华东地区-电子产品”销售额最高，占总销售额的28%；“西南地区-日用品”销售额最低，占比仅5%，为后续营销资源倾斜提供数据支撑。

六、总结：两列相乘新字段的核心价值与拓展应用

数据透视表中通过两列相乘设置新字段，是基于基础汇总数据进行二次挖掘的关键技巧，其核心价值在于“快速生成衍生指标，深化数据分析维度”。无论是Excel还是Power BI，操作逻辑均围绕“选择字段-构建乘法公式-添加到透视表”展开，只需注意字段类型、汇总方式、公式逻辑的合理性，即可快速完成操作。

除了两列相乘，这一思路还可拓展到更复杂的计算场景，如“两列相加/相减”（如“利润=销售额-成本”）、“多列组合计算”（如“毛利率=(销售额-成本)/销售额”）等。掌握这一技巧，能让数据透视表的分析能力大幅提升，更好地满足业务决策的需求。建议结合自身工作中的实际数据，多动手实践，熟练掌握不同工具下的操作方法，让数据透视表真正成为高效分析的“利器”。

推荐学习书籍 《CDA一级教材》适合CDA一级考生备考，也适合业务及数据分析岗位的从业者提升自我。完整电子版已上线CDA网校，累计已有10万+在读~ !

免费加入阅读：https://edu.cda.cn/goods/show/3151?targetId=5147&preview=0