excel - Excel 中与区域设置无关的文本函数

Question

我需要在 Excel 中设置日期格式，并且我正在尝试使用 TEXT 公式。问题在于，当区域设置发生变化时，Excel 对参数的解释也会发生变化。

例如:如果我在单元格 A1 中有一个日期，我想将其转换为年-月-日格式的文本，我必须使用 =TEXT(A1, "yyyy- mm-dd") 如果我的电脑有英语区域设置，但是 =TEXT(A1, "jjjj-MM-tt") (我没有骗你，如果具有德语语言环境，则 M 必须为大写)。这使得该文档无法移植。 (第二个参数是纯文本，因此在更改区域设置时不会转换。)

备注:

• 这只是一个例子，我知道我可以做很长的 =YEAR(A1) & "-"& TEXT(MONTH(A1), "00") & "-"& TEXT(在本例中为 DAY(A1), "00")。我想知道更一般的情况。

• 日期不应该仅仅以某种格式显示，它实际上应该是一个字符串。对于查看该文件的人来说，这没有什么区别，但在其他公式中使用它时，就会有区别。

• 我可以用 VBA 编写 UDF 来解决该问题，但我无法在本文档中使用 VBA。

• 我不关心更改月份名称等。如果月份名称是 June 或 Juni(具体取决于区域设置)，那就没问题。

• 我想强调的是，出现此问题的原因是 PC 的区域设置，而不是 MS Office 版本的 GUI 语言。在上面的示例中，Excel 的 GUI 和公式在两个示例中都是英文的；我刚刚更改了计算机上的区域设置。

非常感谢

Answer 1

这里有一个稍微欺骗性的方法:对将根据您的系统语言而变化的值使用 VLOOKUP - 例如 TEXT(1,"MMMM")

=VLOOKUP(TEXT(1,"MMMM"),{"January","yyyy-MM-dd";"Januar","jjjj-MM-tt"},2,FALSE)

英语:Text(1,"MMMM") = "January"，因此我们对下面的数组执行 VLOOKUP 以获得“yyyy-MM-dd” ”

"January" , "yyyy-MM-dd" ;
"Januar"  , "jjjj-MM-tt"

Auf Deutsche，Text(1,"MMMM") = "Januar"，也 wir machen einen SVERWEIS auf dem Array oben，um "jjjj-MM-tt “祖尔哈尔滕! :)

然后，只需在您的 TEXT 函数中使用它即可:

=TEXT(A1, VLOOKUP(TEXT(1,"MMMM"),{"January","yyyy-MM-dd";"Januar","jjjj-MM-tt"},2,FALSE))

显然，这样做的主要原因是 TEXT(1,"MMMM") 对德语和英语都有效。如果您使用菲律宾语(其中“月份”是“Buwan”)之类的语言，那么您可能会在寻找相互可理解的格式输入时发现一些问题。