excel中如何引用另一个表格的数据用什么函数数组公式内容？

来源：蜘蛛抓取(WebSpider) 时间：2024-04-10 07:40 标签： 引用另一个表格的数据用什么函数
Excel的三种引用，很多人傻傻分不清，但是在面试和工作中会经常用到。我们先来看看这三种引用长什么样。对于A列第一行的单元格，相对引用是A1。绝对引用是列号和行号前面都加了一个美元的符号$A$1。混合引用是在行号或者列号其中一个前面加了美元符号，$A1 或 A$1。我们通过一个实际的例子来解释这三种引用的作用和区别。相对引用下图是员工工资表。员工每月的工资包括了基本工资和补贴，而且每个员工的补贴都是不一样的。这时候要算出每个员工的总工资，也就是将每一行“基本工资”和“补贴”这两个单元格的数值加起来了。在第一行总计的单元格中输入公式=B2+C2，将第一行的基本工资和补贴相加。然后鼠标放到这个单元格右下角变为十字架时，双击十字架，用公式把这一列都填充了。填充的公式里的行号和列号会随着单元格的改变而改变，这就是相对引用。第2行公式相应变成了这一行的值B3+C3，第3行公式相应变成了B4+C4。相对引用是指，当把公式复制到其它单元格中时，行号和列号会根据实际的单元格相应的改变。绝对引用我们把刚才的例子改变一下，假设每个员工的补贴是一样的，都是5000元。现在要计算每个员工的总工资，怎么办呢？如果按前面例子的操作方法输入公式，你会发现只有第一行的数据是有加上补贴的，其他行并没有。这是因为引用了相应的空白单元格，而真正的补贴单元格只是F列的第1行（F1）。对于这种情况，我们就需要用到绝对引用，也就是在复制公式的时候，公式里的补贴单元格的行号和列号不会改变，指向的都是补贴那一个单元格。下面图片是改变成绝对引用后的效果。绝对引用是在行号和列号前面加个美元符号$，对应这个例子里就是在补贴所在的单元格的行和和列号前面都加美元符号，也就是$F$1。然后把这个公式复制到其他单元格，你会发现，补贴所在的单元格行号和列号都没有改变。这就是绝对引用。混合引用混合引用是行或列中有一个是相对引用，另一个是绝对引用，表示加了美元的行号或者列号不变。例如：$A1在行号前面加了一个$符号，行号就固定不变了A$1 在列号前面加了 美元符号，列号就固定不变了。每次手动输入美元符号$非常麻烦，Excel设置了快捷键F4帮助用户迅速切换相对引用、绝对引用和混合引用。步骤如下：1）选定包含该公式的单元格2）在编辑栏中选择要更改的公式内容，并按 F4 键3）以引用单元格A1为例，每次按 F4 键时，Excel会依次在以下组合间切换：按一次F4是绝对引用，按两次、三次F4是混合引用，按四次F4是相对引用我是猴子，中科院硕士/前IBM高级软件工程师/豆瓣8分《数据分析思维》作者，我和知乎知学堂研职在线联合出品的「数据分析训练营」即将开课，3天带你掌握数据分析实用技巧，包含课程+实战带练，工作提效、升职加薪必备神器！报名还有独家Excel自学资料领取，点击开启数据分析升职加薪密码：
VLOOKUP语法格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)VLOOKUP（要查找的值，查找区域，要返回的结果在查找区域的第几列，精确匹配或近似匹配）相信大家都知道Excel不仅仅是办公必备软件，它还是数据分析当中非常重要的工具。如果你能非常熟练的掌握Excel，那么数据分析就非常容易入门了。为了让大家更深入的了解Excel和数据分析，这里推荐给大家业内较为出名的书籍供大家参考。感兴趣的小伙伴，可点击下方卡片查看！一、精确查找根据姓名查找对应部门：输入公式：=VLOOKUP(G2,A:C,3,0)G2：要查找的内容A:C：查找区域，注意查找区域的首列要包含查找的内容3：要返回的结果在查找区域的第3列0：精确查找二、近似查找根据分数查找对应等级：输入公式：=VLOOKUP(B2,E:F,2,1)B2：要查找的内容E:F：查找区域，注意查找区域的首列要包含查找的内容2：要返回的结果在查找区域的第2列1：近似查找注意查找区域中的首列内容必须以升序排序。三、格式不一致的查找查找数据为4的数量：输入公式：=VLOOKUP(D2,A:B,2,0)D2：要查找的内容A:B：查找区域，注意查找区域的首列要包含查找的内容2：要返回的结果在查找区域的第2列0：精确查找这都没错啊，为什么结果会返回错误值#N/A呢？细看之下你就会发现格式不一致查找值数值型（D2单元格内容4是数值型）查找区域文本型（A列的数据是文本型）遇到这样的问题该怎么解决呢？格式一致一是可以利用分列功能将A列分列成常规，与D2单元格格式一致二是可以将D2单元格内容设成文本格式，与A列格式一致三是变公式公式：=VLOOKUP(D2&"",A:B,2,0)将查找值连接空（&""）变为文本接下来顺便说下另一种格式不一致问题：查找值文本型，查找区域数值型查找值文本型（D2单元格内容4是文本型）查找区域数值型（A列的数据是数值型）输入公式：=VLOOKUP(D2^1,A:B,2,0)^1是将查找值转换成和查找区域一致的格式转换方法多种：--、+0、-0、*1、/1...等等四、通配符查找根据简称查找对应应收账款：输入公式：=VLOOKUP("*"&D2&"*",A:B,2,0)星号（*）匹配任意一串字符。五、带“~”的查找根据姓名查找对应部门：公式没有错，结果为什么会返回错误值#N/A呢？因为查找内容带波形符（~）输入公式：=VLOOKUP(SUBSTITUTE(G2,"~","~~"),A:C,3,0)在查找包含通配符其本身内容时，需在通配符前键入“~”用函数SUBSTITUTE将“~”替换成“~~”。六、取消合并单元格内容为数值，取消合并单元格：输入公式：=VLOOKUP(9E+307,A$2:A2,1,1)9E+307是科学记数，表示9*10^307，是Excel允许键入的最大数值。内容为文本，取消合并单元格：输入公式：=VLOOKUP("座",E$2:E2,1,1)七、查找第一次价格根据物料名称查找对应第一次价格：输入公式：=VLOOKUP(F2,B:D,3,0)当查找区域首列出现有两个或更多值与查找值匹配时，函数VLOOKUP返回第一次出现的对应值。八、交叉查询根据产品和地区查找对应销量：输入公式：=VLOOKUP(A12,A2:G8,MATCH(B12,A1:G1,0),0)MATCH(B12,A1:G1,0)部分找到B12单元格内容“华北地区”在区域A1:G1中的位置5，把它作为VLOOKUP函数的第3参数；公式就是：=VLOOKUP(A12,A2:G8,5,0)查找A12单元格内容“产品D”返回值在区域A2:G8中的第5列，即E列即E5单元格中的值6945九、反向查找根据工号查找对应姓名：函数VLOOKUP可以借助IF{1,0}与IF{0,1}、CHOOSE{1,2}与CHOOSE{2,1}等等结构将逆序转换为顺序，从而实现查找。函数VLOOKUP+ IF{1,0}结构：输入公式：=VLOOKUP(D2,IF({1,0},B2:B11,A2:A11),2,0)IF({1,0},B2:B11,A2:A11)部分当为1时条件成立返回B2:B11当为0时条件不成立返回A2:A11可以将IF({1,0},B2:B11,A2:A11)部分抹黑按F9键查看就是两列顺序对换，将逆序转换为顺序函数VLOOKUP+ IF{0,1}结构：输入公式：=VLOOKUP(D2,IF({0,1},A2:A11,B2:B11),2,0)函数VLOOKUP+CHOOSE{1,2}结构：输入公式：=VLOOKUP(D2,CHOOSE({1,2},B2:B11,A2:A11),2,0)函数CHOOSE：根据给定的索引值，从参数串中选出相应值或操作。CHOOSE(index_num, value1, [value2], ...)如果第一参数为1，则CHOOSE返回value1；如果第一参数为2，则CHOOSE返回value2。CHOOSE({1,2},B2:B11,A2:A11)部分当条件为1时，返回B2:B11当条件为2时，返回A2:A11函数VLOOKUP+CHOOSE{2,1}结构：输入公式：=VLOOKUP(D2,CHOOSE({2,1},A2:A11,B2:B11),2,0)CHOOSE({2,1},A2:A11,B2:B11)部分当第一参数为2时，则CHOOSE返回对应B2:B11中的值；当第一参数为1时，则CHOOSE返回对应A2:A11中的值。把CHOOSE({2,1},A2:A11,B2:B11)部分抹黑按F9键查看AB两列顺序对换，将逆序转换为顺序，再用函数VLOOKUP查找。十、查找返回多列数据输入公式：=VLOOKUP($F2,$A:$D,COLUMN(B1),0)，右拉填充公式右拉返回结果在第2、3、4列用函数COLUMN构造COLUMN(B1)=2，公式右拉变成COLUMN(C1)、COLUMN(D1)得到3、4。十一、按指定次数重复输入公式：=IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUMIF(OFFSET(B$2,,,ROW($1:$4)),"<>"),A$2:A$5),2,0),E2)&""按<Ctrl+Shift+Enter>三键结束十二、结果引用合并单元格内容A列区域为合并单元格，根据业务员查找对应的区域：输入公式：=VLOOKUP("座",OFFSET(A2,,,MATCH(D2,B2:B14,0)),1,1)MATCH(D2,B2:B14,0)部分找到业务员“阿文”在区域B2:B14中的位置11OFFSET（基点，偏移行数，偏移列数，行高，列宽）OFFSET(A2,,,11)是以A2单元格为基点，偏移0行0列，返回行高为11的新区域A2:A12的引用。OFFSET部分抹黑按F9键得到：用“座”等较大的汉字查找区域中最后一个单元格内容，即返回“华北地区”。十三、有合并单元格的查找A列产品为合并单元格，如何查找A列产品对应的单价呢？输入公式：=VLOOKUP(VLOOKUP("座",A$2:A2,1,1),F:G,2,0)比如D5单元格公式=VLOOKUP(VLOOKUP("座",A$2:A5,1,1),F:G,2,0)A$2:A5部分返回{"产品1";"产品3";0;0}VLOOKUP("座",A$2:A5,1,1)部分用"座"查找最后一个单元格内容，即返回“产品3”外层再套个VLOOKUP精确查找即D5单元格公式就是=VLOOKUP("产品3",F:G,2,0)，返回单价12十四、与T+IF的组合应用输入公式：=SUM(VLOOKUP(T(IF({1},A2:A8)),D2:E8,2,0)*B2:B8)数组公式，按<Ctrl+Shift+Enter>三键结束IF({1},A2:A8)部分构成三维内存数组VLOOKUP函数第一参数不能直接为数组函数T起降维作用，将三维引用转换为一维数组，其返回的结果仍为数组，用函数SUM求和。十五、多条件查找与反向查找一样，可以借助IF{1,0}与IF{0,1}、CHOOSE{1,2}与CHOOSE{2,1}等结构输入公式：=VLOOKUP(E2&F2,IF({1,0},A2:A11&B2:B11,C2:C11),2,0)数组公式，按<Ctrl+Shift+Enter>三键结束十六、一对多查找输入公式：=IFERROR(VLOOKUP($E$2&ROW(A1),IF({1,0},$A$2:$A$11&COUNTIF(INDIRECT("A2:A"&ROW($2:$11)),$E$2),B$2:B$11),2,),"")数组公式，按<Ctrl+Shift+Enter>三键结束效果图：十七、动态图表【数据】→【数据验证】输入公式：=VLOOKUP($A9,$A$2:$G$5,COLUMN(B1),0) ，右拉填充【插入】→【插入柱形图】操作演示：如果大家能够熟练的掌握Excel，那么入门数据分析就会变得非常简单了。为了让大家更好的入门数据分析，这里推荐给大家一本Excel书籍，感兴趣的小伙伴可以点击下方卡片了解哦！

我要回帖

更多关于 引用另一个表格的数据用什么函数 的文章

 

随机推荐