Здравствуйте. Вчера с коллегой решали простую задачу, а сегодня делюсь этим с Вами. У нас был список дней рождения всех сотрудников компании. Требовалось узнать, сколько из этих дат припадает на каждый из месяцев года.

список дней рождения

Массиву с датами рождения (С3:C252) я присвоил имя «даты», чтобы формула смотрелась проще. Для получения количество дней рождения, используем формулу:

=СУММПРОИЗВ(—(МЕСЯЦ(даты)= номер_месяца ))

счетчик дней рождения

Как это работает? Функция СУММПРОИЗВ работает с массивами, вычисление происходит в таком порядке:

  1. МЕСЯЦ(даты)= номер_месяца – из каждой даты в списке извлекается номер месяца, и сравнивается с искомым. Когда номера совпадают – возвращается ИСТИНА, не совпадают – ЛОЖЬ. В итоге, мы получаем массив вида { ИСТИНА, ЛОЖЬ, ИСТИНА, ИСТИНА,…}
  2. Поскольку функция СУММПРОИЗВ трактует логические значения, как ноль (и ИСТИНУ и ЛОЖЬ), перед выражением из первого пункта поставим двойное отрицание «—». В Excel такая операция заменит ИСТИНУ на 1, а ЛОЖЬ на 0. Массив примет вид: { 1, 0, 1, 1,…}
  3. В конце концов, СУММПРОИЗВ просуммирует все нули и единицы. То есть результатом будет количество совпадений месяцев в исходной таблице с заданным месяцем, т.к. каждой единице в массиве соответствует одна совпавшая дата.

Подсчет если есть пустые ячейки

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

=СУММПРОИЗВ(((МЕСЯЦ(даты)=H3))*(даты<>«»))

Здесь добавилось умножение на выражение (даты<>«»). То есть, когда значение не заполнено, это выражение возвращает ЛОЖЬ и весь элемент массива с результатами будет ЛОЖНЫМ, такая ячейка не будет учтена.

дни рождения с пустыми строками

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

На этом всё, буду рад вашим комментариям и вопросам. До встречи!