5 formas de transponer datos en Excel (Tutorial paso a paso)
Transponer datos significa cambiar la orientación de una matriz convirtiendo sus filas en columnas o viceversa. En este tutorial, compartiremos contigo cinco métodos diferentes para cambiar la orientación de una matriz y lograr el resultado deseado.
Video: Transponer datos en Excel
Cambiar columnas a filas con Pegado Especial
Paso 1: Copia el rango que se va a transponer
Selecciona el rango de celdas donde quieres cambiar filas y columnas, y luego presiona "Ctrl" + "C" para copiar el rango.
Paso 2: Selecciona la opción Pegar Transponer
Haz clic derecho en la primera celda del rango de destino, y luego haz clic en el ícono "Transponer" (bajo "Opciones de Pegado") en el menú emergente.
Resultado
¡Voilà! ¡El rango se transpone en poco tiempo!
(AD) Cambia fácilmente las dimensiones de la tabla con Kutools
Convertir una tabla cruzada (tabla bidimensional) en una lista plana (lista unidimensional), o viceversa, en Excel siempre toma mucho tiempo y esfuerzo. Sin embargo, con Kutools para Excel instalado, su herramienta "Transformar Dimensiones de Tabla" te ayudará a hacer la conversión rápidamente y sin complicaciones.
"Kutools para Excel" - Incluye más de 300 funciones útiles, facilitando enormemente tu trabajo. ¡Obtén una prueba gratuita completa de 30 días ahora!
Transponer y vincular datos a la fuente
Para cambiar dinámicamente la orientación de un rango (cambiar columnas por filas y viceversa) y conectar el resultado cambiado al conjunto de datos original, puedes usar cualquiera de los siguientes enfoques:
- "Función TRANSPONER" (Fácil de usar pero los resultados no son editables)
- "Funciones INDIRECTO, DIRECCIÓN, COLUMNA y FILA" (Fórmula extensa pero permite realizar cambios en los resultados)
- "Pegado Especial y Buscar y Reemplazar" (Complejo pero fácil de entender)
- "Power Query" (Sencillo con resultados formateados en una tabla)
Cambiar filas a columnas usando la función TRANSPONER
Para convertir filas en columnas y viceversa con la función "TRANSPONER", sigue los pasos a continuación.
Paso 1: Selecciona la misma cantidad de celdas en blanco que el conjunto original de celdas, pero en dirección opuesta
Consejo: Omita este paso si está utilizando Excel 365 o Excel 2021.
Supongamos que tu tabla original está en el rango A1:C4, lo que significa que la tabla tiene 4 filas y 3 columnas. Entonces, la tabla transpuesta tendrá 3 filas y 4 columnas. Esto significa que debes seleccionar 3 filas y 4 columnas de celdas en blanco.
Paso 2: Introduce la fórmula TRANSPONER
Escribe la siguiente fórmula en la barra de fórmulas, y luego presiona "Ctrl" + "Shift" + "Enter" para obtener el resultado.
Consejo: Presiona "Enter" si eres usuario de Excel 365 o Excel 2021.
=TRANSPOSE(A1:C4)
- Debes cambiar A1:C4 por tu rango de origen real que deseas transponer.
- Si hay celdas vacías en el rango original, la función "TRANSPONER" convertirá las celdas vacías en 0s (ceros). Para eliminar los resultados en cero y mantener las celdas en blanco mientras se transpone, necesitas aprovechar la función "SI":
-
=TRANSPONER(SI(A1:C4="","",A1:C4))
Resultado
Las filas se convierten en columnas, y las columnas se transforman en filas.
Rotar datos usando las funciones INDIRECTO, DIRECCIÓN, COLUMNA y FILA
Aunque la fórmula anterior es bastante fácil de entender y usar, el inconveniente es que no puedes editar ni eliminar ninguna celda en la tabla rotada. Por eso, te presento una fórmula que utiliza las funciones "INDIRECTO", "DIRECCIÓN", "COLUMNA" y "FILA". Supongamos que tu tabla original está en el rango A1:C4, para realizar una transformación de columna a fila y mantener los datos rotados conectados al conjunto de datos de origen, sigue los pasos a continuación.
Paso 1: Ingresa la fórmula
Introduce la siguiente fórmula en la celda superior izquierda del rango de destino (A6 en nuestro caso), y presiona "Enter":
=INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))
- Debes cambiar A1 a la celda superior izquierda de tu rango de origen real que vas a transponer, y mantener los signos de dólar ($) tal como están. El signo de dólar ($) antes de la letra de la columna y el número de fila indica una referencia absoluta, lo que mantiene la letra de la columna y el número de fila inalterados cuando mueves o copias la fórmula a otras celdas.
- Si hay celdas vacías en el rango original, la fórmula convertirá las celdas vacías en 0s (ceros). Para eliminar los resultados en cero y mantener las celdas en blanco mientras se transpone, necesitas aprovechar la función "SI":
-
=SI(INDIRECTO(DIRECCIÓN(COLUMNA(A1)-COLUMNA($A$1)+FILA($A$1),FILA(A1)-FILA($A$1)+COLUMNA($A$1)))=0,"",INDIRECTO(DIRECCIÓN(COLUMNA(A1)-COLUMNA($A$1)+FILA($A$1),FILA(A1)-FILA($A$1)+COLUMNA($A$1))))
Paso 2: Copia la fórmula hacia las celdas derecha y abajo
Selecciona la celda con la fórmula, y arrastra su controlador de relleno (el pequeño cuadrado verde en la esquina inferior derecha de la celda) hacia abajo y luego hacia la derecha en tantas filas y columnas como sea necesario.
Resultado
Las columnas y filas se intercambian de inmediato.
Convertir columnas en filas con Pegado Especial y Buscar y Reemplazar
Algunos pasos adicionales con el método Pegado Especial junto con la función Buscar y Reemplazar te permiten transponer datos mientras vinculas las celdas de origen con las transpuestas.
Paso 1: Copia el rango que se va a transponer
Selecciona el rango de celdas que deseas transponer, y luego presiona "Ctrl" + "C" para copiar el rango.
Paso 2: Aplica la opción Pegar Vínculo
Haz clic derecho en una celda en blanco, y luego haz clic en "Pegado Especial".
Haz clic en el botón "Pegar Vínculo".
Obtendrás el siguiente resultado:
Paso 3: Busca y reemplaza los signos igual (=) del resultado de Pegar Vínculo
Selecciona el rango de resultados (A6:C9) y presiona "Ctrl" + "H", y luego reemplaza "=" con "@EO" (o cualquier carácter que no exista en el rango seleccionado) en el cuadro de diálogo Buscar y Reemplazar.
Haz clic en "Reemplazar Todo", y luego cierra el cuadro de diálogo. Lo siguiente muestra cómo quedará la información.
Paso 4: Transponer el resultado de Pegar Vínculo reemplazado
Selecciona el rango (A6:C9) y presiona "Ctrl" + "C" para copiarlo. Haz clic derecho en una celda en blanco (aquí seleccioné A11) y selecciona el ícono "Transponer" en las Opciones de Pegado para pegar el resultado transpuesto.
Paso 5: Recupera los signos igual (=) para vincular el resultado transpuesto con los datos originales
Mantén los datos transpuestos A11:D13 seleccionados, y luego presiona "Ctrl" + "H", y reemplaza "@EO" con "=" (lo contrario del "paso 3").
Haz clic en "Reemplazar Todo", y luego cierra el cuadro de diálogo.
Resultado
Los datos se han transpuesto y vinculado a las celdas originales.
Transponer y vincular datos a la fuente con Power Query
Power Query es una poderosa herramienta de automatización de datos que te permite transponer datos fácilmente en Excel. Puedes completar la tarea siguiendo los pasos a continuación:
Paso 1: Selecciona el rango que se va a transponer y abre el Editor de Power Query
Selecciona el rango de datos que se va a transponer. Luego, en la pestaña "Datos", en el grupo "Obtener y Transformar Datos", haz clic en "Desde Tabla/Rango".
- Si el rango de datos seleccionado no está en una tabla, aparecerá un cuadro de diálogo "Crear Tabla"; haz clic en "Aceptar" para crear una tabla.
- Si estás usando Excel 2013 o 2010 y no encuentras "Desde Tabla/Rango" en la pestaña "Datos", deberás descargar e instalarlo desde la página "Microsoft Power Query para Excel". Una vez instalado, ve a la pestaña "Power Query", haz clic en "Desde Tabla" en el grupo "Datos de Excel".
Paso 2: Convertir columnas en filas con Power Query
Ve a la pestaña "Transformar". En el menú desplegable "Usar Primera Fila como Encabezados", selecciona "Usar Encabezados como Primera Fila".
Haz clic en "Transponer".
Paso 3: Guarda los datos transpuestos en una hoja
En la pestaña "Archivo", haz clic en "Cerrar y Cargar" para cerrar la ventana del "Editor de Power Query" y crear una nueva hoja de trabajo para cargar los datos transpuestos.
Resultado
Los datos transpuestos se convierten en una tabla en una hoja de trabajo recién creada.
- Se generan encabezados de columna adicionales en la primera fila como se muestra arriba. Para promover la primera fila debajo de los encabezados a encabezados de columna, selecciona una celda dentro de los datos y haz clic en "Consulta" > "Editar". Luego, selecciona "Transformar" > "Usar Primera Fila Como Encabezados". Finalmente, selecciona "Inicio" > "Cerrar y Cargar".
- Si se realizan cambios en el conjunto de datos original, puedes actualizar los datos transpuestos anteriores con esos cambios haciendo clic en el ícono "Actualizar" junto a la tabla en el panel "Consultas y Conexiones", o haciendo clic en el botón "Actualizar" en la pestaña "Consulta".
(AD) Transforma rangos con Kutools en pocos clics
La utilidad "Transformar Rango" en Kutools para Excel puede ayudarte a transformar (convertir) fácilmente una columna vertical en varias columnas o viceversa, o convertir una fila en múltiples filas o viceversa.
"Kutools para Excel" - Incluye más de 300 funciones prácticas, facilitando enormemente tu trabajo. ¡Obtén una prueba gratuita completa de 30 días ahora!
Artículos relacionados
- Cómo transponer rangos rápidamente y omitir celdas en blanco en Excel?
- Cuando pegamos un rango como transpuesto, también se pegarán las celdas en blanco. Sin embargo, a veces solo queremos transponer los rangos ignorando las celdas en blanco como se muestra en la captura de pantalla a continuación. ¿Hay alguna manera de transponer rápidamente rangos y omitir espacios en blanco en Excel?
- Cómo transponer cada 5 o n filas de una columna a múltiples columnas?
- Supongamos que tienes datos largos en la columna A, y ahora quieres transponer cada 5 filas de la columna A a múltiples columnas, como transponer A1:A5 a C6:G6, A6:A10 a C7:G7, y así sucesivamente como se muestra en la siguiente captura de pantalla. ¿Cómo podrías manejar esta tarea sin copiar y pegar repetidamente en Excel?
- Cómo transponer / convertir columnas y filas en una sola columna?
- Cuando usas una hoja de trabajo de Excel, a veces te encontrarás con este problema: ¿cómo podrías convertir o transponer un rango de datos en una sola columna? (Ver las siguientes capturas de pantalla:) Ahora, te presento tres trucos rápidos para resolver este problema.
- Cómo transponer / convertir columnas y filas en una sola fila?
- ¿Cómo unir múltiples filas y columnas en una sola fila larga? Tal vez, te parezca fácil, porque puedes copiarlas una por una y unirlas en una fila manualmente. Sin embargo, esto puede ser muy lento y tedioso si hay cientos de filas y columnas. Aquí hablaré sobre algunos trucos rápidos para solucionarlo.
Las mejores herramientas de productividad para Office
Potencia tus habilidades en 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 lleva la interfaz de pestañas a 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 nuevas ventanas.
- ¡Aumenta tu productividad en un50% y reduce cientos de clics de ratón cada día!
Tabla de contenido
- Video: Transponer datos en Excel
- Cambiar columnas a filas con Pegado Especial
- Transponer y vincular datos a la fuente
- Usar la función TRANSPONER
- Usar las funciones INDIRECTO, DIRECCIÓN, COLUMNA y FILA
- Usar Pegado Especial y Buscar y Reemplazar
- Usar la función Power Query
- Artículos relacionados
- Las mejores herramientas de productividad para Office
- Comentarios