在日常办公数据分析中，数据透视表凭借高效的汇总、分组功能，成为Excel、WPS等办公软件中最常用的数据分析工具之一。其中，“计数项”是数据透视表中高频使用的汇总方式，多用于统计某类数据的出现次数——例如统计不同部门的员工人数、不同产品的下单次数、不同区域的客户咨询量等。

但在实际分析场景中，仅单独查看两个计数项的结果往往不够，我们常常需要对两个计数项进行进一步的公式计算，比如计算两个计数项的差值、比值、占比等，从而挖掘数据背后的关联与规律。例如：统计“实际到岗人数”与“应到岗人数”两个计数项的差值，得出缺勤人数；计算“成交订单数”与“总咨询订单数”的比值，得出成交率。

很多办公从业者在面对这一需求时，常常陷入困境：要么不知道如何在数据透视表内直接插入公式，要么插入公式后出现计算错误、数据不联动更新等问题。其实，只要掌握正确的操作方法，就能轻松实现两个计数项之间的公式计算，既保证计算精度，又能实现数据的实时联动，大幅提升数据分析效率。

本文将从前期准备、核心操作步骤、不同场景实操案例、常见问题排查四个维度，详细拆解数据透视表中两个计数项的公式计算方法，覆盖Excel与WPS通用操作，兼顾新手入门与进阶技巧，让每一位从业者都能精准掌握、灵活运用。

一、前期准备：确保两个计数项规范设置，规避基础隐患

在进行公式计算前，首先要确保数据透视表中的两个计数项设置规范，这是公式计算准确的前提。很多公式计算错误，本质上是计数项设置不规范、数据源存在问题导致的，具体准备工作分为3步：

1. 确认数据源规范，避免数据干扰

数据源是数据透视表的基础，若数据源存在空值、错误值、文本格式数值等问题，会导致计数项汇总异常，进而影响公式计算结果。因此，在创建数据透视表前，需对数据源进行简单预处理[1]：

• 清除空值与错误值：空值、#N/A、#VALUE!等错误值会导致字段被强制汇总为计数项，或导致计数结果偏差，可通过“定位条件”选择空值并填充为0，或使用IFERROR函数替换错误值（如=IFERROR(A1, 0)）[1]。

• 统一数据格式：确保用于计数的字段数据格式一致，避免部分为文本格式、部分为数值格式，可通过“开始”选项卡的“单元格格式”，将目标列设置为“常规”或“数值”格式[2]。

• 避免合并单元格：合并单元格会导致数据引用错误，若数据源中存在合并单元格，需取消合并并填充对应数据，再创建数据透视表[2]。

2. 正确添加两个计数项，明确汇总方式

创建数据透视表后，需将需要计数的字段正确添加到“值”区域，并确认汇总方式为“计数”，具体操作如下：

1. 将需要计数的两个字段（如“应到岗人数”“实际到岗人数”）依次拖入数据透视表的“值”区域；

2. 右键点击“值”区域中的任意一个字段，选择“值字段设置”，在弹出的对话框中，确认“汇总方式”为“计数”（若默认不是计数，可手动选择）[1]；

3. 为两个计数项重命名（如“计数项：应到岗人数”改为“应到岗人数”，“计数项：实际到岗人数”改为“实际到岗人数”），避免公式引用时混淆。

注意：若两个计数项对应的是同一字段的不同分组（如“各部门新客户数”与“各部门老客户数”），可通过“行”区域分组或“筛选”功能区分，再分别添加为计数项。

3. 确认数据透视表结构，方便公式引用

公式计算需引用两个计数项的单元格，因此需确保数据透视表结构清晰，两个计数项在同一行或同一列，便于后续定位引用。建议将两个计数项相邻放置（如并列在“值”区域的相邻位置），减少公式引用时的错误。

二、核心操作：两种方法实现两个计数项公式计算

针对不同的办公需求，我们提供两种核心操作方法，分别适用于“临时计算、无需联动更新”和“长期使用、需联动更新”的场景，操作简单易懂，新手可根据自身需求选择。

方法一：直接在透视表外插入公式（适合临时计算）

这种方法无需修改数据透视表本身，直接在透视表相邻的空白单元格中插入公式，引用两个计数项的单元格进行计算，适合临时分析、数据量较小的场景，操作最快，具体步骤如下：

1. 定位公式插入位置：在数据透视表右侧或下方的空白单元格中，输入公式名称（如“缺勤人数”“成交率”），方便后续识别；

2. 插入公式并引用计数项：点击需要插入公式的单元格，输入“=”，然后依次点击两个计数项对应的单元格，中间加入计算符号（如差值用“-”、比值用“/”、求和用“+”）；

3. 确认公式并填充：输入完成后按Enter键，即可得出计算结果；若需要对多行数据（如多个部门、多个产品）进行计算，可选中该公式单元格，双击单元格右下角的填充柄，快速填充所有行的计算结果。

示例：若“应到岗人数”在单元格C4，“实际到岗人数”在单元格D4，计算缺勤人数的公式为“=C4-D4”；计算缺勤率的公式为“=(C4-D4)/C4”（需将单元格格式设置为“百分比”）。

注意：这种方法的优势是操作简单、无需修改透视表，但缺点是当数据透视表更新（如添加新数据、调整分组）时，公式引用的单元格可能会偏移，需要手动调整引用范围[2]。

方法二：在透视表内添加计算字段（适合长期使用、联动更新）

这是更推荐的方法，通过在数据透视表内添加“计算字段”，直接将两个计数项的公式计算整合到透视表中，当透视表数据更新时，计算结果会自动联动更新，无需手动调整，适合长期使用、数据频繁更新的场景，具体步骤如下（Excel与WPS操作一致）：

1. 选中数据透视表中的任意一个单元格，此时菜单栏会出现“数据透视表分析”（Excel）或“透视表工具”（WPS）选项卡；

2. 点击该选项卡，找到“计算字段”按钮（Excel在“计算”组，WPS在“分析”组），点击后弹出“插入计算字段”对话框；

3. 设置计算字段名称：在“名称”框中，输入计算字段的名称（如“缺勤人数”“成交率”），便于识别；

4. 输入公式并引用计数项：在“公式”框中，先删除默认的“=0”，然后点击“字段”列表中的第一个计数项（如“应到岗人数”），点击“插入字段”，再输入计算符号（如“-”），接着插入第二个计数项（如“实际到岗人数”），完成公式输入；

5. 确认并完成：点击“确定”，此时数据透视表中会自动新增一列，显示两个计数项的公式计算结果，且该列会随透视表数据的更新而自动更新。

示例：计算“成交率”（成交订单数/总咨询订单数），在“公式”框中输入“=成交订单数/总咨询订单数”，其中“成交订单数”和“总咨询订单数”均为透视表中的计数项，插入后点击确定即可生成成交率数据。

关键技巧：若公式中需要用到常数（如乘以100换算为百分比），可直接在公式中添加，例如“=(成交订单数/总咨询订单数)*100”，生成的结果可直接设置为常规格式，无需再手动转换。

三、实操案例：不同场景下的公式计算应用

为了让大家更直观地掌握方法，结合3个日常办公高频场景，详细拆解两个计数项的公式计算过程，新手可直接套用。

案例1：人事统计——计算各部门缺勤人数与缺勤率

场景需求：现有员工考勤数据，需通过数据透视表统计各部门“应到岗人数”（计数项）和“实际到岗人数”（计数项），并计算各部门的缺勤人数（应到-实际）和缺勤率（缺勤人数/应到岗人数）。

操作步骤：

1. 预处理数据源：清除考勤数据中的空值、错误值，确保“部门”“应到岗”“实际到岗”字段格式规范，无合并单元格[1]；

2. 创建数据透视表：将“部门”拖入“行”区域，“应到岗”“实际到岗”拖入“值”区域，均设置为“计数”项，并分别重命名为“应到岗人数”“实际到岗人数”；

3. 添加计算字段1（缺勤人数）：点击“计算字段”，名称输入“缺勤人数”，公式输入“=应到岗人数-实际到岗人数”，确定后新增“缺勤人数”列；

4. 添加计算字段2（缺勤率）：再次点击“计算字段”，名称输入“缺勤率”，公式输入“=缺勤人数/应到岗人数”，确定后新增“缺勤率”列；

5. 格式调整：选中“缺勤率”列，将单元格格式设置为“百分比”，保留2位小数，完成计算。

案例2：电商运营——计算各产品成交率与未成交率

场景需求：现有电商咨询与成交数据，需统计各产品“总咨询订单数”（计数项）和“成交订单数”（计数项），计算各产品的成交率（成交/总咨询）和未成交率（1-成交率）。

操作步骤：

1. 数据源预处理：筛选咨询与成交数据，清除无效数据（如重复咨询、测试订单），确保“产品名称”“咨询记录”“成交记录”字段无文本格式错误[2]；

2. 创建数据透视表：“产品名称”拖入“行”区域，“咨询记录”“成交记录”拖入“值”区域，设置为“计数”项，重命名为“总咨询订单数”“成交订单数”；

3. 添加计算字段（成交率）：公式输入“=成交订单数/总咨询订单数”，名称为“成交率”；

4. 添加计算字段（未成交率）：公式输入“=1-成交率”，名称为“未成交率”；

5. 优化显示：将成交率、未成交率列设置为百分比格式，调整透视表列宽，确保数据清晰。

案例3：客户管理——计算各区域客户留存数与留存率

场景需求：现有客户数据，需统计各区域“总客户数”（计数项）和“留存客户数”（计数项），计算各区域客户留存数（留存客户数-新增客户数，此处新增客户数为总客户数-留存客户数，可反向计算）和留存率（留存客户数/总客户数）。

操作步骤：

1. 数据源预处理：确认客户数据中“区域”“客户ID”“留存标记”字段规范，使用ISNUMBER函数检查数值格式，清除错误值[1]；

2. 创建数据透视表：“区域”拖入“行”区域，“客户ID”（计数为总客户数）、“留存标记”（计数为留存客户数）拖入“值”区域，重命名计数项；

3. 添加计算字段（留存率）：公式输入“=留存客户数/总客户数”，名称为“客户留存率”；

4. 验证数据：手动核对某一区域的计算结果，确保公式引用正确，无偏差。

四、常见问题排查：解决公式计算错误、不更新等问题

在实际操作中，很多从业者会遇到公式计算错误、结果不更新、引用失败等问题，以下是最常见的4个问题及排查方法，快速解决各类隐患[1][2]：

问题1：公式引用后显示“#DIV/0!”（除以零错误）

原因：两个计数项中，作为除数的计数项结果为0（如总咨询订单数为0，计算成交率时就会出现此错误）。

解决方法：修改公式，添加IFERROR函数规避错误，例如将“=成交订单数/总咨询订单数”改为“=IFERROR(成交订单数/总咨询订单数, 0)”，当除数为0时，显示为0，避免错误提示。

问题2：计算字段不显示，或公式引用不到计数项

原因：1. 未选中数据透视表单元格，导致“计算字段”按钮灰色；2. 计数项名称存在特殊字符（如空格、标点），导致公式无法识别；3. 数据源字段名称与透视表计数项名称不一致。

解决方法：选中透视表任意单元格，确保“计算字段”按钮可点击；修改计数项名称，删除特殊字符，保持名称简洁；公式中引用的字段名称，需与透视表中计数项的名称完全一致（区分大小写）。

问题3：透视表数据更新后，公式计算结果不更新

原因：使用了“方法一”（透视表外插入公式），引用范围固定，未随透视表更新；或使用“方法二”但未刷新透视表。

解决方法：若使用方法一，需手动调整公式引用的单元格范围；若使用方法二，右键点击数据透视表，选择“刷新”，计算字段的结果会自动同步更新。建议长期使用方法二，避免手动调整。

问题4：计数项汇总结果异常，导致公式计算偏差

原因：数据源存在重复数据、空值、文本格式数值，或计数项的汇总方式错误（如误设为“求和”）[1]。

解决方法：重新检查数据源，删除重复数据、处理空值与错误值，统一数据格式；右键点击计数项，选择“值字段设置”，确认汇总方式为“计数”，而非“求和”“平均值”等。

五、进阶技巧：让两个计数项公式计算更高效

• 批量修改计算字段：若需调整公式，可右键点击计算字段列的标题，选择“字段设置”，在弹出的对话框中修改公式，无需重新添加计算字段；

• 利用计算项实现分组计算：若两个计数项是同一字段的不同分组（如“新客户数”“老客户数”），可添加“计算项”，而非“计算字段”，精准实现同一分组内的两个计数项计算；

• 结合Power Query预处理数据：对于海量数据，可使用Power Query批量清洗数据源，批量替换错误值、统一格式，减少后续计数项异常的概率[1]；

• 公式快捷键：输入公式时，可使用F4键快速切换绝对引用、相对引用，避免引用范围偏移（适合方法一）。

六、总结：简单两步，搞定透视表两个计数项公式计算

数据透视表中两个计数项的公式计算，核心是“规范设置计数项+选择合适的公式插入方法”——临时计算用“透视表外插入公式”，快速高效；长期使用用“插入计算字段”，联动更新，无需手动调整。

无论是人事统计、电商运营，还是客户管理，只要掌握本文的操作步骤，就能轻松实现两个计数项的差值、比值、占比等计算，摆脱手动统计的繁琐，提升数据分析的效率与精度。同时，规避数据源不规范、公式引用错误等常见问题，就能让数据透视表真正成为高效的数据分析工具，为办公决策提供精准支撑。

需要注意的是，公式计算的核心是“数据准确”，因此前期的数据源预处理、计数项规范设置至关重要，只有基础数据无误，才能确保公式计算结果的可靠性。掌握这些方法后，你会发现数据透视表的数据分析能力会大幅提升，轻松应对各类办公场景的需求。

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

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