Dúvidas sobre cálculos, funções simples e aninhadas, fórmulas matriciais, etc.
Por HansRB 16 Set 2020 às 15:24
Membro Novato
Mensagens: 5
Reputação: 0
#58925
Boa tarde pessoal!

Estou buscando uma alternativa para um teste que preciso fazer em várias células. Já pesquisei aqui no fórum mas não achei algo que realmente consegi aplicar a minha situação. Já consigo o resultado esperado da planilha, porém a fórmula acredito que não esteja em um formato ideal. A planilha é usada para fazer gestão de riscos, então tenho os seguintes dados:

Fórmula atual: =(SOMA(SE(D3=1;'R-001'!A1;0);SE(E3=1;'R-002'!A1;0);SE(F3=1;'R-003'!A1;0)))/C3

Controle - É um determinado fator ou ação que pode ou não ter riscos associados;
Pontuação Média do Risco - É a média da criticidade dos riscos existentes em um determinado controle;
Total de Riscos - É a soma de quantos riscos estão relacionados a cada controle;
Risco 00X - É o risco em si. Na planilha PRINCIPAL, os valores são booleanos, ou seja, se o risco existe ou não para dado controle. No caso estou usando o valor 1 quando o risco existe e deixo em branco quando não existe;
Planilhas R-00X - São todas as planilhas específicas para cada risco, ou seja, cada risco tem sua própria planilha e dentro dela, um valor (peso) do risco;

O que preciso é que para cada linha (Controle) a fórmula identifique quais riscos (células D3, E3, F3, ...) estão ativos (com valor igual a 1 e somar a quantidade destes riscos ativos na célula C3) e para cada risco ativo também seja buscado o valor (peso) do risco em suas respectivas planilhas (R-001, R-002, R-003,...), somando-se esses valores (pesos) e dividindo pela quantidade já calculada em C3).

Minha questão é justamente que devido a quantidade de Riscos e Controles, não acho ideal usar a fórmula atual, pois deste modo preciso fazer um teste concatenado para cada risco/coluna (hoje em torno de 78 podendo aumentar). Teria que ver se há como fazer esse teste informando o intervalo a ser testado.

Depois, para cada linha (Controle) a fórmula deverá fazer o mesmo processo, apenas alterando o indexador da linha.

Eu tentei deixar a planilha o mais simples possível, pra focar apenas nessa situação que estou precisando, então se não entenderem algo, por favor, perguntem.

Muito obrigado pela atenção e ajuda de vocês.

Abraços
Apenas usuários registrados podem ver ou baixar anexos.
Avatar do usuário
Por mucascosta 16 Set 2020 às 18:23
Membro Novato
Mensagens: 74
Reputação: 34
#58927
Em anexo uma sugestão via macro. Os riscos são informados na própria Aba PRINCIPAL...
Apenas usuários registrados podem ver ou baixar anexos.
Avatar do usuário
Por Deciog 18 Set 2020 às 09:20
Membro 5 Estrelas
Mensagens: 1547
Reputação: 823
#58976
HansRB, Bom Dia.

Esta formula funciona para versão 2013 e superior

Para a versão Excel antes de 2019, feche com CTRL + SHIFT + ENTER, são formula matricial

Confere se é desta forma que desejas

Se minha resposta foi útil, clique em obrigado é uma forma de agradecimento da ajuda

Decio
Apenas usuários registrados podem ver ou baixar anexos.
Por HansRB 19 Set 2020 às 11:28
Membro Novato
Mensagens: 5
Reputação: 0
#59007
mucascosta escreveu:Em anexo uma sugestão via macro. Os riscos são informados na própria Aba PRINCIPAL...

Bom dia, mucascosta! Muito obrigado pela ajuda. A solução com Macro eu estava tentando deixar como segunda opção, pois as planilhas que estou usando são da empresa e não queria alterar a estrutura delas nesse nível. De qualquer modo, eu dei uma olhada e ela funcionou parcialmente. Uma condição que dá problema é se eu não marcar nenhum risco (não colocar o 1 em nenhuma coluna - o que pode acontecer, pois às vezes os controles não apresentam riscos), pois ocorre divisão por zero. Outra coisa que percebi é que o cálculo está com comportamento estranho. Abri a planilha e já consta no Controle 3 por exemplo, onde há um risco com peso 23, mas na média ele dá 105. Enfim, agradeço mesmo assim, pois nunca havia usado Macros antes e agora posso estudar esse exemplo que tu me passou e inclusive tentar entender o motivo desses cálculos que não batem. Muito obrigado pela ajuda!
Por HansRB 19 Set 2020 às 11:37
Membro Novato
Mensagens: 5
Reputação: 0
#59008
Deciog escreveu:HansRB, Bom Dia.

Esta formula funciona para versão 2013 e superior

Para a versão Excel antes de 2019, feche com CTRL + SHIFT + ENTER, são formula matricial

Confere se é desta forma que desejas

Se minha resposta foi útil, clique em obrigado é uma forma de agradecimento da ajuda

Decio


Bom dia, Decio! Era exatamente isso que eu precisava! Deu certinho, a única situação, como comentei para o colega mucascosta, é que eventualmente poderá ocorrer uma divisão por zero uma vez que podem haver controles que não sofram nenhum dos riscos relacionados. Há como colocar mais um condicional na fórmula no caso da soma da coluna C der zero? Por exemplo, se a autosoma da coluna C der zero, então na coluna B apenas aparecer o valor zero ou mesmo não aparecer nada? Muito obrigado pela ajuda e pelo tempo de vocês!
Avatar do usuário
Por Deciog 19 Set 2020 às 12:25
Membro 5 Estrelas
Mensagens: 1547
Reputação: 823
#59010
HansRB, Boa tarde

è só usar desta forma a formula

=SEERRO(SOMARPRODUTO(TRANSPOR(SE($D3:$CE3=1;TRANSPOR(SEERRO(SOMASE(INDIRETO("'R-"&TEXTO(LIN($A$1:$A$80);"000")&"'!A1");">0";INDIRETO("'R-"&TEXTO(LIN($A$1:$A$80);"000")&"'!A1"));0));0)))/C3;0)

Se minha resposta foi útil, clique em obrigado é uma forma de agradecimento da ajuda

Decio
Por HansRB 19 Set 2020 às 13:01
Membro Novato
Mensagens: 5
Reputação: 0
#59011
Deciog escreveu:HansRB, Boa tarde

è só usar desta forma a formula

=SEERRO(SOMARPRODUTO(TRANSPOR(SE($D3:$CE3=1;TRANSPOR(SEERRO(SOMASE(INDIRETO("'R-"&TEXTO(LIN($A$1:$A$80);"000")&"'!A1");">0";INDIRETO("'R-"&TEXTO(LIN($A$1:$A$80);"000")&"'!A1"));0));0)))/C3;0)

Se minha resposta foi útil, clique em obrigado é uma forma de agradecimento da ajuda

Decio


Boa tarde!

Era isso mesmo!, Está resolvido, muitíssimo obrigado! ;)