Tässä artikkelissa opimme kuinka ryhmitellä päivämäärät Excelin päivien lukumäärän mukaan.
Skenaario:
Ongelma on, kun pivot -taulukossa on joukko päivämääräkenttiä. Käyttäjä haluaa ryhmitellä päivämääräkentän joidenkin päivien lukumäärän mukaan vuosien tai erillisten päivämäärien sijaan. Esimerkiksi tuotteiden tuonnin ja viennin ryhmittäminen Excelissä joka 60 päivä. Tai ryhmitellä työntekijätiedot kuukausien tai viikkojen mukaan Excelissä. Tällaisiin ongelmiin käytämme pivot -taulukoita ja ryhmittelyvaihtoehtoja. Yhdistelmätoimintoa käyttäville käyttäjille on toinen tapa. Ymmärretään nämä kaksi prosessia yksi kerrallaan.
Ryhmittele pivot -taulukossa Excelissä
Valitse tiedot> Lisää> Pivot -taulukko> (Lisää uusi pivot -taulukko)> Siirry pivot -taulukkoon lisää päivämääräkenttä riveille> Napsauta hiiren kakkospainikkeella päivämäärän arvoa> Ryhmä…> Valitse kuukaudet tai päivät> Vahvista napsauttamalla OK
Vaihtoehtoinen tapa aggregaattitoiminnolla
Lisää aloituspäivämäärä ja lisäyssolu jokaiseen 60: n väliin. Käytä excel -aggregaattikaavaa, jolla on useita ehtoja.
Esimerkki:
Kaikkien näiden ymmärtäminen voi olla hämmentävää. Ymmärrämme, miten toimintoa käytetään esimerkin avulla. Tässä on otos osavaltioiden myynnistä ja voitoista. Meidän on ryhmiteltävä tiedot 60 päivän (noin 2 kuukauden) mukaan.
Lajittele tiedot päivämääräkentän mukaan. Valitse taulukko ja lisää pivot -taulukko tietoja varten. Lisää päivämäärä riveille ja myynti ja voitto arvoihin.
Kuten näette, päivämääräkenttä on vuoden muodossa. Mutta haluamme ryhmitellä 60 päivän mukaan. Joten napsauta hiiren kakkospainikkeella päivämäärän arvoa> Valitse ryhmä…
Napsauta OK.
Päivämäärä on ryhmitelty 60 päivän välein sekä myynti- ja voittoarvo.
Tämä on helpoin tapa ryhmitellä päivämäärät kuukausittain. Mutta jos tarvitset yllä olevia tietoja sumifs -kaavan avulla, se vie enemmän aikaa, mutta on kätevää käyttää toimintoja tehokkaasti.
Vaihtoehtoinen tapa käyttää SUMIFS -kaavaa
Tee samoista tiedoista uusi taulukko, jossa on aloituspäivämäärä, ja lisää aloituspäivään 59 päivää alla olevan kuvan mukaisesti.
Ja A5 -solussa käytä = B4+1 saadaksesi seuraavan päivämäärän. Kopioi ja liitä nyt B4 -solukaava, kunnes se saa lopetuspäivän. Kopioi ja liitä A5 -solukaava päättymisajanjaksoon.
Käytä nyt sumifs -kaavaa
= SUMIFS (summa -alue, päivämääräsarake taulukosta, "> =" & A4, päivämääräsarake taulukosta, "<=" & B4)
Suodata tyhjä tai 0 -arvoinen solu saadaksesi sama kenttä, jonka saimme pivot -taulukon avulla.
Pivot -taulukossa on erilaisia koostefunktioita, kuten keskiarvo, luku, summa, maksimi, min, var, stdev, mutta Excel -toiminnot AVERAGEIFS, COUNTIFS, SUMIFS, MAXIFS ja MINIFS. MAXIFS ja MINIFS ovat päivitystoimintoja Excel 2016: n tai uuden Excel 365: n yläpuolella.
Tässä on kaikki havaintohuomautukset, joissa käytetään Excelin selitettyä menetelmää
Huomautuksia:
- Valitse tai käytä pivot -taulukkomenetelmää, se on helppo ja siinä on monia toimintoja.
- Excel tallentaa päivämäärät ja kellonajat numeroina, ja toiminto käyttää sitä laskennassa. Siksi on suositeltavaa käyttää päivämääriä soluviittauksina tai käyttää DATE -funktiota funktion suoran argumentin sijasta. Tarkista päivämääräarvojen validointi, muuten funktio palauttaa #ARVO! Virhe.
- Käytä lainausmerkkejä sumifs -kaavalla "> =" ja "<=".
- MAXIFS ja MINIFS uusi ja päivitetty versio MIN ja MAX ehdoilla. Tai käytä taulukkokaavaa, jossa MAX ja MIN ovat käytössä MINIFS- ja MAXIFS -vanhemmissa Excel -versioissa.
Toivottavasti tämä artikkeli päivämäärien ryhmittelystä Excelin päivien lukumäärän mukaan on selittävä. Täältä löydät lisää artikkeleita päivämääräarvojen ja niihin liittyvien Excel -kaavojen laskemisesta. Jos pidit blogistamme, jaa se ystävillesi Facebookissa. Voit myös seurata meitä Twitterissä ja Facebookissa. Haluaisimme kuulla sinusta, kerro meille, kuinka voimme parantaa, täydentää tai innovoida työtämme ja parantaa sitä sinulle. Kirjoita meille sähköpostitse.
Excelin pivot -taulukot : Pivot -taulukot ovat yksi tehokkaimmista työkaluista, ja joka tietää kaikki pivot -taulukoiden ominaisuudet, voi lisätä tuottavuuttaan eksponentiaalisesti. Tässä artikkelissa opimme kaiken pivot -taulukoista yksityiskohtaisesti.
Laske lomia päivämäärien välillä Excelissä : laskea ei -työpäivät kahden annetun päivämäärän välillä käyttämällä DATEDIF- ja NETWORKDAYS -funktiota Excelissä.
Pivot -taulukon ehdollinen muotoilu : Pivot -taulukoiden ehdollinen muotoilu on sama kuin normaalien tietojen ehdollinen muotoilu. Sinun on kuitenkin oltava varovainen muotoillessasi ehdollisia pivot -taulukoita, kun tiedot muuttuvat dynaamisesti.
Kuinka muuntaa päivämäärä tekstiksi Excelissä : Tässä artikkelissa opimme, miten teksti muutetaan päivämääräksi, mutta miten Excel -päivämäärä muutetaan tekstiksi. Excel -päivämäärän muuntamiseksi tekstiksi meillä on muutamia tekniikoita.
Poimi päiviä päivämäärien väliin jättämättä Excelin vuodet huomiotta : Laske päivät kahden päivämäärän välillä käyttämällä Excelin eri toimintoja ja matemaattisia toimintoja.
Laske syntymäpäivät kuukausittain Excelissä : Laske tietyn kuukauden päivämäärät Excelin SUMPRODUCT ja MONTH -funktiolla.
NETWORKDAYS -toiminnon käyttäminen Excelissä : Palauttaa kahden päivämäärän väliset työpäivät NETWORKDAYS -funktiolla.
Suosittuja artikkeleita:
IF -toiminnon käyttäminen Excelissä : Excelin IF -käsky tarkistaa ehdon ja palauttaa tietyn arvon, jos ehto on TOSI, tai palauttaa toisen arvon, jos EPÄTOSI.
VLOOKUP -toiminnon käyttäminen Excelissä : Tämä on yksi eniten käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään arvon etsimiseen eri alueilta ja taulukoilta.
SUMIF -toiminnon käyttäminen Excelissä : Tämä on toinen kojelaudan olennainen toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.
COUNTIF -toiminnon käyttäminen Excelissä : Laske arvot olosuhteilla tämän hämmästyttävän toiminnon avulla. Sinun ei tarvitse suodattaa tietoja laskeaksesi tiettyjä arvoja. Laskutoiminto on välttämätön kojelaudan valmistelemiseksi.