[BUG] Calc: Structured Formulas (Database Range References)

Автор eeigor, 3 июля 2020, 07:03

0 Пользователи и 1 гость просматривают эту тему.

eeigor

Structured Formulas (Database Range References) - Структурные ссылки к диапазонам базы данных

Диапазон базы данных задается в меню «Данные - Задать диапазон...». Откроется форма «Задать диапазон данных».
Здесь под базой данных понимается обычный список (диапазон) с некоторым дополнительным функционалом.

Вероятно, мы говорим о недокументированной возможности LO Calc, но возможности очень полезной, даже необходимой!!

Структурные ссылки позволяют обратиться к различным элементам-поддиапазонам диапазона базы данных, используя более наглядную нотацию, не создавая имена этих поддиапазонов заранее.
Например: =СЧЁТА(ДВССЫЛ("Таблица[[#Всё]]"))
В примере выше вычисляется количество ячеек диапазона базы данных с именем "Таблица", включая строку заголовков и строку итогов, если они есть.
В нотации A1 это могло бы выглядеть так: =СЧЁТА($Лист1.$A$1:$Лист1.$B$5)

Примечание. Структурная ссылка зд. обёрнута функцией ДВССЫЛ(), потому что иначе при повторном открытии файла всякая структурная ссылка будет заменена абсолютной, если использовать родной формат ODS. В формате OOXML (Microsoft Excel 2007 и выше) они сохраняются (зато будет удалён макро-модуль и пр.). Другими словами, использование двойной ссылки представляет собой заплатку для родного формата. Пока так.

Дозволенные структурные метки на английском и далее на русском приведены ниже.
"Заголовок1" и "Заголовок2" - это уже произвольные имена пользователя. Метки предваряются знаком #.

Параметры - LibreOffice Calc - Формула - Параметры формулы: Использовать английские имена функций

BUG 1:
Структурные метки удаётся вводить только на русском языке. Если установить использование английских имён функций, то все формулы и метки будут переведены на английский язык, и они будут работать. Но в этом режиме ввести ту или иную метку на английском языке не удастся: они не воспринимаются. Обратите внимание на "БУДУТ РАБОТАТЬ". Достаточно добавить пробел к рабочей метке на английском и убрать пробел обратно – всё: распознано не будет.

BUG 2, вытекает из первого, всё тот же BUG 1:
Если установить использование английских имён функций, то метки на английском языке в составе аргумента функции INDIRECT() не воспринимаются (тем более на русском).
То есть ни одна из двух приведённых ниже конструкций работать не будет:
=COUNTA(INDIRECT("Таблица[[#Всё]]"))
=COUNTA(INDIRECT("Таблица[[#All]]"))
Вторая могла бы работать, не правда ли, но не работает!

=COUNTA(Таблица[[#All]])
А так работает, но не сохраняется! Работает, если сначала ввести метку "#Всё", а уже потом включить режим отображения «Использовать английские имена функций». Почему эти метки, будучи переведёнными машиной, работают, а введённые вручную – НЕТ?

Работает "по уму" только так:
=СЧЁТА(ДВССЫЛ("Таблица[[#Всё]]"))

Функция ДВССЫЛ(), что, умнее функции INDIRECT()? Или это проблема локализации, когда в русской версии не всё "английское" работает даже в соответствующем режиме? Почему функция INDIRECT() не вычисляет ссылку "#All" и другие (полный перечень ниже) в составе строки?

Таким образом, применение структурных ссылок вынуждает не использовать английские имена функций. Или ограничивать использование структурных ссылок только именами столбцов пользователя (без структурных меток, которые не воспринимаются на английском).
Это не есть хорошо!
А так работать, естественно, будет (берутся данные диапазона столбцов):
=COUNTA(INDIRECT("Таблица[[Заголовок1]:[Заголовок2]]"))


Example Refs (English Names)
Таблица[[#This Row]]
Таблица[[#This Row];[Заголовок1]]
Таблица[[#This Row];[Заголовок1]:[Заголовок2]]
Таблица[[#All]]
Таблица[]
Таблица[[#Headers]]
Таблица[[#Data]]
Таблица[[#Totals]]
Таблица[[#Headers];[#Data];[Заголовок1]]
Таблица[[#Data];[Заголовок1]]
Таблица[[Заголовок1]]
Таблица[[Заголовок1]:[Заголовок2]]
Таблица[[#Totals];[Заголовок1]]

Example Refs (Local Names)
Таблица[[#Эта строка]]
Таблица[[#Эта строка];[Заголовок1]]
Таблица[[#Эта строка];[Заголовок1]:[Заголовок2]]
Таблица[[#Всё]]
Таблица[]
Таблица[[#Заголовки]]
Таблица[[#Данные]]
Таблица[[#Итоги]]
Таблица[[#Заголовки];[#Данные];[Заголовок1]]
Таблица[[#Данные];[Заголовок1]]
Таблица[[Заголовок1]]
Таблица[[Заголовок1]:[Заголовок2]]
Таблица[[#Итоги];[Заголовок1]]

Примечание. Использование двух подряд квадратных скобок в некоторых случаях избыточно, если, к примеру, в скобках помещено имя столбца без пробелов в имени (см. пример ниже).

Нет, конечно, можно использовать обычные именованные диапазоны вместо структурных ссылок, создав их заранее. Так и делают. И тогда вместо структурной ссылки на данные столбца поставим, например, имя Заголовок1 без кавычек. Но ведь их и придумали для того, чтобы избавить нас от необходимости создавать имена! И ещё: одно дело следить за корректностью ссылки на диапазон базы данных с именем "Таблица", и совсем другое дело следить за правильностью ссылок всех именованных диапазонов, построенных на его основе. И нет пока в LO Calc'е "умных таблиц" как в Excel, расширяющихся автоматически (ListObject).
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

sokol92

Некоторые комментарии.
1. Функция ДВССЫЛ (INDIRECT) MS Excel относится к короткому списку "летучих" (volatile) функций. Эти функции пересчитываются при изменении любой ячейки любой открытой книги Excel. Массовое применение этой функции настоятельно не рекомендуется разработчиком и обычно приводит к параличу системы.
2. Структурированные ссылки описаны Microsoft как дополнение к стандарту здесь. В LibreOffice 6.x их поддержка, насколько я понимаю, ограничена интерпретацией формул форматов *.xl??. Не ясно, о каких багах идет речь в заголовке темы.
3. Использование английских имен функций не означает смену локализации. Текстовые литералы в формулах (как в ДВССЫЛ("Таблица[[#Всё]]") ) при отображении формул в локализованном виде никаким преобразованиям не подвергаются.
Владимир.

eeigor

sokol92, ну, за "параличом" следим: всё приемлемо. Кстати, OFFSET() тоже в списке волатильных, и я бы её использовал очень часто, если бы Calc работал с динамическими диапазонами данных. Но нет, не работает... в смысле не принимает их в качестве источника данных диаграммы или сводной таблицы.

При использовании английских имён не работает ни один вариант:
=COUNTA(INDIRECT("Таблица[[#Всё]]"))
=COUNTA(INDIRECT("Таблица[[#All]]"))

О том и писал, относя это к багу.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

mikekaganski

Тоже некоторые мысли.

Описанное - баг.
Но описано так, что читать не хотелось :) - огромная портянка с кучей рассуждений и - самое ужасное - раскраской. Ладно бы была пара мест, выделенных жирным...

Вот то, что было написано в ответе #2, близко к идеалу. Хороший багрепорт - это:

1. Короткий (насколько это возможно).
2. Чёткий - позволяющий чётко проделать что-то и увидеть проблему. (Здесь, если надо, появляются шаги и/или файлы.)
3. В нужном месте. Вот по этому критерию вообще печаль ;)
С уважением,
Михаил Каганский

eeigor

mike, вряд ли это был баг-репорт, ибо место для это выбрано не подходящее. От выделений в будущем воздержусь, но причиной было то неудобство, которое даёт 5-дюймовый экран смартфона: цвет помогал схватывать главное... Это было приглашение к дискуссии. Исправлюсь.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

mikekaganski

#5
Тут, на самом деле, скорее всего именно проблема локализации. При которой не должны были быть переведены строки типа "#All". Или же надо как-то отдельно учитывать язык формул при парсинге строк структурированных ссылок. Надо писать баг и смотреть, что тут можно придумать.

Я вообще противник "дискуссий перед написанием бага". Всё, совершенно всё, что не нравится пользователю, должно быть сначала описано в багрепорте, а потом уже, если надо, обсуждаться. Какой смысл от обсуждения до? ну да, я могу написать "это не баг", и убедить в этом человека; но с таким же успехом это делается в баге. А если это таки баг, то сначала тратится время и силы (и пишущего, и читающих, и дискутирующих) тут, а потом всё равно это надо писать там, и снова обсуждать уже с другими людьми... смысл?
С уважением,
Михаил Каганский

eeigor

#6
mike, согласен. Но я не программист, а продвинутый пользователь. А пользователей у продукта маловато. А именно армия пользователей – главные тестировщики. Уж как смогли – выявили, написали... это уже хлеб. Скорее, я писал в надежде, что кто-то другой, близкий к этому, напишет баг-репорт. Например, вы.
Но продует мне не безразличен.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

mikekaganski

Хе, логично :)

На самом деле не очень здорово. Я, конечно, время от времени пишу такие баги. Но только когда меня это заинтересует. Потому что я здесь - волонтёр. И это потом мне будут приходить вопросы от других, кто будет тестировать баг, и я должен буду там уточнять, в своё свободное время. И возможно, эти вопросы будут ставить меня в тупик: скажем, я не пользуюсь структурированными ссылками, и вопросы о практическом применении заставят меня лезть в дебри того, что мне не надо.

Написание бага - начало работы; работа пользователя, написавшего баг, на этом не закончена. И нет, этот баг мне не интересен с точки зрения принятия за него ответственности.
С уважением,
Михаил Каганский

eeigor

#8
С локализаций здесь также, как со значением ADDRESS в аргументе функции ЯЧЕЙКА(). Зачем в Excel'е его перевели на русский? А в Calc'е оставили на английском, что верно. Но там работает, как оказалось, и английский вариант, с толку сбивала документация на русском.
Но в нашем примере со структурными ссылками есть проблема локализации.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

kompilainenn

Цитата: eeigor от  3 июля 2020, 18:41
mike, согласен. Но я не программист, а продвинутый пользователь.
И я не кодер, а простой, никуда не продвинутый пользователь. Однако это не мешает мне писать баги.
Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут

economist

Не страшно, [структурные_ссылки] даже в MSO Excel ("умные таблицы", диапазоны баз данных) прошли 4-х ступенчатую эволюцию в 2007, 2010, 2013 и 2016 гг., c преодолением десятков багов, включающих "редактирование без права на ошибку". В MSO365 поведение при отображении/редактировании  формул тоже изменилось.

Но в целом все эти "суперформулы" для меня настолько нечитаемы, что разбираться с подобными вещами просто перестал. Всё-таки нет ничего проще простой таблицы с именами в виде "правильных" заголовков строк/столбцов, доступных со времен  MSO 95. Да и давно уже в Calc можно создать "расширяемый" диапазон как на именах, так и на механизмах чтения/импорта/связи с др. файлами (без форматирования, что зело добавляет скорости). 

Тот же =ДВССЫЛ/INDIRECT, если он в 1-3 яч. листа - уместно, терпимо, и даже вау. Но когда на этом строят весь лист, переключающий исходные данные по десяткам листов и для 100-1к формул - начинаются тормоза и "ежики" на внешних/вн ссылках. По опыту - радостные первооткрыватели ДВССЫЛ() через пару-пяток лет от неё полностью отказывались (с Excel работаю с 1995 г., кол-во виденных кейсов - десятки предприятий с медианой ~20 чел. пользователей MSO Excel, что соответствует общей численности от 300 до 3k чел.)

Кмк, для формулы из (0) нужен SQL, выполняющий запрос на отдельном листе. Задачу ТС отразить рез-т автофильтрации с учетом вычислением по =СУММЕСЛИМН() - решить обычными короткими функциями - нельзя. Но наверное это отчасти излишне усложненная, надуманная  задача, порожденная чрезмерной интерактивностью формы журнала.

Тут просится SQL c параметрическим запросом из 1-3х значений ячейки. Или просто другой журнал "текущих" заданий, который легче формировать каждый раз при заново (за 2-3 сек.), чем хранить в актуальном состоянии.
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...