6.4.4 公式操作的实用案例

1. 无函数的公式与计算

(1)案例要求

已知Excel工作簿”E0201素材.xlsx”，其中的工作表“学生成绩表”中是某中学学生的成绩。如图6-21所示。

现在需要计算出每个学生的总分，如果学校规定按照“总分的2倍+奖励的0.5倍”计算学生的补贴值，请算出每个学生的补贴额。最后根据“总额=奖励+补贴”的规则计算每个学生所获金钱的总额。

图6- 21待处理的原始记录

(2)操作思路

针对第1名学生输入一个具有一般意义的公式，然后可以借助“公式填充”或者“公式复制”的方式把公式填满其他学生的相应单元格。

(3)具体过程

首先，在单元格M3中输入公式：=E3+F3+G3+H3+I3+J3+K3+L3。

其次，以鼠标指向单元格M3右下角的填充柄，向下拖动鼠标，直到M24。

第三，在单元格O3中输入公式：=M3*2+N2*0.5。

第四，以鼠标指向单元格O3右下角的填充柄，向下拖动鼠标，直到O24。

第五，在单元格P3中输入公式：=N3+O3。

第六，以鼠标指向单元格P3右下角的填充柄，向下拖动鼠标，直到P24。

(4)最终效果

处理之后的最终效果如图6-22所示。

图6- 22处理之后的最终结果

2. 带有简单函数的公式与计算

(1)案例要求

已知Excel工作簿”E0202素材.xlsx”，其中的工作表“学生成绩表”中是某中学学生的成绩。如图6-23所示。

现在需要计算出每个学生的总分，如果学校规定按照“总分的2倍”计算学生的补贴值，请算出每个学生的补贴额。最后根据“总额=奖励+补贴”的规则计算每个学生所获金钱的总额。

求出全体同学语文、数学等科目的平均值。

图6- 23待处理的学生成绩表

(2)操作思路

针对第1名学生输入一个具有一般意义的公式，然后可以借助“公式填充”或者“公式复制”的方式把公式填满其他学生的相应单元格。

(3)具体过程

首先，在单元格M3中输入公式：=sum(E3:L3)。

其次，以鼠标指向单元格M3右下角的填充柄，向下拖动鼠标，直到M24。

第三，在单元格O3中输入公式：=M3*2。

第四，以鼠标指向单元格O3右下角的填充柄，向下拖动鼠标，直到O24。

第五，在单元格P3中输入公式：=N3+O3。

第六，以鼠标指向单元格P3，键入<Ctrl>+C，以鼠标拖动选中区域P3:P24，然后键入<Ctrl>+V。

第七，在单元格E26中输入公式：=AVERAGE(E3:E24)；

第八，以鼠标指向单元格E26右下角的填充柄，向右拖动鼠标，直到P26。

(4)最终效果

处理之后的最终效果如图6-24所示。

图6- 24处理之后的最终结果

3. 复杂的公式与计算

(1)案例要求

已知Excel工作簿”E0203素材.xlsx”，其中的工作表“学生成绩表”中是某中学学生的成绩。如图6-25所示。

现在需要在B25根据姓名统计出学生人数，在区域E25:P25中统计出数据的个数。接着在E26:P26计算出相应列的均值，在E27:P27中计算出相应列的最大值。

在O29中计算出男生人数，在O30中计算出女生人数。

在区域E30:G33中计算出语文、数学、外语在各个分数段的值。

图6- 25处理之后的最终结果

(2)操作思路

针对第1名学生输入一个具有一般意义的公式，然后可以借助“公式填充”或者“公式复制”的方式把公式填满其他学生的相应单元格。

(3)具体过程

首先，在单元格B25中输入公式：=CountA(B3:B24)。

其次，在E25中输入公式：=count（E3：E24）。

第三，以鼠标指向单元格E25右下角的填充柄，向右拖动鼠标，直到P25。

第四，在单元格E26中输入公式：=average(E3:E24)。在单元格E27中输入公式：=max(E3:E24)。

第四，以鼠标拖动选中区域E26：E27，然后指向区域右下角的填充柄，向右拖动鼠标，直到P27。

第五，在单元格O29中输入公式：=COUNTIF(C3:C24,"男")，在O30中输入公式：=COUNTIF(C3:C24,"女")。

第六，依次在单元格E30:E33中输入公式:

=Countif(E3:E24,”<60”)、

=Countif(E3:E24,”<80”)-Countif(E3:E24,”<60”)、

=Countif(E3:E24,”<90”)-Countif(E3:E24,”<80”)、

=Countif(E3:E24,”>=90”)。

第七，以鼠标拖动选中区域E30：E33，然后指向区域右下角的填充柄，向右拖动鼠标，直到G33。

(4)最终效果

完成所有操作之后的最终结果如图6-26所示。

图6- 26处理之后的最终结果