Gráfico comparativo de linhas - Exemplo Brasileirão 2018 1º turno

Neste artigo é demonstrada detalhes de um gráfico comparativo de linhas, no exemplo a planilha do Brasileirão 2018 no comparativo dos times no primeiro turno. Veja mais sobre gráficos nesta sessão: https://www.guiadoexcel.com.br/grafico/

Material gratuito

Planilha de Controle Financeiro no Excel

Tenha um controle bonito e completo do seu financeiro.

Categorias e subcategorias já configuradas
Planilhas separadas por tabela (configuração, lançamentos, resumo)
Personalize com suas próprias categorias e ícones

BASE DE DADOS

Na base de dados que utilizamos, como ela é uma bse que não será atualizada e para fins didáticos coloquei a base na mesma planilha dos cálculos.

Na base de dados temos os times na coluna da esquerda, e á cada coluna á direita temos a soma dos pontos acumulados nas rodadas.

CÁLCULOS

Para os cálculos fizemos 3 tipos de cálculos, o primeiro para a classificação do time para cada rodada.

A fórmula utilizada foi a mesma para cada uma das células:

=ORDEM.EQ(DESLOC(B$2;CORRESP($A25;$A$2:$A$21;0)-1;0);B$2:B$21)+CONT.SE(B$2:B2;B2)-1

A função ORDEM.EQ realiza a consulta da posição de um determinado valor em uma lista de valores, no caso foi utilizada a função DESLOC(B$2;CORRESP($A25;$A$2:$A$21;0)-1;0);B$2:B$21) para deslocar a célula em B2 para selecionar a célula seguinte.

Em +CONT.SE(B$2:B2;B2)-1 temos uma verificação para evitar que a mesma classificação seja utilizada para várias células em uma mesma coluna. Esta contagem condicional conta a quantidade de pontuações semelhantes ao valor da pontuação do time naquela coluna.

Na segunda parte do cálculo temos a marcação dos itens que serão exibidos no cálculo, veja o quadro abaixo.

Veja que há muitos itens com erros, e é propital, pois os erros não são exibidos pelo gráfico.

O cálculo utilizado é bastante simples: =B25/$U48, ou seja, o valor daquela rodada e time dividido pelo valor da coluna U, chamada marcação.

Se for 0 este valor ele retornará uma divisão por 0 e assim um erro, senão trará a posição do time naquela rodada.

Neste último cálculo temos o item que é selecionado e mostrado automaticamente ao passar o mouse na planilha Gráfico.

Ao selecionar o time a função =PROCV($A$70;$A$25:$T$44;COL();0) retorna a posição do time em cada rodada.

GRÁFICO COMPARATIVO

Na planilha Gráfico temos um gráfico que está ligado com a primeira parte dos cálculos, aonde vemos os cálculos de erros.

E também inclusa a última parte do cálculo chamada de Selecionado, este valor somente aparecerá caso haja um time na linha 70.

VBA

Para automatizar a questão de preencher a célula logo abaixo de Selecionado na planilha Cálculos foi utilizado o código abaixo:

Public Function lfPreencher(ByVal lNome As String, ByVal lDestino As String) As String
    Sheets("Cálculos").Range(lDestino) = lNome
End Function

E na planilha do gráfico foi utilizado o código abaixo:

=SEERRO(HIPERLINK(lfPreencher(ÍNDICE(Cálculos!$A$25:$A$44;CORRESP(LIN() -1;Cálculos!$B$25:$B$44;0));"A70");ÍNDICE(Cálculos!$A$25:$A$44;CORRESP(LIN() -1;Cálculos!$B$25:$B$44;0)));ÍNDICE(Cálculos!$A$25:$A$44;CORRESP(LIN()-1; Cálculos!$B$25:$B$44;0)))

É a mesma técnica utilizada em: Mudar células ao passar o mouse (Mousehover)

A função acima utiliza o código VBA acima e ao passar o mouse a função Hiperlink chama a função e preenche com o nome do time que está naquela posição.

Evento Worksheet Change

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lLinha  As Long
    Dim lLinha2 As Long
    
    If Not Intersect(Target, Range("B2:B21")) Is Nothing And Target.Rows.Count = 1 Then
        lLinha = Application.WorksheetFunction.Match(Target.Offset(0, -1).Value, Calculos.Range("A48:A67"), 0) + 47
        lLinha2 = Application.WorksheetFunction.Match(Target.Offset(0, -1).Value, gRAFICO.Range("U2:U21"), 0) + 1
        
        If Calculos.Cells(lLinha, 21).Value = 0 Then
            Calculos.Cells(lLinha, 21).Value = 1
            Target.Interior.Color = vbGreen
            Cells(lLinha2, 20).Interior.Color = vbGreen
        Else
            Calculos.Cells(lLinha, 21).Value = 0
            Target.Interior.Color = vbWhite
            Cells(lLinha2, 20).Interior.Color = vbWhite
        End If
    End If
End Sub

No código acima ao clicar na célula da colocação a tabela com o cálculo que exibe o gráfico tem a sua coluna Marcação preenchida com o valor de 1 se estiver 0 e vice-versa, fazendo desta forma que os dados sejam exibidos.

Download — baixe aqui: Gráfico de linhas comparativo