Guru do Excel e Power BI

Olá a todos, tudo bem?

Pessoal, estou trabalhando numa planilha, não consigo resolver uma questão.

Tratasse de uma planilha de serviços, onde tenho que buscar os insumos, exemplo:

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.

Espero que tenha conseguido explicar minha dúvida e que você possa me dá essa ajuda.

Segue anexo uma planilha de exemplo.


Grato desde já!
Dailton

PROCV(B6;$G$4:$N$92;8;FALSO)
Esse trecho da sua fórmula faz uma referência circular.

Este trecho da busca, PROCV(B6;$G$4:$N$92;8;FALSO), seria para se, no caso do serviço não ser uma composição principal, ele buscar o código na coluna B pela coluna G das composições auxiliares e assim localizar os insumos.

Tentei esta fórmula matricial, mas também não deu certo: {=SE(PROCV($B4;'PLAN SERVIÇOS'!$B$4:$G$5;1;FALSO)=$B4;(ÍNDICE($B$4:$L$110;MENOR(SE($B$4:$L$110=$B4;LIN($B$4:$L$110)-LIN($B$3));LIN(1:1));9)*PROCV(B4;'PLAN SERVIÇOS'!$B$4:$G$5;5;FALSO)))}

Preciso mesmo de ajuda

Se alguém puder ajudar pelo menos com o exemplo da postagem, já será de grande ajuda e uma luz para eu resolver a planilha original.

Qual a versão do seu Excel?
O q me parece q vc quer é se o valor não for obtido na tabela da aba PLAN SERVIÇOS buscar na tabela de INSUMOS pelo valor na coluna L quando os códigos das colunas B e G forem iguais. Se for isso a fórmula seria:

Estou muito grato pela atenção.
Jé tentei esta fórmula que sugerida. Mas preciso que ele não apenas localize a primeira ocorrência, como some todos os consumos.

Minha versão do Excel é 2013.

Estou anexando outra planilha, mais simplificada, para ajudar na compreensão.

Grato mais uma vez!

Olá pessoal!

Quero compartilhar a solução deste tópico, os créditos são do Rodrigo, ele tem um blog sobre o excel, não citarei o nome em respeito ao fórum (não estou certo sobre a política de citação de outros fóruns ou blog).

Estou anexando a planilha exemplo para quem estiver com o mesmo problema, poder utiliza-la.
A solução se aplica, não somente a quem faz "orçamento para construção civil", mas em diversas áreas, basta alimentar o banco de "COMPOSIÇÕES/INSUMOS" com seu banco disponível (SINAP, ORSE, etc) e fazer a busca partindo de sua planilha de serviços.

Agradeço a atenção e espero que esta planilha seja útil.

Abraços!