Como fazer uma tabela de campeonato de futebol – Pontos corridos

Futebol[1]

Introdução
Lá pelos idos de 2002 caiu no meu colo uma planilha Excel que calculava toda a tabela da copa do mundo de 2002, tudo automático. A planilha era uma beleza, toda decorada com as bandeiras das seleções, calculava os classificados automaticamente e fazia o mata-mata até a final. Tudo automático. Uma das coisas que me deixou mais interessado era que na primeira fase da copa a regra era de todos-contra-todos, pontos corridos e a planilha calculava e classificava os times automaticamente. Tudo isso feito sem macros. Modifiquei a planilha e me inspirei nela. Desde então passei a fazer planilhas para campeonatos de futebol. Postei num site desses de downloads e foi um sucesso. Todos os dias recebo emails pedindo a senha para alterar a planilha para campeonatos de bairro, de PES, Fifa,  vídeo game e por aí vai.

O maior desafio era fazer a planilha do campeonato Brasileiro da série A que passou a ser feito em pontos corridos com 20 times em jogo de ida e volta, 38 rodadas com 10 jogos cada, 380 jogos. Essa planilha está disponível para download aqui na versão do campeonato de 2013. A senha para alterá-la é naotem.  Esta planilha pode ser facilmente modificada para campeonatos com outros times.

Como funciona a planilha
Para quem quer entender como a planilha funciona, eu decidi escrever esse artigo e deixar aberto para discussão, dicas e truques de modo que outras pessoas podem baixar a planilha e modificá-la para seus campeonatos. Para saber como a planilha do Brasileirão de pontos corridos funciona é preciso primeiro entender quais são as regras do campeonato.

No Brasileirão temos 20 times que jogam entre si em jogos de ida e volta. A primeira coisa a fazer é calcular quantos jogos a tabela vai ter. Segundo a análise combinatória, o número de jogos de um campeonato com N times é calculado pela fórmula N * (N – 1) / 2. No caso, a Série A tem 20 times então a fórmula fica 20 * (20 -1) /2 = 20 * 19 / 2 = 380 / 2 = 190 jogos. Acontece que temos dois turnos, para proporcionar a ida e a volta. Então temos 190 * 2 jogos = 380 jogos. Tenha isso em mente quando for fazer a sua tabela do seu campeonato. Se você fizer um campeonato com todos contra todos em ida e volta pode acabar tendo uma quantidade de jogos muito grande que vai requerer muito tempo para jogar. Imagine um campeonato com 15 times de PES, em 2 turnos = 15 * 14 = 210 jogos ! Se cada jogo durar 30 minutos, estamos falando de 105 horas de jogo ininterrupto. Depois trataremos desse assunto dos campeonatos longos demais.

Agora temos que definir as regras para pontuação no campeonato. No Brasileirão a regra é clara. Uma vitória vale 3 pontos, empates valem 1 ponto, derrotas valem 0 (zero) pontos. Isso estimula os times a partirem para vitória e não se contentarem apenas com empates tornando os jogos mais emocionantes.

Depois de estabelecido o critério de pontuação é preciso estabelecer os critérios de classificação. No Brasileirão a regra é a seguinte:
Critérios de classificação
1 – Pontos ganhos
2 – Vitórias
3 – Saldo de gols
4 – Gols marcados
5 – Sorteio

Desde que o campeonato passou a ser assim, a classificação nunca precisou usar o critério de sorteio.

Com essas informações já podemos fazer a planilha para calcular tudo automaticamente, sem macros, inclusive com a classificação dos times. Exibi-los em ordem descendente de pontuação, inclusive usando os critérios de desempate caso a pontuação fique igual.

Estrutura da Planilha
Uma das coisas mais importantes quando vamos fazer uma planilha Excel é estruturar bem os dados. Colocar os dados numa disposição que facilite a elaboração das fórmulas. Quanto melhor planejarmos a estrutura da planilha, mais simples as fórmulas e cálculos serão. Com a planilha do Brasileirão a regra se aplica. A estrutura ficou assim

1 – Planilha de jogos. Nessa planilha está a relação de todos os jogos do campeonato. Nela serão informados os resultados dos jogos a medida em que eles vão acontecendo. Os jogos estão relacionados em ordem cronológica, agrupados por rodadas. Não existe preocupação maior com o agrupamento por rodadas. É só para facilitar o preenchimento. Nenhum cálculo é feito por rodada. Tudo aqui é calculado por jogo. A planilha é dividida em uma parte visível, onde o usuário irá informar os resultados dos jogos, e uma parte oculta, que calcula os pontos ganhos, gols pró e gols contra de cada time. A parte visível fica a esquerda da planilha e a parte oculta fica a direita. Para alterar as fórmulas é só reexibir as colunas da parte oculta. Aqui o pulo do gato é o ninho de SE()´s que calcula o resultado do jogo e a pontuação. O primeiro SE() testa se o jogo foi jogado. Para saber se o jogo foi jogado ou não, testa-se se o gols do time mandante (o time do lado esquerdo) e do time visitante (o time do lado direito) foram informados. Se sim, partimos para calcular quantos pontos o mandante fez e quantos pontos o visitante fez. Também calculamos os gols pró e contra do mandante e os gols pró e contra do visitante. Obviamente, se o mandante ganhou o jogo, 3 pontos para ele, 0 (zero) para o visitante. Se empatou, 1 ponto para cada, se o mandante perdeu, 0 (zero) para ele, 3 para o visitante. Parece simples e óbvio mas essa explicitação do resultado e contra-resultado facilita muito as fórmulas para somatório dos pontos de cada time. Essa planilha é editável nas colunas onde se digita os gols de cada time de cada jogo. Os times propriamente ditos, datas, etc, é tudo bloqueado para edição.
2 – Planilha de pontuação. Nessa planilha os times são relacionados em ordem alfabética. Ao lado de cada time está o sumário dos resultados de cada time: pontuação, número de vitórias, número de empates, saldo de gols, gols pró, gols contra. Essa parte do sumário dos resultados é um monte de soma.se() e cont.se() usando como critério o time relacionado e como origem os resultados dos jogos na planilha de jogos. Aqui a coluna mais importante é a coluna de RANKING que é calculada a partir das outras colunas dessa mesma planilha. Mais sobre essa coluna adiante. Essa planilha é editável nos nomes dos times. Isso significa que se você quer usar essa planilha para fazer seu campeonato, basta mudar os nomes dos times aqui. Ela trocará o nome do time em todas as abas.
3 – Planilha de classifcação. Essa planilha contém os mesmos dados da planilha de pontuação só que classificada, ou seja, o time que está melhor no campeonato lá em cima e o pior lá embaixo. A separação de cores é para denotar as classificações para a Copa Libertadores, quem vai ser rebaixado para a segundona, etc. Não tem o que editar nessa planilha. Ela é toda calculada
4 – Meu time. Essa é uma planilha em que o usuário escolhe um time e os jogos desse time serão destacados na planilha de jogos. Além disso, os jogos do time aparecem filtrados nessa planilha. Nada editável exceto a seleção do Meu Time.
5 – Carreira. Essa é uma planilha muito feia que conta a história da classificação de cada time ao longo do campeonato. O sobe-desce de cada time ao longo das rodadas. Cada time e a colocação dele no campeonato. Ela é a base para ser usada no gráfico que é muito legal e a única parte da planilha que precisa de macro.
6 – Gráfico. É a exibição gráfica do sobe-desce ao longo do campeonato. É muito legal. Você pode selecionar quais times quer ver no gráfico e pedir para exibí-lo. Não adianta colocar os 20 times de uma vez que fica muito poluído, a não ser que você tenha uma tela realmente muito grande no seu computador. Normalmente dá para exibir uns 5 ou 6 times. Para exibir o gráfico, você coloca o número de rodadas que deseja exibir na célula A1 da aba carreira e clica no botão para fazer o gráfico. Os times que tiverem um X ao lado do seu nome serão exibidos no gráfico.
7 – Início, Instruções e Revisões. São planilhas que contém apenas informações e nenhum dado é calculado nelas ou a partir delas.
Vimos como estruturar a planilha e como calcular o ranking de cada time. Agora é a hora de classificá-los e exibi-los na ordem certa. Do 1º colocado para o último.

Como classificar a tabela

Isso é feito na aba classificação. Essa aba é totalmente calculada a partir da aba pontuação, especialmente da coluna R.

A coluna R na aba de pontuação é calculada usando a função ORDEM() do Excel. Essa função é muito útil. Ela recebe 3 argumentos

=ordem( número ; lista de números ; tipo de ordem)

Ela retorna em que posição o número fornecido está na lista de números fornecida. A ordem pode ser descendente (0) ou ascendente (1). No nosso caso, usamos a função Ordem() para dar a classificação do ranking de cada time na lista de rankings de todos os times, em ordem DESCENDENTE (do maior para o menor).

Se usarmos o comando classificar do Excel para classificar a planilha pontuação, teremos a classificação. Mas isso é chato. A cada resultado de jogo, teríamos que ir lá na tabela de pontuação, marcar a área, acionar a classificação, selecionar a coluna de classificação. Uma macro pode fazer isso mas macro é chato. Muita gente desconfia das macros (com razão) e nem todo mundo entende de programação para sequer entender como macro funciona, quanto mais fazer uma. O desafio é fazer classificação sem macro. Esse assunto foi discutido em detalhes nesse post aqui. Vejamos como se aplica no caso dessa tabela em particular.

Se observarem a aba de classificação, verificarão que a coluna B está oculta. Basta ir em Exibir, e marcar a opção Títulos para ver os títulos das linhas e colunas.

A coluna B é o pulo do gato dessa aba. Ela contém um corresp() que vai buscar na planilha de pontuação o time correspondente a classificação 1, 2, 3, 4 … até 20, disponíveis na coluna A. Corresp() vai retornar um número que é o número do time na tabela de pontuação que corresponde a essa classificação. De posse desse número, é só usar a função índice() para trazer os dados da planilha pontuação para a planilha classificação.

Notem que a medida que avançamos na planilha as coisas ficam mais simples. Isso se deve ao fato de termos estruturado a planilha de forma boa, facilitando as fórmulas. Se você analisar verá que não tem fórmulas muito complexas.

Anúncios

Como registrar a data e a hora em que uma célula foi alterada

Uma pergunta muito comum é como registrar numa célula a data+hora em que outra célula foi alterada. A primeira solução que vem a mente é usar a função agora(). Acontece que essa função é reavaliada toda vez que QUALQUER célula for modificada posteriormente. O que precisamos é registrar a data+hora em que a célula foi alterada e nunca mais alterar essa data+hora. Para fazer isso vamos usar uma macro.

Eis a macro que faz o que queremos numa versão bem simples que pode ser baixada aqui.

Private Sub Worksheet_Change(ByVal Alvo As Range)
Dim limite_maximo As Integer
limite_maximo = 100 ‘ altere aqui para limitar a última linha
‘ faz nada se mais de uma célula modificada ou se deu delete
If Alvo.Cells.Count > 1 Or IsEmpty(Alvo) Then Exit Sub

If Alvo.Column = 1 And Alvo.Row >= 2 And Alvo.Row <= limite_maximo Then
‘ o if acima garante que a célula modificada está dentro a2:a100

‘ desliga captura do evento change
Application.EnableEvents = False
‘ muda a célula C da linha correspondente
Alvo.Offset(0, 2).Value = Time()
‘ religa a captura de eventos
Application.EnableEvents = True
End If

End Sub

Como funciona essa macro ?

Essa macro tem um nome especial chamada worksheet_change()
Ela é acionada sempre que qualquer célula for modificada manualmente. Ou seja, quando alguém modificar uma célula e teclar ENTER, essa macro será acionada. Ela não será acionada quando copiar/colar, ou quando  um cálculo mudar o valor de uma célula ou quando outra macro mudar o valor da célula. Ela só é acionada se a modificação for feita manualmente.

Além do nome especial, essa macro também é gravada num local especial. Clique-direito sobre o nome da aba que contém a planilha e acione a opção “Exibir código”. Nesse espaço cole a macro acima. Se colocar em outro lugar, a macro não será acionada automaticamente e não vai funcionar.

A sub worksheet_change() recebe como parâmetro a faixa que foi modificada. Observe que a sub recebe uma faixa e não uma simples célula. Isso significa que é possível em alguns casos editar mais de uma célula ao mesmo tempo.

Resumindo até aqui, a sub worksheet_change é acionada quando alguém edita uma célula (ou faixa de células), tem que ser gravada na parte “exibir código” da planilha e passa como parâmetro a faixa que foi modificada.

Agora vamos ao que a macro faz.

A primeira coisa que a macro faz é criar uma variável que definirá quais células serão monitoradas. Esse limite foi definido simplesmente pelo número da linha e pelo número da coluna. No nosso caso, o limite de número de linha será 100. Isso significa que qualquer célula de linha <= (menor ou igual) a 100 será monitorada. Acima desse limite a macro não fará coisa alguma. Além desse limite superior de linha, existe um limite inferior de linha que está fixo em 2 no 2º IF.

Depois de definida a variável que conterá o limite superior, a próxima coisa que a macro faz é testar quantas células foram modificadas. Com a finalidade de simplificar a macro, testamos se mais de uma célula foi modificada contando quantos elementos tem a faixa passada como parâmetro. Se a faixa modificada tiver mais de uma célula ou se for vazia, saímos da macro sem fazer coisa alguma.

Agora vamos testar se a faixa modificada está dentro do limite escolhido. No caso vamos monitorar apenas o que for modificado na coluna A (coluna 1) a partir da 2ª linha até a linha definida pelo limite superior mencionado antes. Fazemos isso usando um IF em que testamos se a coluna (alvo.column) é 1 ( equivalente a coluna A) e se o número da linha (alvo.row) está dentro da faixa entre 2 e o limite. Se estiver, a brincadeira começa. Primeiro desligamos a captura de novas mudanças na planilha, para evitar que a sub worksheet_change() seja chamada enquanto ainda ela mesma ainda está sendo processada. Essa é uma medida de segurança e é efetivada com o comando application.enablevents = false que desliga a captura de eventos. agora vamos registrar na célula ao lado da célula modificada a data e hora. Isso é feito usando a propriedade OFFSET( 0 , 2 ) da célula modificada, que irá deslocar 0 linhas e 2 colunas para a direita, colocando nessa nova célula o valor da data e hora obtidos com a função vba TIME()

Agora é só religar a captura dos eventos e terminar a rotina.

Uma forma mais clara de detectar se a célula em que houve a mudança está na faixa que devemos monitorar é usar a função vba Intersect() assim

sub worksheet_change( byval alvo as range )
dim a_monitorar as range
set a_monitorar = range( “B2:B10” ) ‘ definimos aqui a faixa que queremos monitorar
if Intersect( alvo , a_monitorar ) Is Nothing Then exit sub ‘ se a interseção das faixas for nada, então está fora da faixa a monitorar, saímos da rotina

‘ colocamos aqui o código que queremos fazer
exit
end

Essa técnica de interceptação do evento de modificação da planilha pode ser usado para disparar macros que fazem outras coisas. Por exemplo, pode-se usar esse evento para chamar uma macro que coloca em ordem alfabética uma lista de pessoas numa faixa. Ou … atualizar um gráfico … ou levar os dados de uma parte da planilha para outra. Use a imaginação.