Función de transposición en Excel

De vez en cuando, un usuario de Excel puede tener la tarea de convertir un rango de datos que tiene una estructura horizontal en una vertical. Este proceso se llama transposición. Esta palabra es nueva para la mayoría de las personas, porque en el trabajo normal de la PC no es necesario utilizar esta operación. Sin embargo, aquellos que tienen que trabajar con grandes cantidades de datos necesitan saber cómo hacerlo. Hoy hablaremos con más detalle sobre cómo realizarlo, con qué función, y también veremos en detalle algunos otros métodos.

Función TRANSPONER: transponer rangos de celdas en Excel

Uno de los métodos de transposición de tablas más interesantes y funcionales en Excel es la función TRANSPORTE. Con su ayuda, puede convertir un rango de datos horizontal en uno vertical, o realizar la operación inversa. Veamos cómo trabajar con él.

Sintaxis de la función

La sintaxis de esta función es increíblemente simple: TRANSPONER(matriz). Es decir, necesitamos usar solo un argumento, que es un conjunto de datos que debe convertirse a una vista horizontal o vertical, dependiendo de lo que era originalmente.

Transposición de rangos verticales de celdas (columnas)

Supongamos que tenemos una columna con el rango B2:B6. Pueden contener tanto valores listos para usar como fórmulas que devuelven resultados a estas celdas. No es tan importante para nosotros, la transposición es posible en ambos casos. Después de aplicar esta función, la longitud de la fila será la misma que la longitud de la columna del rango original.

La secuencia de pasos para usar esta fórmula es la siguiente:

1. Seleccione una línea. En nuestro caso, tiene una longitud de cinco celdas.
2. Después de eso, mueva el cursor a la barra de fórmulas e ingrese la fórmula allí. =TRANSPORTE(B2:B6).
3. Presione la combinación de teclas Ctrl + Shift + Enter.

Naturalmente, en su caso, debe especificar el rango que es típico para su tabla.

Transposición de rangos de celdas horizontales (filas)

En principio, el mecanismo de acción es casi el mismo que el del párrafo anterior. Supongamos que tenemos una cadena con coordenadas de inicio y fin B10:F10. También puede contener tanto directamente valores como fórmulas. Hagamos una columna con él, que tendrá las mismas dimensiones que la fila original. La secuencia de acciones es la siguiente:

1. Seleccione esta columna con el ratón. También puede usar las teclas del teclado Ctrl y la flecha hacia abajo, después de hacer clic en la celda superior de esta columna.
2. Después de eso escribimos la fórmula =TRANSPORTE(B10:F10) en la barra de fórmulas.
3. Lo escribimos como una fórmula de matriz usando la combinación de teclas Ctrl + Shift + Enter.

Transposición con Pegado especial

Otra posible opción de transposición es utilizar la función Pegado especial. Este ya no es un operador para usar en fórmulas, sino que también es uno de los métodos populares para convertir columnas en filas y viceversa.

Esta opción está en la pestaña Inicio. Para acceder a él, debe buscar el grupo "Portapapeles" y allí encontrar el botón "Pegar". Después de eso, abra el menú que se encuentra debajo de esta opción y seleccione el elemento "Transponer". Antes de eso, debe seleccionar el rango que desea seleccionar. Como resultado, obtendremos el mismo rango, solo se refleja lo contrario.

3 formas de transponer una tabla en Excel

Pero, de hecho, hay muchas más formas de convertir columnas en filas y viceversa. Describamos 3 métodos por los cuales podemos transponer una tabla en Excel. Discutimos dos de ellos anteriormente, pero le daremos algunos ejemplos más para que tenga una mejor idea de uXNUMXbuXNUMXbcómo llevar a cabo este procedimiento.

Método 1: Pegado especial

Este método es el más simple. Basta con presionar un par de botones y el usuario recibe una versión transpuesta de la tabla. Pongamos un pequeño ejemplo para mayor claridad. Supongamos que tenemos una tabla que contiene información sobre cuántos productos hay actualmente en stock, así como cuánto cuestan en total. La mesa en sí se ve así.

Vemos que tenemos un encabezado y una columna con números de producto. En nuestro ejemplo, el encabezado contiene información sobre qué producto, cuánto cuesta, cuánto hay en stock y cuál es el costo total de todos los productos relacionados con este artículo que están en stock. Obtenemos el costo de acuerdo con la fórmula donde el costo se multiplica por la cantidad. Para que el ejemplo sea más visual, hagamos que el encabezado sea verde.

Nuestra tarea es asegurarnos de que la información contenida en la tabla esté ubicada horizontalmente. Es decir, para que las columnas se conviertan en filas. La secuencia de acciones en nuestro caso será la siguiente:

1. Seleccione el rango de datos que necesitamos rotar. Después de eso, copiamos estos datos.
2. Coloque el cursor en cualquier lugar de la hoja. Luego haga clic en el botón derecho del mouse y abra el menú contextual.
3. Luego haga clic en el botón "Pegar especial".

Después de completar estos pasos, debe hacer clic en el botón "Transponer". Más bien, marque la casilla junto a este elemento. No cambiamos otras configuraciones, y luego hacemos clic en el botón "Aceptar".

Después de realizar estos pasos, nos quedamos con la misma tabla, solo que sus filas y columnas están dispuestas de manera diferente. También tenga en cuenta que las celdas que contienen la misma información están resaltadas en verde. Pregunta: ¿Qué pasó con las fórmulas que estaban en el rango original? Su ubicación ha cambiado, pero ellos mismos se han mantenido. Las direcciones de las celdas simplemente cambiaron a las que se formaron después de la transposición.

Es necesario realizar casi las mismas acciones para transponer valores, no fórmulas. En este caso, también debe usar el menú Pegado especial, pero antes de eso, seleccione el rango de datos que contiene los valores. Vemos que la ventana Pegado especial se puede llamar de dos maneras: a través de un menú especial en la cinta o el menú contextual.

Método 2. Función TRANSP en Excel

De hecho, este método ya no se usa tan activamente como al comienzo de la aparición de este programa de hoja de cálculo. Esto se debe a que este método es mucho más complicado que usar Pegado especial. Sin embargo, encuentra su uso en la automatización de la transposición de tablas.

Además, esta función está en Excel, por lo que es imprescindible conocerla, aunque ya casi no se usa. Anteriormente consideramos el procedimiento, cómo trabajar con él. Ahora complementaremos este conocimiento con un ejemplo adicional.

1. Primero, debemos seleccionar el rango de datos que se usará para transponer la tabla. Solo necesita seleccionar el área por el contrario. Por ejemplo, en este ejemplo tenemos 4 columnas y 6 filas. Por lo tanto, es necesario seleccionar un área con características opuestas: 6 columnas y 4 filas. La imagen lo muestra muy bien.
2. Después de eso, inmediatamente comenzamos a completar esta celda. Es importante no eliminar accidentalmente la selección. Por lo tanto, debe especificar la fórmula directamente en la barra de fórmulas.
3. A continuación, presione la combinación de teclas Ctrl + Shift + Enter. Recuerde que esta es una fórmula matricial, ya que estamos trabajando con un gran conjunto de datos a la vez, que se transferirán a otro gran conjunto de celdas.

Después de ingresar los datos, presionamos la tecla Enter, luego de lo cual obtenemos el siguiente resultado.

Vemos que la fórmula no se transfirió a la nueva tabla. También se perdió el formateo. poeta

Todo esto tendrá que hacerse manualmente. Además, recuerda que esta tabla está relacionada con la original. Por lo tanto, tan pronto como se cambia alguna información en el rango original, estos ajustes se realizan automáticamente en la tabla transpuesta.

Por lo tanto, este método es adecuado en los casos en los que necesita asegurarse de que la tabla transpuesta esté vinculada a la original. Si utiliza un inserto especial, esta posibilidad ya no existirá.

tabla resumen

Este es un método fundamentalmente nuevo, que hace posible no solo transponer la tabla, sino también llevar a cabo una gran cantidad de acciones. Es cierto que el mecanismo de transposición será algo diferente en comparación con los métodos anteriores. La secuencia de acciones es la siguiente:

1. Hagamos una tabla dinámica. Para hacer esto, debe seleccionar la tabla que necesitamos transponer. Después de eso, vaya al elemento "Insertar" y busque "Tabla dinámica" allí. Aparecerá un cuadro de diálogo como el de esta captura de pantalla.
2. Aquí puede reasignar el rango a partir del cual se realizará, así como realizar una serie de otras configuraciones. Ahora estamos interesados ​​principalmente en el lugar de la tabla dinámica, en una hoja nueva.
3. Después de eso, el diseño de la tabla dinámica se creará automáticamente. Es necesario marcar en él aquellos elementos que usaremos, y luego deben moverse al lugar correcto. En nuestro caso, debemos mover el elemento "Producto" a "Nombres de columna" y "Precio por pieza" a "Valores".
4. Después de eso, finalmente se creará la tabla dinámica. Una bonificación adicional es el cálculo automático del valor final.
5. También puede cambiar otras configuraciones. Por ejemplo, desmarque el ítem “Precio por pieza” y marque el ítem “Costo total”. Como resultado, tendremos una tabla que contiene información sobre cuánto cuestan los productos. Este método de transposición es mucho más funcional que los demás. Describamos algunos de los beneficios de las tablas dinámicas:

1. Automatización. Con la ayuda de tablas dinámicas, puede resumir datos automáticamente, así como cambiar la posición de columnas y columnas arbitrariamente. Para hacer esto, no necesita realizar ningún paso adicional.
2. Interactividad. El usuario puede cambiar la estructura de la información tantas veces como necesite para realizar sus tareas. Por ejemplo, puede cambiar el orden de las columnas, así como agrupar datos de forma arbitraria. Esto se puede hacer tantas veces como el usuario necesite. Y literalmente toma menos de un minuto.
3. Datos fáciles de formatear. Es muy fácil organizar una mesa dinámica de la manera que una persona quiere. Para hacer esto, solo haga unos pocos clics del mouse.
4. Obtención de valores. La abrumadora cantidad de fórmulas que se utilizan para crear informes se encuentran en la accesibilidad directa de una persona y son fáciles de integrar en una tabla dinámica. Estos son datos como la suma, obtener la media aritmética, determinar el número de celdas, multiplicar, encontrar los valores más grandes y más pequeños en la muestra especificada.
5. Capacidad para crear gráficos de resumen. Si se vuelven a calcular las tablas dinámicas, sus gráficos asociados se actualizan automáticamente. Es posible crear tantos gráficos como necesite. Todos ellos se pueden cambiar para una tarea específica y no estarán interconectados.
6. Posibilidad de filtrar datos.
7. Es posible construir una tabla dinámica basada en más de un conjunto de información de origen. Por lo tanto, su funcionalidad será aún mayor.

Es cierto que al usar tablas dinámicas, se deben tener en cuenta las siguientes restricciones:

1. No toda la información se puede utilizar para generar tablas dinámicas. Antes de que puedan usarse para este propósito, las células deben normalizarse. En palabras simples, hazlo bien. Requisitos obligatorios: presencia de una línea de encabezado, plenitud de todas las líneas, igualdad de formatos de datos.
2. Los datos se actualizan semiautomáticamente. Para obtener nueva información en la tabla dinámica, debe hacer clic en un botón especial.
3. Las tablas dinámicas ocupan mucho espacio. Esto puede conducir a alguna interrupción de la computadora. Además, el archivo será difícil de enviar por correo electrónico debido a esto.

Además, después de crear una tabla dinámica, el usuario no tiene la posibilidad de agregar nueva información.