头部广告

这6个Excel小公式太好用了,用过一次可能就上瘾!

今天跟大家分享6个非常实用精妙的Excel小公式,简单实用,建议收藏,用过一次可能就上瘾!

一、SORT函数

功能:SORT函数主要用来对某个区域或数组的内容进行排序。

语法:=SORT(数组,排序依据,排序顺序,按列)

第一参数:「数组」指的是要排序的区域或数组

第二参数:「排序依据」为以某行或列为依据进行排序

第三参数:「排序顺序」指的是所需的排序顺序,1表示升序排序,-1表示降序排序

第四参数:「按列」是一个逻辑值,输入True表示按列排序,输入False表示按行排序,默认按行排序。

特别提醒:

1、这个函数不能用于当前表格数据自动排序,需要制作一个新的表格用来生成一份自动排序后的表格数据

2、原表格数据修改后,通过SORT函数生产的排序表格数据会重新自动排序

3、在WPS中输入SORT函数公式完成后,需要用快捷键组合键【Ctrl+Shift+Enter】将公式以数组的形式填充到表格中。

用法实例:

如下图所示,我们想对左侧表格数据以成绩来排序,按成绩从高到低排序。

1.jpg

操作方法:

首先制作一个新表格→然后选中新表格要排序的单元格数据区域→

输入公式=SORT(A3:E11,2,-1),

输入完成后,用快捷键【Ctrl+Shift+Enter】将此公式以数组的形式填充到表格中获取排序数据。

2.gif

二、SORTBY函数

功能:SORTBY函数可以根据相应范围或值,对单元格内容进行排序。

语法:=SORTBY(数组,排序依据数组1,排序顺序1,排序依据数组2,排序顺序3...)

第一参数数组:指的是要排序的区域或者数组

第二参数排序依据数组:为以某数组为依据进行排序

第三参数排序顺序:指的是所需的排序顺序,1表示升序排序,-1表示降序排序

特别提醒:

1、SORTBY函数不能用于当前表格数据自动排序,需要制作一个新的表格用来生成一份自动排序后的表格数据。

2、原表格数据修改后,通过SORTBY函数生产的排序表格数据会重新自动排序

3、在WPS中输入SORTBY函数公式完成后,需要用快捷键组合键【Ctrl+Shift+Enter】将公式以数组的形式填充到表格中。

如下图所示,左侧表格1是学生考试成绩表格,我们想对左侧表格1数据以【班级】和【总分】进行排序,按照班级从低到高,总分从高到低排序。

3.jpg

操作步骤:

首先制作一个新表格→然后选中新表格要排序的单元格数据区域→

输入公式=SORTBY(A3:F8,B3:B8,1,F3:F8,-1),

输入完成后,用快捷键【Ctrl+Shift+Enter】将此公式以数组的形式填充到表格中获取排序数据。

4.gif

三、LEFT函数

功能:从左开始提取文本字符串

语法:=LEFT(字符串, [字符个数])

第一参数:字符串就是需要提取的文本字符串

第二参数:[字符个数],从左开始提取几位

应用实例:

如下图所示,我们需要提取左侧的省份名称

在目标单元格输入公式:=LEFT(C3:C8,3)

然后点击回车,下拉填充即可

5.gif

四、RIGHT函数

功能:从右开始提取文本字符串

语法:=RIGHT(字符串, [字符个数])

第一参数:字符串就是需要提取的文本字符串

第二参数:[字符个数],从右开始提取几位

应用实例:

如下图所示,我们需要提取右侧的城市名称

在目标单元格输入公式:=RIGHT(C3:C8,3)

然后点击回车,下拉填充即可

6.gif

五、TEXT函数

功能:TEXT函数是一个非常强大的转换函数,也是我们日常工作中经常会使用到的。它的结构也比较简单,只需要两个参数。

语法:=TEXT(值,数值格式)

第一参数值:为数值、计算结果为数字值的公式,或对包含数字值的单元格的引用。

第二参数数值格式:为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。

应用实例:

如下图所示,把小写金额转换为大写金额(包含圆角分)

在目标单元格输入公式:

=TEXT(INT(A4),"[dbnum2]")&TEXT(RIGHT(A4*100,2),"圆[dbnum2]0角0分")

然后点击回车键,最后下拉填充其它数据即可

7.gif

公式解释:

①首先通过TEXT(INT(A4),"[dbnum2]")把小写金额整数转换成大写。通过INT函数获取整数部分,然后TEXT函数第二参数数值格式设置为"[dbnum2]"就是转换成大写。

②然后通过&连接符再通过TEXT(RIGHT(A4*100,2),"圆[dbnum2]0角0分"),把小数部分转换成大小,并且设置单位圆角分。通过RIGHT(A4*100,2)获取后两位小数部分,乘以100是为了把小数部分也转换成大写;

第二个TEXT函数第二参数数值格式设置设置为"圆[dbnum2]0角0分",圆[dbnum2]:代表第一个TEXT函数转换的大写数字单位显示元;0角0分:代表第二个TEXT函数转换的大写数字单位显示角分,格式码中的0有特殊含义,表示要处理的值本身。

③上面公式可以直接套用,把转换的单元格参数换成自己的就可以了。

六、SUM函数实现合计单元格分类求和

功能:SUM函数返回某一单元格区域中所有数字之和。

语法:=SUM(数值1,数值2....)

相信大家对SUM求和函数已经非常熟悉了,我在这里就不多做介绍了,今天要说的是如何对合并后的单元格进行数据求和。如下图所示,这是一个“员工工资信息表”,我们需要按照部门合计工资总数,这就涉及到对合并单元格的求和。

8.jpg

操作方法:

首先选中所有合并的“工资合计”单元格区域→然后在公式编辑栏中输入公式:

=SUM(C3:C9)-SUM(D4:D9)

最后按【Ctrl+Enter】获取数据,这样就可以统计出不同部门的工资总数了。

9.gif

公式解释:

1、这个公式=SUM(C3:C9)-SUM(D4:D9)的思路就是将C列所有的求和减去D列当前单元格之后的求和。

2、SUM(C3:C9)是对“工资”这一列第3行到第9行求和;SUM(D4:D9)是对D列“工资合计”第4行到第9行求和,然后进行错位相减。

在Excel中合并后的单元格然看着是一个单元格了,但是实际上数据是放到合并之前最上面一个单元格内,比如说D列“工资合计”-“业务部”这个单元格,合并之前包含D3、D4、D5,合并后每个合并单元格的第一个单元格是有值,也就是说D3有值,D4、D5是空。到D6时,又有计算结果了,这个计算结果正好被上面的合并单元格减掉。

3、输入公式后一定要按【Ctrl+Enter】获取数据


底部广告