三个Excel表中如何表格关联怎么设置公式计算？

来源：蜘蛛抓取(WebSpider) 时间：2022-10-23 15:32 标签： 表格关联怎么设置公式

以计算距国庆日期为例：

1、在F5单元格输入公式：E5-D5

2、在F5单元格单击右键，在菜单中选择：设置单元格格式...

这时会跳出单元格设置对话框

　　vlookup函数是Excel中最常用的函数之一，大家利用这个函数可以快速的查找到指定值所对应的另一个值，当然vlookup函数的用法不仅仅只有一种，也有很多朋友不清楚excel表格vlookup怎么使用，所以下面小编就给大家带来了vlookup函数的几种使用方法，感兴趣的朋友快来看看吧。

WPS Office 是金山软件股份有限公司出品的一款全面兼容微软Office格式的办公软件。WPS Office2019个人免费是办公必备的软件，软件提供强大的文字编辑处理功能，最常用的文字、表格、演示等多种功能都具备，你可以处理了各种各样的文档，体积很小，下载速度很快，安装也很快捷，有需要的用户欢迎下载！

　　常规方法相信大家都非常的熟悉，在这里我们想要查找西瓜的销售额，只需要将公式设置为：=VLOOKUP(E2,A2:C8,3,0)即可，这样的话就能查找想要的结果。

　　2、核对两列顺序错乱数据

　　如下图，我们想要核对顺序错乱的数据，只需要将公式设置为：=E4-VLOOKUP(D4,$A$3:$B$9,2,0)，在这里如果结果不是0，就是差异的数据。

　　它其实利用的也是vlookup的常规用法，将表1的考核得分引用到表2中，然后再用表2的考核得分减一下即可。

　　使用vlookup查找数据的时候，如果遇到重复的查找值，函数仅仅会返回第一个查找的结果，比如在这里我们要查找销售部王明的考核得分，仅仅用王明来查找数据就会返回75分这个结果，因为它在第一个位置，这个时候就需要增加一个条件来查找数据才能找到精确的结果，只需要将公式设置为：=VLOOKUP(E3&F3,IF({1,0},A1:A10&B1:B10,C1:C10),2,0)然后按ctrl+shift+回车三键填充公式即可。

　　在这里利用连接符号将姓名与部门连接在一起，随后再利用if函数构建一个二维数组就能找到正确的结果。

　　当我们使用vlookup来查找数据的时候，它仅仅只能查找数据区域右边的数据，而不能查找左边的数据，比如在这里我们想要通过工号来查找姓名，因为姓名在工号的左边所以查找不到，这个时候我们就需要将函数设置为：=VLOOKUP(G2,IF({1,0},B2:B10,A2:A10),2,0)然后按ctrl+shift+回车三键填充公式即可。

　　这个与多条件查询十分的相似，我们都是利用if函数构建了一个二维数组来达到数据查询的效果。

　　在这里我们需要用到一个通配符，就是一个星号它代表任意多个字符，我们需要利用连接符号将星号分别连接在关键字的前后作为查找值，这样的话就能达到根据关键字查找数据的效果公式为：=VLOOKUP("*"&E2&"*",A1:A10,1,0)

　　首先我们需要先在数据的最左侧构建一个辅助列，A2单元格输入公式为：=(B2=$G$2)+A1，然后点击回车向下填充，这的话每遇到一个2班就会增加1，此时我们的查找值就变为了从1开始的序列，只需要将公式设置为：=VLOOKUP(ROW(A1),$A$1:$D$10,3,0)向下填充即可。

　　计算销售提成其实就是区间查询，所谓的区间查询就是某一个区间对应一个固定的数值，如下图我们想要计算销售提成的系数，首先需要先构建一个数据区域，将每个区间的最小值提取出来对应该区间的系数，然后进行升序排序，随后我们直接使用vlookup函数的近似匹配来引用结果即可，公式为：=VLOOKUP(B2,$E$11:$F$16,2,1)

　　8、提取固定长度的数字

　　工号的长度都是5位，所以在这里我们利用MID(A2,ROW($1:$20),5)来提取5个字符长度的数据，然后将这个结果乘以0与1，来构建一个二维数组

　　Vlookup也可以用于合并同类项，只不过过程比较复杂，我们需要使用两次公式，首先我们将公式设置为：=B2&IFERROR("、"&VLOOKUP(A2,A3:$C$10,3,0),"")，然后拖动公式至倒数第二个单元格中，随后我们在旁边的单元格中再次使用vlookup函数将结果引用过来，公式为：=VLOOKUP(E3,A:C,3,0)至此合并完毕。

　　2、核对两列顺序错乱数据

　　8、提取固定长度的数字

　　以上的全部内容就是系统城为大家提供的excel中vlookup函数的使用方法具体介绍啦~希望对大家有帮助~系统城感谢您的阅读!

用 Excel 处理数据时，经常会涉及到多页 sheet 数据之间的关联运算需求，用 vlookup 可以完成部分简单关联，但较复杂的情况时仍然不太方便，常常需要多次操作才能完成。另外，当要做关联的文件比较多，需要批量处理时，虽然可以借助 VBA 来实现，但 VBA 不是个专门为结构化计算设计，实现计算非常繁琐。这里给出一些关联运算的示例，分析解决方法并给出 SPL 代码。SPL 是专业计算引擎 esProc 使用的语言，用于处理结构化数据运算非常方便，比 vlookup 及 VBA 更简单。

一、引用复制其它页的列

现有从财务部获取的员工工资表（EMPLOYEE 页），但是其中的部门信息只有部门编号（DEPARTID 列），不方便阅读和出报表。从人事部获取到部门信息表（DEPARTMENT 页）后，需要将员工工资表的 DEPARTID, 逐一到部门信息表中查找到对应的 ID，然后复制部门名称（DEPARTMENT 列）到员工工资表。

这是最简单的关联，采用 vlookup 函数可以实现，但仍有些注意事项。vlookup 函数在做查找时，必须清楚地知道要返回列和查找列的相对位置，如果中间插入删除了列，就需要调整公式；而且还要求被查找列必须位于区域的首列，当返回列在查找列的前面时，要对数据列先做次序调整才能用。

SPL 按列名访问，没有这些问题：

/导入第一页的带标题工资表
/将关联好的结果写出到另一文件

如果获取到的文件数据中不包含某种 ID 主键时，则需要根据多个列来做匹配。如下的学生成绩单，学生的姓和名是分开的列。现在需要根据姓和名，查找出每个学生的所属班级，以方便根据班级统计各班的成绩。

使用 vlookup 函数时，被查找的数据必须位于指定范围的第一列，也就是次序很重要，且一次只能查找一个值。像这样的多列匹配，没法直接使用 vlookup，需要过渡的办法。比如将要查找的列用 textjoin 合并到一个辅助列，目的表也得做同样的合并。最后再通过辅助列来查找，而这些查找前的准备工作，使得复杂度又更上一层楼。

SPL 不需要构成中间辅助列，直接用多个查找列名即可：

/导入第一页的带标题分数表
/采用序表外键关联方法，用分数表的 FirstNames 和 LastNames 字段关联班级表的对应字段，并选出 Class 字段

一个宽表的字段过多时，为了存储以及检索效率，往往需要将字段拆分到主键相同的多个小表中。如下的职员表跟生日表都是小表，现在需要通过 ID 字段来关联，方便查看员工的整体信息。

采用 vlookup 处理一对一关联时，复杂度跟前面的多对一是一样的，仍然有位置问题。但一对一关联的表有时会多于两个时，vlookup 函数没法同时处理多个表关联，只能一个一个来，稍显繁琐。

SPL 可以一次处理多个表和多个关联列：

/导入第一页的带标题员工表
/采用多序表关联，匹配两个表的 ID 字段后，并产生所需字段的新序表

注意跟前面的 A.join 不同，这里关联好的结果要用 new 方法选出关注的字段。

vlookup 只能依据左边的数据为准，即所谓的左连接，意思是当关联表数据有缺失（找不到可关联数据时）用空值填充。但一对一匹配时，我们有时还希望获得内连接和全连接的效果。所谓内连接，即指将关联不上的数据删除掉，只保证可以关联上的数据，如果用 vlookup 实现，就需要在关联后将有空值的数据行删除。而全连接是指如果被关联表有数据在关联表中找不到，也需要抄录过来，vlookup 无法直接实现了，只能分别做关联，然后合并，再去除重复数据，非常繁琐。

现在要实现左连接，也即没填写生日的记录也保留，目标结果为：

在 SPL 中实现左连接，只需加上选项 1。将上面代码中 A3 改为：

join 函数缺省选项时，就是内连接，内连接仅包含两边都匹配的数据。

内连接时，A3 中 SPL 代码为：

也即保留所有生日表中的记录，目标结果为：

实现右连接，其实就是将源和目标对调后的左连接，注意右连接时，选出的 ID 号要从右表 Birthday 中获取，否则就为空了，所以 A3 中的 SPL 代码为：

全连接相当于左右连接的并集，目标结果为：

全连接时，使用选项 f，注意此时的 ID 需要用表达式从非空的表中获取，所以 A3 中的 SPL 代码为：

业务场景中常见的订单，通常会是一个订单下面有多个明细。也就是这类匹配中的一条订单记录会对应多条明细记录，属于一对多的关联。如下的订单跟明细数据，现在需要将每个订单和明细用 OrderID 关联起来，方便用时间维度来统计订单的总额。

由于 vlookup 只能以左边数据为基准，而像这类一对多的关联，需要匹配后添加记录，所以没法实现左连接。而如果是做内连接的话，倒是可以将明细当源表，精确匹配后，再去掉订单为空的记录。又因为没法实现左连接，所以全连接也没法实现。

SPL 指定关联字段，不用考虑对应关系，便可关联：

/导入第一页的带标题订单表
/关联相应字段，并选出关注的字段产生新序表

一对多和一对一的关联方法一样，也是用多表关联方法 join，且同样只需简单指定关联字段名，所以对于左连接、右连接等不同连接方式，都跟第三节一样，这里不再赘述。

前面例子中，不管是哪种对应方式，都是根据条件相等去匹配。实际应用中，还有按非相等条件来匹配的情形。比如第二节中的成绩表，现在需要根据分数段来评级，此时没法直接用分数去目的表中做相等匹配。

抛开关联的条件是按照相等还是范围来匹配，这种关联类似于第一节的多对一关联。用 vlookup 函数时，需要先将分段值按升序排好序，然后再用模糊匹配方式，来实现按范围匹配。

SPL 也提供了非等值连接：

/导入第一页的带标题成绩表
/使用叉乘关联 xjoin，再用过滤条件留下符合要求的匹配
/匹配完成后，产生包含所需字段的新序表

可以使用完全叉乘方法 xjoin，然后再用过滤条件去掉不合要求的匹配。

《SPL Cookbook》中有更多相关计算示例。

我要回帖

更多关于 表格关联怎么设置公式 的文章

 

随机推荐