日常我们工作中经常会需要汇总不同地区发来的表格，或者是需要把不同小组发来的数值叠加到一块计算一个汇总值，除了拿计算器一个一个敲之外，下面分享两种方法：

一、直接用华中B2+华南B2+。。。然后左右拖拉即可。

此方法适用于表格相对较少，简单好记不需要技巧

excel

二、运用函数

=SUM(华东:华北！B2)，其中华东是第一个sheet，华北是最后一个sheet，然后向右向下拖动即可。

此方法适合于sheet较多的情况，直接一个公式就行了，不用一个一个去加还容易遗漏某个sheet，但是就是需要去记住此公式了。

Excel的公式真的太多了，长期不用就忘了，用的时候又想不起来了，小编的办法就是收藏起来说不定哪天就用到了(#^.^#)

有时候我们需要有规律的引用多个分表里的数据，而一般的公式都是限于一张表里进行操作。indirect函数能很好解决这一问题。下面我们就来学习indirect函数的跨表引用吧。以全班学生每科成绩一张分表，现在需要按学生汇总所有科目成绩为例。

一、观察总表、分表 总表的汇总列名称是每个分表的表名称，要提取的期末成绩在每张表的E2单元格。

二、输入如下公式：=INDIRECT(C$2&"!E2",TRUE)

三、向右填充，结果如下。

在上一篇文章里，我们已经通过举例的方式对函数vlookup的基本用法进行了非常详细的阐述，并且也对函数vlookup中的四个参数的相关问题进行了解答。相信看过后的朋友肯定会发现，虽然函数vlookup的数据提取功能确实十分突出，但是在这里似乎非常鸡肋，因为这里的数据挺简单的，再加上只要提取两个结果，我们凭借肉眼也可以完成任务。

事实上，为了介绍函数vlookup的基本操作方法，我们无需太过复杂的excel工作表，所以对表格数据做了简化。我们不妨试想一下，如若考试成绩登记表的数据有几百行，而我们要查找的数据也有五十多个，那么函数vlookup的价值似乎更高了，但是方法与之前介绍的基本操作方法区别却不大，所以今天我向大家讲述一个十分经典的例子来让函数vlookup的身价倍增。（对函函数vlookup的基本操作方法感兴趣的朋友可以看完该篇文章之后参考文章万千数据迷人眼，函数vlookup助你来挑选！

今天要讲述的实例是关于跨表查询的内容，在一个excel工作表中，有sheet1和sheet2两张表，sheet1表里的内容是一张数据源表，sheet2表里的内容是一张调查表，现在调查表中有两项内容，分别是客户ID和公司名称，而现在客户ID我们已知，公司名称未知。我们现在的任务就是运用函数vlookup根据已知的客户ID去查询公司名称的内容，但是我们所需要的查找的数据源在sheet1表中。

这里与我们以往要解决的问题有所不同，以往我们在运用函数vlookup时，所有的数据都位于同一张表中，但是我们所需要的数据却分居两地：sheet1和sheet2。excel工作表的内容具体如下所示：

sheet1如下图所示：

我们可以看到这张数据源表的内容是相当得多的，其中包括客户ID和公司名称在内的内容共有11项内容，为了我们所举的例子简单易懂，除了客户ID和公司名称之外的内容，我们就不进行录入了。另外正因为数据源表自身数据很多且较为复杂，所以与调查表没有放在一起。

sheet2如下图所示：

具体操作方法如下：首先我们在sheet2中B3单元格输入“=VLOOKUP(A3,Sheet1!A:B,2,0)”，然后我们按回车键，就能得到客户ID为‘CHOPS’所对应的公司名称是‘浩天旅行社’，接着通过填充柄拖拽的方式向下拖拽，我们就能得到其他客户ID所对应的公司名称。具体操作可参考下图：

解释：函数式“=VLOOKUP(A3,Sheet1!A:B,2,0)”的得来方式，这里我会分成若干部分来向大家一一讲述，并告诉大家操作过程中容易犯的错误。

第一步，这是我们能在sheet2中完成的内容。在sheet2中B3单元格输入“=VLOOKUP(A3, ”，具体操作可参考下图：

第二步，这是我们能在sheet1中完成的内容。点击表格sheet1，进入sheet1中，选中A列和B列的内容，这里的操作等于在sheet2中B3单元格输入“VLOOKUP(A3,Sheet1!A:B，”，具体操作可参考下图：

第三步，切记：我们不需要点击sheet2返回原来的表中去找B3单元格，这样很可能导致我们所选择的查询区域出错：比如“=VLOOKUP(A3,Sheet1!A:B”变成“=VLOOKUP(A3,Sheet2!”。

这时我们只需要在编辑栏完成最后的操作即可，在编辑栏内输入“=VLOOKUP(A3,Sheet1!A:B,2,0)”，等同于在sheet2中B3单元格输入“=VLOOKUP(A3,Sheet1!A:B,2,0)“，具体操作可参考下图：

最后，通过填充柄拖拽的方式向下拖拽，我们就能得到其他客户ID所对应的公司名称。

关于函数式“=VLOOKUP(A3,Sheet1!A:B,2,0)”的解析：sheet2中A3单元格的”CHOPS“是要进行查找的数据，查找范围是sheet1中A列和B列中的所有内容，首先在查找范围的第一列找到”CHOPS“，然后在查找范围的第二列进行精确匹配，提取与”CHOPS“处于同一行相对应的数据。这样找到的结果也就是我们所要查找的公司名称了。

函数式“=VLOOKUP(A3,Sheet1!A:B,2,0)”中还有一个值得注意的地方，那就是我们选取的查找区域是sheet1中A列和B列整列，这样做的好处在于我们在运用填充柄向下拖拽时，不用对查找区域进行绝对引用了，因为我们已经将所有的区域都选好了。

往期精彩回顾：数据有效性和函数sumif的碰撞，又会擦出怎样的智慧火花呢？

函数sumif居然会抢函数vlookup的饭碗？真有这种可能

原来函数sumif函数式是可以简写的，提高工作效率的机会又来了！

今天的分享就到此结束了，觉得对你们有用的小伙伴们请点赞关注吧！您的鼓励是我前进的动力，也希望擅长运用办公软件的小伙伴们能够不吝赐教，积极的留言，教会小编更多的excel运用的小技巧，欢迎一起来探讨学习！！

找对比，你会首先想到

Vlookup

函数。但在Excel中只会Vlookup函数是远远不够的。今天兰色对查找公式进行一次全面的整理。

对于一个地方需要用到对比的时候我们肯定想到的是Vlookup函数，但是你在用Excel的时候只会用它可不行，今天我们就一起来梳理下常用的！

地球人都知道：一题多解的只选取最优公式！

1、普通查找VLOOKUP函数

我们需要查找李晓峰的应发工资

公式：=VLOOKUP(H2,B:F,5,0)

2、反向查找INDEX

函数

查找吴刚的员工编号

公式：=INDEX(A:A,MATCH(H2,B:B,0))

反向查找

3、交叉查找VLOOKUP函数：

查找3月办公费的金额

公式：=VLOOKUP(H2,A:F,MATCH(I2,1:1,0),0)

4、多条件查找

VLOOKUP函数

查找上海产品B的销量

公式：=LOOKUP(1,0/((A2:A7=E2)*(B2:B7=F2)),C2:C7)

5、区间查找

根据销量从右表中查找提成比率。

公式：=LOOKUP(A2,$D$2:$E$5)

6、双区间查找

根据销量和比率完成情况，从表中查找返利。

公式：=INDEX(B3:F7,MATCH(D11,A3:A7),MATCH(E11,B2:F2))

7、线型插值

A列是数量，B列是数量对应的系数值。现要求出数字8所对应的系数值。

公式：=TREND(OFFSET(B1,MATCH(D3,A2:A6,1),,2,1),OFFSET(A1,MATCH(D3,A2:A6,1),,2,1),D3)

8、查找最后一个符合条件记录

要求查找A产品的最后一次进价。

公式：=LOOKUP(1,0/(B2:B9=A13),C2:C9)

9、模糊查找

要求根据提供的城市从上表中查找该市名的第2列的值。

公式：=VLOOKUP("*"&A7&"*",A1:B4,2,0)

10、匹配查找

要求根据地址从上表中查找所在城市的提成。

公式：=lookup(9^9.find(A$3:A$6,A10),B$3:B$6)

11、最后一个非空值查找

要求查找最后一次还款日期

公式：=LOOKUP(1,0/(B2:B13<>""),$A2:$A13)

12、多工作表查找

【例10】从各部门中查找员工的基本工资，在哪一个表中不一定。

方法1

公式：=IFERROR(VLOOKUP(A2,服务!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,综合!A:G,7,0),IFERROR(VLOOKUP(A2,财务!A:G,7,0),IFERROR(VLOOKUP(A2,销售!A:G,7,0),"无此人信息")))))

方法2：

公式：=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"销售";"服务";"人事";"综合";"财务"}&"!a:a"),A2),{"销售";"服务";"人事";"综合";"财务"})&"!a:g"),7,0)

13、一对多查找

【例】根据产品查找相对应的所有供应商

公式：

A2 =B2&COUNTIF(B$1:B2,B2)

B11=IFERROR(VLOOKUP($A11&COLUMN(A1),$A:$C,3,0),"")

14、查找销量最大的城市

查找销量最大的城市。注意：注意：注意：数组公式按ctrl+shift+enter三键输入

公式：{=INDEX(A:A,MAX((MAX(B3:B7)=B3:B7)*ROW(B3:B7)))}

15、最接近值查找

根据D4的价格，在B列查找最接近的价格，并返回相对应的日期

注意：注意：注意：数组公式按ctrl+shift+enter三键输入

公式：{=LOOKUP(1,0/(MIN(ABS(B3:B7-D4))=ABS(B3:B7-D4))*ROW(B3:B7),A3:A7)}

15、跨多文件查找

跨多个文件查找，网上大概很难找到这样的教程，仔细想想其实原理和跨多表查找一样，也是借助lookup等函数实现。

文件夹中有N个仓库产品表格，需要在“查询”文件完成查询

仓库表样式

在查询表中设置公式，根据产品名称从指定的文件中sheet1工作表查询

入库单价

公式：=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT("["&{"仓库1";"仓库2";"仓库3"}&".xlsx]sheet1!a:a"),A2),"["&{"仓库1";"仓库2";"仓库3"}&".xlsx]sheet1")&"!a:b"),2,0)。

如果你在用vlookup函数做多文件查找的时候也可以用iferror+vlookup的模式的这种模式，看起来公式很长，但是这样不会出错。此外，如果一个表用到的函数太多就需要用宏表函数用Files获取所有excel文件名称了。

老男孩PS

：我能想到的基本上都已经列出来了，但是我相信有很多大家可能看不懂，但是看不懂没关系，赶紧收藏起来，然后直接套用就可以了。

在我的实际工作中，需要对22名员工平时的工作表现做一份考核表，本次具体会分享如下几个Excel操作的小细节，希望对有计算需求的朋友带来方便。

1，这项“团队精神”评判是泛范围的（即评论时可评某人，也可以选择不评），这样一来，参评的人数并不相同，我们就无法简单粗暴地按收到多张评测纸就除以几来计算其平均分值，此时我们可以借助于=SUM('1:22'!C6)进行跨多张表格求和，用=COUNT('1:22'!$C6)来统计参评个数，

如下图共有22人参与评论（就有22份格式一致但内容不同的表）。

在汇总表格的C6储存格输入=SUM('1:22'!C6)，这是将表1--表22的C6格求和，然后拖拽此公式向右向入填充。操作细节：输入=SUM（之后点住名字表1，然后按住SHIFT键不放，再点到名字表-22，以此操作进行区域选择，到时步时公式显示为：＝SUM（'1:22'!，此时再在选区域中的任意一张中点选C6格，再“）”完结公式；

接下来是统计每项评的人数，由于每评一个人必须把10项全评完，这样我们就可以直接以C列为依据进行参评人数的统计了。公式为在N6中输入=COUNT('1:22'!$C6)，表式统计1-22份表中C6格为非空格的数量；

对22份格式一致的参评表格进行统计

接下来再奉献一个非常非常实用的复制+选择性粘帐贴组合，绝对实用：大家再看上图，每项原本最高分都10，可现在全部都是多人之和，也就是与表格表现方式不符。此时我们可以利用shift键（或鼠标拖拉）选择区域到N6：N27------->然后按右键点开“选择性粘贴”--->点选“公式”---->“除”---确认。（具体见下短视频)

当然，我们也可以使用同样的方式直接=AVERAGE('1:22'!C6)来进行求平均，此份主要讲了几个平时工作中比较实用的EXCEL小技巧，希望有用哈！

作为人力资源工作者，经常要统计员工的各种信息，今天给大家介绍一种工作中用到的小技巧。

向下面一张表格（表1）统计了公司所有员工的具体信息，包括工号、姓名、身份证、部门、学历等等。

表1 员工信息表

另外有一份表格（表2）需要统计公司部分员工的部分信息，例如我需要统计工号为5、7、9、13号员工的姓名、部门、学历信息，而其他的身份证、性别等信息不需要。

表2

本文中列举的数据少可以对照表1一个个去复制，但是当要录入的数据比较多时，再去对照表1一个个去复制就比较麻烦，我们利用公式可以轻松的自动完成输入。操作如下图。

在表2中B2单元格输入以下公式：

=IF($A2="","",VLOOKUP($A2,员工信息表!$A$3:$F$16,MATCH(B$1,员工信息表!$2:$2,0),0))，然后拖动复制就完成自动录入了。

在 Excel 中，除在同一工作表求和外，还可以在不同工作表与不同 Excel 文档的工作之间求和；如果只在不同的工作表求和，只需标明工作表名称，如果在不同 Excel 文档之间求和，还要标明文档名称。另外，还可以跨行与跨列求和，它们可以用SUMPRODUCT函数。以下就是Excel跨工作表、跨行与跨列求和的具体操作方法，实例中操作所用版本均为 Excel 2016。

一、Excel跨表求和

1、假如要求一个服装表中 1月、2月和3月的销量之和。选中 E2 单元格，把公式 =SUM('1月'!D2:D6,'2月'!D2:D6,'3月'!D2:D6) 复制到 E2，按回车，则求出一件服装的 1 至 3 月销量之和；把鼠标移到 E2 右下角的单元格填充柄上，按住左键并往下拖，则求出其它服装的 1 至 3 月的销量之和；操作过程步骤，如图1所示：

2、公式说明。公式中 '1月' 是工作表（即 Sheet）的名称，工作表与单元格之间用感叹号 ! 隔开，“2月和3月”的求和单元格的引用方法也一样。

3、跨文档求和。

A、如果待求和的单元格分布在两个或多个 Excel 文档中，则还需要在表格名称的前面加上文档名称。假如要对“服装销量1.xlsx”和“服装销量2.xlsx”两个文档的“1月”工作表的服装销量求和，操作过程步骤，如图2所示：

B、操作过程步骤说明：在“服装销量1”窗口，在 E2 单元格输入公式 =SUM()，把光标定位在括号中，单击一下工作表“1月”，框选 D2:D6，公式变为 =SUM('1月'!D2:D6)，在 D6 后输入逗号（,）；切换到“服装销量2”窗口，单击一下工作表“1月”，同样框选 D2:D6，切换回“服装销量1”窗口，公式变为 =SUM('[服装销量1.xlsx]1月'!D2:D6,'[服装销量2.xlsx]1月'!D2:D6)，即在“服装销量2”文档中选择的单元格自动填到了公式中；按回车，则计算出一件服装的销量之和，同样用往下拖的方法求出其它服装的销量之和。

C、如果要求“服装销量1.xlsx”和“服装销量2.xlsx”1月与2月的服装销量之和，公式可以这样写：=SUM('1月'!D2:D6,'2月'!D2:D6,'[服装销量2.xlsx]1月'!$D$2:$D$6,'[服装销量2.xlsx]2月'!$D$2:$D$6)，如图3所示：

按回车，则求出两个文档的“1月和2月”的服装销量之和，如图4所示：

二、Excel跨行求和

1、假如要求偶数行的和。把公式 =SUMPRODUCT((MOD(ROW($2:$10),2)+1=ROW(A1))*F$2:F$10) 复制到 F11 单元格，按回车，则求出第 2、4、6、8 和 10 行的和，操作过程步骤，如图5所示：

2、公式说明：

A、选中公式所在单元格 F11，按住 Alt 键，按一次 M，按一次 V，打开“公式求值”窗口，单击“求值”一直到求出最终的结果，如图6所示：

B、公式中 ROW($2:$10) 返回一个 2 到 10 的数组，即{2;3;4;5;6;7;8;9;10}；MOD(ROW($2:$10),2) 是把数组中的每个元素与 2 求模；然后再把求得的数组每个元素都加 1；再判断数组的每个元素是否等于 ROW(A1)（即 1，ROW(A1) 是取 A1 行号），如果等于，返回 True，否则返回 False。

C、经过上述计算，求得数组{True;False;True;False;True;False;True;False;True}，数组中为 True 的用 F 列中的数字代替，为 False 的用 0 代替，即得数组{329;0;874;0;982;0;897;0;563}，最后用 SUMPRODUCT函数求出和。

3、如果要求第 3、6、9 行的求，公式可以这样写：=SUMPRODUCT((MOD(ROW($3:$9),3)+1=ROW(A1))*F$3:F$9)，如图7所示：

按回车，计算结果，如图8所示：

三、Excel跨列求和

1、假如要求偶数列的和。把公式 =SUMPRODUCT((MOD(COLUMN($B:$H),2)+1=COLUMN(A1))*$B2:$H2) 复制到 I2 单元格，按回车，则求出第2行指定列的和，同样用往下拖的方法求出其它行指定列的和，操作过程步骤，如图9所示：

2、公式说明：公式与求偶数行的和一样，要注意的是：把对行的绝对引用改为对列的绝对引用，即把 F$3:F$9 改为 $B2:$H2，$ 由在数字前改为在字母前。

隔三岔五就有读者问卢子，VLOOKUP怎么用？跨表、跨工作簿又如何用？

1.同一个工作表查找

每次，卢子都晒出这张经典的VLOOKUP函数说明图。

如果还不懂，再看视频，这个函数是必须要学会的！

2.跨工作表查找

同一个工作表会了，遇到跨工作表还是不懂，怎么破呢？

其实，就是基本功不熟，万变不离其宗。

明细表

查找表

问题：根据姓名，查找明细表对应的工资。

姓名在明细表的B列，所以区域要从明细表的B列开始选，不是A列，切记！实际工资，在这个区域的第5列。

这里的区域，是一个难点，困扰着一批读者。我做一个动画进行演示。

=VLOOKUP(A2,区域,5,0)

区域这里用鼠标选取，借助这个方法就将难度降到最低。

=VLOOKUP(A2,明细表!B:F,5,0)

3.跨工作簿查找

实际工作上，明细表跟查找表有的时候被放在两个工作簿中。希望达到的效果是这样的，就是设置公式以后，以后可以不用打开明细表也可以查找到对应值。

两个工作簿都打开，这个步骤很关键。

鼠标选取区域的妙招再次派上大用场。

=VLOOKUP(A2,[明细表.xlsx]Sheet1!$B:$F,5,0)

公式生成以后，将明细表这个工作簿关闭，输入李小玲，看是否可以查找出来？

经过验证，完全可以。

有一些人喜欢将简单的问题复杂化以体现自己牛逼的水平，而卢子刚好相反，喜欢将复杂的问题简单化。

每天清晨六点，准时与您相约

问题来源

昨天韩老师讲的Excel215 | 跨表查询：查询指定顾客的购买记录，查询公式如下图：

有一位自称EXCEL小白的朋友用一句戏语说：

韩老师，公式这么复杂，臣妾做不到啊！

那韩老师给个不用公式的方法——高级筛选。

实现步骤

我们还是用昨天的消费记录表：

第一步：写条件

在要放查询结果的工作表中，写一个条件区域（我们以查询“李四”的购买记录为例）：

第二步：高级筛选

在要放结果的“查询”工作表，选择【数据】菜单【排序和筛选】功能区【高级】选项，启动【高级筛选】对话框，选择“将筛选结果复制到其他位置”，列表区域选“购买记录表”所有记录，条件区域选择上一步自己输入的条件区，复制到指定的单元格，完成高级筛选。

过程如下动图：

高级筛选与公式的利弊：

高级筛选简单，可以选择指定姓名购买记录，但姓名一旦更改，需要重新筛选；

公式写起来麻烦，但灵活，姓名更改，结果也会随之更改。

数据筛选往期推荐：

Excel100 | SUMIF+CELL使隐藏列不参与汇总

Excel032 | 你的序号，经历筛选，仍一丝不乱！

Excel012 | 筛选竟能如此任性：在数据透视表中使用切片器

Excel004 | 高级筛选

多个Excel之间复制的时候总是拷贝不过来，是什么原因？教你3招。

引用另外一个文件的数据时，下面几个技巧你需要学会使用：

1、快速设置引用公式

输入=号，然后选取另一个excel文件中的单元格，就会设置一个引用公式。如：

=[员工打卡计算示例.xlsx]Sheet3!$A$1

如果需要引用整个表，你需要把公式中的$去掉，然后再复制公式。其实有一个很简便的方法：复制表格-选择性粘贴-粘贴链接

2、动态引用其他excel文件数据

【例】如下图所示的文件夹中，每个月份文件夹中都有一个“销信月报.xlsx”的excel文件，要求在“销售查询”的文件中实现动态查询。

操作方法：

1、在“销售查询”文件中新建各月份工作表，用上面学的粘贴链接的方法，把各月的数据从各文件夹中的销售报表中粘贴过来。

2、在销售查询表中，设置公式：

=INDIRECT(B$1&"!B"&ROW(A2))

设置完成后，就可以在查询表中动态查询各月的数据了。

3、复制工作表到另一个excel文件后的公式引用

如下图所示，A.xlsx文件的“报表”工作表移动到B.xlsx文件中后，公式并不是引用B文件的sheet2数据，而是A文件中的数据。

如果需要修改为引用B文件中的sheet2中数据，可以用替换的方法完成。

▎本文来源：兰色幻想EXCEL，作者：赵志东，会计从业考试(gaoduncy)整理发布。转载请注明作者及来源。