使用Excel进行科研数据预处理

下面将以粒度仪测定的粒径、96孔板测定的吸光值、分光光度计测定的吸光值为处理对象，介绍处理流程，包括

1. 转置（将一大排横着的数据变成竖着便于画图）

2. t检验 对于N个重复实验按照自由度α进行检验，排除错误值

3. Excel散点图的设置，平滑曲线、拟合曲线

本文操作逻辑

①利用转置等操作将数据排整齐

②把有效数据复制到空白的地方，IF

③计算自由度、SD（标准差）、AVG（平均数）、T值

④IF语句排除T检验错误值，条件格式设置上下界特殊颜色

⑤重新计算AVG,SD，绘制散点图

⑥散点图添加误差棒（SD），添加趋势线并美化。

*下面所有给出具体单元格的例子都是以A1或者第一个为准。

转置公式：

=TRANSPOSE(A1:B2)

条件公式：

=IF(条件，条件真时输出值，条件假时输出值)

注意到粒径（粒度仪测定）导出结果为很长的横向排列数据

按照70组数据为一个系列，…附一下粒径检测步骤吧，免得不一样。

在这里导出的文件中，

BY-EP列为Volumes；EQ-HH列为Numbers；HI-JZ列为Intensities。其中，Intensities按照散射光加权，对大颗粒敏感，Volumn按体积加权，Number按粒子数量加权，对小颗粒敏感。作图一般按照number。

按照下图为数据空出空间。

因为测一次重复三次，有时候会测两次，所以在A5:A10留出6行空间。

将不变的数据（粒径横坐标Size）复制到A14：选中区域，右键粘贴-转置。D14同样处理。

在E14输入

IF(TRANSPOSE(BY5:EP10)="","",

TRANSPOSE(BY5:EP10))

意思就是如果这个位置空着，那么让祂空着，如果有数据，就转置。（IF语句可避免空着的地方被填成0影响绘图）

如图所示。其他三处同样处理。6空行允许重复测定，多空一行average放平均数：蓝色地方给出=average，拖拽区域。

如图，绿色位置向下拖拽是Excel重复变换的基本操作，不过需要注意不变的位置需要绝对引用$，后面会提到。

现在我们把A-PN的数据复制过去，就能得到：

这里，D列是时间，E,F为Z-Average和PDI，我们同样可以提取信息

在上方KA,KB,KC,E,F是下面数据所处的列。按红框表达式提取，以免出现0

T：=T.INV.2T(α双侧，自由度)

自由度=组数-1

上面的三组数据是同一个样本连续测定3次，未知总体平均，采用T检验；未知处理对样本影响，采用双侧T检验。

我们需要知道：样本平均、方差、T值

T值查表如图：

上面左侧灰色/橙色为参考表，而Excel可给出T（双侧），所以设置一个便捷操作盒。

公式如上设置，F99是自由度df，

F99=F98-1，F98为组数（手动输入）

α双侧也需要手动输入，一般0.1,0.05；看着办

左下方黄框没有公式，按照颜色对应复制上面数据表即可，因为当你只有Z-Average等数据，没有前面的表格时，就可以直接在这里操作。

右侧栏目较为复杂，下面分开解释。

=STDEV.S(区间)：区间数据的标准差

=SQRT(单元格)：单元格的平方根

I88-N93为A108-F103的复制，用IF(单元格="","",单元格)，以免取到0.

按照T检验公式，我们需要先计算S

S用SD表示，SD也是误差棒的取值

df=F99，之前快捷操作卡的df

根号n是=SQRT(F98)

随后依次计算t*s/根号n。

上侧临界值是AVG+t*s/√n，即=J95+J102

下侧为相减。

随后我们用IF语句判定是否在上下侧临界值之间（虽然更严谨的是用样本估计总体而不是用总体估计样本区间，不过简化操作来看，两种方法结果一致。）

IF的条件只能给一个，A>A1>B是错误的写法，需要用A>A1,A1>B。AND语句可以实现。

在这里，TRUE表示落在区间内，可采纳，0表示舍去，在红色单元格中显示保留的值。（有数据为TRUE,0=FALSE。）

当然这可以简化为：IF(AND(条件)，数据，"")一步到位。

下面粉色的分别是删减后区域的平均数和标准差。

用粉色数据绘制散点图：

新建散点图。右键-选择数据

点击添加/如有数据则点编辑

点击箭头可勾选/输入/修改单元格位置

此处左侧蓝色勾选若取消，则不显示该曲线。

一般随便拖一下，例如

=Sheet1!$B$14:$B$16，然后把16改成83（该列末尾。）注意需要点击确定，不然画图依旧是16，需要检查。

先点击曲线/散点，可以对曲线/散点进行编辑，例如平滑线等

如此勾选

选择平滑线进行平滑连接

点击坐标轴，可以对坐标轴进行修改

可以隐藏散点只保留曲线

例如使用对数刻度

如果你有不同时间测定的粒度，下面也提供了折线图（依旧是二维散点，不过不勾选“平滑”。）

这里可以选择主要和次要坐标轴：选择数据，右侧可以看到

如图选择：勾选误差线后可以看到误差棒

delete删掉水平误差棒，点击垂直误差棒，指定值

正负误差值都选择SD对应值

最后在坐标轴选项给Y轴加粗上色、设置主要刻度线、次要均在内部、调整范围即可。

可以点击+,选择图例添加图例

96孔板测定数据一般连续测三次，因为第一次可能不太准。所以预留了一部分空间。

数据形式如下：

为了方便观察，边界用深灰色表示，到时候把数据搬到上方的空间内

因为做实验时总有一些孔是空的/PBS，以免96孔板边缘气体交换过快导致细胞异常死亡，所以我们需要框选有用的数据。但实际上，我们可以给第一列取平均值，*1.5倍，小于它的认为是空孔。

在这里，只有红框中的数据是采纳的，

AB2为AVERAGE（96孔板第一列）

=IF(B12>1.5*$AB$2,B12-$AD$3,"")

-$AD$3是因为有的实验要求减去背景（培养基），数据可以之后再填。例如11列是空白，可以算完了再把11列的AVG复制数值填过去。

意思是，当数据大于1.5空白时，保留。绝对引用保证在拖拉时不变。

那么df也需要随机应变一些，使用

=IF(COUNT(T12:T19)=0,"",

COUNT(T12:T19)-1)

COUNT计数数据个数，如果是0，输出空，不是0，输出n-1

s/√n

=T21/SQRT(T22+1)，

T22就是df，T21是SD（STDEV.S）

同样计算上下侧临界值。

这里介绍如何让大于上侧的变红。

选择一列数据：

设置条件格式，如图，设置填充。确认。

如果设置错了，在条件格式这里清除。不建议拖拽进行重复操作，会出问题，因为下方为绝对引用，修改了相对引用也有问题，建议一列一列拉。

同理，小于下侧临界值为蓝色。

在下面设计一块区域，作为制图需要的数据，

右边蓝色/下图蓝色表格中是数据的变换，即大部分实验，例如MTT实验，需要绘制的是细胞活性，细胞活性为该样本/对照平均*100%；蓝色部分正是这样处理

橙黄色区域需要选择你的对照平均数。

平均数：=IF(AI43="","",100*U44/$AF$45)

SD：=IF(AI44="","",100*U45/$AF$45)

（因为方差AS^2 x+C=A^2 S^2 x,标准差可以等比例变换）

用于绘图的数据：

=IF(U42="",NA(),U42)

这里不再使用""，而是用NA()，因为空值也可以作为横坐标值，会导致横坐标被识别为文本，散点图横坐标会变成默认的1,2,3……

按照之前的方法绘制散点图，选中数据-右键-添加趋势线。

选中趋势线，右键-设置趋势线格式

选择线性或多项式

柱状图与散点图选取数值及误差棒添加方法与之前相同，上面的图存在明显异常数据，在下面设置一个相同的无格式处理，把上面的数据复制过去，删除异常数据。