办公问答网

 找回密码
 立即注册
搜索
热搜: 活动 交友 discuz
查看: 118|回复: 0

Excel数据分析——函数与公式(中)

[复制链接]

1

主题

3

帖子

5

积分

新手上路

Rank: 1

积分
5
发表于 2023-4-10 09:03:58 | 显示全部楼层 |阅读模式
hiqingle:Excel数据分析——函数与公式(上)

查找与引用

查找与引用类型函数是相对比较复杂的一类函数,我们先将比较重要的这类函数的基本用法示例说明,然后再针对比较常用的函数组合用法详细展开。




  • CHOOSE:从值的列表中选择值
  • IFS:检查是否满足一个或多个条件,且是否返回与第一个 TRUE 条件对应的值。
  • SWITCH:根据值列表计算表达式,并返回与第一个匹配值对应的结果。 如果不匹配,则可能返回可选默认值。
  • ROW:返回引用的行号
  • ROWS:返回引用中的行数
  • COLUMN:返回引用的列号
  • COLUMNS:返回引用中包含的列数
  • ADDRESS:以文本形式将引用值返回到工作表的单个单元格
  • INDIRECT:返回由文本值指定的引用
  • INDEX:使用索引从引用或数组中选择值
  • OFFSET:从给定引用中返回引用偏移量
  • MATCH:在引用或数组中查找值
  • XMATCH:返回项目在数组或单元格区域中的相对位置。match_mode缺省为精确匹配,match_mode为1,为返回精确匹配或下一个最大项
  • VLOOKUP:在数组第一列中查找,然后在行之间移动以返回单元格的值
  • XLOOKUP(VLOOKUP的升级版):搜索区域或数组,并返回与之找到的第一个匹配项对应的项。 如果不存在匹配项,则 XLOOKUP 可返回最接近(近似值)的匹配项。
  • HYPERLINK:创建一个快捷方式,用于打开硬盘、服务器或Internet上的文档

XLOOKUP,比VLOOKUP更强

世人只知VLOOKUP,而不是XLOOKUP,殊不知XLOOKUP比VLOOKUP其实更好用,弥补了VLOOKUP的很多缺陷。刚刚结果的VLOOKUP的复杂用法,其中不乏一些巧思,但用XLOOKUP来解决,就变得异常简单了。下边的几个例子中,每个例子都用了多种解法,整体对比下来,XLOOKUP函数比VLOOKUP使用上更简约、理解上更容易。

  • 单列查找,结果多列(VLOOKUP+MATCH、VLOOKUP+COLUMN、XLOOKUP数组)
  • 在左边的列中查找值(VLOOKUP+IF数组、INDEX+MATCH、XLOOKUP)
  • 双列查找(VLOOKUP+IF数组、INDEX+MATCH数组、XLOOKUP)
  • 在多个表格中查找(VLOOKUP+IF、XLOOKUP+IF)




较为复杂的引用函数


  • 确定一个值在区域内的单元格地址(ADDRESS)
  • 多行多列转换为一列(OFFSET)
  • 引用区域文本转化为参数化的引用(INDIRECT)
  • ROW与ROWS的区别,COLUMN与COLUMNS同理




引用函数

计数和求和

前面提到的函数是放任何领域都比较通用的函数,而从这里开始之后的函数,可能在数据分析领域更为常见一些。我们先介绍最最最常用的计数和求和函数。




  • COUNT:计算参数列表中数字的个数
  • COUNTIF:计算区域内符合给定条件的单元格的数量
  • COUNTIFS:计算区域内符合多个条件的单元格的数量
  • SUBTOTAL:返回列表或数据库中的分类汇总
  • SUM:求参数的和
  • SUMIF:按给定条件对指定单元格求和
  • SUMIFS:在区域中添加满足多个条件的单元格
  • SUMPRODUCT:返回对应的数组元素的乘积和
  • AVERAGE:返回其参数的平均值
  • AVERAGEIF:返回区域中满足给定条件的所有单元格的平均值(算术平均值)
  • AVERAGEIFS:返回满足多个条件的所有单元格的平均值(算术平均值)

计数和求和函数主要分三组,计数、求和和平均(即求和除以计数),每组中均包含XX、XXIF和XXIFS的函数,另外求和函数还多了一个SUBTOTAL和SUMPRODUCT函数。
由于三组函数用法基本相同,本节以求和函数为示例来介绍。

SUBTOTAL与SUM的区别

快捷键ALT+=可以快速填充求和公式,与手写SUM公式的好处在于不用挨个选择求和区域,即可以自动选择(连续)求和区域。有两种用法。
第一种,Ctrl多选求和公式所在单元格后ALT+=,这时公式是SUM(X:X)



第二种,筛选可见单元格后ALT+=,这时对应的公式是=SUBTOTAL(9,X:X),SUBTOTAL函数有个特性是可以仅对可见单元格求和(需要把=SUBTOTAL(9,X:X)改成=SUBTOTAL(109,X:X)),这也是SUM函数做不到的。



条件求和

说到求和函数,必须重点讲一下SUMIF,SUMIF仅针对满足条件的数据求和,也就是筛选后求和。这个筛选可以是数据本身,也可以是数据所在行的其他字段,筛选方式可以是数值比较,也可以是匹配通配符。
下面这张图可以讲清楚常用的条件求和函数。一题多解,主要是为了拓展函数思维。如有疑问,可在评论中提出来,我看到会解答的。

  • 单条件求和(SUMIF)
  • 多条件求和(SUMIFS、SUMPRODUCT、SUM数组公式、SUM+SUMIF)




条件求和函数
加权求和、加权平均





排序






  • MAX:返回参数列表中的最大值
  • MAXIFS:返回一组给定条件或标准指定的单元格之间的最大值
  • MIN:返回参数列表中的最小值
  • MINIFS:返回一组给定条件或标准指定的单元格之间的最小值。
  • LARGE:返回数据集中第K个最大值
  • SMALL:返回数据集中第K个最小值
  • MEDIAN:返回给定数值集合的中值
  • TRIMMEAN:返回数据集的内部平均值
  • QUARTILE.EXC:基于从0到1之间(不含0与1)的百分点值,返回一组数据的四分位点
  • QUARTILE.INC:基于从0到1之间(含0与1)的百分点值,返回一组数据的四分位点
  • RANK.AVG:返回某个数字在一列数字中相对于其他数字的大小排位,如果多个数值排名相同则返回平均排名
  • RANK.EQ:返回某个数字在一列数字中相对于其他数字的大小排位,如果多个数值排名相同则返回最佳排名
  • PERCENTILE.EXC:返回区域中数值的第k个百分点的值,此处的k的范围为0到1(不含0和1)
  • PERCENTILE.INC:返回区域中数值的第k个百分点的值,此处的k的范围为0到1(含0和1)
  • PERCENTRANK.EXC:返回特定数值在一组数中的百分比排名(介于0与1之间,不含0与1)
  • PERCENTRANK.INC:返回特定数值在一组数中的百分比排名(介于0与1之间,含0与1)

排序函数的常见应用


  • 最大值、最小值(MAX、MIN)
  • 次大值、次小值(LARGE、SMALL)
  • 指定数字的排名(RANK、COUNTIF)
  • 不计重复的次大值(LARGE+COUNTIF+MAX)
  • 大于平均值的最小值(LARGE/SMALL+COUNTIF+AVERAGE、MIN+IF+AVERAGE)
  • 前/后N名平均分(AVERAGE+LARGE/SMALL+ROW/COLUMN)
  • 去掉前后N名平均分(TRIMMEAN)




统计

较为简单,这里只简单示例一下。




  • STDEVA:估算样本(包括数字、文本和逻辑值)标准偏差
  • STDEVPA:计算样本总体(包括数字、文本和逻辑值)标准偏差
  • VARA:估算样本(包括数字、文本和逻辑值)方差
  • VARPA:计算样本总体(包括数字、文本和逻辑值)方差
  • RAND:返回 0 和 1 之间的一个随机数
  • RANDBETWEEN:返回位于两个指定数之间的一个随机数
  • CORREL:返回两个数据集之间的相关系数

统计类型函数,还有各类分布的对应函数,如正态分布的NORM.DIST函数和γ分布的GAMMA.DIST函数。我实际工作中几乎没用到过,感兴趣的可以自己再深入学习了解。
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Archiver|手机版|小黑屋|办公问答网

GMT+8, 2025-3-16 14:17 , Processed in 0.113502 second(s), 23 queries .

Powered by Discuz! X3.4

© 2001-2013 Comsenz Inc. Templated By 【未来科技 www.veikei.com】设计

快速回复 返回顶部 返回列表