【办公应用】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查看精彩内容！

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)

效果图

效果图

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

（二）数学函数

(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-飞沙  阅读(1204)  评论(0)  编辑  收藏  举报