A vágólap elforgatott beillesztés funkcióját mindenki ismeri. Ami a forrás tartományban vízszintesen volt, az a céltartományban függőlegesen lesz vagy fordítva. Az Excel különböző verzióiban más-más módokon lehet közvetlenül elérni, de az Irányított beillesztés párbeszédben már régóta ugyanott találjuk. Az a probléma, hogy ezzel az elforgatással a célhely […]
Tovább... →Függvények
A 2010-es Excelben bemutatkozik az ÖSSZESÍT, angol nevén az AGGREAGATE függvény. A RÉSZÖSSZEG (SUBTOTAL) függvény helyett használhatjuk, és érdemes is használni, mert sokkal többet tud nála. Ha esetleg nem ismernéd a RÉSZÖSSZEG függvényt, akkor már nincs is rá szükséged, feltéve, hogy sem Te, sem mások, akikkel Excel […]
Tovább... →Ha az a feladatunk adódik, hogy lekérdezzük két dátum közötti HÉTFŐK vagy VASÁRNAPOK vagy bármilyen napok számát, akkor a következő lehetőségeink adódnak.
- Volt egy korábbi anyag, ahol többek között erre is volt egy tömbképlet:
https://excel-bazis.hu/tutorial/szamok-datumok-rendezett-halmaza-mint-tomb
Ennek előnye, hogy minden Excel verzióban működik. - Használhatjuk az Excel 2010-ben megjelent NETWORKDAYS.INTL() függvényt, aminek magyar neve ÖSSZ.MUNKANAP.INTL
NETWORKDAYS.INTL() – ÖSSZ.MUNKANAP.INTL
Az Analysis Toolpack , ami az Excel 2007 óta bekerült alapból a rendszerbe szintén fejlődik, és ez a függvény a sima NETWORKDAYS() – ÖSSZ.MUNKANAP() függvény kiegészítéseként, az Excel 2010-es verziójával jelent meg. Mindkettő arra való, hogy két dátum között megadja a munkanapok számát.
A sima NETWORKDAYS() hétvégeként csak a szombat-vasárnapokat fogadja el, míg az újabb verziónak egy erre a célra szolgáló argumentumban megadhatjuk, hogy mely napokat tekintsen a héten munkanapoknak, és melyeket ne.
Ez az argumentum egy hét jegyű szöveg (sztring), amiben a karakterek a hét napjait reprezentálják binárisan, hétfőtől kezdődően. NULLA, ha munkanap, és EGYES, ha szünnap.
Példa szünnapokra
| Szünnapok | Sztring | Függvény |
| Szo-Vas (alap) | „0000011” | =NETWORKDAYS.INTL(kezdo_datum;Vegdatum) |
| Csak Szombat | „0000010” | =NETWORKDAYS.INTL(kezdo_datum;Vegdatum;”0000010″) |
| Vas – Hétfő | „1000001” | =NETWORKDAYS.INTL(kezdo_datum;Vegdatum;”1000001″) |
OK, de hogy számoljuk meg pl. a Keddeket?
Hát úgy, hogy csak a Keddeket adjuk meg munkanapként, azaz a második karakter lesz csak NULLA, a többi EGYES.
=NETWORKDAYS.INTL(kezdo_datum;Vegdatum;"1011111") =ÖSSZ.MUNKANAP.INTL(kezdo_datum;Vegdatum;"1011111")
Nézzük működés közben, ahol látható még pár érdekesség
Adott napok száma két dátum között
Gyakori feladat, hogy meg kell vizsgálni, azt, hogy egy érték beleesik-e egy intervallumba vagy sem. Ilyenkor azt kell tennünk, hogy megvizsgáljuk, az adott érték nagyobb vagy egyenlő-e az alsó határnál, és egyben kisebb vagy egyenlő-e a felső határnál. Valahogy így:
=HA(ÉS(vizsgált_érték>=also_hatar; vizsgált_érték<=felső_határ); valami_ha_igaz;valami_ha _hamis)
Milyen jól jönne ilyenkor egy BETWEEN() függvény ugye? De jelenleg még az Excel nem tartalmaz ilyet.
Vagy mégis?
Segítségül hívhatjuk a MEDIÁN() függvényt (angolul ékezet nélkül), ami egy listának a közép értékét keresi és adja vissza. A mi esetünkben a lista 3 elemű. Az alsó határ, a felső határ, és a vizsgált érték. A sorrendjük mindegy.
Ha a vizsgált érték az alsó és a felső határ között van, vagy egyenlő bármelyikkel, akkor saját maga értéke lesz a középérték, és ezt vizsgálhatjuk a következőképpen:
=HA(MEDIÁN(alsó_határ;vizsgált_érték;felső_határ)=vizsgált érték; valami_ha_igaz;valami_ha _hamis)
Konkrét példa
A dátumok közül szeretnénk eldönteni, hogy melyek estek 2012-es évbe. A vizsgált időszak alsó és felső határait tároltuk a G1 és G2 cellákban.
Lássuk működés közben
Between függvény Excelben?
A feltétel szerinti összegzés (Sumif/Szumha), illetve a megszámlálás (Countif/Darabteli) függvényeket legtöbben ismerik. Sőt, az excel 2007-től ezek több feltételt is használni képes nagy-testvérei is egyre inkább bekerülnek a köztudatba. (SumIfs/Sumhatöbb, Countifs/Darabhatöbb). Ezekkel a függvényekkel remek riportokat lehet készíteni, de azért van néhány korlát, amiket nem tudnak átlépni.
Az oktatóanyagban ezt a táblázatot használjuk példaként.
Érdekesebb kérdések ehhez a táblázathoz
- A nyereséges sorok száma
- Az „Északi” nyereségesek száma
- Az „Északi” nyereségesek összbevétele
- Északiak és Déliek együttes bevétele vagy nyeresége vagy vesztesége.
Természetesen lehetne egy „Nyereség” nevű számított oszlopot tenni a végére, de most ezt szándékosan nem tesszük.
Mi a gond az ismert feltételes összesítő függvényekkel?
- A feltételek csak konkrét értékek lehetnek (konstans, cellacím vagy számítás), de olyat már nem tudnak, hogy csak azokat a sorokat vegyék figyelembe, ahol pl. a ‘B’ oszlopban nagyobb érték van, mint a ‘C’ oszlopban
- A feltételeket csak ÉS kapcsolatba tudják hozni, azaz egy következő feltétel tovább szűkíti az előző feltéteknek megfelelő sorok számát. A feltételeket nem képesek VAGY kapcsolatba tenni.
Akkor mi a megoldás?
- Egyik lehetőség, hogy egy külön oszlopba elvégezzük a vizsgálatot. majd ezt az oszlopot értékeljük ki. Ha pl. a nyereséges sorok számát keressük, akkor az utolsó oszlopba írhatunk egy olyan képletet, ami nullát ad eredményül, ha nincs nyereség, különben pedig egyet. Ezen oszlop összesítésével megkapjuk a nyereséges cégek számát. Ez két lépés. Egyik a segédoszlop elkészítése, és a második az összegzés.
A segédoszlop képlete lehetne egy HA() függvény, de Én ezt javaslom beírni, majd lehúzni az oszlopra:
=--(B2>C2)
A zárójelben lévő kifejezés TRUE/FALSE eredményt ad, a dupla mínusz jel pedig ezt alakítja át 0/1 értékekre.
- Második lehetőség, hogy Adatbázis függvényt próbálunk bevetni, és a kritérium tartományt olyan számítással adjuk meg, ami a táblázat első sorában értékel ki. Az egész kritériumtartományt elnevezzük „Criteria” néven.
A függvény pedig a következő:
=DCOUNTA($A$1:$D$13;"Kateg";Criteria)
=DARAB2($A$1:$D$13;"Kateg";Criteria)
És újra a SUMPRODUCT (Szorzatösszeg)
- Harmadik lehetőség, hogy az egészet egy képlettel oldjuk meg.
=SUMPRODUCT(--(B2:B13>C2:C13)) =SZORZATÖSSZEG(--(B2:B13>C2:C13))
Ez a képlet belül a ‘B’ és a ‘C’ oszlopot hasonlítja össze, és egy olyan tömböt ad eredményül, aminek annyi eleme van, ahány sora a két megadott tartománynak, de ott van benne TRUE, ahol a B>C, különben FALSE. A dupla mínusz jel a logikai értékeket 0/1 értékekké alakítja. Végül a SUMPRODUCT összeadja, az eredmény a tömbben szereplő egyesek összege, azaz a nyereséges sorok száma lesz.
Letölthető munkafüzet: szamitott-felt-szerinti-osszesitesek
A kapcsolódó videóban látható a megoldás menete, és a többi kérdésre is választ kapunk
Számított feltétel szerinti összegzés
Az anyagban megmutatom, hogy lehet képlettel megoldani azt a feladatot, amikor a sor, illetve oszlop azonosítók alapján egyszerre kellene összesítést vagy megszámlálást csinálni. Volt egy korábbi anyag, ahol 2D keresést mutattam, de ez más.
Íme a probléma
Tehát ugyanazok az értékek ismétlődgetnek a fejlécben, mint az első oszlopban. Nekünk pedig az alsó, összesített tábálázatot kell összehozni
Hű, ez nagyon érdekes. nem tudom, hogy mennyire gyakori probléma, de Nekem annyira megtetszett, hogy azt éreztem, ezt mindenképpen fel kell tenni az Excel Bázisra.
PIVOT?
Mivel ismétlődő fejléc elemek vannak, PIVOT-al nem lehet megcsinálni. Legalább is Én nem találtam rá megoldást. ha lenne valaki, aki IGEN, akkor commentben ossza már meg velünk!
MACRO?
Igen, makróval meg lehet csinálni. Egymásba ágyazott ciklusokkal. Már ha érted mire gondolok. Ez nagyobb táblázat esetén meglehetősen lassú, ráadásul tudni kell programozni, és a VBA nyelv ismerete is szükséges.
A megoldás – Képlet – SUM vagy SUMPRODUCT
Nem gondolnád, Igaz? Nézzük a feladatot konkretizálva az ‘A‘ kategóriára: Összesíteni kell az adattartomány azon elemeit, ahol a fejlécben ‘A‘ van és a sor elején is ‘A‘ van. Mivel az egyik feltétel vízszintesen van, a SUMIFS (Darabhatöbb) függvény nem jön számításba. Tömbökkel kell dolgoznunk, így esélyes, hogy tömbképlet lesz a megoldás.
Általánosan kb. így néz ki:
=ÖSSZEG((fejléc="A") * (Első oszlop="A") * (adatok))
A fenti kép alapján konkrétan, még mindig csak az „A” kategóriára
=SUMPRODUCT(($B$3:$B$12="A")*($C$2:$J$2="A")*($C$3:$J$12))
A SUMPRODUCT (SZORZATÖSSZEG) remek függvény. Kiválón alkalmas hasonló módon több feltétel szerinti összegzésre. Amíg nem volt 2007-es Excel, és nem nem volt SUMIFS (DARABHATÖBB), addig így lehetett csak hasonló problémákat megoldani.
Hogy működik?
Hát, ezt írásban meglehetősen hosszadalmas lenne elmagyarázni. Inkább nézd meg a videót. Abban elmondom, hogy mi a működés alapja, és megnézzük az összegzésen kívül a megszámlálást is.
Gyakorló munkafüzet: 2D-felt-osszegzes-megszamlalas
Két dimenziós összesítés
Előfordulhat olyan feladat, amikor egy táblázat első oszlopában és a fejlécében kell megkeresni egy-egy konkrét értéket, és a kettő által adott metszéspontban lévő adatot szeretnénk kivenni, úgy, ahogy ez az alábbi ábrán látható.
A feladat során lényeges, hogy mind az első oszlop, mind a fejléc egyedi adatsort tartalmaz, ahol nincs ismétlődő elem. Ez azért fontos, mert a kereséshez a közismert függvényeket fogjuk használni, amelyek alapesetben az első előfordulást keresik.
Két megoldást ismertetnék. Itt azonnal a megoldás látható, a videóban pedig ott a magyarázat is.
1. VLOOKUP-MATCH páros (Fkeres-Hol.Van)
A lényeg az, hogy az egész táblára nézve a ‘B’ oszlopban keressük VLOOKUP-al a kívánt autómárkát (Renault), és azt, hogy melyik oszlopból adja vissza az eredményt, nem egy konstans számmal adjuk meg, hanem a MATCH függvénnyel megkérdezzük, hogy a fejlécben a kívánt hónap (Március) hányadik.
1. lépés: a VLOOKUP
=VLOOKUP("Renault";$B$2:$H$12;4;0)
A VLOOKUP megkeresi az első oszlopban a „Renault” szót, és a negyedik oszlopból visszaadja a 266-os értéket.
2. lépés: a MATCH
=MATCH("Március";$B$2:$H$2;0)
A MATCH megadja, hogy a fejlécben a „Március” hányadik
3. lépés: Egybe rakva
=VLOOKUP("Renault";$B$2:$H$12;MATCH("Március";$B$2:$H$2;0);0)
Az 1. lépésben lévő VLOOKUP képletben a 4-es helyére beletettük a komlett MATCH részt.
INDEX-MATCH páros (Index-Hol.Van)
Ugyanezt a feladatot az INDEX függvénybe ágyazott két MATCH függvénnyel is meg lehet oldani. Ugyanis az INDEX pont azt tudja, amire itt szükségünk van, azaz egy táblázat adott számú sorában és adott számú oszlopának metszéspontjában lévő értéket ad vissza.
=INDEX(táblázat, sor_szám, [oszlop_szám])
Ezt a lehetőséget ritkábban használjuk, sokkal gyakoribb, hogy az INDEX csak egy dimenziós sorozatban (oszlopban) keres, és a második (oszlop) argumentumot meg sem adjuk.
Tehát ha az INDEX függvény sor és oszlop argumentumaiba MATCH függvényekkel kiszámoltatjuk azt, hogy a „Renault” hányadik az első oszlopban, és a „Március” hányadik az első sorban, akkor meg is kapjuk a kettő metszéspontjában lévő értéket.
=INDEX($B$2:$H$12;MATCH("Renault";$B$2:$B$12;0);MATCH("Március";$B$2:$H$2;0))
Letölthető minta dokumentum: Ketdimenzios-kereses
A videóban a megoldást interaktívvá tesszük, azaz választható az autómárka és a hónap, és a megoldást bemutatom Táblázattá alakított tartományon is.
Két dimenziós keresés akció közben
Ha szükségünk lenne egy olyan képletre, ami egy tartományból listázza a valahány (három, négy, öt, stb…) legnagyobb értéket, akkor íme a megoldás. Mivel a képletnek nem csak egy eredménye van, hanem több, a feladatot tömbképlettel tudjuk megoldani.
A megoldáshoz, illetve a leírás megértéséhez ismerni kell a LARGE (NAGY) függvényt, ami egy tartományból visszaadja a valahányadik legnagyobbat. A következő függvény pl. az A1:A20 tartományból visszaadja a második legnagyobbat.
=LARGE(A1:A20;2)
A három legnagyobb érték listázása
Tehát szükségünk van az első, a második és a harmadik legnagyobb értékre az A2:A10 tartományból. A Large függvénynek két tömböt kell megadni. Az egyik az adatokat tartalmazó tartomány, a másik pedig azoknak a számoknak a felsorolása tömbkonstansként vagy tartományi hivatkozásként, amennyiedik értékeket keressük. Utóbbiakat ezután hívjuk index számoknak.
Adatok tartománya: A2-A10
Index számok: E2:E4
- Jelöljünk ki három üres cellát
- Képlet
=LARGE(A2:A10;E2:E4)
A három legnagyobb érték bekerült a három kijelölt cellába.
Ha az adatok módosulnak, és ez érinti a három legnagyobbat, akkor azok is módosulni fognak.
Az index számokat tömbkonstanssal is meg lehet adni.
Az anyaghoz tartozó videóban bemutatom a képlet létrehozását mindkét módszerrel.
Top n legnagyobb érték kiválogatása képlettel
Ez a függvény kevéssé ismert, pedig szerintem nagyobb figyelmet érdemelne. Többszörös „HA” elágazásokat lehet vele sokkal egyszerűben megúszni. Úgy működik, hogy az első argumentumában megadott sorszámot (index) kiértékeli, és a másodiktól az n-edik argumentumok közül az annyiadikat választja.
Általános használata
=CHOOSE(index; érték1; [érték2]; ...)
Ha az index 1, akkor a VÁLASZT függvény az érték1 argumentumot adja vissza, ha 2, az érték2 argumentumot, és így tovább.
Az első argumentum, ami 1-254 közé eshet, lehet konstans szám, számot visszaadó képlet, vagy cellahivatkozás.
Egyszerű, példa
=CHOOSE(2;"alma";"körte";"barack")
Ez a függvény az érték argumentumok közül a harmadikat, azaz a „barack” szót adja vissza.
Összetettebb példa
A függvényben rejlő igazi erő akkor derül ki, amikor megtudjuk, hogy az érték argumentumok nem csak konstans értékek vagy cellahivatkozások lehetnek, hanem tartományok, sőt, képletek is.
=CHOOSE(2;SUM(A1:A10);AVERAGE(A1:A10);MAX(A1:A10))
Az előző példa az index paraméter függvényében más-más műveletet végez el az A1:A10 tartománnyal.
Ha az index is valamilyen számítás, esetleg feltétel eredménye, akkor igen rugalmasan tudjuk használni összetett elágazások esetén.
További példák és részletek a függvénnyel kapcsolatban
http://office.microsoft.com/hu-hu/excel-help/valaszt-fuggveny-HP010342269.aspx
Tovább... →Gyakran előforduló feladat lehet, hogy feltételtől függően egy cellába változó képletet kellene írni vagy végrehajtani. Különösen igaz lehet ez abban az esetben, amikor a felhasználó állítja be pl. egy legördülő menüben, hogy mi legyen a feltétel, és ennek függvényében próbáljuk a képleteket dinamikusan összeállítani.
A modell
Egy munkalapon adott egy azonosító (ID) oszlop, és mellé egy másik munkalapról ki szeretnénk keresni a hozzá tartozó nevet. Igen ám, de azt, hogy melyik munkalapról kell kikeresni, azt egy legördülő menüből szeretnénk választani. Ez egy klasszikus VLOOKUP (FKERES) feladat, de a keresési tábla a legördülő menü szerint változó.
A modellt úgy állíthatjuk fel, hogy a különböző országokhoz tartozó adatok, amiben keresni akarunk, külön-külön munkalapokon vannak, és a munkalapok neve, megegyezik a legördülő menüből választható nevekkel, a mi példánkban „HU” és „DE”.
A „HU” és „DE” lapokon az adatokat táblázattá (Listává) érdemes alakítani, hogy név szerint is lehessen hivatkozni a képletekben, és így a méretük sem kell, hogy egyforma legyen.
Megoldás HA (IF) függvénnyel
Legegyszerűbb megoldásként a képlet feltételes megadását választhatjuk, azaz HA a kiválasztott országkód a B11-es cellában van, akkor a C14-be a következő képletet írhatjuk:
=IF($B$11="HU";VLOOKUP(B14;HU!$A$2:$B$5;2;0);IF($B$11="DE";VLOOKUP(B14;DE!$A$2:$B$5;2;0);0))
Látható, hogy egymásba ágyazott IF függvényeket látunk, és a képlet a B11 tartalmától függően, vagy a HU vagy a DE munkalapról keres, ugyanabban a tartományban. Ezután a képletet lehúzhatjuk a többi ID-re is.
Előny
Az IF (HA) függvényt a legtöbben ismerik, tudják használni.
Hátrány
Több országkód esetén meglehetősen hosszú és összetett lehet a képlet. (91 karakter)
Névtartományok használata
Ha a magyar és a német munkalapokon lévő keresési tartományokat dinamikus névtartománnyá alakítjuk, (lásd a fenti ábrán: HU és DE), akkor a képlet jelentősen lerövidülhet. (71 karakter)
=IF($B$11="HU";VLOOKUP(B14;HU;2;0);IF($B$11="DE";VLOOKUP(B14;DE;2;0);0))
Megoldás CHOOSE (VÁLASZT) függvénnyel
A függvényről külön oktatóanyag készült: https://excel-bazis.hu/tutorial/a-valaszt-choose-fuggveny
Azért jön számításba, mert a példa szerint más-más munkalapokról kell keresnünk értékeket. Ha ezeket sorszámmal tudnánk ellátni, akkor máris elkészült egy, az előző (HA) szerkezetnél rövidebb képlet.
A sorszámot egy segédtábla, és VLOOKUP használatával állítjuk elő
Tehát a VÁLASZT függvény számára a sorszámot a C24 tartalmazza. Most csak a rövidebb, névtartományokat használó képletet írom le, ami a D27-be kerül. (53 karakter)
=CHOOSE($C$24;VLOOKUP(B27;HU;2;0);VLOOKUP(B27;DE;2;0))
Megoldás INDIRECT (INDIREKT) függvénnyel
Az INDIRECT függvényt leggyakrabban arra használjuk, hogy dinamikusan, szöveges hivatkozásokat képzünk, amiket Ő normál hivatkozásokká tud alakítani.
Nézzünk egy példát:
Egy oszlop utolsó kitöltött cellájának értéke
Itt az adja a kihívást, hogy nem tudjuk, hány sor van kitöltve az adott oszlopban. Viszont ki tudjuk számolni, pl. az „A” oszlopban:
=COUNTA(A:A)
Tehát össze kell állítanunk az „A” oszlop utolsó cellájának a címét, ami ugyebár tuti „A”-val kezdődik, és a sor része meg az előző függvény eredménye. Van nekünk egy remek összefűző jelünk, és már mehet is.
=INDIRECT("A"&COUNTA(A:A))
Visszatérve a fenti példához, az INDIRECT függvénnyel azt használjuk ki, hogy az országkód kiválasztása után ugyanolyan képletet kell használni (VLOOKUP), csak a belseje lesz más, mert egyszer a HU, máskor a DE lapon vagy névtartományban kell keresni. ha a „HU” országkódot választottuk, akkor a „HU!A2:B5” tartományban, és „DE” kód esetén pedig a „DE!A2:B5” tartományban. Névtartományok használata esetén pedig a „HU” vagy a „DE” neveket használjuk.
Próbáljuk belefűzni a képletbe dinamikusan a választott országkódot, ami a „B35”-ben van:
=VLOOKUP(B39;INDIRECT($B$35&"!A2:B5");2;0)
Letölthető munkafüzet: Valtozo-adatforras-modellek
Tovább... →