Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

Если вам нужно вычислить разницу между двумя датами не в днях (для чего достаточно вычесть из одной даты другую или воспользоваться функцией ДНИ / DAYS), а в месяцах или годах (например, возраст сотрудника), воспользуйтесь функцией РАЗНДАТ / DATEDIF. При ее вводе не будут отображаться аргументы, но не обращайте на это внимания — она работает во всех версиях приложения.

Вот ее синтаксис:

=РАЗНДАТ(дата_начала; дата_окончания; единица измерения)

Первые два аргумента — даты начала и окончания периода. Они могут быть указаны прямо в формуле в кавычках либо в виде ссылок на ячейки с датами, а также быть заданными функцией СЕГОДНЯ / TODAY.

Единица измерения задается в кавычках. Есть следующие возможные варианты:

• d — число дней (такой параметр не имеет особого смысла, так как для этой задачи подойдет и функция ДНИ / DAYS, и просто вычитание);

• m — число полных месяцев в периоде;

• y — число полных лет в периоде;

• md — разница в днях без учета месяца и года (например, между 01.01.2021 и 15.06.2022 — 14 дней);

• ym — разница в месяцах без учета дня и года (например, между 01.01.2021 и 15.06.2022 — 5 месяцев);

• yd — разница в днях без учета года (например, между 01.01.2021 и 15.06.2022 —165 дней).

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

=РАЗНДАТ(ссылка на ячейку с датой рождения; СЕГОДНЯ(); "y")

=DATEDIF(ссылка на ячейку с датой рождения; TODAY(); "y")

В Google Таблицах функция РАЗНДАТ / DATEDIF тоже есть — ее аргументы будут отображаться в подсказке при вводе функции.

ФУНКЦИИ ДЛЯ ПОЛУЧЕНИЯ ОТДЕЛЬНЫХ ПАРАМЕТРОВ ДАТЫ

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

Обратите внимание, что на выходе эти функции возвращают числа (кроме КОНМЕСЯЦА / EOMONTH), а не даты. Если вы хотите отображать в ячейке, например, только день недели или номер месяца __, но в значении этой ячейки хотите сохранить дату, то лучше воспользоваться числовыми форматами, чтобы оставить в ячейке значение, но отображать только отдельный параметр.

К сожалению, для вычисления номера квартала функции нет ни в Excel любых версий, ни в Google Таблицах. Обычно номер квартала вычисляют составной формулой, опираясь на номер месяца. Например, такой (формула есть в файле с примерами):

=ЦЕЛОЕ((МЕСЯЦ(ячейка с датой) + 2)/3)

Для января (месяц = 1) формула будет возвращать единицу:

(1 + 2)/3 = 1

А для августа (8):

(8 + 2)/3 = 3,(3)

Дробную часть мы убираем с помощью функции ЦЕЛОЕ / INT и за счет этого получаем целое число 3 для августа.

ЦЕЛОЕ((8 + 2)/3) = ЦЕЛОЕ(3,(3)) = 3

ВЫЧИСЛЕНИЯ С РАБОЧИМИ ДНЯМИ

В Excel и Google Таблицах есть две функции для работы с датами — ЧИСТРАБДНИ / NETWORKDAYS / (количество дней между двумя датами) и РАБДЕНЬ / WORKDAY (дата, которая наступит по прошествии заданного числа рабочих дней).

Например, нам нужно знать, какая дата наступит через 30 рабочих, а не календарных дней после некоторой даты, для этого нужна функция РАБДЕНЬ:

=РАБДЕНЬ(дата; число рабочих дней; [Праздники])

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

Так что если нам нужно исключить какие-то дни как праздничные, введем их в отдельных ячейках и будем ссылаться на эти ячейки:

=РАБДЕНЬ(B1;30;G1:G2)

Функция ЧИСТРАБДНИ возвращает не дату, как РАБДЕНЬ, а число — число рабочих дней в периоде от одной даты до другой:

=ЧИСТРАБДНИ(начальная дата; конечная дата; [Праздники])

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

Допустим, нам нужно число рабочих в текущем году на текущую дату:

=ЧИСТРАБДНИ(первый день года; текущая дата; [Праздники])

Текущую дату вычислить легко — это функция СЕГОДНЯ / TODAY.