excel学习教程.ppt

基础篇：第基础篇：第1 1 到第到第5 5章章应用篇：第应用篇：第6 6到第到第1010章章分析篇：第分析篇：第1111到第到第1616章章Microsoft Excel Microsoft Excel 从入门到精通从入门到精通第六章第六章 创建和使用公式创建和使用公式第一节 公式的基本概念_公式是指对工作表数据进行运算的算式，利用公式可以进行数学运算，可以比较工作表数据并返回布尔值等_公式通常由以下五种元素组成：运算符、单元格引用、文字（数字、字符串等）、工作表函数、括号_公式以等号=开始，等号后面是要进行计算的操作数、运算符、函数等_公式按照运算符特定顺序从左至右进行运算，括号（）可改变运算顺序，先运算括号内，再运算括号外应用篇应用篇第二节 公式中使用的运算符算术运算符+加、-减、*乘、/除、%百分号、乘方比较操作符=、=大于等于、=小于等于、不等于文本串联符&文字串联符，把两个字符串首尾连接引用操作符：冒号-引用两个地址间的所有单元格，如B5：C6，包含B5、B6、C5、C6四个单元格，逗号-引用逗号前后的两个单元格或单元格区域，如A1：B2，B4：C5，包含A1、A2、B1、B2和B4、B5、C4、C5公式中符号运算优先次序:、,-%*、/+、-&=、=、=、即：引用负号百分号乘方乘、除加、减文本连接符比较运算符第三节 输入公式直接在单元格中输入公式单击要输入公式单元格输入等号=输入公式内容，回车确认使用“公式选项板”输入公式单击编辑栏上“编辑公式”按钮 ，直接输入公式，或者再单击所需函数，出现“公式选项板”公式中单元格的输入选重要输入公式单元格，输入等号=用鼠标单击要引用单元格，输入单元格地址输入运算符继续上述操作，若输入完毕，按回车键或单击编辑栏上“确认”按钮 结束，也可按Esc或编辑栏上“取消”按钮 取消输入第四节 编辑公式编辑公式就向编辑其他单元格一样双击单元格可在单元格内部直接编辑公式选择要编辑的单元格，再单击“编辑栏”编辑公式单击“编辑栏”中的“编辑公式”按钮，再使用“公式选项板”编辑公式第五节 单元格和区域引用_一个引用地址代表工作表上的一个或一组单元格_引用地址告诉Excel在哪些单元格中查找公式中要用的数值_通过使用引用地址，可以在一个公式中使用工作表不同部分的数据，也可以在几个公式公式用同一单元格数值；同时还可以引用同一工作簿不同工作表的数据，或者引用其他工作簿的数据相对引用在输入和创建公式的过程中，若非特别指明，通常采用单元格的相对位置相对地址是当公式复制到新位置或用一个公式增加一个新的范围时公式中单元格地址会改变在复制包含相对引得公式时，Excel会自动调整复制公式中的引用，以引用相对于当前公式位置的其他单元格（如：A1、B5、A2：C3等）绝对引用一般情况下，复制单元格地址时使用使用相对地址，但我们如果不希望复制公式时系统自动调整引用，就要使用绝对地址引用绝对地址引用是指在复制公式或把公式填入到新位置时，公式的固定单元格地址不变Excel中通过在行号列号前增加$符号表示绝对引用（如：$A$4、$B$7等）混合地址引用相对地址和绝对地址可以混合使用如：$A1、A$8等三维地址引用所谓三维地址引用是指：在同一工作簿中引用不同工作表中单元格或单元格区域中的数据三维地址引用的一般格式为：工作表名称！单元格地址如：Sheet3！B3+C4即Sheet3工作表中B3单元格的值和当前工作表中C4单元格的值相加注意避免循环引用如果公式引用自己所在的单元格，不论是直接还是间接，都称为循环引用如：A1单元格的公式为=A1+A2+A3当系统计算此类公式时，必须使用前一次的结果来进行下一次的计算，一般来说Excel将在100次计算后或相邻两次计算值变化小于0.001时，停止迭代计算过程（可在菜单“工具/选项”-“重新计算”中修改）第六节第六节 移动和复制公式移动和复制公式_移动和复制单元格的操作同移动复制单元格操作_移动复制公式会产生单元格地址的变化_可使用“剪切”“复制”“粘贴”，也可使用鼠标拖拽，还可使用填充柄移动复制公式对相对引用的影响Excel会自动调整位于新粘贴单元格区域内的所有的相对地址引用和混合引用的相关部分移动复制单元格对绝对引用的影响移动复制公式对绝对引用和混合引用中的相关部分没有影响，引用地址保持不变第七节第七节 在工作表中使用名称在工作表中使用名称可用行列标题引用该行或该列数据可用“列标题 行标题”引用该列该行单元格数据可使用已定义的名称引用相应的数据第八节 公式与常见错误第七章第七章 工作表函数的使用工作表函数的使用_函数是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。如：SUM函数用来求和，AVERAGE函数用来求平均第一节 了解工作表函数_在Excel中使用函数，必须先将它们输入到公式中_若函数的参数也是函数，称为函数的嵌套_一个公式最多可以嵌套7层_函数的参数的开始和结束用（）括起，（）必须成对出现，（）前后不能有空格函数的结构以函数名称开始，然后是左圆括号（、以逗号分开的参数和右圆括号）应用篇应用篇函数结构如：例如：求平均=AVERAGE(A1：A5)例如：求最大值=MAX(A1：B10)函数语法有的函数带有必选参数和可选参数如：CELL(info_typeinfo_type，reference)，加粗的是必选参数，常规是可选参数必选参数一定要有，可选参数可省略正确使用：CELL(“format”,B12)，CELL(“format”)，但CELL()是错误的，因为没有必选参数有的函数参数后跟省略号如：SUM(number1,number2number1,number2)等号，如果函数应用于公式中函数名称函数参数，用逗号分隔参数用（）括起带省略号表示用户可使用该数据类型的多个参数正确的使用:SUM(26,31)、SUM(A1:B4)，A1,B2)等有的函数不带参数如：NOW()虽然这些函数没有参数，但使用时一定要带（）常用参数类型number：数字 text：文本reference：引用 value：任意单值结果logical：逻辑值函数中的逗号函数中使用逗号分隔不同参数，不可多键入逗号如果用逗号预留了一个参数位置而未输入该参数，系统将用默认值代替该参数，但必选参数除外例如：求平均AVERAGE(1,2,3,4,5),结果为3;若输入为AVERAGE(,1,2,3,4,5),则结果为(0+0+1+2+3+4+5)/7=2.14,因为用逗号预留的参数用缺省值0补上参与了计算第二节第二节 在公式中输入函数在公式中输入函数_两种函数输入方法v手工输入v使用粘贴函数手工输入函数选定单元格在编辑栏单击“编辑公式”按钮单击“函数”下拉列表框，选择函数，如果函数不在下拉列表中，单击列表中“其他函数”项在粘贴函数对话框中选择函数并输入相应参数粘贴函数选定单元格菜单“插入/函数”或单击工具栏上“粘贴函数”按钮，出现对话框选择函数的种类和具体函数后“确定”填入函数所需的参数（利用“范围”按钮 可以方便快捷输入引用区域）第三节第三节 数学和三角函数数学和三角函数SUM函数格式：SUM(number1number1,number2)参数为130个例:=SUM(4,6)值为10例:=SUM(“3”,6,FALSE)值为9,因为文本“3”被自动 转换为数字3,逻辑值FALSE被转换为数字0例:若F10单元格内容为文本“3”,则=SUM(F10,6)的值为6,因为对非数值型的值的引用不能被转换成数值参与计算如果参数为引用,只有其中的数字被计算,引用中的文本、逻辑值、错误值将被忽略如果参数错误或不能被转换成数字的文本，将导致错误SUMIF函数格式：SUMIF(RANGE,criteriacriteria,sum_RANGE)RANGE:用于条件判断的单元格区域criteria:criteria:为确定哪些单元格将被求和的条件sum_RANGE:为需要求和的实际单元格。只有当RANGE中相应单元格满足条件，才对sum_RANGE 中的单元格求和。若省略sum_RANGE,则直接对RANGE中的单元格求和如:=SUM(A1:A5,“1000”,B1:B5)INT函数格式:INT(numbenumber)对number取整如:=INT(12.4)的值是12如:=INT(-12.4)的值是-13RAND函数格式:RAND()返回大于等于0小于1的随机数如:=RAND()*100,生成1个0100的数如:=INT(RAND()*100),生成1个0100的整数ROUND函数格式:ROUND(number,num_digitsnumber,num_digits)根据number_digits指定的位数为number四舍五入如:=ROUND(3.52,1)值是3.5如:=ROUND(-4.276,2)值是-4.28如:=ROUND(46.5,-1)值是50PI函数格式:PI()返回圆周率3.1415926,精确到小数点后15位如:=SIN(PI()/2)值是1SIN函数格式:SIN(number)返回给定弧度的正弦值SQRT函数格式:SQRT(number)返回number的平方根ABS函数格式:ABS(number)返回number的绝对值TRUNC函数格式:TRUNC(numbernumber,num_digits)根据num_digits指定的位数对number截取小数部分num_digits默认值为0如:=TRUNC(-9.3)值为-9LOG函数格式:LOG(numbernumber,base)返回以base为底number的对数,缺省的base位10如:=LOG(16,2)值是4COUNTIF函数格式:COUNTIF(RANGE,criteriacriteria)计算RANGE指定区域内满足criteria条件的单元格数目如:A3:A6内容分别为“apples”,“oranges”,“peaches”,“apples”,则=COUNTIF(A3:A6,“apples”)值为2如:B3:B6内容分别为32,54,75,86,则=COUNTIF(B3:B6,“55”)值为2第四节第四节 文本函数文本函数CHAR函数格式:CHAR(numbernumber)返回一个ASCII码为number(取值1255)的字符如:CHAR(65)值为“A”LEFT函数和LEFTB函数格式:LEFT(texttext,num_chars)格式:LEFTB(texttext,num_bytes)主要应用于中文字符返回text文本串中前chars个字符,缺省的num_chars 为1,若其大于text总长度,返回整个字符串如:=LEFT(“Sale_Price”,4)值是“Sale”如:A1单元格内容为“Soft”,则=LEFT(A1)值是“S”如:=LEFT(“计算机”,2)值是“计算”如:=LEFTB(“计算机”,4)值是“计算”LEN函数和LENB函数格式:LEN(texttext)格式:LENB(texttext),主要应用于中文字符返回text字符串的长度,空格也作为字符参与计算如:=LEN(“”)值是0如:=LEN(“”)值是1如:=LEN(“计算机”)值是3如:=LENB(“计算机”)值是6MID函数和MIDB函数格式:MID(text,start_num,text,start_num,)格式:MIDB(text,start_num,num_bytestext,start_num,num_bytes)返回指定字符串text中从start_num开始长度为 num _chars或num_bytes的子字符串如果start_num),返回“”空文本如果start_num LEN(text),则返回从start_num到字符串末尾的子字符串如:=MID(“Fluid firm”,7,15)值是“firm”如:=MID(“1234”,5,5)值是“”空文本如:=MID(“计算机”,1,2)值是“计算”如:=MIDB(“计算机”,1,2)值是“计”REPLACE函数和REPLACEB函数格式:REPLACE(old_text,start_num,num_chars,new_text)格式:REPLACEB(old_text,start_num,num_bytes,new_text)在old_text中从start_num开始用new_text替换num_char或num_bytes个字符如:REPLACE(“123456”,1,3,“$”)值是“$456”如:=REPLACE(“计算机”,1,2,“*”)值是“*机”如:=REPLACEB(“计算机”,1,2,“*”)值是“*算机”SEARCH函数和SEARCHB函数格式:SEARCH(find_text,find_text,start_num)格式:SEARCHB(find_text,within_textfind_text,within_text,start_num)在within_textwithin_text字符串中从字符串中从处开始查找find_text,find_text,缺省缺省start_num为1,在find_textfind_text中可以使用通配符中可以使用通配符*和和?SEARCH函数和SEARCHB函数查找指定字符串时不区分大小写如:=SEARCH(“e”,“Statements”,6)值是7如:=SEARCHB(“建”,“福建省”)值是3如:=SEARCH(“建”,“福建省”)值是2如:若B10单元格内容“margin”,A10单元格内容“Profit Margin”,则=SEARCH($B$10,$A$10)值是8如:上例=REPLACE($A$10,SEARCH($B$10,$A$10),6,“Amount”)值是“Profit Amount”FIND函数和FINDB函数格式:FIND(find_text,within_textfind_text,within_text,start_num)格式:FINDB(find_text,within_textfind_text,within_text,start_num)在within_textwithin_text字符串中从字符串中从处开始查找find_text,find_text,缺省缺省start_num为1 在find_textfind_text中不可以使用通配符中不可以使用通配符*和和?查找指定字符串时区分大小写如:=FIND(“M”,“Miriam Mc”)值是1如:=FIND(“m”,“Miriam Mc”)值是6如:=FIND(“M”,“Miriam Mc”,3)值是8如:=FIND(“建”,“福建省”)值是3如:=FINDB(“建”,“福建省”)值是2UPPER函数和LOWER函数格式:UPPER(texttext)格式:LOWER(texttext)将字符串转化成大写、小写形式如:=UPPER(“Total”)值是“TOTAL”如:=LOWER(“Total”)值是“total”EXACT函数格式:EXACT(text1,text2text1,text2)比较第一个字符串text1和第二个字符串text2是否完全相同EXACT区别大小写,但是忽略格式的不同如:=EXACT(“James”,“James”)值是TRUE如:=EXACT(“Diane”,“diane”)值是FALSE第五节 逻辑函数IF函数格式:IF(logical_testlogical_test,value_if_truevalue_if_true,value_if_false)logical_testlogical_test是值为逻辑值是值为逻辑值TRUETRUE或或FALSEFALSE的逻辑表达式的逻辑表达式,若条若条件成立件成立,返回返回value_if_truevalue_if_true的值的值,否则返回否则返回value_if_false的值,value_if_false可省略如:A10单元格中存放当前预算值,如果A10值大于100,显示“超出预算”,否则显示“预算内”=IF(A1089,“A”,IF(average79,”B”,IF(average69,”C”,IF(average59,“D”,“FAIL”)AND函数格式:AND(logical1,logical2logical1,logical2,)所有的参数均为真,返回逻辑值“TRUE”,否则返回“FALSE”如:=AND(1+4=1,2+6=8)值是FALSEOR函数格式:OR(logical1,logical2logical1,logical2,)所有的参数只要有一个为真,返回逻辑值“TRUE”,否则返回“FALSE”如:=OR(1+4=1,2+6=8)值是TRUENOT函数格式:NOT(logicallogical)如果logical为真,返回FALSE;否则,返回TRUE如:=NOT(1+3=2)值是TRUE第六节第六节 时间日期函数时间日期函数_Excel将日期存储为一系列连续的序列数,时间被存储成小数,因为时间可以看作是天的一部分。它们被看作数值后可进行计算，如计算两个日期相差得天数等。_默认情况下，1900年1月1日被看作系统的第一天，序列数是1；9999年12月31日是系统最后一天。_如：2001年1月21日的序列数是36912TODAY函数格式:TODAY()返回当前的系统日期如:假设今天是2001年1月21日,则=TODAY()的值是1/21/2000DATE函数格式:DATE(year,month,dayyear,month,day)返回year年,month月,day日的序列号如:=DATE(2001,1,21)值是36912如果year在01899间,系统会自动在此数值上加1900,当作年份进行计算;如果year在19009999间,系统直接将此数值当作年份计算如:=DATE(100,1,2)等于=DATE(1900+100,1,2)值是36527WEEKDAY函数格式:WEEKDAY(serial_numberserial_number,return_type)返回serial_number代表的日期是星期几,return_type 为确定返回值类型的数字,可省略,默认是1如:=WEEKDAY(“2001/1/21”)值是1,表示星期日如:=WEEKDAY(35981.007)值是1,表示星期日TIME函数格式:TIME(hour,minute,second)返回hour小时,minute分钟,second秒对应的小数值hour取值123,minute和second取值059如:=TIME(12,0,0)值是0.5第七节第七节 财务函数财务函数_财务函数可以进行一般的财务计算,例如确定贷款的支付额,投资的未来值或净现值,以及债券或股票的价值_常用的参数:v未来值(fv)-在所有付款发生后的投资或贷款价值v期间数(nper)-投资的总支付期间数v付款(pmt)-对意向投资或贷款的定期支付数额v现值(pv)-投资初的投资或贷款价值v利率(rate)-投资或贷款的利率或贴现率v类型(type)-付款期间内进行支付的间隔SLN函数格式:SLN(cost,salvage,lifecost,salvage,life)返回一项资产每期的直线折旧费如:一辆价值$30000的卡车,折旧期限10年,资产残值$10000,则每年折旧额为=SLN(30000,10000,10)为$2000RATE函数格式:RATE(nper,pmt,pvnper,pmt,pv,type,guess)返回年金的各期利率如:金额为$8000的4年期贷款,月支付额为$200,则该笔贷款的利率为=RATE(48,-200,8000)为0.77%第八节第八节 统计函数统计函数AVERAGE函数格式:AVERAGE(number1number1,number2,)返回若干个参数的算术平均值如:=AVERAGE(1,2,3)值是2如:A1A4单元格值为2,3,4,5,则=AVERAGE(A1:A4,6)值是4COUNT函数格式:COUNT(value1value1,value2,)返回参数个数,计数时,将把数字,空值,逻辑值,日期或以文字代表的数计算在内,错误值和无法转换成数字的文字被忽略如:B1B4单元格内容是12/8/99,空,10,#DIV/0!,则=COUNT(B1:B4)值是1MAX函数和MIN函数格式:MAX(number1number1,number2,)格式:MIN(number1number1,number2,)返回参数中最大/小值如果 A1:A5 中依次包含数值 10，7，3，27 和 2，那么=MIN(A1:A5)等于 2,=MIN(A1:A5,0)等于 0,=MAX(A1:A5)等于27第九节第九节 函数嵌套函数嵌套_在某些情况下,我们可能将某函数作为另一函数的参数使用,以增加公式使用的灵活性,并且加快数据处理的速度_当前套函数作为参数使用时,它返回的类型必须与参数使用的数值类型相同_公式中最多可以包含7级嵌套_如:=IF(AVERAGE(B3:B7)SUM(C2:C4),SUM(C2:C4),0)第八章第八章 图表制作图表制作_图表具有较好的视觉效果，可方便用户察看数据的差异、图案和预测趋势第一节 初步认识图表用图表表现数据可以在工作表上创建图表，或将图表作为工作表的嵌入对象使用图表的基本元素图表是与生成它的工作表数据相连接的与图表有关的工作表数据变化时，图表也会自动更新将鼠标停留在图表项上，系统会自动显示相关信息应用篇应用篇选定图表项的方法图表设置中，一般都是先选中某图表项，再单击右键选择快捷菜单或在“格式”菜单中执行命令1）单击所需图表项2）单击图表，然后在“图表”工具栏中单击“图表对象”下拉箭头（“视图/工具栏/图表”显示图表工具栏）使用鼠标移动、调整图表项大小单击选中要移动或调整大小的图表项如要移动，鼠标指向后拖拽鼠标到目的位置如要调整大小，鼠标指向尺寸柄变成双向箭头时拖拽鼠标第二节第二节 创建图表创建图表利用图表向导创建图表选定待显示于图表中的数据所在单元格（可以连续也可不连续，也可以不选）单击工具栏上“图表向导”按钮或菜单“插入/图表”根据向导提示，依次选择或输入图表类型、图表数据源（可在此时选择数据区域或数据系列）、图表选项和图表位置第三节 修改图表类型常见图表类型 柱形图：显示一段时间内的数据变化或说明项 目之间的比较结果 条形图：显示各个项目之间的比较情况 折线图：显示相同间隔内数据的预测趋势 饼图：显示构成数据系列的项目相对于项目总 和的比例大小 散点图 面积图 圆环图 雷达图 曲面图 气泡图 股价图 圆柱图 圆锥图 棱锥图修改图表类型单击选中图表菜单“图表/图表类型”或鼠标右键单击图表区快捷菜单中选择“图表类型”第四节第四节 修改图表数据修改图表数据更改图表数据区域单击图表菜单“图表/数据源”或在图表区域单击鼠标右键，快捷菜单中选择“数据源”选择“数据区域”卡片后，选择数据系列产生在行/列的方式，也可重新选择图表产生数据区域选择“系列”卡片后，可以增加或删除图表产生的系列向图表中添加数据通过复制和粘贴向图表中添加数据选择要添加的数据的单元格菜单“编辑/复制”或工具栏上“复制”按钮单击图表如果要让Excel自动将数据粘贴到图表，菜单“编辑/粘贴”或工具栏上“粘贴”按钮；如果要指定数据在图表中的绘制方式，菜单“编辑/选择性粘贴”使用颜色编码区域向嵌入式图表添加、修改数据选定图表区域拖动工作表中紫色、蓝色、绿色的选择柄进行数据的添加或修改通过拖动向嵌入式图表中添加数据选定包含添加数据的单元格（工作表中必须相连）鼠标指向选定区域的边界将选定区域拖动到希望更新的嵌入土表中第五节第五节 更改图表选项更改图表选项_包括：标题、坐标轴、网格线、图例、数据标志修改图表选项单击图表，菜单“图表/图表选项”或在图表区域单击鼠标右键，快捷菜单中选择“图表选项”依次选择“标题”卡片、“坐标轴”卡片、“网格线”和“数据标志”卡片，进行相应的设置第六节第六节 改变图表位置改变图表位置_图表可以嵌入工作表，也可单独放在一个工作表修改图表位置单击图表，菜单“图表/位置”或在图表区域单击鼠标右键，快捷菜单中选择“位置”选择图表位置第七节第七节 设置图表格式设置图表格式_数值轴、分类轴、数值轴主要网格线_绘图区、图表区_数值轴标题、分类轴标题、图表标题_图例可设置的格式:线条、边框、颜色、纹理、过渡填充等数字格式、轴刻度、刻度线等字形、字体、字号、旋转、颜色等设置方法单击选中对象，菜单“格式”单击选中对象，单击鼠标右键在快捷菜单中选择相应格式第八节第八节 更改三维图表更改三维图表修改三维图表的深度和宽度三维图表中单击要修改数据系列菜单“格式/数据系列”，再选择“选项”卡片修改三维图表的三维视图格式单击选定三维图表菜单“图表/设置三维视图格式”弹出的对话框中选择上下仰角、左右转角、透视系数和三维图表的高度第一节 在工作表中插入图片_Excel中涉及到的图片有：剪贴画、来自文件的图片、艺术字、自选图形、组织结构图在工作表中插入图片第九章第九章 图形和数据地图图形和数据地图应用篇应用篇菜单“插入/图片”剪贴画：从剪辑库中挑选合适剪贴画来自文件的图片：选择要插入文件所在的磁盘、文件夹和文件名，插入图片自选图形：当鼠标变成+时拖动鼠标画出图形（也可以使用绘图工具栏）艺术字：先选择艺术字样式，然后输入文字组织结构图：分别根据题是输入经理、经理助理、左同事、右同事等文字效果 菜单“插入/图片/艺术字”“绘图”工具栏上选择横排/竖排文本框绘制自选图形后单击鼠标右键，快捷菜单中选择“添加文本”第二节 建立数据地图创建地图工作表中输入相关数据，并选定包含地理数据的单元格区域（如果只显示地图不显示数据，可不选定单元格区域）单击工具栏上“数据地图”按钮 或菜单“插入/地图”鼠标变成+，拖动鼠标选择合适地图大小