Hivatkozás beillesztése és függvények transzponálása az Excelben

Az Excel hivatkozási és transzponálási funkciói kölcsönösen kizárják egymást. Ez azt jelenti, hogy a transzponált cellák nem működnek hivatkozásként a munkalapon. Más szóval, az eredeti cellákon végzett változtatások nem jelennek meg az átültetett másolaton.

Hivatkozás beillesztése és függvények transzponálása az Excelben

A projektjei azonban gyakran megkövetelik a cellák/oszlopok transzponálását és összekapcsolását. Tehát van mód mindkét funkció használatára? Természetesen van, és négy különböző módszert mutatunk be ennek megvalósítására.

Nyugodtan kijelenthetjük, hogy ezek a trükkök a középhaladó Excel-tudás részét képezik, de ha követi a T betűhöz vezető lépéseket, akkor még teljesen kezdőként sem lesz próbálkozás és hiba.

A beillesztési probléma

Ebben a cikkben tegyük fel, hogy az oszlopokat ugyanarra a lapra kívánja átültetni. Szóval, mivel foglalkozol? Jelölje ki az oszlopokat, nyomja meg a Ctrl + C billentyűkombinációt (Mac esetén a Cmd + C), és válassza ki a beillesztési célt. Ezután kattintson a Beillesztés lehetőségre, válassza a Különleges beillesztés lehetőséget, és jelölje be az Áthelyezés előtti négyzetet.

De amint bejelöli a négyzetet, a Link beillesztése szürkén jelenik meg. Szerencsére van néhány képlet és trükk, amelyek segítenek a probléma megkerülésében. Vessünk egy pillantást rájuk.

TRANSPOSE – Tömbképlet

Ennek a képletnek az a fő előnye, hogy nem kell manuálisan áthúzni a cellákat. Ennek azonban vannak bizonyos árnyoldalai is. Például a méretet nem könnyű megváltoztatni, ami azt jelenti, hogy újra kell használni a képletet, ha a forráscella tartomány megváltozik.

Hasonló problémák azonban más tömbképletekre is vonatkoznak, és ez segít a hivatkozás-transzponálási probléma meglehetősen gyors megoldásában.

1. lépés

Másolja ki a cellákat, és kattintson a bal felső cellára azon a területen, amelybe be szeretné illeszteni a cellákat. Nyomja meg a Ctrl + Alt + V billentyűket a Speciális beillesztés ablak eléréséhez. Ezt az Excel eszköztárból is megteheti.

2. lépés

Miután elérte az ablakot, jelölje be a Formátumok lehetőséget a Beillesztés alatt, válassza a jobb alsó sarokban található Transzponálás lehetőséget, majd kattintson az OK gombra. Ez a művelet csak a formázást transzponálja, az értékeket nem, és ennek két oka van. Először is ismeri a transzponált cellák tartományát. Másodszor, megtartja a cellák eredeti formátumát.

3. lépés

A teljes beillesztési területet ki kell jelölni, és ezt megteheti a formátumok beillesztése után. Most pedig írj =TRANSPOSE ('eredeti tartomány') és nyomja meg a Ctrl + Shift + Enter billentyűket.

Jegyzet: Fontos, hogy az Entert a Ctrl és a Shift billentyűkkel együtt nyomjuk meg. Ellenkező esetben a program nem ismeri fel megfelelően a parancsot, és automatikusan zárójeleket generál.

Összekapcsolás és átültetés – kézi módszer

Igen, az Excel az automatizálásról és a cellák és oszlopok kezelését megkönnyítő függvények használatáról szól. Ha azonban meglehetősen kicsi cellatartománnyal van dolgunk, a kézi összekapcsolás és transzponálás gyakran a leggyorsabb megoldás. El kell ismerni azonban, hogy van lehetőség hibázni, ha nem vagy elég óvatos.

1. lépés

Jelölje ki a cellákat, és másolja/illessze be őket a Speciális beillesztés opcióval. Ezúttal nem jelöli be a Transpone előtti négyzetet, és a Beillesztés alatt hagyja a beállításokat alapértelmezettként.

2. lépés

Kattintson a Hivatkozás beillesztése gombra a bal alsó sarokban, és az adatok hivatkozások formájában kerülnek beillesztésre.

3. lépés

Itt jön a nehéz rész. Manuálisan kell húznia, majd eldobnia a cellákat az új területre. Ugyanakkor ügyelnie kell a sorok és oszlopok cseréjére.

OFFSET képlet

Ez az egyik leghatékonyabb eszköz a cellák beillesztésére, összekapcsolására és transzponálására. Azonban nem biztos, hogy könnyű, ha még nem ismeri az Excelt, ezért igyekszünk a lépéseket a lehető legvilágosabbá tenni.

1. lépés

Elő kell készítenie a bal és a felső számokat. Például, ha három sor van, akkor 0-2, ha pedig két oszlop van, akkor 0-1. A módszer a sorok és oszlopok teljes száma mínusz 1.

2. lépés

Ezután meg kell találnia és meg kell határoznia az alapcellát. Ennek a cellának érintetlenül kell maradnia, amikor másol/beilleszt, ezért használjon speciális szimbólumokat a cellához. Tegyük fel, hogy az alapcella B2: a cella kiemeléséhez be kell szúrnia a dollárjelet. A képletben így kell kinéznie: =OFFSET($B$2.

3. lépés

Most meg kell határoznia az alapcella és a célcella közötti távolságot (sorokban). Ennek a számnak növekednie kell, ha a képletet jobbra mozgatja. Emiatt ne legyen dollárjel a függvényoszlop előtt. Ehelyett az első sor a dollárjellel lesz rögzítve.

Például, ha a függvény az F oszlopban van, a függvénynek így kell kinéznie: =OFFSET($B$2, F$1.

4. lépés

A sorokhoz hasonlóan az oszlopoknak is növekedniük kell az összekapcsolás és az átültetés után. A dollárjelet is használhatja egy oszlop javítására, de engedélyezheti a sorok növekedését. Ennek egyértelművé tétele érdekében a legjobb, ha a példára hivatkozunk, amely így nézhet ki: =ELTOLÁS($B$2, F$1, $E2).

Hogyan Excel az Excelben

A megadott módszer mellett léteznek harmadik féltől származó eszközök is, amelyek segítségével linkelni és transzponálni lehet. És ha a megadott módszerek nem hoznak kielégítő eredményt, akkor a legjobb egy ilyen eszköz használata.

Használta a transzponáló/hivatkozási alkalmazásokat a művelet végrehajtásához az Excelben? Elégedett voltál az eredménnyel? Ossza meg tapasztalatait az alábbi megjegyzések részben.