EXCEL day 02 公式和函数

EXCEL day 02 公式和函数

添加辅助列

利用排序，在第一列之前插入新列，进行编号排序，排序完成后删除辅助列，奇数列为数据，偶数列为空行

开始-排序-升序(自定义排序)

分列

数据-分列

通过特定符号或者字符长度进行拆分源数据,将分列后的数据放到指定位置

快速填充:手动拆分第一个数据,利用快速填充填充

EXCEL 重要函数

文本函数

强调点:函数末尾带B的函数,DBCS语言包括中文(简繁体),日语,朝鲜语.这些语言.一个字符等于2个字节

即:带B的函数,一个字符=2个字符长度

LEFT(text,个数=1) -从文本字符串开始的第一个字符串开始，返回指定个数的字符

个数默认为1，需要几个字符串就写几个个数

LEFTB(text,字符长度)-从文本字符串开始的第一个字符串开始返回指定长度的字符

RIGHT(),RIGHTB()功能同上,从末尾取字符串

MID(text,开始位置,字符长度)-从指定位置返回指定长度的字符

MIDB(text,开始位置,字符长度)-从指定位置返回指定长度的字符

LEN(TEXT)-返回文本字符串中的字符串个数

LENB(text)-返回文本字符串中的字符串个数

tip:可以利用LEN() 和LENB()的差值来计算文本中DBCS语言的文字个数

FIND(需要查找的字符串,text)-查找字符串1在字符串中的位置

FINDB(字符串1,text)-查找字符串1在字符串中位置.

SEARCH(),SEARCHB()-同样的查找功能,注意不区分大小写

REPLACE(TEXT,下标位置,字符长度,替换字符)-将源字符串中的指定位置的指定长度的字符替换成想要的字符

LOWER()-将字符串中的大写转小写

UPPER()-将字符串中的小写转大写

REPT(text,num)-将文本重复一定次数

SUBSTITUTE(TEXT,替换对象,替换内容)-在文本字符串中用新文本替换旧文本

TRIM()-移除文本中的多余空格

TEXT(对象,格式)-自定义单元格格式

VALUE()-将表示数字的文本字符串转换成数字

数学函数

ABS()-返回数字的绝对值

INT()-向小取整

MOD()-求余

RAND()-产生[0,1)的随机数

RANDBETWEEN()-产生[a,b]之间的随机整数

ROUND(NUM,保留位数)-将数字四舍五入到指定位数

SQRT()-J计算算术平方根

SUM()-求和

SUMIF()-对指定范围中满足条件的值求和

SUMIFS()-对指定范围中满足多条件的值进行求和

SUMIF(条件范围,条件,求和范围)

注意:1)条件需要用双引号引起来

2)求和范围和条件范围相同时,可以省略不写

3)如果条件需要引用单元格内容,单元格地址放引号外面,同时单元格的前面需要加&

4)条件中可以用*作为通配符,代表任意符号出现任意次数,如果希望表示本身,用~*

统计函数

AVERAGE()-返回平均值,(数字的和/数字的个数)

AVERAGEA()-返回平均值,包括数字,文本和逻辑值(数字的和/数据的个数)

AVERAGEIF()-返回区域里满足条件的所有单元格的平均值

AVERAGEIFS()-返回满足多个条件的所有单元格的平均值

COUNT()-计算参数列表中数字的个数

COUNTA()-计算参数列表非空值的个数

COUNTIF(),COUNTIFS():满足单个条件或者多个条件的单元格的数量

FREQUENCY(统计数据的范围,分箱节点)-范围是小于等于节点

MAX()/MIN()-返回最大值或者最小值

RANK(目标数,范围)-返回一列数字的排位大小

RANK.AVG()-返回一列数字的排位,如果有相同的数据具有相同排位,返回平均排位值

RANK.EQ()-返回排位,如果具有相同排位,返回最高排位

=FREQUENCY(统计数据范围,分箱节点)
分数	分段	实际分箱	数量
31	60以下	59	3
63	60-70	69	2
89	70-80	79	1
86	80-90	89	3
45	90以上		1
93
59
78
67
87