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

Ez a gyakorlat bemutatja, hogyan használhat többcellás és egycellás tömbképleteket értékesítési számok halmazának számításaihoz. Az első lépéshalmaz egy többcellás képlettel számítja ki a részösszegek halmazát. A második készlet egy egycellás képlettel számítja ki a végösszeget.

• Többcellás tömbképletek

Másolja a teljes alábbi táblázatot a vágólapra, és illessze be egy üres munkalapra az A1 cellába.

Értékesítő  Személy	Autó  Típus	Mennyiség  Eladott	Egység  Ár	Teljes  Forgalom
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)

1. 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 cellákat, írja be a =C2:C11*D2:D11 képletet, majd nyomja le a Ctrl+Shift+Enter billentyűkombinációt.

2. 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.

Amikor lenyomja a Ctrl+Shift+Enter billentyűkombinációt, az Excel kapcsos zárójelekkel ({ }) veszi körül a képletet, és beszúrja a képlet egy példányát a kijelölt tartomá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}. 

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

Írja be a munkafüzet D13 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 1 590 000 USD. Ez a példa jól szemlélteti, hogy mi mindenre használható egy ilyen típusú képlet. Tegyük fel, hogy 1 000 sornyi adat van a munkafüzetben. Az adatok egy részét vagy egészét összeadhatja úgy, hogy egyetlen cellában hoz létre tömbképletet ahelyett, hogy a képletet lefelé húzza az 1 000 soron.

Jól látható, hogy az egycellás képlet (a D13 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 D13 cellában lévő képletre.

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

• Konzisztencia    Egyezőség: Az E2 cellától lefelé bármelyik cellára kattintva ugyanaz a képlet látható. A konzisztencia révén 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. Biztonsági intézkedésként a képlet módosításának megerősítéséhez le kell nyomnia a Ctrl+Shift+Enter billentyűkombinációt.

• 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 általában szabványos képletszintaxist használnak. Mindegyikük egyenlőségjellel (=) kezdődik, és az Excel bármelyik előre definiált függvényét használhatja a tömbképletekben. A fő különbség az, hogy tömbképlet használatakor lenyomja a Ctrl+Shift+Enter billentyűkombinációt a képlet beírásához. Ha ezt teszi, az Excel kapcsos zárójelekkel veszi körül a tömbképletet – ha kézzel írja be a kapcsos zárójeleket, a képlet szöveges sztringgé lesz konvertálva, és nem fog működni.

A tömbfüggvények használata hatékony megoldás lehet összetett képletek létrehozására. 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ö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éplettartományt (például E2:E11), majd nyomja le a Deletebillentyű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.

Bizonyos esetekben szükség lehet a tömbképletek kibontására. Jelölje ki a meglévő tömbtartomány első celláját, és folytassa, amíg ki nem jelöli azt a teljes tartományt, amelybe ki szeretné terjeszteni a képletet. A képlet szerkesztéséhez nyomja le az F2 gombot, majd a CTRL+SHIFT+ENTER billentyűkombinációt a képlettartomány módosítása után. A kulcs a teljes tartomány kijelölése, kezdve a tömb bal felső cellájával. A bal felső cella lesz szerkesztve.

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 a képleteket. A gyakorlatban a tömbképletek magyarázata általában nem a munkalapon van. 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.

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ömbkonstansok 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.

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. Egy üres munkalapon jelölje ki az A1 – E1 cellákat.

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}

   Ebben az esetben be kell írnia a nyitó és a záró kapcsos zárójelet ({ }), az Excel pedig hozzáadja a második készletet.

   Az eredmény a következő.

   

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ő:

   

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 következő képletet a szerkesztőlécre, majd 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ő:

   

Á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.

   

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

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

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

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 a konstans 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})

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ömbkonstansok nem tartalmazhatnak további tömböket, képleteket vagy függvényeket. Más szóval csak vesszővel vagy pontosvesszővel elválasztott szöveget vagy számokat tartalmazhatnak. Az Excel figyelmeztető üzenetet jelenít meg, amikor képletet ad meg, például {1\2\A1:D4} vagy {1\2\SZUM(Q2:Z8)}. A numerikus értékek nem tartalmazhatnak százalékjeleket, dollárjeleket, vesszőket vagy zárójeleket sem.

A tömbkonstansok 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 a Név meghatározása 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:

   

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ő:

   

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.

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ömbkonstansot, 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 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ő konstanst, 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.

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. Azt is megmutatja, hogy milyen módon hozható létre tömbkonstans 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
   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ömbkonstans 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. 

2. Az F9 billentyűt megnyomva alakítsa a cellahivatkozásokat értékké. Az Excel tömbkonstanssá alakítja az értékeket. A képletnek most a következőnek kell lennie: ={10\20\30;40\50\60;70\80\90}.

3. A Ctrl+Shift+Enter billentyűkombinációval írja be a tömbkonstansot 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.

   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)

2. Jelölje ki az A8 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 A10 cellát, majd a Ctrl+Shift+Enter billentyűkombinációval ugorjon az A2:A6 cellatartomány leghosszabb cellájára (A3).

Az A8 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).

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. Írjon be néhány véletlenszerű számot az A1:A11 cellákba.

2. Jelölje ki a C1–C3 cellákat. Ez a cellacsoport fogja tárolni a tömbképlet által visszaadott eredményeket.

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

   =KICSI(A1:A11,{1;2;3})

Ez a képlet egy tömbkonstans 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. Ezzel a képlettel további függvényeket is használhat, például a SZUM vagy az ÁTLAG függvényeket. Például:

=SZUM(KICSI(A1:A10,{1\2\3}))

=ÁTLAG(KICSI(A1:A10,{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 a D1–D3 cellákat.

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

   =NAGY(A1:A10,SOR(INDIREKT("1:3")))

Ezen a ponton ez segíthet egy kicsit megismerni a SOR és a KÖZVETETT függvényeket. A SOR függvénnyel egymást követő egész számokból álló tömböt hozhat létre. Jelöljön ki például egy 10 cellát tartalmazó üres oszlopot a gyakorlati munkafüzetben, írja be ezt a tömbképletet, majd 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 sztringeket használ argumentumként (ezért az 1:10 tartományt idézőjelek veszik körül). Az Excel nem módosítja a szöveges értékeket sorok beszúrásakor vagy a tömbképlet más módon történő áthelyezésekor. Ennek eredményeképpen a SOR függvény mindig létrehozza a kívánt tömböt az egész számokbó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 kié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.

Ahogy a korábbi példákkal, ez a képlet más 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

Lépjen vissza a korábbi szöveges sztringre, írja be a következő képletet egy üres cellába, és nyomja le a Ctrl+Shift+Enter billentyűkombinációt:

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

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 A2:A6 cellatartomány egyes elemeinek hosszát adja vissza. A MAX függvény az elemek közül a legnagyobb értéket számítja ki, amely az A3 cellában található leghosszabb szöveges sztringnek felel meg.

Az alábbiakban egy kicsit összetettebb a helyzet. A HOL.VAN függvény a leghosszabb szöveges sztringet tartalmazó cella eltolását (relatív pozícióját) számítja ki. Ehhez három argumentumra van szükség: egy keresési értékre, egy keresési tömbre és egy egyezéstípusra. A HOL.VAN függvény megkeresi a keresési tömbben a megadott keresési értéket. Ebben az esetben a keresési érték a leghosszabb szöveges sztring:

(MAX(HOSSZ(A2:A6))

A karakterlánc ebben a tömbben van:

HOSSZ(A2:A6)

Az egyezéstípus argumentuma 0. Az egyezés típusa 1, 0 vagy -1 értéket tartalmazhat. Ha 1 értéket ad meg, a HOL.VAN a keresési értéknél kisebb vagy azzal egyenlő legnagyobb értéket adja vissza. Ha 0 értéket ad meg, a HOL.VAN az első értéket adja vissza, ami pontosan megegyezik a keresési értékkel. Ha -1 értéket ad meg, a HOL.VAN a megadott keresési értéknél nagyobb vagy azzal egyenlő legkisebb értéket adja vissza. Ha kihagy egy egyezéstípust, az Excel az 1-et 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 A2:A6 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.

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))