Alkuperäinen kirjoittajaAnalyysi on poistettupäällä15. tammikuuta 2013
Huomaan usein saavani tietoja yhdestä tai toisesta järjestelmästä (tai useista järjestelmistä ja sitten yhdistävän niitä) "päivittäisinä" tiedoina - sarjana päiviä koskevia mittareita. Joskus työskentelen näiden tietojen kanssa päivittäisellä tasolla, mutta usein haluan kerätä tiedot viikoittain, kuukausittain tai jollekin muulle ajanjaksolle.
Jos esimerkiksi haluan tarkastella tietoja viikoittain, käytän joko viikon viimeistä päivää tai viikon ensimmäistä päivää ja käytän sitten uudessa sarakkeessa olevaa kaavaa, joka muuntaa jokaisen todellisen päivän "viikoksi". johon se putoaa:
Yllä olevassa esimerkissä 15.1.2013 on tiistai, joka osuu viikkoon, joka päättyy lauantaina 19.1.2013. Sama pätee keskiviikkoon (16.1.), torstaihin (17.1.), perjantaihin (18.1.) ja lauantaihin (19.1.). Heti kun pääsen 20.1.2013 (sunnuntai), minulla on uusi viikko - viikko, joka päättyy 26.1.2013. Käydä järkeen?
Lisäämällä tämän sarakkeen voin luoda pivot-taulukon, joka voi helposti luoda viikoittaisia tietoja kaikista laskentataulukon mittareista.
Tämä lähestymistapa toimii useilla eri tavoilla, joilla saatat joutua koottamaan päivittäisiä tietoja, joten ajattelin, että postaus, jossa käydään läpi joitain yleisimmistä tavoista ja kunkin muuntamisen kaava, oli kunnossa. Olen laittanut kaikki tämän viestin esimerkit aladattava laskentataulukkojoita voit tarkistaa ja leikkiä.
Viikonpäivä
WEEKDAY()-funktio palauttaa luvun — 1, 2, 3, 4, 5, 6 tai 7. Mutta entä jos todella haluat viikonpäivän englanninkielisenä?
Voit käyttää CHOOSE()-funktiota ja kovakoodiarvoja. Tai voit tehdä erillisen taulukon, joka yhdistää numeron kullekin viikonpäivälle, ja käyttää VLOOKUP-toimintoa arvojen täyttämiseen. En aio keskustella kumpaakaan näistä lähestymistavoista, koska pidän parempana tapana käyttää TEKSTI()-funktiota.
Täysin kirjoitetulle viikonpäivälle ("$A3" on solu, jossa on 15.1.2013 – vedät vain tämän kaavan alas tai, jos käytät Excel-taulukkoa, se täyttää automaattisesti):
=TEKSTI($A3"dddd")
3-kirjaiminen arkipäivä:
=TEKSTI($A3"ddd")
Helppoa, eikö?
Huomautus: Jos haluat vain päivämäärän olevannäytetäänViikonpäivänä et tarvitse kaavaa ollenkaan – voit yksinkertaisesti muuttaa solujen muotoilun mukautettuun muotoon "dddd" (koko viikonpäivälle) tai "ddd" (3-kirjaiminen arkipäivä). Jos teet niin,näyttötiedoista tulee arkipäivänä, mutta taustallaarvoon edelleen todellinen päivämäärä. Tämä kaava tekee arvon itse asiassa viikonpäivän. Riippuen tarpeistasi, yksi tai toinen lähestymistapa on järkevämpi.
Muunna muotoon "Week Of"
Esimerkki, jolla aloitin tämän viestin, on muuttaa jokainen päivä viikon päättyväksi tai viikon alkavaksi päiväksi. Voit tehdä tämän käyttämällä WEEKDAY()-funktiota. Helpoin tapa ymmärtää, miten tämä toimii, on kirjoittaa (tai laittaa Exceliin) sarja päivämääriä ja kirjoittaa sitten numerot 1–7, kun lasket päivämääriä. Mitä pidemmälle viikkoon mennään, sitä suurempi WEEKDAY()-arvo on. Joten jos vähennät WEEKDAY()-arvon todellisesta päivämäärästä, saat saman arvon 7 päivää peräkkäin, jolloin arvo "hyppää" seitsemän päivää. Onko järkeä (se on hämmentävää… kunnes ei ole)?
Jos haluat siis muuntaa päivämäärän sen viikon lauantaiksi, johon päivämäärä osuu, käytä tätä kaavaa ("+7" vain pitää muunnetun arvon olemasta sen viikon lauantaiEdellinenviikko):
=$A3-VIIKKOPÄIVÄ($A3)+7
Se on sama idea, jos haluat käyttää viikon ensimmäistä päivää, jolloin viikko on määritelty alkavaksi sunnuntaina:
=$A3-VIIKKOPÄIVÄ($A3)+1
Ilmeisesti voit käyttää tätä peruskaavaa mihin tahansa "viikon" kriteeriin, jonka haluat. Sinun täytyy vain joko ajatella sitä todella lujasti… tai kokeilla, kunnes se tekee mitä haluat.
Muunna kahden viikon päiväksi
Joskus yritys toimii kahden viikon välein jollakin tavalla. Esimerkiksi monet yritykset maksavat työntekijöilleen palkkaa kahden viikon välein. WEEKDAY() ei toimi tähän, koska se ei kerro, mihin kahdesta viikosta päivä kuuluu.
Tässä tapauksessa käytän MOD(). Tämä funktio on pohjimmiltaan "jäännös"-funktio, ja sen pääasiallinen käyttötarkoitus on laskea jäännös, kun yksi luku jaetaan toisella (esimerkiksi "=MOD(14,4)" palauttaa "2", koska kun jaat 14 x 4, saat loppuosan 2).
Excel-päivämäärät ovat konepellin alla vain numeroita. Sinun ei todellakaan tarvitse tietää tarkalleenmitänumero päivämäärä on (vaikka voit muuttaa solun muotoilun "Numero", kun päivämäärä näytetään ja näet numeron). Mutta jos ajattelet sitä, jos jaat päivämäärän 14:llä, sen jäännös on välillä 1 ja 13. Oletetaan, että jäännös on "2". Joten, mikä on loput, jos jaatSeuraavapäivä klo 14 mennessä? Siitä tulee "3". Ja niin edelleen, kunnes pääset 13:aan, jolloin seuraavan päivän, jos se jaetaan 14:llä, jäännös on 0. Hmmm. Näyttää siltä, että olemme luoneet uudelleen yllä käytetyn WEEKDAY()-funktion… mutta 14 päivän pituisella jaksolla 7 päivän sijasta, eikö niin? Tarkalleen!
Joten jos halusimme muuntaa päivämäärän lauantaiksi kahden viikon jakson lopussa, se olisi toinen näistä kaavoista (riippuen siitä, kumpi lauantai on raja-arvo ja kumpi jakson puoliväli):
=$A3-MOD($A3-1,14)+13
tai
=$A3-MOD($A3-8,14)+13
Jos haluat käyttää jakson alkua siten, että viikko alkaa sunnuntaina, se olisi toinen seuraavista kaavoista:
=$A3-MOD($A3-1,14)
tai
=$A3-MOD($A3-8,14)
Vaatii jälleen kokeilua, jos haluat sopeutua muihin päivämääriin, mutta "14" ei muutu niin kauan kuin työskentelet kahden viikon välein.
Muunna kahden kuukauden päivämääriksi
Joskus (jälleen yrityksen palkkakaudet ovat hyvä esimerkki) kahden viikon kalenterin sijaan haluat käyttää kahden kuukauden kalenteria – jokainen päivämäärä 1. päivästä 14. päivään tulee muuntaa kuukauden ensimmäiseksi päiväksi. , ja jokainen päivä 15. päivästä kuun loppuun tulee koodata 15. päiväksi. Tätä varten käytämme DATE()-funktiota IF()-käskyn kanssa päivän arvolle:
=PÄIVÄYS(Vuosi($A3),KUUKAUSI($A3),JOS(PÄIVÄ($A3)<15,1,15))
Tiedämme, että vuosi on muunnettavan päivämäärän YEAR(), ja tiedämme, että kuukausi on muunnettavan päivämäärän KUUKAUSI(). Mutta meidän on tarkasteltava kuukauden päivää ja tarkistettava, onko se pienempi kuin 15. Jos on, palautamme päivän arvon "1" ja muussa tapauksessa palautamme päivän arvon "15".
Muunna kuukausipäivämääräksi
Kuukausittainen on melkein yhtä yleistä, ellei enemmänkin, kuin viikoittain. Muuntaminen kuukauden ensimmäiseksi päiväksi on yksinkertaista DATE()-funktion käyttöä. Vedämme vuoden käyttämällä YEAR()-funktiota muunnospäivänä, kuukauden käyttämällä KUUKAUSI()-funktiota muunnospäivänä, ja sitten yksinkertaisesti koodaamme "päivän" muotoon "1:".
=PÄIVÄYS(Vuosi($A3),KUUKAUSI($A3),1)
Mutta entä jos haluamme käyttääkestääkuukauden päivä? Emme voi koodata "päivä"-arvoa, koska se päivä voi olla 28, 29 (karkausvuosi), 30 tai 31. Kiroa sinua, gregoriaaninen kalenteri!!!
No, itse asiassa, tämäkään ei ole niin monimutkaista. Miksi? Koska kuukauden viimeinen päivä on ainapäivää ennen seuraavan kuun ensimmäistä päivää.Häh? Oikein. Näin saamme kuukauden viimeisen päivän: käytämme DATE()-funktiota selvittääksemme kuukauden ensimmäisen päivän.Seuraavakuukausi (lisäämällä 1 KUUKAUSI()-arvoon)… ja vähennä sitten 1:
=PÄIVÄYS(Vuosi($A3),KUUKAUSI($A3)+1,1)-1
Mitäs pidät niistä omenoista?![idiomi viite]
Mielenkiintoisena asiana, olisi ymmärrettävää, jos tämä kaava rikkoutuisi joulukuussa. Siinä tapauksessa pyydät Exceliä laskemaan päivämäärän, jossa kuukausi on "13". Onneksi Excel selvittää, mitä tarkoitat, ja palauttaa seuraavan vuoden tammikuun 1. päivän (josta kaava sitten vähentää yhden palauttaen joulukuun 31. päivän).
Entä…
Olen juuri raapinut pintaa mahdollisilla päivämäärämuunnoksilla tässä viestissä. Toivottavasti kuvailemani erilaiset lähestymistavat herättävät idean tai kaksi sinun erityistilanteeseesi. Mutta jos sinulla on jokin, joka häiritsee sinua, jätä kommentti tähän, niin ryhdyn siihen!
Ja kaikki tässä olevat esimerkit sisältyvät tähänladattava laskentataulukko. Voit muuttaa "aloituspäivämäärää" solussa A3 ja kaikki seuraavat päivämäärät päivittyvät automaattisesti. Hyvää päivämäärän muuntamista!
