[Решено] Разные национальные стандарты в формулах

[Sirius34]

Всем доброго времени суток!

Есть база, сделанная в Calc, с большой (около 7000 строк) таблицей данных и несколькими вкладками, в которых
рассчитываются различные показатели, данные к отчётам и т.п.
Также в базе есть довольно немаленький макрос, который работает как с исходными данными, так и, частично,
с другими вкладками, где данные уже обработаны.
В Windows на русских версиях вплоть до 7.2.5 и на Linux'е в русской версии LO (установлена версия 6.0.6.1,
обновлять нельзя, увы) всё работает замечательно.
Я даже и не знал про наличие проблемы, пока на одном из вновь поступивших компов с линуксом по причине какого-то
глюка во время установки (поддержка как-то проглядела) не оказалась установлена версия LO только с английским
интерфейсом (чисто технически, все файлы русского интерфейса присутствовали на диске, но исправить самостоятельно
не получилось). Комп в итоге уехал обратно в поддержку, для исправления, но ошибку в базе мы уже увидели.

Суть в следующем: английская версия LO в нашей базе при преобразовании строки в дату выдаёт ошибку.
В макросе формулы преобразования вычисления с использованием функции CDate вызывают сообщение об ошибке
"Inadmissible value or data type.
Data type mismatch."
Эту проблему мне всё же получилось решить, используя функцию CDateFromIso и переставив местами фрагменты строки -
в русской версии такой вариант оказался вполне работоспособен.

А вот как решить проблему с несколькими тысячами формул, где используются динамически формируемые строковые даты,
как исходник для преобразования в нормальный формат и использования в условиях?
В них везде "Err:502" вместо значений...

Можно, конечно, закопаться и исправить все формулы через "Найти" - "Заменить" на другой стандарт,
но это же невозможно будет делать оперативно при обратной ситуации. Держать два разноязычных варианта базы
тоже ещё то решение.
Чувствую, что придётся писать скрипт, который будет завязан либо на открытие базы, либо на нажатие кнопки.
Но тут мне видится аж две неслабых проблемы:
1. отрабатывать этот скрипт будет очень небыстро, как я думаю
2. при запуске скрипта надо каким-то образом проверять - какой именно стандарт написания даты активирован в настройках LO
на момент открытия базы (или надеяться на сообразительность пользователя, который нажмёт именно нужную кнопку, если их
будет две, хотя и тут может быть косяк в плане того, что в базе уже будут прописаны формулы в нужном стандарте)
В общем, одни мрачные мысли...

Кто что посоветует?

[eeigor]

Почему бы не хранить даты в формате ISO: "YYYY-MM-DD"? Я сам так делаю. Тогда локаль роли не играет.

[economist]

Если не можете обеспечить одинаковое ПО, локали итп, но важно сохранить скорость - храните в одной базе (ODS) в двух разных колонках обе даты: ISO и русскую.

[mikekaganski]

Трудно давать советы, когда в вопросе не дано никакой информации вообще
Какие конкретно форматы дают какие конкретно проблемы в каком конкретно коде? (Уже не говоря о том, что тема в разделе Basic говорит про Calc, используя термин "база", что создаёт дополнительные трудности - на каком из этапов преобразований возникает затык.)

Хотя один совет, универсально применимый к работе с представлением дат, дать можно. Старайтесь всегда и везде использовать формат ISO 8601, и проблем с неоднозначностями/совместимостями не будет. Ну, а локаль-зависимое преобразование используйте только чтобы перевести пользовательский ввод во внутренний формат.

[Sirius34]

Почему бы не хранить даты в формате ISO: "YYYY-MM-DD"? Я сам так делаю. Тогда локаль роли не играет.

Так раньше таких сложностей не было, вот и не задумывался даже. Всё время были только русские версии LO.

Трудно давать советы, когда в вопросе не дано никакой информации вообще

Да, в общем-то, всё там написано. Есть ячейки, в которых в формулах используются динамические даты: склеиваем
1) "ДД.ММ."&"ГГГГ"
2) "ДД."&"ММ."&"ГГГГ"
из разных ячеек, превращаем в число, подсовываем в условие.
ГГГГ всегда берётся из одной ячейки. ДД и ММ могут браться, как из ячеек по отдельности, так и из одной ячейки путём отсекания года.

Какие конкретно форматы дают какие конкретно проблемы в каком конкретно коде?

Вот, собственно на стадии "склеивания" и получаем либо ошибку Err:502, либо, в самом лучшем случае,
условие просто не обрабатывается (в этом моменте ещё не разобрался).

используя термин "база", что создаёт дополнительные трудности

Это моё упрощение: по сути, этот ods-файл является упрощенным вариантом базы данных. Он даёт больше гибкости
и простоты в плане визуальной работы.
Я бы лично для себя предпочёл полноценную базу данных, там гораздо больше возможностей для работы со сложными
запросами и отчётами, но юзвери не любят сложные вещи, а делать двойную работу (свою и чужую) мне не хочется.

p.s. Я прошу прощения у модератора - возможно и надо было в разделе "Calc" тему открыть, но я предполагал необходимость
написания макроса для решения проблемы, поэтому...
Если критично, то переместите, пжлста

[mikekaganski]

Да, в общем-то, всё там написано.

Хм.

Есть ячейки, в которых в формулах используются динамические даты: склеиваем
1) "ДД.ММ."&"ГГГГ"
2) "ДД."&"ММ."&"ГГГГ"
из разных ячеек, превращаем в число, подсовываем в условие.

Ага, вот, кажется, первое, что "там" таки не написано

Вот, собственно на стадии "склеивания" и получаем либо ошибку Err:502

Этого в принципе быть не может. Формула ="ДД.ММ."&"ГГГГ" будет работать на любой версии ЛО (и OOo), с любой локализацией и любой локалью. Результатом будет строка. Или тут опять недосказанность, и речь не о формуле Calc, а о коде Basic? (Кажется, упоминание CDate и CDateFromIso даёт основание ожидать код Basic.) Хотя и в бейсике работать будет.

либо, в самом лучшем случае,
условие просто не обрабатывается (в этом моменте ещё не разобрался).

А вот, кажется, ещё что-то, что так и не описали: что же такое "условие", куда эта строка "подсовывается". Это формула?

[mikekaganski]

Опять же: поскольку неясно, куда отправляется эта строка, также неясно, насколько легко будет, скажем, просто заменить такую "склейку" на использование функции DATE - просто используя DATE("ГГГГ";"ММ";"ДД") и преобразуя строки в числа автоматически (я таки полагаю, что "ГГГГ" - это на самом деле что-то вроде "2022", а не локализованный код формата, но и тут ясности нет).

[Sirius34]

Ага, вот, кажется, первое, что "там" таки не написано

Виноват Пытаюсь исправиться

Этого в принципе быть не может. Формула ="ДД.ММ."&"ГГГГ" будет работать на любой версии ЛО (и OOo), с любой локализацией и любой локалью. Результатом будет строка.

Будет. И, к сожалению, ошибка есть. В русской версии работает, в английской - ошибка

Код Выделить Развернуть


=COUNTIFS($'forcon'.$H$2:$H$699,$A7,$'forcon'.$N$2:$N$699,">=01.02."&$'f21'.$K$3,$'forcon'.$N$2:$N$699,"<="&TEXT(VALUE("01.03."&$'f21'.$K$3)-1,"DD.MM.YYYY"))

или

Код Выделить Развернуть


=COUNTIFS($'forcon'.$H$2:$H$699,$A7,$'forcon'.$O$2:$O$699,">="&$'f21'.$M$3&"."&$'f21'.$L$3&"."&$'f21'.$K$3,$'forcon'.$O$2:$O$699,
"<="&TEXT(VALUE($'f21'.$N$3&"."&$'f21'.$L$3&"."&$'f21'.$K$3),"DD.MM.YYYY"))


упоминание CDateFromIso даёт основание ожидать код Basic

Я там написал, что эта проблема вылезла при работе макроса, и я её решил заменив CDate на CDateFromIso + изменив порядок склеиваемых фрагментов

А вот, кажется, ещё что-то, что так и не описали: что же такое "условие", куда эта строка "подсовывается". Это формула?

Как ни странно, но есть формулы, где частичное исправление убрало ошибку 502, но формула работает некорректно, считает не всё

Код Выделить Развернуть


=$'forcon'.$P700-SUMIFS($'forcon'.$P2:$P699,$'forcon'.$N2:$N699,"<"&DATE(VALUE($'f21'.$K$3), 1, 1),$'forcon'.$Q2:$Q699,">=01.01."&$K$3)


[mikekaganski]

Ага. То есть ошибка, видимо, не при склеивании, а при использовании в качестве критерия в COUNTIFS сотоварищи.

Тем более замените все

Код Выделить Развернуть

">="&$'f21'.$M$3&"."&$'f21'.$L$3&"."&$'f21'.$K$3

на

Код Выделить Развернуть

">=" & DATE($'f21'.$M$3; $'f21'.$L$3; $'f21'.$K$3)

Не должны в таких формулах использоваться даты такого вида - просто подставляйте числовое значение дат.

А насчёт "считает не всё" - трудно сказать, не видя данных. Может, там в данных не даты, а тексты, или ещё что.

[Sirius34]

Не должны в таких формулах использоваться даты такого вида - просто подставляйте числовое значение дат.

Хорошо, попробуем. Правда, в английской версии вроде через запятую параметры идут.

А насчёт "считает не всё" - трудно сказать, не видя данных.

Если у меня не получится, тогда попробую вырезать "лишнюю" часть файла, чтобы можно было остальное выложить
(больно много там служебной информации).

[mikekaganski]

Хорошо, попробуем. Правда, в английской версии вроде через запятую параметры идут.

По умолчанию, при использовании локали en-US, там действительно показываются запятые в качестве разделителей. Тем не менее, внутренний (независимый от локали) формат представления формул Calc использует точку с запятой; и точка с запятой поэтому может быть использована в любой локали (Вы можете формулу с точками с запятой скопировать, вставить в Ваш en-US ЛО, и она будет работать, хотя и превратит визуально их в запятые, к сожалению).

А вот использовать запятые так не получится, потому что это нестандартный, специфичный для локали разделитель.

[Sirius34]

и точка с запятой поэтому может быть использована в любой локали

Не стал искать себе проблем, поэтому при коррекции формул ставил везде точки с запятой.
"Склеивание" динамических дат сделал по Вашему примеру на всех листах книги. Пришлось пару промежуточных "костылей" придумать, но это уже частности.
Проверил - визуально ошибок, в том числе в расчётах (сравнил со старой версией книги) не нашёл.
Поменял локаль, перепустил - всё работает хорошо. Формулы автоматом поменялись на русскоязычные.
Обрадовался - решил, что все проблемы в прошлом.
Сглазил

Теперь вылезла проблема с форматированием суммовых ячеек.
Дело в том, что в таблице для выгрузки присутствуют столбцы смешанного содержания: текст + сумма в формате "0,00".

Код Выделить Развернуть


=P2&CHAR(10)&TEXT(R2;"0,00")

В русской локали ячейки формируются корректно: "название"<LF>5000,00
А в английской: "название"<LF>5,000
Собственно, вопрос: как можно унифицировать эту формулу?
В описании к функции TEXT указано, что она зависит от локальных настроек

to moderators:
Всё-таки переместите топик в раздел "Calc", пжлста. Тема оказалась несколько отличной от моих первоначальных предположений.
Если даже до макросов дело дойдёт, можно будет и там обсудить

[sokol92]

 Есть еще не испорченная функция FIXED.

[mikekaganski]

Используемые спецсимволы в строке формата зависят от локали ячейки. Если Вы зафиксируете локаль ячейки с автоматической (то есть "использовать локаль программы") на русскую (без автоматики), проблемы не будет ни в какой версии.

[Sirius34]

Есть еще не испорченная функция FIXED.

Потестил. Не то получается в результате: дробная часть отсекается точкой.

Путём долгих мытарств и экспериментов пришёл к принудительному формату с явным пробелом в качестве разделителя разрядов:
TEXT(A2;"# ##0,00")
Не совсем то, что хотел, но хотя бы запятые не пишутся.
Ну и ячейки, где суммы, на русскую локаль принудительно настроил, хотя для ячеек, в которых результатом является преобразование
суммы в строку, сомнительна польза этого действия. Но посмотрим в дальнейшем по работе.

Вообще говоря, в свете решения неплановых проблем, идея макросом при открытии книги узнавать из настроек
текущую локаль LO мне всё больше нравится.
Как можно реализовать?