Excel WEEKNUM-toiminto – muuntaa viikon numeron päivämääräksi ja päinvastoin (2023)

Excel WEEKNUM-funktio - syntaksi

VIIKKONUMERO-funktiota käytetään Excelissä palauttamaan vuoden tietyn päivämäärän viikon numero (luku väliltä 1–54). Sillä on kaksi argumenttia, 1^stvaaditaan ja 2^ndon valinnainen:

WEEKNUM(sarjanumero, [palautustyyppi])

• Sarjanumero- mikä tahansa päivämäärä viikon sisällä, jonka numeroa yrität löytää. Tämä voi olla viittaus soluun, joka sisältää päivämäärän, päivämäärä, joka on syötetty PÄIVÄYS-funktiolla tai palautettu jollakin muulla kaavalla.
• Palautustyyppi(valinnainen) - numero, joka määrittää, minä päivänä viikko alkaa. Jos jätetään pois, käytetään oletustyyppiä 1 (sunnuntaista alkava viikko).

Tässä on täydellinen luettelopalautustyyppiarvoja tuetaan WEEKNUM kaavoissa.

WEEKNUM-funktiossa kaksi erilaistaviikon numerointijärjestelmätkäytetään:

• Järjestelmä 1.Viikkoa, joka sisältää tammikuun 1. päivän, pidetään 1^stvuoden viikko ja se on numeroitu viikoksi 1. Tässä järjestelmässä viikko alkaa perinteisesti sunnuntaina.
• Järjestelmä 2.Tämä onISO viikon päivämääräjärjestelmäjoka on osa ISO 8601 päivämäärä- ja aikastandardia. Tässä järjestelmässä viikko alkaa maanantaina ja vuoden ensimmäisen torstain sisältävä viikko katsotaan viikoksi 1. Se tunnetaan yleisesti eurooppalaisena viikkonumerointijärjestelmänä ja sitä käytetään pääasiassa julkishallinnossa ja liiketoiminnassa verovuosien ja ajanmittausten osalta.

Kaikki yllä luetellut palautustyypit koskevat järjestelmää 1, paitsi palautustyyppiä 21, jota käytetään järjestelmässä 2.

Huomautus.Excel 2007:ssä ja aiemmissa versioissa vain vaihtoehdot 1 ja 2 ovat käytettävissä. Palautustyyppejä 11–21 tuetaan vain Excel 2010:ssä ja Excel 2013:ssa.

Excel WEEKNUM -kaavat muuntaaksesi päivämäärän viikon numeroiksi (1 - 54)

Seuraava kuvakaappaus osoittaa, kuinka saat viikkonumerot päivämääristä yksinkertaisimmalla tavalla= VIIKKONUMERO(A2)kaava:

Yllä olevassa kaavassapalautustyyppiargumentti jätetään pois, mikä tarkoittaa, että käytetään oletustyyppiä 1 - sunnuntaina alkavaa viikkoa.

Jos haluat mieluummin aloittaa jollain muulla viikonpäivällä, esimerkiksi maanantaina, käytä sitten 2 toisessa argumentissa:

=VIIKKONUMERO(A2, 2)

Soluun viittaamisen sijaan voit määrittää päivämäärän suoraan kaavaan käyttämällä PÄIVÄYS(vuosi, kuukausi, päivä)-funktiota, esimerkiksi:

=VIIKKONUMERO(PÄIVÄMÄÄRÄ(2015;4;15), 2)

Yllä oleva kaava palauttaa 16, joka on sen viikon luku, joka sisältää 15. huhtikuuta 2015, ja viikko alkaa maanantaina.

Tosielämän skenaarioissa Excelin WEEKNUM-toimintoa käytetään harvoin yksinään. Useimmiten käytät sitä yhdessä muiden toimintojen kanssa suorittaaksesi erilaisia ​​​​viikon numeroon perustuvia laskelmia, kuten kuvassa on esitettylisää esimerkkejä.

Kuinka muuntaa viikon numero päivämääräksi Excelissä

Kuten juuri huomasit, ei ole iso juttu muuttaa päivämäärä viikon numeroksi Excelin WEEKNUM-toiminnolla. Mutta entä jos etsit päinvastaista, eli viikkonumeron muuntamista päivämääräksi? Valitettavasti ei ole olemassa Excel-toimintoa, joka voisi tehdä tämän heti. Joten meidän on rakennettava omat kaavamme.

Oletetaan, että sinulla on vuosi solussa A2 ja viikon numero solussa B2, ja nyt haluat laskea alkamis- ja lopetuspäivämäärät tällä viikolla.

Huomautus.Tämä kaavaesimerkki perustuu ISO-viikkonumeroihin, ja viikko alkaa maanantaina.

Kaava palauttaaAloituspäivämääräviikon päivä on seuraava:

=PÄIVÄYS(A2, 1, -2) - VIIKONPÄIVÄ(PÄIVÄMÄÄRÄ(A2, 1, 3)) + B2 * 7

Missä A2 on vuosi ja B2 on viikon numero.

Huomaa, että kaava palauttaa päivämäärän sarjanumerona, ja jotta se näkyy päivämääränä, sinun on muotoiltava solu vastaavasti. Tarkemmat ohjeet löydät osoitteestaPäivämäärän muodon muuttaminen Excelissä. Ja tässä on kaavan palauttama tulos:

Viikon numeron muuntaminen päivämääräksi ei tietenkään ole triviaali, ja voi kestää hetken, ennen kuin pääset liikkeelle. Joka tapauksessa teen parhaani tarjotakseni merkityksellisen selityksen niille, jotka haluavat päästä pohjaan.

Kuten näet, kaavamme koostuu kahdesta osasta:

• PÄIVÄMÄÄRÄ(A2, 1, -2) - VIIKONPÄIVÄ(PÄIVÄMÄÄRÄ(A2, 1, 3))- laskee edellisen vuoden viimeisen maanantain päivämäärän.
• B2*7- lisää viikkojen lukumäärän kerrottuna 7:llä (viikon päivien lukumäärä) saadakseen kyseisen viikon maanantain (alkamispäivämäärä).

ISO-viikon numerointijärjestelmässä viikko 1 on viikko, joka sisältää vuoden ensimmäisen torstain. Näin ollen ensimmäinen maanantai on aina 29. joulukuuta ja tammikuun 4. päivän välillä. Joten tämän päivämäärän löytämiseksi meidän on löydettävä maanantai ennen 5. tammikuuta.

Microsoft Excelissä voit poimia viikonpäivän päivämäärästä käyttämälläWEEKDAY-toiminto. Ja voit käyttää seuraavaa yleistä kaavaa saadaksesi maanantain välittömästi ennen mitä tahansa päivämäärää:

=Päivämäärä- VIIKKONPÄIVÄ(Päivämäärä-2)

Jos perimmäinen tavoitteemme olisi löytää maanantai välittömästi ennen 5^thvuoden tammikuuta A2:ssa, voisimme käyttää seuraavia DATE-funktioita:

=PÄIVÄYS(A2,1,5) - VIIKONPÄIVÄ(PÄIVÄYS(A2,1,3))

Mutta emme todellakaan tarvitse tämän vuoden ensimmäistä maanantaita, vaan pikemminkin edellisen vuoden viimeistä maanantaita. Joten sinun on vähennettävä 7 päivää tammikuun 5. päivästä, jolloin saat -2 ensimmäisessä DATE-funktiossa:

=PÄIVÄMÄÄRÄ(A2,1,-2) - VIIKONPÄIVÄ(PÄIVÄYS(A2,1,3))

Verrattuna hankalaan kaavaan, jonka olet juuri oppinut, laskemallaPäättymispäiväviikon on pala kakkua :) Saadaksesi kyseisen viikon sunnuntain lisäät vain 6 päivääAloituspäivämäärä, eli=D2+6

Vaihtoehtoisesti voit lisätä 6 suoraan kaavaan:

=PÄIVÄMÄÄRÄ(A2, 1, -2) - VIIKONPÄIVÄ(PÄIVÄMÄÄRÄ(A2, 1, 3)) + B2 * 7 + 6

Varmistaaksesi, että kaavat näyttävät aina oikeat päivämäärät, katso seuraava kuvakaappaus. Yllä käsitellyt aloituspäivämäärä ja lopetuspäivämäärät on kopioitu sarakkeisiin D ja E:

Kuinka saada kuukausi viikon numerosta

Saat viikon numeroa vastaavan kuukauden etsimällä tietyn viikon ensimmäisen päivän, kuten kohdassa selitetääntämä esimerkki, ja kääri sitten kaavaExcelin KUUKAUSI-toimintokuten tämä:

=KUUKAUSI(PÄIVÄMÄÄRÄ(A2, 1, -2) - VIIKONPÄIVÄ(PÄIVÄMÄÄRÄ(A2, 1, 3)) + B2 * 7)

Huomautus.Muista, että yllä oleva kaava toimii perustuenISO viikon päivämääräjärjestelmä, jossa viikko alkaa maanantaina ja viikko, joka sisältää vuoden 1. torstai, katsotaan viikoksi 1. Esimerkiksi vuonna 2016 ensimmäinen torstai on 7. tammikuuta ja siksi viikko 1 alkaa 4. tammikuuta 2016 .

Kuinka saada viikkonumero kuukaudessa (1-6)

Jos liiketoimintalogiikkasi edellyttää tietyn päivämäärän muuntamista viikon numeroksi vastaavan kuukauden sisällä, voit käyttää yhdistelmää WEEKNUM,PÄIVÄMÄÄRÄja MONTH-funktiot:

Olettaen, että solu A2 sisältää alkuperäisen päivämäärän, käytä seuraavaa kaavaa viikolle alkaenmaanantai(huomautus 21 WEEKNUM:n return_type-argumentissa):

=VIIKKONUMERO($A2,21)-VIIKKONUMERO(PÄIVÄYS(VUOSI($A2), KUUKAUSI($A2),1),21)+1

Alkaen viikon ajansunnuntai, jätä palautustyyppi-argumentti pois:

=VIIKKONUMERO($A2)-VIIKKONUMERO(PÄIVÄYS(VUOSI($A2), KUUKAUSI($A2),1))+1

Kuinka laskea arvot yhteen ja löytää keskiarvo viikon numerolla

Nyt kun tiedät, kuinka päivämäärä muunnetaan viikkonumeroksi Excelissä, katsotaanpa, kuinka voit käyttää viikkonumeroita muissa laskelmissa.

Oletetaan, että sinulla on kuukausittaisia ​​myyntilukuja ja haluat tietää kunkin viikon kokonaismäärän.

Selvitetään aluksi kutakin myyntiä vastaava viikkonumero. Jos päivämäärät ovat sarakkeessa A ja myynti sarakkeessa B, kopioi= VIIKKONUMERO(A2)kaava sarakkeen C yli, joka alkaa solusta C2.

Tee sitten luettelo viikkonumeroista johonkin toiseen sarakkeeseen (esimerkiksi sarakkeeseen E) ja laske kunkin viikon myynti käyttämällä seuraavaa SUMIF-kaavaa:

=SUMMA($C$2:$15 C$, $E2, $B$2:$B$15)

Missä E2 on viikon numero.

Tässä esimerkissä työskentelemme maaliskuun myyntiluettelon kanssa, joten meillä on viikkonumerot 10–14, kuten seuraavassa kuvakaappauksessa näkyy:

Samalla tavalla voit laskea tietyn viikon myynnin keskiarvon:

=KESKIARVOJOS($C$2:$15 C$, $E2, $B$2:$B$15)

Jos WEEKNUM-kaavan apusarake ei sovi hyvin tietoasetteluusi, voin valitettavasti todeta, että siitä ei ole yksinkertaista tapaa päästä eroon, koska Excel WEEKNUM on yksi niistä funktioista, jotka eivät hyväksy väliargumentteja. Siksi sitä ei voi käyttää SUMPRODUCT- tai missään muussa taulukkokaavassa, kutenKUUKAUSI-toimintosamanlaisessa skenaariossa.

Kuinka korostaa soluja viikon numeron perusteella

Oletetaan, että jossakin sarakkeessa on pitkä luettelo päivämääristä ja haluat korostaa vain ne, jotka liittyvät tiettyyn viikkoon. Tarvitset vain ehdollisen muotoilusäännön, jonka WEEKNUM-kaava on samanlainen kuin tämä:

=VIIKKONUMERO($A2)=10

Kuten alla olevasta kuvakaappauksesta käy ilmi, sääntö korostaa myynnit, jotka tehtiin viikolla 10, joka on maaliskuun 2015 ensimmäinen viikko. Koska sääntö koskee A2:B15:tä, se korostaa arvot molemmissa sarakkeissa. Saat lisätietoja ehdollisten muotoilusääntöjen luomisesta tässä opetusohjelmassa:Excelin ehdollinen muotoilu, joka perustuu toiseen soluarvoon.