Excel培训教程高级篇课件

目录引用汇总条件判断筛选条件格式录制宏执行宏第一部分函数的高级应用第二部分宏目录引用录制宏第一部分函数的高级应用1第一部分

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

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

任何单个字符

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

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

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

这是6月份的染单进度表，这里综合运用了两个以上函数。条件2条件1条件判断1.这是6月份的染单进度表，这里综合运用了两个以上35条件判断2.公式解释：在SHEET5的B1：AN6区域内查找与AV列“颜色深度”相匹配的值，并返回区域中第二列的值，查找方式为精确查找。VLOOKUP($AV4,Sheet5!$B$1:$AN$6,2,FALSE)索引值条件判断2.公式解释：在SHEET5的B1：AN6区域内查找36条件判断3.再看一下“进缸时间”所用到的函数公式。条件判断3.再看一下“进缸时间”所用到的函数公式。37条件判断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条件判断4.公式解释：IF(AND(P5=P4,P5=P6)38条件判断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)))条件判断5.这里，条件1和条件3返回的结果都是AS4，那么可39条件判断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))条件判断6.仔细看一下公式，还可以将它简化。条件中，当P5=40END条件判断—例1END41条件判断1.这是行政用的一份表格，这里“单价”与“纱支”是对应的。即每一类型的纱支对应一种价格。那么，可以用IF条件函数进行判断。条件判断1.这是行政用的一份表格，这里“单价”与“纱支”是对42条件判断2.在单元格H3中输入公式。如图所示：这里有6种纱支类型，那么对应6种不同的价格。条件判断2.在单元格H3中输入公式。如图所示：这里有6种纱支43条件判断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条件判断3.公式解释:当$C3满足条件1时,返回28.22；44条件判断4.公式输完后,将它拖拽填充就可以了.条件判断4.公式输完后,将它拖拽填充就可以了.45条件判断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",""))))))))

连接符,表示合并.条件判断5.IF函数有七层嵌套的限制,以上已经进行了六次条件46条件判断关于&运算符&（和号）运算符可以代替函数CONCATENATE实现文本项的合并。在这里相当于运用CONCATENATE函数将IF嵌套函数作为它的参数项来合并。由于CONCATENATE可以包含最多30个参数，所以这里&运算符最多也只能连接30个函数公式。条件判断关于&运算符47END条件判断—例2END48筛选１.这是一份Ｄ.Ｏ总表。根据出口编号的不同，需要将“YES总表”分类成“YGS内销总表”，“YGS出口排序”和“WOOL”。一般方法筛选１.这是一份Ｄ.Ｏ总表。根据出口编号的不同，需要将“YE49筛选2.点击“数据”—“自动筛选”，在各字段名称的右侧出现下拉箭头按钮，单击“出口DO编号”右侧的下拉箭头按钮，选取“自定义”。筛选2.点击“数据”—“自动筛选”，在各字段名称的右侧出现下50筛选3.弹出自定义对话框。在“出口DO编号”文本框下输入“等于”“E*”，单击“确定”按钮。筛选3.弹出自定义对话框。在“出口DO编号”文本框下输入“等51筛选4.这样就筛选出“出口DO编号”第一个字母为E的所有记录。然后根据下单日先后进行排序。选择“数据”—“排序”，弹出排序对话框，选择主要关键字“下单日”，“升序”，单击“确定”按钮。筛选4.这样就筛选出“出口DO编号”第一个字母为E的所有记录52筛选5.然后将筛选结果复制到“YGS出口排序”表格中去。根据这种方法，按出口编号的不同，再将“YES总表”分类成“YGS内销总表”，和“WOOL”表。筛选5.然后将筛选结果复制到“YGS出口排序”表格中去。根据53筛选1.要求筛选出“出口DO编号”第一个字母为E的所有记录，返回到“E”这个工作表中。首先，做好数字标记。如图所示：运用公式筛选1.要求筛选出“出口DO编号”第一个字母为E的所有记录，54筛选2.在“E”工作表单元格B4输入如下公式。筛选2.在“E”工作表单元格B4输入如下公式。55筛选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）列的记录，查找方式为精确查找。筛选3.公式解释：如果满足条件的判断。返回VLOOKUP查找56筛选4.这样将公式进行拖拽填充，就完成了记录的筛选。但是此时筛选出的结果比较凌乱，还需要整理一下。筛选4.这样将公式进行拖拽填充，就完成了记录的筛选。但是此时57筛选5.选择区域A3：L1000，再选择“数据”—“排序”，在排序对话框中，主要关键字选“按列B”，“升序”，选择“有标题行”单选框按钮。筛选5.选择区域A3：L1000，再选择“数据”—“排序”，58筛选6.单击“确定”后，就完成了排序。筛选6.单击“确定”后，就完成了排序。59END筛选END60条件格式1.这是船务用的出货排期表。为了清晰的显示出货日期，这里用条件格式来标明颜色。条件格式1.这是船务用的出货排期表。为了清晰的显示出货日期，61条件格式2.如果染单要求出货期已经过了，显示黄色；如果染单要求出货期为当日，显示红色；如果染单要求出货期未到，显示蓝色。条件格式2.如果染单要求出货期已经过了，显示黄色；如果染单要62条件格式3.选择“格式”—“条件格式”命令，打开“条件格式”对话框。输入以下公式。条件格式3.选择“格式”—“条件格式”命令，打开“条件格式”63条件格式4.公式解释：用IF函数进行判断，如果K4为空值，则返回空，否则进行判断，若(TEXT(NOW(),“YYYY-M-D”))-(TEXT(K4,“YYYY-M-D”))=0，则单元格底纹显示红色。表示染单出货期为今日。当前日期日期格式转换为文本条件格式4.公式解释：用IF函数进行判断，如果K4为空值，则64条件格式5.按照这种方法进行判断，若(TEXT(NOW(),“YYYY-M-D”))-(TEXT(K4,“YYYY-M-D”))>0，则单元格底纹显示黄色，表示染单出货期已过；若(TEXT(NOW(),“YYYY-M-D”))-(TEXT(K4,“YYYY-M-D”))<0，则单元格底纹显示蓝色，表示染单出货期未到。条件格式5.按照这种方法进行判断，若(TEXT(NOW(),65条件格式6.将条件格式拖拽进行复制，显示如下效果。条件格式6.将条件格式拖拽进行复制，显示如下效果。66条件格式7.对于实际出货日期，也可以通过标注颜色来识别。若实际出货日期提前或准时，则显示日期字体的颜色为蓝色；若实际出货日期推迟，则显示日期字体的颜色为红色。条件格式7.对于实际出货日期，也可以通过标注颜色来识别。67条件格式8.最后的效果如图。当然你也可以随时根据自己的需要来调整条件格式。条件格式8.最后的效果如图。当然你也可以随时根据自己的需要来68END条件格式END69第二部分宏第二部分70第二部分宏

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

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

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

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

单击“宏安全性”按钮，打开“安全性”对话框，再单击“安全级”选项卡，设置安全级为“中”。一.录制宏12.单击“宏安全性”按钮，打开“安全性”对话框85一.录制宏13.设置安全等级为“中”后，以后每次打开该工作薄，系统将显示提示，单击“取消宏”，则宏命令失效，单击“启用宏”，便可启动宏命令了。一.录制宏13.设置安全等级为“中”后，以后每次打开该工作薄86二.执行宏假设这里有一张类似的表格需要进行分类汇总，那么，现在只需点击一下工具栏上的图标按纽，或者按下设置好的快捷键CTRL+SHIFT+C，就可以执行这个宏了。简单的说，我们已经把分类汇总这个操作过程录制下来了，需要进行分类汇总操作的时候，只需把这个录制执行一下即可。

二.执行宏87二.执行宏在这里再介绍一下如何用控件中的按钮执行宏.1.选择“视图”—“工具栏”—“控件工具箱”命令，弹出控件工具栏，选择命令按钮。二.执行宏在这里再介绍一下如何用控件中的按钮执行宏.88二.执行宏

2.在工作表中拖出一个按钮,然后单击“控件工具箱”的“属性”按钮，设置按钮的属性.单击“外观”的“Caption”项，选中右边的文本“Command-Button1”，并将它改成“分类汇总”二.执行宏2.在工作表中拖出一个按钮,然后单击“控件工具箱89二.执行宏

另外，在“属性”对话框中，还可以根据需要调整按钮的背景、颜色、大小字体等设置。二.执行宏另外，在“属性”对话框中，还可以根据需要调整按钮90二.执行宏3.回到表格中，鼠标指向“分类汇总”按钮，单击右键，弹出快捷菜单，选择“查看代码”命令．

二.执行宏3.回到表格中，鼠标指向“分类汇总”按钮，单击右键91二.执行宏4.在弹出的代码窗口中的PrivateSub和EndSub之间输入录制好的宏的名称。再这中间输入新录制宏的名称输完之后关闭这个窗口就可以了二.执行宏4.在弹出的代码窗口中的PrivateSub和E92二.执行宏

5.上一步完成之后,简单的用按钮实现运行宏的设置就算完成了,以后只要单击工作表中的按钮就可以看到分类汇总后的结果,也就是刚才录制宏的效果.二.执行宏5.上一步完成之后,简单的用按钮实现运行宏的设置93二.执行宏

6.我们还可以用同样的方法重新录制一个“取消分类汇总”的宏，并制作一个按钮来控制它。二.执行宏6.我们还可以用同样的方法重新录制一个“取消分类94开始录制宏操作步骤完成宏录制运行宏录制宏需要经常重复执行的某项任务，就可以把执行这些任务的步骤全部录制在宏里，把宏变为可自动执行的任务

执行宏的操作执行宏的命令在工具栏上设置宏按钮使用窗体控件使用图形对象执行宏

小结开始录制宏操作步骤完成宏录制运行宏录制宏执行宏的操作小结95结束结束96目录引用汇总条件判断筛选条件格式录制宏执行宏第一部分函数的高级应用第二部分宏目录引用录制宏第一部分函数的高级应用97第一部分

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

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

任何单个字符

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

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

例如，“fy91~?”将会查找“fy91?”汇总有关高级筛选中的通配符。请使用若要查找?（问号）任何122汇总4.然后将这些不重复的记录后面对应记录（不包括需要求和汇总的记录）利用INDEX+MATCH公式引用过来。选择单元格AB5，输入如图所示的公式。汇总4.然后将这些不重复的记录后面对应记录（不包括需要求和123汇总5.公式解释INDEX($A$1:$Z$1000,MATCH($AA5,$B$1:$B$1000,0),MATCH(AB$3,$A$3:$J$3,0))查找区域AA5所在行数AA3所在列数汇总5.公式解释INDEX($A$1:$Z$1000,查找区124汇总6.利用公式填充，将其他记录项显示出来。汇总6.利用公式填充，将其他记录项显示出来。125汇总7.此时最关键的是对满足条件的数值进行求和汇总。在单元格AJ5中输入公式，如图所示：汇总7.此时最关键的是对满足条件的数值进行求和汇总。在单元格126汇总8.公式解释：条件——在$B$5:$B$1000区域中与$AA5相同的记录。求和——对K$5:K$1000区域中满足条件的数字求和。SUMIF($B$5:$B$1000,$AA5,K$5:K$1000)汇总8.公式解释：SUMIF($B$5:$B$1000,$A127汇总9.将公式填充，汇总其他需要求和的数值。汇总9.将公式填充，汇总其他需要求和的数值。128汇总10.以上已经完成了初步的汇总，但显得比较混乱，那么，以AA列为关键字排序一下。汇总10.以上已经完成了初步的汇总，但显得比较混乱，那么，以129END汇总END130条件判断1.

这是6月份的染单进度表，这里综合运用了两个以上函数。条件2条件1条件判断1.这是6月份的染单进度表，这里综合运用了两个以上131条件判断2.公式解释：在SHEET5的B1：AN6区域内查找与AV列“颜色深度”相匹配的值，并返回区域中第二列的值，查找方式为精确查找。VLOOKUP($AV4,Sheet5!$B$1:$AN$6,2,FALSE)索引值条件判断2.公式解释：在SHEET5的B1：AN6区域内查找132条件判断3.再看一下“进缸时间”所用到的函数公式。条件判断3.再看一下“进缸时间”所用到的函数公式。133条件判断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条件判断4.公式解释：IF(AND(P5=P4,P5=P6)134条件判断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)))条件判断5.这里，条件1和条件3返回的结果都是AS4，那么可135条件判断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))条件判断6.仔细看一下公式，还可以将它简化。条件中，当P5=136END条件判断—例1END137条件判断1.这是行政用的一份表格，这里“单价”与“纱支”是对应的。即每一类型的纱支对应一种价格。那么，可以用IF条件函数进行判断。条件判断1.这是行政用的一份表格，这里“单价”与“纱支”是对138条件判断2.在单元格H3中输入公式。如图所示：这里有6种纱支类型，那么对应6种不同的价格。条件判断2.在单元格H3中输入公式。如图所示：这里有6种纱支139条件判断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条件判断3.公式解释:当$C3满足条件1时,返回28.22；140条件判断4.公式输完后,将它拖拽填充就可以了.条件判断4.公式输完后,将它拖拽填充就可以了.141条件判断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",""))))))))

连接符,表示合并.条件判断5.IF函数有七层嵌套的限制,以上已经进行了六次条件142条件判断关于&运算符&（和号）运算符可以代替函数CONCATENATE实现文本项的合并。在这里相当于运用CONCATENATE函数将IF嵌套函数作为它的参数项来合并。由于CONCATENATE可以包含最多30个参数，所以这里&运算符最多也只能连接30个函数公式。条件判断关于&运算符143END条件判断—例2END144筛选１.这是一份Ｄ.Ｏ总表。根据出口编号的不同，需要将“YES总表”分类成“YGS内销总表”，“YGS出口排序”和“WOOL”。一般方法筛选１.这是一份Ｄ.Ｏ总表。根据出口编号的不同，需要将“YE145筛选2.点击“数据”—“自动筛选”，在各字段名称的右侧出现下拉箭头按钮，单击“出口DO编号”右侧的下拉箭头按钮，选取“自定义”。筛选2.点击“数据”—“自动筛选”，在各字段名称的右侧出现下146筛选3.弹出自定义对话框。在“出口DO编号”文本框下输入“等于”“E*”，单击“确定”按钮。筛选3.弹出自定义对话框。在“出口DO编号”文本框下输入“等147筛选4.这样就筛选出“出口DO编号”第一个字母为E的所有记录。然后根据下单日先后进行排序。选择“数据”—“排序”，弹出排序对话框，选择主要关键字“下单日”，“升序”，单击“确定”按钮。筛选4.这样就筛选出“出口DO编号”第一个字母为E的所有记录148筛选5.然后将筛选结果复制到“YGS出口排序”表格中去。根据这种方法，按出口编号的不同，再将“YES总表”分类成“YGS内销总表”，和“WOOL”表。筛选5.然后将筛选结果复制到“YGS出口排序”表格中去。根据149筛选1.要求筛选出“出口DO编号”第一个字母为E的所有记录，返回到“E”这个工作表中。首先，做好数字标记。如图所示：运用公式筛选1.要求筛选出“出口DO编号”第一个字母为E的所有记录，150筛选2.在“E”工作表单元格B4输入如下公式。筛选2.在“E”工作表单元格B4输入如下公式。151筛选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）列的记录，查找方式为精确查找。筛选3.公式解释：如果满足条件的判断。返回VLOOKUP查找152筛选4.这样将公式进行拖拽填充，就完成了记录的筛选。但是此时筛选出的结果比较凌乱，还需要整理一下。筛选4.这样将公式进行拖拽填充，就完成了记录的筛选。但是此时153筛选5.选择区域A3：L1000，再选择“数据”—“排序”，在排序对话框中，主要关键字选“按列B”，“升序”，选择“有标题行”单选框按钮。筛选5.选择区域A3：L1000，再选择“数据”—“排序”，154筛选6.单击“确定”后，就完成了排序。筛选6.单击“确定”后，就完成了排序。155END筛选END156条件格式1.这是船务用的出货排期表。为了清晰的显示出货日期，这里用条件格式来标明颜色。条件格式1.这是船务用的出货排期表。为了清晰的显示出货日期，157条件格式2.如果染单要求出货期已经过了，显示黄色；如果染单要求出货期为当日，显示红色；如果染单要求出货期未到，显示蓝色。条件格式2.如果染单要求出货期已经过了，显示黄色；如果染单要158条件格式3.选择“格式”—“条件格式”命令，打开“条件格式”对话框。输入以下公式。条件格式3.选择“格式”—“条件格式”命令，打开“条件格式”159条件格式4.公式解释：用IF函数进行判断，如果K4为空值，则返回空，否则进行判断，若(TEXT(NOW(),“YYYY-M-D”))-(TEXT(K4,“YYYY-M-D”))=0，则单元格底纹显示红色。表示染单出货期为今日。当前日期日期格式转换为文本条件格式4.公式解释：用IF函数进行判断，如果K4为空值，则160条件格式5.按照这种方法进行判断，若(TEXT(NOW(),“YYYY-M-D”))-(TEXT(K4,“YYYY-M-D”))>0，则单元格底纹显示黄色，表示染单出货期已过；若(TEXT(NOW(),“YYYY-M-D”))-(TEXT(K4,“YYYY-M-D”))<0，则单元格底纹显示蓝色，表示染单出货期未到。条件格式5.按照这种方法进行判断，若(TEXT(NOW(),161条件格式6.将条件格式拖拽进行复制，显示如下效果。条件格式6.将条件格式拖拽进行复制，显示如下效果。162条件格式7.对于实际出货日期，也可以通过标注颜色来识别。若实际出货日期提前或准时，则显示日期字体的颜色为蓝色；若实际出货日期推迟，则显示日期字体的颜色为红色。条件格式7.对于实际出货日期，也可以通过标注颜色来识别。163条件格式8.最后的效果如图。当然你也可以随时根据自己的需要来调整条件格式。条件格式8.最后的效果如图。当然你也可以随时根据自己的需要来164END条件格式END165第二部分宏第二部分166第二部分宏

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

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

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

操作过程1.打开工作表，选择“工具”—“宏”—“录制宏”命令。一.录制宏操作过程170一.录制宏2.在弹出的“录制新宏”对话框中输入宏的名称，定