excel公式

1、SUM 求和
SUM(number1，number2，…)
每个参数可以使区域、单元格引用、数组、常数、公式或另一个函数的结果
2、SUMIF条件求和函数
SUMIF(区域，条件，求和区域)
如：=SUMIF(B2：B25，”>5”)让数列B2到B25中大于5的数求和
如：=SUMIF(C2：C10，”女”，E2：E10)将C2：C10区域中单元格为“女”的所对应的E2：E10区域中满足该条件的单元格求和。
3、AVERAGE 求平均值
AVERAGE(number1，number2，…)
4、AVERAGEIF返回某个区域内满足给定条件的所有单元格的平均值(算术平均值)
AVERAGEIF(区域，条件)
5、MAX 求最大值
MAX(number1，number2，…)
6、MIN 求最小值
MIN(number1，number2，…)
7、COUNT计算数字个数
COUNT(Value1，Value2，…)
8、COUNTIF计算某个区域中满足给定条件的单元格数目
COUNTIF(区域，条件)
如：=COUNTIF(b2：b10，”男”)计算b2：b10区域中为“男”的单元格数目。
9、COUNTIFS将条件应用于跨多个区域的单元格，并计算符合所有条件的个数
countifs(criteria_range1,criteria1,criteria_range2,criteria2,…) criteria_range1为第一个需要计算其中满足某个条件的单元格数目的单元格区域(简称条件区域)，criteria1为第一个区域中将被计算在内的条件(简称条件)，其形式可以为数字、表达式或文本。例如，条件可以表示为 48、"48"、">48" 或 "广州"；　　同理，criteria_range2为第二个条件区域，criteria2为第二个条件，依次类推。最终结果为多个区域中满足所有条件的单元格个数。
其结果与dcounta函数一致

注：输入格式：
countifs(条件所在区域1，条件1，条件所在区域2，条件2，……)
sumifs(际求和区域，条件所在区域1，条件1，条件所在区域2，条件2，……)
averageifs(实际求平均值区域，条件所在区域1，条件1，条件所在区域2，条件2，……)

10、dcounta对满足指定条件的数据库中记录字段(列)的非空单元格进行记数。
Dcounta(Database,Field,Criteria)
Database是构成列表或数据库的单元格区域。数据库是相关数据的列表。Field或是用双引号括住的列标签，或是表示该列在列表中位置的数字。Criteria是包含指定条件的单元格区域。区域包括列标签及列标签下满足某个条件的单元格。

如下表，统计“性别为男的人数”
若统计平均分在80以上的男同学人数：(第二个参数，列太多，则将其空着不填)

注：第二个参数Field可写字段名也可写成列号，如“大写”可写成3
11、COUNTA计算区域中不为空的单元格的个数
COUNTA(value1,value2,...)
12、COUNTBLANK计算指定单元格区域中空白单元格的个数
COUNTBLANK (value1,value2,...)
13、ROUND四舍五入函数
ROUND(number,num_digits)
返回某个数字按指定位数舍入后的数字，如果num_digits省略则表示小数点后位数为0
14、INT取整函数
INT(number)
舍去小数点后面的数
15、SQRT平方根函数
SQRT(number)
16、RAND返回大于等于0且小于1的平均分布随机数(计算结果可变)
RAND()
17、ABS返回参数的绝对值
ABS(number)
18、MOD返回两数相除的余数，结果的正负号与除数相同
MOD(number,divisor)
Number为被除数，divisor为除数。
说明：1)如果divisor为零，返回错误值#DIV/0!
19、SIGN返回数字的符号
SIGN(number)
当数字为正数时返回1，为零时返回0，为负数时返回-1
20、LEN返回指定字符串的长度
LEN(text)
说明：每个ASCII字符的长度为1，每个汉字的长度也为1
21、LEFT从文本字符串的第一个字符开始(从左边第一个开始)，返回指定个数的字符。
left(text,num_chars)
text代表用来截取的单元格内容。num_chars代表从左开始截取的字符数
22、RIGHT从文本字符串的第一个字符开始(从右边第一个开始)，返回指定个数的字符
right (text,num_chars)
text代表用来截取的单元格内容。num_chars代表从左开始截取的字符数
23、MID从文字串某一指定位置开始，返回指定长度的字符串。
MID(text,start_num,num_chars)
MID(身份证号所在单元格,7,8)在身份证号中提取出生日期
24、逻辑函数：AND、NOT、OR、TRUE、FALSE函数
AND(x1,x2, …,x30)都为true时结果为ture，有一个是false则为false
OR(x1, x2, …,x30)都为false时结果为false，有一个是ture则为ture
NOT(logical)取反
AND(TRUE,TRUE)=TRUE，
OR(TRUE,TRUE)=TRUE
AND(TRUE,FALSE,TRUE, TRUE)=FALSE，OR(TRUE,FALSE,TRUE,TRUE)=TRUE。
如果B1，B2，B3单元格中的值为 TRUE，FALSE，TRUE，则AND(B1:B3)=FALSE，但OR(B1:B3)=TRUE。AND(2,2+3=5)=TRUE
If函数与and函数的嵌套使用：如：当语文、数学、英语成绩都大于100分，则返回“优”，否则“一般”，公式：=IF(AND(B2>100,C2>100,D2>100),"优","一般")
25、SIN返回角angle(弧度)的正弦值
SIN(angle)
COS(angle)：返回角angle(弧度)的余弦值。
TAN(数值)：返回角angle的正切值
26、PRODUCT将其参数相乘
PRODUCT(number1,number2,….)
Product(A1,A2)=A1A2
Product(A1:A3,C1:C3)=A1A2A3C1C2C3
27、SUMPRODUCT返回相应的区域或数组乘积的和
sumproduct(数据1，数据2，……，数据30)
sumproduct(A1:A3,C1:C3)=A1C1+A2C2+A3C3
sumproduct(A2:A6,B2:B6,C2:C6)= A2B2C2+A3B3C3+A4B4C4+A5B5C5+A6B6C6
注意：1、引用的数据区域大小不一致导致计算错误，返回值为#VALUE!
2、数据区域有文本，计算中系统默认文本值为0，eg：E5是文本KL，则A5E5=0
28、LARGE返回数据集中第k个最大值
Large(array，k)
array为需要找到第K个最大值的数组或数字型数据区域,
K为返回的数据在数组或数据区域里的位置。
可以使用LARGE得到第一名、第二名或第三名的得分
29、SMALL返回数据集中第k个最小值
Small(array，k)与large相反
30、ROW返回引用的行号
Row(reference)
31、COLUMN返回指定单元格引用的列号
Column (reference)
32、NOW返回当前日期和时间的序列号
Now()
33、TODAY返回当前日期的序列号
Today()
34、YEAR(取一个日期的年份)
返回某日期对应的年份。返回值为1900到9999之间的整数。
35、MONTH(取一个日期的月份)
返回以序列号表示的日期中的月份。月份是介于1到12之间的整数。
36、DAY(取一个日期的天数)
返回以序列号表示的某日期的天数，用整数1到31表示
37、DATE其作用是将提取的数字变为日期格式进行显示。
Date(year,month,day)
38、RANK返回某数字在一列数字中相对于其他数值的大小排名
Rank(number,ref,[order])
如果order为0或省略,Excel 对数字的排名是基于ref降序排列而来
如果order为1，Excel 对数字的排位是基于ref 升序排列而来
注意：“区域”中用绝对引用
39、IF函数如果条件为真则公式结果为结果1，否则为结果
IF(条件，结果1，结果2)
函数 IF 可以嵌套七层，用 value_if_false 及 value_if_true 参数可以构造复杂的检测条件。
根据身份证号码用Mid、Mod和IF函数的综合使用判断男、女：
1)取出身份证中出生年月日，18位身份证号：第7—10位为出生年份(四位数)，第11、12位为出生月份，第13、14份为出生日期，第17位代表性别，奇数为男，偶数为女。
操作步骤：先用函数“=MID(身份证单元格，7，8)”取出年月日，然后选中“出生日期”列中的E6：E17单元格，按Ctrl+C进行复制，然后粘贴，选择“选择性粘贴”命令，对话框中选“数值”，然后“确定”。继续选中“出生日期”列中的E6：E17单元格，选择“数据”》“分列”命令，“文本分列向导-3步骤之1”对话框中选“固定宽度”，单击“下一步”， “文本分列向导-3步骤之2”对话框中单击“下一步”， “文本分列向导-3步骤之2”对话框中，在“列数据格式”栏中选中“日期”项，并在右边的下拉列表中选择“YMD”项，单击“完成”。
注：数据分列前，应先将公式计算结果进行“选择性粘贴”。
2)设性别：
IF(MOD(MID(身份证单元格，17，1)，2)=0，“女”，“男”)，如果除以2所得到的余数是0，则结果显示为“女”，否则显示为“男”。
注：男，女两侧的双引号要用英文标点符号状态。
通过身份证号的年计算年龄：
＝YEAR(NOW())－MID(身份证单元格，7，4)
通过出生日期计算年龄：
1步：先取身份证号中的出生日期，MID(身份证单元格，7，8)，然后将其数据>分列，设置为日期格式
2步：＝(today( )－出生日期单元格)/365
39、SUBTOTAL返回列表或数据库中的分类汇总
SUBTOTAL(function_num,ref1,ref2, ...)
Function_num 为 1 到 11(包含隐藏值)或 101 到 111(忽略隐藏值)之间的数字，指定使用何种函数在列表中进行分类汇总计算。ref1……refn参数为要对其进行分类汇总计算的第1至29个命名区域或引用。必须是对单元格区域的引用。

40、VLOOKUP是一个查找函数，给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找到的值。
语法：VLOOKUP(查找目标，查找范围，返回值的列数，精确OR模糊查找)
例：将表2中的职称调入表一中

40、FREQUENCY这是一个专门用于统计某个区域中数据的频率分布函数。
FREQUENCY(data_array,bins_array)
data_array为一数组或对一组数值的引用，用来计算频率。bins_array为一数组或对数组区域的引用，设定对data_array进行频率计算的分段点。
注：1、先要选中存放结果的单元格区域，再输入函数。
2、当输入函数后，要按快捷键Ctrl+Shift+Enter确认。

41、TRIMMEAN函数返回数据集的内部平均值，TRIMMEAN函数先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。当希望在分析中剔除一部分数据的计算时，可以使用TRIMMEAN函数。
TRIMMEAN(array, percent)
TRIMMEAN函数具有下列参数：
　第一，Array：需要进行整理并求平均值的数组或数值区域。 
　第二，Percent：计算时所要除去的数据点的比例，例如，如果 percent = 0.2，在 20 个数据点的集合中，就要除去4个数据点(20×0.2)：即头部除去2个，尾部除去2个。(除去的数量在总数量中所占的百分比，如，6个评委，去掉一个最高分和一个最低分，则Percent参数为2/6)
除掉最高分和最低分，求剩下的平均分：
方法一：
方法二：
TRIMMEAN函数实例：评委打分计算最后得分
　　实例说明:在跳高技能竞技赛中，10位评委分别为前三名的选手打分，计算出选手的最后得分。详见下图。

　　第一步，选中B13单元格，在编辑栏输入公式：=TRIMMEAN(B2:B11,0.2)，确定后，即可得到评委为戴品选手的打分，最后得分为9.20。
　　第二步，选中B13单元格，向右复制公式，就可以得到评委为其余两位选手的打分，最后得分分别为：8.85和9.05。
42、index(查找函数)返回数组中指定的单元格或单元格数组的数值。
Index(Array,Row_num,Column_num)

单元格区域/数组 行号 列号
如：Index(B3:D6,4,3)是指返回B3：D6数据区域中第4行第3列单元格的内容。
43、MATCH函数返回指定数值在指定数组区域中的位置
MATCH(lookup_value, lookup_array, match_type)
lookup_value:需要在数据表(lookup_array)中查找的值。可以为数值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用。可以包含通配符、星号 (*) 和问号 (?)。星号可以匹配任何字符序列;问号可以匹配单个字符。
lookup_array:可能包含有所要查找数值的连续的单元格区域，区域必须是某一行或某一列，即必须为一维数据，引用的查找区域是一维数组。
match_type:表示查询的指定方式，用数字-1、0或者1表示，match_type省略相当于match_type为1的情况。
为1时，查找小于或等于lookup_value的最大数值在lookup_array中的位置，lookup_array必须按升序排列:否则，当遇到比lookup_value更大的值时，即时终止查找并返回此值之前小于或等于lookup_value的数值的位置。如果lookup_array的值均小于或等于lookup_value，则返回数组最后一个值的位置;如果lookup_array的值均大于lookup_value，则返回#N/A。
为0时，查找等于lookup_value的第一个数值，lookup_array按任意顺序排列:
为-1时，查找大于或等于lookup_value的最小数值在lookup_array中的位置，lookup_array必须按降序排列;否则，当遇到比lookup_value更小的值时，即时终止查找并返回此值之前大于或等于lookup_value的数值的位置。如果lookup_array的值均大于或等于lookup_value，则返回数组最后一个值的位置;如果lookup_array的值均小于lookup_value，则返回#N/A。
match(5.5,A1:A9,1)=5返回小于或等于5.5的最大数值在A1:A9中的位置。返回值5。
match(0.5,A1:A9,1)=#N/A返回小于或等于0.5的最大数值在A1:A9中的位置。因为数组中所有值都大于查找值，所以返回错误。
match(10,A1:A9,1)=9返回小于或等于10的最大数值在A1:A9中的位置。因为数组中所有值都小于查找值，所以返回最后一个值的位置。
match_type = -1时，情况与上述类似。
match_type = 0 时，为精确匹配。返回数组中第一个相同的值的位置，当数组中没有lookup_value时，返回错误。
44、ISNUMBER检测一个数据是否为数值，是，则返回“TRUE”，不是，则返回“FALSE”。
经常与if函数套用，判断真假

45、RANDBETWEEN反回一个介于指定数字之间的随机数
Randbetween(bottom，top)