Excel01-lookup函数实现数据分层
今天给大家分享lookup函数的典型应用。比如说我要对网站客户进行数据分层。
案例如下:
我希望对目前的顾客分成3类,按交易次数分:
1-2次 :新客
3-6次 :次新客
7次及以上:老客
客户数据上万条,肯定不能用眼睛,那样数据还没出来,你眼睛已经瞳孔放大了。
下面,我就用lookup来实现数据分层的效果,高效准确。
Lookup常见语法:(查找值,查找区域),用处也是查找。他属于模糊匹配,返回查找值所对应区域的最大值。此用法必须查找区域升序。
是不是感觉很像vlookup,但参数更少些。
下面我们在表格里做辅助列如下:
>7这里用100代替一般也足够了,当然最好可以用max 那列的最大值更佳。
然后输入公式:E2=LOOKUP(B2,$H$2:$J$5).
查找值是B2,查找区域也就是整个辅助列的区域,包括J列。这样返回的结果就是J列里的某项。
比如说B2=12,12落在>7,所以结果是老客。
简单理解可以这样:12 在辅助列区域中最大可以算到7-100,所以返回>7的老客。
B3=4,是最多算到3-6,不能算>7,所以他只能返回次新客。
本篇结束。
Excel02-日期时间函数实现日期时间快速提取
今天给大家分享日期时间函数的典型应用。我如何把带有日期+小时分秒的单元格里的日期和时间分别提取。
案例如下:
我希望对分离出日期和时间
数据上万条,肯定不能用眼睛,那样数据还没出来,你眼睛已经瞳孔放大了。
下面,我就用函数来完美解决问题。
方法1:分列,这是最简单的,就不谈了,如果不会就自己去补基础。免费或付费,自己学会。免费省钱,付费省钱。就看你缺哪个。说个额外话,前辈教导我,用钱帮你做事,用人帮你做事,用工具帮你做事。
方法2:我们今天的重点。为什么方法1就可以,还需要方法2呢。分列和函数改变数据是不同的应用场景,当你接触足够多的报表数据 处理,你就懂了。
B2= DATE(YEAR(A2),MONTH(A2),DAY(A2))
用到最基本的4个时间函数,在我的02 函数系列教程里都有讲到这几个函数,分别就是提取年月日。
追加一步,单元格格式改为日期
C2 =TIME(HOUR(A2),MINUTE(A2),SECOND(A2))
和上面类似进行提取时分秒,追加一步,单元格格式改为时间.函数也就是hour,minute,second。也就是英语这3个词。英语好些,学函数是很容易的。当然如果不好,也不是不能学会。没有学不会的函数,只有不会教的老师,或学生自己瞎研究费事。
本篇结束。
Excel003-和被标记的行值相同则全部标记
今天给大家分享一个高难度的Excel实际应用,后面大家就会理解为什么我说是高难度了。问题来自我们千人付费会员群,我已经注册到公司,所以群改名为资越科技教程会员群。本群都是起码购买教程,消费满50起的会员,严格控制进入门槛,但离开自愿,随时。
回到正题:
问题是这样的,简化下问题:需要对标记和被标记的行值相同则全部标记。
准确描述问题是这样的:比如B4被标记为4,那么和A4一样等于2的值,都需要被标记。
问题描述清楚了,然后怎么实现呢?
下面介绍方法1和2。
1 、在于思路巧妙,我写的。
2、在于函数功底很牛,我一个函数大神朋友,都出书好几本了,也有个淘宝店铺,专门解决疑难。
方法1: 我是这样想的。可以借助vlookup,查找值,那值一样的话,自然就能把一样的都标记出来。
所以我多了辅助橙色区域。
因为标记要删除B列数据就2条,所以辅助区域也就2行。如果数据更多,总之去源数据筛选B列不为空的,粘贴到辅助区域就行。
最终C2=IF(ISNUMBER(IFERROR(VLOOKUP(A2,$G$3:$H$4,2,0),''))=TRUE,'删除','')。
Iferror是返回如果不存在则怎么办,isnumber判断是否为数值,因为B列都是数值。
方法2:函数大神解法。
D2=IFERROR(IF(A2=LOOKUP(1,0/(B$2:B2<>''),A$2:A2),'删除',''),''),无辅助列,一步到位。理解难度高,运用了lookup所谓的二分法什么的,我暂时看不懂,也没兴趣如此深入研究函数。
最后说一句,Excel学习主要还是解决实际问题,而不是纠结哪个函数快,哪个函数短。关键是思路清晰,逻辑清楚。
本篇结束。
Excel004-VBA智能提取超级链接文本下的链接
今天来分享下如何提取超级链接文本下的链接。比如说:
百度我们要把www.baidu.com提取出来放在B列。
如何实现呢?函数肯定是不能实现的,暂时没提取超级链接文本的函数,以后可能会有。
VBA解决方案如下:
插入VBA代码步骤如下:按ALT+F11, 进入VBE编辑器,点插入模块,输入以下代码:
Sub 提取链接()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address‘就是说把链接放在非单独链接的后面一列。
Next
End Sub
以下代码的作用就是把文本下的链接提取,并放在后面1列。不熟悉VBA代码如何输入,请百度 VBA基础,基本也就了解了。
本篇结束。
Excel005-多条件求和计算收款
今天来分享一个财务收款计算应用,问题是这样的。
比如说我要计算各门店已收款是多少,未收款是多少。类似的应用场景是很广泛的。源数据如下:
数据是模拟的。
结果是这样的:颜色区域即为结果。
如何实现从表1到表2呢,手动肯定是不可能的,一步到位,估计也比较难。
问题其实不难,关键是先理解怎么样的表1记录是已回。已回就是已经回款,那就是说回款日期不为空。
因此第一步是对表1的款账记录进行分类,哪些是已经,哪些是未回款。
=IF(LEN(D2)=0,'未回','已回')
Len 代表字符的长度,if就是判断。
这步搞定后,后面就是多条件求和了。
对于表2中的温州已回的汇总其实就是条件1 温州,条件2 已回,在表1进行这2个条件的求和。
最终公式如下:
=SUMIFS($C$2:$C$36,$A$2:$A$36,I2,$F$2:$F$36,J$1)
解决这个问题的关键是首先辅助列把表1记录进行已回和未回分类,然后就是sumifs函数进行多条件汇总。涉及到的函数都体现在本人的原创函数实战教程里。
本篇结束。
作者Sharpen,微软Excel认证专家。来源数据网友博客,感谢!
联系客服