Útmutatások és példák tömbképletek használatához

Útmutatások és példák tömbképletek használatához

Fontos : Ez a cikk gépi fordítással lett lefordítva, lásd a jognyilatkozatot. A cikk angol változatát itt találhatja meg.

Az Excel power felhasználója váljon kell tudnia végezhet számításokat végezhet, amelyek nem tömbképletek használatával nem tömbképletek használatát. A következő cikk van az Excel Power felhasználói oszlopok Ambrus Zsolt írják és készült 14 és az Excel-képletekben 2002, János Walkenbach, az Excel MVP által írt könyv 15 fejezetekben pénzáramlás.

Tudnivalók a tömbképletekről

A tömbképletekre gyakran CSE-képletként (Ctrl+Shift+Enter) is hivatkoznak, mivel az ilyen képletek beírásakor nem az Enter billentyűt, hanem a Ctrl+Shift+Enter billentyűkombinációt kell lenyomni.

Miért érdemes tömbképleteket használni?

Ha már rendelkezik némi tapasztalattal az Excel képleteinek használatáról, akkor jól tudja, hogy alkalmazásukkal néhány kifejezetten bonyolult művelet is végrehajtható: kiszámítható például, hogy mennyi lesz egy hitel összes költsége a megadott számú éven keresztül. A tömbképletek a következő összetett feladatok végrehajtására alkalmasak:

  • Cellatartományban lévő karakterek számának kiszámítása

  • Csak az adott feltételeknek megfelelő számok összegzése (például meghatározott számtartomány legalacsonyabb, felső és alsó határérték közé eső értékei)

  • Értéktartomány minden n-edik értékének összegzése

A tömbök és tömbképletek rövid ismertetése

A tömbképlet a kérdéses tömb egy vagy több elemén képes több számítást is végrehajtani, majd több vagy egyetlen képletet visszaadni. A tömbre úgy gondoljon, mint értékek egy sorára vagy oszlopára, vagy éppen az ilyen sorok és oszlopok együttesére. Egy cellatartományban létrehozott tömbképlettel például kiszámítható a részösszegek egy oszlopa vagy sora. A tömbképlet elhelyezhető egyetlen cellában is egyetlen összeg kiszámítása végett. A több cellában jelenlévő tömbképleteket többcellás képleteknek, az egyetlen cellában lévőket egycellás képleteknek nevezzük.

A következő szakaszban ismertetett példák a többcellás és az egycellás tömbképletek létrehozásának módját mutatják be.

Próbálja ki!

Ez a gyakorlat azt szemlélteti, hogy miként használhatók többcellás és egycellás tömbképletek az értékesítési mutatók készletének kiszámításához. Az első műveletcsoport egy többcellás képlettel kiszámítja a részösszegek halmazát, a második pedig a végösszeget számítja ki egycellás képlet használatával.

Többcellás tömbképletek

Az alábbiakban egy böngészőbe beágyazott munkafüzetet láthat, amely mintaadatokat tartalmaz. Tudnia kell, hogy a beágyazott munkafüzetek nem alkalmasak tömbképletek létrehozására és módosítására – ehhez szüksége van az Excelre. Bár a beágyazott munkafüzetben láthatja a válaszokat, és bizonyos leírások a tömbképlet működését is elmagyarázzák, de a tömbképletekkel járó előnyök valódi megtapasztalásához az Excelben kell megnyitnia a munkafüzetet.

Többcellás tömbképlet létrehozása
  1. Másolja a teljes alábbi táblázatot a vágólapra, és illessze be egy üres Excel-munkalapra az A1 cellába.

    Üzletkötő

    Autótípus

    Értékesített
    mennyiség

    Egységár

    Teljes
    értékesítés

    Barkóczi

    Négyajtós

    5

    33000

    Kétajtós

    4

    37000

    Harmath

    Négyajtós

    6

    24000

    Kétajtós

    8

    21000

    Lukács

    Négyajtós

    3

    29000

    Kétajtós

    1

    31000

    Pozsony

    Négyajtós

    9

    24000

    Kétajtós

    5

    37000

    Fischer

    Négyajtós

    6

    33000

    Kétajtós

    8

    31000

    Képlet (Végösszeg)

    Végösszeg

    '=SZUM(C2:C11*D2:D11)

    =SZUM(C2:C11*D2:D11)

  2. A kétajtósok és a négyajtósok értékesítőnkénti összforgalmának kiszámításához jelölje ki az E2:E11 cellatartományt, írja be a =C2:C11*D2:D11 képletet, majd nyomja le a Ctrl+Shift+Enter billentyűkombinációt.

  3. Az összes eladás végösszegének kiszámításához jelölje ki az F11 cellát, írja be a =SZUM(C2:C11*D2:D11) képletet, majd nyomja le a Ctrl+Shift+Enter billentyűkombinációt.

Ezt a munkafüzetet letöltheti, ha a munkafüzet alján látható fekete sáv zöld Excel-gombjára kattint. Ezután megnyithatja a fájlt az Excelben, kijelölheti a tömbképleteket tartalmazó cellákat, és a Ctrl+Shift+Enter billentyűkombináció lenyomásával működővé teheti a képletet.

Ha párhuzamosan az Excelben is végrehajtja a műveleteket, akkor válassza a Munka1 lapot, majd jelölje ki az E2:E11 tartományt. Nyomja le az F2 billentyűt, majd írja be az aktuális E2 cellába a következő képletet: =C2:C11*D2:D11. Ha az Enter billentyűt nyomja le, akkor láthatja, hogy ezzel csak az E2 cellába írja be a képletet, és ennek értéke 165000 lesz. A képlet beírása után az Enter helyett nyomja le a Ctrl+Shift+Enter billentyűkombinációt. Ekkor az egész E2:E11 tartományban megjelennek az eredmények. Észreveheti azt is, hogy a szerkesztőlécen a {=C2:C11*D2:D11} képlet látható. Ez jelzi, hogy a képlet tömbképlet, amint az az alábbi táblázatban is látható.

A Ctrl+Shift+Enter lenyomásakor az Excel kapcsos zárójelek közé helyezi a képletet ({ }), majd beszúrja a képlet egy-egy példányát a kijelölt cellatartomány minden cellájába. Mindez nagyon gyorsan történik, így az E oszlopban az autótípusokhoz tartozó értékesítőnkénti összesített érték lesz látható. Ha most kijelöli az E2, majd az E3, (E4 és így tovább) cellát, láthatja, hogy a képlet a következő marad: {=C2:C11*D2:D11}.

Az E oszlop tömbképlettel kiszámított végösszegei

Egycellás tömbképlet létrehozása

Írja be a munkafüzet F10 cellájába az alábbi képletet, és nyomja le a Ctrl+Shift+Enter billentyűkombinációt:

=SZUM(C2:C11*D2:D11)

Az Excel összeszorozza a tömbben lévő értékeket (a C2–D11 cellatartományban), és a SZUM függvénnyel összeadja az összegeket. Az eladások végösszege 159 000 000 Ft. Ez a példa jól szemlélteti, hogy mi mindenre használható egy ilyen típusú képlet. Tegyük fel, hogy 1000 sornyi adat van a munkafüzetben: az adatok egy részének vagy egészének összesítéséhez elég egy tömbképletet beszúrni valamelyik cellába, és nem kell az 1000 soron át lehúzni a képletet.

Jól látható, hogy az egycellás képlet (a G11 cellában) teljesen független a többcellás képlettől (az E2–E11 cellában lévő képlettől). Ez a tömbképletek másik előnyös tulajdonsága: a rugalmasság. Az E oszlopbeli képleteket anélkül módosíthatja vagy törölheti, hogy ez hatással lenne a G11 cellában lévő képletre.

A tömbképletek előnyei közé tartoznak még a következők:

  • Egyezőség:    Az E2 cellától lefelé bármelyik cellára kattintva ugyanaz a képlet látható, így pontosabban végezhető a munka.

  • Biztonság:    A többcellás tömbképletek összetevői nem írhatók felül. Kattintson például az E3 cellára, és nyomja le a Delete billentyűt. A teljes tömb képletének módosításához ki kell jelölnie a teljes cellatartományt (E2–E11), és úgy végrehajtania a változtatást, vagy változatlanul kell hagynia a tömböt. A fokozott biztonság érdekében csak a Ctrl+Shift+Enter billentyűkombináció lenyomása véglegesíti a képletmódosítást.

  • Kisebb fájlméretek:    Sok esetben egyetlen tömbképlet is elegendő több köztes képlet helyett: a munkafüzet például egy tömbképletet használ az E oszlop eredményeinek kiszámításához. Ha szokásos képleteket használt volna (például =C2*D2, C3*D3, C4*D4...), akkor tizenegy különböző képlet adta volna ugyanazt az eredményt.

A tömbképletek szintaxisa

A tömbképletek legnagyobbrészt a szokásos képletszintaxist használják: mindegyikük egyenlőségjellel (=) kezdődik, és az Excel legtöbb előre definiált függvénye használható hozzájuk. A legfontosabb különbség az, hogy tömbképlet használatakor a Ctrl+Shift+Enter billentyűkombinációval írhatja be a képletet. Ekkor az Excel kapcsos zárójelek közé helyezi a tömbképletet – ha kézzel írja be a kapcsos zárójeleket, a program szöveggé alakítja a képletet, és így az nem fog működni.

A tömbképletek a bonyolult képletek összeállításának valóban hatékony eszközei. Az =SZUM(C2:C11*D2:D11) tömbképlet például megegyezik a következővel: =SZUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Tömbképletek megadása és módosítása

Fontos:    A tömbképletek beírásakor és szerkesztésekor mindig a Ctrl+Shift+Enter billentyűkombinációt kell használni. Ez az egycellás és a többcellás képletekre egyaránt vonatkozik.

Többcellás képletek használatakor ne feledje a következőket sem:

  • Az eredmények tárolására szolgáló cellatartományt a képlet beírása előtt jelölje ki. Ezt a többcellás képlet létrehozása előtt az E2–E11 cellatartomány kijelölésével hajtotta végre.

  • A tömbképletben lévő egyes cellák tartalma nem változtatható meg. Jelölje ki az E3 cellát a munkafüzetben, és nyomja le a Delete billentyűt. Az Excel ekkor egy üzenetben közli, hogy a tömb részenként nem módosítható.

  • Áthelyezni vagy törölni csak egy teljes tömbképletet lehet, annak egy-egy részét nem. Ez azt jelenti, hogy tömbképlet szűkítéséhez először a már meglévő képletet kell törölni, majd újra kell kezdeni a műveletsort.

  • Tömbképlet törléséhez jelölje ki a teljes képletet (például =C2:C11*D2:D11), nyomja le a Delete billentyűt, és nyomja le a Ctrl+Shift+Enter billentyűkombinációt.

  • Többcellás tömbképletbe nem lehet üres cellákat beszúrni, és cellák sem törölhetők a képletből.

Tömbképlet kibontása

Bizonyos esetekben szükség lehet a tömbképletek kibontására. A folyamat nem túl bonyolult, de a fenti szabályokat be kell tartani.

Ezen a munkalapon néhány további értékesítési adatot adtunk meg a 12–17. sorban. Szeretnénk úgy frissíteni a tömbképleteket, hogy ezeket a kiegészítő sorokat is tartalmazzák.

Ezt mindenképpen az asztali Excelben kell elvégeznie (miután letöltötte a munkafüzetet a számítógépére).

Tömbképlet bővítése
  1. Másolja a teljes táblázatot egy Excel-munkalap A1 cellájába.

    Üzletkötő

    Autótípus

    Értékesített
    mennyiség

    Egységár

    Teljes
    értékesítés

    Barkóczi

    Négyajtós

    5

    33000

    165000

    Kétajtós

    4

    37000

    148000

    Harmath

    Négyajtós

    6

    24000

    144000

    Kétajtós

    8

    21000

    168000

    Lukács

    Négyajtós

    3

    29000

    87000

    Kétajtós

    1

    31000

    31000

    Pozsony

    Négyajtós

    9

    24000

    216000

    Kétajtós

    5

    37000

    185000

    Fischer

    Négyajtós

    6

    33000

    198000

    Kétajtós

    8

    31000

    248000

    Tóth

    Négyajtós

    2

    27000

    Kétajtós

    3

    30000

    Belinszki

    Négyajtós

    4

    22000

    Kétajtós

    1

    41000

    Verebélyi

    Négyajtós

    5

    32000

    Kétajtós

    3

    36000

    Végösszeg

  2. Jelölje ki az E18 cellát, írja be a végösszeget kiszámító =SZUM(C2:C17*D2:D17) képletet az A20 cellába, és nyomja le a Ctrl+Shift+Enter billentyűkombinációt.
    Az eredmény 2 131 000 lesz.

  3. Jelölje ki az aktuális tömbképletet tartalmazó cellatartományt (E2:E11), valamint az új adatok mellett lévő üres cellákat (E12:E17) – végeredményben az E2:E17 cellatartományt.

  4. Az F2 billentyűt megnyomva lépjen szerkesztési módba.

  5. A szerkesztőlécen írja át a C11 értéket C17 értékre, a D11 értéket D17 értékre, és nyomja le a Ctrl+Shift+Enter billentyűkombinációt.
    Az Excel frissíti a képletet az E2–E11 cellatartományban, és beilleszti a képlet egy példányát az E12–E17 új cellatartományba.

  6. Írja be a =SZUM(C2:C17*D2*D17) tömbképletet az F17 cellába, hogy a 2–17. sorra hivatkozzon, majd nyomja le a Ctrl+Shift+Enter billentyűkombinációt a tömbképlet beviteléhez.
    Az új végösszeg 2 131 000 lesz.

A tömbképletek használatának hátrányai

A tömbképletek nagyszerűek ugyan, de lehetnek hátrányaik is:

  • Nem kizárt, hogy a felhasználó elfelejti lenyomni a Ctrl+Shift+Enter billentyűkombinációt. Ez még a leggyakorlottabb Excel-felhasználóval is előfordulhat. Tömbképlet beírásakor és szerkesztésekor mindig ezt a billentyűkombinációt kell használni.

  • Előfordulhat, hogy a munkafüzet más felhasználói nem értik meg az összeállított képleteket. A tömbképletekről rendszerint kevés magyarázat áll rendelkezésre a kérdéses munkalapon, ezért ha másoknak módosítaniuk kell a kapott munkafüzeteket, célszerű kerülni a tömbképletek használatát, vagy meg kell győződni arról, hogy az érintettek tisztában vannak a tömbképletek használatával és szükség esetén a módosításuk mikéntjével.

  • A számítógép feldolgozási sebességétől és memóriamennyiségétől függően a nagyméretű tömbképletek lassíthatják a számítási folyamatokat.

Vissza a lap tetejére

Tudnivalók a tömbállandókról

A tömbképletek tömbállandókat is magukban foglalnak. Tömbállandók létrehozásához be kell írni egy elemlistát, és kézzel kell kapcsos zárójelek közé ({ }) zárni azt a következő módon:

={1,2,3,4,5}

Mostanra tudja, hogy tömbképletek létrehozásakor le kell nyomni a Ctrl+Shift+Enter billentyűkombinációt. Mivel a tömbállandók a tömbképletek részei, kézzel kell kapcsos zárójelek közé zárni őket. Ezután a Ctrl+Shift+Enter billentyűkombinációval írható be az egész képlet.

Ha vesszővel választja el egymástól az elemeket, vízszintes tartomány (egy sor) jön létre. Pontosvesszőkkel történő elválasztás esetén függőleges tömb (egy oszlop) az eredmény. Kétdimenziós tömb létrehozásához minden egyes sorban vesszőkkel kell tagolni az elemeket, a sorok tagolását pedig pontosvesszőkkel kell végezni.

Íme egy tömb egyetlen sorban: {1,2,3,4}. Ez pedig egy tömb egyetlen oszlopban: {1;2;3;4}. És íme egy tömb, amely két sorból és négy oszlopból áll: {1,2,3,4;5,6,7,8}. A kétsoros tömbben az első sor az 1, 2, 3 és 4, a második sor az 5, 6, 7 és 8 értékből áll. A két sort egy pontosvessző választja el egymástól a 4 és az 5 között.

A tömbképletekhez hasonlóan a tömbállandók is használhatók az Excel legtöbb előre definiált függvényével. A következő szakaszokból megtudhatja, hogy miként hozhatók létre az egyes állandótípusok, és azok hogyan használhatók az Excel függvényeivel.

Vissza a lap tetejére

Egydimenziós és kétdimenziós állandók létrehozása

A következő eljárásokkal gyakorlatot szerezhet a vízszintes, a függőleges és a kétdimenziós állandók létrehozásában.

Vízszintes állandó létrehozása

  1. Használja az előző példákban megismert munkafüzetet, vagy hozzon létre egy újat.

  2. Jelölje ki az A1–E1 cellatartományt.

  3. Írja be a szerkesztőlécre az alábbi képletet, és nyomja le a Ctrl+Shift+Enter billentyűkombinációt:

    ={1,2,3,4,5}

    A jelen esetben kézzel kell beírnia a nyitó és a záró kapcsos zárójelet ({ }).

    Az eredmény a következő:

    Vízszintes tömbállandó a képletben

Függőleges állandó létrehozása

  1. Jelöljön ki a munkafüzetben függőlegesen öt cellát.

  2. Írja be a következő képletet a szerkesztőlécre, majd nyomja le a Ctrl+Shift+Enter billentyűkombinációt:

    ={1;2;3;4;5}

    Az eredmény a következő:

    Függőleges tömbállandó a tömbképletben

Kétdimenziós állandó létrehozása

  1. Jelöljön ki a munkafüzetben egy négy oszlop szélességű és három sor magasságú cellaterületet.

  2. Írja be a szerkesztőlécre az alábbi képletet, és nyomja le a Ctrl+Shift+Enter billentyűkombinációt:

    ={1,2,3,4;5,6,7,8;9,10,11,12}

    Az eredmény a következő:

    Kétdimenziós tömbállandó a tömbképletben

Állandók használata képletekben

Íme egy egyszerű példa, amely állandókat használ:

  1. Hozzon létre egy új munkalapot a mintamunkafüzetben.

  2. Írjon az A1 cellába 3-at, a B1 cellába 4-et, a C1 cellába 5-öt, a D1 cellába 6-ot és az E1 cellába 7-et.

  3. Írja be az A3 cellába az alábbi képletet, és nyomja le a Ctrl+Shift+Enter billentyűkombinációt:

    =SZUM(A1:E1*{1,2,3,4,5})

    Figyelje meg, hogy az Excel újabb kapcsos zárójeleket helyez az állandó köré, mert az tömbképletként lett beírva.

    Tömbképlet tömbállandóval

    Az A3 cellában megjelenik a 85 érték.

A következő szakaszból megtudhatja, hogy miként működik a képlet.

Tömbállandók szintaxisa

Az imént használt képlet több részből áll.

Tömbállandóval rendelkező tömbképlet szintaxisa

1. Függvény

2. Tárolt tömb

3. Operátor

4. Tömbállandó

A zárójelen belüli utolsó elem a tömbállandó: {1,2,3,4,5} . Emlékezzen, hogy az Excel nem teszi kapcsos zárójelek közé a tömbállandókat, így ezt Önnek kell megtennie. Arról se feledkezzen meg, hogy az állandó tömbképlethez adása után a Ctrl+Shift+Enter billentyűkombináció lenyomásával kell bevinnie a képletet.

Mivel az Excel először a zárójelek közötti kifejezéseken hajtja végre a műveleteket, a soron következő két elem a munkafüzetben tárolt értékhalmaz (A1:E1), valamint az operátor. A képlet összeszorozza a tárolt tömbben lévő értékeket az állandó megfelelő értékeivel. Mindez így néz ki:

=SZUM(A1*1,B1*2,C1*3,D1*4,E1*5)

Végül a SZUM függvény összeadja az értékeket, és megjeleníti az összeget (85) az A3 cellában.

Ha nem szeretné a tárolt tömböt használni, hanem csupán a memóriában szeretné végrehajtani a műveletet, írja felül a tárolt tömböt egy másik tömbállandóval:

=SZUM({3,4,5,6,7}*{1,2,3,4,5})

Ennek kipróbálásához másolja a függvényt a vágólapra, jelöljön ki egy üres cellát a munkafüzetben, illessze be a képletet a szerkesztőlécre, és nyomja le a Ctrl+Shift+Enter billentyűkombinációt. Ugyanaz az eredmény jelenik meg, mint amely a korábbi gyakorlatban megjelent a képlet használatkor:

=SZUM(A1:E1*{1,2,3,4,5})

Állandókban használható elemek

A tömbállandók tartalmazhatnak számokat, szöveget, logikai értékeket (például IGAZ és HAMIS) és hibaértékeket (például #HIÁNYZIK). A számok megadhatók egészként, decimális vagy tudományos alakban. Ha szöveget ír be, akkor idézőjelek (") közé kell tennie.

A tömbállandók nem tartalmazhatnak további tömböket, képleteket vagy függvényeket, csak vesszővel vagy pontosvesszővel tagolt szöveget vagy számokat. Az Excel figyelmeztetést jelenít meg a képletek (például {1,2,A1:D4} vagy {1,2,SZUM(Q2:Z8)}) beírásakor. A számértékek nem tartalmazhatnak százalékjelet, dollárjelet, vesszőt vagy zárójelet.

Tömbállandók elnevezése

A tömbállandók használatának egyik legcélszerűbb módja az, ha nevet ad nekik. Az elnevezett állandók használata sokkal egyszerűbb, és így részben el is rejthető a tömbképletek összetettsége mások elől. Tömbképlet elnevezéséhez és képletben történő használatához az alábbiakat kell tennie:

  1. A Képletek lap Definiált nevek csoportjában kattintson a Név megadása gombra.
    Megjelenik az Új név párbeszédpanel.

  2. A Név mezőbe írja be az 1. negyedév kifejezést.

  3. A Hivatkozás mezőbe írja be a következő állandót (ne felejtse el begépelni a kapcsos zárójeleket):

    ={"Január","Február","Március"}

    Ekkor a párbeszédpanel tartalma így néz ki:

    Az Új név párbeszédpanel képlettel

  4. Kattintson az OK gombra, majd jelöljön ki egy sorban három üres cellát.

  5. Írja be a következő képletet, majd nyomja le a Ctrl+Shift+Enter billentyűkombinációt:

    =1. negyedév

    Az eredmény a következő:

    Elnevezett tömb képletként beírva

Ha elnevezett állandót használ tömbképletként, ne felejtse el beírni az egyenlőségjelet. Ha elfelejti, az Excel szöveges karakterláncként fogja értelmezni a tömböt, így a képlet nem a várt eredményt fogja adni. Tartsa szem előtt azt is, hogy szöveg és számok kombinációi is használhatók.

Tömbállandók – problémamegoldás

Ha nem működnek a tömbállandók, a következő problémák okozhatják a hibát:

  • Lehet, hogy néhány elem között nem a megfelelő tagoló karakter áll. Ha kihagy egy vesszőt vagy egy pontosvesszőt, netán rossz helyre írja be valamelyiket, az Excel esetleg helytelenül hozza létre a tömbállandót, vagy figyelmeztetés jelenhet meg.

  • Nem kizárt, hogy a kijelölt cellatartomány nem felel meg az állandóban lévő elemek számának. Ha kijelöl például egy oszlopban hat cellát, hogy azokat egy ötcellás állandóval használja, a #HIÁNYZIK hibaérték fog megjelenni az üres cellában. Ha viszont túl kevés cellát jelöl ki, az Excel kihagyja a műveletből azokat az értékeket, amelyekhez nem tartozik cella.

A tömbállandók működése

A következő példák néhány olyan módszert szemléltetnek, amellyel a tömbállandók használhatók a tömbképletekben. Néhány példában a TRANSZPONÁLÁS függvény oszlopokká alakítja a sorokat, vagy éppen fordítva.

Tömb összes elemének összeszorzása

  1. Hozzon létre egy új munkalapot, majd jelöljön ki egy négy oszlop szélességű és három sor magasságú, üres cellákból álló területet.

  2. Írja be a következő képletet, majd nyomja le a Ctrl+Shift+Enter billentyűkombinációt:

    ={1,2,3,4;5,6,7,8;9,10,11,12}*2

Tömbben lévő elemek négyzetre emelése

  1. Jelöljön ki egy négy oszlop szélességű és három sor magasságú, üres cellákból álló területet.

  2. Írja be a következő tömbképletet, majd nyomja le a Ctrl+Shift+Enter billentyűkombinációt:

    ={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

    Megteheti azt is, hogy a beszúrási jelet (^) használó következő tömbképletet írja be:

    ={1,2,3,4;5,6,7,8;9,10,11,12}^2

Egydimenziós sor transzponálása

  1. Jelölje ki egy oszlop öt üres celláját.

  2. Írja be a következő képletet, majd nyomja le a Ctrl+Shift+Enter billentyűkombinációt:

    =TRANSZPONÁLÁS({1,2,3,4,5})

Noha vízszintes tömbállandót írt be, a TRANSZPONÁLÁS függvény oszloppá alakítja a tömbállandót.

Egydimenziós oszlop transzponálása

  1. Jelölje ki egy sor öt üres celláját.

  2. Írja be a következő képletet, majd nyomja le a CTRL+SHIFT+ENTER billentyűkombinációt:

    =TRANSZPONÁLÁS({1;2;3;4;5})

Noha függőleges tömbállandót írt be, a TRANSZPONÁLÁS függvény sorrá alakítja az állandót.

Kétdimenziós állandó transzponálása

  1. Jelöljön ki egy három oszlop szélességű és négy sor magasságú cellaterületet.

  2. Írja be a következő állandót, majd nyomja le a Ctrl+Shift+Enter billentyűkombinációt.

    =TRANSZPONÁLÁS({1,2,3,4;5,6,7,8;9,10,11,12})

A TRANSZPONÁLÁS függvény minden sort oszlopok sorozatává alakít.

Vissza a lap tetejére

Egyszerű tömbképletek használata

Ebben a szakaszban egyszerű tömbképletekre talál példákat.

Tömbök és tömbállandók létrehozása már meglévő értékekből

A következő példából megtudhatja, hogy miként hozható létre kapcsolat tömbképletekkel a különböző munkalapok cellatartományai között, valamint hogy milyen módon hozható létre tömbállandó ugyanabból az értékkészletből.

Tömb létrehozása már meglévő értékekből

  1. Egy Excel-munkalapon jelölje ki a C8:E10 cellatartományt, és írja be a következő képletet:

    ={10,20,30;40,50,60;70,80,90}

    Mivel számtömböt hoz létre, ne felejtse el beírni a nyitó kapcsos zárójelet { a 10 elé, illetve a záró kapcsos zárójelet } a 90 után.

  2. Ezután nyomja le a Ctrl+Shift+Enter billentyűkombinációt, amely beviszi ezt a számtömböt a C8:E10 cellatartományba egy tömbképlet használatával.
    A munkalap C8:E10 cellatartományának így kell kinéznie:

    10

    20

    30

    40

    50

    60

    a 70

    80

    90

  3. Jelölje ki a C1:E3 cellatartományt.

  4. Írja be a következő képletet a szerkesztőlécen, majd nyomja le a Ctrl+Shift+Enter billentyűkombinációt:

    =C8:E10

    Megjelenik egy 3x3 cellás cellatartomány a C1:E3 cellatartományban ugyanazokkal az értékekkel, mint a C8:E10 cellatartományban.

Tömbállandó létrehozása már meglévő értékekből

  1. Jelölje ki a C1:C3 cellatartományt, és az F2 billentyűt megnyomva lépjen szerkesztési módba.
    A tömbképlet ekkor még mindig =C8:E10 kell, hogy legyen.

  2. Az F9 billentyű megnyomásával alakítsa a cellahivatkozásokat értékekké. Az Excel tömbállandóvá alakítja az értékeket. A képlet most már ={10;20;30;40;50;60;70;80;90}, ugyanúgy, mint a C8:E10 cellatartományé.

  3. A Ctrl+Shift+Enter billentyűkombinációval írja be a tömbállandót tömbképletként.

Cellatartományban lévő karakterek megszámlálása

A következő példából megtudhatja, hogy miként állapítható meg a kérdéses cellatartományban található karakterek szóközökkel együtt mért száma.

  1. Másolja a teljes táblázatot a vágólapra, és illessze be egy Excel-munkalapra az A1 cellába.

  2. Jelölje ki az A9 cellát, majd a Ctrl+Shift+Enter billentyűkombinációt lenyomva számítsa ki az A2:A6 cellatartományban lévő karakterek számát (66).

  3. Jelölje ki az A12 cellát, majd a Ctrl+Shift+Enter billentyűkombinációval ugorjon az A2:A6 cellatartomány leghosszabb cellájára (A3).

Adatok

Ezek itt

olyan cellák,

amelyek

mondatot

alkotnak.

Karakterek száma az A2:A6 cellatartományban

=SZUM(HOSSZ(A2:A6))

A leghosszabb cella tartalma (A3)

=INDEX(A2:A6;HOL.VAN(MAX(HOSSZ(A2:A6));HOSSZ(A2:A6);0);1)

Az A9 cellában használt következő képlet megszámolja a karaktereket az A2–A6 cellában (66).

=SZUM(HOSSZ(A2:A6))

Ebben az esetben a HOSSZ függvény a tartomány egyes celláiban talált szöveges karakterláncok hosszát adja eredményül. A SZUM függvény ezt követően összeadja a szóban forgó értékeket, majd megjeleníti az eredményt (66) a képletet tartalmazó A9 cellában.

Cellatartomány n legkisebb értékének keresése

Ez a példa azt mutatja, hogy milyen módszerrel kereshető meg a kérdéses cellatartomány három legkisebb értéke.

  1. Jelölje ki az A16–A18 cellákat.
    Ez a cellacsoport fogja tárolni a tömbképlet által visszaadott eredményeket.

  2. Írja be a következő képletet a szerkesztőlécre, majd nyomja le a Ctrl+Shift+Enter billentyűkombinációt:

    =KICSI(A5:A14,{1;2;3})

Az A16–A18 cellában a 400, a 475 és az 500 érték szerepel.

Ez a képlet egy tömbállandó használatával háromszor értékeli a KICSI függvényt, majd visszaadja a tömbben (tehát az A1:A10 cellatartományban) tárolt legkisebb (1), második legkisebb (2) és harmadik legkisebb (3) tagot. További értékek kereséséhez további argumentumokat kell felvenni az állandóba, illetve ugyanennyi eredménycellát kell beszúrni az A12:A14 cellatartományba. Ezzel a képlettel egyéb függvények is használhatók, például a SZUM vagy az ÁTLAG függvény:

=SZUM(KICSI(A 5 :A1 4 ;{1;2;3}))

=ÁTLAG(KICSI(A 5 :A1 4 ;{1;2;3}))

Cellatartomány n legnagyobb értékének keresése

Valamely tartomány legnagyobb értékeinek megkereséséhez írja felül a KICSI függvényt a NAGY függvénnyel. A következő példában ezenfelül a SOR és az INDIREKT függvény is helyet kapott.

  1. Jelölje ki az A1–A3 cellatartományt.

  2. Írja be a szerkesztőlécre a következő képletet, és nyomja le a Ctrl+Shift+Enter billentyűkombinációt:

    =NAGY(A5:A14;SOR(INDIREKT("1:3")))

Az A1–A3 cellában a 3200, a 2700 és a 2000 érték szerepel.

E ponton nem árt szót ejteni a SOR és az INDIREKT függvényről. A SOR függvénnyel egymást követő egész számok tömbje hozható létre: jelölje ki például a gyakorló munkafüzet egyik üres oszlopának tíz celláját, írja be a következő képletet az A5:A14 cellatartományba, és nyomja le a Ctrl+Shift+Enter billentyűkombinációt:

=SOR(1:10)

A képlet tíz egymás után következő egész számot helyez el az oszlopban. Az egyik lehetséges probléma megértéséhez szúrjon be egy sort a tömbképletet tartalmazó tartomány (az első sor) fölé. Az Excel frissíti a sorhivatkozásokat, és a képlet a 2 és 11 közötti egész számokat jeleníti meg. A probléma megoldása végett vegye fel az INDIREKT függvényt a képletbe:

=SOR(INDIREKT("1:10"))

Az INDIREKT függvény szöveges karakterláncokat használ argumentumaiként (ezért szerepel idézőjelek között az 1:10 tartomány). Az Excel nem igazítja a szöveges értékeket sorok beszúrásakor vagy a tömbképlet más módon történő áthelyezésekor, ezért a SOR függvény mindig a kívánt egész számok tömbjét adja eredményül.

Érdemes megvizsgálni a korábban már alkalmazott képletet (=NAGY(A5:A14,SOR(INDIREKT("1:3")))) a belső zárójelektől kifelé haladva: az INDIREKT függvény szöveges értékek készletét adja vissza, jelen esetben az 1, a 2 és a 3 értéket. A SOR függvény viszont egy háromcellás oszlopos tömböt eredményez. A rendszer által háromszor értékelt NAGY függvény az A5:A14 cellatartomány értékeit használja, a SOR függvény által visszaadott minden egyes hivatkozáshoz egyszer. A háromcellás oszlopos tömbbe a 3200, a 2700 és a 2000 érték kerül. Ha további értékeket szeretne keresni, nagyobb cellatartományt kell hozzáadnia az INDIREKT függvényhez.

Ez a képlet egyéb függvényekkel is használható, például a SZUM és az ÁTLAG függvénnyel.

Cellatartomány leghosszabb szöveges karakterláncának megkeresése

A képlet csak akkor működik, ha az adattartomány egyetlen oszlopnyi cellát tartalmaz. Adja meg a következő képletet a Munka3 lap A16 cellájában, majd nyomja le a Ctrl+Shift+Enter billentyűkombinációt:

=INDEX(A6:A9;HOL.VAN(MAX(HOSSZ(A6:A9));HOSSZ(A6:A9);0);1)

Az A16 cellában megjelenik az „olyan cellák,” kezdetű szöveg.

A képletet a belső elemektől kifelé haladva több elem építi fel. A HOSSZ függvény az A6:A9 cellatartományban lévő egyes elemek hosszát adja eredményül. A MAX függvény kiszámítja az elemek közül a legnagyobb értéket, amely egyben az A7 cellában található leghosszabb szöveges karakterlánc.

Itt kezd egy kicsit bonyolultabb lenni a képlet: a HOL.VAN függvény kiszámítja a leghosszabb szöveges karakterlánc eltolását (viszonylagos helyzetét). Ehhez három argumentumra van szüksége: egy keresett értékre, egy keresési tömbre és egy egyezéstípusra. A HOL.VAN függvény megkeresi a megadott keresett értéket a keresési tömbben. A jelen esetben a keresett érték a leghosszabb szöveges karakterlánc:

(MAX(HOSSZ( A6 : A9 ))

A karakterlánc ebben a tömbben van:

HOSSZ( A6:A9 )

Az egyezéstípus argumentuma 0, értéke pedig 1, 0 vagy -1 lehet. Ha az 1 értéket adja meg, a HOL.VAN függvény azt a legnagyobb értéket adja eredményül, amely kisebb vagy egyenlő, mint a keresett érték. A 0 érték hatására a HOL.VAN függvény azt az első értéket eredményezi, amely pontosan egyenlő a keresett értékkel. A -1 értéket megadva a HOL.VAN függvény azt a legkisebb értéket keresi meg, amely nagyobb vagy egyenlő, mint a keresett érték. Ha nem ad meg keresett értéket, az Excel az 1 értéket feltételezi.

Az INDEX függvény egy tömböt, valamint egy azon belül található sor- és oszlopszámot használ. Az A6:A9 cellatartomány a tömböt, a HOL.VAN függvény a cellacímet adja meg, az utolsó argumentum (1) pedig azt, hogy az értéket a tömb első oszlopából olvassa a program.

Vissza a lap tetejére

Speciális tömbképletek használata

Ebben a szakaszban speciális tömbképletekre talál példákat.

Hibaértékeket tároló tartomány összegzése

Az Excel SZUM függvénye nem használható akkor, ha hibaértéket (például #HIÁNYZIK) tartalmazó tartományt szeretne összegezni. A következő példából megtudhatja, hogy miként összegezhetők a hibákat is tartalmazó Adatok nevű tartományban tárolt értékek:

=SZUM(HA(HIBÁS(Adatok),"",Adatok))

A képlet egy, az eredeti értékeket a hibaértékek nélkül tartalmazó új tömböt hoz létre. Belülről kifelé haladva: a HIBÁS függvény megkeresi a cellatartományban (Adatok) a hibákat. A HA függvény meghatározott értéket ad eredményül, ha egy megadott feltétel IGAZ értékű, és egy másik értéket, ha a feltétel értéke HAMIS. A jelen esetben valamennyi hibaértékhez üres karakterláncokat fog visszaadni (""), mivel az értékek IGAZ értékkel teljesülnek – visszaadja a fennmaradó értékeket is a tartományból (Adatok), hiszen azok HAMIS értékkel teljesülnek, azaz nem szerepelnek bennük hibaértékek. A SZUM függvény ezután kiszámítja a szűrt tömb végösszegét.

Tartomány hibaértékszámának kiszámítása

Ez a példa hasonló az előző képlethez, de kiszűrésük helyett az Adatok nevű tartományban lévő hibaértékek számát adja eredményül:

=SZUM(HA(HIBÁS(Adatok);1;0))

Ez a képlet olyan tömböt hoz létre, amely az 1 értékkel jelzi a hibákat tartalmazó, 0 értékkel pedig a hiba nélküli cellákat. A képlet egyszerűsíthető úgy, hogy eredménye ugyanaz legyen – ehhez távolítsa el a HA függvényhez tartozó harmadik argumentumot a következő módon:

=SZUM(HA(HIBÁS(Adatok);1))

Ha nem adja meg az argumentumot, a HA függvény a HAMIS eredményt fogja adni abban az esetben, ha egy cella nem tartalmaz hibaértéket. A képlet még tovább egyszerűsíthető:

=SZUM(HA(HIBÁS(Adatok)*1))

Ez a változat azért működik, mert az IGAZ*1 művelet 1, a HAMIS*1 művelet pedig 0 értékű.

Értékek összegzése feltételek alapján

Bizonyos esetekben szükség lehet az értékeket feltételek alapján összegezni. A következő tömbképlet például csak a pozitív egész számokat összegzi az Értékesítés nevű tartományban:

=SZUM(HA(Értékesítés>0;Értékesítés))

A HA függvény pozitív és hamis értékek tömbjét hozza létre. A SZUM függvény figyelmen kívül hagyja a hamis értékeket, mert a 0+0 művelet eredménye 0. Az ebben a képletben használt cellatartomány tetszőleges számú sorból és oszlopból állhat.

Lehetősége van több feltételnek eleget tévő értékek összegzésére is. Az alábbi tömbképlet például kiszámítja a nullánál nagyobb, de ugyanakkor ötnél nem nagyobb értékeket:

=SZUM((Értékesítés>0)*(Értékesítés<=5)*(Értékesítés))

Tartsa szem előtt, hogy a képlet hibát eredményez, ha a tartomány akár csak egy nem numerikus cellát is tartalmaz.

Létrehozhatók VAGY típusú feltételt használó tömbképletek is. Összegezheti például az ötnél kisebb és tizenötnél nagyobb értékeket:

=SZUM(HA((Értékesítés<5)+(Értékesítés>15);Értékesítés))

A HA függvény megkeresi az összes, ötnél kisebb és tizenötnél nagyobb értéket, majd átadja a talált értékeket a SZUM függvénynek.

Az ÉS, valamint a VAGY függvény nem használható közvetlenül tömbképletekben, mert egyetlen eredményt ad vissza (IGAZ vagy HAMIS), míg a tömbfüggvények működéséhez eredménytömbök szükségesek. A probléma az előző képletben használt logika alkalmazásával megoldható. Megfogalmazható mindez úgy is, hogy matematikai műveleteket (például összeadást vagy szorzást) hajt végre a VAGY, illetve az ÉS feltételnek eleget tévő értékeken.

Nullákat kizáró átlag kiszámítása

E példa alapján eltávolíthatja a nullákat az adott tartományból, ha átlagolni szeretné az abban lévő értékeket. A képletben egy Értékesítés nevű adattartomány szerepel:

=ÁTLAG(HA(Értékesítés<>0;Értékesítés))

A HA függvény létrehozza a nullával nem egyenlő értékek tömbjét, és átadja a talált értékeket az ÁTLAG függvénynek.

Két cellatartomány közötti különbségek megszámlálása

Ez a tömbképlet összehasonlítja az Adatok1 és az Adatok2 cellatartomány értékeit, és a két tartomány különbségeinek számát adja eredményül. Amennyiben a két tartomány megegyező tartalmú, a képlet nullát ad eredményül. A képlet használatához a cellatartományoknak megegyező méretűnek és kiterjedésűnek kell lenniük (például ha az Adatok1 tartomány 3 soros és 5 oszlopos, akkor az Adatok2 tartománynak is 3 sorból és 5 oszlopból kell állnia):

=SZUM(HA( Adatok1 =Adatok2;0;1))

A képlet létrehoz egy új tömböt, amelynek mérete megegyezik az összehasonlított tartományok méretével. A HA függvény feltölti a tömböt a 0 és az 1 értékkel (a 0 a különbségeket, az 1 az azonos cellákat jelöli). A SZUM függvény ezt követően kiszámítja a tömbben lévő értékek összegét.

A képlet egyszerűsíthető:

=SZUM(1*( Adatok1 <> Adatok2 ))

Az adott tartományban lévő hibaértékeket megszámláló képlethez hasonlóan ez a képlet is azért működik, mert az IGAZ*1 művelet eredménye 1, a HAMIS*1 műveleté pedig 0.

Tartománybeli legnagyobb érték helyének megkeresése

Ez a tömbképlet az Adatok nevű egyoszlopos tartomány legnagyobb értékét tartalmazó sor számát adja eredményül:

=MIN(HA(Adatok=MAX(Adatok),SOR(Adatok),""))

A HA függvény létrehoz egy, az Adatok nevű tartománynak megfelelő új tömböt. Ha valamelyik megfelelő cella tartalmazza a tartomány legnagyobb értékét, a tömbben szerepel a sor száma. Ha nem, a tömb egy üres karakterláncot ("") tartalmaz. A MIN függvény az új tömböt használja második argumentumaként, és a legkisebb értékét adja vissza – ez az érték az Adatok tartomány legnagyobb értékét tároló sor számával egyezik meg. Ha az Adatok nevű tartomány azonos legnagyobb értékeket tartalmaz, a képlet az első érték sorának számát adja eredményül.

A legnagyobb értéket tartalmazó cella tényleges címének megjelentéséhez a következő képletet használhatja:

=CÍM(MIN(HA(Adatok=MAX(Adatok),SOR(Adatok),"")),OSZLOP(Adatok))

Vissza a lap tetejére

További segítségre van szüksége?

Bármikor segítséget kérhet az Excel technikai közösségétől és az Answers-közösségtől, az Excel User Voice webhelyen pedig új funkciókra vagy fejlesztésekre tehet javaslatot.

Megjegyzés : Gépi fordítás jognyilatkozata: Ez a cikk számítógép által, emberi közreműködés nélkül lett lefordítva. A Microsoft ezeket a gépi fordításokat azért nyújtja, hogy az angol nyelvet nem beszélők minél több tartalomhoz tudjanak hozzáférni a Microsoft termékeivel, szolgáltatásaival és technológiáival kapcsolatban. A gépi fordítás miatt előfordulhat, hogy a szöveg szóhasználati, szintaktikai vagy helyesírási hibákat tartalmaz.

Lásd még

A képletek – áttekintés

Ismeretek bővítése
Oktatóanyagok megismerése
Új szolgáltatások listájának lekérése
Részvétel az Office Insider programban

Hasznos volt az információ?

Köszönjük a visszajelzését!

Köszönjük visszajelzését. Jobbnak látjuk, ha az Office egyik támogatási szakemberéhez irányítjuk.

×