计算机应用基础：使用函数和公式提高计算效率

2023-11-26 理论教育版权反馈

【摘要】：由此不难看出，当公式中出现众多单元格的引用时，公式的输入量大增、可读性变差，若改用函数描述，将使公式变得简洁、明了、快速。图4-13 九九乘法表利用填充按规律变化的系列数据来完成A3：A11和B2：J2的数据自动填充是非常方便的，这里不再复述。

函数和公式功能是体现Excel功能强大的主要标志之一。通常大多数表格都需要产生各种形式的汇总结果，其计算量之大、复杂度之高根本难以靠人工完成。在单元格里使用函数和公式，就能方便地实现复杂公式的自动快速计算。

4.3.1 公式

在单元格中使用公式必须按下面形式给出：

＝表达式

例如，对图4-5所示的工作表中的2—3班的同学计算平均助学金，并将计算结果放在G15单元格中，我们可以在G15单元格中输入公式：＝（F13＋F14＋F15）/3，在按回车键后， G15单元格中便显示该公式的计算结果140。

4.3.2 在公式中插入函数

若要对图4-5所示的工作表中的全体同学计算平均助学金，则汇总的数据源为F4 F19这16个单元格，假设计算结果放在F20单元格中，此时在F20单元格中输入的公式为：＝（F4＋F5＋F6＋F7＋F8＋F9＋F10＋F11＋F12＋F13＋F14＋F15＋F16＋F17＋F18＋F19）/16。由此不难看出，当公式中出现众多单元格的引用时，公式的输入量大增、可读性变差，若改用函数描述，将使公式变得简洁、明了、快速。

例如，用函数改写上面公式，公式变为：＝sum（F4：F19）/16或＝average（F4：F19），显然，这两个公式的描述简洁明了，其中的sum，average为函数名，代表要执行求和运算和求平均值运算，F4：F19为单元格区域引用，其中“：”为区域运算符。

由于Excel 2010提供的内置函数十分丰富，有财务、数学和三角函数、日期和时间、统计、查找与引用、文本、逻辑、数据库、信息、工程和多维数据集共十一大类，函数的名字与形式难以记住，因此可以直接在单元格中通过插入函数来完成公式描述。Excel2010已将函数升格为预定义公式，插入函数时连公式的引导符“＝”都不用打，方法如下：

（1）单击欲插入函数的单元格。

（2）从“编辑栏”中单击“插入函数”按钮（fx按钮）或打开“公式”选项卡，从“函数库”组中单击“fx按钮”，打开“插入函数”对话框（图4-8）。

（3）从“或选择类别（C）”下拉框中选择函数类别（本例为“常用函数”），再从“选择函数（N）”列表框中选择函数名（本例为AVERAGE），单击“确定”按钮，打开“函数参数”对话框（图4-9）。

（4）向Number1参数框输入数据区域的引用范围（本例为F4：F19）。若对单元格区域的引用地址吃不准，可以单击参数框右端的红色折叠按钮，使对话框暂时隐藏，显露工作表，然后用鼠标选定区域并按回车键，数据区域的引用地址就被输入到参数框中，单击“确定”按钮，公式的计算结果便显示在当前单元格中。

图4-8 “插入函数”对话框

图4-9 “函数参数”对话框

4.3.3 公式复制与地址引用

公式可以复制，这是Excel自动计算功能的精髓，它能有效地避免公式的重复输入，同类型的计算项目越多，效率越高。

1.公式复制

选定已经输入公式的单元格，将鼠标指针移至该单元格的填充柄上，拖曳填充柄至其他欲使用相同计算公式的单元格中，便完成了公式的复制。(www.chuimin.cn)

2.地址引用

公式复制时，Excel系统并不是进行简单的公式内容照搬，而是根据公式原来的位置相对于新位置的变化规律推算出新位置的公式所引用的单元格地址。例如，图4-10（a）显示，当前光标在G3单元格中，编辑栏中显示的公式为E3*D3＋F3（即姓名为“曾为群”的实发工资由其基本工资*职务补贴系数＋奖金得到），而图4-10（b）显示当前光标转到G4单元格中，编辑栏中显示的公式为E4*D4＋F4，原先引用的“曾为群”的三个数据都变为引用第四行“周燕艳”的了，同样后续单元格中的公式都会按照此规则自动调整。在Excel中，单元格地址分为相对地址、绝对地址和混合地址三种。

图4-10 “插入函数”示例

（1）相对地址

相对地址的形式就是〈列名〉〈行号〉形式，例如，图4-10计算实发工资使用的就是相对地址。使用相对地址，在公式复制或移动时，单元格的引用地址会自动调整。

（2）绝对地址

实际应用中，往往需要公式中引用的地址在公式复制或移动时不能随单元格位置变化而变化，这种情况就必须使用绝对地址。绝对地址的形式为：〈$列名〉〈$行号〉，例如，$H$3是第3行第8列单元格的绝对地址，下面以图4-11工作表为例介绍“绝对地址”的概念。

现在对工作表按电器名称统计全年销售额占公司年度总销售额的比例。由图4-11可见，公司年度总销售额已经算出，并存于F7单元格中，F3单元格为电视机的全年销售额占公司年度总销售额的比例，其公式为：＝SUM（B3：E3）/$F$7，分母引用的是绝对地址，为什么要使用绝对地址呢？因为该公式复制到F4，F5，F6单元格后，公式的分母都是引用F7，是不能变的。若将F3中的公式改为：＝SUM（B3：E3）/F7，该公式复制到F4，F5，F6单元格以后，就出现了如图4-12所示的出错信息，F4，F5，F6单元格的公式自动调整为：＝SUM （B4：E4）/F8，＝SUM（B5：E5）/F9，＝SUM（B6：E6）/F10，而F8，F9，F10三个单元格中根本就没有内容，系统自动将其视为0，因此出现了除以0的出错信息。

图4-11 “绝对地址”示例

图4-12 公式出错示例

（3）混合地址

行号或列名中有一个加上前缀符“$”的就称为混合地址。例如，A$1和$A1均为混合地址，下面以图4-13为例介绍“混合地址”的概念。

创建一个九九乘法表，要求A3：A11列和B2：J2行的数字通过自动填充获得，在工作表的B3单元格中输入一次公式，其余单元格的公式都通过复制B3单元格而获得。

图4-13 九九乘法表

利用填充按规律变化的系列数据来完成A3：A11和B2：J2的数据自动填充是非常方便的，这里不再复述。关键是B3单元格中的公式，由于该公式被复制以后必须保证始终引用与当前单元格同一行第1列和同一列第2行两个单元格的乘积，因此B3单元格中的公式必须使用混合地址：＝$A3*B$2，这样就锁定了第1列和第2行，使得公式被复制到其他单元格后，始终引用与当前单元格同一行第一列和同一列第二行两个单元格。

4.3.4 公式中常用的运算符

表4-2列出了公式中常用的几类运算符。

表4-2 Excel常用运算符一览表

计算机应用基础：使用函数和公式提高计算效率

相关推荐