trükkök

Az Excel tippek és trükkök sorozatában válaszokat adunk a résztvevők Excel-hez kapcsolódó kérdéseire, amelyek a tanfolyamaink tantervében nem gyakoriak. Létrehoztuk a válaszok mozaikját, amely segíthet az Excel és az Ön munkájának…
Ez az ötödik rész, amelyben elsősorban az idő függvényeivel foglalkozunk, hogyan számoljuk ki az Excelben a munkaviszony korát vagy időtartamát stb.

Szembesült már azzal, hogy az ember életkorát év, hónap, vagy akár egy nap pontossággal kell kiszámítani? Vagy azzal a feladattal, hogy kiderüljön, a szülőknek kell-e magával vinni az iskolalátogatásról szóló igazolást, mert gyermeke betöltötte a 18. életévét? Hogyan lehet kiszámítani a vállalaton belüli foglalkoztatás időtartamát? Hány nap kell ahhoz, hogy a hajó egy ázsiai kikötőből Európába utazzon?

Az Excelben ezt könnyen és nehezen, nagyon pontosan és megközelítőleg… gyorsan és lassan tudjuk megtenni 🙂

Dátumfüggvények az Excelben

Közel ötszáz funkciója között az Excelnek számos olyan funkciója van, amelyek segítenek kiszámítani a dátumokat és időpontokat.
A képen a tetején áttekintést láthat az alapvető leírással - NAP, HÓNAP, ÉV, HÉTNUM és ISOHÉTNY.
Az első három függvény a dátumtól kezdve adja vissza a nap, a hónap vagy az év értékét. A WEEKNUM eredménye a hivatkozott cellában a dátum hetének sorszáma. Az ISOWEEKNUM a hivatkozott cellában visszaadja a dátum hetének sorszámát is, de a WEEKNUM-tal ellentétben az eredmény a nemzetközi szabvány szerinti hét száma. A gyakorlatban azonban a másik két funkciót inkább használják.

Egyébként - tudtad, hogy a hetek száma változik a világon?
Vannak olyan országok, amelyek az év első hetét január 1-jének tartják. Ugyanakkor vannak olyan országok, amelyek az év első hetét hétfőnek, keddnek, szerdának vagy csütörtöknek tartják. Vagy amikor valamikor januárban velük ünneplik az újévet ... Tehát ami az év első hete?
A Nemzetközi Szabványügyi Szervezet az 1980-as évek végén vezette be az ISO 8601-et, az ISO-év első hete a gregorián év első csütörtökje, január 4-e.

Mire jó?
Például… az exportőr értesíti a külföldi kereskedelmi partnert, hogy az áruk készen állnak a szállításra keddtől, a 23. héten. Teherautót, hajót, repülőgépet rendel az áru raktárába szállítására. És semmi. A teherautó vár, a raktárban nem találják a szállítmányt. Telefonálás, káromkodás, idegesség. Egy órás keresés után kiderül, hogy az információk helyesek, de az új munkavállaló a hét helyi megjelölését használta. Eszébe sem jutott, hogy egy külföldi hetet másként jelölhetnének meg (a szerző saját tapasztalata az exportőrrel). Tud.

Az életkor kiszámítása az Excel alkalmazásban

Nézzük meg, hogyan lehet kiszámítani a dátumot az Excel-ben konkrét példákon.
Arra vagyunk kíváncsiak - főleg a különféle munkahelyi adminisztratív feladatokra -, hogy egy embernek (munkavállalónak, gyermeknek, stb.) Hány éve van a jelenlegi "ma". Érdekel, hogyan lehet kiszámítani az életkort az Excelben? Dolgozott évek száma?
Nem nehéz, ha tudjuk a megfelelő funkciókat, vagy tudjuk, hogy az Excel hogyan működik a dátumokkal.

Tegyük fel, hogy azokat az embereket, akiknek meg akarjuk tudni a korosztályt, a táblázatban elrendeztük, mint a képen.

1. módszer

Az Excel lehetővé teszi számunkra, hogy megtudjuk a két dátum közötti egyszerű különbséget hány nap telt el a két dátum között. Ezért az aktuális dátumot ma írhatnánk a szomszédos oszlopba a születési dátum oszlop mellé (most helytelenül jelöltük kornak). És a következő oszlopba írunk képlet, amellyel kiszámítjuk a két dátummal rendelkező cellák közötti különbséget melletti sorban. A kezdők így csinálják, az Excel számlálásának legelején.
Az eredmény helyes, de…

(Ha a napok száma helyett egy nem beszélő dátum jelenik meg az oszlopban, ne essen kétségbe. A probléma csak cella formátumban van. Állítsa a kijelölést a Szám panel panel Kezdőlapjának felső ablakában Általános értékre.)

A kapott eredménynek egyetlen hátránya van - csak 2020.20.7-én lesz érvényes. Egy kissé fejlettebb "kiválóság" ezért olyan képletet fog figyelembe venni, amely megkönnyíti a számítást.

Annak érdekében, hogy ne csak egy "ma" dátum legyen kötve, amelyet az előző számítási módszer a D oszlopba írtunk be, a képletben egy másik függvényt fogunk használni. Dátum függvény TODAY az Excel programban mindig az aktuális dátumot használja a számítógép naptárából. Tehát egyáltalán nem kell beírnunk a táblázatba az aktuális dátumot. A számítás minden alkalommal megtörténik, amikor megnyitjuk a fájlt, az új aktuális "mai" dátumig. Pedig jó?

A függvény írás közben kissé furcsán néz ki, mert érvetlen - zárójelben nincs argumentuma. A vele látható formula így néz ki egy cellában:

A funkcióba nem írunk semmit, nincs hely. Csak a bal és a jobb zárójel.
MA megmutatja az eredményt a cellában az aktuális dátum formájában.

Példánkban értékelni fogjuk a függvényt, mert használata után a megoldás így néz ki.
A dátum helyett írja be a különbségképletet a D (D7) oszlop első cellájába:

és másolja át az alábbi cellákba, duplán kattintva a cella sarkában lévő négyzet alakú fogantyúra.
(Az előző eljáráshoz hasonlóan, ha a napok száma helyett egy nem beszélő dátum jelenik meg az oszlopban, akkor a probléma csak cella formátumban van. A kiválasztott formátumot a Dátum beállítása Általános helyett a Kezdőlap felső mezőjében állítsa be. )

Így kaptuk meg a munkavállaló életének napszámát. Ha évekre vágyunk, akkor is évekre kell váltanunk az eredményt. Hogyan?
Mivel minden negyedik év átjárható, elosztjuk az elért napok számát 365,25-tel. Megkapjuk az összes alkalmazott meglehetősen pontos életkorát.
Az első cellában lévő teljes képlet ekkor így néz ki:

Kattintson duplán a fogantyúra, hogy átmásolja az egész oszlopba.
Két dátum közötti különbség években így tudtuk meg a TODAY funkcióval rendelkező képleten keresztül.
Kiszámítja a dátumokkal rendelkező két sejt közötti különbséget.

Csak az egész évek eredményét tudjuk megjeleníteni az INT (egész) függvény használatával, amely csak az egész rész megjelenítését biztosítja. Így néz ki:

Jegyzet:
Abban az esetben, ha egy adott nap életkorát kell kiszámítanunk, például 2019.12.31-ig, a MA helyett a DATE (2019; 12; 31) formában egy adott dátumot írunk az űrlapba.

2. módszer

A számításhoz egy másik Excel dátumfüggvényt fogunk használni - YEARFRAC. A YEARFRAC függvénynek két kötelező argumentuma van (kezdő és befejező dátum) és egy választható argumentum a releváns pénzügyi műveletnek megfelelően (nem általánosan használt).

Az oldalsó oszlopban elvégezzük a számítást, hogy összehasonlíthassuk az eredményeket. Az első cella így fog kinézni:

Kattintson duplán a négyzet alakú fogantyúra, hogy átmásolja az oszlop többi részébe.
A két dátum közötti különbséget a YEARFRAC függvény segítségével találtuk meg, amellyel kiszámoljuk két cella dátummal való különbségét.

A két Age oszlop összehasonlításakor különbséget látunk. A 365,25-tel való osztással és osztással számított életkor nem teljesen pontos. A YEARFRAC függvény által kapott eredmény pontos a helyesen beírt beviteli értékekkel (kivételes esetekben a 30/360 banki éveket 29,2-es kezdési dátummal számolva. Helytelen eredményeket adhat).
Ha csak az évek száma érdekel, akkor mindkét számítás elegendő.

Ha ki kell számolnunk egy adott nap dátumkülönbségét, például 2019.12.31-ig, még akkor is, ha a YEARFRAC függvényt használjuk TODAY () helyett, akkor egy konkrét dátumot DATE (2019; 12; 31) formában írunk. ) benne.

(Az utolsó sorban láthatóak a képletek, amelyeket a felette lévő oszlop kiemelt cellájának celláiban használnak.)

A munkaviszony időtartamának kiszámítása

Az előző esethez hasonlóan Excelben számoljuk ki a munkaviszony időtartamát.
Vessen egy pillantást az asztalra.
A születési dátum helyett beírjuk vagy más táblázatból másoljuk a munkavállalók munkába állásának dátumát. A képlet létrehozásának eljárása és logikája hasonló.

A számítást a G oszlop YEARFRAC függvényével végezzük.
Az első cella így fog kinézni:

Kattintson duplán a négyzet fogantyújára a cella sarkában, hogy a képletet az oszlop többi részébe másolja.

Ha csak évekre van szüksége, vagy nem tetszik a tizedesjegyek száma, a Szám panel (Számformátum) Kezdőlap nyíl eszközeivel állíthatja be. Összeadja és kivonja a megjelenített tizedesjegyek számát. Vagy használhatjuk a fent említett INT függvényt közvetlenül a képletben az alábbiak szerint:

Abban az esetben, ha meg kell számolnunk egy adott napon ledolgozott évek számát, például 2019.12.31-én, a TODAY () helyett a függvény belsejébe DATE (2019; 12; 31) formába írunk egy adott dátumot, így a formula így fog kinézni:

Természetesen kiszámíthatjuk a munkaviszony időtartamát is a fent leírt első módon - két dátum levonásával és a különbség elosztásával 365,25-tel. Az eredmény kevésbé lesz haj pontos, mint a YEARFRAC funkció.

Az Excel műértőinek

Az életkor vagy az időszak kiszámítása az eredmény megjelenítésének követelményeitől függően sokféle lehet.
Az eddigi évek számának (túlélések, ledolgozott órák stb.) Egyszerű megjelenítésének képlete a következő lehet:

ahol a C3 cella lesz az a dátum, amelytől számoljuk az éveket, azaz. születési dátum, a munkaviszony megkezdésének napja,…
Az évek, hónapok és napok kiszámításának pontosabb képlete így nézhet ki:

Az alábbi képen számos számítási módszer, köztük az év, év, év,… akár az angol nyelvű szlovák szavak használatával kapcsolatos problémák is láthatók az alábbi képen. Ha valaki ezeket a képleteket tesztelni szeretné, vagy a gyakorlatban szeretné használni, akkor a cikk végén található gombra kattintva letöltheti számítógépére az Excel táblázatfájlt.

A képlet kiszámításakor a kevésbé ismert DATEIF Excel dátumfüggvényt használtam.
Részletesebb leírását 2019-től az Excel súgójában találja (F1 billentyű).

Következtetés

Amint az az utolsó táblázatból kiderül, a dátum kiszámításakor több megoldás is létezik.
Gyakorlati célokra ajánlom - válassza ki azt, amelyikhez a legjobban ért. Annak érdekében, hogy bármikor könnyedén értelmezhesse vagy beállíthassa.

Ugyanezeket az elveket és képleteket lehet alkalmazni a gyakorlatban, például annak eldöntésére, hogy a szülőnek be kell-e nyújtania a munkáltatónak az iskolalátogatásról szóló igazolást (a képletben a születési dátumot és a MA-t vagy más gyakorlati feladatokat használunk. Az Excel funkcióival megkönnyíti az életünket

Szeretne többet megtudni az Excel és az időfunkciók használatáról? Gyere el egy Excel tanfolyamra, tanulj meg és próbálj ki példákat. Két nap elteltével, amelyet az Excel I vagy az Excel II tanfolyamon töltött, többet megtudhat a táblázattal való munkavégzésről, megismerheti a billentyűparancsokat, a táblázattípusok megkülönböztetésének módját, és mikor kell használni, megtanulja a képletek létrehozását, a függvények használatát,.
Tudni fogja, hogyan kell kiszámítani a dátumot az Excel II után, az időfüggvényeket részletesebben átvesszük az Excel III-n 🙂