海生PPT

耐心、方法、目标、效率、只为帮助你！

普通员工追求完成工作任务

优秀员工追求高效工作

卓越员工追求方法技能

混迹职场

避免不了与数据打交道

因此

也就不得不使用Excel

然而

大多数伙伴使用Excel最常用的方法便是

“加 减 乘 除”

或者

“复制 粘贴”

比如

点击A表，找到想要提取的数据，右键-复制（CTRL+C），再点击B表，找到相应的单元格，右键-粘贴（CTRL+V）。

如此操作也没错

但是效率大大降低

关键是没掌握好的方法

如果3个或者三个以上的表格

也要如此操作吗

所以告别加班

告别“复制 粘贴”

就从关注海生PPT掌握好的方法开始

今天就给大家分享2个提高效率的方法

引用不同工作表数据的

1

跨表引用

在Excel公式中,可以引用其他工作表的数据参与运算

假设

要在"出行日期"表中直接引用"表妹1""表妹2""表妹3"的数据

则公式为：=表妹1!B2

（注：此公式没有添加判断条件，要准确引用相关数据，需要对各个“表妹”中知否赴约做判断。具体判断方法后面的文章中具体分享）

这个公式中的跨表引用由工作表名称、半角叹号(1)目标单元格地址3部分组成

除了直接手动输入公式外,还可以采用在工作表上直接“点选”的模式来快速生成引用,操作

步骤如下

Step1在出行日期表的单元格中输入公式开头的“=”号。

Step2单击表妹1的工作表标签,切换到表妹1工作表,再选择B2单元格。

Step3按回撤键结束。此时就会自动完成这个引用公式

注意：

如果跨表引用的工作表名称是以数字开头,或者包含空格及以下非字母字符

则公式中的引用工作表名称需要用一对半角单引号包含。例如:=`2月`!C3

2

跨工作簿引用

在Excel公式中,还可以引用其他工作簿中的数据.

假设

在"约会记录“要引用工作簿"开房记录"的数据明细工作表的B3单元格

如果当前Exce工作窗口中同时打开了被引用工作簿,也可以采用前面所说的直接点选方式自动产生引用公式。如果被引用的工作簿当前没有被打开,引用公式中被引用工作簿的名称需要包含完整的文件路经

如公式：

=`D:\工作目录[海生的开房记录.xlsx]数据明细!$B$2

请留意其中的半角单引号的使用

方法需要多次练习才能掌握

祝你每天进步一点点

不变是更加痛苦的

一份耕耘l一份收货

好知识 乐分享

-----------商务PPT定制呈现找海生------------

转发分享你会更美

心事留言，小编答你

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

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

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

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

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

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

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

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

excel

二、运用函数

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

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

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

Hi，大家好，我是胖斯基

又到了一个暖风熏得游人醉的周五，风和日丽，微风拂面

奈何又到月底，又是财务MM即将结账的日子，此时，完美的映衬了那句话：陪伴，是最长情的告白……

于是，周末愉快的在加班中度过

今天的主题是关于：如何利用Excel来进行数据的跨表填充？

比如说：针对零售行业，财务人在月末会统计各店铺的各类产品的销售业绩，并汇总到一个表中，如图：

当然，加班狗经常会这么操作

复制-粘贴-复制-粘贴-复制-粘贴……

无限循环

然后Go dead！

你说，倘若几十个门店，要真这么玩耍，你不加班谁加班？

1

通过分析观察，可以看出每个门店中每个项目都一样，并且排序也是一样；

那如此，我们可以如此操作：

公式：=INDIRECT(B$1&"!B"&ROW())

这里用到了一个很核心的函数：INDIRECT

这个函数的功能就是引用指定的位置并获取其内容，很明显，这里要跨取多个表，并且汇总表的表头中已经涵盖了各Sheet页签的名字，So，可以用Ta来摆平！

那INDIRECT 是如何使用的呢，看下图：

公式：=INDIRECT($A$2) 引用的是A2的位置，而A2里面的内容是B2，所以直接获取B2单元格里面的内容，结果为1.333

注意一点的是：

INDIRECT的函数引用中，一种加引号，一种不加引号。

一种是：=INDIRECT("A1")，加引号，表示文本引用，即引用A1单元格所在的文本（B2）;

另外一种是：=INDIRECT(A1)，不加引号，表示地址引用，因为A2的值为B2，B2又=1.333，所以返回1.333

所以你理解了Ta，那刚才的范例中的公式：=INDIRECT(B$1&"!B"&ROW())就不难理解了！

2

也许，刚才的范例有些理想化，因为每个门店的项目相同，那实际中，可能有的门店对应项目没有，有的有，不统一规范，那如何处理呢？

如果还按照复制-粘贴-复制-粘贴-复制-粘贴……，这种可能性基本为0了，那该如何处理呢？

依旧采用INDIRECT函数，但是这里需要借助Vlookup（借助其查找匹配功能，带回相应数值），如下：

公式：=IFERROR(VLOOKUP($A2,INDIRECT(B$1&"!A:B"),2,0),"-")

这里通过INDIRECT(B$1&"!A:B")，构建了一个新的区域，然后借助Vlookup，来获取信息

怎么样？速度是不是提效了很多？

胖斯基 | 说：

对财务人而言，有些工作看似重复繁杂，但其实若抓住了其中规律，并利用好有效的工具，你会发现，加班好像不再是事儿！

更多精彩，敬请关注Excel老斯基！

excel求和很简单，我们大家都会。但是excel求和仅仅是同工作表求和，如果我们需要跨工作表求和，又该怎么实现呢？这时候就需要我们运用到公式。下面我就给大家一步一步慢慢解说，看过之后，你一定也会觉得非常简单。

为了方便大家学习本经验，我特地制作了几个简单的表，这个表总共是有四个工作表：一个是汇总表，另外三个是单独的个人表。

通过上面几个工作表我们可以看到，张三、李四、王五的成绩都是已知的，而汇总表却是空白的，那么，我们应该怎么将他们的成绩汇总到汇总表内呢。

首先，我们先来填汇总表中所有人第一学期的语文成绩

这里需要一个公式，就是跨工作表的公式：

=（sheet1！X1+sheet2！X1+sheet3！X1）

我给大家解说一下这个公式--sheet代表工作表；X代表工作表内纵坐标的英文字母；1则代表工作表内横坐标的数字。

我们现在将公式带入：

很简单的，我们得到了第一个数值，也就是汇总表内所有人的第一学期的语文成绩

用同样的方法，我们可以得到第一学期所有人的各科成绩。

那么合计又怎么填呢？没错，用求和。之前也写过关于求和的经验，有不明白的大家也可以参考一下噢

总计也是用同样的方法~

就这样，我们填完了所有汇总表的格子啦。是不是很容易呢~

一听到Excel数据多表合并计算，感觉很难，其实简单的Excel数据多表合并计算，实际上很简单的。

Excel数据多表合一的问题在工作中经常遇到，将两张表合并在在一起计算，我们想合成一张报表，怎么处理呢？

今天跟各位朋友分享下“Excel数据多表合并计算”操作步骤如下：

1、将鼠标定位在O1单元格中，然手点击【数据】选项卡下的“合并计算功能”；

2、在弹出的“合并计算”对话框中，先选择计算方式“求和”，再点击浏览旁边的“红色小箭头”按钮，

在弹出的“合并计算—引用位置”对话框中，选择左边表格中的数据；

引用位置选择

3、将“引用位置”添加到“所有引用位置"，再次选“引用位置”红色小箭头按钮

4、在弹出的“合并计算—引用位置”处选择右表的数据，将"引用位置"添加到“所有引用位置”处，勾选标签位置的“首行”和“最左列”，点击确定；

选择数据引用位置动态操作图

5、初步效果出来，左表加右表得出第3个数据表。

6、将合并计算得出的数据表格进行美化，得出最终数据表格。

今天分享的Excel多表合并计算你学会了吗？如果是跨表合并的话，选择引用位置到另一个表格提取即可操作。希望今天的分享对各位朋友在工作中有所帮助！

更多关于Office——Excel操作技巧可加入我们 群 一起交流学习！159534659

在 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)

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

经过验证，完全可以。

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

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