Jokainen liiketoimi suoritetaan tiettynä aikana tai ajanjaksona ja sidotaan sitten tiettyyn päivämäärään. Excelissä päivämäärät muunnetaan kokonaisluvuiksi. Toisin sanoen jokaisella päivämäärällä on oma kokonaislukunsa, esimerkiksi 01/01/1900 on numero 1 ja 01/02/1900 on numero 2 jne. Vuosien, kuukausien ja päivien määrittäminen ei ole muuta kuin sopiva muotoilu seuraaville numeroarvoille. Tästä syystä jopa yksinkertaisimmat toiminnot päivämäärillä Excelissä (esimerkiksi lajittelu) osoittautuvat erittäin ongelmallisiksi.

Lajittele Excelissä päivämäärän ja kuukauden mukaan

Alla olevassa kuvassa olevan taulukon esimerkin avulla näytämme, kuinka se valmistetaan mukavaa päivämäärän lajittelua varten. Päivämäärätapahtumien luettelomme kattaa solualueen B1:C15.

Ensinnäkin luodaan sarake, jossa numeroimme kaikki tapahtumat. Pidä tätä toimintoa hyvänä muotona kaikenlaiselle lajittelulle. Tällaisissa tapauksissa, jos tiedot sekoitetaan väärin, meillä on aina mahdollisuus palata taulukon alkuperäiseen muotoon. Tätä varten:

Tämän seurauksena sarake täytetään automaattisesti tapahtumanumeroilla 1–14.

Hyödyllinen neuvo! Excelissä useimmille ongelmille on useita ratkaisuja. Voit normalisoida sarakkeet automaattisesti Excelissä käyttämällä hiiren oikeaa painiketta. Voit tehdä tämän siirtämällä osoitinta näppäimistön kohdistinmerkin päälle (solussa A2) ja pitämällä vain hiiren oikeaa painiketta painettuna, vetämällä merkkiä saraketta pitkin. Kun vapautat hiiren oikean painikkeen, kontekstivalikko

josta sinun on valittava "Täytä" -vaihtoehto. Ja sarake täytetään automaattisesti numerosarjalla, joka on samanlainen kuin ensimmäinen automaattinen täyttötapa.

1. Nyt on aika luoda lisäsarakkeita, jotka auttavat merkittävästi lajittelemaan tapahtumaluetteloa tehokkaasti. Sinun on noudatettava muutamia yksinkertaisia ​​ohjeita:
2. Täytä solut D1, E1, F1 otsikoiden nimillä: "Vuosi", "Kuukausi", "Päivä".

• Kirjoita kunkin sarakkeen mukaan vastaavat funktiot otsikoiden alle ja kopioi ne jokaiseen sarakkeeseen:
• D1: =Vuosi(B2);
• E1: =KUUKAUSI(B2);

F1: =PÄIVÄ(B2).

Taulukkomme on valmis ja mahdollistaa tapahtumien monisuuntaisen lajittelun päivämäärän mukaan. Tehdään ensin testilajittelu varmistaaksemme, että kaikki toimii.

Oletetaan, että haluamme lajitella tapahtumapäivät kuukauden mukaan. Tässä tapauksessa päivien ja vuosien järjestyksellä ei ole väliä. Voit tehdä tämän siirtymällä mihin tahansa soluun "Kuukausi" -sarakkeessa (E) ja valitsemalla työkalu: "TIEDOT" - "Lajittele ja suodata" - "Lajittele nouseva".

Nyt voit nollata lajittelun ja palauttaa taulukon tiedot alkuperäiseen muotoonsa siirtymällä mihin tahansa sarakkeen "Ei" (A) soluun ja valitsemalla uudelleen sama "Lajittele nouseva" -työkalu.



Kuinka lajitella päivämäärät useiden ehtojen mukaan Excelissä

Nyt voit aloittaa päivämäärien monimutkaisen lajittelun useiden ehtojen mukaan. Tehtävä on seuraava: tapahtumat on lajiteltava seuraavassa järjestyksessä:

1. Vuodet nousevassa järjestyksessä.
2. Tiettyjen vuosien kuukaudet ovat laskevassa järjestyksessä.
3. Tiettyjen kuukausien päivät ovat laskevassa järjestyksessä.

Menetelmä tehtävän toteuttamiseksi:

Tämän seurauksena suoritimme päivämäärät monimutkaisen lajittelun useiden ehtojen mukaan:

Taulukon arvojen lajittelemiseksi päivämäärämuodossa Excel tarjoaa pudotusvalikon vaihtoehtoja, kuten Vanhasta uuteen ja Uudesta vanhaan. Mutta käytännössä, kun työskentelet suurten tietomäärien kanssa, tulos ei aina täytä odotuksia. Koska varten Excel ohjelmat päivämäärät ovat kokonaislukuja, on turvallisempaa ja tehokkaampaa lajitella ne tässä artikkelissa kuvatulla menetelmällä.

Jokainen rivi on erillinen tapahtuma. Sinun on tehtävä raportti kuukaudelta. Kuinka monta tuotetta myytiin kuukaudessa. On olemassa erilaisia ​​tapoja ratkaista tämä ongelma, mutta oletetaan, että tehtävämme on poimia kuukausi päivämäärästä, jotta myöhemmin on helppo tehdä yhteenveto niistä.

Menetelmä 1. Hanki kuukausi päivämäärästä käyttämällä KUUKAUSI-toimintoa Excelissä

Kirjoitamme kaavan:

KUUKAUSI(A2)

Venytetään kaavaa ja saadaan kuukausi päivämäärästä numeron muodossa. 5 on toukokuu, 8 on elokuu ja niin edelleen.

On olemassa samanlaisia ​​toimintoja: VUOSI, KUUKAUSI, VIIKKO, PÄIVÄ, TUNTI, MINUUTTIT, SEKUNNIT, jotka toimivat samalla tavalla. Jos esimerkiksi esimerkissämme kirjoitamme =YEAR(A2), niin tulos on 2013 ja niin edelleen

Joskus sinun täytyy saada kuukausi päivämäärästä tekstimuodossa: "tammikuu, helmikuu, maaliskuu...", tässä tapauksessa käytämme toista toimintoa.

Menetelmä 2. Hanki kuukausi päivämäärästä käyttämällä Excelin TEKSTI-toimintoa

Syntaksi on seuraava

TEKSTI(arvo, muoto)

Arvo on viittaus soluun, jossa on päivämäärä
Muoto - sinun on käytettävä kuukausia saadaksesi iso kirjain"M". Lisäksi näyttömuoto riippuu sen määrästä (kuukauden ensimmäinen kirjain, kuukauden koko nimi, lyhyt nimi, kaksinumeroisena ja yksinumeroisena)
Tämä näkyy selvästi kuvakaappauksessa.

Päivämäärät ovat usein tärkeä osa data-analyysiä. Usein kysyt esimerkiksi seuraavia kysymyksiä: milloin tuote ostettiin, kuinka kauan projektin tehtävän suorittaminen kestää tai mikä on tilikauden keskimääräinen liikevaihto? Päivämäärien oikea syöttäminen on välttämätöntä tarkkojen tulosten varmistamiseksi. Jotta voit ymmärtää nämä tulokset oikein, sinun on muotoiltava päivämäärät, jotta ne on helpompi ymmärtää.

Tärkeä: Koska säännöt, jotka koskevat päivämäärien tulkintaa kaikissa laskentaohjelmissa, ovat monimutkaisia, sinun on oltava mahdollisimman päivämääräkohtainen jokaisessa syötössä. Näin varmistetaan, että päivämäärälaskelmat ovat mahdollisimman tarkkoja.

Tietoja laskelmista ja päivämäärämuodoista

Excel tallentaa päivämäärät sarjanumeroina, joita kutsutaan peräkkäisiksi arvoiksi. Esimerkiksi Excel for Windowsissa 1. tammikuuta 1900 on järjestysnumero 1 ja 1. tammikuuta 2008 on järjestysnumero 39448, koska se on 39 448 päivää tammikuun 1. päivän 1900 jälkeen.

Excel tallentaa aika-arvot desimaaleina, koska aikaa käsitellään vuorokauden murto-osana. Desimaaliluku on arvo, joka vaihtelee välillä 0 (nolla) - 0,99999999, joka edustaa aikaa 0:00:00 (0:00:00) - 23:59:59 (23:59:59).

Koska päivämäärä- ja aikaarvot ovat arvoja, ne voidaan lisätä, vähentää ja sisällyttää muihin laskelmiin. Voit tarkastella päivämäärää järjestysarvona ja kellonaikaa desimaalilukuna muuttamalla päivämäärän tai kellonajan sisältävän solun muodon yleiseen muotoon.

Kahden päivämääräjärjestelmän ymmärtäminen

Excel for Mac ja Excel for Windows tukevat 1900- ja 1904-päivämääräjärjestelmiä. Windowsin Excelin oletuspäivämääräjärjestelmä on 1900. ja Excel for Macin oletuspäivämääräjärjestelmä on 1904.

Excel for Windows perustui alun perin vuoden 1900 päivämääräjärjestelmään, koska se paransi yhteensopivuutta muiden taulukkolaskentaohjelmien kanssa, jotka on suunniteltu käyttöjärjestelmä MS-DOS ja Microsoft Windows ja sellaiseksi hänestä tuli vakiojärjestelmä Päivämäärä Excel for Mac perustui alun perin vuoden 1904 päivämääräjärjestelmään, koska se paransi yhteensopivuutta varhaisten Macintosh-tietokoneiden kanssa, jotka eivät tukeneet päivämääriä ennen 2. tammikuuta 1904, joten siitä tuli oletuspäivämääräjärjestelmä.

Alla oleva taulukko näyttää kunkin päivämääräjärjestelmän ensimmäisen päivämäärän ja viimeisen ajanjakson päivämäärät sekä kuhunkin päivämäärään liittyvän peräkkäisen merkityksen.

Koska kahdessa päivämääräjärjestelmässä käytetään eri aloituspäiviä, sama päivämäärä esitetään eri sarjanumeroilla kussakin päivämääräjärjestelmässä. Esimerkiksi 5. heinäkuuta 2007 voi olla kaksi erilaista peräkkäistä merkitystä käytetystä päivämääräjärjestelmästä riippuen.

Kahden päivämääräjärjestelmän välinen ero on 1 462 päivää; Tämä tarkoittaa, että päivämäärän sarjanumero vuoden 1900 päivämääräjärjestelmässä on aina 1 462 päivää pienempi kuin saman päivämäärän sarjanumero vuoden 1904 päivämääräjärjestelmässä. Toisaalta päivämäärän sarjanumero vuoden 1904 päivämääräjärjestelmässä on aina 1 462 päivää. pienempi kuin samojen päivämäärien sarjaarvo vuoden 1900 päivämääräjärjestelmässä 1 462 päivää vastaa 4 vuotta ja yksi päivä (mukaan lukien yksi karkauspäivä).

Kaksinumeroisen vuoden tulkintatavan muuttaminen

Tärkeä: Varmistaaksesi, että vuosiarvot tulkitaan oikein, syötä neljä numeroa (esimerkiksi 2001 01:n sijaan). Jos syötät nelinumeroiset vuodet, Excel ei tulkitse vuosisatoja.

Jos syötät kaksinumeroisen vuoden päivämäärän tekstisoluun tai tekstiargumenttia funktiossa, kuten "=vuosi" ("1/1/31"), Excel tulkitsee vuoden seuraavasti:

00 – 29 tulkitaan vuosiksi 2000–2029. Esimerkiksi päivämäärää syötettäessä 5/28/19 Excel arvioi päivämäärän olevan 28.5.2019.

30 - 99 tulkitaan vuosiksi 1930–1999. Esimerkiksi päivämäärää syötettäessä 5/28/98 Excel arvioi päivämäärän olevan 28. toukokuuta 1998.

Microsoft Windowsissa voit muuttaa tapaa, jolla vuoden kaksi numeroa tulkitaan kaikille asennetut ohjelmat Windows.

Windows 10

Ohjauspaneeli ja valitse sitten Ohjauspaneeli.

Luvussa kello, kieli ja alue klikkaus .

Napsauta Kieli-kuvaketta ja alueelliset standardit.

Valintaikkunassa alueella napsauta painiketta Lisävaihtoehdot.

Avaa välilehti Päivämäärä .

Kentällä

Napsauta painiketta OK.

Windows 8

Hae Hae), kirjoita hakukenttään Ohjauspaneelin elementti Ohjauspaneeli.

Luvussa kello, kieli ja alue klikkaus .

Valintaikkunassa alueella napsauta painiketta Lisävaihtoehdot.

Avaa välilehti Päivämäärä .

Kentällä Kun olet syöttänyt vuoden kaksi numeroa, tulkitse se vuodeksi välillä kehyksiä, muuttaen silmäluomen ylärajaa.

Kun yläraja muuttuu, vuosi vaihtuu automaattisesti alarajaksi.

Napsauta painiketta OK.

Windows 7

Napsauta painiketta alkaa ja valitse Ohjauspaneeli.

Valitse tavara.

Valintaikkunassa alueella napsauta painiketta Lisävaihtoehdot.

Avaa välilehti Päivämäärä .

Kentällä Kun olet syöttänyt vuoden kaksi numeroa, tulkitse se vuodeksi välillä kehyksiä, muuttaen silmäluomen ylärajaa.

Kun yläraja muuttuu, vuosi vaihtuu automaattisesti alarajaksi.

Napsauta painiketta OK.

Muuta päivämäärän oletusmuotoa näyttämään vuosi nelinumeroisena

Kun syötät päivämääriä työkirjaan, päivämäärät muotoillaan oletusarvoisesti näyttämään kaksinumeroisia vuosia. Kun muutat päivämäärän oletusmuodon toiseen muotoon tällä toimenpiteellä, työkirjaan aiemmin syötettyjen päivämäärien näyttö muuttuu muotoon uusi muoto, jota ei ole vielä alustettu "" -valintaikkunassa Solumuoto" "(välilehdellä Koti ryhmässä määrä napsauta painiketta soittaa valintaikkunaan).

Windows 10

Kirjoita tehtäväpalkin hakukenttään Ohjauspaneeli ja valitse sitten Ohjauspaneeli.

Luvussa kello, kieli ja alue klikkaus muuttaa päivämäärän, kellonajan ja numeromuotoja .

Napsauta Kieli-kuvaketta ja alueelliset standardit.

Valintaikkunassa alueella napsauta painiketta Lisävaihtoehdot.

Avaa välilehti Päivämäärä .

Lyhyellä listalla Päivämäärämuoto

Napsauta painiketta OK.

Windows 8

Pyyhkäise näytön oikeasta reunasta, napauta Hae(tai jos käytät hiirtä, siirrä hiiri näytön oikeaan yläkulmaan, siirrä se alas ja napsauta Hae), kirjoita hakukenttään Ohjauspaneelin elementti ja paina sitten tai Valitse Ohjauspaneeli.

Luvussa kello, kieli ja alue klikkaus muuttaa päivämäärän, kellonajan tai numeromuotoja.

Valintaikkunassa alueella napsauta painiketta Lisävaihtoehdot.

Avaa välilehti Päivämäärä .

Lyhyellä listalla Päivämäärämuoto valitse nelinumeroinen muoto vuodelle (yyyy).

Napsauta painiketta OK.

Windows 7

Napsauta painiketta alkaa ja valitse Ohjauspaneeli.

Valitse esine kieli- ja aluestandardit.

Valintaikkunassa alueella napsauta painiketta Lisävaihtoehdot.

Avaa välilehti Päivämäärä .

Lyhyellä listalla Päivämäärämuoto valitse nelinumeroinen muoto vuodelle (yyyy).

Napsauta painiketta OK.

Päivämääräjärjestelmän muuttaminen Excelissä

Päivämääräjärjestelmä muuttuu automaattisesti, kun avaat toisella alustalla luodun dokumentin. Jos esimerkiksi työskentelet Excelissä ja avaat Excel for Macissa luodun asiakirjan, " päivämääräjärjestelmä 1904" asennetaan automaattisesti.

Voit muuttaa päivämääräjärjestelmää noudattamalla alla olevia ohjeita.

Valitse Tiedosto > Vaihtoehdot > Lisäksi.

Luvussa tätä kirjaa laskettaessa valitse haluamasi kirja ja valitse sitten valintaruutu tai poista valinta käytä 1904 päivämääräjärjestelmää .

Ongelma: Ongelmia päivämäärien kanssa eri päivämääräjärjestelmiä käyttävien kirjojen välillä

Saatat kohdata ongelmia, kun kopioit ja liität päivämääriä sekä luot ulkoisia linkkejä kirjojen välille kahden perusteella erilaisia ​​järjestelmiä Päivämäärä Päivämäärät voidaan tulostaa neljä vuotta aikaisemmin tai myöhemmin kuin odotettu päivämäärä. Näitä ongelmia saattaa ilmetä, jos käytät Excel for Windowsia, Excel for Macia tai molempia.

Jos esimerkiksi kopioit päivämäärän 5. heinäkuuta 2007 työkirjasta, joka käyttää 1900 päivämääräjärjestelmää, ja liität sen sitten työkirjaan, joka käyttää 1904 päivämääräjärjestelmää, päivämääränä näkyy 5. heinäkuuta 2011, joka on 1 462 päivää. myöhemmin. Lisäksi, jos kopioit päivämäärän 5. heinäkuuta 2007 työkirjasta, joka käyttää 1904 päivämääräjärjestelmää, ja liität sen sitten työkirjaan, joka käyttää 1900 päivämääräjärjestelmää, päivämäärä on 4. heinäkuuta 2003, mikä on 1 462 päivää aikaisemmin . Yleistä tietoa löytyy Excelin Päivämääräjärjestelmät-osiosta.

Korjaa kopiointi- ja liittämisvirhe

Jos haluat asettaa päivämääräksi neljä vuotta ja yksi päivä myöhemmin, lisää siihen 1 462

Sheet1!$1+1462

Päivämäärän asettaminen vähintään neljäksi vuodeksi varhainen versio, vähennä siitä 1 462. Esimerkki

Sheet1!$1-1462 A$

lisäinformaatio

Voit aina esittää kysymyksen Excel Tech Community -asiantuntijalle, pyytää apua Answers-yhteisöstä ja myös ehdottaa uusi ominaisuus tai parannus Excel User Voice -verkkosivustolla

Yksinkertaisin ja nopea tapa Nykyisen päivämäärän tai kellonajan syöttäminen soluun on painamalla pikanäppäinyhdistelmää CTRL+“;” (nykyinen päivämäärä) ja CTRL+SHIFT+";" (nykyinen aika).

On paljon tehokkaampaa käyttää TODAY()-funktiota. Loppujen lopuksi se ei vain aseta, vaan myös päivittää automaattisesti solun arvon joka päivä ilman käyttäjän toimia.

Kuinka laittaa nykyinen päivämäärä Exceliin

Lisää nykyinen päivämäärä Exceliin käyttämällä TODAY()-funktiota. Voit tehdä tämän valitsemalla työkalun "Kaavat" - "Päivämäärä ja aika" - "TÄNÄÄN". Tämä toiminto ei ole argumentteja, joten voit kirjoittaa soluun: "=TÄNÄÄN()" ja painaa ENTER.

Nykyinen päivämäärä solussa:

Jos solun on tarpeen päivittää automaattisesti paitsi nykyisen päivämäärän myös kellonajan arvo, on parempi käyttää "=TDATE()"-toimintoa.

Nykyinen päivämäärä ja aika solussa.



Nykyisen päivämäärän asettaminen Excelissä ylä- ja alatunnisteisiin

Nykyisen päivämäärän lisääminen Exceliin toteutetaan useilla tavoilla:

1. Asettamalla ylä- ja alatunnisteasetukset. Tämän menetelmän etuna on, että nykyinen päivämäärä ja kellonaika leimataan kaikille sivuille samanaikaisesti.
2. TODAY()-funktion käyttäminen.
3. Käyttämällä pikanäppäinyhdistelmää CTRL+; – nykyisen päivämäärän asettaminen ja CTRL+SHIFT+; – nykyisen ajan asettaminen. Haitta - tässä menetelmässä solun arvoa ei päivitetä automaattisesti nykyisiin ilmaisimiin dokumenttia avattaessa. Mutta joissakin tapauksissa tiedon puute on etu.
4. VBA-makrojen käyttäminen ohjelmakoodin funktioiden avulla: Date();Time();Now().

Ylä- ja alatunnisteiden avulla voit asettaa nykyisen päivämäärän ja kellonajan tulostimelle tulostettavan asiakirjan sivujen ylä- tai alareunaan. Lisäksi alatunnisteen avulla voimme numeroida asiakirjan kaikki sivut.

Voit määrittää nykyisen päivämäärän Excelissä ja sivunumerointia käyttämällä ylä- ja alatunnisteita seuraavasti:

Ylä- ja alatunnisteiden avulla voimme tehdä muutakin kuin vain asettaa päivämääriä ja sivunumeroita. Voit myös lisätä paikan raportista vastaavan henkilön allekirjoitukselle. Muokataan nyt esimerkiksi sivun vasenta alaosaa ylä- ja alatunnistealueella.