医疗数据分析常用函数及技巧

医疗竞价的关键在于成本控制，很多朋友经常会问我如何去做数据分析，也有不少朋友会问我有没有数据分析类的表格能够更方便有效的去做数据分析。

毕竟竞价里边没有多么复杂的数据源，所以我个人主张从竞价入手，而非数据分析，碰到竞价中的问题，然后去找方法，去汇总融汇贯通。

记得之前带竞价又一次告诉他做数据分析的时候，告诉他思路的时候，我问他之前这么想过吗，他说都想过，就是之前没有找到一个合适的方法感觉无从下手，其实他缺少最后一步没有做，没有与分享他的问题，去寻找方法。

今天小磊也不是讲什么深刻的分析方法，只是说入门，说思路，高手可以避道，谢谢。

首先，提起分析，不得不说的就是函数，函数让数据的整合方便而高效，当然竞价的数据分析也离不开数据的整合，如：从消费报表到关键词的转化报表、从关键词着陆页报表到消费报表，等等需要将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"。

四个参数已经给大家介绍清楚了，下面看看原始数据中如何使用。

所以H3单元格中的公式应该是我红框内的公式。

最后，初学的朋友可以根据学号查找返回下数学语文成绩。

ok，关于上边区域选择的时候我是用了绝对引用的符号“$”，不明白的朋友可以点击：Excel数据透视表相对引用和绝对引用以及混合引用。

vlookup函数就为大家简单介绍到这里。

关于vlookup的实际中的应用大家可以参考一下文章。

1)、百度竞价关键词跟踪_关键词标记
vlookup函数为大家介绍完毕。

二、COUNTIF函数介绍
除了vlookup函数，COUNTIF也是不得不为大家介绍下常用的一个统计函数countif(条件计数函数)，这个函数在竞价中最常用的功能就是计算某个关键词的数量，计算某个关键词产生的对话数等，例如，我们通过商务通数据想快速汇总出每个搜索词产生的对话多少条，每个着陆页产生对话多少条等等，这中功能在数据透视表中其实也可以实现，后边给大家附上数据透视表的学习动画及学习地址。

先给大家通过通俗的语言介绍什么是countif函数。

一句话解释：在一组数据中统计符合某一条件的值的数目。

(某一条件可以是一个单元格如A5,也可以是一个条件)
上图：
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)，下拉填充，搞定。

三、SUMIF函数入门
紧接着上边countif给大家为大家介绍下条件求和函数吧。

说起这个函数的作用。

例如可以从关键词报告中快速统计出来包括医院的关键词的消费总和。

包括“治”的关键词的消费总额，可以快速统计每个计划的消费总和。

这些功能也可以使用数据透视表进而简化。

但是入门还是个大家介绍些好。

一句话解释：在一组数据中统计符合某一条件的值的和。

(某一条件可以是一个单元格如A5,也可以是一个条件)基本同上，一个统计数目一个求和。

简单说明，sumif有三个参数，比sumif多一个参数。

第一个参数和countif相同，是区域，第二个参数也和countif相同表示条件，第三个参数表示汇总值的区域。

那下边第一个问题来说，参数一就是A2：A10区域，第二个参数就是计划名称，第三个参数因为我们要汇总消费，所以第三个参数就是D2：D10，这块大家记得回顾我前面说的公式里边的相对引用和绝对引用。

$$一定要多使用。

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

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

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

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

输入函数=SUMIF(C2:C10,"*医院*",D2:D10)
第二个参数我做下解释，“*医院*”大家都知道“*”代替任意字符。

所以，我这块表示医院前边有任意字符或后边有任意字符，即只要关键词中包含医院则符合该条件。

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

这个比较简单了。

输入公式=SUMIF(D2:D10,">450",D2:D10)即可。

我就不做解释了。

四、SUBSTITUTE函数介绍
这个函数看着比较长，参数也稍微比较多，大家不要被吓着，其实这个就是很简单的替换函数有时候对我们竞价还是很有帮助的，这块我就提一个例子。

希望大家能够吸收，然后据为己有。

会是什么呢?竞价中大家最头疼的可能莫过于写创意了。

很多人发现我一个一个复制也很复杂，每次复制完了还需要去修改通配符里边的关键词。

是不是很麻烦呢。

没错，给大家介绍的这个函数就可以实现快速批量修改通配符里的关键词。

下面给大家说说思路还有操作方法吧。

1、就是你将创意分好类，这个分类就是医院类的创意，治疗类的创意，症状类的创意，可以是现成偷来的也可以是自己针对病种写的。

这个是怕你到时候把弄得创意四不像。

这个事先自己将创意复制进每个单元，不需要修改通配符里的关键词，一会统一大批量改。

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

3、开始操作。

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

2)将通配符里的关键词替换为“△”(可以是任意一个字符，一会将他替换成该单元的核心关键词)方法大家知道吗?替换{*}为{△}
3)通过VLOOKUP函数将单元对应的核心关键词匹配过来。

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

五、字符长度len/lenb函数介绍
LEN返回文本串的字符数。

最后再给大家推荐下数据透视表的学习视频，及动画学习教程吧。

希望有兴趣的朋友可以去学习学习。

通配符在数据统计中的妙用
通配符“?”“*”“~”可以代表任意或一定范围的字符。

在Excel中，利用通配符不仅可以进行模糊查询替换，而且还可以通过与函数的配合进行模糊计算。

在Excel中通配符有3个，分别为?(问号)、*(星号)、~(波形符)。

?(问号)可代表任意单个字符;
*(星号)可代表任意数量的字符;
~(波形符)后可跟?、*或~问号、星号或波形符，例如，“fy06~?”可找到“fy06?”
一、用通配符进行查询替换
实例描述：在作品登记表中，有作者的名字中某个字出现了同音不同字的现象，要求都改成正确的名字(图1)。

我们分析一下，这个表中作者“马震安”中的第二个字分别出现了“震”、“振”、“镇”三种情况，要改成正确的“马震安”，如果一个一个地改，数量少了还不费事，但是要改的数量多了，那还是很让人头疼的。

这时，我们可以利用通配符来对作者进行查询替换。

利用组合键Ctrl+F调出“查找和替换”对话框，选择“替换”标签，在“查找内容”出输入“马?安”(注意是英文半角的“?”)，在“替换为”处输入正确的“马震安”，最后点击“全部替换”按钮，这时就会把表中作者是“马震安”、“马振安”、“马镇安”的，都替换成“马震安”(图2)。

可见，有了通配符的帮助，在Excel中可进行更大范围的查询替换，在日常办公中还是非常有用的。

二、通配符在计算中的应用
实例描述：某商场要对一星期内的各个品牌的电动车销售情况进行统计。

这家商场的电动车主要有捷安特、
彪牌、追风鸟、雅迪等这4个品牌，要求对一个星期的流水账按这4大品牌进行分类统计。

我们分析一下，如果我们按通常的做法对数据进行分类汇总，然后再把得到的结果填到相应的单元格中，但
当我们按商品进行分类时，这些品牌都有两种型号，这一个品牌的两种型号就分成了两类，这显然是不行的。

有
没有什么方法一次性地来对这些品牌的销售进行统计呢?
这时，我们可以利用SUMIF函数对数据进行统计。

因为SUMIF函数中允许使用通配符，所以就能够很好地解决
分类问题。

我们可以在F4单元格中输入“=SUMIF($B$2:$B$25,E4&"*",$C$2:$C$25)”，然后拖动填充柄将公式填
充到F7单元格即可(图3)。

SUMIF函数的作用是按给定条件对指定单元格求和。

其语法为SUMIF(range,criteria,sum_range)。

range是要根据条件查找的单元格区域。

即公式中的$B$2:$B$25
Criteria为确定对哪些单元格相加的条件，其形式可以为数字、表达式或文本。

例如，条件可以表示为
32、"32"、">32"或"apples"。

公式中的E4&"*"，其中用到了&符号，它表示相连接，也就是把E4单元格的值与
通配符“*”相连接，得到“捷安特*”。

Sum_range为要相加的实际单元格(如果区域内的相关单元格符合条件)。

如果省略sum_range，则当区域中
的单元格符合条件时，它们既按条件计算，也执行相加。

整个公式就表示在B2:B25查找品牌是“捷安特”的商品，找到后计算它们的总销量。