Excel培训教程高级篇

会计学1Excel培训教程高级篇第一部分

函数的高级应用第1页/共96页引用1.这是生产统计用的“生产损耗及回修报表”。其中的一些数据需要引用到“染单进度表”里的数据。怎样通过公式快速的进行输入呢？

应用VLOOKUP第2页/共96页引用2.找出需要从“染单进度表”里引用的字段。在字段名的上方插入一行，标注上它们在“染单进度表”中的列数。第3页/共96页引用3.在表格的最左列也插入一列，注明序列号，此序号从“染单进度表”的第一个记录所在行开始。依次向下递增。第4页/共96页引用4.在单元格B5输入如下公式。第5页/共96页引用5.公式解释：以$A5的值（即4）为索引值,在“染单进度表（6月）”的$A$1:$AQ$1000区域范围中查找相对于索引值所在列第B$3列（即4）的记录，查找方式为精确查找。VLOOKUP($A5,'[染单进度表（6月）.xls]Sheet1'!$A$1:$AQ$1000,B$3,FALSE)第6页/共96页引用应用INDEX+MATCH1.下面再介绍一种引用数据的方法。同样，将数字标记保留。第7页/共96页引用2.在单元格B5输入如下公式。第8页/共96页引用3.公式解释INDEX('[染单进度表（6月）.xls]Sheet1'!$A$1:$AQ$1000,MATCH($A5,'[染单进度表（6月）.xls]Sheet1'!$A$1:$A$1000,0),MATCH(B$3,'[染单进度表（6月）.xls]Sheet1'!$A$2:$AQ$2,0))查找区域返回行数返回列数第9页/共96页引用4.那么运用此公式的目的为：在“染单进度表”中查找记录的行、列数，从而准确的引用了“染单进度表”中的记录。将公式拖拽填充，就完成了所有记录的引用。第10页/共96页引用5.MATCH函数语法：MATCH（需要查找的数值，查找的区域，查找的类型）查找的类型有三种：1表示在查找区域查找小于或等于需要查找的数值的最大数值。0表示在查找区域查找等于需要查找的数值的第一个数。2表示在查找区域查找大于或等于需要查找的数值的最小数值。第11页/共96页引用MATCH($A5,'[染单进度表（6月）.xls]Sheet1'!$A$1:$A$1000,0)在区域‘[染单进度表（6月）.xls]Sheet1’!$A$1:$A$1000中查找与$A5（4）相同的数值,返回该数值在区域中的位置.即“4”所在行数。第12页/共96页MATCH(B$3,'[染单进度表（6月）.xls]Sheet1'!$A$2:$AQ$2,0))在区域‘[染单进度表（6月）.xls]Sheet1’!$A$2:$AQ$2中查找与B$3（4）相同的数值,返回该数值在区域中的位置.即“染单号”所在列数。引用第13页/共96页END引用—例1第14页/共96页引用1.这是用于仓库的“成品纱进销存帐”工作薄，打开“成品纱进销存”工作表，这是一份原始手工录入表，现在需要将这份表做成报表形式。首先将表格做好数字标记。如图所示：第15页/共96页引用2.先将表头做好，给字段区分不同的颜色。按它们在“分类帐”中的位置来设置颜色，即在同一行的字段标记同种颜色。并在字段的上方添加一行，标上它们在“分类帐”中所在的列数。在表格的最左列添加一列序号。第16页/共96页引用3.在单元格B5输入如图所示的公式。第17页/共96页引用4.公式解释：在成品纱进销存!$A$1:$Q$6000区域范围,查找与“$A$8+14*$A4”的值相匹配的值,并返回相对于此值所在列的第“B$2”列.（这里为了便于公式的填充,通过引用B$2单元格的值13来代替输入数字13），查找方式为精确查找。VLOOKUP($A$8+14*$A4,成品纱进销存!$A$1:$Q$6000,B$2,FALSE)第18页/共96页引用5.把两个表格对比一下,可以很清楚的看到：在“分类帐”的第一笔帐中“100%COMBEDCOTTON”所在的行是“4”，与第二笔帐中“100%COMBEDCOTTON”相隔14行，即每一笔帐中相同记录相隔14行。用表达式“$A$8+14*$A4”来表示．所在列值保持不变。第19页/共96页引用6.因为不同颜色标注的字段所在行是不一样的，所以可通过改变表达式“$A$8+14*$A4”中“$A$8”的值来确定它们的行数。最后将公式进行拖拽添充,完成所有数据的录入。第20页/共96页END引用—例2第21页/共96页汇总1.以上介绍了如何根据原始分类帐，利用公式自动生成报表，但报表制作完成后，最后还要进行汇总。即将相同“品名”，“支数”和“色相”的记录进行汇总。第22页/共96页汇总2.建立一个“成品纱(出口)进销存报表”的副本。在A列右边插入一列，在这一列中，将“品名”，“支数”和“色相”三项内容合并成一项。第23页/共96页汇总3.利用“高级筛选”筛选出B列不重复的记录。这里“条件区域”用到的“*”，表示查找出全部记录，但在“高级筛选”对话框中需在“选择不重复的记录”单选框中划“√”。第24页/共96页汇总有关高级筛选中的通配符。以下通配符可作为筛选以及查找和替换内容时的比较条件。请使用若要查找?（问号）

任何单个字符

例如，sm?th查找“smith”和“smyth”*（星号）任何字符数

例如，*east查找“Northeast”和“Southeast”~（波形符）后跟?、*或~问号、星号或波形符

例如，“fy91~?”将会查找“fy91?”第25页/共96页汇总4.然后将这些不重复的记录后面对应记录（不包括需要求和汇总的记录）利用INDEX+MATCH公式引用过来。选择单元格AB5，输入如图所示的公式。第26页/共96页汇总5.公式解释INDEX($A$1:$Z$1000,MATCH($AA5,$B$1:$B$1000,0),MATCH(AB$3,$A$3:$J$3,0))查找区域AA5所在行数AA3所在列数第27页/共96页汇总6.利用公式填充，将其他记录项显示出来。第28页/共96页汇总7.此时最关键的是对满足条件的数值进行求和汇总。在单元格AJ5中输入公式，如图所示：第29页/共96页汇总8.公式解释：条件——在$B$5:$B$1000区域中与$AA5相同的记录。求和——对K$5:K$1000区域中满足条件的数字求和。SUMIF($B$5:$B$1000,$AA5,K$5:K$1000)第30页/共96页汇总9.将公式填充，汇总其他需要求和的数值。第31页/共96页汇总10.以上已经完成了初步的汇总，但显得比较混乱，那么，以AA列为关键字排序一下。第32页/共96页END汇总第33页/共96页条件判断1.

这是6月份的染单进度表，这里综合运用了两个以上函数。条件2条件1第34页/共96页条件判断2.公式解释：在SHEET5的B1：AN6区域内查找与AV列“颜色深度”相匹配的值，并返回区域中第二列的值，查找方式为精确查找。VLOOKUP($AV4,Sheet5!$B$1:$AN$6,2,FALSE)索引值第35页/共96页条件判断3.再看一下“进缸时间”所用到的函数公式。第36页/共96页条件判断4.公式解释：当满足条件1时，即P5=P4，P5=P6同时成立，返回AS4的值；否则返回条件2的判断，即当满足P5=P6，P5不等于P4时，返回AR$2的值;否则返回条件3的判断，即当满足P5不等于P6，P5=P4时，返回AS4的值。如果以上条件均不满足，则返回“0”。IF(AND(P5=P4,P5=P6),AS4,IF(AND(P5=P6,P5<>P4),AR$2,IF(AND(P5<>P6,P5=P4),AS4,0)))条件1条件2条件3第37页/共96页条件判断5.这里，条件1和条件3返回的结果都是AS4，那么可以将这两个条件合并成一个条件。此时的条件1用了OR函数将两个条件合并到一起。IF(OR(AND(P5=P4,P5=P6),AND(P5<>P6,P5=P4)),AS4,IF(AND(P5=P6,P5<>P4),AR$2,0))IF(AND(P5=P4,P5=P6),AS4,IF(AND(P5=P6,P5<>P4),AR$2,IF(AND(P5<>P6,P5=P4),AS4,0)))第38页/共96页条件判断6.仔细看一下公式，还可以将它简化。条件中，当P5=P4时，不管P5=P6，还是P5<>P6,返回的值都是AS4。将P5与P6的判断省略，只要P5=P4条件成立，就会返回值AS4。IF(OR(AND(P5=P4,P5=P6),AND(P5<>P6,P5=P4)),AS4,IF(AND(P5=P6,P5<>P4),AR$2,0))IF(P5=P4,AS4,IF(AND(P5=P6,P5<>P4),AR$2,0))第39页/共96页END条件判断—例1第40页/共96页条件判断1.这是行政用的一份表格，这里“单价”与“纱支”是对应的。即每一类型的纱支对应一种价格。那么，可以用IF条件函数进行判断。第41页/共96页条件判断2.在单元格H3中输入公式。如图所示：这里有6种纱支类型，那么对应6种不同的价格。第42页/共96页条件判断3.公式解释:当$C3满足条件1时,返回28.22；满足条件2时,返回31.53；满足条件3时,返回34.17；满足条件4时,返回9.81；满足条件5时,返回7.5；满足条件6时,返回4.72；如果以上条件均不满足，则返回0。IF($C3="2/20100%CARDEDCOTTON",28.22,IF($C3="2/20100%COMBEDCOTTON",31.53,IF($C3="2/32100%COMBEDCOTTON",34.17,IF($C3="1/1560%LINEN40%COTTON",9.81,IF($C3="2/20100%WOOL",7.5,IF($C3="100%COTTON(DEFECTIVEYARN)",4.72,0))))))条件1条件2条件3条件4条件5条件6第43页/共96页条件判断4.公式输完后,将它拖拽填充就可以了.第44页/共96页条件判断5.IF函数有七层嵌套的限制,以上已经进行了六次条件判断,嵌套了5层,如果以后有新类型的砂支,且对应不同的单价,嵌套多于七层,那么该如何判断?

例子：假如

A1＝1，则

B1＝A；A1＝2，则

B1＝B

……

A1＝16，则

B1＝P=IF(A1=1,"A",IF(A1=2,"B",IF(A1=3,"C",IF(A1=4,"D",IF(A1=5,"E",IF(A1=6,"F",IF(A1=7,"G",IF(A1=8,"H",""))))))))&IF(A1=9,"I",IF(A1=10,"J",IF(A1=11,"K",IF(A1=12,"L",IF(A1=13,"M",IF(A1=14,"N",IF(A1=15,"O",IF(A1=16,"P",""))))))))

连接符,表示合并.第45页/共96页条件判断关于&运算符&（和号）运算符可以代替函数CONCATENATE实现文本项的合并。在这里相当于运用CONCATENATE函数将IF嵌套函数作为它的参数项来合并。由于CONCATENATE可以包含最多30个参数，所以这里&运算符最多也只能连接30个函数公式。第46页/共96页END条件判断—例2第47页/共96页筛选１.这是一份Ｄ.Ｏ总表。根据出口编号的不同，需要将“YES总表”分类成“YGS内销总表”，“YGS出口排序”和“WOOL”。一般方法第48页/共96页筛选2.点击“数据”—“自动筛选”，在各字段名称的右侧出现下拉箭头按钮，单击“出口DO编号”右侧的下拉箭头按钮，选取“自定义”。第49页/共96页筛选3.弹出自定义对话框。在“出口DO编号”文本框下输入“等于”“E*”，单击“确定”按钮。第50页/共96页筛选4.这样就筛选出“出口DO编号”第一个字母为E的所有记录。然后根据下单日先后进行排序。选择“数据”—“排序”，弹出排序对话框，选择主要关键字“下单日”，“升序”，单击“确定”按钮。第51页/共96页筛选5.然后将筛选结果复制到“YGS出口排序”表格中去。根据这种方法，按出口编号的不同，再将“YES总表”分类成“YGS内销总表”，和“WOOL”表。第52页/共96页筛选1.要求筛选出“出口DO编号”第一个字母为E的所有记录，返回到“E”这个工作表中。首先，做好数字标记。如图所示：运用公式第53页/共96页筛选2.在“E”工作表单元格B4输入如下公式。第54页/共96页筛选3.公式解释：如果满足条件的判断。返回VLOOKUP查找的记录，否则返回空。IF(LEFT(YGS总表!$E4)="E",VLOOKUP($A4,YGS总表!$A$1:$L$1000,B$2,FALSE),"")条件判断：YGS总表!$E4单元格中文本字符串的第一个字符等于“E”。

以$A4（4）为索引值，在YGS总表!$A$1:$L$1000区域范围查找，并返回相对于索引值所在列的第B$2（2）列的记录，查找方式为精确查找。第55页/共96页筛选4.这样将公式进行拖拽填充，就完成了记录的筛选。但是此时筛选出的结果比较凌乱，还需要整理一下。第56页/共96页筛选5.选择区域A3：L1000，再选择“数据”—“排序”，在排序对话框中，主要关键字选“按列B”，“升序”，选择“有标题行”单选框按钮。第57页/共96页筛选6.单击“确定”后，就完成了排序。第58页/共96页END筛选第59页/共96页条件格式1.这是船务用的出货排期表。为了清晰的显示出货日期，这里用条件格式来标明颜色。第60页/共96页条件格式2.如果染单要求出货期已经过了，显示黄色；如果染单要求出货期为当日，显示红色；如果染单要求出货期未到，显示蓝色。第61页/共96页条件格式3.选择“格式”—“条件格式”命令，打开“条件格式”对话框。输入以下公式。第62页/共96页条件格式4.公式解释：用IF函数进行判断，如果K4为空值，则返回空，否则进行判断，若(TEXT(NOW(),“YYYY-M-D”))-(TEXT(K4,“YYYY-M-D”))=0，则单元格底纹显示红色。表示染单出货期为今日。当前日期日期格式转换为文本第63页/共96页条件格式5.按照这种方法进行判断，若(TEXT(NOW(),“YYYY-M-D”))-(TEXT(K4,“YYYY-M-D”))>0，则单元格底纹显示黄色，表示染单出货期已过；若(TEXT(NOW(),“YYYY-M-D”))-(TEXT(K4,“YYYY-M-D”))<0，则单元格底纹显示蓝色，表示染单出货期未到。第64页/共96页条件格式6.将条件格式拖拽进行复制，显示如下效果。第65页/共96页条件格式7.对于实际出货日期，也可以通过标注颜色来识别。若实际出货日期提前或准时，则显示日期字体的颜色为蓝色；若实际出货日期推迟，则显示日期字体的颜色为红色。第66页/共96页条件格式8.最后的效果如图。当然你也可以随时根据自己的需要来调整条件格式。第67页/共96页END条件格式第68页/共96页第二部分宏第69页/共96页第二部分宏

有关概念所谓“宏”，就是将一系列的命令和指令组合在一起，形成一个命令，以实现任务执行的自动化。它可以替代人工进行一系列费时而重复的操作，是一个极为灵活的自定义命令。在Excel中运用宏可有两种途径，一种是录制宏，另一种是采用Excel自带的VisualBasic编辑器来编辑宏命令。前一种使用较多，操作简练，也易于理解。这里主要介绍录制宏的方法。

第70页/共96页第二部分宏宏录制器excel宏录制器的整个操必须预演从哪一个操作”开始”,一直到结束的操作程序。首先准备所需要操作的“工作表”,再启动”宏录制器”,然后开始执行需要录制的excel的操作,再关闭”宏录制器”。这样您或其它用户就具有了这种录好的自动程序,并且可以指定一个按钮来执行此自动程序的功能。宏录制工具栏第71页/共96页第二部分宏

应用实例选择一张公司表格，将分类汇总这个操作命令录制成宏命令，并设置快捷键。如果以后需要对表格进行分类汇总操作时，只需执行这个宏命令就可以了。第72页/共96页一.录制宏

操作过程1.打开工作表，选择“工具”—“宏”—“录制宏”命令。第73页/共96页一.录制宏2.在弹出的“录制新宏”对话框中输入宏的名称，定义执行宏的快捷键，并选择保存在“当前工作薄”选项中，然后单击确定。注意：定义快捷键时，选取的字母必须为大写。第74页/共96页一.录制宏3.单击完确定之后的同时弹出一个宏录制工具（如图），就可以开始执行excel的操作.这里在工作表中执行一个分类汇总的动作。这里点击一下相对引用按钮。第75页/共96页一.录制宏4.选择“数据”—“分类汇总”命令，这里以“合约号”为分类字段，将下单量和实际重量汇总求和。第76页/共96页一.录制宏5.完成以后，点击一下宏录制器的停止按钮。第77页/共96页一.录制宏6.为了使用方便，可将其嵌入工具栏中。单击“工具”—“自定义”，选中“命令”对话框中“类别”下拉框中的“宏”，在右边可出现“自定义按钮”，选中它并将其拖至想要放的工具栏上。第78页/共96页一.录制宏7.此时在工具栏上会发现多出一个形状的按钮，单击“自定义”对话框中的“更改所选内容”按钮，会出现一下拉菜单，选择“指定宏”。第79页/共96页一.录制宏8.弹出“指定宏”对话框，选择宏名。单击“确定”按钮。第80页/共96页一.录制宏9.这时还可更改按钮图标，选中你喜欢的图标。第81页/共96页一.录制宏10.为便于理解，可选中“图标与文字”选项，工具栏中的该按钮便改为“图标+说明”的形式，把它命名为“分类汇总”。这样就完成宏的引用工作。第82页/共96页一.录制宏11.建立了宏以后，还必须设置宏安全性，选择“工具”—“选项”命令，打开“选项”对话框，单击“安全性”选项卡。第83页/共96页一.录制宏12.

单击“宏安全性”按钮，打开“安全性”对话框，再单击“安全级”选项卡，设置安全级为“中”。第84页/共96页一.录制宏13.设置安全等级为“中”后，以后每次打开该工作薄，系统将显示提示，单击“取消宏”，则宏命令失效，单击“启用宏”，便可启动宏