excel高级应用技巧

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

1、,数据处理方法与技巧-EXCEL高级应用,江苏大学教师教育学院 陶 明 华 电话：13505280548 E-Mail:,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+;键可将计算机中的日期

3、调入单元格） 4、输入时间（例如：14：30：20、CTRL+SHIFT+:键可将计算机中的时间调入单元格）,一、基本概念及数据输入技巧,5,二、数据输入及技巧 5、等差或等比数列的输入方法 (1)先输入二个数据 (2)选定这二个单元格 (3)鼠标靠向填充柄，等鼠标指针变为【】 (4)按住鼠标右键拖动至目的地，松开鼠标标右键，从弹出的快捷菜单中选【等差序列】或【等比序列】 6、日期输入方法 （1）先输入一个日期 （2）选定这个单元格 （3）鼠标靠向填充柄，等变为【】 （4）按住鼠标右键拖动至目的地，松开鼠标，从弹出的快捷菜单中选【以年填充】或【以月填充】等。,一、基本概念及数据输入技巧,6,一

4、、基本概念及数据输入技巧,7、在一个区域内输入相同的数据CTRL+ENTER键 8、自定义序列 单击【工具】菜单下的【选项】,再单击【自定义序列】(【 office按钮】下的【 EXCEL选项】下的【常用】标签的【编辑自定义列表】,进行相应的操作即可 9、学号录入后三位即可显示为2006152XXX 10、班级录入编号（如1）即可显示为全称（2006级数控技术1班） 11、性别可通过下拉框选择 12、身份证号具有提示功能及位数较验功能,7,一、基本概念及数据输入技巧,在“考试报名表”工作簿中进行操作（数据格式的设置） 操作步骤： （1）单击“格式”菜单下的“单元格”（或“开始”菜单下“单元格”

5、菜单下的“设置单元格格式.”） （2）进行自定义格式的设置：“2006152”000，“2006级数控技术”0“班” （3）选定学号下的区域，设定为自定义格式：“2006152”000 （4）选定班级下的区域，设定为自定义格式，“2006级数控技术”0“班”,思考题：能否设定输入： 江苏大学10级2班，江苏大学11级3班,8,一、基本概念及数据输入技巧,数据有效性的设置 操作步骤： （4）选定性别下的区域，单击“数据”菜单下的“数据有效性”，允许选“序列”，来源框中输入：男，女（或单击“数据”菜单下的“数据有效性”下的“数据有效性”） （5）选定身份证号下的区域，从E3开始，单击“数据”菜单下

6、的“数据有效性”，进行设置,9,一、基本概念及数据输入技巧,10,一、基本概念及数据输入技巧,11,一、基本概念及数据输入技巧,数据有效性的清除（例如：清除“性别”标题下的数据有效性） 选定性别下的区域，单击“数据”菜单下的“数据有效性”，从弹出的对话框中单击左下角的”全部清除”按钮即可,12,操作方法：（在“基础操作表”工作簿中进行） (1) 选定需要清除值为0的数据区域（E2：F204） (2) 单击【编辑】菜单下的【替换】命令或按快捷键Ctrl+H（或【开始】菜单下的【编辑】菜单下的【查找和选择】菜单下的【替换】） (3) 在弹出的“查找和替换”对话框中的查找内容处输入0，在替换为处不输

7、入任何内容，选中“单元格匹配”，单击【全部替换】命令按钮。即可将（E2：F204）区域中所有值为0的单元格清除。也可以用“查找”命令去做,二、清除值为0的单元格,13,例:如何将借方金额和贷方金额中为空的单元格输入0 操作方法：(在基础表中进行操作） (1)选定数据区域（E2：F204） (2)单击【编辑】菜单下的【定位】命令（或【开始】菜单下的【编辑】菜单下的【查找和选择】菜单下的【定位条件】） (3)在弹出的“定位”对话框中，单击【定位条件】按钮 (4)在弹出“定位条件”对话框中选“空值”，单击【确定】 (5)原区域中所有空值的单元格均被选中 (6)输入0值，然后按CTRL+ENTER 也

8、可以用“查找”命令去做,三、在空单元格中输入相同的值,14,打开“成绩表”工作簿，将成绩表中的语文、数学、英语成绩分别复制相应的三张工作表中 、要求数据同步变化。 使用“选择性粘贴”中的“粘贴链接”即可 、将计算结果复制到其它单元格中（用“选择性粘贴”中的“数值” 、数据的跨工作簿计算 打开“跨工作簿计算用数据表”文件夹中的四个工作簿可实现数据的跨工作薄计算。 、打开“工资单”工作簿，用合并计算的方法求出男女同志的平均工资、平均奖金,四、数据的同步变化、数据跨工作簿计算、合并计算,15,打开“工资单”工作簿 1、RANK函数，用于乱序数据的排序号。例：在J2中输入 =RANK(I2,$I$2:

9、$I$40,0)，0表示从大到小 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时，找大于等于

10、要找值的最小值，数据必须从大到小排序。,五、各种函数的应用,16,打开“工资单”工作簿 4、VLOOKUP函数，定位查找相应的值 VLOOKUP函数的有数：搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回单元格的值。缺省表以升序排序。 参数1：需要在数据表首列进行搜索的值 参数2：需要在其中搜索数据的信息表，可以是一个区域 参数3：满足条件的单元格在数据区域中的列序号，首列为1 参数4：指定在查找时要求大致匹配还是精确匹配，FALSE为大致匹配上，TRUE为精确匹配，缺省为精确匹配 例如：F46单元格中 =VLOOKUP(E46,B2:E40,4,0)，公式中4表示

11、区域中的第四列，即基本工资这一列。,五、各种函数的应用,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)，第一参数为作为参照系区域在

12、原始表中的偏移量，第二个参数是行相对于参照系的偏移量，第三个参数是列相对参照系的偏移量，第四个参数是新区域的行数，第五个参数是新区域的列数,五、各种函数的应用,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

13、”排三班的班级排名,19,七、将不及格的成绩用红色表示,打开“考试成绩表”工作簿 操作步骤： （1）选定C3:G122区域 （2）单击“格式”菜单下的“条件格式”（ （或【开始】菜单下的【样式】菜单下的【条件格式】菜单下的【新建规则】，从弹出的对话框中选“只为以下内容设置单元格格式”） （3）在弹出的对话框中进行相应的设置,20,八、定位查找,打开“定位查找”工作簿中的定位查找工作表（MATCH函数的使用） 操作步骤： （1）在B21单元格中输入“输入条件”，合并B21 C21单元格 （2）在B22中输入“行”，C22中输入“列” （3)将光标定位到B23,在“数据”菜单下选“有效性”，从弹出

14、的对话框中选“设置“标签，允许选“序列”，来源设为“=$A$2:$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,九、等级考试发证问题,打开“技能等级办证登记单”工作簿（IF函数的使用） 操作步骤，选办证登记单,成绩高于70分通过标记为“P”,且巳交费者

15、可办证 （1）选定C3单元格，输入”=IF(B3=70,”P”,”) （2）选定E3,输入“=IF(C3“P“,“,IF(D3=30,“是“,“否“)”,22,十、会计科目问题,打开“科目表”工作簿（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函数的有数：搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回单元格的

16、值。缺省表以升序排序。 参数1：需要在数据表首列进行搜索的值 参数2：需要在其中搜索数据的信息表，可以是一个区域 参数3：满足条件的单元格在数据区域中的列序号，首列为1，注意：要搜索的值必须是区域的第一列，且此列巳按升序排序。 参数4：指定在查找时要求大致匹配还是精确匹配，FALSE为大致匹配上，TRUE为精确匹配，缺省为精确匹配,23,十一、盘库打印问题,打开“盘库打印条”工作簿 （INDEX函数的使用） 操作步骤： (1)单击“插入”菜单下的“名称”下的“定义”，在对话框中输入“DATA”,引用位置输入：=OFFSET(数据库!$B$3,COUNTA(数据库!$B$3:$B$999),8)，第一参数为作为参照系区域在原始表中的偏移量，第二个参数是行相对于参照系的偏移量，第三个参数是列相对参照系的偏移量，第四个参数是新区域的行数，第五个参数是新区域的列数 （2）在“打印表”的J1单元格中输入：“请输入页数:” J2单元格中输入数值1 （3）在“打印表”工作表中，输入所