Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Расчет стоимости по уровням вложенной иерархии, только формулы
 
#1
15.05.2025 12:33:45
Добрый день.
С вопросом про сводную получилось грустненько.

Продолжаю терзать ту же задачку с другой стороны. Технически правильные данные я получаю через Power query.
Но начальство сводной пугается, хочет "на формулах".

Я кое как решила, результат считается, но вид промежуточных столбцов пугает почти как несворачиваемая сводная с пустыми аналитиками.

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

Итог на оранжевом, надо посчитать итоги для всех вложенных уровней. На белом мои бешеные формулы.

Вопрос - можно ли это как-то менее страшно сделать.
 
 
 
#2
15.05.2025 13:39:29
Добрый день, Xel
А если для начальства сделать отдельный файлик , где будут только нужные им столбца и строки в виде значений (без формул).

В моей практике для начальства ежедневно подавалась табличка три столбца - три строки, которые были выжимкой из файла с десятком листов на которых были расчеты...
 
 
 
#3
15.05.2025 13:48:35
Мне после истерического долбания с 2  6 десятками вспомогательных столбцов (уровней и разных видов цен больше на самом деле)  уже самой чертовски интересно, можно ли это сделать как-то менее уродско.

И в рабочие многочисленные файлы мне это теперь копировать и орать от ужаса каждый раз  :D  Я все надеюсь, что есть колдунство, которое мне в панике в голову не пришло и это делается одной коротенькой формулой, как тут бывает.

И начальству это надо не для взгляд бросить, а для покопаться всласть во всех  тысячах строк.
Изменено: Xel - 15.05.2025 13:51:30
 
 
 
#4
15.05.2025 16:58:01
Цитата
Xel:   ...надеюсь, что есть колдунство, ... и это делается одной коротенькой формулой...
если "уровень" оставить, то можно    сравнительно не сложно:
=ЕСЛИ(I5>0;I5;СУММЕСЛИ(L6:ИНДЕКС(L6:L$21;ПОИСКПОЗ(1=1;ИНДЕКС(L6:L$21<=L5;); )-1);L5+1;K6:ИНДЕКС(K6:K$21;ПОИСКПОЗ(1=1;ИНДЕКС(L6:L$21<=L5;); )-1)))*J5
...без него уже мне не понравится и, вероятно, вашему вычислителю )
 
 
 
#5
15.05.2025 17:30:48
Цитата
Павел \Ʌ/ написал:
если "уровень" оставить, то можно    сравнительно не сложно:
Спасибо огромное!
Сейчас я об эту красивую формулу расширю сознание и как пойму ее ...
Изменено: Xel - 15.05.2025 17:31:02
 
 
 
#6
15.05.2025 21:27:05
напрашивается вопрос, зачем дублировать иерархию?
По вопросам из тем форума, личку не читаю.
 
 
 
#7
16.05.2025 15:20:02
Второй лист во вложенном файле и формулу массива в нем удалось не сразу обнаружить  :oops:

Зато разобралась что и как, пока неправильно пристраивала формулу куда попало  :D

Еще раз огромное спасибо, это супер.
Изменено: Xel - 16.05.2025 17:15:53
 
 
 
#8
16.05.2025 17:11:56
1) Оставить одну колонку с уровнями (больше не надо)
2) Разбить данные на группы: Пирог, Кес... (рекомендуется всегда, иначе будут ошибки)
3) Пересчитать цены =IF(E5="",SUMIFS($H$5:$H$20,$D$5:$D$20,D5+1,$B$5:$B$20,B5),E5)
Все
 
 
 
#9
16.05.2025 17:42:34
Цитата
Metanor написал:
Разбить данные на группы: Пирог, Кес... (рекомендуется всегда, иначе будут ошибки)

Спасибо.

Ваш метод работает, пока в каждой "группе" есть только по одной ветке из вложенных уровней. А их там даже не десятки.
В примере, если добавить еще раз в Пирог строки, начиная с "Упаковка пирога 2" и дальше вниз, сразу все начинает безудержно на все умножаться.

Так что группы не спасут, и они есть, просто именно в таком  иерархическом виде, в который возжелали получить формулы, они не очевидно видны. Хранятся данные совсем в другом виде, для своих расчетов я все потрошу об PQ и там есть группы на все уровни, не только на первый, без них и правда никуда.

Иногда трудно не упустить важные детали, типа этой, делая демонстрашку для примера, приношу извинения.
Изменено: Xel - 16.05.2025 17:44:18
 
 
 
#10
16.05.2025 18:04:49
Xel, пожалуста
Цитата
формулу массива в нем удалось не сразу обнаружить
)
...формула не требует массивного ввода, но это можно исправить, убрав пару ИНДЕКС()ов...    ;-)
 
 
 
Страницы: 1
Читают тему
Наверх