¿Cómo convertir en Excel una tabla con formato de matriz en tres columnas?
Al trabajar con datos en Excel, es frecuente encontrarse con tablas con formato de matriz que presentan información en una cuadrícula, donde tanto las filas como las columnas actúan como encabezados. Aunque este formato resulta visualmente útil para ciertos análisis, puede necesitar transformar dicha matriz en una «lista» o tabla de tres columnas para tareas como la importación a bases de datos, normalización de datos, creación de gráficos o análisis avanzado. Convertir la matriz en una tabla de tres Columnas (a veces denominada datos «despivotados») facilita el filtrado, la agregación y la integración con otras herramientas de datos. A continuación, se muestra un ejemplo que ilustra esta transformación:
➤ Convierta una tabla con formato de matriz en lista con una tabla dinámica
➤ Convierta una tabla con formato de matriz en lista con código VBA
➤ Convierta una tabla con formato de matriz en lista con Kutools para Excel
➤ Convierta una tabla con formato de matriz en lista con fórmulas de Excel
Convertir una tabla con formato de matriz en lista mediante una tabla dinámica
En Excel no existe un comando integrado directo para convertir una tabla con formato de matriz en una tabla de tres columnas. Sin embargo, utilizando el Asistente para tablas dinámicas, puedes transformar eficazmente una matriz cruzada en datos planos y tabulares, listos para análisis posteriores. Este enfoque es ideal para conjuntos de datos pequeños o medianos y especialmente útil cuando necesitas aplanar una estructura compleja de informes. No obstante, no resulta tan adecuado para grandes volúmenes de datos ni para usuarios poco familiarizados con las operaciones de tablas dinámicas.
1. Abra la hoja que contiene su matriz. Pulse Alt + D, luego P para abrir el Asistente para tablas y gráficos dinámicos. En el asistente:
- En ¿Dónde están los datos que desea analizar?, elija Varios rangos de consolidación.
- En ¿Qué tipo de informe desea crear?, seleccione Tabla dinámica.
2. Haga clic en Siguiente. En el cuadro de diálogo Paso 2a de 3, seleccione Crearé los campos de página yo mismo:
3. Haga clic en Siguiente. En Paso 2b de 3, haga clic en el botón 
y seleccione todo el rango de datos de la matriz, incluidos los encabezados de fila y columna. Haga clic en Agregar para insertar el rango en la lista Todos los rangos. Confirme que su selección abarca toda la matriz.
4. Haga clic en Siguiente. En el Paso 3 de 3, elija si desea colocar la tabla dinámica en una hoja de cálculo nueva o en una celda específica:
5. Haga clic en Finalizar. Excel generará una tabla dinámica que resume la matriz. De forma predeterminada, muestra totales agregados en las intersecciones. No necesita ajustar la estructura de la tabla dinámica para esta tarea; simplemente continúe con el siguiente paso:
6. Haga doble clic en la celda donde se cruzan las etiquetas de fila y columna del Total general (por ejemplo, la celda F22). Excel creará una hoja nueva con una tabla de tres columnas, donde cada fila muestra una combinación única de encabezado de fila y encabezado de columna junto con su valor correspondiente.
7. Para terminar, seleccione la nueva tabla, haga clic con el botón derecho y elija Tabla > Convertir en rango. Así eliminará el formato de tabla y obtendrá una lista sencilla y editable:
Consejo: Si su matriz cambia con frecuencia, deberá repetir este proceso para actualizar la tabla de tres columnas. Este método es ideal para datos estáticos. Además, si su matriz contiene celdas en blanco o combinadas, puede ser necesario realizar una limpieza previa antes de aplicar esta técnica.
Convertir una tabla con formato de matriz en lista mediante código VBA
Si prefiere automatizar el proceso o necesita aplicar esta transformación de forma repetida, una macro VBA puede convertir rápidamente cualquier tabla con formato de matriz en una tabla estructurada de tres columnas. Este método resulta especialmente eficaz para grandes volúmenes de datos o diseños variados, y elimina la necesidad de formateo manual. Es ideal para usuarios familiarizados con la ejecución de scripts VBA.
1. Pulse 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. A continuación, 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. Pulse F5 o haga clic en Ejecutar para ejecutar la macro. Una serie de mensajes le guiará durante las selecciones requeridas:
Paso 1:Seleccione las etiquetas de columna(normalmente la Fila Superior de su matriz):
Paso 2:Seleccione las etiquetas de fila(normalmente la primera columna de su matriz):
Paso 3:Seleccione los datos reales de la matriz(excluyendo los Encabezados de fila y columna):
Paso 4: Elija la celda de destino donde debe comenzar la tabla de tres columnas convertida. Se recomienda utilizar una celda vacía o una hoja de cálculo nueva:
Paso 5: Haga clic en Aceptar. Su matriz se convertirá ahora en una tabla plana de tres columnas.
⚠️ Notas y consejos:
• Asegúrese de no incluir encabezados de columnas ni de filas en el rango de datos de la matriz.
• Si su matriz tiene celdas combinadas, desagréguelas antes de ejecutar la macro para evitar errores.
• Si detecta errores, revise nuevamente los rangos seleccionados y asegúrese de que estén correctamente alineados.
Convierta una tabla con formato de matriz en lista con Kutools para Excel
Aunque los métodos anteriores son eficaces, pueden resultar tediosos o intimidantes para usuarios menos experimentados. Si busca una solución rápida y fácil de usar, Kutools para Excel ofrece una utilidad específica denominada Convertir Dimensión de Tabla, diseñada especialmente para este propósito.
Esta herramienta es ideal para usuarios que convierten frecuentemente tablas con formato de matriz o necesitan realizar procesamiento por lotes. Conserva el formato original —como la fuente, el color de relleno y las fórmulas— cuando es necesario. Un posible inconveniente es que Kutools es un complemento de terceros que requiere instalación, pero constituye una opción potente para quienes reestructuran datos regularmente en Excel.
Pasos:
1. Tras instalar Kutools, vaya a la pestaña Kutools, haga clic en Rango y, a continuación, seleccione Convertir Dimensión de Tabla:
2. En el cuadro de diálogo Convertir Dimensión de Tabla:
- (1) Elija Convertir Tabla Bidimensional a Tabla Unidimensional en la sección Tipo de Conversión.
- (2) Haga clic en el botón
situado junto a Rango de origen para seleccionar su tabla de matriz. - (3) Haga clic en el botón situado junto a Rango de resultados para indicar dónde colocar la salida.
Asegúrese de seleccionar toda la matriz, incluidos tanto los encabezados como los datos, para evitar conversiones parciales o resultados incorrectos.
3. Haga clic en Aceptar. La matriz se convertirá inmediatamente en una tabla de tres columnas, conservando el formato original de las celdas siempre que sea posible:
Consejo: Esta función también admite la operación inversa: convertir una lista plana en una matriz bidimensional. Es ideal para reconstruir informes o preparar datos para análisis cruzados. Consulte: cómo convertir una lista en una matriz 2D Tabla Bidimensional.
➤ Más información sobre la función Convertir Dimensión de Tabla
⏬ ¡Descargue y pruebe Kutools para Excel gratis ahora!
Convierta una tabla con formato de matriz en lista con fórmulas de Excel
Si prefiere un enfoque basado en fórmulas —especialmente útil si desea que su tabla de tres columnas se actualice dinámicamente al modificar la matriz—, puede combinar las funciones ÍNDICE, FILA, COLUMNA y CONTARA para deshacer manualmente la estructura de los datos. Esta solución no requiere VBA ni complementos, lo que la hace ideal para quienes quieren evitar macros o herramientas externas. No obstante, exige prestar especial atención a las referencias de las fórmulas, ya que normalmente deben introducirse como fórmulas matriciales o copiarse sistemáticamente hacia abajo o lateralmente. Es especialmente práctica para matrices de tamaño moderado y en situaciones donde la lista debe permanecer activa y responder automáticamente a los cambios en los datos de origen.
Supongamos que su Rango de datos es la siguiente:
- Las etiquetas de fila se encuentran en las celdas A2:A10.
- Las etiquetas de columna se encuentran en las celdas B1:J1.
- Los valores de la matriz se encuentran en las celdas B2:J10.
1. Cree una hoja de cálculo nueva o comience en un área vacía de su hoja existente. En la celda L2, introduzca 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, introduce 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 detención debe ser número de filas × número de columnas (en este ejemplo, 9 filas × 9 columnas = 81 filas en total).
✅ Consejos:
- Ajuste todos los rangos para que se adapten perfectamente a la estructura real de sus datos.
- Utilice
SIyESBLANCOpara filtrar filas vacías si es necesario. - Para lograr un comportamiento de expansión automática, considere usar
DESPLAZAMIENTOo rangos con nombre dinámicos. - Este método resulta más práctico cuando el tamaño de la matriz es estático y el volumen de datos, moderado.
ℹ️ Notas adicionales:
- Ventajas: Las fórmulas permanecen activas y reflejan automáticamente los cambios en la matriz. No requieren VBA ni complementos.
- Inconvenientes: Puede ralentizar el rendimiento del libro con matrices grandes. Requiere una configuración cuidadosa.
Demostración: Convierta una tabla con formato de matriz en lista con Kutools para Excel
Las mejores herramientas de productividad para Office
Potencie sus habilidades en Excel con Kutools para Excel y experimente una eficiencia como nunca antes.Kutools para Excel ofrece más de 300 funciones avanzadas para aumentar su productividad y Ahorrar tiempo.Haga clic aquí para obtener la función que más necesita...
Office Tab aporta una interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Active la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas dentro de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumente su productividad en un 50 % y elimine cientos de clics del ratón cada día!
Todos los complementos de Kutools en un solo instalador.
Kutools for Office es la suite que incluye complementos para Excel, Word, Outlook y PowerPoint, además de Office Tab Pro, ideal para equipos que trabajan en distintas aplicaciones de Office.
- Suite integral— complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un instalador, una licencia— configuración en minutos (compatible con MSI)
- Rendimiento mejorado en conjunto— productividad optimizada en todas las aplicaciones de Office
- Prueba gratuita de 30 días con todas las funciones— sin registro ni tarjeta de crédito
- La mejor relación calidad-precio— ahorre frente a la compra individual de complementos