Funció de transposició a Excel

De tant en tant, un usuari d'Excel pot tenir la tasca de convertir una sèrie de dades que tenen una estructura horitzontal en una de vertical. Aquest procés s'anomena transposició. Aquesta paraula és nova per a la majoria de la gent, perquè en el treball normal d'ordinador no cal utilitzar aquesta operació. Tanmateix, aquells que han de treballar amb grans quantitats de dades han de saber com fer-ho. Avui parlarem amb més detall de com fer-ho, amb quina funció i també veurem alguns altres mètodes en detall.

Funció TRANSPOSE: transposa els intervals de cel·les a Excel

Un dels mètodes de transposició de taules més interessants i funcionals a Excel és la funció TRANSP. Amb la seva ajuda, podeu convertir un rang de dades horitzontal en un de vertical o realitzar l'operació inversa. Vegem com treballar-hi.

Sintaxi de la funció

La sintaxi d'aquesta funció és increïblement senzilla: TRANSPOSE (matriu). És a dir, només hem d'utilitzar un argument, que és un conjunt de dades que s'ha de convertir a una vista horitzontal o vertical, depenent del que fos originalment.

Transposició d'intervals verticals de cel·les (columnes)

Suposem que tenim una columna amb el rang B2:B6. Poden contenir tant valors ja fets com fórmules que retornen resultats a aquestes cel·les. No és tan important per a nosaltres, la transposició és possible en tots dos casos. Després d'aplicar aquesta funció, la longitud de la fila serà la mateixa que la longitud de la columna de l'interval original.

La seqüència de passos per utilitzar aquesta fórmula és la següent:

1. Seleccioneu una línia. En el nostre cas, té una longitud de cinc cel·les.
2. Després d'això, moveu el cursor a la barra de fórmules i introduïu-hi la fórmula =TRANSP(B2:B6).
3. Premeu la combinació de tecles Ctrl + Maj + Retorn.

Naturalment, en el vostre cas, heu d'especificar el rang que és típic de la vostra taula.

Transposició d'intervals de cel·les horitzontals (files)

En principi, el mecanisme d'acció és gairebé el mateix que el paràgraf anterior. Suposem que tenim una cadena amb les coordenades inicial i final B10:F10. També pot contenir tant directament valors com fórmules. Fem-ne una columna, que tindrà les mateixes dimensions que la fila original. La seqüència d'accions és la següent:

1. Seleccioneu aquesta columna amb el ratolí. També podeu utilitzar les tecles Ctrl del teclat i la fletxa cap avall, després de fer clic a la cel·la superior d'aquesta columna.
2. Després d'això, escrivim la fórmula =TRANSP(B10:F10) a la barra de fórmules.
3. Ho escrivim com una fórmula matricial utilitzant la combinació de tecles Ctrl + Maj + Enter.

Transposició amb Paste Special

Una altra opció de transposició possible és utilitzar la funció Enganxa especial. Aquest ja no és un operador que s'utilitza en fórmules, però també és un dels mètodes populars per convertir columnes en files i viceversa.

Aquesta opció es troba a la pestanya Inici. Per accedir-hi, heu de trobar el grup "Porta-retalls" i allà trobareu el botó "Enganxa". Després d'això, obriu el menú que es troba sota aquesta opció i seleccioneu l'element "Transposa". Abans d'això, heu de seleccionar l'interval que voleu seleccionar. Com a resultat, obtindrem el mateix rang, només es reflecteix el contrari.

3 maneres de transposar una taula a Excel

Però, de fet, hi ha moltes més maneres de convertir columnes en files i viceversa. Anem a descriure 3 mètodes mitjançant els quals podem transposar una taula a Excel. Hem comentat dos d'ells més amunt, però donarem uns quants exemples més perquè tingueu una millor idea de com dur a terme aquest procediment.

Mètode 1: Enganxa especial

Aquest mètode és el més senzill. N'hi ha prou amb prémer un parell de botons i l'usuari rep una versió transposada de la taula. Posem un petit exemple per a una major claredat. Suposem que tenim una taula que conté informació sobre quants productes hi ha actualment en estoc, així com quant costen en total. La taula en si es veu així.

Veiem que tenim una capçalera i una columna amb números de producte. En el nostre exemple, la capçalera conté informació sobre quin producte, quant costa, quant està en estoc i quin és el cost total de tots els productes relacionats amb aquest article que estan en estoc. Obtenim el cost segons la fórmula on el cost es multiplica per la quantitat. Per fer l'exemple més visual, fem la capçalera de color verd.

La nostra tasca és assegurar-nos que la informació continguda a la taula està ubicada horitzontalment. És a dir, perquè les columnes es converteixin en files. La seqüència d'accions en el nostre cas serà la següent:

1. Seleccioneu el rang de dades que necessitem per girar. Després d'això, copiem aquestes dades.
2. Col·loca el cursor a qualsevol lloc del full. A continuació, feu clic al botó dret del ratolí i obriu el menú contextual.
3. A continuació, feu clic al botó "Enganxa especial".

Després de completar aquests passos, heu de fer clic al botó "Transposa". Més aviat, marqueu la casella que hi ha al costat d'aquest element. No canviem cap altra configuració i, a continuació, feu clic al botó "D'acord".

Després de realitzar aquests passos, ens queda la mateixa taula, només les seves files i columnes estan disposades de manera diferent. Tingueu en compte també que les cel·les que contenen la mateixa informació es destaquen en verd. Pregunta: què va passar amb les fórmules que estaven en el rang original? La seva ubicació ha canviat, però ells mateixos s'han mantingut. Les adreces de les cel·les simplement van canviar a les que es van formar després de la transposició.

S'han de realitzar gairebé les mateixes accions per transposar valors, no fórmules. En aquest cas, també heu d'utilitzar el menú Enganxa especial, però abans, seleccioneu l'interval de dades que conté els valors. Veiem que la finestra Enganxa especial es pot cridar de dues maneres: mitjançant un menú especial a la cinta o el menú contextual.

Mètode 2. Funció TRANSP a Excel

De fet, aquest mètode ja no s'utilitza tan activament com ho era al principi de l'aparició d'aquest programa de fulls de càlcul. Això és degut a que aquest mètode és molt més complicat que utilitzar Paste Special. Tanmateix, troba el seu ús per automatitzar la transposició de taules.

A més, aquesta funció es troba a Excel, per la qual cosa és imprescindible conèixer-la, tot i que ja no s'utilitza gairebé. Abans vam considerar el procediment, com treballar-hi. Ara complementarem aquest coneixement amb un exemple addicional.

1. En primer lloc, hem de seleccionar l'interval de dades que s'utilitzarà per transposar la taula. Només cal seleccionar l'àrea al contrari. Per exemple, en aquest exemple tenim 4 columnes i 6 files. Per tant, cal seleccionar una zona amb característiques oposades: 6 columnes i 4 files. La imatge ho mostra molt bé.
2. Després d'això, immediatament comencem a omplir aquesta cel·la. És important no eliminar accidentalment la selecció. Per tant, heu d'especificar la fórmula directament a la barra de fórmules.
3. A continuació, premeu la combinació de tecles Ctrl + Maj + Retorn. Recordeu que aquesta és una fórmula matricial, ja que estem treballant amb un gran conjunt de dades alhora, que es transferiran a un altre gran conjunt de cel·les.

Després d'introduir les dades, premem la tecla Enter, després de la qual cosa obtenim el següent resultat.

Veiem que la fórmula no es va traslladar a la nova taula. També es va perdre el format. Poeta

Tot això s'haurà de fer manualment. A més, recordeu que aquesta taula està relacionada amb l'original. Per tant, tan aviat com es modifica alguna informació en l'interval original, aquests ajustos es fan automàticament a la taula transposada.

Per tant, aquest mètode és molt adequat en els casos en què cal assegurar-se que la taula transposada està vinculada a l'original. Si utilitzeu una inserció especial, aquesta possibilitat ja no serà.

taula resum

Aquest és un mètode fonamentalment nou, que permet no només transposar la taula, sinó també dur a terme un gran nombre d'accions. És cert que el mecanisme de transposició serà una mica diferent en comparació amb els mètodes anteriors. La seqüència d'accions és la següent:

1. Fem una taula dinàmica. Per fer-ho, cal seleccionar la taula que hem de transposar. Després d'això, aneu a l'element "Insereix" i cerqueu "Taula dinàmica" allà. Apareixerà un quadre de diàleg com el d'aquesta captura de pantalla.
2. Aquí podeu reassignar l'interval des del qual es farà, així com fer una sèrie d'altres configuracions. Ara ens interessa principalment el lloc de la taula dinàmica, en un full nou.
3. Després d'això, es crearà automàticament el disseny de la taula dinàmica. Cal marcar-hi aquells elements que utilitzarem, i després s'han de traslladar al lloc correcte. En el nostre cas, hem de moure l'element "Producte" a "Noms de columna" i "Preu per peça" a "Valors".
4. Després d'això, finalment es crearà la taula dinàmica. Una bonificació addicional és el càlcul automàtic del valor final.
5. També podeu canviar altres paràmetres. Per exemple, desmarqueu l'element "Preu per peça" i marqueu l'element "Cost total". Com a resultat, tindrem una taula amb informació sobre quant costen els productes. Aquest mètode de transposició és molt més funcional que els altres. Descrivim alguns dels avantatges de les taules dinàmiques:

1. Automatització. Amb l'ajuda de taules dinàmiques, podeu resumir les dades automàticament, així com canviar la posició de columnes i columnes de manera arbitrària. Per fer-ho, no cal que realitzeu cap pas addicional.
2. Interactivitat. L'usuari pot canviar l'estructura de la informació tantes vegades com necessiti per realitzar les seves tasques. Per exemple, podeu canviar l'ordre de les columnes, així com agrupar les dades d'una manera arbitrària. Això es pot fer tantes vegades com ho necessiti l'usuari. I, literalment, triga menys d'un minut.
3. Fàcil de formatar dades. És molt fàcil organitzar una taula pivot de la manera que una persona vol. Per fer-ho, només cal fer uns quants clics del ratolí.
4. Obtenir valors. La gran quantitat de fórmules que s'utilitzen per crear informes es troben a l'accessibilitat directa d'una persona i són fàcils d'integrar en una taula dinàmica. Es tracta de dades com la suma, obtenir la mitjana aritmètica, determinar el nombre de cel·les, multiplicar, trobar els valors més grans i més petits de la mostra especificada.
5. Capacitat per crear gràfics de resum. Si es tornen a calcular les taules dinàmiques, els seus gràfics associats s'actualitzen automàticament. És possible crear tants gràfics com necessiteu. Tots es poden canviar per a una tasca concreta i no estaran interconnectats.
6. Capacitat de filtrar dades.
7. És possible crear una taula dinàmica basada en més d'un conjunt d'informació font. Per tant, la seva funcionalitat serà encara més gran.

És cert que quan s'utilitzen taules dinàmiques, s'han de tenir en compte les restriccions següents:

1. No tota la informació es pot utilitzar per generar taules dinàmiques. Abans que es puguin utilitzar per a aquest propòsit, les cèl·lules s'han de normalitzar. En paraules senzilles, encerta. Requisits obligatoris: presència d'una línia de capçalera, plenitud de totes les línies, igualtat de formats de dades.
2. Les dades s'actualitzen de manera semiautomàtica. Per obtenir informació nova a la taula dinàmica, heu de fer clic en un botó especial.
3. Les taules pivotants ocupen molt d'espai. Això pot provocar alguna interrupció de l'ordinador. A més, el fitxer serà difícil d'enviar per correu electrònic a causa d'això.

A més, després de crear una taula dinàmica, l'usuari no té la possibilitat d'afegir informació nova.