Excel常用函数总结

Excel常用函数总结

2016-10-28 Kevin 叼着奶瓶撩妹

1. VLOOKUP函数

常见形式

问题描述:将下图中G列的数据根据学生的姓名填充到D列。

公式解析:

=VLOOKUP(A2,$F$2:$G$16,2,FALSE)

参数1:需要查询的数据

参数2:指定查询的区域,必须包含查找值和返回值,且第1列必须是查找值

参数3:$F$2:$G$16区域的第2例

参数4:指定的查找方式,TRUE表示模糊 查找,FALSE表示精确查找

解法之二

上图中的问题也可使用INDEX+MATCH解决,如下如图所示。


公式解析:

=INDEX($G$2:$G$16,MATCH(A2,$F$2:$F$16,0))

先用MATCH函数在$F$2:$F$16区域查找A2的位置,在使用INDEX引用$G$2:$G$16区域该位置上的数据

返回多列查询结果

问题描述:将下图中C、D列的数据根学生的姓名分别填充到对应的G、H列。

公式解析:

=VLOOKUP($F2,$A$2:$D$16,COLUMN(C:C),FALSE)

COLUMN(C:C),返回C列的列号

逆向查找

问题描述:

将下图中A列学生的姓名根据相应的学号填充到E列。


公式解析:

=VLOOKUP(D2,IF({1,0},$B$2:$B$16,$A$2:$A$16),2,FALSE)

多条件查找

问题描述:

下图中根据姓名和班级将C列数据填充到G列。


公式解析:

=VLOOKUP(E2&F2,IF({1,0},$B$2:$B$16&$A$2:$A$16,$C$2:$C$16),2,FALSE)

需使用Ctrl+Shift+Enter键

2. IF函数

公式解析:

=IF(A2>=60,"及格","不及格")

=IF(A2>=90,"优秀",IF(A2>=80,"良好",IF(A2>=60,"及格","不及格")))


公式解析:

=IF(AND(A2>=295,A2<=305),"合格","不合格")

等价于这个公式:

=IF(OR(A2<295,A2>305),"不合格","合格")

3. SUMIF函数

单条件求和

公式解析:

=SUMIF(A$2:A$10,">="&C2)

参数1:求和区域

参数2:求和条件


公式解析:

=SUMIF($B$2:$B$10,$F2,C$2:C$10)

参数1:条件区域

参数2:求和条件

参数3:求和区域

多条件求和


公式解析:

=SUMIFS(D$2:D$12,$B$2:$B$12,$H2,$C$2:$C$12,$G2)

参数1:求和区域

参数2:条件1区域; 参数3:求和条件1

参数4:条件2区域; 参数5:求和条件2

据说可以写很127个求和条件。

4. COUNTIF函数

单条件计数

公式解析:

=COUNTIF(B$2:B$16,D2)

参数1:计数区域

参数2:计数条件

多条件计数

公式解析:

=COUNTIFS(B$2:B$16,E2,C$2:C$16,">="&F2)

参数1:计数区域1; 参数2:计数条件1

参数3:计数区域2; 参数4:计数条件2

5. AVERAGEIF函数

单条件求平均值

公式解析:

=AVERAGEIF(B$2:B$16,$E2,C$2:C$16)

参数1:条件区域1

参数2:求值条件

参数3:求值区域

多条件求平均值

公式解析:

=AVERAGEIFS(C$2:C$16,C$2:C$16,">="&F2,B$2:B$16,$E2)

参数1:求值区域

参数2:条件1区域; 参数3:条件1;

参数4:条件2区域; 参数5:条件2;

6. 字符串函数

连接字符串

公式解析:

=A2&"-"&B2

也可以使用这个公式:

=CONCATENATE(A3,"-",B3)

截取字符串

公式解析:

=LEFT(A2,3)

也可用这个公式:

=LEFT(A2,FIND("-",A2)-1)

截取姓名可用这个公式:

=RIGHT(A2,LEN(A2)-FIND("-",A2))

时间: 05-05

Excel常用函数总结的相关文章

Excel常用函数大全

1.ABS函数  函数名称:ABS  主要功能:求出相应数字的绝对值.  使用格式:ABS(number)  参数说明:number代表需要求绝对值的数值或引用的单元格.  应用举例:如果在B2单元格中输入公式:=ABS(A2),则在A2单元格中无论输入正数(如100)还是负数(如-100),B2中均显示出正数(如100).  特别提醒:如果number参数不是数值,而是一些字符(如A等),则B2中返回错误值“#VALUE!”. 2.AND函数  函数名称:AND  主要功能:返回逻辑值:如果所

Excel常用函数公式TOP6(下)

正如我们所知,Excel在数据分析中是一个必不可少的工具,不管是分析师.业务还是运营人员,最常用到的工具软件非Excel莫属,接下来介绍一下在Excel中较为常用的一些函数公式. 1.vlookup---条件查找函数用法:=vlookup(要查找的目标,查找范围,返回第几列的内容,精确或者近似结果)PS:查找值必须在返回值的前面. 2.lookup---多条件查找函数用法:=lookup(1,0/((条件区域1=条件1)*(条件区域2=条件2)),查找目标的区域) 3.mid---提取字符串函数

【转】.NET NPOI操作Excel常用函数

最近因项目接触了NPOI,感觉还是蛮不错的,网络上的教程普遍版本较老,本篇记录所常用操作,采用NPOI 2.0版本. 推荐: NPOI官方网站 NPOI 1.2.4/1.2.5 官方教程 新建Excel HSSFWorkbook hssfworkbook =newHSSFWorkbook(); ISheet sheet1 = hssfworkbook.CreateSheet("sheet1");//建立Sheet1 保存(导出)Excel FileStream file =newFil

EXCEL常用函数公式

1    EXCEL基本操作 1      基本概念:工作簿,工作表,行,列,单元格 2      数据录入 批量录入相同数据(ctrl+enter) ***号码输入(设置单元格属性为"文本"或者前面加 ' ) 分数输入(直接输1/3EXCEL默认为1月3日,应该写成0空格 1/3) 单元格里换行(alt+enter) 3      查找 :模糊查找.值查找 4      冻结窗口 5      筛选:一般筛选,空行筛选 6      行列转置 7      插入对象:对象格式的设置

excel常用函数

判断: if(条件,成立结果,否定结果) 截取字段: =left(A2,2) =right(A2,3) =mid(A2,3,2) 四舍五入: =ROUND(A2,1)                四舍五入到十位 roundup                                    绝对值变大的方向取值 rounddown                                绝对值减小的方向取值 trunc(只要整数位) 保留整数 int(取小于或等于参数的最大值) 条

Excel 常用函数

函数的参数:可以在编辑栏中输入 =left() 会有函数的提示信息.文本函数:1.left2.right3.mid:MID(text,stat_num,num_chars)4.len:5.exact6.replace7.trim8.find9.search10.text: TEXT(value,format_text)时间函数:1.数字格式转化成日期 例如 20170617 date(left("20170617",4),mid("20170617",5,2),ri

Excel怎样从一串字符中的某个指定“字符”前后截取字符及截取字符串常用函数

怎么样可以从一串字符中的某个指定位置的前或后截取指定个数的字符. 如:12345.6789,我要截取小数点前(或后)的3个字符.怎么样操作, 另外,怎么样从右边截取字符,就是和left()函数相反的那个功能. =find(".",a2)返回在数字中字符(小数点)的位置. 具体公式如下:字符(小数点)前三位=MID(A2,FIND(".",A2)-3,3) 字符(小数点)后三个=MID(A2,FIND(".",A2)+1,3) 字符(小数点)前面的

PHP盛宴——常用函数集锦

最近写了蛮多PHP,也接触到挺多常用的函数,大多都记了笔记,发个博客出来,共同学习.其实感觉学习一门语言,语法逻辑是软素质,而对语言的熟悉程度只能随着使用时间的增长而慢慢增长,当对一门语言的函数.库.特性都深深了解了,才能勉强称得上是熟练或者精通吧. 1. trim(),从字符串两端删除空白字符和其他预定义字符,当然可以删除指定的字符. 类似的还有ltrim().rtrim(). 2. __CLASS__,该常量返回该类被定义时的名字. 3. strtotime(),将任何英文文本的日期时间描述

Python3NumPy的常用函数

Python3NumPy的常用函数 1. txt文件 (1) 单位矩阵,即主对角线上的元素均为1,其余元素均为0的正方形矩阵. 在NumPy中可以用eye函数创建一个这样的二维数组,我们只需要给定一个参数,用于指定矩阵中1的元素个数. 例如,创建3×3的数组: import numpy as np I2 = np.eye(3) print(I2) [[1. 0. 0.] [0. 1. 0.] [0. 0. 1.]] (2) 使用savetxt函数将数据存储到文件中,当然我们需要指定文件名以及要保