Tarkime, kad turite valdyti susitikimų datas „Excel“ faile. Dabar norite sužinoti, kiek datų (susitikimų) yra einamąjį mėnesį, kitą mėnesį ar praėjusį mėnesį. Paprastai tam naudosite filtrus. Bet tai nėra sprendimas.
Galime suskaičiuoti dabartinių datų skaičių iš „Excel“ diapazono naudodami vieną formulę. Jis bus automatiškai atnaujinamas ir jums nebereikės naudoti filtrų datoms skaičiuoti.
Galite naudoti tris formules. Jie pagrįsti:
1. COUNTIFS naudojimas su EOMONTH ir TODAY funkcija
2. COUNT ir IF naudojimas su MONTH ir TODAY funkcija
3. SUMPRODUCT naudojimas su MONTH ir TODAY funkcija
Patikrinkime juos po vieną.
Metodas 1. Skaičiuokite einamojo mėnesio datas naudodami „Excel COUNTIFS“ su EOMONTH ir TODAY funkcija
Bendra formulė yra
| = COUNTIFS (datos, "> =" & EOMONTH (TODAY (),-1)+1, datos, "<=" & EOMONTH (TODAY (), 0)) |
Datos:Tai yra diapazonas, kuriame yra datos. Tai gali būti diapazonas arba pavadintas diapazonas.
EOMONTH (TODAY (),-1) +1:Norėdami gauti pirmą einamojo mėnesio datą. Galite pakoreguoti šį skyrių, kad gautumėte kito mėnesio ar praėjusio mėnesio pirmą datą, atitinkamai pakeisdami -1 į 0 ir 1. Padidinkite mėnesio skaičių ir padidinkite poslinkį.
EOMONTH (TODAY (), 0):Tai yra paskutinė einamojo mėnesio data. Jei padidinsite šio segmento skaičių, kitą mėnesį gausite paskutines datas, jei sumažinsite - paskutinę praėjusių mėnesių datą.
Naudokite aukščiau nurodytus du segmentus, kad galėtumėte valdyti mėnesį.
Pavyzdys: suplanuokite susitikimus, suplanuotus einamąjį mėnesį
Čia mes turime lentelę, kurioje yra susitikimo datos. Turime gauti datų skaičių iš diapazono, suplanuoto einamąjį mėnesį.
Parašykite šią formulę:
| = COUNTIFS (B2: B12, "> =" & EOMONTH (TODAY (),-1)+1, B2: B12, "<=" & EOMONTH (TODAY (), 0)) |
Tai grąžina 5, nes 2020 m. Vasario mėn. Yra 5 datos.
Kaip tai veikia?
Iš esmės norime skaičiuoti datas, kurios yra didesnės arba lygios einamojo mėnesio pirmajai datai ir mažesnės arba lygios paskutinei einamojo mėnesio dienai. Taigi mes naudojame funkciją COUNTIF.
Dabar turime įvertinti tik pirmą einamojo mėnesio datą ir paskutinę einamojo mėnesio datą. Mes tai darome naudodami „Excel“ funkcijas EOMONTH ir TODAY.
Funkcija EOMONTH grąžina paskutinę pateiktos datos mėnesio datą. Mes galime kontroliuoti mėnesį prieš ir po kito argumento. Šiandienos datą pateikiame naudodami funkciją TODAY. Dabar EOMONTH (TODAY (),-1) +1 teiginys grąžina pirmąją einamojo mėnesio datą (išsamiai apie EOMONTH funkciją skaitykite čia). EOMONTH (TODAY (), 0) grąžina paskutinę einamojo mėnesio datą.
Galų gale formulė tampa paprasta COUNTIFS formule.
| = COUNTIFS (B2: B12, "> =" & 43862, B2: B12, "<=" & 43890) |
Ši formulė grąžina einamojo mėnesio skaičiavimo datas diapazone B2: B12.
2 metodas. Skaičiuokite einamojo mėnesio datas naudodami COUNT ir IF su MONTH, YEAR ir TODAY funkcija
Jei nenorite naudoti EOMONTH funkcijos, galite naudoti šią formulę, kad suskaičiuotumėte einamojo mėnesio datas.
Bendra formulė:
| = COUNT (IF (MONTH (datos) & YEAR (datos) = MONTH (TODAY ()) & YEAR (TODAY ()), 1, "")) |
Datos:Tai yra diapazonas, kuriame yra datos. Tai gali būti diapazonas arba pavadintas diapazonas.
Panaudokime šią bendrąją formulę aukščiau pateiktame pavyzdyje. Formulė bus tokia:
| = SKAITAS (JEI (MĖNESIS (B2: B12) ir METAI (B2: B12) = MĖNESIS (ŠIANDIEN ()) ir METAI (ŠIANDIEN ()), 1, "")) |
Kaip tai veikia?
TheMĖNESIS (B2: B12) ir METAI (B2: B12) dalis grąžina masyvą, kuriame yra kiekvienos datos mėnuo ir metai. Masyvas bus
{"32020";"22020";"32020";"32020";"12020";"22020";"22020";"32020";"22020";"22020";"32020"}.
TheMONTH (TODAY ()) & YEAR (TODAY ())dalis sukuria eilutę, kurioje yra einamojo mėnesio mėnesio numeris ir metai. Šiame pavyzdyje tai yra „22020“. Ši reikšmė lyginama su kiekviena aukščiau esančio masyvo verte, naudojant ženklą lygybė (=). Tai grąžina TIESA ir NETIESA masyvą.
{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}
Dabar kiekvienai TRUE reikšmei IF funkcija grąžina 1, o FALSE - nieko ("").
{"";1;"";"";"";1;1;"";1;1;""}
Galiausiai funkcija COUNT suskaičiuoja skaitines reikšmes iš masyvo ir grąžina dabartinio mėnesio datas iš pasirinkto diapazono.
3 metodas. Skaičiuokite einamojo mėnesio datas naudodami SUMPRODUCT su MONTH, YEAR ir TODAY funkcija
SUMPRODUCT funkcija yra nuostabi, kai reikia skaičiuoti ir susumuoti vertes pagal beprotiškus kriterijus.
Taikydami šį metodą, mes naudojame loginę logiką, kad skaičiuotume einamojo mėnesio intervalo datas.
| = SUMPRODUCT (-(MONTH (datos) ir YEAR (datos) = MONTH (TODAY ()) & YEAR (TODAY ()))) |
Datos:Tai yra diapazonas, kuriame yra datos. Tai gali būti diapazonas arba pavadintas diapazonas.
Kai naudojame šią funkciją savo pavyzdyje, gauname tą patį atsakymą.
| = SUMPRODUKTAS (-(MĖNESIS (B2: B12) ir METAI (B2: B12) = MĖNESIS (ŠIANDIEN ()) ir METAI (ŠIANDIEN ()))) |
Kaip tai veikia?
(MĖNESIS (B2: B12) ir METAI (B2: B12) = MĖNESIS (ŠIANDIEN ()) ir METAI (ŠIANDIEN ())):Šis segmentas daro tą patį, kaip ir COUNT ir IF metodu, ir pateikia masyvą TRUE ir FALSE.
{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}
Be šio teiginio, turime dvigubą neigiamą operatorių (-), kuris šias TIKRAS ir FALSE reikšmes pavers 1s ir 0s. Galiausiai turime supaprastintą formulę:
SUMPRODUCT ({0; 1; 0; 0; 0; 1; 1; 0; 1; 1; 0})
Funkcija SUMPRODUCT apibendrina šį masyvą ir grąžina einamojo mėnesio skaičių datų skaičių diapazone.
Taigi taip, vaikinai, šitaip du skaičiuoja einamojo mėnesio datas. Pasirinkite tą, kuris jums labiausiai tinka. Tikiuosi, kad tai buvo paaiškinanti ir naudinga. Jei turite kokių nors abejonių dėl šios temos, paklauskite žemiau esančiame komentarų skyriuje. Man bus malonu išgirsti jus.
Kaip suskaičiuoti likusias dienas „Excel“ | Likusioms dienoms skaičiuoti „Excel“ naudojame paprastą atimties formulę. Jei norite suskaičiuoti likusias dienas nuo dabartinės datos, mes naudojame funkciją TODAY.
Kaip apskaičiuoti dienas, mėnesius ir metus| Norėdami apskaičiuoti dienas, mėnesį ir metus, „Excel“ turi tris funkcijas, vadinamas DAY, MONTH ir YEAR. Šios funkcijos gauna dieną, mėnesį ir metus nuo nurodytos datos.
Kaip apskaičiuoti likusias mėnesio dienas „Excel“ |Likusioms mėnesio dienoms apskaičiuoti naudojame funkciją EOMONTH. Iš mėnesio pabaigos datos atimame dabartinę datą.
„Excel“ skaičiuokite gimimo datas pagal diapazoną| Naudojant SUMPRODUCT ir MONTH funkcijas. Ši funkcija leis mums žinoti, kiek gimimo datų yra konkretų mėnesį.
Populiarūs straipsniai:
50 „Excel“ nuorodų, skirtų produktyvumui padidinti | Greičiau atlikite savo užduotį. Šie 50 sparčiųjų klavišų leis dar greičiau dirbti naudojant „Excel“.
Kaip naudotis „Excel VLOOKUP“ funkcija| Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama norint ieškoti vertės iš įvairių diapazonų ir lapų.
Kaip naudotis „Excel“ Funkcija COUNTIF| Naudodami šią nuostabią funkciją, suskaičiuokite reikšmes su sąlygomis. Norint suskaičiuoti konkrečią vertę, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.
Kaip naudotis „SUMIF“ funkcija „Excel“ | Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.