Excel高级函数汇总

1、Excel高级函数汇总Excel高级函数汇总Excel高级函数汇总Excel高级函数汇总大数据办理必备最常用的统计软件非SPSS 、 Stata莫属了，但它们各有不足。SPSS的数据录入比较繁琐，能达成的统计功能限制在已经设计好的“剖析”模块里。 SPSS 的作图功能也是不敢奉承。Stata轻巧便捷，但是需要自己输入命令，新手上手需要一个过渡期。以上两个软件的计算功能也称不上便捷。其实， excel就能够承担起相当一部分计算、数据录入、数据筛选，甚至是统计、作图的功能。利用excel强大的“函数”功能，能够实现诸如计算两个指定日期相差多少天、计数切合某条件的单元格有多少个、在复杂的病理报告中自

2、动选出含有某个免疫组化阳性的蛋白、按特定需求查找单元格内容等任务。excel的函数分为以下几大类，分别是 计算类 （如求绝对值、余数） 、统计类 （如求和、 求标准差、 t 查验） 、时间类 、条件语句 （如果 则 ） 、逻辑类 （返回真/ 假，包括和/ 或 / 非 / 异或等）、等级排序类、计数单元格个数类、文本类 （如查找、替换文本中的某字符串）、查找类 （如查找某内容的单元格的位置）等。利用这些函数的组合能够实现多种功能，且直接记录在excel表格中，无需像SPSS 等软件还要转抄结果。计算（针对数值）余数 =MOD绝对值 =ABS ，高斯取整（向下取整）=INT截断取整 =TRUNC(

3、数值 , 保存小数位数)【保存的小数位数中，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,3,5,8;1

4、,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】求排

5、名倒数第n 的数值是多少=SMALL(数据区 ,n)求中位数 =MEDIAN ，求众数（较小的）=MODE求四分位数 =QUARTILE( 数据区 ,k)【 k 取 04 ，最小值 0，较小四分位数1 ，中位数2 ，较大四分位数3 ，最大值 4】求百分位数 =PERCENTILE( 数据区 ,k)【 k 取 01 ，若 k 不是 1/(n-1)的倍数， 函数使用插值法来确定第k 个百分位数的值，如PERCENTILE(1,2,3,4,0.3)为 1.9】求每个数值与均值的差的平方和=DEVSQ求偏斜度 =SKEW ，求峰值 =KURT2.1. 在某条件下求和当数据区1 知足某条件下求数据区2

6、的对应地区的和：=SUMIF(数据区1, 条件 ,数据区2)【条件能够是 男 、 A2 ，数据区2 必须和数据区1 对应，若无数据区2默认对知足条件的数据区1 求和】逻辑算符【独自使用返回TRUE/FALSE ，可用于IF 函数】同时知足 n 个条件为真：=AND(条件 1,条件 2, )知足其中一个条件即为真：=OR( 条件 1, 条件 2, )两条件真假不同则为真（异或）：=XOR( 条件 1, 条件 2, )不知足条件为真：=NOT(条件 )单元格A1与 A2相同（不划分大小写）：=A1=A2单元格A1与 A2相同（划分大小写）：=EXACT(A1,A2)条件语句=IF( 条件 ,若真则

7、怎样,若假则怎样)【返回的能够是单元格内容、文本、数值，甚至是地区】【条件可嵌套逻辑算符】入选定单元格或结果出现错误（ERROR ）时怎样 =IFERROR( 单元格或计算式 , 出现错误会怎样)【例 =IFERROR(A2, 哈哈 ) ， =IFERROR(1+A2, B3)】【错误种类包括#N/A、#VALUE!、#REF! 、#DIV/0!、#NUM!、#NAME?或 #NULL!】等级排序求 D3 数值在选定数值的排位（降序排）：=RANK(D3,数据区 ,0)【重复数值返回较小排位，9 在 10,9,9,8,7,6中排第2 】求 D3 数值在选定数值的排位（升序排）：=RANK(D3

8、,数据区 ,1)【重复数值返回较小排位，9 在 10,9,9,8,7,6中排第4 】【数据区后为0 降序排，数据区后为1 升序排】求各样种类的单元格的个数【 3 个函数 COUNT是计数数值种类的单元格个数，COUNTA是计数各种种类的单元格个数，COUNTIF 是计数在某条件下的单元格个数 各种函数后的 IF 都是指在某条件下，如SUMIF 】【任何函数后边加 “ A ”表示不忽略非数值型内容，如 COUNTA、MAXA、MINA ，文本型、真空、FALSE 为 0， TRUE 为 1 】真空单元格个数：=COUNTIF(数据区 ,=)【数据区A:G 为 A 列到 G 列所有单元格， 19:

9、21为19行到21 行所有单元格】非空单元格个数：=COUNTIF(数据区 ,)文本型 单元格个数：=COUNTIF( 数据区 ,*)非空文本型单元格个数：=COUNTIF(数据区 ,=!)所有单元格个数：=COUNTIF(数据区 , )【单元格含有 时公式不可立】逻辑值为TRUE 的单元格个数：=COUNTIF(数据区 ,TRUE)等于 50的单元格个数：=COUNTIF(数据区 ,50)不等于 50 的单元格个数：=COUNTIF( 数据区 ,50)大于 50的单元格个数：=COUNTIF(数据区 ,50)【小于 = ，小于等于 50)-COUNTIF(数据区 ,100)等于E5 单元格

10、数值的单元格个数：=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&*)【 & 是

11、连结符， $ 表示绝对引用，不随插入其他值引起的行号、列号变化而变化，如 $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 查找切合多个条件的单元格的

12、个数=COUNTIFS(数据区1, 条件 1, 数据区2, 条件 2,)【数据区必须是对应的】文本函数【输入文本时需要键入半角双引号】检测是否为文本格式： =T( 单元格 )【若为文本格式显示原内容，若不是文本显示空白】归并字符串：= CONCATENATE(文本 1, 文本 2, 文本 3,)归并某地区的字符串：=PHONETIC(数据区1, 数据区2, 数据区3,)【区域如 A1:B5 ，先横向归并，再纵向归并】【碰到数值格式、日期格式、公式则跳过不归并，因此能够预先把数值格式变为文本格式】重复显示某文本k 次： =REPT( 文本 ,k)将阿拉伯数字（数值）变为文本格式罗马数字：=ROM

13、AN(数值 ,k) 【由I 、 V 、 X 、 C 等组成，非Unicode单字符】【 k 取 04/TRUE/FALSE， 0 为经典罗马数字，14为逐步简化版，4为最简版，TRUE 为经典版，FALSE 为最简版】将文本中全角字符变为半角字符：=ASC( 文本 )将文本中半角字符变为全角字符：=WIDECHAR(文本 )将大写字母变为小写字母：=LOWER(文本 )将小写字母变为大写字母：=UPPER( 文本 )将数字转变为 ASC 码对应的字符（十进制）：=CHAR( 数值 )显示文本中第一个字符的ASC 码对应的数字：=CODE( 文本 )将数字转变为 Unicode码对应的字符（十进

14、制）：=UNICHAR(数值 )显示文本中第一个字符的Unicode 码对应的数字：=UNICODE(文本 )将首个字母变为大写字母，其余字母变为小写字母：=PROPER(文本 )数字文本转变为数值格式：=VALUE(文本 )数值格式转变为文本格式：=FIXED(数值 , 保存小数位数 ,逻辑值 )【除了能够转变为文本格式外，还能够格式化输出，如四舍五入和千分符；逻辑值为TRUE ，文本不出现数字千分符；逻辑值为FALSE ，文本出现数字千分符】【若只想格式化输出，不想转变为文本格式，直接右键 设置单元格格式】显示文本的字符数：=LEN( 文本 )显示文本的字节数：=LENB( 文本 )【对于

15、 “ 字节 ”的函数都是在 “ 字符 ”的函数后加 “ B”，如 LENB 、LEFTB 、FINDB 、 REPLACEB 、 SEARCHB 】某字符串在文本中第一次出现时是第几个字符（划分大小写） ：=FIND( 某字符串 ,文本 ,从第几个字符开始查找)【有时要查找的字符串不只出现1 次，要查后边出现的字符串能够设置从第几个字符开始查找】某字符串在文本中第一次出现时是第几个字符（不划分大小写）：=SEARCH( 某字符串 ,文本 ,从第几个字符开始查找)【 search 与 find函数区别在于划分大小写，以及search 能够使用通配符 *?】提取文本中左起的k 个字符串：=LEFT

16、( 文本 ,k)提取文本中右起的k 个字符串：=RIGHT( 文本 ,k)提取文本中第j 个字符开始的k 个字符串： =MID(文本 ,j,k)替换文本中第j 个字符开始的k 个字符为新字符串：=REPLACE( 文本 ,j,k,新字符串 )替换文本中的旧字符串为新字符串：=SUBSTITUTE(文本 ,旧字符串 ,新字符串 ,k) 【若旧字符串有多个，指明替换第k 个】【 replace 是替换指定位置的字符串，substitute是替换指定内容的字符串】消除文本中的非打印字符：=CLEAN(文本 )【非打印字符主假如ASCII码 0031，分别是标题开始、正文开始、正文结束、传输结束、恳求

17、、收到通知、响铃、退格、水平制表符、换行键、垂直制表符、换页键、回车键、不用切换、启用切换、数据链路转义、设施控制1 、设施控制2 、设施控制 3 、设施控制 4 、拒绝接收、同步安闲、结束传输块、取消、媒介结束、代替、换码(溢出 )、文件分开符、分组符、记录分开符、单元分开符】消除文本中所有空格（单词间留1 个）： =TRIM(文本 ) 【空格的 ASCII码为 32】查找内容【使用查找或按行、按列查找功能能够实现不同工作表的数据的联动，相当于使用 Acess 数据库！】【使用 LOOKUP 、 VLOOKUP 、 HLOOKUP 必须要升序排列！】【查找类的函数都能够使用通配符】在某行 /

18、 列或其一部分查找内容，返回与“ 第一个找到的单元格” 对应的另一地区的单元格内容：=LOOKUP(需查内容,某行 / 列或其一部分, 对应的行 / 列或其一部分)【需查内容：能够是单元格、文本、数值】【若数值不按升序排列，会以二分法查找数值，详细见此网页 】【 LOOKUP只能模糊查找，VLOOKUP、 HLOOKUP能够模糊查找、精确查找】8.1 按列查找内容在某地区查找内容，返回与“ 第一个找到的单元格” 相同行，且指定列的单元格内容：=VLOOKUP(需查内容 ,数据区 ,需返回的数据区的行数,k)【虽然是数据区，其实只是查找第一列的内容而已】【需查内容：能够是单元格、文本、数值】【需

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

20、k 为 1/TRUE 时模糊查找 （包含即可）】【模糊查找数值有个 “ 隐藏功能 ” ，若没有完全同样的能够返回比该数值小且相差最小的数，但要求第一列升序排列】8.3 查找相对位置在某行 / 列或其一部分查找内容，返回“ 第一个找到的单元格” 在该地区的第几个：=MATCH(需查内容,某行 / 列或其一部分,k)【 k 为 0 精准查找，对查找地区的数值排列无要求；k 为 1 查找小于该数值的最大值，要求升序排列；k 为 -1 查找大于该数值的最小数，要求降序排列】【需查内容：能够是单元格、文本、数值】【若无查找内容则返回“错误 ”】8.4 查找绝对位置某单元格的行号：=ROW(单元格 )【若

21、空则返回公式所在单元格，若是地区则返回该地区第一行所在单元格】某地区的行数：=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

22、 样式， 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 行

23、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(A

24、10)：返回 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时间函数今天是哪天（年月日）：=TODAY某日期在哪年：=YEAR( 日期 )某日期在哪