精华内容
下载资源
问答
  • 本次的练习是:如下图1所示,有一组非连续的单元格区域,由任意数量的单列区域组成,每个区域有数字、文本或空格。要求从单元格A2开始,使用公式生成一个列表,这个列表由上述非连续单元格区域所有只出现了...

    学习Excel技术,关注微信公众号:

    excelperfect

    导语:继续研究来自于excelxor.com的案例。这个案例不用数组公式就能解决,但仍有很多值得学习的技术。

    本次的练习是:如下图1所示,有一组非连续的单元格区域,由任意数量的单列区域组成,每个区域中的值有数字、文本或空格。要求从单元格A2开始,使用公式生成一个列表,这个列表由上述非连续单元格区域中所有只出现了一次的数字组成(如图1所示,129这三个数字在非连续的单元格区域中只出现了一次)。

    8e60261924a082933f2ef1f60394272a.png

    1

    注意,虽然图1中在单元格区域C1:N12中有很多单元格为空,但解决方案的公式中要考虑这些单元格也可能存在数据的情况。

    先不看答案,自已动手试一试。

    公式

    在单元格A2中输入公式:

    =IF(ROWS(A$2:A2)>$A$1,"",AGGREGATE(15,6,(ROW(INDIRECT("1:"& MAX(RNG)+1))-1)/(FREQUENCY(RNG,ROW(INDIRECT("1:" &MAX(RNG)+1))-1)=1),ROWS(A$2:A2)))

    下拉直至出现空单元格为止。

    在单元格A1中,公式:

    =SUMPRODUCT(--(FREQUENCY(RNG,ROW(INDIRECT("1:" &MAX(RNG)+1))-1)=1))

    计算该非连续单元格区域中满足要求的数字数量。

    公式解析

    公式中的RNG是定义的名称。

    名称:RNG

    引用位置:=$C$2:$C$7,$E$2:$E$4,$E$6:$E$9,$G$3:$G$11,$J$1:$J$9,$L$5:$L$6,$N$3:$N$7,$N$9:$N$10,$N$12

    注意,这个联合的单元格区域并不能传递给所有的工作表函数,但还是有些工作表函数能够处理它们。

    1. 首先,看看单元格A1中返回满足要求的数字数量的公式:

    =SUMPRODUCT(--(FREQUENCY(RNG,ROW(INDIRECT("1:"&MAX(RNG)+1))-1)=1))

    这里的重点是使用FREQUENCY函数,该函数是非常有用的一个函数,能够处理这种不连续的单元格区域。

    另一个函数是MAX函数,也可以操作多个、非连续的单元格区域,因此:

    MAX(RNG)

    能够得到组成RNG的单元格区域中所有数值的最大值,忽略逻辑值、文本。很显然,其返回的结果是9

    这样,公式中的:

    ROW(INDIRECT("1:"&MAX(RNG)+1))-1

    转换成:

    ROW(INDIRECT("1:"&9+1))-1

    转换成:

    ROW(INDIRECT("1:"&10))-1

    转换成:

    {1;2;3;4;5;6;7;8;9;10}-1

    结果为:

    {0;1;2;3;4;5;6;7;8;9}

    这里,我们创建了一个由0到区域中最大值的数值组成的数组,用于FREQUENCY函数的参数bins_array

    此时,公式中的:

    FREQUENCY(RNG,ROW(INDIRECT("1:"&MAX(RNG)+1))-1)

    成为:

    FREQUENCY(RNG,{0;1;2;3;4;5;6;7;8;9})

    结果为:

    {0;1;1;0;0;5;2;2;0;1;0}

    因此,公式:

    =SUMPRODUCT(--(FREQUENCY(RNG,ROW(INDIRECT("1:"&MAX(RNG)+1))-1)=1))

    可转换为:

    =SUMPRODUCT(--({0;1;1;0;0;5;2;2;0;1;0}=1))

    转换为:

    =SUMPRODUCT(--({FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}))

    两个减号强迫TRUE/FALSE转换成1/0,即:

    =SUMPRODUCT({0;1;1;0;0;0;0;0;0;1;0})

    结果为3

    2. 下面来看看从单元格A2开始用来获取值的公式:

    =IF(ROWS(A$2:A2)>$A$1,"",AGGREGATE(15,6,(ROW(INDIRECT("1:" &MAX(RNG)+1))-1)/(FREQUENCY(RNG,ROW(INDIRECT("1:" &MAX(RNG)+1))-1)=1),ROWS(A$2:A2)))

    根据前面公式推导的内容,上面的公式中:

    AGGREGATE(15,6,(ROW(INDIRECT("1:"& MAX(RNG)+1))-1)/(FREQUENCY(RNG,ROW(INDIRECT("1:" &MAX(RNG)+1))-1)=1),ROWS(A$2:A2))

    可以转换为:

    AGGREGATE(15,6,({0;1;2;3;4;5;6;7;8;9})/({FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}),ROWS(A$2:A2))

    这是以这种方式使用AGGREGATE函数时要注意的关键技术。因为如果我们在此函数中将第二个参数options设置为6,即“忽略错误值”,那么它将恰好做到这一点。

    上述公式可转换为:

    AGGREGATE(15,6,{#DIV/0!;1;2;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;9;#N/A},ROWS(A$2:A2))

    其第一个参数function_num被设置成15,等价于执行SMALL函数。(你可能想,为什么不将第一个参数设置成5,即MIN,这是不合适的。因为在AGGREGATE函数的第一个参数的所有可选项中,仅14-15能够保证在传递给函数的数组不是实际的工作表区域时能正常运行,而这里的数组是由其他函数生成的,如果设置成1-13中的任一个,则需要传递给函数的数组是实际的工作表区域。)

    对于单元格A2的公式中来说,最后一个参数k的值是1,即ROWS(A$2:A2)的返回值。因此,AGGREGATE函数部分转换为:

    AGGREGATE(15,6,{#DIV/0!;1;2;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;9;#N/A},1)

    结果为1

    这样,单元格A2中的公式转换为:

    =IF(1>$A$1,"",1)

    即:

    =IF(1>3,"",1)

    结果为1

    小结

    本示例展示了解决涉及到非连续单元格区域的问题的技术,以及哪些函数能够处理非连续单元格区域。此外,也讲解了AGGREGATE函数的使用技术。

    bbc8989f7ac8ba615faf2bff54abe92b.png

    展开全文
  • 1问题 QQ群里的提问 ...3 实际上数组公式我不太懂(下面是百度到的,稍后学下) https://baijiahao.baidu.com/s?id=1610563904907045859&wfr=spider&for=pc =IFERROR(INDEX($A$2:$D$21,SMALL(IF($C$...

     

    1 按条件去取最大值等

    1.1 问题  QQ群里里有个挺神的哥们,这几天每天提了个类似的问题

     

    1.2 按条件取最大值

    • 需要用数组公式

     

    1.3 按条件,去取数量,平均值,中位数等等

    • 都是数组公式
    • =count(if(A:A=E4,B:B,""))  数组不好用,只能用 COUNTIF(A:A,E4)
    • =MAX(IF(A:A=E4,B:B,""))
    • =AVERAGE(IF(A:A=E4,B:B,""))
    • =MEDIAN(IF(A:A=E4,B:B))

     

     

     

     

    2 容易出问题的几个地方思考

    2.1 取中位数,要注意数组筛选后剩下的元素个数是奇数才好--否则EXCEL算的不对

    • 如果是奇数没问题
    • 如果是偶数,EXCEL会自己算一个新的中位数出来---理论上应该是2个中位数。

     

    2.2 偶数数列的中位数

    • 偶数数列中位数
    • 有些地方处理,是显示2个数为中位数
    • 有些处理方法,是中位数就是中间两数靠前的那个
    • 也有少像EXCEL这样处理,为中间2个数的平均值,其实感觉VB EXCEL这样处理不太好

     

    2.3 如果是多条件呢?

    • 比如 多条件求和
    • 数组公式也能做
    • 用数组公式,多个条件的话,不是用 and() ,而是直接用2个条件() * ()  数组相乘的方法

     

    • 错误方法
    • =SUM(IF(AND($A$3:$A$20=M$3,$B$3:$B$20=$L4),$C:$C))
    • 因为AND函数仅能返回单值,不能用作数组的中间步骤

     

    • 正确方法
    • =SUM(IF(($A:$A=G$3)*($B:$B=$F4),$C:$C))
    • IF($A:$A=G$3) 或 IF ($B:$B=$F4) 都将返回一个由TRUE或FALSE组成的数组。
    • 用*或其它四则运算符号运算时,TRUE被当作1参与,FALSE被当作0参与。
    • 只有两个都是1时,才会得到1。
    • 所以这里乘的意思是与AND一样的,但它可以保留数组,不象AND只能返回单值。
    • 如果不用乘,而用加,那么意思就是两个条件中只要任一符合,就符合。相当于“或”。
    • SUM(IF(($A:$A=G$3)+($B:$B=$F4),$C:$C))

     

    • sumif 也能做
    • 那么区别是什么?

     

     

    2.2 如果是多条件,求平均值,中位数,其他非countifs,sumifs 有内置函数的,是不是必须用数组公式了?

    2.2.1 EXCEL内置的这些函数,是组合函数,基本能实现这一类的数组函数简单功能

    • countif()   VS   countifs()   
    • sumif()     VS   sumifs()
    • averageif()
    • product()
    • sumproduct()

     

    2.2.2 几个特例

    • countif() 好像和  count(if()) 不太匹配,count(if()) 不太好用
    • product()

     

    2.3 相同情况下,数组公式的写法是  "函数的嵌套+数组公式"

    • 如果是多条件,记得不是用 and() ,而是直接用2个条件() * ()
    • sum(if())   ----这个比较特殊,不如countif() 好用
    • count(if())
    • average(if())

     

    3 数组公式的哪儿是数组?什么时候该用数组公式?

    3.1 普通公式 和数组公式的差别

    • 普通公式
    • 可以输入 数组,处理数组,但是不能输出数组
    • 并且只能输出到一个单元格(也只能针对一个单元格)

     

    • 数组公式
    • 可以输入数组,处理数组,输出的也是数组
    • 输入的结果(数组),必须输出道多个单元格
    • 或者被其他可以处理数组的函数处理为单个值输入到1个单元格

     

    3.2 数组公式的计算过程

    • 举例
    • 比如sum(if(a:a=e4,b:b,""))
    • 第1步   数组公式的数组在哪儿?在这里:输出的结果(中间结果)为数组!
    • if(a:a=e4,b:b,"")  先生成了一个新的数组
    • 数组公式不经过处理,输出结果(数组)不能输出到一个单元,那样只会输出数组的第1个元素!其他元素都看不到
    • 特别注意,如果简单粗暴用 a:a 而不用 a4:a16 有错位问题,因为a:a 包含了列里的其他行元素,不只你看到的a4:a16
    • 但如果只当数组用,不关心每个具体的无所谓
    •  
    •  
    • 第2步 用sum()  处理if 生成的新数组
    • 相当于,sum() 重新处理了if生成的新数组   if(a:a=e4,b:b,"")     
    • 一般来说,这里的 if(a:a=e4,b:b,"")     [特殊用false不用"" 为了好看懂]
    • 这样sum()  average() mediam() 处理新的数组,就可以忽略"" 值
    • 但例外的是count(if()) 好像不行,count() 会 算上if处理后的元素的个数 "" 也是count() 会计数的。

     

     

    3.3 数组公式使用情况1:数组公式的结果是数组,输入到多个单元格

    • 普通公式: 1个单元格输入普通公式,然后往下拖动
    • 数组公式:选中多个单元格,在编辑栏输入= ,然后 ctrl=shift+enter ,整体输入数组公式
    •  

     

    3.4  数组公式使用情况2: 先用if() 等将原始数组处理为新数组,其他函数再把新数组处理为一个结果,输入到一个单元格里

    • =IF($A$4:$A$16=E4,$B$4:$B$16)
    • =IF(A:A=E4,B:B)

     

     

    3.5 这里数组公式都可以用透视表来实现?好像可以

    • 好像可以差不多一样的效果

     

     

    3.6  count(if())  嵌套 数组公式不好用,为啥呢?

    • 但例外的是count(if()) 好像不行
    • 怀疑是count() 会 算上if处理后的元素的个数 "" ,或false, 并不能把数组中去掉元素,所以也是count() 会计数的。

     

     

    展开全文
  • Excel常用公式

    2020-04-02 11:46:49
    输入公式的时候手速慢一点,多看公式的描述。... median() 求中位数 mode() 求众数 large() 求第K个最大 small 求第K个最小值 quartile() 求四分位数 power() 求幂 var() 求方差 stdev(...

    输入公式的时候手速慢一点,多看公式的描述。

    1. 理解什么是相对引用和绝对引用!F4进行切换
    2. 双击填充柄,快速填充公式
    3. ctrl+回车 对算选单元格填充公式
    4. ctrl + '~' 进入公式视图模式
    5. sum() 求和函数 ,sumif() 对满足条件的求和(很好用)! sumifs()
    6. average() 求平均值,averageif() ,averageifs()
    7. max() 求最大值
    8. min() 求最小值
    9. median() 求中位数
    10. mode() 求众数
    11. large() 求第K个最大值
    12. small  求第K个最小值
    13. quartile() 求四分位数
    14. power() 求幂
    15. var() 求方差
    16. stdev() 求标准差
    17. frequency() 求频率分布 (这个需要一点操作)
    18. rank() 根据数值求排名 (这个需要一点操作) / large() 根据排名求数值
    19. 环比、同比、复合增长率{ (期末值/期初值)^(1/N) -1 }、预期值{ 期初值(1+复合增长率)^N }
    20. 常用的条件统计函数:sumif() 、countif()、averageif() 
    21. 常用的多条件统计函数:sumifs()、countifs()、averageifs()
    22. 不等于符号:<>
    23. 逻辑函数TRUE和FALSE,逻辑运算中非0为TRUE,0为FALSE
    24. if() 满足返回一个值,不满足返回一个值
    25. if(if()) 嵌套的 if 函数
    26. and() 判断多个条件是否同时成立
    27. or() 判断多个条件中是否有成立的
    28. not() 对逻辑值求反,条件为真返回假,条件为假返回真
    29. vlookup()  根据条件,在区域内查找,返回对应值 ,查找区域是垂直分布
    30. hlookup() 根据条件,在区域内查找,返回对应值 ,查找区域是水平分布
    31. vlookup( vlookup() ) 函数嵌套
    32. match()  查找指定值在区域内的位置
    33. index() 在指定区域内,根据行列号,返回交叉点的值
    34. index( match() ,match() ) 嵌套使用来查询值
    35. lookup() 类似于vlookup() 
    36. choos() 给出一组值中的索引,返回对应值
    37. count() 统计数值个数,counta()统计非空单元格个数 ,countblank() 统计空单元格,countif(),countifs()
    38.  rank() 返回值在区域内的排名
    39. offset() 设置参考点,通过指定偏移量,返回索引处的值
    40. row() 返回引用单元格的行号,用来自动填充序号非常好
    41. column() 返回引用单元格的列号
    42. rows() 返回区域的行数
    43. columns() 返回区域的列数
    44. 工资条的骚操作
    45. round() 对指定位数对数字进行四舍五入,该函数会改变数值的大小,而不仅仅是显示格式,这要注意,因为会产生误差。
    46. rounddown() 向下舍入小数,roundup()向上舍入小数
    47. int() 向下取整 ,trunc() 切断取整
    48. mod() 取余数
    49. rand() 返回 0-1之间的随机小数
    50. randbetween()  返回区间内的随机整数
    51. convert() 度量转换函数
    52. ******************************************************************************************************************日期函数
    53. 日期的本质数数值,所以可以参与加减
    54. ctrl + ; 插入当前日期 ,ctrl + shif + ;  插入当前时间
    55. yyyy/mm/dd 保留日期前面的0
    56. month() 获得日期中的月份
    57. year() 获取日期中的年份
    58. day() 获取日期中的天数
    59. hour() 获取日期中的小时数
    60. minute() 获取日期中的分钟数
    61. second() 获取日期中的秒数
    62. now() 返回当前日期
    63. days() 获取两个日志之间相差几天
    64. date() 根据年月日返回日期  操作完之后要粘贴为数值比较好
    65. time() 根据分时秒返回时间
    66. weekday() 根据日期返回指定类型的星期数
    67. networkdays() 计算两个日期之间完整的工作日天数
    68. workday() 计算某个日期之前或之后的指定多少个工作日之后的日期
    69. eomonth() 计算指定日期当月的最后一天的日期
    70. edate() 返回指定日期几个月之后的日期
    71. datedif() 计算两个日期之间,指定单位的间隔数
    72. ***************************************************************************************************************字符串函数
    73. len() 返回文本的字符个数
    74. find() 从指定位置开始,在某个字符串中查找某个字符的位置
    75. search() 类似与find() ,不区分大小写
    76. mid() 从文本指定位置开始,提取指定数目的字符
    77. left() 从一个文本的第一位开始,返回指定位数的字符串
    78. right() 从一个文本的最后一位开始,返回指定位数的字符串
    79. trim() 删除字符串中多余的文本
    80. 使用ctrl+F 替换所有空格为无
    81. clean() 删除文本中非打印字符,非打印字符会导致工作簿文件较大
    82. concatenate() 将多个文本合并
    83. “&” 是连字符,可以达到concatenate()的作用
    84.  upper() 将单词每个字母转为大写
    85. lower() 将单词每个字母转为小写
    86. proper() 将单词首字母转为大写,其余字母转为小写
    87. replace() 替换掉文本中指定位置的字符
    88. substitute() 类似于replace() 函数,不过不是按位置索引
    89. text() 根据指定的格式,将数字转为文本
    90. ***********************************************************************************************信息函数
    91. istext()  判断单元格内容是不是文本
    92. isnumber() 判断单元格内容是不是数字
    93. isblank() 判断单元格内容是否为空
    94. iferror() 如果表达式错误,那么将返回一个给定的
    95. ************************************************************************************************数组
    96. 使用数组计算式,使用ctrl+shift+回车得到返回值
    97. 数组操作有点像np.array()
    98. 数组会方便很多
    99. frequency()  以一列垂直数组,返回一组数据的频率分布
    100. transpose() 转置数组区域
    展开全文
  • Excel打印如何不显示错误符号 对于一些不可打印的字符的处理 用那个函数可将个位数前面的零显示出来? 如果你要在A3的前面插入100行 请问如何每隔30行粘贴一新行 在工作表里有连续10行数据, 现在要每行间格2行 ...
  • EXCEL函数公式

    热门讨论 2010-03-16 03:26:38
    Excel打印如何不显示错误符号 对于一些不可打印的字符的处理 用那个函数可将个位数前面的零显示出来? 如果你要在A3的前面插入100行 请问如何每隔30行粘贴一新行 在工作表里有连续10行数据, 现在要每行间格2行 ...
  • 学习Excel技术,关注微信...本次的练习是:如下图1所示,在一个4行4列的单元格区域A1:D4,每个单元格内都是一个一整数,并且目标单元格(此处为F2)也为整数,要求在单元格G2编写一个公式返回单元格A1:D4...

    学习Excel技术,关注微信公众号:

    excelperfect

    导语:继续研究来自于excelxor.com的案例。这个案例很复杂,但解决方案却很精彩,值得好好研究。建议结合本文参阅原文,会有更大的收获。

    本次的练习是:如下图1所示,在一个44列的单元格区域A1:D4中,每个单元格内都是一个一位整数,并且目标值单元格(此处为F2)也为整数,要求在单元格G2中编写一个公式返回单元格A1:D4中四个不同值的组合的数量,条件如下:

    1. 这四个值的总和等于F2中的值

    2. 这四个值中彼此位于不同的行和列

    ea373ceb15899dc59c385731fd375330.png

    1

    下图2是图1示例中满足条件的6种组合。

    1948a965d338e9f965268941485d87a1.png

    2

    先不看答案,自已动手试一试。

    公式

    在单元格G2中的数组公式为:

    =SUM(0+(MMULT(IFERROR(N(OFFSET(A1,IF(MMULT(0+(ISNUMBER(FIND({1,2,3,4},ROW(INDIRECT("1234:4321"))))),{1;1;1;1})=4,MID(ROW(INDIRECT("1234:4321")),{1,2,3,4},1)-1,""),{0,1,2,3},,)),0),{1;1;1;1})=F2))

    公式解析

    本案例的条件是:在所给定的单元格区域中选择四个单元格,其和等于目标值,并且这四个单元格彼此都不同行同列。这就意味着,结果在下列组合中产生:

    =A1+B2+C3+D4 =A2+B1+C3+D4 =A3+B1+C2+D4 =A4+B1+C2+D3

    =A1+B2+C4+D3 =A2+B1+C4+D3 =A3+B1+C4+D2 =A4+B1+C3+D2

    =A1+B3+C2+D4 =A2+B3+C1+D4 =A3+B2+C1+D4 =A4+B2+C1+D3

    =A1+B3+C4+D2 =A2+B3+C4+D1 =A3+B2+C4+D1 =A4+B2+C3+D1

    =A1+B4+C2+D3 =A2+B4+C1+D3 =A3+B4+C1+D2 =A4+B3+C1+D2

    =A1+B4+C3+D2 =A2+B4+C3+D1 =A3+B4+C2+D1 =A4+B3+C2+D1

    共有24种组合。组合数为n!,对于44列为4!=24

    现在,看看上面给出的那24个和,可以推断出它们可以使用OFFSET获得,即:

    =A1+B2+C3+D4

    等价于:

    =SUM(N(OFFSET(A1,{0,1,2,3},{0,1,2,3})))

    同样:

    =A2+B1+C3+D4

    等价于:

    =SUM(N(OFFSET(A1,{1,0,2,3},{0,1,2,3})))

    同样:

    =A3+B1+C2+D4

    等价于:

    =SUM(N(OFFSET(A1,{2,0,1,3},{0,1,2,3})))

    ……

    等等。

    关键是,参数cols固定为数组{0,1,2,3},显然意味着四个元素组合中的每个都将分别来自四个不同列,然后变换传递给参数rows的数组,即满足确保没有两个元素在同一行的条件的所有可能排列。因此,这24rows参数传递给OFFSET时将产生与先前给出的24个和相等的结果,即集合{0,1,2,3}24个可能的排列,即:

    {0,1,2,3} {1,0,2,3} {2,0,1,3} {3,0,1,2}

    {0,1,3,2} {1,0,3,2} {2,0,3,1} {3,0,2,1}

    {0,2,1,3} {1,2,0,3} {2,1,0,3} {3,1,0,2}

    {0,2,3,1} {1,2,3,0} {2,1,3,0} {3,1,2,0}

    {0,3,1,2} {1,3,0,2} {2,3,0,1} {3,2,0,1}

    {0,3,2,1} {1,3,2,0} {2,3,1,0} {3,2,1,0}

    现在,我们有24个单独的和要计算。然而,我们不仅限于将一维数组传递给OFFSET函数:如果我们能够以某种方式生成一个数组,该数组由上述四个元素组成的所有数组组成。该数组如下:

    {0,1,2,3;1,0,2,3;2,0,1,3;3,0,1,2;0,1,3,2;1,0,3,2;2,0,3,1;3,0,2,1;0,2,1,3;1,2,0,3;2,1,0,3;3,1,0,2;0,2,3,1;1,2,3,0;2,1,3,0;3,1,2,0;0,3,1,2;1,3,0,2;2,3,0,1;3,2,0,1;0,3,2,1;1,3,2,0;2,3,1,0;3,2,1,0}

    一个由244列组成的数组,其中的每一行等于上面给出的24种排列之一,然后将其传递给OFFSET函数,实现对所有24个数组的同时处理。

    实际上,这也是为什么有些解决方案将定义排列为:

    ={1234;1243;1324;1342;1423;1432;2134;2143;2314;2341;2413;2431;3124;3142;3214;3241;3412;3421;4123;4132;4213;4231;4312;4321}

    的原因。然后,进一步操作该数组以获取传递给OFFSET函数的矩阵。

    可是,尽管这样确实可以提供我们所需要的结果,但我们还是希望能够动态生成这样的数组。因为如果案例扩展到55列或66列,那么矩阵元素会大幅增长,手工构造排列就不可取了。

    不幸的是,在Excel中生成这种排列的数组绝非易事。在Excel中生成大型数组唯一现实的方法是通过使用ROW函数的公式构造。但是,这不仅限制了结果数组的大小(我们至少不能生成比工作表中的行数即1,048,576多的元素的数组),而且意味着,取决于我们所需的输出,最终可能想要比预期更多的元素。

    虽然我们可以将诸如SMALL之类的函数与其他一些函数例如LARGEFREQUENCYMODE.MULT一起使用,返回一个大小与传递给函数的大小不同的数组,但是通常根本没有必要将数组缩减到这样的程度:可以简单地将大数组传递给IF语句,排除无关的元素。这就是本案例使用的技术。

    首先,获取传递给OFFSET函数作为参数rows的排列数组,即公式中的:

    IF(MMULT(0+(ISNUMBER(FIND({1,2,3,4},ROW(INDIRECT("1234:4321"))))),{1;1;1;1})=4,MID(ROW(INDIRECT("1234:4321")),{1,2,3,4},1)-1,"")

    虽然会在一开始就生成一个比必需的值大得多的数组,但是由于存在最小和最大的返回值,因此可以将数组大小进行限制。本例中,我们感兴趣的将是12344321(实际上我们最终需要的是01233210;但是,如果将0123传递给ROW函数,将被解释为123,因此我们的计算将是比目前更大的数组。以这种方式获得123412431324等,然后在公式的后面将它们处理为012301320213等。)

    处理1234:4321至少比将整列的值(即1:1048576)传递给ROW要占用更少的资源。因此:

    ROW(INDIRECT("1234:4321"))

    将生成由12344321范围内的整数构成的数组:

    {1234;1235;1236;1237;1238;1239;1240;1241;1242;1243;1244;1245;1246;1247;1248;1249;1250;1251;1252;1253;1254;1255;1256;1257;1258;1259;1260;1261;1262;1263;1264;1265;1266;1267;1268;1269;1270;1271;1272;1273;1274;1275;1276;1277;1278;1279;1280;1281;1282;1283;1284;1285;1286;1287;1288;1289;1290;1291;1292;1293;1294;1295;1296;1297;1298;1299;1300;1301;1302;1303;1304;1305;1306;1307;1308;1309;1310;1311;1312;1313;1314;1315;1316;1317;1318;1319;1320;1321;1322;1323;1324;...}

    然后测试数组中每个元素是否都包含数字1234

    FIND({1,2,3,4},ROW(INDIRECT("1234:4321")))

    将产生一个30884列的数组,其12352个元素将是对上述数组的所有3088个元素分别查找1234的结果:

    {1,2,3,4;1,2,3,#VALUE!;1,2,3,#VALUE!;1,2,3,#VALUE!;1,2,3,#VALUE!;1,2,3,#VALUE!;1,2,#VALUE!,3;1,2,#VALUE!,3;1,2,#VALUE!,3;1,2,4,3;1,2,#VALUE!,3;1,2,#VALUE!,3;1,2,#VALUE!,3;1,2,#VALUE!,3;1,2,#VALUE!,3;1,2,#VALUE!,3;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,4,#VALUE!;1,2,#VALUE!,4;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,4,#VALUE!;1,2,#VALUE!,4;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,4,#VALUE!;1,2,#VALUE!,4;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,4,#VALUE!;1,2,#VALUE!,4;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,4,#VALUE!;1,2,#VALUE!,4;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,2,#VALUE!,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,4,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,4;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,4,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,4;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,#VALUE!,2,#VALUE!;1,3,2,#VALUE!;1,3,2,#VALUE!;1,3,2,#VALUE!;1,3,2,#VALUE!;1,3,2,4;...}

    由于仅对具有由四个非错误值组成的行感兴趣,因此首先将此数组传递给ISNUMBER并强制将结果布尔值TRUE/FALSE转换成为数字,从而:

    0+(ISNUMBER(FIND({1,2,3,4},ROW(INDIRECT("1234:4321")))))

    的结果为:

    {1,1,1,1;1,1,1,0;1,1,1,0;1,1,1,0;1,1,1,0;1,1,1,0;1,1,0,1;1,1,0,1;1,1,0,1;1,1,1,1;1,1,0,1;1,1,0,1;1,1,0,1;1,1,0,1;1,1,0,1;1,1,0,1;1,1,0,0;1,1,0,0;1,1,0,0;1,1,1,0;1,1,0,1;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,1,0;1,1,0,1;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,1,0;1,1,0,1;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,1,0;1,1,0,1;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,1,0;1,1,0,1;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,1,0,0;1,0,1,0;1,0,1,0;1,1,1,0;1,0,1,0;1,0,1,1;1,0,1,0;1,0,1,0;1,0,1,0;1,0,1,0;1,0,1,0;1,0,1,0;1,0,1,0;1,1,1,0;1,0,1,0;1,0,1,1;1,0,1,0;1,0,1,0;1,0,1,0;1,0,1,0;1,0,1,0;1,1,1,0;1,1,1,0;1,1,1,0;1,1,1,0;1,1,1,1;...}

    上面的数组传递给MMULT作为第一个参数,其第二个参数为{1;1;1;1},这样:

    MMULT(0+(ISNUMBER(FIND({1,2,3,4},ROW(INDIRECT("1234:4321"))))),{1;1;1;1})

    的结果为:

    {4;3;3;3;3;3;3;3;3;4;3;3;3;3;3;3;2;2;2;3;3;2;2;2;2;2;2;2;2;3;3;2;2;2;2;2;2;2;2;3;3;2;2;2;2;2;2;2;2;3;3;2;2;2;2;2;2;2;2;3;3;2;2;2;2;2;2;2;3;2;3;2;2;2;2;2;2;2;3;2;3;2;2;2;2;2;3;3;3;3;4;...}

    数组中的4表明原来的ROW函数生成的值中分别包含1234各一个,将该数组与4进行比较:

    MMULT(0+(ISNUMBER(FIND({1,2,3,4},ROW(INDIRECT("1234:4321"))))),{1;1;1;1})=4

    得到:

    {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;...}

    数组中标红的TRUE值与ROW生成的原数组中的123412431324相对应。

    现在,对于将在公式的IF语句中生成TRUE24个值(123412431324等)中的每一个,提取一个由这四个数字组成的数组(其每个数组为{1,2,3,4}{1,2,4,3}{1,3,2,4}等)传递给OFFSET函数。使用MID函数来实现,其参数start_num指定为{1,2,3,4}

    MID(ROW(INDIRECT("1234:4321")),{1,2,3,4},1)-1

    转换为:

    MID({1234;1235;1236;1237;1238;1239;1240;1241;1242;1243;1244;1245;1246;1247;1248;1249;1250;1251;1252;1253;1254;1255;1256;1257;1258;1259;1260;1261;1262;1263;1264;1265;1266;1267;1268;1269;1270;1271;1272;1273;1274;1275;1276;1277;1278;1279;1280;1281;1282;1283;1284;1285;1286;1287;1288;1289;1290;1291;1292;1293;1294;1295;1296;1297;1298;1299;1300;1301;1302;1303;1304;1305;1306;1307;1308;1309;1310;1311;1312;1313;1314;1315;1316;1317;1318;1319;1320;1321;1322;1323;1324;...},{1,2,3,4},1)-1

    转换为:

    {"1","2","3","4";"1","2","3","5";"1","2","3","6";"1","2","3","7";"1","2","3","8";"1","2","3","9";"1","2","4","0";"1","2","4","1";"1","2","4","2";"1","2","4","3";"1","2","4","4";"1","2","4","5";"1","2","4","6";"1","2","4","7";"1","2","4","8";"1","2","4","9";"1","2","5","0";"1","2","5","1";"1","2","5","2";"1","2","5","3";"1","2","5","4";"1","2","5","5";"1","2","5","6";"1","2","5","7";"1","2","5","8";"1","2","5","9";"1","2","6","0";"1","2","6","1";"1","2","6","2";"1","2","6","3";"1","2","6","4";"1","2","6","5";"1","2","6","6";"1","2","6","7";"1","2","6","8";"1","2","6","9";"1","2","7","0";"1","2","7","1";"1","2","7","2";"1","2","7","3";"1","2","7","4";"1","2","7","5";"1","2","7","6";"1","2","7","7";"1","2","7","8";"1","2","7","9";"1","2","8","0";"1","2","8","1";"1","2","8","2";"1","2","8","3";"1","2","8","4";"1","2","8","5";"1","2","8","6";"1","2","8","7";"1","2","8","8";"1","2","8","9";"1","2","9","0";"1","2","9","1";"1","2","9","2";"1","2","9","3";"1","2","9","4";"1","2","9","5";"1","2","9","6";"1","2","9","7";"1","2","9","8";"1","2","9","9";"1","3","0","0";"1","3","0","1";"1","3","0","2";"1","3","0","3";"1","3","0","4";"1","3","0","5";"1","3","0","6";"1","3","0","7";"1","3","0","8";"1","3","0","9";"1","3","1","0";"1","3","1","1";"1","3","1","2";"1","3","1","3";"1","3","1","4";"1","3","1","5";"1","3","1","6";"1","3","1","7";"1","3","1","8";"1","3","1","9";"1","3","2","0";"1","3","2","1";"1","3","2","2";"1","3","2","3";"1","3","2","4";...}-1

    1后得到:

    {0,1,2,3;0,1,2,4;0,1,2,5;0,1,2,6;0,1,2,7;0,1,2,8;0,1,3,-1;0,1,3,0;0,1,3,1;0,1,3,2;0,1,3,3;0,1,3,4;0,1,3,5;0,1,3,6;0,1,3,7;0,1,3,8;0,1,4,-1;0,1,4,0;0,1,4,1;0,1,4,2;0,1,4,3;0,1,4,4;0,1,4,5;0,1,4,6;0,1,4,7;0,1,4,8;0,1,5,-1;0,1,5,0;0,1,5,1;0,1,5,2;0,1,5,3;0,1,5,4;0,1,5,5;0,1,5,6;0,1,5,7;0,1,5,8;0,1,6,-1;0,1,6,0;0,1,6,1;0,1,6,2;0,1,6,3;0,1,6,4;0,1,6,5;0,1,6,6;0,1,6,7;0,1,6,8;0,1,7,-1;0,1,7,0;0,1,7,1;0,1,7,2;0,1,7,3;0,1,7,4;0,1,7,5;0,1,7,6;0,1,7,7;0,1,7,8;0,1,8,-1;0,1,8,0;0,1,8,1;0,1,8,2;0,1,8,3;0,1,8,4;0,1,8,5;0,1,8,6;0,1,8,7;0,1,8,8;0,2,-1,-1;0,2,-1,0;0,2,-1,1;0,2,-1,2;0,2,-1,3;0,2,-1,4;0,2,-1,5;0,2,-1,6;0,2,-1,7;0,2,-1,8;0,2,0,-1;0,2,0,0;0,2,0,1;0,2,0,2;0,2,0,3;0,2,0,4;0,2,0,5;0,2,0,6;0,2,0,7;0,2,0,8;0,2,1,-1;0,2,1,0;0,2,1,1;0,2,1,2;0,2,1,3;...}

    由上文得出的结果,可知:

    IF(MMULT(0+(ISNUMBER(FIND({1,2,3,4},ROW(INDIRECT("1234:4321"))))),{1;1;1;1})=4,MID(ROW(INDIRECT("1234:4321")),{1,2,3,4},1)-1,"")

    转换为:

    IF({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;...},{0,1,2,3;0,1,2,4;0,1,2,5;0,1,2,6;0,1,2,7;0,1,2,8;0,1,3,-1;0,1,3,0;0,1,3,1;0,1,3,2;0,1,3,3;0,1,3,4;0,1,3,5;0,1,3,6;0,1,3,7;0,1,3,8;0,1,4,-1;0,1,4,0;0,1,4,1;0,1,4,2;0,1,4,3;0,1,4,4;0,1,4,5;0,1,4,6;0,1,4,7;0,1,4,8;0,1,5,-1;0,1,5,0;0,1,5,1;0,1,5,2;0,1,5,3;0,1,5,4;0,1,5,5;0,1,5,6;0,1,5,7;0,1,5,8;0,1,6,-1;0,1,6,0;0,1,6,1;0,1,6,2;0,1,6,3;0,1,6,4;0,1,6,5;0,1,6,6;0,1,6,7;0,1,6,8;0,1,7,-1;0,1,7,0;0,1,7,1;0,1,7,2;0,1,7,3;0,1,7,4;0,1,7,5;0,1,7,6;0,1,7,7;0,1,7,8;0,1,8,-1;0,1,8,0;0,1,8,1;0,1,8,2;0,1,8,3;0,1,8,4;0,1,8,5;0,1,8,6;0,1,8,7;0,1,8,8;0,2,-1,-1;0,2,-1,0;0,2,-1,1;0,2,-1,2;0,2,-1,3;0,2,-1,4;0,2,-1,5;0,2,-1,6;0,2,-1,7;0,2,-1,8;0,2,0,-1;0,2,0,0;0,2,0,1;0,2,0,2;0,2,0,3;0,2,0,4;0,2,0,5;0,2,0,6;0,2,0,7;0,2,0,8;0,2,1,-1;0,2,1,0;0,2,1,1;0,2,1,2;0,2,1,3;…},"")

    得到:

    {0,1,2,3;"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";0,1,3,2;"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";0,2,1,3;...}

    这个数组为OFFSET函数的rows参数值,而先前已讨论过,其cols参数值为{0,1,2,3},因此:

    N(OFFSET(A1,IF(MMULT(0+(ISNUMBER(FIND({1,2,3,4},ROW(INDIRECT("1234:4321"))))),{1;1;1;1})=4,MID(ROW(INDIRECT("1234:4321")),{1,2,3,4},1)-1,""),{0,1,2,3},,))

    转换为:

    N(OFFSET(A1,{0,1,2,3;"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";0,1,3,2;"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";0,2,1,3;...},{0,1,2,3},,))

    结果为:

    {1,6,3,2;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;1,6,4,2;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!,#VALUE!;1,7,2,2;...}

    传递给IFERROR函数,这样:

    IFERROR(N(OFFSET(A1,IF(MMULT(0+(ISNUMBER(FIND({1,2,3,4},ROW(INDIRECT("1234:4321"))))),{1;1;1;1})=4,MID(ROW(INDIRECT("1234:4321")),{1,2,3,4},1)-1,""),{0,1,2,3},,)),0)

    转换为:

    {1,6,3,2;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;1,6,4,2;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;1,7,2,2;...}

    数组中红色数字分别对应着执行下面的公式操作:

    N(OFFSET(A1,{0,1,2,3},{0,1,2,3},,))

    N(OFFSET(A1,{0,1,3,2},{0,1,2,3},,))

    N(OFFSET(A1,{0,2,1,3},{0,1,2,3},,))

    接着使用MMULT对已经生成的数组矩阵中的每行求和,因此:

    MMULT(IFERROR(N(OFFSET(A1,IF(MMULT(0+(ISNUMBER(FIND({1,2,3,4},ROW(INDIRECT("1234:4321"))))),{1;1;1;1})=4,MID(ROW(INDIRECT("1234:4321")),{1,2,3,4},1)-1,""),{0,1,2,3},,)),0),{1;1;1;1})

    转换为:

    MMULT({1,6,3,2;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;1,6,4,2;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;1,7,2,2;...},{1;1;1;1})

    得到:

    {12;0;0;0;0;0;0;0;0;13;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;12;...}

    然后,与单元格F2中的目标值比较:

    =SUM(0+(MMULT(IFERROR(N(OFFSET(A1,IF(MMULT(0+(ISNUMBER(FIND({1,2,3,4},ROW(INDIRECT("1234:4321"))))),{1;1;1;1})=4,MID(ROW(INDIRECT("1234:4321")),{1,2,3,4},1)-1,""),{0,1,2,3},,)),0),{1;1;1;1})=F2))

    转换为:

    =SUM(0+({12;0;0;0;0;0;0;0;0;13;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;12;...}=12))

    可以返回结果:

    6

    至此,公式解析完毕。

    现在,我们来看一下减少数组元素的方法。上文中,由

    ROW(INDIRECT("1234:4321"))

    生成了含有3088个元素的数组,而我们只对其中的24个元素感兴趣,也就是由1234进行排列的元素,因此与我们无关的有3064个元素,占总元素的99%以上。

    这里首先将这个数组减少到只有256个元素,这样无关元素比提高至了91%。采取下面的公式构造:

    TEXT(MMULT(MOD(INT((ROW(1:256)-1)/4^{3,2,1,0}),4),10^{3;2;1;0}),"0000")

    这种构造背后的原理是Excel与数学的综合。如果我们减少元素的数量,只考虑3×3的类似构造,将更容易解释,也更容易理解。此时,上面的公式构造等价于:

    TEXT(MMULT(MOD(INT((ROW(1:27)-1)/3^{2,1,0}),3),10^{2;1;0}),"000")

    暂时不考虑TEXTMMULT函数,此公式构造的关键是生成一个由三个整数组成的数组,包含数字012的所有可能排列。即:

    {0,0,0;0,0,1;0,0,2;0,1,0;0,1,1;0,1,2;0,2,0;0,2,1;0,2,2;1,0,0;1,0,1;1,0,2;1,1,0;1,1,1;1,1,2;1,2,0;1,2,1;1,2,2;2,0,0;2,0,1;2,0,2;2,1,0;2,1,1;2,1,2;2,2,0;2,2,1;2,2,2}

    这是上述构造中取27的原因。对于4个元素取256,因为n的元素的可能排列数为n^n,所以3^3=274^4=256

    这样,公式构造中的:

    MOD(INT((ROW(1:27)-1)/3^{2,1,0}),3)

    将转换成的数组是什么呢?

    实际上,我们在这里所做的就是将一系列以10为底的值转换为以3为底的值。因此,以10为底的026之间的值,我们可以用3的底数表示的等效表为:

    e6650085648aeb42f20bbccac71b5a09.png

    3

    这正是我们要生成的27个排列。

    对于以10为底的给定值,为了确定该值的以3为底的表示形式中存在的3^23^13^0的数量,首先确定3^23^13^0的以基数为10的值,然后对所得值取模(模为3)。例如,以10为底的值7,以3为底的值的表示形式为021,由于3^2=97中出现0次且MOD(0,3)=03^1=37中出现2次且MOD(2,3)=23^0=17中出现1次且MOD(1,3)=1。这意味着:

    MOD(INT((ROW(1:27)-1)/3^{2,1,0}),3)

    转换为:

    {0,0,0;0,0,1;0,0,2;0,1,0;0,1,1;0,1,2;0,2,0;0,2,1;0,2,2;1,0,0;1,0,1;1,0,2;1,1,0;1,1,1;1,1,2;1,2,0;1,2,1;1,2,2;2,0,0;2,0,1;2,0,2;2,1,0;2,1,1;2,1,2;2,2,0;2,2,1;2,2,2}

    然后,使用MMULT和合适的第二个数组将该数组合并为10为底的值:

    MMULT(MOD(INT((ROW(1:27)-1)/3^{2,1,0}),3),10^{2;1;0})

    转换为:

    MMULT({0,0,0;0,0,1;0,0,2;0,1,0;0,1,1;0,1,2;0,2,0;0,2,1;0,2,2;1,0,0;1,0,1;1,0,2;1,1,0;1,1,1;1,1,2;1,2,0;1,2,1;1,2,2;2,0,0;2,0,1;2,0,2;2,1,0;2,1,1;2,1,2;2,2,0;2,2,1;2,2,2},{100;10;1})

    得到:

    {0;1;2;10;11;12;20;21;22;100;101;102;110;111;112;120;121;122;200;201;202;210;211;212;220;221;222}

    最后,使用TEXT函数格式化以生成所需要的排列:

    TEXT(MMULT(MOD(INT((ROW(1:27)-1)/3^{2,1,0}),3),10^{2;1;0}),"000")

    得到结果:

    {"000";"001";"002";"010";"011";"012";"020";"021";"022";"100";"101";"102";"110";"111";"112";"120";"121";"122";"200";"201";"202";"210";"211";"212";"220";"221";"222"}

    小结

    1.找到规律,然后寻求解决之道。

    2.不仅要理解Excel函数原理,而且要打好数学基础,这是灵活应用公式的一切。

    23fdeb044a6c14869adc36c72e7d6a00.png

    展开全文
  • 今天,给大家列举10个能看懂、能学会的10个Excel函数公式,职场必备哦!一、提取指定。目的:从“文件编号”提取“年份”、“部门”、“编号”。方法:在目标单元格输入公式:=LEFT(B3,4)、=MID(B3,5,3)、=...
  • 本书从函数功能、函数格式、参数说明、注意事项、Excel 版本提醒、案例应用、交叉参考7 个方面,全面、细致地介绍了Excel 2016/2013/2010/2007/2003 中公式和函数的使用方法、实际应用和操作技巧。最后3 章还将公式...
  • 学习Excel技术,关注微信...本次的练习是:如下图1所示,在一个4行4列的单元格区域A1:D4,每个单元格内都是一个一整数,并且目标单元格(此处为F2)也为整数,要求在单元格G2编写一个公式返回单元格A1:D4...
  • Excel中16进制递增、公式后加符号显示的方法 2012年11月21日18:16:11 想做一张8086的中断向量表。用到了Excel中的进制转换函数、求商返回商整数部分的函数。 方法: 1、先用一列递增的10进制数做原始数据。然后...
  • Excel是我们日常办公经常用到的办公软件,今天,小编跟大家分享一下,在工作,最常用到的Excel函数公式,赶快来看一下吧!一、数字处理1、取绝对值=ABS(数字)2、取整-INT(数字)3、因舍五入-ROLND(数字,小数位数...
  • 下图红框的数字控制着随机数有效数字位数,如果想保留三有效数字,将其改成3即可。 3、拉伸填...答:1、如下图所示,在F6单元格输入指定的平均,需要在C3:H11单元格区域内生成一组随机数。要求这...
  • Excel函数公式中,有部分函数的使用率是比较低的,但是其功能也是非常强大的。一、Median函数。功能:返回一组数的中值。中值就是一组数的中间数值,如果参数包含的数值是偶数,Median函数将返回位于中间两个的...
  • 在平时使用公式出现错误时通常有一些错误,各个错误代表不同的含义,每个错误都有不同的原因和解决方法,下面就逐个来了解一下。快来学习吧!####错误此错误表示列不够宽,或者使用了负日期或时间.当列宽不足以...
  • 有不懂的可以加我好友 一起交流学习一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误显示为空 公式:C2=IFERROR(A2/B2,"") 说明...
  • 对于刚学习excel的小白,很多老师都会叮嘱他们,要注意表格规范、函数参数格式等等,这些固然重要,但是今天,苗老师还要告诉大家另一个需要引起重视的东西:小心超过15的数字。这是什么意思呢?一起来看看下面的...
  • Match函数 作用:提取指定的相对位置 语法结构:=MATCH(查找,查找范围,查找模式) 查找模式有0、-1、1三种,其中0...作用:提取查询范围指定行列交叉处的。 语法结构:INDEX(查询范围,行,列)。 ...
  • 应用场景下图表格A列身份证号第7到14数字为出生日期,将其提取出来返回至B列,并转换为用斜杠“/”分隔的标准日期格式,参考效果见下图。操作步骤1、选中B2单元格,在英文输入法状态下输入公式:=MID(A2,7,8),...
  • 中位数 》一组数据按顺序排序后的中间 若数据数量为奇数,那么取 位于(n+1)/2位置的数 若数据数量为偶数,那么取 最中间的两位数相加/2 公式:=median() 极差 》一组数据的最大-最小值 公式:=max()-min() ...
  • 身份证号码不单单是一串简单的数字,其蕴藏着很多重要的信息,例如,出生年月、性别等……那么,如何在Excel中提取身份证号码的出生年月、性别等信息呢?不妨从学习下文开始吧……一、Text+Mid:提取出生年月。...
  • Excel中的函数公式非常的繁多,如果全部掌握,几乎不可能,但是作为职场的我们,对常用的函数公式必须熟练掌握哦,对于我们的工作效率将会有很大的提高!一、Vlookup函数:查找引用。作用:返回指定范围内符合条件的...
  • 在平时使用公式出现错误时通常有一些错误,各个错误代表不同的含义,每个错误都有不同的原因和解决方法,下面就逐个来了解一下。快来学习吧!####错误此错误表示列不够宽,或者使用了负日期或时间.当列宽不足以...
  • 使用Excel校验VIN码(车架号)第九

    千次阅读 2018-09-20 14:33:03
    使用Excel校验VIN码第九键入公式说明样例 键入公式 Excel中 A1 单元格键入需要进行校验的VIN码 Excel中 B1 单元格键入如下公式: =SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE(...
  • 对于未分组数据,可使用Excel的MEDIAN函数求解中位数。 对于分组数据,分为: 1. 组离散数据的中位数: 首先要构造累积频率分布表,然后通过累积频率分布表确定数据的中位数对应的观测的位置,然后根据观测的...
  • 问题来源韩老师讲了Excel151 | 只给有销量的产品添加序号,就有一教师朋友问我:如果成绩为0不参加排名次,怎么写公式?示例数据与结果:排序函数rank函数:语法:RANK(number,ref,[order])中文语法:RANK(要找到...
  • 它包括合并工具、财务工具、图片工具、重复工具、文件处理工具、打印工具、一键录入公式等等多种类型的工具集合,适用于各行业的办公文员。 《E灵4.0》包含约140个功能, 支持Excel 2007、2010、2013和2016,也...
  • 最近工作中需要处理一个实际的问题:找出几类产品(实际上一共是10类)...只可惜,公司用的mysql库,并没有内置中位数函数median()。那怎么办呢?导出来用excel吧,之前100万条数据的时候,我就分产品导出来,然后...
  • EXCEL中12个条件判断

    2020-07-23 15:31:31
    在目标单元格输入公式:=IF(ISODD(MID(C3,17,1)),"男","女")。 解读: 1、身份证号码的第17代表性别,如果为奇数,则为男性,否则为女性。 2、公式中用Mid函数提取第17,作为Isodd函数的参数,Isodd函数的...
  • 最近工作中需要处理一个实际的问题:找出几类产品(实际上一共是10类)...只可惜,公司用的mysql库,并没有内置中位数函数median()。那怎么办呢?导出来用excel吧,之前100万条数据的时候,我就分产品导出来,然后...

空空如也

空空如也

1 2 3 4 5 ... 10
收藏数 200
精华内容 80
关键字:

中位值excel公式