浅析Excel数据引用函数

浅析Ｅｘｃｅｌ数据引用函数　

赵阳孙向群

（山东省农业管理干部学院，　济南、２５０１００）

摘要：Ｅｘｃｅｌ是办公自动化中的一款重要软件，其功能强大、应用范围广，逐渐被许多行业所应用。在进行“Ｅｘｃｅｌ在会计与财务管理中的应用”课程的教学过程中，　Ｅｘｃｅｌ中的引用函数在Ｅｘｃｅｌ财务应用中使用最频繁。本文采用实际教学中使用的“学生成绩分析表”案例，对Ｅｘｃｅｌ中的引用函数进行了分析，对数据引用所涉及的问题以及问题的解决方法进行了详细的说明，减少函数误用。　

关键字：Ｅｘｃｅｌ函数，数据引用，函数说明　

Excel是办公自动化中的一款重要软件，其功能强大、应用范围广，逐渐被许多行业所应用。我院会计系就开设了“Excel在会计与财务管理中的应用”这门课程。在进行“Excel 在会计与财务管理中的应用”课程的教学过程中，我们发现Excel中的引用函数是Excel财务应用中使用最频繁。因为Excel中的引用函数有多个而且每个函数之间的作用差别不大，学生在实际使用中经常会混淆甚至有时会造成一些因函数使用不当而造成的错误，所以在此对Excel中的引用函数做一个详细的分析和比较，减少函数误用。

一、Excel数据引用基本概念

1、数据引用的作用

单元格的引用是把单元格的数据和公式联系起来，标识工作表中单元格或单元格区域，指明公式中使用数据的位置。通过引用，可以在公式中使用工作表不同单元格中的数据，或者在多个公式中使用同一单元格的数值。还可以引用同一工作簿不同工作表的单元格、不同工作簿的单元格、甚至其它应用程序中的数据。

2、数据引用的含义

Excel单元格的引用有四种方式分别为：

外部引用：不同工作簿中的单元格的引用称为外部引用。

远程引用：引用其它程序中的数据称为远程引用。

相对引用：相对引用是指单元格引用会随公式所在的位置变化而进行改变，公式的值将会依据更改后的单元格地址的值重新计算。

绝对引用：绝对引用是指公式中的单元格或单元格区域地址不随着公式位置的改变而发生改变，不论公式的单元格处在什么位置，公式中所引用的单元格位置都是其在工作表中的确切位置。

3、引用的表示方法

单元格的引用表示方式如表1所示。

表1

引用标识引用的单元格和区域

C2 当前表列C和行2交叉处的单元格

A1:A10 当前表列A和行1到行10之间的单元格区域

B15:E15 当前表行15和列B到列E之间的单元格区域3:3 当前表行3中的全部单元格

2:10 当前表行2到行10之间的全部单元格

B:B 当前表列B中的全部单元格

C:J 当前表列C到列J之间的全部单元格

A10:E20 当前表列A到列E和行10到行20之间的单元格区域

[Book3]Sheet1!B5:H8 工作簿[Book3]中Sheet1工作表的列B到列H和行5到行

8之间的单元格区域

二、Excel查询与引用函数案例分析

在了解了引用的概念后，我们以学生成绩为案例分析Excel中数据引用所涉及的问题。

在学生成绩Excel应用案例中，学生成绩Excel工作簿中共设计了五个表，包括：第一学期成绩表、第二学期成绩表、学生成绩单直接引用例表、学生成绩单查询例表和成绩统计分析表。

1、第一学期成绩表

表结构如图1所示。

图1

该表用于填写每个学生每科成绩，表结构设计为关系型二维表结构，第一行为列名称（课程名）其余各行每行为一个学生的成绩信息。要求：

第一：每个学生的成绩在一行填写，不能跨行。

第二：表中不能有空白行，不能有空白列。

第三：表中需要有一列可以惟一的表示一条记录（主关键字），在此例中使用学号（其他各列都可能存在重复值）来唯一的表示一个学生的成绩。

该表是其余各表引用数据的数据源表。数据源表的设计要符合规则，所以要求学生在设计数据源表时要符合以上3个要求既，表中第一行为列标题、不能有空行和空列、一行表示一条记录、要有一列数据可以惟一的表示一条记录（主关键字）。

第二学期成绩表表设计与第一学期成绩表表设计要求相同。

2、学生成绩单直接引用例表

表结构设计如图2所示。

图2

该表使用的方法为数据直接引用。既在表中需要显示指定数据的单元格中直接输入被引用数据的单元格地址，如图3所示。

图3

直接引用数据的语法为：“=表名!单元格地址”。例如：“=第一学期成绩!C2”。注意，在编辑栏中使用的符号应为英文半角符号，如输入的地址中“!”应为英文半角。

在该方式中，如果被引用单元格内没有数据有时会造成引用单元格内出现错误数据，例如图4所示。

图4

在图4中，左侧数据源表中“体育1”的成绩为空，但在右侧表中引用位置显示为“0”，空和零分的意义不同，此处由于直接引用方式的缺陷而造成错误数据的出现。因此需要使用函数和公式对直接引用方式进行优化。这里使用“ISBLANK”函数和“IF”函数，联合编写优化公式。

“ISBLANK”函数：其功能是判断参数的内容是否为空白，如果为空，则返回TURE 值（真值），否则返回FALSE值（假值）。函数的语法规则为：

ISBLANK(value)

参数value表示需要进行检验的数值或单元格地址。

“IF”函数：其功能是执行真假值判断，根据逻辑计算的真假值，返回不同结果。可以使用“IF”函数对数值和公式进行条件检测。函数的语法规则为：

IF(logical_test,value_if_true,value_if_false)

Logical_test表示计算结果为 TRUE （真）或 FALSE （假）的任意值或公式。value_if_true

表示判断为真值时需要执行的数值或公式，value_if_false表示判断为假值时需要执行的数值或公式。

结合“ISBLANK”函数和“IF”函数对直接引用方式进行优化结果如图5所示。

图5

图5中，在需要引入体育1成绩的单元格内写入公式：=IF(ISBLANK(第一学期成绩!C2),"",第一学期成绩!C2)

其中“ISBLANK”用于判断被引用的单元格是否为空，“IF”函数进行判断输入。如果为空则在引用处输入空值（英文双引号""表示空值）；如果不为空则输入被引用单元格中的数据。优化设计之后的结果如图2所示。

单元格数据直接引用方式公式编写简单易理解，但在进行多单元格数据引用时，公式编辑比较费时并且对编辑人员的仔细程度要求较高；同时直接引用方式不方便复制，如果需要生成新学生的成绩单需要将引用的单元格地址全部重新编写，公式的重复利用率低。

由于直接引用方式存在以上的缺陷，因此采用数据查询的方式对学生成绩单数据表进行重新设计。

3、学生成绩单查询例表

表结构设计如图6所示。

图6

该成绩单表设计为在指定单元格中（图中红圈部分）输入学生的学号，在其后的一行中会显示学生的姓名及各科目成绩。在该表中使用了“VLOOKUP”函数。

“VLOOKUP”函数：其功能是在指定的数据范围中的执行横向查找，按列进行数据查询。“VLOOKUP”函数的语法规则为：

VLOOKUP(lookup_value,table_array,row_index_num,

range_lookup)

Lookup_value为需要在数据表第一列中进行查找的数值。Lookup_value 可以为数值、引用或文本字符串。

Table_array为需要在其中查找数据的数据表。使用对区域或区域名称的引用。

Row_index_num为table_array 中待返回的匹配值的行序号。Row_index_num 为 1 时，返回 table_array 第一列的数值，row_index_num 为 2 时，返回 table_array 第二列的数值，以此类推。如果 row_index_num 小于1，函数“VLOOKUP”返回错误值 #VALUE!；如果 row_index_num 大于 table_array 的列数，函数“VLOOKUP”返回错误值#REF!。

Range_lookup为一逻辑值，指明函数“VLOOKUP”查找时是精确匹配，还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值。也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值。如果 lookup_value 为 FALSE，函数“VLOOKUP”将查找精确匹配值，如果找不到，则返回错误值 #N/A。

在学生成绩单查询例表中，显示的姓名C3单元格内使用的公式为：

=VLOOKUP(B3,第一学期成绩!A:J,2,FALSE)

如图7所示，该公式的含义为：需要查找的数据为学生成绩单查询例表中“B3”单元格(图7左侧红框)内的学号数据即公式中的B3；数据查找的范围是第一学期成绩表中A列到J列（图7右侧红框）即公式中的“第一学期成绩!A:J”；如果找到了对应的学号，就将学号所在行的第二列数据返回即公式中的“2”；公式中的“False”表示精确查找。例如：图7中左侧需要查找的学号为“2010051801204”，在右侧查找范围内找到了该学号，公式将该学号对应行的第二列数据“董玉亭”填入左侧表C3单元格中。

图7

在学生成绩单查询例表中学生成绩的提取公式同上面介绍的学生姓名的提取公式类似，并且对空白数据进行了处理。如图8所示，表中D3单元格使用“IF”函数对空白数据处理后的最终公式为：

=IF(VLOOKUP(B3,第一学期成绩!A:J,3,FALSE),VLOOKUP(B3,第一学期成绩!A:J,3,FALSE),"")

图8

该表中其他单元格中的公式与D3单元格中的公式相类似只是对应的数据提取列不同，所以能将不同列成绩提取到表中。这种采用查询函数生成学生成绩单的方式，使用的函数比直接引用方式难、公式设计复杂，但是该成绩单表一次设计成功之后可以多次使用，表格的重复利用率高。

综合以上直接引用和查询引用两种数据提取的方式，在实际情境教学中，要求所有学生良好掌握直接引用方式进行数据提取，对接受程度较好的学生要求其掌握查询引用方法进行数据提取。

4、成绩统计分析表

表结构设计如图9所示。

图9

成绩统计分析表中考试人数使用“COUNT”函数进行统计。

“COUNT”函数：其功能是返回包含数字以及包含参数列表中的数字的单元格的个数。利用“COUNT”函数可以计算单元格区域或数字数组中数字字段的输入项个数。

以成绩统计分析表中“体育1”考试人数的统计为例，如图10所示，在图10右侧成绩统计分析表B2单元格中使用的公式为：=COUNT(第一学期成绩!C2:C18)。

由公式可知，B2单元格的统计范围是第一学期成绩表中C2到C18单元格，即体育1成绩列。但是通过图10左侧的显示可知，该列中只有5个单元格有数据，因此“COUNT”函数只统计了有数据的单元格个数即有成绩的学生人数也就是考试人数。

图10

成绩统计分析表中最高分使用“MAX”函数统计；统计最低分使用“MIN”函数统计。

“MAX”函数：其功能是返回一组值中的最大值，也用于单元格区域中求最大值。语法为：MAX(range)，参数range代表单元格数据区域。

“MIN”函数：其功能是返回一组值中的最小值。也用于单元格区域中求最小值。语法为：MIN(range)，参数range代表单元格数据区域。

以成绩统计分析表中“体育1”成绩，统计最高分和最低分为例，如图11所示。

图11

在最高分C2单元格使用的公式为：=MAX(第一学期成绩!C2:C18)。

在最低分D2单元格使用的公式为：=MIN(第一学期成绩!C2:C18)。

成绩统计分析表中各门成绩中的及格人数使用“COUNTIF”函数进行统计。

“COUNTIF”函数：功能是用于计算给定区域内满足特定条件的单元格的数目，在进行有条件计数时常使用该函数，特别是指定单个条件时。语法为：COUNTIF(range,criteria) Range 为需要计算其中满足条件的单元格数目的单元格区域。

Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。例如，条件可以表示为 32、"32"、">32" 、"apples" 或 B4。

注意：“COUNTIF”函数只能设置一个条件，在使用数组运算时除外；应用“COUNTIF”函数需要设置条件，设条件的形式可以是数字、表达式或文本。

以成绩统计分析表中“体育1”成绩统计该科目考试及格人数为例，如图12所示。

图12

在图12右侧E2单元格内使用的公式为：=COUNTIF(第一学期成绩!C2:C18,">=60")。由公式可知，该例中“COUNTIF”函数统计的条件是“>=60”,即统计考试成绩在60分以上的学生人数。

成绩统计分析表中各门课成绩的平均分使用“AVERAGE”函数进行统计。

“AVERAGE”函数：其功能是返回一组值的平均值，也用于单元格区域求平均值。语法为：AVERAGE (range)，参数range代表数值或单元格区域。

以成绩统计分析表中“体育1”成绩统计该科目平均成绩为例，如图13所示。

图13

在图13右侧F2单元格内使用的公式为：=AVERAGE(第一学期成绩!C2:C18)。当统计范围没有分数输入时会显示错误结果，因此利用“COUNT”函数和“IF”函数进行优化，优化后的公式为：=IF(COUNT(第一学期成绩!C2:C18)>0,AVERAGE(第一学期成绩!C2:C18),"")。公式说明：首先用“COUNT”函数计算需要算平均分的区域“C2:C18”中有数据的单元格数目是否大于0，如果大于0说明有数据，此时F2单元格内结果为平均分“AVERAGE(第一学期成绩!C2:C18)”；如果不大于0，说明区域内没有数据，此时F2单元格内填入空字符""。

成绩统计分析表中各门课成绩的标准差使用“STDEV”函数进行统计。

“STDEV”函数：其功能是返回一组值的标准偏差值，也用于单元格区域求标准偏差。语法为：STDEV (range)，参数range代表数值或单元格区域。

以成绩统计分析表中“体育1”成绩统计该科目标准偏差为例，如图14所示。

图14

在图14右侧G2单元格内使用的公式为：= STDEV(第一学期成绩!C2:C18)。当统计范围没有分数输入时会显示错误结果，因此同样利用“COUNT”函数和“IF”函数进行优化，优化后的公式为：=IF(COUNT(第一学期成绩!C2:C18)>0, STDEV(第一学期成绩!C2:C18),"")。

综上所述，EXCEL提供了丰富的函数可用于多种要求的数据引用分析。通过使用函数和编写公式，EXCEL可以完成许多实用的数据引用分析工作。同时在主案例中的采用的错误数据优化处理方法同样可以在其他实际情景和案例中使用，并且在主案例中预留部分未进行错误优化处理的单元格公式，学生可以参考已有的优化公式进行模拟练习，从而加深对EXCEL函数和公式认识。

EXCEL的函数大全(完整版)

实用EXCE的函数 1.ADDRESS 用途：以文字形式返回对工作簿中某一单元格的引用。语法：ADDRESS(row_num，column_num，abs_num，a1，sheet_text) 参数：Row_num是单元格引用中使用的行号;Column_num是单元格引用中使用的列标;Abs_num指明返回的引用类型(1或省略为绝对引用，2绝对行号、相对列标，3相对行号、绝对列标，4是相对引用);A1是一个逻辑值，它用来指明是以A1或R1C1返回引用样式。如果A1为TRUE或省略，函数ADDRESS返回A1样式的引用;如果A1为FALSE，函数ADDRESS 返回R1C1样式的引用。Sheet_text为一文本，指明作为外部引用的工作表的名称，如果省略sheet_text，则不使用任何工作表的名称。实例：公式“=ADDRESS(1，4，4，1)”返回D1。 2.AREAS 用途：返回引用中包含的区域个数。语法：AREAS(reference)。参数：Reference是对某一单元格或单元格区域的引用，也可以引用多个区域。注意:如果需要将几个引用指定为一个参数，则必须用括号括起来，以免Excel将逗号作为参数间的分隔符。实例：公式“=AREAS(a2:b4)”返回1，=AREAS((A1:A3，A4:A6，B4:B7，A16:A18))返回4。 3.CHOOSE 用途：可以根据给定的索引值，从多达29个待选参数中选出相应的值或操作。语法：CHOOSE(index_num，value1，value2，...)。参数：Index_num是用来指明待选参数序号的值，它必须是1到29之间的数字、或者是包含数字1到29的公式或单元格引用;value1，value2，...为1到29个数值参数，可以是数字、单元格，已定义的名称、公式、函数或文本。实例：公式“=CHOOSE(2，"电脑"，"爱好者")返回“爱好者”。公式“=SUM(A1:CHOOSE(3，A10，A20，A30))”与公式“=SUM(A1:A30)”等价(因为CHOOSE(3，A10，A20，A30)返回A30)。 4.COLUMN

如何运用公式把一张EXCEL里的数据链接到另一张EXCEL里

在Excel中，一个表格怎样引用另一个表格的数据？在平时的统计中，经常根据需要在Excel中进行一个表格直接引用另一个表格的数据工作；若是把数据直接复制粘贴过来，则数据没有对应的生成功能（也就是若是复制粘贴的数据，原表格的数据变化了，复制粘贴的数据不会变化），若采用以下的步骤来操作，进行数据的引用则可以解决这个统计过程中的小烦恼： 1.首先同时打开两个表格，然后在其中一个表格表1（如：《初三1.2班段考数学成绩》）的某单元输入“=”且使其呈输入状态； 2.在另一个表格表2（如：《2016.中考成绩统计表（参考人数）》）点一下所需要引用的数据； 3.然后回车（必须按回车，这一点很重要！），也可以回到表1按回车. 4.返回表1就可以看到刚才输入“=”的单元格的引用的表2数据； 5.再次单击该单元格，就可以在公式栏看到“=201 6. 中考成绩统计表（参考人数）!B10”；或“=2016. 中考成绩统计表（参考人数）!$B$10”； 6.若在公式里的序号中生成插入了“$’（在同一工作薄中电子表格之间的相互引用则序号不会生成“$”，在不同工作薄中电子表格的相互引用则有“$”）,记住将“$”符号删除掉； 7.将鼠标指向该单元格的右下角，呈黑“+”字型，双击或拖移（可以上下左右拖移）“复制”出其它数据. 值得注意的是： ①.可以向下、向左或整体拖移出其它引用表（表2）相对应．．．的数据；②.引用表表2的数据发生变化，则表1的数据也要发生相应的变化; ③.若关闭或删除或恢复表2，则打开表1会有“更新”还是“不更新”的提示. “更新”就是要随现有的数据的变化而变化，不更新”就是保留原引用的数据；④.删除表2，则表1源单元格不能拖移到其它相应的数据；若移动表2的盘符和文件夹的位置（桌面除外），则要在表1的源单元格公式栏的前面修改盘符和文件夹的实际位置后才能拖移出相应的数据.注意：若关闭表2，则在公式栏会显示盘符、文件夹、文件名和源单元格的序列号等完整的名称（见下面的截图），呈打开且是能引用的状态则在公式栏不会显示. ⑤. 引用只能引用数据，不能引用文本格式.引用时要注意是否有合并和拆分单元格的情况.合并的单元格引用到未合并的单元格，引用时数据会默认到第一个单元格，其余的会显示为0和#VALUE!(见下面的截图)，可以在引用完后再根据表2的格式合并或拆分单元格)；未合并的的单元格不能引用到合并的单元格，一句话要具有“相同的单元格大小”，要直接引用需重新按表2设置成“相同大小”的单元格.

EXCEL引用介绍

EXCEL中相对引用、绝对引用和混合引用三种情况简介 EXCEL里的"$D$5"是什么意思?"$"是什么意思? 在EXCEL中引用单元格分为:相对引用、绝对引用和混合引用三种。是指在公式中使用单元格或单元格区域的地址时，当将公式向旁边复制时，地址是如何变化的。 D5是相对引用 $D$5是绝对引用 $D5和D$5叫作混合引用当复制公式时，有$的引用地址就不会变化，无$的引用地址会变化。具体情况举例说明： 1、相对引用，复制公式时地址跟着发生变化，如C1单元格有公式：=A1+B1 当将公式复制到C2单元格时变为：=A2+B2↓ 当将公式复制到D1单元格时变为：=B1+C1 → 2、绝对引用，复制公式时地址不会跟着发生变化，如C1单元格有公式：=$A$1+$B$1 当将公式复制到C2单元格时仍为：=$A$1+$B$1 ↓ 当将公式复制到D1单元格时仍为：=$A$1+$B$1 → 3、混合引用，复制公式时地址的部分内容跟着发生变化，如C1单元格有公式：=$A1+B$1 当将公式复制到C2单元格时变为：=$A2+B$1 ↓ 当将公式复制到D1单元格时变为：=$A1+C$1 → $代表定位的意思 $D$5就是你复制公式，左拉，右拉上拉下拉，等不会改变， $D5这样你下拉就会变成$D6 D$5你下拉不变，右拉就变成E$5 混合引用: 如$A$1 和$A1 & A$1 到底在操作过程中有什么不同的结果？ $A$1是绝对引用,就是说无论你把它复制到哪个单元格,它的值都是A1的值 $A1 引用A列的数值,A是永远不变的（→）,变的是行也就是1（↓） A$1引用第一行数据,1是永远不变的（↓）,变的是列也就是A（→）横拉就是定行不定列,竖拉就是定列不定行。绝对引用与相对引用的区别：相对引用在创建公式时，单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置。在下面的示例中，单元格B6 包含公式＝A5 ；Microsoft Excel 将在距单元格B6 上面一个单元格和左面一个单元格处的单元格中查找数值，这就是相对引用。在复制包含相对引用的公式时，Excel 将自动调整复制公式中的引用，以便引用相对于当前公式位置的其他单元格。在下面的示例中，单元格B6 中含有公式：=A5，A5 是B6 左上方的单元格，当公式复制至单元格B7 时，其中的公式已经改为=A6，即单元格B7 左上方单元格处的单元格。绝对引用如果在复制公式时不希望Excel 调整引用，那么请使用绝对引用。例如，如果公式将单元格A5 乘以单元格C1 (=A5*C1)，现在将公式复制到另一单元格中，则Excel 将调整公式中的两个引用。可以在不希望改变的引用前加上美元符号（$），这样就能对单元格C1 进行绝对引用。如果要对单元格C1 进行绝对引用，请在公式中加入美元符号：=A5*$C$1

EXCEL中的绝对引用相对引用混合引用全解析

E X C E L中的绝对引用相对引用混合引用全解析 This model paper was revised by the Standardization Office on December 10, 2020

$美元符号在Excel单元格数据区域中，代表的是绝对引用。绝对引用，一般是在函数的参数中使用。那么，当我们使用函数，在填写其参数的数据区域的时候，如何通过Ｆ４快捷键来快速自动的输入绝对引用呢方法如下。比如下表。上表中，我们使用了函数，现在，把ＦＸ输入框的函数公式全部选中。 ①按一次Ｆ４键将变成如下的绝对引用，=SUM($A$1:$B$5)，注意哦，这种形式成为行列的绝对引用。 ②按两次Ｆ４键将变成如下的绝对引用，=SUM(A$1:B$5)，这种形式称为行的绝对引用。 ③按三次Ｆ４键

将变成如下的绝对引用，=SUM($A1:$B5)，这种形式成为列的绝对引用。如果按下第四次Ｆ４键，将变成普通的引用。如此循环。用法的要点就是，选择整个公式或者函数中的某个数据区域或某个单元格的引用区域，按下Ｆ４键不断进行调换就行了。知识扩展：Excel中单元格引用的知识 ①相对引用公式中的相对单元格引用（例如 A1）是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。默认情况下，新公式使用相对引用。例如，如果将单元格 B2 中的相对引用复制到单元格 B3，将自动从 =A1 调整到 =A2。 ②绝对引用

单元格中的绝对单元格引用（例如＄A＄1）总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。如果多行或多列地复制公式，绝对引用将不作调整。默认情况下，新公式使用相对引用，需要将它们转换为绝对引用。例如，如果将单元格 B2 中的绝对引用复制到单元格 B3，则在两个单元格中一样，都是＄A＄1。 ③混合引用混合引用具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用＄A1、＄B1 等形式。绝对引用行采用 A＄1、B＄1 等形式。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。如果多行或多列地复制公式，相对引用自动调整，而绝对引用不作调整。例如，如果将一个混合引用从 A2 复制到 B3，它将从 =A＄1 调整到 =B＄1。

5-Excel中三个查找引用函数的用法(十分有用)

Excel中三个查找引用函数的用法(十分有用) 在Excel中，我们经常会需要从某些工作表中查询有关的数据复制到另一个工作表中。比如我们需要把学生几次考试成绩从不同的工作表中汇总到一个新的工作表中，而这几个工作表中的参考人数及排列顺序是不完全相同的，并不能直接复制粘贴。此时，如果使用Excel的VLOOKUP、INDEX或者OFFSET函数就可以使这个问题变得非常简单。我们以Excel 2007为例。图1 假定各成绩工作表如图1所示。B列为姓名，需要汇总的项目“总分”及“名次”位于H列和I列（即从B列开始的第7列和第8列）。而汇总表则如图2所示，A 列为姓名列，C、D两列分别为要汇总过来的第一次考试成绩的总分和名次。其它各次成绩依次向后排列。图2

一、VLOOKUP函数我们可以在“综合”工作表的C3单元格输入公式“=VLOOKUP($B3,第1次!$B$1:$I$92,7,FALSE)”，回车后就可以将第一位同学第一次考试的总分汇总过来了。把C3单元格公式复制到D3单元格，并将公式中第三个参数“7”改成“8”，回车后，就可以得到该同学第一次考试名次。选中C3:D3这两个单元格，向下拖动填充句柄到最后就可以得到全部同学的总分及名次了。是不是很简单呀？如图3所示。 VLOOKUP函数的用法是这样的：VLOOKUP(参数1,参数2,参数3,参数4)。“参数1”是“要查找谁？”本例中B3单元格，那就是要查找B3单元格中显示的人名。“参数2”是“在哪里查找？”本例中“第1次!$B$1:$I$92”就是告诉Excel在“第1次”工作表的B1:I92单元格区域进行查找。“参数3”是“找第几列的数据？”本例中的“7”就是指从“第1次”工作表的B列开始起，第7列的数据，即H列。本例中“参数4”即“FALSE”是指查询方式为只查询精确匹配值。该公式先在“第1次”工作表的B!:I92单元格区域的第一列（即B1:B92单元格区域）查找B3单元格数据，找到后，返回该数据所在行从B列起第7列（H列）的数据。所以，将参数3改成“8”以后，则可以返回I列的数据。由此可以看出，使用VLOOKUP函数时，参数1的数据必须在参数2区域的第一列中。否则是不可以查找的。

EXCEL分析工具库教程

EXCEL分析工具库教程第一节：分析工具库概述 “分析工具库”实际上是一个外部宏（程序）模块，它专门为用户提供一些高级统计函数和实用的数据分析工具。利用数据分析工具库可以构造反映数据分布的直方图；可以从数据集合中随机抽样，获得样本的统计测度；可以进行时间数列分析和回归分析；可以对数据进行傅立叶变换和其他变换等。本讲义均在Excel2007环境下进行操作。 1.1. 分析工具库的加载与调用打开一张Excel表单，选择“数据”选项卡，看最右边的“分析”选项中是否有“数据分析”，若没有，单击左上角的图标，单击最下面的“E xcel选项”，弹出“Excel选项”对话框，在左侧列表中选择“加载项”，在下方有“管理：Excel加载项转到”，单击“转到”，勾选“分析工具库”（加载数据分析工具）和“分析工具库-VBA”（加载分析工具库所需要的VBA函数）（图 1-1），单击确定，则“数据分析”出现在“数据｜分析”中。图 1-1 加载分析工具库

1.2. 分析工具库的功能分类分析工具库内置了19个模块，可以分为以下几大类：表 1-1 随机发生器功能列表第二节．随机数发生器重庆三峡学院关文忠 1.随机数发生器主要功能 “随机数发生器”分析工具可用几个分布之一产生的独立随机数来填充某个区域。可以通过概率分布来表示总体中的主体特征。例如，可以使用正态分布来表示人体身高的总体特征，或者使用双值输出的伯努利分布来表示掷币实验结果的总体特征。 2.随机数发生器对话框简介

执行如下命令：“数据｜分析｜数据分析｜随机数发生器”，弹出随机数发生器对话框（图2-1）。图2-1随机数发生器对话框该对话框中的参数随分布的选择而有所不同，其余均相同。变量个数：在此输入输出表中数值列的个数。随机数个数：在此输入要查看的数据点个数。每一个数据点出现在输出表的一行中。分布：在此单击用于创建随机数的分布方法。包括以下几种：均匀分布、正态分布、伯努利分布、二项式、泊松、模式、离散。具体应用将在第3部分举例介绍。随机数基数：在此输入用来产生随机数的可选数值。可在以后重新使用该数值来生成相同的随机数。输出区域：在此输入对输出表左上角单元格的引用。如果输出表将替换现有数据，Excel 会自动确定输出区域的大小并显示一条消息。新工作表：单击此选项可在当前工作簿中插入新工作表，并从新工作表的A1单元格开始粘贴计算结果。若要为新工作表命名，请在框中键入名称。新工作簿：单击此选项可创建新工作簿并将结果添加到其中的新工作表中。 3.随机数发生器应用举例

EXCEL中引用单元格内容

EXCEL中引用单元格内容（以文本公式表达的,没有“=”号）并直接得出结果问题：如何对上图实例中A列单元格内以文本（没有“=”）表达的公式进行快速计算，将得出的结果填列在B列？！一般人，包括我在内，碰到这个问题，会做如下两种偿试：（1）在B1中直接输入：=A1，但，得到的结果仍然是调用了A1的表达式，无法求得结果。此时，B1 中的公式"=A1"，只是引用地址，"=A1"也只是调用单元格A1的内容。（2）在B1中输入：=(=A1)，理想当中，就是相当于输入了“=5+6+7+8 ”这个公式的实际内容？，其实不然，结果让自己大失所望：excel将认为它是错误的输入。其实，你或许知道上述两种方法显然是不可行的，但是因为没有其他办法，所以你会去偿试，结果就是确定不能行！没有办法的办法就是：将A列数据复制粘贴到B列，然后在单元格文本表达式前输入“=”，逐个单元格进行操作。如B1单元格内容为“=5+6+7+8”，作为一个独立的计算公式，在输入“=”后，即可显示结果26。但如果内容很多，你就必须花费很多时间进行操作........... 基于上述疑惑，我从百度上找寻解决方案，发现这是个“疑难问题”，无法运用常规函数解决？！给出的最佳答案都是使用了一个叫做EVALUATE的宏表函数（可以说是一个宏，点击查看详情>>>>），结合我对电子表格应用的掌握，整理该问题解决方案如下：

方法一：首先，对于这个疑难问题，网上最多的答案，就是单元格“定义”法：先左键选中B1 ，点击菜单命令“插入－名称－定义”，弹出“定义名称对话框：上面一行“在当前工作簿中的名称”录入“B ”者是其他什么内容，可以是字母或是文字；下面一行“引用位置”录入“=EVALUATE(A2) ”，点击“确定”退出对话框。然后，在B1中输入“=B”，向下拉动单元格，填充需要计算的区域，即可得到结果。方法二：对“没有办法的办法”进行改进，具体可如下操作：在B1单元格录入公式：="="&A1，可以得到显示结果为=5+6+7+8，向下拉动单元格，填充需要计算的区域，但这个结果也只是一个文本表达式，只是前面加上了“= ”，实际上是两个符号“= ” 与“5+6+7+8 ”的一个组合（& ，文本连接符），如下图所示：接下来，选定并复制B列，同样选定B列，进行“选择性粘贴”为“数值”，此时，B1里的内容就不再是公式，而是=5+6+7+8 ，虽然B列单元格格式已经设置为“数值”了，但此时，B1仍未显示结果，可进行操作：执行ctrl+h或（ctrl+f），调出查找与替换窗口，直接把“=”替换一次“=”就会重算了，即可显示结果！

如何在Excel中引用其它位置的数据

如何在Excel中引用其它位置的数据在使用Excel电子表格时，有时会引用其它表格中的数据。引用其它表格中数据的情况有三种，第一种：引用同一工作薄（即同一个文件）中其它工作表中的数据；第二种：引用同一文件夹下其它工作薄中的数据；第三种：引用不同文件夹下其它工作薄中的数据。本文介绍在这三种情况下引用Excel中的数据。本文以在“总数据”工作薄中引用“原数据1”和“原数据2”中的数据为例来讲解。第一种情况：引用同一工作薄中其它工作表中的数据。如：在“原数据1”工作薄Sheet2工作表C4单元格中引用Sheet1工作表中C4单元格的数据。在Sheet2工作表C4单元格中输入如下公式：=Sheet1!C4，按回车键，即可显示出来结果。引用前引用后第二种情况：引用同一文件夹下不同工作薄中的数据。如：在“总数据”工作薄Sheet1工作表C4单元格中引用“原数据1”和“原数据2”工作薄Sheet1工作表C4单元格中的数据之和。

在“总数据”工作薄Sheet1工作表C4单元格中输入公式：=’[原数据1.xls]Sheet1’!C4+’[原数据2.xls]Sheet1’!C4，按回车键，即可。引用其它工作薄时要带文件类型名称，即“.xls”，且引号为英文状态下的单引号“’”。第三种情况：引用不同文件夹下工作薄中的数据。把“原数据2.xls”文件放到其它目录中，如：“F:\书籍”文件夹下。如：在“总数据”工作薄Sheet1工作表E6单元格中引用“F:\书籍”文件夹下“原数据2”工作薄Sheet1工作表E6单元格中的数据。在“总数据”工作薄Sheet1工作表E6单元格中输入公式：=’ F:\书籍\[原数据2.xls]Sheet1’!E6，按回车键即可。后记：

EXCEL中查找函数的妙用

EXCEL中查找函数的妙用作为一个电子表格处理程序,EXCEL除了具有强大的计算和汇总功能外,其内嵌的各类功能函数在进行表格处理中给我们提供了极大的便利,可以极高的提高工作效率和准确性。下面介绍几个在实际工作中可能会经常用到的查找函数:MATCH()、LOOKUP()、HLOOKUP()、VLOOKUP(),这些查找函数不仅仅具有查对的功能,同时还能根据查找的结果和参数的设定得到我们需要的数值。特别是这几个函数的配合使用,并以两个逻辑函数IF()和ISERROR()辅助,我们就可以在两个或多个有一定关联的工作簿中动态生成新的数据列。 1.MATCH() 格式:MATCH(lookup_value,lookup_array,match_type) 从英文含义我们可以看出,它是一个匹配的函数,主要功能是按照match_type参数所指定的查找方式,在lookup_array参数所代表的数据组中查找lookup_value参数代表的数值,如果查找成功则返回lookup_value在lookup_array中的位置,不成功返回错误信息#N/A。 EXCEL在“编辑”菜单中提供的“查找”功能只能进行单一数值的查找,而MATCH()函数可以实现批量数值的查找,所需要的只是在需要查找的首个单元格中定义好该函数,然后拖动该单元格直至到需要的范围,所有需要查对单元格的查找结果会自动完成。参数解释: lookup_value——需要查找的值,可以是实际的数字、字符串或逻辑值,也可以是某一单元格的引用,通常使用单元格的引用; lookup_array——查找范围,可以是定义的数组,也可以是自定义的多个连续单元格的引用,这个引用我们可以通过两种方式实现: ?直接输入连续单元格的地址,通常使用绝对引用地址,如$B$2:$B$50; ?选定一个连续的单元格区域后,通过“插入”菜单中“定义”项的“名称”给这段单元格区域定义一个名称,如NAME1,这样在以后所有使用该单元格区域的时候都可以用定义的名称代表。 match_type,查找的方式,其值为-1、0、1,默认为1。其中-1是在lookup_array查找大于或等于lookup_value的最小数值,要求lookup_array必须按降序排列;0是在lookup_array查找等于lookup_value的第一个数值,不需要排序;1是在lookup_array 查找小于或等于lookup_value的最大数值,要求lookup_array必须按升序排列。函数举例: 例1:有两个工作簿 BOOK1和BOOK2,它们的SHEET1中都具有名为“卡片号”的列,列标为B(假设BOOK1中该列包含数据的单元格为B2:B80,BOOK2中包含数据的单元格为 B2:B1000),想查对一下BOOK1中的卡片号是否能够在BOOK2中的卡片号中全部查找到。

excel统计分析工具

excel统计分析工具 Microsoft Excel 提供了一组数据分析工具，称为“分析工具库”，在建立复杂统计或工程分析时可节省步骤。只需为每一个分析工具提供必要的数据和参数，该工具就会使用适当的统计或工程宏函数，在输出表格中显示相应的结果。其中有些工具在生成输出表格时还能同时生成图表。相关的工作表函数 Excel 还提供了许多其他统计、财务和工程工作表函数。某些统计函数是内置函数，而其他函数只有在安装了“分析工具库”之后才能使用。访问数据分析工具“分析工具库”包括下述工具。要使用这些工具，请单击“工具”菜单上的“数据分析”。如果没有显示“数据分析”命令，则需要加载“分析工具库”加载项（加载项：为 Microsoft Office 提供自定义命令或自定义功能的补充程序。）程序。方差分析方差分析工具提供了几种方差分析工具。具体使用哪一种工具则根据因素的个数以及待检验样本总体中所含样本的个数而定。方差分析：单因素此工具可对两个或更多样本的数据执行简单的方差分析。此分析可提供一种假设测试，该假设的内容是：每个样本都取自相同基础概率分布，而不是对所有样本来说基础概率分布都不相同。如果只有两个样本，则工作表函数 TTEST 可被平等使用。如果有两个以上样本，则没有合适的 TTEST 归纳和“单因素方差分析”模型可被调用。方差分析：包含重复的双因素此分析工具可用于当数据按照二维进行分类时的情况。例如，在测量植物高度的实验中，植物可能使用不同品牌的化肥（例如 A、B 和 C），并且也可能放在不同温度的环境中（例如高和低）。对于这 6 对可能的组合 {化肥，温度}，我们有相同数量的植物高度观察值。使用此方差分析工具，我们可检验： 1.使用不同品牌化肥的植物的高度是否取自相同的基础总体；在此分析中，温度可以被忽略。 2.不同温度下的植物的高度是否取自相同的基础总体；在此分析中，化肥可以被忽略。 3.是否考虑到在第 1 步中发现的不同品牌化肥之间的差异以及第 2 步中不同温度之间差异的影响，代表所有 {化肥，温度} 值的 6 个样本取自相同的样本总体。另一种假设是仅基于化肥或温度来说，这些差异会对特定的 {化肥，温度} 值有影响。

如何在word文档中引用Excel表格数据wk

如何在word文档中引用Excel表格数据（数字、文字等）在我们的日常工作中，有的工作性质需要有大量的数据需要整理，比如，有几百人的报名表需要做成文档打印，如果要做几百份不同人名的word文档格式报名表，需要一项一项的手工输入或从Excel表格中复制粘贴姓名、出生年月数据等肯定会太麻烦，浪费时间，最重要的是容易出现错误，下面亲自分享一种快捷、准确的方法。即Word文档，引用Excel表格数据，生成单独的文档或打印 1、准备一份数据源，如含有姓名、性别、民族、出生年月日的Excel表格；如下图： 2需要填写打印的报名表：需要填写内容如下图荧光色标注：

可以看出，每一份报名表都是不同的信息，如姓名、拼音，出生年月日、民族国籍等等，如果一个一个的输入或者复制粘贴，数量十几个倒不太大工作量，如果有几百人，那么工作量就很大，工作效率就会很低，还可能容易出错，数据更新之后，还要重新输入。下面就开始介绍一种超级实用方便的方式来取巧 -----利用word文档的邮件合并功能！ 3、打开word文档，工具栏——邮件——选择收件人——使用现有列表，按提示选择并打开Excel表格数据源：

4、选择excel表含有数据的sheet1，确定，然后再点击“编辑收件人列表”

弹出窗口，将数据的第一行标题等非输入数据的√取消，剩下需要引用的数据行、列 5、鼠标先点击word文档的“姓名”处，再点击“插入合并域”，选择F2,即excel表中姓名那一列数据。

随后，姓名处会显示《F2》字样，表示准备引用。 6、其他栏的“姓名拼音”、“出生年月日”，性别、国籍、民族同样的方法依此类推选择：

excel中多条件查找的5种方法

EXCEL中多条件查找的15种方法 excel多条件查找的思路很多，例如查找、求和、最值、数据库等函数等等。像SUM函数、lookup函数、VLOOKUP+CHOOSE函数、OFFSET+MATCH函数、INDIRECT+MATCH函数等等如下所示：示例：题目：如下图所示，根据第9行的产品和型号，从上面表中查找“销售数量”，结果如C10所示 SUM函数：公式{=SUM((A2:A6=A9)*(B2:B6=B9)*C2:C6)} 公式简介：使用(条件)*（条件)因为每行符合条件的为0，不符合的为1，所以只有条件都符合的为非零数字。所以SUM求和后就是多条件查找的结果 SUMPRODUCT函数：公式：=SUMPRODUCT((A2:A6=A9)*(B2:B6=B9)*C2:C6) 公式简介：和SUM函数用法差不多，只是SUMPRODUCT函数不需要数组运算 MAX函数： {=MAX((A2:A6=A9)*(B2:B6=B9)*C2:C6)} SUM是通过求和把符合条件的提出来，这里是使用MAX提取出最大值来完成符合条件的值提取。 lookup函数：公式1=LOOKUP(A9&B9,A2:A6&B2:B6,C2:C6)

公式简介：LOOKUP函数可以直接进行数组运算。查找的连接起来，被查找区域也连接起来。公式2：=LOOKUP(1,0/((A2:A6=A9)*(B2:B6=B9)),C2:C6) 公式3=LOOKUP(1,0/((A2:A6&B2:B6)=(A9&B9)),C2:C6) 公式4=LOOKUP(1,1/(((A2:A6=A9)+(B2:B6=B9))=2),C2:C6) MIN+IF函数：公式=MIN(IF((A2:A6=A9)*(B2:B6=B9),C2:C6)) SUM+IF函数：公式 =SUM(IF(A2:A6=A9,IF(B2:B6=B9,C2:C6,0),0)) INDEX+MATCH函数组合：公式1：{=INDEX(C2:C6,MATCH(A9&B9,A2:A6&B2:B6,0))} 公式2：{=INDEX(C2:C6,MATCH(1,(A9=A2:A6)*(B9=B2:B6),0))} OFFSET+MATCH函数：公式 =OFFSET(C1,MATCH(A9&B9,A2:A6&B2:B6,0),) INDIRECT+MATCH函数：公式 =INDIRECT("C"&MATCH(A9&B9,A1:A6&B1:B6,0)) VLOOKUP+CHOOSE函数：公式： =VLOOKUP(A9&B9,CHOOSE({1,2},A2:A6&B2:B6,B2:C6),2,0) HLOOKUP+TRANSPOSE+CHOOSE函数：公式 =HLOOKUP(A9&B9,TRANSPOSE(CHOOSE({1,2},A2:A6&B2:B6,B2:C6)),2,0 ) VLOOKUP+IF函数：公式1 =VLOOKUP(A9&B9,IF({1,0},A2:A6&B2:B6,B2:C6),2,0)

Excel的统计分析功能

Excel的统计分析功能 Excel是办公自动化中非常重要的一款软件，很多巨型国际企业和国内行政、企事业单位都用Excel 进行数据管理。它不仅能够方便地进行图形分析和表格处理，其更强大的功能还体现在数据的统计分析研究方面。然而很多缺少数理统计基础知识而对Excel强大统计分析功能不够了解的人却难以更加深入、更高层次地运用Excel。笔者认为，对Excel统计分析功能的不了解正是阻挡普通用户完全掌握Excel的拦路虎，但目前这方面的教学文章却又很少见。下面笔者对Excel的统计分析功能进行简单的介绍，希望能够对Excel进阶者有所帮助。 Microsoft Excel提供了一组数据分析工具，称为“分析工具库”，在建立复杂统计或工程分析时，只需为每一个分析工具提供必要的数据和参数，该工具就会使用适宜的统计或工程函数，在输出表格中显示相应的结果。其中有些工具在生成输出表格时还能同时生成图表。在使用Excel的“分析工具库”时，如果“工具”菜单中没有“数据分析”命令，则需要安装“分析工具库”。步骤如下：在“工具”菜单中，单击“加载宏”命令，选中“分析工具库”复选框完成安装。如果“加载宏”对话框中没有“分析工具库”，请单击“浏览”按钮，定位到“分析工具库”加载宏文件“Analys32.xll”所在的驱动器和文件夹（通常位于“Microsoft Office\Office\Library\Analysis”文件夹中）（Microsoft OfficeXP：插入光盘，即可）；如果没有找到该文件，应运行“安装”程序。安装完“分析工具库”后，要查看可用的分析工具，请单击“工具”菜单中的“数据分析”命令，Excel提供了以下15种分析工具。 1、方差分析(anova) 本工具提供了三种工具，可用来分析方差。具体使用哪一工具则根据因素的个数以及待检验样本总体中所含样本的个数而定。（1）“Anova：单因素方差分析”分析工具此分析工具通过简单的方差分析(anova)，对两个以上样本均值进行相等性假设检验（抽样取自具有相同均值的样本空间）。此方法是对双均值检验（如t-检验）的扩充。（2）“Anova：可重复双因素分析”分析工具此分析工具是对单因素anova分析的扩展，即每一组数据包含不止一个样本。（3）“Anova：无重复双因素分析”分析工具此分析工具通过双因素anova分析（但每组数据只包含一个样本），对两个以上样本均值进行相等性假设检验（抽样取自具有相同均值的样本空间）。此方法是对双均值检验（如t-检验）的扩充。 2、相关系数分析工具此分析工具及其公式可用于判断两组数据集（可以使用不同的度量单位）之间的关系。总体相关性计算的返回值为两组数据集的协方差除以它们标准偏差的乘积：可以使用“相关系数”分析工具来确定两个区域中数据的变化是否相关，即，一个集合的较大数据是否与另一个集合的较大数据相对应（正相关）；或者一个集合的较小数据是否与另一个集合的较小数据相对应（负相关）；还是两个集合中的数据互不相关（相关性为零）。 3、协方差分析工具此分析工具及其公式用于返回各数据点的一对均值偏差之间的乘积的平均值。协方差是测量两组数据相关性的量度。（公式略）可以使用协方差工具来确定两个区域中数据的变化是否相关，即，一个集合的较大数据是否与另一个

EXCEL引用数据方法

excel数据引用公式的使用方法 excel数据引用分为四种，我给大家简单介绍下： 1、excel数据相对引用。这种最为简单，通常大家都知道，就是普通的公式计算。简单介绍几个公式，相信大家都很清楚。 1）、在d2单元格中需要计算：数据一和数据二的平均数与数据三的差。就可以输入 =(A2+B2)/2-C2 或者输入 =sum(A2:B2)/2-C2 其中，括号必须是英文状态或者是半角中文状态，其中的列号ABC输入大小写没有关系，都会自动变成大写。如果求和的数据过多则不要用加号了，用sum求和函数较为方便。（求括号内英文冒号两侧的单元格之间的数据之和）sum函数不但可以求横向的行之和、竖向的列之和，还可以求对角线所辖区域的矩形区域之和。 2）、计算：数据一、数据二、数据三的最大值与最小值的差，可以输入： =max(a2:d2)-min(a2:d2) 其中，max是求最大值函数，min是求最小值函数。和sum一样，这两个函数都支持对角线所辖区域矩形的数据运算。 3）、计算：如果数据一大于1500，则综合数据是数据一、数据二、数据三求和，若数据一介于1000至1500之间则计算数据一和数据二之和，若数据一低于1000，则只记录数据一的结果。（这个例子在公司计算工资的时候很常用，但是要非常小心边界的判断，需要的时候可以用>=表示大于等于，用<=来表示小于等于）关于这个if判断语句的写法要非常小心括号的运用，我们可以在有图早期的一篇个人所得数计算公式中看到多次嵌套的运用。 =IF(A2>1500,SUM(A2:C2),IF((A2<1000),A2,SUM(A2:B2)))

这三个例子主要是介绍大家常见的三种模式：直接运算，用excel的函数进行运算，用程序语句进行运算。（这种提法是我自己随便想的，不一定有真正的三中模式之说。） 2、excel数据的绝对引用。参加了办公自动化培训班的朋友和自学excel的朋友应该对他不陌生。举例说明一下，还以上面的那些数据为例：计算数据一中每个数据与最大值的差。（呵呵，也很常用的，就是计算平级的人或者部门之间的差距）我们一般会在d2单元格输入 =MAX(A2:A6)-A2 可是我们无法通过拖动，生成但d3、d4、d5等下面单元格的数据，当我们拖动的时候，在d3单元格变成了 =MAX(A3:A7)-A3 这显然不是我们要的，我们在d3单元格想要的是 =MAX(A2:A6)-A3 所以，我们可以使用$来固定那些需要不变的数据，如上面a2单元格的公式就可以写成 =MAX(A$2:A$6)-A2 随你怎样拖动，max计算的区域就不会变化了。如果你想固定的更牢靠一点，可以在列标号前加$。 3、同文件内excel表间引用。同文件内不同表之间的数据引用和计算通常是使用英文状态的引号表名结合而来的，例如本例中：

excel怎样引用其他工作薄中的数据

excel怎样引用其他工作薄中的数据在公式中，用户除了可以引用当前工作表的单元格数据外，还可以引用其他工作薄中的数据。 1.如图：需要将“十二月份工资”工作薄中的“工资”列的数值与“年终奖励”工作薄中的“奖励金额”列的数值相加，并显示在“年终奖励”工作薄中的“应发工资”列。 2.“年终奖励”工作薄中，选择C2单元格，输入“=B2+”，不要按回车确认。

3.再点“十二月份工资”工作薄中B2单元格进行选择，选择后，“年终奖励”工作薄中C2会自动写入公式“=B2+[十二月份工资.xls]Sheet1!$B$2”。这个公式也可以直接在“年终奖励”工作薄的C2单元格中输入，不过“[”、“]”不方便输入，所以建议使用平铺窗口浏览，同一界面显示打开的所以工作薄。公式表示：当前工作表中的B2单元格内的数值+“十二月份工资.xls”工作薄中 “Sheet1”工作表中B2单元格内的数值引用其他工作薄的数据，格式为：[工作薄名称]工作表名称！单元格地址

4.输入公式后按回车，显示结果。 5.因为按上述方式输入的公式为绝对引用，如果直接复制C2单元格填充至C3： C5，计算会出错，所以完成C3：C5区域的计算有以下两个办法：一、用第二步到第四步的步骤完成C3：C5区域的计算；二、左键双击C2单元格，删除公式中的两个“$”，公式成为“=B2+[十二月份工资.xls]Sheet1!B2”后是相对引用，再复制C2单元格填充至C3：C5即可。

注意事项在引用其他工作薄中的数据时，所引用的工作薄必须打开，如果引用的工作薄没有打开，则必须给出所引用工作薄的完整路径，否则将不能计算出正确的结果。

EXCEL中查找函数的妙用

EXCEL中查找函数的妙用（巨好用的公式）作为一个电子表格处理程序,EXCEL除了具有强大的计算和汇总功能外,其内嵌的各类功能函数在进行表格处理中给我们提供了极大的便利,可以极高的提高工作效率和准确性。下面介绍几个在实际工作中可能会经常用到的查找函数:MATCH()、LOOKUP()、HLOOKUP()、VLOOKUP(),这些查找函数不仅仅具有查对的功能,同时还能根据查找的结果和参数的设定得到我们需要的数值。特别是这几个函数的配合使用,并以两个逻辑函数IF()和ISERROR()辅助,我们就可以在两个或多个有一定关联的工作簿中动态生成新的数据列。 1.MATCH() 格式:MATCH(lookup_value,lookup_array,match_type) 从英文含义我们可以看出,它是一个匹配的函数,主要功能是按照match_type参数所指定的查找方式,在lookup_array参数所代表的数据组中查找 lookup_value参数代表的数值,如果查找成功则返回lookup_value在 lookup_array中的位置,不成功返回错误信息#N/A。 EXCEL在“编辑”菜单中提供的“查找”功能只能进行单一数值的查找,而MATCH()函数可以实现批量数值的查找,所需要的只是在需要查找的首个单元格中定义好该函数,然后拖动该单元格直至到需要的范围,所有需要查对单元格的查找结果会自动完成。参数解释: lookup_value——需要查找的值,可以是实际的数字、字符串或逻辑值,也可以是某一单元格的引用,通常使用单元格的引用; lookup_array——查找范围,可以是定义的数组,也可以是自定义的多个连续单元格的引用,这个引用我们可以通过两种方式实现: ? 直接输入连续单元格的地址,通常使用绝对引用地址,如$B$2:$B$50; ? 选定一个连续的单元格区域后,通过“插入”菜单中“定义”项的“名称”给这段单元格区域定义一个名称,如NAME1,这样在以后所有使用该单元格区域的时候都可以用定义的名称代表。

Excel公式中引用地址方式简介

Excel公式中引用地址方式简介在Excel公式与函数中，经常引用单元格或单元格区域。单元格或单元格区域的引用有相对引用、绝对引用和混合引用三种。相对引用、绝对引用和混合引用的区别是，在公式中使用单元格或单元格区域的地址时，当复制公式时，地址的变化不同。具体情况举例说明：1．相对引用：复制公式时地址跟着发生变化，如： C1单元格中有公式：=A1+B1 当将公式向下复制时，到C2单元格时公式变为：=A2+B2，即行号随单元格行的改变而改变。当将公式向右复制到D1单元格时变为：=B1+C1，即列标随单元格列的改变而改变。可见相对引用时，公式中的地址随单元格行与列的改变而改变。 2．绝对引用：复制公式时地址不会跟着发生变化，如： C1单元格中有公式：=$A$1+$B$1 当将公式复制到C2单元格时仍为：=$A$1+$B$1 当将公式复制到D1单元格时仍为：=$A$1+$B$1 3．混合引用：复制公式时地址的部分内容跟着发生变化，如： C1单元格有公式：=$A1+B$1 当将公式复制到C2单元格时变为：=$A2+B$1，即公式中的A1单元格中的列标A不变，行号1改变为2；B1单元格中的行号1不变。当将公式复制到D1单元格时变为：=$A1+C$1，即公式中的A1单元格中的列标A不变；B1单元格中的列标B改变为列标C，行号1不变。规律：加上了绝对引用符“$”的列标和行号为绝对地址，在公式向旁边复制时不会发生变化，没有加上绝对地址符号的列标和行号为相对地址，在公式向旁边复制时会跟着发生变化。混合引用时部分地址发生变化。下面利用Excel中具体的例子比较一下“C4”“$C4”“C$4”和“$C$4”之间的区别。在一个工作表中，C4、C5中的数据分别是60、50，D4、D5中的数据分别是70、80。如果在E4单元格中输入“=C4”，那么将E4向下拖动到E5时，E5中的内容就变成了50，里面的公式是“=C5”，将E4向右拖动到F4，F4中的内容是70，里面的公式变成了“=D4”。如果把E4复制到F5，F5中的内容是80，里面的公式变成了“=D5”。图1 相对引用

工作中最常用的excel函数公式大全

工作中最常用的excel函数公式大全一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空公式：C2 =IFERROR(A2/B2,"") 说明：如果是错误值则显示为空，否则正常显示。

2、IF多条件判断返回值公式：C2 =IF(AND(A2<500,B2="未到期"),"补款","") 说明：两个条件同时成立用AND,任一个成立用OR函数。三、统计公式 1、统计两个表格重复的内容公式:B2 =COUNTIF(Sheet15!A:A,A2) 说明：如果返回值大于0说明在另一个表中存在，0则不存在。

2、统计不重复的总人数公式：C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。四、求和公式

1、隔列求和公式：H3 =SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)说明：如果标题行没有规则用第2个公式 2、单条件求和公式：F2 =SUMIF(A:A,E2,C:C) 说明：SUMIF函数的基本用法

3、单条件模糊求和公式：详见下图说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。

4、多条件模糊求和公式：C11 =SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 说明：在sumifs中可以使用通配符* 5、多表相同位置求和公式：b2 =SUM(Sheet1:Sheet19!B2) 说明：在表中间删除或添加表后，公式结果会自动更新。 6、按日期和产品求和