excel函数总结

E X C E L函数总结

-标准化文件发布号：（9556-EUATWK-MWUB-WUNN-INNUL-DDQTY-KII

一、数据公式汇总处理（求和、计数、平均数）

一、求和

1、sum的“与”

可与数组结合使用，更好用

sum与“与”结合使用

数组中用*

补充：当有多条件求和时可直接使用数组，and 用* ，or用+，表示出1或是0

2、sum与“或”条件应用

3、sumpoduct！！！--相乘再加减

！！！--相乘再加减

补充：直接是多个数组相乘 sumproduct(array1(),array2(),array3()....)

先判断条件，然后做相乘，最后的和是sum做

4、sumif 单条件求和，不能多条件求和

--条件判断区域，（不能是一个数组，只能是一个单元格区域）

--条件

---求和区域。如省略，将直接使用条件区域求和

5、sumif与通配符的使用

通配符作为条件：

识别通配符：~

开始是：我*

结束是：*我

包含：*我*

固定长度：

6、sumif 与数组

数组作为条件判断{元素；元素}

7、sumif精妙使用

8、sumifs 多条件求和

--求和区域

--成对的条件区域和条件

--与数组、通配符结合使用

二、计数

1、count 计算是数字的个数

2、counta 计算非空单元格

3、计数空白单元格个数

4、countif

--区域

--条件

--countif与数组、通配符结合使用5、countifs

--成对条件区域和条件

--数组、通配符结合使用

三、平均数

1、averagea

文本、FLase函数值为0. True为1 2、averageif

--条件区域

--条件

--平均区域（用法同sumif）

3、 averageifs 多条件求平均值

同sumifs

4、trimmean

--平均修剪函数

--求平均值的区域或是数组

--percent 找到分子和分母

--经常计算求去掉一个最高分和最低分5、排名

--rank美式

--number 排名的值

--数组区域ref

--order 降序，0或是空，升序，为1

中式排名：sumproduct() 排除重复的继续按正常排名

=SUMPRODUCT((B$6:B$14>B6)*(1/COUNTIF(B$6:B$14,B$6:B$14)))+1 --countif 排除不重复的值

例子：

true和false转换成1和0的各种方法

逻辑值数值公式位置

TRUE 1 =--A13 “--”在前

FALSE 0 =Q14*1 “*”前后均可

TRUE 1 =L15/1 “/”在后

TRUE 1 =Q15-0 “-”在后

FALSE 0 =Q16+0 “+”前后均可

FALSE 0 =N(A18) 置于函数N中

总结规律：

AND可以用*来代

替

OR可以用+来代替

二、日期、时间函数

1、年月日函数

--year(日期) 值：1990-9999

--month（日期）值：1-12月

--day(日期) 值：1-31天

--date(year,month，day)

总结：弄清楚各个日期之间的单位，进行转换

2、日期转换成天数

设置下数值格式

3、季度

4、now and today

=today() 返回当前日期

=now（）返回当前日期和时间，时间精确到分钟，可自定到秒

这2个函数都没有对应参数

5、dateif()

6、星期几函数weekday()

=weekday(日期值)，返回数字）；1，从星期日到星期六。2，中国习惯，星期一到星期日

7、今天到底第几周weeknum

返回一年中的周数

=weeknum(日期值，) 同weekday类似

8 、工作日workday，具体日期

=workday(日期，工作日数，节假日数（可数组）)

9、networkdays 工作日数

=networkdays(start_date，end_date，holiday)

10、时分秒

时：=hour()

分：=minute()

秒：=second()

时间：=time()

三、查找函数

一、vlookup

1）精确查找：

=vlookup(查找值，查找区域，返回值所在列，0,false）

！！第三参数：返回值所在列，可使用数组

2）模糊查找：

=vlookup(查找值，查找区域，返回值所在列，1，true）

查找：比查找值小的最大值

3）通配符查找：

二、Hlookup

Hlookup（查找值，查找区域，返回值所在行，0或1，类似vlookup)

三、Lookup

1）向量用法：lookpup(查找值，lookupventor,results_ventor) lookupventor：一行一列，需按升序排列是否排序，都已是升序，只能选一列或是一行

results_ventor