02#EXCEL函数【基础】

1、choose函数

2、convert函数

CONVERT函数

CONVERT(number,from_unit,to_unit)

[将数字从一个度量系统转换到另一个度量系统中]

number:要转换度量单位的数字

from_unit:转换前的单位

to_unit:转换前的单位

3、count函数

count  计算参数中包含数字的个数

counta 计算参数中包含非空值的个数

countblank  计算区域中空白单元格的个数

4、countif函数

    

     countif(range,criteria)

 

    range:选择区域

    criteria:判断条件

    【计算满足给定条件的单元格的个数】

     

5、index函数

INDEX(array,row_num,[colomn_num])

        [返回指定内容的所在位置的值]

        返回单元格区域或数组中行与列交叉位置上的值

array 指定区域

row_num  返回的行号

colomn_num 返回的列号

6、indirect函数

INDIRECT(ref_text,[a1])

【返回由文本字符串指定的引用】

ref-text :引用文本

a1：引用类型

7、int   trunc函数

INT(number)

[将数字向下舍入到最接近的整数]

TRUNC函数

TRUNC(number,[num_digits])

[将数字的小数部分截取，返回整数]

8、large  small函数

LARGE(array,k)

【返回数据集中的第k个最大值】

SMALL(array,k)

【返回数据集中第k个最小值】

9、lookup函数

向量形式  LOOKUP(look_value,lookup_vector,result_vector)

        lookup_vector查询区域需要升序排列

        result_vector返回结果的区域

数组形式  LOOKUP(lookup_value,array)

        查询第一列 返回最后一列

        查询的行数大于等于列数

        查找区域第一行必须升序排列

向量形式：仅在单行单列中查找数据

数组形式：在区域或数组中查找数据

10、match函数

MATCH(lookup_value,lookup_array,[match_type])

        [返回指定内容的所在位置]

        在单元格区域中收索制定像

        然后返回该项在单元格区域中的相对位置

LOOKUP_VALUE  查找的值    

LOOKUP_ARRAY  查找的范围（只可以是一行或者一列）

match_type  查找的类型  0精确查找

                        1 -1 模糊查找

11、median（mode）函数

MEDIAN(number1,number2,……)

    【返回给定参数中的中值】

MODE(number1,number2,……)

    【返回给定参数中的众数】

12、mod函数

MOD 函数

MOD(number,divisor)

[返回两数相除的余数，结果的正负号与除数相同]

13、offset函数

OFFSET(reference,rows,cols,[height],[width])

【根据给定的偏移量返回新的引用区域】

reference 参考点

新的一天没有值也需要输入0

14、rand()函数

RAND用于产生0~1之间的随机数     RAND()   

RAND()*1000  随机产生0-100；之间的数字

如果想要产生  a-b 之间的随机数字

RAND()*(B-A)+A 

如果想要控制小数位 可以引入ROUND函数

ROUND(RAND()*100,3)

RANDBETEEN函数

RANDBETWEEN(bottom,top)

【返回位于指定的俩个数之间的一个随机整数】

可以用两个日期之间的随机日期

15、rank函数

RANK(number,ref,[order])

RANK.EQ(number,ref,[order])

RANK.AVG(number,ref,[order]) 

【返回一个数值在一组数字中的排位】

number:要排位的数字

ref:   要排位的数字区域

order: 排位的方式

16、round函数

ROUND(number,num_digits)

[按指定位数对数字进行四舍五入]

    number:要处理的数据

    num_digits:要舍去的位数

ROUNDDOWN  向下舍入

ROUNDUP    向上舍入

MROUND   

CEILING   

FLOOR

EVEN

ODD

17、row函数

ROW([reference])

[返回引用的行号]

18、sumif函数

SUMIF (range,criteria,[sum_range])

    range:条件区域

    criteria:条件

    sun_range:求和区域

【按给定条件对指定单元格求和】

同样可以用AVERAGEIF函数

多个求和条件用 sumifs

SUMIFS(sum_range,criterria_range1,criteria1,[ criterria_range2,criteria2,]……)

同样还有 COUNTIFS函数

19、日期函数

YEAR(serial_number)   返回年份

MONTH（serial_number）  返回月份

DAY（ serial_number）  返回某一天

HOUR( serial_number)   小时

MINUTE( serial_number)  分钟

SECOND( serial_number)   秒数

DATE(year,month,day)  将三个合并

TIME(hour,minute,second)  合并时间

20、weekday函数

WEEKDAY(serial_number,[return_type])

【返回某日期为星期几】

21、networkdays函数  workday函数

    

NETWORKDAYS(start_date,end_date,[holiday])

【计算日期之间所有的工作日】

holiday :要排除的日期

WORKDAY(start_date,day,[holidays])

【计算指定日期向前向后数个工作日后的日期】

22、eonmonth函数 edate函数

EOMONTH(start_date,months)

【指定日期向前或向后几个月的那个月的最后一天的日期】

months:间隔月数

EDATE函数

EDATE( start_date,months)

【指定日期向前或向后几个月的日期】

23、datedif函数

DATEDIF(start_date,end_date,unit)

【计算开始与结束日期之间的日期间隔】

unit:计算单位 双引号引起来的 y m d 

24、find mid函数

FIND函数

FIND(find_text,within_text,[start_num])

【以字符为单元并且区分大小写查找指定字符位置】

SEARCH( find_text,within_text,[start_num])

【以字符为单元并且不区分大小写查找指定字符位置】

within_text:查找的位置

MID(text，start_num,num_chars)

[从文本制定位置起提取制定个数的字符]

MID(A2,FIND("-",A2)-1,4)   数据提取用

 25、left right 函数

LEFT(text,[num_chars])

【从文本左侧起提取指定个数的字符】

RIGHT( text,[num_chars])

【从文本右侧起提取指定个数的字符】

26、trim函数  clean函数

TRIM函数

trim(text) 

【删除多余的空格】

CLEAN函数

CLEAN(TEXT)

【删除无法打印的字符】

27、conctenate函数

CONCTENATE(text,[text2])

【将多个文本合并在一起】

&可以用这个代替这个函数

28、upper lower函数

PROPER 将文本中的每个单词的首字母转换成大写

UPPER  将文本转换成大写

LOWER  将文本转换成小写

29、replace substitute函数

REPLACE(old_text,start_num,num_chars,new_text)

【根据指定的位置进行替换】

SUBSTITUTE(text,old_text,new_text,[instance_num])

【根据指定文本进行替换】

[instance_num]:替换第几次出现的文本

30、text函数

TEXT(value,format_text)

【将数字转换为按指定格式显示的文本】

TEXT(F1,"000-00000000")

31、istext isnumber  isnontext 函数

    

istext()

【判断单元格是否是文本】

32、iserror  isree  isna函数

和if和用  如果有错误值产生 就返回“NA”或空

IF(ISERROR(A/B),"")

33、使用数组函数时  

      shift+ctril+enter

34、frequency函数  数组函数

FREQUENCY(data_array,bins_array)

【以垂直数组形式返回数据的频繁分布】

date_array:要统计的区域

bins_array:频率分布区间

35、transpose函数

行列转置；数据可以联动

36、IF函数

=IF(B11>12,"合格","不合格")

37、非时间数据处理成时间格式

      

非日期数据	转换成日期数据	截至日期	两个日期之间的时间长度
20160725	2016-07-25	2016/7/27	2
20160521	2016-05-21	2016/7/27	67
	=LEFT(A2,4)&"-"&MID(A2,5,2)&"-"&RIGHT(A2,2)		=DATEDIF(B2,C2,"d")

文本函数：

函数名	函数说明	语法
ASC	将字符串中的全角（双字节）英文字母更改为半角（单字节）字符	ASC(text)
JIS	将字符串中的半角英文字母更改为全角字符	JIS（text）
CHAR	返回对应于数字代码的字符，函数CHAR可将其他类型计算机文件中的代码转换为字符	CHAR（number）
CLEAN	撤除文本中不可以打印的字符。	CLEAN（text）
CODE	返回字符串中第一个字符的数字代码。返回的代码对应于计算机当前使用的字符集	CODE（text）
CONCATENATE	将若干字符串合并到一个字符串中	CONCATENATE(text1,text2,...)
DOLLAR	依照货币格式将小数四舍五入到指定的位数并转换成文字	DOLLAR或RMB（number,decimals）
EXACT	该函数测试两个字符串是否完全相同。如果他们完全相同，则返回TRUE	EXACT(text1,text2)
FIND	查找其他字符串内的文本串	FIND(find_text,within_text,start_num)
FIXED	指定的小数位数进行四舍五入，利用句号和逗号，以小数格式对该数设置格式，并以文字串的形式返回	FIXED（number,decimals,no_commas）
SUBSTITUTE	替换	SUBSTITUTE（text,old_text,new_text,instance_num）

时间函数：