美文网首页
Excel数组公式

Excel数组公式

作者: 坚持到底v2 | 来源:发表于2017-10-26 19:35 被阅读0次

需要注意的:


  1. 虽然 + * 有时可以和 orand 互换,但是他们是有区别的,例如:
    =sum(if(and(A1:A2>0,A1:A2<8),B1:B2))
    VS
    =sum(if((A1:A2>0)*(A1:A2<8),B1:B2))

前者只有当 A1:A2 都介于0和8之间时才返回 B1:B2 的和,否则得到的结果是 0
后者则分开了,比如A1介于0和8之间,A2不介于0和8之间,那么求得的结果是B1的和,而不是0。
AND(logical1,logical2, ...) --详见 and 函数帮助:当所有的logical们都为True时返回True --这是1个单值
OR(logical1,logical2, ...) --详见 or 函数帮助:当所有的logical们只要有一个为True时返回True --这是1个单值
(A1:A2>0)*(A1:A2<8) -- 返回的是{True;False}*{True;True}类型的数组相乘的结果{1;0}——这是一个数组
(A1:A2>0)+(A1:A2<8) -- 返回的是{True;False}+{True;True}类型的数组相加的结果{2;1}——这是一个数组
就是说And、OR计算出来的总是单值,不是数组。

  1. -- 用于把文本转换成数字,也可以用 *1 代替,(或者 N("xx"+0) ?)

  2. 应用: 将文本中的数字取出,例如:"收取32.0元"
    =LOOKUP(9E+307,--MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&1234567890) ),ROW(INDIRECT("1:"&LEN(A1)))))

还可以再加上对 MIN(FIND...) 的值与 LEN(A1) 的比较,以免出现 #N/A

解释:构造了一个搜索数组,从最先出现数字的位置开始取子字符串,然后将其转换为数字(不能转换为数字的为#N/A),然后用Lookup指定一个9E+307这样的大数,最后返回小于等于他的(实际上也就是最接近它的)数,也就是最长的可以转换为数字的子字符串。

  1. 应用:取出某列中非空不重复的单元格的内容:
Excel数组公式

这里加一个要求:返回B1:B20内不重复非空单元格。
步骤分解:
(1)首先取出非空不重复单元格所在的行号:
IF($B$1:$B$20<>"",IF(MATCH($B$1:$B$20,$B$1:$B$20,0)=ROW($B$1:$B$20),ROW($B$1:$B$20)))

因为MATCH函数不能使用空值作为第一个参数,所以先使用IF语句把空值排除出去。
然后由于MATCH函数返回搜索值中在整个区域第一次出现的位置,将其和自身所在行号进行比对,就可以判断是否重复。
为了方便将它定义为一个名称x,这是一个数组。

(2)然后使用 IF(ROW(1:1)>COUNT(x),"",INDEX(A:A,SMALL(x,ROW(1:1)))) 得到想要的结果,这是使用数组得到一个单值。

  1. 应用:返回B列内等于“A”的对应A列的记录:
Excel数组公式

步骤分解:
(1)首先使用 IF($B$1:$B$20="A",ROW($B$1:$B$20)) 实现判断B列等于"A"的记录的行号。
注意这个结果是一个数组,使其有了不同的用途,为了方便可以将其定义为名称 x
(2)使用 SMALL(x,ROW(1:1)) 把符合条件的行号按照从小到大的顺序列出来,这是使用数组得到一个单值。
(3)使用 INDEX($A$1:$A$20,SMALL(x,ROW(1:1))) ,把A列对应的记录取出来。
(4)为了不出现错误值,使用 SUM(($B$1:$B$20="A")*1)<ROW(1:1) 作为判断条件,一般使用SUM数组公式,可以组合多个条件,且的关系使用 *,或的关系使用 + ,只有一个条件时要加个常用条件,例如本例 *1
如果在增加一个条件,则在此主公式中加入 IF(($B$1:$B$20="A")*($C$1:$C$20="B"),ROW($B$1:$B$20)) , 然后相应地在 (SUM(($B$1:$B$20="A")*1)<ROW(1:1) 中加入相应的判断条件

(5)最终公式为:
=IF(SUM(($B$1:$B$20="A")*1)<ROW(1:1),"",INDEX($A$1:$A$20,SMALL(x,ROW(1:1))))

  1. 返回重复次数最多的值


    Excel数组公式

解题步骤:
(1)首先使用IF(COUNTIF($B$1:$B$20,$B$1:$B$20)=MAX(COUNTIF($B$1:$B$20,$B$1:$B$20)),ROW($B$1:$B$20))
得到一个数组,用于符合条件的行的行号,也就是G列所示的值,为了方便可以将其定义为名称 x
(2)使用 INDEX($C$1:$C$20,SMALL(x,ROW(1:1))),把C列中的内容取出来。
(3)为了不出现错误值,可以使用 IF(COUNTIF($B$1:$B$20,$B$1:$B$20)=MAX(COUNTIF($B$1:$B$20,$B$1:$B$20)),1) 得到另外一个数组,用于符合条件的行的计数,将其定义为名称y。然后使用 SUM(y)<ROW(1:1) 作为判断条件。
(4)最终公式为
=IF(SUM(y)<ROW(1:1),"",INDEX($C$1:$C$20,SMALL(x,ROW(1:1))))

  1. 按照重复次数的大小列示内容
Excel数组公式 - supershll - 记忆里

解题步骤:
(1)首先得到不重复单元格所在的行号数组:
IF(MATCH($B$1:$B$20,$B$1:$B$20,0)=ROW($B$1:$B$20),ROW($B$1:$B$20))
如E列所示。

(2)然后得到不重复单元格的重复次数数组:
IF(MATCH($B$1:$B$20,$B$1:$B$20,0)=ROW($B$1:$B$20),COUNTIF($B$1:$B$20,$B$1:$B$20))
如F列所示

(3)为了进行排序的同时又得到行号,所以综合E列和F列的数据,使用如下:
IF(MATCH($B$1:$B$20,$B$1:$B$20,0)=ROW($B$1:$B$20),COUNTIF($B$1:$B$20,$B$1:$B$20)+ROW($B$1:$B$20)/1000)

这样整数部分是重复次数,小数部分是行号。
如G列所示,这个数组符合我们的使用要求了,将其定义为名称 x

(4)使用LARGE函数进行排序。LARGE(x,ROW(1:1)) ,这是使用数组得到一个单值。

(5)然后使用MOD函数取出行号。MOD(LARGE(x,ROW(1:1))*1000,1000)

(6)然后在使用INDEX函数取出对应行的记录。INDEX($B$1:$B$20,MOD(LARGE(x,ROW(1:1))*1000,1000))

(7)最后屏蔽错误值,IF(ROW(1:1)>COUNT(x),"",INDEX($B$1:$B$20,MOD(LARGE(x,ROW(1:1))*1000,1000))), 也可以使用ROW(1:1)>SUM(1/COUNTIF($B$1:$B$20,$B$1:$B$20)) 作为判断条件, 这个SUM的意思就是说区域内去重后的单元格的个数(假设重复的单元格a的个数n,n个(n/1)相加最后变成1)。

  1. 返回区域内符合条件的值
Excel数组公式 - supershll - 记忆里

解题步骤:
(1)首先使用 IF($A$2:$H$12=1,ROW($A$2:$H$12)*10000+COLUMN($A$2:$H$12)) 找出符合条件的单元格行号和列号数组,这是一个2维数组。将其定义为名称 x

(2)如果想使用INDEX函数就需要找出相关的参数来,即INDEX(数据区,行数,列数) 。使用INT(SMALL(x,ROW(1:1))/10000) 获得行号, MOD(SMALL(x,ROW(1:1)),10000) 获得列号。

(3)最后使用INDEX获得结果,此公式是姓名列的公式(其中Column()-17是根据公式所在列设置的) INDEX($A$1:$H$12,INT(SMALL(x,ROW(1:1))/10000),MOD(SMALL(x,ROW(1:1)),10000)+COLUMN()-17)

(4)最后就是屏蔽错误值的问题。使用IF(SUM(IF($A$2:$H$12=1,1))<ROW(1:1),"","原公式")

  1. 按顺序返回不重复值


    Excel数组公式 - supershll - 记忆里

解题步骤:
(1)使用MATCH函数获取不重复行的行号和值的数组,将其定义为名称x
IF(MATCH($B$1:$B$6,$B$1:$B$6,0)=ROW($B$1:$B$6),ROW($B$1:$B$6)*100+$B$1:$B$6)

(2)使用SMALL函数:
SMALL(MOD(SMALL(x),{1;2;3}),100),ROW(1:1))

  1. 返回数字中不重复的前3位数。
Excel数组公式 - supershll - 记忆里

解题步骤:
(1)使用 ROW(INDIRECT("1:"&LEN(A1*B1))) 求出乘积结果的位数序号数组,如F列所示,定义为名称 x

(2)使用 MID(A1*B1,x,1) 把乘积的结果按照原有顺序组成内存数组,如G列所示,定义为名称 y

(3)然后使用MATCH函数获取不重复行的行号和值的数组,定义为名称 m ,如J列所示 IF(MATCH(y,y,0)=x,x*100+y)

(4)使用SMALL函数,SMALL(MOD(SMALL(m),{1;2;3}),ROW(1:1)) ,如M列所示,这是由数组得到的单值

(5)将结果串起来 TEXT(SUM(SMALL(MOD(SMALL(m),{1;2;3}),{1;2;3})*10^{2,1,0}),"000") 再次使用数组得到一个单值。

  1. 总结:使用数组公式,一定要时刻明白,何时结果是数组,何时结果是单值。然后再根据结果参与计算,例如送数组中取出单值或把单值组合成数组

相关文章

  • 中级和函数

    二 加强阶段重点学习方向 1,数组公式(将函数和数组结合) 数组公式可以认为是Excel对公式和数组的一种扩充,换...

  • Excel 数组公式

    数组公式对一组或多组值执行多重计算,并返回一个或多个结果。 编辑栏可以看到以“{}”括起来的公式就是数组公式。 在...

  • Excel数组公式

    需要注意的: 虽然 + * 有时可以和 or 、and 互换,但是他们是有区别的,例如:=sum(if(and(A...

  • Excel 数组公式

    数组公式:http://windyli.blog.51cto.com/1300305/306009在某个单元格输入...

  • 应用技巧二十一:数组公式

    对于希望精通Excel函数与公式的用户来说,数组运算和数组公式是必须跨越的门槛。通过本文的介绍,让用户能够对数组公...

  • Excel学习日打卡D4-2020.4.29

    今日学习书目:《Excel2010函数与公式——疑难千寻千解丛书(二)》 今日学习章节:第1章1.4数组与数组公式...

  • 【ROWS】函数技巧,你一定要知道的!!!

    Excel的ROWS函数主要返回引用或数组的行数。本文介绍 Microsoft Excel中ROWS函数的公式语法...

  • Excel数组公式顶级绝密 Excel Array formul

    本文目标: 1、 理解excel软件背后数据处理的基本原理; 2、 搞懂excel数组公式原理,并会灵活运用,放大...

  • Excel数组公式的使用

    如果需要同时对一组或两组以上的数据进行计算,那么计算的结果可能是一个,也可能时多个,这个情况只有数组公式才能处理。...

  • Excel—神奇的数组公式

    数组公式是一种公式,可以针对数组中的一个或多个项执行多个计算。 可以将数组视为值的行或列,或值行和列的组合。 数组...

网友评论

      本文标题:Excel数组公式

      本文链接:https://www.haomeiwen.com/subject/kigauxtx.html