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... →Excel 2010
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
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
Ismétlődések eltávolítása (Remove Duplicates)
Gyakori feladat, hogy egy listából, amiben ismétlődő elemek is vannak, képezni kell egy olyan listát, amiben minden elem csak egyszer szerepel. Ezt a listát hívjuk egyedi (Unique) listának.
Erre a feladatra több megoldás is létezik.
- Haladó szűrés speciális esete
- PVOT tábla speciális esete
- Excel 2007-től külön gomb van rá
Melyiket használjam?
Ha Excel 2007-nél korábbi verzió esetén az első két megoldás jöhet számításba. Ha Excel 2007 után vagyunk, akkor viszont attól függ, hogy a táblázat egy vagy több oszlopból áll, illetve attól, hogy az eredeti listát is meg szeretnénk-e őrizni. Az is szempont lehet, hogy a folyamatot esetleg akarjuk-e automatizálni, azaz makrózni.
Megoldás a haladó szűrővel
- Kijelöljük az eredeti listát
- Adatok->Speciális (Advanced) szűrő
- Nem adunk feltételt (ha minden elem kell)
- Beállítjuk, hogy helyben vagy más helyre szűrjön
- Csak az egyedi rekordok (Unique records only)
Minden egyéb pontosan ugyanúgy működik, mint ahogy a többi leírásban olvashattad, azaz szűrhető helyben is, az eredmény tehető azonnal másik munkalapra, használhatók nevek a forrásokban, és akár még feltétel is adható.
Letölthető munkafüzet
Mindez működés közben
A videóban természetesen szó lesz a másik két megoldási lehetőségről is
Egyedi lista készítése
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
Az eredmény másik munkalapra egyből?
Talán van, aki tapasztalta, hogy a haladó szűrő nem engedi a szűrés eredményét egyből másik munkalapra tenni. Emiatt azt előbb az aktuális munkalapra, majd onnan másolva és beillesztve lehet áttenni más lapra.
Íme a hibaüzenet
Hosszú évekig bosszantott, hogy miért van ez így, míg egyszer csak rátaláltam a megoldásra. Ami mindig is ott volt, csak nem gondolkodtam, hanem csak idegeskedtem. Szóval a megoldás benne van az üzenetben. Ha csak az aktív munkalapra kerülhetnek az eredmények, akkor mi lenne, ha az egészet egy üres munkalapról indítom, és az adatforrást választom ki másik lapról (mert azt lehet)?
A fenti beállításban a Listatartomány másik lapról lett kijelölve, a Szűrőtartomány és a Hová másolja pedig az aktuálisról.
És működik!
Letölthető munkafüzet
Lássuk a videót!
Haladó szűrés eredménye másik munkalapra
Az 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
Csak bizonyos oszlopok lekérése az eredménybe (projekció)
Ez a művelet elvileg adatbázis-kezeléshez kapcsolódik, és szűréssel nem szoktak ilyesmit csinálni, de a haladó szűrő tudja ezt is. Tehát adott az alap táblázatunk:
Ha semmi mást nem szeretnénk, csak kivenni néhány tetszőleges oszlopot, akkor ezt kell tenni:
- Egy üres területre (praktikusan az alaptábla mellé (de alá is lehet), másoljuk ki a kívánt oszlopok neveit(mezőnevek) abban a sorrendben, ahogy szeretnénk őket
- Haladó szűrő indul
- Más helyre másol, Feltétel nincs, és célként jelöljük ki az előkészített mezőneveket!
- Eredményként csak a kiválasztott három oszlopot kapjuk.
Szűrés és projekció egyben
Itt csak az lesz a különbség, hogy megadjuk a kritérium tartományt is, azaz nem marad üresen.
Letölthető munkafüzet
És persze a kapcsolódó videó
Projekció haladó szűrővel
Adatlekérdezések és feldolgozás során leggyakrabban valamilyen feltételnek megfelelő adatrekordokkal végzünk műveletet, tehát az eredeti adattáblát feltétel szerint szűkítjük. A feltételt meg kell valahogy adni vagy fogalmazni.
Ennek a témának egy összefoglaló anyagát már bemutattam, emlékeztetőként, vagy gondolatébresztőként érdemes megnézni.
https://excel-bazis.hu/tutorial/halado-szuresi-technikak-szurooszlop-vagy-iranyitott-szuro
Most a feltételtartomány fogalmát és gyakorlati példáit nézzük meg. Ez az alapelv aztán visszaköszön az MS QUERY vagy az ACCESS lekérdező varázslójában.
Tehát van egy adattáblánk
Ehhez a táblához szeretnénk szűrőfeltételeket létrehozni. A legegyszerűbb szűrés az Autoszűrő lenne, talán mindenki ismeri,most nem térek ki rá. Ott oszloponként állíthatunk szűrési feltételt. Minden további feltétel tovább szűkíti a találati listát, mert az egymás mellett lévő feltételek ÉS kapcsolatba kerülnek, emiatt az eredmény rekordok azok lesznek, amelyek minden beállított feltételnek megfelelek.
Vannak azonban olyan esetek, amelyekhez nem tudunk beállítani az Autoszűrővel egy lépésben feltételt.
Autoszűrő fricska
Szűrjük a Budapesti férfiakat és a Miskolci nőket egy lépésben!
A Település oszlopban még csak beállítom, hogy a Budapestieket meg a Miskolciakat is mutassa, de a Nem oszlopban akkor mit állítsak, hogy a fenti feltétel teljesüljön?
Na, ilyen esetekben szokták két lépésben megoldani a feladatot. Szűrik először az egyik adagot, kimásolják egy új lapra, majd a másikra is szűrnek, és az előző eredmény után másolják.
Nem valami jó, viszont rossz. Legalább is nem optimális.
A megoldás: Kritérium tartomány(Criteria Range) és irányított szűrő (Advanced Filter)
A kritérium tartomány az alap adattáblától külön lévő kis táblázat, amiben speciális módon meg tudjuk fogalmazni a feltételeket, és ezt a tartományt adjuk paraméterként az Irányított szűrőnek és az összes adatbázis függvénynek.
Az alap feltétel tartományok a táblázat mezőneveiből és a feltételekből állnak. A működése egyszerű. A feltételeket soronként értékeli ki az Excel. Ami egymás mellett van, az ÉS kapcsolatot jelöl, ami egymás alatt van, az pedig VAGY kapcsolatot.
Alap szűrőtartományok. Ezeket Autoszűrővel is lehet
Összetettebb feltételek, amiket autoszűrővel nem lehet
Helyettesítő (Joker) karakterek használata
Ha ki szeretnénk szűrni azokat az elemeket, amik tartalmaznak egy adott karakter sorozatot, akkor használhatjuk a jól ismert joker karaktereket. (* és ?)
Ha esetleg a szűrendő szövegrészben van csillag vagy kérdőjel, akkor azokat úgy szűrhetjük, hogy elé teszünk egy hullámjelet (tilde)
Használat az irányított (speciális) szűrőhöz
- A listatartomány a teljes táblázat fejléccel együtt
- A szűrőtartomány pedig a külön részben megfogalmazott feltételtartomány lesz
- Az eredményt lehet az aktuális munkalap más helyére is másolni azonnal.
Használat az adatbázis függvényekhez
Például egy cellába szeretném megtudni a Budapesti férfiak és a Miskolci nők összlétszámát, akkor erre az AB.DARAB2
( DCOUNTA ) függvényt használhatjuk. A NÉV oszlopban számoljuk meg a külön kritériumtartományban megfogalmazottaknak megfelelő sorok számát.
Adatbázis: A teljes táblázat fejléccel
Mező: Az az oszlop, ahol a megszámlálást kell végezni. Megadható sorszámmal, hogy hányadik oszlop vagy a nevével, vagy a fejléc szöveget tartalmazó cella címével.
Kritérium: A feltételtartomány címe
Letölthető gyakorló munkafüzet
iranyitott-szuro-adatbazis-fuggveny-felteteltartomany
Mindez működés közben
Feltétel tartományok az Excelben
