excel搞定krakan2微生物组成数据

这个软件给出的结果很日鬼，数据相当杂乱。首先这个数据里直接看不出每个级别下未注释的序列数，其次，细菌、古菌、真核生物和病毒的分类级别不一样。我们先把数据整齐划一了，首先用excel打开文件，把tax列的"; "替换成“;”，然后把tax列按照“;”分列。在真菌的分类级别中，这软件把真菌划成了域，那真菌这里就有2个域“真核生物域”和“真菌域”，我们把真菌域划到真菌界吧。在域这一列把d__替换为k__。

 

在最后一列后添加以下列

d

k

p

c

o

f

c

o

f

g

s

在新建的d列中输入以下公式

其中match函数返回BV2:CC2列中包含"d__*"内容的单元格列号（*为通配符，从BV2开始算第1个），最后一个参数：0表示被查找单元格内容等于关键字。INDEX函数查找从BV2:CC2范围内，默认行（第一行）、第X列（MATCH返回值）的内容，如无命中则显示为错误。

=INDEX(BV2:CC2,,MATCH("d__*",BV2:CC2,0))

也可以直接用hlookup函数解决，查找值是d__*,范围，查找的第几行（本例中只有1行），匹配方式为精确（0）

=HLOOKUP("d__*",BV2:CC2,1,0)

粘贴后保留值、不保留公式内容（方便编辑）,把#/NA替换成NULL(随便什么字符都好)

接下来就是要计算各级别中未注释的结果，我们先看种水平上未注释的内容，从下表中能看出来，在属Butyrivibrio中有80029条序列，能注释到种水平的就是这个属下的3个种求和，把这两块求差值就是该属中未能注释到种的序列数，说起来简单，手动计算得搞个几天，本来想用VBA，奈何这个语言不太熟悉，不知道筛选（本来打算把autofilter把属单独提出来，用for each循环挨个计算后把结果输出，奈何失败啊）如何挨个做遍历，还是考虑下怎么编辑公式吧

98	d	k	p	c	o	f	g	s
6068358	d__Bacteria	NULL	NULL	NULL	NULL	NULL	NULL	NULL
1344441	d__Bacteria	NULL	p__Firmicutes	NULL	NULL	NULL	NULL	NULL
704629	d__Bacteria	NULL	p__Firmicutes	c__Clostridia	NULL	NULL	NULL	NULL
673248	d__Bacteria	NULL	p__Firmicutes	c__Clostridia	o__Clostridiales	NULL	NULL	NULL
174311	d__Bacteria	NULL	p__Firmicutes	c__Clostridia	o__Clostridiales	f__Lachnospiraceae	NULL	NULL
80029	d__Bacteria	NULL	p__Firmicutes	c__Clostridia	o__Clostridiales	f__Lachnospiraceae	g__Butyrivibrio	NULL
51961	d__Bacteria	NULL	p__Firmicutes	c__Clostridia	o__Clostridiales	f__Lachnospiraceae	g__Butyrivibrio	s__Butyrivibrio fibrisolvens
14508	d__Bacteria	NULL	p__Firmicutes	c__Clostridia	o__Clostridiales	f__Lachnospiraceae	g__Butyrivibrio	s__Butyrivibrio proteoclasticus
13559	d__Bacteria	NULL	p__Firmicutes	c__Clostridia	o__Clostridiales	f__Lachnospiraceae	g__Butyrivibrio	s__Butyrivibrio hungatei

 先做自动筛选，选择属这列，导出。然后将导出的属名复制到g这一列的最后面

 

某一属的序列总数是属名+种为NULL对应的序列数，该属可注释的种的序列数为该属名+有明确种名的序列数的和，用以下公式计算。sumif不方便做多条件，所以我把所有属名是这个的序列求和，刚好多加了一个属的总序列数，所以前面乘以2。

=2*INDEX(BU$2:BU$10230,MATCH("NULL"&$CB10232,$CC$2:$CC$10230&$CB$2:$CB$10230,0))-SUMIF($CB$2:$CB$10230,$CB10232,BU$2:BU$10230)

带着公式这个文件会非常大且计算慢，我们检查结果没问题后复制粘贴数值，不保留公式。然后把这些属对应的科目纲门界域信息贴上去，处理这个可以用透视表。

计算科水平上未注释的微生物，这个运算躲不过多条件了（其实也可以，只不过运算公式会更长），用下sumifs()，公式如下，不做详解。

=INDEX(BU$2:BU$10230,MATCH("NULL"&$CA12111,$CB$2:$CB$10230&$CA$2:$CA$10230,0))-SUMIFS(BU$2:BU$10230,$CA$2:$CA$10230,$CA12111,$CB$2:$CB$10230,"<>NULL",$CC$2:$CC$10230,"NULL")

检查下惊奇的发现有很多负值，比如如下：

可以看到，这第一列中，这个科水平下总共有31条序列，结果这个科下有的属居然有8842条，简直是莫名其妙。这个karaken2软件绝对有问题。 再往上不算了，我只做到这一级别吧，光看下能注释到种水平的结果得了，看到这个恶心的结果我想换软件重新跑了。不管怎么样，还是看下结果

先按照s排序，把带NULL的行全部删除掉，发现可以注释到种的9226个，其中细菌4621个种，甲烷菌302个，真核生物553个，病毒3751个（好家伙有这么多）。接下来把刚刚做的能注释到属不能注释到种的种这列填写unknown。