目前进度:
- Excel大厂周报制作
- tableau仪表盘制作
- SQL
- Python(有基础)(学习一半)
- spss(读研时用的数分工具,后续复习一下)
学习目标1:
Excel大厂周报制作
(接上一篇)
PART02:
4. SUBTOTAL
subtotal可以选择函数,如下图列出。如果要求和,就写为subtotal(9,…),此时subtoal的作用与sum是一致的。如下图所示,还可以计数,求最大值最小值,求平均值等等。但是我此处也就只用过sum这一个功能。我这里还备注了一句话hhh。
5.if函数
(1)if函数比较简单,如图,if后面跟条件,然后判断是否符合该条件,符合返回TRUE,不符合返回FALSE。
比如,,判断每个月的GMV是否大于目标值10万,就可以使用if函数。IF(判断条件,‘符合则返回某个值’,‘不符合则返回某个值’)。图中,符合的返回yes,不符合的返回no。
以上都是if单条件判断,如果需要考虑多个条件限制判断的话,则需要对用if进行嵌套判断。
(2)如果条件为“大于月目标10万且花费少于5千的为达标”,那么既需要满足GMV>10万,又需要cpc<5000,这是“and”的关系,需要同时满足。那么可以通过if嵌套来实现,如下:
if(C80>100000,if(D80<5000,"达标","不达标"),"不达标")
首先,第一个判断条件为月目标GMV>10万,若符合则进入第二个判断条件,若不符合则直接pass,我个人感觉这里有点类似于c语言里的for循环,判断条件是否符合,符合则进入下一层循环,不符合则直接跳出循环,just个人感觉。
如果第一个条件符合,那么进入第二个if判断,若cpc小于5千,那么符合则返回“达标”,不符合返回“不达标”。
第二个例子,更深入的理解if嵌套。判断A和B为0的情况。
首先,判断A是否等于0,若该条件满足,则可知A等于0,那么此时进入第二个条件,判断B是否等于0,若B等于0,那么可以下结论AB都等于0,否则那就是A等于0但是B不等于0;若不满足第一个条件A=0,那么进入第三个条件,再次判断B是否等于0,此时我们知道A不等于0,那么只需要判断B是否等于0即可下结论,B等于0就是A不等于0B等于0,若不符合第三个条件,则结论为AB都不等于0。这一段嵌套稍微有一丢丢绕,但是理解了if函数就很好理解,我也是再重新温习这里时,更加清晰的了解if嵌套的使用。
if(I83=0,if(J83=0,"AB都等于0","A等于0B不等于0"),if(J83=0,"A不等于0B等于0","AB都不等于0")
6.VLOOKUP函数
终于来到了vlookup函数,这个函数的作用我愿称之为Excel最常用的函数(此处没有拉踩,只是我个人工作中最常用到这个函数而已,其他函数也很牛)。
1. 先看一下vlookup函数的结构:
vlookup(要查找的数据,要查找的数据所在的区域,要返回的数据在所选区域的第几列,模糊匹配(表示为1/true)或者精确匹配(表示为0/false))。看上去略显复杂,但是大白话来说就是,你想要匹配查找的这个数据他在哪一部分区域,然后你要匹配哪一个属性,精确匹配还是模糊匹配。一般都是采用精确匹配,不咋用模糊匹配。
举个例子,有一张表,包含了客户的信息,但是你需要匹配到每个客户对应的客户经理的信息,完整的客户和客户经理的信息在另一张表,此时你就可以用vlookup来匹配,客户名或者客户号作为你要查找的数据,完整的信息表作为查找区域,如果客户经理在查找区域的第二列,那么col_index_num输入2,再输入0,即可完成客户与客户经理的匹配。
另外,vlookup函数是可以跨表查询的,此处跨表指跨两个.xlsx,不是sheet1、sheet2表单,当然表单也是可以跨的。这个函数大家自行操作熟练几遍即可掌握了。
2. 精确匹配一定是完全符合,才能匹配上。举个栗子:
这里查找a返回错误,说明没有找到a,先给大家看一下这里用的函数表达式:表达式看上去是没有错的,但是为什么匹配不到呢?原因在于!左边的大表格中,a的后面有空格,这样也是无法匹配成功的!所以,这里引出我们的通配符!
1) * :代替不定数量的字符
2)?:英文输入,代替一个字符
此时,=VLOOKUP(I96&"*",F96:G103,2,0)
,注意需要用 & 符号连接,并且加双引号(英文的),其实本该包含a的值都返回的,但是vlookup函数只返回第一个值。
查找b开头并且是三个字符所对应的数值:
=VLOOKUP(I99&"??",F96:G103,2,0)
到此时,vlookup查询已经告一段落。
接下来是vlookup根据聚合查询。
首先,涉及到数据透视表,选中源数据表,点击插入–数据透视表-- 选择位置插入–行标签选择门店id和gmv–求和,即可得到透视表。
如果每一个求和项都要拉一张透视表的话,这对于实际业务场景来说是不合适的,因此我们需要使用vlookup函数引用数据透视表计算好的内容。
以下基操,不多赘述:
上图中写好了第一个vlookup,其他的可以直接下拉完成快速填充。与其他函数写法不同的在于,这里在选中区域时,多了一个 $ 符号,这是锁定区域的作用,如果不锁定的话,各位小伙伴可以自己去尝试一下,会发生整体区域向下偏移的错误。
小tip:可以按F4快捷锁定区域。
后续是Excel高级用法,index和match的联用,内容较多且复杂,不过熟悉之后会是一大“杀器”。