文字排序顺序的MIN/MAX [英] MIN/MAX on text sort order

问题描述

在SQL Server中，MIN和MAX可以作用于不会计算为数字的文本，返回具有最低或最高文本排序顺序的文本项，或以SQL Server所说的排序规则"的方式返回.

是否可以在Excel中执行此操作而无需进入实际排序的UDF?

例如，对于MIN("bb"，"aa"，"cc")返回"aa"，而MAX("bb"，"cc"，"aa")返回"cc".

Excel的MIN/MAX会忽略文本，尽管MINA/MAXA可以处理文本，但它们会破坏无法解析为数字的文本.大/小也不做.

FWIW，一位同事问我如何做到这一点.我看不到不使用自定义功能的方法.我错了吗?

解决方案

此数组公式看起来很有希望.由于它是一个数组，因此需要使用ctrl-shift-enter输入.

最大:

=INDEX(A2:A6,MATCH(0,COUNTIF(A2:A6,">"&A2:A6), 0))

最小值:

=INDEX(A2:A6,MATCH(0,COUNTIF(A2:A6,"<"&A2:A6), 0))

将三个范围更改为所需的范围.

除了>与<之外，最大版本和最小版本相同.

In SQL Server, MIN and MAX can act on text that doesn't evaluate to numbers, returning the text item with the lowest or highest text sort order, or as it's known in SQL Server-speak, "collation order".

Is it possible to do that in Excel without going to a UDF that actually sorts?

For example, for MIN("bb","aa","cc") to return "aa", and MAX("bb","cc","aa") to return "cc".

Excel's MIN/MAX ignore text, and although MINA/MAXA can work on text, they break on text that doesn't resolve to a number. LARGE/SMALL don't do it either.

FWIW, a coworker asked me how to do this in a pivot. I don't see a way without going to a custom function. Am I wrong?

解决方案

This array formula looks promising. since it is an array it needs to be entered with ctrl-shift-enter.

Max:

=INDEX(A2:A6,MATCH(0,COUNTIF(A2:A6,">"&A2:A6), 0))

Min:

=INDEX(A2:A6,MATCH(0,COUNTIF(A2:A6,"<"&A2:A6), 0))

Change the three ranges to what you want.

The max and min versions are the same except for the > versus <.

这篇关于文字排序顺序的MIN/MAX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！