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