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... →Excel 2013
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 olyan tartományt akarunk összegezni, amelyben bármilyen hibaüzenet van, akkor az Excel SZUM (SUM) függvénye hibát ad vissza, ahogy a következő ábrán is látható. A hiba ott keletkezik, hogy az „D” oszlopban lévő FKERES (VLOOKUP) függvény a termékkód alapján megpróbálja megkeresni a termék egységárát a termék-törzs táblában, […]
Tovább... →Főleg importált adatok esetén fordul elő gyakran, hogy egy oszlopban minden adat aposztróffal kezdődik. Ezt arra használják, hogy mindenképpen szöveges formátumú legyen az adott oszlop. Extrém esetben az üres cellák is tartalmaznak legalább egy aposztrófot. Megszabadulni ezektől nem is olyan egyszerű, de a most ismertetett műveletsorral megoldható. Alap […]
Tovább... →Az Excel hatékony használatához nélkülözhetetlen, hogy gyorsan tudjunk navigálni, azaz egyik helyről a másik helyre ugrani a cellakurzorral. Ez a feladat munkalapon belül, munkalapok között, és munkafüzetek között fordul elő. Összegyűjtöttem néhány hasznos tippet , próbáljátok ki, és vezessétek be a mindennapokba, érdemes! Munkalapon belüli gyors navigációk […]
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?
Egy nagyon ötletes képletet szeretnék mutatni, amivel azt lehet megvizsgálni, hogy egy tartomány minden eleme azonos-e vagy sem.
Legyen a vizsgált tartomány az A1:F1 tartományban
=AVERAGE(COUNTIF(A1:F1;A1:F1))=COUNTA(A1:F1) =ÁTLAG(DARABTELI(A1:F1;A1:F1))=DARAB2(A1:F1)
Angol rendszerben az argumentum elválasztót pontosvesszőről vesszőre kell cserélni.
Hogy működik?
COUNTIF(A1:F1;A1:F1)
Ez egy tömbképlet, ami a tartomány minden elemére megvizsgálja, hogy hányszor szerepel a tartományban, és egy tömböt ad vissza, az egyes elemek előfordulási számával. Ha minden elem egyforma, akkor mindegyik ugyanannyiszor lesz benne, tehát a visszaadott tömb minden eleme ugyanaz lesz, és ez megegyezik a tartomány celláinak a számával.
A konkrét példánál, mivel a tartománynak 6 cellája van egymás mellett, a visszaadott tömb minden elemének 6-nak kell lennie.
AVERAGE(COUNTIF(A1:F1;A1:F1))
Ha minden elem 6, akkor ezek átlaga is 6, ez pedig éppen egyenlő a tartomány elemeinek a számával. Nem marad más hátra, mint ellenőrizni, hogy a kapott érték valóban megegyezik-e a tartomány kitöltött celláinak számával. Ezt a COUNTA ( DARABTELI ) függvénnyel tudjuk lekérdezni.
Letölthető munkafüzet
Videón talán érthetőbb
Tartomány minden eleme egyforma?
A haladó szűrő eszközt érdemtelenül kevesen ismerik. Többet érdemelne. Emiatt egy sorozatot indítok a lehetőségeiről. Volt már szó korábban róla, de úgy döntöttem, még egyszer nekifutok. A haladó szűrőt speciális vagy irányított szűrőnek is szokták nevezni. Az autoszűrővel egy lépésben nem megoldható, összetettebb szűrésekhez, illetve a gyakran ismétlődő, akár egyszerűbb szűrésekhez használjuk.
Lényege, hogy a szűrendő táblázaton kívül egy külön tartományban fogalmazzuk meg a szűrési feltételeket, majd a haladó szűrő párbeszédében megadjuk a feltételtartomány helyét, pár dolgot még beállítunk, és kész!
A haladó szűrő előnyei
- Bonyolultabb szűrésekre is alkalmas egy lépésben
- Az eredmény akár azonnal új helyre másolható, akár másik lapra
- A szűrési feltételeket tároljuk, nem kell folyton megcsinálni, kitalálni
- Az eredményből kiszűrhetjük a szükségtelen oszlopokat (Projekció)
- Szűrés közben az oszlopok sorrendjét is módosíthatjuk
A cikksorozat tagjai
Mi az, hogy számított feltétel?
Amikor a feltételben nem állandó (konstans) értékhez viszonyítunk, hanem egy számítás eredményéhez. Alapvetően kétféle esetről beszélhetünk.
Egy oszlop minden elemének hasonlítása ugyanahhoz a számításhoz
- Hasonlítás egy konkrét számítás eredményéhez
- Hasonlítás egy másik cellában lévő értékhez
Ilyenkor a feltételként egy képletet adunk meg. De a képlet megadási módja speciális.
="relációsjel" & képlet vagy cellacím
Konkrét példa: Az oszlop átlagánál nagyobb
=">"&AVERAGE(oszlop) =">"&ÁTLAG(oszlop)
Konkrét példa: A H1 cellánál nagyobb
=">"&H1
Hogy működik?
A relációs jel után fűzi a számítás eredményét, és a kettőt együtt tekinti feltételnek.
Soronkénti feltételvizsgálat
Ebben az esetben egy minden sorban kiértékelendő képletet adunk meg. Például arra vagyunk kíváncsiak, hogy az egyik oszlopban lévő érték, hogy viszonyul egy ugyanabban a sorban, de másik oszlopban lévőhöz.
Két nagyon fontos dolgot kell ilyenkor tenni:
- A feltételtartomány fejlécének üresnek kell maradni, vagy ha adunk nevet, az nem egyezhet meg a szűrendő táblázat egyik mezőnevével sem.
- A szűréshez használt képletet a táblázat első adatsorára kell megadni
Konkrét példa
Tegyük fel, hogy a fenti táblázatban keressük azokat a sorokat, ahol a számlaérték nem egyezik meg a fizetett összeggel, azaz valami hiba van. Ezt másképpen úgy fogalmazhatjuk meg, hogy keressük azokat a sorokat, ahol a C oszlopban lévő érték nem egyezik meg a D oszlopban lévővel.
A feltételtartományt a fentiek szerint kell megadni, tehát mindkét megoldás megfelelő.
Hogy működik?
A haladó szűrő futtatásakor a megadott vizsgálatot minden soron elvégzi, és azokat a sorokat, ahol a képlet FALSE (HAMIS) eredményt ad, elrejti (kiszűri)
Letölthető munkafüzet
És persze a videó
Számított feltétel a kritérium tartományban
A haladó szűrő eszközt érdemtelenül kevesen ismerik. Többet érdemelne. Emiatt egy sorozatot indítok a lehetőségeiről. Volt már szó korábban róla, de úgy döntöttem, még egyszer nekifutok. A haladó szűrőt speciális vagy irányított szűrőnek is szokták nevezni. Az autoszűrővel egy lépésben nem megoldható, összetettebb szűrésekhez, illetve a gyakran ismétlődő, akár egyszerűbb szűrésekhez használjuk.
Lényege, hogy a szűrendő táblázaton kívül egy külön tartományban fogalmazzuk meg a szűrési feltételeket, majd a haladó szűrő párbeszédében megadjuk a feltételtartomány helyét, pár dolgot még beállítunk, és kész!
A haladó szűrő előnyei
- Bonyolultabb szűrésekre is alkalmas egy lépésben
- Az eredmény akár azonnal új helyre másolható, akár másik lapra
- A szűrési feltételeket tároljuk, nem kell folyton megcsinálni, kitalálni
- Az eredményből kiszűrhetjük a szükségtelen oszlopokat (Projekció)
- Szűrés közben az oszlopok sorrendjét is módosíthatjuk
A cikksorozat tagjai
Névtartományok használata
A szűrésnél a Listatartományt (List range), a szűrőtartományt (Criteria range), sőt, akár a hova másolja (Copy to) tartományt is megadhatjuk névtartománnyal. Ezzel egyszerűbbé és rugalmasabbá tehetjük a szűrő használatát.
A névtartományok létrehozásával és kezelésével kapcsolatban már volt egy korábbi anyag, amit itt olvashatsz el:
https://excel-bazis.hu/tutorial/dinamikus-nevtartomany-hasznalata
Táblázatok használata
Abban az esetben, amikor az adatforrást (listatartomány) táblázattá alakítottuk, tisztában kell lennünk azzal, hogy a táblázat egyes részeire hogyan hivatkozhatunk. Erről bővebben pedig itt lehet olvasni:
https://excel-bazis.hu/tutorial/strukturalt-hivatkozas-adattablak-hasznalata-eseten
Ebből számunkra az lesz a legfontosabb, hogy a haladó szűrésnél a listatartománynak tartalmaznia kell a fejléc adatokat is. Ha viszont csak a nevét használjuk, akkor az nem tartalmazza a fejlécet. A teljes táblára fejléccel együtt, szabályos, un. struktúrált hivatkozással így kell hivatkozni:
Magyar Excel: Tabla_neve[#Mind]
Angol Excel: Tabla_neve[#All]
Letölthető munkafüzet
Mindez működés közben
Névtartományok haladó szűrésnél
