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