Función de transposición en Excel

De cando en vez, un usuario de Excel pode ter a tarefa de converter unha serie de datos que teñen unha estrutura horizontal nunha vertical. Este proceso chámase transposición. Esta palabra é nova para a maioría da xente, porque no traballo normal de PC non tes que usar esta operación. Non obstante, aqueles que teñen que traballar con grandes cantidades de datos precisan saber como facelo. Hoxe falaremos con máis detalle sobre como realizalo, con que función e tamén analizaremos algúns outros métodos en detalle.

Función TRANSPOSE: transpón intervalos de celas en Excel

Un dos métodos de transposición de táboas máis interesantes e funcionais en Excel é a función TRANSP. Coa súa axuda, pode converter un intervalo de datos horizontal nun vertical ou realizar a operación inversa. Imos ver como traballar con el.

Sintaxe da función

A sintaxe desta función é incriblemente sinxela: TRANSPOSE (matriz). É dicir, necesitamos usar só un argumento, que é un conxunto de datos que hai que converter nunha vista horizontal ou vertical, dependendo do que fose orixinalmente.

Transposición de intervalos verticais de celas (columnas)

Supoñamos que temos unha columna co rango B2:B6. Poden conter tanto valores prefabricados como fórmulas que devolven resultados a estas celas. Non é tan importante para nós, a transposición é posible en ambos os casos. Despois de aplicar esta función, a lonxitude da fila será a mesma que a lonxitude da columna do intervalo orixinal.

A secuencia de pasos para usar esta fórmula é a seguinte:

1. Seleccione unha liña. No noso caso, ten unha lonxitude de cinco celas.
2. Despois diso, move o cursor á barra de fórmulas e introduce a fórmula alí =TRANSSP(B2:B6).
3. Prema a combinación de teclas Ctrl + Maiús + Intro.

Por suposto, no seu caso, cómpre especificar o rango que é típico para a súa táboa.

Transposición de intervalos de celas horizontais (filas)

En principio, o mecanismo de acción é case o mesmo que o parágrafo anterior. Supoñamos que temos unha cadea con coordenadas inicial e final B10:F10. Tamén pode conter directamente valores e fórmulas. Fagamos dela unha columna, que terá as mesmas dimensións que a fila orixinal. A secuencia de accións é a seguinte:

1. Seleccione esta columna co rato. Tamén pode usar as teclas do teclado Ctrl e a frecha cara abaixo, despois de facer clic na cela superior desta columna.
2. Despois diso escribimos a fórmula =TRANSSP(B10:F10) na barra de fórmulas.
3. Escribimos como unha fórmula matricial usando a combinación de teclas Ctrl + Maiús + Intro.

Transposición con Pegar Especial

Outra opción posible de transposición é utilizar a función Pegar especial. Este xa non é un operador para usar en fórmulas, pero tamén é un dos métodos populares para converter columnas en filas e viceversa.

Esta opción está na pestana Inicio. Para acceder a el, cómpre atopar o grupo "Portapapeis" e alí atopar o botón "Pegar". Despois diso, abra o menú que se atopa baixo esta opción e seleccione o elemento "Transpoñer". Antes diso, cómpre seleccionar o intervalo que quere seleccionar. Como resultado, obteremos o mesmo rango, só se reflicte o contrario.

3 xeitos de transpoñer unha táboa en Excel

Pero, de feito, hai moitas máis formas de converter columnas en filas e viceversa. Imos describir 3 métodos polos que podemos transpoñer unha táboa en Excel. Comentamos dous deles anteriormente, pero daremos algúns exemplos máis para que teñas unha mellor idea de como levar a cabo este procedemento.

Método 1: Pegar especial

Este método é o máis sinxelo. Basta con premer un par de botóns e o usuario recibe unha versión transposta da táboa. Poñamos un pequeno exemplo para unha maior claridade. Supoñamos que temos unha táboa que contén información sobre cantos produtos hai actualmente en stock, así como canto custan en total. A mesa en si ten este aspecto.

Vemos que temos unha cabeceira e unha columna cos números de produtos. No noso exemplo, a cabeceira contén información sobre que produto, canto custa, canto está en stock e cal é o custo total de todos os produtos relacionados con este artigo que están en stock. Obtemos o custo segundo a fórmula onde o custo se multiplica pola cantidade. Para facer o exemplo máis visual, poñamos a cabeceira en verde.

A nosa tarefa é asegurarnos de que a información contida na táboa estea situada horizontalmente. É dicir, para que as columnas se convertan en filas. A secuencia de accións no noso caso será a seguinte:

1. Seleccione o intervalo de datos que necesitamos xirar. Despois diso, copiamos estes datos.
2. Coloca o cursor en calquera lugar da folla. A continuación, faga clic co botón dereito do rato e abra o menú contextual.
3. A continuación, fai clic no botón "Pegar especial".

Despois de completar estes pasos, cómpre facer clic no botón "Transpoñer". En vez diso, marque a caixa ao lado deste elemento. Non cambiamos outras opcións e, a continuación, fai clic no botón "Aceptar".

Despois de realizar estes pasos, quedamos coa mesma táboa, só as súas filas e columnas están dispostas de forma diferente. Teña en conta tamén que as celas que conteñen a mesma información están resaltadas en verde. Pregunta: que pasou coas fórmulas que estaban no rango orixinal? A súa localización cambiou, pero eles mesmos permaneceron. Os enderezos das celas simplemente cambiaron aos que se formaron despois da transposición.

Hai que realizar case as mesmas accións para traspoñer valores, non fórmulas. Neste caso, tamén cómpre usar o menú Pegar especial, pero antes diso, seleccione o intervalo de datos que contén os valores. Vemos que a xanela Pegar especial pódese chamar de dúas formas: a través dun menú especial na cinta ou o menú contextual.

Método 2. Función TRANSP en Excel

De feito, este método xa non se usa tan activamente como o era ao comezo da aparición deste programa de follas de cálculo. Isto débese a que este método é moito máis complicado que usar Paste Special. Non obstante, atopa o seu uso na automatización da transposición de táboas.

Ademais, esta función está en Excel, polo que é imprescindible coñecela, aínda que xa non se usa case. Antes consideramos o procedemento, como traballar con el. Agora completaremos este coñecemento cun exemplo adicional.

1. En primeiro lugar, debemos seleccionar o intervalo de datos que se utilizará para transpoñer a táboa. Só tes que seleccionar a zona pola contra. Por exemplo, neste exemplo temos 4 columnas e 6 filas. Polo tanto, é necesario seleccionar unha zona con características opostas: 6 columnas e 4 filas. A imaxe móstrao moi ben.
2. Despois diso, comezamos inmediatamente a encher esta cela. É importante non eliminar accidentalmente a selección. Polo tanto, debes especificar a fórmula directamente na barra de fórmulas.
3. A continuación, prema a combinación de teclas Ctrl + Maiús + Intro. Lembre que esta é unha fórmula matricial, xa que estamos a traballar cun gran conxunto de datos á vez, que se transferirán a outro gran conxunto de celas.

Despois de introducir os datos, prememos a tecla Intro, despois de que obtemos o seguinte resultado.

Vemos que a fórmula non foi trasladada á nova mesa. Tamén se perdeu o formato. Poeto

Todo isto terá que facerse manualmente. Ademais, lembra que esta táboa está relacionada coa orixinal. Polo tanto, en canto se modifica algunha información no rango orixinal, estes axustes realízanse automaticamente na táboa transposta.

Polo tanto, este método é moi axeitado nos casos nos que precisa asegurarse de que a táboa transposta estea ligada á orixinal. Se usas unha inserción especial, esta posibilidade deixará de existir.

táboa resumo

Este é un método fundamentalmente novo, que permite non só traspoñer a táboa, senón tamén levar a cabo un gran número de accións. É certo que o mecanismo de transposición será algo diferente en comparación cos métodos anteriores. A secuencia de accións é a seguinte:

1. Imos facer unha táboa dinámica. Para iso, cómpre seleccionar a táboa que necesitamos traspoñer. Despois diso, vai ao elemento "Inserir" e busca alí "Táboa dinámica". Aparecerá unha caixa de diálogo como a desta captura de pantalla.
2. Aquí pode reasignar o intervalo desde o que se fará, así como facer outras opcións. Agora estamos interesados ​​principalmente no lugar da táboa dinámica: nunha nova folla.
3. Despois diso, o deseño da táboa dinámica crearase automaticamente. Cómpre marcar nel aqueles elementos que utilizaremos, e logo trasladalos ao lugar correcto. No noso caso, necesitamos mover o elemento "Produto" a "Nomes das columnas" e "Prezo por peza" a "Valores".
4. Despois diso, finalmente crearase a táboa dinámica. Unha bonificación adicional é o cálculo automático do valor final.
5. Tamén podes cambiar outras opcións. Por exemplo, desmarque o elemento "Prezo por peza" e marque o elemento "Custo total". Como resultado, teremos unha táboa que contén información sobre o custo dos produtos. Este método de transposición é moito máis funcional que os outros. Imos describir algúns dos beneficios das táboas dinámicas:

1. Automatización. Coa axuda de táboas dinámicas, pode resumir os datos automaticamente, así como cambiar a posición de columnas e columnas de forma arbitraria. Para iso, non precisa realizar ningún paso adicional.
2. Interactividade. O usuario pode cambiar a estrutura da información tantas veces como necesite para realizar as súas tarefas. Por exemplo, pode cambiar a orde das columnas, así como agrupar datos de forma arbitraria. Isto pódese facer tantas veces como o necesite o usuario. E, literalmente, leva menos dun minuto.
3. Fácil de formatar datos. É moi sinxelo organizar unha táboa dinámica do xeito que quere unha persoa. Para iso, só tes que facer algúns clics do rato.
4. Obtención de valores. O gran número de fórmulas que se usan para crear informes sitúanse na accesibilidade directa dunha persoa e son fáciles de integrar nunha táboa dinámica. Estes son datos como a suma, obter a media aritmética, determinar o número de celas, multiplicar, atopar os valores máis grandes e máis pequenos na mostra especificada.
5. Capacidade para crear gráficos de resumo. Se as táboas dinámicas se recalculan, os seus gráficos asociados actualízanse automaticamente. É posible crear tantos gráficos como necesites. Todos eles pódense cambiar para unha tarefa específica e non estarán interconectados.
6. Capacidade de filtrar datos.
7. É posible construír unha táboa dinámica baseada en máis dun conxunto de información fonte. Polo tanto, a súa funcionalidade será aínda maior.

É certo que ao usar táboas dinámicas, hai que ter en conta as seguintes restricións:

1. Non se pode usar toda a información para xerar táboas dinámicas. Antes de que se poidan usar para este fin, as células deben normalizarse. En palabras sinxelas, acerta. Requisitos obrigatorios: presenza dunha liña de cabeceira, plenitude de todas as liñas, igualdade de formatos de datos.
2. Os datos actualízanse de forma semiautomática. Para obter nova información na táboa dinámica, debes facer clic nun botón especial.
3. As táboas dinámicas ocupan moito espazo. Isto pode provocar algunha interrupción do ordenador. Ademais, o ficheiro será difícil de enviar por correo electrónico por iso.

Ademais, despois de crear unha táboa dinámica, o usuario non ten a posibilidade de engadir nova información.