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

Bloquear colar dados incorretos em validação de dados capa
Colar em uma planilha obedecendo a validação de dados Excel
14 de agosto de 2018
Importar imagens em planilhas Excel VBA
11 de setembro de 2018

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/

Gráfico comparativo de linhas Excel

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.

Gráfico comparativo de linhas 1

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.

Gráfico comparativo de linhas 2

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.

Gráfico comparativo de linhas 3

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.

Gráfico comparativo de linhas 4

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.

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.