网络营销学而行

学而行营销网 > 广告传播 > SEM搜索广告

4个Excel函数解决sem优化师90%数据分析问题

雷公子 艾奇SEM 2018-02-02

提起分析,不得不说的就是函数,函数让数据的整合方便而高效,当然竞价的数据分析也离不开 数据的整合,如:从消费报表到关键词的转化报表、从关键词着陆页报表到消费报表等等,需要将2份或者以上报表汇总在一起的很多,之前就给群里的朋友做过一次从搜索词消费数据与关键词数据的合并,还有之前所做的关键词标记等,下面我就引出第一个我们需要了解的函数:VLOOKUP(a,b,c,d)函数。

  一、VLOOKUP函数介绍   

vlookup函数有四个参数,我分别用a、b、c、d表示。

vlookup的作用:通过某一字段将另一个表格中的数据引入到当前表格。字段如果有朋友没听说过的话可以暂时理解为共同项。

举个很简单的例子:我有2份信息表,第一份是某个班级的学生的信息,包括学生的姓名,学号,年龄,身高,民族...等数据,另外一份表格是学生的期末考试成绩单,其中包括学生的学号,姓名,还有语文成绩,数学成绩等等。

现在我需要得到一份既有学生信息(姓名,学号,年龄,身高,民族)等,还要有(语文成绩,数学成绩等考试成绩的报表),大家应该认为这个很简单。按照姓名一个个去找,然后写进来就行了。或者有的学生就感觉不对了,我们应该按照学号去找,避免重名重姓的学生,很好。这块我给大家说两个知识点,这个姓名或者学号都可以理解为一个单独的字段。通过两个表格中共有的字段,将数据映射过来,这就是vlookup的简单应用以及原理。

我们不要被四个参数abcd给吓住,假设想把数学语文成绩紧跟在第一张学生信息表的后边两列,下面通过上边例子介绍vlookup的四个参数分别代表什么。

首先我说的一个参数a,如果我们排除重名重姓的情况通过姓名查找的话a就代表:“张三、李四、王五...”等,如果我们通过学号去找,那么a就代表:"09001、09002、09003..."等,那b呢,b就代表你要去查找的区域,如($a$1:$d$100)区域,如下图。

a的字段必须在b区域的第一列,相当于在$a$1:$d$100区域的第一列也就是A列查找“张三”,第三个参数c就代表你要返回“张三”所在行的数据区域$a$1:$d$100的第几列,大家应该可以清晰的看到,如果是语文成绩的话应该返回第三列,c的值应该为“3”,如果查找数学成绩则应该返回第四列,c的值就应该为“4”。最后一个参数d的意思是近似匹配或者完全匹配,我们查找姓名或者学号当然需要完全匹配,及d的值为“false"。  

1.png

四个参数已经给大家介绍清楚了,下面看看原始数据中如何使用。所以H3单元格中的公式应该是我红框内的公式。最后,初学的朋友可以根据学号超找返回下数学语文成绩。

2.png

OK,关于上边区域选择的时候我是用了绝对引用的符号“$”,不明白的朋友可以自行百度“绝对引用"。vlookup函数就为大家简单介绍到这里。关于vlookup的实际中的应用大家可以参考一下文章。

vlookup函数为大家介绍完毕。

  二、COUNTIF函数介绍  

除了vlookup函数,COUNTIF也是不得不为大家介绍下常用的一个统计函数 countif(条件计数函数),这个函数在竞价中最常用的功能就是计算某个关键词的数量,计算某个关键词产生的对话数等。

例如,我们通过商务通数据想快速汇总出每个搜索词产生的对话多少条,每个着陆页产生对话多少条等等,这中功能在数据透视表中其实也可以实现。先给大家通过通俗的语言介绍什么是 countif 函数。

一句话解释:在一组数据中统计符合某一条件的值的数目。(某一条件可以是一个单元格如A5,也可以是一个条件)

3.png

1、统计消费大于等于500的关键词有多少个。输入公式=COUNTIF(B2:B10,">=500"),结果为“3”。统计消费小于250元的关键词,同理=COUNTIF(B2:B10,"<250")

2、统计极佳对话的个数,输入函数=COUNTIF(E2:E23,"极佳对话"),返回值“11”,表示E2:E23区域内极佳对话为11。

3、统计每个关键词产生的对话数,这块因为导出的每个关键词都是产生对话的关键词,我直接用 countif 计数,不用多条件去区分极佳和较好对话了。先复制一份关键词删除重复项,在H2处输入函数=COUNTIF(D2:D23,G2),下拉填充,搞定。  

4.jpg

  三、SUMIF函数入门  

紧接着上边 countif 给大家为大家介绍下条件求和函数吧。说起这个函数的作用。例如可以从关键词报告中快速统计出来包括医院的关键词的消费总和。包括“治”的关键词的消费总额,可以快速统计每个计划的消费总和。这些功能也可以使用数据透视表进而简化。但是入门还是个大家介绍些好。

一句话解释:在一组数据中统计符合某一条件的值的和。(某一条件可以是一个单元格如A5,也可以是一个条件)基本同上,一个统计数目一个求和。

简单说明,sumif 有三个参数,比 sumif 多一个参数。第一个参数和 countif 相同,是区域,第二个参数也和 countif 相同表示条件,第三个参数表示汇总值的区域。那下边第一个问题来说,参数一就是A2:A10区域,第二个参数就是计划名称,第三个参数因为我们要汇总消费,所以第三个参数就是D2:D10,这块大家记得回顾我前面说的公式里边的相对引用和绝对引用。$$一定要多使用。

1、统计计划消费的总和。

1)复制计划名称,删除重复项。

2)在G2处输入公式=SUMIF(A:A,F2,D:D)然后下拉填充即可看到下图效果。  

5.png

2、统计关键词中包括“医院”的词的消费总和。

输入函数=SUMIF(C2:C10,"*医院*",D2:D10)

第二个参数我做下解释,“*医院*”大家都知道“*”代替任意字符。所以,我这块表示医院前边有任意字符或后边有任意字符,即只要关键词中包含医院则符合该条件。

3、统计消费高于450元的关键词的消费总额。

这个比较简单了。输入公式=SUMIF(D2:D10,">450",D2:D10)即可。我就不做解释了。

四、SUBSTITUTE函数介绍  

这个函数看着比较长,参数也稍微比较多,大家不要被吓着,其实这个就是很简单的替换函数。有时候对我们推广还是很有帮助的,这块我就提一个例子。希望大家能够吸收,然后据为己有。会是什么呢?竞价中大家最头疼的可能莫过于写创意了。很多人发现我一个一个复制也很复杂,每次复制完了还需要去修改通配符里边的关键词。是不是很麻烦呢。

没错,给大家介绍的这个函数就可以实现快速批量修改通配符里的关键词。下面给大家说说思路还有操作方法吧。

1、先将创意分好类,这个分类就是医院类的创意,治疗类的创意,症状类的创意,可以是从别的地方拿来的,也可以是自己针对病种写的。这个是怕你到时候把弄得创意四不像。这个事先自己将创意复制进每个单元,不需要修改通配符里的关键词,一会统一大批量改。

2、每个单元里边的核心词也就是你将要在通配符中展现的关键词,需要整理出来。

3、开始操作。

1)从账户中将需要替换的创意全部复制到excel中。

2)将通配符里的关键词替换为“△”(可以是任意一个字符,一会将他替换成该单元的核心关键词)方法大家知道吗?替换{*}为{△}

3)通过VLOOKUP函数将单元对应的核心关键词匹配过来。

6.png

4)在N1处输入公式“=SUBSTITUTE(A2,"△",$M$2)”,然后向右拉两格,然后向下填充即可看到替换后的创意出现在你的面前。  

7.jpg


知识点:

编辑声明:本站收集的部分公开资料来源于互联网,转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。如发现网站有侵犯您知识产权的作品,请联系我们及时修改或删除。联系方式:qq或邮箱:284332981@qq.com

华哥

学而行营销网 站长/编辑

网络营销与数字广告策划专家/培训师
个人网站:www.xiaoyunhua.com

栏目导航
市场营销百科
圣诞节活动策划 STP分析 市场细分 消费者行为心理 借势营销 波特五力模型 事件营销 六一儿童节活动策划 端午节活动方案 定位理论 市场调研 李叫兽 酒店营销 饥饿营销 usp理论 swot分析法 营销心理学 4R营销理论 4PS营销理论 4P营销理论 4C营销理论 口碑营销 渠道管理
数字营销策略
外贸网络营销 场景营销 二维码营销 全网营销 微博营销 网红营销 大数据营销 病毒式营销 社会化营销 自媒体营销 新闻营销 内容营销 软文营销 网络营销工程师 网络营销师 邮件营销
广告策划投放
百度竞价 圣诞节文案 微信广告 百度信息流广告 程序化广告 广点通 腾讯社交广告 公益广告 父亲节文案 原生广告 可口可乐广告 网盟推广 叶茂中 广告可见性 大卫•奥格威 sem优化 ASM 信息流广告 着陆页 媒介策划 AB测试 杜蕾斯广告 DSP广告 RTB广告模式 广告法
运营增长官
竞品分析 axure 微信小程序 excel数据透视表 百度统计 增长黑客 热力图 社群营销 Google Analytics 互联网产品经理 SEO优化 ASO
电商淘宝操盘手
新零售 农村淘宝 淘宝客推广 钻石展位 跨境电商 速卖通 微商怎么做 阿里巴巴推广
创业经管大佬
杰克韦尔奇 互联网创业 阿米巴经营模式 稻盛和夫 共享经济 网红经济 松下幸之助 陈春花 乔吉拉德 菲利普·科特勒
职场销售女神
情绪管理 时间管理 化妆品销售技巧 服装销售技巧和话术 汽车销售技巧和话术
搜索