Сколько пятниц в январе 2016 года

Задача

Предположим, что нам нужно узнать сколько именно определенных дней недели попадает в заданный интервал дат. Например, сколько точно пятниц в январе 2016 года.

Решение

Нужная нам формула будет выглядеть так:

how-many-fridays1.png

Не забудьте, что это формула массива (фигурные скобки в строке формул об этом напоминают), т.е. после ее ввода нужно нажать не обычный Enter, а сочетание Ctrl+Shift+Enter.

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

При внешней компактности, в этой формуле зарыты, на самом деле, несколько хитрых техник. Давайте их разберем.

Каждая дата воспринимается Microsoft Excel как число-счетчик, а именно - количество дней от точки отсчета дат в Excel (01.01.1900) до заданной даты. Это число можно увидеть, если поменять формат ячейки с датой на числовой или общий:

how-many-fridays2.png

Т.е., с точки зрения Excel, нам нужно проверить интервал дат 42370:42400.

В то же время числами в Excel обозначается еще кое-что, гораздо более всем знакомое - номера строк на листе. Попробуйте в любой произвольной ячейке набрать, например =7:10 - и Excel сошлется на указанные строки, выделив их целиком:

how-many-fridays3.png

Фрагмент нашей формулы B1&":"&B2 аналогичным образом  формирует диапазон целиком выделенных строк с 42370-й по 42400-ю, а функция ДВССЫЛ (INDIRECT) превращает текстовую строку "42370:42400" в настоящую ссылку на строки с этими номерами.

Затем функция СТРОКА (ROW) определяет номера строк в этом диапазоне и выдает нам массив чисел {42370, 42371, 42373 ... 42400}.

После этого мы используем функцию ТЕКСТ (TEXT), чтобы определить день недели для каждой даты, соответствующей нашим числам. Второй аргумент этой функции "ддд" запрашивает у Excel день недели для даты в виде двухбуквенного сокращения (Пн, Вт, Ср и т.д.).

Наконец, в дело вступает функция ЕСЛИ (IF), которая проверяет полученный день недели. Если он равен нужному (Пт), то прибавляем к общему зачету единичку, иначе - ноль.

Вот такие хитрушки ;)

Ссылки по теме



MCH
19.01.2016 20:33:36
Для подсчета конкретно пятниц, можно использовать формулу:
=ОТБР((B2-B1+ОСТАТ(B1;7)+1)/7)

при этом не нужно создавать массив дат.

Для любого дня недели:
=ОТБР((B2-B1+ОСТАТ(B1+5-B3;7)+1)/7)

где в B1 - начальная дата
в B2 - конечная дата
в B3 - день недели который считается (1 - пн ... 7 - вс)
29.01.2016 00:21:22
Спасибо за уточнение, Михаил!
В данном случае хотел, по большей части, показать технику создания числовых последовательностей для анализа дат с помощью формул массива.
21.01.2016 13:12:31
А как вычислить 7 пятниц на неделе? Шутка :) А за статью спасибо Николай. Очень полезная!