【办公应用】Excel常用公式及常用知识点总结，超详细！！！

  1. 原文链接：https://www.cnblogs.com/MrFlySand/p/14394314.html
  2. 文章标签：常用函数公式、常用知识点、常见考点、常见纸质考点注意事项
  3. WPS办公应用职业技能等级证书认证考试平台：http://ks.wpsx.com.cn/
  4. 1+X成绩查询及职业技能等级证书下载：https://www.cnblogs.com/MrFlySand/p/18085760
  5. 文章发表日期：2021年2月9日
  6. 文章更新日期：2024年4月4日

文本格式

日期类

  1. m"月"d"日" h:mm [$-804]aaaa;@：12月21日 9:00 星期四（在输入时只需要输入日期和时刻即可，不用输入星期，excel会自动计算当前日期）
  2. m"月"d"日"(aaaa) h:mm;@：12月21日(星期四) 9:00（知识点：常常用于日常工作安排，星期能更加直观的体现这周的工作的时间）
    • aaaa：表示星期
      image

Excel函数 ！！！

前言（说明）

  1. 在excel中函数一定要使用英文的标点符号。
  2. 点击下图中的fx可以搜索查看函数的功能。在输入表达式时按tab可以自动添加新的语句内容。
    image
  3. @纸质考试的同学，在记忆公式功能的同时也要记忆每个公式中的参数位置、参数形式，以及是否要写双引号。如下图：sumif的第1个参数应该是“区域”，不是“求和区域”，同时还要注意写双引号。
    image
  4. 各公式的参数位置，及常见重点记忆
    1. countif(区域,条件)
    2. countifs(区域1,条件1,区域2,条件2)
    3. sumif(条件区域,条件,求和区域)
    4. sumifs(求和区域,条件1,条件区域1,条件2,条件区域2)
    5. averageif(条件区域, 条件, 计算区域)
    6. averageifs(计算区域, 条件区域1,条件1, 条件区域2,条件2)
    7. lookup(查找值,查找区域,横向对应区域)
    8. vlookup(查找目标,查找范围,返回值的列数,0:精确匹配/1或省略:大致匹配) <0是一个圈，在圈内精确匹配；1是一条路大致匹配>
    9. rank(查找数字,区域范围,0或省略:降序/1:升序) <0小降，1大升>
    10. 奇数为男，偶数为女 <1先男，2后女>

常用函数

（一）常用函数详细说明

(1) if

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

(2) count

① count统计数字个数
  1. =count(value1，value2，…)
  2. =count(区域)统计一个区域的数字个数
② countif 计算某个区域中满足给定条件的单元格数目
  1. countif(区域,条件)，公式：=countif(range,criteria)
    1. range区域;
    2. criteria判断区域中符合的条件。
  2. =countif(a1:a4,"苹果")统计a1:a4区域中有苹果字符的单元格数量。
  3. =COUNTIF(A1:A10,"")统计a1:a10区域中没有任何字符的单元格数量。img
③ countifs：将条件应用于跨多个区域的单元格，并计算符合所有条件的个数
  1. countifs(区域1,条件1,区域2,条件2)，公式：=countifs(criteria_range1, criteria1, [criteria_range2, criteria2]…)
    1. range1区域，criteria1判断区域1中的条件，条件形式可以为数字、表达式或文本，例如，条件可以表示为48、"48"、">48" 或 "广州"
    2. range2区域，criteria1判断区域2中的条件。
  2. =countifs(a1:a4,"苹果",b1:b4,">60")在单元格a1:a4统计苹果的数量，并且在单元格统计b1:b4统计数量>60的个数。
④ counta计算区域中不为空的单元格的个数
  1. =counta(value1,value2,...)
    img
⑤ countblank计算指定单元格区域中空白单元格的个数
  1. =countblank(value1,value2,...)
    img

(3) sum

① sum 求和
  1. =sum(number1,number2,…) 每个参数可以使区域、单元格引用、数组、常数、公式或另一个函数的结果
② sumif
  1. 条件求和函数：sumif(条件区域,条件,求和区域)，公式：sumif(range, criteria, [sum_range])，返回某个区域内满足给定条件的所有单元格的平均值(算术平均值)
  2. 如：=sumif(b2:b25, ">5")让数列b2到b25中大于5的数求和
  3. 如：=sumif(c2:c10, "女", e2:e10)将c2：c10区域中单元格为“女”的所对应的e2:e10区域中满足该条件的单元格求和。
  4. =sumif(a1:a11,"苹果",b1:b11)在单元格a1:a30判断条件为苹果，将有苹果在b1:b30所对应的横排数量相加。
    img
③ sumifs
  1. sumifs(求和区域,条件1,条件区域1,条件2,条件区域2)，公式：=sumifs(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
  2. =sumifs(b1:b30,a1:a30,"苹果",b1:b30,">90")在单元格a1:a30是苹果，b1:b30数量>90的,所对应在b1:b30相加。
视频推荐
  1. 【Excel里的三大求和函数SUM / SUMIF / SUMIFS，一个视频教会你-哔哩哔哩】 https://b23.tv/zTPdZd9
  2. 【Sumif 和 Sumifs 函数求和 , 保姆级讲解-哔哩哔哩】 https://b23.tv/sk07ZGF
  3. 【sumif函数怎么用？超详细的原理和技巧讲解！一定要收藏哦~-哔哩哔哩】 https://b23.tv/W6Zg1Hb

(4) average

① average求平均值
  1. =average(number1,number2, …)
② averageif
  1. averageif(条件区域, 条件, 计算区域)，公式：averageif(range, criteria, [average_range])range判断条件区域；criteria条件；要计算的平均值的区域
  2. =averageif(a1:a4,"<=100")求a1:a4区域小于100的平均值
  3. =averageif(a1:a4,"<=100",b1:b4)求a1:a4区域小于100的行,计算在b1:b4所横向对应的平均值
③ averageifs
  1. averageifs(计算区域, 条件区域1, 条件1, 条件区域2,条件2, ...)，公式：=averageifs(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
    1. average_rangey：计算平均值的区域;
    2. 判断条件区域1;条件1;
    3. 判断条件区域2;条件2。

(5) lookup

① lookup
  1. lookup(查找值,查找区域,横向对应区域)，公式：=lookup(lookup_value, lookup_vector, [result_vector])是一个查找函数，给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找到的值。
  2. =lookup("mrflysand",a1:a26,b1:b26)在a1:a26查找单词mrflysand，返回b1:b26横向所对应的值
② vlookup
  1. vlookup(查找目标,查找范围,返回值的列数,0:精确匹配/1或省略:大致匹配)，语法：=vlookup(lookup_value, table_array, col_index_num, [range_lookup])
    1. range_lookup：指定在查找时是要求精确匹配，还是大致匹配。如果为FALSE，精确匹配。如果为TRUE或忽略，大致匹配
  2. =vlookup("a",a1:d10,2)在a1:a10区域中查找a,返回第2列的值

(6) rank

  1. rank(查找数字,区域范围,0或省略:降序/1:升序)公式：=rank(number,ref,[order])返回某数字在一列数字中相对于其他数值的大小排名
    1. number：需要排名的数值
    2. ref：区域范围
    3. order：order为0或省略，降序(被查找的数从大到小排列，序号顺序从1~n)；order为1，升序(被查找的数从小到大排列，序号顺序从1~n)。
      img

(7) 逻辑函数：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),"优","一般")

(8) isnumber

  1. isnumber检测一个数据是否为数值。是，则返回“true”；不是，则返回“false”。(经常与if函数套用，判断真假)

(9) rand

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

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

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

(10) max和min

① max 求最大值
  1. =max(number1，number2，…)
② 求最小值
  1. =min(number1，number2，…)

(11) large和small

① large返回数据集中第k个最大值
  1. =large(array,k)
    1. array为需要找到第k个最大值的数组或数字型数据区域,
    2. k为返回的数据在数组或数据区域里的位置。可以使用large得到第一名、第二名或第三名的得分
② small返回数据集中第k个最小值
  1. =small(array，k)与large相反

(12) 返回引用的行号/列号

① row返回引用的行号
  1. =row(reference)
② column返回指定单元格引用的列号
  1. =column (reference)
③ index(查找函数)返回数组中指定的单元格或单元格数组的数值。
  1. =index(array,row_num,column_num)单元格区域/数组 行号 列号

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

④ match函数返回指定数值在指定数组区域中的位置
  1. =match(lookup_value, lookup_array, match_type)
    1. lookup_value：需要在数据表(lookup_array)中查找的值。可以为数值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用。可以包含通配符、星号(*)和问号(?)。星号可以匹配任何字符序列;问号可以匹配单个字符。
    2. lookup_array：可能包含有所要查找数值的连续的单元格区域，区域必须是某一行或某一列，即必须为一维数据，引用的查找区域是一维数组。
    3. match_type：表示查询的指定方式，用数字-1、0、1表示，match_type省略相当于match_type为1的情况。
      1. 为1时，查找小于或等于lookup_value的最大数值在lookup_array中的位置，lookup_array必须按升序排列:否则，当遇到比lookup_value更大的值时，即时终止查找并返回此值之前小于或等于lookup_value的数值的位置。如果lookup_array的值均小于或等于lookup_value，则返回数组最后一个值的位置;如果lookup_array的值均大于lookup_value，则返回#n/a。
      2. 为0时，查找等于lookup_value的第一个数值，lookup_array按任意顺序排列。
      3. 为-1时，查找大于或等于lookup_value的最小数值在lookup_array中的位置，lookup_array必须按降序排列;否则，当遇到比lookup_value更小的值时，即时终止查找并返回此值之前大于或等于lookup_value的数值的位置。如果lookup_array的值均大于或等于lookup_value，则返回数组最后一个值的位置;如果lookup_array的值均小于lookup_value，则返回#n/a。
  2. match(5.5,a1:a9,1)=5返回小于或等于5.5的最大数值在a1:a9中的位置。返回值5。
  3. match(0.5,a1:a9,1)=#n/a返回小于或等于0.5的最大数值在a1:a9中的位置。因为数组中所有值都大于查找值，所以返回错误。
  4. match(10,a1:a9,1)=9返回小于或等于10的最大数值在a1:a9中的位置。因为数组中所有值都小于查找值，所以返回最后一个值的位置。
  5. match_type = -1时，情况与上述类似。
  6. match_type = 0时，为精确匹配。返回数组中第一个相同的值的位置，当数组中没有lookup_value时，返回错误。

(13) ranspose()转置表格区域

【文章推荐】excel表格transpose()函数详解 https://www.cnblogs.com/MrFlySand/p/16878837.html

(14) 截取字符函数

① len
  1. =len(text)：返回指定字符串的长度
  2. 说明：每个ascii字符的长度为1，每个汉字的长度也为1
② left
  1. =left(text,num_chars)：从文本字符串的第一个字符开始(从左边第一个开始)，返回指定个数的字符。
    1. text代表用来截取的单元格内容
    2. num_chars代表从左开始截取的字符数
  2. 如=left("微信公众号：小知识酷",5)的结果是“微信公众号”。
③ right
  1. right(text,num_chars)从文本字符串的第一个字符开始(从右边第一个开始)，返回指定个数的字符
    1. text代表用来截取的单元格内容
    2. num_chars代表从右开始截取的字符数
  2. 如=right("微信公众号：小知识酷",4)的结果是“小知识酷”。
⑤ mid
  1. =mid(text,start_num,num_chars)从文字串某一指定位置开始，返回指定长度的字符串。
    1. text：文本
    2. start_num：字符开始的位置，在公式中下标(位置)最小为1。
    3. num_chars：截取的字符长度，而不是范围区间（可以把下面的公式记下）
  2. 举例：mid(身份证号所在单元格,7,8)在身份证号中提取出生日期（年月日，如：20010510）
练习1：根据身份证号码用mid、mod和if函数的综合使用判断男、女
  1. 知识点(考试)：取出身份证中出生年月日，18位身份证号：第7—10位为出生年份(四位数)；第11、12位为出生月份；第13、14份为出生日期；第17位代表性别，奇数为男，偶数为女。
  2. 操作步骤：先用函数“=mid(身份证单元格，7，8)”取出年月日，然后选中“出生日期”列中的e6：e17单元格，按ctrl+c进行复制，然后粘贴，选择“选择性粘贴”命令，对话框中选“数值”，然后“确定”。继续选中“出生日期”列中的e6：e17单元格，选择“数据”》“分列”命令，“文本分列向导-3步骤之1”对话框中选“固定宽度”，单击“下一步”， “文本分列向导-3步骤之2”对话框中单击“下一步”， “文本分列向导-3步骤之2”对话框中，在“列数据格式”栏中选中“日期”项，并在右边的下拉列表中选择“ymd”项，单击“完成”。
    注：数据分列前，应先将公式计算结果进行“选择性粘贴”。
  3. 性别：
    =if(mod(mid(身份证单元格,17,1),2)=0,"女","男")，第17位数字如果除以2所得到的余数是0，则结果显示为“女”，否则显示为“男”。
    注：男，女两侧的双引号要用英文标点符号状态。
练习2：通过身份证号的年计算年龄：

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

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

(15) 日期函数

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

now()

② today返回当前日期的序列号

today()

③ year(取一个日期的年份)

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

④ month(取一个日期的月份)

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

⑤ day(取一个日期的天数)

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

⑥ date其作用是将提取的数字变为日期格式进行显示
  1. =date(year,month,day)
⑦ dateif
  1. =dateif(start date,end date,unit)：计算两个日期之间的天数、月数或年数。
    1. unit：Y/M/D任意填写一个，Y代表相隔多少年，M表示相隔多少月，D表示相隔多少天。
  2. =dateif("2010/2/5",NOW(),"Y")：计算2010/2/5到现在的时候一共过去多少年。

(16) 字母转数字&数字转字母

  1. =(SUBSTITUTE(ADDRESS(1,(COLUMN(INDIRECT("A"&1))),4),1,"")

(17)查找函数find

find
  1. 截取（）中里面的1个字符：=MID(B2,FIND("（",B2,1)+1,1)
    image
  2. 截取（）中里面的文本：=MID(A1,FIND("（",A1,1)+1,FIND("）",A1)-FIND("（",A1)-1)

(18)=FILTER

80 我说个数(Excel&数据分析)发布了一篇小红书笔记，快来看吧！ 😆 wUVLo2hXUzLt9iP 😆 http://xhslink.com/mloqEM，复制本条信息，打开【小红书】App查看精彩内容！

截取特定文本之间的文本（截取【】之间的文本）
  1. 公式：=MID(B2,FIND("【",B2,1)+1,FIND("】",B2,1)-FIND("【",B2,1)-1)
    image

【相关文章推荐】Excel公式：查找字符第n次出现的位置https://zh-cn.extendoffice.com/excel/formulas/excel-find-position-of-nth-occurrence-of-a-character.html

截取2个*之间的文本
  1. 使用技巧：当文本中有多个需要截取的内容时可以复制下面的内容到一个单元格中，然后用数据分列对￥进行分隔
  2. 提取一对*中的内容：MID(A2,FIND("|",SUBSTITUTE(A2,"*","|",1))+1,FIND("|",SUBSTITUTE(A2,"*","|",2))-FIND("|",SUBSTITUTE(A2,"*","|",1))-1)
  3. 提取多对*中的内容：
MID(A2,FIND("|",SUBSTITUTE(A2,"*","|",1))+1,FIND("|",SUBSTITUTE(A2,"*","|",2))-FIND("|",SUBSTITUTE(A2,"*","|",1))-1)
￥=MID(A2,FIND("|",SUBSTITUTE(A2,"*","|",3))+1,FIND("|",SUBSTITUTE(A2,"*","|",4))-FIND("|",SUBSTITUTE(A2,"*","|",3))-1)
￥=MID(A2,FIND("|",SUBSTITUTE(A2,"*","|",5))+1,FIND("|",SUBSTITUTE(A2,"*","|",6))-FIND("|",SUBSTITUTE(A2,"*","|",5))-1)
￥=MID(A2,FIND("|",SUBSTITUTE(A2,"*","|",7))+1,FIND("|",SUBSTITUTE(A2,"*","|",8))-FIND("|",SUBSTITUTE(A2,"*","|",7))-1)
￥=MID(A2,FIND("|",SUBSTITUTE(A2,"*","|",9))+1,FIND("|",SUBSTITUTE(A2,"*","|",10))-FIND("|",SUBSTITUTE(A2,"*","|",9))-1)
￥=MID(A2,FIND("|",SUBSTITUTE(A2,"*","|",11))+1,FIND("|",SUBSTITUTE(A2,"*","|",12))-FIND("|",SUBSTITUTE(A2,"*","|",11))-1)
￥=MID(A2,FIND("|",SUBSTITUTE(A2,"*","|",13))+1,FIND("|",SUBSTITUTE(A2,"*","|",14))-FIND("|",SUBSTITUTE(A2,"*","|",13))-1)

效果图

效果图

（二）数学函数

(1) round四舍五入函数，保留小数

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

(2) int取整函数

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

(3) sqrt平方根函数

sqrt(number)

(4) abs返回参数的绝对值

abs(number)

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

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

(6) sign返回数字的符号

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

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

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

(8) product将其参数相乘

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

(9) sumproduct返回相应的区域或数组乘积的和

  1. sumproduct(数据1，数据2，……，数据30)
  2. sumproduct(a1:a3,c1:c3)=a1c1+a2c2+a3c3
  3. sumproduct(a2:a6,b2:b6,c2:c6)= a2b2c2+a3b3c3+a4b4c4+a5b5c5+a6b6c6
注意：
  1. 引用的数据区域大小不一致导致计算错误，返回值为#value!
  2. 数据区域有文本，计算中系统默认文本值为0，eg：e5是文本kl，则a5e5=0

（三）其他函数

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

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

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

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

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

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

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

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

(4) trimmean

  1. trimmean函数返回数据集的内部平均值，trimmean函数先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。当希望在分析中剔除一部分数据的计算时，可以使用trimmean函数。
  2. 格式：trimmean(array, percent)
    1. array：需要进行整理并求平均值的数组或数值区域。
    2. 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。

按公式单词顺序填写格式
  1. countif(区域,条件)
  2. countifs(区域1,条件1,区域2,条件2)
  3. sumif(条件区域，条件，求和区域)
  4. sumifs(求和区域,条件1,条件区域1,条件2,条件区域2)
posted @ 2021-02-09 21:54  MrFlySand-飞沙  阅读(1564)  评论(0)  编辑  收藏  举报