收藏的Excel函数大全公式再多，几天不用也会忘记。怎么才能不忘？你需要了解公式的运行原理。兰色今天不再推送一大堆函数公式，而是根据提问最多的问题，精选出6个实用的，然后详细的解释给大家。

1、计算两个时间差

=TEXT(B2-A2,"[m]分钟")

兰色注：

Text函数（数字,格式）：可以把数字或文本格式化显示，如同单元格自定义格式效果。"[m]分钟"：在表示时间的自定义格式中，小时为h，分种是m，秒是s，所以如果是间隔小时，把m替换成h。而加[ ]则是可以显示大于24小时的小时数、60分种的分钟数。

2、计算到期时间

=EDATE($A2,$B2)

EDate（开始时间,间隔月数)：N月后的日期。如间隔月数为负则表示N月前的日期。

3、隔列求和公式

按标题求和

=SUMIF($A$2:$G$2,H$2,A3:G3)

因为有规则的标题，所以隔列求和转换为条件求和公式

如果没有标题

=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)

Column(区域):取得区域的所在列数，构成一组数字。MOD（数字,2）：一个数字除以2的余数。如果余数是0表示该数字为偶数，否则为奇数（条件）**B3:G3：在公式中经常会见到这样的结构。条件成立时返回的是True（运算时等同于1），不成立返回的是False（运算时等同于0）。两组数据相乘的结果是满足条件的数会保留下来，不满足的会变成0。Sumproduct：它和Sum函数的主要区别是，这里可以直接执行数组运算的求和，而Sum函数需要按三键输入。

4、超链接公式

=HYPERLINK("#"&B2&"!A1","进入")

Hyperplink函数：在Excel中能用公式创建超链接的，只有Hyperplink函数，它可以把一个字符串转换为超链接。"#"&B2&"!A1"：B列是工作表中，连接后 "#工作表名!A1"，前面加#表示本工作簿

5、跨表批量取值

=INDIRECT(B2&"!b10")

Indirect函数：它的作用是把单元格地址串转换为引用。如本例中 B2&"!b10" 连接起来虽然是工作表名！单元格，但它只是个字符串而已，并不会把单元格中的值取出来。利用Indirect转换后则可以取单元格中的值了。

6、提取不重复值

数组公式：按三键输入

｛=INDEX(A$1:A$99,MATCH(0,COUNTIF(C$1:C1,A$1:A$99),0))&""｝

COUNTIF(C$1:C1,A$1:A$99)：让C列本公式上面的姓名在A列逐个用Countif函数统计个数，如果不存在（正是要提取的唯一值）结果为0，然后用Match(0,统计的个数,0)查找0所在的行数。INdex（区域,行数）:可以根据位置提取出相对应的值。

兰色说：Excel函数公式的学习也需要循序渐进，如果上面的解释你看的还是不明白，就要从函数的基础用法学起了。

End.

来自：Excel精英培训

在办公室职场数据处理中，很多时候，要用到隔行取数或是隔列取数，下面小编分享下如何隔列取数要求：

关注小编，每天共同学习职场办公应用技能

步骤1，在F4单元格输入公式=SUM(VLOOKUP(A1,A1:O1,ROW(1:8)*2-1,FALSE))，而后三键结束，得出的，就是奇数列的和

公式解释：

1，这里用到VLOOKuP函数，VLOOKUP(要查询的值,查询区域，返回区域内的第几列,查询方式，精确查找还是模糊查找),

2，ROW(1:8)*2-1，因为要取奇数列的值，我们必须在VLOOKUP第三个参数内，形成一个1,3,5,7,9的数组，而且不能超出第二个参数的范围，所以我们用到这个公式，按F9键就可以看出，得到的结果为{1;3;5;7;9;11;13;15}而后外套VLOOKUP函数，相当于(VLOOKUP(A1,A1:O1,{1;3;5;7;9;11;13;15},FALSE)，通常情况下，第三个参数为单个值，因为求和，这里才会需要一个数组，

3，选择VLOOKUP函数的区域，得到的结果是{2;4;6;8;10;12;14;16}，z这里是ROW(1:8)*2的到的结果，VLOOKUP函数确定后，只能得到一个值，这里是一个一个的数值，并不能得到汇总求和的结果所以我们才外套一个sum，就相当于把所有值加起来，得到结果。

以上为小编个人理解，如有错误，欢迎探讨！欢迎关注小编！

求和，应该是老生常谈的话题了，从我们接触Excel开始，就有了求和，但是，你真的会求和吗？会对常见的错误进行分析吗？

一、Alt+=：快速批量求和。

方法：

1、选中数据源。

2、快捷键：Alt+=。

解读：

1、通过观察公式，我们可以发现，其实Alt+=就是Sum函数的快捷键而已。但是非常的实用！

二、Sumif：单条件求和。

目的1：计算男、女生的成绩之和。

方法：

在目标单元格中输入公式：=SUMIF(C3:C9,N3,K3:K9)。

解读：

单条件求和函数为Sumif，其语法结构为：=Sumif（条件范围，求和条件，求和范围）。当条件范围和求和范围相同时，求和范围课省略。

目的2：隔列求和。

方法：

在目标单元格中输入公式：=SUMIF($D$3:$K$3,L$3,$D4:$K4)。

解读：

1、条件范围为D3:K3，而且不会变化，所以采用绝对引用的形式。

条件为“计划”和“实际”，要根据不同的情况进行变化，而“计划”和“实际”在不同列，同一行，故列采用相对引用的形式，而行采用绝对引用的形式。求和范围为数据区D4：K4，列的范围不会发生变化，而行要变化，随意采用绝对和相对引用相结合的形式。

三、SUMIFS：多条件求和。

目的：计算男、女销售员的计划销售额大于等于300的总和。

方法：

在目标单元格中输入公式：=SUMIFS(L4:L10,C4:C10,P4,L4:L10,">=300")

解读：

多条件求和函数为Sumifs，其语法结构为：=Sumifs(求和范围，条件1范围，条件1，条件2范围，条件2……条件N范围，条件N）。

四、不同列求和。

目的：计算出“地区”总的销售额。

方法：

在目标单元格中输入公式：=SUMIF(D:L,O3,C:K)。

解读：

从公式中我们可以看出，使用的函数为单条件求和Sumif函数。

结束语：

本文主要讲解了常见的求和类型及解决办法，实用性极高，希望对大家的工作有所帮助。不明白之处欢迎大家在留言区留言讨论哦！

我们用排序就可以实现隔行或隔列取值，方法很简单，只要给奇数行填充数字序列，偶数行保留空白，然后按照辅助的序列排序就可以了。不过，这一篇介绍的是如何用index函数实现隔行或隔列取值。

为什么可以用排序法，还要学习公式法呢？

这是因为，可以用公式做一个模板。以后就只要替换数据，就会自动获得隔行或隔列的数据，连同基于这些新数据所做的图表和各种汇总都可以自动更新，就像上面这张GIF图所示的一样。

而如果你用排序法，每次都要操作一次。这无疑增加了你加班的概率。

废话不多说了，直接进入主题。

隔行取值

隔列取值

公式注意事项

index函数共3个参数，第一个参数表示操作的范围。这个范围的第一行就是index函数指向的第一行。也就是说隔列取值的公式中的第一行是“刘庭”的数据，而不是表格的第一行。

index第2、3个参数表示行数和列数，这个行数和列数指的是相对于第一个参数范围是第几行第几列。

第一个参数范围，必须使用绝对引用，这样填充公式到其他单元格时才不至于出错。

输入左上角第一个公式，向右或向下填充公式，当公式返回结果“REF!”时，表明所有的结果都已经取完了。

相关文章：WPS Excel: 用index函数制作动态图表

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

Hi，大家好，我是胖斯基

数据求和，在Excel的使用中可谓是频率极高的一种，无论是初入新手，还是E场老斯基，其应用范围面都有所涉及。

那就数据求和而言，最常见的有以下几种：

1

快速求和系列

快捷键：AL+= , 选中求和区域，快捷键一下即可

2

快速累计求和系列

Excel中锁定符号[$]的应用，使得结果逐行累加，更多应用请见《技巧 | 隐藏在Excel中的定海神针》

3

忽略文本跨列求和系列

Excel中sum函数本身对文本类型数据自动忽略，故用来对跨列的数值型数据求和

4

单条件求和系列

Excel中SUMIF，单条件求和的万能公式

5

多条件求和系列

Excel中SUMIFS，多条件求和的万能公式

6

单条件多区域求和系列

例3和例4的综合应用

7

避开错误值求和系列

依旧采用SUMIF函数的应用，其中条件"<9.9E+307"，其含义是9.9E+307是一个极大的数值，故通过规则：数值<文本

8

通配符求和系列

通配符*代表多个字符，故*茶*的含义就是只要涵盖了[茶]这个字，即符合调价，纳入条件求和范围

So，常见的8种求和方式如上所述，你知道几种？

有如下数据，需要分别对一个月的上班、请假、加班、旷工时间进行汇总：

可以使用数组公式，如：D4单元格可以设置公式：=SUMPRODUCT((MOD(COLUMN(I4:EB4)-9,4)=0)*I4:EB4)

D5单元格可以设置公式：=SUMPRODUCT((MOD(COLUMN(J4:EC4)-10,4)=0)*J4:EC4)其它公式可以类似设置：

上班

请假

=SUMPRODUCT((MOD(COLUMN(I4:EB4)-9,4)=0)*I4:EB4)

=SUMPRODUCT((MOD(COLUMN(J4:EC4)-10,4)=0)*J4:EC4)

=SUMPRODUCT((MOD(COLUMN(I5:EB5)-9,4)=0)*I5:EB5)

=SUMPRODUCT((MOD(COLUMN(J5:EC5)-10,4)=0)*J5:EC5)

=SUMPRODUCT((MOD(COLUMN(I6:EB6)-9,4)=0)*I6:EB6)

=SUMPRODUCT((MOD(COLUMN(J6:EC6)-10,4)=0)*J6:EC6)

=SUMPRODUCT((MOD(COLUMN(I7:EB7)-9,4)=0)*I7:EB7)

=SUMPRODUCT((MOD(COLUMN(J7:EC7)-10,4)=0)*J7:EC7)

=SUMPRODUCT((MOD(COLUMN(I8:EB8)-9,4)=0)*I8:EB8)

=SUMPRODUCT((MOD(COLUMN(J8:EC8)-10,4)=0)*J8:EC8)

=SUMPRODUCT((MOD(COLUMN(I9:EB9)-9,4)=0)*I9:EB9)

=SUMPRODUCT((MOD(COLUMN(J9:EC9)-10,4)=0)*J9:EC9)

=SUMPRODUCT((MOD(COLUMN(I10:EB10)-9,4)=0)*I10:EB10)

=SUMPRODUCT((MOD(COLUMN(J10:EC10)-10,4)=0)*J10:EC10)

=SUMPRODUCT((MOD(COLUMN(I11:EB11)-9,4)=0)*I11:EB11)

=SUMPRODUCT((MOD(COLUMN(J11:EC11)-10,4)=0)*J11:EC11)

其中的-9、-10是表示需要开始计算的列号离第一列的列数；其中的4表示数据每隔4列需要相加。由Column函数求出某列是第几列；Mod函数求出除4的余数，通过逻辑等式确定是否逻辑值是真还是假；逻辑值为真是在数组中代表1，假时代表0；Sumproduct函数可以对两个数组中按顺序对应的数组元素值相乘，得到一个乘值数组，乘值数组元素再求和。

最终的运算结果如下：

在工作中，有时候excel表格需要每天统计数据，表格的行数会随着每天记录的数据而不断增加。像下表，每天都要记录出库数量，并且每天都要统计出库合计数量。当每天新增记录时，出库合计数量不需要再重新计算，根据新增的记录数据自动求和。

今天的小技巧运用到OFFSET函数和COUNTA函数。

OFFSET函数：以指定的引用为参照系，通过给定的偏移量返回新的引用。

函数语法：OFFSET(reference, rows, cols, [height], [width])

这个函数有5个参数：

第一个参数是基点。

第二个参数是要偏移几行，正数向下，负数向上。

第三个参数是要偏移几列，正数向右，负数向左。

第四个参数是新引用几行。

第五个参数是新引用几列。

第一至第三个参数为必需，第四个和第五个参数为可选。

例如公式：=OFFSET(B2,3,2,3,2)

就是以B2为基点，向下偏移3行，向右偏移2列，新引用的行数是3行，新引用的列数是2列，最终得到对D5:E8区域的引用。和SUM函数组合=SUM(OFFSET(B2,3,2,3,2))就是对D5:E8区域求和。

COUNTA函数：计算区域中不为空的单元格的个数。

接下里求出库数量合计，在B1单元格输入公式：=SUM(OFFSET(C3,0,0,COUNTA(C:C)-1,1))。当我们再次想数据表格中增加数据时，就会自动统计出库数量。

今天和新手们分享有关条件求和的内容，高手请忽略。

先来看一组销售数据，是某商场不同品牌电视机的三天销售记录：

现在需要根据G列的品牌，计算其三天的销售总和。

想必有表亲已经想到办法了，既然是按条件求和，就用SUMIF呗：

=SUMIF(B:B,G2,C:C)+SUMIF(B:B,G2,D:D)+SUMIF(B:B,G2,E:E)

公式分别对C、D、E列条件求和，最后相加。

这样看是没有问题的，可是如果需要对30列数据条件求和，那岂不是太费劲了哦。

嗯，还有一种简便一点的公式：

这里用到了数组公式：

=SUM(IF(B$2:B$72=G2,C$2:E$72))

首先用IF函数进行处理，如果B2:B72中等于G2单元格指定的品牌，就返回C2:E72单元格区域中对应行的数值，否则返回逻辑值FLASE，最后使用SUM函数求和。

这里因为执行了数组运算，所以公式编辑完成后，要单击编辑栏，再按住Shift+Ctrl不放，按回车。注意公式两侧的花括号是自动生成的，手工敲入是无效的哦。

这个公式比较简短，但是需要按三键才能输入，有些新人朋友可能一时还找不到感觉，耐心点，多试几次吧。

还有一个不需要按三键输入的公式：

=SUMPRODUCT((B$2:B$72=G2)*C$2:E$72)

首先使用B$2:B$72=G2判断B列是否等于指定的品牌，得到逻辑值TRUE或是FLASE，再用逻辑值与C2:E72的数值相乘，最后使用SUMPRODUCT计算出乘积之和。

本例中，除了使用以上公式之外，如果使用数据透视表汇总，将会更加便捷方便，你会使用吗？

作者：Hoa小熊猫 图文整理：祝洪忠

ExcelHome，微软技术社区联盟成员

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

在Excel中，我们还会经常对数据进行统计计算，这里呢，简单的和大家分享关于隔行隔列求和的实际案例讲解，之前很多小伙伴有问到对应情况。今天和大家谈谈吧。

1、利用sumproduct函数进行隔行求和

这里要求的是成都、重庆的总销售金额，如求成都总销售金额，这里我们用到的公式是：

=SUMPRODUCT((MOD(ROW(C2:C16)-1,2)=1)*C2:C16)。

接下来我们分析对这个函数组合的相关认识：

①sumproduct函数的使用原理。

sumproduct函数返回数组区域乘积的和，如下图案例：

因此可以总结出sumproduct函数求和语法：

sumproduct（（条件）*（求和范围））

②MOD函数代表的是求余函数，在这里是用来判断奇偶数。Row函数代表的是返回行号的函数，这里是要进行对区域进行减1操作，最后得到的行号然后进行判断奇偶性。如果是奇数列的话，条件就是mod（）=1。

2、利用sumif函数隔列求和

比如下方情况，现在要是隔列去求和。

这里我们直接用到sumif函数，条件求和，这里用到的公式是：

=SUMIF($B$1:$G$1,$B$1,B2:G2)，

这里的条件范围和条件都进行绝对了，因为这些都是不变的，而求和区域是变化的，所以这里就用到相对引用，但是这里的条件对应名称要固定一致，

上面就是和大家分享的隔行隔列求和的两种思路，希望能帮助到大家。如果喜欢，不要忘记点赞哦~

如果你是新朋友，扫码关注下方二维码，便每天可以和小菜一起学习，一起提升技能！当然大家也可以技巧分享，学习更多办公技巧哦！

每天一起学习，一起进步。