水平与垂直数组定界符-国际 [英] Horizontal vs Vertical array delimiters - International

问题描述

紧跟在之前的问题上，我对它们各自的分隔符有疑问.

Following up on an earlier question I had about horizontal vs vertical arrays, I have a question about it's respective delimiters.

问题定义:

Problem definition:

下面是一个比较两个数组的错误方法的示例:

Hereby an example of an incorrect way of comparing two arrays:

{=SUMPRODUCT(--({"Apple","Pear"}={"Apple","Lemon","Pear"}))}

在使用英语应用程序国家/地区代码的情况下，正确的方法是:

The correct way, in case of an English application countrycode would be:

{=SUMPRODUCT(--({"Apple","Pear"}={"Apple";"Lemon";"Pear"}))}

在Excel的英语版本中(最有可能不仅仅是英语)，这些定界符分别是逗号，(用于水平数组)和分号; (用于垂直数组).在此可以找到大量在线信息.

Within an English version (most likely more than just English) of Excel these delimiters would respectively be a comma , for horizontal arrays and a semicolon ; for vertical ones. Plenty of online information to be found on this.

在应用程序上使用荷兰国家/地区代码的机器上工作，但这并不是一个完整的故事.令人沮丧的是，我的分隔符都不同，分别是; 和 \ .能够相当简单地检索分号，事实证明，要找到有关这些国际标准定界符的文档都是很棘手的.

Working on a machine with a Dutch country code on it's application however, it't a complete other story. It does frustrate that my delimiters would both be different, respectively ; and a \. Being able to rather simply retrieve the semi-colon it's proven to be tricky to find any documentation on these delimiters for international version.

解决方法:

Workaround:

如果不预先知道这些定界符，那么使用该应用程序的各种国际版本的任何人都很难使用这些类型的公式.一个相当简单的解决方法是使用 TRANSPOSE():

Not knowing these delimiters up-front makes it tricky for anyone on a variety of international versions of the application to work with these type of formulas. A rather easy workaround would be to use TRANSPOSE():

{=SUMPRODUCT(--({"Apple";"Pear"}=TRANSPOSE({"Apple";"Lemon";"Pear"})))}

在进行内置评估之后，我们可以将反斜杠用作列分隔符.另一种方法是使用 Application.International属性是 xlColumnSeparator 和 xlRowSeparator .

Going through the build-in evaluation we can then retrieve the backslash as the column seperator. Another way would be to use the Application.International property and it's xlColumnSeparator and xlRowSeparator.

问题

Question

我们可以通过Excel(文件>选项>高级)或VBA( Application.DecimalSeparator =-)找到甚至覆盖 xlDecimalSeparator 和 xlThousandsSeparator ")，但我们在哪里可以找到:

We can both find and even override the xlDecimalSeparator and xlThousandsSeparator through Excel (File > Options > Advanced), or VBA (Application.DecimalSeparator = "-") but where can we find:

• 一个实际查看您自己的应用程序中使用了哪些 xlRowSeparator 和 xlColumnSeparator 的地方，除了我所描述的解决方法.寻找类似于千位分隔符和/或官方MS文档的界面.

• A place to actually see which xlRowSeparator and xlColumnSeparator are used within your own application, other than the workarounds I described. Looking for an interface similar to thousands and decimal seperator and/or official MS-documentation.

此外(不是专门针对此内容)位于此处:

Furthermore (not specifically looking for this), is there:

• 像小数点和千位分隔符一样覆盖它们的地方
• 如果不通过Excel界面，是否可以通过VBA以某种方式强行执行此操作?

我很好奇是否提供了官方文档，并且/或者是否可以进行上述操作.

I'm very curious if official documentation is present, and/or if the above can be done.

推荐答案

不是声称这是正确的答案，但是在其他用户的评论帮助下，也许下面的内容可以使您有所澄清:

Not claiming this is the right answer, but with the help from comments from other users, maybe the below can clarify things a bit:

关于此问题，没有任何正式文档的迹象，而且看似随机的行和列分隔符@Gserg显示了一种技巧，该技巧使用下.尽管这是MS Office支持信息，但您看到的定界符为FALSE.它们可能以.一个，一个； 一个:一个 \ 出现，或者甚至是 | .通过将LCID从URL更改为感兴趣的LCID，例如 fr-fr .

With no sign of any official documentation on this matter, and seemingly random row and column delimiters @Gserg showed a trick to retrieve information for any LCID using these unique id's on MS office support under "Create one-dimensional and two-dimensional constants". While this is MS office support information, the delimiters you see there are FALSE. They might come up as . a , a ; a : a \ or even a |. You get this results by changing the LCID from the URL to a LCID of interest, e.g.: fr-fr.

尽管大约有600个不同的LCID，但它们都被重定向到默认的LCID.在@FlorentB的帮助下.我们发现不仅MS Office支持文档是错误的，而且看来这些分隔符毕竟不是那么随机.使用小数点查看国家，他们使用，作为列定界符(水平数组)和; 作为行定界符(垂直数组).但是，使用小数逗号的国家/地区分别使用 \ 作为列定界符，并使用; 表示行.

Although there are about 600 different LCID's they all get redirected to a default LCID. With the help of @FlorentB. we discovered that not only the MS office support documentation is wrong, it seems that these delimiters are not that random after all. Looking at countries using a decimal point, they use the , as a column delimiter (a horizontal array) and a ; as a row delimiter (a vertical array). Countries using a decimal comma however use a \ as a column delimiter and a ; for rows respectively.

更改系统国家/地区设置，在Excel中检查所有默认的LCID，最后得到下面的矩阵，其中显示了每个默认LCID的所有行和列定界符:

Changing the system country settings, checking all default LCID's in Excel, we ended up with the matrix below showing all row and column delimiters per default LCID:

| LCID  | Row | Column |
|-------|-----|--------|
| ar-sa | ;   | ,      |
| bg-bg | ;   | \      |
| cs-cz | ;   | \      |
| da-dk | ;   | \      |
| de-de | ;   | \      |
| el-gr | ;   | \      |
| en-gb | ;   | ,      |
| en-ie | ;   | ,      |
| en-us | ;   | ,      |
| es-es | ;   | \      |
| et-ee | ;   | \      |
| fi-fi | ;   | \      |
| fr-fr | ;   | \      |
| he-il | ;   | ,      |
| hr-hr | ;   | \      |
| hu-hu | ;   | \      |
| id-id | ;   | \      |
| it-it | ;   | \      |
| ja-jp | ;   | ,      |
| ko-kr | ;   | ,      |
| lt-lt | ;   | \      |
| lv-lv | ;   | \      |
| nb-no | ;   | \      |
| nl-nl | ;   | \      |
| pl-pl | ;   | \      |
| pt-br | ;   | \      |
| pt-pt | ;   | \      |
| ro-ro | ;   | \      |
| ru-ru | ;   | \      |
| sk-sk | ;   | \      |
| sl-si | ;   | \      |
| sv-se | ;   | \      |
| th-th | ;   | ,      |
| tr-tr | ;   | \      |
| uk-ua | ;   | \      |
| vi-vn | ;   | \      |
| zh-cn | ;   | ,      |
| zh-hk | ;   | ,      |
| zh-tw | ;   | ,      |

显而易见的结论是，所有国家/地区都使用分号作为行(垂直)分隔符.根据小数点分隔符的不同，国家/地区会在数组公式中使用反斜杠或逗号作为列(水平)分隔符.

The apparent conclusion is that all countries use a semicolon as a row (vertical) delimiter. And depending on decimal seperator countries use a backslash or comma as a column (horizontal) delimiter within array formulas.

因此，即使没有适当的MS文档，也没有在Excel界面中放置任何位置(就像千位小数分隔符一样)，显然，知道您所在国家的十进制分隔符将自动意味着您要么使用\ 或 作为列定界符.

So even without proper MS-documentation, nor a place within the Excel interface (like thousand en decimal delimiter do have), on this matter it is apparent that knowing your country's decimal seperator will automatically mean you either use a \ or , as a column delimiter.

| Dec_Seperator | Row | Column |
|---------------|-----|--------|
| .             | ;   | ,      |
| ,             | ;   | \      |

我很乐意收到有关以上内容和/或存在任何正确的MS Office文档的更多信息.

I would happily recieve more information about the above and/or presence of any correct MS office documentation to add to this.

这篇关于水平与垂直数组定界符-国际的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！