Интересные задачи

[sokol92]

Олимпиадная задача (для тех, у кого есть Excel). Сначала можно попробовать решить "без доски".

1. В Calc (локализация ru_RU) выделена ячейка, имеющая текстовый формат. Что будет в ячейке в результате действия оператора:

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

ThisComponent.CurrentSelection.SetFormula Now()

2. То же для Excel.

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

Selection.Formula = Now()

Объяснить результаты.

Решение - через 24 часа.

[sokol92]

Итак, решение.
1. Calc

Ячейка будет содержать текст следующего вида: 09.12.2021 13:46:51
Объяснение. Метод setFormula имеет параметр типа String. Соответственно, Basic преобразует значение, полученное от функции Now, в текст. Преобразование производится, как мы уже говорили, с учетом локализации. В свою очередь, Calc полученный текст присваивает без дополнительного анализа, поскольку ячейка имеет текстовый формат.

2. Excel

Ячейка будет содержать текст следующего вида: 12/9/2021  1:46:51 PM
Ответ является неожиданным даже для некоторых специалистов по Excel.
Объяснение. Свойство Range.Formula имеет тип Variant. Поэтому VBA передает для присвоения свойству значение типа Variant/Date, поскольку Now dозвращает значение типа Date. Далее вступает в игру Excel. Так как ячейка имеет текстовый формат, то полученное значение типа Variant/Date преобразуется в текст в соответствии с локализацией en_US, поскольку Excel "внутри" работает в соответствии с этой локализацией (как и Calc).

A что изменится, если выделенная ячейка будет иметь общий формат?

1. Calc

Basic передаст методу setFormula тот же текст, что и в ранее разобранном случае. Calc произведет анализ текста с точки зрения возможности преобразования в число или дату в соответствии с локализацией en_US. Поскольку это не возможно, в ячейку будет занесен полученный текст, формат ячейки преобразован в текстовый.

2. Excel

Получив значение типа Variant/Date Excel преобразует его в число занесет в ячейку. Локализованный числовой формат ячейки будет ДД.ММ.ГГГГ ч:мм (в сооответствии региональными установками Windows).

Для самостоятельного разбора предлагается разобрать ситуации, когда производится присвоение свойства FormulaLocal (Calc и Excel).

Если задача кому-то показалась интересной, то дайте мне знать, и мы продолжим спецсеминар. 

[mikekaganski]

Пожалуйста, продолжайте, Владимир! Для меня, например, очень интересно - в контексте сравнения и совместимости; я совсем плохо знаком с автоматизацией MSO, поэтому какие-то вопросы совместимости не вижу.

(Я разделил тему - ей не место в старом месте )

[sokol92]

Михаил, спасибо! Через небольшое (надеюсь) время подготовлю задачу на тему интерпретации текстов при занесении в свойство Formula (FormulaLocal) ячейки.

[sokol92]

Назвался груздем...  

Задача посвящена изучению особенностей присвоения значений свойствам FormulaLocal и (псевдосвойству) Formula ячеек Calc.
В прилагаемом документе в столбце A расположены различные тексты. Требуется (сначала можно в уме) понять, каким будет значение ячейки, если это значение присвоить свойству Formula (столбец B) или FormulaLocal (столбец C).

Задача предполагает наличие у пользователя LibreOffice с локализацией ru_RU.
Узнать результат можно, запустив макрос Main. В столбцах C и D показаны форматы ячеек после операции присвоения.
На скрытом листе Sheet2 приведены результаты для конфигурации Win 10, LO 7.2.3.2.

Критика решительно приветствуется, вдруг я пропустил какие-то интересные случаи (или криво написал макрос)?

Планирую 15.12.2021 19.12.2021 добавить аналогичный опыт для Excel и попытаться сформулировать свои гипотезы относительно правил поведения (см. также это сообщение).

[mikekaganski]

Маленькая придирка: первое "Number (ru_RU)" на первом листе должно быть "Number (en_US)"

[sokol92]

Спасибо.

[sokol92]

Добавим еще одни файл для экспериментов - книгу Excel TestFormula.xlsm. Эту книгу следует поместить в ту же папку, что и TestFormula.ods.

Открываем эту книгу в Excel и запускаем в ней макрос Main. Выполняются следующие действия:
- Открывается в приложении Calc документ TestFormula.ods и выполняется макрос (Basic) Main этого документа.
- Сформированные в Calc данные переносятся через буфер обмена в новую книгу Excel.
- Справа добавляются и заполняются столбцы для сравнения данных полученных в Calc и Excel.

Макрос VBA Main показывает некоторые возможности для работы с Calc из VBA.

Некоторые технические подробности:
- не все свойства ячеек переносятся из Calc в Excel через буфер обмена
- документ TestFormula.ods открывается как шаблон. Если этого не сделать, то возможна повторная загрузка в Calc одного и того же документа (у меня это вызвало удивление).

Предлагаю встретиться через неделю для возможных выводов.

[sokol92]

Итак, попробуем сформулировать некоторые обобщения для следующих случаев присвоения свойств Formula и FormulaLocal ячейке в Calc и Excel.
Ячейка имеет "общий" формат, локализация Calc и Excel для определенности ru_RU, в Calc используется язык интерфейса по умолчанию и не используются английские имена функций.

Все выводы являются гипотезами, поскольку не основаны на анализе исходных текстов программ.

1. Правила интерпретации текстов при присвоении свойства FormulaLocal те же, что используются при интерпретации текста, который пользователь вводит интерактивно в ячейку.

2. При интерпретации текстов, которые присваиваются свойству Formula ячейки, используются правила для локализации en_US (некоторые уточнения см. ниже).

3. Интерпретация текстов, которые начинаются на знак апострофа.

В Excel в обоих случаях в ячейку заносится текст с удаленным первым символом. Свойство ячейки Range.PrefixCharacter устанавливается в знак апострофа (благодаря этому свойству, в строке формул ячейка отображается с ведущим апострофом).

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

В Calc при присвоении свойства FormulaLocal производится анализ, может ли значение с удаленным первым сиволом быть интерпретировано как число, дата, логическое значение в соответствии с локализацией по умолчанию. Если может, то в ячейку заносится текст с удаленным первым символом. Если нет, то заносится исходный текст без преобразования. В обоих случаях формат ячейки не изменяется.

4. Интерпретация текстов, которые начинаются на знак равенства (=).

Исходный текст интерпретируется как формула (локализованная в случае FormulaLocal и не локализованная для Formula).
Если формула содержит синтаксическую ошибку то в Excel возникает исключительная (аварийная) ситуация.
В Calc в случае синтаксической ошибки  в ячейку будет занесено "специальное" (?) значение.

5. Интерпретация текстов, содержащих логические значения.
Речь идет о значениях "TRUE", "FALSE", "ИСТИНА", "ЛОЖЬ" (регистронезависимо).

В Excel в ячейку заносится значение логического типа (в объектной модели Calc логический тип ячейки отсутствует).

В Calc при занесении "TRUE", "FALSE" в свойство Formula в ячейку будут занесены числа 1 и 0 (формат ячейки не меняется). При занесении текcтов "ИСТИНА", "ЛОЖЬ" в свойство FormulaLocal в ячейку заносятся числа 1, 0 и формат ячейки меняется на BOOLEAN.

6. Интерпретация текстов, содержащих ошибочные значения.
Речь идет о значениях "#DIV/0!", "#N/A" и их локализованных аналогах.

В Excel в этих случаях в ячейку будут занесены значения ошибочного типа (в объектной модели Calc ошибочный тип ячейки явно не присутствует) (?).

В Calc такие тексты не интерпретируются специальным образом.

7. В Calc при занеcении свойства Formula в случаях, когда занесенное значение может быть интерпретировано как число, дата, логическое значение в соответствии с локализацией по умолчанию (в нашем случае ru_RU) и не может быть интерпретировано как число, дата, логическое значение в соответствии с локализацией en_US, в ячейку заносится исходный текст, а формат ячейки меняется на текстовый.

8. Интерпретация текстов, "похожих" на числа.
Если текст может быть интерпретирован как число в локализации en_US (Formula) или ru_RU (FormulaLocal), то в ячейку заносится число. При анализе текста отбрасываются возможные начальные и конечные пробелы. Текст может содержать число как в "обычном", так и в "научном" формате.

В случае использования разделителей тысяч в Excel каждая группа цифр, кроме крайней левой, должна содержать не менее трех знаков.
В случае использования разделителей тысяч в Calc каждая группа цифр, кроме крайней левой, должна содержать три знака.

Для чисел, содержащих разделители тысяч и(или) чисел в "научном" формате Excel меняет формат ячеек на соответствующий.

Обе программы распознают текст, содержащий число с заключительным знаком "%", заносят в этом случае в ячейку соответствующее число, деленное на 100, и меняют формат ячейки на "процентный".

Правила для текстов, "похожих" на даты, довольно сложны, постараюсь их сформулировать в отдельном сообщении.

[mikekaganski]

2. При интерпретации текстов, которые присваиваются свойству Formula ячейки, используются правила для локализации en_US (некоторые уточнения см. ниже).

Точнее, "каноническое" представление (в идеале - прописанное в ODF).

[eeigor]

В Excel в ячейку заносится значение логического типа (в объектной модели Calc логический тип ячейки отсутствует).

Поэтому, в отличие от Excel, при использовании формулы SUMPRODUCT с условиями не надо преобразовывать логический результат в число, чтобы перемножить (OR) или сложить (AND) условия.
В Excel мы обычно делаем так:
=SUMPRODUCT(--(Условие1);--(Условие2))
А в Calc это сделать проще, что выглядит более естественно:
=SUMPRODUCT(Условие1;Условие2)

[mikekaganski]

Вообще отмеченные различия в поведении, когда FormulaLocal меняет формат ячеек, а Formula для аналогичного ввода не меняет - на мой взгляд баг.

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

В Calc при присвоении свойства FormulaLocal производится анализ, может ли значение с удаленным первым сиволом быть интерпретировано как число, дата, логическое значение в соответствии с локализацией по умолчанию. Если может, то в ячейку заносится текст с удаленным первым символом. Если нет, то заносится исходный текст без преобразования. В обоих случаях формат ячейки не изменяется.

И это тоже.

[mikekaganski]

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

Это просто артефакт наблюдения в среде ru-RU. На самом деле три - не фиксированное значение, а задано в локали (вшито в код), и может быть любым, причём не одинаковым для первой/второй/... последующих групп. Например, для Индии и Бутана это 3-2-..., т.е. первый (самый правый) разделитель обязан быть после 3 цифр, второй и последующие - после 2. Но наблюдаемое требование точного положения действительно отличает от Excel. Интересно, что для распознавания чисел в Basic, скажем, с помощью CInt, положение разделителей групп непринципиально.

[mikekaganski]

Вообще отмеченные различия в поведении, когда FormulaLocal меняет формат ячеек, а Formula для аналогичного ввода не меняет - на мой взгляд баг.

Баг 146440.

[sokol92]

Коллеги, спасибо за продуктивное внимание к теме. Попробую описать обработку текстов, "похожих на даты", хотя все случаи рассмотреть трудно. К данному сообщению прикладываю документ TestFormula.ods (версия 2), в котором увеличено число текстов для анализа по сравнению с ранее опубликованным вариантом.

9. Интерпретация текстов, похожих на даты.

Если Excel распознает текст как дату, то во всех случаях он меняет числовой формат ячейки на соответствующий формат даты.
Про возможные изменения форматов в Calc при присвоении свойства Formula см. п. 7 (это касается и всех примеров в пункте 9).

9.1. Даты в "ISO" формате.
Если дата задана без компоненты времени ("YYYY-MM-DD") то Excel и Calc ее распознают  - как при присвоении свойству Formula, так и FormulaLocal (по крайней мере, в ru_RU).
В компонентах месяца и дня незначащий ноль может быть отброшен.

Если год указан 2 цифрами, то Calc распознает такой текст как дату в формате ISO только при присвоении свойству Formula.
Excel не распознает такой текст при присвоении свойству Formula. При присвоении этого текста свойству FormulaLocal Excel (в локализации ru_RU) интерпретирует "-" как разделитель даты (то есть текст интепретируется по возможности, как "ДД.MM.ГГ").
Короче говоря, двузначный год лучше не применять (и ISO это не разрешает).

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

При присвоении таких текстов свойству FormulaLocal Calc меняет формат ячейки на соответствующий ("YYYY-MM-DD", "YYYY-MM-DD HH:MM:SS"). Eсли год указан 2 цифрами, то формат ячейки устанавливается в "DD.MM.YY HH:MM".

Этот раунд (9.1), на мой взгляд, в пользу Calc.

9.2. Даты в формате en_US (или, как указал Михаил, в каноническом формате).

Если месяц указан текстом (October) и присваивается свойству Formula, то Calc "понимает" как полную форму названия месяца, так и сокращенную до 3 букв (Oct). Excel "понимает" и промежуточные варианты (October, Octobe, Octob, Octo, Oct). Регистр букв в обоих случаях значения не имеет. Excel устанавливает в этих случаях формат "d-mmm-yy".

При указании месяца текстом Excel распознает дату в большем количестве случаев при присвоении свойству Formula, чем Calc (мне кажется). Например, такую дату "28 october 20" Excel распознает, а Calc нет.

Обе программы распознают даты D/M/YYYY и D/M/YY при присвоении свойству Formula.
Если такой текст присваивать свойству FormulaLocal, то Calc это значение интерпретирует как текст, а Excel - как дату DD/MM/YYYY, что приводило и приводит к многочисленным казусам (месяц и день меняются местами).

(Окончание следует)