Transponeringsfunksjon i Excel

Fra tid til annen kan en Excel-bruker ha i oppgave å gjøre om en rekke data som har en horisontal struktur til en vertikal. Denne prosessen kalles transponering. Dette ordet er nytt for de fleste, fordi man i vanlig PC-arbeid ikke trenger å bruke denne operasjonen. Men de som må jobbe med store datamengder må vite hvordan det skal gjøres. I dag vil vi snakke mer detaljert om hvordan du utfører det, med hvilken funksjon, og også se på noen andre metoder i detalj.

TRANSPOSER-funksjon – transponer celleområder i Excel

En av de mest interessante og funksjonelle tabelltransponeringsmetodene i Excel er funksjonen TRANSP. Med dens hjelp kan du gjøre om et horisontalt dataområde til et vertikalt, eller utføre omvendt operasjon. La oss se hvordan du jobber med det.

Funksjonssyntaks

Syntaksen for denne funksjonen er utrolig enkel: TRANSPOSER(matrise). Det vil si at vi bare trenger å bruke ett argument, som er et datasett som må konverteres til en horisontal eller vertikal visning, avhengig av hva det opprinnelig var.

Transponering av vertikale områder av celler (kolonner)

Anta at vi har en kolonne med området B2:B6. De kan inneholde både ferdige verdier og formler som returnerer resultater til disse cellene. Det er ikke så viktig for oss, transponering er mulig i begge tilfeller. Etter å ha brukt denne funksjonen, vil radlengden være den samme som den opprinnelige rekkeviddekolonnelengden.

Sekvensen av trinnene for å bruke denne formelen er som følger:

1. Velg en linje. I vårt tilfelle har den en lengde på fem celler.
2. Etter det, flytt markøren til formellinjen, og skriv inn formelen der =TRANSP(B2:B6).
3. Trykk på tastekombinasjonen Ctrl + Shift + Enter.

Naturligvis, i ditt tilfelle, må du spesifisere området som er typisk for bordet ditt.

Transponering av horisontale celleområder (rader)

I prinsippet er virkningsmekanismen nesten den samme som i forrige avsnitt. Anta at vi har en streng med start- og sluttkoordinater B10:F10. Den kan også inneholde både direkte verdier og formler. La oss lage en kolonne av den, som vil ha samme dimensjoner som den opprinnelige raden. Rekkefølgen av handlinger er som følger:

1. Velg denne kolonnen med musen. Du kan også bruke tastene på tastaturet Ctrl og pil ned etter å ha klikket på den øverste cellen i denne kolonnen.
2. Etter det skriver vi formelen =TRANSP(B10:F10) i formellinjen.
3. Vi skriver det som en matriseformel ved å bruke tastekombinasjonen Ctrl + Shift + Enter.

Transponering med Paste Special

Et annet mulig transponeringsalternativ er å bruke Paste Special-funksjonen. Dette er ikke lenger en operator som skal brukes i formler, men det er også en av de populære metodene for å gjøre kolonner om til rader og omvendt.

Dette alternativet er på Hjem-fanen. For å få tilgang til den, må du finne "Utklippstavlen" -gruppen, og der finner du "Lim inn" -knappen. Etter det, åpne menyen som ligger under dette alternativet og velg "Transponer" -elementet. Før det må du velge området du vil velge. Som et resultat vil vi få samme rekkevidde, bare det motsatte speiles.

3 måter å transponere en tabell i Excel

Men faktisk er det mye flere måter å gjøre kolonner om til rader og omvendt. La oss beskrive 3 metoder som vi kan transponere en tabell på i Excel. Vi diskuterte to av dem ovenfor, men vi vil gi noen flere eksempler slik at du får en bedre ide om uXNUMXbuXNUMXbhvordan du skal utføre denne prosedyren.

Metode 1: Lim inn spesial

Denne metoden er den enkleste. Det er nok å trykke på et par knapper, og brukeren mottar en transponert versjon av bordet. La oss gi et lite eksempel for større klarhet. Anta at vi har en tabell som inneholder informasjon om hvor mange produkter som er på lager for øyeblikket, samt hvor mye de koster totalt. Selve bordet ser slik ut.

Vi ser at vi har en overskrift og en kolonne med varenumre. I vårt eksempel inneholder overskriften informasjon om hvilket produkt, hvor mye det koster, hvor mye det er på lager, og hva er totalkostnaden for alle produkter relatert til denne varen som er på lager. Vi får kostnaden etter formelen hvor kostnaden multipliseres med mengden. For å gjøre eksemplet mer visuelt, la oss gjøre overskriften grønn.

Vår oppgave er å sørge for at informasjonen i tabellen er plassert horisontalt. Altså slik at kolonnene blir rader. Handlingsrekkefølgen i vårt tilfelle vil være som følger:

1. Velg rekkevidden av data som vi trenger for å rotere. Etter det kopierer vi disse dataene.
2. Plasser markøren hvor som helst på arket. Klikk deretter på høyre museknapp og åpne kontekstmenyen.
3. Klikk deretter på "Lim inn spesial"-knappen.

Etter å ha fullført disse trinnene, må du klikke på "Transponere" -knappen. Merk heller av i boksen ved siden av dette elementet. Vi endrer ikke andre innstillinger, og klikker deretter på "OK"-knappen.

Etter å ha utført disse trinnene sitter vi igjen med den samme tabellen, bare radene og kolonnene er ordnet annerledes. Vær også oppmerksom på at celler som inneholder samme informasjon er uthevet i grønt. Spørsmål: hva skjedde med formlene som var i det opprinnelige området? Plasseringen deres har endret seg, men de har selv blitt værende. Adressene til cellene endret seg ganske enkelt til de som ble dannet etter transponeringen.

Nesten de samme handlingene må utføres for å transponere verdier, ikke formler. I dette tilfellet må du også bruke Paste Special-menyen, men før det, velg dataområdet som inneholder verdiene. Vi ser at Paste Special-vinduet kan kalles på to måter: gjennom en spesiell meny på båndet eller kontekstmenyen.

Metode 2. TRANSP-funksjon i Excel

Faktisk er denne metoden ikke lenger brukt så aktivt som den var helt i begynnelsen av utseendet til dette regnearkprogrammet. Dette er fordi denne metoden er mye mer komplisert enn å bruke Paste Special. Imidlertid finner den sin bruk i automatisering av tabelltransponering.

Denne funksjonen er også i Excel, så det er viktig å vite om den, selv om den nesten ikke lenger brukes. Tidligere har vi vurdert prosedyren, hvordan man jobber med den. Nå skal vi supplere denne kunnskapen med et ekstra eksempel.

1. Først må vi velge dataområdet som skal brukes til å transponere tabellen. Du trenger bare å velge området tvert imot. For eksempel, i dette eksemplet har vi 4 kolonner og 6 rader. Derfor er det nødvendig å velge et område med motsatte egenskaper: 6 kolonner og 4 rader. Bildet viser det veldig godt.
2. Etter det begynner vi umiddelbart å fylle ut denne cellen. Det er viktig å ikke fjerne utvalget ved et uhell. Derfor må du spesifisere formelen direkte i formellinjen.
3. Deretter trykker du på tastekombinasjonen Ctrl + Shift + Enter. Husk at dette er en matriseformel, siden vi jobber med et stort sett med data på en gang, som vil bli overført til et annet stort sett med celler.

Etter at vi har lagt inn dataene, trykker vi på Enter-tasten, hvoretter vi får følgende resultat.

Vi ser at formelen ikke ble overført til den nye tabellen. Formatering gikk også tapt. Poeto

Alt dette må gjøres manuelt. Husk i tillegg at denne tabellen er relatert til den originale. Derfor, så snart noe informasjon er endret i det opprinnelige området, blir disse justeringene automatisk gjort til den transponerte tabellen.

Derfor er denne metoden godt egnet i tilfeller der du må sørge for at den transponerte tabellen er knyttet til den originale. Hvis du bruker en spesiell innsats, vil denne muligheten ikke lenger være det.

oppsummeringstabell

Dette er en fundamentalt ny metode, som gjør det mulig ikke bare å transponere tabellen, men også å utføre et stort antall handlinger. Riktignok vil transponeringsmekanismen være noe annerledes sammenlignet med de tidligere metodene. Rekkefølgen av handlinger er som følger:

1. La oss lage en pivottabell. For å gjøre dette, må du velge tabellen som vi trenger å transponere. Etter det, gå til "Sett inn" -elementet og se etter "Pivottabell" der. En dialogboks som den i dette skjermbildet vil vises.
2. Her kan du tilordne området det skal lages fra på nytt, samt gjøre en rekke andre innstillinger. Vi er nå først og fremst interessert i plasseringen av pivotbordet – på et nytt ark.
3. Etter det vil oppsettet til pivottabellen opprettes automatisk. Det er nødvendig å merke varene vi skal bruke i den, og deretter må de flyttes til riktig sted. I vårt tilfelle må vi flytte "Produkt"-elementet til "Kolonnenavn" og "Pris per stykke" til "Verdier".
4. Etter det vil pivottabellen endelig bli opprettet. En ekstra bonus er den automatiske beregningen av sluttverdien.
5. Du kan også endre andre innstillinger. Fjern for eksempel merket for "Pris per stk" og merk av for "Totalkostnad". Som et resultat vil vi ha en tabell som inneholder informasjon om hvor mye produktene koster. Denne transponeringsmetoden er mye mer funksjonell enn de andre. La oss beskrive noen av fordelene med pivottabeller:

1. Automasjon. Ved hjelp av pivottabeller kan du oppsummere data automatisk, samt endre plasseringen av kolonner og kolonner vilkårlig. For å gjøre dette, trenger du ikke utføre noen ekstra trinn.
2. Interaktivitet. Brukeren kan endre strukturen på informasjonen så mange ganger han trenger for å utføre oppgavene sine. Du kan for eksempel endre rekkefølgen på kolonner, samt gruppere data på en vilkårlig måte. Dette kan gjøres så mange ganger brukeren trenger. Og det tar bokstavelig talt mindre enn et minutt.
3. Enkelt å formatere data. Det er veldig enkelt å arrangere et pivotbord på den måten en person ønsker. For å gjøre dette, gjør bare noen få museklikk.
4. Å få verdier. Det overveldende antallet formler som brukes til å lage rapporter er plassert i den direkte tilgjengeligheten til en person og er enkle å integrere i en pivottabell. Dette er data som summering, å få det aritmetiske gjennomsnittet, bestemme antall celler, multiplisere, finne de største og minste verdiene i den angitte prøven.
5. Evne til å lage oppsummeringsdiagrammer. Hvis pivottabeller beregnes på nytt, oppdateres de tilknyttede diagrammene automatisk. Det er mulig å lage så mange diagrammer du trenger. Alle kan endres for en spesifikk oppgave, og de vil ikke være sammenkoblet.
6. Evne til å filtrere data.
7. Det er mulig å bygge en pivottabell basert på mer enn ett sett med kildeinformasjon. Derfor vil funksjonaliteten deres bli enda større.

Det er sant at når du bruker pivottabeller, må følgende begrensninger tas i betraktning:

1. Ikke all informasjon kan brukes til å generere pivottabeller. Før de kan brukes til dette formålet, må cellene normaliseres. Med enkle ord, få det riktig. Obligatoriske krav: tilstedeværelse av en overskriftslinje, fylde av alle linjer, likestilling av dataformater.
2. Dataene oppdateres halvautomatisk. For å få ny informasjon i pivottabellen må du klikke på en spesiell knapp.
3. Pivotbord tar opp mye plass. Dette kan føre til noen forstyrrelser på datamaskinen. Dessuten vil filen være vanskelig å sende på e-post på grunn av dette.

Etter å ha opprettet en pivottabell, har ikke brukeren muligheten til å legge til ny informasjon.