上次给大家分享了《2017年最全的excel函数大全8—文本函数（中）》，这次分享给大家文本函数（下）。

MID、MIDB 函数

描述

MID 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。

MIDB 根据您指定的字节数，返回文本字符串中从指定位置开始的特定数目的字符。

重要:

这些函数可能并不适用于所有语言。

MID 适用于使用单字节字符集 (SBCS) 的语言，而 MIDB 适用于使用双字节字符集 (DBCS) 的语言。 您的计算机上的默认语言设置对返回值的影响方式如下：

无论默认语言设置如何，函数 MID 始终将每个字符（不管是单字节还是双字节）按 1 计数。

当启用支持 DBCS 的语言的编辑并将其设置为默认语言时，函数 MIDB 会将每个双字节字符按 2 计数。 否则，MIDB 会将每个字符按 1 计数。

支持 DBCS 的语言包括日语、中文（简体）、中文（繁体）以及朝鲜语。

用法

MID(text, start_num, num_chars)MIDB(text, start_num, num_bytes)

MID 和 MIDB 函数用法具有下列参数：

文本必需。 包含要提取字符的文本字符串。start_num必需。 文本中要提取的第一个字符的位置。 文本中第一个字符的 start_num 为 1，以此类推。num_chars必需。 指定希望 MID 从文本中返回字符的个数。Num_bytes必需。 指定希望 MIDB 从文本中返回字符的个数（字节数）。

备注

如果 start_num 大于文本长度，则 MID 返回空文本 ()。如果 start_num 小于文本长度，但 start_num 加上 num_chars 超过了文本的长度，则 MID 只返回至多直到文本末尾的字符。如果 start_num 小于 1，则 MID 返回 错误值 #VALUE!。如果 num_chars 为负数，则 MID 返回 错误值 #VALUE!。如果 num_bytes 为负数，则 MIDB 返回 错误值 #VALUE!。

案例

NUMBERVALUE 函数

描述

以与区域设置无关的方式将文本转换为数字。

用法

NUMBERVALUE(Text, [Decimal_separator], [Group_separator ])

NUMBERVALUE 函数用法具有下列参数。

文本 必需。 要转换为数字的文本。Decimal_separator 可选。 用于分隔结果的整数和小数部分的字符。Group_separator 可选。 用于分隔数字分组的字符，例如，千位与百位之间以及百万位与千位之间。

备注

如果未指定 Decimal_separator 和 Group_separator 参数，则使用当前区域设置中的分隔符。如果 Decimal_separator 或 Group_separator 参数中使用了多个字符，则只会使用第一个字符。如果空字符串 () 被指定为文本参数，则结果为 0。文本参数中的空格（即使位于参数中间）也将被忽略。 例如，“ 3 000 ”将返回 3000。如果文本参数中多次使用小数分隔符，则 NUMBERVALUE 返回错误值 #VALUE! 。如果在文本参数中数组分隔符出现在小数分隔符之前，则将忽略数组分隔符。如果在文本参数中数组分隔符出现在小数分隔符之后，则 NUMBERVALUE 返回错误值 #VALUE! 。如果有任何参数无效，则 NUMBERVALUE 返回错误值 #VALUE! 。如果文本参数以一个或多个百分号 (%) 结束，这些百分号将用于结果计算。 如果文本参数中使用了多个百分号，则如同百分号用于公式中一样，这些百分号将累加。 例如，=NUMBERVALUE(9%%) 与公式 =9%% 返回的结果相同，都是 0.0009。

案例

PHONETIC 函数

描述

提取文本字符串中的拼音 (furigana) 字符。该函数只适用于日文版。

用法

PHONETIC(reference)

PHONETIC 函数用法具有下列参数：

引用必需。文本字符串或对单个单元格或包含 furigana 文本字符串的单元格区域的引用。

备注

如果 reference 为单元格区域，则返回区域左上角单元格中的 furigana 文本字符串。如果 reference 为不相邻单元格的区域，将返回错误值 #N/A。

案例

如果单元格 C4 中包含“

”，而单元格 B7 中包含“

”，则有：

=PHONETIC(C4)等于“

”

=PHONETIC(B7)等于“

”

PROPER 函数

描述

将文本字符串的首字母以及文字中任何非字母字符之后的任何其他字母转换成大写。 将其余字母转换为小写。

用法

PROPER(text)

PROPER 函数用法具有下列参数：

文本必需。 用引号括起来的文本、返回文本值的公式，或者对包含要进行部分大写转换文本的单元格的引用。

案例

REPLACE、REPLACEB 函数

描述

根据指定的字符数，REPLACE 将部分文本字符串替换为不同的文本字符串。

REPLACEB 使用其他文本字符串并根据所指定的字节数替换某文本字符串中的部分文本。

重要:

这些函数可能并不适用于所有语言。REPLACE 适用于使用单字节字符集 (SBCS) 的语言，而 REPLACEB 适用于使用双字节字符集 (DBCS) 的语言。 您的计算机上的默认语言设置对返回值的影响方式如下：无论默认语言设置如何，函数 REPLACE 始终将每个字符（不管是单字节还是双字节）按 1 计数。当启用支持 DBCS 的语言的编辑并将其设置为默认语言时，函数 REPLACEB 会将每个双字节字符按 2 计数。 否则，REPLACEB 会将每个字符按 1 计数。

支持 DBCS 的语言包括日语、中文（简体）、中文（繁体）以及朝鲜语。

用法

REPLACE(old_text, start_num, num_chars, new_text)REPLACEB(old_text, start_num, num_bytes, new_text)

REPLACE 和 REPLACEB 函数用法具有下列参数：

old_text必需。 要替换其部分字符的文本。start_num必需。 old_text 中要替换为 new_text 的字符位置。num_chars必需。 old_text 中希望 REPLACE 使用 new_text 来进行替换的字符数。Num_bytes必需。old_text 中希望 REPLACEB 使用 new_text 来进行替换的字节数。new_text必需。 将替换 old_text 中字符的文本。

案例

REPT 函数

描述

将文本重复一定次数。 使用 REPT 来在单元格中填充文本字符串的大量实例。

用法

REPT(text, number_times)

REPT 函数用法具有下列参数：

Text必需。 需要重复显示的文本。Number_times必需。 用于指定文本重复次数的正数。

备注

如果 number_times 为 0（零），则 REPT 返回 （空文本）。如果 number_times 不是整数，将被截尾取整。REPT 函数结果的长度不能超过 32,767 个字符，否则 REPT 返回 #VALUE!。

案例

RIGHT、RIGHTB 函数

描述

RIGHT 根据所指定的字符数返回文本字符串中最后一个或多个字符。

RIGHTB 根据所指定的字节数返回文本字符串中最后一个或多个字符。

重要:RIGHT 适用于使用单字节字符集 (SBCS) 的语言，而 RIGHTB 适用于使用双字节字符集 (DBCS) 的语言。 您的计算机上的默认语言设置对返回值的影响方式如下：

无论默认语言设置如何，函数 RIGHT 始终将每个字符（不管是单字节还是双字节）按 1 计数。当启用支持 DBCS 的语言的编辑并将其设置为默认语言时，函数 RIGHTB 会将每个双字节字符按 2 计数。 否则，RIGHTB 会将每个字符按 1 计数。

支持 DBCS 的语言包括日语、中文（简体）、中文（繁体）以及朝鲜语。

用法

RIGHT(text,[num_chars])RIGHTB(text,[num_bytes])

RIGHT 和 RIGHTB 函数具有下列参数：

文本必需。 包含要提取字符的文本字符串。num_chars可选。 指定希望 RIGHT 提取的字符数。Num_bytes可选。 按字节指定要由 RIGHTB 提取的字符的数量。

备注

Num_chars 必须大于或等于零。如果 num_chars 大于文本长度，则 RIGHT 返回所有文本。如果省略 num_chars，则假定其值为 1。

案例

SEARCH、SEARCHB 函数

描述

SEARCH和SEARCHB函数可在第二个文本字符串中查找第一个文本字符串，并返回第一个文本字符串的起始位置的编号，该编号从第二个文本字符串的第一个字符算起。例如，若要查找字母“n”在单词“printer”中的位置，可以使用以下函数：

=SEARCH(n,printer)

此函数会返回4，因为“n”是单词“printer”的第四个字符。

也可以在一个单词中搜索另一个单词。例如，以下函数：

=SEARCH(base,database)

会返回5，因为单词“base”是从单词“database”的第五个字符开始的。使用SEARCH和SEARCHB函数可以确定某个字符或文本字符串在另一个文本字符串中的位置，然后可使用MID和MIDB函数返回文本，或使用REPLACE和REPLACEB函数更改文本。本文中的案例 1中演示了这些函数。

重要:

这些函数可能并不适用于所有语言。只有在将 DBCS 语言设置为默认语言时，函数 SEARCHB 才会将每个字符按 2 个字节计数。否则，函数 SEARCHB 的行为与 SEARCH 相同，即将每个字符按 1 个字节计数。

支持 DBCS 的语言包括日语、中文（简体）、中文（繁体）以及朝鲜语。

用法

SEARCH(find_text,within_text,[start_num])SEARCHB(find_text,within_text,[start_num])

SEARCH 和 SEARCHB 函数具有下列参数：

find_text必需。要查找的文本。within_text必需。要在其中搜索find_text参数的值的文本。start_num可选。within_text参数中从之开始搜索的字符编号。

备注

SEARCH和SEARCHB函数不区分大小写。如果要执行区分大小写的搜索，可以使用FIND和FINDB函数。可以在find_text参数中使用通配符（问号 (?) 和星号 (*)）。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在字符前键入波形符 (~)。如果找不到find_text的值，则返回 错误值 #VALUE!。如果省略了start_num参数，则假设其值为 1。如果start_num不大于 0（零）或大于within_text参数的长度，则返回 错误值 #VALUE!。可以使用start_num来跳过指定数目的字符。以SEARCH函数为例，假设要处理文本字符串“AYF0093.YoungMensApparel”。若要在文本字符串的描述部分中查找第一个“Y”的位置，请将start_num设置为 8，这样就不会搜索文本的序列号部分（即本例中的“AYF0093”）。SEARCH函数从第 8 个字符开始，在下一个字符处查找在find_text参数中指定的字符，并返回数字 9。SEARCH函数总是返回从within_text参数的起始位置计算的字符的编号，如果start_num参数大于 1，则会计算跳过的字符。

案例

SUBSTITUTE 函数

描述

在文本字符串中用 new_text 替换 old_text。 如果需要在某一文本字符串中替换指定的文本，请使用函数 SUBSTITUTE；如果需要在某一文本字符串中替换特定位置处的任意文本，请使用函数 REPLACE。

用法

SUBSTITUTE(text, old_text, new_text, [instance_num])

SUBSTITUTE 函数用法具有下列参数：

文本必需。 需要替换其中字符的文本，或对含有文本（需要替换其中字符）的单元格的引用。old_text必需。 需要替换的文本。new_text必需。 用于替换 old_text 的文本。Instance_num可选。 指定要用 new_text 替换 old_text 的事件。 如果指定了 instance_num，则只有满足要求的 old_text 被替换。 否则，文本中出现的所有 old_text 都会更改为 new_text。

案例

T 函数

描述

返回值引用的文字。

用法

T(value)

T 函数用法具有下列参数：

值必需。 要测试的值。

备注

如果值是文字或引用文字，则 T 返回值。 如果值未引用文字，则 T 返回 （空文字）。由于 Microsoft Excel 会根据需要自动转换值，因此通常无需在公式中使用 T 函数。 提供此函数是为了与其他电子表格程序兼容。

案例

TEXT 函数

描述

TEXT函数可通过格式代码向数字应用格式，进而更改数字的显示方式。如果要按更可读的格式显示数字，或者将数字与文本或符号组合，它将非常有用。

注意:TEXT 函数会将数字转换为文本，这可能使其在以后的计算中难以引用。最好将原始值保存在一个单元格中，然后在另一单元格中使用 TEXT 函数。随后如果需要构建其他公式，请始终引用原始值，而不是 TEXT 函数结果。

TEXT 函数最简单的形式表示：

=TEXT(Value you want to format, Format code you want to apply)

下面是一些常用案例，可将其直接复制到 Excel 自行进行试验。请注意引号内的格式代码。

注意:虽然可使用 TEXT 函数更改格式，但这不是唯一的方法。不使用公式也可更改格式，方法是：按CTRL+1（或在 Mac 上按

+1），然后从“格式单元格”“数字”对话框中选择所需格式。

常见方案

TEXT函数很少单独使用，通常与其他内容配合使用。假设你想合并文本和数值，如“报告打印日期:12/03/14”或“每周收入: $66,348.72”。你可将此内容手动键入 Excel，但这违背了让 Excel 执行此操作的目的。遗憾的是，在合并文本和带格式的数字（如日期、时间、货币等）时，Excel 不知道你所希望的显示方式，因此会省去数字格式。这便是TEXT函数的价值所在，因为它能使用“格式代码”（如日期格式“YY/MM/DD”）强制 Excel 按照你所希望的方式设置值的格式。

以下案例将显示不使用TEXT函数合并文本和数字的结果。本例中，我们使用与号() 连接一个文本字符串、一个空格 ( ) 和一个带=A2 B2的值。

正如你看到的，Excel 删除了 B2 单元格中的日期格式。下一案例将展示 TEXT 函数如何使你应用所需的格式。

更新的公式为：

Cell C2:=A2 TEXT(B2,mm/dd/yy)- Date format

TEXTJOIN 函数

描述

TEXTJOIN 函数将多个区域和/或字符串的文本组合起来，并包括你在要组合的各文本值之间指定的分隔符。如果分隔符是空的文本字符串，则此函数将有效连接这些区域。

用法

TEXTJOIN(分隔符, ignore_empty, text1, [text2], …)

例如，=TEXTJOIN ( ,TRUE, , 太阳, 将, 升, 起、明天) 将�...

实用性非常强的Excel小技巧，希望能够帮助大家！

一、快速筛选。

方法：

1、选定目标单元格。

2、右击-【筛选】-【按所选单元格的值筛选】。

二、随机生成字母。

方法：

在目标单元格中输入公式：=INDEX($F$3:$F$9,RANDBETWEEN(1,7))。

三、逆向查找指定内容。

方法：

1、Ctrl+F打开【查找和替换】对话框。

2、输入查找内容，并单击【查找下一个】会发现查找内容向下移动。

3、如果要查找内容向上（逆向）移动，需首先按住Shift键，再单击【查找下一个】。

四、批量删除前缀。

方法：

1、选定目标单元格。

2、【数据】-【分列】-【固定宽度】-【下一步】。

3、在【数据预览区】要删除的字符后单击并【下一步】。

4、选中【不导入此列（跳过）】并【完成】。

五、快速选取指定列。

方法：

1、选中目标单元格。

2、【公式】-【根据所选内容创建】，选中【首行】并【确定】。

3、单击左上角名称框的下拉箭头，选取相应的名称即可。

六、快速隐藏相同列。

方法：

1、选取首行并Ctrl+F打开【查找和替换】对话框。

2、在【查找内容】中输入要隐藏列的【列标】并【全部查找】。

3、快捷键：Ctrl+A全选并【关闭】。

4、快捷键：Ctrl+0隐藏。

七、隔行填充颜色。

方法：

1、选中目标单元格。

2、【条件格式】-【新建规则】，选择规则类型【使用公式确定要设置格式的单元格】，并在【为符合此公式的值设置格式】中输入：=mod(row(),2)。

3、单击右下角【格式】-【填充】，选取填充色并【确定】。

八、快速隔列求和。

方法：

1、在目标单元格中输入公式：=SUMIF($C$3:$H$3,I$3,$C4:$H10)。

备注：

特别注意绝对引用和相对引用的使用。

Excel的强大之处在于数据的处理分析，如果对简单的数据不会处理分析，那就有些OUT了……

一、自动记录资料录入时间。

方法：

1、选中记录时间单元格。

2、快捷键：Ctrl+1打开【设置单元格格式】对话框，选择【分类】中的【自定义】，【类型】中的【yyyy/m/d h:mm】。

3、确定。

4、【文件】-【选项】-【公式】，选择【计算选项】中的【启用迭代计算】。

5、目标单元格中输入公式：=IF(B3="","",IF(F3="",NOW(),F3))。

二、数据可视化。

方法：

1、设置目标单元格的字体为：Arial Black，字号为80号。

2、输入公式：=REPT("-",C3)。

3、Ctrl+Enter填充。

三、按指定条件填充序号。

方法：

1、选定目标单元格。

2、输入公式：=IF(B3<>"",1,D2+1)。

3、Ctrl+Enter填充。

四、指定条件下的排序。

方法：

1、选定目标单元格。

2、输入公式：=COUNTIF(E$3:E3,E3)。

3、Ctrl+Enter填充。

五、对合并单元格填充序号。

方法：

1、选定目标单元格。

2、输入公式：=MAX(A$2:A2)+1。

3、Ctrl+Enter填充。

六、对合并单元格填充序号。

方法：

1、选定目标单元格。

2、输入公式：=COUNTA(A$2:A2)。

3、Ctrl+Enter填充。

七、合并单元格计数。

方法：

1、选定目标单元格。

2、输入公式：=COUNTA(E3:E9)-SUM(F4:F9)。

3、Ctrl+Enter填充。

八、合并单元格求和。

方法：

1、选定目标单元格。

2、输入公式：=SUM(C3:C9)-SUM(F4:F9)。

3、Ctrl+Enter填充。

实际的工作中，我们用到的函数公式并不多，相对于那些高大上的函数公式，我们更多用到的是一些常见的，相对比较简单的函数公式。例如IF函数、SUMIF函数、COUNTIF函数等……

一、IF函数：条件判断。

目的：判断相应的分数，划分类别。

方法：

在目标单元格中输入公式：=IF(C3=100,"满分",IF(C3>=95,"优秀",IF(C3>=80,"良好",IF(C3>=60,"及格","不及格"))))。

解读：

IF函数不仅可以单独进行条件判断，还可以嵌套进行使用。

二、SUMIF函数：单条件求和。

目的：计算出男生或女生的成绩总和。

方法：

在目标单元格中输入公式：=SUMIF(D3:D9,G3,C3:C9)。

解读：

SUMIF函数的语法结构是：=SUMIF(条件范围，条件，求和范围）。其中求和范围可以省略，如果省略，默认和条件范围一致。主要作用是对符合条件的数进行求和。

三、COUNTIF函数：单条件计数。

目的：按性别统计人数。

方法：在目标单元格中输入公式：=COUNTIF(D3:D9,G3)。

解读：COUNTIF函数的语法结构是：=COUNTIF（条件范围，条件）。主要作用是统计符合条件的数。

四、LOOKUP函数：单条件或多条件查询。

目的：查询学生的考试成绩档次。

方法：

在目标单元格中输入公式：=LOOKUP(1,0/(B3:B9=$G$3),E3:E9)。

解读：

此方法是LOOKUP函数的变异用法，查询值为1，而0/(B3:B9=$G$3)的判断结果是党B3:B9范围中的值等于G3单元格中的值时，返回TRUE，0/TRUE等于0，如果不等于G3单元格中的值时，返回FALSE ,0/FALSE,返回FALSE ,然后用查询值对0/(B3:B9=$G$3)返回的结果进行对比分析，返回最接近查询值的对应位置上的值。

五、LOOKUP函数：逆向查询。

目的：根据分数查询出对应的姓名。

方法：

在目标单元格中输入公式：=LOOKUP(1,0/(C3:C9=$G$3),B3:B9)。

六、INDEX+MATCH：查询号搭档。

目的：查询对应人的所有信息。

方法：

在目标单元格中输入公式：=INDEX($B$2:$E$9,MATCH($G$3,$B$2:$B$9,0),MATCH(H$2,$B$2:$E$2,0))。

七、DATEDIF函数：计算年龄。

目的：计算销售员的年龄。

方法：

在目标单元格中输入公式：=DATEDIF(D3,TODAY(),"y")。

解读：

DATEDIF函数是系统的隐藏函数，其主要作用是计算两个时间段之间的差异，可以是年、月、日任意一种需求。

八、TEXT+MID：提取出生年月。

目的：根据身份证号提取出生年月。

方法：

在目标单元格中输入公式：=TEXT(MID(C3,7,8),"0-00-00")。

九、SUMPRODUCT:中国式排名。

目的：对成绩进行排名。

方法：在目标单元格中输入公式：

=SUMPRODUCT(($D$3:$D$9>D3)/COUNTIF($D$3:$D$9,$D$3:$D$9))+1或=SUMPRODUCT((D3>$D$3:$D$9)/COUNTIF($D$3:$D$9,$D$3:$D$9))+1。

解读：

1、=SUMPRODUCT(($D$3:$D$9>D3)/COUNTIF($D$3:$D$9,$D$3:$D$9))+1为降序排序。

2、=SUMPRODUCT((D3>$D$3:$D$9)/COUNTIF($D$3:$D$9,$D$3:$D$9))+1为升序排序。

1 VLOOKUP函数

VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配）

2 向量形式LOOKUP(lookup_value,lookup_vector,result_vector) 数组形式LOOKUP(lookup_value,array)

3 IF函数(判断条件,符合条件时返回的值,不符合条件时返回的值)

4 SUMIF函数

SUMIF(要找的内容所在的区域,要找的内容,与要找的内容所在的区域对应的数值区域)

5 COUNTIF函数

COUNTIF(要找的内容所在的区域,要找的内容)

每天更新Excel知识，喜欢的点击关注

上午好，伙伴们！丢掉Excel帮助文件，跟小编一起轻松学常用的十大Excel函数。

一、IF函数

作用：条件判断，根据判断结果返回值。

用法：IF(条件,条件符合时返回的值，条件不符合时返回的值)

案例：假如国庆节放假7天，我就去旅游，否则就宅在家。

=IF(A1=7,"旅游","宅在家")，因为A1单元格是3，只放假3天，所以返回第二参数，宅在家。

二、时间函数

TODAY函数返回日期。NOW函数返回日期和时间。比如要获取今天的日期，可以输入：=TODAY()，要获取日期时间，可以输入：=NOW()

计算部落窝教育EXCEL贯通班上线多少天了，可以使用：=TODAY()-开始日期

三、最大值函数

excel最大值函数常见的有两个，分别是Max函数和Large函数。

案例：分别取出产品A、产品B、产品C在2015年6月1日-6月10日的最大产量。

在B12单元格输入公式：=Max(B2:B11)，然后向右拖动复制得到产品B和产品C的最大产量。前面我们说了excel取最大值函数有MAX函数和Large函数，那么Large函数一样可以做到，公式为=Large(B2:B11,1)。

Max函数只取最大值，而large函数会按顺序选择大，比如第一大的、第二大的、第三大的。

四、条件求和：SUMIF函数

作用：根据指定的条件汇总。

用法：=SUMIF(条件范围,要求,汇总区域)

SUMIF的第三个参数可以忽略，第三个参数忽略的时候，第一个参数应用条件判断的单元格区域就会用来作为需要求和的区域。

五、条件计数

说到Excel条件计数，下面几个函数伙伴们需要了解一下。

COUNT函数：数字控，只要是数字，包含日期时间也算是数值，都统计个数。

案例：A1：B6区域，用count函数统计出的数字单元格个数为4。日期和时间也是属于数字，日期和时间就是特殊的数字序列。

COUNTA函数（COUNT+A）：统计所有非空单元格个数。

输入公式=COUNTA(A1:C5)，返回6，也就是6个单元格有内容。

COUNTIF函数（COUNT+IF）：统计符合条件的单元格个数。

语法：=countif（统计的区域，“条件”）

统计男性有多少人：=COUNTIF(B2:B8,"男")

六、查找函数

VLOOKUP(查找值,查找区域,返回查找区域的第几列,精确还是模糊查找)

E4单元格输入公式：=VLOOKUP(E2,A:B,2,)

Excel曾经一度出现了严重Bug，主要有两种比较悲催的情况，首先是这种：

更加悲催的是这种：

言归正传，今天和大家分享一组常用函数公式的使用方法：职场人士必须掌握的12个Excel函数，用心掌握这些函数，工作效率就会有质的提升。

建议收藏备用着，有时间多学习操练下。

目录：数字处理、判断公式、统计公式、求和公式、查找与引用公式、字符串处理公式、其他常用公式等

一、数字处理

01.取绝对值

=ABS(数字)

02.数字取整

=INT(数字)

03.数字四舍五入

=ROUND(数字,小数位数)

二、判断公式

04.把公式返回的错误值显示为空

公式：C2

=IFERROR(A2/B2,"")

说明：如果是错误值则显示为空，否则正常显示。

05.IF的多条件判断

公式：C2

=IF(AND(A2<500,B2="未到期"),"补款","")

说明：两个条件同时成立用AND,任一个成立用OR函数。

三、统计公式

06.统计两表重复

公式:B2

=COUNTIF(Sheet15!A:A,A2)

说明：如果返回值大于0说明在另一个表中存在，0则不存在。

07.统计年龄在30~40之间的员工个数

=FREQUENCY(D2:D8,{40,29})

08.统计不重复的总人数

公式：C2

=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。

09.按多条件统计平均值

F2公式

=AVERAGEIFS(D:D,B:B,"财务",C:C,"大专")

10.中国式排名公式

=SUMPRODUCT(($D$4:$D$9>=D4)*(1/COUNTIF(D$4:D$9,D$4:D$9)))

四、求和公式

11.隔列求和

公式：H3

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

或

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

说明：如果标题行没有规则用第2个公式

12.单条件求和

公式：F2

=SUMIF(A:A,E2,C:C)

说明：SUMIF函数的基本用法

13.单条件模糊求和

公式：详见下图

说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。

14.多条求模糊求和

公式：C11

=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)

说明：在sumifs中可以使用通配符*

15.多表相同位置求和

公式：b2

=SUM(Sheet1:Sheet19!B2)

说明：在表中间删除或添加表后，公式结果会自动更新。

16.按日期和产品求和

公式：F2

=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)

说明：SUMPRODUCT可以完成多条件求和

五、查找与引用公式

17.单条件查找

公式1：C11

=VLOOKUP(B11,B3:F7,4,FALSE)

说明：查找是VLOOKUP最擅长的，基本用法

18.双向查找

公式：

=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))

说明：利用MATCH函数查找位置，用INDEX函数取值

19.查找最后一个符合条件记录

公式：详见下图

说明：0/(条件)可以把不符合条件的变成错误值，而lookup可以忽略错误值

20.多条件查找

公式:详见下图

说明:公式原理同上一个公式

21.指定非空区域最后一个值查找

公式;详见下图

说明：略

22.区间取值

公式：详见下图

公式说明：VLOOKUP和LOOKUP函数都可以按区间取值，一定要注意，销售量列的数字一定要升序排列。

六、字符串处理公式

23.多单元格字符合并

公式：c2

=PHONETIC(A2:A7)

说明：Phonetic函数只能对字符型内容合并，数字不可以。

24.截取除后3位之外的部分

公式：

=LEFT(D1,LEN(D1)-3)

说明：LEN计算出总长度,LEFT从左边截总长度-3个

25.截取 - 之前的部分

公式:B2

=Left(A1,FIND("-",A1)-1)

说明：用FIND函数查找位置，用LEFT截取。

26.截取字符串中任一段

公式:B1

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))

说明:公式是利用强插N个空字符的方式进行截取

27.字符串查找

公式：B2

=IF(COUNT(FIND("河南",A2))=0,"否","是")

说明: FIND查找成功，返回字符的位置，否则返回错误值，而COUNT可以统计出数字的个数，这里可以用来判断查找是否成功。

28.字符串查找一对多

公式：B2

=IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其他","东北")

说明：设置FIND第一个参数为常量数组，用COUNT函数统计FIND查找结果

七、日期计算公式

29.两日期间隔的年、月、日计算

A1是开始日期（2011-12-1），B1是结束日期(2013-6-10)。计算：

相隔多少天？=datedif(A1,B1,"d") 结果：557

相隔多少月? =datedif(A1,B1,"m") 结果：18

相隔多少年? =datedif(A1,B1,"Y") 结果：1

不考虑年相隔多少月？=datedif(A1,B1,"Ym") 结果：6

不考虑年相隔多少天？=datedif(A1,B1,"YD") 结果：192

不考虑年月相隔多少天？=datedif(A1,B1,"MD") 结果：9

datedif函数第3个参数说明：

"Y" 时间段中的整年数。

"M" 时间段中的整月数。

"D" 时间段中的天数。

"MD" 天数的差。忽略日期中的月和年。

"YM" 月数的差。忽略日期中的日和年。

"YD" 天数的差。忽略日期中的年。

30.扣除周末的工作日天数

公式：C2

=NETWORKDAYS.INTL(IF(B2

说明：返回两个日期之间的所有工作日数，使用参数指示哪些天是周末，以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日

八、其他常用公式

31.创建工作表目录的公式

把所有的工作表名称列出来，然后自动添加超链接，管理工作表就非常方便了。

使用方法：

第1步：在定义名称中输入公式：

=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(NOW())

第2步、在工作表中输入公式并拖动，工作表列表和超链接已自动添加

=IFERROR(HYPERLINK("#'"&INDEX(Shname,ROW(A1))&"'!A1",INDEX(Shname,ROW(A1))),"")

32.中英文互译公式

=FILTERXML(WEBSERVICE("http://fanyi.youdao/translate?&i="&A2&"&doctype=xml&version"),"//translation")

excel中的函数公式千变万化，今天就整理这么多了。如果你能掌握一半，在工作中也基本上遇到不难题了。

建议收藏备用着，有时间多学习操练下。

1、为了更加的直观，有的时候我们需要在excel中符号来表示，比如输入1的时候，让他显示"√"，输入2显示"×"。要达到这个目的，我们可以这样操作，自定义代码[=1]"√";[=2]"×"后，可以实现输入1显示"√"，输入2显示"×"

2、隐藏单元格内所有内容，只需要输入三个分号 ;;;

3、按条件设置颜色，自定义格式中双括号内[ ]可以表示颜色,也可以表示条件. 大于100的数字显示字体显示成红色的自定义代码为 ：[红色][>100]0

4、快速求和，只需按住快捷键组合【Alt+=】即可实现快速求和。

5、在Excel中获取网络数据，数据→导入外部数据→新建web查询→输入表格所在的网址→选择（点绿图标边的小框）→点击导入即可。

6、对合并单元格求和，如下图所示。

7、已经录入数据的表格，要调整某一列的位置怎么办？

只要选中要调整的数据区域，然后按住Shift键不放，光标放到绿色边框位置后，按下鼠标左键不放拖动即可：

8、快速插入多行，要插入几行，就拖动鼠标选中多个行号，然后按住Shift键不放，光标变成上下箭头形状时往下拖动，就可以快速插入多行。

一、根据身份证号码判断性别

=IF(MOD(MID(A2,15,3),2),"男","女")

二、根据出生年月计算年龄

=DATEDIF(A2,TODAY(),"y")&"周岁"

三、根据身份证号码提取性别：

=IF(MOD(MID(A4,15,3),2),"男","女")

四、下一个工作日是哪天

=WORKDAY(A2,1,A5:A7)

五、计算合同到期：

=EDATE(A4,3)

六、根据人员查询部门：

=VLOOKUP(C14,B1:C10,2,)

七、将“20150601”格式转换为日期

=--TEXT(A2,"0!/00!/00")

八、计算个人所得税：

=ROUND(MAX((A3-3500)*0.1*{0.3,1,2,2.5,3,3.5,4.5}-5*{0,21,111,201,551,1101,2701},0),2)

公式中的 A3，指的是应发工资减去应扣减的五金之后的余额。

九、根据税后工资倒推税前工资

=IF(A10

十、根据个税倒推税前工资：

=ROUND(MIN(((A16+5*{0,21,111,201,551,1101,2701})/(0.1*{0.3,1,2,2.5,3,3.5,4.5}))+3500),2)

公式中的 A16为缴纳的个税，注意个税额不能为0，年终奖不适用本公式。

End.

文章转载自：Excel之家ExcelHome

一、SUM函数（求和），函数公式为=sum（）

二、AVERAGE函数（求平均数），函数公式为=AVERAGE( )

三、count函数（计数），函数公式为=COUNT( )

四、IF函数（符合条件的数值），IF函数公式为：=IF(逻辑判断,为TRUE时的结果,为FALSE时的结果)

五、SUMIF

主要用途：对报表范围中符合指定条件的值求和，即条件求和

例如下表有很多班级的成绩，需要求下A班的总成绩：

第一个参数值：A:A，即为条件判断的区域，即整个A列；

第二个参数值，E2，即在4列中等于E2单元格数值的数值

第三个参数值，C:C，即我们需要求和的区域，

整体函数意思：满足A列中班级等于A班的总成绩

六、COUNTIF：主要用途：对指定区域中符合指定条件的单元格计数的一个函数，即条件计数

例如下表我们需要计算本次考试中所有及格的人数，

第一个参数值：C:C，即所有计算的某个条件区域，即本例所需的成绩所在区域

第二个参数值：">=60"即满足条件大于等60，

另如果是手动写公式请注意因为此处为文本类型，所以需要加上英文状态下的双引号，如果此处是等于E2单元格的值，则，只需要他选择E2即可，不需要双引号。

其实看了上边两个公式后，了解数据透视表的人会知道，这两个公式和基本的数据透视表的实现的效果是一样的，但是函数有个好处就是我们可以建立模板，比如下次我们只需要把原始数据粘贴到源数据出，则所有结果自动生成。

七、VLOOKUP：主要用途：按列查找，最终返回该列所需查询列序所对应的值，纵向查找函数

例如下表，我们需要其中某几个人的成绩

第一个参数：B2，即我们要查找的值或者说所要匹配的数值

第二个参数：B:C，B即我们所要查找的值所在的列，C我们想得到的值所在的列，所以选择了B到C区域

第三个参数：2，即从B列开始数，C列在第几列

第四个参数：0或TRUE真实匹配，1或FLASH模糊匹配

当F2按照公式匹配完后，鼠标放在F2右下角，变成+号的时候，双击即可

注，我们选择函数的时候一般出现的是类似下边的这样一个对话框，直接按照我们上边所讲的在excel里选择或填写（有些条件值需要填写）即可

八、MIN和MAX：即在单元格区域中找到最大和最小的数值，函数公式：=max（）或=min（）

这些函数是在我们日常办公最为常用的一些函数，建议大家可以收藏保留，以备不时之需(#^.^#)