Osa Excelin normeista on työskentely numeroiden ja päivämäärien kanssa, koska se on Excelin vahvuus. Kun päivämäärät ovat tietojen keskeinen osa, saatat työskennellä niiden parissa lisätäksesi niihin päiviä, kuukausia tai vuosia tai löytääksesi niiden välisen eron päivinä, kuukausina ja vuosina.
Ohjaamme sinut laskemaan viikkojen määrän kahden päivämäärän välillä tänään. Tämän opetusohjelman loppuun mennessä opit laskemaan kokonaisia viikkoja, lähimpiä kokonaisia viikkoja sekä viikkoja ja päiviä päivämäärien välillä yhdistettynä muutamaan Excel-temppuun.
Aletaan laskea!
Sisällysluettelo
Viikkojen laskeminen päivämäärien välillä
Opitaan ensin, kuinka voimme laskea viikkojen lukumäärän kahden päivämäärän välillä Excelissä. Voimme sitten ottaa asioita ylös ja laskea viikkoja ja päiviä kahden päivämäärän välillä.
Kokonaisia viikkoja lasketaan
Aloitetaan tavallisella vaniljalla ja hankitaan lisäosat myöhemmin. Ensimmäinen asia, jonka aiomme tehdä, on laskea päivämäärien välinen ero viikkoina hieman tasoittaen käyttämällä INT-toimintoa. INT-toimintopyöristää numeronalas lähimpään kokonaislukuun. Katso alla olevaa esimerkissä käytettyä kaavaa, ja näytämme sinulle, miksi haluamme INT-funktion laskevan viikkoja päivämäärien välillä:
=INT((D3-C3)/7)
Otamme ensin lopetuspäivämäärän (solussa D3) ja vähennämme siitä aloituspäivämäärän (C3). D3-C3 antaa meille vastauksen päivissä eli 14. D3-C3 jaetaan sitten 7:llä, joten saamme vastauksen sen sijaan viikoissa, ja niin saavumme 2:een.
Tämä ensimmäinen ilmentymä oli suoraviivainen, mutta jos katsomme C4:tä ja D4:ää, ero on 20 päivää, ja jaettuna seitsemällä, saadaan 2,85714 ja enemmän. Tällaisia tapauksia varten meillä on INT-funktio valmiina kaavassa pyöristämään luku alaspäin lähimpään kokonaislukuun. Siksi saamme toisessa tapauksessa tulokseksi 2, vaikka se on lähempänä 3:a. Tämä johtuu siitä, että tavoitteenamme on laskea kokonaiset viikot, jotka ovat kuluneet näiden kahden päivämäärän välillä.
Numeerisesti looginen tapa laskea päivämäärien välinen ero on vähentää aikaisempi päivämäärä myöhemmästä päivämäärästä, koska päinvastoin syntyisi miinusmerkki. Tämä voidaan ratkaista ABS-toiminnolla, joka jättää huomioimatta luvun etumerkin. Jos nyt vähentäisit myöhemmän päivämäärän aikaisemmasta päivämäärästä, tämä olisi käytettävä kaava:
=ABS(PYÖRISTÄÄ ALASPÄIN((C3-D3)/7,0))
Kuten näet, emme ole vain lisänneet ABS-toimintoa, vaan olemme myös korvanneet INT-toiminnon ROUNDDOWN-toiminnolla. ROUNDDOWN pyöristää tuloksen C3-D3)/7 alaspäin ja vaatii myös haluamasi määrän desimaaleja. Koska haluamme kokonaisluvun, ROUNDDOWNin toinen argumentti on 0. Lopetuspäivämäärä vähennetään. Siksi kokonaisluku sisältää miinusmerkin. ABS-funktio palauttaa arvon ilman etumerkkiä.
Sitä kaavassa tapahtuu. Kerromme sinulle, miksi et voi laajentaa INT-kaavaa, kun käsittelet miinusmerkkejä. INT pyöristää luvun alaspäin lähimpään kokonaislukuun, joten jos olemme -2,85:ssä, INT pyöristää sen alas -3:een. ROUNDDOWN-funktio pyöristää luvun alas kohti nollaa. Näin ollen negatiivisia lukuja ei pyöristetä alaspäin suurempaan negatiiviseen lukuun, vaan ne pyöristetään lähelle nollaa, eli -2.
Lähimmät kokonaiset viikot
Aiemmin laskimme kokonaisia viikkoja kahden päivämäärän välillä. Nyt näytämme, kuinka lasketaan ero, joka on lähempänä lähintä kokonaislukua viikkoja. Vain pieni muutos yllä olevaan kaavaan pitäisi tehdä sen. Huomaa seuraava kaava:
=PYÖRISTÄÄ((D3-C3)/7,0)
Vähentämällä päivämäärät, jakamalla ne 7:llä ja pitäen kiinni siitä, että emme halua desimaaleja, ROUND-funktio pyöristää luvun ylös- tai alaspäin lähimpään numeroon ja mainittuun numeroiden määrään. Tuloksia tarkasteltaessa voimme toisessa tapauksessa todeta, että 2,85 on pyöristetty ylöspäin 3:ksi 2:n sijaan.
Tekstin lisääminen
Kuinka monta kertaa olet vastannut puhtaasti numeerisesti matematiikan tunneilla, että opettaja kysyy sinulta "5 mitä? 5 aasia, 5 pizzaa, 5 kenkiä?". Jos mittayksikkö on kiusallisen tärkeä, voit lisätä tulokseen vastaavan tekstin seuraavasti. Käytä seuraavaa kaavaa:
=INT((D3-C3)/7)&"viikkoa"
Olemme ottaneet päivän ensimmäisen ja tavallisen vaniljakaavamme ja laajentaneet sitä &-operaattorilla ja tekstillä "viikkoja" lainausmerkeissä. Muista lisätä välilyönti ennen tekstiä, jotta et saa 2 viikkoa tulokseen.
Onnellisia opettajia, onnellisia päiviä.
Huomautus:Kaavan tulokset ovat napsahtaneet tasauksesta oikealle vasemmalle, koska niistä on tullut tekstiarvoja tekstin lisäämisen jälkeen. Vältä tekstin lisäämistä samaan soluun, jos haluat käyttää tuloksena saatua arvoa edelleen laskelmissa ja lukukaavoissa.
Viikkojen ja päivien laskeminen päivämäärien välillä
Rakennamme kaavaa, emme aio vain saada päivämäärät eroa viikkoina, vaan viikkoina ja päivinä. Päivät-elementille MOD-funktio laskee jäljellä olevat päivät. MOD-funktio palauttaa jäännöksen, kun kaksi lukua jaetaan. Tämä kaava palauttaa kokonaiset viikot ja jäljellä olevat päivät:
=INT((D3-C3)/7)&"viikkoa"&MOD(D3-C3,7)&"päivää"
Siirrämme edellisen kaavan, jossa lisäsimme tekstiä, C3:n ja D3:n päivämäärän välistä kuluneisiin kokonaisiin viikkoihin. Nyt päivien lisäämiseksi &-operaattori on ketjuttanut MOD-funktion tuloksen. MOD vähentää C3:n D3:sta ja jakaja on 7; aivan kuten olemme tehneet INT-kaavalla eli D3-C3)/7. Tämä lauseke antaa meille kokonaiset viikot ja MOD-funktio antaa meille loppuosan tästä lausekkeesta, eli loppuosan kokonaisten viikkojen jälkeen.
C3:lla ja D3:lla on tasan kaksi viikkoa eroa, joten meillä on aluksi 0 päivää; " päivää" lisätty tekstinä MOD-funktion jälkeen &-operaattorilla. Huomioi välilyönnin oikea käyttö kaavaan lisätyissä merkkijonoissa. Voit halutessasi lisätä myös välimerkkejä, kuten pilkkua.
Toisessa tapauksessa päivämäärien välinen ero on 20 päivää, INT-funktio palauttaa arvon 2 ja MOD-funktio palauttaa 6. Tämä tekee 2 viikkoa 6 päivää: paikalla!
Säätö 0 viikkoon
Juuri nyt huomasit, että esimerkissämme C3 ja D3 antavat meille eron 2 viikkoa ja 0 päivää, 0 päivää näyttää hieman oudolta, ellei paljon. Nyt kun tavallinen vanilja on täynnä ja ladattu täyteen, saamme viimeiset lisäosat kaavaan käyttämällä uudelleen MOD-toimintoa ja lisäämälläIF-toiminto. Tämän kaavan pitäisi auttaa sinua poistamaan tuloksesta 0 päivää:
=INT((D3-C3)/7)&"viikkoa"&JOS(MOD(D3-C3,7)=0",",","&MOD(D3-C3,7)&"päivää")
Edistäen kaavaa edellisestä osiosta, olemme lisänneet IF-funktion keskelle. Lisätty bitti lukee, että jos D3-C3 jaettuna 7:llä on 0, palauttaa tyhjä merkkijono (merkitty "").
Joten koko kaava menee - viikkojen lukumäärä (laskettu D3-C3/7), pyöristetty alaspäin INT-funktiolla ja liitetty tekstillä "viikkoja". Sitten, jos jäljellä olevien viikkojen laskelma on 0, palauta tyhjä teksti, muuten palauta loput viikot ja lisää "päivät" tekstiin loppuun.
Nyt voit nähdä tietojoukkoon sovelletusta kaavasta; viikkojen lukumäärää jatketaan vain päivien lukumäärällä, jos jälkimmäinen ei ole 0.
Käyttäjän määrittämä viikkojen laskentatoiminto
Teemme omia kaavojamme koko ajan, joten pyrimme tänään poikkeaviin; tehdä omaa toimintaamme. KäyttääVBAluodaksesi toiminnon ja käyttääksesi sitä vaivattomasti. Toiminto on samanlainen kuinDATEDIF-toimintoja löydät sen alla olevista vaiheista sekä toiminnon luomisohjeet:
- Siirry kohtaanKehittäjä-välilehti>Koodiryhmä>Visual Basic-kuvaketta tai painaAlt + F11avaimet käynnistääksesiVisual Basic
- KlikkaaLisäävälilehti >Moduulimahdollisuus avata aModuuli
- Kopioi alla oleva koodi ja liitä seModuuli
Tekijä: ExcelTrick.com
Toiminto WeekDiff(päivämäärä 1Kuten Päivämäärä, päivämäärä 2Kuten Päivämäärä)Kuten Pitkä
WeekDiff=DateDiff("ww", päivämäärä1, päivämäärä2, vbmaanantai)
Loppu Toiminto
Toiminnon syntaksi annetaan koodin ensimmäisellä rivillä, päivämäärä1 on aikaisempi päivämäärä ja päivämäärä2 myöhempi päivämäärä. Toisella rivillä asetamme kolmanneksi argumentiksi vbMonday, joka laskee maanantain viikon ensimmäiseksi päiväksi.
Jos kolmas argumentti jätetään pois, funktio pitää sunnuntaita oletuksena ensimmäisenä arkipäivänä, mutta jos haluat muuttaa sen kuten teimme, kun kirjoitat pilkun päivämäärän2 jälkeen riville 2, näkyviin tulee IntelliSense-luettelo, jossa on kuplakommentti DateDiff-funktion syntaksi:
- Valitse haluamasi vaihtoehto kaksoisnapsauttamalla sitä:
- KlikkaaJuosta-painiketta työkalupalkissa suorittaaksesi koodin tai painaF5
Näet aMakrotvalintaikkuna tulee näkyviin, kun suoritat koodin. Syötä funktion nimi (tässä tapauksessa Weekdiff) annettuun kenttään ja valitseLuoda-painiketta.
- SuljeVisual Basiceditori nyt, kun toiminto on luotu.
Takaisin laskentataulukkoon näyttää siltä, että mikään ei ole muuttunut, mutta kun siirryt kohdesoluun syöttämään kaava, kirjoita luodun funktion nimi ja näet Excelin IntelliSense-sovelluksessa funktion kirjainkoolla olevan merkinnän.
- Valitse funktio ja syötä kaava, jossa on aikaisempi päivämäärä ja sitten myöhempi päivämäärä.
Ja sinulla on viikot päivämäärien välillä luodulla toiminnolla! Viikkojen lukumäärä kallistuu lähimpään kokonaislukuun.
Kun olet tutustunut Exceliin, alat selvittää, kuinka vähän sinun täytyy tehdä itse, mikä on aina hyvä asia – varsinkin Excel-jakeessa. Toivomme, että löydät menetelmistä, vinkeistä ja temppuista apua viikkojen laskemiseen päivämääräjen välillä. Jatka lisäämistä tavalliseen vaniljaan tarpeidesi mukaan. Annamme sinulle myöhemmin lisää kaikkea Excel-jazzia!