Многоуровневые спецификации для производства в excel / php или др.

Добрый день!

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

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

Для понимания прилагаю файл с простым примером, что в целом нужно получить.

В файле:

1. Перечень номенклатурных позиций (собственно, элементов, которые участвуют в многоуровневой структуре) + цена на элементы "последнего уровня в своей структурной ветке". http://prntscr.com/xwz280 - используется как отдельный набор данных ("справочник").

2. Спецификации в "плоской" таблице - строки с элементами, входящими в другие элементы. http://prntscr.com/xwz6s3 Т.е. это набор "правил", по которым собираются элементы верхних уровней, промежуточных уровней. Там присутствует только правила "подчинения" - т.е. какой компонент входит в текущий элемент. Это главная таблица, которая влияет на построение "дерева спецификаций".

3. Собственно, расчетная таблица (блок, набор элементов) - где визуально оформлен результат вывода (калькуляции) данных в виде древовидной структуры. Как это будет оформлено в экселе - точно не могу сказать, можно приблизительно, просто чтобы визуально было видно "дерево" (можно как в моем примере, можно пробелами оформлять вложенные/подчиненные строки) - тут жду предложений.

http://prntscr.com/xwzhwt

На каком "языке" будет создано, если в экселе - не принципиально - VBA, DAX (Power Query), или (если вдруг получится) простыми формулами. Насколько я в своих первичных/скромных познаниях понимаю, система должна строиться на "рекурсивных" запросах (может и ошибаюсь, но много перепробовал для построения такого функционала).

Также можно рассмотреть варианты какого-то расчетного скрипта для данного "дерева" на виртуальный сервер (PHP, JS, SQL/MYSQL или др.), но который обязательно должен выгружать готовый расчет в эксель, т.к. далее полученные данные будут участвовать в дальнейших расчетах себестоимости и других аналитических расчетах (в примере файла видно, что после построения дерева - на полученные данные "накидываются" другие элементы подсчета себестоимости - их пока учитывать не нужно, главное - построение многоуровневых спецификаций).

Еще момент - заранее не известны уровни, их нужно будет вычислять по исходным данным (п.2 - выше Спецификации). И вывод нужен каждого уровня со своими подуровнями и соответственно, подсчетами по ним: Т.е. Продукция - со своими "ветками", Полуфабрикаты - ниже (или среди продукции - не принципиально размещение) - со своими подуровнями-ветками. Таблица может быть огромной, т.к. будут добавляться уровни, а элементы, естественно, могут повторяться, т.к. состоять друг из друга.

Постарался вкратце, возможно что-то не учел, поэтому готов обсуждать варианты исполнения, отвечу на доп.вопросы. Также у вас возможно есть готовые/полуготовые решения - рассмотрю их тоже. Условие - это должны быть отдельные программы, в виде файла (если в экселе, или программа на Винде), или скрипты для размещения на виртуальном/локальном сервере. Какие-либо спец.сервисы с платными подписками или урезанным функционалом типа Odoo, OpenBOM, а также сложные программы типа 1С прошу не предлагать. Нужно уникальное решение под мои требования с единоразовым вознаграждением за ваш труд.

Спасибо.

------------

Добавил примеры файлов с расчетами, которые я пытался сформировать. Но не хватает навыков, как правильно построить - там есть вкладки VAR 2 и VAR 3 в одном из файлов - там примерно собрана структура, как должно выглядеть, но не показан верхний уровень и неправильно считает калькуляцию товара по подуровням.

Информацию брал из этого источника, можно ознакомиться, может поможет

https://www.thebiccountant.com/2017/05/08/dynamic-bill-of-material-bom-solution-in-excel-and-powerbi/

https://www.thebiccountant.com/2017/06/13/bill-of-material-bom-explosion-part2-costing-in-excel-and-powerbi