excel解决实际问题.doc

Excel是办公室自动化中非常重要的一款软件,它不仅仅能够方便的处理表格和进行图形分析，其更强大的功能体现在对数据的自动处理和计算，在此总结一些Excel的不被人注意的几个问题。一Excel中会变色的数字财务人员经常要用有颜色的数字来表示负数，作为教务工作者希望能用颜色区分出成绩的不同分数段。1 负数用红色显示 用鼠标选取数据区域，右击出现快捷菜单，选中“设置单元格”项，选“数字”选项卡,在“分类”列表中选“数值”项，在右下方出现“负数”格式设置表中选其中的红色的“-1234.10”，再按“确定”退出，这样数据中的负数会以红色显示。2 对数据作简单的颜色分类显示 例如对大于1000的用红色表示，小于1000的用绿色表示。可用自定义的方法解决。先选取数据区域，用“格式”菜单中的“单元格”，打开对话框，选“数值”选项卡，在“分类”列表中选“自定义”，项目，然后在“类型”输入框内输入“=1000红色#0.00”,最后按“确定”3 对多段数据作颜色分类显示 例如对一组考试成绩（在0100分之间）作分组，要求：小于60分是不及格，用红色显示，6070分为及格，用蓝色表示，7085分为良好，用绿色显示，85分以上为优秀，用黑色表示。这可以用条件格式的方法处理，选取数据区域，在“格式”菜单中选“条件格式”项，打开“条件格式”对话框，在“条件1”中的第一栏不改变默认的“单元格数据”，第二栏（条件）选择“小于”，第三栏输入“60”，再单击“格式”，在“单元格格式”对话框中有三个选项分别为“字体”、“边框”和“图案”，选择“字体”中“颜色”为红色。单击下面的“添加”，自动出现“条件2”，第二栏选择为：“介于”，第三栏输入“60”，第四栏输入“70”，单击“格式”，选择“字体”页中的“颜色”为蓝色。再单击下方的“添加”，出现了“条件3”，第二栏选择为“介于”，第三栏输入“70”，第四栏输入“85”。再单击“格式”，选择“字体”页中的“颜色”为绿色，然后按下“确定”。此时从0到85分数段的分数都已改变颜色，85分以上的分数没有改变颜色，则为黑色。二数据的统计对教师而言，在考试后经常要统计学生的人均分数、及格人数、及格率，每个学生的平均分以及学生成绩的排序。1对学科的总分、人均分、及格率、及格人数等的统计 假设A1：A30单元格存放某班学生的某课考试成绩，若要计算该科的总分、人均分数、及格人数、及格率。(1)求总分：若要将该科的总分存放在A31单元格中，只需选定该单元格并输入求和公式：“SUM(A1：A30)”，回后即可计算出该科的总分。(2)求人均分：若要将该科人均分数存放在A32单元格中，选定该单元格并输入求平均值公式：“AVERAGE(A1：A30)”，回车后即可计算出人均分数(3)求及格人数：若要将该科的及格人数存放在A33中，选定该单元格并输入条件计数公式“COUNTIF(A1：A30，60)”回车，即可计算出该科的及格人数。COUNTIF函数可以统计某一区域中符合条件的单元格数目，它的语法为COUNTIF（range，criteria）。其中range为参与统计的单元格区域，criteria是以数字、表达式或文本形式定义的条件。其中数字可以直接写入，表达式和文本必须加引号。(4)求及格率：若要将该科的及格率存放在A34中，选定该单元格并输入公式“COUNTIF(A1：A30，60)/ COUNT (A1：A30)”回车，即可计算出该科的及格率(其中“COUNT (A1：A30)”是计算A1：A30单元格中为实际考试人数， COUNT 是计算数组或单元格区域中数字项的个数的函数)。2、对学生的总分和平均分的统计若A1：G1区域存放某个学生各科的成绩，需在H1中存放该学生的总分，选定该单元格并输入公式“=(A1：G1)”回车即可计算出该生各科的总分，按照上面介绍的方法沿列拖动鼠标即可将全班学生各科的总分均可计算出来。若某生未考试，但不希望在其总分单元格填入“0”，只需将公式修改为“IF(ISNUMBER(A1)，SUM(A1：G1)，)”回车，单元格中会填入空格而不会出现“0”。函数的语法为IF(logical_test,value_if_true,value_if_false)，Logical_test 指计算结果为 TRUE 或 FALSE 的任何数值或表达式。Value_if_true指 Logical_test 为 TRUE 时函数的返回值。Value_if_false指 Logical_test 为 FALSE 时函数的返回值。函数的语法为ISNUMBER(Value)，Value 为需要进行检验的数值。Value是数值则为真，其它则为假。3、对学生的成绩排序若要将该班学生的班级排名计算出来，但不希望打乱学生原来按学号的顺序，则可利用排位函数RANK轻松完成。该函数的最大优点是，可以处理重名次，可使数值相同的单元格计算出的名次相同。如上例在H1：H30区域中存放某班学生的总分，要将班级排名结果放在I1：I30区域中，只需在I1中输入公式“RANK(H1，$H$1：$H$30)”回车，即可计算出该生在班级中所排名次，然后沿列拖动复制，即将全班学生的排名计算出来。RANK函数是Excel计算序数的主要工具，它的语法为:RANK （number，ref，order），其中number为参与计算的数字或含有数字的单元格，ref是对参与计算的数字单元格区域的绝对引用，order是用来说明排序方式的数字（如果order为零或省略，则以降序方式给出结果，反之按升序方式，函数 RANK 对重复数的排位相同，但重复数的存在将影响后续数值的排位。）。如果某些学生未参加考试，又不希望在其存放名次的单元格内显示出错信息(如：#VALUE！)，则可将公式修改为“IF(ISNUMBER(H1)，RANK(H1，$H$1:$H$30), )”。4、把分数转化为等级计算如学校采用等级评定考试成绩，若标准为：考试分数高于或等于85分为A等；考试分数低于85分且高于或等于70分为B等；考试分数低于70分且高于或等于60分为C等；考试分数低于60分为D等；没有参加考试的不划等级。假设分数存放在B列，计算结果存入C列，则用Excel计算等第的公式如下：“IF(B185，A，IF(B170，B，IF(B160，C，IF(ISNUMBER(B1)，D，IF(ISBLANK(B1)，)。这是一个IF函数嵌套公式，第二个 IF 语句同时也是第一个 IF 语句的参数 value_if_false。同样，第三个 IF 语句是第二个 IF 语句的参数 value_if_false，以此类推。如果第一个逻辑判断表达式B185为TRUE(真)，则C1单元格式被填入“A”；如果为FALSE(假)，则计算第二个IF语句“IF(B170”；以此类推直至计算结束。其中ISNUMBER函数在B1为空时返回FALSE(假)，接着执行最后一个IF语句，否则在C1单元格中填入“D”。ISBLANK函数在B1为空时返回TRUE(真)，则在C1单元格中填入一个鉴定会格。使用ISNUMBER函数和ISBLANK函数，可防止某个学生没有参加考试(即考试成绩为空)，但仍然给他评定为D等的情况发生。如需计算其他学生的成绩等第，只需将公式复制即可。函数的语法为 ISBLANK (Value)，Value 为需要进行检验的数值。Value是空则为真，其它则为假。如果成绩等第划分标准发生了变化，只须改变逻辑判断式中的值(85，70，60)即可，也可将A、B、C、D分别换成优秀、良好、及格、不及格。5.查最高分和最低分若要查询最高分和最低分，则可利用MAX(MIN)函数。假设C1：C30区域中存放着30名学生的考试成绩，则可选中一个空白单元格，在编辑栏输入公式“MAX(C1：C30)”回车后即可计算出其中的最高分，如果将上述公式中的函数名改为MIN，其他都不变，就可以计算出C1：C30区域中的最低分。三、一些日常生活中的数据统计1. 求工资总数以常见的工资表统计为例，它的特点是需要对行内若干个单元格求和，并要自动扣除“水电费”，“税金”等。根据习惯，这些项目并不输负数，这时可以构造如下公式：“SUM(A2：C2，D2，E2)”。其中A2：C2引用是收入，而D2，E2表示支出。由于Excel不允许在单元格引用前面加负号，所以，应在表示支出的单元格前加负号，这样即可计算出正确结果。即使支出数据所在的单元格连续，也必须用逗号将它们逐个隔开。2评比打分统计一般比赛采用打分的方法进行评价，为了防止个别人的极端行为，一般计算平均分要去掉最高分和最低分。假若B1：B10区域存放某位参赛者的比赛得分，则去掉一个最高分和一个最低分后的平均分计算公式为“TRIMMEAN(B1：B10，0.2)”。公式中的0.2表示10个数据中去掉2个(100.2)，即一个最高分和一个最低分。若只有7位评委打分，则只需将0.2改成2/7即可。函数 TRIMMEAN 先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。当希望在分析中剔除一部分数据的计算时，可以使用此函数。其语法格式为：TRIMMEAN(array,percent)，Array 为需要进行筛选并求平均值的数组或数据区域，Percent 为计算时所要除去的数据点的比例。3贷款的计算如今贷款买房子或车子的人越来越多，计算某一贷款的月偿还金额是考虑贷款的重要依据，Excel提供的PMT函数是完成这一任务的好工具，这是一个基于固定利率及等额分期付款方式的函数，返回投资或贷款的每期付款额。其语法格式为：PMT(rate,nper,pv,fv,type)，语法中的rate为各期利率，是一固定值；nper为总投资（或贷款）期；pv为现值，是贷款本金；fv为未来值，最后一次付款后剩余的贷款金额，如果省略，则认为它的值为零；type为0或1，用来指定付款时间是在月初还是月末，如果省略，则认为其值为0，1为月初，0或忽略为月末。假如为购房贷款十万元，如果年利率为7%，每月末还款，采用十年还清方式时，月还款额计算公式为“PMT(7%/12,120,100000)”。其结果为-1,161.08，就是你每月须还贷款1161.08元。三数据的快速录入1. 工作簿的共享设置共享假设有甲乙用户分工合作共同完成录入操作，二人分别在1号机和2号机上同时操作，这二台机器已通过网络连接起来，学生学籍资料信息2003.xls存放在1号机器的硬盘E:上。(1)在“工具”菜单中单击“共享工作簿”命令，然后单击“编辑” 标签。(2)选择“允许多用户同时编辑，同时允许工作簿合并”复选框，在“高级”标签中可以对“自动更新间隔”、“用户间修订冲突解决方法”以及“个人视图属性”等进行设置，在通常情况下，“高级”标签中的各个参数可直接使用系统默认值，然后单击确定即可。 (3)出现提示时，保存工作簿。(4)在“文件”菜单中单击“另存为”，然后将共享工作簿保存在其他用户可以访问到的一个网络资源上(如1号机器的E:)。使用共享查看冲突日志查看冲突日志有两种方法：一是在工作表上将鼠标箭头停留在被修订过的单元格上，突出显示详细的修改信息；二是在单独的冲突日志工作表上罗列出每一处冲突，方法如下：(1)在“工具”菜单中，选择“修订”子菜单，再单击“突出显示修订”命令。(2)选中“编辑时跟踪修订信息，同时共享工作簿”复选框，该复选框将开启工作簿共享和冲突日志。(3)选中“在屏幕上突出显示修订”复选框，这样在工作表上进行需要的修改后，Excel会以突出显示的颜色标记修改、插入或删除的单元格。 (4)选中“在新工作表中显示修订”选项，将启动冲突日志工作表。(5)单击确定按钮，当弹出对话框提示保存工作簿时，再次单击确定按钮，保存工作簿。2下拉菜单实现输入有时候在各列或各行中都输入同样的几个值，比如说，输入学生的等级时只输入四个值：优秀，良好，合格，不合格。希望Excel 2000单元格能够象下拉框一样，让输入者在下拉菜单中选择就可以实现输入。操作步骤：先选择要实现效果的行或列；再点击“数据”菜单下的“有效性”，打开“数据有效性”对话框；选择“设置”选项卡，在“允许”下拉菜单中选择“序列”；在“数据来源”中输入“优秀，良好，合格，不合格”(注意要用英文输入状态下的逗号分隔！)；选上“忽略空值”和“提供下拉菜单”两个复选框，即完成设置。3自动实现输入法中英文转换有时，在不同行或不同列之间要分别输入中文和英文。希望Excel能自动实现输入法在中英文间转换。操作步骤：假设在A列输入学生的中文名，B列输入学生的英文名。先选定B列，点击进入“数据有效性”，打开“数据有效性”对话框；选择“输入法”对话框，在“模式”下拉菜单中选择“关闭(英文模式)”；然后再“确定”。4. 输入提速在编辑Excel工作表时，可能会遇到大量重复数据需要录入的情况。操作如下：选择需要的单元格区域，然后打开“单元格格式”对话框，单击“数字”选项卡，在“分类”框中单击“自定义”，在“类型”框中选择“”（“”是半角字符）后跟肯需重复的字，例如