SELECT * FROM Vzakladke.net

Статьи об автоматизации и программировании


Расчет суммы значений по дереву элементов с учетом применяемости вышестоящих узлов в Excel применяя формулы или VBA

 

 

Алгоритм, который позволяет считать сумму цен по составу изделия, когда состав выгружается в иерархической последовательности, с учетом как применяемости самих деталей, так и применяемости вышестоящих сборочных узлов. При этом, если задана цена на сборочный узел, то расчет ее внутренних составляющих не требуется. Если цены нет на сборку, то данную ячейку нужно залить красным цветом, а желтым цветом – с не полностью оцененным составом. 

Начинаем формировать таблицу.

Первый столбец «» – задается путь по структуре в виде иерархии, например, 1 – 1.1 – 1.2 – 1.2.1 – 1.2.2 – 1.3.

Второй столбец «Идентификатор» принимает значение обозначения элемента. Зададим тогда и столбец Наименование».

Далее представим столбец «Тип объекта», где будем указывать «Сборочная единица», «Деталь», «Прочее» и т.д.

Столбец «Есть ли структура» будет позволять нам иметь представление о наличии структуры у элемента.

Столбец «Количество» содержит количество элементов на заданном уровне.

Последним в исходной таблице будет столбец «Цена».

Для реализации задачи по подсчету суммы с учетом количества вышестоящих уровней в MS Excel попробуем применить формулы. Для начала попробуем найти по первом столбцу (колонка A) номер пути родительского элемента, например, в 5-ой строке:

=ЕСЛИОШИБКА(ЛЕВСИМВ(A5;ЕСЛИОШИБКА(НАЙТИ(".";A5;ДЛСТР(A5)-1);ЕСЛИОШИБКА(НАЙТИ(".";A5;ДЛСТР(A5)-2);ЕСЛИОШИБКА(НАЙТИ(".";A5;ДЛСТР(A5)-3);"")))-1);"")

Узнаем уровень, на котором находится наш элемент:

=ЕСЛИ(A5="0";"";ДЛСТР(A5)-ДЛСТР(ПОДСТАВИТЬ(A5;".";""))+1)

Можем даже посмотреть наверх, чтобы уточнить значения в столбце «Количество» у родительских элементов

=ЕСЛИОШИБКА(ВПР(AB5;$A:$K;СТОЛБЕЦ($K$1);0);1)

Где AB5 – это ячейка с родительским номером в пути, а СТОЛБЕЦ($K$1) – это номер столбца для отступа вправо по функции ВПР.

Далее мы столбец с ценой умножим на столбец с количеством (запишем результат в столбец Z), но с условием что на родительский элемент не проставлена цена, иначе мы будем считать цену, которая задана сверху:

=ЕСЛИ(ЕСЛИОШИБКА(ВПР(AB5;A:Y;СТОЛБЕЦ(Y$3);0);1)>0;0;Y5*K5*ЕСЛИОШИБКА(ВПР(AB5;A:K;СТОЛБЕЦ(K$3);0);1))

Позже попробуем просуммировать все нижестоящие элементы по каждому узлу – если есть сумма, то мы используем ее, иначе суммируем:

=ЕСЛИ(Z5>0;Z5;СУММЕСЛИМН(Z5:СМЕЩ(Z$5;0;0;СЧЁТЗ(B:B);1);AB5:СМЕЩ(AB$5;0;0;СЧЁТЗ(B:B);1);A5&"*"))

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

 

Остальной текст могут видеть только привилегированные пользователи зарегистрированные на этом сайте.

Дата публикации: 2017-02-23 19:06:26

VBA, Полезное

2

Отзывы:

Джонни Д.
А верхний уровень я могу найти такой формулой: =ЕСЛИОШИБКА(ЛЕВСИМВ(A5;НАЙТИ(".";A5)-1);0)
олег
Помогите разобраться или как правильно сделать? Или к к ому обратиться "ответьте пожалуйста что это значит это пароль и логин для парсера где взять" "В парсере нужно указывать данные прокси серверов, а не хостинга вот такую фразу я обнаружил что это значит ?" curl_setopt($curl, CURLOPT_PROXYUSERPWD, 'логин:пароль'); в этой строке это как вы поняли я указал хостинг нужен логин и пароль нужно указывать данные прокси серверов, а не хостинга ? как это сделать не подскажите что бы парсер работал?

Ваше имя:

Ваш e-mail (необязательно):

Сообщение:

Captcha