Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Проблема с региональным разделителем разрядов
 
#1
23.09.2017 14:59:47
Привет!

Суть такова. По работе пришлось создать модель, где я массово использовал юзерские форматы. Во вложенном примере есть файл с числом 1111111. На графике оно должно отражаться следующим образом - "+1,111". Для этого был использован юзерский формат. В частности, единицы в тысячи я переводил таким образом:
Код
=text(B3;"#,#,")

И потом еще дорисовывал плюс.

Теперь вопрос. Когда файл открывает человек в другой стране, у которого в региональных настройках разделителем разрядов является пробел, он видит какую-то галиматью в виде "111111,1,". Связано это с тем, что при изменении региональных настроек Excel, видимо, в формулах не заменяет автоматически запятые на пробелы. Формула продолжает иметь вид
Код
=text(B3;"#,#,")
, а не
Код
=text(B3;"# # ")

Это как-то решается? Может, я неправильно формат задаю? Надо, чтоб при изменении региональных настроек разделитель в пользовательских форматах менялся автоматически. Дело в том, что во встроенных Custom форматах, которые предлагает выбрать Excel, разделитель групп разрядов меняется автоматически.
Изменено: colonel - 27.09.2017 20:05:52
 
 
 
#2
23.09.2017 19:05:26
Доброе время суток.
Файл не смотрел. Просто прилагаю набросок, может поможет в рассуждениях, как бороться с проблемой. В жёлтой ячейке функция ТЕКСТ (TEXT) с форматом, определяемым по остальным участникам вычисления. При переключении форматов с Россия на США и обратно, число преобразовывается в текст в соответствии выбранным форматом. Другого пути, честно говоря не вижу.
Успехов.
 
 
 
#3
24.09.2017 01:40:40
Спасибо. Как вариант. Конечно, создавать дублированные наборы для всех вычислений - это оч грустно. Встроенные форматы ведь автоматически переключаются. Возможно, есть другое решение.
Изменено: colonel - 24.09.2017 01:45:47
 
 
 
#4
24.09.2017 04:49:14
Вариант1 - см. приложенный пример Example1.xlsx
Вместо функции ТЕКСТ (англ - TEXT) примените необходимый формат непосредственно к ячейкам с числовыми значениями.
Тогда при любой локализации автоматически будут использоваться локальные разделители.

Вариант2 - см. приложенный пример Example2.xlsb
Можно использовать имя MyFormat с функцией копирования формата ячейки:
=ПОЛУЧИТЬ.ЯЧЕЙКУ(7;Sheet1!$F$2) или для англ  =GET.CELL(7,Sheet1!$F$2),
а в ячейке F2 (может быть любой другой ячейкой, в т.ч. и пустой) установить требуемый формат.
Тогда формула =ТЕКСТ(E3;MyFormat) или для английской версии =TEXT(E3,MyFormat) будет работать в любой локализации.
Но так как в имени MyFormat использована макрофункция ПОЛУЧИТЬ.ЯЧЕЙКУ(), то формат книги должен поддерживать макросы, а сами макросы нужно разрешать при загрузке книги.
Изменено: ZVI - 24.09.2017 04:52:39
 
 
 
#5
24.09.2017 08:29:51
Цитата
colonel написал:
Конечно, создавать дублированные наборы для всех вычислений - это оч грустно
Вот оно международное недопонимание :(
Я всего лишь предложил вместо статического указания формата числа для функции ТЕКСТ (TEXT), подобно тому как это сделано в ячейках А4 и А5, использовать вычисляемую строку формата. В ячейке А3 именно это и используется.
В ней в качестве аргумента формата содержится именованная ссылка numFormat, которая ссылается на ячейку G1, в которой в зависимости от результатов вычисления в ячейках D1, D2 и выбирается требуемый в зависимости от локали (США или Россия) формат числа, а именно число с разделителями тысяч и двумя знаками после запятой/точки. По другому, если во всех вычислениях будет использоваться =TEXT(Address, numFormat), то не будет ошибки форматирования.
То есть не нужно дублировать все вычисления, а
1. Определится как должен выглядеть результат функции ТЕКСТ в зависимости от локали
2. Определить как детектировать, какая локаль используется
3. На основании определения локали выбирать правильную строку формата
4. Использовать выбранную строку в функции TEXT
 
 
 
#6
24.09.2017 15:35:28
Здравствуйте!

А что, "лобовые" методы не приветствуются?

Разделитель тысяч меняется при изменении региональных настроек (предполагается, что значение ячейки A1 неотрицательно):
="+" & ФИКСИРОВАННЫЙ(A1/1000;0)
Разделитель тысяч - пробел при любых региональных настройках:
="+" & ПОДСТАВИТЬ(ФИКСИРОВАННЫЙ(A1/1000;0); ПСТР(ФИКСИРОВАННЫЙ(1234);2;1);" ")
(1234 можно заменить на любое 4-значное число).
Изменено: sokol92 - 27.09.2017 20:06:17 (Уточнение ответа)
Владимир
 
 
 
#7
27.09.2017 19:04:04
Есть способ и для функции ТЕКСТ. Надо заставить EXCEL вернуть значения с этими разделителями:
разделитель разрядов:
=ПСТР(1/2;2;1)
разделитель тысяч (идея предыдущего оратора):
=ПСТР(ФИКСИРОВАННЫЙ(1000);2;1)
И теперь значение
=ТЕКСТ(B3;"+#"&ПСТР(1/2;2;1)&"#"&ПСТР(ФИКСИРОВАННЫЙ(1000);2;1))
должно правильно отображаться при любых 1-символьных разделителях.
Изменено: PerfectVam - 27.09.2017 20:06:36
Следствие из третьего закона Чизхолма:
"Даже если ясность изложения исключает неверное толкование, все равно найдется кто-то, кто поймет Вас неправильно."
 
 
 
#8
28.09.2017 08:24:41
Возможно будет полезно, а точнее, наверняка вам будет полезна эта ссылка!
Параметры локали
 
 
 
#9
28.09.2017 09:50:13
Ссылка, указанная в #8, предназначена для VB. Авторы Excel VBA любезно предоставили нам свойство Application.International
Владимир
 
 
 
#10
28.09.2017 15:54:37
И снова о "без макросов".
Уж не знаю, но вдруг в каких-то экзотических локалях любят разделять больше чем одним символом?
Можно универсализировать конструкцию.
Разделитель(и) разрядов:
=ПСТР(1/2;2;ДЛСТР(1/2)-2)
разделитель(и) тысяч:
=ПСТР(ФИКСИРОВАННЫЙ(1000;0);2;ДЛСТР(ФИКСИРОВАННЫЙ(1000;0))-4)
И теперь значение
=ТЕКСТ(B3;"+#"&ПСТР(1/2;2;ДЛСТР(1/2)-2)&"#"&ПСТР(ФИКСИРОВАННЫЙ(1000;0);2;ДЛСТР(ФИКСИРОВАННЫЙ(1000;0))-4))
теоретически должно правильно отображаться при любых разделителях, даже длиной больше одного символа.
Если, конечно, правила задания пользовательского формата останутся при этом неизменны...
Следствие из третьего закона Чизхолма:
"Даже если ясность изложения исключает неверное толкование, все равно найдется кто-то, кто поймет Вас неправильно."
 
 
 
Страницы: 1
Наверх