Funkció transzponálása Excelben

Időről időre egy Excel-felhasználónak feladata lehet, hogy egy vízszintes szerkezetű adattartományt függőlegessé alakítson át. Ezt a folyamatot transzponálásnak nevezik. Ez a szó a legtöbb ember számára új, mert normál számítógépes munkában nem kell ezt a műveletet használnia. Azonban azoknak, akiknek nagy mennyiségű adattal kell dolgozniuk, tudniuk kell, hogyan kell ezt megtenni. Ma részletesebben beszélünk arról, hogyan kell végrehajtani, milyen funkcióval, és részletesen megvizsgálunk néhány más módszert is.

TRANSPOSE funkció – cellatartományok transzponálása az Excelben

Az egyik legérdekesebb és legfunkcionálisabb táblázattranszpozíciós módszer az Excelben a függvény TRANSP. Segítségével vízszintes adattartományt függőlegessé alakíthat, vagy fordított műveletet hajthat végre. Lássuk, hogyan kell vele dolgozni.

Függvény szintaxis

Ennek a függvénynek a szintaxisa hihetetlenül egyszerű: TRANSPOSE(tömb). Vagyis csak egy argumentumot kell használnunk, ez egy adathalmaz, amelyet vízszintes vagy függőleges nézetté kell konvertálni, attól függően, hogy eredetileg mi volt.

Függőleges cellatartományok (oszlopok) transzponálása

Tegyük fel, hogy van egy B2:B6 tartományú oszlopunk. Tartalmazhatnak kész értékeket és képleteket is, amelyek eredményeket adnak vissza ezekhez a cellákhoz. Számunkra ez nem annyira fontos, az átültetés mindkét esetben lehetséges. A függvény alkalmazása után a sor hossza megegyezik az eredeti tartomány oszlophosszával.

A képlet használatának lépéseinek sorrendje a következő:

1. Válasszon egy sort. A mi esetünkben öt cella hosszúságú.
2. Ezután vigye a kurzort a képletsorra, és írja be ott a képletet =TRANSP(B2:B6).
3. Nyomja meg a Ctrl + Shift + Enter billentyűkombinációt.

Természetesen az Ön esetében meg kell adnia a táblázatra jellemző tartományt.

Vízszintes cellatartományok (sorok) transzponálása

A hatásmechanizmus elvileg majdnem ugyanaz, mint az előző bekezdésben. Tegyük fel, hogy van egy karakterláncunk B10:F10 kezdő- és végkoordinátákkal. Tartalmazhat közvetlenül értékeket és képleteket is. Csináljunk belőle egy oszlopot, aminek mérete megegyezik az eredeti sor méreteivel. A műveletek sorrendje a következő:

1. Jelölje ki ezt az oszlopot az egérrel. Használhatja a billentyűzet Ctrl és a lefelé mutató billentyűit is, miután az oszlop legfelső cellájára kattintott.
2. Ezt követően írjuk fel a képletet =TRANSP(B10:F10) a képletsávban.
3. Tömbképletként írjuk a Ctrl + Shift + Enter billentyűkombináció segítségével.

Transzponálás a Paste Special segítségével

Egy másik lehetséges átültetési lehetőség a Paste Special funkció használata. Ez már nem a képletekben használható operátor, hanem az egyik legnépszerűbb módszer az oszlopok sorokká alakítására és fordítva.

Ez a lehetőség a Kezdőlap lapon található. A hozzáféréshez meg kell találnia a „Vágólap” csoportot, és ott a „Beillesztés” gombot. Ezután nyissa meg az ezen opció alatt található menüt, és válassza a „Transpone” elemet. Ezt megelőzően ki kell választania a kiválasztani kívánt tartományt. Ennek eredményeként ugyanazt a tartományt kapjuk, csak az ellenkezője tükröződik.

3 módszer a táblázat átültetésére Excelben

Valójában azonban sokkal több módja van az oszlopok sorokká alakítására és fordítva. Ismertessünk 3 módszert, amellyel Excelben transzponálhatunk egy táblázatot. A fentiek közül kettőt tárgyaltunk, de adunk még néhány példát, hogy jobb képet kapjon az eljárás végrehajtásáról.

1. módszer: Speciális beillesztés

Ez a módszer a legegyszerűbb. Elég megnyomni néhány gombot, és a felhasználó megkapja a táblázat transzponált változatát. Mondjunk egy kis példát a jobb érthetőség kedvéért. Tegyük fel, hogy van egy táblázatunk arról, hogy hány termék van jelenleg raktáron, és mennyibe kerülnek összesen. Maga a táblázat így néz ki.

Látjuk, hogy van egy fejlécünk és egy oszlopunk a termékszámokkal. Példánkban a fejléc információkat tartalmaz arról, hogy milyen termék, mennyibe kerül, mennyi van raktáron, és mennyi a raktáron lévő, ehhez a cikkhez kapcsolódó összes termék összköltsége. A költséget a képlet szerint kapjuk meg, ahol a költséget megszorozzuk a mennyiséggel. A példa vizuálisabbá tételéhez tegyük zöldre a fejlécet.

Feladatunk annak biztosítása, hogy a táblázatban szereplő információk vízszintesen helyezkedjenek el. Vagyis úgy, hogy az oszlopok sorokká váljanak. A műveletek sorrendje esetünkben a következő lesz:

1. Válassza ki az adatok tartományát, amelyet el kell forgatnunk. Ezt követően ezeket az adatokat másoljuk.
2. Helyezze a kurzort a lap tetszőleges pontjára. Ezután kattintson a jobb egérgombbal, és nyissa meg a helyi menüt.
3. Ezután kattintson a „Speciális beillesztés” gombra.

A lépések elvégzése után kattintson a „Transpone” gombra. Inkább jelölje be az elem melletti négyzetet. A többi beállítást nem változtatjuk meg, majd kattintson az „OK” gombra.

Ezen lépések végrehajtása után ugyanaz a táblázat marad, csak a sorai és oszlopai vannak másképp elrendezve. Vegye figyelembe azt is, hogy az azonos információt tartalmazó cellák zöld színnel vannak kiemelve. Kérdés: mi történt azokkal a képletekkel, amelyek az eredeti tartományban voltak? Helyük megváltozott, de ők maguk megmaradtak. A cellák címei egyszerűen megváltoztak azokra, amelyek az átültetés után keletkeztek.

Szinte ugyanazokat a műveleteket kell végrehajtani az értékek átültetéséhez, nem a képletekhez. Ebben az esetben is a Paste Special menüt kell használni, de előtte válassza ki az értékeket tartalmazó adattartományt. Látjuk, hogy a Speciális beillesztés ablak kétféleképpen hívható meg: a szalag speciális menüjéből vagy a helyi menüből.

2. módszer. TRANSP függvény az Excelben

Valójában ezt a módszert már nem használják olyan aktívan, mint a táblázatkezelő program megjelenésének kezdetén. Ez azért van, mert ez a módszer sokkal bonyolultabb, mint a Paste Special használata. Használható azonban a táblázattranszponálás automatizálásában.

Ezenkívül ez a funkció az Excelben van, ezért feltétlenül tudni kell róla, bár már szinte nem használják. Korábban megvizsgáltuk az eljárást, hogyan kell dolgozni vele. Ezt a tudást most egy további példával egészítjük ki.

1. Először is ki kell választanunk azt az adattartományt, amelyet a táblázat transzponálásához használunk. Éppen ellenkezőleg, ki kell választania a területet. Például ebben a példában 4 oszlopunk és 6 sorunk van. Ezért olyan területet kell kiválasztani, amelynek jellemzői ellentétesek: 6 oszlop és 4 sor. A kép nagyon jól mutatja.
2. Ezt követően azonnal elkezdjük kitölteni ezt a cellát. Fontos, hogy véletlenül se távolítsa el a kijelölést. Ezért a képletet közvetlenül a képletsorban kell megadnia.
3. Ezután nyomja meg a Ctrl + Shift + Enter billentyűkombinációt. Ne feledje, hogy ez egy tömbképlet, mivel egyszerre nagy adatkészlettel dolgozunk, amelyek egy másik nagy cellakészletbe kerülnek át.

Az adatok megadása után megnyomjuk az Enter billentyűt, ami után a következő eredményt kapjuk.

Látjuk, hogy a képlet nem került át az új táblába. A formázás is elveszett. Poeto

Mindezt kézzel kell megtenni. Ezenkívül ne feledje, hogy ez a táblázat kapcsolódik az eredetihez. Ezért amint bizonyos információk megváltoznak az eredeti tartományban, ezek a módosítások automatikusan megtörténik a transzponált táblázatban.

Ezért ez a módszer jól alkalmazható olyan esetekben, amikor meg kell bizonyosodni arról, hogy a transzponált tábla kapcsolódik az eredetihez. Ha speciális betétet használ, ez a lehetőség megszűnik.

összefoglaló táblázat

Ez egy alapvetően új módszer, amely nemcsak a táblázat átültetését teszi lehetővé, hanem hatalmas számú művelet végrehajtását is. Igaz, az átültetési mechanizmus némileg eltér a korábbi módszerektől. A műveletek sorrendje a következő:

1. Készítsünk egy pivot táblát. Ehhez ki kell választanunk azt a táblázatot, amelyet transzponálnunk kell. Ezután lépjen a „Beszúrás” elemre, és keresse meg ott a „Pivot Table” elemet. Megjelenik az ezen a képernyőképen láthatóhoz hasonló párbeszédpanel.
2. Itt újra hozzárendelheti azt a tartományt, amelyből készül, valamint számos egyéb beállítást is elvégezhet. Bennünket most elsősorban a pivot tábla helye érdekel – új lapon.
3. Ezt követően automatikusan létrejön a pivot tábla elrendezése. Jelölni kell benne azokat a tárgyakat, amelyeket használni fogunk, majd azokat a megfelelő helyre kell helyezni. Esetünkben a „Termék” elemet át kell helyeznünk az „Oszlopnevek”-be, a „Price per Piece”-t pedig az „Értékek”-be.
4. Ezt követően végre létrejön a pivot tábla. További bónusz a végső érték automatikus kiszámítása.
5. Más beállításokat is módosíthat. Például törölje a pipát a „Price per Darb” elemnél, és jelölje be a „Teljes költség” tételt. Ennek eredményeként lesz egy táblázatunk, amely információkat tartalmaz arról, hogy a termékek mennyibe kerülnek. Ez az átültetési módszer sokkal funkcionálisabb, mint a többi. Ismertessen a pivot táblák előnyeit:

1. Automatizálás. A pivot táblák segítségével automatikusan összesítheti az adatokat, valamint tetszőlegesen megváltoztathatja az oszlopok és oszlopok helyzetét. Ehhez nem kell további lépéseket végrehajtania.
2. Interaktivitás. A felhasználó annyiszor változtathatja meg az információ szerkezetét, ahányszor feladatai elvégzéséhez szüksége van. Például tetszőleges módon módosíthatja az oszlopok sorrendjét, valamint csoportosíthatja az adatokat. Ezt annyiszor lehet megtenni, ahányszor a felhasználónak szüksége van. És szó szerint kevesebb, mint egy percet vesz igénybe.
3. Könnyen formázható adatok. Nagyon könnyű egy pivot táblát úgy elrendezni, ahogyan az ember szeretné. Ehhez csak néhány egérkattintással.
4. Értékek megszerzése. A jelentések készítéséhez használt képletek túlnyomó része egy személy közvetlen elérhetőségében található, és könnyen integrálható egy pivot táblába. Ezek olyan adatok, mint az összegzés, a számtani átlag beszerzése, a cellák számának meghatározása, a szorzás, a megadott minta legnagyobb és legkisebb értékének megtalálása.
5. Összefoglaló diagramok készítésének képessége. Ha a kimutatások újraszámításra kerülnek, a hozzájuk tartozó diagramok automatikusan frissülnek. Lehetőség van annyi diagram létrehozására, amennyire szüksége van. Mindegyik módosítható egy adott feladathoz, és nem kapcsolódnak egymáshoz.
6. Adatszűrési képesség.
7. Lehetőség van pivot tábla felépítésére egynél több forrásinformáció alapján. Ezért funkcionalitásuk még nagyobb lesz.

Igaz, pivot táblák használatakor a következő korlátozásokat kell figyelembe venni:

1. Nem minden információ használható fel pivot táblák létrehozására. Mielőtt erre a célra felhasználnák, a sejteket normalizálni kell. Egyszerű szavakkal: tedd rendbe. Kötelező követelmények: fejléc sor megléte, minden sor teljessége, adatformátumok egyenlősége.
2. Az adatok félautomatikusan frissülnek. Ha új információkat szeretne kapni a pivot táblában, kattintson egy speciális gombra.
3. A pivot táblázatok sok helyet foglalnak el. Ez a számítógép megszakítását okozhatja. Emiatt a fájlt e-mailben is nehéz lesz elküldeni.

Ezenkívül a pivot tábla létrehozása után a felhasználónak nincs lehetősége új információk hozzáadására.