在 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，第三列是预期行为，最后一列是是否预期行为与实际相符.

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屋！