excel公式技巧:使用公式排序
发布时间:2022-05-04 21:04:42
Excel提供了排序功能,可以方便地对选中的列表进行排序。本文给出一个基于公式的排序解决方案,将指定区域内的数据按字母顺序排序。
如下图1所示,在单元格区域A2:A11中是一组未排序的数据,在单元格区域B2:B11中是已排序的数据。
图1
解决方案
在单元格B2中输入公式:
=LOOKUP(1,0/FREQUENCY(ROWS($1:1),COUNTIF($A$2:$A$11,”<=”&$A$2:$A$11)),$A$2:$A$11)
向下拉至单元格B11。
工作原理
让我们以单元格B8中的公式为例来分析:
=LOOKUP(1,0/FREQUENCY(ROWS($1:7),COUNTIF($A$2:$A$11,”<=”&$A$2:$A$11)),$A$2:$A$11)
与单元格B2中的公式相比,唯一的变化是ROWS函数内由1改成了7。
公式中:
COUNTIF($A$2:$A$11,”<=”&$A$2:$A$11)
对于该区域内的每个字符串,返回一个值数组,对应该区域内按字母顺序位于该字符串之前或等于该字符串的字符串数。因此,上述公式转换为:
{3;4;9;5;7;2;1;6;10;8}
例如,所得到的数组中的第7个元素是1,是单元格B8中的字符串“Belinda”比较后的结果:按字母顺序,在区域内只有一个字符串在该字符串之前或等于该字符串,因此该字符串就是“Belinda”自身。
同样,在所得到的数组中的第2个元素是4,对应单元格B3中的“Laquita”比较后的结果:按字母顺序,在区域内有四个字符串在该字符串之前或等于该字符串,分别是“Belinda”、“Bula”、“Cathy”和“Laquita”自身。
现在,将这个数组作为参数bins_array的值传递给FREQUENCY函数,将公式所在单元格对应行的相对行号(此处为7,由ROWS($ 1:7)给出)作为参数data_array的值。这样:
FREQUENCY(ROWS($1:7),COUNTIF($A$2:$A$11,”<=”&$A$2:$A$11))
转换为:
FREQUENCY(7,{3;4;9;5;7;2;1;6;10;8})
得到:
{0;0;0;0;1;0;0;0;0;0;0}
然后,选择适当的值来调整该数组(这里选择的是0;也可选择1,这样的话lookup_value的值应为2而不是1更保险),此时:
0/FREQUENCY(ROWS($1:7),COUNTIF($A$2:$A$11,”<=”&$A$2:$A$11))
转换为:
{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}
将其传递给LOOKUP函数,公式:
=LOOKUP(1,0/FREQUENCY(ROWS($1:7),COUNTIF($A$2:$A$11,”<=”&$A$2:$A$11)),$A$2:$A$11)
转换为:
LOOKUP(1,{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},$A$2:$A$11)
在数组中唯一的数字在第5位,因此可得到结果:
Raymonde
也可以使用下面的公式获得同样的结果:
=INDEX($A$2:$A$11,MATCH(1,FREQUENCY(ROWS($1:7),COUNTIF($A$2:$A$11,”<=”&$A$2:$A$11)),0))
小结
虽然可以使用Excel的排序功能,但使用公式可以实时更新数据。
猜你喜欢
- word文档打不开多半是因为感染性病毒感染电脑中所有exe文件所致,你可以尝试用下面的方法解决word文档打不开的问题。"word
- Windows 和 微软Office软件属于收费软件,在过了试用期后,需要购买密钥(许可证)并激活后才可以使用。在此,我们备用了激活工具,用
- 使用word进行整理和粘贴数据的过程中,我们经常会用到的就是换行功能了,那么word怎么实现自动换行呢,下面分享下简单的实现过程,希望能帮助
- 电脑c盘又又又满了,按照网上的教程扩展c盘的内存,但是扩展卷是灰色的,无法点击,这该怎么办?其实我们应该先在框内空闲区域建一个分区,在Dis
- word软件是office办公软件中的一部分,一出现就受到了很多用户的喜欢,这款办公软件是用户用来编辑文本内容的软件,一般用户会用来编辑论文
- 第1步,打开Word2013文档窗口,依次单击“文件”→“选项”按钮。 第2步,打开“Word选项”对话框,切换到
- 死磕数据也要玩出花样~Excel 读取单元格内容功能,录入数据同步读音校对,让耳朵替眼睛分担分担压力!方法:文件>选项>快速访问
- 除了使用鼠标选定文本外,用户还可以使用键盘上的组合键选取文本。在使用组合键选择文本前,用户应该根据需要将光标定位在适当的位置,然后再按下相应
- 在Word文档中如何将文字放大或缩小呢?Word文字放大缩小快捷键。1、缩小放大文字先选中文字,按下快捷键Ctrl+shift+</&
- 对办会计和财务人员来说,Excel表格是日常生活中必须要用到的软件之一。在使用过程中,难免会出现一些症状,比如Excel打不开了,那么exc
- word右侧灰色区域删除的方法:1、点击图中的“最终状态”按钮:2、点击图中的”以嵌入方式显示所有修订“的按钮:
- 1、启动Excel2013,圈中数据区域,单击菜单栏--插入--柱形图,选择二维柱形图中的簇状柱形图。 2、鼠标选
- 现在很多电脑都能够将电脑屏幕画面投屏到电视上,有很多小伙伴就会把电影或是游戏,投屏到电视上,更大的画面让人更加的享受。那么如何使用Win10
- 在绘制表格的时候,我们通常都是见到一些连续的单元格输入相同或者顺序的数字时,我们可以拖动选定区域达到填充指定内容的效果!但是当我们遇到那些不
- 使用excel提供的提取字符串函数,能够快速准确地提取相关信息,下面小编就为大家介绍excel如何提取身份证信息中的数据方法介绍,操作很简单
- 在实际工作中,用户需要对比的数据可能不是位置一一相对应的。如果工作表中两列编号的排列顺序并不相同,要把两列不匹配的编号一一标记出来,条件格式
- word中自带纵横混排的功能,但是用起来非常的不方便,所以笔者建议大家用文本框来实现纵横混排。而且你还可以给文本框设置背景颜色,更改文字也非
- 首先,在需要统计的这十几个选项的最后(末尾)留一个空格(即增加一格或行,作为统计的总和),请将这十几个选项刷黑(包括空格)刷黑,点击右键,在
- word文档编写中,经常要调整字距与行距,以下内容是word文档字距与行距调整方法。一、调整汉字与英文字母间的距离word中的汉字、英文字母
- excel2013怎么给自定义的数字加单位?很多朋友都不是很清楚,下面小编就为大家详细介绍一下,不会的朋友可以参考本文,希望能对大家有所帮助