Selles artiklis õpime, kuidas kuupäevi Excelis päevade arvu järgi rühmitada.
Stsenaarium:
Probleem on siis, kui meil on liigendtabelis kuupäevaväljade komplekt. Ja kasutaja soovib kuupäevavälja rühmitada mõne päeva asemel aastate või diskreetsete kuupäevade järgi. Näiteks toodete impordi ja ekspordi rühmitamine Excelis iga 60 päeva tagant. Või rühmitage Excelis töötajate andmed kuude või nädalate kaupa. Selliste probleemide korral kasutame liigendtabeleid ja rühmitamisvalikuid. Liitmisfunktsiooni kasutavate kasutajate jaoks on veel üks viis. Mõistame neid kahte protsessi ükshaaval.
Rühmitage Exceli pöördtabelis
Valige andmed> Lisa> Pivot -tabel> (Sisestage uus liigendtabel)> Minge liigendtabelisse kuupäevavälja lisamiseks ridadesse> Paremklõpsake kuupäeva väärtust> Grupp…> Valige kuud või päevad> Kinnitamiseks klõpsake nuppu OK
Alternatiivne viis koondfunktsiooniga
Lisage alguskuupäev ja juurdekasvu lahter iga 60. intervalliga. Kasutage Exceli koondvalemit, millel on mitu kriteeriumi.
Näide:
Kõiki neid võib segadusse ajada. Mõistame, kuidas funktsiooni kasutada näite abil. Siin on meil näidisandmed müügi ja kasumi kohta osariikide kaupa. Peame andmed rühmitama 60 päeva (umbes 2 kuud) järgi.
Sorteeri andmed kuupäevavälja järgi. Valige tabel ja sisestage andmete jaoks pöördetabel. Lisage ridadele kuupäev ja väärtustele müük ja kasum.
Nagu näete, on kuupäeva väli aastavormingus. Kuid me tahame rühmitada 60 päeva kaupa. Nii et paremklõpsake kuupäeva väärtust> Valige rühm…
Klõpsake nuppu OK.
Kuupäev on rühmitatud 60 -päevase intervalliga koos müügi ja kasumi väärtusega.
See on lihtsaim viis kuupäevade rühmitamiseks kuude kaupa. Kui aga vajate ülaltoodud andmeid sumifsi valemi abil, on see aeganõudvam, kuid funktsioone tõhusalt kasutada on mugav.
Alternatiivne viis SUMIFS valemi abil
Samade andmete jaoks tehke uus tabel, mis nimetab alguskuupäeva, ja lisage alguskuupäevale 59 päeva, nagu allpool näidatud.
Ja A5 lahtris kasutage järgmise kuupäeva saamiseks = B4+1. Nüüd kopeerige ja kleepige B4 lahtrivalem, kuni see saab lõppkuupäeva. Ja kopeerige A5 lahtrivalem lõpukuupäevavahemikku.
Nüüd kasutage sumifide valemit
= SUMIFS (summa vahemik, kuupäevade veerg tabelist, "> =" & A4, kuupäevade veerg tabelist, "<=" & B4)
Filtreerige tühi või 0 väärtusega lahter, et saada sama väli, mille saime pöördetabeli abil.
Pivot -tabel pakub teile erinevaid koondfunktsioone, nagu keskmine, arv, summa, max, min, var, stdev, kuid Exceli funktsioonid AVERAGEIFS, COUNTIFS, SUMIFS, MAXIFS ja MINIFS. MAXIFS ja MINIFS on värskendusfunktsioonid eespool Excel 2016 või uus Excel 365.
Siin on kõik vaatlusmärkused, kasutades Exceli selgitatud meetodit
Märkused:
- Eelistage või kasutage pivot tabeli meetodit, see on lihtne ja sellel on palju funktsioone.
- Excel salvestab kuupäevad ja kellaaja numbriteks ning seda kasutab funktsioon arvutamisel. Seega on soovitatav kasutada kuupäevi lahtri viitena või kasutada funktsiooni DATE, selle asemel, et funktsioonile otsest argumenti esitada. Kontrollige kuupäeva väärtuste valideerimist, vastasel juhul tagastab funktsioon #VALUE! Viga.
- Kasutage sumfide valemiga jutumärke "> =" ja "<=" jaoks.
- MAXIFS ja MINIFS MIN ja MAX uus ja uuendatud versioon koos kriteeriumidega. Või kasutage massiivi valemit MAX ja MIN paigas MINIFS ja MAXIFS vanemates Exceli versioonides.
Loodetavasti on see artikkel, kuidas Excelis kuupäevi rühmitada päevade arvu järgi, selgitav. Siit leiate rohkem artikleid kuupäevaväärtuste arvutamise ja sellega seotud Exceli valemite kohta. Kui teile meeldisid meie ajaveebid, jagage seda oma sõpradega Facebookis. Samuti saate meid jälgida Twitteris ja Facebookis. Tahaksime sinust kuulda, andke meile teada, kuidas saaksime oma tööd täiustada, täiendada või uuendada ning muuta see teie jaoks paremaks. Kirjuta meile meilisaidile.
Exceli liigendtabelid : Pivot -tabelid on üks võimsamaid tööriistu ja see, kes teab kõiki pöördtabelite funktsioone, võib oma tootlikkust plahvatuslikult tõsta. Selles artiklis õpime üksikasjalikult kõike liigendtabelite kohta.
Lugege puhkused Excelis kuupäevade vahel : loe kahe tööpäeva vahel tööpäevad, kasutades Excelis funktsiooni DATEDIF ja NETWORKDAYS.
Pivot -tabeli tingimuslik vormindamine : Pivot -tabelite tingimusvorming on sama mis tavaandmete tingimusvorming. Kuid te peate olema ettevaatlik liigendtabelite tingimusliku vormindamise ajal, kuna andmed muutuvad dünaamiliselt.
Kuidas teisendada kuupäev tekstiks Excelis : Selles artiklis õppisime, kuidas teksti kuupäevaks teisendada, kuid kuidas teisendada Exceli kuupäev tekstiks. Exceli kuupäeva tekstiks teisendamiseks on meil mõned tehnikad.
Väljavõtte kuupäevad kuupäevade vahel, ignoreerides Excelis aastaid : loe kahe kuupäeva vahelisi päevi, kasutades Excelis erinevaid funktsioone ja matemaatilist toimingut.
Loendage Excelis sünnikuupäevad kuude kaupa : loendage kuupäevad, mis asuvad konkreetsel kuul, kasutades Exceli funktsiooni SUMPRODUCT ja MONTH.
Funktsiooni NETWORKDAYS kasutamine Excelis : Tagastab tööpäevad kahe antud kuupäeva vahel, kasutades funktsiooni NETWORKDAYS.
Populaarsed artiklid:
Funktsiooni IF kasutamine Excelis : Exceli IF -lause kontrollib tingimust ja tagastab konkreetse väärtuse, kui tingimus on TRUE, või tagastab mõne muu väärtuse, kui FALSE.
Funktsiooni VLOOKUP kasutamine Excelis : See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
Funktsiooni SUMIF kasutamine Excelis : See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.
Funktsiooni COUNTIF kasutamine Excelis : Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea oma andmeid teatud väärtuste loendamiseks filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.