Transponeringsfunktion i Excel

Då och då kan en Excel-användare ha till uppgift att förvandla en rad data som har en horisontell struktur till en vertikal. Denna process kallas transponering. Det här ordet är nytt för de flesta, för i vanligt PC-arbete behöver du inte använda den här operationen. Men de som ska jobba med stora datamängder behöver veta hur man gör. Idag kommer vi att prata mer i detalj om hur man utför det, med vilken funktion, och även titta på några andra metoder i detalj.

TRANSPOSERA-funktion – transponera cellområden i Excel

En av de mest intressanta och funktionella tabelltransponeringsmetoderna i Excel är funktionen ÖVERFÖRING. Med dess hjälp kan du förvandla ett horisontellt dataområde till ett vertikalt, eller utföra den omvända operationen. Låt oss se hur man arbetar med det.

Funktionssyntax

Syntaxen för denna funktion är otroligt enkel: TRANSPOSERA(matris). Det vill säga, vi behöver bara använda ett argument, vilket är en datamängd som måste konverteras till en horisontell eller vertikal vy, beroende på vad det var ursprungligen.

Transponera vertikala intervall av celler (kolumner)

Anta att vi har en kolumn med intervallet B2:B6. De kan innehålla både färdiga värden och formler som returnerar resultat till dessa celler. Det är inte så viktigt för oss, införlivande är möjligt i båda fallen. Efter att ha tillämpat denna funktion kommer radlängden att vara densamma som den ursprungliga intervallkolumnlängden.

Sekvensen av steg för att använda denna formel är som följer:

1. Välj en rad. I vårt fall har den en längd på fem celler.
2. Flytta sedan markören till formelfältet och skriv in formeln där =TRANSP(B2:B6).
3. Tryck på tangentkombinationen Ctrl + Shift + Enter.

Naturligtvis måste du i ditt fall ange det intervall som är typiskt för ditt bord.

Transponera horisontella cellområden (rader)

I princip är verkningsmekanismen nästan densamma som i föregående stycke. Anta att vi har en sträng med start- och slutkoordinater B10:F10. Den kan också innehålla både direkta värden och formler. Låt oss göra en kolumn av den, som kommer att ha samma dimensioner som den ursprungliga raden. Sekvensen av åtgärder är som följer:

1. Välj denna kolumn med musen. Du kan också använda tangenterna på tangentbordet Ctrl och nedåtpilen efter att ha klickat på den översta cellen i denna kolumn.
2. Efter det skriver vi formeln =TRANSP(B10:F10) i formelfältet.
3. Vi skriver det som en matrisformel med tangentkombinationen Ctrl + Shift + Enter.

Transponera med Paste Special

Ett annat möjligt transponeringsalternativ är att använda funktionen Klistra in special. Detta är inte längre en operator som ska användas i formler, men det är också en av de populära metoderna för att omvandla kolumner till rader och vice versa.

Det här alternativet finns på fliken Hem. För att komma åt den måste du hitta gruppen "Urklipp" och där hitta knappen "Klistra in". Därefter öppnar du menyn som finns under det här alternativet och väljer alternativet "Transponera". Innan dess måste du välja det intervall som du vill välja. Som ett resultat kommer vi att få samma intervall, bara motsatsen speglas.

3 sätt att överföra en tabell i Excel

Men i själva verket finns det mycket fler sätt att förvandla kolumner till rader och vice versa. Låt oss beskriva 3 metoder med vilka vi kan överföra en tabell i Excel. Vi diskuterade två av dem ovan, men vi kommer att ge några fler exempel så att du får en bättre uppfattning om hur uXNUMXbuXNUMXb ska utföra denna procedur.

Metod 1: Klistra in special

Denna metod är den enklaste. Det räcker med att trycka på ett par knappar, så får användaren en transponerad version av bordet. Låt oss ge ett litet exempel för större tydlighet. Anta att vi har en tabell som innehåller information om hur många produkter som finns i lager för närvarande, samt hur mycket de kostar totalt. Själva bordet ser ut så här.

Vi ser att vi har en rubrik och en kolumn med produktnummer. I vårt exempel innehåller rubriken information om vilken produkt, hur mycket den kostar, hur mycket den finns i lager och vad är den totala kostnaden för alla produkter relaterade till denna vara som finns i lager. Vi får kostnaden enligt formeln där kostnaden multipliceras med kvantiteten. För att göra exemplet mer visuellt, låt oss göra rubriken grön.

Vår uppgift är att se till att informationen i tabellen är placerad horisontellt. Det vill säga så att kolumnerna blir rader. Handlingsföljden i vårt fall kommer att vara följande:

1. Välj det dataområde som vi behöver rotera. Efter det kopierar vi dessa uppgifter.
2. Placera markören var som helst på arket. Klicka sedan på höger musknapp och öppna snabbmenyn.
3. Klicka sedan på knappen "Klistra in special".

När du har slutfört dessa steg måste du klicka på knappen "Transponera". Markera istället rutan bredvid det här objektet. Vi ändrar inte andra inställningar och klickar sedan på knappen "OK".

Efter att ha utfört dessa steg står vi kvar med samma tabell, bara dess rader och kolumner är ordnade annorlunda. Observera också att celler som innehåller samma information är markerade i grönt. Fråga: vad hände med formlerna som fanns i det ursprungliga intervallet? Deras plats har förändrats, men de har själva blivit kvar. Cellernas adresser ändrades helt enkelt till de som bildades efter transponeringen.

Nästan samma åtgärder måste utföras för att överföra värden, inte formler. I det här fallet måste du också använda menyn Klistra in special, men innan dess väljer du dataintervallet som innehåller värdena. Vi ser att fönstret Klistra in special kan anropas på två sätt: genom en speciell meny på menyfliksområdet eller snabbmenyn.

Metod 2. TRANSP-funktion i Excel

Faktum är att denna metod inte längre används lika aktivt som den var i början av uppkomsten av detta kalkylprogram. Detta beror på att denna metod är mycket mer komplicerad än att använda Paste Special. Den finner dock sin användning vid automatisering av tabelltransponering.

Den här funktionen finns också i Excel, så det är absolut nödvändigt att veta om det, även om det nästan inte längre används. Tidigare har vi övervägt proceduren, hur man arbetar med den. Nu ska vi komplettera denna kunskap med ytterligare ett exempel.

1. Först måste vi välja dataintervallet som ska användas för att transponera tabellen. Du behöver bara välja området tvärtom. Till exempel, i det här exemplet har vi 4 kolumner och 6 rader. Därför är det nödvändigt att välja ett område med motsatta egenskaper: 6 kolumner och 4 rader. Bilden visar det väldigt bra.
2. Efter det börjar vi omedelbart fylla i den här cellen. Det är viktigt att inte ta bort markeringen av misstag. Därför måste du ange formeln direkt i formelfältet.
3. Tryck sedan på tangentkombinationen Ctrl + Shift + Enter. Kom ihåg att detta är en matrisformel, eftersom vi arbetar med en stor uppsättning data på en gång, som kommer att överföras till en annan stor uppsättning celler.

När vi har skrivit in data trycker vi på Enter-tangenten, varefter vi får följande resultat.

Vi ser att formeln inte överfördes till den nya tabellen. Formateringen gick också förlorad. Poeto

Allt detta måste göras manuellt. Kom dessutom ihåg att denna tabell är relaterad till den ursprungliga. Så fort viss information ändras i det ursprungliga intervallet görs därför dessa justeringar automatiskt i den transponerade tabellen.

Därför är den här metoden väl lämpad i de fall du behöver se till att den transponerade tabellen är länkad till den ursprungliga. Om du använder en speciell insats kommer denna möjlighet inte längre att finnas.

Översiktstabell

Detta är en i grunden ny metod, som gör det möjligt att inte bara införliva tabellen utan också att utföra ett stort antal åtgärder. Det är sant att införlivningsmekanismen kommer att vara något annorlunda jämfört med de tidigare metoderna. Sekvensen av åtgärder är som följer:

1. Låt oss göra en pivottabell. För att göra detta måste du välja tabellen som vi behöver överföra. Efter det, gå till "Infoga" -objektet och leta efter "Pivottabell" där. En dialogruta som den i denna skärmdump visas.
2. Här kan du tilldela om intervallet från vilket det kommer att göras, samt göra ett antal andra inställningar. Vi är nu främst intresserade av pivotbordets plats – på ett nytt ark.
3. Efter det kommer layouten för pivottabellen att skapas automatiskt. Det är nödvändigt att markera de föremål som vi kommer att använda i den, och sedan måste de flyttas till rätt plats. I vårt fall måste vi flytta objektet "Produkt" till "Kolumnnamn" och "Pris per stycke" till "Värden".
4. Därefter kommer pivottabellen äntligen att skapas. En extra bonus är den automatiska beräkningen av slutvärdet.
5. Du kan också ändra andra inställningar. Avmarkera till exempel posten "Pris per styck" och markera posten "Total kostnad". Som ett resultat kommer vi att ha en tabell med information om hur mycket produkterna kostar. Denna transponeringsmetod är mycket mer funktionell än de andra. Låt oss beskriva några av fördelarna med pivottabeller:

1. Automatisering. Med hjälp av pivottabeller kan du sammanfatta data automatiskt, samt ändra positionen på kolumner och kolumner godtyckligt. För att göra detta behöver du inte utföra några ytterligare steg.
2. Interaktivitet. Användaren kan ändra strukturen på informationen så många gånger som han behöver för att utföra sina uppgifter. Du kan till exempel ändra ordningen på kolumner, samt gruppera data på ett godtyckligt sätt. Detta kan göras så många gånger som användaren behöver. Och det tar bokstavligen mindre än en minut.
3. Lätt att formatera data. Det är väldigt enkelt att arrangera ett pivotbord på det sätt en person vill. För att göra detta, gör bara några musklick.
4. Få värden. Det överväldigande antalet formler som används för att skapa rapporter finns i en persons direkta tillgänglighet och är lätta att integrera i en pivottabell. Dessa är data som summering, få det aritmetiska medelvärdet, bestämma antalet celler, multiplicera, hitta de största och minsta värdena i det angivna urvalet.
5. Möjlighet att skapa sammanfattande diagram. Om pivottabeller räknas om uppdateras deras tillhörande diagram automatiskt. Det är möjligt att skapa så många diagram som du behöver. Alla kan ändras för en specifik uppgift och de kommer inte att kopplas samman.
6. Möjlighet att filtrera data.
7. Det är möjligt att bygga en pivottabell baserat på mer än en uppsättning källinformation. Därför kommer deras funktionalitet att bli ännu större.

Det är sant att när du använder pivottabeller måste följande begränsningar beaktas:

1. All information kan inte användas för att generera pivottabeller. Innan de kan användas för detta ändamål måste cellerna normaliseras. Med enkla ord, gör det rätt. Obligatoriska krav: närvaro av en rubrikrad, alla raders fyllighet, lika dataformat.
2. Uppgifterna uppdateras halvautomatiskt. För att få ny information i pivottabellen måste du klicka på en speciell knapp.
3. Pivottabeller tar mycket plats. Detta kan leda till vissa avbrott i datorn. Dessutom kommer filen att vara svår att skicka med e-post på grund av detta.

Efter att ha skapat en pivottabell har användaren inte heller möjlighet att lägga till ny information.