用Excel电子表格计算的五种形式是:快速计算法,公式计算法,函数表达式计算法 ,单变量求解和规划求解计算法。下面以实例说明。 一. 快速计算法。 快速计算法的特点是不用输入公式,只需在Excel电子表格选中有关单元格,单击有关公式的名称,就能得出结果。用填充柄拖放就能相对引用单元格,几秒钟就能完成几个小时甚至几天的计算量,并且不会出差错。 请看图-1“期考成绩表”:
图-1
例子: 1. 计算杨欣的总分。按住鼠标左键,从B2拖到F2 ,即选中B2到F2区域,然后单击右上角的∑“自动求和”下拉箭头▼ ,在下拉列表中单击“求和”,(已用红笔标出),见图-2:
图-2
在G2单元格就能看到杨欣的总分是434分。单击G2单元格,把光标放在G2上,当方框右下角显示十字形时,按住鼠标左键,填充柄从G2拖到G13 , 释放鼠标,就能得出从杨欣到吴章各个学生的总分。(因为他们都相对引用了杨欣计算总分时所用的公式)见图-3:
图-3
2.计算杨欣的平均分。按住鼠标左键,从B2拖到F2 ,然后单击右上角的∑“自动求和”下拉箭头▼ ,在下拉列表中单击“平均值”,在H2单元格就能看到杨欣的平均分是86.8分,最后单击H2单元格,把光标放在H2上,当方框右下角显示十字形时,按住鼠标左键,填充柄从H2拖到H13 , 释放鼠标,就能得出从杨欣到吴章各个学生的平均分。(因为他们都相对引用了杨欣计算平均分时所用的公式)见图-4:
图-4
3.计算语文科最高分。按住鼠标左键,从B2拖到B13 ,然后单击右上角的∑“自动求和”下拉箭头▼ ,在下拉列表中单击“最大值”,就能在B14单元格看到语文科的最高分是90分,单击B14单元格,当方框右下角显示十字形时,从B14拖到F14,释放鼠标,就显示出从语文到化学这五科各科最高分。(这个过程叫用填充柄拖放), 注意,如果操作过程中出现差错,请按屏幕左上角的“撤消”按钮,撤消错误操作。或用屏幕右侧的Delete键删除错误。 见图-5:
图-5
练习 请你用“快速计算法”计算出各科最低分。 “快速计算法”具体步骤请大家看日志 《 如何用Excel电子表格统计学习成绩或企业业绩 》。 二. 公式计算法 公式计算法的特点是,由于公式主要由常量组成,所以一般具有数学知识的人都可以根据语法自行组建公式,并用填充柄拖放,达到快速计算的目的。 例子: 1. 如上面图-1,用公式法计算杨欣的总分。 在G2单元格输入=B2+C2+D2+E2+F2 然后按Enter键,在G2单元格就得出了杨欣的总分是434分。用填充柄向下拖放就得出了各个学生的总分。 也可以在G2单元格输入=SUM(B2:F2) ,按Enter键,结果相同。(注 SUM表示求和,B2:F2表示B2,C2,D2,E2,F2这个区域。) 还可以在G2单元格输入=SUM(89,81,93,88,83) ,然后按回车键。结果相同。 上面列举了三个结果相同的公式。 (注 在单元格输入公式计算,必须有等号=开头。否则系统就不会启动计算程序。)见图-6:
图-6
2. 用公式法计算杨欣的平均分。 在H2单元格输入=AVERAGE(B2:F2) ,按回车键,得杨欣的平均分为86.8 ,用填充柄向下拖放就得出了各个学生的平均分。 (注 average 是平均值的意思) 3. 用公式法计算体育生模拟考总分合计。现有一个工作簿,里有sheet1,sheet2,sheet3 三个工作表,按固定顺序分别登记了某校6个体育生参加3次高考模拟考成绩。三个工作表G2单元格是考生杨洋的3次模拟考总分,(分别是312,301,300分)。H2到H7单元格是待填的各体育生总分合计。怎样算出他们3次模拟考总分合计? 在3个工作表任选一个工作表(例如sheet1),在H2单元格输入以下公式 =SUM(sheet1:sheet3!G2) 按Enter键,就得出了考生杨洋3次模拟考总分合计,(913分)。鼠标放在H2单元格,方框右下角出现 十字形时,填充柄向下拖动,就得出了所有体育生3 次模拟考总分合计。 4.计算语文科及各科及格人数。(即符合分数大于59分条件的学生的个数)。在图-6的B17单元格输入以下公式 =COUNTIF(B2:B13,">59") 按Enter键,就得出语文科的及格人数12 ,把鼠标放在B17单元格,当方框右下角出现十字形时,填充柄从B17拖到F17,释放鼠标,即得到各科及格人数。 注 上面COUNT是统计个数,IF(如果)是表示条件。公式中标点符号必须是在英文输入法时输入。 如何了解Excel公式用法? 连网情况下, 在打开的Excel 电子表格中按功能键F1, 弹出“Excel帮助”对话框,在搜索框输入你要提 问的问题,例如“ countif 公式的用法”,单击“搜索”,在弹出的对话框中就可以查到此公式的各 种用法 和实例。一般不需要在百度谷歌等搜索网站寻找. 练习 (1) 计算语文科和各科的最高分 。(注 最大值的名称是MAX) 。 (2)计算语文科和各科的最低分 。(注 最小值的名称是MIN ) 三.函数表达式计算法 函数属于预定义公式,利用特定参数按特定顺序进行计算,能进行更复杂的计算。函数表达式根据设定的公式返回运算结果。 例子. 1. 计算图-4 成绩表的“优秀否”。 计算“优秀否”要先算出各个学生的平均分。(此表已算出各个学生的平均分。),平均分大于或等于80分被评为优秀。该函数表达式是: IF(H2>=80,”优秀”) 该表达式意思是 如果(英文IF)H2单元格的数值大于或等于80,则为优秀,否则为假(即FALSE).(注意 表达式中的标点符号必须在英文输入法中输入,把表达式输入单元格时,必须以等号=开头,否则出错。) 在I 2单元格输入=IF(H2>=80,”优秀“) ,然后按Enter键。 得出杨欣成绩是“优秀”。单击I2单元格,鼠标放在上面,当方框右下角显示十字形时,向下拖动填充柄到i13,得出各生优秀否。没有被评上优秀的系统用FALSE表示。 见图-7和图-8:
图-7
图-8
用“查找-替换”法把FALSE 删除掉。只留下“优秀”。 2. 计算直角三角形的边长。 下图给出了直角三角形勾和股长,见-9:
图-9
直角三角形勾,股,弦有如下关系: 弦2 =勾2 + 股2 , 可用N个数的平方和的函数表达式表示: SUMSQ (number1,number2,number3,……) (注意 此处不能有加号+)。 SUMSQ 表示平方和,number 表示有关参数,在上图B4单元格输入=SUMSQ(B1,B2) 按Enter键,得出弦的平方是225 。 也可以输入=SUMSQ(12,9) 按Enter 键,结果相同。(注意 此处不能写加号+) 如果求弦长,可用以下函数表达式: POWER(number1,number2,…1/2) POWER 表示乘幂,1/2表示开平方,1/3开立方,…… 在上图B3单元格输入=POWER(B4,1/2) , 按Enter键,得弦长为15 。见图-10:
图-10
3. 计算向银行房贷每月还贷金额。每月还贷函数表达式是:
PMT(rate,nper,pv,fv,type) 语法说明:PMT是还贷函数名称,rate是贷款月利率,nper是还贷月限,pv是贷款金额,fv是未来值,未来还清后,值为0。type是贷款人每月应还款时间,月初还为1,月底还为0 。 假如你向银行房贷20万元,贷款年利率为6.55% (2014年房贷利率)。还款期为10年,你每月初还贷,你每月应还款多少元呢? 新建Excel工作表之前,先整理有关数据:年利率为6.55% ,那么月利率应为0.0655/12=0.005458 , (一年有12个月,所以月利率应为年利率除以12 )。还款期为10年,因为要按月还贷,所以10年要化为120个月,即 10*12=120 ,另,20万元应写成200000 ,未来还清款,未来值为0,月初还贷,值为1. 在以下新建Excel工作表中,分别在A1,B1,C1,D1,E1,F1单元格中输入: 贷款月利率,还贷月限,贷款金额,未来值,还贷时间,每月还贷金额 。分别在A2,B2,C2,D2,E2输入 0.005458, 120, 200000,0,1 。 (注意 A1 ,F1, 单元格字数较多,你要增大列宽,否则,按下Enter键后,系统会弹出列宽不够的警告提示:##### ).见图11-:
图-11
根据每月还贷函数表达式,在上图F2单元格输入=PMT(A2,B2,C2,D2,E2) 。见图12:
图-12 按Enter键,得¥-2263.66 即在10年还款时间内,每月应还款2263.66元。见图-13:
图13 10年内,你连利息在内,还给银行271639.2元,你实际上还了利息71539.2元。
练习 假如你要购买商品房,向银行贷款50万元,分15年还清,年利率为6.55% ,每月月末还款,计算你每月应还款多少? (此题的答案是-4369.18元) 四.单变量求解计算法
单变量求解是公式求解的逆过程,它知道公式的结果,求解获得此结果的输入值,因此它为此事先建立好数学模型。在模型里填入有关数值,就能得出计算结果。 单变量求解一个重要功能就是直接求解方程式。把A1单元格设为存放X的可变单元格,把A2单元格设为输入公式的目标单元格,在“数据”选项卡“数据工具”组中单击“假设分析—单变量求解”,在弹出的数学模型中填空就能得到答案。 单变量求解的例子,请大家看我的日志《如何用Excel电子表格解方程》一文,这里不再重复。 五.规划求解计算法 规划求解也要建立数学模型,确定目标单元格,可变单元格,确定目标值与可变值之间的关系,以及约束条件等,它可以求解更多变量。规划求解一个重要功能就是直接求解方程组,既快捷又准确。 |
|