Excel高级函数汇总.docx

1、Excel高级函数汇总Excel高级函数汇总大数据处理必备最常用的统计软件非SPSS、Stata莫属了，但它们各有不足。SPSS的数据录入比较繁琐，能完成的统计功能局限在已经设计好的“分析”模块里。SPSS的作图功能也是不敢恭维。Stata轻巧便捷，然而需要自己输入命令，新手上手需要一个过渡期。以上两个软件的计算功能也称不上便捷。其实，excel就可以承担起相当一部分计算、数据录入、数据筛选，甚至是统计、作图的功能。利用excel强大的“函数”功能，可以实现诸如计算两个指定日期相差多少天、计数符合某条件的单元格有多少个、在复杂的病理报告中自动选出含有某个免疫组化阳性的蛋白、按特定需求查找单元格

2、内容等任务。excel的函数分为以下几大类，分别是计算类（如求绝对值、余数）、统计类（如求和、求标准差、t检验）、时间类、条件语句（如果则）、逻辑类（返回真/假，包括和/或/非/异或等）、等级排序类、计数单元格个数类、文本类（如查找、替换文本中的某字符串）、查找类（如查找某内容的单元格的位置）等。利用这些函数的组合可以实现多种功能，且直接记录在excel表格中，无需像SPSS等软件还要转抄结果。1.计算（针对数值）余数=MOD绝对值=ABS，高斯取整（向下取整）=INT截断取整=TRUNC(数值,保留小数位数)【保留的小数位数中，0表示保留到个位数，-1表示保留到十位数，依次类推】四舍五入=R

3、OUND(数值,保留小数位数)乘方=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

4、【除以自由度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取04，最小值0，较小四分位数1，中位数2，较大四分位数3，最大值4】求百分位数=PERCEN

5、TILE(数据区,k)【k取01，若k不是1/(n-1)的倍数，函数使用插值法来确定第k个百分位数的值，如PERCENTILE(1,2,3,4,0.3)为1.9】求每个数值与均值的差的平方和=DEVSQ求偏斜度=SKEW，求峰值=KURT2.1.在某条件下求和当数据区1满足某条件下求数据区2的对应区域的和：=SUMIF(数据区1,条件,数据区2)【条件可以是男、A2，数据区2必须和数据区1对应，若无数据区2默认对满足条件的数据区1求和】3.逻辑算符【单独使用返回TRUE/FALSE，可用于IF函数】同时满足n个条件为真：=AND(条件1,条件2,)满足其中一个条件即为真：=OR(条件1,条件2

6、,)两条件真假不同则为真（异或）：=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

7、!】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、MI

8、NA，文本型、真空、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的单元格个数：=COUNTI

9、F(数据区,50)【小于=，小于等于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*，就是BB】包含D3单元格内容的单元格个数：=COUNTIF(数据区,*&D3&*)【

10、&是连接符，$表示绝对引用，不随插入其他值引起的行号、列号变化而变化，如$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,数据

11、区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取04/TRUE

12、/FALSE，0为经典罗马数字，14为逐步简化版，4为最简版，TRUE为经典版，FALSE为最简版】将文本中全角字符变为半角字符：=ASC(文本)将文本中半角字符变为全角字符：=WIDECHAR(文本)将大写字母变为小写字母：=LOWER(文本)将小写字母变为大写字母：=UPPER(文本)将数字转变为ASC码对应的字符（十进制）：=CHAR(数值)显示文本中第一个字符的ASC码对应的数字：=CODE(文本)将数字转变为Unicode码对应的字符（十进制）：=UNICHAR(数值)显示文本中第一个字符的Unicode码对应的数字：=UNICODE(文本)将首个字母变为大写字母，其余字母变为小写字

13、母：=PROPER(文本)数字文本转化为数值格式：=VALUE(文本)数值格式转化为文本格式：=FIXED(数值,保留小数位数,逻辑值)【除了可以转化为文本格式外，还可以格式化输出，如四舍五入和千分符；逻辑值为TRUE，文本不出现数字千分符；逻辑值为FALSE，文本出现数字千分符】【若只想格式化输出，不想转化为文本格式，直接右键设置单元格格式】显示文本的字符数：=LEN(文本)显示文本的字节数：=LENB(文本)【关于“字节”的函数都是在“字符”的函数后加“B”，如LENB、LEFTB、FINDB、REPLACEB、SEARCHB】某字符串在文本中第一次出现时是第几个字符（区分大小写）：=FI

14、ND(某字符串,文本,从第几个字符开始查找)【有时要查找的字符串不只出现1次，要查后面出现的字符串可以设置从第几个字符开始查找】某字符串在文本中第一次出现时是第几个字符（不区分大小写）：=SEARCH(某字符串,文本,从第几个字符开始查找)【search与find函数区别在于区分大小写，以及search可以使用通配符*?】提取文本中左起的k个字符串：=LEFT(文本,k)提取文本中右起的k个字符串：=RIGHT(文本,k)提取文本中第j个字符开始的k个字符串：=MID(文本,j,k)替换文本中第j个字符开始的k个字符为新字符串：=REPLACE(文本,j,k,新字符串)替换文本中的旧字符串为新

15、字符串：=SUBSTITUTE(文本,旧字符串,新字符串,k)【若旧字符串有多个，指明替换第k个】【replace是替换指定位置的字符串，substitute是替换指定内容的字符串】清除文本中的非打印字符：=CLEAN(文本)【非打印字符主要是ASCII码0031，分别是标题开始、正文开始、正文结束、传输结束、请求、收到通知、响铃、退格、水平制表符、换行键、垂直制表符、换页键、回车键、不用切换、启用切换、数据链路转义、设备控制1、设备控制2、设备控制3、设备控制4、拒绝接收、同步空闲、结束传输块、取消、媒介结束、代替、换码(溢出)、文件分隔符、分组符、记录分隔符、单元分隔符】清除文本中所有空格

16、（单词间留1个）：=TRIM(文本)【空格的ASCII码为32】8.查找内容【使用查找或按行、按列查找功能可以实现不同工作表的数据的联动，相当于使用Acess数据库！】【使用LOOKUP、VLOOKUP、HLOOKUP必须要升序排列！】【查找类的函数都可以使用通配符】在某行/列或其一部分查找内容，返回与“第一个找到的单元格”对应的另一区域的单元格内容：=LOOKUP(需查内容,某行/列或其一部分,对应的行/列或其一部分)【需查内容：可以是单元格、文本、数值】【若数值不按升序排列，会以二分法查找数值，具体见此网页】【LOOKUP只能模糊查找，VLOOKUP、HLOOKUP可以模糊查找、精确查找】

17、8.1按列查找内容在某区域查找内容，返回与“第一个找到的单元格”相同行，且指定列的单元格内容：=VLOOKUP(需查内容,数据区,需返回的数据区的行数,k)【虽然是数据区，其实只是查找第一列的内容而已】【需查内容：可以是单元格、文本、数值】【需返回的数据区的行数：指被查找的数据区的第几行】【k为0/FALSE时精确查找（完全一致），k为1/TRUE时模糊查找（包含即可）】【模糊查找数值有个“隐藏功能”，若没有完全一样的可以返回比该数值小且相差最小的数，但要求第一列升序排列】8.2按行查找内容在某区域查找内容，返回与“第一个找到的单元格”相同列，且指定行的单元格内容=HLOOKUP(需查内容,数

18、据区,需返回的数据区的列数,k)【虽然是数据区，其实只是查找第一行的内容而已】【需查内容：可以是单元格、文本、数值】【需返回的数据区的行数：指被查找的数据区的第几行】【k为0/FALSE时精确查找（完全一致），k为1/TRUE时模糊查找（包含即可）】【模糊查找数值有个“隐藏功能”，若没有完全一样的可以返回比该数值小且相差最小的数，但要求第一列升序排列】8.3查找相对位置在某行/列或其一部分查找内容，返回“第一个找到的单元格”在该区域的第几个：=MATCH(需查内容,某行/列或其一部分,k)【k为0精确查找，对查找区域的数值排列无要求；k为1查找小于该数值的最大值，要求升序排列；k为-1查找大于

19、该数值的最小数，要求降序排列】【需查内容：可以是单元格、文本、数值】【若无查找内容则返回“错误”】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时仅绝对引用

20、行号，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)【起

21、点为与该单元格距离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内容所示单元格的内容，即1

22、1，相当于二次引用=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、C

23、OLUMNS、INDEX、CHOOSE、TRANSPOSE需要位置（行号、列号）：ADDRESS、INDEX、OFFSET9.时间函数今天是哪天（年月日）：=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(时刻

24、)某时刻所在几分：=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值为正数】