Excel数据分析：函数公式之文本类函数

最新推荐文章于 2024-06-13 22:15:09 发布

uo向左的ng

最新推荐文章于 2024-06-13 22:15:09 发布

阅读量278

点赞数

文章标签： excel 数据分析数据挖掘

本文链接：https://blog.csdn.net/qq_59125402/article/details/132508674

版权

公式和函数在Excel数据分析中是数据清洗中必不可少的一环，用于汇总统计、文本处理、时间计算、查找匹配等

说明：本文仅做个人笔记记录，如有疑问欢迎戳我~

1、公式使用技巧

单元格地址引用时的锁定

在锁定位置前加$符号

功能强大的公式审核

位置：“公式”选项卡——公式审核模块

1、追踪引用单元格：在公式选项卡的公式审核中，点击追踪引用单元格，用箭头标识出当前公式引用了哪些单元格

2、追踪从属单元格：在公式选项卡的公式审核中，点击追踪引用单元格，用箭头标识出哪些单元格引用了当前单元格

3、显示公式：显示公式内容，而不是计算结果，并标识关联的单元格

4、公式求值：在公式选项卡的公式审核中，点击公式求值，【求值】按钮逐步显示公式计算结果，帮助快速理解复杂的公式

5、错误检查：在公式选项卡的公式审核中，点击错误检查，可以查看公式错误的原因

2、文本类函数

有以下单元格及其内容：

A1：数据

A2：分析

A3：这些事

A4：儿

A5：数据分析这些事儿

CONCATENATE

含义：将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中

表达式：=CONCATENATE(text1,text2,……)

示例：

连接A1-A4单元格文本：=CONCATENATE(A1,A2,A3,A4) —— 结果返回：数据分析这些事儿

REPT

含义：根据指定次数重复文本

表达式：=REPT(text,number_times)

示例：

重复3次"数据"：=REPT("数据",3) —— 结果返回：数据数据数据

TEXT

含义：按指定格式数值转换为文本

表达式：=TEXT(value,format_text)

参数说明：

value：必须为数值

format_text：设置value格式，相当于设置格式的自定义中的选项，要加双引号

示例：

有单元格D1，格式为2000，将其添加2个小数点：=TEXT(D1,"0.00") —— 结果返回：2000.00

有单元格D2，格式为2020/10/15，将其转换成为年月日格式：=TEXT(D2,"YYYY年M月D日") —— 结果返回：2020年10月15日

FIXED

含义：按指定小数位数四舍五入一个数

表达式：=FIXED(number,decimals,no_commas)

参数说明：

number：要进行四舍五入并转换成文字的数

decimals：数值，用以指定小数点邮编的小数位数

no_commas：逻辑值，TRUE返回的文字不含逗号，FALSE或省略则返回的文字中包含逗号

示例：

将2000.0120四舍五入保留2位小数：=FIXED(2000.0120，2) —— 结果返回：2,000.01

LEN

含义：返回文本字符串的字符数（非字节数）

表达式：=LEN(text)

参数说明：text为文本

示例：

查看A5单元格的字符数：=LEN(A5) —— 结果返回：8

LENB

含义：LenB函数返回字符长度，双字节字符返回2，单字节返回1

表达式:=LENB(text)

示例：

查看A5单元格的字符长度（字节数）：=LENB(A5) —— 结果返回：16

EXACT

含义：判断两个字符串是否完全相同

表达式：=EXACT(text1,text2)

示例：

判断A1和A2是否完全相同：=EXACT(A1,A2) —— 结果返回：FALSE

LEFT

含义：返回文本中从左至右指定长度的字符

表达式：LEFT(text,num_chars)

参数说明：

text：包含提取字符的文本

num_chars：提取的字符串长度，必须大于等于0

示例：

截取A5中的从左至右2个字符：=LEFT(A5,2) —— 结果返回：数据

如未注明字符串长度，则返回左边第一：=LEFT(A5) —— 结果返回：数

如注明的字符串为0，则返回空：=LEFT(A5,0) —— 结果返回：

如注明字符串长度大于文本长度，则返回整个文本：=LEFT(A5,10) —— 结果返回：数据分析这些事儿

如注明字符串为负数，则返回错误：=LEFT(A5,-1) —— 结果返回：#VALUE!

RIGHT

含义：返回文本中从右至左指定长度的字符

表达式：=RIGHT(text,num_chars)

参数说明：

text：包含提取字符的文本

num_chars：提取的字符串长度，必须大于等于0

示例：

截取A5中的从右至左4个字符：=RIGHT(A5,4) —— 结果返回：这些事儿

整体用法与LEFT相似，不同的是RIGHT取右边的字符

MID

含义：返回从指定位置开始的指定数的字符

表达式：=MID(text,start_num,num_chars)

参数说明：

text：文本单元格位置

start_num：起始位置，从1开始

num_chars：字符串长度

示例：

截取A5中的"分析"：=MID(A5,3,2) —— 结果返回：分析

如果起始位置大于文本长度，则返回空：=MID(A5,10,5) —— 结果返回：

如果字符串长度大于文本长度，则返回剩余所有文本：=MID(A5,3,10) —— 结果返回：分析这些事儿

如果起始位置为负数，或字符长度为负数，则返回错误：=MID(D6,-1,10) ——结果返回：#VALUE!

FIND

含义：查找一个字符串在另一个字符串的位置，区分大小写

表达式：=FIND(find_text,within_text,start_num)

参数说明：

find_text：要查找的字符串，需要加双引号

within_text：需要被查找的字符串所在的单元格

start_num：起始位置，若不填写则从第一个开始查找

示例：

查找e在Excel的位置：=FIND("e","Excel") —— 结果返回：4

SEARCH

含义：查找一个字符串在另一个字符串的位置，不区分大小写

表达式：=SEARCH(find_text,within_text,start_num)

参数说明：

find_text：要查找的字符串

within_text：要查找的单元格

start_num：起始位置

示例：

查找e/E在Excel的位置：=SEARCH("e","Excel") —— 结果返回：1

ASC

含义：将字符串中的全角（双字节）英文字母转换为半角（单字节）

表达式：=ASC(text)

示例：

将全角ｅｘｃｅｌ转换为半角：=ASC("ｅｘｃｅｌ") —— 结果返回：excel

WIDECHAR

含义：将字符串中的半角（单字节）英文字母转换为全角（双字节）

表达式：=WIDECHAR(text)

示例：

将半角excel转换为全角：=WIDECHAR("excel") —— 结果返回：ｅｘｃｅｌ

LOWER

含义：将文本中所有大写字母转换为小写

表达式：=LOWER(text)

参数说明：text：包含待转换字母的文字串

示例：

将Excel转换为小写：=LOWER("Excel") —— 结果返回：excel

UPPER

含义：将文本中所有小写字母转换为大写

表达式：=UPPER(text)

参数说明：text是包含待转换字母的文字串

示例：

将Excel转换为大写：=UPPER("Excel") —— 结果返回：EXCEL

PROPER

含义：将文本中所有首字母转换为大写

表达式：=PROPER(text)

参数说明：text是包含待转换字母的文字串

示例：

将excel首字母大写：=PROPER("excel") —— 结果返回：Excel

NUMBERSTRING

含义：将阿拉伯数字转换成中文数字

表达式：=NUMBERSTRING(value,type)

参数说明：

value：要转换的大于0的数字，如果数字为小数，则转换为四舍五入的正整数

type：返回结果的类型，共三种类型，可以为以下值

1：常规汉字数字

2：大写汉字数字

3：将数字逐个转换为汉字

示例：

type为1时转换2018：=NUMBERSTRING(2018,1) —— 结果返回：二千〇一十八

type为2时转换2018：=NUMBERSTRING(2018,2) —— 结果返回：贰仟零壹拾捌

type为3时转换2018：=NUMBERSTRING(2018,3) —— 结果返回：二〇一八