Gráfico de dispersão agrupado, eixo X categórico no Excel

Question

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?

Answer 1

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.