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