¿Cómo convertir una tabla de estilo matricial en tres columnas en Excel?
Al trabajar con datos en Excel, es posible que encuentre con frecuencia tablas de estilo matricial que presentan la información en una cuadrícula, donde tanto las filas como las columnas actúan como encabezados. Aunque este formato es visualmente útil para algunos análisis, puede necesitar transformar esta matriz en una "lista" o tabla de tres columnas para tareas como la importación de bases de datos, la normalización de datos, la creación de gráficos o el análisis avanzado. Convertir la matriz en una lista de tres columnas (a veces llamada datos "despivotados") permite filtrar, agregar e integrar más fácilmente con otras herramientas de datos. Vea el ejemplo a continuación que ilustra la transformación:
➤ Convertir tabla de estilo matricial en lista con Tabla dinámica
➤ Convertir tabla de estilo matricial en lista con código VBA
➤ Convertir tabla de estilo matricial en lista con Kutools para Excel
➤ Convertir tabla de estilo matricial en lista con fórmulas de Excel
Convertir tabla de estilo matricial en lista con Tabla dinámica
En Excel, no hay un comando incorporado directo para convertir una tabla de estilo matricial en una lista de tres columnas. Sin embargo, utilizando el Asistente para Tablas Dinámicas, puede transformar eficientemente una matriz tabulada en datos planos y tabulares adecuados para análisis adicionales. Este enfoque es ideal para conjuntos de datos pequeños o medianos y es especialmente útil cuando desea aplanar una estructura de informe compleja. Es menos adecuado para grandes conjuntos de datos o usuarios desconocedores de las operaciones de Tabla Dinámica.
1. Abra la hoja de cálculo que contiene su matriz. Presione Alt + D, luego P para abrir el Asistente para Tablas Dinámicas y Gráficos Dinámicos. En el asistente:
- Bajo ¿Dónde están los datos que desea analizar?, elija Rangos de consolidación múltiple.
- Bajo ¿Qué tipo de informe desea crear?, seleccione Tabla dinámica.
2. Haga clic en Siguiente. En el Paso 2a de 3 del cuadro de diálogo, seleccione Crearé los campos de página:
3. Haga clic Siguiente. En Paso 2b de 3haga clic en el 
botón y seleccione el rango completo de datos de la matriz, incluidos los encabezados de fila y columna. Haga clic Agregar para insertar el rango en Todos los rangos lista. Confirme que la selección del rango cubre toda la matriz.
4. Haga clic en Siguiente. En Paso 3 de 3, elija si desea colocar la Tabla dinámica en una nueva hoja de cálculo o en una celda específica:
5. Haga clic en Finalizar. Excel genera una Tabla dinámica que resume la matriz. Por defecto, muestra totales agregados en las intersecciones. No necesita ajustar la estructura de la Tabla dinámica para esta tarea; simplemente proceda al siguiente paso:
6. Haga doble clic en la celda donde se cruzan los Totales generales de fila y columna (por ejemplo, la celda F22). Excel creará una nueva hoja que contiene una lista de tres columnas, donde cada fila muestra una combinación única de encabezado de fila y columna con el valor correspondiente.
7. Para finalizar, seleccione la nueva tabla, haga clic derecho y elija Tabla > Convertir en rango. Esto elimina el formato de tabla, dejando una lista sencilla y editable:
Consejo: Si su matriz cambia con frecuencia, deberá repetir este proceso para actualizar la lista de tres columnas. Este método es mejor para datos estáticos. Además, si su matriz contiene celdas vacías o combinadas, puede ser necesario realizar una limpieza antes de usar esta técnica.
Convertir tabla de estilo matricial en lista con código VBA
Si prefiere la automatización o desea aplicar esta transformación repetidamente, el uso de una macro VBA puede convertir rápidamente cualquier tabla de estilo matricial en una lista estructurada de tres columnas. Este método es especialmente efectivo para grandes conjuntos de datos o diseños diversos y elimina la necesidad de formateo manual. Es ideal para usuarios familiarizados con la ejecución de scripts VBA.
1. Presione Alt + F11 para abrir el editor de Microsoft Visual Basic para Aplicaciones.
2. En el editor, haga clic en Insertar > Módulo para crear un nuevo módulo. Luego pegue el siguiente código en la ventana del módulo:
📜 Código VBA: Convertir matriz en lista
Sub ConvertTable()
' Updated by Extendoffice
Dim Rng As Range
Dim cRng As Range
Dim rRng As Range
Dim xOutRng As Range
xTitleId = "KutoolsforExcel"
Set cRng = Application.InputBox("Select your Column labels", xTitleId, Type:=8)
Set rRng = Application.InputBox("Select Your Row Labels", xTitleId, Type:=8)
Set Rng = Application.InputBox("Select your data", xTitleId, Type:=8)
Set outRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set xWs = Rng.Worksheet
k = 1
xColumns = rRng.Column
xRow = cRng.Row
For i = Rng.Rows(1).Row To Rng.Rows(1).Row + Rng.Rows.Count - 1
For j = Rng.Columns(1).Column To Rng.Columns(1).Column + Rng.Columns.Count - 1
outRng.Cells(k, 1) = xWs.Cells(i, xColumns)
outRng.Cells(k, 2) = xWs.Cells(xRow, j)
outRng.Cells(k, 3) = xWs.Cells(i, j)
k = k + 1
Next j
Next i
End Sub3. Presione F5 o haga clic en Ejecutar para ejecutar la macro. Una serie de mensajes le guiarán a través de las selecciones requeridas:
Paso 1: Seleccione las etiquetas de columna (generalmente la fila superior de su matriz):
Paso 2: Seleccione las etiquetas de fila (generalmente la primera columna de su matriz):
Paso 3: Seleccione el rango de datos real de la matriz (excluyendo los encabezados de fila y columna):
Paso 4: Elija la celda de salida donde debe comenzar la lista de tres columnas convertida. Se recomienda usar una celda en blanco o una nueva hoja de trabajo:
Paso 5: Haga clic en Aceptar. Su matriz ahora se convertirá en una lista plana de tres columnas.
⚠️ Notas y consejos:
• Asegúrese de no incluir encabezados de columna o fila en el rango de datos de la matriz.
• Si su matriz tiene celdas combinadas, descombínelas antes de ejecutar la macro para evitar errores.
• Si encuentra errores, verifique nuevamente los rangos seleccionados y asegúrese de que estén alineados correctamente.
Convertir tabla de estilo matricial en lista con Kutools para Excel
Aunque los métodos anteriores son efectivos, pueden parecer tediosos o intimidantes para usuarios menos experimentados. Si está buscando una solución rápida y fácil de usar, Kutools para Excel ofrece una utilidad dedicada llamada Convertir dimensión de tabla, específicamente diseñada para este propósito.
Esta herramienta es ideal para usuarios que frecuentemente convierten tablas de estilo matricial o necesitan realizar procesamiento por lotes. Puede preservar el formato original—como fuente, color de relleno y fórmulas—si es necesario. Un posible inconveniente es que Kutools es un complemento de terceros que necesita instalarse, pero es una opción poderosa para cualquiera que reestructure datos regularmente en Excel.
Pasos:
1. Después de instalar Kutools, vaya a la pestaña Kutools, haga clic en Rango y luego seleccione Convertir dimensión de tabla:
2. En el cuadro de diálogo Convertir dimensión de tabla:
- (1) Elija Convertir tabla bidimensional a lista bajo la sección Tipo de conversión.
- (2) Haga clic en el
botón junto a Rango de origen para seleccionar su tabla matricial. - (3) Haga clic en el botón junto a Rango de resultados para especificar dónde colocar la salida.
Asegúrese de seleccionar toda la matriz (incluidos los encabezados y los datos) para evitar conversiones parciales o resultados incorrectos.
3. Haga clic en Aceptar. La matriz se convertirá instantáneamente en una lista de tres columnas, preservando el formato de celda original cuando sea posible:
Consejo: Esta función también admite la operación inversa: convertir una lista plana en una matriz bidimensional. Es útil para reconstruir informes o preparar datos para análisis cruzado. Vea: cómo convertir una lista en una tabla cruzada 2D.
➤ Aprenda más sobre la función Convertir dimensión de tabla
⏬ ¡Descargue y pruebe Kutools para Excel gratis ahora!
Convertir tabla de estilo matricial en lista con fórmulas de Excel
Si prefiere un enfoque basado en fórmulas—especialmente útil si desea que su lista de tres columnas se actualice dinámicamente a medida que cambia la matriz—puede usar una combinación de las funciones INDICE, FILA, COLUMNA y CONTARA para despivotar manualmente los datos. Esta solución no requiere VBA ni complementos y es ideal para aquellos que desean evitar usar macros u herramientas externas. Sin embargo, requiere atención cuidadosa a las referencias de fórmula ya que las fórmulas generalmente deben llenarse como matrices o arrastrarse sistemáticamente hacia abajo/hacia el lado. Es más práctico para matrices de tamaño moderado y situaciones donde la lista necesita permanecer activa y receptiva a los cambios en los datos de origen.
Supongamos que su rango de datos es el siguiente:
- Las etiquetas de fila están en las celdas A2:A10.
- Las etiquetas de columna están en las celdas B1:J1.
- Los valores de la matriz están en las celdas B2:J10.
1. Cree una nueva hoja de trabajo o comience en un área vacía de su hoja existente. En la celda L2, ingrese la siguiente fórmula para extraer la etiqueta de fila:
=INDEX($A$2:$A$10,INT((ROW(A1)-1)/COUNTA($B$1:$J$1))+1)2. En la celda M2, ingrese esta fórmula para extraer la etiqueta de columna correspondiente:
=INDEX($B$1:$J$1,MOD(ROW(A1)-1,COUNTA($B$1:$J$1))+1)3. En la celda N2, extraiga el valor de la matriz con:
=INDEX($B$2:$J$10,INT((ROW(A1)-1)/COUNTA($B$1:$J$1))+1,MOD(ROW(A1)-1,COUNTA($B$1:$J$1))+1)4. Seleccione las celdas L2:N2 y arrastre el controlador de relleno hacia abajo. El punto de parada debería ser número de filas × número de columnas (en esta muestra, son 9 filas × 9 columnas = 81 filas en total).
✅ Consejos:
- Ajuste todos los rangos para que se ajusten a su estructura de datos real.
- Use
SIyESBLANCOpara filtrar filas vacías si es necesario. - Para un comportamiento de expansión automática, considere usar
DESREFo rangos nombrados dinámicos. - Este método es más práctico para un tamaño de matriz estática y un volumen de datos moderado.
ℹ️ Notas adicionales:
- Ventajas: Las fórmulas permanecen activas y reflejan automáticamente los cambios en la matriz. No se requieren VBA ni complementos.
- Desventajas: Puede ralentizar el rendimiento del libro para matrices grandes. Requiere una configuración cuidadosa.
Demo: Convertir tabla de estilo matricial en lista con Kutools para Excel
Las mejores herramientas de productividad para Office
Mejora tu dominio de Excel con Kutools para Excel y experimenta una eficiencia sin precedentes. Kutools para Excel ofrece más de300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haz clic aquí para obtener la función que más necesitas...
Office Tab incorpora la interfaz de pestañas en Office y facilita mucho tu trabajo
- Habilita la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abre y crea varios documentos en nuevas pestañas de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumenta tu productividad en un50% y reduce cientos de clics de ratón cada día!
Todos los complementos de Kutools. Un solo instalador
El paquete Kutools for Office agrupa complementos para Excel, Word, Outlook y PowerPoint junto con Office Tab Pro, ideal para equipos que trabajan en varias aplicaciones de Office.
- Suite todo en uno: complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un solo instalador, una licencia: configuración en minutos (compatible con MSI)
- Mejor juntos: productividad optimizada en todas las aplicaciones de Office
- Prueba completa de30 días: sin registro ni tarjeta de crédito
- La mejor relación calidad-precio: ahorra en comparación con la compra individual de complementos