Convertir una tabla de referencia cruzada (2D) en una lista plana en Excel: Una guía completa

Al trabajar con datos en Excel, es común encontrarse con tablas de referencia cruzada (también conocidas como tablas dinámicas o tablas de matriz) donde los datos están organizados en un formato bidimensional. Sin embargo, muchas tareas de análisis de datos e informes requieren que los datos estén en un formato de lista normalizada (una tabla en "formato largo"). Este artículo explica varios métodos eficientes para transformar una tabla de referencia cruzada en una lista, facilitando el análisis, filtrado y uso en bases de datos.

Convertir una tabla de referencia cruzada (tabla bidimensional) en una lista en Excel

• Usando Power Query
• Usando Kutools para Excel
• Usando fórmulas

Convertir una tabla de referencia cruzada en una lista con POWER QUERY

En Excel, Power Query es la forma más eficiente de transformar datos, especialmente al manejar grandes conjuntos de datos.

1. Seleccione sus datos y vaya a la pestaña "Datos", luego haga clic en "Desde Tabla/Rango", vea la captura de pantalla:
   
2. En el cuadro de diálogo emergente "Crear Tabla", haga clic en el botón Aceptar.
   
3. Luego, se abrirá una ventana del "Editor de Power Query". En la ventana, seleccione todos los encabezados de columna excepto el primero (Categoría).
   
4. Y luego, haga clic en "Transformar" > "Despivotar Columnas". Power Query creará dos nuevas columnas: "Atributo" (sus antiguos encabezados de columna) y "Valor" (los valores de las celdas).
   
5. Cambie el nombre de estas columnas según sea necesario (por ejemplo, "Mes" y "Ventas") para adaptarlas a sus necesidades.
6. Luego, haga clic en "Inicio" > "Cerrar y Cargar" para devolver la lista transformada a Excel. La lista plana aparecerá en una nueva hoja.
   

Convertir una tabla de referencia cruzada en una lista con Kutools para Excel

Si bien Power Query puede lograr esto, Kutools para Excel proporciona la solución más rápida y fácil con solo unos pocos clics. Con la función "Convertir Dimensión de Tabla" de Kutools para Excel, puede convertir rápidamente tablas de referencia cruzada complejas en listas estructuradas o viceversa sin escribir fórmulas ni pasar por múltiples pasos de transformación.

Después de instalar Kutools para Excel, siga los siguientes pasos:

1. Seleccione la tabla que desea convertir en una lista. Luego, haga clic en "Kutools" > "Rango" > "Convertir Dimensión de Tabla".
   
2. En el cuadro de diálogo "Convertir Dimensión de Tabla", marque la opción "Convertir tabla bidimensional a lista", en la sección "Rango de resultados", seleccione una celda para colocar el resultado, vea la captura de pantalla:
   
3. Finalmente, haga clic en el botón Aceptar, verá que la tabla original se convierte en una lista plana.
   

Convertir una tabla de referencia cruzada en una lista usando fórmulas

Si aún no se siente cómodo usando Power Query o necesita una solución que funcione en versiones anteriores de Excel, estas potentes fórmulas convertirán eficazmente sus datos de referencia cruzada en un formato de lista estructurada.

Para listar la columna de categoría, aplique la siguiente fórmula:

=INDEX($A$2:$A$4, INT((ROW(A1)-1)/COLUMNS($B$1:$D$1))+1)

Arrastre la fórmula hacia abajo para obtener todos los elementos de la categoría hasta que se muestren valores de error. Vea la captura de pantalla:

Para listar la columna de mes, aplique la siguiente fórmula:

=INDEX($B$1:$D$1, MOD(ROW(A1)-1, COLUMNS($B$1:$D$1))+1)

Auto-llene la fórmula hacia abajo para completar todas las filas requeridas, asegurándose de que el rango coincida con el recuento de filas de su columna de categoría original.

Para listar todos los demás datos, aplique la siguiente fórmula:

=INDEX($B$2:$D$4, INT((ROW(A1)-1)/COLUMNS($B$1:$D$1))+1, MOD(ROW(A1)-1, COLUMNS($B$1:$D$1))+1)

Arrastre la fórmula hacia abajo hasta que se extraigan todos los datos. Vea la captura de pantalla:

Conclusión

Para la mayoría de los usuarios, Power Query ofrece el equilibrio ideal entre potencia, flexibilidad y mantenimiento para convertir tablas de referencia cruzada en listas. Kutools ofrece la solución más simple para aquellos que lo tienen instalado, mientras que las fórmulas siguen siendo una opción viable cuando no hay otras herramientas disponibles.

Elija el método que mejor se adapte a su nivel de comodidad técnica, versión de Excel y la frecuencia con la que necesita realizar esta transformación. Para informes o paneles recurrentes, la capacidad de actualización de Power Query lo convierte en el claro ganador en la mayoría de los escenarios. Si está interesado en explorar más consejos y trucos de Excel, nuestro sitio web ofrece miles de tutoriales para ayudarlo a dominar Excel.

Artículos relacionados:

• Convertir lista plana en tabla bidimensional de referencia cruzada