Excel: päivämäärien muuntaminen neljänneksiksi
Excelissä on sisäänrakennetut toiminnot vuoden, kuukauden numeron, kuukauden nimen, viikon numeron, arkipäivän tai päivän palauttamiseen mistä tahansa solusta, joka sisältää päivämäärän. Mutta ei ole sisäänrakennettua tapaa palauttaa neljännesnumeroa. Vuosineljännekset ovat vaikeampia, koska vuosineljänneksen määritelmä vaihtelee tilikauden päättymispäivän mukaan.
kalenterivuosina
jos tilikausi päättyy 31. Joulukuuta, voit käyttää lyhyttä kaavaa. (Kuva 1 näyttää Excel-toiminnot, joiden avulla voit muuntaa päivämäärän vuosiksi, kuukausiksi, viikoiksi tai arkipäiviksi.) Ensimmäinen, käytä = kuukausi () funktio muuntaa päivämäärä kuukauden numero. Jaa tulos 3: lla. Päivämäärät tammikuussa palaavat 0.33, helmikuussa 0.67, maaliskuussa 1.00, huhtikuussa 1.33 ja niin edelleen. Käyttämällä = ROUNDUP() funktio, sinulla on 1 tammi -, helmikuu-ja Maaliskuu; 2 huhtikuusta kesäkuuhun; ja niin edelleen.
jos haluat näyttää vuosineljänneksen muodossa ” Q1 ”tai” Qtr 1″, Yhdistä sopiva teksti ennen tulosta. Kuviossa 2 solu A7 käyttää kaavaa = ” Q ” &ROUNDUP(kuukausi (A2)/3,0)&”-”&vuosi (A2) palata ”Q1-2017.”
muina verovuosina
31. tammikuuta päättyvät verovuodet ovat yleisiä vähittäiskauppayrityksissä. Valtion urakoitsijat saattavat käyttää tilikauden lopussa syyskuun 30. Jos haluat laskea julkisen talouden vuosineljänneksen, muuntaa päivämäärä ensin kuukauden numeroksi käyttämällä =kuukausi (). Tämä tulos on rajoitettu 12 kokonaislukua 1-12.
vlookupin kaavalla voi muuntaa kuukauden numeron neljännesnumeroksi. Mutta kaava =VLOOKUP(kuukausi (A2), {1,4;2,1;5,2;8,3;11,4},2) on vaikea muistaa ja kirjoittaa. Array vakio sisällä kihara henkselit pitää 5-rivi 2-sarakkeen lookup taulukko.
sen sijaan =CHOOSE () – funktion avulla voit määrittää 12 neljännesnumeroa, jotka vastaavat kuukausia 1-12. Jos tilivuosi päättyy tammikuussa, käyttäisit =Valitse(kuukausi (A2),4,1,1,1,2,2,2,3,3,3,4,4). Maaliskuussa päättyvällä tilikaudella käyttö =Valitse (kuukausi(A2),4,4,4,1,1,1,2,2,2,3,3,3).
jos haluat mieluummin näyttää kvartaalin Q1: nä 1: n sijasta, voit käyttää ampeereja ja yhdistää ”Q”: n ennen VALINTAFUNKTIOTA. Tulokset esitetään kuvan 2 soluissa A13 ja A20, kaavat D13 ja D20.
kun verovuosi liitetään vuosineljännekseen
, joissakin yrityksissä verovuosi määräytyy sen kalenterivuoden mukaan, jona vuoden 12.kuukausi tapahtuu. Esimerkiksi tilikaudella, joka ulottuu helmikuusta 2017 tammikuuhun 2018, olisi neljänneksiä nimeltään Q1-2018 kautta Q4-2018. Muissa yhtiöissä tilivuodeksi saatetaan kutsua jotain ” 17-18.”
jos tilivuosi on päättyvän kuukauden vuosi, Käytä If () – funktiota testataksesi, onko kuukauden numero pienempi kuin alkava kuukausi. Esimerkiksi kun verovuosi päättyy tammikuussa, verovuosi on = vuosi (A2), jos päivämäärä on ennen helmikuuta ja =vuosi(A2)+1 helmikuussa ja myöhemmin. Näin ollen tilikauden ja vuosineljänneksen sisällyttämiseksi voit käyttää =IF(kuukausi (A2)<2,0,1) + vuosi(A2).
maaliskuussa päättyvälle vuodelle muuttaisit <2-testin <4: =IF(kuukausi(A2)<4,0,1)+vuosi(A2). Syyskuussa päättyvän vuoden ajan testattaisiin <10: =IF(kuukausi(A2)<10,0,1)+vuosi(A2).
Kuvan 2 solussa A21 esitetään, miten julkisen talouden vuosineljännes ja vuosi Yhdistetään käyttäen tätä pitkää kaavaa: =”Q”&CHOOSE(MONTH(A17),4,1,1,1,2,2,2,3,3,3,4,4)&”-”&IF(MONTH(A17)<2,0,1)+YEAR(A17). Se palauttaisi neljänneksen muodossa ” Q4-2017.”
jos sen sijaan tarvitsee sanoa 17-18, pitkä kaava pitenee entisestään. Vain saada vuoden osa kaavan, käyttäisit: =IF(MONTH(A2)<4,MOD(YEAR(A2)-1,100)&”-”&TEXT(A2,”YY”),TEXT(A2,”YY”)&”-”&MOD(YEAR(A2)+1,100)).
palautan jakson kuukauden sijaan
johdin äskettäin ima® – seminaarin Nashvillessä. Koska Tennesseen osavaltion hallitus on tilivuosi päättyy kesäkuussa, he mieluummin ilmoittaa käyttäen jakson numero sijasta kuukausi. Heinäkuu on ajanjakso 1, joulukuu on ajanjakso 6, tammikuu on ajanjakso 7, ja niin edelleen. Tässä tapauksessa monet työkaverit käyttivät vlookup-taulukkoa pois datan oikealle puolelle. Mutta he myös voisivat käyttää =valita(kuukausi (A2),7,8,9,10,11,12,1,2,3,4,5,6) saman tuloksen saavuttamiseksi.
päivämäärät eivät voi olla tekstiä
kaikkien näiden kaavojen varoitus on, että tallennat päivämääräsi oikeina päivämäärinä etkä tekstinä. Nopea tapa tarkistaa: paina Ctrl ja Grave Accent key (`) siirtyäksesi Näytä kaavat-tilaan. Todelliset päivämäärät muuttuvat sarjanumeroksi, kuten 42783. Jos vielä näet tekstipäivän, kaavat eivät toimi. Paina Ctrl + ’ uudelleen poistuaksesi Näytä kaavat.
juoksin äskettäin koko laskentataulukon, jossa johtaja halusi päivämäärät näyttämään 1. helmikuuta 2017, 2.helmikuuta 2017, 3. helmikuuta 2017, 4. helmikuuta 2017. Päivämäärän jälkeiset jälkiliitteet (”st”,” nd”,” rd ”ja” th”) muuttavat kardinaaliluvun ordinaaliluvuksi. Vaikka Excel-täyttökahvan avulla voit luoda kardinaalinumeroiden sarakkeen helposti, tämän artikkelin kaavoista tulee paljon monimutkaisempia. 41 merkin kaava kasvoi 2 438 merkkiin tekstipäivien käsittelemiseksi (KS.kuva 3).
