Excel: dátumok konvertálása negyedekre
az Excel beépített funkciókkal rendelkezik az év, a hónap száma, a hónap neve, a hét száma, a hétköznap vagy a nap visszaküldésére bármely dátumot tartalmazó cellából. De nincs beépített módszer a negyedszám visszaadására. A negyedévek nehezebbek, mert a negyedév meghatározása a pénzügyi év befejezési dátumától függően változik.
naptári évekre
ha a pénzügyi év December 31-én ér véget, akkor használhat egy rövid képletet. (Az 1. ábra azokat az Excel-függvényeket mutatja, amelyekkel a dátumot évekre, hónapokra, hetekre vagy hétköznapokra konvertálhatja.) Először használja a = MONTH () függvényt a dátum hónapszámra konvertálásához. Osszuk el ezt az eredményt 3-mal. A januári dátumok 0,33, február 0,67, március 1,00, április 1,33 stb. A =ROUNDUP() függvény használatával 1 lesz januárra, februárra és márciusra; 2 áprilistól júniusig; és így tovább.
ha a negyedet “Q1” vagy “Qtr 1” néven szeretné megjeleníteni, összefűzze a megfelelő szöveget az eredmény előtt. A 2. ábrán az A7 cella a következő képletet használja: = “Q” & ROUNDUP(hónap (A2)/3,0)&”-“&év (A2) visszatérni ” Q1-2017.”
más pénzügyi évekre
a Január 31-ig tartó pénzügyi évek gyakoriak a kiskereskedelmi vállalatoknál. A kormányzati vállalkozók szeptember 30-i pénzügyi évet használhatnak. A pénzügyi negyedév kiszámításához először konvertálja a dátumot havi számra a =hónap () használatával. Ez az eredmény 12 egész számra korlátozódik 1 – től 12-ig.
használhatja a VLOOKUP képletet a hónap számának negyedéves számra konvertálásához. De a képlet =VLOOKUP(hónap (A2), {1,4;2,1;5,2;8,3;11,4},2) nehéz megjegyezni és beírni. A göndör zárójelek belsejében lévő tömb állandó 5 sorról 2 oszlopra keresési táblázatot tartalmaz.
ehelyett a = CHOOSE () függvény lehetővé tenné 12 negyedszám megadását, amelyek megfelelnek az 1-12 hónapoknak. Ha a pénzügyi év januárban ér véget ,akkor a = CHOOSE(MONTH (A2),4,1,1,1,2,2,2,3,3,3,4,4). A márciusban végződő pénzügyi év esetében használja a = CHOOSE(MONTH (A2),4,4,4,1,1,1,2,2,2,3,3,3).
ha a negyedet Q1-ként szeretné megjeleníteni az 1 helyett, akkor az ampersand segítségével összefűzheti a “Q” – t a CHOOSE függvény előtt. Az eredményeket a 2.ábra A13 és A20 cellájában mutatjuk be, a képleteket pedig a D13 és a D20 cellában.
pénzügyi év hozzáfűzése a negyedévhez
egyes vállalatoknál a pénzügyi évet az a naptári év határozza meg, amelyben az év 12.hónapja bekövetkezik. Például egy 2017 februárjától 2018 januárjáig tartó pénzügyi évben a Q1-2018-tól a Q4-2018-ig terjedő negyedévek lennének. Más vállalatoknál a pénzügyi évet úgy lehet nevezni, mint ” 17-18.”
abban az esetben, ha a pénzügyi év a záró hónap éve, használjon IF() függvényt annak tesztelésére, hogy a hónap száma kisebb-e, mint a kezdő hónap. Például, ha a pénzügyi év januárban zárul, a pénzügyi év =év(A2) lesz, ha a dátum február előtt van, és = év(A2)+1 februárban vagy később. Így a pénzügyi évnek a negyedévhez való hozzáadásához használhatja =IF (hónap(A2)<2,0,1) + év(A2).
egy márciusban végződő évre az <2 tesztet <4-re változtatná: =IF(hónap(A2)< 4,0,1)+év(A2). A szeptemberben végződő évre <10: =IF(hónap(A2)<10,0,1)+év(A2) tesztet végez.
a 2. ábra A21 cellája bemutatja, hogyan lehet összekapcsolni a pénzügyi negyedévet és az évet ezzel a hosszú képlettel: =”Q”&CHOOSE(MONTH(A17),4,1,1,1,2,2,2,3,3,3,4,4)&”-“&IF(MONTH(A17)<2,0,1)+YEAR(A17). Ez visszaadná a negyedévet a “Q4-2017.”
ha ehelyett 17-18-at kell mondania, akkor a hosszú képlet még hosszabb lesz. Csak azért, hogy megkapja a képlet évrészét, használnád: =IF(MONTH(A2)<4,MOD(YEAR(A2)-1,100)&”-“&TEXT(A2,”YY”),TEXT(A2,”YY”)&”-“&MOD(YEAR(A2)+1,100)).
visszatérve egy időszak helyett egy hónap
nemrég végzett szemináriumot IMA ons Nashville. Mivel a Tennessee állam kormányának pénzügyi éve júniusban ér véget,inkább egy hónap helyett időszakszámot használtak. Július időszak 1, December időszak 6, január időszak 7, stb. Ebben az esetben, sok munkatárs VLOOKUP táblát használt az adatok jobb oldalán. De használhatnák a = CHOOSE(MONTH (A2),7,8,9,10,11,12,1,2,3,4,5,6) ahhoz, hogy ugyanazt az eredményt.
a dátumok nem lehetnek szöveg
ezeknek a képleteknek a figyelmeztetése az, hogy a dátumokat valós dátumként tárolja, nem pedig szövegként. Egy gyors módja annak, hogy ellenőrizze: nyomja meg a Ctrl és a Grave ékezet gombot ( ‘ ) váltani show képletek módban. A valós dátumok sorozatszámra, például 42783-ra konvertálódnak. Ha továbbra is megjelenik egy szöveges dátum, a képletek nem fognak működni. Nyomja meg ismét a Ctrl + ` billentyűkombinációt a képletek megjelenítéséhez.
nemrég futottam át egy munkalapot, amelyben a menedzser azt akarta, hogy a dátumok megjelenjenek 1st Feb 2017, 2nd Feb 2017, 3rd Feb 2017, 4th Feb 2017. A dátum utáni utótagok (“st”,” nd”,” rd “és” th”) a kardinális számot sorszámmá alakítják. Míg az Excel kitöltő fogantyúja lehetővé teszi a bíboros számok oszlopának egyszerű létrehozását, a cikkben szereplő képletek sokkal összetettebbé válnak. A 41 karakteres képlet 2438 karakterre nőtt a szöveges dátumok kezelése érdekében (lásd a 3.ábrát).
