{Ctrl + Shift + Enter} CD-ROM
www.offcafe.hu
{Ctrl + Shift + Enter} Tömbképletek (Titkok – Trükkök – Tippek) Tartalom Lekérdezés tömbképlettel (Példák) Összetett lekérdezések A CSE elsajátításához szükséges alapismertek Az Excel aranyszabálya A képletek elemei Aritmetikai operátorok Összehasonlító operátorok Szövegoperátor Műveletek sorre dje Példák Boole-operátorok ÉS VNEM XVAGY
Példák Néhány többször alkalmazott függvény NAGY és SOROK függvény: „ ővíthető tarto á ok” a „top…” kinyerésére KICSI és OSZLOPOK függvény: „ ővíthető tarto á ok” a „legalsó, legutolsó, legkise kinyerésére ÖSSZESÍT és SOROK függvény: „ ővíthető tarto á ok” a „legalsó, legutolsó, legkise kinyerésére INDEX és HOL.VAN példák A HOL.VAN függvény (5 példa) Az INDEX függvény (4 példa) A SZUMHATÖBB függvény Bevezető 1) A „tömb” 2) Tömbök az Excelben 3) A tömbképlet Hag o á os képletek űveletei eg -egy értékkel 5) Hagyományos képletek „összesítő” űveletei Tö képlet: arit etikai űvelet eg tö e 7) A legnagyobb eltérés kiszámítása 8) Tömbök a képletben: {=MAX(E21:E24-D21:D24)} A eíráshoz spe iális ille t űko i á ióra va szükség Ha egfeledkezü k a Ctrl + Shift + E ter g ors ille t űről 11) Melyik a jobb módszer: segédoszlop vagy tömbképlet? 12) A {=MAX(E21:E24-D21:D24)} tömbképlet 13) Matematikai operátorok A tö képletek elő ei 15) A tömbképletek hátrányai
1
…” …”
{Ctrl + Shift + Enter} CD-ROM
www.offcafe.hu
Összehaso lító tö b űveletek 1) Tömbképlet; „összehasonlító” űveletet végez eg tö e 2) A legkisebb érték (MIN) teamenként 3) Tömbök a képletben: =MIN(HA($B$7:$B$12=E7;$C$7:$C$12)). 4) Miért jó, hogy a HA függvény a HAMIS értéket teszi a tömbbe? 5) Alkalmazható az AB.MIN függvény? 6) Kimutatás (Pivot) alkalmazható? 7) Mi a jobb: MIN/HA tömbképlet vagy az AB.MIN vagy a Kimutatás (Pivot)? 8) A {=MIN(HA($B$7:$B$12=E7;$C$7:$C$12))} tömbképlet 9) Összehasonlító operátorok Összefűző tö b űveletek 1) Tömbképlet, mely „összefűző” űveletet szövegoperátor végez a tö e 2) Két keresési érték alapján az eladott mennyiségek táblázatba foglalása 3) Használható az AB.MEZŐ? 4) Használható segédoszlop? 5) Az {=INDEX($D$8:$D$21;HOL.VAN($F9&G$8;$B$8:$B$21&$C$8:$C$21;0))} tömbképlet Ha re dez i tudjuk a keresési oszlopot, a po tos eg ezés hel ett a egközelítő eg ezés is alkalmazható Függvé yargu e tu tö b űveletek 1) Tömbképlet, mely a függvé argu e tu ot tö űveletté változtatja: az argu e tu eg etle értéket vár, de többet kap Keressük eg az eg es ter ékek költségeit, ajd adjuk össze őket. 3) Definiált nevek 4) Ha a függvény argumentumába, mely egy értékre számít, több értéket írunk be, tömböt hozunk létre. Néhá függvé argu e tu Ctrl + Shift + E ter élkül is tö ökkel végzett űveletekre képes (így a SZORZATÖSSZEG). 6) Miért nem használjuk a SZUM függvényt a SZORZATÖSSZEG helyett? 7) A =SZORZATÖSSZEG(SZUMHATÖBB(KtsgO;TermékO;EladO)) képlet Tömbállandók a képletekben 1) A három legkisebb pontszám összege, ha a harmadik helyen „holtverseny” van, csak az egyik szerepel. 2) Tömbképlet: =SZUM(KICSI(C7:C12;{1\2\3})) 3) A tömbállandók beviteléhez nincs szükség a Ctrl + Shift + E ter g ors ille t űre. 4) A =SZUM(KICSI(C7:C12;{1\2\3})) tömbképlet Hag o á os képlet, a har adik hel e lévő holtverse eszá ításával 6) Magyarázat 7) Képlet: megtalálható az elem egy listában (B csoport)? 8) A hagyományos képletek tömbállandókkal helyet takarítanak meg. Példa: képletek adatok keresése táblázatból 9) A tömbállandókat definiált névként is menthetjük A TRANSZPONÁLÁS tömbfüggvény 1) Tömbfüggvények: a) Eredményük több érték, b A képlet eírása előtt tö ellát kell kijelöl i, c) CSE! 2) TRANSZPONÁLÁS: Tö képlet tö érték tö ellá a küldésére. Függőleges ellatarto á t vízszintesként ad eredményül, és megfordítva 3) TRANSZPONÁLÁS és HA a nullák (0) elkerülésére 4) Megjegyzések 5) Tömbfüggvények: alapok
2
{Ctrl + Shift + Enter} CD-ROM
www.offcafe.hu
Tömbképlet: több érték több cellába Tö képlet evitele a Ctrl + Shift + E ter ille t űko i á ióval eg szerre tö ellá a 2) 1-től -ig számsorrend létrehozása, melyekben az egyes értékeket nem lehet törölni 3) A {=SOR(B7:B16)-SOR(B7)+1} tömbképlet 4) Megjegyzések: ha egy cellánál több cellába írtuk be a képletet a Ctrl + Shift + Enter használatával A SZORZATÖSSZEG függvény 1) A SZORZATÖSSZEG függvé t alkal azhatjuk, ha eg tö e végzett űvelet ered é é ek összegét akarjuk megkapni 2) Egycellás megoldással kell kiszámítani az értékesítés kerekített összegét (a darabszám és az ár külön oszlopban) 3) A =SZORZATÖSSZEG(KEREKÍTÉS(B6:B10*C6:C10;-2)) képlet 4) Ne használjuk fölöslegesen a SZORZATÖSSZEG függvényt, ha más függvények hatékonyabbak 5) Számoljuk meg a 2015-ös adatokat (pl. utalások dátumai), ha a dátumok egy oszlopban számokként találhatók. Három módszer. 6) Módszer 1: DARABTELI és ÉV, segédoszlop 7) Módszer 2: egycellás tömbképlet: SZORZATÖSSZEG, ÉV és két mínuszjel 8) Módszer 3: egycellás tömbképlet: SZUM, ÉV és HA függvények A KERES függvény 1) A =KERES paraméterei 2) A keresési_vektor; eredmény_vektor argu e tu ok CSE élkül is tö ökkel végzik a űveleteket. 3) „Keresési érték összeadása” a =KERES függvény keresési_érték argumentumával, mely tömböt tartalmaz. 4) Keressük meg a termékek költségeit, és adjuk össze az értékeket. 5) Értékesítések után járó jutalékok összesítése 6) Utolsó elem (szám vagy szöveg) egy listában a =KERES segítségével, melyben a keresési_vektor argumentum tömböt képez Függvények, melyek tömböket kezelnek a Ctrl + Shift + Enter nélkül Függvé ek, el ek tö öket kezel ek a Ctrl + Shift + E ter ille t űko i á ió élkül 2) SZORZATÖSSZEG (példa) 3) Egycellás megoldás: a tényleges megtakarítás összegének kiszámítása 4) KERES (példa) 5) Városonként a dátum szerinti utolsó értékesítés összege 6) A =KERES(2;1/($C$23:$C$29=$B32);D$23:D$29) képlet 7) INDEX példa 8) Táblázat készítése, mely két keresési érték alapján a mennyiséget adja eredményül. 9) Az =INDEX($D$72:$D$85;HOL.VAN($F73&G$72;INDEX($B$72:$B$85&C$72:$C$85;);0)) képlet 10) ÖSSZESÍT (példa) A verse zők legjo ideje KICSI függvé eg kritériu al 12) Az =ÖSSZESÍT(15;6;$C$110:$C$121/($B$110:$B$121=$E110);F$109) képlet 13) Az =ÖSSZESÍT függvény (részletek) Függvények, melyek nem kezelnek tömböket még a Ctrl + Shift + Enterrel sem (SZUMHA és hasonlók) Mi a tee dő, ha a SZUMHA, SZUMHATÖBB, DARABTELI, DARABHATÖBB, ÁTLAGHA, ÁTLAGHATÖBB nem kezeli a tömbargumentumban a tömbszámításokat? 2) Adatok összegzése az év kritérium alapján segédoszloppal (ÉV) 3) A tartomány és kritériumtartomány argumentumok nem kezelik a tömböket. 4) A SZUMHA és az ÉV függvények tömbképlet megoldásainak kiváltása (4 megoldás) 5) Adatok megszámlálása az év kritérium alapján segédoszloppal (ÉV)
3
{Ctrl + Shift + Enter} CD-ROM
www.offcafe.hu
Hel ettesítő képletele ek a DARABTELI és az ÉV számításokhoz 7) Átlagszámítás az Év kritériummal Hel ettesítő képletele ek az ÁTLAGHA és ÉV számításokhoz a tartomány argumentumokban. 9) A DARABTELI és hasonló függvények kritérium argumentuma kezeli a tömböket SZUM és beágyazott HA tö függvé ké t Értékesítés ered é e külö öző kritériu ok alapjá SZUMHATÖBB (Értékesítés eredménye az összes kritérium alapján) Dinamikus és eredményül kapott tartományok az INDEX és az ELTOLÁS függvényekkel 1) Dinamikus tartomány: meghatározás 2) INDEX & ELTOLÁS: eredménye cellatartomány (egyfajta tömb) lehet, melyet képletekben, diagramokban és egyéb formákban használhatunk, melyek cellahivatkozások 3) INDEX: tartományban (sor vagy oszlop) hivatkozását vagy értékét adja eredményként 4) Ha az INDEX függvényben nincs „tö űvelet” tömb létrehozására, nincs szükség a Ctrl + Shift + E ter ille t űpara sra 5) Excel Táblázat 6) Érvek az Excel Táblázat alkalmazásával szemben 7) Definiált nevek G ors ille t űk 9) Az =ELTOLÁS függvény – dinamikus tartományok 10) Dinamikus tartomány, melynek alapja egy lenyíló kritériuma 11) Dinamikus tartomány: az utolsó 5 cella értékeinek összege 12) SZUMHA, SZUMHATÖBB, DARABTELI és hasonló függvények, melyek az INDEX függvénnyel dinamikus tartományokat hoznak létre INDEX: definiált névvel dinamikus tartományok létrehozása, az oszlop szöveget tartalmaz 2) INDEX: definiált névvel dinamikus tartományok létrehozása, az oszlop számokat tartalmaz 3) INDEX: definiált névvel dinamikus tartományok létrehozása, az oszlop szöveget és számokat
tartalmaz 4) INDEX: definiált névvel dinamikus tartományok létrehozása, 3 példa 5) FKERES és Érvényesítés: definiált névvel megadott dinamikus tartomány 6) Definiált névvel megadott dinamikus tartomány, mely kezeli az adatok alatti üres sort Boole-logika, ÉS, VAGY kritérium, az IGAZ és HAMIS konvertálása 1) Boole logika vagy matematika az Excelben 2) ÉS operátor = szorzás = minden logikai vizsgálat eredményének IGAZ-nak kell lennie 3) A SZORZATÖSSZEG kezeli az ÉS kritériumot 4) DARABHATÖBB, SZUMHATÖBB, ÁTLAGHATÖBB és a beágyazott HA függvények kezelik az ÉS kritériumot 5) A HA függvény és az ÉS űködése tö ök e 6) Az ÉS 2 vagy több logikai vizsgálatot tud végezni 7) VAGY feltétel = összeadás = legalább 1 logikai vizsgálatnak IGAZ-nak kell lennie 8) Boole-féle összeadás a VAGY operátorral: ha az eredmény 2 vagy több lehet 9) A SZORZATÖSSZEG és a Boole-féle összeadás a VAGY operátorral, ha az eredmény 2 vagy több lehet 10) Feladat: a VAGY tételek mennyisége. „Duplán számolás” kizárva. 11) Tömbképlet: {=SZORZATÖSSZEG(--(((B76:B80=F77)+(C76:C80=F79))>0))} 12) A HA függvény logikai_vizsgálat argu e tu a a Boole VAGY operátorral törté ő összeadás a , melyben az eredmény 2 vagy több lehet. 13) Cél: a maximális értékesítés '' városban vagy ahol a '' eladásra került. 14) Alkalmazható az AB.MAX a maximális értékesítés megtalálására? 15) A {=MAX(HA((B133:B137=F134)+(C133:C137=G134);D133:D137))} képlet
4
{Ctrl + Shift + Enter} CD-ROM
www.offcafe.hu
16) VAGY feltétel, melyben soha nem lehet 2 IGAZ érték. 17) Négy képlet az egymást kizáró VAGY feltétellel törté ő szá láláshoz 18) A HOL.VAN függvény a keresési_érték argumentumban cellatartománnyal (tömbbel) 19) Az IGAZ és HAMIS értékeket i de ate atikai űvelet -ekké és 0-ákká konvertálja. 21) ÉS és VAGY feltétel útmutató A tömbképletek általános szabályai (összefoglalás) 1) A „tömb” 2) Tömbök az Excelben 3) Tömbképlet (meghatározás) Tö képletek eírása a ellák a: ol kor a Ctrl + Shift + E ter ille t űko i á ióra va szükség 5) A Ctrl + Shift + Enter és a kapcsos zárójelek Ha elfelejtjük eír i a Ctrl + Shift + E ter ille t űko i á iót? 7) A segédoszlop/segédcella megoldás sok esetben gyorsabb az egycellás tömbképletnél Sok eset e a tö képlet él eg szerű az adat ázisfüggvé ek alkalmazása Sok eset e a Ki utatás Pivot Ta le eg szerű , i t a tö függvé ek alkal azása 10) Ha „kereső” tö képleteket hasz álu k, re dezzük a egfelelő oszlopot, és hasz áljuk a „ egközelítő eg ezés” opciót a számítások felgyorsítására 11) Ha tömbképlettel létrehozott eredményeket akarunk összeadni, a SZORZATÖSSZEG segíthet 12) Tömbállandók szintaxisa és szabályai 13) A nem-tömbképletekben a tömbállandókkal helyet takaríthatunk meg a munkalapon 14) Tömbfüggvények (alapok) 15) Tömbképletek beírása több mint egy cellába a Ctrl + Shift + Enter használatával 16) A tömbképletekhez jól kiválasztott függvények csökkentik a számítások idejét A függvé ek argu e tu ai, el ek Ctrl + Shift + E ter élkül is tö űveleteket végez ek 18) A SZUMHA, SZUMHATÖBB, DARABTELI és hasonló függvények tartomány és kritérium_tartomány argumentumai nem kezelik a tömböket 19) Az INDEX és ELTOLÁS függvények eredménye lehet cellatartomány (egyfajta tömb) 20) ÉS és VAGY Boole-operátorok 21) Az IGAZ és HAMIS logikai értékeket az összes aritmetikai operátor 1-ekké és 0-ákká konvertálja Elő ök és hátrá ok, a tö képletek alkal azásá ak elő ei és hátrá ai, egfo tolások A tö képletek elő ei A tömbképletek hátrányai Mindig gondoljuk meg, melyik a jobb, hatékonyabb módszer Adatok (rekordok) kinyerése 1) Segédoszlopot alkalmazó képlet adatok kinyerésére az ÉS függvény segítségével 2) Tömbképlet az INDEX és KICSI függvények alkalmazásával adatok kinyerésére az ÉS logikai űvelettel Az ÖSSZESÍT függvény Az ÖSSZESÍT függvé sak E el + űködése 1) Tömbképlet: INDEX és ÖSSZESÍT adatok kinyerésére az ÉS Boole-operátorral Adatok kinyerése: HAHIBA 1) A HAHIBA függvény hatékony és hatástalan alkalmazása 2) Kapott eredmény bemutatása – függőleges tö e : SOROK, vízszintesen: OSZLOPOK 3) Hagyományos képlet, segédoszloppal rekordok kinyerésére a VAGY függvénnyel 4)Tömbképlet rekordok kinyerésére a VAGY Boole-operátorral (3 példa) Adatok kinyerése: kétdimenziós keresés 1) Kétdimenziós keresés: INDEX és ÖSSZESÍT függvények tömbképletben, VAGY és ÉS Booleoperátorral
5
{Ctrl + Shift + Enter} CD-ROM
www.offcafe.hu
Listák Nevek kig űjtése listá ól: Nevek a Lista -ben, melyek nincsenek a Lista 1-ben: segédoszloppal 2) Nevek a Lista 2-ben, melyek nincsenek a Lista 1-ben: 2010+ 3) Nevek a Lista 2-ben, melyek nincsenek a Lista 1-ben: Excel 2003 4) Segédoszlop alkalmazása az eredmény területen (ha nem lehet az adatoknál) Tömbfüggvény: GYAKORISÁG 1) Tömbképlet: GYAKORISÁG függvény – hány szám van az egyes kategóriákban 2) A GYAKORISÁG ered é e függőleges tö . Ha vízszi tes tarto á t akaru k: TRANSZPONÁLÁS 3) A GYAKORISÁG nem veszi figyelembe a szöveget és az üres cellákat 4) Ha a GYAKORISÁG függvé e azo os soportok duplikátu ok va ak, a függvé sak az elsőt veszi figyelembe. A GYAKORISÁG függvény ereje 1) Egyedi értékek (számok) egy listában: GYAKORISÁG vagy DARABTELI? 2) Egyedi értékek száma egy listában – egy feltétel alapján 3) Egyedi értékek keresése vegyes adatok között 4) Egyedi érékek keresése vegyes adatok és üres cellák esetén A tilde ~ alkal azása, hog a hel ettesítő karaktereket té leges karakterekké t kezeljük Tilde alkal azása hel ettesítő karaktereket is tartal azó eg edi értékek egszá lálására a tartományban üres cellák is vannak) 7) Tilde: egyedi értékek dara szá a eg feltétel alapjá ; hel ettesítő karakterek és üres ellák 8) Segédoszlop: egyedi értékek megkeresése a DARABTELI és DARABHATÖBB függvényekkel 9) Dinamikus tartomány: egyedi értékek megkeresése A SZORZATÖSSZEG tömbfüggvény A SZORZATÖSSZEG függvény Alapvető egoldások A két mínuszjel A =SZORZATÖSSZEG(--(B4:B9=F4);--(C4:C9=G4);D4:D9) képlet SZORZATÖSSZEG – matematika
Mate atikai
űveletek & SZORZATÖSSZEG (7 példa)
SZORZATÖSSZEG és nem numerikus értékek
Az MSZORZAT tömbfüggvény MSZORZAT példák: Súlyozott átlag kiszá ítása: 3 lehetőség MSZORZAT példák (2)
Listák és rendezés 1) Egyedi elemek kinyerése az INDEX és a KICSI függvénnyel 2) Egyedi elemek kinyerése: segédoszlop 3) Egyedi elemek kinyerése (több oszlop): segédoszlop segítségével 4) Számok rendezése képlettel 5 Segédoszlop: rekordok e iségé ek C oszlop: Készlet övekvő sorre d e re dezéséhez 6 Tö képlet: e elkedő sorre d u erikus értékek alapjá 7) A 3 legjobb (Top 3) eredmény a névvel együtt – holtverseny lehet 8) Segédoszlop: övekvő sorre d e re dezés szöveget tartal azó oszlop alapjá 9) Egyedi elemek (szavak) egy listában – rendezés, nincsenek üres cellák Feltételes formázás tömbképletekkel Feltételes for ázás: adott verse ző legjo ideje segéd ellával 2 Feltételes for ázás: adott verse ző legjo ideje MIN 3) Feltételes formázás: A legkisebb érték (MIN) teamenként 6
{Ctrl + Shift + Enter} CD-ROM
www.offcafe.hu
Excel 2010: MÓDUSZ.TÖBB függvény 1) MÓDUSZ (2007 és korábbi) vagy MÓDUSZ.EGY (2010 ++) 2) MÓDUSZ.TÖBB: gyors módszer. 3) MÓDUSZ.TÖBB: képlet a #HIÁNYZIK hibaérték kiküszöbölésére. 4) Képlet: vízszintes megjelenítés Le yűgöző tö bképletek: Érdekességek 1) Hány karakter hosszúságú a leghosszabb szó egy oszlopban? 2) A leghosszabb karakterlánc egy cellában 3) KERESÉS: az 1. oszlopban fölösleges szóközök, az oszlop nem rendezett 4) KERESÉS: az 1. oszlopban fölösleges szóközök, az oszlop rendezett 5) Karakterek száma egy oszlopban 6) Hány rosszul beírt (fölösleges) szóköz van egy oszlopban? Tö u kalapo lévő tö oszlop értékei ek eg kritériu alapjá törté ő összeadása a SZUMHA, INDIREKT és SZORZATÖSSZEG függvényekkel 8 Eg sor a az első érték, üres ellák előfordulhat ak 9 Eg sor a az utolsó érték, üres ellák előfordulhat ak 10 Dátu egkeresése a sor a lévő első szá alapján 11) Adatok oszlopokban, találat oszlopban 12) Egy oszlop egyedi értékeinek összeadása 13) Egy sor egyedi értékeinek összeadása 14) Egy oszlop egyedi értékeinek összeadása, üres cellák lehetségesek 15) Sorrend feltétellel 16 Két eg ást követő ap ered é é ek a i u a: RÉSZÖSSZEG, ELTOLÁS és MAX függvény 17) A VÁLASZT függvény két oszlop „egybemosására” egy adattáblában 18) Keresés – szorzással 19) Minden 3-ik szám összege 20) Számok kinyerése karakterláncból, szorzó megállapítása 21) Szöveg rendezése tömbképlettel 22) Egy lista 3 legnagyobb értékének megkeresése Adatok
7
