Excel高级函数汇总.docx

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值为正数】