Excel高级函数汇总.docx
- 文档编号:3859239
- 上传时间:2022-11-25
- 格式:DOCX
- 页数:10
- 大小:21.83KB
Excel高级函数汇总.docx
《Excel高级函数汇总.docx》由会员分享,可在线阅读,更多相关《Excel高级函数汇总.docx(10页珍藏版)》请在冰豆网上搜索。
Excel高级函数汇总
Excel高级函数汇总—大数据处理必备
最常用的统计软件非SPSS、Stata莫属了,但它们各有不足。
SPSS的数据录入比较繁琐,能完成的统计功能局限在已经设计好的“分析”模块里。
SPSS的作图功能也是不敢恭维。
Stata轻巧便捷,然而需要自己输入命令,新手上手需要一个过渡期。
以上两个软件的计算功能也称不上便捷。
其实,excel就可以承担起相当一部分计算、数据录入、数据筛选,甚至是统计、作图的功能。
利用excel强大的“函数”功能,可以实现诸如计算两个指定日期相差多少天、计数符合某条件的单元格有多少个、在复杂的病理报告中自动选出含有某个免疫组化阳性的蛋白、按特定需求查找单元格内容等任务。
excel的函数分为以下几大类,分别是计算类(如求绝对值、余数)、统计类(如求和、求标准差、t检验)、时间类、条件语句(如果…则…)、逻辑类(返回真/假,包括和/或/非/异或等)、等级排序类、计数单元格个数类、文本类(如查找、替换文本中的某字符串)、查找类(如查找某内容的单元格的位置)等。
利用这些函数的组合可以实现多种功能,且直接记录在excel表格中,无需像SPSS等软件还要转抄结果。
1.计算(针对数值)
余数=MOD
绝对值=ABS,高斯取整(向下取整)=INT
截断取整=TRUNC(数值,保留小数位数)
【保留的小数位数中,0表示保留到个位数,-1表示保留到十位数,依次类推】
四舍五入=ROUND(数值,保留小数位数)
乘方=POWER(数值,幂),开方=SQRT或=数值^(1/2)
e的乘方=EXP,自然对数=LN
对数=LOG(数值,底),10为底对数=LOG10
阶乘=FACT
随机数(≥0,<1)=RAND,随机数(任意范围)=RANDBETWEEN(较小数,较大数)
最大公约数=GCD(数值1,数值2,…),最小公倍数=LCM(数值1,数值2,…)
2.统计(针对数据区)
【“数组”用{},如{2,3,5,8;1,4,7},半角的分号表示换行,逗号表示换列】
求和(相加)=SUM,相乘=PRODUCT
求算术平均数=AVERAGE,求几何平均数=GEOMEAN,求调和平均数=HARMEAN
求方差=VAR【除以自由度n-1】,=VARP【除以样本量n】
求标准差=STDEV【除以自由度n-1】,=STDEVP【除以样本量n】
求平均差(每个数值与算术平均数的差的绝对值的算术平均数)=AVEDEV
求最大值=MAX,求最小值=MIN
求排名第n的数值是多少=LARGE(数据区,n)
【算法是把选定数值排成一排,再选出第n个数,因此10,9,9,8,7在n为3时返回9】
求排名倒数第n的数值是多少=SMALL(数据区,n)
求中位数=MEDIAN,求众数(较小的)=MODE
求四分位数=QUARTILE(数据区,k)
【k取0~4,最小值0,较小四分位数1,中位数2,较大四分位数3,最大值4】
求百分位数=PERCENTILE(数据区,k)
【k取0~1,若k不是1/(n-1)的倍数,函数使用插值法来确定第k个百分位数的值,如PERCENTILE({1,2,3,4},0.3) 为1.9】
求每个数值与均值的差的平方和=DEVSQ
求偏斜度=SKEW,求峰值=KURT
2.1.在某条件下求和
当数据区1满足某条件下求数据区2的对应区域的和:
=SUMIF(数据区1,条件,数据区2)
【条件可以是"男"、A2,数据区2必须和数据区1对应,若无数据区2默认对满足条件的数据区1求和】
3.逻辑算符
【单独使用返回TRUE/FALSE,可用于IF函数】
同时满足n个条件为真:
=AND(条件1,条件2,…)
满足其中一个条件即为真:
=OR(条件1,条件2,…)
两条件真假不同则为真(异或):
=XOR(条件1,条件2,…)
不满足条件为真:
=NOT(条件)
单元格A1与A2相同(不区分大小写):
=A1=A2
单元格A1与A2相同(区分大小写):
=EXACT(A1,A2)
4.条件语句
=IF(条件,若真则如何,若假则如何)
【返回的可以是单元格内容、文本、数值,甚至是区域】
【条件可嵌套逻辑算符】
当选定单元格或结果出现错误(ERROR)时如何=IFERROR(单元格或计算式,出现错误会如何)
【例=IFERROR(A2, "哈哈"),=IFERROR(1+A2, B3)】
【错误类型包括#N/A、#VALUE!
、#REF!
、#DIV/0!
、#NUM!
、#NAME?
或 #NULL!
】
5.等级排序
求D3数值在选定数值的排位(降序排):
=RANK(D3,数据区,0)
【重复数值返回较小排位,9在10,9,9,8,7,6中排第2】
求D3数值在选定数值的排位(升序排):
=RANK(D3,数据区,1)
【重复数值返回较小排位,9在10,9,9,8,7,6中排第4】
【数据区后为0降序排,数据区后为1升序排】
6.求各种类型的单元格的个数
【3个函数COUNT是计数数值类型的单元格个数,COUNTA是计数各种类型的单元格个数,COUNTIF是计数在某条件下的单元格个数——各种函数后的IF都是指在某条件下,如SUMIF】
【任何函数后面加“A”表示不忽略非数值型内容,如COUNTA、MAXA、MINA,文本型、真空、FALSE为0,TRUE为1】
真空单元格个数:
=COUNTIF(数据区,"=")
【数据区A:
G为A列到G列所有单元格,19:
21为19行到21行所有单元格】
非空单元格个数:
=COUNTIF(数据区,"<>")
文本型单元格个数:
=COUNTIF(数据区,"*")
非空文本型单元格个数:
=COUNTIF(数据区,">=!
")
所有单元格个数:
=COUNTIF(数据区,"<>""")【单元格含有"时公式不成立】
逻辑值为TRUE的单元格个数:
=COUNTIF(数据区,TRUE)
等于50的单元格个数:
=COUNTIF(数据区,50)
不等于50的单元格个数:
=COUNTIF(数据区,"<>50")
大于50的单元格个数:
=COUNTIF(数据区,">50")
【小于<,大于等于>=,小于等于<=】
大于50小于等于100的单元格个数:
=COUNTIF(数据区,">50")-COUNTIF(数据区,">100")
等于E5单元格数值的单元格个数:
=COUNTIF(数据区,$E$5)
大于E5单元格数值的单元格个数:
=COUNTIF(数据区,">"&$E$5)
【小于<,大于等于>=,小于等于<=】
含有两个字符的单元格个数:
=COUNTIF(数据区,"?
?
")
【1个汉字或字母算1个字符,文本型的每个数字算1个】
含有两个字符并且第2个是B的单元格个数:
=COUNTIF(数据区,"?
B")
【通配符仅用于文本型,?
代表1个字符,*代表n个字符】
【包含B—*B*,第2个是B但字符数随便—?
B*,就是B—B】
包含D3单元格内容的单元格个数:
=COUNTIF(数据区,"*"&D3&"*")
【&是连接符,$表示绝对引用,不随插入其他值引起的行号、列号变化而变化,如$E5为列固定在E, E$5为行固定在5,$E$5为固定在E5】
第2个字符起是D3单元格内容的单元格个数:
=COUNTIF(数据区,"?
"&D3&"*")
6.1.查找重复内容的单元格的个数
查找与D3重复内容的单元格的个数=COUNTIF(数据区,D3)
查找某行/列或其一部分的不重复的单元格个数:
=SUMPRODUCT(1/COUNTIF(某行/列或其一部分,该行/列或其一部分))【原理是每个不重复的单元格为1,重复n次的单元格为1/n,但出现n次,仍然为1】
6.2查找符合多个条件的单元格的个数
=COUNTIFS(数据区1,条件1,数据区2,条件2,…)
【数据区必须是对应的】
7.文本函数
【输入文本时需要键入半角双引号】
检测是否为文本格式:
=T(单元格)【若为文本格式显示原内容,若不是文本显示空白】
合并字符串:
=CONCATENATE(文本1,文本2,文本3,…)
合并某区域的字符串:
=PHONETIC(数据区1,数据区2, 数据区3,…)【区域如A1:
B5,先横向合并,再纵向合并】
【遇到数值格式、日期格式、公式则跳过不合并,因此可以事先把数值格式变为文本格式】
重复显示某文本k次:
=REPT(文本,k)
将阿拉伯数字(数值)变为文本格式罗马数字:
=ROMAN(数值,k)【由I、V、X、C等组成,非Unicode单字符】
【k取0~4/TRUE/FALSE,0为经典罗马数字,1~4为逐步简化版,4为最简版,TRUE为经典版,FALSE为最简版】
将文本中全角字符变为半角字符:
=ASC(文本)
将文本中半角字符变为全角字符:
=WIDECHAR(文本)
将大写字母变为小写字母:
=LOWER(文本)
将小写字母变为大写字母:
=UPPER(文本)
将数字转变为ASC码对应的字符(十进制):
=CHAR(数值)
显示文本中第一个字符的ASC码对应的数字:
=CODE(文本)
将数字转变为Unicode码对应的字符(十进制):
=UNICHAR(数值)
显示文本中第一个字符的Unicode码对应的数字:
=UNICODE(文本)
将首个字母变为大写字母,其余字母变为小写字母:
=PROPER(文本)
数字文本转化为数值格式:
=VALUE(文本)
数值格式转化为文本格式:
=FIXED(数值,保留小数位数,逻辑值)
【除了可以转化为文本格式外,还可以格式化输出,如四舍五入和千分符;逻辑值为TRUE,文本不出现数字千分符;逻辑值为FALSE,文本出现数字千分符】
【若只想格式化输出,不想转化为文本格式,直接右键→设置单元格格式】
显示文本的字符数:
=LEN(文本)
显示文本的字节数:
=LENB(文本)
【关于“字节”的函数都是在“字符”的函数后加“B”,如LENB、LEFTB、FINDB、REPLACEB、SEARCHB】
某字符串在文本中第一次出现时是第几个字符(区分大小写):
=FIND(某字符串,文本,从第几个字符开始查找)
【有时要查找的字符串不只出现1次,要查后面出现的字符串可以设置从第几个字符开始查找】
某字符串在文本中第一次出现时是第几个字符(不区分大小写):
=SEARCH(某字符串,文本,从第几个字符开始查找)
【search与find函数区别在于区分大小写,以及search可以使用通配符*?
】
提取文本中左起的k个字符串:
=LEFT(文本,k)
提取文本中右起的k个字符串:
=RIGHT(文本,k)
提取文本中第j个字符开始的k个字符串:
=MID(文本,j,k)
替换文本中第j个字符开始的k个字符为新字符串:
=REPLACE(文本,j,k,新字符串)
替换文本中的旧字符串为新字符串:
=SUBSTITUTE(文本,旧字符串,新字符串,k)【若旧字符串有多个,指明替换第k个】
【replace是替换指定位置的字符串,substitute是替换指定内容的字符串】
清除文本中的非打印字符:
=CLEAN(文本)
【非打印字符主要是ASCII码00~31,分别是标题开始、正文开始、正文结束、传输结束、请求、收到通知、响铃、退格、水平制表符、换行键、垂直制表符、换页键、回车键、不用切换、启用切换、数据链路转义、设备控制1、设备控制2、设备控制3、设备控制4、拒绝接收、同步空闲、结束传输块、取消、媒介结束、代替、换码(溢出)、文件分隔符、分组符、记录分隔符、单元分隔符】
清除文本中所有空格(单词间留1个):
=TRIM(文本)【空格的ASCII码为32】
8.查找内容
【使用查找或按行、按列查找功能可以实现不同工作表的数据的联动,相当于使用Acess数据库!
】
【使用LOOKUP、VLOOKUP、HLOOKUP必须要升序排列!
】
【查找类的函数都可以使用通配符】
在某行/列或其一部分查找内容,返回与“第一个找到的单元格”对应的另一区域的单元格内容:
=LOOKUP(需查内容,某行/列或其一部分,对应的行/列或其一部分)
【需查内容:
可以是单元格、文本、数值】
【若数值不按升序排列,会以二分法查找数值,具体见此网页】
【LOOKUP只能模糊查找,VLOOKUP、HLOOKUP可以模糊查找、精确查找】
8.1按列查找内容
在某区域查找内容,返回与“第一个找到的单元格”相同行,且指定列的单元格内容:
=VLOOKUP(需查内容,数据区,需返回的数据区的行数,k)
【虽然是数据区,其实只是查找第一列的内容而已】
【需查内容:
可以是单元格、文本、数值】
【需返回的数据区的行数:
指被查找的数据区的第几行】
【k为0/FALSE时精确查找(完全一致),k为1/TRUE时模糊查找(包含即可)】
【模糊查找数值有个“隐藏功能”,若没有完全一样的可以返回比该数值小且相差最小的数,但要求第一列升序排列】
8.2按行查找内容
在某区域查找内容,返回与“第一个找到的单元格”相同列,且指定行的单元格内容
=HLOOKUP(需查内容,数据区,需返回的数据区的列数,k)
【虽然是数据区,其实只是查找第一行的内容而已】
【需查内容:
可以是单元格、文本、数值】
【需返回的数据区的行数:
指被查找的数据区的第几行】
【k为0/FALSE时精确查找(完全一致),k为1/TRUE时模糊查找(包含即可)】
【模糊查找数值有个“隐藏功能”,若没有完全一样的可以返回比该数值小且相差最小的数,但要求第一列升序排列】
8.3查找相对位置
在某行/列或其一部分查找内容,返回“第一个找到的单元格”在该区域的第几个:
=MATCH(需查内容,某行/列或其一部分,k)
【k为0精确查找,对查找区域的数值排列无要求;k为1查找小于该数值的最大值,要求升序排列;k为-1查找大于该数值的最小数,要求降序排列】
【需查内容:
可以是单元格、文本、数值】
【若无查找内容则返回“错误”】
8.4查找绝对位置
某单元格的行号:
=ROW(单元格)
【若空则返回公式所在单元格,若是区域则返回该区域第一行所在单元格】
某区域的行数:
=ROWS(数据区)
【{1,2,3;4,5,6}有一个;,表明有2行】
某单元格的列号:
=COLUMN(单元格)
【若空则返回公式所在单元格,若是区域则返回该区域第一列所在单元格】
某区域的行数:
=COLUMNS(数据区)
【{1,2,3;4,5,6}有2个,,表明有3行】
返回单元格的位置:
ADDRESS(行号,列号,k,m,外部数据表名)
【k为1或省略时绝对引用,k为2时仅绝对引用行号,k为3时仅绝对引用列号,k为4时相对引用】
【m为1/TRUE/省略时显示为A1样式,m为0/FALSE时显示为R1C1样式】
【可用于其他函数的“单元格”,查找各种单元格的位置,如最小值的位置】
8.5定位函数
【定位函数可与查找位置函数结合使用】
在某区域的第j行第k列的单元格的内容:
=INDEX(数据区,j,k)
在多个区域中第n个区域的第j行第k列的单元格的内容:
=INDEX((数据区1,数据区2,数据区3,…),j,k,n)
与某单元格距离j行k列的单元格的内容:
=OFFSET(单元格,j,k)
【j、k取正数时为向下、向右】
生成以某单元格为起点的数据区:
OFFSET(单元格,j,k,m,n)
【起点为与该单元格距离j行k列的单元格,区域有m行、n列】
定位第k个单元格的内容:
=CHOOSE(k,单元格1,单元格2,单元格3,…)
【常与IF、余数MOD函数嵌套】
选择第k个数据区:
CHOOSE(k,数据区1,数据区2,数据区3,…)
转置数据区:
TRANSPOSE(数据区)【即每个单元格的相对行号、列号互换】
连续数据区的个数:
=AREAS((数据区1,数据区2,数据区3,…))
【需要两个括号,因为此函数只能输一个数据区,需要一个括号把各数据区合并起来】
8.6二次定位函数
INDIRECT函数:
假设A10单元格内容为B2,B2单元格内容为11
=INDIRECT(A10):
返回A10内容所示单元格的内容,即11,相当于二次引用
=INDIRECT(“A10”):
返回A10内容,即B2,相当于一次引用
=INDITECT(“A”&B2):
返回A11内容
【可用于引用有规律的行/列数的数值,免于使用Word替换功能】
8.7总结
返回单元格内容:
LOOKUP、INDEX、OFFSET、CHOOSE、INDIRECT
返回位置(数值):
MATCH、ROW、COLUMN
返回位置(单元格):
ADDRESS
返回数据区:
IF、OFFSET、CHOOSE、TRANSPOSE
需要单元格:
LOOKUP、MATCH、ROW、COLUMN、OFFSET、CHOOSE、INDIRECT
需要数据区:
LOOKUP、MATCH、ROWS、COLUMNS、INDEX、CHOOSE、TRANSPOSE
需要位置(行号、列号):
ADDRESS、INDEX、OFFSET
9.时间函数
今天是哪天(年月日):
=TODAY
某日期在哪年:
=YEAR(日期)
某日期在哪月:
=MONTH(日期)
某日期在哪天:
=DAY(日期)
某日期在周几:
=WEEKDAY(日期,k)
【k为1或省略则周日是第1天,k为2则周一是第1天】
某日期是那一年的第几周:
=WEEKNUM(日期,k)
【k为1或省略则周日是第1天,k为2则周一是第1天;1月1日所在周为第1周,之后的第1个周日/周一起算第2周】
现在的时刻(年月日时分秒):
=NOW【随时变化】
某时刻所在几时:
=HOUR(时刻)
某时刻所在几分:
=MINUTE(时刻)
某时刻所在几秒:
=SECOND(时刻)
两日期相差的天数:
=DAYS(结束日期,开始日期)
两日期相差的天数(按1年360天算):
=DAYS360(结束日期,开始日期)
输入日期格式:
=DATE(年,月,日)【用于在其他函数中嵌套日期格式】
输入时刻格式:
=TIME(时,分,秒)【用于在其他函数中嵌套时间格式】
10.t检验
成组t检验求P值:
=T.TEST(数据区1,数据区2,k,2)
【k取1为单侧检验,k取2为双侧检验】
成组t’检验求P值:
=T.TEST(数据区1,数据区2,k,3)
【k取1为单侧检验,k取2为双侧检验】
配对t检验求P值:
=T.TEST(数据区1,数据区2,k,1)
【k取1为单侧检验,k取2为双侧检验】
已知P值求t值:
=t.inv.2t(P值,自由度)【得出的t值为正数】
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 高级 函数 汇总
![提示](https://static.bdocx.com/images/bang_tan.gif)