在Excel中使用不等式进行字符串比较 [英] String Comparison Using Inequalities in Excel
问题描述
有人可以提供有关Excel如何使用Excel中的不等式运算符比较字符串的参考吗?我发现了一些意外的行为:
Can someone provide a reference to how excel compares strings using inequality operators in Excel? I have found some unexpected behavior:
在以下示例中,前两列为字符串,列C为表达式=A2<B2
,第三列为预期行为,最后一列为预期行为是否与实际匹配.
In the below examples, the first two columns are strings, column C is of the expression =A2<B2
, the third column is the expected behavior, and the final column is whether the expected behavior matches the actual.
Comparison Benchmark Is A < B? Expected Pass?
Q1 2009 Q4 2012 TRUE TRUE TRUE
Q2 2009 Q4 2012 TRUE TRUE TRUE
Q3 2009 Q4 2012 TRUE TRUE TRUE
Q4 2009 Q4 2012 TRUE TRUE TRUE
Q1 2010 Q4 2012 TRUE TRUE TRUE
Q2 2010 Q4 2012 TRUE TRUE TRUE
Q3 2010 Q4 2012 TRUE TRUE TRUE
Q4 2010 Q4 2012 TRUE TRUE TRUE
Q1 2011 Q4 2012 TRUE TRUE TRUE
Q2 2011 Q4 2012 TRUE TRUE TRUE
Q3 2011 Q4 2012 TRUE TRUE TRUE
Q4 2011 Q4 2012 TRUE TRUE TRUE
Q1 2012 Q4 2012 TRUE TRUE TRUE
Q2 2012 Q4 2012 TRUE TRUE TRUE
Q3 2012 Q4 2012 TRUE TRUE TRUE
Q4 2012 Q4 2012 FALSE FALSE TRUE
Q1 2013 Q4 2012 TRUE FALSE FALSE
Q2 2013 Q4 2012 TRUE FALSE FALSE
Q3 2013 Q4 2012 TRUE FALSE FALSE
Q4 2013 Q4 2012 FALSE FALSE TRUE
Q1 2014 Q4 2012 TRUE FALSE FALSE
Q2 2014 Q4 2012 TRUE FALSE FALSE
Q3 2014 Q4 2012 TRUE FALSE FALSE
对于基准测试之前的日期,Excel似乎会比较整个字符串,而在基准测试日期之后,Excel只会比较字符串,直到发现比比较中的类似字符少的单个字符为止.
For dates before the benchmark, it seems like Excel compares the whole string, whereas after the benchmark date, Excel only compares the string until it finds a single character less than the analogous character in the comparison.
推荐答案
Excel此处执行的操作是从左到右逐个字符地比较两个字符串,直到发现不匹配为止,然后具有较高字符值的字符串变得更大.细绳.这是比较字符串的标准方法-几乎所有编程语言,数据库和其他软件包都使用了这种方法.
What Excel does here is it compares the two strings character by character left to right until it finds a mismatch, and then the string with higher value character becomes the greater string. This is the standard method to compare strings -- it's used in pretty much all programming languages, databases and other software packages.
例如,对于第一行,比较将如下所示:
So, for example, for the first line the comparison would go like this:
Comparison Benchmark Outcome
Q Q Equal
1 4 Benchmark is greater - done comparing
[space] [space] Ignored
2 2 Ignored
0 0 Ignored
0 1 Ignored
9 2 Ignored
第四行是:
Comparison Benchmark Outcome
Q Q Equal
4 4 Equal
[space] [space] Equal
2 2 Equal
0 0 Equal
0 1 Benchmark is greater - done comparing
9 2 Ignored
最后一行是:
Comparison Benchmark Outcome
Q Q Equal
3 4 Benchmark is greater - done comparing
[space] [space] Ignored
2 2 Ignored
0 0 Ignored
1 1 Ignored
4 2 Ignored
这篇关于在Excel中使用不等式进行字符串比较的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!