收藏的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精英培训

转载自百家号作者：excel函数教程

工作簿中多表合并为一表，纯工具操作，不会写公式函数和VBA的同学们不用担心，看完之后就会了。操作版本为微软office2016，使用工具是Power Query，如果您的版本是excel2010或2013，可以在网上下载Power Query插件安装即可。

以下图为例，需要将工作簿中的三个工作表合并为一个工作表。（不管你工作表有多少个，都可以轻松合并）

选择数据选项卡-获取数据-至文件-从工作簿 ，然后选择电脑上要合并的工作簿点击导入即可

导入之后出现导航器对话框，选择工作簿文件，点击下方编辑

这时候进入查询编辑器对话框，然后点击Date列右边的扩展按钮，勾选展开，然后点击确定。

这时可以看到数据已经展开显示出来了。

但是因为每个工作表都有一行标题，所以标题也被合并进去了，这时候选择开始选项卡下的“将第一行用作标题”

然后筛选姓名这一列，将“姓名”取消勾选，点击确定。

之后将下面的滚动栏滚到最右边，可以发现有三列多余的数据，按住CTRL键选选择三列数据，点击开始选项卡下的删除列即可。

最后点击开始选项卡的“关闭并上载至”

就可以看到自动新建了一个合并后的工作表数据。

如果以后分别在其它三个工作表中有增加了新数据，可以直接点击数据选项卡下的“全部刷新”，数据就会同步到合并工作表中。

收藏的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精英培训

在使用Excel时，经常会通过编辑公式来进行计算，但使用公式时，如果把含公式的单元格复制粘贴到其他位置，则公式的计算结果可能就会发生改变，另外更改公式中引用单元格的内容或者删除引用的单元格等都会令公式的计算结果发生改变。如果想避免这种情况，通常需要把公式转变成数字，Excel中把公式转变成数字的方法有多种，这里以Excel2007为例，介绍一种个人认为是最快速的方法，供大家参考。

●例如下图表格“合计”列中显示的内容虽然是数字，但如果选中其中的单元格，在编辑栏就会发现实际上该列中的内容都是公式，显示的数字只是公式的计算结果。

●如果想公式转变成数字，一般比较常用的是选择性粘贴的方法。即先选择公式所在的区域，进行复制操作，再在该区域中点击鼠标右键，然后点击右键菜单中的“选择性粘贴”。

●再在选择性粘贴对话框中点击“数值”选项，最后点击“确定”按钮，则所选区域中的公式就转变成数字了。

●上述选择性粘贴的转换方法虽然也不算多复杂，但实际上还有更快的转换方法。如果只是想转换一个单元格中的公式，可以在该单元格中双击鼠标，则该单元格就会处于编辑状态，显示的内容变成了实际的公式。

●此时按键盘的“F9”键，则该单元格中的内容就会立即变成数字，即公式的计算结果。

●如果想把一个区域内的单元格中的公式转变成数字，也有一种更快的方法：先选中要转换的区域。

●选中区域后，将鼠标指针指向区域的边缘，当指针形状变成四个箭头时，按住鼠标右键不放。

●按住鼠标右键不放的同时，向左侧或者右侧移动鼠标，移动一列即可。

●不要松开鼠标右键，再将鼠标移回选择区域的位置。

●鼠标移回选择区域的位置后，松开鼠标右键，此时会弹出一个菜单，点击其中的“仅复制数值”。

●点击后，选择区域内单元格中的公式就都变成数字了。

上述介绍的只是个人认为最快将Excel公式变成数字的方法。当然如果会编辑宏，则制作一个专门的宏会更快的将公式变成数字，但启用宏可能会有一定的安全问题，另外也不适合非相关专业的人员。如果读者还有其他更快的方法，欢迎在评论中提出，大家互相学习。

最近问函数问题的学员优点多，所以小编在这里给大家介绍一个功能，可以让我们在使用的过程中少出一点错误——审核公式。

公式审核中有错误检查和追踪引用的功能，可以先通过运用错误检查找到公式不一致的单元格，然后借助追踪引用单元格显示出错误的引用。

当然它的功能远不止于此：快速找到引用的单元格，快速显示所有公式，还可以查看函数计算过程等等！

那今天小编就来教大家如何使用公式审核中的错误检查，来追踪不一致公式的所有公式引用，大家可以跟着小编来学习一下。

首先我们可以打开excel工作簿素材，在菜单栏中选择“公式”选项卡，在“公式审核”功能组中单击“错误检查”按钮。

在弹出的“错误检查”对话框中，我们可以看到表格中有的错误。

然后单击“下一个”按钮，如果没有其他错误，单击“确定”按钮。

多次单击“公式审核”功能组中的“追踪引用单元格”按钮，直到显示所有公式的引用，即可完成操作。

如果你想要去掉箭头，在“公式审核”功能组中点击“移去箭头”命令，即可！

大多数时候，我们并不关心工作表里公式的情况，但是对于一些重要的工作表，我们对里面一些公式的原始性非常重视。那么怎么保护excel工作表，防止公式被他人复制？

这期课程，IT君将为大家解决这个问题。

就如小伙伴问的，如何做到就算复制到其他工作表，公式依然看不见，可不可以做到呢？

答案当然是可以的，只需要在保护工作表前，做一件事情，就是对公式的单元格进行隐藏工作。

具体操作：

选中公式所在的单元格，按Ctrl+1打开单元格式的窗口，在保护选项卡中勾选隐藏功能。看下GIF操作：

搞定后，在复制到其他表，公式就不会被复制了。看动图操作。是不是公式复制不了。

如果要做到连复制的功能都要限制住的话，有个隐蔽的“绝招”。保护完工作表后不允许他进行复制，真是够绝的。

方法也很简单，把保护所有的权限都去掉，尤其是下面两个选项一定要去掉。

具体操作如下：

总结：Excel的有些操作体现“细节”操作的微妙，不然IT君怎么说Office是一个伟大的软件呢？

顺便说一句，该技巧Excel2007版本以上有效哦！

文章若好，敬请收藏！

Excel表格中 符号 大全 汇总

一、公式中常用符号

： 表示一个单元格区域，如A1:B10+ - * / 加减乘除运算符> 大于号>= 大于等于号< 小于号<= 小于等于号<>不等于号，如=IF(A1<>"销售部",,)^数字 乘方运算，如2^3结果8。^(1/数字) 开方运算 8^(1/3)结果为2& 文本连续符，如 "A"& 1 结果为 A1* 通配符 表示任意字符多个字符? 通配符，表示单个任意字符{数字} 常量数组{公式} 数组公式标志，在公式后按Ctrl + shift + Enter后在公式两端自动添加的。$ 绝对引用符号，可以在复制公式时防止行号或列标发生变动，如A&1公式向下复制时，1不会变成2,3..如果不加$则会变化！ 工作表和单元格的隶属关于，如表格sheet1的单元格A1,表示为 Sheet1!A1

二、自定义格式中的符号

0 单个数字占位符，如果0的位数大于数字个数，会用0补齐，如123设置格式00000, 会显示成 00123# 单个数字占位符，和0区别是不会补位! 强制显示它后面的字符\ 作用同 ![ ] 设置条件，如 [>100][红色] 大于100的数字显示红色yyyy 4位年d 日m 月aaa 星期代码，只显示大写数字aaaa 星期代码，显示星期+大写数字ddd 星期代码，英文简写dddd 星期代码，英文全称mmm 英文短月份mmmm 英文月份完整单词h 小时[h] 显示大于24小时的数字m 分种s 秒* 用*后的字符补齐位置，让字符长度填满单元格_ 添加一个空格和_后的字符宽度相等@ 文本占位符，可以显示单元格的文本内容，如爱你 设置@@后可以显示为爱你爱你

Excel解释说明：想整理Excel中常用符号的想法来由来以久，由于lan一直没动手，可左等右等几还不见网上有动静，所以必须要动手了，同学们收藏备用吧。

工作中常常会遇到一些特殊情况，一个Excel工作簿中可能会有几十，或者上百个工作表。

这么多工作表存在一个Excel表中，该如何快速定位到特定的表格呢？如果能有一个工作表目录真是太方便了！

最常见的方法，要定义名称 + 长长的让人很难记住的Excel公式。制作这个目录对新手还是有点难度。

定义名称：Shname =MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(NOW())在目录表中任一单元格输入并复制以下公式=IFERROR(HYPERLINK("#'"&INDEX(Shname,ROW(A1))&"'!A1",INDEX(Shname,ROW(A1))),"")

这里分享一个非常简单的Excel目录制作方法（适合2003以上版本）

操作步骤：

1、输入公式

全选所有表，在前面添加一空列。然后在单元格A1中输入公式 =xfd1

注：这个方法的原理是输入让03版无法兼容的公式（03版没有XFD列），然后检查功能诱使Excel把所有工作表名列出来。

2、生成超链接列表

文件 - 信息 - 检查兼容性 - 复制到工作表，然后Excel会插入一个内容检查的工作表，并且在E列已自动生成带链接的工作表名称。

3、制作目录表

把带链接的工作表名称列表粘贴到“主界面”工作表中，替换掉'!A1，稍美化一下，目录效果如下：

4、制作返回主界面的链接

全选工作表 - 在任一个表的A1输入公式（会同时输入到所有表中）

=HYPERLINK("#主界面!A1","返回主界面")

注:HYPERLINK函数可以在Excel中用公式生成超链接

完工！如果以后新增了表格，手工添加校新表超链接也不麻烦，如果想完全自动，可以使用本文开头所介绍的使用宏表函数创建目录。

如果是针对多工作簿，而且工作簿存在多个工作表，该如何合并？

一个文件夹内有多个工作簿

一个工作簿中含有多个工作表

现在要将格式相同的所有表格合并在一张表格中，如果采用最原始的复制粘贴是多么恐怖的一件事。模拟的数据只是几个工作簿，而实际工作可能有几十个，光复制粘贴，手都废了。

而对于高手，编写一段VBA代码也要花1小时，有没适合普通人的简单办法？

有！不过前提是你安装了Excel2016。

Excel2016只需要一个简单的公式就可以搞定。

Step 01关闭打开的工作簿，新建一个新工作簿。单击数据→新建查询→从文件→从文件夹。

Step 02浏览到指定的文件夹，单击确定。

Step 03单击编辑。

Step 04单击添加列→自定义列，输入自定义列公式，确定。注意：字母区分大小写，必须保持一致。

=Excel.Workbook([Content])

Step 05单击自定义的扩展按钮，确定。

Step 06单击自定义.Data的扩展按钮，确定。

Step 07这样默认就将需要合并的列全部选择，右键删除其他列。

Step 08将第一行用做标题。

Step 09取消多余的标题筛选。

Step 10关闭并上载。

这样就合并成功。

因为有一些人第一次接触，所以我在截图的时候比较详细。这样看起来步骤就很多，熟练以后，实际上操作起来很快，1、2分钟就搞定。

批量输入公式批量修改公式快速查找公式显示公式部分计算结果保护公式隐藏公式显示所有公式把公式转换为数值显示另一个单元格的公式把表达式转换为计算结果快速查找公式错误原因

1批量输入公式

选取要输入的区域，在编辑栏中输入公式，按CTRL+ENTER即可批量填充公式。

2批量修改公式

如果是修改公式中的相同部分，可以直接用替换功能即可。

3快速查找公式

选取表格区域 - 按Ctrl+g打开定位窗口 - 定位条件 - 公式，即可选取表中所有的公式

4显示公式部分计算结果

选取公式中的要显示的部分表达式，按F9键

按F9键后的结果

想恢复公式，按esc退出编辑状态即可。

5保护公式

选取非公式的填写区域，取消单元格锁定。公式区域不必操作。

设置单元格格式后，还需要保护工作表：审阅 - 保护工作表。

6隐藏公式

隐藏公式和保护公式多了一步：选取公式所在单元格区域，设置单元格格式 - 保护 - 勾选“隐藏” - 保护工作表。

隐藏公式效果：

7显示所有公式

需要查看表中都设置了哪些公式时，你只需按alt+~组合键（或 公式选项卡 - 显示公式）

把公式转换为数值

8把公式转换为数值

公式转换数值一般方法，复制 - 右键菜单上点123（值）

9显示另一个单元格的公式

如果想在B列显示A列的公式，可以通过定义名称来实现。

公式 - 名称管理器 - 新建名称：G =get.cell(6,sheet3!a4)

在B列输入公式=G，即可显示A列的公式。

excel2013中新增了FORMULATEXT函数，可以直接获取公式文本。

10把表达式转换为计算结果

方法同上，只需要定义一个转换的名称即可。

zhi =Evaluate(b2)

在B列输入公式 =zhi即可把B列表达式转换为值

11快速查找公式错误原因

当一个很长的公式返回错误值，很多新手会手足无措，不知道哪里出错了。兰色介绍排查公式错误的技巧，3秒就可以找到错误。

下面兰色做了一个简单的小例子

【例】：如下图所示，单元格的公式返回值错误。要求排查出公式的哪部分出现了错误。

操作方法：

1、 打开单元格左上角绿三角，点“显示计算步骤”

2、在打开的“公式求值”窗口中，求值会自动停在即将出错的位置。这时通过和编辑栏中的公式比对，就可以找出产生错误的单元格。（D7）

如果公式中有多处错误，可以先修正前一次，然后再点显示计算步骤，查找下一处错误。