☰

按本地化字母顺序对 Excel 表格进行排序

tag-icon • tag-icon microsoft-excel sorting localization
按本地化字母顺序对 Excel 表格进行排序

英文 Excel（错误地）对以下字符串进行排序，如下所示：

SARKIN
ŞENER
SEZER
ŞİMŞEK
SÜRMELİ

但是，这些都是土耳其语字符串，其中 S 和 Ş 是严格不同的字母，并且 Ş 位于 S 之后。因此，正确的土耳其语排序应如下：

SARKIN
SEZER
SÜRMELİ
ŞENER
ŞİMŞEK

如何使英语 Excel 正确排序本地化字符串，特别是土耳其语？

答案1

我怀疑您已将土耳其语设置为系统语言，并且已将其设置在 Excel 自己的语言设置中。如果您使用本地化的“SIRALA”功能，它可能会实现正确的土耳其语字母排序顺序。您可以使用 Windows 语言栏快捷键在系统语言之间快速切换（左移 + ALT，或 Windows 按钮 + 空格）。希望 Excel 同时切换自己的语言系统。

或者，创建一个辅助列并根据此列进行排序：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A1),"Ç","Czzzzz"),"Ğ","Gzzzzz"),"İ","Izzzzz"),"Ö","Ozzzzz"),"Ş","Szzzzz"),"Ü","Uzzzzz")

答案2

下列操作有效：

=LET(Range, A1:A5,  Sort, IFERROR(UNICHAR(SORT(TEXT(IFERROR(UNICODE(MID(Range,SEQUENCE(1,MAX(LEN(Range))),1)),0),"0000")))," "),

     TRIM(MID(TEXTJOIN("",FALSE,Sort),SEQUENCE(ROWS(Range),1,1,MAX(LEN(Range))),MAX(LEN(Range))))
     )

它并不属于“优雅”类型，只是解决问题并取得圆满结果。

将名称放在第一行的意义Range在于，它位于公式的绝对开头，因此即使仅通过键盘也可以看到并轻松选择，以便编辑以匹配当时的确切范围。这比在长公式中搜索所有出现的情况或必须选择单元格和另一个单元格来执行操作要好得多Find and Replace。我喜欢首先将它们全部放在一起，主要是因为这个原因，但也因为如果我有两个以上，我会将“主要”名称放在第一行（这样我可以轻松编辑它们），然后将次要名称放在后面的行中。我也喜欢按层次结构执行它们，因此第二行使用第一行中的内容，但不使用其自身中的内容，因为这些内容进入第三行，依此类推。这确保我不会操之过急并使用尚未定义的名称。（我还对上面显示的公式进行了一些编辑，以便它显示了缩进排列。但是，如果您复制并粘贴，则需要在 >第一行中添加几个空格以恢复它们在 Excel 中的显示Formula Bar。

（顺便说一下，你可以将Formula Bar底部边缘向下拖动，这样就可以显示任意多的行。需要 13 行才能看到整个公式吗？只需将其向下拖动即可。）

在 中LET()，Sort名称执行以下操作：

  1. MID()将每个单词拆分成每个字符的字符串。这里的“陷阱”是，如果您在 中对“ROWS”参数使用除“1”以外的值SEQUENCE()，则会出现问题。您可能会认为“5”可以解决此问题，但是...
  2. UNICODE()获取范围内最长单词的字符数的 Unicode 字符值。
  3. IFERROR()将错误（当许多单词少于最长单词的字符数时发生）转换为零。不是空白，因为您希望它们在下一步中成功。
  4. TEXT()将这些值转换为文本，这本身并不是必需的，但是为了使值的长度达到四个字符，以便接下来的“字母排序”步骤能够顺利进行，这是必不可少的。如果 Unicode 值可以更长，您可以为每个值的文本输出添加更多数字。您将对每个值进行排序，将其作为完整的文本字符串，而不是数字集合，因此您需要这样做。这类似于以表单形式存储日期的技术，(yy)yymmdd以便它们按文本排序，而不是像 Excel 那样使用分配给它们的数值。以这种方式执行此操作，将其作为大小相等的块的字符串，可以按第一个字母排序，然后按第二个字母排序，依此类推，正如人们所期望的那样，而不必提供动态的多个单独排序，就好像单词中的每个位置都是一列一样。
  5. SORT()对字符串进行排序。
  6. UNICHAR()返回字母。空白处会失败，因此...
  7. IFERROR()将这些错误转换为单个空格。它们将全部出现在末尾，稍后TRIM()将删除它们。（TRIM()不能在这个“一半”中使用，否则单词会在错误的地方变成新词。）

现在您有了按单词排序的单个字母数组。

然后“工作公式”，即公式的后半部分（您可以将它全部放在一个较长的公式字符串中，但我认为在此处将其断开似乎很自然），开始工作：

  1. TEXTJOIN()将所有字符（包括空格）收集到一个字符串中。也许 Excel 给媒体留下深刻印象的新文本/数组函数会在一两年内使这一过程变得更简单，但就目前而言……实际上，如果CONCAT()需要的话，这种情况会很好用，因为这里没有添加分隔符，所以末尾没有多余的分隔符，这通常是它的问题。在这种情况下，您可能可以编写一个优雅的程序，INDEX()它会为您提供七个字符长的字符串（在本例中），但这只是分块……
  2. MID()将字符串分成七个字符块，用来SEQUENCE()生成多少行（ROWS(Range)部分），您需要一个输出列（所以不是“七”，希望提供前半部分数组中的列数为七的事实：您专注于这里的输出，那是一个单列。它使用第三和第四个参数从字符 1 开始，并取七个字符长的片段......所以七（MAX(LEN(Range))）确实发挥了作用，只是不在函数的两个地方。
  3. TRIM()然后删除大多数这些字符串末尾的多余空格。这里的优点是，任何单词中出现的单个空格（我知道，单词与单词，但我们都知道，我们首先想到的“单词”（单数）往往是两个或多个单词（复数）或至少两组或多组字母之间有空格。）都会被保留。我熟悉的语言（不是土耳其语，这一点很遗憾！）不会在构成单个“单词”的单词之间使用多个空格，因此TRIM()在公式中呈现的单词中不会造成任何问题。

现在您有了一个正确排序的列表。“正确”在计算机术语中，因为大多数计算机进行的排序实际上是按字符“值”排序（如果幸运的话，则是按 Unicode 字符值排序……所以在 Excel 中不是这样……）。

我确实指出了两个潜在的问题：

  1. 如果您的操作系统和/或 Excel 语言版本以土耳其语为本机语言，则可能会发现遵循 Unicode 值并不是对土耳其语进行排序的方式。我不知道，所以我无法谈论这一点，或者如何在这里捕获和允许这种差异。因此，此公式的输出可能无法完美地捕获正确的土耳其语排序，并且可能不容易修复。但是，如果土耳其语排序确实遵循 Unicode 字符值（并且我相信在大多数 Unicode 语言块中放置字符的人在这样做时至少会考虑排序），那么这将做到这一点。

关于“Excel 土耳其语版本”的澄清点：我的意思并不是简单地在 Excel 中安装土耳其语包。我尝试过这样做，看看它是否能工作（或提供额外的标准数字/日期格式选项），但并没有。我想它现在会拼写检查土耳其语单词，如果我需要的话（没有找到卸载它的方法，所以我想我会保留它），也许还有一些其他的小东西。如果我告诉它作为默认语言，我想会更大，但对我没什么用。顺便说一句，我没有这样做，所以实际上，如果我这样做，对土耳其语单词进行排序可能立即就没有任何问题了，但我对此表示强烈怀疑。我认为它更像是错误消息和菜单项（哎呀……对不起……RIBBON 项目……Ribbon 不是带有许多子菜单的菜单系统，MS 广告多年来一直这么说）将使用土耳其语。2. 该TEXTJOIN()命令在公式内部构建一个字符串。通常，这类东西都有长度限制，公式中受长度限制的任何东西的最长长度限制约为 32,767 个字符，通常更少，有时只有 6,000 多一点。并非所有内部数组都是内部字符串，但这个肯定是。实际上，这是一个确定的事实：刚刚进行了实验，TEXTJOIN()很高兴构建了一个 32,767 个字符长的字符串，但当#CALC!我再添加一个字符时会出现错误。

所以……如果你的列表不是太长，这个公式就行得通。这组五个单词的长度是七，所以 4,681 个单词（恰好，很有趣）是可行的。

如果达到 32,767 个字符的限制，仅包含部分的辅助列IFERROR(TEXT())就可以让您完美地排序，无论列表有多长和有多少个字符，至少最多可达 Excel 可以操作的 4GB（减去酒店负载）。

这将是提及我上面提到的更优雅的解决方案的好时机，可以使用INDEX()而不是，TEXTJOIN()这可能是一个有用的想法。TEXTJOIN()使该过程创建一个长度为“ROWS * MAX（LEN（individual words））的字符串，或在本例中为 5*7 = 35，这很容易与公式内部工作中的最大字符串长度相冲突。它将需要不到 5,000 个单词，最长的只有七个字符。只需一个 20 个字符的单词，由于创建了该字符串，您将减少到 1,600 个左右。

INDEX()会创建一个字符串数组，而不是单个长字符串。然后该限制适用于它创建的每个字符串。您的数据中没有 32,767 个字符长的名称，因此这个问题就消失了。在我试验过的其他东西中，我从未遇到INDEX()无法使用任何范围的情况，即使是非常大的范围。我遇到过其他函数在达到某个限制时创建发育不良的数组（以元素中间结束，甚至没有在最后一个完整元素处截断数组），而且很可能也INDEX()会这样做，只是我从未以这种方式使用它。我的用途似乎是寻找特定的行（INDEX/MATCH-style）或足够小的子集以传递给其他东西，这样我从未达到限制。所以甚至INDEX()可能无法处理大量的名称。由于我现在要结束了（很难想象此时还有人在阅读答案！），并且没有尝试在这里使用它，我不能谈论这个但是......如果你发现上述内容有用，它似乎是值得的，但它在数据集方面太有限了，它可以一次对所有数据集进行排序，因为你通常可能有超出其限制的数据集。

相关内容