Mira el vídeo – Cómo transponer datos en Excel
Si tienes un conjunto de datos y quieres transponerlos en Excel (lo que significa convertir filas en columnas y columnas en filas), hacerlo manualmente es un completo NO.
Transponer datos usando Pegado Especial
El Pegado Especial puede hacer un montón de cosas increíbles, y una de ellas es transponer datos en Excel.
Suponga que tiene un conjunto de datos como el que se muestra a continuación:
Estos datos tienen las regiones en una columna y los cuartos en una fila.
Ahora, por alguna razón, si necesitas transponer estos datos, aquí es cómo puedes hacerlo usando el especial de pegado:
- Selecciona el conjunto de datos (en este caso A1:E5).
- Copie el conjunto de datos (Control + C) o haga clic con el botón derecho y seleccione copiar.
- Ahora puede pegar los datos transpuestos en una nueva ubicación. En este ejemplo, quiero copiar en G1:K5, así que haz clic con el botón derecho del ratón en la celda G1 y selecciona pegar especial.
- En el cuadro de diálogo pegar especial, marca la opción de transponer en la parte inferior derecha.
- Haga clic en Aceptar.
- Seleccione el conjunto de datos (A1:E5).
- Cópialo (Control + C, o haz clic con el botón derecho y selecciona copiar).
- Ahora puedes pegar los datos transpuestos en una nueva ubicación. En este ejemplo, quiero copiar en G1:K5, así que haz clic con el botón derecho del ratón en la celda G1 y selecciona pegar especial.
- En el cuadro de diálogo Pegar especial, haz clic en el botón Pegar enlace. Esto le dará el mismo conjunto de datos, pero aquí las celdas están vinculadas al conjunto de datos original (por ejemplo, G1 está vinculado a A1, y G2 está vinculado a A2, y así sucesivamente).
- Con estos nuevos datos copiados seleccionados, Pulsa Control + H (o ve a Inicio -> Edición -> Buscar & Seleccionar -> Reemplazar). Esto abrirá el cuadro de diálogo Buscar & Reemplazar.
- En el cuadro de diálogo Buscar & Reemplazar, utilice lo siguiente:
- En Buscar qué: =
- En Reemplazar con: !@# (tenga en cuenta que estoy usando !@# ya que es una combinación única de caracteres que es poco probable que sea una parte de sus datos. Puede utilizar cualquier conjunto único de caracteres de este tipo).
- Haga clic en Reemplazar todo. Esto reemplazará el igual a de la fórmula y tendrá !@# seguido de la referencia de la celda en cada celda.
- Haga clic con el botón derecho y copie el conjunto de datos (o utilice Control + C).
- Seleccione una nueva ubicación, haga clic con el botón derecho y seleccione pegar especial. En este ejemplo, lo estoy pegando en la celda G7. Puedes pegarlo donde quieras.
- En el cuadro de diálogo Pegar especial, selecciona Transponer y haz clic en Aceptar.
- Copia y pega estos datos transpuestos recién creados a la que se crea.
- Ahora abre de nuevo el cuadro de diálogo Buscar y Reemplazar y sustituye !@# por =.
- Seleccione las celdas donde desea transponer el conjunto de datos. Ten en cuenta que tienes que seleccionar el número exacto de celdas que los datos originales. Así, por ejemplo, si tienes 2 filas y 3 columnas, tienes que seleccionar 3 filas y 2 columnas de celdas donde quieras los datos transpuestos. En este caso, como hay 5 filas y 5 columnas, necesita seleccionar 5 filas y 5 columnas.
- Introduzca =TRANSPOSE(A1:E5) en la celda activa (que debe ser la celda superior izquierda de la selección y pulse Control Mayúsculas Intro.
- Es una función de matriz, por lo que debes usar Control-Mayúsculas-Intro y no sólo Intro.
- No puedes borrar una parte del resultado. Tiene que borrar toda la matriz de valores que devuelve la función TRANSPOSE.
- La función Transpose sólo copia los valores, no el formato.
- Seleccionar los datos e ir a Datos -> Obtener & Transformar -> Desde la Tabla.
- En el cuadro de diálogo Crear Tabla, asegúrese de que el rango es correcto y haga clic en Aceptar. Esto abrirá el cuadro de diálogo Editor de consultas.
- En el cuadro de diálogo Editor de consultas, seleccione la pestaña ‘Transformar’.
- En la pestaña Transformar, vaya a Tabla -> Usar la primera fila como cabecera -> Usar cabecera como primera fila. Esto asegura que la primera fila (que contiene encabezados: Q1, Q2, Q3 y Q4) también se tratan como datos y se transponen.
- Haga clic en Transponer. Esto transpondría instantáneamente los datos.
- Haga clic en Usar la primera fila como encabezado. Esto hará que la primera fila de los datos transpuestos se convierta en cabecera.
- Ir a Archivo -> Cerrar y cargar. Esto cerrará la ventana de Power Editor y creará una nueva hoja que contenga los datos transpuestos.
- Multiplicar en Excel usando el Pegado Especial.
- CONCATENAR Rango de Excel (con y sin separador).
- Limpiar Datos en Hojas de Cálculo de Excel.
- Comparar Columnas en Excel
- Texto de Excel a Columnas
- Función TRIM de Excel
- Fusionar Tablas en Excel
Esto copiaría y pegaría instantáneamente los datos pero de forma que se hayan transpuesto. A continuación hay una demostración que muestra todo el proceso.
Los pasos mostrados arriba copian el valor, la fórmula (si la hay), así como el formato. Si sólo desea copiar el valor, seleccione ‘valor’ en el cuadro de diálogo especial de pegado.
Note que los datos copiados son estáticos, y si usted hace cualquier cambio en el conjunto de datos original, esos cambios no se reflejarían en los datos transpuestos.
Si desea que estas celdas transpuestas estén vinculadas a las celdas originales, puede combinar la potencia de Buscar & Reemplazar con Pegado especial.
Transponer datos utilizando Pegado especial y Buscar & Reemplazar
El uso de Pegado especial por sí solo le proporciona datos estáticos. Esto significa que si tus datos originales cambian y quieres que los datos transpuestos se actualicen también, entonces tienes que usar Pegado Especial de nuevo para transponerlos.
Aquí tienes un truco genial que puedes usar para transponer los datos y seguir teniendo un enlace con las celdas originales.
Suponga que tiene un conjunto de datos como el que se muestra a continuación:
Aquí están los pasos para transponer los datos pero mantener los enlaces intactos:
Esto te dará unos datos enlazados que han sido transpuestos. Si realizas algún cambio en el conjunto de datos original, los datos transpuestos se actualizarían automáticamente.
Nota: Como nuestros datos originales tienen A1 como vacío, tendrías que eliminar manualmente el 0 en G1. El 0 aparece cuando pegamos enlaces, ya que un enlace a una celda vacía seguiría devolviendo un 0. Además, necesitarías formatear el nuevo conjunto de datos (puedes simplemente copiar y pegar el formato sólo del conjunto de datos original).
Transponer datos utilizando la función TRANSPOSE de Excel
La función TRANSPOSE de Excel – como su nombre indica – puede utilizarse para transponer datos en Excel.
Suponga que tiene un conjunto de datos como el que se muestra a continuación:
Aquí tiene los pasos para transponerlo:
Esto transpondría el conjunto de datos.
Aquí hay algunas cosas que debes saber sobre la función TRANSPOSE:
Transponer datos usando Power Query
Power Query es una potente herramienta que le permite transponer datos rápidamente en Excel.
Power Query forma parte de Excel 2016 (Consigue & Transformar en la pestaña Datos) pero si estás usando Excel 2013 o 2010, entonces necesitas instalarlo como un complemento.
Suponga que tiene el conjunto de datos que se muestra a continuación:
Aquí tiene los pasos para transponer estos datos utilizando Power Query:
En Excel 2016
Note que como la celda superior izquierda de nuestro conjunto de datos estaba vacía, obtiene un nombre genérico Columna1 (como se muestra a continuación). Puede eliminar esta celda de los datos transpuestos.
En Excel 2013/2010
En Excel 2013/10, necesita instalar Power Query como complemento.
Haga clic aquí para descargar el complemento y obtener instrucciones de instalación.
Una vez que haya instalado Power Query, vaya a Power Query -> Datos de Excel -> Desde la tabla.
Esto abrirá el cuadro de diálogo de creación de tabla. Ahora sigue los mismos pasos que se muestran para Excel 2016.
También te pueden gustar los siguientes tutoriales de Excel:
.