Excel在SEM中的应用
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
SEM中EXCEL的应用
在我们日常的优化工作当中，Excel是最常用的工具之一。熟练的运用Excel会使你的优化事半功倍。本节内容并非是对Excel的功能全景介绍，而是针对于在SEM中经常用到的Excel功能及函数进行汇总。这其中可能有些内容是你天天在用的，也可能有些功能是你并不熟悉。本节中对于所提到的公式只是做一个简单的介绍，想要更深入的了解这些公式应用请查看Excel相关资料。
图3：分列功能在Office2007中的位置：数据——分列
图3
4：删除重复项
删除重复项主要功能就是去重。有时我们需要给所有推广单元添加一条新广告，如果我们手中有一份关键词结构列表，这时就需要对这份列表关于推广单元删除重复项。最终得到一份推广单元唯一的列表，再结合其他公式撰写广告，最终可快速实现给所有推广单元添加广告的目的。
5：Trim
Trim函数的语法为：Trim(text)
Trim函数是为了去掉字符串首部和尾部的空格。有时我们通过第三方拓词/排名监测工具所生成的Excel文件中，每个关键词数据的末尾都包含一个隐藏的空格。这个空格会使得我们下一步的操作变得很困难，但使用Trim函数即可批量去掉这些空格。
6：Concatenate/”&”
最后要提到的是：本节内容更看重的是Excel的一些功能能给我们SEM的工作带来多少便捷。我们需要更多的思考这个问题，而不是仅仅停留在Excel公式本身。只有抱着这样的思维，才能发现并创造出更多的公式用法，从而进一步提升我们的工作效率。
1：常用功能及公式函数讲解
(1)：SEM中Excel常用功能
1：排序
排序是EXCEL中最基础的功能之一，在SEM中排序的应用更是必不可少。我们对于账户进行优化时，首先需要挑选出哪些词是我们的优化目标。一般我们手中会拿到一份关键词消费报告，我们会把更多精力放在关注那些花费较高、转化较多、展示量较大的关键词表现。而如何将这些词挑选出来，便需要用到排序功能。
图1：排序功能在Office2007中的位置：开始——排序和筛选——升序/降序
Subtotal函数功能比较多，根据”function_num”对应数字的不同，一共有22种功能，其中function_num=9代表的就是Sum。Subtotal相对于Sum的好处是可以支持筛选后的数据求和。同时我们可以通过：“开始——自动求和”对于一个存在筛选的数据进行求和加总，这时自动得到的公式便是”Subtotal”。
图2：筛选功能在Office2007中的位置：开始——排序和筛选——筛选
图2
3：分列
分列功能常用于将一些有规律的文字进行分离，拿到其中自己想要的部分。
比如：
推广计划名称：
产品词-服装-价格
产品词-家居-报价
产品词-品牌-促销
……
上面是我们比较常见的推广计划命名规则，现在需要将推广计划中描述产品类型的词语取出。这时我们就需要用到分列功能。
2：柱形图、折线图的基本操作
图表的制作是Excel中的基本操作。在日常工作中经常会用的到。对于SEM工作者来说更是家常便饭。在本节中会介绍一些基本图表的制作方法，同时告诉大家如何将两种图表类型放在同一张图中，并设立两条独立的坐标轴。这些小技巧都是我们在做一般图表时经常会用到的，希望大家都可以掌握。
(1)：按照图，将数据表格填入Excel表格中：
Sumifs函数的语法为：
Sumifs(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Sumif函数与Countif的语法非常相似，只不过一个是用来计数，一个是用来求和。在我们做表时有时需要通过这个公式来将不同渠道的数据进行汇总。Sumifs函数可在Sumif的基础上添加更多的限定条件。
(3)：SEM制作报告中常用公式：
1：Today
Today函数的语法为：TODAY()
Today函数就是用来得到今天的日期（与计算机记录的日期一致）。他会根据当天实际的日期进行更新。
很多人不知道日期也是可以”+/-”的。如果我们想得到昨天的日期，那就可以用“=Today()-1”来实现。这个函数在平日制作日报是都可以用的上。
另外，在日常拓词时，偶尔也会碰到同一个关键词被同时拓展好几遍的情况出现。这时也可以通过删除重复项的功能将其去除。
图4：删除重复项在Office2007中的位置：数据——删除重复项
图4
5：数据有效性
数据有效性的功能在优化中应用并不多，但是在做一份计划或报告时却很有用。有时我们需要做一份优化工作记录表，来记录我们每天的优化内容。这其中可能有关键词调价、修改匹配、重新分组、增减创意等等的优化手段。在一个相对完善的工作记录表中，便会用一列内容记录这些优化内容。这时我们可以通过数据有效性的功能，固定该列可以填写的内容，使其变为可选的下拉菜单。
Countif函数是用来计数目标范围内符合要求的关键词个数。符合的要求可以是取值范围、可以是包含某个汉字或字符等等。
Countifs函数与Countif的区别是前者可以连续设定多个限定条件，只有同时满足多个限定条件的时候才能被计数。
4：Sumif/Sumifs
Sumif函数的语法为：Sumif(range,criteria,sum_range)
图5：数据有效性在Office2007中的位置：数据——数据有效性
图5
6：窗口重排
在运用Excel进行编辑时，很多时候需要同时用到多个Excel工具表。这时候就需要将这几个Excel表一起排在Office2007中的位置：视图——窗口重排
图6
7：冻结窗口
Weeknum函数是用来返回一个日期所对应为本年度的星期数。
这两个公式通常需要结合日期公式一起使用。都是制作报告时常用的日期函数。
3：Countif/Countifs
Countif函数的语法为：Countif(range, criteria)
Countifs函数的语法为：
Countifs(criteria_range1, criteria1, [criteria_range2, criteria2]…)
3：Iferror
Iferror函数的语法为：Ifferror（value,value_if_error）
Iferror函数的功能是避免一些特殊情况导致公式出现错误，并给错误的单元格附上一个新值。这个公式在SEM中更多是与除法结合起来使用。有时候需要去计算CPA、转化率等等数据辅助分析，但如果除数为”0”，最终的结果会显示成”#DIV/0!”。为了避免这种情况的发生，就需要用到这个这个公式。
2：Weekday/Weeknum
Weekday函数的语法为：Weekday(serial_number，return_type)
Weeknum函数的语法为：Weeknum(serial_number，return_type)
Weekday函数是用来返回一个日期所对应当周第几天。返回值为1（星期天）到7（星期六）之间的一个整数。
图8：宏在Office2007中的位置：视图——宏
图8
(2)：SEM优化中常用公式：
1：SUM/Subtotal
Sum函数的语法为：sum(number1,number2, ...)
Subtotal函数的语法为：Subtotal(function_num,ref1,ref2,…)
Sum函数主要用于求和。在优化中我们经常需要对一些数据进行求和，在这时候我们就需要用到这个公式。我们也可以通过：“开始——自动求和”来实现相同的功能。
（8）：最终调整一下表格大小，大功告成
3：数据透视表的基本操作
数据透视表在SEM中也是非常常用的功能之一。比如我们的账户中分为品牌词、产品词、通用词、竞品词和人群词5部分，每一部分都对应几个不同的推广计划。现在我想要得到这五部分关键词的表现情况，这时候就需要用到数据透视表。数据透视表是一种可以快速汇总、分析大量数据表格的交互式工具。使用数据透视表可以按照数据表格的不同字段从多个角度进行透视，并建立交叉表格，用以查看数据表格不同层面的汇总信息、分析结果以及摘要数据。建立数据透视表的主要步骤如下：
Find函数和Search函数的用法非常相似，都是为了找出某个字符串在原始字符串中首次出现的位置。Search和Find函数的区别主要有两点：首先Find函数区分大小写，Search不区分。其次Search函数支持通配符，Find不支持。也就是说Find的使用范围相对小一些。
Left/Right函数是为了得到指定字符串从左/右算起指定个数的字符。之所以将这两个函数放在一起介绍是因为在SEM中这两个公式有时候需要一起使用。通过”Search/Find”找到目标字符所在的位置；再通过”Left/Right”去截取目标位置的字符，最终得到自己想要的结果。
有时候我们需要将Excel表中的第一行或第一列定住，在向下或者向右拖动表格时，使得第一行/列的内容一直存在，不会因为表格的移动而移动位置。这时候我们就需要用到冻结窗口的功能。
图7：冻结窗口在Office2007中的位置：视图——冻结窗口
图7
8：宏
宏的应用非常广泛，用一本书的内容去研究宏的应用也不算过分。宏在SEM中的应用也非常广泛。比如一些做报告的步骤，我们可以通过录制宏保留下来，然后在下次制作同样报告的时候通过播放宏直接得到完整的报告。再比如一些Excel小工具都是通过宏的后台编程从而实现的。这一点会在下文中关于小工具的章节再做介绍。
4：Find/Search，Left/Right
Find函数的语法为：Find(find_text,within_text,start_num)
Search函数的语法为：Search(find_text,within_text,start_num)
Left/Right函数的语法为：Left/Right(string , n)
图1
2：筛选
筛选同排序一样，都是平时最常用的功能之一。在SEM中我们经常需要查看某一个或某几个推广计划/单元的表现情况，或者需要找出包含某一个特定词语的关键词表现情况。这时候都需要用到筛选功能。
筛选功能支持：文本筛选、数字筛选、颜色筛选。灵活的运用筛选和排序功能可以帮助优化师快速有效的定位需优化的关键词。
2：Len/Lenb
Len函数的语法为：Len(text)
Lenb函数的语法为：Lenb(text)
Len函数的功能是返回文本中的字符数。无论是1个汉字、1个英文、1个数字都算作”1”个单位。
Lenb函数的功能是返回文本中代表字符的字节数。也就是汉字会算成2个单位，其他英文或数字还会算成1个。
我们通常需要用到Len用的地方。
5：Vlookup
Vlookup函数的语法为：Vlookup(lookup_value,table_array,col_index_num,range_lookup)
Vlookup函数在Excel中的应用非常广，在不同工作领域中也都需要用到它。这个公式是按列查找，最终返回该列所需查询列序所对应的值。在日常的工作中我们经常会用到。比如查看某一批关键词过去两个时间段的数据对比，比如查看两列关键词中有多少重复内容等等。这个公式建议一定要好好学习，活学活用。会在数据分析中帮到很多忙。
(2)：选中数据区域，插入->柱形图：
(3)：改变柱状图大小：
(4)：选中棕红色的柱状图，右击，在弹出的快捷菜单中选中“设置数据系列格式”：
(5)：在弹出的对话框中选中“次坐标轴”，然后单击“关闭”：
(6)：选中“点击量”，右击，选中“更改系列图表类型”：
(7)：调整表格坐标轴单位，以及下方文字的大小
6：Getpivotdata
Getpivotdata函数的语法为：
Getpivotdata(data_field, pivot_table, [field1, item1, field2, item2], ...)
Getpivotdata函数是专门针对数据透视表的函数，在制作日报时我们通常需要用到数据透视表合并数据，但是最终体现在报告中并不能以数据透视表的形式。这时候就需要用到这个公式从数据透视表中取数。好好学习这个公式会使得我们无论是制作报告还是做常规的数据分析变得更加自动化。这是一个经常被人们忽视的函数。
Concatenate函数的语法为：Concatenate(text1,[text2],…)
Concatenate函数主要用于将多个文本合并成一个。同时用”&”符号将几个单元格链接起来也能达到同样的效果。这个公式无论在我们组合关键词还是在编写创意时都有可能用到。灵活运用这个公式会大幅提高我们的工作效率。
在我们日常的优化工作当中，Excel是最常用的工具之一。熟练的运用Excel会使你的优化事半功倍。本节内容并非是对Excel的功能全景介绍，而是针对于在SEM中经常用到的Excel功能及函数进行汇总。这其中可能有些内容是你天天在用的，也可能有些功能是你并不熟悉。本节中对于所提到的公式只是做一个简单的介绍，想要更深入的了解这些公式应用请查看Excel相关资料。
图3：分列功能在Office2007中的位置：数据——分列
图3
4：删除重复项
删除重复项主要功能就是去重。有时我们需要给所有推广单元添加一条新广告，如果我们手中有一份关键词结构列表，这时就需要对这份列表关于推广单元删除重复项。最终得到一份推广单元唯一的列表，再结合其他公式撰写广告，最终可快速实现给所有推广单元添加广告的目的。
5：Trim
Trim函数的语法为：Trim(text)
Trim函数是为了去掉字符串首部和尾部的空格。有时我们通过第三方拓词/排名监测工具所生成的Excel文件中，每个关键词数据的末尾都包含一个隐藏的空格。这个空格会使得我们下一步的操作变得很困难，但使用Trim函数即可批量去掉这些空格。
6：Concatenate/”&”
最后要提到的是：本节内容更看重的是Excel的一些功能能给我们SEM的工作带来多少便捷。我们需要更多的思考这个问题，而不是仅仅停留在Excel公式本身。只有抱着这样的思维，才能发现并创造出更多的公式用法，从而进一步提升我们的工作效率。
1：常用功能及公式函数讲解
(1)：SEM中Excel常用功能
1：排序
排序是EXCEL中最基础的功能之一，在SEM中排序的应用更是必不可少。我们对于账户进行优化时，首先需要挑选出哪些词是我们的优化目标。一般我们手中会拿到一份关键词消费报告，我们会把更多精力放在关注那些花费较高、转化较多、展示量较大的关键词表现。而如何将这些词挑选出来，便需要用到排序功能。
图1：排序功能在Office2007中的位置：开始——排序和筛选——升序/降序
Subtotal函数功能比较多，根据”function_num”对应数字的不同，一共有22种功能，其中function_num=9代表的就是Sum。Subtotal相对于Sum的好处是可以支持筛选后的数据求和。同时我们可以通过：“开始——自动求和”对于一个存在筛选的数据进行求和加总，这时自动得到的公式便是”Subtotal”。
图2：筛选功能在Office2007中的位置：开始——排序和筛选——筛选
图2
3：分列
分列功能常用于将一些有规律的文字进行分离，拿到其中自己想要的部分。
比如：
推广计划名称：
产品词-服装-价格
产品词-家居-报价
产品词-品牌-促销
……
上面是我们比较常见的推广计划命名规则，现在需要将推广计划中描述产品类型的词语取出。这时我们就需要用到分列功能。
2：柱形图、折线图的基本操作
图表的制作是Excel中的基本操作。在日常工作中经常会用的到。对于SEM工作者来说更是家常便饭。在本节中会介绍一些基本图表的制作方法，同时告诉大家如何将两种图表类型放在同一张图中，并设立两条独立的坐标轴。这些小技巧都是我们在做一般图表时经常会用到的，希望大家都可以掌握。
(1)：按照图，将数据表格填入Excel表格中：
Sumifs函数的语法为：
Sumifs(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Sumif函数与Countif的语法非常相似，只不过一个是用来计数，一个是用来求和。在我们做表时有时需要通过这个公式来将不同渠道的数据进行汇总。Sumifs函数可在Sumif的基础上添加更多的限定条件。
(3)：SEM制作报告中常用公式：
1：Today
Today函数的语法为：TODAY()
Today函数就是用来得到今天的日期（与计算机记录的日期一致）。他会根据当天实际的日期进行更新。
很多人不知道日期也是可以”+/-”的。如果我们想得到昨天的日期，那就可以用“=Today()-1”来实现。这个函数在平日制作日报是都可以用的上。
另外，在日常拓词时，偶尔也会碰到同一个关键词被同时拓展好几遍的情况出现。这时也可以通过删除重复项的功能将其去除。
图4：删除重复项在Office2007中的位置：数据——删除重复项
图4
5：数据有效性
数据有效性的功能在优化中应用并不多，但是在做一份计划或报告时却很有用。有时我们需要做一份优化工作记录表，来记录我们每天的优化内容。这其中可能有关键词调价、修改匹配、重新分组、增减创意等等的优化手段。在一个相对完善的工作记录表中，便会用一列内容记录这些优化内容。这时我们可以通过数据有效性的功能，固定该列可以填写的内容，使其变为可选的下拉菜单。
Countif函数是用来计数目标范围内符合要求的关键词个数。符合的要求可以是取值范围、可以是包含某个汉字或字符等等。
Countifs函数与Countif的区别是前者可以连续设定多个限定条件，只有同时满足多个限定条件的时候才能被计数。
4：Sumif/Sumifs
Sumif函数的语法为：Sumif(range,criteria,sum_range)
图5：数据有效性在Office2007中的位置：数据——数据有效性
图5
6：窗口重排
在运用Excel进行编辑时，很多时候需要同时用到多个Excel工具表。这时候就需要将这几个Excel表一起排在Office2007中的位置：视图——窗口重排
图6
7：冻结窗口
Weeknum函数是用来返回一个日期所对应为本年度的星期数。
这两个公式通常需要结合日期公式一起使用。都是制作报告时常用的日期函数。
3：Countif/Countifs
Countif函数的语法为：Countif(range, criteria)
Countifs函数的语法为：
Countifs(criteria_range1, criteria1, [criteria_range2, criteria2]…)
3：Iferror
Iferror函数的语法为：Ifferror（value,value_if_error）
Iferror函数的功能是避免一些特殊情况导致公式出现错误，并给错误的单元格附上一个新值。这个公式在SEM中更多是与除法结合起来使用。有时候需要去计算CPA、转化率等等数据辅助分析，但如果除数为”0”，最终的结果会显示成”#DIV/0!”。为了避免这种情况的发生，就需要用到这个这个公式。
2：Weekday/Weeknum
Weekday函数的语法为：Weekday(serial_number，return_type)
Weeknum函数的语法为：Weeknum(serial_number，return_type)
Weekday函数是用来返回一个日期所对应当周第几天。返回值为1（星期天）到7（星期六）之间的一个整数。
图8：宏在Office2007中的位置：视图——宏
图8
(2)：SEM优化中常用公式：
1：SUM/Subtotal
Sum函数的语法为：sum(number1,number2, ...)
Subtotal函数的语法为：Subtotal(function_num,ref1,ref2,…)
Sum函数主要用于求和。在优化中我们经常需要对一些数据进行求和，在这时候我们就需要用到这个公式。我们也可以通过：“开始——自动求和”来实现相同的功能。
（8）：最终调整一下表格大小，大功告成
3：数据透视表的基本操作
数据透视表在SEM中也是非常常用的功能之一。比如我们的账户中分为品牌词、产品词、通用词、竞品词和人群词5部分，每一部分都对应几个不同的推广计划。现在我想要得到这五部分关键词的表现情况，这时候就需要用到数据透视表。数据透视表是一种可以快速汇总、分析大量数据表格的交互式工具。使用数据透视表可以按照数据表格的不同字段从多个角度进行透视，并建立交叉表格，用以查看数据表格不同层面的汇总信息、分析结果以及摘要数据。建立数据透视表的主要步骤如下：
Find函数和Search函数的用法非常相似，都是为了找出某个字符串在原始字符串中首次出现的位置。Search和Find函数的区别主要有两点：首先Find函数区分大小写，Search不区分。其次Search函数支持通配符，Find不支持。也就是说Find的使用范围相对小一些。
Left/Right函数是为了得到指定字符串从左/右算起指定个数的字符。之所以将这两个函数放在一起介绍是因为在SEM中这两个公式有时候需要一起使用。通过”Search/Find”找到目标字符所在的位置；再通过”Left/Right”去截取目标位置的字符，最终得到自己想要的结果。
有时候我们需要将Excel表中的第一行或第一列定住，在向下或者向右拖动表格时，使得第一行/列的内容一直存在，不会因为表格的移动而移动位置。这时候我们就需要用到冻结窗口的功能。
图7：冻结窗口在Office2007中的位置：视图——冻结窗口
图7
8：宏
宏的应用非常广泛，用一本书的内容去研究宏的应用也不算过分。宏在SEM中的应用也非常广泛。比如一些做报告的步骤，我们可以通过录制宏保留下来，然后在下次制作同样报告的时候通过播放宏直接得到完整的报告。再比如一些Excel小工具都是通过宏的后台编程从而实现的。这一点会在下文中关于小工具的章节再做介绍。
4：Find/Search，Left/Right
Find函数的语法为：Find(find_text,within_text,start_num)
Search函数的语法为：Search(find_text,within_text,start_num)
Left/Right函数的语法为：Left/Right(string , n)
图1
2：筛选
筛选同排序一样，都是平时最常用的功能之一。在SEM中我们经常需要查看某一个或某几个推广计划/单元的表现情况，或者需要找出包含某一个特定词语的关键词表现情况。这时候都需要用到筛选功能。
筛选功能支持：文本筛选、数字筛选、颜色筛选。灵活的运用筛选和排序功能可以帮助优化师快速有效的定位需优化的关键词。
2：Len/Lenb
Len函数的语法为：Len(text)
Lenb函数的语法为：Lenb(text)
Len函数的功能是返回文本中的字符数。无论是1个汉字、1个英文、1个数字都算作”1”个单位。
Lenb函数的功能是返回文本中代表字符的字节数。也就是汉字会算成2个单位，其他英文或数字还会算成1个。
我们通常需要用到Len用的地方。
5：Vlookup
Vlookup函数的语法为：Vlookup(lookup_value,table_array,col_index_num,range_lookup)
Vlookup函数在Excel中的应用非常广，在不同工作领域中也都需要用到它。这个公式是按列查找，最终返回该列所需查询列序所对应的值。在日常的工作中我们经常会用到。比如查看某一批关键词过去两个时间段的数据对比，比如查看两列关键词中有多少重复内容等等。这个公式建议一定要好好学习，活学活用。会在数据分析中帮到很多忙。
(2)：选中数据区域，插入->柱形图：
(3)：改变柱状图大小：
(4)：选中棕红色的柱状图，右击，在弹出的快捷菜单中选中“设置数据系列格式”：
(5)：在弹出的对话框中选中“次坐标轴”，然后单击“关闭”：
(6)：选中“点击量”，右击，选中“更改系列图表类型”：
(7)：调整表格坐标轴单位，以及下方文字的大小
6：Getpivotdata
Getpivotdata函数的语法为：
Getpivotdata(data_field, pivot_table, [field1, item1, field2, item2], ...)
Getpivotdata函数是专门针对数据透视表的函数，在制作日报时我们通常需要用到数据透视表合并数据，但是最终体现在报告中并不能以数据透视表的形式。这时候就需要用到这个公式从数据透视表中取数。好好学习这个公式会使得我们无论是制作报告还是做常规的数据分析变得更加自动化。这是一个经常被人们忽视的函数。
Concatenate函数的语法为：Concatenate(text1,[text2],…)
Concatenate函数主要用于将多个文本合并成一个。同时用”&”符号将几个单元格链接起来也能达到同样的效果。这个公式无论在我们组合关键词还是在编写创意时都有可能用到。灵活运用这个公式会大幅提高我们的工作效率。