暴躁的EXCEL测序数据分析方法（前传）

在上一期文章中，本宫介绍了利用EXCEL进行测序数据分析的方法，之后许多朋友提出了不少的问题，这里本宫对一些细节部分进行更为详细的介绍，同时分享一些EXCEL的使用技巧。

PowerPivot插件

有不少朋友发现这么一个问题，本宫介绍的那个SOFT文件不能完全加载。

原因在于EXCEL只能显示104,8576行，而这个测序数据有200多万行。

微软的免费工具PowerPivot可以帮我们解决这个问题。（Office2010需要下载该插件，下载地址：http://go.microsoft.com/fwlink/?LinkId=155905；Office2013及以上版本是自动集成在EXCEL里的，这里以2013版本为例）

这个插件隐藏的有点深，用之前需要把它拖出来。首先在主选项卡中把“开发工具”给调出来。

然后打开“开发工具”进入“COM加载项”，然后勾选使用PowerPivot。

然后在主选项卡上就会显示PowerPivot这个插件。点击“管理数据模型”就能打开PowerPivot窗口。

接下来我们需要处理一下之前的SOFT文件，把SOFT文件用写字板打开，保存成TXT文件，并把文件名中的“.SOFT”去掉。

之后在PowerPivot窗口中，在主选项卡“开始”中选择“从其它源”导入数据。

然后选择从“文本文件”导入数据

导入之后可以看到下面这样的界面

不过，这里在导入的过程有一个小的瑕疵，由于是通过TXT文件导入的，在区分列的过程中，会有部分识别不出来，从而导致部分列的数据丢失。这里导入完了之后丢失了Gene的介绍，不过在用EXCEL打开SOFT文件时，这部分数据可以看得很清楚的。

小结：PowerPivot的功能是十分强大的，可以进行多表格的数据关联，关联数据库直接进行数据对接，以及代替EXCEL储存更多的数据。这里仅介绍了EXCEL无法承载过多行数而使用PowerPivot辅助显示的功能。

数据表头信息

后台还有不少朋友对SOFT以及GEO2R分析的差异基因的EXCEL文件里的列名提出了不少疑问，这里作一下介绍。

首先是芯片数据的SOFT文件

其实在芯片数据的文件里是有相关注释的

ID = Affymetrix Probe Set ID，这里是Affymetrix的探针ID

GB_ACC = GenBank Accession，GenBank的编号

Species Scientific Name，物种名称，Homo sapiens表示人类

Gene Title是基因名称的全称（可能没有）

Gene Symbol是基因名称的简称（可能没有），在没有Gene Symbol的情况，可以通过其它方式识别基因，在DAVID的在线分析中，其它识别方式都是可以识别基因的，包括探针ID。

ENTREZ_GENE_ID = Entrez GeneDatabase UID，Entrez基因数据库中的ID

Gene Ontology Biological Process、Gene Ontology Cellular Component、Gene Ontology Molecular Function分别代表GO分析中BP、CC、MF的GO代码。

这里的话我们主要关注的是ID和Gene Symbol。

有关GEO2R分析的差异基因EXCEL文件的列名注释

GEO2R是用了R语言中的limma包中的ebayes算法处理后的数据。
P.value是进行bayes检验的P-value。

而adjust.P.Val 是经过调整后的P-value。
t 是bayes调整后，所要比较的两组表达值进行T检验后的t 值。
B是经过bayes后得到的标准差的对数值。

logFC是指两组表达值间以2为底对数化的变化倍数。

这里最为关键的是adjust.P.Val和logFC，常用作差异基因的筛选标准，上一期中也已经提及。

下面分享一些有关EXCEL使用的小技巧。

1、标题行始终可见

在处理多列大数据的过程中，经常会出现找到数据后，却忘记数据对应的是哪一列的情况。

这里，本宫分享两个方法：

（1）利用插入“表格”

点击任一单元格，在“插入”选项卡中选择“表格”，在弹出的“创建表”对话框中勾选“表包含标题”。

创建后如下图，标题始终可见

（2）利用“冻结窗格”

在“视图”中选择“冻结窗格”中的“冻结首行”即可冻结标题行

如果要同时冻结多行多列，我们可以选择“冻结拆分窗格”功能。比如我们要同时冻结第一行和第一列，我们就把光标选中B2单元格，然后在“冻结窗格”中选择“冻结拆分窗格”。

冻结后效果如下图，左右或上下拖动时，第一列和第一行始终显示

有关“拆分窗格”还有其它的用途，大家可以自己进行尝试摸索。

2、两列数据合并成一列

有的时候我们希望把两列数据合并成一列，如果直接一个个复制粘贴处理会非常浪费时间。这里通过举一个例子给大家分享一种快捷的方法。

比如下面这样的一个两列表格，如何快速的处理成一列

首先我们选中需要处理的区域，然后按Ctrl+C，并打开剪贴板

然后在一个空的单元格的公式输入区域，利用剪贴板进行粘贴

之后，利用Ctrl+C复制公式输入区域的全部内容，在剪贴板上得到第二个材料

最后，在一个空的单元格，利用剪贴板中的数据进行粘贴，即可完成两列单元格的合并

我们来看一下效果图

3、自动生成间隔条纹

当EXCEL表格的行数过多时，长时间地查看一成不变的白色背景时，容易产生视觉疲劳，同时也不容易准确识别数据。

这里本宫分享一个创建间隔条纹的背景的小技巧。

首先在主选项卡中选择“开始”，然后选择“条件格式”，“新建规则”。

“使用公式确定要设置格式的单元格”，填写以下公式“=(Mod(Row(),2)=1)*(A1<>"")”，然后为此公式设置格式。

格式中选择“填充”，选择一种颜色，这里本宫选择了浅绿色

这样就建立好了一种颜色的条纹

之后按照相同的方法，设置另外一个颜色，公式部分改成“=(Mod(Row(),2)=0)*(A1<>"")”

这样就设置好了双色条纹，并且不会随着单元格的增减而改变。

4、用颜色条展现数值大小

最后介绍的这个技巧脑洞略大~~~

首先在主选项卡选择“开始”中的“条件格式”，然后选择“数据条”填充。

然后在“条件格式”中选择“管理规则”

选择“编辑规则”

将“格式样式”选为“数据条”，勾选仅显示数据，下面还可以根据喜好更改填充的颜色。

效果图如下

好了，今天就扯到这里了，下期再见！

That's all. Thank you!

请长按二维码识别关注“小张聊科研”。

关注后获取《科研修炼手册》1.0、2.0、3.0、基金篇精华合集。