Excel количество кварталов между датами

Здесь я перечислил 5 основных способов, с помощью которых вы можете получить квартал с даты. Каждый из этих методов уникален, и вы можете использовать любой из них в соответствии с вашими потребностями.

1. Получить квартал с помощью функций ROUNDUP и MONTH

Использование комбинации ROUNDUP и MONTH – лучший способ найти четверть даты. Он возвращает квартал в виде числа (например, 1,2,3,4).

Вот формула.

= ROUNDUP (МЕСЯЦ (A1)/3,0)

Здесь мы используем 26 мая 2018 года в качестве даты, а в результате формула вернет 2. Вы можете использовать приведенную ниже формулу, если хотите добавить Q перед числом квартала.

 = «Q -» & ROUNDUP (MONTH (A1)/3,0  ) 

Как работает эта формула

Эта формула состоит из трех частей.

В первой части у нас есть использовал функцию МЕСЯЦ и ссылался на дату, которая возвращает номер месяца.

После этого в вторая часть, мы разделили номер месяца на 3, что дает нам число с десятичными знаками.

Наконец, в третьей части мы использовали функцию ROUNDUP для округления (этого числа с десятичными знаками), что дает нам фактический квартал.

❤️ Вот настоящая вещь:

Вы знаете, что есть четыре квартала и двенадцать месяцев.

Верно?

А если вы делите все числа месяца w с тремя (да, только с 3) вы получите что-то вроде ниже.

И когда вы их округлите вы всегда получите номер квартала месяца.

2. Получить квартал с помощью финансового календаря

В приведенном выше примере мы использовали простую формулу, чтобы отсчитать квартал от даты, но иногда вам нужно использовать финансовый календарь.

А для расчета квартала финансового календаря нам нужно использовать другую формулу, и в этой формуле нам нужно объединить функцию ВЫБОР и МЕСЯЦ.

Вот формула:

 = ВЫБРАТЬ (МЕСЯЦ (A1), 4,4,4,1,1,1,2,2,2  , 3,3,3) 

(Здесь я использовал индийский финансовый год, который начинается 1 апреля и заканчивается 31 марта)

В результате возвращается 1, так как месяц май, а наш год начинается с апреля, а май – второй месяц в году под первый квартал.

Как работает эта формула

Здесь мы объединили две разные функции, и она работает в двух частях.

В первая часть, мы использовали функцию MONTH, чтобы получить номер месяца из даты, и здесь она возвращает 5.

После этого, во второй части , мы использовали функцию для получения числа в соответствии с номером месяца, возвращаемым функцией MONTH.

Здесь вы можете узнать больше о CHOOSE.

Если вы посмотрите, в функции ВЫБОР мы указали 12 разных чисел (4,4,4,1,1,1,2,2,2,3,3,3), поэтому, когда МЕСЯЦ возвращает число, ВЫБОР возвращает число из этот список.

Вот в чем дело:

Для первых трех чисел мы имеет 4, что означает, что если он вернет 1,2 или 3 (январь-март), мы получим 4, а в следующие три месяца мы получим 1 (это потому, что наш финансовый год начинается с апреля).

3. Преобразование даты в квартал с помощью кода VBA

Здесь мы используем код VBA для простого преобразования даты в ее номер квартала.

Вот код:

Чтобы использовать этот код, все, что вам нужно сделать, это добавить его в свой редактор VB.

А затем просто выберите ячейки, в которых нужно преобразовать дату в число квартала, и, наконец, запустите этот код.

вот как работает этот код. ..

Когда вы запускаете этот код, сначала он проверяет, есть ли в ячейке дата или нет, и если это дата, то преобразует ее в номер четверти.

Проверьте это: 100 лучших полезных макросов для новичков VBA

4. Получить дату начала квартала по дате

Давайте забудем о номере квартала и будем использовать дату начала квартала.

Это означает, что если у нас есть дата 26 мая 2018 года, поэтому вместо квартала номер 2 мы можем иметь 01 апреля 2018 года, которое является датой начала первого месяца квартала.

Вот формула, которую нам нужно использовать:

 = DATE (YEAR (A1),  FLOOR.MATH (  МЕСЯЦ (A1) -1,3) +1,1) 

Когда вы вводите эту формулу, она просто возвращает дату начала квартала.

Как работает эта формула

Эта формула состоит из четырех разных частей, которые дают вам дату начала квартала.

В первой части мы использовали функцию MONTH, которая возвращает номер месяца для даты.

Итак, когда MONTH возвращает 5 в качестве номера месяца (тогда минус один из него), во второй части , FLOOR округлите его до 3, а затем прибавьте к нему единицу, чтобы получить 4, что соответствует первому месяцу (апрелю) того же квартала.

После этого, в третьей части, мы использовали функцию ГОД, чтобы получить год от фактической даты.

И в четвертой части мы просто объединили функцию ДАТА, чтобы создать дату, объединяющую значения берутся из первых двух частей.

5. Получить дату окончания квартала из даты

ОК, поэтому вам нужна дата окончания квартала вместо даты начала или числа, и для этого формула который мы можем использовать:

 = DATE (YEAR (A2), ((INT ((MONTH (A2) -1)/3) +1)  * 3) +1,1) -1 

Для 26 мая 2018 года возвращает 30 июня 2018 год, который является последней датой квартала.

Как работает эта формула

В этой формуле у нас четыре разных части.

В первая часть, мы объединили функции INT и MONTH.

MONTH возвращает номер месяца в дате, а затем мы разделили его с 3. После этого INT округлите его до ближайшего целого.

В конце концов, мы умножили его на 3, чтобы получить 6 (июнь), который является месяцем конца квартала, а затем добавили к нему 1.

Проще говоря, эта часть формулы просто преобразует месяц фактической даты в первый месяц следующего квартала.

Во второй части мы использовали функцию ГОД, чтобы получить год от фактической даты.

После этого, в третьей части, просто использовал 1, завершив дату функцией ДАТА.

Извините! еще кое-что.

В четвертой минус 1 из всей формулы, чтобы мы могли иметь последнюю дату квартала вместо первой даты следующего квартала.

Образец файла

➜ загрузите этот образец файла отсюда, чтобы узнать больше

Заключение

Номер квартала в MS EXCEL

​Смотрите также​=ОСТАТ(МЕСЯЦ(A5)-1;3)+1​: Формула для первого​: Добрый день!​

​ Excel».​ «ОК». Получилось так.​Если изменим цифру дней​​Смотрим в таблице​​ активной. Заходим на​ пример.​ дату смотрите в​
​md – считает​

​ Здесь будет записана​ Excel посчитать стаж».​ числовой​
​ или сколько дней​ Условным форматированием.​

​Имея дату (01/02/09), определим​eldarkorneev​ квартала​
​Необходимо посчитать даты​В таблице Excel​ Смотрите ячейку В19.​

​ с 11 на​ результат.​ закладку «Формулы» в​=​ статье «Текущая дата​ дни​ дата в таком​

excel2.ru

Дата конца и начала квартала в MS EXCEL

​Для работы с​-​ осталось до отпуска,​Найдем дату конца квартала,​ квартал, к которому​: vikttur,спасибо большое!​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ(Ч(МЕСЯЦ(D4:D30)={1;2;3}))​ по кварталам и​

​ можно сравнить даты​​ Вместо полной даты​​ 25, то получится​Такой расчет даты удобен​ раздел «Библиотека функций»​ДАТА(ГОД(A13)+2;МЕСЯЦ(A13);ДЕНЬ(A13))​ в Excel».​

​«г.», «мес.», «дн.»​ виде, как мы​ датами, их можно​

excel2.ru

Дата в Excel. Формула.

​это не формат​ посчитать возраст, расчитать​ к которому относится​ она принадлежит (ответ:​Вопрос по оптимизации:​​и массивный вариантКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТ(1/(МЕСЯЦ(D4:D30)={1;2;3}))Для​​ занести в желтые​ из разных столбцов​ стоит только год.​ такая дата.​ тем, что всегда​ и выбираем функцию​Или прибавить сразу всё​2.​ написанные между знаками​ её пропишем в​ записать по–разному, для​ ячейки. Как это​ дату срока оплаты​ заданная дата. Например,​ первый квартал).​
​ при пустой ячейке​ других кварталов поменять​ ячейки количество дат,​ и выделить разные​Другими соответствующими функциями выбрали​Как ввести адрес ячейки​ можно изменить цифру​
​ «Дата и время».​
​ – года, месяцы,​Как прибавить к дате​ & — так​ формуле.​ разных формул. И​ сделать, читайте в​ счета или окончания​ если задана дата​Найдем номер квартала для​ даты (A5) формула​ числа в массивах​ попадающих в определенный​​ даты. Как это​​ месяц (ячейка В20)​ быстро смотрите в​ месяца, не меняя​Или можно открыть​ дни. По такой​ дни в Excel.​
​ будут подписаны года,​В ячейке А6​ формат ячеек с​ статье «Преобразовать текст​ договора, посчитать рабочие​ 05.11.12, то дата​
​ любой даты из​ рассчитывает значение порядкового​eldarkorneev​ квартал.​ сделать, смотрите в​ и день (ячейка​ статье «Сложение, вычитание,​ формулы. Например, фирма​
​ мастер функций кнопкой​ формуле можно рассчитать​Первый способ.​ месяца и дни​ пишем первую дату​ формулами должен быть​ в дату Excel»​ дни и многие,​ конца квартала 31.12.12.​
​ ячейки​ номера месяца в​: Друзья, привет!​Файл приложен.​ статье «Как сравнить​ В21). Формат в​ умножение, деление в​ не успевает сделать​ «Вставить функцию» (на​​ срок службы, срок​
​В примере прибавим​​ в итоговой дате.​​ (начало периода).​
​ определенный. Все это​ тут.​ многие другие действия​ Также найдем дату​B5​ квартале как «1».​Нужна помощь. Автоматизирую​Pelena​ два столбца в​ этих ячейках можно​ Excel».​ заказ вовремя, сроки​ закладке «Формулы») и​
​ окончания договора, проекта,​ 9 месяцев.​ Можно между кавычками​В ячейке В6​ прописано в тексте​Для подсчета дат​ с датами и​ начала соответствующего квартала.​
​. Для этого используем​ Как от этого​ отчетность на работе.​
​: Здравствуйте.​ Excel на совпадения»​ установить другой.​
​В Excel есть​ окончания работы продлеваются.​
​ выбрать категорию «Дата​
​ отпуска, срок оплаты​В ячейке А13​ написать по-разному –​ пишем вторую дату​ –​ нужно сделать таблицу​ временем можно проводить​Пусть в ячейке​
​ формулу (см. файл​​ избавиться?​
​Столкнулся с проблемой:​Сводной можно с​
​ здесь.​Конечно, можно изменить​
​ три функции, которые​ Меняем цифру в​
​ и время».​ счета, многое другое.​
​ пишем дату, к​ полное слово или​ (конец периода).​обращайте внимание.​ в Excel и​ в Excel.​А1​ примера):​Как сделать так,​ для корректной работы​ группировкой по кварталам​​Если, для работы​ формат даты в​
​ выбирают из даты​ ячейке G 13​Теперь выбираем из​Второй способ.​
​ которой будем прибавлять​ просто поставить точку.​​В ячейке С6​​1.​ установить в ней​Все расчеты с​имеется дата 05.11.12.​=ЦЕЛОЕ((МЕСЯЦ(B5)+2)/3)​ чтобы при пустой​ отчета мне необходимо,​sboy​ нужно изменить формат​ ячейке или написать​ или только число,​
​ и формула расчитывает​​ списка функцию «ДАТА».​Пишем формулу с​
​ 9 месяцев.​
​ Смотрите пример.​ пишем формулу:​
​Разница дат в Excel​ формулы. Этим мы​ датами описаны в​ Записав формулу =КОНМЕСЯЦА(A1;ЦЕЛОЕ((МЕСЯЦ(A1)+2)/3)*3-МЕСЯЦ(A1))​
​Выведем номер квартала и​ ячейке с датой,​ чтобы Excel автоматически​: Добрый день.​ даты на текстовый,​ свой формат. Как​ или только месяц,​ новую дату. По​
​ Выходит диалоговое окно.​ помощью функции «ДАТА».​В ячейке D13​В ячейке С6 стоит​=РАЗНДАТ(A6;B6;»y»)&»г.»&РАЗНДАТ(A6;B6;»ym»)&»мес.»&РАЗНДАТ(A6;B6;»md»)&»дн.»​высчитывается так.​​ сейчас и займемся.​ нескольких статьях. Смотрите​ определим дату конца​ год в формате​
​ формула выводила «0»​​ определял порядковый номер​​как вариант формулой​ то смотрите статью​ создать свой формат,​ или год. Это​ такому принципу можно​ В нем пишем:​В ячейке А13​ пишем формулу. Можно​
​ формула, искомая дата.​
​Эту формулу пишем​Столбец А –​
​Как простыми способами​ перечень статей в​ квартала, к которому​ 4 кв. 2010г.​ ???​
​ месяца в квартале​ (пример для 1-го​ «Преобразовать дату в​ смотрите в статье​ функции «ГОД», «МЕСЯЦ»,​ прибавлять, отнимать по​
​«Год» — год​ пишем дату, к​ написать формулу в​А здесь, вместо​ вручную (можно здесь​ пишем дату начала​ прибавить к дате​ конце статьи в​ относится эта дата.​=ЦЕЛОЕ((МЕСЯЦ(B5)+2)/3)&» кв. «&ТЕКСТ(B5;»гггг»)&»г.​
​vikttur​ (1, 2 или​ квартала)​ текст Excel».​ «Как создать пользовательский​ «ДЕНЬ».​
​ отдельности и вместе​ нашей даты.​ которой будем прибавлять.​ соседней ячейке, просто,​
​ букв «г.», «мес.»,​ скопировать, заменить адреса​
​ периода, столбец В​ или вычесть из​ разделе «Другие статьи​Формула =КОНМЕСЯЦА(A1;ЦЕЛОЕ((МЕСЯЦ(A1)+2)/3-1)*3-МЕСЯЦ(A1))+1 вернет дату​ «​:​ 3) исходя из​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИМН($D$4:$D$30;»>31.12.2015″;$D$4:$D$30;»​
​В Excel можно​ формат в Excel»​
​Делаем активной ячейку​
​ года, месяцы и​«Месяц» — пишем​
​ В примере прибавим​ в примере, она​
​ «дн.» просто поставили​ ячеек), п.ч. в​ – пишем дату​ даты дни, месяци​ по этой теме».​ начала, т.е. 01.10.12​Чтобы вывести значение в​=ЕСЛИ(условие;формула;0)​ введенной в ячейку​Nic70y​ считать не только​ тут.​ В19. На закладке​ дни.​ месяц нашей даты​ 3 месяца.​ стоит там –​
​ точку.​ русскоязычной версии Microsoft​ окончания периода. В​ или года, смотрите​Внимание!​ (см. файл примера).​ формате IV кв.​eldarkorneev​
​ даты.​
​:​ даты, но и​
​В Excel есть​ «Формулы» в разделе​Например, прибавим к​ числом, ставим плюс​
​В следующей ячейке​ в ячейке D13.​Если в ячейку В6​ Excel, она не​ столбце А и​
​ в статье «Работа​Если формулы не​Сложить даты в Excel,​ 2011г. запишем формулу:​: vikttur,​Определять порядковый номер​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИМН($D$4:$D$30;»>=»&ДАТА(ПРАВБ(F3;4);ЛЕВБ(F3)*3-2;1);$D$4:$D$30;»​ время. Как высчитывать​
​ функции, которыми можно​ «Библиотека функций» нажимаем​ дате 1 год,​ и пишем адрес​ пишем число месяцев,​ =ДАТА(ГОД(A13);МЕСЯЦ(A13)+9;ДЕНЬ(A13))​ поставим​ видна в перечне​ В устанавливаем формат​ с датами в​ будут работать, проверьте​ вычесть даты, прибавить​=РИМСКОЕ(ЦЕЛОЕ((МЕСЯЦ(B5)-1)/3)+1) & «​Разобрался, для месяца​ квартала я научился,​Dryha​ время, читайте в​
​ сложить данные из​ на функцию «Дата​ 2 месяца и​ ячейки, в которой​ которое будем прибавлять​В формуле все​
​текущую дату​ функций, но работает.​ ячейки «Дата». Где​ Excel».​ формат дат. Бывает,​ к дате дни,​ кв. » &​ будет выглядеть так:​ благодаря данному ресурсу.​
​:​ статье «Как посчитать​ таблицы за конкретный​ и время». В​ 11 дней. Пишем​ стоит число месяцев,​ к дате. В​ три адреса ячейки​, то сможем узнать​Пояснения к формуле​ найти эту функцию​Как посчитать разные​
​ при копировании из​ месяцы, года, посчитать​ ГОД(B5)& «г.»​ =ЕСЛИ(RC[-2]=»»;0;ОСТАТ(МЕСЯЦ(RC[-2])-1;3)+1)​ А вот с​Pelena​ время в Excel»​ день, т.д. Этот​ появившемся окне выбираем​ года, месяцы и​
​ которое нужно прибавить​ примере – это​ одинаковые, т.к. дата​ стаж по сегодняшнее​:​ и как установить​
​ периоды стажа, других​ других программ в​ стаж, возраст, время​В файле примера создана​Для квартала: =ЕСЛИ(RC[-1]=»»;0;ЦЕЛОЕ((МЕСЯЦ(RC[-1])+2)/3))​ месяцами пока никак..​, Спасибо!​ тут.​
​ способ пригодится при​ нужную функцию. Например,​ дни, которые будем​ к дате.​

excel-office.ru

Выбрать даты по кварталам (Формулы/Formulas)

​ ячейка G13.​​ записана в одной​
​ число. Например, узнать​РАЗНДАТ – это​ её, смотрите в​ разных дат и,​ Excel даты вставляются​-​
​ таблица с датами.​

​Спасибо за помощь!​​Прикрепляю файл примера.​
​Dryha​Как посчитать возраст,​

​ проведении анализа в​​ мы выбрали функцию​
​ прибавлять, в разных​«День» — пишем​В ячейке Н13​
​ ячейке. Здесь все​

​ стаж работника на​​ название функции.​​ статье «Число Excel.​

​ затем, сложить их​​ в текстовом формате.​​все это можно​​ Для каждой даты​

​Dante132​​Прошу помощи!​

​: Всем спасибо!​​ смотрите в статье​

​ таблице, т.д. Подробнее​​ «ГОД». В появившемся​ ячейках.​​ день нашей даты.​​ будет стоять результат,​ верно. Получилось.​ предприятии. Каждый день​

excelworld.ru

Порядковый номера месяца в квартале по введенной дате

​y – считает​​ Формат».​
​ вместе, чтобы узнать​ Тогда формулы не​
​ сделать формулами. Как​ вычислен номер квартала.​: Нужно немного поправить​vikttur​jakim​ «Как посчитать возраст​ о таком способе,​ диалоговом окне указали​Диалоговое окно заполним так.​Получилось так.​
​ здесь пишем формулу.​Таким же образом можно​ стаж будет пересчитываться​ года​Столбец С –​
​ общее количество дней,​
​ считают. Чтобы формулы​

​ посчитать дату выхода​​ Даты, относящиеся к​ формулу​
​: Почти то же.​

​: С функцией СУМПРОИЗВ.​​ в Excel».​
​ читайте в статье​ адрес ячейки с​Получилась такая формула и​Нажимаем «ОК». Получилась такая​ Нажимаем на эту​ прибавить года, изменив​ на текущую дату.​ym – считает​
​ будет стоять формула​ др., смотрите в​ считали, нужно изменить​ сотрудника из отпуска​ Четным кварталам выделены​

​=ЕСЛИ(RC[-1]=»»;0;ЦЕЛОЕ((МЕСЯЦ(RC[-1])+2)/3))или так =ЕСЛИ(RC[-1]<>»»;ЦЕЛОЕ((МЕСЯЦ(RC[-1])+2)/3);0)​​ Остаток:​​AlexM​

​Dryha​​ «Функция «СУММЕСЛИ» в​
​ датой (А19). Нажали​ такой результат.​ формула. =ДАТА(1979;11+G13;1)​
​ ячейку, делаем её​
​ чуть формулу. Смотрите​

​ Как установить текущую​​ месяца​ и результат подсчета.​
​ статье «Как в​

planetaexcel.ru

​ формат ЗНАЧЕНИЙ на​

Просмотров:
9 620

При работе с наборами данных, может возникнуть необходимость посчитать количество значений/элементов между двумя датами. Решить подобную задачу с помощью встроенных функций в Excel, не так уж и трудно. Чаще всего такая задача возникает при формировании отчета по набору аналогичных записей.

Исходные данные

Возьмем в качестве примера такие данные.

Значения в первом столбце нам не важны, используем их в данном примере только как ориентиры. Потому для нас интерес представляет столбец «C» содержащий даты. Подсчет будет осуществляться по датам, так что будут отражать/представлять/указывать даты не важно. Например, пусть в первом столбце ФИО посетителей, во втором столбце даты их посещения.

Количество значений/элементом меду двумя датами, будем находить при помощи двух функции Excel, «СЧЕТЕСЛИМН» и «ДАТА». Однако использование функции «ДАТА» будет зависеть от контекста подсчёта.

Так, например, для подсчета количества элементов в рамках года или полугодия, использование функции «ДАТА» необходимо. Однако при подсчёте количества элементов за квартал или месяц можно использовать только ссылки на значения в ячейках. Возможен и комбинированный вариант применения, всё зависит от представления данных в таблице.

Подсчёт данных в пределах года

Введем в ячейку F3 формулу =СЧЁТЕСЛИМН($C$3:$C$18;»>=»&ДАТА(E3;1;1);$C$3:$C$18;»<=»&ДАТА(E3;12;31))

Разберем как работает формула:

Отличие функции «СЧЕТЕСЛИМН» от «СЧЕТЕСЛИ» заключается в возможности указания нескольких условий фильтрации. Как следствие множественная фильтрация, обеспечивает возможность подсчета значений в диапазоне.

$C$3:$C$18 – указание на диапазон ячеек, в котором будет производится подсчет. В примере задано условие фильтрации в пределах 2016 и 2017 года, в отдельности. Вводя формулу в ячейку F3 мы подсчитываем результат для 2016 года, что указано в ячейке E3. Для расчета за 2017 год, необходимо ввести формулу в ячейку F4, самый простой способ сделать это, скопировать ее из ячейки F3. В примере используются абсолютные ссылки на ячейки, потому при копировании не произойдет сдвиг ячеек, в которых осуществляется подсчет значений.

После указания диапазона, указывается логическое условие. В формуле используется строковое определение условия с добавлением к нему результата функции «ДАТА». Начало диапазона задается как «>=»&ДАТА(E3;1;1), конец диапазона как «<=»&ДАТА(E3;12;31). Знак «&» в формуле используется для соединения двух строковых значений, условия и результата функции «ДАТА».

Для того чтобы определить две даты мы используем функцию «ДАТА», первое значение функции ссылается на ячейку с данными, в нашем случае ячейки содержат года «2016» и «2017». Второй параметр функции определяет порядковый номер месяца, третий число месяца.

Таким образом результатом вычисления функции «ДАТА» в условии получаются значения первого дня года и последнего дня года. ДАТА(E3;12;31) – E3 ссылка на ячейку содержащую значение 2016. Второй параметр функции номер месяца, а третий параметр число. Соответственно результатом вычисления функции будет 31 декабря 2016 года.

В результате выполнения функций логические условия будут читаться следующим образом, например, для 2016 года: >=01.01.2016 – больше или равно 1 января 2016

<=31.12.2016 – меньше или равно 31 декабря 2016

Таким образом подсчитываться будут только те даты, которые попадают в диапазон фильтра, в текущем примере в целый год.

Подсчет данных в пределах месяца

Для подсчета значений в месяц мы будем ссылаться не только на год, но и на месяц. В целом формула для подсчета значений за месяц похожа на подсчет за год. Отличие только в диапазоне. Проблема заключается только в определении последнего дня месяца.

Дату начала месяца определить легко, указав в функции «ДАТА» ссылки на год и месяц, а день задать константой — цифрой 1. Однако же, последний день месяца зависит от месяца, в феврале последний день может быть 28, и 29, в остальные месяцы 30 или 31 число. Поэтому для определения последнего дня лучше всего использовать функцию «КОНМЕСЯЦА». Первым параметром функции будет результат вычисления функции «ДАТА», а значение второго параметра будет равно 0.

Таким образом, формула будет иметь вид:

=СЧЁТЕСЛИМН($C$3:$C$18;»>=»&ДАТА(E7;F7;1);$C$3:$C$18;»<=»&КОНМЕСЯЦА(ДАТА(E7;F7;1);0))

Что соответствует описанию: подсчитать все значения в диапазоне $C$3:$C$18. Удовлетворяющие условию фильтра: больше или равно 1 числу месяца, номер которого указан в ячейке F7, год которого указан в ячейке E7. Притом значение должно быть меньше или равно последнему дню месяца, первое число которого вычисляется функцией и соответствует первому числу месяца, номер которого указан в ячейке F7, год указан в ячейке E7.

Написать формулу для подсчета значений между двумя датами, еще проще. Нет необходимости использовать дополнительные функции, такие как «ДАТА» и «КОНЦМЕСЯЦА». Достаточно в условиях указать ссылки на ячейки, содержащие начальную и конечную дату диапазона.

Кстати, не во всех языках программирования есть стандартные функции для определения последнего дня месяца. При этом существуют стандартные функции для работы с датами, а именно для сложения и вычитания дат, и значений к датам. Так что, одним из приемов получения последнего дня месяца, является вычитание одного дня из первого дня следующего месяца.

Хитрости »

18 Октябрь 2021              6795 просмотров

Создать список дат(месяцев, кварталов, лет) на основании даты начала и окончания периода

Скачать файл, используемый в видеоуроке:

  Список дат при помощи Power Query.xlsx (72,7 KiB, 405 скачиваний)

Представим ситуацию, что у нас есть таблица следующего вида:

В ней перечислены клиенты, которые подписались на наш продукт(условно клиент А и клиент В). В качестве дат указаны дата начала подписки и дата окончания. И нам необходимо построить что-то вроде диаграммы Ганта, на которой будет видно в хронологическом порядке активность клиентов

Т.е. по сути нам нужно создать даты из двух таким образом, чтобы на каждый день подписки была одна дата. Например, для клиента «А» с подпиской на журнал «За рулем» у нас должно получиться 115 строк дат, т.к. подписка длилась 115 дней(от 05.10.2020 до 27.01.2021). Проблема в том, что встроенных средств создания списка дат между двумя заданными датами в Power Query нет. Поэтому придется что-то изобретать.
Загружаем данные нашей таблицы в редактор Power Query: выделяем таблицу -вкладка Данные(Data)(или Power Query) —Получить и преобразовать данные(Get & Transform Data) —Из таблицы(From Table/Range).
После того как загрузили таблицу в Power Query следует убедиться, что тип данных в столбцах «Дата подписки» и «Дата отказа» именно дата, а не дата и время или тем более не тип любой или текст. Для этого на каждом столбце щелкаем левой кнопкой мыши по значку слева от заголовка и выбираем «Дата»

(Date)

Или выделяем сразу оба столбца с датами -вкладка Главная(Home) -группа Преобразование(Transform) -раскрываем список Тип данных(Data type) и выбираем Дата(Data).
Далее, чтобы получить список дат для каждого клиента от одной даты до другой, необходимо использоваться функцию List. Идем на вкладку Добавление столбца(Add Column) —Настраиваемый столбец(Custom column). В качестве имени запишем «Даты», а в качестве формулы следующее:

List.Dates([Дата подписки], Duration.Days(Duration.From([Дата отказа]-[Дата подписки])) +1, #duration(1, 0, 0, 0))

Подробнее про создание вычисляемых столбцов и просмотр функций в PowerQuery можно ознакомиться в этой статье: Вычисления в PowerQuery
List.Dates – функция языка M, которая берет начальную дату и прибавляет к ней указанное кол-во дней, и все это выводит в виде списка(тип List). Для определения кол-ва дней мы используем обычное вычитание дат([Дата отказа]-[Дата продажи]), преобразуя при этом полученную разность в тип Длительность при помощи функции Duration.From, а далее в количество дней при помощи Duration.Days. Если этого не сделать, то можем получить ошибку несовпадения типов. После этого задаем шаг в днях, который нужно использовать для создания списка — #duration(1, 0, 0, 0). #duration(кол-во дней, кол-во часов, кол-во минут, кол-во секунд)
Будет добавлен новый столбец, в котором для каждого товара каждого клиента будет создан список дат использования продукта. Остается лишь щелкнуть левой кнопкой мыши на значке разнонаправленных стрелок в заголовке созданного столбца и выбрать Развернуть в новые строки(Expand to New Rows):

Полученный столбец со всеми датами преобразуем в тип Дата(вкладка Главная(Home) -группа Преобразование(Transform) -раскрываем список Тип данных(Data type) и выбираем Дата(Data).
Если в дальнейшем мы планируем работать с результатом запроса в Excel через выгрузку в сводную таблицу(Главная(Home) —Закрыть и загрузить(Close & Load) —Отчет сводной таблицы(Pivot table report)), то на этом шаге можно сохранить и загрузить запрос и уже в сводной таблице просто поместить созданный столбец «Даты» в область Столбцов.
Но если мы хотим развернуть эти даты в столбцы непосредственно через PowerQuery, то для начала необходимо отсортировать столбец с датами по возрастанию: жмем на значок стрелки в правом верхнем углу столбца и из раскрывающегося списка выбираем Сортировка по возрастанию(Sort Ascending):

После чего используем столбец сведения: выделяем созданный столбец дат -вкладка Преобразование(Transform) —Столбец сведения(Pivot Column). В качестве Столбца значений(Values column) указываем столбец Дата подписки. Нажимаем кнопку Расширенные параметры(Advanced options) и в качестве Функции агрегированного значения(Aggregate Value Function) указываем Количество(все)(Count (All))

После этого можно удалить лишние столбцы (например, столбец «Дата отказа») и останется выгрузить данные на лист: Главная(Home) —Закрыть и загрузить(Close & Load) —Таблица(Table). Созданная таблица выгрузится на лист.
В загруженном листе нам останется сделать форматирование, чтобы отметить только те ячейки, в которых у клиентов были активные подписки. Выделяем ячейки со значениями 0 и 1(те, что под датами) —Главная(Home) —Условное форматирование(Conditional formatting) —Правила выделения ячеек(Highlight Cells Rules) —Равно(Equal To). Вписываем значение 1 и выбираем формат(если предустановленные форматы не подходят, то в раскрывающемся списке выбираем «Пользовательский формат»).
А чтобы нам единички и нули не портили картину, мы опять выделим все ячейки с нулями и единичками -Правая кнопка мыши —Формат ячеек(Format cells) -вкладка Число(Number). Идем в самый низ –(все форматы)(Custom). Выставляем в поле тип такой формат: ;;;
Теперь нули и единички видны не будут, а формат будет работать. В итоге добьемся нужного результата.

Но что делать, когда надо отследить подписку не по дням, а по месяцам? Самый большой тип длительности, который поддерживает функция duration – это дни. Поэтому придется хитрить. Здесь функция

List.Dates

нам уже не поможет, т.к. построена именно на возможностях функции duration. Но мы можем использовать более гибкую функцию:

List.Generate

. Она способна создавать практически любые списки.
Добавляем новый столбец(вкладка Добавление столбца(Add Column) —Настраиваемый столбец(Custom column)). В качестве формулы запишем:

let 
        start = [Дата продажи],
        end = [Дата отказа]
in
        List.Generate(
                ()=>[day=start],
                each [day]<=end,
                each [day=Date.AddMonths([day], 1)],
                each [day]
            )

А дальше все так же: щелкнуть левой кнопкой мыши на значке разнонаправленных стрелок —Развернуть в новые строки(Expand to New Rows). И при необходимости все это «перевернуть» в столбцы(алгоритм точно такой же, как описан выше для списка дат).
Чуть подробнее про то, что мы записали в нашем созданном столбце:
Let – здесь мы задаем дату начала и окончания для каждой строки списка. Можно сказать просто запоминаем их для дальнейшего использования.
List.Generate – специальная функция, которой мы задаем начальное значение даты ( ()=>[day=start],); далее указываем условие по которому необходимо завершить цикл(each [day]<=end ) – т.е. на какой дате закончить создавать список; после этого указываем что именно делать внутри каждого шага цикла(each [day=Date.AddMonths([day], 1)]) – добавить один месяц и указываем, что проделывать это надо для каждого нового значения(each [day]).
Т.е. функция берет дату начала как точку отсчета([day=start]), прибавляет к ней указанный интервал(Date.AddMonths([day], 1)]) и так до тех пор, пока новая дата не станет меньше или равна конечной([day]<=end). И на каждом шаге новая дата добавляется в итоговый список.
На самом деле, для того, чтобы это использовать не надо особо вдумываться в процесс. Копи-паст никто не отменял 😊 Самое главное, на что здесь стоит обратить внимание, это функция Date.AddMonths. Она при каждом шаге цикла прибавляет к переменному значению day(которое начинается у нас с Даты продажи) по одному месяцу. Если нужен другой период(дни, недели, годы), то нужно просто подставить вместо AddMonths другую функцию. Вот возможные варианты:

1. Date.AddDays – добавляет дни
2. Date.AddWeeks – добавляет недели
3. Date.AddMonths – добавляет месяцы(что мы и делали)
4. Date.AddQuarters – добавляет кварталы
5. Date.AddYears – добавляет годы

ВАЖНО: Что надо помнить, если используем создание списка не под дням, а, например, по месяцам. Даты для месяцев у нас могут быть разные (например, в примере выше дата начала для клиента «А» 10.09.2020, а дата окончания — 07.02.2021). Это может привести к тому, что в каких-то строках могут пропасть последние месяцы. Решается это довольно просто: сразу после того, как загрузили данные и изменили тип столбцов на тип Дата(второй шаг запроса — перед добавлением пользовательского столбца), необходимо щелкнуть правой кнопкой мыши по столбцу дат -выбрать пункт Преобразование(Transform) —Месяц(Month) —Начало месяца(Start of Month). Это заставит нашу функцию вычислять интервалы по месяцам корректно. Точно такой же алгоритм и для других периодов: лет, недель, кварталов.

Скачать файл:

  Список дат при помощи Power Query.xlsx (72,7 KiB, 405 скачиваний)

Так же см.:
Получить данные из файлов XML при помощи Power Query
Как перейти к определенной строке таблицы в Power Query
Относительный путь к данным PowerQuery
Курс валют при помощи Power Query

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика