excel图表如何按顺序排列
发布时间:2022-04-22 18:06:37
给定单元格区域A1:D5(其中每个单元格中都是整数,并且在该单元格区域内是唯一的),使用单个公式生成一个数组,该数组由该区域中所有连续的数值组成,连续的数值至少包含两个,且返回的数组中的元素按从小到大的顺序排列。
图1
也就是说,可以返回结果:
{1;2;3;12;13;14;15;16;17;36;37}
也可以返回结果:
{1,2,3,12,13,14,15,16,17,36,37}
注意,所给出的公式应包含最少的字符;在公式中必须同时包含行列引用,不允许全是列引用(如A:D)或行引用(如1:5);公式中不允许使用名称。
先不看答案,自已动手试一试。
公式
公式1:
=MODE.MULT(SMALL(A1:D5,ROW(A1:A20)),IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5))
使用了91个字符。
公式2:
=MODE.MULT(IFERROR(SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1},””))
使用了103个字符。
尝试通过同时评估两个COUNTIF构造来缩短此公式构造-并消除了对IFERROR子句的需要,但最终比上述公式稍长一些,有105字符,即公式3:
=MODE.MULT(IF(MMULT(COUNTIF(A1:D5,SMALL(A1:D5,ROW(A1:A20))-{1,-1}),{1;1}),{1,1}*SMALL(A1:D5,ROW(A1:A20))))
或者公式4:
=MODE.MULT(IF(MMULT(COUNTIF(A1:D5,SMALL(A1:D5,ROW(A1:A20))-{1,-1}),{1;1}),SMALL(A1:D5,ROW(A1:A20))),A1:D5)
公式解析
由于我们想要从所给区域中返回一个数组,该数组由区域内至少两个连续的数值构成,因此,执行此操作的一种方法是针对区域内的每个值检查是否在该区域内还会发现比该值大1或小1的值。
这样,我们需要查看下面两种构造的结果:
COUNTIF(A1:D5,A1:D5+1)
和
COUNTIF(A1:D5,A1:D5-1)
由于我们要求对于每个值,其两个返回值中只有一个为非零值即可,因此可以通过简单地将上述构造加在一起,这等价于OR操作。于是,公式中的:
COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1)
转换为:
{0,0,1,0;0,0,0,0;1,1,0,1;1,0,1,1;1,0,0,0}+{0,1,1,0;0,0,0,1;0,1,1,1;1,0,0,0;1,0,0,0}
结果为:
{0,1,2,0;0,0,0,1;1,2,1,2;2,0,1,1;2,0,0,0}
从中可以看出,元素0表示:在区域中未找到比要查找的值大1或小1的值。同样,元素1表示:在区域中找到了比要查找的值大1或小1的值。值2表示在区域中找到了比要查找的值大1的值和小1的值。因此:
IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5)
转换为:
IF({0,1,2,0;0,0,0,1;1,2,1,2;2,0,1,1;2,0,0,0},A1:D5)
得到:
{FALSE,17,14,FALSE;FALSE,FALSE,FALSE,37;12,15,3,2;13,FALSE,1,36;16,FALSE,FALSE,FALSE}
现在,我们已经生成了所需的值。但是,仍然存在两项工作要解决:按升序对它们进行排序以及删除任何非数字元素。
使用SMALL函数来完成排序操作:
SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))
转换为:
SMALL({FALSE,17,14,FALSE;FALSE,FALSE,FALSE,37;12,15,3,2;13,FALSE,1,36;16,FALSE,FALSE,FALSE},ROW(A1:A20))
转换为:
SMALL({FALSE,17,14,FALSE;FALSE,FALSE,FALSE,37;12,15,3,2;13,FALSE,1,36;16,FALSE,FALSE,FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20})
结果为:
{1;2;3;12;13;14;15;16;17;36;37;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}
通常,我们可能试图通过构造一个合适的数组传递给SMALL函数作为其参数k的值来缩小数组,然而,这里使用了MODE.MULT函数,更简洁。注意,这里使用的缩小数组的技术,当我们希望返回的元素在被处理的数组中出现的频率相等时才合适。此外,该频率必须至少为两倍,因为如果没有一个以上的值出现,那么MODE.MULT不会返回任何值。
因此,先与数组{1,1}相乘的原因是,当我们将SMALL构造(一个20行1列的数组)与数组{1,1}(一个1行2列的数组)相乘时,生成20行2列的数组,其每行中列的元素都是相同的。这样:
SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1}
转换为:
{1;2;3;12;13;14;15;16;17;36;37;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}*{1,1}
结果为:
{1,1;2,2;3,3;12,12;13,13;14,14;15,15;16,16;17,17;36,36;37,37;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!}
可以看出,我们已经成功地使每个元素的数量简单地翻了一倍,现在可以安全地将其传递给MODE.MULT函数。但是,我们先要消除这些错误值。像绝大多数函数一样,MODE.MULT函数不会处理包含此类错误值的数组。
这样,公式中的:
IFERROR(SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1},””)
转换为:
IFERROR({1,1;2,2;3,3;12,12;13,13;14,14;15,15;16,16;17,17;36,36;37,37;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!;#NUM!,#NUM!},””)
得到:
{1,1;2,2;3,3;12,12;13,13;14,14;15,15;16,16;17,17;36,36;37,37;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””}
最后,将其传递给MODE.MULT函数:
MODE.MULT(IFERROR(SMALL(IF(COUNTIF(A1:D5,A1:D5+1)+COUNTIF(A1:D5,A1:D5-1),A1:D5),ROW(A1:A20))*{1,1},””))
转换为:
MODE.MULT({1,1;2,2;3,3;12,12;13,13;14,14;15,15;16,16;17,17;36,36;37,37;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””;””,””})
得到:
{1;2;3;12;13;14;15;16;17;36;37}
小结
MODE.MULT函数的妙用!
猜你喜欢
- 苹果官方在近日发布了 iOS 13.6 以及 iOS 14 的第三个测试版,有很多用户想进行升级体验,不过升级的过程中,可能会遇到 iPho
- 本教程带来的是我经常会做到的条形码,可能我用的条形码并非今天说的这类,不过原理都差不多,这次主要是用excel2003制作条形码,能够实现自
- 1、原始数据表如图所示,其中分别在一些单元格中标注了黄色和红色。 2、单击“产品名称”列上的筛选按钮,选择“按颜色
- word中怎么给文字添加边框?word中有很多文字,想将一句话用方框框起来,该怎么设置呢?下面我们就来看看word文字添加边框的教程,很简单
- 关于win10系统的众多版本,有没有想过用win10专业版来玩游戏,或者是企业版更好。笔者觉得两种系统性能上没有什么差别,只是表现出功能上的
- 公司HR小李制作了一份Excel2013电子表格,需要交到各个员工手上进行填写,但是又怕有的人乱写,或者格式不规范,免得自己又要重新修改。如
- 如果WORD文档里面有很多个括号,并且,每一对括号里面也有内容,那么,我们如何才能一次性就删除WORD文档里面的所有内容呢?如果WORD文档
- word教程刚刚群里一位朋友突然问到在Word里面“五角星符号怎么打”,让我教他。其实插入特殊字符里面就有这个符号,而且输入五角星符号的方法
- 对于职场人来说,工作中使用Word早已成了习惯。而如何提高Word的操作效率,是许多职场人一直都需要研究探索的课题。因此,今天小编将为大家分
- 许多用户在使用电脑的时候,经常会在网上下载一些资源进行安装,但是最近有不少用户在使用电脑下载安装软件的时候经常会下载到msu后缀格式的文件,
- 在稍复杂的EXCEL数据表中,我们需要知道数据区第一行、第一列、数值区各属什么类别,因此,需要制作斜线表头。如下:或:前者是单斜线表头,后者
- 十天前,苹果给大家发布了iOS 14.4.1正式版本,该版本主要是进行安全漏洞方面的修补,修复了一个可能导致浏览器执行恶意代码的问题,并通过
- 之前看到很多媒体都报道了Windows 10会自带Office的消息,让很多网友都误以为只要升级或全新安装Windows 10就可以获取到最
- Excel 2007的筛选和排序功能的使用,使用word处理表格,数据较少的情况下还可以,数据量稍微大一点,还是得请Excel 2007这位
- Excel函数一共有11类,分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函
- Excel作为一款表格办公软件很多时候可以用它来完成很多复杂的工作,我们还可以使用excel可以完成排序工作,在排序的时候如何设置表头不参与
- 今天的安卓手机的综合体验、性能并不输于苹果,甚至在很多方面安卓的技术要更为领先一些。但即便是这样,安卓手机在全球的销量表现依旧不及iPhon
- 第一步:打开Excel表格,单击第一排,第三个“单元格”,也就是C1,在C1中输入“=A1-B1”; 第二步:这个
- 文字格式主要包括字体、字号、颜色等等,使用格式后文章看起来很整齐,也有利于阅读,下面我们通过一个练习来学习设置文字格式;1、选择字体1)启动