数据处理中常用的Excel基本操作及函数-生信基础版
文章目录
- 数据处理中常用的Excel基本操作及函数-生信基础版
- 第一部分:键盘快捷键操作
- 一、CTRL系列
- 二、其他系列
- 第二部分:Excel函数
- 一、清洗处理类
- 1. 非函数类:
- 1、数据拆分
- 2、去除重复数据
- 3、 筛选
- 4、 排序
- 2.函数类
- 1、 Trim:清除字符串空格
- 2、 CONCAT/CONCATENATE函数:连接单元格内的内容
- 3、 LEFT/RIGHT/Mid函数:**截取左边/中间/右边字符串**
- 4、 Replace/Substitute:替换单元格中内容
- 5、 LEN/LENB 长度计算
- 6、Find/Search:查找文本在单元格中的位置
- 7、Upper/Lower/Proper:文本转大写/转小写函数/首字母大写
- 二、关联匹配类
- 1、引用
- 2、VLOOKUP函数
- 3、Index:返回值或值的引用
- 4、Match:返回指定内容在指定区域的位置
- 5、Row/Colum:返回单元格在第几行(在第几列)
- 6、Rows/Colums:返回引用或数组的行数(列数)
- 三、逻辑运算类
- 1、IF/IFS:如果但是
- 2、And / Or:并/或
- 3、IS系列
- 四、计算统计类:以描述性统计为准
- 1、Sum/Sumif/Sumifs 统计满足条件的单元格总和
- 2、Max/Min:返回最大最小值
- 3、Average/Averageif/Averageifs 统计满足条件的单元格平均值
- 4、Count/Countif/Countifs 统计满足条件的字符串个数
- 5、Stdev:求标准差
- 6、INT/MOD:取整/取余函数
- 7、CONFIDENCE:返回可信区间
- 8、CORREL:计算相关系数
- 9、PEARSON:计算Pearson乘积矩相关系数 r
- 10、TTEST:t 检验
- 11、PERCENTILE
第一部分:键盘快捷键操作
一、CTRL系列
-
Ctrl+H /F显示“查找和替换”对话框。
-
Ctrl+D 填充选中单元格,可以不连续,不连续,不连续
-
Ctrl+↓(↑→←):跳转到本工作表最后一行有数据的区域(注:非常有用)
-
Ctrl+shift+↓(↑ → ←) :选中当前行到本工作表最后一行有数据的区域(注:非常有用)。
-
Ctrl+B加粗单元格字体
-
Ctrl+C 复制单元格区域内容
-
Ctrl+Insert 复制单元格区域内容
-
Ctrl+X 剪切单元格区域内容
-
Ctrl+V 粘贴单元格内容
-
Ctrl+Z 撤销操作
二、其他系列
- Alt+Enter :单元格内换行
- Tab 跳转下列
第二部分:Excel函数
一、清洗处理类
数据处理之前,需要对提取的数据进行初步清洗,如清除字符串空格,合并单元格、替换、截取字符串、查找字符串出现的位置等。
1. 非函数类:
1、数据拆分
【工具栏】-【数据】-【分列】-【分割符号】/【固定宽度】
分隔符号分列即识别字段中含有的分隔符号。
Tab键 (制表符) 、分号、逗号、空格、其他 [ 自定义分割符号]
2、去除重复数据
【工具栏】-【数据】-【去除重复项】
3、 筛选
数字筛选
文本筛选
4、 排序
升序
降序
2.函数类
-
清除字符串前后空格:使用Trim
-
合并单元格:使用concat ,&
-
截取字符串:使用Left/Right/Mid
-
替换单元格中内容:Replace/Substitute
-
查找文本在单元格中的位置:Find/Search
-
获取字符长度:Len/Lenb
1、 Trim:清除字符串空格
用途:主要用于把单元格内容前后的空格去掉,但并不去除字符之间的空格。
语法:=TRIM(单元格text)
参数:单元格内的需要清除其中空格的文本。
实例:
A1 | =TRIM(A1) |
---|---|
Hello world | Hello world |
2、 CONCAT/CONCATENATE函数:连接单元格内的内容
用途:将几个文本字符串合并为一个文本字符串
语法:=CONCATENATE(字符串1,字符串2,…)
参数:字符串1,字符串2,…: 需要合并的第1、 2、… 、N个文本项(N<=30),这些文本项可以文本字符串、数字或单个单元格的引用.注意:在将数字和文本合并到一个单元格中时,数字将转换成文本,而不再用做数字,也就是说,无法再对其进行任何数字运算
实例:
A1 | B1 | C1 | =CONCATENATE(A1,B1,C1) | =A1&" “B1&” "&C1 |
---|---|---|---|---|
Hello | world | ! | Heloworld! | Hello world ! |
3、 LEFT/RIGHT/Mid函数:截取左边/中间/右边字符串
语法与用途:
LEFT(指定字符串,截取长度): 得到字符串左部指定个数的字符
RIGHT(指定字符串,截取长度): 得到字符串右部指定个数的字符
Mid(指定字符串,开始位置,截取长度):得到字符串中间指定个数的字符。
实例:
A1 | =LEFT(A1,5) | =MID(A1,2,7) | =RIGHT(A1,5) |
---|---|---|---|
Hello world | Hello | ello wo | world |
A2(氨基酸序列) | =LEFT(A2,3) | =MID(A1,6,2) | =RIGHT(A2,1) |
CAFGTCGGAKLVF | CAF | CG | F |
4、 Replace/Substitute:替换单元格中内容
- Replace
用途:替换掉单元格的字符串
语法:=REPLACE(old_text,start_num,num_chars,new_text))
参数:Old_text 是要替换其部分字符的文本。 Start_num 是要用 new_text 替换的 old_text 中字符的位置。
Num_chars 是希望 REPLACE 使用 new_text 替换 old_text 中字符的个数。New_text 将替换 old_text 中字符的文本。
实例:
A1 | =REPLACE(A1,1, 5,“Hi”) |
---|---|
Hello world | Hi world |
2.Substitute
用途:Substitute根据文本内容替换,需要提供替换的旧文本和新文本,以及替换第几个旧文本等。因此Replace实现固定位置的文本替换,Substitute实现固定文本替换。
语法:SUBSTITUTE(text,old_text,new_text,instance_num)
参数:Text 为需要替换其中字符的文本,或对含有文本的单元格的引用。 Old_text 为需要替换的旧文本。 New_text 用于替换 old_text 的文本。 Instance_num 为一数值,用来指定以 new_text 替换第几次出现的 old_text,默认条件下为全部替换。
实例:
A1 | =SUBSTITUTE(A1,“Hello”, “Hi”) | =SUBSTITUTE(A1,“Hello”, “Hi”,2) |
---|---|---|
Hello world, Hello! | Hi world ,Hi ! | Hello world, Hi ! |
5、 LEN/LENB 长度计算
LEN
用途:返回字符串的字符数
语法:=LEN(字符串)
参数:字符串是指包含数字、字母、符号等的一串字符。
实例1:
A1 | =LEN(A1) | |
---|---|---|
ACTAGTAGTCGGTCA | 15 |
实例2:
计算DNA序列中GC的含量
A1 | 公式 | 结果 | 说明 |
---|---|---|---|
ACTAGTAGTCGGTCA | B1=LEN(A1) | 15 | 计算序列总长 |
B2=SUBSTITUTE(A1,“G”, “”) | ACTATATCTCA | 将G替换为空 | |
B3=LEN(SUBSTITUTE(A1,“G”, “”)) | 11 | 计算替换后的序列总长 | |
B4=LEN(A1)-LEN(LEN(SUBSTITUTE(A1,“G”, “”))) | 4 | 计算得到G的个数 | |
同理 | B5=LEN(A1)-LEN(LEN(SUBSTITUTE(A1,“C”, “”))) | 3 | 计算得到C的个数 |
B6=(B4+B5)/(B1) | =100*(4+3)/15 | 计算GC含量 |
LENB:
用途:返回字符串的字节数
语法:=LEN(字符串)
参数:字符串为要查找其长度的文本。空格将作为字符进行计数。len是按字符数计算的、lenb是按字节数计算的。数字、字母、英文、标点符号(半角状态)都是按1计算的,汉字、全角状态下的标点符号,每个字符按2计算。
A1 | =LENB(A1) | =LEN(A1) | =LEFT(A1,LENB(A1)-LEN(A1)) |
---|---|---|---|
张三(20201101) | 14 | 12 | 张三 |
6、Find/Search:查找文本在单元格中的位置
语法与参数:
=FIND(要查找字符,指定字符串,第几个字符)
=SEARCH(要查找字符,指定字符串,第几个字符)
可与Left/Right/Mid结合能完成简单的文本提取。Search和Find类似,区别是Search大小写不敏感,支持通配符(包括问号 (?) 和星号 (*))。问号匹配任意单个字符;星号匹配任意一串字符。如果要查找实际的问号或星号,请在该字符前键入波形符 (~)。
实例:
A | B | C | |
---|---|---|---|
1 | Hello#World-2020 | Hi#World-2021 | |
2 | 公式 | 结果 | 说明 |
=FIND(" #",A1,1) | 6 | 从文本中查找#所在位置 | |
=MID(A2,1**,FIND(" #",A1,1)**-1) | Hello | 字符串中提取从位置 1 到位置 # 的文本 | |
=SEARCH(“H*1”,C1) | 1 | 从文本中查找“H[匹配任意字符串]1”的文本,并返回H所在位置。 | |
=SEARCH(“i?W”,C1) | 2 | 从文本中查找含有”i(任一字符)W“的文本,并返回i所在位置。 |
7、Upper/Lower/Proper:文本转大写/转小写函数/首字母大写
语法与参数:
=UPPER(字符串)
=LOWER(字符串)
=PROPER(字符串)
实例:
A | 公式 | 结果 | |
---|---|---|---|
1 | TAGCtagc | =UPPER(A1) | ATGCATGC |
2 | TAGCATGC | =LOWER(A2) | tagctagc |
3 | CD1 | =PROPER(A3) | Cd1 |
二、关联匹配类
1、引用
- 以A1为例,说说规则
- A1—相对引用,拉动时,行变列也变
- $A$1—绝对引用,拉动时,行不变列也不变
- $A1—混合引用,拉动时,行变列不变
- A$1—混合引用,拉动时,行不变列变
A | B | C | D1=A1 往下往右拉 | 相对引用 | D1=$A$1 往下往右拉 | 绝对引用 | |
---|---|---|---|---|---|---|---|
1 | Gene | FC2 | Gene(D1=A1) | FC2(E1=B1) | Gene(D1=$A$1) | Gene(E1=$A$1) | |
2 | CD1 | 1 | CD1(D2=A2) | 1(E2=B2) | Gene(D2=$A$1) | Gene(E2=$A$1) | |
3 | CD2 | -1 | CD2(D3=A3) | -1(E3=B3) | Gene(D3=$A$1) | Gene(E3=$A$1) | |
4 | D1=$A1往下往右拉 | 行变列不变 | D1=A$1往下往右拉 | 列变行不变 | |||
5 | Gene(D1=$A1) | Gene(E1=$A1) | Gene(D1=A$1) | FC2 (E1=B$1) | |||
6 | CD1(D2=$A2) | CD1 (E2=$A2) | Gene(D2=A$1) | FC2(E2=B$1) | |||
7 | CD2(D3=$A3) | CD2 ( E3=$A3) | Gene(D3=A$1) | FC2(E3=B$1) |
2、VLOOKUP函数
用途:在表格的首列查找指定的数据,并返回指定的数据所在行中的指定列处的单元格内容内容
语法:
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
=VLOOKUP(查找的值, 哪里找, 找哪个位置的值, 精准匹配0/模糊匹配1)
参数:
- lookup_value: 要在表格或区域的第一列中查找的值,其参数可以是值或引用
- table_array: 包含数据的单元格区域,可以使用绝对区域(如:A2:D8)或区域名称的引用。 table_array第一列中的值是由lookup_value搜索的值。这些值可以是文本、数字或逻辑值
- col_index_num: 希望返回的匹配的列序号,其参数为1时,返回table_array第一列中的值,以此类推
- range_lookup: 近似匹配(1)还是精确匹配(0),一般情况选0
实例:
A | B | C | D | E | |
---|---|---|---|---|---|
1 | Gene name | WT | KO | Lookgene | 结果(备注:公式) |
2 | CD1 | 6 | 24 | CD2 | 6 备注 E2=VLOOKUP(D2,A2:C4,2,0),返回WT列 |
3 | CD2 | 36 | 9 | CD3 | 9 备注 E3=VLOOKUP(D3,A2:C4,3,0),返回KO列 |
4 | CD3 | 45 | 45 |
3、Index:返回值或值的引用
用途:
语法: =INDEX(array, row_num, [column_num])
参数: =INDEX(查找的区域,区域内第几行,区域内第几列)
A | B | C | |||
---|---|---|---|---|---|
1 | Gene | WT | KO | 公式 | =INDEX(A1:C4,3,2) |
2 | CD1 | 6 | 24 | 结果 | 36 |
3 | CD2 | 36 | 9 | 说明 | 返回到表格第三行第二列 |
4 | CD3 | 45 | 45 |
4、Match:返回指定内容在指定区域的位置
用途:
语法:MATCH(lookup_value,lookup_array,match_type)
参数:=MATCH(查找指定的值,查找所在区域,匹配类型)。
和Lookup类似,但是可以按照指定方式查找,匹配类型有三个值,0表示精确查找,1表示小于,-1表示大于,返回值所在的位置
A | B | C | |||
---|---|---|---|---|---|
1 | Gene | WT | KO | 公式 | =MATCH(“CD2",A1:A4,0) |
2 | CD1 | 6 | 24 | 结果 | 3 备注,查找CD2,返回值为第三行 |
3 | CD2 | 36 | 9 | 公式 | =INDEX(A1:C4,MATCH(“CD2",A1:A4,0),2) |
4 | CD3 | 45 | 45 | 结果 | 36 备注:查找CD2这一行,第2列的数据 |
5、Row/Colum:返回单元格在第几行(在第几列)
语法与参数:
=ROW(单元格)
=COLUMN(单元格)
6、Rows/Colums:返回引用或数组的行数(列数)
语法与参数:
=ROWS(数组)
=COLUMNS(数组)
三、逻辑运算类
1、IF/IFS:如果但是
IF:条件判断
用途:使用逻辑函数 IF函数时,如果条件为真,该函数将返回一个值;如果条件为假,函数将返回另一个值。
语法=IF(logical_test, value_if_true, [value_if_false])
参数:
logical_test:必需,要测试的条件。
value_if_true:必需,logical_test 的结果为 TRUE 时,期望返回的值。
value_if_false:可选,logical_test 的结果为 FALSE 时,期望返回的值。
IFS:多条件判断
用途:IFS 函数检查是否满足一个或多个条件,且是否返回与第一个 TRUE 条件对应的值。IFS 可以取代多个嵌套 IF 语句,并且可通过多个条件更轻松地读取。
语法:=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…)
参数 =IFS(条件1,第一个true时返回值,条件2,第二个true时返回值,条件3,第三个true时返回值…)
实例:判断基因在WT到KO 组中表达变化情况
A | B | C | |||
---|---|---|---|---|---|
1 | gene | WT | KO | 公式 | =IF(C2>B2,“上调‘,IF(C2=B2,"不变”,“下调”)) |
2 | CD1 | 24 | 6 | 结果 | 下调((判断CD1在KOvsWT的表达情况) |
3 | CD2 | 9 | 36 | 公式 | =IFS(C3>B3,“Up”,C3=B3,“Eque”,C3<B3,“Down”) |
4 | CD3 | 54 | 54 | 公式 | UP (判断CD2在KOvsWT的表达情况) |
2、And / Or:并/或
语法:
AND全部参数为True,则返回True,经常用于多条件判断
OR只要参数有一个True,则返回Ture,经常用于多条件判断
实例:求RNA-seq数据基因表达显著差异的基因
1.确定表达显著差异的条件为FC2>1(或FC2<-1),且FDR<0.05。
A | B | C | |||
---|---|---|---|---|---|
1 | Gene | FC2 | FDR | 公式 | 结果 |
2 | CD1 | -2 | 0.01 | =IF(OR(B2<-1,B2>1),"差异’’,“无差异”) | 差异 |
3 | CD2 | 2 | 0.07 | =IF(AND(ABS(B3)>1,C3<0.05),“显著差异”,“无显著差异”) | 无显著差异 |
4 | CD3 | 0 | 0 | =IF(AND(B4>1,C4<0.05),“UP”,IF(AND(B4<-1,C4<0.05,“Down”,“NS”)) | NS |
3、IS系列
常用判断检验,返回的都是布尔数值True和False。常用ISERR,ISERROR,ISNA,ISTEXT,可以和IF嵌套使用。
四、计算统计类:以描述性统计为准
1、Sum/Sumif/Sumifs 统计满足条件的单元格总和
语法与参数:
=SUM(求和区域)
=SUMIF(条件区域,求和条件,实际求和区域)
=SUMIFS(实际求和区域,第一个条件区域,第一个对应的求和条件,第二个条件区域,第二个对应的求和条件,第N个条件区域,第N个对应的求和条件)
实例:计算TCR-seq数据中V-J 组合数量
A | B | C | D | 公式 | =SUM(D2:D4) | |
---|---|---|---|---|---|---|
1 | Clone ID | V gene | J gene | Count | 结果 | 70 (计算count总数) |
2 | Clone1 | V1 | J1 | 25 | 公式 | =SUMIF(B:B,“J2”,D:D) |
3 | Clone2 | V2 | J2 | 20 | 结果 | 35 (计算含有J2的克隆counts总数) |
4 | Clone3 | V3 | J2 | 15 | 公式 | =SUMIFS(D:D,B:B,$B 2 , C : C , 2,C:C, 2,C:C,C$2) |
5 | Clone4 | V1 | J1 | 10 | 结果 | 35 (计算含有V1,J1的克隆counts总数) |
扩展思考,如何计算任意V-J组合的count分布?(熟悉掌握混合引用的原则)
公式 | A | B | C |
---|---|---|---|
6 | J1 | J2 | |
7 | V1 | =SUMIFS (D2:D5,B2:B5,$A1,C2:C5,B$6) | =SUMIFS (D2:D5,B2:B5,$A1,C2:C5,C$6) |
8 | V2 | =SUMIFS (D2:D5,B2:B5,$A2,C2:C5,B$6) | =SUMIFS (D2:D5,B2:B5,$A2,C2:C5,C$6) |
9 | V3 | =SUMIFS (D2:D5,B2:B5,$A3,C2:C5,B$6) | =SUMIFS (D2:D5,B2:B5,$A3,C2:C5,C$6) |
结果 | J1 | J2 | |
V1 | 25 | 0 | |
V3 | 0 | 15 | |
V2 | 0 | 25 |
2、Max/Min:返回最大最小值
语法与参数
=MAX(指定区域)
=MIN(指定区域)
3、Average/Averageif/Averageifs 统计满足条件的单元格平均值
语法与参数:
=AVERAGE(求平均值范围)
=AVERAGEIF(条件区,条件,平均值区域)
=AVERAGEIFS(求平均值范围, 条件范围1, 条件1, [条件范围2, 条件2], ...)
4、Count/Countif/Countifs 统计满足条件的字符串个数
语法与参数:
=COUNT(区域):计算纯数字的单元格的个数
=COUNTIF(条件区域,条件)
=COUNTIFS(条件匹配查询区域1,条件1,条件匹配查询区域2,条件2,以此类推......)
实例:
A | B | C | 公式 | =COUNT(A:A)-1 | |
---|---|---|---|---|---|
1 | ID | V | J | 结果 | 4(计算克隆子总数) |
2 | Clone1 | V1 | J1 | 公式 | =COUNTIF(B:B,“V1”) |
3 | Clone2 | V2 | J2 | 结果 | =2(计算含有V1的克隆子数) |
4 | Clone3 | V3 | J2 | 公式 | =CONTIF(B:B,$B 2 , C : C , 2,C:C, 2,C:C,C$2) |
5 | Clone4 | V1 | J1 | 结果 | =2(计算含有V1,J1的克隆子数) |
5、Stdev:求标准差
语法与参数:=STDEV(序列)
综合实例:数据标准化
z-score 标准化(正态标准化)是基于原始数据的**均值(mean)和标准差(standard deviation)**进行数据的标准化。公式如下。
z
=
(
x
−
m
e
a
n
(
x
)
)
/
s
t
d
(
x
)
z=(x-mean(x))/std(x)
z=(x−mean(x))/std(x)
应用场景:RNA-seq数据中多组数据不同基因的表达热图?
A | B | C | D | E | F | G | H | I | J | |
---|---|---|---|---|---|---|---|---|---|---|
1 | Gene | WT-1 | WT-2 | WT-3 | KO-1 | KO-2 | KO-3 | Mean | Std | WT-1 |
2 | CD1 | 22 | 26 | 24 | 4 | 6 | 8 | =AVERAGE(B2:G2) | =STDEV(B2:G2) | =(B2-H2)/I2 |
3 | CD27 | 220 | 260 | 240 | 40 | 60 | 80 | =AVERAGE(B3:G3) | =STDEV(B3:G3) | |
思考:在公式中如何引入"$"引用模式,,实现数据的自动填充。
6、INT/MOD:取整/取余函数
语法与参数
=INT(数值)
=MOD(被除数,除数)
实例
INT(3.14)=3
INT(-3.14)=-4
MOD(5,3)=2(5÷3=1,余数为2)
扩展实例:颜色RGB十进制记法转换为十六进制记法
A | B | C | ||
---|---|---|---|---|
1 | R | G | B | |
2 | 247 | 252 | 185 |
引入概念:
颜色RGB记法转化为HEX(十六进制记法),十六进制的语法为#RRGGBB,其中第一个R/G/B数值为原RGB每个数值除以16之后的整数部分,第二个R/G/B数值为原RGB每个数值除以16之后的余数部分。由于RGB的数值均在255以内,因此,整数部分与余数部分均小于16。在颜色十六进制记法中,将10-15分别赋值为A(10)、B(11)、C(12)、D(13)、E(14)、F(15),因此255的16进制形式为FF。
255
÷
16
=
15
(
整
数
部
分
)
…
…
15
(
余
数
部
分
)
255÷16=15(整数部分)……15(余数部分)
255÷16=15(整数部分)……15(余数部分)
第一步:利用INT、MOD函数转换RGB十进制数值为16进制数值。
A | B | C | D(取整数) | E(取余数) | |||||
---|---|---|---|---|---|---|---|---|---|
1 | R | G | B | =INT(A2/16) | =MOD(A2,16) | G1 | G2 | B1 | B2 |
2 | 247 | 252 | 185 | 15 | 7 | 15 | 12 | 11 | 9 |
第二步:利用IFS函数将10-15数值转换为A-F字符。
D | E | F | G | H | I | IFS函数 | ||
---|---|---|---|---|---|---|---|---|
1 | R1 | R2 | G1 | G2 | B1 | B2 | R1 | R2 |
2 | 15 | 7 | 15 | 12 | 11 | 9 | =IFS(D2<10,D2,D2=10,“A”,D2=11,“B”,D2=12,“C”,D2=13,“D”,D2=14,“E”,D2=15,“F”) | |
第三步:利用CONCAT函数合并字符串
A | B | C | D | E | F | G | H | I | 公式 | |
---|---|---|---|---|---|---|---|---|---|---|
1 | R | G | B | R1 | R2 | G1 | G2 | B1 | B2 | =“#”&CONCAT(D2:I2) |
2 | 247 | 252 | 185 | F | 7 | F | C | B | 9 | #F7FCB9 |
7、CONFIDENCE:返回可信区间
用途: 返回总体平均值的置信区间,它是样本平均值任意一侧的区域。
语法: CONFIDENCE(alpha ,standard_dev ,size)
。
参数: Alpha 是用于计算置信度 (它等于 100*(1-alpha)% ,如果 alpha 为 0.05 ,则
置信度为 95%) 的显著水平参数, Standard_dev 是数据区域的总体标准偏差, Size 为样本
容量。
实例: 假设样本取自 46 名学生的考试成绩, 他们的平均分为 60,总体标准偏差为
5 分,则平均分在下列区域内的置信度为 95% 。公式 “=CONFIDENCE(0.05 ,5,46) ”返回
1.44 ,即考试成绩为 60±1.44分。
8、CORREL:计算相关系数
用途: 返回单元格区域 array1 和 array2 之间的相关系数。它可以确定两个不同事
物之间的关系,例如检测学生的物理与数学学习成绩之间是否关联。
语法: CORREL(array1 ,array2)
参数: Array1 第一组数值单元格区域。 Array2 第二组数值单元格区域。
实例: 如果 A1=90 、A2=86 、A3=65 、A4=54 、A5=36 、B1=89 、B2=83 、B3=60 、
B4=50 、B5=32 ,则公式 “=CORREL(A1:A5 ,B1:B5) ”返回 0.998876229 ,可以看出 A、B
两列数据具有很高的相关性。
9、PEARSON:计算Pearson乘积矩相关系数 r
用途:返回 Pearson( 皮尔生 )乘积矩相关系数 r,它是一个范围在 -1.0 到 1.0 之间 (包 括-1.0 和 1.0 在内 )的无量纲指数,反映了两个数据集合之间的线性相关程度。
语法: PEARSON(array1 ,array2)
参数: Array1 为自变量集合, Array2 为因变量集合。
实例: 如果 A1=71 、A2=83 、A3=71 、A4=49 、A5=92 、A6=88 ,B1=69 、B2=80 、
B3=76 、B4=40 、B5=90 、B6=81 ,则公式 “=PEARSON(A1:A6 ,B1:B6) ”返回 0.96229628
综合扩展:分析基因在WT vs KO 条件下基因表达变化与染色质开放变化的相关性。
A | B (RNA-seq) | C (ATAC-seq) | |||
---|---|---|---|---|---|
1 | Gene | FC2 (WT vs KO) | FC2 (WT vsKO) | 公式 | =PERSON(B2:B5,C2:C5) |
2 | CD1 | -2 | -1 | 结果 | 0.99 |
3 | CD2 | 2 | 1.5 | 说明 | 基因在KO敲除后,基因表达变化与其染色质开放变化成正相关。 |
4 | CD3 | 0 | 0.5 | ||
5 | CD4 | 1 | 1 |
10、TTEST:t 检验
用途: 返回与学生氏-t 检验相关的概率。它可以判断两个样本是否来自两个具有相
同均值的总体。
语法: TTEST(array1 , array2 , tails , type)
参数: Array1 是第一个数据集, Array2 是第二个数据集, Tails 指明分布曲线的尾
数。如果tails=1 ,TTEST 函数使用单尾分布。如果tails=2 ,TTEST 函数使用双尾分布。
Type 为t 检验的类型。如果type 等于(1、2、3)检验方法( 成对、等方差双样本检验、异方
差双样本检验)
实例: 公式“=TTEST({3,4,5,8,9,1, 2,4,5}, {6,19 ,3,2,14 ,4,5,
17 , 1},2, 1) ”返回0.196016 。
11、PERCENTILE
用途: 返回数值区域的 K 百分比数值点。例如确定考试排名在 80 个百分点以上的分数。
语法: PERCENTILE(array ,k)
参数: Array 为定义相对位置的数值数组或数值区域, k 为数组中需要得到其排位
的值。
实例: 如果某次考试成绩为 A1=71 、A2=83 、A3=71 、A4=49 、A5=92 、A6=88 ,
则公式 “=PERCENTILE(A1:A6 , 0.8) ”返回 88,即考试排名要想在 80 个百分点以上,则分
数至少应当为 88 分。
参考:
一文搞定Excel函数-数据分析必备
更多推荐
数据处理中常用的Excel基本操作及函数
发布评论