▷ AHP no MS Excel – Analytic Hierarchy Process Ferramenta de Análise de Projetos

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

https://en.wikipedia.org/wiki/Analytic_hierarchy_process

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!!!!