Olá Pessoal seja bem vindo ao meu novo artigo! Hoje irei comentar neste artigo esta poderosa técnica que aprendi quando estava fazendo o Mestrado com o meu amigo e Prof. Msc. Eng. Fábio José Ricardo, onde eu estava estudando o Fuzzy e ele o AHP.
Esta técnica irá te ajudar na tomada de decisão, pois quando temos somente um parâmetro fica muito difícil avaliar se o parâmetro maior ou menor, será a melhor escolha para a tomada de decisão. Visto essa dificuldade essa técnica veio fomentar e subsidiar a tomada de decisão ficando muito mais assertiva e direcionada com estratégias que iremos descrever passo a passo aqui abaixo.
Primeiramente segue o artigo do AHP no Wikipédia
Mas vamos ao exemplo prático desta técnica. No exemplo abaixo, temos quatro projetos de diferentes valores:
Qual destes projetos você escolheria para a sua companhia? O que muitos fazem é escolher o projeto de maior valor, sem considerar nenhum critério adicional.
A aplicação desta técnica AHP - Analytic Hierarchy Process, devemos adicionar mais 2 critérios a nossa análise, são eles:
Critério 1 - Estratégico, Critério 2 - Financeiro (os valores informados inicialmente) e Critério 3 - Qualidade.
Para isso, o autor recomenda utilizar a escala de Saaty, onde os valores vão de 1 a 9.
Com esta escala, teremos que converter os valores informados acima nessa escala.
Vamos as fórmulas
Célula D35 - Maior Valor do Financeiro
=MÁXIMO(D30:D33)
ou em inglês
=MAX(D30:D33)
Célula D36 - Menor Valor do Financeiro
=MÍNIMO(D30:D33)
ou em inglês
=MIN(D30:D33)
Célula E30 a E33 - Conversão para interpolar para a escala de Saaty
=((D30-$D$36)/($D$35-$D$36))*($E$35-$E$36)+$E$36
Célula G30 a G33 - Ordenado do maior para o menor
=MAIOR($E$30:$E$33;LINS($E$30:E30))
ou em inglês
=LARGE($E$30:$E$33;ROWS($E$30:E30))
Célula I30 a I33 - Trazendo os nomes dos Projetos ordenados
=ÍNDICE($C$30:$C$33;CORRESP(G30;$E$30:$E$33;0);0)
ou em inglês
=INDEX($C$30:$C$33;MATCH(G30;$E$30:$E$33;0);0)
Com estes critérios, teremos que criar uma Matriz de Comparação de Critérios onde deverá informar valores entre 1 a 9 de graus de importância, utilizando a escala de Saaty.
Após inserido o peso do Critério Estratégico para o Critério Financeiro, que neste exemplo tem o peso 3, informado na célula D49, temos que realizar o inverso dele Critério Financeiro para o Critério Estratégico, onde teremos que colocar o valor inverso do digitado, sendo na célula C50 o valor de 1/3, para isso é utilizado a seguinte fórmula que segue abaixo nas células C50, C51, D51
=1/DESLOC($B$48;CORRESP(C$48;$B$49:$B$51;0);CORRESP($B50;$C$48:$E$48;0))
ou em inglês
=1/OFFSET($B$48;MATCH(C$48;$B$49:$B$51;0);MATCH($B50;$C$48:$E$48;0))
E nas células C52 a E52, é realizado uma soma que segue abaixo
=SOMA(C49:C51)
Com essa matriz finalizada é necessário normalizar seus dados (deixar toda ela na base 1 ou 100%),
Segue as fórmulas nas células L49 a L51
=C49/SOMA(C$49:C$51)
ou em inglês
=C49/SUM(C$49:C$51)
Segue as fórmulas nas células M49 a M51
=D49/SOMA(D$49:D$51)
ou em inglês
=D49/SUM(D$49:D$51)
Segue as fórmulas nas células N49 a N51
=E49/SOMA(E$49:E$51)
ou em inglês
=E49/SUM(E$49:E$51)
Após criada a Matriz Normalizada é necessário criar o Vetor de Critério, onde este vetor será necessário na última parte dessa técnica, onde se utiliza da média dos valores normalizados, segue a fórmula na célula Q49 a Q52
=MÉDIA(L49:N49)
ou em inglês
=AVERAGE(L49:N49)
Quando se utiliza essa técnica é necessário que no mínimo um critério seja "Qualitativo", onde os dados terão que ser inseridos por "sentimento", ou painel de especialistas com opiniões.
Após calculado a Matriz de Comparação de Critérios, teremos que calcular a "Taxa de Consistência" dos nossos dados para ver se será aceito ou não os pesos inseridos entre os critérios, pois se mais de um critério tiver muita importância e com pesos muitos altos ou muitos baixos o mesmo irá alertar, invalidando a técnica, resolvendo esse problema é só retirar um critério para que a Taxa de Consistência seja válido e com isso prosseguir com os cálculos. Mas antes vamos mostrar o cálculo passo a passo dos índices intermediários como o Lambda, Índice de Consistência Médio, Índice de Consistência e depois a Taxa de Consistência.
Vamos aos cálculos
Célula E61 - Cálculo do Lambda
=MMULT(C52:E52;Q49:Q51)
ou em inglês
=MMULT(C52:E52;Q49:Q51)
Célula H61 - Cálculo do Índice de Consistência Médio
=SE(E61<1;0;ÍNDICE(B59:P59;0;CORRESP(E61;$B$58:$P$58;1)))
ou em inglês
=IF(E61<1;0;INDEX(B59:P59;0;MATCH(E61;$B$58:$P$58;1)))
Célula E63 - Número de Critérios do modelo
=CONT.VALORES(E13:E15)
ou em inglês
=COUNTA(E13:E15)
Célula H63 - Índice de Consistência
=(E61-E63)/(E63-1)
Célula H65 - Taxa de Consistência
=H63/H61
Onde a Taxa de Consistência ficando abaixo ou igual a 10% é considerado "ótimo", se a taxa ficar entre 10% a 20% será considerado "aceitável", caso seja maior o valor da taxa, será rejeitado e não existirá viés no processo dos dados. Mas no exemplo demonstrado a Taxa de Consistência foi de 4,773% considerado Ótimo para o nosso modelo.
Com essa validação, podemos prosseguir para as demais etapas, onde iremos detalhar agora as matrizes de comparações das alternativas em relação a todos os critérios.
1° Critério - Estratégico
Informar o quanto que um projeto será mais estratégico do que o outro. Lembre-se que sempre terá que utilizar a escala de Saaty, que é entre 1 a 9. Este critério é qualitativo.
Deverá ser aplicado a fórmula do peso inverno na matriz inferior, que segue nas células C76, C77, D77, C78, D78 e E78
=1/DESLOC($B$74;CORRESP(C$74;$B$75:$B$78;0);CORRESP($B76;$C$74:$F$74;0))
ou em inglês
=1/OFFSET($B$74;MATCH(C$74;$B$75:$B$78;0);MATCH($B76;$C$74:$F$74;0))
E também é preciso normalizar os dados que segue a formula abaixo:
Célula L75 a L78 e o mesmo deverá ser feito para as outras colunas
=C75/SOMA(C$75:C$78)
ou em inglês
=C75/SUM(C$75:C$78)
Onde o Vetor Estratégico final será a Média da Matriz Normalizada, que segue a fórmula na célula Q75 a Q78
=MÉDIA(L75:O75)
ou em inglês
=AVERAGE(L75:O75)
O mesmo procedimento teremos que fazer para os Critérios Financeiro e Critério de Qualidade,
Para o Critério Financeiro, como já informamos os valores, podemos já atribuir os valores realizando a divisão de um sobre o outro.
2° Critério - Financeiro
Segue abaixo os valores do Critério Financeiro, sendo esse quantitativo.
A estratégia é a mesma sobre o Critério Estratégico, segue as fórmulas nas células D86, E86, F86, E87, F87 e F88
=ÍNDICE($E$30:$E$33;CORRESP($A86;$C$30:$C$33;0);0)/ÍNDICE($E$30:$E$33;CORRESP(D$84;$C$30:$C$33;0);0)
ou em inglês
=INDEX($E$30:$E$33;MATCH($A86;$C$30:$C$33;0);0)/INDEX($E$30:$E$33;MATCH(D$84;$C$30:$C$33;0);0)
3° Critério - Qualidade
A mesma estratégia deverá ser realizada para a Matriz de Comparação de Qualidade
Após gerado todos os vetores, será necessário realizar o cálculo Final
Onde devemos utilizar os dados da nossa primeira Matriz de Comparação entre os Critérios e somar multiplicando com as Matrizes dos Critérios,
Segue a fórmula na célula M114 a M117
=H109*$B$115+H115*$B$116+H121*$B$117
Ordenando do maior valor para o menor valor, segue a fórmula na célula O114 a O118
=MAIOR($M$114:$M$117;LINS($M$114:M114))
ou em inglês
=LARGE($M$114:$M$117;ROWS($M$114:M114))
E trazendo os nomes dos projetos nas células P114 a P118
=ÍNDICE($L$114:$L$117;CORRESP(O114;$M$114:$M$117;0);0)
ou em inglês
=INDEX($L$114:$L$117;MATCH(O114;$M$114:$M$117;0);0)
E segue a analise final comparatória
O grande interessante desta técnica é que quando avaliamos com outros critérios para a nossa tomada de decisão, a primeira opção não é o projeto de maior valor que neste exemplo seria o Projeto B, mas sim o Projeto C, devido a aplicação da técnica AHP, onde colocamos os critérios de Estratégico e Qualidade a nossa análise e deste reduzimos significativamente o risco.
Espero que tenham gostado desse artigo! Desculpe se foi longo, mas a aplicação dele achei sensacional quando eu comecei a estudar ele, onde, o meu amigo Prof. Msc. Eng. Fabio José Ricardo me apresentou.
Caso tenha alguma sugestão de melhorias, poste aqui nos comentários.
Se gostou, curta e compartilhem para que todos saibam o que é possível fazer dentro do Excel.
E ja conhece o meu novo curso online de Excel?
Abraços a todos e até o próximo artigo!
Fabio BALDINI
Frase do Dia: "Se você encontrar um caminho sem obstáculos, ele provavelmente não leva a lugar nenhum". Autor: Frank Clark
Este artigo é uma homenagem ao meu grande amigo e Mestre Alessandro Trovato!!! Parabéns pelos mais de 100.000 inscritos em seu Canal!!!!
Link permanente
ótimo conteudo. Se você disponibilizou o material por favor eu gostaria de ter acesso.
Link permanente
Olá Rafael tudo bom, estarei fazendo um grupo mas para frente onde estarão disponíveis todos os modelos aqui apresentados, mas avisarei sim, Abraços
Link permanente
Prezado Fabio! Excelente seu artigo explicando como construir a planilha AHP. Porém com não sou Jedi em Excel ainda, gostaria de saber se pode me enviar um exemplo para eu aprender melhor. É possível?
Grato desde já
Link permanente
Olá Rafael tudo bom, estarei fazendo um grupo mas para frente onde estarão disponíveis todos os modelos aqui apresentados, mas te avisarei sim, Abraços
Link permanente
Caro Fabio
parabéns pelo trabalho e pela persistência, contudo não encontrei os valores das células B59:P59, poderia esclarecer esse passo?
Muito obrigado
Lino
Link permanente
Olá Lino, esses números são os valores calculados do Saaty, onde tem um estudo mais aprofundado de onde ele tirou esses valores que não foram aleatórios. Abraços Prof. Baldini
Link permanente
Boa noite, estou fazendo TCC e vou utilizar este método.
Como faço para ter acesso a planilha pronta?
Não sou expert em excel então através de pesquisas cheguei até vc.
Parabéns pelo artigo.
Link permanente
Olá Gilson tudo bom, não conseguiu reproduzir o artigo? Caso não enviei por email. Abraços Prof. Baldini