伪题图:逼死强迫症之重新加载。下图为真题图
2400字,约6分钟,思考问题的熊 专栏6
懒是人类进步的绊脚石,偷懒是人类进步的阶梯。如果你完成任何一项工作心里时感觉复杂,想必就还有更简单的方法。
在生信技能树的微信群,时不时会看到有人问Excel相关的问题,今天我的第六篇专栏文章,就和各位一起梳理一下使用 Excel 必须要知道的基础知识和几个函数。希望对你有些许帮助。
Excel 的几个基本常识
- Excel 可以处理的数值有效位数最多为15位
- 公式中文本类型的常量必须写在半角双引号内
- 运算符包括算数运算符和比较运算符,其中比较运算符返回逻辑值
- <> 表示不等于
- 所有数据类型中,数值最小,文本大于数值,最大的是逻辑值true
- 文本运算符 & 可以将两个数据合并为一个文本类型数据
- 引用运算符包括:冒号;单个空格; 逗号。单个空格是交集运算符,而逗号是联合运算符,冒号是区域运算符
- 相对引用和绝对引用最好搭配使用,F4 可以快速切换
- 确定使用提示列表里的某个函数可以按 tab 键补全
- ctrl+shift+A 可以显示出函数所有需要的参数
SUMIF函数
用法:=SUMIF(条件区域,求和条件,求和区域)
- 如果条件区域和求和区域相同,只需要写出求和区域即可
- 条件区域的写法示例: ">100" "<>100"
- 求和条件可以是数字、文本、单元格引用格式和公式,文本必须在双引号之间
- SUMIFS 可以用来多条件求和
- AVERAGEIF 按照条件求平均值,用法和SUMIF 相同
按照模糊条件求和时可以结合通配符使用
- 写法:SUMIF(范围,“Chr*”,求和范围)
- Excel中只有两种通配符,分别是 ? 和 * ,其中*代表任意字符,?代表单一字符
COUNTIF函数
用法:=COUNTIF(单元格区域,计数条件)
- 参数可以是数字,表达式和单元格引用以及文本字符串,且可以使用比较运算符和通配符
- 示例:=COUNTIF(A2:A10,"??") 或 (A2:A10,""&B2&"")
- 统计所有非真空单元格个数,可以使用筛选条件"<>"
- COUNTBLANK 专门用来统计所有空单元格个数
- COUNTA 统计所有非真空单元格个数
- COUNT 统计所有数值单元格个数
多条件统计
- COUNTIFS(A1:A10,">=60",B1:B10,">=80")
对数据进行取舍
- ROUND(取舍数值,保留位数)
- 保留的位数可正可负可0
- 强行向上取舍,使用ROUNDUP
- 强行向下取舍,使用ROUNDDOWN
- 取整还可以用INT和TRUNC
对字符串进行操作
字符串进行合并
- Excel可以非常方便的对数据进行分列。但并没有一个合列的选项。如果合并单元格,会犯非常低级的错误。
- 使用CONCATENATE函数
- 示例:CONCATENATE(A2,A3,"任意其它字符串",A4)
- 如果觉得函数太长,可以用 &
统计文本的长度
- 长度可以用字符和字节表示,一个中文占一个字符,占两个字节
- 统计字符长度用LEN()
- 统计字节长度用LENB()
- 如果一个函数针对的是字符,那么再后面加上B往往就可以处理字节
Excel本身是不区分大小写的,可以使用函数EXACT来完成
查找字符所在的位置
- 使用 FIND 或者 SEARCH(查找的字符,查找的位置,从第几个字符开始查找)
- 只有FIND 区分大小写,只有SEARCH可以使用通配符,如果想把通配符当作普通字符来处理,需要再*前面加~
截取字符
- 可左可右可中间
- 左边 LEFT(要处理的字符串,要提取的字符个数)
- 右边 RIGHT 同理
- 中间:MID(要处理的字符串, 从第几个字符开始提取,提取几个字符)
替换字符:类似与linux中的sed,一个是SUBSTITUTE,另一个是REPLACE
- 用法:SUBSTITUTE(需要替换字符的文本或者单元格引用, 需要替换的文本,需要替换成什么,替换第几次出现的字符)
- 用来替换指定文本的,如果不知道具体文本,只是想在指定位置进行替换要使用下面的函数
- REPLACE(需要替换字符的文本或者指定的单元格, 要替换的起始位置,替换字符的个数,要替换成什么)
- 例如:给电话号码中间四位加星号 =REPLACE(A1,4,4,"****")
常用的查找函数
VLOOKUP 最常用函数,具体的用法就是(你找啥,在哪找,要找对应的那一列,精确查找还是模糊查找)
需要注意
- 第一个参数可以使用通配符进行模糊匹配
- 查找区域中匹配的内容必须位于第一列
- 有多个对应值只会返回第一个值
- 0/FALSE 表示精确匹配,excel 里的说明有问题
在平时的实际应用中,有一个问题曾经困扰了我很久。就是怎么同时返回多列对应的数值。
- 这通过对第一第二个参数使用绝对引用,对第三个参数使用相对应用,利用COLUMN 函数。
- =VLOOKUP($E2,$A$2:$D$100,COLUMN(B:B),0)
MATCH 可以在某一个范围内搜索特定的项
- MATCH(要查找的内容,搜索的区域,匹配类型)
- 查找的内容可以是值,数字,单元格引用
- 查找的范围只能是一行或者一列
- 匹配类型有三种
- -1 MATCH 查找大于或等于查找值的最小值,查找范围内的值必须按降序排列
- 1 小于或者等于查找值的最大值,查找范围内的值必须按照升序排列
- 0 完全等于
- MATCH返回的是位置而非值本身,匹配文本时不区分大小写
- 同样可以配合通配符使用
INDEX 返回所在区域交叉处的位置
- INDEX(范围,行序号,列序号)
- 将 INDEX 和 MATCH 连用可以解决 VLOOKUP 未解之谜
- 逆向搜索:=INDEX($A$2:$C$33,MATCH("P450",$A$2:$A$33,0),3)