none
Fórmula para contar o maior Vencedor e maior Perdedor consecutivos - Identificar a maior sequencia RRS feed

  • Pergunta

  • Boa tarde pessoal!

    Tenho uma tabela de campeonato com vitorias (V) e derrotas (D) onde preciso contar quem mais ganhou jogos consecutivos, e quem mais perdeu jogos consecutivos, para premiação.

    Vejam na imagem abaixo que tenho a lista de jogadores e preenchi uma coluna com a maior sequencia de vitorias e derrotas de cada, as cores verde  vermelho são para facilitar a visualização. 

    As duas colunas do final preenchi manualmente para mostrar o resultado que quero, alguém pode me ajudar a resolver via fórmula ?  Ou seja, que fórmula eu colocaria naquelas 2 ultimas colunas para identificar a maior sequência de "V" consecutivos e de "D" consecutivos ?

    Desde já agradeço.

    Att,

    Vinicius




    domingo, 19 de junho de 2016 19:46

Respostas

  • Olá Vinícius.

    O ideal seria abrir um tópico novo para esta segunda duvida.

    De qualquer forma estava pensando nesta questão e penso em 3 alternativas:

    1) Uso de VBA

    2) Uso de uma tabela auxiliar para calcular a posição de vitorias e derrotas consecutivas

    3) Uso de uma coluna de apoio para descobrir a coluna que iniciam as vitorias e derrotas consecutivas.

    Apesar de ser um fórum de VBA, vou te sugerir as soluções 2 e 3, que são aplicadas diretamente na planilha.

    A solução 2 com tabela pode ser vista na imagem:

    A fórmula da imagem em V2 é =SE(U2=VERDADEIRO;SE(CONT.SE($U2:U2;VERDADEIRO)<$S$2;VERDADEIRO;FALSO);CONT.SE(DESLOC(A2;0;1;1;$S2);"V")=$S2)

    Na imagem só tem a tabela de apoio para vitória, então se adotar essa soluçao tem que construir uma segunda tabela de apoio para calcular o intervalo de derrotas. A fórmula é basicamente a mesma mudando o "V" para "D".

    Caso haja mais de um intervalo com a mesma quantidade de vitórias ou derrotas, o primeiro intervalo ficará totalmente pintado e os demais intervalos ficarão somente com a primeira célula formatada. É necessário aprimorar a lógica para ter todos intervalos formatados.

    A solução 3, é mais simples, veja na imagem abaixo, que só preciso de 2 colunas de apoio:

    As fórmulas da imagem são:

    FORMULA DE AS2 =MÁXIMO(SE(CONT.SE(DESLOC(B2;0;COL(B2:Q2)-2;1;$S2);"V")=$S2;COL(B2:Q2);0))

    FORMULA DE AT2 =MÁXIMO(SE(CONT.SE(DESLOC(B2;0;COL(B2:Q2)-2;1;$T2);"D")=$T2;COL(B2:Q2);0))

    Ambas matriciais, devendo ser inseridas com CTRL+SHIFT+ENTER.

    Fórmulas da formatação condicional com o intervalo B2:Q2 selecionado com célula ativa em B2 são:

    Para derrotas: =E(COL(B2)-$AT$2>=0;COL(B2)-$AT$2<$T$2)

    Para vitorias: =E(COL(B2)-$AS$2>=0;COL(B2)-$AS$2<$S$2)

    Observe que as colunas de apoio coloquei em colunas distantes da tabela de resultados. Isso porque estou usando DESLOC na fórmula para gerar novos intervalos desejados no cálculo, e se a coluna de apoio estiver próxima da tabela, o intervalo gerado no DESLOC pode englobar a celula que está a própria fórmula e causar um erro de referência circular.

    Uma observação é que nesta solução se houver dois intervalos com o mesmo número de derrotas ou vitórias consecutivas, apenas o primeiro intervalo será formatado. É possível você criar mais colunas para pegar o inicio do segundo (terceiro, quarto...) intervalo, é só usar a função MAIOR ao invés de máximo e mudar o argumento n-ésimo conforme quantidades de colunas que achar necessário. E criar regras com as devidas referencias ajustadas.

    Talvez o Felipe tenha outras soluções mais simples, pois ele tem bastante experiência.

    Abraços!


    Rafael Kamimura - http://excelmaniacos.com




    terça-feira, 21 de junho de 2016 13:49
  • Considernado que o primeiro D está na célula B2 e o último D está na Q2 e que a célula "Maior Sequência Vitórias" em S2, escreva em S2:

    =MÁXIMO(FREQÜÊNCIA(SE(B2:Q2="V";COL(B2:Q2));SE(B2:Q2<>"V";COL(B2:Q2))))

    Escreva em T2:

    =MÁXIMO(FREQÜÊNCIA(SE(B2:Q2="D";COL(B2:Q2));SE(B2:Q2<>"D";COL(B2:Q2))))

    As fórmulas são matriciais (devem ser entradas com Ctrl+Shift+Enter).


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    segunda-feira, 20 de junho de 2016 00:31
    Moderador
  • Segue link para o arquivo para você ver como apliquei:

    https://drive.google.com/file/d/0B4utYM3AOedCY293TmpzeVJxYm8/view?usp=sharing


    Rafael Kamimura - http://excelmaniacos.com

    terça-feira, 21 de junho de 2016 16:56
  • Olá Rafael, muito obrigado pelo envio do link para download!

    Acabei utilizando a sua sugestão nº3 utilizando colunas extras... (E não tabelas auxiliares)

    Fiz apenas alguns pequenos ajustes pois meu projeto envolve uma tabela com várias linhas, e não 1 linha única como você fez.

    Explicarei abaixo as modificações para os demais amigos do forum:

    1) Acrescentei as demais linhas na tabela... (Jogador 1, jogador 2, jogador 3, .... , jogador 21)

    2) Nas colunas AV e AW coloquei o valor máximo das colunas S e T na primeira linha, ou seja:

    AV1 = MÁXIMO(S2:S21)

    AW1 = MÁXIMO(T2:T21)

    3) Nas células AV2 para baixo e também AW2 para baixo, fiz um comparativo com AV1 e AW1 se aquele jogador daquela linha era um dos que representavam o máximo ou não, ou seja:

    AV2 = SE(S2=$AV$1;1;0)  .......... arrastar até o ultimo jogador

    AW2 = SE(T2=$AW$1;1;0)  .......... arrastar até o ultimo jogador

    4) Nas fórmulas condicionais acrescentei a condição de verificar esta nova coluna, fazendo assim com que pintasse apenas 1 linha para o maior vencedor (+ vit. consec.) e apenas 1 para o maior perdedor (+ derr. consec.), pois antes pintava a maior seq. de vitorias e de derrotas para todos os jogadores.

    Ficou assim:

    Para vitorias: =E(COL(B2)-$AS2>=0;COL(B2)-$AS2<$S2;$AV2=1)

    Para derrotas: =E(COL(B2)-$AT2>=0;COL(B2)-$AT2<$T2;$AW2=1)

    Obs.: Note que nesta parte também foi necessário liberar o bloqueio de linha que constava em AS e AT

    Com isto ficou tudo perfeito, exatamente como era meu objetivo.

    Fica aqui então meu MUITÍSSIMO OBRIGADO ao Felipe Gualberto e Rafael Kamimura!

    Um grande abraço a todos !!!

    Att,

    Vinicius

    quarta-feira, 22 de junho de 2016 00:45

Todas as Respostas

  • Considernado que o primeiro D está na célula B2 e o último D está na Q2 e que a célula "Maior Sequência Vitórias" em S2, escreva em S2:

    =MÁXIMO(FREQÜÊNCIA(SE(B2:Q2="V";COL(B2:Q2));SE(B2:Q2<>"V";COL(B2:Q2))))

    Escreva em T2:

    =MÁXIMO(FREQÜÊNCIA(SE(B2:Q2="D";COL(B2:Q2));SE(B2:Q2<>"D";COL(B2:Q2))))

    As fórmulas são matriciais (devem ser entradas com Ctrl+Shift+Enter).


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    segunda-feira, 20 de junho de 2016 00:31
    Moderador
  • Funcionou perfeitamente Felipe! Muito obrigado pelo seu retorno!

    Vou aproveitar para ver se me ajuda com mais um incremento que eu queria colocar, sabe me dizer se existe alguma maneira de utilizar a formatação condicional para pintar de vermelho apenas a sequência de "D" que tornou o nome 1 maior perdedor, e pintar de verde a sequência de "V" que tornou o nome 4 maior vencedor ?

    Eu tentei fazer e acrescentei 2 colunas novas que deixarei ocultas, comparando o máximo da coluna com o número particular da linha, com isso eu consigo identificar se aquele jogador é o maior vencedor (ou perdedor), em conjunto com isto e com base no próprio valor da célula (D ou V), consigo pintar de verde ou vermelho, porém só consegui fazer pintando todos os D ou todos os V da linha, e não apenas a sequência (Maior consecutiva)

    Resumindo, seria colocar uma formatação condicional na tabela toda (B2:Q21) onde ficariam pintadas de vermelho apenas as células G2:M2 e ficariam pintadas de verde apenas as células D5:H5

    Consegue me ajudar ?

    segunda-feira, 20 de junho de 2016 23:08
  • Olá Vinícius.

    O ideal seria abrir um tópico novo para esta segunda duvida.

    De qualquer forma estava pensando nesta questão e penso em 3 alternativas:

    1) Uso de VBA

    2) Uso de uma tabela auxiliar para calcular a posição de vitorias e derrotas consecutivas

    3) Uso de uma coluna de apoio para descobrir a coluna que iniciam as vitorias e derrotas consecutivas.

    Apesar de ser um fórum de VBA, vou te sugerir as soluções 2 e 3, que são aplicadas diretamente na planilha.

    A solução 2 com tabela pode ser vista na imagem:

    A fórmula da imagem em V2 é =SE(U2=VERDADEIRO;SE(CONT.SE($U2:U2;VERDADEIRO)<$S$2;VERDADEIRO;FALSO);CONT.SE(DESLOC(A2;0;1;1;$S2);"V")=$S2)

    Na imagem só tem a tabela de apoio para vitória, então se adotar essa soluçao tem que construir uma segunda tabela de apoio para calcular o intervalo de derrotas. A fórmula é basicamente a mesma mudando o "V" para "D".

    Caso haja mais de um intervalo com a mesma quantidade de vitórias ou derrotas, o primeiro intervalo ficará totalmente pintado e os demais intervalos ficarão somente com a primeira célula formatada. É necessário aprimorar a lógica para ter todos intervalos formatados.

    A solução 3, é mais simples, veja na imagem abaixo, que só preciso de 2 colunas de apoio:

    As fórmulas da imagem são:

    FORMULA DE AS2 =MÁXIMO(SE(CONT.SE(DESLOC(B2;0;COL(B2:Q2)-2;1;$S2);"V")=$S2;COL(B2:Q2);0))

    FORMULA DE AT2 =MÁXIMO(SE(CONT.SE(DESLOC(B2;0;COL(B2:Q2)-2;1;$T2);"D")=$T2;COL(B2:Q2);0))

    Ambas matriciais, devendo ser inseridas com CTRL+SHIFT+ENTER.

    Fórmulas da formatação condicional com o intervalo B2:Q2 selecionado com célula ativa em B2 são:

    Para derrotas: =E(COL(B2)-$AT$2>=0;COL(B2)-$AT$2<$T$2)

    Para vitorias: =E(COL(B2)-$AS$2>=0;COL(B2)-$AS$2<$S$2)

    Observe que as colunas de apoio coloquei em colunas distantes da tabela de resultados. Isso porque estou usando DESLOC na fórmula para gerar novos intervalos desejados no cálculo, e se a coluna de apoio estiver próxima da tabela, o intervalo gerado no DESLOC pode englobar a celula que está a própria fórmula e causar um erro de referência circular.

    Uma observação é que nesta solução se houver dois intervalos com o mesmo número de derrotas ou vitórias consecutivas, apenas o primeiro intervalo será formatado. É possível você criar mais colunas para pegar o inicio do segundo (terceiro, quarto...) intervalo, é só usar a função MAIOR ao invés de máximo e mudar o argumento n-ésimo conforme quantidades de colunas que achar necessário. E criar regras com as devidas referencias ajustadas.

    Talvez o Felipe tenha outras soluções mais simples, pois ele tem bastante experiência.

    Abraços!


    Rafael Kamimura - http://excelmaniacos.com




    terça-feira, 21 de junho de 2016 13:49
  • Olá Rafael, boa tarde!

    Primeiramente muito obrigado pela sua resposta, acabei utilizando o mesmo tópico pois imaginei que ficaria mais prático já que consta uma parte importante que foi solucionada pelo Felipe.

    Uma outra questão é que não estou me apegando exatamente no "Como", mas sim em fazer dar certo, por mais que haja coluna ou tabela auxiliar.

    Tentei reproduzir as 2 sugestões que você deu (Itens 2 e 3), no caso do item 2 eu criei uma tabela auxiliar e simulei exatamente como está na sua imagem, porém o meu resultado não ficou igual ao seu. Como não tenho muita prática em fórmulas matriciais, não consegui validar a lógica da sua fórmula (Célula V2), porém quando olho para as células e intervalos que ela utiliza a princípio me causa estranhamento, pois ela nem utiliza a própria linha da tabela. Ressalto que entrei com Ctrl+Shift+Enter, utilizei exatamente as mesmas células para o teste, e mesmo colocando a sua sequencia de D e V (que está diferente da minha), ainda assim não obtive o mesmo resultado que o seu.

    Sobre o item 3 eu também tentei reproduzir, fiz nas colunas AS e AT conforme você instruiu, devido ao DESLOC, porém também não funcionou, ele pinta algumas células totalmente erradas. Apenas reforçando o que já mencionei acima, tenho bastante prática em vba e fórmulas, com exceção justamente desta parte "matricial", onde sempre fico meio perdido sobre o funcionamento lógico.

    É possível você me passar o link para download deste arquivo onde você fez os procedimentos? Pois talvez seja algum erro meu na reprodução.

    Desde já agradeço e fico no aguardo.

    Att,

    Vinicius

    terça-feira, 21 de junho de 2016 16:49
  • Segue link para o arquivo para você ver como apliquei:

    https://drive.google.com/file/d/0B4utYM3AOedCY293TmpzeVJxYm8/view?usp=sharing


    Rafael Kamimura - http://excelmaniacos.com

    terça-feira, 21 de junho de 2016 16:56
  • Olá Rafael, muito obrigado pelo envio do link para download!

    Acabei utilizando a sua sugestão nº3 utilizando colunas extras... (E não tabelas auxiliares)

    Fiz apenas alguns pequenos ajustes pois meu projeto envolve uma tabela com várias linhas, e não 1 linha única como você fez.

    Explicarei abaixo as modificações para os demais amigos do forum:

    1) Acrescentei as demais linhas na tabela... (Jogador 1, jogador 2, jogador 3, .... , jogador 21)

    2) Nas colunas AV e AW coloquei o valor máximo das colunas S e T na primeira linha, ou seja:

    AV1 = MÁXIMO(S2:S21)

    AW1 = MÁXIMO(T2:T21)

    3) Nas células AV2 para baixo e também AW2 para baixo, fiz um comparativo com AV1 e AW1 se aquele jogador daquela linha era um dos que representavam o máximo ou não, ou seja:

    AV2 = SE(S2=$AV$1;1;0)  .......... arrastar até o ultimo jogador

    AW2 = SE(T2=$AW$1;1;0)  .......... arrastar até o ultimo jogador

    4) Nas fórmulas condicionais acrescentei a condição de verificar esta nova coluna, fazendo assim com que pintasse apenas 1 linha para o maior vencedor (+ vit. consec.) e apenas 1 para o maior perdedor (+ derr. consec.), pois antes pintava a maior seq. de vitorias e de derrotas para todos os jogadores.

    Ficou assim:

    Para vitorias: =E(COL(B2)-$AS2>=0;COL(B2)-$AS2<$S2;$AV2=1)

    Para derrotas: =E(COL(B2)-$AT2>=0;COL(B2)-$AT2<$T2;$AW2=1)

    Obs.: Note que nesta parte também foi necessário liberar o bloqueio de linha que constava em AS e AT

    Com isto ficou tudo perfeito, exatamente como era meu objetivo.

    Fica aqui então meu MUITÍSSIMO OBRIGADO ao Felipe Gualberto e Rafael Kamimura!

    Um grande abraço a todos !!!

    Att,

    Vinicius

    quarta-feira, 22 de junho de 2016 00:45

  • Ola amigos, tenho uma duvida nao sei se sera possivel uma solucao.

    Eu queria fazer uma sequencia de vitorias, de modo que o excel ignore NP,D e celulas em branco, pelo que pesquisei isso nao seria possivel.

    Explicando melhor queria que o excel contasse a sequencia de V ate achar a letra D ou NP.

    Tentei postar imagem mas o sistema do forum nao aceitou 

    EX.

    V V V V NP D D D V V V V NP  

    Queria que ele contasse a sequencia de V ate achar a letra D ou NP e ignorar celulas em branco.

    Desde ja agradeco.

    Obrigado.


    sexta-feira, 13 de dezembro de 2019 22:51