Excel区域设置代码中的130000 [$

2023-08-21 00:41| 来源: 网络整理| 查看: 265

首先，我认为这个函数在技术上是不准确的(而且会根据你的系统设置产生不正确的信息)，因为excel是一个8位数字。从技术上讲，正确的格式是...xxyyzzzz

xx = 00

yy = 13

zzzz = 0000

=Text(A1, "[$-00130000]d/m/yyyy")

如果您不使用前两位数字，则Microsoft将确定您使用的是'00‘。这将转换为使用您的默认系统设置(见下文)。

前两个数字(xx)表示数字(Xxyyzzzz)的外观：

Hexadecimal value = Reserved Bit for Application Use (Application Specific - From what I have been reading) 00 = System Defaults (Set in Control panel) 01 = Western language 02 = Arabic Hindi 03 = Extend Arabic Hindi 04 = Sanskrit 05 = Bengali 06 = Gorumuchi 07 = Gujarati 08 = Oriya 09 = Tamil 0A = Telugu 0B = Kannada 0C = Malayalam 0D = Thai 0E = Laotian 0F = Tibetan language 10 = Burmese 11 = Ethiopian 12 = Cambodian 13 = Mongolian 1B = Japanese 1 1C = Japanese 2 1D = Japanese 3 1E = Simplified Chinese 1, Chinese lowercase 1F = Simplified Chinese 2, Chinese uppercase 20 = Simplified Chinese 3, full-width numbers 21 = Traditional Chinese 1, traditional lowercase 22 = Traditional Chinese 2, traditional uppercase 23 = Traditional Chinese 3, full-width numbers 24 = Korean 1 25 = Korean 2 26 = Korean 3 27 = Korean 4

接下来的两个数字(yy)表示日历格式(xxyyzzzz)：

Hexadecimal value = Calendar ID / Sort ID = See Library Source Below 00 = System Defaults (Set in Control panel) 01 = Gregorian calendar (localization) 02 = Gregorian calendar (United States) 03 = Japanese calendar (and calendar) 04 = Taiwan calendar 05 = Korean calendar (Tanji) 06 = Hajj (Arab Lunar Calendar) 07 = Thai 08 = Jewish Calendar 09 = Gregorian calendar (Middle Eastern French) 11 = Lunar Calendar (Not Officially published) 12 = Lunar Calendar (Not Officially published) 13 = Lunar Calendar (Not Officially published) 0A = Gregorian calendar (Arabic) 0B = Gregorian calendar (translated English) 0E = Lunar Calendar (Not Officially published)

最后，最后四位数字(zzzz)表示语言代码(xxyyzzzz)：

Hexadecimal value = Language ID Values= LCID 0000 = System Defaults (Set in Control panel) = Not certain if the "control panel" has an LCID 0401 = Arabic = 1025 0402 = Bulgarian = 1026 0403 = Catalan = 1027 0404 = traditional Chinese) = 1028 0405 = Czech = 1029 0406 = Danish = 1030 0407 = German = 1031 0408 = Greek = 1032 0409 = English (United States) = 1033 040B = Finnish = 1035 040C = French = 1036 040D = Hebrew = 1037 040E = Hungarian = 1038 040F = Icelandic = 1039 0410 = Italian = 1040 0411 = Japanese = 1041 0412 = Korean = 1042 0413 = Dutch = 1043 0414 = Norwegian (Birkmer) = 1044 0415 = Polish = 1045 0416 = Portuguese (Brazil) = 1046 0418 = Romanian = 1048 0419 = Russian = 1049 041A = Croatian = 1050 041B = Slovak = 1051 041C = Albanian = 1052 041D = Swedish = 1053 041E = Thai = 1054 041F = Turkish = 1055 0420 = Urdu = 1056 0421 = Indonesian = 1057 0422 = Ukrainian = 1058 0423 = Belarusian = 1059 0424 = Slovenian = 1060 0425 = Estonian = 1061 0426 = Latvian = 1062 0427 = Lithuanian = 1063 0428 = Tajik = 1064 0429 = Persian = 1065 042A = Vietnamese = 1066 042B = Armenian = 1067 042C = Azerbaijani (Latin) = 1068 042D = Basque = 1069 042F = Macedonian = 1071 0436 = Afrikaans = 1078 0437 = Georgian = 1079 0438 = Faroese = 1080 0439 = Hindi = 1081 043A = Maltese = 1082 043D = Yiddish = 1085 043E = Malay = 1086 043F = Kazakh = 1087 0440 = Kyrgyz = 1088 0441 = Swahili = 1089 0442 = Turkmen = 1090 0443 = Uzbek (Latin) = 1091 0444 = Proverb = 1092 0445 = Bengali = 1093 0446 = Punjabi = 1094 0447 = Gujarati = 1095 0448 = Oriya = 1096 0449 = Tamil = 1097 044A = Telugu = 1098 044B = Kannada = 1099 044C = Malayalam = 1100 044D = Assamese = 1101 044E = Marathi = 1102 044F = Sanskrit = 1103 0450 = Mongolian = 1104 0456 = Galician = 1110 0457 = Gungan = 1111 0458 = Manipur = 1112 0459 = Sindhi = 1113 045A = Syrian = 1114 045B = Sinhalese = 1115 045C = Congga = 1116 045D = Inuit = 1117 045E = Amharic = 1118 045F = Tamasic (Berber / Arab) = 1119 0460 = Kashmiri (Arabic) = 1120 0461 = Nepali = 1121 0462 = Frisian = 1122 0463 = Pashto = 1123 0464 = Filipino = 1124 0465 = Dhivehi = 1125 0466 = Kwa = 1126 0467 = Furbe = 1127 0468 = Hausa = 1128 0469 = Ibibio = 1129 046A = Yoruba = 1130 0470 = Igbo = 1136 0471 = Kanuri = 1137 0472 = Kucht = 1138 0473 = Tigrinya (Ethiopia) = 1139 0475 = Hawaiian = 1141 0476 = Latin = 1142 0477 = Somali = 1143 0478 = Proverb = 1144 0804 = Chinese (Simplified) = 2052 0807 = German (Switzerland) = 2055 0809 = English (UK) = 2057 0814 = Norwegian (Nynorsk) = 2068 0816 = Portuguese (Portugal) = 2070 081A = Serbian (Latin) = 2074 082C = Azeri (Cyrillic) = 2092 0843 = Uzbek (Cyrillic) = 2115 0873 = Tigrinya (Eritrea) = 2163 085F = Tamasic (Latin) = 2143 0C07 = German (Austria) = 3079 0C09 = English (Australia) = 3081 0C0A = Spanish = 3082 0C0C = French (Canada) = 3084 0C1A = Serbian (Cyrillic) = 3098 1009 = English (Canada) = 4105

如果你想自己测试我的发现，那么你可以试一下这个例子：

=Text("02/05/2019", "[$-1E020404]mmmm dd yyyy")

xx = 1E =简体中文1，中文小写(对我来说更容易翻译)

yy = 02 =公历(美国)

zzzz = 0404 =繁体中文

这将转化为：

二月0五二0一九

如果你把这个从中文翻译成英文，你会得到2019年2月5日。如果你还是不相信我的话你可以试试...

=Text("02/05/2019", "[$-00080409]mmmm dd yyyy")

xx = 00 =默认系统设置-我的设置为英语-美国

yy = 08 =犹太日历(同样，未发布)

zzzz = 0409 =英语(美国)(或者我可以在我的PC上将其设置为0000以获得相同的结果)

这会将日期转换为犹太日历，即"Shvat 30 5779“。

资料来源：

了解有关LCID = https://msdn.microsoft.com/en-us/globalization/mt778914.aspx https://docs.microsoft.com/en-us/windows/desktop/Intl/language-identifiers的更多信息

有关LCID = https://docs.microsoft.com/en-us/windows/desktop/Intl/language-identifier-constants-and-strings的详细信息

排序ID和语言ID的库列表：https://msdn.microsoft.com/en-us/library/cc233968.aspx

日历IDs https://docs.microsoft.com/en-us/windows/desktop/Intl/calendar-identifiers

最后(花很多时间阅读所有这些参考链接)- https://docs.microsoft.com/en-us/windows/desktop/Intl/national-language-support-constants

【本文地址】

公司简介

联系我们