Estou tentando criar um gráfico de dispersão agrupado com eixo X categórico. Meus dados e minha tentativa de gráfico atual:
Estou tentando agrupar todas as letras como pontos com o mesmo valor x, sem que nenhuma letra seja repetida... por exemplo, todos os D espalhados verticalmente:
Como posso fazer isso?
Responder1
Aqui está uma solução:Excel - Plotando diferentes valores de y sobre os mesmos valores de x
Parece que o truque está em converter os rótulos em números (eles provavelmente nunca ouviram falar de dados nominais e ordinais em ms, hein). Isso é muito fácil de conseguir por meio do contador de classificação e cumsum. O problema é que o eixo X é numérico e brincar manualmente com o espaçamento, conforme sugerido acima, pode não ser viável em grande escala.
Outra abordagem é mesclar os dados em uma única linha por categoria - o que é bastante simples, seja por meio de fórmula ou vba - e depois aplicar o gráfico de dispersão à tabela. Isso criará uma série para cada coluna, cada uma com uma forma da cor do arco-íris. Corrigi-los simultaneamente, imagino, deveria ser possível em vba com a propriedade de array correta (não examinei a documentação). Além disso, ter espaços em branco durante a geração do gráfico parece confundi-lo, então substituí-os inicialmente por valores de espaço reservado (algo como -412613) e depois, uma vez gerado o gráfico, substituí todos os espaços reservados por espaços em branco, removendo-os convenientemente do gráfico sem maiores consequências. Portanto, esse é um gasto único maior para codificar e, a partir de então, 2 minutos para aplicar.
Ou se você tiver acesso a um software de plotagem melhor, bem...
Editar (30/06/19): Mediante solicitação, os detalhes técnicos serão elaborados abaixo. Meus dados randomizados estão originalmente em A+C.
Cumprimento:
B1=1 ; B2=if(a2=a1,b1,b1+1)
Mesclar usando a fórmula:
D1=1 ; D2 = if(a2=a1,0,1)
E1=if(index($a2:$a$999,column(a1),1)=$a1, _
value(index($c2:$c$999,column(a1),1)),-0.413612)
Cole E ao longo de todas as linhas e em tantas colunas quanto a maior quantidade de observações - você saberá quando um filtro da coluna mostrar apenas placeholder. Filtre de acordo com D = 1, cole os valores em outra planilha e exclua D. Eu prefiro esse método pessoalmente, pois é um pouco mais 'plataforma cruzada', funciona em libre.
Alternativa - mesclar usando vba:
Const BASESHEET As String = "Base"
Const MERGESHEET As String = "Merge2"
Sub MergePairs()
Dim rowcnt As Long
Dim srcsht
Dim trgsht
Dim pid As String
Dim stidx As Long
Dim trgidx As Long
Set srcsht = Sheets(BASESHEET)
Set trgsht = Sheets(MERGESHEET)
' Sort the data.
srcsht.Sort.SortFields.Clear
srcsht.Sort.SortFields.Add Key:=Range("$A:$A"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With srcsht.Sort
.SetRange Range("$A:$C")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' Copy unique ids.
rowcnt = srcsht.Cells(srcsht.Rows.Count, 1).End(xlUp).Row
srcsht.Range(srcsht.Cells(1, 1), srcsht.Cells(rowcnt, 1)).Copy
trgsht.Range("A1").PasteSpecial
trgsht.Range("$A:$A").RemoveDuplicates Columns:=1, Header:=xlNo
' Copy each set of points.
pid = ""
For i = 1 To rowcnt + 1
If pid = "" Then
pid = srcsht.Cells(i, 1).Value
stidx = i
trgidx = 1
ElseIf pid <> srcsht.Cells(i, 1).Value Then
srcsht.Range(srcsht.Cells(stidx, 3), srcsht.Cells(i - 1, 3)).Copy
trgsht.Cells(trgidx, 2).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
pid = srcsht.Cells(i, 1).Value
stidx = i
trgidx = trgidx + 1
End If
Next i
End Sub
Não se esqueça de criar um gráfico selecionando todos os valores e, em seguida, substituir todos os espaços reservados por espaços em branco.
Altere TODOS os marcadores: parece que os rótulos dos eixos em um gráfico de dispersão não são facilmente flexíveis (possivelmente até mesmo sem permissão para fazê-lo); por outro lado, é bastante simples eliminar as linhas de um gráfico de linhas. (Observe que isso altera todos os gráficos da planilha, portanto, certifique-se de que esteja sozinho ou modifique o loop para um parm.)
Sub XChart()
Dim chrt As ChartObject
Dim ser As Excel.Series
For Each chrt In Worksheets("Merge1").ChartObjects
chrt.Chart.ChartType = xlLine
For Each ser In chrt.Chart.SeriesCollection
ser.Format.Line.Visible = msoFalse
ser.MarkerStyle = xlMarkerStyleX
ser.MarkerForegroundColor = RGB(0, 0, 0)
ser.MarkerBackgroundColor = RGB(255, 255, 255)
ser.MarkerSize = 7
Next ser
Next chrt
End Sub
Algumas imagens para demonstração a seguir.
Fase de seleção:
Gráfico gerado com esses dados (preste atenção ao resultado final dos espaços reservados):
Substituindo espaços reservados por espaços em branco:
Aqui está o resultado final, com cada grama de vivacidade sugada de seus ossos.
Editar (24/04/20): Como fui informado, parece que as versões do Excel 2013+ alteraram as configurações do gráfico de linhas para o eixo do tipo de valor, aparentemente sem qualquer meio de alterá-lo. No entanto, os caracteres de coluna/barra (ainda) usam um eixo de tipo de categoria; Um possível hack de baixa tecnologia é criar um gráfico de dispersão/linha conforme descrito e, em seguida, um segundo gráfico de barras dos rótulos + 0 valores apenas - isso cria uma grade vazia, que pode ser limpa de legendas, marcações, etc., para deixar apenas o eixo horizontal . Então, o alinhamento dos dois gráficos é possível, embora feio, ajustando o intervalo numérico da dispersão (aproximadamente de 0/1 ao número de rótulos+) e a largura e posição da coluna. É possível obter uma referência ao eixo (excel.) através de Worksheets("Merge").ChartObjects(1).Chart.Axes(xlCategory,xlPrimary), onde as diferenças de valor/categoria são bastante claras; apesar disso, ele não respondeu a nenhuma solicitação de alteração de propriedade que tentei.