Excel: Laske viikkoja - näin se toimii kaavan kanssa

Etsi oikea toiminto

Microsoft Excelissä on käytettävissä kalenteriviikko -toiminto, jonka avulla voit näyttää tietyn päivämäärän kalenteriviikon. Voit esimerkiksi näyttää automaattisesti, millä viikolla tapaaminen järjestetään. Excelin CALENDAR WEEK -taulukkotoiminnolla on myös sudenkuoppiaan. Tämä johtuu siitä, että Saksassa, Itävallassa ja Sveitsissä kalenteriviikko määritellään eri tavalla kuin Euroopan naapurimaissa tai Yhdysvalloissa. Tämä artikkeli näyttää, kuinka voit edelleen laskea oikean kalenteriviikon ja erot kalenteriviikon välillä DACH -alueella ja muissa maissa.

Kalenterin viikko -toiminto Excelissä

CALENDAR WEEK -toiminto laskee myös kalenteriviikot Excelin eurooppalaisissa versioissa käyttäen amerikkalaista standardia. Vuoden ensimmäinen päivä on yleensä kalenteriviikolla 1.

Kalenteriviikon laskennan syntaksi on:

= KALENTERIVIIKKO (PÄIVÄYS)

Kalenteriviikko määritellään eri tavalla Saksassa, Itävallassa ja Sveitsissä. Se on periaatteessa viikko, joka koostuu vuoden ensimmäisestä viikosta vähintään neljästä päivästä. Jos ensimmäinen tammikuu osuu perjantaihin, lauantaihin tai sunnuntaihin, kalenteriviikon taulukkotoiminto palauttaa väärän tuloksen Euroopalle.

Määritä kalenteriviikot oikein Excelissä - näin se toimii

Jotta voit näyttää oikean kalenteriviikon Saksalle, Itävallalle tai Sveitsille, tarvitset kaavan. Esimerkiksi solun C4 osalta sen on oltava seuraava:

= TRIM ((A4-DATE (VUOSI (A4-REMAIN (A4-2,7) +3); 1; REMAIN (A4-2,7) -9)) / 7)

Kaava on monimutkainen ja laaja. Selittääksemme, miten se toimii, tarkastelemme kaavan yksittäisiä komponentteja erikseen:

• Funktio REST (A4-2; 7) näkyy useita kertoja kaavassa. Tämä funktio jakaa päivämäärän 7: llä ja palauttaa tuloksena olevan funktion. Tämä funktio palauttaa arvon 0 maanantaisin, arvon 1 tiistaisin ja enintään arvon 6 sunnuntaisin.
• Kaavassa luku 2 vähennetään päivämäärän arvosta. Syynä tähän on se, että peruspäivä 1.1.1900 oli sunnuntai. Jaettuna 7 tuloksella ja loput 1. Tästä syystä maanantai ratkaistaan numeroksi 2. Jos haluat korjata päivämäärän 0: ksi sunnuntaina, 1 maanantaina jne., Vähennetään numero 2 päivämäärästä.
• Tämän viikonpäivän laskelman perusteella osa (VUOSI (A4-REST (A4-2; 7) +3)) antaa vuotuisen arvon, joka on vähennetty yhdellä vuoden kolmen ensimmäisen päivän aikana edellyttäen, että nämä päivät ovat perjantai, lauantai tai sunnuntai toimii.
• Tämä vuosiarvo sisältyy päivämäärään PÄIVÄYS -funktiossa. Tämä päivämäärä muodostetaan yleensä tammikuun toisen argumentin 1 avulla.
• Siirrä päiväarvoksi edellä kuvattu REST -funktio DATE -funktioon miinus arvo 9. Tämä johtaa yleensä negatiiviseen päivittäiseen arvoon. Kaava osoittaa, että päivämäärä, joka lasketaan DATE, on yleensä joulukuussa.
• Vuoden ensimmäisen kolmen päivän aikana on kuitenkin joulukuu ennen toiseksi viimeistä, edellyttäen, että nämä kolme päivää ovat perjantai, lauantai tai sunnuntai.
• Vähennät tämän päivämäärän, joka on laskettu PÄIVÄMÄÄRÄ -funktiolla sarakkeen A päivämäärästä, jaat eron seitsemällä (viikon seitsemän päivän ajan). Käytät tätä koko laskelmaa LYHYT -funktiossa saadaksesi vain kokonaislukuja.

Vaihtoehtoisesti: Käytä funktioargumenttia WOCHENSTART

Toinen mahdollisuus työskennellä Microsoft Excelin CALENDAR WEEK -funktion kanssa ja saavuttaa oikeat tulokset on lisätä funktion argumentti viikko start kaavaan. Kuten edellä esitetty kaava, kaavan rakentaminen vaatii vaivaa.

Toimintoargumentti WOCHENSTART voi erottaa kaksi eri järjestelmää kalenteriviikon laskemiseksi:

1. Järjestelmä 1 (Amerikkalainen järjestelmä) - raportoi viikon 1 viikon, joka sisältää tammikuun ensimmäisen.
2. Järjestelmä 2 (Eurooppalainen standardi ISO 8601: n mukaan) - KW 1 on viikko, joka sisältää vuoden ensimmäisen torstain.

Jos käytät funktioargumenttia WOCHENSTART ja kalenteriviikkotoimintoa, sinun on aina syötettävä seuraavat numerot argumentteina:

maanantai	2 tai 11
tiistai	12
keskiviikko	13
torstai	14
perjantai	15
Lauantai	16
sunnuntai	1 tai 17

Yhdistä CALENDAR WEEK -toiminto funktioargumenttiin WOCHENSTART kuudessa vaiheessa

Jos haluat linkittää kalenteriviikon toiminnon funktion argumentin viikon alkuun, toimi seuraavasti:

1. Kirjoita päivämäärä Excel -laskentataulukkoon.

2. Napsauta hiiren kakkospainikkeella solua, johon haluat sisällyttää kalenteriviikon.

3. Siirry FORMULAS -välilehdelle ja napsauta hiiren vasemmalla painikkeella "Insert function".

   

4. Kirjoita hakukenttään CALENDAR WEEK.

   

5. Kun olet löytänyt kalenteriviikon toiminnon, vahvista valintasi OK -painikkeella.

6. Heti kun olet funktion syöttökentässä, syötä kaavan komponentit seuraavasti:

   • Sarjanumero = solu, joka sisältää päivämäärän, esimerkiksi A1.
   • Numerotyyppi = viikonpäivän numeerinen arvo yllä olevan taulukon mukaisesti.
   

Kalenteriviikko tulostetaan oikein ISO 8601 -standardin mukaisesti.

Kuinka laskea minkä tahansa kalenteriviikon alku ja loppu Excelissä

Kalenteriviikoilla on tärkeä rooli ammatillisessa aikataulussa. Tapaamiset suunnitellaan tai sovitaan usein tietylle kalenteriviikolle. Siinä tapauksessa sinun on

1. Tiedä myös, mihin kalenteriviikkoon tietty päivämäärä kuuluu
2. Selvitä, milloin tietty kalenteriviikko alkaa tai päättyy.

Nämä tiedot auttavat sinua esimerkiksi suunniteltaessa tuotantopäiviä teollisessa ympäristössä tai sovittaessa toimitus- tai laskutuspäiviä.

Erikoiskaavan avulla voit helposti määrittää minkä tahansa kalenteriviikon alun ja lopun. Kaavalla on seuraava rakenne:

= TEKSTI (PÄIVÄYS (vuosiluku; 1; 7 * calweek-3-
WEEKDAY (DATE (vuosinumero;;); 3)); "PP.KK.VV")
& "-" & TEXT (DATE (vuosiluku; 1; 7 * kalenteriviikko + 3-
WEEKDAY (DATE (vuosinumero ;;); 3)); "PP.KK.VV")

Välität kaksi argumenttia kaavalle: Argumentilla Vuosiluku määritä vuosi, jossa kalenteriviikko sijaitsee. Välität kalenteriviikon numeron argumentilla Kalweek.

Kaavan tulos on tämän kalenteriviikon maanantain päivämäärä, jota seuraa yhdysviiva ja kalenteriviikon sunnuntain päivämäärä.

Esitetyssä työkirjassa seuraava solun D4 kaava antaa tuloksena ajanjakson "30.9.2013-10.6.13":

= TEKSTI (PÄIVÄYS (C4; 1; 7 * B4-3-WEEKDAY)
(DATE (C4;;); 3)); "DD.MM.YY") & "-" & TEXT
(PÄIVÄYS (C4; 1; 7 * B4 + 3-WEEKDAY (DATE (C4;;); 3));
"PP.KK.VV")

Tämä ajanjakso on kalenteriviikko 40 solusta B4 vuonna 2013 solusta C4.

Kaavan tulos antaa tekstin, jota et voi käyttää laskemisen jatkamiseen. Tuloksen aikajakson selventämiseksi voit näyttää tuloksen siten, että myös viikonpäivät näytetään. Voit tehdä tämän muuttamalla kaavan kahden TEKSTI -funktion numeromuotoa seuraavasti:

= TEKSTI (PÄIVÄYS (C4; 1; 7 * B4-3-WEEKDAY)
(DATE (C4;;); 3)); “DDD DD.MM.
KY ") &" - "& TEKSTI (PÄIVÄYS (C4; 1; 7 * B4 + 3-
WEEKDAY (DATE (C4;;); 3)); "DDDDD.MM.YY")

Kun kaava on kopioitu kaikille taulukon riveille, päivämäärät näytetään aina viikonpäivinä.

Kuinka laskea viikkoja Excelissä ja löytää oikea päivämäärä

Monissa suunnitelmissa viikoilla on rooli yksikkönä, esimerkiksi kalenteriviikolla tai tuotantoviikolla. Viikot voidaan myös laskea ja näyttää Excelissä helppolukuisilla lomakkeilla.

Seuraavassa kuvassa on luettelo päivämäärän arvoista sarakkeessa A. Sarakkeessa B on arvo, joka osoittaa, kuinka monta viikkoa tämä päivämääräarvo on siirrettävä:

Sarakkeessa C haluat käyttää kaavaa, joka laskee uuden päivämäärän sarakkeen A päivämäärän ja sarakkeen B viikkojen perusteella. Jos viikkojen määrä on suurempi kuin nolla, päivämäärä siirretään tulevaisuuteen, jos arvo on negatiivinen, se siirtyy menneisyyteen.

Voit saavuttaa tämän tavoitteen kertomalla viikkojen määrän arvolla 7 ja lisäämällä sen alkuperäiseen päivämäärään. Yllä kuvatulla taulukkorakenteella solun C2 kaava näyttää tältä:

= A1 + 7 * B1

Kaava soveltuu, koska Microsoft Excel tallentaa päivämääräarvot sisäisesti numeroina. Etäisyys päivästä toiseen on 1. Seuraava kuva esittää kaavan käytön esimerkkitaulukossa:

Johtopäätös: Kalenteriviikot ovat ratkaisevia ammattitaitoisen tapaamisen hallinnassa

Nimitykset määräävät elämämme sekä työelämässä että yksityiselämässä. Tapaamisia suunniteltaessa kalenteriviikoista tulee tärkeämpiä tapaamisintensiteetin kasvaessa, sillä niiden avulla voidaan lisätä selkeyttä tapaamisten suunnittelussa.

Microsoft Excel pystyy määrittämään oikean kalenteriviikon jokaiselle päivämäärälle. Ratkaiseva tekijä tässä on se, että eurooppalaiset käyttäjät noudattavat ISO 8601 -standardin mukaista eurooppalaista päivämääräjärjestelmää, kun he rakentavat Excel -kaavojaan. Oletuksena kalenteriviikko lasketaan Yhdysvaltain järjestelmän mukaan, mikä johtaa poikkeamiin. Yhdysvalloissa vuoden ensimmäinen päivä on yleensä varattu kalenteriviikolle 1. Sitä vastoin Saksassa, Itävallassa ja Sveitsissä ensimmäinen kalenteriviikko määritellään yleensä viikoksi, joka vuoden ensimmäisenä viikona koostuu vähintään neljästä päivästä. Käytännössä tämä tarkoittaa sitä, että eurooppalaisen järjestelmän viikko 1 on viikko, joka sisältää vuoden ensimmäisen torstain.

Voit laskea kalenteriviikon oikein käyttämällä Excelissä sekä KALENTERIVIIKO -funktiota että WEEKSTART -funktion argumenttia. On myös mahdollista määrittää yksittäisiä kaavoja ja mukauttaa niitä erityisesti laskentataulukkoosi.