Excel: conversia datelor în trimestre
Excel are funcții încorporate pentru returnarea anului, numărului lunii, numelui lunii, numărului săptămânii, zilei săptămânii sau zilei din orice celulă care conține o dată. Dar nu există o modalitate încorporată de a returna un sfert de număr. Trimestrele sunt mai dificile, deoarece definiția unui sfert variază în funcție de data de încheiere a anului fiscal.
pentru anii calendaristici
dacă exercițiul dvs. financiar se încheie la 31 decembrie, atunci puteți utiliza o formulă scurtă. (Figura 1 prezintă funcțiile Excel pe care le puteți utiliza pentru a converti o dată în ani, luni, săptămâni sau săptămâni.) Mai întâi, utilizați funcția =MONTH() pentru a converti data la un număr de lună. Împărțiți rezultatul la 3. Datele din ianuarie vor reveni 0.33, 0.67 februarie, 1.00 Martie, 1.33 aprilie și așa mai departe. Prin utilizarea funcției = ROUNDUP (), veți avea 1 Pentru ianuarie, februarie și martie; 2 Pentru aprilie până în iunie; și așa mai departe.
pentru a afișa trimestrul ca ” Q1 „sau” Qtr 1″, concatenați textul corespunzător înainte de rezultat. În Figura 2, celula A7 folosește o formulă de = ” Q ” & ROUNDUP(luna(A2)/3,0)&”-„&anul (A2) pentru a reveni „Q1-2017.”
pentru alți ani fiscali
anii fiscali care se încheie la 31 ianuarie sunt obișnuiți în companiile cu amănuntul. Contractorii guvernamentali ar putea folosi un an fiscal la sfârșitul lunii septembrie 30. Pentru a calcula un trimestru fiscal, convertiți mai întâi data la un număr de lună folosind =lună(). Acest rezultat este limitat la 12 numere întregi de la 1 la 12.
puteți utiliza o formulă VLOOKUP pentru a converti numărul lunii în numărul trimestrului. Dar formula = VLOOKUP(luna (A2),{1,4;2,1;5,2;8,3;11,4},2) este dificil de reținut și de tip. Constanta matricei din interiorul bretelelor buclate deține un tabel de căutare cu 5 rânduri pe 2 coloane.
în schimb, funcția =CHOOSE() vă permite să specificați 12 numere trimestriale care corespund lunilor 1 până la 12. Dacă anul dvs. fiscal se încheie în ianuarie, utilizați = alegeți (luna(A2),4,1,1,1,2,2,2,3,3,3,4,4). Pentru un an fiscal care se încheie în martie, utilizați = alegeți (luna(A2),4,4,4,1,1,1,2,2,2,3,3,3).
dacă preferați să afișați trimestrul ca Q1 în loc de 1, puteți utiliza ampersand pentru a concatena un „Q” înainte de funcția de alegere. Rezultatele sunt prezentate în celulele A13 și A20 din Figura 2, cu formulele prezentate în D13 și D20.
adăugarea unui an FISCAL la trimestrul
în unele companii, anul fiscal este determinat de anul calendaristic în care are loc a 12-a lună a anului. De exemplu, un an fiscal care se desfășoară din februarie 2017 până în ianuarie 2018 ar avea trimestre numite Q1-2018 până în Q4-2018. În alte companii, anul fiscal ar putea fi denumit ceva de genul „17-18.”
pentru cazul în care anul fiscal este anul lunii care se încheie, utilizați o funcție IF() pentru a testa dacă numărul lunii este mai mic decât Luna care începe. De exemplu, cu un an fiscal care se încheie în ianuarie, anul fiscal va fi =An(A2) dacă Data este înainte de februarie și =an(A2)+1 în februarie și mai târziu. Astfel, pentru a include anul fiscal cu trimestrul, puteți utiliza = IF(luna(A2)<2,0,1)+anul (A2).
pentru un an care se încheie în martie, ați schimba testul <2 pentru a fi <4: =IF(luna(A2)< 4,0,1)+an(A2). Pentru un an care se încheie în septembrie, ați testa <10: =IF(luna(A2)< 10,0,1)+an(A2).
celula A21 din Figura 2 arată cum să concatenăm trimestrul fiscal și Anul împreună folosind această formulă lungă: =”Q”&CHOOSE(MONTH(A17),4,1,1,1,2,2,2,3,3,3,4,4)&”-„&IF(MONTH(A17)<2,0,1)+YEAR(A17). Aceasta ar returna trimestrul într-un format de „Q4-2017.”
dacă, în schimb, aveți nevoie să spuneți 17-18, formula lungă devine și mai lungă. Doar pentru a obține porțiunea de an a formulei, ați folosi: =IF(MONTH(A2)<4,MOD(YEAR(A2)-1,100)&”-„&TEXT(A2,”YY”),TEXT(A2,”YY”)&”-„&MOD(YEAR(A2)+1,100)).
revenind o perioadă în loc de o lună
am efectuat recent un seminar pentru Ima la Nashville. Deoarece guvernul statului Tennessee are un an fiscal care se încheie în iunie, au preferat să raporteze folosind un număr de perioadă în loc de o lună. Iulie este perioada 1, decembrie este perioada 6, ianuarie este perioada 7 și așa mai departe. În acest caz, o mulțime de colegi au folosit un tabel VLOOKUP în partea dreaptă a datelor. Dar, de asemenea, ar putea folosi = alege (luna(A2),7,8,9,10,11,12,1,2,3,4,5,6) pentru a obține același rezultat.
datele nu pot fi TEXT
avertismentul pentru toate aceste formule este că vă stocați datele ca date reale și nu ca text. O modalitate rapidă de a verifica: apăsați Ctrl și tasta grav Accent (`) pentru a comuta în modul arată formule. Datele reale se vor converti la un număr de serie, cum ar fi 42783. Dacă vedeți în continuare o dată text, formulele nu vor funcționa. Apăsați din nou Ctrl+` pentru a ieși din Afișare formule.
recent am dat peste o foaie de lucru în care managerul dorea ca datele să fie afișate ca 1st Feb 2017, 2nd Feb 2017, 3rd Feb 2017, 4th Feb 2017. Sufixele după dată („st”,” nd”,” rd „și” th”) convertesc un număr cardinal într-un număr ordinal. În timp ce mânerul de umplere Excel vă va permite să creați cu ușurință o coloană de numere cardinale, formulele din acest articol devin mult mai complexe. O formulă de 41 de caractere a crescut în 2.438 de caractere pentru a gestiona datele textului (a se vedea Figura 3).
