Сводная на базе сводной таблицы

[eeigor]

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

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

Похоже, здесь я не могу реализовать ни то, ни другое: ни построить вторую сводную на базу первой, ни создать вычисляемое поле во второй.

Какие могут быть варианты вот для такого ступенчатого анализа данных?
Только макросом? Например, сгруппировать данные в сводной таблице, после чего "забрать" эти данные и поместить в отдельный диапазон, к которому добавить вычисляемое поле, и на этом диапазоне построить вторую сводную таблицу.

Я без претензий, но заниматься развитием сводных таблиц никто, видимо, не хочет. Отставание более чем разительное. Не говоря уже о том, что в Excel с 2013 года сводные таблицы поддерживают реляционную модель данных и позволяют строить отчёты на базе нескольких таблиц и установленных связях между ними по ключевым полям.

Опять макросы. Но они снижают быстродействие. Или Base + SQL.

[kompilainenn]

Я без претензий, но заниматься развитием сводных таблиц никто, видимо, не хочет. Отставание более чем разительное. Не говоря уже о том, что в Excel с 2013 года сводные таблицы поддерживают реляционную модель данных и позволяют строить отчёты на базе нескольких таблиц и установленных связях между ними по ключевым полям.

Нанимаете кодеров - они вам пилят нужный вам функционал. Красота

[eeigor]

Можно ли провести вычисления прямо в Calc (без обращения к Base) с использованием SQL в макросе? В Excel я без особого труда подключался к библиотекам DAO (старой) и ADODB ("новой"). Новая позволяла создавать объекты независимо (например Recordset), тогда как старая требовала создавать объекты по иерархии сверху вниз и забирала больше ресурсов. Приходилось использовать и иерархические наборы данных (SQL-операторы SHAPE, RELATE TO). Как это сделать из Calc?

Могу ли я подключить DatabaseRange к такому источнику прямо в Calc?

[sokol92]

В этой теме много вопросов. 

Можно ли провести вычисления прямо в Calc (без обращения к Base) с использованием SQL в макросе?

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

[economist]

Тема "вычисляемых полей" хорошо решена в Pandas. В Таблице с полями Поставщик, Сумма, Колво - колонка цена вставляется незатейливо:

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

df.eval("Цена=Сумма/Колво")       # так     
df.assign(Цена=df.Сумма/df.Колво) # или так, если данные в разных таблицах

Причем Pandas кучу геморроя с делением нуля и "на 0" - берет на себя, если значения просто пустые (как и положено). Векторные вычисления матриц выполняются очень быстро.

Более того, сводная таблица (df.pivot_table), а также ее 99%-е аналоги pd.crosstab (шахматка) и группировка df.groupby() на выходе сами дают df-таблицы и могут быть сгруппированы следующей командой или цепным методом, в этой же строке df.groupby().groupby()

Практически всякий раз когда слышу восторги про SmartTables/PowerQuery/PowerPivot/DAX/M/SQL от пользователей MSO, я вижу где это взято (у Pandas/R/Matlab). Для чего это сделал Microsoft - понятно, им нужно было срочно "восхитить" паству и оторваться от окрепших свободных офисных пакетов, увеличив длину своего списка киллер-фич во много раз. И они это сделали за счет СПО, затмив не только конкурентов, но и самих себя.

А как быть нам в LO Calc? Весь этот огромный (и уже прекрасно реализованный в другом СПО) функционал киллер-фич - тянуть в Calc, кмк, просто неподъемно для разработчиков. Умные таблицы не настолько умны и быстры, а сводные не настолько важны, чтобы без них нельзя было сделать 80% работы. Оставшееся можно сделать в другом свободном ПО.

[eeigor]

@economist, я с Вами согласен. Единственно, если не ошибаюсь, впервые придуманы и реализованы сводные таблицы были в Excel ещё в начале 90-х. Поэтому сводные таблицы являются неотъемлемой частью электронных таблиц. Их надо иметь под рукой. И возможность создавать вычисляемые поля, и сохранять форматирование когда-нибудь всё-таки разработчики LO Calc нам предоставят.

А Pandas, да, оказался на редкость удачным проектом.

когда слышу восторги про SmartTables/PowerQuery/PowerPivot/DAX/M/SQL от пользователей MSO

Дело в том, что там сильнее развит интерактивный режим работы. Python + Pandas использует больше программист (а это другое мышление), а не пользователь (который "работает мышкой"). Технологии Microsoft – это другой "слой". Язык M в Power BI, к примеру, является способом записи "всего и вся" вслед за пользователем (прекрасный macrorecorder). С самим языком можно познакомиться и позднее или вообще этого не делать. А сама запись на M приближена к естественному языку. Кстати, некоторые кадровые органы считают владение Power BI обязательным и учитывают это при найме сотрудников. Не знаю, как сейчас будет.

UPD. https://ru.m.wikipedia.org/wiki/Сводная_таблица

[economist]

В решениях от Microosft

сильнее развит интерактивный режим работы.

- согласен. Но эти решения (особенно PowerBI) теперь не купишь (либо живи по принципу "воруй - не пасодют" и бойся закладок уже не на шутку). Интерактивный режим поначалу неплох, но многие на нем застревают, накликивая мышкой абсолютно все.

На мой взгляд важным итогом прошедшего десятилетия оказался массовый уход от hardcode-аналитики и интерактивного over-clicking мышью в пользу... Итеративной аналитики.

Внезапно оказалось что чистка и анализ данных, DS/ML - это сугубо итеративные действия с многократным возвратом "на десять" шагов назад. PowerBI c SQL/DAX/M (и Excel) - не дают итеративности. Здесь массово "зашла" Pandas c cвязке с оболочкой Jupyter/JupyterLab (интерактивный интерпретатор Python, своеобразная GUI для Pandas).

Этот принцип повторили все DS-платформы, все облачные провайдеры (Amazon, Google итд): код по ячейкам блокнота теперь увидишь везде. До заката Excel, конечно, далеко (его и не будет), но роль абсолютно незаменимого инструмента он, на мой взгляд, для бизнеса уже утратил. И не только у нас, но и во всем мире. Поэтому копирование фич 1:1 из Excel в Calc, за которое я сам ратовал 10 лет назад - теперь неактуально. Нужно брать только лучшее и важное, и в этом разработчики, кмк, правы.