Excel数据分析 |
您所在的位置:网站首页 › 过滤器拦截器区别图解视频 › Excel数据分析 |
Excel数据分析——函数与公式(上)Excel数据分析——函数与公式(中) 本篇继续讲Excel函数与公式。 数组函数“数组”这个概念在编程语言中并不陌生,但在Excel中可能很多人以为自己没接触过,但其实随手可见。 如下动图所示,引用的区域B3:C5其实就是一个数组,在编辑栏按F9,得到B3:C5对应的3行2列的常量数组{1,2;3,4;5,6}。从这里我们可以知道,数组是一对花括号{}把所有元素包含起来,用逗号代表列与列的间隔,分号代表行与行的间隔。 如果数组只存在于计算过程的内存中,最终输出结果只有一个单元格,那么这就是单单元格数值公式,典型的如SUM函数。 如果返回结果是数组,输出结果是存在多个单元格中,这就是多单元格公式了,典型的如TRANSPOSE函数。由于多单元格数组公式是一次计算产出多单元格结果,比起多单元格的多次重复计算,计算效率是高很多的。 在Excel 2019版本之前的数值公式,需要编辑完成后按下CSE(Ctrl+Shift+Enter)才是正确的,但现在可以跟普通函数一样,只按Enter即可。因为Excel特别的溢出设计。有了这个就不需要先计算好有结果是几乘几的数组,再选好同等大小区域来应用数组公式了。 没有花括号的单单元格数组公式,看起来跟普通公式毫无二致。比如上文提到的求最高三项平均值,公式是=AVERAGEA(LARGE(E3:E12,ROW(1:3))),就是先利用ROW(1:3)产生一个{1,2,3}的常量数组,作为LARGE函数的第2个参数,取出区域中的TOP1、TOP2和TOP3,再对这三个值求平均。再比如查找与应用函数中的第一个例子,单列查找结果多列,公式=XLOOKUP($G5,B$2:B$26,C2:D26)也用到了溢出,不用考虑旁边各自的公式要怎么写了。 多单元格数组公式具有同一性,因为数组本来就是按相对位置计算的,所以绝对和相对引用的区分就没那么重要了。只需要在左上角编辑一个公式,Excel就会自动溢出到周边单元格,整个数组公式区域用一个发光的边框框起来。事实上只有数组的左上角单元格可编辑,其他单元格的编辑栏置灰不可编辑。 那么本小节的数组公式,就着重讲多单元格数组公式的应用了。 若你需要经常做某种计算,而用Excel自带公式实现,步骤很多容易出错,建议你把计算逻辑固化为自定义函数。 我曾经编辑过一个函数,实现功能是:将一个指标值映射到Ymin-Ymax之间,也就是指数化。具体功能是,指标值大于Xmax时,Y取Ymax,指标值小于Xmin时,Y取Ymin,指标值在Xmin-Xmax之间时,Y在Ymin-Ymax之间线性分布。 '自定义函数VBA代码 Function zs_score(low As Double, high As Double, weight As Double, value As Double, up As Boolean) flag = 1 If up = False Then flag = -1 End If If value * flag >= high * flag Then zs_score = weight ElseIf value * flag |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |