excel话单手工分析

一、话单手工分析

1、清洗86开头手机号

在话单中，经常看到86（中国国际区号）开头的手机号码，在数据分析时会被认为成两个号码，如何清洗呢?

首先如何快速发现这些位数异常的手机号码呢？那就是使用LEN函数（表示返回文本串的字符数），在号码右边新插入一列，使用=LEN(D2)函数

返回文本串的长度为11

双击右下角可以全部填充

筛选查看13位的电话号码，使用RIGHT函数（右边起提取指定位数的字符串）截取11位号码。

下拉拖拽进行复制

然后清除筛选，选中新增加的号码这一列，ctrl+c复制，然后粘贴为数值，然后这些电话就成为实际的值了，而不再是函数内容了，然后再筛选出这些号码，输入=E17进行替换，下拉拖拽全部应用即可。

然后清除筛选，选择号码这一列，赋值粘贴为值，删除新增加的这一列，86开头手机号就清洗成标准的11位手机号码了。

2、日期时间相关

日期时间分隔

为了将时间分割为日期和时分，我们可以使用分列功能，首先新插入一列（不然分列后会覆盖右侧内容），选中起始时间这一列，选择分列功能，选择分隔符号，再选择空格，可以下拉预览一下有无异常情况，没有的话确定即可，然后将分出来的两列格式设置成日期格式即可。

日期时间合并

那么如果拿到的日期就是分隔的，如果需要合并到一起呢？那我们就需要使用“+”号函数了，首先在右侧新增一列，设置时间格式。

使用+函数让两列相加

回车应用函数

双击右下角在该列应用函数，最后赋值粘贴为值。

快速从日期判断星期几

使用WEEKDAY和TEXT函数，=TEXT(WEEKDAY(A2,1),”aaaa”)，可以将数字表示为星期几这样的格式显示。

WEEKDAY 是一个 Excel 函数，返回代表一周中第几天的数值，是一个1到7（或0到6）之间的整数。语法格式 WEEKDAY(date,type)，其中 date为日期；type表示返值是从1到7还是从0到6，以及从星期几开始计数，如省略则返值为1到7，且从星期日起计。

TEXT函数将数值转换为按指定数字格式表示的文本。语法格式TEXT(value,format_text)，其中Value 为数值、计算结果为数字值的公式，或对包含数字值的单元格的引用，Format_text 为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。

如图：

3、提取身份证号

提取出生日期

方法一：分列

数据分列，选择固定宽度，截取身份证号中间出生日期，完成即可，然后将其他两列数据删除。

方法二：MID()函数

MID()函数函数的格式为MID(text, start_num, num_chars)，功能是实现从字符串任意位置提取任意长度的字符，text是需要查找的字符串文本，可以手动输入，也可以引用单元格。start_num是查找字符串文本中的起始位置，从第一个字符计算，默认为1。num_chars是所从起始位置开始的提取字符串个数，num_chars不可为负数，如大于文本长度，则提取剩余文本。

截取以后的出生日期不是标准的日期格式，需要使用REPLACE函数来替换字符从而实现标准日期格式。

REPLACE()函数用新字符串替换旧字符串，而且替换的位置和数量都是指定的。语法格式为：2.replace函数的语法格式

=Replace(old_text,start_num,num_chars，new_text)

=replace（要替换的字符串，开始位置，替换个数，新的文本）

注意：第四个参数是文本，要加上引号。

直接使用嵌套函数REPLACE(REPLACE(D2,5,0,”/“),8,0,”/“)即可

4、通讯号码分析

获取通讯录名单

将对方号码复制至新的表，选择该列，选择数据工具里的删除重复项即可。

可以使用条件格式-突出显示单元格规则-重复值验证是否有重复项，如果有重复项该项会以设置的颜色显示。

通话次数分析

全选通话数据，使用数据透视表功能，右侧选择对方号码列，然后将通信时长字段拖入时长框内，默认为计数项，这样，机主与每个号码通信次数就显示出来。

然后点击通话次数数值，右键排序-降序，然后按照通话次数就降序排序，很方便可以看到频繁联系人。

如果这样看不方便，还可以使用数据透视图功能，以图形形式之间显示。