Да речем, че трябва да управлявате датите на срещите във файл на Excel. Сега искате да видите колко дати (срещи) има в текущия месец, следващия месец или предходния месец. Обикновено ще използвате филтри за това. Но това не е решение.
Можем да преброим броя на текущите дати от диапазон на Excel, използвайки една формула. Той ще се актуализира автоматично и вече няма да е необходимо да използвате филтри за преброяване на дати.
Има три формули, които можете да използвате. Те се основават на:
1. Използване на COUNTIFS с EOMONTH и TODAY функция
2. Използване на COUNT и IF с функции MONTH и TODAY
3. Използване на SUMPRODUCT с функции МЕСЕЦ и ДНЕС
Нека ги проверим един по един.
Метод 1. Пребройте датите в текущия месец с помощта на Excel COUNTIFS с функции EOMONTH и TODAY
Общата формула е
| = COUNTIFS (дати, "> =" & EOMONTH (ДНЕС (),-1)+1, дати, "<=" & EOMONTH (TODAY (), 0)) |
Дати:Това е диапазонът, който съдържа датите. Това може да бъде диапазон или именуван диапазон.
EOMONTH (ДНЕС (),-1) +1:Това за да получите първата дата на текущия месец. Можете да коригирате този раздел, за да получите първата дата на следващия месец или последния месец, като промените съответно -1 на 0 и 1. Увеличете броя и увеличете отместването на месеца.
EOMONTH (ДНЕС (), 0):Това е, за да получите последната дата от текущия месец. Ако увеличите броя в този сегмент, получавате последните дати следващия месец, ако намалите броя, получавате последната дата от предходните месеци.
Използвайте горните два сегмента, за да контролирате месеца.
Пример: Вземете срещи, насрочени за текущия месец
Тук имаме таблица с датите на срещата. Трябва да извлечем броя дати от диапазона, планиран за текущия месец.
Напишете тази формула:
| = COUNTIFS (B2: B12, "> =" & EOMONTH (ДНЕС (),-1)+1, B2: B12, "<=" & EOMONTH (TODAY (), 0)) |
Това връща 5, тъй като има 5 дати от февруари 2020 г.
Как работи?
По принцип искаме да броим дати, по -големи или равни на първата дата на текущия месец и по -малка или равна на последния ден от текущия месец. Затова използваме функцията COUNTIF.
Сега трябва само да оценим първата дата на текущия месец и последната дата на текущия месец. Правим го с помощта на функциите на Excel EOMONTH и TODAY.
Функцията EOMONTH връща последната дата от месеца на предоставената дата. Можем да контролираме месеца преди и след като използваме следващия аргумент. Ние предоставяме днешната дата, използвайки функцията TODAY. Сега EOMONTH (TODAY (),-1) +1 изразът връща първата дата на текущия месец (прочетете подробно за функцията EOMONTH тук). EOMONTH (TODAY (), 0) връща последната дата на текущия месец.
В крайна сметка формулата се превръща в проста формула COUNTIFS.
| = COUNTIFS (B2: B12, "> =" & 43862, B2: B12, "<=" & 43890) |
Тази формула връща датите за броене на текущия месец в диапазон B2: B12.
Метод 2. Пребройте датите в текущия месец, като използвате COUNT и IF с функции MONTH, YEAR и TODAY
Ако не искате да използвате функцията EOMONTH, можете да използвате тази формула, за да преброите датите, които се случват в текущия месец.
Обща формула:
| = COUNT (АКО (МЕСЕЦ (дати) & ГОДИНА (дати) = МЕСЕЦ (ДНЕС ()) & ГОДА (ДНЕС ()), 1, "")) |
Дати:Това е диапазонът, който съдържа датите. Това може да бъде диапазон или именуван диапазон.
Нека използваме тази обща формула в горния пример. Формулата ще бъде:
| = COUNT (АКО (МЕСЕЦ (B2: B12) & ГОДИНА (B2: B12) = МЕСЕЦ (ДНЕС ()) & ГОДИНА (ДНЕС ()), 1, "")) |
Как работи?
TheМЕСЕЦ (B2: B12) & ГОДИНА (B2: B12) part връща масив, който съдържа месеца и годината на всяка дата. Масивът ще бъде
{"32020";"22020";"32020";"32020";"12020";"22020";"22020";"32020";"22020";"22020";"32020"}.
TheМЕСЕЦ (ДНЕС ()) & ГОДИНА (ДНЕС ())part създава низ, който съдържа номера и годината на текущия месец. В този пример това е "22020". Тази стойност се сравнява с всяка стойност в горния масив, като се използва знак за равенство (=). Това връща масив от TRUE и FALSE.
{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}
Сега за всяка стойност TRUE функцията IF връща 1, а за FALSE не връща нищо ("").
{"";1;"";"";"";1;1;"";1;1;""}
И накрая, функцията COUNT брои числовите стойности от масива и връща датите на текущия месец от избрания диапазон.
Метод 3. Преброяване на датите в текущия месец с помощта на SUMPRODUCT с функции MONTH, YEAR и TODAY
Функцията SUMPRODUCT е невероятна, когато става въпрос за преброяване и сумиране на стойности с луди критерии.
В този метод използваме логическа логика, за да преброим датите на текущия месец в диапазон.
| = SUMPRODUCT (-(МЕСЕЦ (дати) & ГОДА (дати) = МЕСЕЦ (ДНЕС ()) & ГОДИНА (ДНЕС ()))) |
Дати:Това е диапазонът, който съдържа датите. Това може да бъде диапазон или именуван диапазон.
Когато използваме тази функция в нашия пример, получаваме абсолютно същия отговор.
| = SUMPRODUCT (-(МЕСЕЦ (B2: B12) & ГОДИНА (B2: B12) = МЕСЕЦ (ДНЕС ()) & ГОДИНА (ДНЕС ()))) |
Как работи?
(МЕСЕЦ (B2: B12) & ГОДИНА (B2: B12) = МЕСЕЦ (ДНЕС ()) & ГОДИНА (ДНЕС ())):Този сегмент прави същото като в метода COUNT и IF и връща масив от TRUE и FALSE.
{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}
Извън това твърдение имаме двоен отрицателен оператор (-) за преобразуване на тези ИСТИНСКИ и ЛЪЖНИ стойности в 1 и 0. И накрая, формулата е опростена до:
СУМПРОДУКТ ({0; 1; 0; 0; 0; 1; 1; 0; 1; 1; 0})
Функцията SUMPRODUCT обобщава този масив и връща броя на датите с номера на текущия месец в диапазона.
Така че да, момчета, това са начините, по които две броят дати на текущия месец. Изберете този, който ви подхожда най -добре. Надявам се, че беше обяснително и полезно. Ако имате някакви съмнения по тази тема, попитайте в секцията за коментари по -долу. Ще се радвам да чуя от вас.
Как да отброявате оставащите дни в Excel | За да преброим оставащите дни, използваме проста формула за изваждане в Excel. Ако искате да преброите оставащите дни от текущата дата, тогава използваме функцията TODAY.
Как да се изчислят дни, месеци и години| За изчисляване на дни, месец и година Excel има три функции, наречени ДЕН, МЕСЕЦ и ГОДИНА. Тези функции получават деня, месеца и годината от дадената дата.
Как да изчислим оставащите дни в месеца в Excel |За да изчислим оставащите дни в месеца, използваме функцията EOMONTH. Изваждаме текущата дата от датата на края на месеца.
Пребройте датите на раждане от диапазона по месеци Excel| Използване на функция SUMPRODUCT и MONTH. Тази функция ще ни уведоми колко рождени дати има в определен месец.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност | Бъдете по -бързи в задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате функцията Excel VLOOKUP| Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате Excel COUNTIF функция| Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да преброите конкретна стойност. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
