Transponeringsfunktion i Excel

Fra tid til anden kan en Excel-bruger have til opgave at omdanne en række data, der har en horisontal struktur, til en lodret. Denne proces kaldes transponering. Dette ord er nyt for de fleste, fordi man i normalt pc-arbejde ikke skal bruge denne operation. De, der skal arbejde med store mængder data, skal dog vide, hvordan det skal gøres. I dag vil vi tale mere detaljeret om, hvordan man udfører det, med hvilken funktion, og også se på nogle andre metoder i detaljer.

TRANSPOSER funktion – transponer celleområder i Excel

En af de mest interessante og funktionelle tabeltransponeringsmetoder i Excel er funktionen TRANSP. Med dens hjælp kan du omdanne et vandret dataområde til et lodret eller udføre den omvendte handling. Lad os se, hvordan man arbejder med det.

Funktions syntaks

Syntaksen for denne funktion er utrolig enkel: TRANSPOSER(matrix). Det vil sige, at vi kun skal bruge ét argument, som er et datasæt, der skal konverteres til en vandret eller lodret visning, alt efter hvad det oprindeligt var.

Transponering af lodrette områder af celler (kolonner)

Antag, at vi har en kolonne med området B2:B6. De kan indeholde både færdige værdier og formler, der returnerer resultater til disse celler. Det er ikke så vigtigt for os, omsættelse er mulig i begge tilfælde. Efter anvendelse af denne funktion vil rækkelængden være den samme som den oprindelige rækkevidde kolonnelængde.

Rækkefølgen af ​​trin for at bruge denne formel er som følger:

1. Vælg en linje. I vores tilfælde har den en længde på fem celler.
2. Flyt derefter markøren til formellinjen og indtast formlen der =TRANSP(B2:B6).
3. Tryk på tastekombinationen Ctrl + Shift + Enter.

I dit tilfælde skal du naturligvis angive det område, der er typisk for dit bord.

Transponering af vandrette celleområder (rækker)

I princippet er virkningsmekanismen næsten den samme som i det foregående afsnit. Antag, at vi har en streng med start- og slutkoordinater B10:F10. Det kan også indeholde både direkte værdier og formler. Lad os lave en kolonne ud af det, som vil have samme dimensioner som den oprindelige række. Rækkefølgen af ​​handlinger er som følger:

1. Vælg denne kolonne med musen. Du kan også bruge tasterne på tastaturet Ctrl og pil ned efter at have klikket på den øverste celle i denne kolonne.
2. Derefter skriver vi formlen =TRANSP(B10:F10) i formellinjen.
3. Vi skriver det som en matrixformel ved hjælp af tastekombinationen Ctrl + Shift + Enter.

Transponering med Paste Special

En anden mulig transponeringsmulighed er at bruge funktionen Indsæt speciel. Dette er ikke længere en operator, der skal bruges i formler, men det er også en af ​​de populære metoder til at omdanne kolonner til rækker og omvendt.

Denne mulighed er på fanen Hjem. For at få adgang til den skal du finde gruppen "Udklipsholder", og der skal du finde knappen "Sæt ind". Åbn derefter menuen, der er placeret under denne mulighed, og vælg punktet "Transponer". Før det skal du vælge det område, du vil vælge. Som et resultat vil vi få det samme område, kun det modsatte afspejles.

3 måder at transponere en tabel i Excel

Men faktisk er der meget flere måder at omdanne kolonner til rækker og omvendt. Lad os beskrive 3 metoder, hvormed vi kan transponere en tabel i Excel. Vi diskuterede to af dem ovenfor, men vi vil give et par flere eksempler, så du får en bedre ide om uXNUMXbuXNUMXb, hvordan du udfører denne procedure.

Metode 1: Indsæt speciel

Denne metode er den enkleste. Det er nok at trykke på et par knapper, og brugeren modtager en transponeret version af bordet. Lad os give et lille eksempel for større klarhed. Antag, at vi har en tabel, der indeholder oplysninger om, hvor mange produkter der er på lager i øjeblikket, samt hvor meget de koster i alt. Selve bordet ser sådan ud.

Vi ser, at vi har en overskrift og en kolonne med produktnumre. I vores eksempel indeholder overskriften information om hvilket produkt, hvor meget det koster, hvor meget det er på lager, og hvad er den samlede pris for alle produkter relateret til denne vare, der er på lager. Vi får omkostningerne efter formlen, hvor omkostningerne ganges med mængden. For at gøre eksemplet mere visuelt, lad os gøre overskriften grøn.

Vores opgave er at sikre, at oplysningerne i tabellen er placeret vandret. Altså så kolonnerne bliver til rækker. Rækkefølgen af ​​handlinger i vores sag vil være som følger:

1. Vælg den række af data, som vi skal rotere. Derefter kopierer vi disse data.
2. Placer markøren hvor som helst på arket. Klik derefter på højre museknap og åbn kontekstmenuen.
3. Klik derefter på knappen "Indsæt speciel".

Når du har gennemført disse trin, skal du klikke på knappen "Transponere". Markér i stedet afkrydsningsfeltet ud for dette element. Vi ændrer ikke andre indstillinger, og klikker derefter på knappen "OK".

Efter at have udført disse trin står vi tilbage med den samme tabel, kun dens rækker og kolonner er arrangeret anderledes. Bemærk også, at celler, der indeholder de samme oplysninger, er fremhævet med grønt. Spørgsmål: hvad skete der med formlerne, der var i det oprindelige område? Deres placering har ændret sig, men de er selv blevet. Cellernes adresser ændrede sig simpelthen til dem, der blev dannet efter transponeringen.

Næsten de samme handlinger skal udføres for at transponere værdier, ikke formler. I dette tilfælde skal du også bruge menuen Indsæt speciel, men før det skal du vælge det dataområde, der indeholder værdierne. Vi ser, at vinduet Sæt speciel kan kaldes på to måder: gennem en speciel menu på båndet eller kontekstmenuen.

Metode 2. TRANSP funktion i Excel

Faktisk bruges denne metode ikke længere så aktivt, som den var i begyndelsen af ​​dette regnearksprogram. Dette skyldes, at denne metode er meget mere kompliceret end at bruge Paste Special. Det finder dog sin anvendelse i automatisering af tabeltransponering.

Denne funktion er også i Excel, så det er bydende nødvendigt at vide om det, selvom det næsten ikke længere bruges. Tidligere overvejede vi proceduren, hvordan man arbejder med den. Nu vil vi supplere denne viden med et ekstra eksempel.

1. Først skal vi vælge det dataområde, der skal bruges til at transponere tabellen. Du skal blot vælge området tværtimod. For eksempel har vi i dette eksempel 4 kolonner og 6 rækker. Derfor er det nødvendigt at vælge et område med modsatte egenskaber: 6 kolonner og 4 rækker. Billedet viser det meget godt.
2. Derefter begynder vi straks at udfylde denne celle. Det er vigtigt ikke at fjerne markeringen ved et uheld. Derfor skal du angive formlen direkte i formellinjen.
3. Tryk derefter på tastekombinationen Ctrl + Shift + Enter. Husk, at dette er en matrixformel, da vi arbejder med et stort sæt data på én gang, som vil blive overført til et andet stort sæt celler.

Efter at vi har indtastet dataene, trykker vi på Enter-tasten, hvorefter vi får følgende resultat.

Vi ser, at formlen ikke blev overført til den nye tabel. Formatering gik også tabt. Poeto

Alt dette skal gøres manuelt. Derudover skal du huske, at denne tabel er relateret til den originale. Derfor, så snart nogle oplysninger ændres i det oprindelige område, foretages disse justeringer automatisk til den transponerede tabel.

Derfor er denne metode velegnet i tilfælde, hvor du skal sikre dig, at den transponerede tabel er knyttet til den originale. Hvis du bruger en speciel indsats, er denne mulighed ikke længere.

oversigtstabel

Dette er en grundlæggende ny metode, som gør det muligt ikke kun at transponere tabellen, men også at udføre et stort antal handlinger. Sandt nok vil transponeringsmekanismen være noget anderledes sammenlignet med de tidligere metoder. Rækkefølgen af ​​handlinger er som følger:

1. Lad os lave en pivottabel. For at gøre dette skal du vælge den tabel, som vi skal transponere. Gå derefter til punktet "Indsæt" og se efter "Pivottabel" der. En dialogboks som den i dette skærmbillede vises.
2. Her kan du omtildele det område, som det vil blive lavet fra, samt foretage en række andre indstillinger. Vi er nu primært interesserede i pivotbordets plads – på et nyt ark.
3. Derefter oprettes layoutet af pivottabellen automatisk. Det er nødvendigt at markere de varer, som vi vil bruge i det, og så skal de flyttes til det rigtige sted. I vores tilfælde skal vi flytte "Produkt"-elementet til "Kolonnenavne" og "Pris pr. styk" til "Værdier".
4. Derefter bliver pivottabellen endelig oprettet. En ekstra bonus er den automatiske beregning af slutværdien.
5. Du kan også ændre andre indstillinger. Fjern for eksempel fluebenet "Pris pr stk" og marker punktet "Samlet pris". Som et resultat vil vi have en tabel, der indeholder oplysninger om, hvor meget produkterne koster. Denne transponeringsmetode er meget mere funktionel end de andre. Lad os beskrive nogle af fordelene ved pivottabeller:

1. Automatisering. Ved hjælp af pivottabeller kan du opsummere data automatisk, samt ændre placeringen af ​​kolonner og kolonner vilkårligt. For at gøre dette behøver du ikke udføre yderligere trin.
2. Interaktivitet. Brugeren kan ændre informationsstrukturen så mange gange, som han har brug for at udføre sine opgaver. For eksempel kan du ændre rækkefølgen af ​​kolonner, samt gruppere data på en vilkårlig måde. Dette kan gøres så mange gange som brugeren har brug for. Og det tager bogstaveligt talt mindre end et minut.
3. Nem at formatere data. Det er meget nemt at arrangere et pivotbord på den måde, en person ønsker. For at gøre dette skal du blot lave et par museklik.
4. At få værdier. Det overvældende antal formler, der bruges til at oprette rapporter, er placeret i en persons direkte tilgængelighed og er nemme at integrere i en pivottabel. Disse er data såsom summering, at få det aritmetiske middelværdi, bestemme antallet af celler, multiplicere, finde de største og mindste værdier i den specificerede prøve.
5. Mulighed for at oprette oversigtsdiagrammer. Hvis pivottabeller genberegnes, opdateres deres tilknyttede diagrammer automatisk. Det er muligt at oprette så mange diagrammer, som du har brug for. Alle kan ændres til en specifik opgave, og de vil ikke være indbyrdes forbundet.
6. Evne til at filtrere data.
7. Det er muligt at bygge en pivottabel baseret på mere end ét sæt kildeoplysninger. Derfor vil deres funktionalitet blive endnu større.

Sandt nok, når du bruger pivottabeller, skal følgende begrænsninger tages i betragtning:

1. Ikke al information kan bruges til at generere pivottabeller. Før de kan bruges til dette formål, skal cellerne normaliseres. Med enkle ord, få det rigtigt. Obligatoriske krav: tilstedeværelse af en overskriftslinje, fylde af alle linjer, lighed mellem dataformater.
2. Dataene opdateres semi-automatisk. For at få ny information i pivottabellen skal du klikke på en speciel knap.
3. Pivotborde fylder meget. Dette kan føre til nogle afbrydelser af computeren. Desuden vil filen være svær at sende via e-mail på grund af dette.

Efter at have oprettet en pivottabel, har brugeren heller ikke mulighed for at tilføje nye oplysninger.