Olá Pessoal, estamos aqui para mais um artigo, que eu posso dizer que tem uma complexidade média para alta, ainda não é os dos mais "complexos", mas vamos a prática,
Esta técnica aprendi com a Prof.a Doutora Denise Tsunoda, em sua disciplina de Mineração de Dados na UFPR no curso de Graduação em Gestão da Informação, primeiramente muito Obrigado Professora pelo conhecimento e adorei a matéria!
Mas vamos ao conceito, esta técnica descreve sobre a combinação entre produtos, neste caso iremos demonstrar a compra de produtos onde foi utilizado coletado as informações de compra de 9 pessoas (vamos supor em um mercado, mas pode ser uma loja, etc.), segue abaixo
Analisando os dados acima, podemos descrever do seguinte modo, a primeira observação o consumidor 1 comprou os produtos 1, 4 e 6, o consumidor 2 comprou o produto 1, 4, 5, 6, 7, 8 e assim sucessivamente,
Após a coleta de todos os dados, precisaremos contar quantas vezes os produtos aparecem nos registros de comprados, segue a formula na Célula C1 até a Célula L1
=SE(C2="";"";CONT.SE(C4:C30;$B$1))ou em inglês
=IF(C2="";"";COUNTIF(C4:C30;$B$1))Precisaremos listar quantos itens serão observados dinamicamente, caso apareça mais um produto com o critério "Sim" deverá mostrar 9 produtos, segue na Célula C2 até a Célula L2
=SE(CONT.SE(C4:C30;$B$1)=0;"";COLS($C$2:C2))ou em inglês
=IF(COUNTIF(C4:C30;$B$1)=0;"";COLUMNS($C$2:C2))Para a quantidade de compradores foi adicionado a fórmula, que segue na Célula B4 até a Célula B30
=SE(CONT.SE(C4:L4;"<>")>0;LINS($B$4:B4);"")ou em inglês
=IF(COUNTIF(C4:L4;"<>")>0;ROWS($B$4:B4);"")Qual é a ligação entre os produtos? Qual é influência na compra de um produto sobre o outro? Nessa técnica que se descobriu a ligação de compra de fraldas com a compra de cerveja, onde é possível aplicar descontos ou até aproximar produtos para que a compra seja potencializada, consequentemente o lucro de uma loja/supermercado.
Visto os dados amostrais acima dos 9 consumidores e 8 produtos, para iniciarmos teremos que definir dois intervalos o 1° o Suporte Mínimo neste caso foi colocado o valor de 0,3 na célula Q2, e a Confiança Mínima que foi estipulado como 0,7 (podemos falar que tem 70% de influência um produto sobre o outro) na célula Q3.
Para obter a quantidade de amostras segue a formula na Q4 que segue abaixo:
=CONT.VALORES(B4:B30)-CONTAR.VAZIO(B4:B30)ou em inglês
=COUNTA(B4:B30)-COUNTBLANK(B4:B30)Deste ponto teremos que calcular o Suporte Individual de cada produto,
Primeiro os listei na Célula O8 até a O17 (fiz até 10 produtos mas é possível fazer para 16.384 que é o limite de colunas do Excel), segue a formula onde será transposto os dados, OBS: não gosto utilizar ativar a função matricial (Control+Shift+Enter - CSE) TRANSPOR,
=DESLOC($B$2;0;LIN(O8)-LIN($O$7))ou em inglês
=OFFSET($B$2;0;ROW(O8)-ROW($O$7))O Cálculo do Suporte individual que está nas células P8 até P17, segue abaixo
=SE(O8="";"";DESLOC($B$1;0;LIN(O8)-LIN($O$7))/$Q$4)ou em inglês
=IF(O8="";"";OFFSET($B$1;0;ROW(O8)-ROW($O$7))/$Q$4)Com estes valores definidos podemos já colocar um critério de seleção de quais produtos serão feitos as combinações, que segue nas células Q8 até a Q17,
=SE(O8="";"";SE(P8>=$Q$2;"Sim";"Não"))ou em inglês
=IF(O8="";"";IF(P8>=$Q$2;"Sim";"Não"))Com os resultados de "Sim" e "Não", é devemos separar os valores "Sim" com a fórmula abaixo, nas células R8 até a R17
=SE(O8="";"";SE(Q8="Sim";O8;""))ou em inglês
=IF(O8="";"";IF(Q8="Sim";O8;""))Pronto! A primeira parte (do cálculo do suporte mínimo) está realizada, vamos para as combinações, neste artigo vou demonstrar as combinações 2x2, 3x3 e 4x4 onde contém complexidades interessantes, mas caso você queira desenvolver com mais combinações fique à vontade para aperfeiçoar este modelo proposto.
Combinação 2x2 - Apriori
Primeiro adaptei a fórmula para utilizar todas as combinações possíveis entre os valores encontrados (não é necessário pois o cálculo da combinação resultará no mesmo resultado que iremos ver a frente), preferi deixar assim para retornar todas as combinações e validar 100% todas as combinações, retirei a formula do site abaixo (recomendo muito a todos pois é incrível o que é feito e demonstrado neste site)
Adicionado o parâmetro K = 2 na Célula Y2 e na Célula Y3, será retornado à quantidade de registros dos "Sim" que segue abaixo
=CONT.SE(Y6:AG6;">0")ou em inglês
=COUNTIF(Y6:AG6;">0")Para isso, trouxe de maneira transposta os nossos valores "Sim" na Célula Y6 até a AJ6 segue a fórmula:
=SEERRO(MENOR($R$8:$R$18;COL()-COL($X$6));"")ou em inglês
=IFERROR(SMALL($R$8:$R$18;COLUMN()-COLUMN($X$6));"")Com todas essas colunas prontas podemos resultar a 1° a coluna de Combinação, iniciando na Célula X8 até a X90
=SEERRO(MENOR(ÍNDICE(SE(SUBSTITUIR(MATRIZ.MULT(10^(1+MOD(INT((LIN(DESLOC($A$1;;;Y$3^Y$2))-1)/(Y$3^(COL(DESLOC($A$1;;;;Y$2))-1)));Y$3));LIN(DESLOC($A$1;;;Y$2))^0);0;"")=REPT(1;Y$2);MATRIZ.MULT(1+MOD(INT((LIN(DESLOC($A$1;;;Y$3^Y$2))-1)/(Y$3^(COL(DESLOC($A$1;;;;Y$2))-1)));Y$3);10^(LIN(DESLOC($A$1;;;Y$2))-1)));0);LIN($A1));"")ou em inglês
=IFERROR(SMALL(INDEX(IF(SUBSTITUTE(MMULT(10^(1+MOD(INT((ROW(OFFSET($A$1;;;Y$3^Y$2))-1)/(Y$3^(COLUMN(OFFSET($A$1;;;;Y$2))-1)));Y$3));ROW(OFFSET($A$1;;;Y$2))^0);0;"")=REPT(1;Y$2);MMULT(1+MOD(INT((ROW(OFFSET($A$1;;;Y$3^Y$2))-1)/(Y$3^(COLUMN(OFFSET($A$1;;;;Y$2))-1)));Y$3);10^(ROW(OFFSET($A$1;;;Y$2))-1)));0);ROW($A1));"")Coluna Y, realizando a concatenação, iniciando na célula Y8 até a Y90
=SE(X8="";"";Z8&","&AA8)em inglês
=IF(X8="";"";Z8&","&AA8)E separando as combinações, 1° combinação na célula Z8 até a Z90
=SEERRO(ÍNDICE($Y$6:$AG$6;0;ESQUERDA(X8;1));"")ou em inglês
=IFERROR(INDEX($Y$6:$AG$6;0;LEFT(X8;1));"")Separação do 2° combinação, nas células AA8 até a AA90
=SEERRO(ÍNDICE($Y$6:$AG$6;0;DIREITA(X8;1));"")ou em inglês
=IFERROR(INDEX($Y$6:$AG$6;0;RIGHT(X8;1));"")A combinação resultará os valores entre combinações de 1 a 6, mas temos que obter os dados coletados do "Sim" no Suporte Individual, para isso devemos fazer as associações corretas entre os números 1, 2, 4, 5, 6 e 8.
Agora teremos que contar a quantidade de vezes que o "Sim" aparece entre combinações do 1° elemento e o 2° elemento juntos, segue a fórmula nas Células AE8 até a AE90
=SE(Z8="";"";CONT.SES(DESLOC($B$1;3;Z8;$Q$4;1);$B$1;DESLOC($B$1;3;AA8;$Q$4;1);$B$1))ou em inglês
=IF(Z8="";"";COUNTIFS(OFFSET($B$1;3;Z8;$Q$4;1);$B$1;OFFSET($B$1;3;AA8;$Q$4;1);$B$1))Com a quantidade já calculada, teremos que dividir estes valores pela quantidade de indivíduos observados (9), que segue nas Células AF8 até a AF90
=SE(AE8="";"";AE8/$Q$4)ou em inglês
=IF(AE8="";"";AE8/$Q$4)Adicionaremos uma coluna com o resultado que se este valor está acima do Suporte Mínimo (0,3) irá mostrar "Sim", se não "Não", segue nas Células AG8 até a AG90
=SE(AF8="";"";SE(AF8>=$Q$2;"Sim";"Não"))ou em inglês
=IF(AF8="";"";IF(AF8>=$Q$2;"Sim";"Não"))Neste ponto, teremos que obter os valores de quantos registros apareceram nas combinações da primeira validação, Ex: se a combinação foi 1,2, iremos pegar quantas vezes o "Sim" apareceu no 1° produto, para isso colocaremos a formula nas Células AH8 até a AH80
=SE(X8="";"";AE8/ÍNDICE($C$1:$L$1;0;CORRESP(Z8;$C$2:$L$2;0)))ou em inglês
=IF(X8="";"";AE8/INDEX($C$1:$L$1;0;MATCH(Z8;$C$2:$L$2;0)))Validando este número com a Confiança Mínima (0,7), que segue a fórmula nas células AI8 até a AI90
=SE(AH8="";"";SE(AH8>=$Q$3;"Sim";"Não"))ou em inglês
=IF(AH8="";"";IF(AH8>=$Q$3;"Sim";"Não"))E para finalizar, só será validado a combinação se o suporte mínimo for maior ou igual ao estipulado (0,3) e a confiança mínima foi maior ou igual ao estipulado (0,7), sendo assim será adicionado o "OK" nesta combinação e os números serão coletados para prosseguir para a próxima combinação de 3x3
Segue as fórmulas nas Colunas AJ8 até a AJ90
=SE(E(AG8="Sim";AI8="Sim");"OK";"")ou em inglês
=IF(AND(AG8="Sim";AI8="Sim");"OK";"")Segue as fórmulas nas Colunas AK8 até a AK90
=SE(AJ8="OK";Z8;"")ou em inglês
=IF(AJ8="OK";Z8;"")Segue as fórmulas nas Colunas AL8 até a AL90
=SE(AJ8="OK";AA8;"")ou em inglês
=IF(AJ8="OK";AA8;"")Para coletar todos os dados validados como "OK" em uma listagem única sem os espaços vazios dentro da listagem (neste exemplo limitei até a linha 2.000), foi realizado a seguinte fórmula nas células T3 até a T90, segue abaixo
=SEERRO(ÍNDICE(Y:Y;MENOR(ÍNDICE(($AJ$1:$AJ$2000="OK")*LIN($AJ$1:$AJ$2000);0);LINS($T$3:T3)+CONT.SE($AJ$1:$AJ$2000;"<>OK"));0);"")ou em inglês
=IFERROR(INDEX(Y:Y;SMALL(INDEX(($AJ$1:$AJ$2000="OK")*ROW($AJ$1:$AJ$2000);0);ROWS($T$3:T3)+COUNTIF($AJ$1:$AJ$2000;"<>OK"));0);"")Com está coluna está finalizada a combinação 2x2 e podemos ir para a combinação 3x3!
Combinação 3x3 - Apriori
Nesta combinação ela terá os mesmos passos da combinação 2x2, mas com algumas modificações, onde teremos que fazer o processo de retorno, EX: combinação entre 1, 2 e 4, teremos então 1-2,4 / 1-4,2 / 2,4-1 / 4,2-1 / 2-1,4 / 2-4,1 / 1,4-2 / 4,1-2 / 4-1,2 / 4-2,1 / 1,2-4 / 2,1-4.
Precisaremos de uma célula auxiliar para trazer o número de combinações existentes para a combinação 3x3, onde foi adicionado na Célula AS3, segue abaixo
=CONT.NÚM(AO:AO)ou em inglês
=COUNT(AO:AO)Nesta combinação, teremos que mudar o k que era 2 (para a combinação 2x2) agora neste caso para k =3 (na combinação 3x3) onde está demonstrado na Célula AP2. Também nesta combinação precisamos contar quantas combinações, onde foi adicionada na Célula AP3, segue abaixo
=CONT.SE(AP6:AX6;">0")ou em inglês
=COUNTIF(AP6:AX6;">0")No mesmo processo da 2x2, precisaremos novamente de todos os dados das combinações, segue as formulas das Linhas, Intervalo da Célula AP5 arrastando até BC5
=SE(Y6="";"";SE(CONT.SE($AK$8:$AL$410;Y6)>=1;Y6;""))ou em inglês
=IF(Y6="";"";IF(COUNTIF($AK$8:$AL$410;Y6)>=1;Y6;""))E do Intervalo dos valores associados, informados na célula AP6 até a BC6
=SEERRO(MENOR($AP$5:$BC$5;COL()-COL($AO$6));"")ou em inglês
=IFERROR(SMALL($AP$5:$BC$5;COLUMN()-COLUMN($AO$6));"")Vamos as fórmulas das Colunas, AN, AO, AP, AQ, AR, AS, AV, AW, AX, AY, AZ, BA, BB, BC e BD, neste modelo foi iniciado todas as fórmulas na linha 8
O processo é similar a combinação 2x2, nesta teremos o incremento de mais uma coluna auxiliar que iremos precisar para a combinação 4x4
Vamos as fórmulas!!!
Coluna AN - Chave necessária para a combinação 4x4
=SE(AP8="";"";AQ8&","&AR8&","&AS8)ou em inglês
=IF(AP8="";"";AQ8&","&AR8&","&AS8)Coluna AO - Combinação 3x3
=SEERRO(MENOR(ÍNDICE(SE(SUBSTITUIR(MATRIZ.MULT(10^(1+MOD(INT((LIN(DESLOC($A$1;;;AP$3^AP$2))-1)/(AP$3^(COL(DESLOC($A$1;;;;AP$2))-1)));AP$3));LIN(DESLOC($A$1;;;AP$2))^0);0;"")=REPT(1;AP$2);MATRIZ.MULT(1+MOD(INT((LIN(DESLOC($A$1;;;AP$3^AP$2))-1)/(AP$3^(COL(DESLOC($A$1;;;;AP$2))-1)));AP$3);10^(LIN(DESLOC($A$1;;;AP$2))-1)));0);LIN($A1));"")ou em inglês
=IFERROR(SMALL(INDEX(IF(SUBSTITUTE(MMULT(10^(1+MOD(INT((ROW(OFFSET($A$1;;;AP$3^AP$2))-1)/(AP$3^(COLUMN(OFFSET($A$1;;;;AP$2))-1)));AP$3));ROW(OFFSET($A$1;;;AP$2))^0);0;"")=REPT(1;AP$2);MMULT(1+MOD(INT((ROW(OFFSET($A$1;;;AP$3^AP$2))-1)/(AP$3^(COLUMN(OFFSET($A$1;;;;AP$2))-1)));AP$3);10^(ROW(OFFSET($A$1;;;AP$2))-1)));0);ROW($A1));"")Coluna AP - Combinações de Ida e Volta 3x3, (não foi descrito o caminho de volta na combinação 2x2, porque já foi mapeado o processo de retorno, Ex: 1-3, 3-1)
=SE(LINS(AP$8:AP8)>AS$3*2;"";SE(LINS(AP$8:AP8)>AS$3;ÍNDICE(AR$8:AR8;LINS(AP$8:AP8)-AS$3;0)&","&ÍNDICE(AS$8:AS8;LINS(AP$8:AP8)-AS$3;0)&"-"&ÍNDICE(AQ$8:AQ8;LINS(AP$8:AP8)-AS$3;0);AQ8&"-"&AR8&","&AS8))ou em inglês
=IF(ROWS(AP$8:AP8)>AS$3*2;"";IF(ROWS(AP$8:AP8)>AS$3;INDEX(AR$8:AR8;ROWS(AP$8:AP8)-AS$3;0)&","&INDEX(AS$8:AS8;ROWS(AP$8:AP8)-AS$3;0)&"-"&INDEX(AQ$8:AQ8;ROWS(AP$8:AP8)-AS$3;0);AQ8&"-"&AR8&","&AS8))Coluna AQ até a Coluna AS - 1° ao 3° elemento da combinação
=SE(LINS(AP$8:AP8)>$AS$3*2;"";SE(LINS(AP$8:AP8)>$AS$3;EXT.TEXTO($AP8;SOMA(AP$7:AQ$7);1);SEERRO(ÍNDICE($AP$6:$AX$6;0;EXT.TEXTO($AO8;AQ$7;1));"")))ou em inglês
=IF(ROWS(AP$8:AP8)>$AS$3*2;"";IF(ROWS(AP$8:AP8)>$AS$3;MID($AP8;SUM(AP$7:AQ$7);1);IFERROR(INDEX($AP$6:$AX$6;0;MID($AO8;AQ$7;1));"")))Coluna AV- Sumarização dos critérios
=SE(AP8="";"";CONT.SES(DESLOC($B$1;3;AQ8;$Q$4;1);$B$1;DESLOC($B$1;3;AR8;$Q$4;1);$B$1;DESLOC($B$1;3;AS8;$Q$4;1);$B$1))ou em inglês
=IF(AP8="";"";COUNTIFS(OFFSET($B$1;3;AQ8;$Q$4;1);$B$1;OFFSET($B$1;3;AR8;$Q$4;1);$B$1;OFFSET($B$1;3;AS8;$Q$4;1);$B$1))Coluna AW - Cálculo do Suporte Mínimo
=SE(AV8="";"";AV8/$Q$4)ou em inglês
=IF(AV8="";"";AV8/$Q$4)Coluna AX - Critério Sim/Não do Suporte Mínimo
=SE(AW8="";"";SE(AW8>=$Q$2;"Sim";"Não"))ou em inglês
=IF(AW8="";"";IF(AW8>=$Q$2;"Sim";"Não"))Coluna AY - Cálculo da Confiança Mínima
=SE(AP8="";"";SEERRO(AV8/SEERRO(ÍNDICE($C$1:$L$1;0;CORRESP(AQ8;$C$2:$L$2;0));ÍNDICE($AE:$AE;CORRESP(ESQUERDA(AP8;AP$2);Y:Y;0);0));0))ou em inglês
=IF(AP8="";"";IFERROR(AV8/IFERROR(INDEX($C$1:$L$1;0;MATCH(AQ8;$C$2:$L$2;0));INDEX($AE:$AE;MATCH(LEFT(AP8;AP$2);Y:Y;0);0));0))Coluna AZ - Critério Sim/Não dá Confiança Mínima
=SE(AY8="";"";SE(AY8>=$Q$3;"Sim";"Não"))ou em inglês
=IF(AY8="";"";IF(AY8>=$Q$3;"Sim";"Não"))Coluna BA - Critério OK para a combinação ser válida
=SE(E(AX8="Sim";AZ8="Sim");"OK";"")ou em inglês
=IF(AND(AX8="Sim";AZ8="Sim");"OK";"")Coluna BB até a Coluna BD - Separação dos elementos válidos
=SE($BA8="OK";AQ8;"")ou em inglês
=IF($BA8="OK";AQ8;"")E com isso podemos trazer todos os resultados da combinação 3x3 na Coluna U, segue a fórmula abaixo iniciando na Célula U3
=SEERRO(ÍNDICE(AP:AP;MENOR(ÍNDICE(($BA$1:$BA$2000="OK")*LIN($BA$1:$BA$2000);0);LINS($U$3:U3)+CONT.SE($BA$1:$BA$2000;"<>OK"));0);"")ou em inglês
=IFERROR(INDEX(AP:AP;SMALL(INDEX(($BA$1:$BA$2000="OK")*ROW($BA$1:$BA$2000);0);ROWS($U$3:U3)+COUNTIF($BA$1:$BA$2000;"<>OK"));0);"")Pronto! A combinação 3x3 está pronta, agora seguiremos para a última combinação 4x4.
Combinação 4x4 - Apriori
Esta combinação 4x4 é bem próxima da combinação 3x3, sendo que teremos que adicionar novas combinações, pois agora teremos combinações 1-2,3,4 ; 4,2,2-1 ; 1,2-3,4 ; 3,4-1,2 como todas as possibilidades entre elas.
Primeiro vamos alterar o k para 4 na célula BH2, e trazer a quantidade de combinações resultantes da 3x3 na Célula BH3, que segue a fórmula abaixo
=CONT.SE(BH6:BP6;">0")ou em inglês
=COUNTIF(BH6:BP6;">0")segue também a fórmula da quantidade de combinações possíveis na Célula BK3
=CONT.NÚM(BG:BG)ou em inglês
=COUNT(BG:BG)Para as fórmulas das Linhas - BH5 até a BU5 - Valores únicos da combinação 3x3
=SE(AP6="";"";SE(CONT.SE($BB$8:$BD$410;AP6)>=1;AP6;""))ou em inglês
=IF(AP6="";"";IF(COUNTIF($BB$8:$BD$410;AP6)>=1;AP6;""))E a fórmula para trazer ordenado da BH6 até a BU6
=SEERRO(MENOR($BH$5:$BU$5;COL()-COL($BG$6));"")ou em inglês
=IFERROR(SMALL($BH$5:$BU$5;COLUMN()-COLUMN($BG$6));"")Aqui pessoal irei adotar a mesma sequência da 3x3 só mostrando as formulas nas Colunas BF, BG, BH, BI, BJ, BK, BL, BM, BN, BO, BP, BQ, BR, BS, BT, BU, BV e BW todas iniciando na linha 8, vamos as fórmulas:
Coluna BF - Chave Necessária para a combinação 5x5
=SE(BH8="";"";BI8&","&BJ8&","&BK8&","&BL8)ou em inglês
=IF(BH8="";"";BI8&","&BJ8&","&BK8&","&BL8)Coluna BG - Combinação 4x4
=SEERRO(MENOR(ÍNDICE(SE(SUBSTITUIR(MATRIZ.MULT(10^(1+MOD(INT((LIN(DESLOC($A$1;;;BH$3^BH$2))-1)/(BH$3^(COL(DESLOC($A$1;;;;BH$2))-1)));BH$3));LIN(DESLOC($A$1;;;BH$2))^0);0;"")=REPT(1;BH$2);MATRIZ.MULT(1+MOD(INT((LIN(DESLOC($A$1;;;BH$3^BH$2))-1)/(BH$3^(COL(DESLOC($A$1;;;;BH$2))-1)));BH$3);10^(LIN(DESLOC($A$1;;;BH$2))-1)));0);LIN($A1));"")ou em inglês
=IFERROR(SMALL(INDEX(IF(SUBSTITUTE(MMULT(10^(1+MOD(INT((ROW(OFFSET($A$1;;;BH$3^BH$2))-1)/(BH$3^(COLUMN(OFFSET($A$1;;;;BH$2))-1)));BH$3));ROW(OFFSET($A$1;;;BH$2))^0);0;"")=REPT(1;BH$2);MMULT(1+MOD(INT((ROW(OFFSET($A$1;;;BH$3^BH$2))-1)/(BH$3^(COLUMN(OFFSET($A$1;;;;BH$2))-1)));BH$3);10^(ROW(OFFSET($A$1;;;BH$2))-1)));0);ROW($A1));"")Coluna BH - Combinações de Ida, Volta e Intermediárias 4x4
=SE(LINS(BH$8:BH8)>BK$3*4;"";SE(LINS(BH$8:BH8)>BK$3*3;ÍNDICE(BK$8:BK8;LINS(BH$8:BH8)-BK$3*3;0)&","&ÍNDICE(BL$8:BL8;LINS(BH$8:BH8)-BK$3*3;0)&"-"&ÍNDICE(BI$8:BI8;LINS(BH$8:BH8)-BK$3*3;0)&","&ÍNDICE(BJ$8:BJ8;LINS(BH$8:BH8)-BK$3*3;0);SE(LINS(BH$8:BH8)>BK$3*2;ÍNDICE(BI$8:BI8;LINS(BH$8:BH8)-BK$3*2;0)&","&ÍNDICE(BJ$8:BJ8;LINS(BH$8:BH8)-BK$3*2;0)&"-"&ÍNDICE(BK$8:BK8;LINS(BH$8:BH8)-BK$3*2;0)&","&ÍNDICE(BL$8:BL8;LINS(BH$8:BH8)-BK$3*2;0);SE(LINS(BH$8:BH8)>BK$3;ÍNDICE(BJ$8:BJ8;LINS(BH$8:BH8)-BK$3;0)&","&ÍNDICE(BK$8:BK8;LINS(BH$8:BH8)-BK$3;0)&","&ÍNDICE(BL$8:BL8;LINS(BH$8:BH8)-BK$3;0)&"-"&ÍNDICEX(BI$8:BI8;LINS(BH$8:BH8)-BK$3;0);BI8&"-"&BJ8&","&BK8&","&BL8))))ou em inglês
=IF(ROWS(BH$8:BH8)>BK$3*4;"";IF(ROWS(BH$8:BH8)>BK$3*3;INDEX(BK$8:BK8;ROWS(BH$8:BH8)-BK$3*3;0)&","&INDEX(BL$8:BL8;ROWS(BH$8:BH8)-BK$3*3;0)&"-"&INDEX(BI$8:BI8;ROWS(BH$8:BH8)-BK$3*3;0)&","&INDEX(BJ$8:BJ8;ROWS(BH$8:BH8)-BK$3*3;0);IF(ROWS(BH$8:BH8)>BK$3*2;INDEX(BI$8:BI8;ROWS(BH$8:BH8)-BK$3*2;0)&","&INDEX(BJ$8:BJ8;ROWS(BH$8:BH8)-BK$3*2;0)&"-"&INDEX(BK$8:BK8;ROWS(BH$8:BH8)-BK$3*2;0)&","&INDEX(BL$8:BL8;ROWS(BH$8:BH8)-BK$3*2;0);IF(ROWS(BH$8:BH8)>BK$3;INDEX(BJ$8:BJ8;ROWS(BH$8:BH8)-BK$3;0)&","&INDEX(BK$8:BK8;ROWS(BH$8:BH8)-BK$3;0)&","&INDEX(BL$8:BL8;ROWS(BH$8:BH8)-BK$3;0)&"-"&INDEX(BI$8:BI8;ROWS(BH$8:BH8)-BK$3;0);BI8&"-"&BJ8&","&BK8&","&BL8))))Coluna BI até a Coluna BL - 1° ao 4° elemento da combinação
=SE(LINS(BH$8:BH8)>$BK$3*4;"";SE(LINS(BH$8:BH8)>$BK$3;EXT.TEXTO($BH8;SOMA(BH$7:BI$7);1);SEERRO(ÍNDICE($BH$6:$BP$6;0;EXT.TEXTO($BG8;BI$7;1));"")))ou em inglês
=IF(ROWS(BH$8:BH8)>$BK$3*4;"";IF(ROWS(BH$8:BH8)>$BK$3;MID($BH8;SUM(BH$7:BI$7);1);IFERROR(INDEX($BH$6:$BP$6;0;MID($BG8;BI$7;1));"")))Coluna BN - Sumarização dos Critérios 4x4
=SE(BH8="";"";CONT.SES(DESLOC($B$1;3;BI8;$Q$4;1);$B$1;DESLOC($B$1;3;BJ8;$Q$4;1);$B$1;DESLOC($B$1;3;BK8;$Q$4;1);$B$1;DESLOC($B$1;3;BL8;$Q$4;1);$B$1))ou em inglês
=IF(BH8="";"";COUNTIFS(OFFSET($B$1;3;BI8;$Q$4;1);$B$1;OFFSET($B$1;3;BJ8;$Q$4;1);$B$1;OFFSET($B$1;3;BK8;$Q$4;1);$B$1;OFFSET($B$1;3;BL8;$Q$4;1);$B$1))Coluna BO - Cálculo do Suporte Mínimo
=SE(BN8="";"";BN8/$Q$4)ou em inglês
=IF(BN8="";"";BN8/$Q$4)Coluna BP - Critério Sim/Não para o Suporte Mínimo
=SE(BO8="";"";SE(BO8>=$Q$2;"Sim";"Não"))ou em inglês
=IF(BO8="";"";IF(BO8>=$Q$2;"Sim";"Não"))Coluna BQ - Cálculo da Confiança Mínima
=SE(BH8="";"";SEERRO(BN8/SEERRO(SEERRO(ÍNDICE($C$1:$L$1;0;CORRESP(BI8;$C$2:$L$2;0));ÍNDICE($AV:$AV;CORRESP(ESQUERDA(BH8;5);AN:AN;0);0));ÍNDICE($AE:$AE;CORRESP(ESQUERDA(BH8;3);Y:Y;0);0));0))ou em inglês
=IF(BH8="";"";IFERROR(BN8/IFERROR(IFERROR(INDEX($C$1:$L$1;0;MATCH(BI8;$C$2:$L$2;0));INDEX($AV:$AV;MATCH(LEFT(BH8;5);AN:AN;0);0));INDEX($AE:$AE;MATCH(LEFT(BH8;3);Y:Y;0);0));0))Coluna BR - Critério Sim/Não para a Confiança Mínima
=SE(BQ8="";"";SE(BQ8>=$Q$3;"Sim";"Não"))ou em inglês
=IF(BQ8="";"";IF(BQ8>=$Q$3;"Sim";"Não"))Coluna BS - Critério OK para a combinação 4x4 ser válida
=SE(E(BP8="Sim";BR8="Sim");"OK";"")ou em inglês
=IF(AND(BP8="Sim";BR8="Sim");"OK";"")Com a coluna BS pronta podemos agora podemos finalizar a coluna V, resultando todas as combinações "OK" 4x4, segue a fórmula iniciando na V3
=SEEERO(ÍNDICE(BH:BH;MENOR(ÍNDICE(($BS$1:$BS$2000="OK")*LIN($BS$1:$BS$2000);0);LINS($V$3:V3)+CONT.SE($BS$1:$BS$2000;"<>OK"));0);"")ou em inglês
=IFERROR(INDEX(BH:BH;SMALL(INDEX(($BS$1:$BS$2000="OK")*ROW($BS$1:$BS$2000);0);ROWS($V$3:V3)+COUNTIF($BS$1:$BS$2000;"<>OK"));0);"")E pronto! Temos a Mineração de Dados de maneira Dinâmica dentro do Excel
Pessoal espero que tenham gostado desse novo artigo, foi um pouco extenso, mas recompensador quando temos todos os dados de maneira dinâmica, nos próximos artigos irei descrever a diferença entre modelos estáticos e os modelos dinâmicos, suas vantagens e desvantagens.
Com as lógicas criadas é possível fazer as combinações 5x5 e 6x6, mas a complexidade destas terei que colocar em um novo artigo, mas convido a você a realizar e nos mostrar.
Se gostou que curtam e compartilhem para que todos saibam o que é possível fazer dentro do Excel.
Caso este artigo tenha mais de 100 compartilhamentos, irei disponibilizar o link do arquivo gratuitamente.
Abraços a todos e até o próximo artigo!
Fabio Baldini
Frase do dia: Richard Feynman - "No matter how beautiful your theory, no matter how clever you are or what your name is, if it disagrees with experiment, it’s wrong" (Não importa quão bonita seja sua teoria, não importa quão inteligente você seja ou qual seja seu nome, se não estiver de acordo com a experiência, está errado)
