5.4.5 公式操作的实用案例

1. 无函数的公式与计算

(1)案例要求

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

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

图5- 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)最终效果

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

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

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

(1)案例要求

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

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

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

图5- 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)最终效果

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

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

3. 复杂的公式与计算

(1)案例要求

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

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

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

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

图5- 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)最终效果

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

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

4. 带有混合坐标的公式与计算

(1)案例要求

已知Excel工作簿“E0204素材.xlsx”，请在其中创建一个乘法口诀表。如图5-27所示。

图5- 27乘法口诀表的最终结果

(2)操作思路

利用公式填充的方式分别输入水平和垂直的数据序列1，2，3……，然后输入一个具有一般意义的公式，最后把公式复制到矩形区域。最终，实现一张乘法口诀表。

(3)具体过程

首先，在单元格B1和C1中输入数据1、2，以鼠标拖动选中区域B1:C1，然后鼠标指向区域右下角的填充柄，向右拖动到J1，填充一个1-9的等差数列。

其次，利用【开始】选项卡的【字体】区块，设置区域的背景色为“浅蓝色”。

第三，在单元格A2和A3中输入数据1、2，以鼠标拖动选中区域A2:A3，然后鼠标指向区域右下角的填充柄，向下拖动到A10，填充一个1-9的等差数列。

第四，利用【开始】选项卡的【字体】区块，设置区域的背景色为“红色”。

第五，在单元格B2中输入公式：=$A2*B$1；

第六，以鼠标指向B2右下角的填充柄，向下拖动鼠标直到B10。此时，区域B2:B10被填充并构成区域；以鼠标指向此区域的右下角填充柄，向右拖动填充区域B2:J10，使之形成乘法口诀表。