Transponeerfunctie in Excel

Van tijd tot tijd kan een Excel-gebruiker de taak hebben om een ​​reeks gegevens met een horizontale structuur om te zetten in een verticale. Dit proces wordt transpositie genoemd. Dit woord is voor de meeste mensen nieuw, omdat je bij normaal pc-werk deze handeling niet hoeft te gebruiken. Degenen die met grote hoeveelheden data moeten werken, moeten echter weten hoe ze dat moeten doen. Vandaag zullen we in meer detail praten over hoe het moet worden uitgevoerd, met welke functie, en ook enkele andere methoden in detail bekijken.

TRANSPOSE-functie - transponeer celbereiken in Excel

Een van de meest interessante en functionele tabelomzettingsmethoden in Excel is de functie TRANSP. Met zijn hulp kunt u een horizontaal gegevensbereik in een verticaal gegevensbereik veranderen of de omgekeerde bewerking uitvoeren. Laten we eens kijken hoe ermee te werken.

Functiesyntaxis:

De syntaxis voor deze functie is ongelooflijk eenvoudig: TRANSPOSE(matrix). Dat wil zeggen, we hoeven slechts één argument te gebruiken, namelijk een gegevensset die moet worden geconverteerd naar een horizontale of verticale weergave, afhankelijk van wat het oorspronkelijk was.

Verticale celbereiken (kolommen) transponeren

Stel dat we een kolom hebben met het bereik B2:B6. Ze kunnen zowel kant-en-klare waarden als formules bevatten die resultaten naar deze cellen retourneren. Het is voor ons niet zo belangrijk, omzetting is in beide gevallen mogelijk. Na het toepassen van deze functie is de rijlengte hetzelfde als de oorspronkelijke kolomlengte van het bereik.

De volgorde van stappen voor het gebruik van deze formule is als volgt:

1. Selecteer een lijn. In ons geval heeft het een lengte van vijf cellen.
2. Verplaats daarna de cursor naar de formulebalk en voer de formule daar in =TRANSP(B2:B6).
3. Druk op de toetscombinatie Ctrl + Shift + Enter.

Uiteraard moet u in uw geval het bereik specificeren dat typerend is voor uw tafel.

Horizontale celbereiken (rijen) transponeren

In principe is het werkingsmechanisme bijna hetzelfde als in de vorige paragraaf. Stel dat we een string hebben met begin- en eindcoördinaten B10:F10. Het kan ook zowel directe waarden als formules bevatten. Laten we er een kolom van maken, die dezelfde afmetingen heeft als de originele rij. De volgorde van acties is als volgt:

1. Selecteer deze kolom met de muis. U kunt ook de toetsen op het toetsenbord Ctrl en de pijl-omlaag gebruiken, nadat u op de bovenste cel van deze kolom hebt geklikt.
2. Daarna schrijven we de formule =TRANSP(B10:F10) in de formulebalk.
3. We schrijven het als een matrixformule met de toetsencombinatie Ctrl + Shift + Enter.

Transponeren met Paste Special

Een andere mogelijke transpositieoptie is om de functie Plakken speciaal te gebruiken. Dit is niet langer een operator die in formules moet worden gebruikt, maar het is ook een van de populaire methoden om kolommen in rijen om te zetten en vice versa.

Deze optie staat op het tabblad Start. Om toegang te krijgen, moet je de groep "Klembord" vinden en daar de knop "Plakken". Open daarna het menu dat zich onder deze optie bevindt en selecteer het item "Transponeren". Daarvoor moet u het bereik selecteren dat u wilt selecteren. Hierdoor krijgen we hetzelfde bereik, alleen het tegenovergestelde wordt gespiegeld.

3 manieren om een ​​tabel in Excel te transponeren

Maar in feite zijn er veel meer manieren om kolommen in rijen te veranderen en vice versa. Laten we 3 methoden beschrijven waarmee we een tabel in Excel kunnen transponeren. We hebben er hierboven twee besproken, maar we zullen nog een paar voorbeelden geven zodat u een beter idee krijgt van hoe u deze procedure kunt uitvoeren.

Methode 1: Plakken speciaal

Deze methode is de eenvoudigste. Het is voldoende om op een paar knoppen te drukken en de gebruiker ontvangt een getransponeerde versie van de tabel. Laten we een klein voorbeeld geven voor meer duidelijkheid. Stel dat we een tabel hebben met informatie over hoeveel producten er momenteel op voorraad zijn en hoeveel ze in totaal kosten. De tafel zelf ziet er zo uit.

We zien dat we een kop en een kolom met artikelnummers hebben. In ons voorbeeld bevat de kop informatie over welk product, hoeveel het kost, hoeveel het op voorraad is en wat de totale kosten zijn van alle producten met betrekking tot dit artikel die op voorraad zijn. We krijgen de kosten volgens de formule waarbij de kosten worden vermenigvuldigd met de hoeveelheid. Laten we de kop groen maken om het voorbeeld visueel te maken.

Het is onze taak ervoor te zorgen dat de informatie in de tabel horizontaal staat. Dat wil zeggen, zodat de kolommen rijen worden. De volgorde van acties in ons geval is als volgt:

1. Selecteer het gegevensbereik dat we moeten roteren. Daarna kopiëren we deze gegevens.
2. Plaats de cursor ergens op het blad. Klik vervolgens met de rechtermuisknop en open het contextmenu.
3. Klik vervolgens op de knop "Plakken speciaal".

Na het voltooien van deze stappen, moet u op de knop "Transponeren" klikken. Vink liever het vakje naast dit item aan. We veranderen geen andere instellingen en klik vervolgens op de knop "OK".

Na het uitvoeren van deze stappen houden we dezelfde tabel over, alleen zijn de rijen en kolommen anders gerangschikt. Merk ook op dat cellen die dezelfde informatie bevatten, groen zijn gemarkeerd. Vraag: wat is er gebeurd met de formules die in het oorspronkelijke bereik zaten? Hun locatie is veranderd, maar ze zijn zelf gebleven. De adressen van de cellen veranderden eenvoudig in de adressen die na de transpositie werden gevormd.

Bijna dezelfde acties moeten worden uitgevoerd om waarden te transponeren, niet formules. In dit geval moet u ook het menu Plakken speciaal gebruiken, maar selecteer eerst het gegevensbereik dat de waarden bevat. We zien dat het venster Plakken speciaal op twee manieren kan worden opgeroepen: via een speciaal menu op het lint of via het contextmenu.

Methode 2. TRANSP-functie in Excel

In feite wordt deze methode niet meer zo actief gebruikt als aan het begin van het verschijnen van dit spreadsheetprogramma. Dit komt omdat deze methode veel gecompliceerder is dan het gebruik van Paste Special. Het vindt echter zijn nut bij het automatiseren van tabeltranspositie.

Deze functie bevindt zich ook in Excel, dus het is absoluut noodzakelijk om hiervan op de hoogte te zijn, ook al wordt deze bijna niet meer gebruikt. Eerder hebben we de procedure overwogen, hoe ermee te werken. Nu vullen we deze kennis aan met een extra voorbeeld.

1. Eerst moeten we het gegevensbereik selecteren dat zal worden gebruikt om de tabel te transponeren. U hoeft alleen het gebied te selecteren, integendeel. In dit voorbeeld hebben we bijvoorbeeld 4 kolommen en 6 rijen. Daarom is het noodzakelijk om een ​​gebied met tegengestelde kenmerken te selecteren: 6 kolommen en 4 rijen. De foto laat het heel goed zien.
2. Daarna beginnen we meteen met het invullen van deze cel. Het is belangrijk om de selectie niet per ongeluk te verwijderen. Daarom moet u de formule rechtstreeks in de formulebalk opgeven.
3. Druk vervolgens op de toetscombinatie Ctrl + Shift + Enter. Onthoud dat dit een matrixformule is, aangezien we met een grote reeks gegevens tegelijk werken, die naar een andere grote reeks cellen worden overgebracht.

Nadat we de gegevens hebben ingevoerd, drukken we op de Enter-toets, waarna we het volgende resultaat krijgen.

We zien dat de formule niet is overgebracht naar de nieuwe tabel. Formatteren ging ook verloren. Poeto

Dit zal allemaal handmatig moeten gebeuren. Onthoud bovendien dat deze tabel gerelateerd is aan de originele. Daarom worden deze aanpassingen automatisch doorgevoerd in de getransponeerde tabel zodra er informatie in het oorspronkelijke bereik wordt gewijzigd.

Daarom is deze methode zeer geschikt in gevallen waarin u ervoor moet zorgen dat de getransponeerde tabel aan de originele is gekoppeld. Als u een speciale inzet gebruikt, is deze mogelijkheid er niet meer.

samenvattingstabel

Dit is een fundamenteel nieuwe methode, die het niet alleen mogelijk maakt om de tabel te transponeren, maar ook om een ​​groot aantal acties uit te voeren. Het is waar dat het omzettingsmechanisme enigszins zal verschillen van de vorige methoden. De volgorde van acties is als volgt:

1. Laten we een draaitabel maken. Om dit te doen, moet u de tabel selecteren die we moeten transponeren. Ga daarna naar het item "Invoegen" en zoek daar naar "Pivot Table". Er verschijnt een dialoogvenster zoals in deze schermafbeelding.
2. Hier kunt u het bereik waaruit het wordt gemaakt opnieuw toewijzen, evenals een aantal andere instellingen. We zijn nu vooral geïnteresseerd in de plaats van de draaitabel – op een nieuw blad.
3. Daarna wordt de lay-out van de draaitabel automatisch gemaakt. Het is noodzakelijk om daarin de items te markeren die we zullen gebruiken, en dan moeten ze naar de juiste plaats worden verplaatst. In ons geval moeten we het item "Product" verplaatsen naar "Kolomnamen" en "Prijs per stuk" naar "Waarden".
4. Daarna wordt de draaitabel eindelijk gemaakt. Een extra bonus is de automatische berekening van de eindwaarde.
5. U kunt ook andere instellingen wijzigen. Schakel bijvoorbeeld het item "Prijs per stuk" uit en vink het item "Totale kosten" aan. Als gevolg hiervan hebben we een tabel met informatie over hoeveel de producten kosten. Deze omzettingsmethode is veel functioneler dan de andere. Laten we enkele voordelen van draaitabellen beschrijven:

1. Automatisering. Met behulp van draaitabellen kunt u gegevens automatisch samenvatten en de positie van kolommen en kolommen willekeurig wijzigen. Om dit te doen, hoeft u geen extra stappen uit te voeren.
2. Interactiviteit. De gebruiker kan de informatiestructuur zo vaak wijzigen als nodig is om zijn taken uit te voeren. U kunt bijvoorbeeld de volgorde van kolommen wijzigen en gegevens op een willekeurige manier groeperen. Dit kan zo vaak worden gedaan als de gebruiker nodig heeft. En het duurt letterlijk minder dan een minuut.
3. Gemakkelijk te formatteren gegevens. Het is heel eenvoudig om een ​​draaitabel in te richten zoals een persoon dat wil. Om dit te doen, hoeft u slechts een paar muisklikken te maken.
4. Waarden krijgen. Het overweldigende aantal formules dat wordt gebruikt om rapporten te maken, bevindt zich in de directe toegankelijkheid van een persoon en is eenvoudig te integreren in een draaitabel. Dit zijn gegevens zoals sommatie, het rekenkundig gemiddelde krijgen, het aantal cellen bepalen, vermenigvuldigen, de grootste en kleinste waarden vinden in de opgegeven steekproef.
5. Mogelijkheid om samenvattende grafieken te maken. Als draaitabellen opnieuw worden berekend, worden de bijbehorende grafieken automatisch bijgewerkt. U kunt zoveel grafieken maken als u nodig heeft. Ze kunnen allemaal worden gewijzigd voor een specifieke taak en zijn niet met elkaar verbonden.
6. Mogelijkheid om gegevens te filteren.
7. Het is mogelijk om een ​​draaitabel te bouwen op basis van meer dan één set broninformatie. Daarom zal hun functionaliteit nog groter worden.

Het is waar dat bij het gebruik van draaitabellen rekening moet worden gehouden met de volgende beperkingen:

1. Niet alle informatie kan worden gebruikt om draaitabellen te genereren. Voordat ze voor dit doel kunnen worden gebruikt, moeten de cellen worden genormaliseerd. In eenvoudige woorden, doe het goed. Verplichte vereisten: aanwezigheid van een kopregel, volheid van alle regels, gelijkheid van gegevensformaten.
2. De gegevens worden semi-automatisch bijgewerkt. Om nieuwe informatie in de draaitabel te krijgen, moet u op een speciale knop klikken.
3. Draaitafels nemen veel ruimte in beslag. Dit kan leiden tot enige verstoring van de computer. Ook zal het bestand hierdoor moeilijk per e-mail kunnen worden verzonden.

Ook heeft de gebruiker na het maken van een draaitabel niet de mogelijkheid om nieuwe informatie toe te voegen.