Excel实战:时间转换、字符串拆分和多条件判断
来源:互联网 发布:三国演义电视剧 知乎 编辑:程序博客网 时间:2024/06/06 10:58
1 概述
1.1案例
有如下通话记录数据,包括主叫、被叫和通话时长。
+----------+------------------+------------------+----------+| date | caller | callee | duration |+----------+------------------+------------------+----------+| 20170820 | +12086828321 | +919500953657 | 0 || 20170820 | +12086828321 | +919500953657 | 0 || 20170819 | +12094106177 | +12462493750 | 967 || 20170819 | +12135364952 | +12462493750 | 967 || 20170819 | +12139082474 | +12462493750 | 967 || 20170819 | +12192022323 | +12462493531 | 0 || 20170819 | +12192022323 | +12462493531 | 0 || 20170819 | +12192022323 | +12462493531 | 0 || 20170819 | +12192022323 | +12462493531 | 0 || 20170819 | +12192022323 | +12462493531 | 0 || 20170819 | +12192022323 | +12462493531 | 0 || 20170819 | +12192022323 | +12462493531 | 0 |··· ··· ··· ···| 20170918 | +971569515973 | +8801866849184 | 0 || 20170918 | +971569515973 | +8801866849184 | 0 || 20170918 | +971569515973 | +8801874247298 | 0 || 20170918 | +971569515973 | +8801874247298 | 300 || 20170918 | +971569515973 | +8801882829872 | 0 || 20170918 | +971582051122 | +971564146669 | 0 || 20170918 | +971582902340 | +971505708937 | 17 |+----------+------------------+------------------+----------+
其中,电话号码包含国家码前缀,通话时长为0表示没有接通。
1.2 任务
需要统计用户拨打本地电话数、拨打国外电话数,拨通本地电话数,拨通国外电话数,并且以周为单位进行汇总。
1.3 分析
- 已有主叫和被叫电话,可以通过判断电话号码的国家码前缀是否相同来判断是否本国电话;
- 已有通话时长,通过通话时长是否大于0来判断是否接通;
- 已有时间,可以通过时间进行 分类汇总;
1.4 函数
需要用到的函数:
1. date函数,用于将数字或字符串转化成时间格式;
2. Mid函数,从文本字符串中制定的起始位置返回特定场长度的字符;
3. if函数,用于条件判断;
4. and函数,组合多个条件;
2 实战
2.1 将数据放入Excel表格中
2.2 将数字形式的时间转化成标准时间格式
在B列前面新建一列A,并输入图中公式,确认后往下拉。
说明:
- =mid(text,start_num,num_chars),返回某一部分的字符串;比如mid(b2,1,4)表示返回b2中前4位多的数字;
- =date(year,month,day),返回Excel时间格式;
2.3 判断电话号码前缀是否相同,是则返回1,否则返回0
新建一列F,在f2中输入=IF(MID(C2,1,2)=MID(D2,1,2),1,0)
函数说明:
- if(logical_test,value1_true,value2_false),如果条件成立,返回value1,如果条件不成立则返回value2。
这里截取了主叫和被叫的前两位进行比较,如果相同,那么返回1,如果不同就返回0;
同样的道理,我们也可以在后面增加一列,判断下相同则返回0,不同返回1。
2.4 判断是否拨通
这个比较简单,简单的条件判断函数if就可以实现,=IF(E2>0,1,0)
,完成后下拉,如图所示:
到了这一步,就已经分离出了本地拨打,国际电话拨打,以及是否接通,接下来将这三个因素组合一下,就可以得到,接通的本地拨打和国际拨打数;
2.5 接通的本地拨打和国际拨打
还是使用if函数,只是在逻辑条件中增加and(logical1,logical2)函数来增加与条件,
同样的道理,完成后下拉,得打拨通的本地拨打数。
同样使用公式=IF(AND(G2=1,H2=1),1,0)
,得到拨通的国家电话拨打数。
到这一步,就已经完成了基本的数据处理工作,接下来,使用数据透视表进行分类汇总和结果呈现。
2.6 透视表分类汇总
- 透视表
选中所有数据,选择“插入-数据透视表”,将时间作为列,刚刚通过公式得到的本地拨打数same,国际电话拨打数diff,拨通的本地拨打数same_dur,拨通的国际电话数diff_dur放在行,选择求和汇总:
这个时候,列是以每一天进行汇总的,而我们需要的数据是以周进行汇总,网上有大量各种办法,增加列,以星期作为选项,我觉得最简单的办法就是分类汇总。
- 分类汇总
在透视表时间数据“右键-创建组”,步长选择“日”,天数选择7天,然后点击“确定”。
到这一步,我们就已经剥离出了任务所需的拨打国内电话数,拨打国际电话数,拨通的国内电话数和拨通的国际电话数。
总结
主要应用了三个函数,mid函数提取字符串中指定几位字符,if函数判断条件并返回相应的值,date函数进行格式的转换,逻辑非常简单,实现非常容易。
- Excel实战:时间转换、字符串拆分和多条件判断
- richfaces界面数值转换和条件判断
- excel中多条件判断求和
- excel 条件判断
- excel中用宏拆分字符串
- excel字符串拆分为行
- python 字符串和时间转换
- 字符串和时间转换time_t
- 字符串和时间转换time_t
- Hive(十)--数据类型转换、字符串函数、条件判断
- mysql时间字符串和时间戳转换
- Java日期转换SimpleDateFormat格式大全 和 判断是否为合法的日期时间字符串?
- Excel中IF函数和AND函数结合使用进行多条件判断
- Excel 常用的2个宏(拆分单元格并自动填充数据、取满足条件的值连接成字符串)
- java字符串处理常用函数(数字型和字符串转换,字符串拆分、查找、截取)
- Excel 时间戳和时间格式的互相转换
- Android 时间和字符串之间的转换
- freemarker字符串转换成日期和时间
- Java总结篇系列:Java多线程(二)
- OSG的impostor的几个问题
- ViewPager加上Fragment 显示ListView
- 《项目二》盖板成像中边缘过渡带的去除
- 【18】javascript中闭包的工作原理
- Excel实战:时间转换、字符串拆分和多条件判断
- java并发多线程基础知识
- android运行时BUG
- 编译TensorFlow Android Camera Demo
- El表达式、Jstl
- mongodb 创建数据库
- Dubbo环境搭建
- 第3周项目2
- MySQL数据库应用-从入门到精通配套视频、PPT、安装包与教程