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

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

Megjegyzés: Szeretnénk, ha minél gyorsabban hozzáférhetne a saját nyelvén íródott súgótartalmakhoz. Ez az oldal gépi fordítással lett lefordítva, ezért nyelvtani hibákat és pontatlanságokat tartalmazhat. A célunk az, hogy ezek a tartalmak felhasználóink hasznára váljanak. Kérjük, hogy a lap alján írja meg, hogy hasznos volt-e az Ön számára az itt található információ. Az eredeti angol nyelvű cikket itt találja.

A tömbképletek olyan képletek, amelyek több műveletet végezhetnek egy tömb egy vagy több elemében. Úgy gondolhatja, hogy egy tömb az értékek sorában vagy oszlopában található, illetve a sorok és az oszlopok kombinációja. A tömbképletek több találatot vagy egyetlen eredményt adhatnak vissza.

Az Office 365szeptember 2018-as frissítésével kezdődően a több találatot visszaadó képletek automatikusan kiöntik őket lefelé vagy szomszédos cellákba. Ez a változás a működésben számos új dinamikus tömb függvénytis tartalmaz. Dinamikus tömbképletek, legyen szó akár meglévő függvényekről vagy a dinamikus tömbképletek használatáról, csak egyetlen cellába kell bevinni, majd az ENTER billentyűt lenyomva megkell erősíteni. Korábbi verziók esetén a régi tömbképletek először a teljes kimeneti tartomány kijelölését igénylik, majd a CTRL + SHIFT + ENTER billentyűkombinációvaligazolják a képletet. Ezek általában CSE -képletek.

A tömbképletek komplex feladatok elvégzéséhez használhatók, például:

  • Minta adatkészletek gyors létrehozása

  • Megszámolja a cellatartományban található karakterek számát.

  • Csak az adott feltételeknek megfelelő számok összegzése (például egy adott értéktartomány legalacsonyabb értékei vagy a felső és az alsó határérték közé eső számok).

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

Az alábbi példák bemutatják, hogy miként hozhat létre többcellás és egycellás tömbképletek. Ha lehetséges, a dinamikus tömbképletek némelyikével, valamint a meglévő tömbképletek dinamikus és örökölt tömbökben is szerepelnek példák.

Példák letöltése

Töltse le a példaként használt munkafüzetet a jelen cikkben szereplő összes tömbképletként.

Ebből a gyakorlatból megtudhatja, hogy miként használhatja a többcellás és az egycellás tömbképletek az értékesítési számok halmazának kiszámításához. Az első lépések a többcellás képletek segítségével számítják ki a részösszegeket. A második halmaz egy egycellás képlettel számítja ki a végösszeget.

  • Többcellás tömbképletek

    Többcellás tömb függvény a következő cellában: F19 * G10: G19 az Egységár által eladott autók számának kiszámításához

  • Itt számítjuk ki a Coupes és a szedánok teljes értékesítését az egyes üzletkötők számára, ha beírja az = F10: F19 * G10: G19 a H10 cellában.

    Ha lenyomja az ENTERbillentyűt, a találatok kifolyását a H10: H19 cellákban láthatja. Figyelje meg, hogy a kifolyási tartománnyal kiemelve van a szegély, ha a kifolyási tartománnyal egy tetszőleges cellát jelöl ki. Azt is megteheti, hogy a H10: H19 cellákban lévő képletek szürkén jelennek meg. Csak itt van hivatkozás, ezért ha módosítani szeretné a képletet, ki kell jelölnie a H10 cellát, ahol a fő képlet él.

  • Egycellás tömbképlet

    Egycellás tömbképlet a végösszeg kiszámításához az = SZUM (F10: F19 * G10: G19) képlettel

    A minta H20 írja vagy másolja be az = SZUM (F10: F19 * G10: G19)képletet, és nyomja le az ENTER billentyűt.

    Ebben az esetben az Excel összeszorozza a tömb értékeit (az F10-től G19), majd a SZUM függvénnyel összeadja az összegeket. Az eredmény a $1 590 000 végösszege az értékesítésekben.

    Ez a példa jól szemlélteti, hogy mi mindenre használható egy ilyen típusú képlet. Tegyük fel például, hogy 1 000-ös adatsorral rendelkezik. A képletek egy részét vagy egészét összesítheti úgy, hogy a képletet a 1 000 sorok között húzza le a képlet helyett. Azt is megfigyelheti, hogy a H20 cellában lévő egycellás képlet teljesen független a többcellás képlettől (a H10-től H19-ig terjedő cellákban lévő képlet). Ez a tömbképletek másik előnyös tulajdonsága: a rugalmasság. A többi képletet módosíthatja a H oszlopba anélkül, hogy a képletet befolyásolná a H20. Azt is megteheti, hogy független összegeket szeretne benyújtani, mivel ez a funkció segít ellenőrizni az eredmények pontosságát.

  • A dinamikus tömbképletek a következő előnyöket is kínálják:

    • Következetesség:    Ha a H10 lefelé mutató bármelyik cellájára kattint, akkor ugyanazt a képletet láthatja. Az egységesség a nagyobb pontosság érdekében segíthet.

    • Biztonság:    Többcellás tömbképlet összetevőjét nem lehet felülírni. Kattintson például a cella H11 gombra, és nyomja le a DELETE billentyűt. Az Excel nem változtatja meg a tömb kimenetét. A módosításhoz ki kell jelölnie a tömb bal felső celláját vagy a H10 cellá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. Az autós értékesítési példa például egy tömbképlet használatával számítja ki az E oszlop eredményét. Ha olyan normál képleteket használt, mint például = F10 * G10, F11 * G11, F12 * G12 stb., a 11 különböző képletet használta az eredmény kiszámításához. Ez nem egy nagy üzlet, de mi a teendő, ha több ezer sor van összesen? Ezután nagy különbséget tehet.

    • Hatékonyság:    A tömbképletek hatékony megoldást adhatnak a komplex képletek készítésére. Az = SZUM (F10: F19 * G10: G19) tömbképlet megegyezik a következővel: = SZUM (F10 * G10, F11 * G11, F12 * G12, F13 * f14, G14 * F15, F16 * G16, F17 * G17, F18 * G18, F19 * G19, *

    • Ömlött    A dinamikus tömbképletek automatikusan bekerülnek a kimeneti tartományba. Ha a forrásadatok egy Excel-táblázatban szerepelnek, akkor a dinamikus tömbképletek automatikusan átkerülnek az adatsorokba.

    • #SPILL! hiba    A dinamikus tömbök bevezette a #SPILL! hibát, amely azt jelzi, hogy a tervezett kifolyási intervallum valamilyen okból le van tiltva. A blokkolás feloldásakor a képlet automatikusan átkerül a képletbe.

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} vagy = {"január", "február", "március"}

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. Ha kétdimenziós tömböt szeretne létrehozni, az egyes sorokban lévő elemeket pontosvesszővel kell elválasztani, és minden sort pontosvesszővel kell korlátozni.

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. Ebben a példában a szekvencia függvény segítségével automatikusan létrehozhatja a tömbök állandóit, valamint a kézzel beírt konstansokat.

  • Vízszintes állandó létrehozása

    Használja az előző példákban megismert munkafüzetet, vagy hozzon létre egy újat. Jelöljön ki egy üres cellát, és írja be az = szekvenciát (1, 5). A SZEKVENCIa függvény 1 sort hoz létre 5 oszlopból az = {1, 2, 3, 4, 5}értékkel. A következő eredmény jelenik meg:

    Vízszintes tömbképlet létrehozása = SZEKVENCIával (1, 5) vagy = {1, 2, 3, 4, 5}

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

    Jelölje ki az alatta lévő üres cellát, és írja be az = szekvencia (5)vagy az = {1; 2; 3; 4; 5}képletet. A következő eredmény jelenik meg:

    Függőleges tömbképlet létrehozása = SZEKVENCIával (5) vagy = {1; 2; 3; 4; 5}

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

    Jelöljön ki egy üres cellát a helyiséggel jobbra és alatta, és írja be az = szekvencia (3, 4)értéket. Az eredmény a következő:

    3 sor létrehozása 4 oszlopból álló tömb állandóval az = SZEKVENCIával (3, 4)

    A következőt is megadhatja: vagy = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12}, de figyelnie kell arra, hogy hol helyezi el a pontosvesszőt és a vesszőt.

    Ahogy láthatja, a SZEKVENCIa lehetőség jelentős előnyöket nyújt a tömbképletek manuális beírásakor. Elsősorban időt takarít meg, de segíthet csökkenteni a kézi bevitelből származó hibákat is. Az is könnyebben olvasható, különösen, ha a pontosvesszők nehezen különböztethetők meg a vessző elválasztótól.

Íme egy példa, amely a tömbök állandóit a nagyobb képlet részeként használja. A minta munkafüzetben lépjen az állandóra egy képlet munkalapon, vagy hozzon létre egy új munkalapot.

A D9 cellába beírtuk az = szekvencia (1, 5, 3, 1)képletet, de az A9: H9 3, 4, 5, 6 és 7 celláját is megadhatja. Az adott számú kijelölésről semmi nem jellemző, csak az 1-5-re választottuk a különbséget.

Írja be a E11 cellába a = SZUM (D9: H9 * szekvencia (1, 5))vagy az = SZUM (D9: H9 * {1, 2, 3, 4, 5})képletet. A képletek visszaadják a 85.

Tömbök állandóit használhat képletekben. Ebben a példában az = SZUM (D9: H (* sorozat (1; 5)) képletet használtuk.

A SZEKVENCIa függvény az {1, 2, 3, 4, 5} tömbös konstans megfelelőjét építi fel. Mivel az Excel az első zárójelben megadott kifejezéseken végez műveleteket, a következő két elem a következő: a D9: H9 és a szorzási 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 (D9 * 1, ben * 2, F9 * 3, G9 * 4, H9 * 5)vagy = SZUM (3 * 1, 4 * 2, 5 * 3, 6 * 4, 7 * 5)

Végül a SZUM függvény összeadja az értékeket, és a 85 értéket adja eredményül.

Ha nem szeretné a tárolt tömböt használni, és a műveletet teljes egészében meg szeretné őrizni, egy másik állandóval lecserélheti:

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

A tömbökben használható elemek

  • A tömbképletek tartalmazhatnak számokat, szöveget és logikai értékeket (például igaz vagy hamis), valamint hibaértéket, például #N/A.. A számokat egész, decimális és tudományos formátumban használhatja. Ha szöveget vesz fel, azt idézőjelek közé ("szöveg") kell belefoglalni.

  • A tömbök állandói nem tartalmazhatnak további tömböket, képleteket vagy függvényeket. Más szóval csak azokat a szöveget vagy számokat tartalmazhatják, amelyek vesszőkkel vagy pontosvesszővel vannak elválasztva. Az Excel figyelmeztetést jelenít meg, ha olyan képletet ad meg, mint például {1, 2, a1: D4} vagy {1, 2, SZUM (Q2: Z8)}. A numerikus értékek nem tartalmazhatnak százalékértéket, a dollár jeleit, a vesszőket vagy a zárójeleket.

Az egyik legjobb módszer a tömbképletek használatára, ha átnevezi őket. 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:

Lépjen a képletek > definiált nevek > a név megadásagombra. A név mezőbe írja be a negyedév1. 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"}

A párbeszédpanel ekkor az alábbihoz hasonlóan néz ki:

Névvel ellátott tömbképletek felvétele a képletek > definiált nevek > Name Manager > új

Kattintson az OK gombra, majd jelölje ki bármelyik sort három üres cellával, és írja be az = negyedév1.

A következő eredmény jelenik meg:

Egy névvel ellátott tömbképlet használata egy képletben, például = Negyedév1, ahol a Negyedév1 definiálása = {"január", "február", "március"}

Ha a találatokat vízszintesen, hanem függőlegesen szeretné átvezetni, használja az =TRANSZPONÁLÁS(negyedév1)függvényt.

Ha 12 hónapból álló listát szeretne megjeleníteni, például a pénzügyi kimutatások készítésekor, a sorozat függvény segítségével kioszthatja az aktuális évet. Ez a függvény a remek dolog, ha csak a hónap jelenik meg, a többi számításban használható érvényes dátum. Ezek a példák a példaként használt munkafüzet névvel ellátott tömb állandó és rövid minta DataSet munkalapjain találhatók.

= SZÖVEG (dátum (év (ma ()), SZEKVENCIa (1, 12), 1), "mmm")

A szöveg, a dátum, az év, a ma és a SZEKVENCIa függvény kombinációjának használata a 12 hónapos dinamikus lista létrehozásához

Ez a Date függvény segítségével hoz létre egy dátumot az aktuális évhez képest, a sorozat a január és december közötti tartomány állandóját hozza létre, majd a szöveg függvény a megjelenítési formátumot "mmm"-ra (Jan, Feb, Mar stb.) konvertálja. Ha meg szeretné jeleníteni a teljes hónap nevét, például januárban, használja a "mmmm" nevet.

Ha egy névvel ellátott állandót tömbképletként használ, ne felejtse el beírni az egyenlőségjelet (például = Negyedév1), ne csak az Negyedév1. 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. Végül tartsa szem előtt, hogy a függvények, a szöveg és a számok kombinációját használhatja. Minden attól függ, hogy milyen kreatívt szeretne kapni.

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élda a TRANSZPONÁLÁS függvény segítségével sorokat oszlopokra konvertál, és fordítva.

  • Tömb elemeinek többszöröse

    ENTER = szekvencia (1, 12) * 2, vagy = {1, 2, 3, 4; 5; 6, 7, 8, 9, 10, 11, 12} * 2

    A következőt is eloszthatja: (/), hozzáadhatja (+), és kivonja a (-) függvényt.

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

    ENTER = szekvencia (1, 12) ^ 2, vagy = {1, 2, 3, 4; 5, 6, 7, 8, 9, 10, 11, 12} ^ 2

  • Egy tömbben található négyzetes elemek négyzetes gyökerének megkeresése

    Írja be a =Sqrt(szekvencia (1; 12) ^ 2)vagy = Sqrt ({1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12} ^ 2)

  • Egydimenziós sor transzponálása

    ENTER = TRANSZPONÁLÁS (szekvencia (1; 5))vagy = 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

    ENTER = TRANSZPONÁLÁS (szekvencia (5; 1))vagy = 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

    ENTER = TRANSZPONÁLÁS (szekvencia (3; 4))vagy = 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 létrehozása már meglévő értékekből

    Az alábbi példa azt szemlélteti, hogy miként hozhat létre új tömböt egy meglévő tömbből a tömbképletek használatával.

    Írja be a = szekvencia (3, 6, 10, 10), vagy az = {10, 20, 30, 40, 50, 60; 70; 80; 90100110120; 130140150160170180}

    Ne felejtse el beírni a {(nyitó kapcsos zárójel) karakterláncot, mielőtt beírja a 10-es számot, és} (záró kapcsos zárójel) értéket ír be az 180 beírása után, mert számok tömbjét hozza létre.

    Ezután írja be a következőt: = D9 #vagy = D9: I11 egy üres cellába. A 3 x 6 cellatartomány a D9: D11-ban ugyanazokat az értékeket jeleníti meg. A # jel neve a kiömlött tartományban szereplő, és a teljes Excel's úgy hivatkozik, hogy nem kell beírnia.

    A kiömlött tartományban lévő operátor (#) használata meglévő tömbre való hivatkozáshoz

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

    Elvégezheti a kiömlött tömbképlet eredményét, és átalakíthatja őket a hozzájuk tartozó alkotórészekre. Jelölje ki a D9 cellát, majd az F2 billentyűt lenyomva váltson szerkesztési módba. Ezután az F9 billentyűt lenyomva konvertálja a cellahivatkozások értékekre mutató hivatkozását, amelyeket az Excel ekkor tömbös állandóra konvertál. Ha lenyomja az ENTER billentyűt, akkor a = D9 # képletet az = {10, 20, 30; 40, 50, 60; 70; 80; 90} képlettel adjameg.

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

    Az alábbi példa azt szemlélteti, hogy miként számíthatja ki a cellatartomány karaktereinek számát. A szóközöket is beleértve.

    Egy tartományban lévő karakterek teljes számát, valamint a szöveges karakterláncok használatát szolgáló egyéb tömbök számát számítja ki.

    = SZUM (HOSSZ (C9: C13))

    Ebben az esetben a Hossz függvény az egyes szöveges karakterláncok hosszát adja eredményül a cellatartomány egyes celláiban. A SZUM függvény ezután összeadja ezeket az értékeket, és megjeleníti az eredményt (66). Ha átlagos számú karaktert szeretne kapni, használhatja a következőt:

    = AVERAGE (HOSSZ (C9: C13))

  • A leghosszabb cella tartalma a C9: C13

    = INDEX (C9: C13, EGYEZÉS (MAX (HOSSZ (C9: C13)), LEN (C9: C13); 0); 1)

    A képlet csak akkor működik, ha az adattartomány egyetlen oszlopnyi cellát tartalmaz.

    A képletet a belső elemektől kifelé haladva több elem építi fel. A Hossz függvény a D2: D6 cellatartomány elemeinek hosszát számítja ki. A Max függvény kiszámítja az elemek közötti legnagyobb értéket, amely a D3 cellában lévő leghosszabb szöveges karakterláncnak felel meg.

    Itt találhatja meg, hogy egy kicsit bonyolultak legyenek. A hol . van függvény a leghosszabb szöveges karakterláncot tartalmazó cella eltolását (relatív pozícióját) számítja ki. Ehhez három argumentum szükséges: a keresési érték, a keresési tömb és a egyezés típusa. A hol. van függvény a megadott keresési érték keresési tömbjét keresi meg. Ebben az esetben a keresési érték a leghosszabb szöveges karakterlánc:

    MAX (HOSSZ (C9: C13)

    A karakterlánc ebben a tömbben van:

    LEN (C9: C13)

    Az egyezés típusa argumentum ebben az esetben 0. A hol. van típus értéke 1, 0 vagy-1 lehet.

    • 1 – a keresés a keresési tartománynál kisebb vagy azzal egyenlő legnagyobb értéket ad eredményül.

    • 0 – az első érték visszaadása pontosan egyenlő a keresési értékkel

    • -1 – a megadott keresési értéknél nagyobb vagy azzal egyenlő legkisebb értéket adja eredményül.

    • Ha kihagy egy egyezési típust, az Excel az 1 értéket veszi figyelembe.

    Végül az index függvény az alábbi argumentumokat foglalja magában: egy tömb, valamint egy sor és egy oszlop száma a tömbben. A cellatartomány C9: C13 megadja a tömböt, a hol. van függvény adja meg a cella címét, a záró argumentum (1) pedig azt adja meg, hogy az érték a tömb első oszlopáról származik.

    Ha el akarta érni a legkisebb szöveges karakterlánc tartalmát, a fenti példában a MAX értéket a minértékre cseréli.

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

    Ez a példa azt szemlélteti, hogy miként lehet megkeresni egy cellatartomány három legkisebb értékét, ahol a B9: B18has-es cellákban a minta adatok tömbje a következő: = int (véletlentömb(10; 1) * 100). Ügyeljen arra, hogy a VÉLETLENTÖMB egy változékony függvény, így új véletlenszerű számokat kap minden alkalommal, amikor az Excel kiszámítja.

    Excel tömbképlet az n-edik legkisebb érték megkereséséhez: = kicsi (B9 #, SZEKVENCIa (D9))

    Írja be a = kicsi (B9 #, szekvencia (D9), = Small (B9: B18; {1; 2; 3})

    Ez a képlet egy tömbképlet segítségével háromszor értékeli ki a kis függvényt , és a legkisebb 3 tagot adja vissza a B9: B18, ahol a 3 a D9 cellában szereplő változó érték. További értékek megkereséséhez növelheti a SZEKVENCIa függvény értékét, vagy további argumentumokat adhat az állandóhoz. További függvényeket is használhat a képlettel (például SZUM vagy átlag). Például:

    = SZUM (KICSI (B9 #, SZEKVENCIA (D9))

    = AVERAGE (KICSI (B9 #, SZEKVENCIA (D9))

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

    Ha meg szeretné keresni egy cellatartomány legnagyobb értékeit, a nagyfüggvényt lecserélheti. A következő példában ezenfelül a SOR és az INDIREKT függvény is helyet kapott.

    ENTER = nagy (B9 #; sor (indirekt ("1:3"))) vagy = nagy (B9: B18, sor (indirekt ("1:3")))

    Ebben a pontban a sor-és a közvetett függvényekkel kapcsolatban is lehet hasznos tudni. Az egymást követő egész számok tömbjét a sor függvény segítségével is létrehozhatja. Jelölje ki például az üres értéket, és írja be az alábbiakat:

    =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 módosítja a sorok hivatkozását, és a képlet mostantól 2 és 11 közötti egész számot hoz létre. 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 argumentumai a szöveges karakterláncokat használják (ezért a 1:10-as intervallumot idézőjelek közé kell tenni). Az Excel nem módosítja a szöveges értékeket sorok beszúrásakor, vagy más módon áthelyezi a tömbképletet. Ennek eredményeként a sor függvény mindig a kívánt egész számokat hozza létre. Egyszerűen használhatja a SORRENDet:

    = SZEKVENCIA (10)

    Nézzük meg a korábban használt képletet – = nagy (B9 #, sor (indirekt ("1:3"))) – a belső zárójelből és a munkamennyiségből kifelé haladva: az indirekt függvény a szöveges értékek halmazát adja eredményül, ebben az esetben az 1 – 3 értéket adja eredményül. A sor függvény három cellából álló oszlop tömböt hoz létre. A nagy függvény a B9: B18 cellatartomány értékeit használja, és a függvény a sor függvény által visszaadott összes hivatkozás után háromszor értékeli ki. Ha további értékeket szeretne keresni, nagyobb cellatartományt kell hozzáadnia az INDIREKT függvényhez. Végül a kis példaként használhatja ezt a képletet más függvényekkel, például a SZUM és az átlag függvénnyel.

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

    Az Excel SZUM függvénye nem működik, ha hibaértéket (például #VALUE!) tartalmazó tartománnyal próbál összegezni. vagy #N/A. Ebből a példaból megtudhatja, hogy miként összegezheti a hibát tartalmazó adatok nevű tartomány értékeit:

    Tömbök használata a hibák kezeléséhez. Például = SZUM (ha (hibás (Data), "", Data) – akkor is összesíti az elnevezett adattartományt, ha a hibát tartalmaz, például #VALUE! vagy #NA!.

  • =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 olyan, mint az előző képlet, de a szűrés helyett az adatok nevű tartomány 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ű.

Bizonyos esetekben szükség lehet az értékeket feltételek alapján összegezni.

A tömbök segítségével bizonyos feltételek alapján kiszámíthatja a számításokat. Az = SZUM (ha (Sales>0; értékesítés)) az értékesítések nevű tartománnyal a 0-nál nagyobb értékeket összesíti.

Ez a tömbképletek például csak a pozitív egész számokat összegzik az értékesítés nevű tartományban, amely a fenti példában szereplő cellák ben: E24.

=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. Ez a tömbképlet például a nullánál nagyobb és 2500- nál kisebb értékeket számítja ki:

= SZUM ((Sales>0) * (Sales<2500) * (é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. Többek között az 2500-nél nagyobb vagy kisebb értékeket is összegezheti például:

= SZUM (ha ((Sales>0) + (Sales<2500), értékesítés))

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ó. Más szóval matematikai műveleteket (például összeadás vagy szorzás) végez a vagy vagy a vagy függvénynek megfelelő értékeken.

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.

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 cellák tartományának méretének és méretének azonosnak kell lennie. Ha például a Adatok1 a 3 sor 5 oszlopból áll, akkor a adatok2 5 oszlopból kell kitölteni:

=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 * (MyData<>YourData))

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.

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

A minta munkafüzetben hasonló példákat talál az adatkészletek munkalapja közötti különbségekre .

Ebből a gyakorlatból megtudhatja, hogy miként használhatja a többcellás és az egycellás tömbképletek az értékesítési számok halmazának kiszámításához. Az első lépések a többcellás képletek segítségével számítják ki a részösszegeket. A második halmaz egy egycellás képlettel számítja ki a végösszeget.

  • Többcellás tömbképletek

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

Sales (értékesítés ) Személy

Car (autós ) Type (típus )

Number (szám ) Elkelt

Unit (egység ) Price (ár )

Total (összeg ) Sales (é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)

  1. Ha meg szeretné nézni a kupé és a szedánok teljes értékesítését az egyes üzletkötők esetében, jelölje ki az E2: E11, írja be a következő képletet: = C2: C11 * D2: D11, majd nyomja le a CTRL + SHIFT + ENTERbillentyűkombinációt.

  2. Az összes eladás végösszegének megtekintéséhez jelölje ki az F11 cellát, írja be az = SZUM (C2: C11 * D2: D11)képletet, majd nyomja le a CTRL + SHIFT + ENTERbillentyűkombinációt.

A CTRL + SHIFT + ENTERbillentyűkombináció lenyomásakor az Excel kapcsos zárójelekkel ({}) veszi körül a képletet, és a kijelölt cellatartomány minden cellájába beszúrja a képlet egy példányát. 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 D13 a következő képletet, majd nyomja le a CTRL + SHIFT + ENTERbillentyűkombinációt:

=SZUM(C2:C11*D2:D11)

Ebben az esetben az Excel összeszorozza a tömb értékeit (a C2 – D11 cellatartományt), majd a SZUM függvénnyelösszeadja az összegeket. Az eredmény a $1 590 000 végösszege az értékesítésekben. Ez a példa jól szemlélteti, hogy mi mindenre használható egy ilyen típusú képlet. Tegyük fel például, hogy 1 000-ös adatsorral rendelkezik. A képletek egy részét vagy egészét összesítheti úgy, hogy a képletet a 1 000 sorok között húzza le a képlet helyett.

Azt is megfigyelheti, hogy a D13 cellában lévő egycellás képlet teljesen független a többcellás képlettől (az E2-től E11-ig terjedő tartomány képlete). Ez a tömbképletek másik előnyös tulajdonsága: a rugalmasság. Az E oszlop képleteit módosíthatja, vagy teljesen törölheti az oszlopot, anélkül, hogy befolyásolná a képletet a D13.

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

  • Következetesség:    Ha az E2 cellában lévő egyik cellára kattint, ugyanazt a képletet jeleníti meg. Az egységesség a nagyobb pontosság érdekében segíthet.

  • Biztonság:    A többcellás tömbképletek összetevői nem írhatók felül. Jelölje be például az E3 cellát, és nyomja le a deletebillentyű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. További biztonsági intézkedésként a képlet módosításainak megerősítéséhez nyomja le 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 az általános képletek szintaxisát használják. Mindegyikük egyenlő (=) jellel kezdődik, és a legtöbb beépített Excel-függvényt használhatja a tömbképletek között. A fő különbség az, hogy tömbképlet használatakor a CTRL + SHIFT + ENTER billentyűkombinációt lenyomva írja be a képletet. Ezt követően az Excel kapcsos zárójelekkel veszi körül a tömbképletet (ha kézzel beírja a kapcsos zárójeleket), a képlet szöveges karakterlánccá változik, és nem fog működni.

A tömbképletek hatékony megoldást adhatnak a komplex képletek készítésére. 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).

Fontos: A tömbképlet beírásához nyomja le a CTRL + SHIFT + ENTER billentyűkombinációt. 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 cellatartományt (például E2: E11), majd nyomja le a deletebillentyűt.

  • Többcellás tömbképlet esetén nem lehet üres cellákat beszúrni, és cellákat sem törölni.

Bizonyos esetekben szükség lehet a tömbképletek kibontására. Jelölje ki az első cellát a meglévő tartományban, és folytassa addig, amíg ki nem jelöli a képletet kibővíteni kívánt teljes tartományát. Nyomja le az F2 billentyűt a képlet szerkesztéséhez, majd a CTRL + SHIFT + ENTER billentyűkombinációt lenyomva erősítse meg a képletet, miután módosította a képletet. A billentyűvel a teljes tartományban kijelölheti a teljes cellatartományt, a tömb bal felső cellájától kezdve. A legfelső cellája a szerkesztve lesz.

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

  • Alkalmanként elfelejtheti a CTRL + SHIFT + ENTERbillentyű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 többi felhasználója nem érti a képleteit. A gyakorlatban a tömbképletek általában nem magyarázják meg a munkalapokon. Ezért, ha a többi személynek módosítania kell a munkafüzeteket, el kell kerülnie a tömbképletek beírását, vagy meg kell győződnie arról, hogy az illetőek a tömbképletek esetében is tudni fogják, és hogy hogyan módosíthatják őket, ha szükségesek.

  • 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}

Most már tudja, hogy a tömbképletek létrehozásakor a CTRL + SHIFT + ENTER billentyűkombinációt kell lenyomnia. 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ót használva adja meg a teljes képletet.

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.

Itt egy tömb egyetlen sorba: {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ét sor sorában az első sor 1, 2, 3 és 4, a második sor pedig 5, 6, 7 és 8. 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 cellatartományt.

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

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

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

    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 szerkesztőlécen a következő képletet, majd nyomja le a CTRL + SHIFT + ENTERbillentyű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écen a következő képletet, majd nyomja le a CTRL + SHIFT + ENTERbillentyű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. Az a3 cellába írja be a következő képletet, majd nyomja le a CTRL + SHIFT + ENTERbillentyű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.

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. Ne feledje, hogy miután állandót vett fel egy tömbképletként, a CTRL + SHIFT + ENTER billentyűkombinációt lenyomva írja be 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})

A művelet kijavításához másolja a vágólapra a függvényt, jelöljön ki egy üres cellát a munkafüzetben, illessze be a képletet a szerkesztőlécen, majd nyomja le a CTRL + SHIFT + ENTERbillentyű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ömbök állandói nem tartalmazhatnak további tömböket, képleteket vagy függvényeket. Más szóval csak azokat a szöveget vagy számokat tartalmazhatják, amelyek vesszőkkel vagy pontosvesszővel vannak elválasztva. Az Excel figyelmeztetést jelenít meg, ha olyan képletet ad meg, mint például {1, 2, a1: D4} vagy {1, 2, SZUM (Q2: Z8)}. A numerikus értékek nem tartalmazhatnak százalékértéket, a dollár jeleit, a vesszőket vagy a zárójeleket.

A tömbképletek használatának egyik legjobb módja az, ha megnevezi őket. 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 megadá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:

    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 + ENTERbillentyű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.

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 nem ad meg vesszőt vagy pontosvesszőt, vagy ha nem a megfelelő helyre helyezi el, előfordulhat, hogy a tömbképletek nem megfelelően jelennek meg, vagy figyelmeztető üzenet 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élda a TRANSZPONÁLÁS függvény segítségével sorokat oszlopokra konvertál, és 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 + ENTERbillentyű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 + ENTERbillentyű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 + ENTERbillentyű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 + ENTERbillentyű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 + ENTERbillentyű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

Az alábbi példa bemutatja, hogyan lehet tömbképletek segítségével kapcsolatokat létrehozni a különböző munkalapokon lévő cellatartomány között. Azt is megtudhatja, hogy miként hozhat létre tömbös konstanst ugyanabból az értékbő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. Nyomja le a CTRL + SHIFT + ENTERbillentyűkombinációt, amely a következő számú számot adja meg a C8: E10 cellatartomány 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 + ENTERbillentyűkombinációt:

    =C8:E10

    A C1 – E3 cellában egy 3x3-as cella jelenik meg, ugyanazokkal az értékekkel, amelyeket a C8-től az E10-ig lát.

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

  1. A C1: C3 cella kijelölve, az F2 billentyűt lenyomva váltson szerkesztési módba. 

  2. Az F9 billentyű lenyomásával konvertálja a cellahivatkozások értékekre mutató hivatkozásokat. Az Excel tömbállandóvá alakítja az értékeket. A képletnek most = {10, 20, 30, 40, 50, 60; 70; 80; 90}-nek kell lennie.

  3. Nyomja le a CTRL + SHIFT + ENTER billentyűkombinációt a tömbképlet tömbképletként való megadásához.

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 tekintse meg az a2: a6 cellatartományban lévő karakterek teljes számát (66).

  3. Jelölje ki az A10 cellát, majd a CTRL + SHIFT + ENTER billentyűkombinációt lenyomva tekintse meg az a2: a6 cellatartomány leghosszabb cellájára (a3).

Az A8 cellában az alábbi képlet számítja ki az A2-től A6-ig terjedő cellatartományban lévő karakterek teljes számát (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 ezután összeadja ezeket az értékeket, és 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 cellatartományt. 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 + ENTERbillentyűkombinációt:

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

Ez a képlet egy tömbképlet segítségével háromszor értékeli ki a kis függvényt, és a legkisebb (1), a második legkisebb (2) és a harmadik legkisebb (3) tagot adja vissza az a1: A10 cellákban lévő tömbben, ha további értékekre szeretne keresni, további argumentumokat ad a állandó. További függvényeket is használhat a képlettel (például SZUM vagy átlag). Például:

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

= AVERAGE (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 cellatartományt.

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

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

Ebben a pontban a sor -és a közvetett függvényekkel kapcsolatban is lehet hasznos tudni. Az egymást követő egész számok tömbjét a sor függvény segítségével is létrehozhatja. Jelöljön ki például egy 10 cellából álló üres oszlopot a gyakorlat munkafüzetben, írja be a következő tömbképletet, majd nyomja le a CTRL + SHIFT + ENTERbillentyű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 argumentumai a szöveges karakterláncokat használják (ezért a 1:10-as intervallumot idézőjelek közé kell tenni). Az Excel nem módosítja a szöveges értékeket sorok beszúrásakor, vagy más módon áthelyezi a tömbképletet. Ennek eredményeként a sor függvény mindig a kívánt egész számokat hozza létre.

Lássuk a korábban használt képletet – = nagy (a5: A14; sor (indirekt ("1:3") )) – a belső zárójelből és a munkamennyiségből kifelé haladva: az indirekt függvény a szöveges értékek halmazát adja eredményül, ebben az esetben az 1 – 3 értéket adja eredményül. A SOR függvény viszont egy háromcellás oszlopos tömböt eredményez. A nagy függvény az A5: A14 cellatartomány értékeit használja, és a függvény a sor függvény által visszaadott összes hivatkozás után háromszor értékeli ki. 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.

A korábbi példákhoz hasonlóan használhatja ezt a képletet más függvényekkel, például a SZUM és az átlagfüggvénnyel.

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

Térjen vissza a korábbi szöveges karakterláncra, írja be a következő képletet egy üres cellába, és nyomja le a CTRL + SHIFT + ENTERbillentyűkombinációt:

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

A "megjelenő cellatartomány" 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 számítja ki. A Max függvény kiszámítja a legnagyobb értéket ezek közül az elemek között, amelyek az a3 cellában lévő leghosszabb szöveges karakterláncnak felelnek meg.

Itt találhatja meg, hogy egy kicsit bonyolultak legyenek. A hol . van függvény a leghosszabb szöveges karakterláncot tartalmazó cella eltolását (relatív pozícióját) számítja ki. Ehhez három argumentum szükséges: a keresési érték, a keresési tömbés a egyezés típusa. A hol . van függvény a megadott keresési érték keresési tömbjét keresi meg. Ebben az esetben a keresési érték a leghosszabb szöveges karakterlánc:

(MAX (HOSSZ (A2: A6))

A karakterlánc ebben a tömbben van:

HOSSZ (A2: A6)

Az egyezés típusa argumentum értéke 0. A hol. van típus az 1, a 0 vagy a-1 értékből állhat. Ha az 1 értéket adja meg, akkor a egyezés a keresési értéknél kisebb vagy azzal egyenlő legnagyobb értéket adja eredményül. Ha a 0 értéket adja meg, akkor a egyezés az első olyan értéket adja eredményül, amely pontosan megegyezik a keresési értékkel. Ha a-1 értéket adja meg, akkor a egyezés a megadott keresési értéknél nagyobb vagy azzal egyenlő legkisebb értéket keresi meg. Ha kihagy egy egyezési típust, az Excel az 1 értéket veszi figyelembe.

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 adja meg a tömböt, a hol . van függvény adja meg a cella címét, a záró argumentum (1) pedig azt adja meg, hogy az érték a tömb első oszlopáról származik.

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 * (MyData<>YourData))

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

Elismervényt

A jelen cikk egyes részei a Colin Zsolt által írt Excel Power User oszlopok sorozatán alapultak, amelyet az Excel 2002-es képletei, a John Walkenbach, a volt Excel MVP által írt könyv (14) és 15.

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.

Lásd még

Dinamikus tömbök és kibontott tömb viselkedése

Dinamikus tömbképletek és a régebbi CSE-tömbképletek

SZŰRŐ függvény

VÉLETLENTÖMB függvény

SORSZÁMLISTA függvény

EGYETLEN függvény

SORBA.RENDEZ függvény

RENDEZÉS.ALAP.SZERINT függvény

EGYEDI függvény

#KITÖLTÉS! hibák az Excelben

Képletek – Áttekintés

Office-jártasság 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.

×