在我的实际工作中，需要对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小技巧，希望有用哈！

最近经常会有同学在问：两个格式相同的工作表怎么核对出不同的数据内容？

两份相同的销售报表，要从里面找出不同的数据要怎么做呢？数据少的话，一看就能找出不同的；数据多的话，那要自己一个个看就没那么容易找出来了。今天就来跟大家分享在Excel文件中两个工作表如何快高效快速的核对出不同的数据内容？

我们来看下下面的两份销售报表，需要核对出两表中不同的数据内容。

下图是销售报表1：

销售报表1

下图是销售报表2

销售报表2

我们需要将；两份销售报表中不同的数据内容在其中一份销售报表内突显标记。为了方便大家能看出效果，我先将两份销售报表中的不同数据内容均已突显标记。如下图：

核对两表不同数据内容突显标记

1、Excel文件中两份销售报表在销售报表1

通过条件格式新建规则设置

2、在“条件格式”的“新建格式规则”对话框中选择“使用公式确定要设置格式的单元格”——为符合此公式的值设置格式：=A1<>销售报表1!A1；点击格式处设置单元格格式填充颜色确定后，会返回到新建格式规则对话框，再点击确定。

新建格式规则设置完成

3、设置完成之后，就会看到在销售报表1里突显标记与销售报表2不同的几处数据内容。

两表快速核对出不同的数据

如果想在销售报表2也突显标记与销售报表1不同的数据内容与上面的操作步骤一样设置就可以完成。如下图：

在销售报表2处突显标记与销售报表1不同的数据内容

两个相同格式的工作表，在通过Excel表格中条件格式设置就可快速核对出两个工作表不同的内容，还有一种方法也能核对出两个工作表不同的内容，那就是通过选择性粘贴——运算——“减”来完成核对两个工作表中的差异数。

今天就简单的跟大家分享Excel文件中通过条件格式设置来高效快速的核对两个工作表中不同内容进行突显标记，希望对各位朋友在工作中有所帮助！

更多关于Office——Excel操作技巧可加入我们交流！

合并多个表格文件，Excel中可以使用power query，WPS中可以使用数据透视表来完成，两者各有优缺点。

例如，有下面3个表格，表格的标题行中有部分内容相同。怎样合并这样的3个文件呢？

步骤1：将这3个文件存放到同一个文件夹下（很重要），随意打开一个文件（例如01.xlsx），再新建一个空白表格文件。

步骤2：在空白表格文件中，插入数据透视表，选择“使用多重合并计算区域”——“选定区域”——“创建单页字段”——“下一步”。

步骤3：用鼠标选取01.xlsx文件中所有的数据，点击“添加”，就会在“所有区域”看到该数据区域；接着一一选取其他文件区域。

文件很多的话，这一步也是很麻烦的。可以使用BAT命令先将文件重命名成类似的名字，这样选取了第一个表格数据之后，后面的数据就不需要打开表格选取，而是直接修改文件名字。每个表格中的数据不同，在添加区域时，可以将区域设置的大一些，例如使用“$1:$4”(表示表格前4行）。

步骤4：数据选取完毕，就可以点击“确认”键创建一个新的数据透视表，会看到如下的汇总结果。界面中部的数据就是各个文件中的原始数据。

步骤5：还可以将筛选器下的“页1”拖到行字段下，这样我们就会看到界面左侧有“项1”、“项2”和“项3”，分别对应步骤3所添加的文件啦。

步骤6：如果某个原始表格中的数据变更了，可以在汇总表格上右键一下，点击“刷新”，汇总表数据也会随之更新。

和Excel的Power Query相比：

1. Excel中操作更简单一些，不过也只有Excel2016以上的版本才支持。

2. Excel的power query合并表格文件时，要求每个表格中的工作表名称相同，列数也必须相同，否则合并后数据会有遗漏或差错；使用数据透视表则没有这个限制。

3. 如果表格中列标题相同、行标题不相同，使用Excel的power query则不能合并；使用数据透视表则可以合并。

4. 原始数据更新时，两者都可以同步更新。

5. 数据透视表合并原始数据的同时还能汇总数据。

谢谢阅读，欢迎点赞、评论、关注和点击头像。

昨日，分享了使用Power Query插件快速合并多个Excel的方法，可惜很多朋友表示还没用上Excel2016。所以，今天分享一个新方法，不管什么版本的Excel，或者WPS表格，都适用。

实例

将一个部门所有人半年的销售数据合并到一份Excel文件中。

思路

我们想获取单元格A1的内容，就会输入公式“=A1”；如果要获取Sheet1下的A1内容，就会输入“=Sheet1!A1”；如果要获取的在“解晴.xlsx”文件下，就会输入“='D:\数据\[解晴.xlsx]Sheet1'!A1”。基于此，我们可以不用打开Excel文件，便轻松合并多个Excel。

步骤1：提取Excel文件名称

在命令提示符中输入“dir /b Excel文件所在路径”获取所有Excel文件的名称，复制名称到一个空白Excel工作表中。然后复制A列所有的名称，选中“B1:H8”区域，按“Ctrl + V”粘贴，这样“A-H”列都粘贴上了Excel文件名。

为什么是“A-H”列呢，因为原数据就是这样的。

步骤2：Excel名称转换成一列

复制“A-H”列数据粘贴到Word文档中，选择“只保留文本”，然后使用“文本转表格”工具转换成一列数据的表格。

步骤3：创建辅助表，获取源数据单元格地址

将一列的Excel名称粘贴到汇总表的A列，在“B-I”列输入“A1-H8”，即要合并的Excel文件中数据的单元格地址。

复制“B1-I8”数据，然后在输入栏左侧的名称中输入“B9:I40”，按Enter键选中这块区域，最后按“Ctrl + V”粘贴，效果同步骤1。

步骤4：输入公式，连接Excel文件名和数据地址

J1公式：="t='D:\数据\["&$A1&"]Sheet1'!"&B1

公式中的“D:\数据\”是Excel文件路径，“Sheet1”是要合并的Excel文件中的工作表名称。

公式输入后，向右填充，直到结果显示“H1”为止。然后选中这些公式，双击填充所有的公式。

步骤5：粘贴为数值，然后将文本公式转换成真正的公式

复制步骤4的结果，选择性粘贴为数值。然后将“t=”替换为“=”。

所有Excel文件的内容就都合并到一起啦。

看起来似乎有点复杂，但其实很容易，只要稍微理解一下整个的思路就好了。

方法优点：

不限Excel版本和 wps版本，比Power Query灵活。可用于合并多个工作表。想提取几行或几列都可以，取决于你在创建辅助表时，输入了多少单元格地址。本例中的“A1-H1”是标题行，创建辅助表时，删除“A1-H1”，就不会多次提取到标题了。

谢谢阅读，每天学一点，省下时间充实自己。欢迎点赞、评论、关注和点击头像。

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

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

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

excel

二、运用函数

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

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

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

之前写过一篇文章，介绍如何合并一个 Excel 文件中的多个工作表。

以后如果碰到要合并12个月的

每一年的

全部学院的

还有来自五湖四海的

各种五花八门成千上万行的数据，都可以在几秒内轻松搞定。

不用函数公式，不用编写VBA代码，还能自动更新。

体验了这一个功能，我就对 Excel 2016 的查询功能爱不释手了！还能不能更牛C一点呢？

很多时候，我们要汇总合并的表格不在一个文件里头。

比如，做好报名人员信息登记表模板，群发给了100个训练班的班主任。他们登记好表格表格发回给我，肯定是分开一个个文件的呀。有没有办法把他们自动合并到一个汇总表里头呢？

再比如，我每周都要汇总一次公司所有产品的销售明细，拿到手的却只有单周数据。难道我还要每周复制粘贴，每周重做一遍统计工作吗？

这不科学

……

幸好幸好，查询这个功能还可以合并多个文件中的多个表格。操作起来和汇总单个文件中的多个表差不多，只是多了几步而已。

以每个业务员发出的赠品明细表为例。每人提交的表格文件，统一放入一个文件夹中。

接下来我们就看如何，用5个步骤，将这个文件夹里的所有表格数据全部提取出来合并到一个新的汇总表中。

第一步：导入文件夹

在数据选项卡下，【新建查询】-选择【从文件】-【文件夹选项】。

导入文件夹后，跟随提示进入查询编辑器。

上一篇文章，在导入单个Excel文件中的多个工作表后，直接就开始对数据进行整理操作了。

但是这一次导入的是文件夹中的多个Excel文件，目前为止获取到的数据，都是Excel工作簿的名称、格式、创建日期等文件基本信息，还没有文件夹、工作簿的“外壳”包裹着。

所以需要额外做的是穿透文件夹、工作簿，提取到每个工作簿中的表格和数据。

第二步：提取工作簿

要穿透工作簿提取出工作表，需要在查询编辑器中创建一个辅助列。所以，先选择【添加列】-【添加自定义列】。

然后添加自定义对话窗中，写入一条公式。

（别担心，很短，只要一模一样复制过去就可以了）

=Excel.Workbook([Content])

注意，一定要一模一样，包括字母大小写。

一定要用英文符号！一定要用英文符号！一定要用英文符号！

重要的事情说三遍

点击确定以后，就将文件夹中的全部 Excel 工作簿放入编辑器中。

第三步：提取工作表

点击自定义列旁边的扩展按钮，展开按钮工作表列表。

再继续提取工作表中的详细数据。

第四步：提取数据

点击Custom.Data列旁的扩展按钮，就能展开明细数据。

第五步：清洗数据

后面的操作就和上一篇操作步骤一样了。再简单复习一遍。选中需要保留的数据列，然后删除其他列。

将第一行设为标题行。

去除标题同名数据行、Null空行，筛选出最终需要的汇总数据。

第六步：完成合并

将加工完成的数据加载至工作表中。以后再添加新工作表，直接丢进文件夹，然后打开合并中刷新查询就行。

Tips:如果从管理系统中导出的文件是 CSV、TXT 等文本格式的文件就更加简单。从文件夹导入数据到查询编辑器之后，不需要写公式提取工作表，直接将 Content 列扩展即可。

扩展得到数据列表后，继续扩展得到详细数据，再按照上述步骤清洗数据，就能达到同样的查询效果。

看，就是简单的点击操作，最复杂的也就是一行固定不变的简短代码，=Excel.Workbook([Content])

意思是来源于 Excel 软件的工作簿内容。

有了这一招，只要搭建好统计报表的框架，数据引用自合并以后的汇总表。以后有新的数据表，就丢进文件夹里 100 多份表格汇总、统计分析、别人几天的工作量，每月来一次。

就这样轻轻松松点击一下刷新，搞定！可以喝咖啡去咯~

无论你从事财务、人事或销售，都会面对多个excel文件表格的汇总问题。绝大部分同学都是逐个打开excel文件，复制粘贴到一个表格中，然后再进行汇总，如果是月报或日报，每月每天都要重复这个工作。今天分享一种通用的多表汇总方法，非常简单。

【例】如下图所示文件夹中，有3个子公司的报表和汇总表。要求：更新分公司文件后，汇总表会自动更新汇总。

1、把各分公司数据引入到汇总表中。

打开分公司报表， 复制 - 打开汇总表，选择性粘贴 - 粘贴链接。然后在A列输入该公司名称。（注意在复制时，多复制些空行，这样可以在添加新车型后也可以自动更新。）

2、用数据透视表生成汇总表

选取合并后的数据，插入 - 数据透视表 - 调整格式。

二、使用

使用很简单，把新报表替换旧报表，再打开汇总表更新数据透视表即可。

测试：新的一月A公司报表（为了方便测试我们把B车型销售数量改成1000）复制，粘贴到汇总文件夹内。

A公司新报表

复制A文件粘贴到文件夹中，替换原来的A公司文件。

其他公司同A公司，直接替换各个公司旧文件即可。

注意：为了防止某个公司未替换造成上个月数据的遗留，建议用空白文件把各个公司的文件先替换一下，然后再用当月文件替换

Excel解释说明：今天分享的技巧是一劳永逸的技巧。我们在实际工作中大量运用。工作量比原来不知省去了多少倍，一分钟就搞定所有汇总。

在 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，$ 由在数字前改为在字母前。

应用场景1

老板：小王啊，调查一下大家对我们人事部的服务有什么意见或建议没。小王：好的。

小王做好一份Excel调查表模板，分发下去。然后收到了100份结果文件。

应用场景2

老板：小王啊，还有5分钟我就要开会了，我们12家门店的销售情况表汇总到一起了吧。

小王应该怎么合并这些Excel文件？一个一个复制到手软吗？

合并多个Excel文件

第一步：将所有的Excel调查表文件存放在同一个文件夹下。打开一个空白Excel文件，点击“新建查询”，选择“从文件夹”中导入，然后粘贴所有Excel调查表所在路径。

第二步：在“组合”下拉菜单中选择“合并和编辑”。

第三步：选中要合并的工作表。

第四步：点击Power Query编辑器中的“关闭并上载”。

稍等片刻，所有的文件就都合并到同一个工作表啦。

可以看到表头的两行重复显示了很多遍。使用定位或筛选功能，删除多余的行即可。

当然，也可以在上述步骤4“关闭并上载”之前删除一些行。

更新数据

小王好开心，100份Excel调查表就这么轻松地合并到一起了。小丽：小王，我的调查表修改了一下，发一份新的给你吧。小王……

小王，莫急，直接在原来的文件夹下替换小丽的调查表，然后在合并后的汇总表上，右键一下，点击“刷新”即可。

合并多个Excel工作表

和合并excel文件类似，用Power Query同样可以快速完成。

第一步：打开一个空白Excel文件，点击“新建查询”，选择“从工作簿”中导入，然后打开需要汇总的Excel文件。

第二步：选中所有要合并的工作表名称，然后点击“编辑”。

第三步：按照图示的步骤，点击“追加查询”，然后将所有的工作表添加进来。

最后点击“关闭并上载”，所有工作表内容就合并到一个工作表下啦。同样的，如果原始的表格数据更新，在汇总表上刷新一下即可同步更新数据。

注意：以上步骤都基于Power Query插件，Excel2016才支持。使用低版本的Excel或WPS的朋友请查考明日的文章。

相关阅读：《多个word文档怎么合并》。

谢谢阅读，每天学一点，省下时间充实自己。欢迎点赞、评论、关注和点击头像。

多个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)整理发布。转载请注明作者及来源。