Excel常用公式

原文链接：https://www.cnblogs.com/MrFlySand/p/14394314.html

文本格式

日期类

1. m"月"d"日" h:mm [$-804]aaaa;@：12月21日 9:00 星期四
2. m"月"d"日"(aaaa) h:mm;@：12月21日(星期四) 9:00（知识点：常常用于日常工作安排，星期能更加直观的体现这周的工作的时间）
   • aaaa：表示星期
     

函数

常用函数

rand

(1)rand()产生一个0~1之间的随机数

=rand() 返回大于等于0且小于1的平均分布随机数(计算结果可变)

(2)randbetween()

• randbetween(minnumber,maxnumber)，如=randbetween(50,100)产生一个50~100之间的数
• 随机小写字母：char(int(rand()*26)+97)
• 随机大写字母：char(int(rand()*26)+65)

max和min

(1)max 求最大值

max(number1，number2，…)

(2)求最小值

min(number1，number2，…)

large和small

(1)large返回数据集中第k个最大值

large(array，k)
array为需要找到第k个最大值的数组或数字型数据区域,
k为返回的数据在数组或数据区域里的位置。
可以使用large得到第一名、第二名或第三名的得分

(2)small返回数据集中第k个最小值

small(array，k)与large相反

if

1. =if(条件，结果1，结果2)：函数如果条件为真则公式结果为结果1，否则为结果2
2. 函数 if 可以嵌套七层，用 value_if_false 及 value_if_true 参数可以构造复杂的检测条件。

count

(1)count计算数字个数

count(value1，value2，…)

(2)countif 计算某个区域中满足给定条件的单元格数目

• countif(range,criteria):range区域;criteria判断区域中符合的条件。
• =countif(a1:a4,"苹果")统计a1:a4区域中有苹果的单元格数量。

(3)countifs：将条件应用于跨多个区域的单元格，并计算符合所有条件的个数

• countifs(criteria_range1, criteria1, [criteria_range2, criteria2]…):range1区域，criteria1判断区域1中的条件；:range2区域，criteria1判断区域2中的条件。
• =countifs(a1:a4,"苹果",b1:b4,">60")在单元格a1:a4统计苹果的数量，并且在单元格统计b1:b4统计数量>60的个数。

countifs(criteria_range1,criteria1,criteria_range2,criteria2,…)
criteria_range1为第一个需要计算其中满足某个条件的单元格数目的单元格区域(简称条件区域)，criteria1为第一个区域中将被计算在内的条件(简称条件)，其形式可以为数字、表达式或文本。例如，条件可以表示为 48、"48"、">48" 或 "广州"；　　同理，criteria_range2为第二个条件区域，criteria2为第二个条件，依次类推。最终结果为多个区域中满足所有条件的单元格个数。
其结果与dcounta函数一致

注：输入格式：

1. countifs(条件所在区域1，条件1，条件所在区域2，条件2，……)
2. sumifs(际求和区域，条件所在区域1，条件1，条件所在区域2，条件2，……)
3. averageifs(实际求平均值区域，条件所在区域1，条件1，条件所在区域2，条件2，……)

(4)counta计算区域中不为空的单元格的个数

counta(value1,value2,...)

(5)countblank计算指定单元格区域中空白单元格的个数

countblank (value1,value2,...)

sum

(1)sum 求和

sum(number1，number2，…)
每个参数可以使区域、单元格引用、数组、常数、公式或另一个函数的结果

(2)sumif条件求和函数：返回某个区域内满足给定条件的所有单元格的平均值(算术平均值)

sumif(区域，条件，求和区域)
如：=sumif(b2：b25，”>5”)让数列b2到b25中大于5的数求和
如：=sumif(c2：c10，”女”，e2：e10)将c2：c10区域中单元格为“女”的所对应的e2：e10区域中满足该条件的单元格求和。

• sumif(range, criteria, [sum_range]):range 必需,用于条件判断的单元格区域;criteria条件;range求和区域
• =sumif(a1:a30,"苹果",b1:b30):在单元格a1:a30判断是否有苹果,将有苹果在b1:b30所对应的横排数量相加。

(3)sumifs

• sumifs(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...):sum_range求和区域；判断条件区域1；条件1；判断条件区域2；条件2。

• =sumifs(b1:b30,a1:a30,"苹果",b1:b30,">90")在单元格a1:a30是苹果,b1:b30数量大于90的,所对应在b1:b30相加

• lookup(lookup_value, lookup_vector, [result_vector])lookup_value查找的值；查找区域；返回对应的列数的值

• =lookup("z",a1:a26,b1:b26)在a1:a26查找是z的表格，返回b1:b26横向所对应的值

• vlookup(lookup_value, table_array, col_index_num, [range_lookup])lookup_value查找的值;查找的区域;返回的列数;是否精准查找

• =vlookup("a",a1:d26,2)在a1:a26区域中查找a,返回第2列的值

average

(1)average求平均值

• =average(number1，number2，…)

(2)averageif

• averageif(range, criteria, [average_range])range判断条件区域；criteria条件；要计算的平均值的区域
• =averageif(a1:a4,"<=100")求a1:a4区域小于100的平均值
• =averageif(a1:a4,"<=100",b1:b4)求a1:a4区域小于100的行,计算在b1:b4所横向对应的平均值

(3)averageifs

• =averageifs(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
• average_rangey计算平均值的区域;
• 判断条件区域1;条件1;
• 判断条件区域2;条件2。

rank返回某数字在一列数字中相对于其他数值的大小排名

rank(number,ref,[order])
如果order为0或省略,excel 对数字的排名是基于ref降序排列而来
如果order为1，excel 对数字的排位是基于ref 升序排列而来
注意：“区域”中用绝对引用

vlookup是一个查找函数，给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找到的值。

语法：vlookup(查找目标，查找范围，返回值的列数，精确or模糊查找)

逻辑函数：and、not、or、true、false函数

1. and(x1,x2, …,x30)都为true时结果为ture，有一个是false则为false
2. or(x1, x2, …,x30)都为false时结果为false，有一个是ture则为ture
3. not(logical)取反

举例

1. and(true,true)=true，
2. or(true,true)=true
3. and(true,false,true, true)=false，or(true,false,true,true)=true。
4. 如果b1，b2，b3单元格中的值为 true，false，true，则and(b1:b3)=false，但or(b1:b3)=true。and(2,2+3=5)=true
5. if函数常常与逻辑函数的嵌套使用：如：当语文、数学、英语成绩都大于100分，则返回“优”，否则“一般”，公式：=if(and(b2>100,c2>100,d2>100),"优","一般")

isnumber检测一个数据是否为数值，是，则返回“true”，不是，则返回“false”。

经常与if函数套用，判断真假

返回引用的行号/列号

(1)row返回引用的行号

row(reference)

(2)column返回指定单元格引用的列号

column (reference)

(3)index(查找函数)返回数组中指定的单元格或单元格数组的数值。

=index(array,row_num,column_num)单元格区域/数组 行号 列号

如：index(b3:d6,4,3)是指返回b3：d6数据区域中第4行第3列单元格的内容。

(4)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时，返回错误。

dcounta对满足指定条件的数据库中记录字段(列)的非空单元格进行记数。

dcounta(database,field,criteria)
database是构成列表或数据库的单元格区域。数据库是相关数据的列表。field或是用双引号括住的列标签，或是表示该列在列表中位置的数字。criteria是包含指定条件的单元格区域。区域包括列标签及列标签下满足某个条件的单元格。

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

注：第二个参数field可写字段名也可写成列号，如“大写”可写成3

subtotal返回列表或数据库中的分类汇总

subtotal(function_num,ref1,ref2, ...)
function_num 为 1 到 11(包含隐藏值)或 101 到 111(忽略隐藏值)之间的数字，指定使用何种函数在列表中进行分类汇总计算。ref1……refn参数为要对其进行分类汇总计算的第1至29个命名区域或引用。必须是对单元格区域的引用。

frequency这是一个专门用于统计某个区域中数据的频率分布函数。

frequency(data_array,bins_array)
data_array为一数组或对一组数值的引用，用来计算频率。bins_array为一数组或对数组区域的引用，设定对data_array进行频率计算的分段点。
注：1、先要选中存放结果的单元格区域，再输入函数。
2、当输入函数后，要按快捷键ctrl+shift+enter确认。

trimmean

1. 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。

数学函数

round四舍五入函数

round(number,num_digits)：返回某个数字按指定位数舍入后的数字，如果num_digits省略则表示小数点后位数为0

int取整函数

int(number) 舍去小数点后面的数

sqrt平方根函数

sqrt(number)

abs返回参数的绝对值

abs(number)

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

1. mod(number,divisor)：number为被除数，divisor为除数。
2. 说明：如果divisor为零，返回错误值#div/0!

sign返回数字的符号

sign(number)：当数字为正数时返回1，为零时返回0，为负数时返回-1

sin返回角angle(弧度)的正弦值

sin(angle)
cos(angle)：返回角angle(弧度)的余弦值。
tan(数值)：返回角angle的正切值

product将其参数相乘

product(number1,number2,….)
product(a1,a2)=a1a2
product(a1:a3,c1:c3)=a1a2a3c1c2c3

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

截取字符函数

len

1. =len(text)：返回指定字符串的长度
2. 说明：每个ascii字符的长度为1，每个汉字的长度也为1

left

1. =left(text,num_chars)：从文本字符串的第一个字符开始(从左边第一个开始)，返回指定个数的字符。
2. text代表用来截取的单元格内容。num_chars代表从左开始截取的字符数

right从文本字符串的第一个字符开始(从右边第一个开始)，返回指定个数的字符

1. right (text,num_chars)
   text代表用来截取的单元格内容。num_chars代表从左开始截取的字符数

mid从文字串某一指定位置开始，返回指定长度的字符串。

1. =mid(text,start_num,num_chars)
2. 举例：mid(身份证号所在单元格,7,8)在身份证号中提取出生日期

练习1：根据身份证号码用mid、mod和if函数的综合使用判断男、女

1. 知识点(考试)：取出身份证中出生年月日，18位身份证号：第7—10位为出生年份(四位数)，第11、12位为出生月份，第13、14份为出生日期，第17位代表性别，奇数为男，偶数为女。
2. 年月日：=mid(身份证单元格，7，8)
3. 性别：
   =if(mod(mid(身份证单元格，17，1)，2)=0，“女”，“男”)，如果除以2所得到的余数是0，则结果显示为“女”，否则显示为“男”。
   注：男，女两侧的双引号要用英文标点符号状态。

练习2：通过身份证号的年计算年龄：

=year(now())-mid(身份证单元格，7，4)

通过出生日期计算年龄：
第1步：先取身份证号中的出生日期，=mid(身份证单元格,7,8)，然后将其数据>分列，设置为日期格式
第2步：`=(today()-出生日期单元格)/365

日期函数

now返回当前日期和时间的序列号

now()

today返回当前日期的序列号

today()

year(取一个日期的年份)

返回某日期对应的年份。返回值为1900到9999之间的整数。

month(取一个日期的月份)

返回以序列号表示的日期中的月份。月份是介于1到12之间的整数。

day(取一个日期的天数)

=day(now())：返回以序列号表示的某日期的天数，用整数1到31表示

date其作用是将提取的数字变为日期格式进行显示。

=date(year,month,day)