EXCEL培训.ppt

1、EXCEL 培训,电脑系 2006.6,培训层次,第一阶(初级):基础概念及操作.(介绍基本概念,图表,窗格冻结和工作表保护等) 第二阶(中级):技巧提高与函数应用.(介绍部分操作技巧以及函数在工作表中的运用) 第三阶(高级):工作表的高级应用.(包括数组公式,数据透视表和宏等高级应用),第一阶:基础概念及操作,1:相关概念 工作簿、工作表 工作簿EXCEL文件 工作表EXCEL文件中的子表（Sheet） 绝对引用、相对应用 相对引用公式中的相对单元格引用（例如 A1）是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。 绝对引用单元格中的绝对单元格引用

2、（例如 $A$1）总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。,2.图表,插入图表 菜单 折线图销售数量趋势图,图表,柱状图公司销售及终端销售对比,图表,饼图销售品种百分比,3冻结窗格,当工作表比较大的时候需要冻结窗格,锁定表头.使表格在滚动时保持行和列标志可见. 如下表首行即被冻结,冻结窗格,1.若要冻结窗格，请执行下列操作之一： 顶部水平窗格 选择待拆分处的下一行。 左侧垂直窗格 选择待拆分处的右边一列。 同时生成顶部和左侧窗格 单击待拆分处右下方的单元格。 2.在“窗口”菜单上，单击“冻结窗格”。 3.当无须冻结时,在“窗口”菜单上，单击“取消冻结窗格”。,

3、4.工作表保护,工作表保护的方式 保护整个工作表，只能查看，不能对工作表的任何内容作修改 部分保护工作表，可限定用户对工作表的操作，如限定用户可输入数据的单元格，限定单元格的数据类型（有效性）等。,工作表保护,工作表保护实例 下表是模拟一个销售清库的报表，该表可以增加删除行，只有黄色区域可以填写信息，其余部分不可以修改。入库时间规定了必须输入日期型数据格式为“YYYY-MM-DD”。,工作表保护,工作表保护实例实现步骤： 1. 设定“入库时间”字段的日期格式：选中“入库时间”数据区域，进入“数据有效性”菜单。如图,工作表保护,工作表保护实例实现步骤： 2. 设置可编辑的工作区域： 将黄色的数据

4、区域选中，进入“工具保护允许用户编辑区域”，点击“新建”，创建保护区域。如下图,工作表保护,工作表保护实例实现步骤： 3. 设置工作表保护：进入“工具保护保护工作表”，可设置保护的密码和允许用户的操作。这里我们勾选“选定锁定单元格”、“选定未锁定单元格”、“插入行”、“删除行”。如图：,第二阶:技巧提高与函数应用,行列转置 选中需要转置的区域，复制。 进入菜单“编辑选择性粘贴”，在右下角将“转置”选中,技巧,单元格绝对引用和相对引用的快速切换 第一次按F4键，行、列单元格均进行绝对引用（如：sum（$A$1:$B$1)） 第二次按F4键，列绝对引用，行相对引用（如：sum（A$1:B$1)）

5、第三次按F4键，行相对引用，列绝对引用（如：sum（$A1:$B1)） 第四次按F4键，行、列单元格均进行相对引用（如：sum（A1:B1)）,技巧,快速调整列宽度 选中需要调整的列，当光标变成如下图所示的形状时双击，EXCEL会根据输入数据的宽度自动调整列宽,鼠标形状,技巧,快速拖动单元格 如下图，当鼠标变成十字形状时，我们既可以向下拖动鼠标将公式复制下去，也可以直接双击鼠标自动完成拖动,技巧,选择性粘贴（编辑选择性粘贴） 当复制粘贴含有公式的单元格时，粘贴的结果往往会发生变化。可以使用选择性粘贴，只粘贴数值。该功能还有许多其他的功能可用，如下图：,技巧,自定义筛选 用户可以自己设定筛选条件

6、，并可设置多个筛选条件。如下图,技巧,分页预览 使用视图分页预览功能，可以方便的设置打印区域。虚线框是页面区域，实线框是数据区域。拖动调整虚线框可以改变页面的打印区域,技巧,超链接（插入超链接） 当一套报表中含有多个子表的时候，可以创建一个目录工作表，为每个子表建立一个标题，并使用超链接链接到各个子表。如下图：,技巧,快速计算 计算一系列单元格的合计、平均值、最大值等，虽然可以通过公式得到，但也可以选中这些单元格，在Excel下方的状态栏上看到。默认是求和，在该区域单击右键可以其他计算方式。如图：,技巧,快速选择工作表 当一个工作簿有多个工作表（sheet）时，如何快速的选择这个工作表？在工作

7、簿的左下角，工作表滚动轴处单击鼠标右键，即可快速选中工作表。如图：,技巧,工作表内快速移动 Ctrl+箭头键 ：移动到当前数据区域的边缘 Ctrl+Shfit+箭头键：移动并选择到当前数据区域的边缘 Home ：移动到行首 Ctrl+End：移动到工作表的最后一个单元格，该单元格位于数据所占用的最右列的最下行中 Ctrl+D ：向下填充 Ctrl+R ：向右填充,技巧,不显示零值 在单元格内如果是零值，则显示空白工具选项视图中将窗口选项的“零值”勾掉 自动保存（Excel2003） 工具选项保存标签 在Excel2000版本中可以在工具加载宏中实现,技巧,筛选不重复的记录 选中需要筛选的列，进

8、入 数据筛选高级筛选，如下图,注意选中,技巧,筛选不重复的记录（实例）,条件格式,根据单元格的不同数值显示不同的格式（如不同的颜色，字体等）。 在菜单栏上 “格式条件格式”,函数,TRIM(文本) 去掉文本两端的空格，如TRIM(A1) MID(文本，起始位置，长度) 截取部分字符，如MID(A1,2,3) IF(判断条件，判断为真的返回值，判断为假的返回值) 如IF(A1=0,”零值”,”非零值”) ISERROR() 判断单元格的结果是否是出错。比如单元格的结果为“#N/A”、“#DIV/0!”等的时候返回为真。,函数,VLOOKUP（查找值,查找表区域，返回列序号，匹配选项） 查找值：需

9、查找的数值 查找表区域：查找的值必须在该表区域的第一列 返回列序号：该表区域的列序号 匹配选项（true/false）：精确匹配使用false，大致匹配使用true（Excel翻译的有误） 查找区域使用绝对引用 VLOOKUP可以在工作表之间，也可以在工作簿之间查找,函数,VLOOKUP举例 根据姓名，将sheet2中的年龄带入sheet1中年龄列,Sheet1,Sheet2,函数,VLOOKUP举例（续） 在sheet1的C2单元格插入VLOOKUP公式，如下图,注意使用绝对引用,第三阶:高级应用,1.数组公式 数组公式可以同时进行多个计算并返回一种或多种结果。数组公式对两组或多组被称为数组

10、参数的数值进行运算。每个数组参数必须有相同数量的行和列 数组公式必须用Ctrl+Shift+Enter生成,数组公式,应用举例一 收货数量乘以单价后，并求和 在单元格A7输入公式 =SUM(C2:C5*D2:D5)，并按Ctrl+Shift+Enter生成公式，EXCEL自动在公式外上大括号 ，表示其为数组公式,数组公式,应用举例二 销售报表中的根据经销商汇总生成地区汇总 公式：J2=SUM($A2:$A459)=$I2)*1*(D2:D459),2.数据透视表,数据透视表是交互式报表，可快速合并和比较大量数据。您可旋转其行和列以看到源数据的不同汇总，而且可显示感兴趣区域的明细数据 如果要分析

11、相关的汇总值，尤其是在要合计较大的列表并对每个数字进行多种比较时，可以使用数据透视表,数据透视表,数据透视表实例 如下图，需要将表一的明细数据生成表二的汇总形式。,表一,表二,数据透视表,数据透视表实例实现步骤 1. 选择需要创建数据透视表的数据区域，进入“数据数据透视表和数据透视图”菜单。如下图：,数据透视表,数据透视表实例实现步骤 2. 点击下一步，进入数据区域选择对话框，如图：,数据透视表,数据透视表实例实现步骤 3. 点击下一步，进入透视表设置对话框，如图： 可选择在新的工作表或在当前工作表的指定区域生 成数据透视表,数据透视表,数据透视表实例实现步骤 4. 点击完成，生成一张空的数据

12、透视表。 5. 拖动字段至相应区域（如图）,数据透视表,数据透视表实例实现步骤 6. 生成数据透视表结果如表一 7. 可将地区拖入到列区域，进行分类汇总，如表二,表一,表二,数据透视表,数据透视表实例实现步骤 8. 也可以将表一“终端销售数”拖入数据区域，形成汇总对比，如图：,表一,3.宏的应用,关于宏 如果经常在 Microsoft Excel 中重复某项任务，那么可以用宏自动执行该任务。宏是一系列命令和函数，存储于 Visual Basic 模块中，并且在需要执行该项任务时可随时运行。,宏,宏的安全性 可在“工具宏安全性”菜单设置宏的安全级别，如下图,宏,录制宏 进入“工具宏录制新宏”，如

13、下图： 确定后，宏开始录制，Excel的操作将被记录下来，屏幕上出现 ，可以停止宏的录制,宏,管理宏 进入“工具宏宏”菜单，如图： 要运行宏，可以单击“执行”，如需对宏排错，可用单步执行,宏,宏实例生成销售看板汇总报表,明细表,汇总表,宏,宏实例生成销售看板汇总报表 生成整理明细数据宏A. 录制新宏“整理明细数据”，如图B. 首先添加明细表的表头：“地区代码”、“经销商代码”、“经销商名称”、“零件号”、“启票数量”C. 在“零件号”列后新增一列“车型”，输入公式=MID(D2,1,13)，拖动公式至表底。D. 停止宏录制,宏,宏实例生成销售看板汇总报表 2. 生成数据透视表宏A. 选中数据区域，进入“数据数据透视表”菜单，注意在第三步选择数据透视表位置时，选择“现有工作表”，如图,宏,宏实例生成销售看板汇总报表 2. 生成数据透视表宏 B. 将相应的数据拖入到数据透视表内，如图C. 停止录制,宏,宏实例生成销售看板汇总报表 3. 录制宏，将生成的数据透视表复制到“汇总数据”工作表，并调整格式,宏,宏实例生成销售看板汇总报表 4. 将最新的明细数据拷贝到“明细数据”工作