搜索
重庆邮电大学经济管理学院周玉敏要求掌握基本的Excel操作，例如：单元格格式的设置、简单的公式使用等。对基本概念熟悉，如：绝对引用、相对应用。以应用案例的方式讲解Excel的相关知识，不再讲解基本操作部分。理论学时16学时、实验学时16学时，缺席3次取消考试资格。推荐教材：电子工业出版社《Excel与管理数据分析（第3版）》杜茂康主编推荐学习网站：认识Excel2.如何设计表格（2h）3.函数的使用（6h）4.图表的制作（4h）5.数据分析（4h）Excel的发展历史你认为利用Excel能做哪些工作？◦画表格◦计算：求和、计算平均值结论：Excel易学、功能简单咱们做一点测试题吧你能得多少分呢？1.认识Excel2.如何设计表格3.函数的使用4.图表的制作5.数据分析专业化表格设计原则利用“条件格式”和“数据有效性”设计友好表格•整张报表很少用表格线，却非常清晰、明了•作为一个局外人我们可以在最短的时间了解报表的内容和所要表达的意图如果我们来做这张报表，一般会是什么样呢？按照我们的习惯，做出的表格一般是这样的同专业人士制作的表格相比有什么区别呢？不能看清表格的内容，但依然能清楚地了解表格内容的层次没有规律的空间分隔，像蜘蛛网一样的表格线，不知从何看起根据项目类别将不同项目间距扩大，相同项目间距缩小；将最重要的放在左上角，对于报表的核心数据应用下划线标出，把不重要的放在下方；对于重点区域用不同的符号进行标注，但标注不宜过多；报表正文字型一般选择“宋体”较为严谨，“黑体”作为标题或需特别强调的区域，显得较为庄重；设置单元格格式：数字显示格式、对齐方式、填充颜色等，使报表更清晰明了。完成本表涉及到的知识点：•单元格命名•数据有效性•条件格式•函数indirect的用法1、名字概述可以给一个单元格或单元格区域取一个名字，这个名字可以出现在公式中，用来指代引用的单元格或单元格区域，这会使公式的意义更加明确。名字其实是一个标识符，由字母或下划线开头的一个或多个字符组成。在Excel中，还可以用汉字字符作为名字。名字不区分字母的大小写，比如ABC、abc、Abc都是同一个名字。可以为同一单元格或单元格区域定义多个不同的名字。名字一旦定义就可以在同一工作薄的不同工作表之间共用2、名字的意义在Excel中，同一个工作簿中的名字是共享的，在一个工作表中定义的名字，可以被另一个工作表的公式引用，这使公式具有更强大的功能，它能够利用单元格的名字在不同的工作表中查找到正确的单元格或单元格区域。名字给单元格的引用带来了许多方便，因为它使人们不必记住单元格在工作表中的引用位置，用名字就能找到它。3、名字的定义在Excel中，可以为一个独立的单元格、连续的单元格区域或许多不连续的单元格构成的单元格组合定义一个名字（或多个名字）方法二：公式|定义名称方法一：选中单元格，名称框直接输入3、名字的应用1）通过名称在不同的工作表之间传递数据通过名称查找蔬菜单价指定A2:B12最左边为名称3、名字的应用2）名称结合Indirect函数的应用指定A2:B11区域的“最左列”为名称在D2中输入公式：=INDIRECT(B2)向下复制该公式！什么是数据有效性?数据有效性一个包含帮助你在工作表中输入资料提示信息的工具.它有如下功能:--给用户提供一个选择列表--限定输入内容的类型或大小--自定义设置条件格式选项编辑条件格式规则条件格式：对不同的数据，按不同的条件设置其显示格式Indirect(x)其中的X可以是单元格引用或名称。若X为单元格引用，它必须用“”引起来，如：=INDIRECT(“A1”)，将返回A1单元格中的内容若X为单元格名称，它将返回该名称所对应的单元格的内容。销售金额=100000合格70000=销售金额100000警戒数销售金额70000不合格1.认识Excel2.如何设计表格3.函数的使用4.图表的制作5.数据分析1.常用函数使用范例◦条件函数：If()◦统计函数：countif()、countifs()sumif()sumifs()◦查找函数：Vlookup()、Index()、Match()◦随机函数：rand()◦字符函数：len()、right()、left()、mid()2.数组公式的应用=IF(E2=0,D2,)IF(Logical,Value_if_true,Value_if_false)Logical代表逻辑判断表达式Value_if_true表示当判断条件为逻辑“真（TRUE）”时的显示内容，如果忽略返回“TRUE”Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容，如果忽略返回“FALSE”。①库存数=30补货③50=库存数=60下单②30库存数50准备④库存数60充足COUNTIF(range,criteria)用来搜索指定条件的单元格个数RANGE=指定作为搜索对象的单元格区域.即找是否有与条件相配的单元格区域CRITERIA=指定搜索RANGE单元格的条件,即说明条件Excel2007还提供了一个多条件统计函数COUNTIFS，它可以一次对多个不同区域进行不同条件的计数，其用法如下：COUNTIFS(range1，criteria1，range2，criteria2，……，range127，criteria127)【例题】有学生成绩表如下图所示。统计其中总分220分以上的人数；统计总分200分以上，物理80分以下，地理70分以下，化学65分以上的人数；统计姓王的同学人数。SUMIF（Range‚Criteria‚Sum_Range）Sum_range为需要求和的单元格、区域或引用Range为用于条件判断的单元格区域Criteria是由数字、逻辑表达式等组成的判定条件条件求和函数SUMIFS用法SUMIFS(sum_range,range1,criteria1,range2,criteria2…)其中range1,range2,…是计算关联条件的1至127个区域；Criteria1,criteria2,…是数字、表达式、单元格引用或文本形式的1至127个条件，用于定义要对哪些单元格求和。这些区域与条件是对应的，即Criteria1是用于range1区域的条件，criteria2是用于range2的条件，以此类推。Sum_range是求和区域。◦功能SUMIFS函数对某一区域内满足多重条件的单元格求和。【例题】某家电商场的销售记录如下图所示的A1:G13所示。现要统计出统计每位职工销售各种产品的总数量，并将它统计在I8:L13区域中。（1）在J10单元格中输入统计李本成销售的彩电总数量的计算公式：=SUMIFS($E$3:$E$13,$D$3:$D$13,$I10,$A$3:$A$13,J$9)(2)将此公式向下角复制到L13，求和范围条件1条件2功能：Vlookup按列查找的方式从指定数据表区域的最左列查找特定数据，它能够返回查找区域中与找到单元格位于相同行不同列的单元格内容。语法：VLOOKUP(x‚table‚n‚f)x是要查找的值table是一个单元格区域n是table区域中要返回的数据所在列的序号。n=1时，返回table第1列中的数值；n=2时，返回table第2列中的数值；以此类推。f是一个逻辑值，表示查找的方式。当其为true（或1）时，表示模糊查找；当它为false（或0）时，表示精确查找。（1）在I31单元格中输入如下的查询公式：=LOOKUP(H31,$A$31:$B$38,2,TRUE)(2)将此公式向下角复制到I39，Match格式◦Match(x，r，f)◦其中x是要查找的数值，r可以是一个数组常量，或某列（或行）连续的单元格区域，其中可能包含有要查找的x。f用于指定match的查找方式，它可以是-1，0或1。功能◦Match(x，r，f)表示的意思是：在数组或连续的单元格区域r中查找x，并返回x在r中的位置编号。◦当f为0是，match进行精确查找，当f为1（或-1）时，match进行模糊查找。Match查找的方式取值函数功能-1r必须按降序排列，查找大于或等于x的最小数值0r不必排序，查找等于x的第一个数值1r必须按升序排列，查找小于或等于x的最大数值Index(Area，r，c，n)其中，Area是1个或多个单元格区域；r是某行的行序号，c是某列的列序号，该函数返回指定的行与列交叉处的单元格引用。如果r等于0，则返回整行单元格引用，如果c等于0，则返回整列单元格引用。当Area包括多个单元格区域时，n=1就表示结果来自于Area中的第1个区域，n=2表示结果来源于第2个单元格区域……。如果省略n表示结果来源于第1个单元格区域。功能Index（Area，r，c，n）的功能是返回Area中第n个单元格区域中的r行，c列交叉处的单元格引用。已有“员工参保资料”表和“按人月汇总”表制作如下表格，选择姓名、月份后查阅该职工工号和当月社保缴纳情况（1）姓名、月份用“数据有效性”进行设定，姓名数据来源与“员工参保资料”表，月份可直接在“数据有效性”对话框中输入。（2）“工号”查询公式：=VLOOKUP($C$4,按人月汇总!$B:$D,2,0)（3）查询“单位缴费_养老保险”的公式：=INDEX(按人月汇总!$E$5:$P$31,MATCH(按人分月调阅表!$C$4,按人月汇总!$B$5:$B$31,),MATCH(按人分月调阅表!$E$4,按人月汇总!$E$1:$P$1,))功能：返回大于等于0及小于1的均匀分布随机实数另外，Excel2007增加函数randbetween(bottom,top)，可生成两个数之间的随机数。怎样快速将七年级同学随机安排考场考试？常用字符函数◦LEN(text)计算text文本的长度◦RIGHT(text,n)该函数从text文本的右边取出n个字符。◦LEFT(text,n)该函数从text文本的左边取出n个字符◦MID(text,n,m)该函数从text文本的第n个字符起，取出m个字符。=--TEXT(MID(B2,7,6+(LEN(B2)=18)*2),#-00-00)概述数组公式：能够同时对一组或两组以上的数据进行计算，计算的结果可能是一个，也可能是多个。普通公式只执行一个简单计算，并且返回一个运算结果。数组公式的建立方法（1）选中需要保存数组公式结果的单元格或单元格区域。（2）输入公式的内容。（3）按Ctrl+Shift+Enter键。问题1：在D2:D4求出商品的销售金额。问题1：在D2:D4求出商品的销售金额。一般解决方法：三个普通公式问题1：在D2:D4求出商品的销售金额。数组公式解决：数组公式的优点：（1）批量计算，节省计算时间；（2）不能修改其中任一部分，保持公式集合的完整性。问题2：在F1求出商品的销售总金额普通公式解决办法：（1）插入辅助列，先求出各商品的销售额，然后再求总和。（2）直接在F1输入公式“=SUM(B2*C2,B3*C3,B4*C4)”，这样看上去不错，可是，如果有100行数据，一千行号数据呢？先不考虑单元格能容纳多少字符的问题，就光输入公式，累也得把你累趴下，显然是行不通的。数组公式：选中F1单元格，输入公式“=SUM(B2:B4*C2:C4)”，三键确认输入即可。数组公式解决：选中F1单元格，输入公式“=SUM(B2:B4*C2:C4)”，三键确认输入即可。（1）三键输入数组公式。（2）数组公式同时进行多个计算，可返回一个或多个结果。（3）多单元格数组公式需选中多个单元格进行输入，多单元格数组公式具有保护公式的作用。（4）数组公式可以完成复杂的中间运算得到最终想要的运算结果。1、行列数相同数组的运算2、数组与单一的数据的运算3、单列数组与单行数组的计算4、行数（或列数）相同的单行（或单列）数组与多行多