excel公式技巧:使用公式排序
正文:
excel公式技巧:使用公式排序
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的排序功能但使用公式可以实时更新数据。
其他类似文章:excel如何设置单色打印 excel设置单色打印操作方法 >>>进入阅读
在日常工作中经常会使用excel有时表格中由于某些原因设置了很多背景色在打印的时候这些颜色也会打印出来如何单色打印表格呢? >>>进入阅读