Excel 优化使用.ppt

1、1,Excel应用,-凡是重复性的动作，都可以简化。,Date: 2012 02 24,2,内容简介,收集数据的概念page: 35 EXCEL快捷键page: 6-11 数据分析page: 12-32 公式与函数page: 33,3,收集数据的概念,问题到底是什么？每解决一下问题之前，先想一下：是不是这个问题？ 收集数据之前要考虑之后怎么分析，用什么统计方法。以便适应统计要求收集足够的数据。避免事后发现做的试验、收集的数据无法用来统计。 连续性数据与离散性数据相结合地描述问题。 多角度对问题描述，立体化认识问题。可以从以下方面思考问题的描述： 时段 大小 严重性 位置 密度 种类 ,4,收集

2、数据的概念,举例： 目标：减少绿油下杂物，确定收集数据计划,5,收集数据的概念,草案 绿油房的分析 将绿油洁净房二楼分割，每个入口命名为区域A1，A2； 将非门口区域分十个区域，命名为B1，B2； 每天隔六个小时，将时段分为C1，C2，对上面划分的区域进行取样 取样步骤：粘尘辘在地面或者台面滚一下，然后在粘尘纸上滚一下 样本数据：1、一张粘尘纸上总体垃圾数量；2、不同大小垃圾个数；3、垃圾类型（纸，灰尘) 板的分析 相应地记录不同生产段出来的板垃圾情况（大小，类型，位置）， 针对两类客户做不同的生产保护，对比垃圾情况 以上草案执行三周之后，看数据结果，再优化方案,6,Excel快捷方式,数据输

3、入快捷键 ESC:取消单元格输入。 Alt+Enter:在单元格中换行。 Shift+Enter:完成单元格输入并向上选取上一个单元格。 Tab:完成单元格输入并向右选取下一个单元格。 Shift+Tab:完成单元格输入并向左选取上一个单元格。 Home:移到行首。End:移到行末。 F4或Ctrl+Y:重复上一次操作。 Ctrl+Shift+F3:由行列标志创建名称。 Ctrl+D:向下填充。,7,Excel快捷方式,数据输入快捷键 Ctrl+R:向右填充。 Ctrl+F3:定义名称。 Ctrl+K:插入超链接。 Ctrl+;（分号）:输入日期。 Ctrl+Shift+:（冒号）:输入时间。

4、 Alt+向下键:显示清单的当前列中的数值下拉列表。 Ctrl+Z:撤消上一次操作。 F2:编辑活动单元格，并将插入点放置到单元格内容末尾。 Alt+Enter:在单元格中换行。 Ctrl+Delete:删除插入点到行末的文本。 F7:显示“拼写检查”对话框。 Shift+F2:编辑单元格批注。 Ctrl+Shift+Z:显示自动更正智能标记时，撤消或恢复上一次的自动更正,8,Excel快捷方式,单元格编辑快捷键 ALT+TAB:切换到下一个程序 Ctrl+C : 复制选定的单元格。 Ctrl+C，紧接着再按一次 Ctrl+C : 显示 Microsoft Office 剪贴板（多项复制与粘贴

5、）。 Ctrl+X : 剪切选定的单元格。 Ctrl+V : 粘贴复制的单元格。 Ctrl+Shift+加号 : 插入空白单元格。 Ctrl+Shift+箭头键: 向上、下、左或右选取连续单元格。,9,Excel快捷方式,窗口快捷键 ALT+TAB:切换到下一个程序 ALT+SHIFT+TAB:切换到前一个程序 CTRL+ESC:显示windows“开始”菜单 CTRL+W:关闭活动工作簿窗口 CTRL+F5:恢复活动工作簿窗口 CTRL+F6:切换到下一个工作簿窗口 CTRL+SHIFT+F6:切换到前一个工作簿窗口 CTRL+F7:执行”移动”命令(菜单栏中的工作簿图标菜单) CTRL+F

6、8:运行”大小”命令(菜单栏中的工作簿图标菜单) CTRL+F9:将工作簿窗口最小行为图标 CTRL+F10:最大化或恢复工作簿窗口 F5:在”打开”或”另存为”对话框(“文件”中)中更新可见的文件,10,Excel快捷方式,工具栏快捷键 F10或ALT:激活菜单栏 CTRL+TAB或CTRL+SHIFT+TAB:选定前一个或下一个工具栏 TAB或SHIFT+TAB:选定工具栏中的下一个或前一个按钮或菜单,菜单快捷键 SHIFT+F10:显示快捷菜单 F10或ALT:激活菜单栏 ALT+SPACEBAR:显示程序图标菜单(在程序标题栏中) 下箭头键或上箭头键:在菜单或子菜单中选定下一个或前一个

7、命令 左箭头键或右箭头键:选定左面或右面的菜单或者当子菜单可见时,在主菜单和子菜单之间进行切换 HOME或END:选定菜单或子菜单中的第一个或最后一个命令 ALT:同时关闭可见的菜单和子菜单CTRL+TAB或CTRL+SHIFT+TAB:选定前一个或下一个工具栏 TAB或SHIFT+TAB:选定工具栏中的下一个或前一个按钮或菜单,11,Excel快捷方式,同时在多页中输入内容 选定多页, CTRL+SHIFT+PAGEDOWN 在相同单元格中输入相同内容,12,数据分析,数据表建立注意事项 排序与高级筛选 数据透视表,13,建立数据表的注意事项,字段名称最好不要重复 字段行不能空白项. 否则出

8、现错误如下 数据表内不能有合并项, 或者文字格式输入的数据,避免计算错误.,14,文本改为数值,选择数据单元格, 复制/选择性粘贴/加 如何发现哪些单元格是文本输入 选择所有数据单元, 点”增加小数点”的按钮 单元格的小数点如果没有增加, 就是输入时为文本,15,如何快速填充,在单元格中快速填充相同内容 CTRL+F ,输入要找到的内容, 点”替换”,输入新的内容, 然后回车 在空白单元格中快速填充不同内容 筛选空白, 输入公式”=上面单元格”, 复制公式到所有单元格,16,数据列表按行排序,选择需要排序的内容 数据/排序/选项/方向: 按行排序,17,高级筛选,“关系与”条件的高级筛选 复制

9、需要筛选的字段 在相关字段下输入需要的条件, 条件输入相同行列 数据/筛选/高级筛选: 选择条件区域+将筛选结果复制到其它区域,提醒：筛选结果只能复制在原数据的同一个工作表中,18,高级筛选,“关系或”条件的高级筛选 复制需要筛选的字段 在相关字段下输入需要的条件, 不同的条件分别输入不同行列 数据/筛选/高级筛选: 选择条件区域+将筛选结果复制到其它区域,19,高级筛选,利用高级筛选选择不重复的记录 数据/筛选/高级筛选:将筛选结果复制到其它区域+选择不重复的记录,20,高级筛选,使用计算条件的高级筛选 空一行，在下一行输入与数据的头行相应的条件公式。 选择：数据/筛选/高级筛选: 选择条件

10、区域+将筛选结果复制到其它区域,21,高级筛选,同时使用”关系与”和“关系或”条件的高级筛选 例子：,22,命名动态名称,使用记录单为数据列表命名动态名称,注: 名称 为了更加直观地标识单元格或单元格区域，我们可以给它们赋予一个名称，从而在公式或函数中直接引用。 需要特别说明的是，创建好的名称可以被所有工作表引用，而且引用时不需要在名称前面添加工作表名(这就是使用 名称的主要优点)，,23,数据透视表,使用数据透视表，报表用户可完成： 生成许多不同的视图和报表 把字段移动到报表中的不同位置上 确定显示哪一个字段 用各种方法对数值型字段进行汇总 用筛选来控制显示字段中的哪些值 挖掘展示数值型数据

11、的底层数据集 从单个数据透视表创建多个报表,练习:,24,命名动态名称,定义名称创建动态数据透视表, 这样在纪录单上输入数据后直接更新数据透视表, 不再需要更改”数据源”. 这样比引用整个页好, 整个页会有”空白”字段.,25,分页显示数据透视表,首先做一个有”页字段”的数据透视表 然后复制此表, 选择每页显示的不同数据,26,按日期或时间项组合,组合功能是针对 在同一字段下, 数据内容是重复的几项(如:同年不同月) 多个字段进行计算 数据透视表的工具栏的下拉菜单中直接选择”组及显示明细数据/组合”.,27,按数字项组合,同年,由每月显示按为每二月或者三月显示数据 同年按多个季度组合,28,按

12、指定项组合,在数据透视表中选择需要组全的单元格 按右键点取”组及显示明细数据/组合”.,29,创建计算字段,数据透视表的工具栏的下拉菜单中直接选择”公式/计算字段” 在活动窗口中将”字段1” 改为”销售人员提成”, 在公式框中输入”销售人员*0.015”,确定,30,添加计算项,添加计算项之前要求取消组合 数据透视表的工具栏的下拉菜单中直接选择”公式/计算项” 在活动窗口中将”公式1” 改为”国际业务-国内市场”, 在公式框中输入”国际业务-国内市场”,确定,31,创建复合范围的EXCEL数据透视表,数据/数据透视表和数据透视图,点取”多重合并计算数据区域”,32,公式及函数,1.什么是函数

13、Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式 2.什么是公式 函数与公式既有区别又互相联系。如果说前者是Excel 预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的公式,33,多表区域引用,加总多个表格的A1单元格 在单元格输入=SUM(, 按CTRL+SHIFT+PAGEDOWN, 选择A1, 输入), 回车.,34,利用混合引用制作九九乘法表,相对引用, “引用区域”随着复制后变化. 绝对引用, 利用$锁定行号,列号, 以便复制公式函数后, “引用的单元格或者区域”仍然正确. 如=$A1, 在复制后, =$A2, =$A3, 仍然是A列, 但是行

14、号会变化,35,名称,给一个单元格或区域命名的方法 选中要命名的单元格或单元格区域，鼠标单击编辑栏顶端的“名称框”，在其中输入名称后回车。 选中要命名的单元格或单元格区域，单击“插入名称定义”菜单命令，在打开的“定义名称”对话框中输入名称后确定即可。 删除已经命名的区域， 可以按相同方法打开“定义名称”对话框，选中你要删除的名称删除即可。,36,创建EXCEL动态名称,例子,37,使用名称,名称代替引用 图表中使用名称 相对引用和混合引用名称,38,利用文本比较标识首次出现的记录,IF 用途：执行逻辑判断，它可以根据逻辑表达式的真假，返回不同的结果，从而执行数值或公式的条件检测任务。 语法：I

15、F(logical_test，value_if_true，value_if_false)。,39,更改数据字母大小写,文本函数 ASC：将字符串中的全角(双字节)英文字母更改为半角(单字节)字符。 CHAR：返回对应于数字代码的字符，该函数可将其他类型的电脑文件中的代码转换为字符 LOWER：将一个文字串中的所有大写字母转换为小写字 PROPER：将文字串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写 UPPER：将文本转换成大写形式 WIDECHAR：将单字节字符转换为双字节字符,40,使用查找函数拆分空格分隔的数据,FIND：用于查找其他文本串(within_tex

16、t)内的文本串(find_text)，并从within_text 的首字符开始返回find_text 的起始位置编号。此函数适用于双字节字符，它区分大小写但不允许使用通配符。 语法：FIND(find_text，within_text，start_num) LEFT：根据指定的字符数返回文本串中的第一个或前几个字符。此函数用于双字节字符。 语法：LEFT(text，num_chars)或LEFTB(text，num_bytes)。 LEN：LEN 返回文本串的字符数。（中文一个字算1） RIGHT：根据所指定的字符数返回文本串中最后一个或多个字符。 语法：RIGHT(text，num_char

17、s)，RIGHTB(text，num_bytes)。,41,利用SEARCHB 函数分离全半角字符,SEARCHB：与search用法相同，但search 以字符数为单位，searchb 以字节数为单位。 MID：返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。MIDB 返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。MIDB 函数可以用于双字节字符。 语法：MID(text，start_num，num_chars)或MIDB(text，start_num，num_bytes)。 LENB：返回文本串中所有字符的字节数（中文一个字算2）,例子：,练习：,42,日期函

18、数,日期函数 DATE：返回代表特定日期的序列号。 语法：DATE(year，month，day) DAY：返回用序列号(整数1 到31)表示的某日期的天数，用整数 1 到 31 表示。 语法：DAY(serial_number) YEAR：返回某日期的年份。其结果为1900 到9999 之间的一个整数。 语法：YEAR(serial_number) MONTH：返回以序列号表示的日期中的月份，它是介于 1(一月)和12(十二月)之间的整数。 语法：MONTH(serial_number) WEEKDAY：用途：返回某日期的星期数。在默认情况下，它的值为1(星期天)到7(星期六)之间的一个整数

19、。 语法：WEEKDAY(serial_number，return_type),43,日期函数,例子 1：利用日期函数计算公民年龄 2：利用运算符计算日期相差天数 3：利用日期函数计算员工退休日期 4：返回目标日期是全年中的第几天 5：计算指定日期的星期值,44,查找与引用,VLOOKUP：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用函数VLOOKUP代替函数HLOOKUP。 语法：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup) 例子1，2：查询所有

20、信息,45,查找与引用,COUNTIF：统计某一区域中符合条件的单元格数目。 语法：COUNTIF(range，criteria) HLOOKUP：在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。 语法：HLOOKUP(lookup_value，table_array，row_index_num，range_lookup) LOOKUP：返回向量(单行区域或单列区域)或数组中的数值。该函数有两种语法形式：向量和数组，其向量形式是在单行区域或单列区域(向量)中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值； 语法1(向量形式)：LOOKUP(look

21、up_value，lookup_vector，result_vector),46,查找与引用,ROW：返回给定引用的行号。 语法：ROW(reference)。 Reference为需要得到其行号的单元格或单元格区域。 例1：公式“=ROW(A6)”返回6，如果在C5单元格中输入公式“=ROW()”，其计算结果为5。 例2：生成永恒的序号 COLUMN：返回给定引用的列标。,47,查找与引用,INDEX：返回表格或区域中的数值或对数值的引用。 语法：INDEX(reference，row_num，column_num，area_num)返回引用中指定单元格或单元格区域的引用。 需要注意的是：他的行号列号，是以reference的第一个单元格为“1”值为坐标。所以reference选择范围为数值，不包含字段名。 INDEX与ROW，COLUMN结合使用例子,48,组合查询资料,Match: 返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH 函数。 语法：MATCH(lookup_value，lookup_array，match_type)。 向左查找 INDEX，MATCH 两条件交叉查询 INDEX，MATCH与条件格式,49,查找与