众所周知，从事SEO的第一步是准备关键词词库。而获得关键词的方法有很多种，比如采集下拉框及相关搜索，采集竞争对手词库，乃至更高效的直接通过搜索引擎的API来建立词库等等。
假设你现在需要新建一个词库，则最起码你需要有一批词根，以此做第一轮的相关关键词扩展。对于绝大部分朋友来说，词根其实就是产品名，比如mp3,iphone,galaxy s3等等。本文不讨论这类词根的纵向拓展（纵向拓展词的定义：类似mp4作为mp3的相关搜索词，北京旅游作为南京旅游的相关搜索词，我们在此定义为纵向扩展词；横向拓展词的定义：类似mp3
在整理好属性相同的一批词根列表（比如mp3,mp4,mp5他们同作为视频播放器，是可以视为统一聚类进行分析的，因为搜索的用户是同一群人。其他的如北京水魔方，常州恐龙园，苏州乐园也属于属性相同的词根）之后，我们可以通过一些工具（如光年百度推广关键词获取工具）来对词根进行扩展，在得出结果之前我们都是很happy的，但是得到结果之后绝大部分人就崩溃了。因为绝大部分人的词根列表少则几千多则上万，而扩展得出的结果少说也有10万条，数据量少的时候我们可以通过手动提取单个词的长尾前后缀，但是词库扩展后的结果显然完全没法人工来做。。。
至此，如果我们要分析长尾关键词前后缀，第一步就是剔除横向扩展后词库当中的词根，得到前后缀部分。批量剔除词根的方法有很多，比如shell，但是我本人更亲睐于excel，毕竟他很方便而且直观。
在打开的excel表格中，我们假设sheet1存放了关键词词库，sheet2存放了1000个词根如下图所示。
示例扩展后的关键词词库如下图：
示例用于扩展的词根如下图：
接下来我们在sheet1的C1单元格中输入为包含什么词根，在C2单元格中输入以下数组函数{=INDEX(sheet2!$A$2:$A$1000,MATCH(TRUE,ISNUMBER(FIND(sheet2!$A$2:$A$1000,A2,1)),0))}（注意match_type=0是一定要写的，不然这段程序会出错），然后双击填充所有下方单元格。当当当当，所有词根横向扩展出来的长尾关键词就全部出现鸟。接下来要做的就是筛选，勾选掉#N/A的部分，得出的就是所有横向扩展出的长尾关键词啦，成果如下图所示：
接下来我们要做的是在sheet1中的D1，E1单元格分别输入长尾词前缀,长尾词后缀。然后在D2,E2单元格分别输入以下函数：=LEFT(A2,FIND(C2,D2)-1)以及=RIGHT(A2,LEN(A2)-FIND(C2,A2)-LEN(C2)+1)。然后双击向下填充所有单元格即可。至此我们已经得到了所有长尾关键词的前后缀了，终于快完工了哦！
但是这边还有一点需要考虑到的是，尽管词根列表都是唯一的，但是其中有可能会有自我包含的情况的。比如在我的词根当中会有这么两行数据，“西湖”以及“瘦西湖”，这两个词语代表的是两个不同的景点，但是其中都包含了同一词语也就是“西湖”。那么如果我们不对词根所在的sheet2做任何处理的话，有可能在去词根时出现偏差，比如在sheet1中的“瘦西湖门票价格”该行进行词根匹配的时候，词根“西湖”以及“瘦西湖”的命中都为TRUE，这个时候MATCH函数会从两者中返回第一个出现的词语所在sheet2中的行数，那如果在词根列表中“西湖”行数小于“瘦西湖”行数，那返回的结果就是“西湖”，那么剔除词根的时候，后缀长尾词倒是不会受到影响，但是前缀长尾词就悲剧的变为“瘦”了。。。
那么如何避免这个问题呢，其实很简单，首先，我们对词根所在的sheet2进行自包含的查询，新建一列数据，假设是B列，在B2中填入以下数组函数公式做自包含查询，然后向下填充：{=COUNT(FIND(A2,$A$2:$A$1000))}
该函数得到的数字可以理解为这样一个过程：你ctrl+c复制该单元格然后ctrl+f调出查找选项框,然后ctrl+v粘贴最后点击查找全部后结果中得出来的行数。
还有尽可能将选中的绝对列数据拉长点以避免将来表格中有新数据加入。然后进行筛选，查看其中是否存在自我包含。如果词根不存在自包含，那就谢天谢地啥都不用干了，但如果存在的话，则再新建一列，假设是C列，在C2中填入以下公式：=LDN(a2)，然后向下填充。最后对表格内所有数据按照该列字符串长度进行降序排列即可。
这么做能解决上面提到的西湖、瘦西湖问题的原理是：长词优先。
最后一步就是利用excel自带的数据透视图就OK了，操作很简单的，所以直接上图了，至此我们就全部完成了词库从获取，到筛选出横向长尾关键词，到剔除词根截取到长尾关键词前后缀，到生成数据报告的全过程了。-道哥
