Como criar um gráfico com várias séries (1000) no Excel?

Question

Eu tenho uma planilha do Excel com 1000 linhas, cada uma com 100 colunas com números ordenados do menor para o maior da esquerda para a direita (todas essas datas ou anos em que algo aconteceu). Eu quero criar um índice ordinal para essas datas, plotando cada coluna por 1-100.

A imagem a seguir mostra um exemplo do gráfico que me interessa. Aqui, eu só plotei 2 de 1000 linhas, mas isso é essencialmente o que eu gostaria de fazer com o conjunto de dados inteiro. Exemplo de gráfico de dispersão

Aqui também há um link para uma captura de tela da planilha do Excel. Então, basicamente, estou procurando uma maneira mais rápida de plotar as linhas 1-1000 na linha 1001. Exemplo de planilha

Existe alguma maneira de fazer isso no Excel que seja mais rápido do que criar 1000 séries e selecionar dados 1000 vezes? Ou há outro programa para fazer isso? Eu sinto que r seria útil, mas eu não tenho nenhuma experiência nesse programa.

    

Answer 1

Sim, há um caminho mais rápido. Você pode usar uma macro para criar seu gráfico programaticamente.

Se você conhece o VB, pule para o passo 3.

1. Abra a janela do Visual Basic - na guia Desenvolvedor ou Alt-F11.
2. No canto superior esquerdo, há uma lista de folhas e módulos de pastas de trabalho. Encontre o nome da sua pasta de trabalho, clique com o botão direito do mouse no nome dela e selecione "Inserir > Módulo". Na árvore de arquivos, agora deve haver uma pasta chamada Modules, com um elemento chamado "Module1". Clique duas vezes nele.

3. Você está agora em um módulo do Excel VB. Adicione o seguinte código:

   Sub chart()
   ActiveSheet.Shapes.AddChart.Select
   ActiveChart.ChartType = xlXYScatter
   ActiveChart.Legend.Delete
   i = 1
   numSeries = 1000
   Do While i < numSeries + 1
     ActiveChart.SeriesCollection.NewSeries
     ActiveChart.SeriesCollection(i).XValues = "=Sheet1!$A$" & i & ":$CV$" & i
     ActiveChart.SeriesCollection(i).Values = "=Sheet1!$A$" & numSeries + 1 & ":$CV$" & numSeries + 1
     i = i + 1
   Loop
   End Sub
   

4. Execute o código, seja usando o botão ou menu de execução na janela do Visual Basic, ou através da guia Desenvolvedor no Excel.

5. Você pode ver no código os nomes das colunas $ A e $ CV. Altere estes se o resultado não corresponder aos seus dados por algum motivo. a variável numSeries pode ser alterada se você quiser um número diferente de linhas posteriormente. Esse código presume que você não tem linha de cabeçalho e que os dados do eixo X estão na linha final, que é uma maneira incomum de armazenar dados do gráfico, mas corresponde ao seu exemplo.

Editar: X e Y mudaram:

Sub chart()
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.Legend.Delete
i = 1
numSeries = 1000
Do While i < numSeries + 1
  ActiveChart.SeriesCollection.NewSeries
  ActiveChart.SeriesCollection(i).Values = "=Sheet1!$A$" & i & ":$CV$" & i
  ActiveChart.SeriesCollection(i).XValues = "=Sheet1!$A$" & numSeries + 1 & ":$CV$" & numSeries + 1
  i = i + 1
Loop
End Sub

e agora tentando obter 1000 itens abaixo do limite de 255 fazendo com que cada série exiba 4 conjuntos de dados:

Sub chart()
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.Legend.Delete
i = 1
numSeries = 1000

s0 = "Sheet1!$A$" & numSeries + 1 & ":$CV$" & numSeries + 1


Do While i < (numSeries / 4 + 1)
  s1 = "=Sheet1!$A$" & i & ":$CV$" & i
  s2 = ", Sheet1!$A$" & i + numSeries / 4 & ":$CV$" & i + numSeries / 4
  s3 = ", Sheet1!$A$" & i + 2 * numSeries / 4 & ":$CV$" & i + 2 * numSeries / 4
  s4 = ", Sheet1!$A$" & i + 3 * numSeries / 4 & ":$CV$" & i + 3 * numSeries / 4


  ActiveChart.SeriesCollection.NewSeries
  ActiveChart.SeriesCollection(i).Values = s1 & s2 & s3 & s4
  ActiveChart.SeriesCollection(i).XValues = "=" & s0 & ", " & s0 & ", " & s0 & ", " & s0
  i = i + 1
Loop
End Sub