Localizar insumos, multiplicando pelo serviço e somando essa multiplicação


#1

Olá a todos,

Amigos, preciso de ajuda.

Tenho uma planilha de serviços, e outra que é um banco de dados com as quantidades de insumos de cada serviço.
Tenho que localizar cada insumo dentro da composição do serviço, multiplicar o consumo do insumo dentro de cada composição e depois somar essa multiplicação.

Para construir uma muro de alvenaria de bloco, gasto determinada quantidade de insumos (bloco, cimento, areia, hora de pedreiro e de ajudante, encargos sociais complementares, etc), se tenho vários serviços e em quantidades variadas, os insumos serão somados pela quantidade gasta em cada serviço de acordo sua unidade, no caso do muro será em metro quadrado, multiplicado pela área total do muro exemplo. Até nesse ponto, consigo fazer buscas satisfatórias, porém, quando dentro de uma composição de serviço, tem uma outra “composição auxiliar”, e dentro de uma composição auxilar tem outra composição auxiliar me complico, pois o mesmo insumo pode estar em mais de uma composição principal e nas auxiliares, usando PROCV, ele localiza o primeiro resultado, mas ignora os demais, sem somar todos os resultados do mesmo insumo localizado em mais de uma composição ou composições auxiliares.

Explicando o exemplo do muro, caso queira levantar 5,00 m2 (metros quadrados):

COMPOSIÇÃO: MURO DE ALVENARIA DE BLOCO
DESCRIÇÃO---------------UNIDADE------CONSUMO-------TIPO

Pedreiro-----------------------Hora-----------0,5----------------Composição Aux.
Ajudante----------------------Hora-----------0,5----------------Composição Aux.
Argamassa de levante-----m3 -------------0,02--------------Composição Aux.
Bloco cerâmico--------------unid-----------13-----------------Insumo

ARGAMASSA DE LEVANTE
DESCRIÇÃO----------- UNIDADE--------CONSUMO-------TIPO

Pedreiro----------------Hora-------------0,25--------------Composição Aux.
Ajudante---------------Hora-------------0,25--------------Composição Aux.
Areia--------------------m3----------------0,0185-----------Insumo
Cimento----------------kg----------------1,5----------------Insumo

PEDREIRO
DESCRIÇÃO--------------UNIDADE------CONSUMO------TIPO

Pedreiro------------------Hora -----------1------------------Insumo
Vale Trasporte----------unid------------0,22--------------Insumo
EPI-------------------------hora------------1------------------Insumo

AJUDANTE
DESCRIÇÃO--------------UNIDADE--------CONSUMO-------TIPO

Ajudante-----------------Hora--------------1------------------Insumo
Vale Trasporte----------unid--------------0,22---------------Insumo
EPI-------------------------hora--------------1------------------Insumo

No exemplo acima, foi ignorado o preço unitário dos itens, o interesse é na soma das quantidades de cada um.

Sendo 5,00 m2 de muro, o resultado deveria ser: Pedreiro = (5 x (0,5 x 1)+(5 x 0,25 x 1) = 3,75 horas.
O mesmo se aplicando para os demais insumos do pedreiro e do ajudante.

Preciso somar as horas consumidas de pedreiro para a composição principal e a auxiliar para 5m2 de muro, porém, os insumos de pedreiro e servente, são buscados na composição de MURO DE ALVENARIA DE BLOCO, e são ignorados pelo procv na composição de ARGAMASSA DE LEVANTE, e ainda, o procv não deveria contabilizar os consumos quando estes fossem composições auxiliares para não gerar duplicidade.

Já apliquei: ÍNDICE, SOMASE, LIN combinados com o PROCV, mas não obtive êxito, acredito que estou me debatendo com alguma parâmetro nas fórmulas não consigo ver.

Não tive permissão para anexar a planilha aqui, mas espero que tenha conseguido explicar minha necessidade.

Desde ja, grato a todos!


#2

@ICP, veja se a função SOMARPRODUTO te ajuda nessa tarefa:


#3

Boa noite Xavier,

Primeiro quero agradecer a atenção, mas ja tentei usar soma de produto, porém, esse produto é indireto. Observe que o insumo “pedreiro”, aparece na composição MURO DE ALVENARIA tendo consumo de 0,5 dentro da composição auxiliar PEDREIRO. Em seguida, ele aparece na composição de argamassa de levante tendo consumo de 0,25 dentro da comp. aux. PEDREIRO. Porém, a composição de argamassa é uma composição auxiliar da composição MURO DE ALVENARIA e tem o consumo de 0,02. Sendo assim, o insumo pedreiro, seria a soma do produto da quantidade de serviço de muro pelo consumo nesta composição e o produto deste insumo pelo consumo de argamassa na composição de muro pelo consumo do pedreiro na composição da argamassa.

A conta seria mais ou menos esta para os 5 m2 de muro:
(0,55)+(0,020,25*5)
esta conta se aplicaria a todos demais insumos.
Imaginando que minha planilha original, tenho mais de 100 composições de serviços, isso me dá em torno de umas 10 mil composições totais, incluindo as auxiliares e uns 15 mil insumos, é inviável fazer essa busca manualmente. Preciso de uma fórmula que busque a incidência de cada insumo nas composições e nas auxiliares e os some pelo produto do produto (no caso de ser uma composição auxiliar) destas composições pela quantidade de cada serviço.

Espero que tenha conseguido explicar minhas necessidades.

Já tentei usar combinado as funções ÍNDICE, LIN, PROCV, SOMASE, SE, MENOR etc, mas não obtive êxito, resolvi so parte do problema da busca

Grato mais uma vez


#4

@ICP, pode postar o arquivo?


#5

Ja tentei anexar a planilha simplificada de exemplo, mas nao tenho permissão para anexar arquivo por ser membro novo


#6

Acabei de receber uma mensagem de SELO DE CONFIANÇA BÁSICO, pensei que já fosse possível postar a planilha, mas ainda nao tenho permissão rs
Vou aguardar até ter essa moral rs


#7

Ganhei permissão de posta link.

Esse link é de um site de transferência de arquivos.

Link para download


#8

@ICP, me desculpe, mas ainda não consegui compreender de onde vem todas as informações e todas as regras de cálculo.


#9

Na planilha exemplo, tem duas abas: Planilha de Serviços e Banco de Composições

Digamos que tenha uma planilha de serviço com vários serviços (geralmente mais de 100 itens).
Eu tenho um banco de composições que serve como banco de dados.
Mas em orçamento de obra, tenho que dá ao cliente uma lista de todos insumos que ele precisará comprar e suas quantidades.
Para isso, tenho que criar uma fórmula ou em uma nova aba ou na própria aba “banco de composições”, (nos meus testes apliquei esta fórmula na coluna H), onde nesta fórmula eu consiga localizar as composições, cujo o código na coluna B da “Planilha de Serviço”, seja localizado na coluna B da planilha de “Banco de composições”, a partir desta localização, é que ele identificará a ocorrência do insumo em determinadas composições, porém alguns insumos, como no exemplo de pedreiro, ele aparece mais de uma vez de forma indireta. Primeiro ele aparece na composição de “MURO DE ALVENARIA DE BLOCO” que é o serviço propriamente dito e ele aparece de forma implícita na composição de “ARGAMASSA DE CIMENTO”.
Afim de simplificar a ajuda, eu descartei a busca da composição principais (no exemplo da planilha principal, pois so tem uma composição de serviço), ficando apenas na localização multiplicação pela composição e soma destes insumos tanto na composição principal, como na composição auxiliar.

Espero que tenha conseguido explicar


#10

@xavier
Boa tarde,
Conseguiu entender que a busca é pelo código da coluna B na planilha de serviços e a busca posso colocar numa coluna ao lado do banco de composições e insumos para que cada vez que localize o insumo ou uma composição auxiliar que tenha o insumo ele multiplique e some?

Realmente já não sei o que fazer, ja tentei diversas fórmulas


#11

Pessoal, ainda estou precisando de ajuda com esse problema, se alguém puder, fico muito agradecido pela ajuda!

Teste_SOMA_de_INSUMOS.xlsx (15,9 KB)


#12

@ICP, ainda não entendi, não. Na verdade eu vi que tem o serviço e a tabela dele lá, mas não era isso, claramente, a sua dúvida. De qualquer forma, essa abordagem não está muito boa, talvez valha a pena tentar pensar em outra abordagem.

Eu até fiz um lance ali mas acho que não era o que você estava buscando. Dá uma olhada aqui: Teste_SOMA_de_INSUMOS.xlsx (16,9 KB)