《excel高级应用技巧.ppt》由会员分享，可在线阅读，更多相关《excel高级应用技巧.ppt（37页珍藏版）》请在三一办公上搜索。
1、,数据处理方法与技巧-EXCEL高级应用,江苏大学教师教育学院 陶 明 华电话：,2,内 容,1、EXCEL基本概念及数据输入技巧2、清除值为0的单元格 3、在空单元格中输入相同的值4、数据的同步变化、跨工作簿计算5、各种函数(rank match index vlookup offset)6、确定年级班名次7、将不及格的成绩用红色表示8、定位查找9、等级考试发证问题10、盘库打印问题11、数据透视表12、高级筛选问题13、双轴图表的绘制14、邮件合并（图片的使用）15、宏与VBA,3,一、基本概念1、工作簿（一个文件，等价于一本活页夹）2、工作表（等价于活页夹中的活页纸）(一个工作簿中最多可
2、包含255个工作表)3、单元格（等价于活页纸上的小方格）(一个工作表中有65536行(165536),256列(AIV)4、活动工作簿5、活动工作表6、活动单元格7、单元格地址（相对地址、绝对地址、混合地址）例:B2$B$2 B$2或$B2 打开“投资额”工作簿，计算投资比例,一、基本概念及数据输入技巧,4,二、数据输入及技巧1、输入文本（例如：姓名，职称，电话号码，身份证号码）2、输入数值（例如:整数、实数、科学记数、分数）3、输入日期（例如：2003-7-23、CTRL+;键可将计算机中的日期调入单元格）4、输入时间（例如：14：30：20、CTRL+SHIFT+:键可将计算机中的时间调入
3、单元格）,一、基本概念及数据输入技巧,5,二、数据输入及技巧5、等差或等比数列的输入方法(1)先输入二个数据(2)选定这二个单元格(3)鼠标靠向填充柄，等鼠标指针变为【】(4)按住鼠标右键拖动至目的地，松开鼠标标右键，从弹出的快捷菜单中选【等差序列】或【等比序列】6、日期输入方法（1）先输入一个日期（2）选定这个单元格（3）鼠标靠向填充柄，等变为【】（4）按住鼠标右键拖动至目的地，松开鼠标，从弹出的快捷菜单中选【以年填充】或【以月填充】等。,一、基本概念及数据输入技巧,6,一、基本概念及数据输入技巧,7、在一个区域内输入相同的数据CTRL+ENTER键8、自定义序列单击【文件】菜单下的【选项】
4、，【高级】标签中的【编辑自定义列表】,进行相应的操作即可.例:教授,副教授,讲师,助教,7,一、基本概念及数据输入技巧,在“考试报名表”工作簿中进行操作（数据格式的设置）操作步骤：（1）“选定区域”，单击“开始”菜单下“数字”下的“其他数字格式.”）;或“选定区域”，单击右键选“设置单元格格式”,（2）单击”数字”标签,再单击”自定义”,进行自定义格式的设置：“2006152”000，“2006级数控技术”0“班”,思考题：能否设定输入：江苏大学11级2班，江苏大学12级3班,8,一、基本概念及数据输入技巧,数据有效性的设置操作步骤：（3）选定性别下的区域，单击“数据”菜单下的“数据有效性”，
5、允许选“序列”，来源框中输入：男，女（或单击“数据”菜单下的“数据有效性”下的“数据有效性”）（4）选定身份证号下的区域，从E3开始，单击“数据”菜单下的“数据有效性”，进行设置,9,一、基本概念及数据输入技巧,10,一、基本概念及数据输入技巧,11,一、基本概念及数据输入技巧,数据有效性的清除（例如：清除“性别”标题下的数据有效性）选定性别下的区域，单击“数据”菜单下的“数据有效性”，从弹出的对话框中单击左下角的”全部清除”按钮即可,12,操作方法：（在“基础操作表”工作簿中进行）(1)选定需要清除值为0的数据区域（E2：F204）(2)单击【开始】菜单下的【查找和替换】中的替换命令(3)在
6、弹出的“查找和替换”对话框中的查找内容处输入0，在替换为处不输入任何内容，选中“单元格匹配”，单击【全部替换】命令按钮。即可将（E2：F204）区域中所有值为0的单元格清除。也可以用“查找”命令去做,二、清除值为0的单元格,13,例:如何将借方金额和贷方金额中为空的单元格输入0 操作方法：(在基础表中进行操作）(1)选定数据区域（E2：F204）(2)单击【开始】菜单下的【查找和替换】下的【定位条件】）(3)在弹出的“定位”对话框中，单击【定位条件】按钮(4)在弹出“定位条件”对话框中选“空值”，单击【确定】(5)原区域中所有空值的单元格均被选中(6)输入0值，然后按CTRL+ENTER也可以
7、用“查找”命令去做,三、在空单元格中输入相同的值,14,打开“成绩表”工作簿，将成绩表中的语文、数学、英语成绩分别复制相应的三张工作表中、要求数据同步变化。使用“选择性粘贴”中的“粘贴链接”即可、将计算结果复制到其它单元格中（用“选择性粘贴”中的“数值”、数据的跨工作簿计算打开“跨工作簿计算用数据表”文件夹中的四个工作簿可实现数据的跨工作薄计算。、打开“工资单”工作簿，用合并计算的方法求出男女同志的平均工资、平均奖金,四、数据的同步变化、数据跨工作簿计算、合并计算,15,打开“工资单”工作簿1、RANK函数，用于乱序数据的排序号。例：在J2中输入=RANK(I2,$I$2:$I$40,0)，0
8、表示从大到小2、IF函数，根据给定的条件确定相应的值。例：实发工资大于等于600，评价为“高”，实发工资大于等于500小于600评价为“中”;实发工资小于500评价为“低”在K2单元格中输入=IF(I2=600,高,IF(I2=500,中,低)3、MATCH函数，匹配函数，如工作表中的C46位置中的=MATCH(B46,B2:B40,0),得出B46中名字与B2:B40中的第几个名字匹配，成功给出第几个的值。匹配类型为1时，找小于等于要找值的最大值，数据必须从小到大排序.匹配类型为0时,查找等于要找值的第一个值，数据无需排序。匹配类型为-1时，找大于等于要找值的最小值，数据必须从大到小排序。,
9、五、各种函数的应用,16,打开“工资单”工作簿4、VLOOKUP函数，定位查找相应的值VLOOKUP函数的有数：搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回单元格的值。缺省表以升序排序。参数1：需要在数据表首列进行搜索的值参数2：需要在其中搜索数据的信息表，可以是一个区域参数3：满足条件的单元格在数据区域中的列序号，首列为1参数4：指定在查找时要求大致匹配还是精确匹配，FALSE为大致匹配上，TRUE为精确匹配，缺省为精确匹配例如：F46单元格中=VLOOKUP(E46,B2:E40,4,0)，公式中4表示区域中的第四列，即基本工资这一列。,五、各种函数的应用,
10、17,打开“工资单”工作簿5、ISERROR函数，判断是否错误（判断单元格是否错误）6、INDEX函数，返回指定行列交叉处引用的单元格 参数1：为指定的要搜索的范围 参数2：为指定返回的行序号 参数3：为指定返回的列序号 参数4：返回该区域中行和列交叉域，一般为省略例：返回A1：C10区域，第五行，第二列的值！=INDEX（A1:C10，5，2）例：在C50的单元格中输入：=INDEX(A2:I40,3,2)，返值为“常镇”7、OFFSET函数，偏移定位=OFFSET(数据库!$B$3,20,8)，第一参数为作为参照系区域在原始表中的偏移量，第二个参数是行相对于参照系的偏移量，第三个参数是列相
11、对参照系的偏移量，第四个参数是新区域的行数，第五个参数是新区域的列数,五、各种函数的应用,18,六、确定年级、班级名次,打开“考试成绩表”工作簿操作步骤(1)在I3单元格中输入“=RANK(H3,$H$3:$H$122,0)”，注意区域需用绝对地址，0表示从大到小(2)在J3单元格中输入“=RANK(H3,$H$3:$H$42,0)”排一班的班级排名(3)在J43单元格中输入“=RANK(H43,$H$43:$H$82,0”排二班的班级排名(4)在J83单元格中输入“=RANK(H83,$H$83:$H$122,0”排三班的班级排名,19,七、将不及格的成绩用红色表示,打开“考试成绩表”工作簿
12、操作步骤：（1）选定C3:G122区域（2）单击【开始】菜单下的【样式】菜单下的【条件格式】菜单下的【新建规则】，从弹出的对话框中选“只为包含以下内容设置单元格格式”）（3）在弹出的对话框中进行相应的设置如果有多个条件，则重复以上操作（注意2003中，多个条件需一次完成）,20,八、定位查找,打开“定位查找”工作簿中的定位查找工作表（MATCH函数的使用）操作步骤：（1）在B21单元格中输入“输入条件”，合并B21 C21单元格（2）在B22中输入“行”，C22中输入“列”（3)将光标定位到B23,在“数据”菜单下选“有效性”，从弹出的对话框中选设置“标签，允许选“序列”，来源设为“=$A$2
13、:$A$17”、同理将C22单元格进行设置，来源设为“=$B$1:$O$1”（4）在F22中输入“结果”，F23中输入“=INDEX(B2:O17,MATCH(B23,A2:A17,0),MATCH(C23,B1:O1,0)”match函数中的0为匹配方式(5)选中B2:O17区域，设置“条件格式”，选“公式”，“=($B$23=$A2)+($C$23=B$1)”，颜色设置为“黄色。,21,八、定位查找,22,九、等级考试发证问题,打开“技能等级办证登记单”工作簿（IF函数的使用）操作步骤，选办证登记单,成绩高于70分通过标记为“P”,且巳交费者可办证（1）选定C3单元格，输入”=IF(B3=
14、70,”P”,”)（2）选定E3,输入“=IF(C3P,IF(D3=30,是,否)”,23,十、会计科目问题,打开“科目表”工作簿（VLOOKUP函数的使用）操作步骤:在C2单元格中输入：=IF(LEN(A2)=4,B2,VLOOKUP(LEFT(A2,4),A:B,2,0)在D2单元格中输入：=IF(LEN(A2)=7,B2,IF(LEN(A2)7,VLOOKUP(LEFT(A2,7),A:B,2,0),)VLOOKUP函数的有数：搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回单元格的值。缺省表以升序排序。参数1：需要在数据表首列进行搜索的值参数2：需要在其中搜
15、索数据的信息表，可以是一个区域参数3：满足条件的单元格在数据区域中的列序号，首列为1，注意：要搜索的值必须是区域的第一列，且此列巳按升序排序。参数4：指定在查找时要求大致匹配还是精确匹配，FALSE为大致匹配上，TRUE为精确匹配，缺省为精确匹配,24,十一、盘库打印问题,打开“盘库打印条”工作簿（INDEX函数的使用）操作步骤：(1)单击“插入”菜单下的“名称”下的“定义”，在对话框中输入“DATA”,引用位置输入：=OFFSET(数据库!$B$3,COUNTA(数据库!$B$3:$B$999),8)，第一参数为作为参照系区域在原始表中的偏移量，第二个参数是行相对于参照系的偏移量，第三个参数
16、是列相对参照系的偏移量，第四个参数是新区域的行数，第五个参数是新区域的列数（2）在“打印表”的J1单元格中输入：“请输入页数:”J2单元格中输入数值1（3）在“打印表”工作表中，输入所示的内容（4）在A4单元格中输入：=INEDX(DATA,4*($J$2-1)+1,COLUMN(),横向拖动公式（5）在A12单元格中输入：=INEDX(DATA,4*($J$2-1)+2,COLUMN(),横向拖动公式（6）在A21 A30中进行类似的输入=INEDX(DATA,4*($J$2-1)+3,COLUMN(),=INEDX(DATA,4*($J$2-1)+4,COLUMN()，横向拖动公式,25,
17、十二、数据透视表,操作步骤（打开材料库存）数据透视表（1）选定数据区域（2）【插入】菜单下的【表格】菜单下的【数据透视表】）,26,十二、数据透视表,27,十三、数据的高级筛选,操作步骤（打开高级筛选综述表）高级筛选（1）先配好筛选的条件（将要用到的标题复制到数据区域的旁边，空一列即可。在复制出来的标题下输入筛选条件，同一行上为“并且”条件，不同行上为“或者”条件）（2）选定数据区域（3）【数据】【排序和筛选】【筛选】【高级】（4）从弹出的对话框中选定：方式、数据区域、条件区域、以及数据将要复制的位置（5）单击【确定】按钮注意：(1)在条件区域中，条件单元格内包含单元格引用：如“=D5800”
18、，条件区域标题虽然可以不填，但在选择筛选条件时却不能不选，即如果只选择“=D5800”所在的单元格作为筛选条件，结果是错误的。必须把筛选条件单元格的上一个单元格一并选中。(2)在条件区域中，条件单元格内不包含单元格引用，如“800”，必须写上与数据区标题相同名称。其他任何名称或不填都会产生错误结果。建议使用复制粘贴的方法，避免输入失误造成筛选结果出错。,28,十四、特殊排序,操作步骤（打开教师通信录表）按表中的职务进行排序（1）【开始】下的【选项】下的【高级】标签的【编辑自定义列表】）导入相应的序列（2）选定数据区域（3）单击“数据”菜单下的“排序”，单击对话框中的“次序”按钮，确定自定义的排
19、序序列。（4）选定主关键字，单击“确定”,29,十五、双轴图表的绘制,操作步骤（打开双轴图表.xls）1.选择数据区域中的某个单元格，单击菜单“插入图表”中的“折线图”，选”数据点折线图”。,30,2.这时可以看到插入的两个数据系列中，由于销售增长率数值太小而无法显示其变化趋势。右击“销售增长率”系列，在弹出的快捷菜单中选择“设置数据系列格式”。在弹出的“数据系列格式”对话框中选择“系列选项”选项卡，选择“次坐标轴”。单击“确定”。,十五、双轴图表的绘制,31,3.右击“销售额”系列，选择“更改系列图表类型”，将“销售额”系列的图表类型更改为“柱形图”。4.最后，修改绘图区图案背景、修改图例位
20、置并完成双轴图。,十五、双轴图表的绘制,32,打开“投资额”工作簿，1、选定有错误信息的区域，例如：C2:C62、单击【开始】菜单下的【样式】菜单下的【条件格式】菜单下的【新建规则】，从弹出的对话框中选“使用公式确定要设置单元格格式”）（3）在弹出的对话框中进行相应的设置公式中输入=ISERROR(C2),字体颜色设为白色,十六、去掉出错单元格的错误信息,33,一、准备工作：准备一份Excel的学生信息数据。需要注意的是：在使用Excel工作簿时，必须保证数据文件是数据库格式，即第一行必须是字段名，数据行中间不能有空行等。比如第一行里面包含了学号、姓名、性别、照片格式、照片名等信息的字段，（照
21、片名必须与学生姓名一致，“照片”的输入如下图所示，单击G2，在G2的编辑栏中输入“=A2&F2”后敲回车键，再双击单元格进行自动填充即可）。数据准备工作的正确与否，关系到以后打印出来的证件正确与否，所以必须要仔细校对检查。,十七、邮件合并（带图片的邮件合并）,34,三、照片的拍摄采集与编辑照片格式：jpg；大小：1015 KB为宜；名称：要与学生姓名一致，不能有同音字和形近字。然后将刚才编辑的Excel数据、word模板格式、编辑好的照片放在同一个文件夹中。（如果照片放在子文件夹，则路径中分隔符需用二个反斜杠）四、利用Word邮件合并功能将Excel数据、照片合并到word模板，步骤如下：1启
22、动word程序，从菜单栏中选择“视图”菜单下“工具栏”下的“邮件合并”，显示“邮件合并”工具栏2在邮件合并工具栏上单击打开”数据源“工具，打开相应的数据源（EXCEL工作簿文件）,十七、邮件合并（带图片的邮件合并）,35,3将光标定位到相应的位置，单击插入“插入域”工具按钮，插入相应的域4下面是实现批量打印照片的关键一步，就是照片域的实现：先把光标定位在要插入照片的地方，单击“插入”“域”在“域名”处选择“Includepicture”在“域属性”文件名中，为了方便起见填入任意字符，比如“1”单击确定。如图：单击刚才插入的“域”“shift”键+“F9”键选择“1”点击“插入域”插入“照片名”
23、点击“合并到新文档”“确定”单击“保存”（将此生成的文件与主文档保存在同一目录下）按“ctrl+A”键全选按“F9”。在同一页纸中出现了多个准考证。,十七、邮件合并（带图片的邮件合并）,36,宏是在应用程序中可以自动运行的一连串功能命令。能够完成大量的重复操作。VBA是一种编程语言，专门用于OFFICE中打开“客户资料”工作簿（1）单击“工具”菜单下的“宏”下的“安全性”，将安全级设置为“中”（或【开始】下的【选项】下的【信任中心】标签的【信任中心设置】的“宏设置”，将安全级设置为“中”（2）将活动单元格定位在B2的位置（2）单击“工具”菜单下的“宏”下的“录制新宏”，单击选“相对引用”，一定要使“相对引用”处于选定的状态（或选择【视图】下的【宏】标签【录制宏】）（3）将第一个人的资料复制粘贴到指定的位置，复制、粘贴用快捷键进行，将活动单元格定位到第二个人的姓名中（B6)单元格完成一个周期操作（4）单击“停止录制”（5）单击“工具”菜单下的“宏”下的“宏.”，单击宏的名称，再单击“执行”按钮即可。如果单击“选项”按钮，可为宏设置快捷键。如果按ALT+F11可进入VBA编程，如果在过程中加入语句：dim j as integer for j=1 to 500 next j循环体为原来的宏过程，即可自动完成相应的操作,十八、宏与VBA,37,谢谢大家！,
