对于如何计算加权平均值的问题,让我们来一起探索一下。我们会借助一个非常实用的Excel函数——SUMPRODUCT,它的操作过程其实相当直观。
图示1展示了各销售人员的获客详情列表。从表中我们可以看到,每位销售人员每次的获客数和平均获客成本都不尽相同。我们的目标是计算出每位销售的平均获客成本。
效果图2则展示了计算后的结果。
下面,让我们一步步来操作:
步骤1:复制A列的数据到E列。
步骤2:选择E列的数据表区域,然后在菜单栏上点击“数据”,接着选择“删除重复值”。
步骤3:在弹出的对话框中,选择“以当前选定区域排序”,然后点击“删除重复项”。
步骤4:保持对话框中的默认设置不变,点击“确定”。
步骤5:完成上述步骤后,点击“确定”继续。
步骤6:接着,在F列中完善表格,可以使用格式刷将C列的格式复制过来,使表格看起来更加整洁。
步骤7:在F2单元格中输入以下公式,并下拉单元格以复制公式:
```
=SUMPRODUCT((A2:A21=E2)B2:B21C2:C21)/SUMIF(A2:A21,E2,B2:B21)
```
此公式虽然看起来有些长,但分解开来理解就不难了。
公式详解:
- `A2:A21=E2`:此部分会将A列的每个单元格与E2单元格的值进行比较,生成一组由TRUE或FALSE组成的数值。
- ...`B2:B21C2:C21`:上述数组与B列和C列相乘,得到“王钢蛋”的每次获客总成本和0组成的数组。
- SUMPRODUCT(...):此函数会对数组进行求和,得到“王钢蛋”所有获客的总成本。
- SUMIF(A2:A21,E2,B2:B21):此函数计算“王钢蛋”的总获客数。
- 将上述两个结果相除,即可得到加权平均后的获客成本。
请留意公式中的绝对和相对引用,确保公式的准确性。