excel常用的33个函数-excel删除后自动排序 收藏 0

常用的excel文本函数都在这了，共计17个，动图讲解，简单易懂

1.连字符：&

公式：=B3&C3

2.连续区域合并

PHONETIC函数，

用法：=PHONETIC（需要合并的区域）

公式：=PHONETIC(A2:C2)

3. 不连续区域链接

CONCATENATE函数

用法：=CONCATENATE（单元格1，单元格2，,,,,,等)

参数仅能选择一个一个的单元格

公式：=CONCATENATE(B3,D3)

4. 重复文本

REPT函数

用法：=REPT（重复内容，重复几次)

公式：=REPT(B4,5)

文本比较适用于有规律可循的数据

1. 从右开始提取

RIGHT函数

用法：=RIGHT（字符串，从右开始提取几位）

公式：=RIGHT(C3:C9,2)

2. 从左开始提取

LTFT函数

用法：=RLEFT（字符串，从左开始提取几位）

公式：=LEFT(C3:C9,3)

3. 先选定位置开始截取

MID函数

用法：=MID（字符串，从第几位开始截取，截取几位数）

公式：=MID(D3:D9,4,4)

1. 删除单元格中所有非打印字符

CLEAN函数

用法：=CLEAN（要清洗的单元格）

删除不包括空格，只能删除不能打印的字符如：换行符等

公式：=CLEAN(A1)

2. 删除单元格中的多余空格

TRIM函数

用法：=TRIM（要清洗的单元格）

删除首尾的多余空格，删除数据中的多余空格，会在数据间保留一个空格

1. 将旧文本替换为新文本

SUBSTITUTE函数

用法：=SUBSTITUTE（需要替换的文本，旧文本，新文本，第N个旧文本）

第四个参数表示替换第几个旧文本

如果省略表示全部替换

公式：SUBSTITUTE(C3,\” \”,\”\”)

2. 指定位置将旧字符替换为新字符

REPLACE函数

用法：=REPLACE（需要替换的文本，开始替换位置，替换几个字符，替换为什么）

公式：=REPLACE(C4,4,4,\”****\”)

1. 查找字符位置

FIND函数

用法：=FIND（寻找的字符，寻找的字符串，从寻找字符串的第几位查找）

第三个参数如果忽略，则默认从第一个字符开始查找

不可使用通配符

公式：=FIND(\”男\”,C3)

2. 查找字符位置

SEARCH函数

用法：=SEARCH（寻找的字符，寻找的字符串，从寻找字符串的第几位查找）

第三个参数如果忽略，则默认从第一个字符开始查找

可使用通配符

公式：=SEARCH(\”王?\”,C3)

1.计算文本字符数

LEN函数

用法：=LEN(字符串）

计算字符串的文本个数，数值也按一个文本计算

公式：=LEN(C3)

2. 计算文本字节数

LENB函数

用法：=LENB(字符串）

数字与字母占1个字节，汉字占2个字节

公式：=LENB(C3)

1. 字母大小写转换

Upper函数：小写字母变大写

Lower函数：大写字母变小写

用法：=Upper（字符串）

=Lower（字符串）

2. 格式转换函数

TEXT函数：对数据进行格式转换

用法:TEXT(数值，想要设置的格式）

这个实在太多了我们还是以一张表格来看看把

第二参数必须以引号引用后再输入

怎么样？你还知道哪些工作中常用的文本函数呢？

我是excel从零到一，关注我持续分享更多excel技巧

这几个Excel函数，简单又常用

小伙伴们好啊，今天咱们来分享一组简单常用的Excel函数公式，点滴积累，也能提高工作效率。

1、如下图所示，要统计部门为销售一部，并且单笔额度在500（万元）以上的总销售额。

F2单元格公式为：

=SUMIFS(C:C,B:B,E2,C:C,\”>500\”)

SUMIFS常用写法为：

=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2……)

注意，求和区域是在最开始的位置。

2、如下图，要根据E列的部门，计算该部门的最高销售额。

F3单元格公式为：

=MAXIFS(C:C,A:A,E3)

MAXIFS用来统计符合多个条件的最大值，用法与SUMIFS类似。

3、如下图，要根据I3单元格指定的省份，在左侧对照表中查询省会城市，J3单元格公式为：

=VLOOKUP(I3,B2:G8,3,0)

VLOOKUP函数的常用方法是：

VLOOKUP(要找谁,在哪个区域的首列查找,返回第几列的内容,[匹配方式])

公式表示在B2:G8这个区域的首列中查找指定内容“江苏”，并返回该区域第三列的信息，第四参数使用0，表示使用精确匹配的方式。

4、如下图，要根据G3单元格中的姓名，在左侧数据区域中查询所属部门。

H3单元格公式为：

=XLOOKUP(G3,D2:D7,B2:B7)

XLOOKUP函数的常规用法是：

=XLOOKUP(查找内容,一行或一列的查找区域,要回传内容的区域)

公式中的G3是要查询的姓名，D2:D7是姓名所在的区域，B2:B7就是咱们要回传信息的区域了。

5、如下图，要根据H2单元格中指定的姓名，在C列查询到该姓名，并返回B列对应的准考证号：

=LOOKUP(1,0/(C2:C10=H2),B2:B10)

使用LOOKUP函数可以实现任意方向的数据查询，模式化写法为：

=LOOKUP(1,0/(包含条件的一行或一列=指定的条件),要返回内容的行或列)

如果要进行多个条件的查询，模式化写法为：

=LOOKUP(1,0/(条件区域1=指定条件1)/(条件区域2=指定条件2),要返回内容的行或列)

好了，今天咱们分享的内容就是这些吧，祝大家一天好心情。

图文制作：祝洪忠

Excel工作表中最常用的10个函数，中文解读，动图演示

Excel工作表中的函数是非常的繁多的，如果要全部掌握，几乎是不可能的，也没有这个必要，不用行业，不同部门对函数需求都不同，所以，只需要掌握自己常用的部分函数即可，但是，下文中的10个函数是部分行业和部门的，所有的从业人员必须100%全部掌握！

一、Excel工作表函数：Sum。

功能：求和

语法结构：=Sum(值或单元格区域)。

目的：计算总“月薪”。

方法：

在目标单元格中输入公式：=SUM(1*G3:G12)，并用Ctrl+Shift+Enter填充即可。

解读：

因为“月薪”为文本型数值，所以直接用Sum函数求和时，得到的结果为0，此时我们需要将每个值转换为数值，所以给每个值乘以1，然后用Sum函数求和即可。

二、Excel工作表函数：If

功能：判断是否满足某个条件，如果满足则返回一个值，如果不满足则返回另一个值。

语法结构：=IF(判断条件,条件为真时的返回值,条件为假时的返回值)。

目的：“月薪”＞4000，返回“高”，＞3000，返回“中”，否则返回“底”。

方法：

在目标单元格中输入公式：=IF(G3>4000,\”高\”,IF(G3>3000,\”中\”,\”低\”))。

解读：

If函数除了常规的判断之外，还可以嵌套使用，公式的函数以为：如果当前单元格的值＞4000，则直接返回“高”，终止判断，否则继续执行当前单元格的值是否＞3000，如果大于，返回“中”，否则返回“低”。

三、Excel工作表函数：Lookup

功能：从单行或单列或数组中查找一个值。

Lookup具有两种形式：向量形式和数组形式。

（一）向量形式

功能：从单行或单列中查找查找指定的值，返回第二个单行或单列中相同位置的值。

语法结构：=Lookup(查找值,查找值所在的范围，[返回值所在的范围])。

当“查找值所在的范围”和“返回值所在的范围”相同时，可以省略“返回值所在的范围”。

目的：查询员工的“月薪”。

方法：

1、选定数据源区域，以“员工姓名”为主要关键字“升序”排序。

2、在目标单元格中输入公式：=LOOKUP(J3,B3:B12,G3:G12)。

解读：

如果未对数据源以查询关键在所在列进行升序排序，则查询的结果是不准确的，甚至返回错误代码，所以在使用Lookup函数时，先对查询关键字所在的列为主要关键字升序排序，然后再查询。

（二）数组形式

功能：从指定的范围第一列或第一行中查询指定的值，返回指定范围中最后一列或最后一行对应位置上的值。

语法结构：=Lookup(查询值,查询范围)。

解读：

从从“功能”中可以看出，Lookup函数的数组形式，查找值必须在查询范围的第一列或第一行中，返回的值必须是查询范围的最后一列或最后一行对应的值。即：查找值和返回值在查询范围的“两端”。

目的：查询员工的“月薪”。

方法：

1、选定数据源区域，以“员工姓名”为主要关键字“升序”排序。

2、在目标单元格中输入公式：=LOOKUP(J3,B3:G12)。

解读：

数据范围B3:G12中，B列为查询值J3所在的列，G列为返回值所在的列。

（三）优化形式（单条件查询）

在使用Lookup函数时，如果每次都要排序，会非常的麻烦，所以我们可以对其进行优化处理。

目的：查询员工的“月薪”。

方法:

在目标单元格中输入公式：=LOOKUP(1,0/(B3:B12=J3),G3:G12)

解读：

1、仔细分析公式=LOOKUP(1,0/(B3:B12=J3),G3:G12)，不难发现，其本质还是为向量形式，查询值为1，查询范围为“0”和“错误值”组成的新数组……。

2、查询范围：0/(B3:B12=J3)，如果J3和B3:B12范围中的值相等，则返回1,如果不相等，则返回0,0/1=0,0/0则返回错误。而Lookup函数在查询时，如果找不到对应的查询值，则自动“向下匹配”，其原则为：小于或等于查询值的最大值作为当前的查询值。即只有0符合条件，返回0所对应位置的值。得到查询结果。

（四）优化形式（多条件查询）

目的：查询员工在“已婚”和“未婚”时的工资。

方法：

在目标单元格中输入公式：=LOOKUP(1,0/((J3=B3:B12)*(K3=E3:E12)),G3:G12)。

解读：

当两个条件都为真时，其乘积也为真，其中一个为假或两个都为假时，其乘积也为假。所以多条件查询和单条件查询的原理是相同的。

（五）多层区间查询

目的：查询“月薪”对应的等级，≥4000的为“高”；≥3000且＜4000的为“中”，＜3000的为“低”。

方法：

在目标单元格中输入公式：=LOOKUP(G3,$J$3:$K$5)。

解读：

此方法主要应用了Lookup函数的数组形式和“向下匹配”的特点。

四、Excel工作表函数：Vlookup

功能：搜索工作表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值。

语法结构：=Vlookup(查询值，数据范围，返回值列数，匹配模式)。

其中匹配模式有两种，分别为“0”或“1”。其中“0”为精准匹配，“1”为模糊匹配。

（一）常规查询

目的：查询员工的“月薪”。

方法：

在目标单元格中输入公式：=VLOOKUP(J3,B3:G12,6,0)。

解读：

由于“月薪”在数据范围B3:G12的第6列，所以参数“返回值列数”为6。

（二）反向查询

目的：根据“身份证号码”查询“员工姓名”。

方法：

在目标单元格中输入公式：=VLOOKUP(J3,IF({1,0},C3:C12,B3:B12),2,0)。

解读：

公式中的IF({1,0},C3:C12,B3:B12)的作用为形成一个以C3:C12为第一列、B3:B12为第二列的临时数组。

（三）多条件查询

目的：根据“员工姓名”和\”婚姻”查询对应的“月薪”。

方法：

在目标单元格中输入公式：=VLOOKUP(I3&J3,IF({1,0},B3:B12&D3:D12,F3:F12),2,0)，并用Ctrl+Shift+Enter 填充。

解读：

1、当有多个查询的条件时，用连接符“&”连接在一起，对应的数据区域也用“&”连接在一起。

2、公式中IF({1,0},B3:B9&C3:C9,D3:D9)的作用为形成一个以B3:B9和C3:C9为第一列，D3:D9为第二列的临时数组。

五、Excel工作表函数：Match

功能：返回符合特定值特定顺序的值在数组中的位置。

语法结构：=Match(定位值,定位范围,[匹配模式])，其中“匹配模式”有-1、0、1三种，分别为：“大于”、“精准”、“小于”。

目的：根据“员工姓名”定位其在对应列中的相对位置。

方法：

在目标单元格中输入公式：=MATCH(I3,B3:B12,0)。

解读：

此处的位置相对而言的，具体要看“定位范围”的大小。

六、Excel工作表函数：Choose

功能：根据给定的索引值，从参数中选取相应的值或操作。

语法结构：=Choose(索引值,表达式1,表达式2……表达式N)。

如果参数“索引值”超出“表达式”的个数，则返回错误值。

目的：根据“索引值”返回相应的“员工姓名”。

方法：

在目标单元格中输入公式：=CHOOSE(I3,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12)。

七、Excel工作表函数：Datedif

功能：以指定的方式计算两个日期之间的差值。

语法结构：=Datedif(开始日期,结束日期,统计方式)，常用的统计方式有“Y”、“M”、“D”，即“年”、“月”、“日”。

目的：计算距离2021年元旦的天数。

方法：

在目标单元格中输入公式：=DATEDIF(TODAY(),\”2021-1-1\”,\”D\”)。

解读：

“开始日期”用函数Today（），而不用指定日期的原因在于，其值会随着日期的变化自动更新。

八、Excel工作表函数：Days

作用：返回两个日期之间的天数。

语法结构：=Days(结束日期，开始日期)。

目的：计算距离2021年元旦的天数。

方法：

在目标单元格中输入公式：=DAYS(\”2021-1-1\”,TODAY())。

解读：

此函数的依次为“结束日期”、“开始日期”，而并不是“开始日期”、“结束日期”，和Datedif函数的参数顺序要区别对待。

九、Excel工作表函数：Find

功能：返回一个字符串在另一个字符串中出现的起始位置（区分大小写）。

语法结构：=Find(查找字符串,源字符串,[起始位置])；当省略“起始位置”时，默认从第一个字符串开始。

目的：提取“员工编号”中“—”的位置。

方法：

在目标单元格中输入公式：=FIND(\”-\”,C3,1)。

解读：

也可以用公式：=FIND(\”-\”,C3)来实现，省略参数“起始位置”时，默认从第一个字符开始。

十、Excel工作表函数：Index

功能：返回指定区域中指定行和列交汇处的值或引用。

语法结构：=Index(数据范围,行,[列])，当省略参数“列”时，默认值为1。

目的：返回相应行的“员工姓名”。

方法：

在目标单元格中输入公式：=INDEX(B3:B12,J3,1)。

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com