☰

Seleccionar series automáticamente en el diagrama de dispersión

tag-icon • tag-icon microsoft-excel charts
Seleccionar series automáticamente en el diagrama de dispersión

Usando Excel 365

Aquí hay un ejemplo en el que he estado trabajando y que se acerca bastante a lo que quiero:https://docs.google.com/spreadsheets/d/1zZiFDaUhuZAhyHm3HZkcZrUl9KEoV97N/edit?usp=sharing&ouid=108210493177158563166&rtpof=true&sd=true

Básicamente, tengo los datos A, B y C, y para cada A único con más de una entrada en la tabla, quiero trazar B frente a C en un gráfico de dispersión con una línea. Estoy diseñando una plantilla y deberá funcionar con todas las permutaciones de datos.

Lo que tengo es bastante parecido a eso con algunos problemas que no he podido solucionar:

  1. Necesito asignar suficiente espacio para todos los valores posibles de A y B en las tablas. Este no es un gran problema, los conjuntos de datos con los que estoy trabajando en este momento son bastante pequeños, pero sería bueno si fuera completamente dinámico, capaz de manejar cualquier cantidad de valores.

  2. La leyenda muestra entradas en blanco para todas las columnas no utilizadas de la tabla. Solo quiero que muestre las entradas para las que tengo datos.

  3. La línea no está conectada para valores no adyacentes de B (consulte la línea 3 para ver un ejemplo). Me gustaría que estuviera conectado dentro de cada serie.

Espero que esto tenga sentido, no dudes en hacer cualquier pregunta aclaratoria.

Gracias de antemano.

Respuesta1

Esto es lo que debe hacer, hacer que todo sea dinámico (consulte la animación):

ingrese la descripción de la imagen aquí

  • En primer lugar, en lugar de utilizar rangos en blanco para iterar, utilice Structured Referencestambién conocido como Tables--> Tabla denominada comoTable1
  • Fórmulas utilizadas para Columnas F3:G16y J2:O8que se irán ampliando paulatinamente.

• Fórmula utilizada en la celdaF3

=LET(
     _Data, Table1,
     _A, TAKE(_Data,,1),
     _Uniq, UNIQUE(_A),
     HSTACK(_Uniq, COUNTIF(_A,_Uniq)))

• Fórmula utilizada en la celdaJ2

=LET(
     _Data, Table1,
     _A, TAKE(_Data,,1),
     _B, INDEX(_Data,,2),
     _C, TAKE(_Data,,-1),
     _UniqA, TOROW(UNIQUE(_A)),
     _UniqB, UNIQUE(_B),
     _ArrayB, VSTACK("",_UniqB),
     _DataBody, VSTACK(_UniqA&"A", MAKEARRAY(ROWS(_UniqB), COLUMNS(_UniqA), LAMBDA(r,c, 
                FILTER(_C, (INDEX(_UniqA,1,c)=_A)*(INDEX(_UniqB,r)=_B),NA())))),
     HSTACK(_ArrayB, _DataBody))

La fórmula anterior también se puede definir usando LAMBDA()--> Entonces, lo que debe hacer es envolver la fórmula anterior dentro de LAMBDA()-->parameter_or_calculationas arrayy reemplace el Table1as array. Así se convierte y debe ser ingresado enrefers toal definir el nombre como

=LAMBDA(array, LET(_Data, array, the rest continues as is)

ingrese la descripción de la imagen aquí

En Excella hoja ingrese como:

=SCATTER_PLOT(Table1)

ingrese la descripción de la imagen aquí

  • Tenga en cuenta que he usado MAKEARRAY()una LAMBDA()función auxiliar en lugar de usar REDUCE(), que también se puede usar REDUCE(), pero es una función de recursos pesados ​​y no será tan eficiente como MAKEARRAY()cuando se apilan datos.
  • Al insertar el, Scatter with smooth lines and markersasegúrese de seleccionar el rango de datos J2:O8y recuerde Connect data points with lineseleccionar el Hidden andcuadro Celdas vacías.
  • También lo he usado Conditional Formattingpara las fronteras. aquí está elSobresalir.

Respuesta2

Posibles modificaciones:

  1. Para dejar espacio para más datos:

    F3: =UNIQUE(FILTER(B:B,ISNUMBER(B:B)))
    G3: =COUNTIF(B:B,F3#)
    K3: =TRANSPOSE(F3#)
    K4: =K3# & " A"
    J5: =UNIQUE(FILTER(C:C,ISNUMBER(C:C)))
    K5: =FILTER($D:$D,($B:$B=K$3)*($C:$C=$J5),#N/A)copiar a la región necesaria.
    Es mejor usar aquí los rangos realmente necesarios, por ejemplo
    =FILTER($D$1:$D$100,($B$1:$B$100=K$3)*($C$1:$C$100=$J5),#N/A)

  2. Depende de Chart data range. No conozco un método para hacerlo dinámico (sin VBA). Podemos definir una fórmula que defina este rango de datos dinámicamente; sin embargo, si la usamos aquí, se calculará de inmediato y se guardará como una dirección estática.

  3. Para establecer una conexión entre puntos, debe cambiar una opción del gráfico:
    Select Data Source > Hidden and Empty Cells > Connect data points with line

información relacionada