excel实例与应用

《excel实例与应用》由会员分享，可在线阅读，更多相关《excel实例与应用（44页珍藏版）》请在金锄头文库上搜索。

1、ExcelExcel实例与应用实例与应用实例与应用实例与应用ExcelExcel实例与应用实例与应用实例与应用实例与应用 （适用于已经掌握基本操作者）（适用于已经掌握基本操作者）李津 （李津 （）信息参考部馆员）信息参考部馆员 环境学院、土水学院学科馆员环境学院、土水学院学科馆员示例文档及课件下载地址示例文档及课件下载地址：示例文档及课件下载地址示例文档及课件下载地址：用户名用户名 密码密码：用户名用户名/密码密码：exceltest2012讲座提纲讲座提纲 公式/函数 公式/函数 图表图表 数据透视表/数据透视图 条件格式 数据有效性公式公式/ /函数函数 公式是函数的基础，它是单元格中的一

2、系列值公式公式/ /函数函数公式是函数的基础，它是单元格中的一系列值 、单元格引用、名称或运算符的组合，可以生 成新值。成新值。公式输入是以“开始的 公式输入是以“=”开始的 简单的公式有加、减、乘、除等计算 3*6 2A2+B16&=3*6-2 =A2+B16 =you&me 复杂一些的公式可能包含函数 =AVERAGE(A9: D17)*E1+50=AVERAGE(A9: D17)*E1+50 函数是Excel附带的预定义或内置的公式 表示方式：函数名(参数1，参数2,.) 如:=average(f2:f8,g2:g8) 参数：数字、文本、逻辑值、表达式、单元格引用、区域 等 没有参数的函

3、数，括号不能省略 没有参数的函数，括号不能省略 如: PI( )，RAND( ), NOW( ),TODAY()相对引用/绝对引用 A1相对引用$A1绝对引用列 A$1绝对引用行$A$1绝对引用单元格A$1绝对引用行$A$1绝对引用单元格 F4是在四种引用间相互转换的快捷键2007版函数函数的函数的12大类共计大类共计346个个 统计函数82版函数 统计函数82 数学和三角函数60 财务函数53 财务函数53 工程函数39 文本函数27 文本函数27 日期和时间函数20 查找和引用函数18 查找和引用函数18 信息函数17 数据库函数12 数据库函数12 多维数据集函数7 逻辑函数6 逻辑函数

4、6 加载宏和自动化函数 5常见错误形式及说明常见错误形式及说明常用公式 求和：SUM (number1,number2, .) 求平均值： AVERAGE(Number1 Number2) 求平均值： AVERAGE(Number1,Number2) 按条件查找： VLOOKUP(lookup value table array col index num ranVLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) 删除字符串中多余空格：删除字符串中多余空格： TRIM(text)/LTRIM(text)/RTRIM(tex

5、t) 得到字符串左部指定个数的字符：LEFT(string, n)得到字符串左部指定个数的字符：(g, ) 得到字符串左部指定个数的字符： RIGHT(string, n) 计算单元格区域或数组中含有数字的单元格个数： COUNT(value1，value2，.) 计算单元格区域或数组中不为空的单元格个数： COUNTA(value1，value2，.)应用举例按条件查找VLOOKUP搜索某个单元格区域 的第一列，然后返回该区域相同行上任搜索某个单元格区域 的第一列，然后返回该区域相同行上任 何单元格中的值。VLOOKUP 中的 V 表示垂直方向。当比较值位于所需查找的VLOOKUP 中的 V

6、 表示垂直方向。当比较值位于所需查找的 数据的左边一列时，可以使用VLOOKUP语法：VLOOKUP(lklbll i d语法：VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)参数参数简单说明简单说明输入数据类型输入数据类型参数参数简单说明简单说明输入数据类型输入数据类型lookup_value要查找的值数值、引用或文本字 符串要查找的区域数据表区域table_array要查找的区域数据表区域col_index_num返回数据在区域的第 几列数正整数range_lookup精确匹配TRUE（或不填） /FALSE应用

7、举例去掉单元格内空格 TRIM函数查找 替换 查找/替换去掉空单元格？去掉空单元格？应用举例TRIM 函数 除了单词之间的单个空格外，清除文本中所有的空 格。格。 语法：Trim (text) () Text需要清除其中空格的文本。应用举例一列转多行多列 不知道使用什么函数怎么办？公式-插入函数-搜索函数（简单说明）图表图表图表图表图表是数据的可视化表示。 图表是数据的可视化表示。 图表在总结一系列数据的相互关系方面特别图表在总结一系列数据的相互关系方面特别 有用。 有助于发现容易忽视的趋势和模式。图表类型图表类型图表类型图表类型图表类型使用方式使用方式图表类型图表类型图表类型使用方式使用方式

8、 柱形图 （直方图）不同时期数据、不同类别数据的变化 数量对比（直方图）数量对比条形图不同类别数据的变化条形图折线图显示数据的变化趋势数据变化饼图描述比例或构成信息成分对比饼图描述比例或构成信息成分对比XY散点图描述数据之间的关系相关性对比雷达图数据相对中心点及其他数据点的变化图表类型图表类型图表类型使用方式使用方式图表类型图表类型图表类型使用方式使用方式面积图折线图的另一种表现形式面积图 曲面图折线图与面积图的另一种形式圆环图与饼图类似，但可显示多个数据圆环图与饼图类似，但可显示多个数据气泡图是XY散点图的扩展，增加了第3个变量股价图专用图形，判断股票或期货市场行情应用举例面积图/折线图应用

9、举例柱线组合图应用举例麦肯锡细分瀑布图数据透视表数据透视表/ /数据透视图数据透视图 数据透视表是一种交互式、交叉制表的El报数据透视表数据透视表/ /数据透视图数据透视图 数据透视表是一种交互式、交叉制表的Excel报 表，使用数据透视表可以深入分析数值数据， 对数值数据进行分类汇总和聚合。对数值数据进行分类汇总和聚合。 数据透视图为数据透视中的数据提供图形表示数据透视图为数据透视中的数据提供图形表示 形式，数据透视图也是交互性的。注：虽然Excel允许同时创建数据透视表和数据透视图，但是没有数注：虽然Excel允许同时创建数据透视表和数据透视图，但是没有数 据透视表就无法创建数据透视图。路

10、径：插入-数据透视表路径：插入-数据透视表适合数据透视表（图）的数据适合数据透视表（图）的数据 数据透视表要求数据是长方形数据库格式的。可 以把数据存放在工作表区域或外边数据库。 一般而言，数据库表格字段包含两种类型： 一般而言，数据库表格字段包含两种类型： 数据数据：包含要汇总的值或数据 类别类别：对数据的描述。类别类别：对数据的描述。如何创建数据透视表（图）数据在工作表区域内如何创建数据透视表（图） 数据在工作表区域内选择任意单元格插入-数据透视表-创建数据透视表/选择任意单元格插入-数据透视表-创建数据透视表/ 图-选择一个表或区域 数据在外部数据源选择任意单元格插入-数据透视表-创建数

11、据透视表-选择任意单元格插入-数据透视表-创建数据透视表- 使用外部数据源-选择链接指定数据透视表布局 拖动指定数据透视表布局 拖动将字段名称拖到“报表筛选、行标签、列标签、数值 ”的任意区域 右键 右键右击字段名称，从快捷右击字段名称，从快捷菜单选择区域注：不含数字的字段将位于左侧， 含有数字的字段将位于右侧，而不含有数字的字段将位于右侧，而不 管您选择字段的顺序如何。数据透视表工具 选项数据透视表工具 选项 设计设计数据透视表工具-选项 选项-数据透视表-选项数据透视表工具-选项 选项-数据透视表-选项数据透视图工具 设计数据透视图工具 设计布局 布局数据透视图工具 格式数据透视图工具 格

12、式分析 分析应用 1、每位销售人员的销量应用 2、销售人员在每个国家的销量、各个国家或地区的总销售额 3、各个国家或地区的总销售额 4、按日期查看 4、按日期查看 5、移动条件格式条件格式 条件格式允许您基于表单上出现的逻辑条件为条件格式条件格式条件格式允许您基于表单上出现的逻辑条件为 控件指定不同的格式选项。如果在用户填写表 单时满足指定条件，那么将显示该条件格式。单时满足指定条件，那么将显示该条件格式。 您可以使用条件格式为控件应用加粗、下划线 、倾斜、删除线、字体颜色和背景色。您还可、倾斜、删除线、字体颜色和背景色。您还可 以指定条件可见性，这是允许您指定 控件何时在表单上可见的一种条件

13、格控件何时在表单上可见的一种条件格 式。路径：开始-样式-条件格式注注：每个单元格不再局限于三种格式条每个单元格不再局限于三种格式条注注：每个单元格不再局限于三种格式条每个单元格不再局限于三种格式条 件，可以指定件，可以指定任意多种任意多种。 突出显示 突出显示 项目选择规则项目选择规则 数据条数据条 色阶色阶 图标集图标集应用标记未完成项目应用分班组标记应用分班组标记数据有效性数据有效性 数据有效性是对单元格或单元格区域输入的数据有效性数据有效性数据从内容到数量上的规则。对于符合条件 的数据，允许输入；不符合条件的数据，则 禁止输入。 数据有效性功能可以在尚未输入数据时，预先 设置，以保证输

14、入数据的正确性； 一般情况下不能检查已输入的数据。应用创建下拉列表步骤：1、在单一行或列区域输入列表项、选定应用下拉列表的区域2、选定应用下拉列表的区域3、数据 数据工具 数据有效性3、数据-数据工具-数据有效性4、有效性条件：序列5、来源：指定包含列表的区域注：如列表较短，可直接在“来源”框录入，每项间注：如列表较短，可直接在“来源”框录入，每项间用逗号隔开；如指定区域，该区域必须在相同工作表，如在不同列表，需为该区域定义名称，并作表，如在不同列表，需为该区域定义名称，并以该名称为“来源”，方式“=区域名”应用禁止输入重复数据如何用数据有效性定义我只能在同一列输入唯一 的值，如果我A列存在一

15、个人名，就不能在A列再的值，如果我A列存在一个人名，就不能在A列再 次输入这个人的名字了。有效性条件：自定义；公式：公式：=COUNTIF(A:A,A1)=1COUNTIF 函数函数:对区域中满足单个指定条件的单对区域中满足单个指定条件的单 元格进行计数元格进行计数。元格进行计数元格进行计数。语法语法：COUNTIF(range, criteria)语法语法：COUNTIF(range, criteria)应用指定有效性标准整数、小数、日期、时间、文本长度整数、小数、日期、时间、文本长度常用快捷键 定位：Ctrl+G 选择连续单元格：CTRL+SHIFT+方向键 完成单元格输入并在选定区域中下移：ENTER 完成单元格输入并在选定区域中下移：ENTER 完成单元格输入并在选定区域中平移：TAB 用当前输入项填充选定的单元格区域：CTRL+ENTER 向下填充：CTRL+D 向右填充：CTRL+R 重复最后一次操作：F4 或 CTRL+Y 重复最后一次操作：F4 或 CTRL+Y 将公式作为数组公式输入：CTRL+SHIFT+ENTER 撤消最后一次操作：CTRL+Z学习资源 微软课程：www microsoft com/china/technet/