Estoy intentando crear un diagrama de dispersión agrupado con un eje X categórico. Mis datos y mi intento de gráfico actual:
Estoy tratando de agrupar todas las letras como puntos con el mismo valor de x, sin que se repita ninguna letra... por ejemplo, todas las D distribuidas verticalmente:
¿Cómo puedo lograr esto?
Respuesta1
Aquí hay una solución:Excel: trazar diferentes valores de y encima de los mismos valores de x
Parece que el truco está en convertir las etiquetas en números (probablemente no hayan oído hablar de datos nominales y ordinales en ms, eh). Esto es bastante fácil de lograr mediante el contador de clasificación y suma acumulada. El problema es que el eje X es numérico y jugar manualmente con el espaciado como se sugiere más arriba puede no ser viable a gran escala.
Otro enfoque es fusionar los datos en una sola fila por categoría (eso es bastante sencillo, ya sea mediante fórmula o vba) y luego aplicar el diagrama de dispersión a la tabla. Esto creará una serie para cada columna, cada una con una forma de color del arco iris. Me imagino que corregirlos simultáneamente debería ser factible en vba con la propiedad de matriz correcta (no he examinado la documentación). Además, tener espacios en blanco mientras se genera el gráfico parece confundirlo, por lo que los reemplacé con valores de marcador de posición inicialmente (algo así como -412613) y luego, una vez que se genera el gráfico, reemplacé todos los marcadores de posición con espacios en blanco, eliminándolos convenientemente del gráfico. sin mayores consecuencias. Por lo tanto, es un gasto mayor de una sola vez para codificar y luego 2 minutos para aplicarlo.
O si tienes acceso a un mejor software de trazado, bueno...
Editar (30/06/19): Previa solicitud, los detalles técnicos se detallarán a continuación. Mis datos aleatorios están originalmente en A+C.
Cumsum:
B1=1 ; B2=if(a2=a1,b1,b1+1)
Fusionar usando la 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)
Pegue E a lo largo de todas las filas y en tantas columnas como haya la mayor cantidad de observaciones; sabrá cuándo un filtro de la columna muestra solo un marcador de posición. Filtre según D=1, pegue los valores en otra hoja y elimine D. Personalmente, prefiero este método ya que es un poco más "multiplataforma" y funciona de forma gratuita.
Alternativa: fusionar 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
No olvide crear un gráfico seleccionando todos los valores y luego reemplazar todos los marcadores de posición con espacios en blanco.
Cambie TODOS los marcadores: Parece que las etiquetas de los ejes en un gráfico de dispersión no son fácilmente flexibles (posiblemente ni siquiera tengan permiso para hacerlo); por el contrario, es bastante sencillo deshacerse de las líneas en un gráfico de líneas. (Tenga en cuenta que esto cambia todos los gráficos de la hoja, así que asegúrese de que esté solo o modifique el bucle a un parámetro).
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
Algunas imágenes para la demostración a continuación.
Fase de selección:
Gráfico generado con estos datos (preste atención a la línea inferior de marcadores de posición):
Reemplazo de marcadores de posición con espacios en blanco:
Aquí está el resultado final, con cada gramo de vivacidad extraído de sus huesos.
Editar (24/04/20): Como me han informado, parece que las versiones de Excel 2013+ cambiaron la configuración del gráfico de líneas al eje de tipo de valor, aparentemente sin ningún medio para modificarlo. Sin embargo, los caracteres de columna/barra utilizan (todavía) un eje de tipo de categoría; Un posible truco de baja tecnología es crear un gráfico de líneas/dispersión como se describe, luego un segundo gráfico de barras de las etiquetas + valores 0 solamente; esto crea una cuadrícula vacía, que se puede limpiar de leyendas, marcas, etc. para dejar solo el eje horizontal. . Luego, la alineación de los dos gráficos es factible, aunque desagradable, ajustando el rango numérico de la dispersión (aproximadamente desde 0/1 hasta el número de etiquetas+) y el ancho y la posición de la columna.
Es posible obtener una referencia al eje (excel.) a través de Worksheets("Merge").ChartObjects(1).Chart.Axes(xlCategory,xlPrimary), donde las diferencias entre valor/categoría son bastante claras; no obstante, no respondió a ninguna solicitud de cambio de propiedad que intenté.