Сколько пятниц в январе 2016 года
Задача
Предположим, что нам нужно узнать сколько именно определенных дней недели попадает в заданный интервал дат. Например, сколько точно пятниц в январе 2016 года.
Решение
Нужная нам формула будет выглядеть так:
Не забудьте, что это формула массива (фигурные скобки в строке формул об этом напоминают), т.е. после ее ввода нужно нажать не обычный Enter, а сочетание Ctrl+Shift+Enter.
Как работает эта формула
При внешней компактности, в этой формуле зарыты, на самом деле, несколько хитрых техник. Давайте их разберем.
Каждая дата воспринимается Microsoft Excel как число-счетчик, а именно - количество дней от точки отсчета дат в Excel (01.01.1900) до заданной даты. Это число можно увидеть, если поменять формат ячейки с датой на числовой или общий:
Т.е., с точки зрения Excel, нам нужно проверить интервал дат 42370:42400.
В то же время числами в Excel обозначается еще кое-что, гораздо более всем знакомое - номера строк на листе. Попробуйте в любой произвольной ячейке набрать, например =7:10 - и Excel сошлется на указанные строки, выделив их целиком:
Фрагмент нашей формулы B1&":"&B2 аналогичным образом формирует диапазон целиком выделенных строк с 42370-й по 42400-ю, а функция ДВССЫЛ (INDIRECT) превращает текстовую строку "42370:42400" в настоящую ссылку на строки с этими номерами.
Затем функция СТРОКА (ROW) определяет номера строк в этом диапазоне и выдает нам массив чисел {42370, 42371, 42373 ... 42400}.
После этого мы используем функцию ТЕКСТ (TEXT), чтобы определить день недели для каждой даты, соответствующей нашим числам. Второй аргумент этой функции "ддд" запрашивает у Excel день недели для даты в виде двухбуквенного сокращения (Пн, Вт, Ср и т.д.).
Наконец, в дело вступает функция ЕСЛИ (IF), которая проверяет полученный день недели. Если он равен нужному (Пт), то прибавляем к общему зачету единичку, иначе - ноль.
Вот такие хитрушки ;)
Ссылки по теме
- Как Excel на самом деле понимает, хранит и обрабатывает даты
- Как определить, пересекаются ли два интервала дат?
- Как сделать всплывающий календарь для удобного ввода дат в ячейки листа Excel
при этом не нужно создавать массив дат.
Для любого дня недели:
где в B1 - начальная дата
в B2 - конечная дата
в B3 - день недели который считается (1 - пн ... 7 - вс)
В данном случае хотел, по большей части, показать технику создания числовых последовательностей для анализа дат с помощью формул массива.
Немного модифицировал под свои нужды, мне показалось удобнее использовать функцию WEEKDAY() и N(). Также вместо массива можно воспользоваться SUMPRODUCT(). В итоге получится что-то такое:
=SUMPRODUCT( N( WEEKDAY( ROW( INDIRECT(B$1&":"&B$2)),2)=A5))
B1 - начальная дата, B2 - конечная дата, 2 - чтобы понедельник первым днём недели считался, A5 - искомый номер дня недели
также можно добавить второй аргумент в SUMPRODUCT() если надо найти количество дней в диапазоне дней между, к примеру, понедельником и средой, ну и далее по списку.
=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(J$2&":"&J$3)),2)>=$F405), N(WEEKDAY(ROW(INDIRECT(J$2&":"&J$3)),2)<=$G405))