数据分析之Excel常用公式与函数

数据清洗类函数

1.1Excel常用公式与函数知识体系图

1.2数据清洗类知识体系图

1.3数据清洗类

1.3.1TRIM()

功能:去除单元格两端的空格。

语法:=TRIM(text)

text指要移除空格的文本或者单元格名称

用法:

1.例如，在单元格B2左端有空格，如图所示:

2.在对单元格B2下方输入“=trim(B2)”后，回车，可以看到，空格已经去除了。

小提示：这里函数名称都是大写，是为了好看，统一成大写，实际操作过程中，小写也可以。

1.3.2   CONCATENATE

功能：连接单元格内的内容

语法：= CONCATENATE(text1, [text2], ...)

1.text1为必需，要联接的第一个项目。项目可以是文本值、数字或单元格引用。

2.[text2]表示text2为选填的意思，下同。

类似函数-&

用法：

例如，有张班级表，里面有人数、平均成绩等信息，需要根据表中的数据连成一句话，例如，要组织这样一句话，“1班，人数是55人，平均成绩90”。

找一个空白单元格，输入“=CONCATENATE(B2,",人数是",C2,"人，平均成绩",D2)”，回车即可。

1.3.3  MID

功能： 提取字符串中间的字符串

语法： = MID(text, start_num, num_chars)

1.text必填。包含要提取字符的文本字符串。

2.Start_num必填。文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。

3.Num_chars必填。指定希望 MID 从文本中返回字符的个数。

用法：

例如，B3所在的单元格有一个身份证号码，想从里面提取出生年月日

在右边的空白单元格D3里，输入公式“=MID(C3,7,8)”，回车，就可以得到。

1.3.4  实用小技巧

如果要继续提取，有两种方式，以后经常会用到！

方式一：选中单元格C3，鼠标移至右下角，当光标变成十字的时候，按住鼠标左键往下拖动即可。

方式二：选中单元格C3，鼠标移至右下角，当光标变成十字的时候，双击鼠标左键。

1.3.5   LEFT

功能：提取字符串左边的字符串

语法：= LEFT(text, [num_chars])

1.Text，必需。包含要提取的字符的文本字符串。

2.num_chars，可选。指定要由 LEFT 提取的字符的数量。

(1)Num_chars必须大于或等于零。

(2)如果num_chars大于文本长度，则LEFT返回全部文本。

(3)如果省略num_chars，则假定其值为1。

用法：

例如，在C3单元格里，需要把年月日里面的年份提取出来。

1.在旁边的空白单元格D4里，输入“=LEFT(C3,4)”，回车即得结果。

2.继续提取C4至C8中的年月信息，鼠标移至单元格D3的右下角，当光标变成十字的时候，双击鼠标左键，具体参考前面介绍的实用小技巧。

1.3.6   RIGHT

功能：提取字符串右边的字符串

语法：=RIGHT(text,[num_chars])

1.text 必需。包含要提取字符的文本字符串。

2.num_chars可选。指定希望RIGHT提取的字符数。

用法：

例如，在C3单元格里，需要把年月日里面的月份和日期提取出来。

1.在旁边的空白单元格E4里，输入“=RIGHT(C3,4)”，回车即得结果。

2.继续提取C4至C8中的月日信息，鼠标移至单元格E3的右下角，当光标变成十字的时候，双击鼠标左键。

1.3.7 REPLACE

功能：替换字符串中的连续几个字符或者某个字符

语法：= REPLACE(old_text, start_num, num_chars, new_text)

1.old_text必填。要替换其部分字符的文本。

2.start_num必填。old_text 中要替换为 new_text 的字符位置。

3.num_chars必填。old_text 中希望 replace 使用 new_text 来进行替换的字符数。

4.new_text必填。将替换 old_text 中字符的文本。

用法：

为了保护用户隐私，通常需要隐藏手机号后4位，可以利用replace函数来实现，这里对B3单元格里的手机号进行处理。

在空白单元格C3里输入公式“=REPLACE(B3,8,4,"****")”，回车即可得到结果。

1.3.8  substitute

也可以利用substitute实现。

功能：替换字符串中的连续几个字符或者某个字符

语法：=SUBSTITUTE(text, old_text, new_text, [instance_num])

1.text必填。需要替换其中字符的文本，或对含有文本（需要替换其中字符）的单元格的引用。

2.old_text必填。需要替换的文本。

3.new_text必填。用于替换 old_text 的文本。

4.Instance_num为一数值，用来指定以new_text （新文本）替换第几次出现的old_text（旧文本）。如果指定了instance_num，则只有满足要求的old_text被替换。否则，文本中出现的所有old_text都会更改为new_text。

用法：

在空白单元格D3里输入“=SUBSTITUTE(B3,RIGHT(B3,4),"****")”，回车即得。

1.3.9 len&lenb

功能：

LEN 返回文本字符串中的字符个数。

LENB 返回文本字符串中用于代表字符的字节数。

语法：

=LEN(text)

=LENB(text)

text必填。要查找其长度的文本。空格将作为字符进行计数。

如果要求字符串的长度，则可以使用len或者lenb，两者的区别在于中文字符的计算上，对于len来说，一个中文字符的长度计算为1，对于lenb来说，一个中文字符的长度计算为2，对于英文字符或者数字则没有差异，用法如下。

LEN

LENB