A SZÖVEG (TEXT) függvénnyel tudunk számokból vagy dátumértékekből szöveget készíteni. Az első argumentumban adjuk meg, hogy mit kell átalakítani, a másodikban pedig azt, hogy hogy jelenjen meg, azaz a számformátumot. =SZÖVEG(eredeti szám;”számformátum kód”) Lássunk néhány példát magyar rendszerre érvényes kódokkal: Az egyéni számformátumokról bővebben itt olvashatsz. Probléma […]
Tovább... →Dátum és Időkezelés
Több megoldás is létezik arra a problémára, hogy a hónap sorszámából szöveges hónapnevet állítsunk elő. Megoldható egyedi számformázással, az INDEX függvénnyel, ha képezzük a hónapok neveit egy külön tartományban, és megoldható a SZÖVEG függvénnyel is. Ezt a leírást az utóbbira hegyezném ki, de azért a klasszikus megoldásokat […]
Tovább... →Tegyük fel, hogy szükségünk lenne arra, hogy a hónapneveket sorszámmá alakítsuk. Tehát a Január lesz az 1-es, és a December a 12-es. Lehet, hogy egy adattisztítási feladatunk van, ahol a dátum értékek szövegesen vannak, és abból nekünk kell rendes dátumot készíteni. Ennek egyik lehetséges megoldása, hogy az […]
Tovább... →Gyakran találkozhatunk olyan esettel, amikor egy külső forrásból letöltött, vagy egyéb helyről érkező Excel munkafüzet olyan dátum oszlopot tartalmaz, ami valójában szöveg. Amennyiben szükségünk van a dátum értékekre, át kell alakítanunk a szöveges dátumokat valódi dátumokra. Az Excel dátumkezelése nem témája ennek a cikknek, de a megértéshez […]
Tovább... →Érdekes feladat lehet, amikor egy adott dátumot megelőző vagy következő hétfőt kell megkeresni. És persze ebből következik, hogy hová eshet egy év első és utolsó hétfője, vagy egy adott év adott hónapjának mik a hétfői. Miért pont a hétfők? Mert ha a hétfő megvan, akkor bármely másik […]
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?
Feltételezve, hogy a vizsgált év január 1 és decmber 31 között tart, az adott dátumhoz kapcsolódó negyedévet a következő képletekkel lehet számítani:
Hagyományos módszer
A Hónap (MONTH) függvénnyel kivesszük a dátumból a hónapot,ehhez kettőt hozzáadunk, majd az így kapott eredményt hárommal osztjuk, és képezzük az eredmény egész részét.
Feltételezve, hogy az átalakítandó dátum az ‘A2’ cellában van, abból így számolhatunk negyedévet:
=INT((MONTH(A2)+2)/3)
Érdekes módszer
A Választ (CHOOSE) függvényről már egy másik anyagban említést tettem. Ebben az esetben is tökéletesen használható. Ha az adott dátum (A2) hónap értéke 1 vagy 2 vagy 3, akkor 1. negyedév, ha 4, 5, vagy 6, akkor második negyedév, stb. Tulajdonképpen pontosan úgy működik, mint ahogyan mi fejben számítjuk a negyedévet.
CHOOSE(MONTH(A2),1,1,1,2,2,2,3,3,3,4,4,4)
A választ fv. első paraméterében kiszámolja az ‘A2’-ben lévő dátum hónap értékét, majd az ezt követő argumentum listájából az annyiadikat választja.
Lássuk működés közben:
Negyedév számítása a VÁLASZT függvénnyel
Ha egy dátumhoz szeretnénk megmutatni az adott nap nevét is, akkor a következőket lehet tenni:
Triviális, de összetettebb megoldás
A dátumból képezzük a megfelelő függvénnyel a nap sorszámát, majd a kapott sorszám segítségével kikeressük egy segéd táblából a megfelelő napot.
Hét.Napja (WeekDay) függvény
Megadja, hogy az adott dátum a hét hányadik napjára esik
=Hét.Napja(dátum;2)
A második argumentum azt szabályozza, hogy a függvény melyik napot tekintse a hét első napjának. Ha a hétfőt szeretnénk (mint általában), akkor ez legyen mindig 2.
A következő képlet megadja, hogy az aktuális nap a hét hányadik napjára esik.
=weekday(today();2) =Hét.Napja(Ma();2)
Hogyan lesz ebből meg a nap neve?
Erre segédtáblákat használhatunk, amik lehetnek egy cellatartományban vagy akár elnevezett tömbkonstansokban, és ezekből az INDEX vagy az FKERES (Vlookup) függvényekel kereshetjük meg a nap nevét.
=INDEX(napok_neve_tartomany;het.napja(datum;2))
A nap beállítása cellaformázással
Ha ‘A1’-ben van a dátum, akkor ‘A2’-be írjuk a következő képletet:
=SZÖVEG(A1;"nnnn") =TEXT(A1;"nnnn")
Az „nnnn” kóddal azt kérjük, hogy írja ki a nap hosszú nevét. Angol rendszerben természetesen ennek „dddd”-nek kell lennie.
Egyéb érdekességek
Ha nem tudjuk, hogy mi az aktuális nyelvi verzió napot jelentő formázó karaktere, akkor a Visual Basic felületen a parancs ablakban futtassuk az alábbi kódot:
Application.International(xlDayCode)
Ha az ország kódot változtatgatjuk, akkor a kódnak megfelelően írja ki a nap nevét a következő módosítással:
=SZÖVEG(A1;"[$-040e]nnnn")
=TEXT(A1;"[$-040e]nnnn")
A szögletes zárójelben a dollár után az adott ország kódja van hexa formátumban.
A 040e ( decimálisan 1038 ) magyarország kódja.
Országkódok
http://msdn.microsoft.com/en-US/goglobal/bb964664.aspx
Mind ez működés közben látható a videóban.
Letölthető munkafüzet: Nap_nevenek_megjelenitese
Dátumhoz tartozó nap neve
Hogyan válaszolnánk meg Excel segítségével a következő kérdéseket:
- Két egész szám között hány 3-al osztható szám van?
- Két dátum között hány hétfő van?
- Két dátum között hány dátum esik hétvégére
Megoldások
Első
Nézzük az első feladatot. Hagyományos módon ezt úgy csinálnánk, hogy képezzük egy oszlopba a növekvő értékeket, és mellette egy segédoszlopban logikai értékként megadnánk az oszthatóságot, majd feltételes összegzéssel megkapjuk az eredményt. Az ábrán látható, hogy a „B” oszlopban a hárommal való oszthatóságot vizsgáljuk.
Ezután nincs több dolgunk, mint egy eredménycellába megszámolni a „B” oszlopban lévő TRUE értékek számát.
Mondjuk a „D2”-be írjuk be a képletet:
=COUNTIF(B2:B11;TRUE)
Második
A megoldást egyetlen képlettel is megadhatjuk, ami az egész tartományban vizsgálja az oszthatóságot, a visszakapott TRUE/FALSE értékeket 1-re és 0-ra alakítja, és összeadja az eredményt. Értelemszerűen csak ott lesz 1-es, ahol az érték osztható, és az 1-esek összegzése megadja, hogy hány szám van a tartományban, ami osztható 3-al.
=SUMPRODUCT(--(MOD(A2:A11;3)=0))
Tól-Ig tömbök képzése
Próbáljuk ki a következőket:
Egy cellába írjuk be a következő képletet, de mielőtt lezárnánk, nyomjuk meg az F9 billentyűt :
=SOR(INDIREKT("34:55"))
angol verzióban
=ROW(INDIRECT("34:55"))
A tartalom átváltozik erre
={34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55}
Tehát ez az összefüggés létrehozza a memóriában a kezdő és végérték közötti számok növekvő halmazát. (Ctrl+Z-vel vagy ESC-el lehet visszatérni.)
Ha a kezdő és a végérték nem konstans, akkor felvehetjük őket egy-egy cellába, és a képlet így alakul
=SOR(INDIREKT(A1 &":" & A2))
Látható, hogy az Indirekt függvénynek egy szövegre (stringre) van szüksége, ezt összefűzéssel állítjuk elő, belecsempészve a kettőspontot, mint a kezdő és a végérték elválasztójelét.
Ugyanez dátumokkal
A dátumokat ugyebár az Excel sorszámként kezeli. Egy egy szomszédos nap között pont egy egész különbség van. Két dátum közötti tömböt az előzőekhez hasonlóan képezhetjük, azaz a kezdő és végdátumot beírjuk két cellába, és az előző képletet beírva, majd F9-et nyomva ezt láthatjuk:
Tehát a 2013 január 1 és 5 közötti dátumszámok növekvő értékeit.
Mire jó ez az egész?
Ha visszatérünk a fenti feladatokhoz, akkor abban az esetben, ha nem akarjuk vagy nem lehetséges képezni a tömb elemeit külön a munkalapon, akkor ezt a megoldást alkalmazhatjuk a képletekben.
Hány hárommal osztható szám van két szám között (A1 és A2):
=SUMPRODUCT(--(MOD(ROW(INDIRECT(A1&":"&A2));3)=0))
Két dátum között hány hétfő van?
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$1 & ":" & $A$2));2)=1))
A WEEKDAY függvény magyar verzióban a HÉT.NAPJA. Azt mondja meg, hogy egy dátum a hét hányadik napjára esik. A képlet végén 1-el hasonlítjuk össze, mert a Hétfő a hét első napja.
Két dátum között hány dátum esik hétvégére
=SUMPRODUCT((WEEKDAY(ROW(INDIRECT($A$18&":"&$A$2));2)=6)+(WEEKDAY(ROW(INDIRECT($A$1&":"&$A$2));2)=7))
A hatodik nap a szombat, a hetedik nap pedig a vasárnap. A kettő közötti plusz jel a logikai VAGY műveletet jelenti.
Letölthető munkafüzet, Video
Ertekek-kozotti-szamolas-row-indirect
Növekvő számhalmazok
