Excel中与语言环境无关的文本函数 [英] Locale-independent Text function in Excel

问题描述

我需要在Excel中设置日期格式，而我正在尝试使用TEXT公式.问题在于，当语言环境更改时，Excel对参数的解释也会更改.

I need to format dates in excel, and I'm trying to use the TEXT formula. The problem is that Excel's intepretation of the arguments changes when the locale changes.

例如:如果我在A1单元格中有一个想要转换为文本的日期，则采用年-月-日格式，如果我的PC拥有英语，则必须使用=TEXT(A1, "yyyy-mm-dd")语言环境，但如果=TEXT(A1, "jjjj-MM-tt")具有德语语言环境，则为=TEXT(A1, "jjjj-MM-tt")(我不告诉您，M必须为大写).这使文档不可移植. (第二个参数是纯文本，因此在更改语言环境时不会转换.)

For example: if I have a date in cell A1, that i'd like to convert to text, in the year-month-day-format, I have to use =TEXT(A1, "yyyy-mm-dd") if my PC has an English-language locale, but =TEXT(A1, "jjjj-MM-tt") (I kid you not, the M has to be upper case) if it has a German-language locale. This makes the document unportable. (The second argument is plain text and therefore not converted when changing locale.)

备注:

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

• This is just an example, I know I could do the long =YEAR(A1) & "-" & TEXT(MONTH(A1), "00") & "-" & TEXT(DAY(A1), "00") in this case. I'm wondering about the more general case.

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

The date should not just be displayed in a certain format, it should actually be a string. For someone viewing the file this doesn't make a difference, but when using it in other formulas, it does.

我可以在VBA中编写UDF来解决此问题，但是在本文档中我无法使用VBA.

I could write a UDF in VBA to solve the issue, but I cannot use VBA in this document.

我不在乎更改月份等的名称.如果月份的名称是6月或Juni(取决于地区)，就可以了.

I do not care about changing the names of the months etc. It's fine, if the name of the month is June or Juni depending on the locale.

我想强调指出，此问题是由于PC的区域设置而不是由于MS Office版本的GUI语言引起的.在上面的示例中，两个示例中的Excel的GUI和公式都是英语.我只是在机器上更改了语言环境.

I want to stress that the issue occurs due to the PC's locale - not due to the GUI language of the MS Office version. In the example above, Excel's GUI and formulas were in English in both examples; I just changed the locale on the machine.

非常感谢

推荐答案

这是一个比较狡猾的方法:在将根据您的系统语言而变化的值上使用VLOOKUP-例如TEXT(1,"MMMM")

Here is a slightly cheaty method: Use a VLOOKUP on a value that will change based on your System Language - for example 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"

In English: Text(1,"MMMM") = "January", so we do a VLOOKUP on the Array below to get "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，"jjjj-MM-tt" zu erhalten！ :)

Auf Deutsche, Text(1,"MMMM") = "Januar", also wir machen einen SVERWEIS auf dem Array oben, um "jjjj-MM-tt" zu erhalten! :)

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

Then, just use that in your TEXT function:

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

很明显，此方法起作用的主要原因是TEXT(1,"MMMM")对德语和英语均有效.如果您使用的是菲律宾语(其中月"为"Buwan")，则在查找可相互理解的格式输入时可能会遇到一些问题.

Obviously, the main reason this works is that TEXT(1,"MMMM") is valid for both German and English. If you are using something like Filipino (where "Month" is "Buwan") then you might find some issues finding a mutually intelligible formatting input.

这篇关于Excel中与语言环境无关的文本函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！