当前所在位置: 首页 > 数码科技 > 正文

常用的excel表格制作公式有哪些

2022-12-04 互联网 【 字体:

  在Excel工作表中,有一个非常重要的工具,那就是函数公式,如果要全部掌握,几乎是不可能的。但是对于一些常用的公式则必须掌握,那么常用的excel表格制作公式有哪些?下面小编就把常用的excel表格制作公式分享给大家。

  工具/原料

  系统版本:windows10系统

  品牌型号:联想ThinkPad

  方法一、常用的excel表格制作公式有哪些的分享

  1、Excel工作表函数:求和类。

  (一)Sum。

  功能:计算指定的单元格区域中所有数值的和。

  语法结构:=Sum(值1,值2……值N)。

  目的:计算总“月薪”。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=SUM(1*G3:G12),并用Ctrl+Shift+Enter填充。

  解读:

  如果直接用Sum函数进行求和,结果为0,究其原因就在于“月薪”为文本型的数值,如果不想调整数据类型,可以给每个参数乘以1将其强制转换为数值类型,然后用Sum函数进行求和。

  (二)Sumif。

  功能:对满足条件的单元格求和,即单条件求和。

  语法结构:=Sumif(条件范围,条件,[求和范围]),当“条件范围”和“求和范围”相同时,可以省略“求和范围”。

  目的:根据“性别”计算总“月薪”。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=SUMIF(D3:D12,J3,G3:G12)。

  解读:

  由于“条件范围”和“求和范围”不相同,所以不能省略参数“求和范围”。

  (三)Sumifs。

  功能:对一组给定条件指定的单元格求和。

  语法结构:=Sumifs(求和范围,条件1范围,条件1,条件2范围,条件2……)

  目的:根据“性别”统计相应“学历”下的总“月薪”。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=SUMIFS(G3:G12,D3:D12,J3,F3:F12,K3)。

  解读:

  参数“条件范围”和“条件”必须成对出现,否则公式无法正确执行!

  2、、Excel工作表函数:判断类。

  (一)If。

  功能:判断是否满足某个条件,如果满足返回一个值,如果不满足则返回另一个值。

  语法结构:=If(判断条件,条件为真时的返回值,条件为假时的返回值)。

  目的:判断“月薪”的档次,如果≥4000,则返回“高”,如果≥3000,则返回“中”,否则返回“低”。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=IF(G3>=4000,"高",IF(G3>=3000,"中",IF(G3<3000,"低")))。

  解读:

  IF函数除了单独判断外,还可以嵌套使用,但多级嵌套时,需要理清逻辑关系,否则容易出错!

  (二)Ifs。

  功能:判断是否满足一个或多个条件并返回与第一个TRUE条件对应的值。

  语法结构:=Ifs(条件1,返回值1,条件2,返回值2……)。

  目的:判断“月薪”的档次,如果≥4000,则返回“高”,如果≥3000,则返回“中”,否则返回“低”。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=IFS(G3>=4000,"高",G3>=3000,"中",G3<3000,"低")。

  解读:

  参数中的“条件”和“返回值”必须成对出现,但该函数仅能应用于16及以上版本的Excel中,在WPS高版本中也可以使用哦!

  3、Excel工作表函数:查找类。

  (一)VLOOKUP。

  功能:搜索表区域首列满足条件的元素,确定待检索单元格在区域中的的序号,再进一步返回选定单元格的值。

  语法结构:=Vlookup(查询值,数据范围,返回值列数,查询模式),查询模式分为精准查询和模糊查询。

  目的:根据“员工姓名”查询对应的“月薪”。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=VLOOKUP(J3,B3:G12,6,0)。

  解读:

  参数“返回值列数”要根据“数据范围”来确定,是返回值所在的的相对列数。

  (二)Lookup。

  功能:从单行或单列或单数组中查找一个值。

  Lookup函数具有两种语法结构:向量形式和数组形式。

  1、向量形式。

  功能:从单行或单列中查找查找指定的值,返回第二个单行或单列中相同位置的值。

  语法结构:=Lookup(查找值,查找值所在范围,[返回值所在范围]),当“查找值所在范围”和“返回值所在范围”相同时,可以省略“返回值所在范围”。

  目的:根据“员工姓名”查询对应的“月薪”。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  1、以“员工姓名”为主要关键字进行升序排序。

  2、在目标单元格中输入公式:=LOOKUP(J3,B3:B12,G3:G12)。

  解读:

  在使用Lookup函数查询数据时,首次要以“查询值”为主要关键字进行升序排序,否则无法得到正确的结果。

  2、数组形式。

  功能:从指定的范围第一列或第一行中查询指定的值,返回指定范围中最后一列或最后一行对应位置上的值。

  语法:=Lookup(查找值,查询范围)。

  重点解读:

  从“功能”中可以看出,Lookup函数的数组形式,查找值必须在查询范围的第一列或第一行中,返回的值必须是查询范围的最后一列或最后一行对应的值。即:查找值和返回值在查询范围的“两端”。

  目的:根据“员工姓名”查询对应的“月薪”。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  1、以“员工姓名”为主要关键字进行升序排序。

  2、在目标单元格中输入公式:=LOOKUP(J3,B3:G12)。

  解读:

  查询值必须在数据范围的第一列,返回值必须在数据范围得最后一列。

  3、优化形式。

  目的:根据“员工姓名”查询对应的“月薪”。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=LOOKUP(1,0/(B3:B12=J3),G3:G12)。

  解读:

  “优化形式”其本质还是向量形式,但在此必须了解Lookup函数的一个特定,就是当查询不到指定的值时,会自动向下匹配,原则为小于当前值的最大值。如果公式中的条件不成立,则返回错误值,如果公式成立,则返回0,小于查询值的最大值为0,所以返回相应位置的值。

  4、Excel工作表函数:统计类。

  (一)countif。

  功能:计算指定区域中的满足条件的单元格数量,即单条件计数。

  语法结构:=Countif(条件范围,条件)。

  目的:计算“月薪”在指定范围内的人数。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=COUNTIF(G3:G12,">"&J3)。

  解读:

  条件计数函数除了Countif函数外,还有多条件计数函数Countifs。

  (二)averageifs。

  功能:查找一组给定条件指定的单元格的算术平均值。

  语法结构:=Averageifs(数值范围,条件1范围,条件1,条件2范围,条件2……)

  目的:根据“性别”统计相应“学历”下的平均“月薪”。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=AVERAGEIFS(G3:G12,D3:D12,J3,F3:F12,K3)。

  解读:

  参数“条件范围”和“条件”必须成对出现。

  5、Excel工作表函数:提取类。

  (一)Left。

  功能:从一个字符串中的第一个字符开始返回指定个数的字符。

  语法结构:=Left(字符串,[字符长度]),当省略“字符长度”时,默认值为1。

  目的:提取“员工姓名”中的“姓”。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=LEFT(B3,1)。

  解读:

  也可以使用公式:=LEFT(B3)。

  (二)Mid。

  功能:从指定字符串中的指定位置起返回指定长度的字符。

  语法结构:=Mid(字符串,开始位置,字符长度)。

  目的:返回“月薪”中的第2、3位。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=MID(G3,2,2)。

  6、Excel工作表函数:日期类。

  (一)datedif。

  功能:以指定的方式计算两个日期之间的差值。

  语法结构:=Datedif(开始日期,结束日期,统计方式),常见的统计方式有“Y”、“M”、“D”,即“年”、“月”、“日”。

  目的:计算距离2021年元旦的天数。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=DATEDIF(TODAY(),"2021-1-1","d")。

  解读:

  用Today函数获取当前日期,计算距离2021年1月1日的天数。

  (二)Days。

  功能:返回两个日期之间的天数。

  语法结构:=Days(结束日期,开始日期)。

  目的:计算距离2021年元旦的天数。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=DAYS("2021-1-1",TODAY())。

  解读:

  Days函数的参数顺序为“结束日期”、“开始日期”,而并不是“开始日期”、“结束日期”,和Datedif函数要区别使用!

  7、Excel工作表函数:数字处理类。

  (一)Round。

  功能:按指定的位数对数值四舍五入。

  语法结构:=Round(值或单元格引用,小数位数)。

  目的:对“月薪”四舍五入后保留2位小数。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=ROUND(G3,2)。

  (二)Randbetween。

  功能:返回介于指定的数值之间的随机值。

  语法结构:=Randbetween(下限值,上限值)。

  目的:生成1000至2000之间的随机值。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=RANDBETWEEN(1000,2000)。

  解读:

  如果要更改随机值,按F9即可。

  8、Excel 工作表函数:数据库函数。

  (一)Dsum。

  功能:求满足给定条件的数据库中记录的字段(列)数据的和。

  语法结构:=Dsum(数据库区域,返回值所在的相对列数(列标题的相对引用、列标题),条件区域)。

  注意事项:

  1、参数“数据库区域”和“条件区域”必须包含有效的列标题。

  2、第二个参数用“列标题”作为返回依据时,其值必须包含在""(英文双引号)中,如“月薪”、“婚姻”等。

  目的:根据“性别”统计“月薪”。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=DSUM(B2:G12,"月薪",J2:J3)。

  (二)Dget。

  功能:从数据库中提取符合指定条件且唯一存在的记录。

  语法结构:=Dget(数据库区域,返回值所在的相对列数(列标题的相对引用、列标题),条件区域)。

  注意事项:

  1、参数“数据库区域”和“条件区域”必须包含有效的列标题。

  2、第二个参数用“列标题”作为返回依据时,其值必须包含在""(英文双引号)中,如“月薪”、“婚姻”等。

  目的:根据“员工姓名”查询对应的“月薪”。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=DGET(B2:G12,"月薪",J2:J3)。

  9、Excel工作表函数:其他类。

  (一)Len。

  功能:返回文本字符串中的文本个数。

  语法结构:=Len(值或单元格引用)。

  目的:计算“员工姓名”的长度。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=LEN(B3)。

  (二)Cell。

  功能:返回引用中第一个单元格的格式,位置或内容的有关信息。

  语法结构:=Cell(返回类型,[单元格区域])。

  目的:显示当前工作表的文件路径。

Excel工作表中最常用的9类21个公式,动图演示,中文解读

  方法:

  在目标单元格中输入公式:=CELL("filename")。

  总结

  1、Excel工作表函数:求和类。

  2、Excel工作表函数:判断类。

  3、Excel工作表函数:查找类。

  4、Excel工作表函数:统计类。

  5、Excel工作表函数:提取类。

  6、Excel工作表函数:日期类。

  7、Excel工作表函数:数字处理类。

  8、Excel 工作表函数:数据库函数。

  9、Excel工作表函数:其他类。

阅读全文
相关推荐

男子越狱29年被抓时在学校当保安 担任保安队长

男子越狱29年被抓时在学校当保安 担任保安队长
12月29日,湖南攸县警方通报一名越狱潜逃29年的逃犯被抓获,1991年何某青因犯强奸罪被判刑六年,1993年在攸县网岭监狱服刑期间脱逃,被列为网上在逃人员。2022年12月,攸县民警获得线索,广州市某学校发现疑似何某青踪迹,警方赶赴广州研判确认男子正是潜逃2

男生考研分到高中母校被班主任监考 幸运又惊喜

男生考研分到高中母校被班主任监考 幸运又惊喜
高考是人生的第一大考,但除了高考外,考研也备受学生重视。为了不影响考试,能够在考场上正常发挥,家长们鼎力相助,学生们也意识到了竞争的激烈以及学历的重要性。经过努力学习,没日没夜的备考,考研党们也终于迎来了研究生考试初试,不知道各位考生是否做好了充分的准备,相信

贝利去世 直击医院送别现场 大批球迷前往医院悼念

贝利去世 直击医院送别现场 大批球迷前往医院悼念
埃德松·阿兰特斯·多·纳西门托,又名贝利,出生于巴西特雷斯科拉松伊斯,巴西著名足球运动员,司职前锋,曾被国际足联誉为“球王”。1957年,贝利入选巴西国家队,代表巴西队参赛92场打进77球,夺得1958、1962、1970年三届世界杯冠军,为巴西永久保留雷米特

美国动物园将归还大熊猫丫丫和乐乐 结束20年的租借期

美国动物园将归还大熊猫丫丫和乐乐 结束20年的租借期
据路透社报道,美国田纳西州孟菲斯动物园当地时间12月21日宣布,将把旅美大熊猫丫丫和乐乐归还中国,结束20年的租借期。在宣布这一消息之前,保护动物组织批评孟菲斯动物园对大熊猫“丫丫”和“乐乐”提供的照顾不足。动物保护人士对将大熊猫归还中国表示支持,认为两只大熊

南极洲65%物种面临消失风险 帝企鹅风险居首位

南极洲65%物种面临消失风险 帝企鹅风险居首位
据美国有线电视新闻网(CNN)当地时间12月22日报道,一项发表在《科学公共图书馆·生物学》杂志上的研究认为,如果不能控制导致全球变暖的化石燃料的排放,南极洲65%的本土物种可能会在21世纪末消失,其中帝企鹅面临的消失风险居首位。最新数据表明,南极洲海冰目前的

侃爷前经理人称侃爷已失联数周 无法取得联系

侃爷前经理人称侃爷已失联数周 无法取得联系
12月27日,据外媒报道,侃爷前业务经理在采访时称,侃爷已经失联数周,无法与他取得联系,迄今为止没有任何消息。据悉,侃爷曾在2020年竞选美国总统,但最终败选,并展望2024年再次参加选举。据报道称,侃爷正在计划2024年的总统竞选,并发布了一段视频,与此同时

沙特队长等两员大将伤退告别世界杯 爆冷也付出了代价

沙特队长等两员大将伤退告别世界杯 爆冷也付出了代价
北京时间11月22日,世界杯小组赛C组第1轮,沙特爆冷2-1逆转战胜阿根廷。11月24日,从卡塔尔多哈传来消息,在比赛中受伤的两名沙特队球员沙赫拉尼、法拉杰已确认退出本届世界杯,这对于沙特队余下赛事的竞争,无疑造成了不利影响。11月22日,在当场比赛的最后时刻

内马尔出场79分钟被侵犯9次 退场后伤心不已

内马尔出场79分钟被侵犯9次 退场后伤心不已
北京时间11月25号,在2022世界杯小组赛G组的比赛中,巴西队以2-0的成绩战胜了塞尔维亚队,拿下了卡塔尔世界杯的开门红。在这场比赛中,巴西队的当家球星内马尔受伤被换下,下场后,内马尔在替补席上拉起球衣掩面落泪。内马尔·达·席尔瓦·桑托斯·儒尼奥尔(Neym

日本动漫神预言 足球小将表情包疯传 引起球迷们热议

日本动漫神预言 足球小将表情包疯传 引起球迷们热议
北京时间11月23日,卡塔尔世界杯小组赛继续进行,德国遭遇爆冷。在上半场,德国凭借着点球以1-0的成绩领先,但是到了下半场,德国队浪费了多次机会,日本实现逆转,球队在10分钟之内连进两球,最终球队以2-1战胜德国。在赛前,社交媒体上的一张日本动漫《足球小将》的

世界杯又一队出局 克罗地亚4:1大比分淘汰加拿大

世界杯又一队出局 克罗地亚4:1大比分淘汰加拿大
北京时间11月27日晚-28日凌晨,世界杯小组赛第二轮又一个比赛日结束了,加拿大队成为了继卡塔尔队之后又一提前出局的球队,德国队终于1-1拿到积分了,日本队遗憾输球,被誉为“欧洲红魔”的比利时更是轰然塌房。日本队vs哥斯达黎加:0-1,遗憾败北。首轮日本队以2
本文Tag