▷ ELECTRE I no Excel

Olá seja bem vindo ao meu mais novo artigo

Os primeiros métodos da Escola Francesa de Apoio Multicritério à Decisão foram os da família ELECTRE (Elimination Et Choix Traduisant la Réalité - Eliminação e Escolha como Expressão da Realidade), sendo o ELECTRE I, proposto por Roy em 1968.

Família ELECTRE, alguns dos modelos;

  • ELECTRE I | Roy (1968) | Tipo de Problema: Seleção | Utiliza Pesos: Sim
  • ELECTRE II | Roy e Bertier (1973) | Tipo de Problema: Ordenação | Utiliza Pesos: Sim
  • ELECTRE III | Roy (1978) | Tipo de Problema: Ordenação | Utiliza Pesos: Sim
  • ELECTRE IV | Roy e Hugonnard (1982) | Tipo de Problema: Ordenação | Utiliza Pesos: Não
  • ELECTRE IS | Roy e Skalka (1985) | Tipo de Problema: Seleção | Utiliza Pesos: Sim
  • ELECTRE TRI | Yu Wei (1992) | Tipo de Problema: Classificação | Utiliza Pesos: Sim

Estes métodos ELECTRE admitem um modelo flexível para solução do problema, pois consiste em uma análise de relações de dominâncias.

Serão utilizados dois índices: O índice de concordância, que mede a vantagem relativa de cada alternativa sobre as outras, e o índice de discordância, que mede a desvantagem relativa.

Mas gostaria de agradecer ao Prof. Dr. Dalessandro Soares Vianna, por disponibilizar um material passo a passo.

O modelo proposto no MS Excel poderá ter 5 critérios e até 7 alternativas.

Mas vamos ao problema.

Necessitamos encontrar a melhor alternativa de investimento de um parque recreativo.

Critérios:

Obtenção da maior lucratividade | Lucro (Max)

Aumento de novos postos de trabalho | Emprego (Max)

Impacto mínimo ambiental, na geração de resíduos | Resíduos (Min)

Aumento do número de visitantes | Turismo (Max)

Após estas definições, é necessário definir o peso para cada critério, definido na linha 5 acima

Vamos aos cálculos

Para calcular a diferença entre o Mínimo e o Máximo dos valores por critério,

Diferença entre o Máximo e Mínimo

segue a fórmula iniciando na célula F16

=SE(F5="";"";MÁXIMO(F8:F14)-MÍNIMO(F8:F14))

ou em inglês

=IF(F5="";"";MAX(F8:F14)-MIN(F8:F14))

Após os dados iniciais, será necessário construir três matrizes, a matriz de concordância, discordância e a de superação.

Matriz de Concordância

Segue a fórmula iniciando na célula F20

=SEERRO(SE(OU(LINS($E$20:$E20)=COLS($F$19:F$19);LINS($E$20:$E20)>$C$4
;COLS($F$19:F$19)>$C$4);0;SOMA(ÍNDICE(DESLOC($F$5;0;0;1;$C$3)*
((DESLOC($E$8;CORRESP($E20;DESLOC($E$8;0;0;$C$4;1);0)-1;1;1;$C$3)
>=DESLOC($E$8;CORRESP(F$19;DESLOC($E$8;0;0;$C$4;1);0)-1;1;1;$C$3))*
(DESLOC($F$2;0;0;1;$C$3)="Max")+(ESLOC($E$8;CORRESP($E20;DESLOC(
$E$8;0;0;$C$4;1);0)-1;1;1;$C$3)<=DESLOC($E$8;CORRESP(F$19;DESLOC(
$E$8;0;0;$C$4;1);0)-1;1;1;$C$3))*(DESLOC($F$2;0;0;1;$C$3)="Min"))
;0)));0)

ou em inglês

=IFERROR(IF(OR(ROWS($E$20:$E20)=COLUMNS($F$19:F$19);ROWS($E$20:$E20)
>$C$4;COLUMNS($F$19:F$19)>$C$4);0;SUM(INDEX(OFFSET($F$5;0;0;1;$C$3)*
((OFFSET($E$8;MATCH($E20;OFFSET($E$8;0;0;$C$4;1);0)-1;1;1;$C$3)
>=OFFSET($E$8;MATCH(F$19;OFFSET($E$8;0;0;$C$4;1);0)-1;1;1;$C$3))*
(OFFSET($F$2;0;0;1;$C$3)="Max")+(OFFSET($E$8;MATCH($E20;OFFSET(
$E$8;0;0;$C$4;1);0)-1;1;1;$C$3)<=OFFSET($E$8;MATCH(F$19;OFFSET(
$E$8;0;0;$C$4;1);0)-1;1;1;$C$3))*(OFFSET($F$2;0;0;1;$C$3)="Min"))
;0)));0)

Após com a matriz criada é necessário o cálculo do p' e depois o p (similaridade  de comparação)

Cálculo do p'

Segue a fórmula na célula H18

=SOMA(F20:L26)/($C$4*($C$4-1))

ou em inglês

=SUM(F20:L26)/($C$4*($C$4-1))

Cálculo do p

p é a similaridade de preferência e se define como o maior ou igual o valor acima de p'. Segue a fórmula na célula J18

=MAIOR($F$20:$L$26;CONT.SE($F$20:$L$26;">="&H18))

ou em inglês

=LARGE($F$20:$L$26;COUNTIF($F$20:$L$26;">="&H18))

Após a criação da Matriz de Concordância e seus parâmetros p' e p, é necessário a criação da Matriz de Discordância

Matriz de Discordância

Segue a fórmula iniciano na célula F30

=SEERRO(SE(OU(LINS($E$30:$E30)=COLS($F$29:F$29);LINS($E$30:$E30)
>$C$4;COLS($F$29:F$29)>$C$4);0;AGREGAR(14;6;(((DESLOC($E$8;CORRESP(
F$29;DESLOC($E$8;0;0;$C$4;1);0)-1;1;1;$C$3)-DESLOC($E$8;CORRESP(
$E30;DESLOC($E$8;0;0;$C$4;1);0)-1;1;1;$C$3))/DESLOC($F$16;0;0;1;$C$3))
*(DESLOC($F$2;0;0;1;$C$3)="Max")+((DESLOC($E$8;CORRESP($E30;DESLOC(
$E$8;0;0;$C$4;1);0)-1;1;1;$C$3)-DESLOC($E$8;CORRESP(F$29;DESLOC(
$E$8;0;0;$C$4;1);0)-1;1;1;$C$3))/DESLOC($F$16;0;0;1;$C$3))*(DESLOC(
$F$2;0;0;1;$C$3)="Min"));1));0)

ou em inglês

=IFERROR(IF(OR(ROWS($E$30:$E30)=COLUMNS($F$29:F$29);ROWS($E$30:$E30)
>$C$4;COLUMNS($F$29:F$29)>$C$4);0;AGGREGATE(14;6;(((OFFSET($E$8;MATCH(
F$29;OFFSET($E$8;0;0;$C$4;1);0)-1;1;1;$C$3)-OFFSET($E$8;MATCH(
$E30;OFFSET($E$8;0;0;$C$4;1);0)-1;1;1;$C$3))/OFFSET(
$F$16;0;0;1;$C$3))*(OFFSET($F$2;0;0;1;$C$3)="Max")+((OFFSET(
$E$8;MATCH($E30;OFFSET($E$8;0;0;$C$4;1);0)-1;1;1;$C$3)-OFFSET($E$8;
MATCH(F$29;OFFSET($E$8;0;0;$C$4;1);0)-1;1;1;$C$3))/OFFSET(
$F$16;0;0;1;$C$3))*(OFFSET($F$2;0;0;1;$C$3)="Min"));1));0)

Após com a matriz criada é necessário o cálculo do q' e depois o q (similaridade  de comparação)

Cálculo do q'

Segue a fórmula na célula H28

=SOMASE(F30:L36;">0";F30:L36)/($C$4*($C$4-1))

ou em inglês

=SUMIF(F30:L36;">0";F30:L36)/($C$4*($C$4-1))

Cálculo do q

q é a similaridade de indiferença e se define como o menor ou igual o valor abaixo de q'. Segue a fórmula na célula J28

=MAIOR($F$30:$L$36;CONT.SE($F$30:$L$36;">"&H28)+1)

ou em inglês

=LARGE($F$30:$L$36;COUNTIF($F$30:$L$36;">"&H28)+1)

Após a criação da Matriz de Discordância e seus parâmetros q' e q, é necessário a criação da Matriz de Superação

Matriz de Superação

Segue a fórmula na célula F40

=SE(LINS($E$30:$E30)=COLS($F$29:F$29);0;SE(F30<=$J$28;1;0))*
SE(LINS(E$20:$E20)=COLS($F$19:F$19);0;SE(F20>=$J$18;1;0))

ou em inglês

=IF(ROWS($E$30:$E30)=COLUMNS($F$29:F$29);0;IF(F30<=$J$28;1;0))*
IF(ROWS(E$20:$E20)=COLUMNS($F$19:F$19);0;IF(F20>=$J$18;1;0))

Cálculos Finais

Após todas as matrizes criadas é necessário verificar os índices que recebem e que enviam as superações, realizar a diferença e após criar um ranking, sendo o 1° do Ranking será a escolha.

Índice de Recebimento

Segue a fórmula iniciando na célula F48

=SOMA(F40:F46)

ou em inglês

=SUM(F40:F46)

Índice de Envio

Segue a fórmula da coluna N, iniciando na célula N40

=SOMA(F40:L40)

ou em inglês

=SUM(F40:L40)

Será necessário transpor os valores de recebimento que segue a fórmula na coluna O.

=DESLOC($E$48;0;LINS($O$40:O40))

ou em inglês

=OFFSET($E$48;0;ROWS($O$40:O40))

Diferença

Para o cálculo da Diferença será necessário somar o Envio menos o Recebimento, segue a fórmula na coluna P, iniciando na célula P40

=SE(LINS($P$40:P40)<=$C$4;N40-O40;"")

ou em inglês

=IF(ROWS($P$40:P40)<=$C$4;N40-O40;"")

Ranking

Para definir a melhor escolha, deverá obter o número com a maior diferença, caso houver empate na diferença, o 2° critério será quem tem o índice de recebimento maior. Segue a fórmula na coluna Q iniciando na célula Q40.

=SEERRO(SOMA(ÍNDICE((((P40+1000)*10000)+((N40+10)*10)<=ÍNDICE(((
DESLOC($P$40;0;0;$C$4;1)+1000)*10000)+((DESLOC($N$40;0;0;$C$4;1)
+10)*10);0))/CONT.SES(DESLOC($P$40;0;0;$C$4;1);DESLOC(
$P$40;0;0;$C$4;1);DESLOC($N$40;0;0;$C$4;1);DESLOC($N$40;0;0;$C$4;1)
);0));"")

ou em inglês

=IFERROR(SUM(INDEX((((P40+1000)*10000)+((N40+10)*10)<=INDEX(((
OFFSET($P$40;0;0;$C$4;1)+1000)*10000)+((OFFSET($N$40;0;0;$C$4;1)
+10)*10);0))/COUNTIFS(OFFSET($P$40;0;0;$C$4;1);OFFSET(
$P$40;0;0;$C$4;1);OFFSET($N$40;0;0;$C$4;1);OFFSET($N$40;0;0;$C$4;1)
);0));"")

Obrigado Mestre Fabio Gatti pela indicação da solução elegante nesta parte. A solução que eu tinha encontrado segue abaixo

=CORRESP(ÍNDICE(--(ORDEM($P$40:$P$43;$P$40:$P$43)=1)*(1000-$N$40:$N$43)
/1000+ORDEM($P$40:$P$43;$P$40:$P$43);LINS($Q$51:Q51));ÍNDICE(AGREGAR(
15;6;ÍNDICE(--(ORDEM($P$40:$P$43;$P$40:$P$43)=1)*(1000-$N$40:$N$43)
/1000+ORDEM($P$40:$P$43;$P$40:$P$43);0);LIN(INDIRETO("1:4")));0);0)

ou em inglês

=MATCH(INDEX(--(RANK($P$40:$P$43;$P$40:$P$43)=1)*(1000-$N$40:$N$43)
/1000+RANK($P$40:$P$43;$P$40:$P$43);ROWS($Q$51:Q51));INDEX(AGGREGATE(
15;6;INDEX(--(RANK($P$40:$P$43;$P$40:$P$43)=1)*(1000-$N$40:$N$43)
/1000+RANK($P$40:$P$43;$P$40:$P$43);0);ROW(INDIRECT("1:4")));0);0)

e por fim, resultar a escolha

Escolha

Segue a fórmula na coluna R, iniciando na célula R40

=SE(Q40=1;E40;"")

ou em inglês

=IF(Q40=1;E40;"")

Segue o Modelo Multicritério ELECTRE I no Excel de maneira dinâmica

Animated GIF - Find & Share on GIPHY

Espero que tenha gostado desse artigo!

Caso tenha alguma sugestão de outra técnica ou melhoria do conteúdo acima, poste aqui nos comentários.

Se gostou, curta e compartilhe este artigo para que todos saibam o que é possível fazer dentro do MS Excel.

E você já conhece o meu novo Curso de Excel EAD?

Abraços a todos e até o próximo artigo!

Fabio BALDINI

Frase do Dia: "O Universo não te dá quem você quer, ele te dá quem você precisa, para te ajudar, te machuhcar, te deixar, te amar e te transformar na pessoa que você deve ser." Autor: Desconhecido

2 Comentários


Deixe um comentário

O seu endereço de e-mail não será publicado.