| Excel数据分析 | 您所在的位置：网站首页 › excel的匹配公式 › Excel数据分析 |
Excel数据分析
|
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函数。我实际工作中几乎没用到过，感兴趣的可以自己再深入学习了解。 |
【本文地址】