Olá Pessoal seja bem vindo ao meu novo artigo! Após realizar o artigo anterior sobre o ANOVA - Um fator, neste artigo será demonstrado uma aplicação do ANOVA com dois fatores com repetição. Será utilizado o seguinte exemplo: 4 fornecedores (Fornecedor 1 ao 4) e 4 tipos de materiais (Aço Carbono 1020, 1045, 1090 e o Aço Inox 316).
A questão é, será que existe alguma variação significativa na entrega dos materiais? Caso houver, em qual fornecedor e em qual material? Vamos a base dos dados, que segue abaixo:
Com esses dados disponibilizados deverá ser convertido em outra disposição, que segue abaixo:
Neste momento foi criado duas colunas auxiliares para retornar os valores não duplicados que estão na coluna O e P, onde irá auxiliar na construção da tabela da Célula E1 até a célula L36, segue suas fórmulas abaixo:
Fornecedor (Células O2 a O23)
=SE(SEERRO(ÍNDICE($A$2:$A$40; CORRESP(0; ÍNDICE(CONT.SE($O$1:O1;$A$2:$A$40);0;0);0));"")=0;"";SEERRO(ÍNDICE($A$2:$A$40; CORRESP(0; ÍNDICE(CONT.SE($O$1:O1;$A$2:$A$40);0;0);0));""))ou em inglês
=IF(IFERROR(INDEX($A$2:$A$40; MATCH(0; INDEX(COUNTIF($O$1:O1;$A$2:$A$40);0;0);0));"")=0;"";IFERROR(INDEX($A$2:$A$40; MATCH(0; INDEX(COUNTIF($O$1:O1;$A$2:$A$40);0;0);0));""))Material (Células P2 a P23)
=SE(SEERRO(ÍNDICE($B$2:$B$40; CORRESP(0; ÍNDICE(CONT.SE($P$1:P1;$B$2:$B$40);0;0);0));"")=0;"";SEERRO(ÍNDICE($B$2:$B$40; CORRESP(0; ÍNDICE(CONT.SE($P$1:P1;$B$2:$B$40);0;0);0));""))ou em inglês
=IF(IFERROR(INDEX($B$2:$B$40; MATCH(0; INDEX(COUNTIF($P$1:P1;$B$2:$B$40);0;0);0));"")=0;"";IFERROR(INDEX($B$2:$B$40; MATCH(0; INDEX(COUNTIF($P$1:P1;$B$2:$B$40);0;0);0));""))Necessitaremos da quantidade combinada entre o Fornecedor e o Material, neste caso tivemos 3 amostras por fornecedor e material, que segue a fórmula da célula N1
=MÁXIMO(ÍNDICE(CONT.SES($A:$A;DESLOC($O$1;1;0;CONT.SE(O:O;">=a")-1;1);$B:$B;DESLOC($P$1;1;0;CONT.SE(P:P;">=a")-1;1));0))ou em inglês
=MAX(INDEX(COUNTIFS($A:$A;OFFSET($O$1;1;0;COUNTIF(O:O;">=a")-1;1);$B:$B;OFFSET($P$1;1;0;COUNTIF(P:P;">=a")-1;1));0))Com esses dados definidos agora podemos converter os dados na tabela demonstrada, que segue abaixo
Fornecedores com duplicação - Célula E2 a E29
=SE(LINS($E$2:E2)=1;DESLOC($O$1;1;0);SE(CONT.SE($E$1:E1;E1)<$N$1;E1;ÍNDICE(O:O;CORRESP(E1;O:O;0)+1;0)))ou em inglês
=IF(ROWS($E$2:E2)=1;OFFSET($O$1;1;0);IF(COUNTIF($E$1:E1;E1)<$N$1;E1;INDEX(O:O;MATCH(E1;O:O;0)+1;0)))Material sem duplicação - Célula F1 a L1
=SE(DESLOC($P$1;COLS($F$1:F1);0)="";"";DESLOC($P$1;COLS($F$1:F1);0))ou em inglês
=IF(OFFSET($P$1;COLUMNS($F$1:F1);0)="";"";OFFSET($P$1;COLUMNS($F$1:F1);0))Dados ajustados entre os fornecedores e materiais - Célula F2 a L29
=SE(OU(F$1="";$E2="";MAIOR(ÍNDICE(($A$1:$A$100=$E2)*($B$1:$B$100=F$1)*LIN($C$1:$C$100);0);CONT.SE($E$2:$E2;$E2))=0);"";ÍNDICE($C:$C;MAIOR(ÍNDICE(($A$1:$A$100=$E2)*($B$1:$B$100=F$1)*LIN($C$1:$C$100);0);CONT.SE($E$2:$E2;$E2));0))ou em inglês
=IF(OR(F$1="";$E2="";LARGE(INDEX(($A$1:$A$100=$E2)*($B$1:$B$100=F$1)*ROW($C$1:$C$100);0);COUNTIF($E$2:$E2;$E2))=0);"";INDEX($C:$C;LARGE(INDEX(($A$1:$A$100=$E2)*($B$1:$B$100=F$1)*ROW($C$1:$C$100);0);COUNTIF($E$2:$E2;$E2));0))Com os dados já estruturados, vamos aos cálculos necessários para a identificação das diferenças ou não entre os fornecedores e materiais, que segue abaixo:
1° Parte - Análises Iniciais e Cálculo da Soma Quadrática
Obtendo os dados de maneira transposta - Célula AD3 até a AI3
=DESLOC($P$1;COLS($T$3:T3);0) ou em inglês
=OFFSET($P$1;COLUMNS($T$3:T3);0) Contagem dos Produtos - Célula AD4 até AI4
=SE(AD3="";"";CONT.NÚM(F:F)) ou em inglês
=IF(AD3="";"";COUNT(F:F))Soma dos Produtos - Célula AD5 até AI5
=SE(AD3="";"";SOMA(F:F))ou em inglês
=IF(AD3="";"";SUM(F:F))Média dos Produtos - Célula AD6 até AI6
=SE(AD3="";"";MÉDIA(F:F))ou em inglês
=IF(AD3="";"";AVERAGE(F:F))Variância dos Produtos - Célula AD7 até AI7
=SE(AD3="";"";VAR.A(F:F)) ou em inglês
=IF(AD3="";"";VAR.S(F:F))Será necessário calcular o SSW (Sum of Squares Within Groups) entre os Fornecedores e Materiais, segue os cálculos abaixo:
SSW do Grupo dos Fornecedores - Célula AD11 até AD16
=SE(AC11="";"";DEVSQ(DESLOC($E$1;CORRESP($AC11;$E:$E;0)-1;1;$N$1;7))) ou em inglês
=IF(AC11="";"";DEVSQ(OFFSET($E$1;MATCH($AC11;$E:$E;0)-1;1;$N$1;7)))Soma dos Fornecedores - Célula AE10
=SOMA(AE11:AE16) ou em inglês
=SUM(AE11:AE16) Soma dos Fornecedores entre os Materiais - Célula AE11 até AE16
=SE(AC11="";"";SOMA(AF11:AJ11)) ou em inglês
=IF(AC11="";"";SUM(AF11:AJ11))SSW do Grupo dos Materiais - Célula AF10 até AK10
=SE(AF$9="";"";DEVSQ(DESLOC($A$1;1;CORRESP(AF$9;$1:$1;0)-1;CONT.NÚM($C:$C);1)))ou em inglês
=IF(AF$9="";"";DEVSQ(OFFSET($A$1;1;MATCH(AF$9;$1:$1;0)-1;COUNT($C:$C);1)))SSW entre os Grupos Fornecedores/Materiais - Célula AF11 até AK16
=SE(OU(AF$9="";$AC11="");"";DEVSQ(DESLOC($A$1;CORRESP($AC11;$E:$E;0)-1;CORRESP(AF$9;$1:$1;0)-1;$N$1;1))) ou em inglês
=IF(OR(AF$9="";$AC11="");"";DEVSQ(OFFSET($A$1;MATCH($AC11;$E:$E;0)-1;MATCH(AF$9;$1:$1;0)-1;$N$1;1))) Com os dados calculados poderemos realizar o ANOVA 2 fatores com repetição, conforme a imagem abaixo
SS - Coluna AD
Amostras - Célula AD24
=$AD$29-AJ24Colunas - Célula AD25
=$AD$29-AJ25Interações - Célula AD26
=AD29-AD27-AD24-AD25Resíduos - Célula AD27
=AE10Total - Célula AD29
=DEVSQ(C:C)Graus de Liberdade (df) - Coluna AE
Amostras - Célula AE24
=CONT.SE(O:O;">=a")-1-1ou em inglês
=COUNTIF(O:O;">=a")-1-1Colunas - Célula AE25
=CONT.SE(P:P;">=a")-1-1ou em inglês
=COUNTIF(P:P;">=a")-1-1Interações - Célula AE26
=((CONT.SE(O:O;">=a")-1)-1)*((CONT.SE(P:P;">=a")-1)-1)ou em inglês
=((COUNTIF(O:O;">=a")-1)-1)*((COUNTIF(P:P;">=a")-1)-1)Resíduos - Célula AE27
=AE29-SOMA(AE24:AE26)ou em inglês
=AE29-SUM(AE24:AE26)Total - Célula AE29
=CONT.NÚM(C:C)-1ou em inglês
=COUNT(C:C)-1MS - Célula AF24 a AF27
=AD24/(AE24)F - Célula AF24 a AF26
=AF24/$AF$27P-Value - Célula AF24 a AF26
=DIST.F.CD(AG24;AE24;$AE$27)ou em inglês
=F.DIST.RT(AG24;AE24;$AE$27)F-crítico
=INV.F.CD($AI$22;AE24;$AE$27)ou em inglês
=F.INV.RT($AI$22;AE24;$AE$27)SSW Amostras - Célula AJ24
=SOMA(AD11:AD16)ou em inglês
=SUM(AD11:AD16)SSW Colunas - Célula AJ25
=SOMA(AF10:AK10)ou em inglês
=SUM(AF10:AK10)Com os dados calculados podemos realizar duas verificações para verificar se os dados apresentados contêm alguma diferença significativa
1° Verificação, Comparação do Cálculo do F com o F Crítico - Células AK24 a AK26
=SE(AG24>AI24;"Há Diferença Significativa!")ou em inglês
=IF(AG24>AI24;"Há Diferença Significativa!")2° Verificação, Comparação do Valor-P com alfa - Células AL24 a AL26
=SE(AH24<$AI$22;"Há diferença Significativa!";"")ou em inglês
=IF(AH24<$AI$22;"Há diferença Significativa!";"")Neste momento constamos que há uma diferença significativa nos dados apresentados e será necessário aplicar o modelo de Tukey
2° Parte - Cálculo de Tukey (1949)
Será necessário os seguintes dados para calcular o Q-Crítico, são eles:
Graus de Liberdade das amostras (k) - Célula AE24 +1 | 4
Graus de Liberdade do Resíduo (df) - Célula AE27 | 32
Alfa - Célula AI22 | 0,05
Com estas três variáveis definidas aplicando na tabela estudentizada (nos próximos artigos irei descrever o código onde irá resultar esse valor sem a necessidade de tabelas), resultou em 3,83306121826171
Com o valor de Q-Crítico definido, deverá ser calculado os DMS (Diferença Mínima Significativa) para a amostra e para o grupo, que segue abaixo
DMS - n (Amostra) - Célula AD34
=AD33*RAIZ(AF27/(AE25+1))ou em inglês
=AD33*SQRT(AF27/(AE25+1))DMS - Group (Grupo) - Célula AD35
=AD33*RAIZ(AF27/(SOMA($AE$24:$AE$26)))ou em inglês
=AD33*SQRT(AF27/(SUM($AE$24:$AE$26)))Para avaliarmos se um grupo possui diferença significativa, segue a fórmula na célula AH34 até a AH35 (Atenção essa fórmula só irá funcionar no Excel 2016 ou superior)
=SE($AE34="";"";ABS(MÉDIASES($C:$C;$A:$A;$AF34)-MÉDIASES($C:$C;$A:$A;$AG34)))ou em inglês
=IF($AE34="";"";ABS(AVERAGEIFS($C:$C;$A:$A;$AF34)-AVERAGEIFS($C:$C;$A:$A;$AG34)))Com os valores, deverá verificar se o valor é maior do que o DMS - Grupo, caso o valor calculado for maior, pode afirmar que existe diferença significativa entre os grupos.
Para avaliarmos se os grupos e os materiais tem diferença significativa, segue a fórmula na célula AI34 até AN45
=SE(OU(AI$33="";$AE34="");"";ABS(MÉDIASES($C:$C;$A:$A;$AF34;$B:$B;AI$33)-MÉDIASES($C:$C;$A:$A;$AG34;$B:$B;AI$33)))ou em inglês
=IF(OR(AI$33="";$AE34="");"";ABS(AVERAGEIFS($C:$C;$A:$A;$AF34;$B:$B;AI$33)-AVERAGEIFS($C:$C;$A:$A;$AG34;$B:$B;AI$33)))Nesta análise deverá verificar se o valor calculado é maior do que o DMS - amostra, caso o valor for maior, podemos afirmar que existe uma diferença significativa entre os fornecedores e com seu respectivo material.
Espero que tenha gostado desse artigo!
Caso tenha alguma sugestão de outra técnica, poste aqui nos comentários.
Se gostou, curta e compartilhe este artigo 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: "Você não pode vencer uma pessoa que nunca desiste" Autor: Babe Ruth
OBS: Quero agradecer imensamente ao Grande Mestre e Amigão Alessandro Trovato pelas eternas revisões dos artigo para que sempre fique esmero. Obrigadão grande Mestre!
