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

Знать про это нужно, ведь, если вы видите числа там, где должны быть даты, дело может быть только в числовом форматировании: нужно поменять формат на «Дату» или любой другой формат даты и/или времени. И благодаря тому что «под капотом» даты — число, с ним можно проводить арифметические операции (об этом — через пару абзацев).

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

Даты в формулах можно использовать:

• как константы, указывая в кавычках в одном из стандартных форматов ("01.01.2021", "01/01/2021", "2021-01-01", "ГГГГ/ММ/ДД");

• ссылаясь на ячейки, где даты хранятся, — уже без кавычек.

С датами можно производить операции вычитания и сложения:

• вычесть из одной даты другую, чтобы получить разницу в днях (результатом вычитания будет число — количество дней);

• прибавить к дате число и получить дату, которая наступит через соответствующее количество дней.

ФУНКЦИИ ДЛЯ ОТОБРАЖЕНИЯ ТЕКУЩЕЙ ДАТЫ

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

=СЕГОДНЯ()

=ТДАТА()

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

Если вам нужно вставить текущую дату как значение, чтобы она не пересчитывалась и не менялась в будущем, воспользуйтесь сочетанием клавиш Ctrl +;(^ +;).

А для вставки текущего времени как значения — сочетанием Ctrl + Shift +:(

ФУНКЦИЯ РАЗНДАТ / DATEDIF

Если вам нужно вычислить разницу между двумя датами не в днях (для чего достаточно вычесть из одной даты другую или воспользоваться функцией ДНИ / 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.

А вот первый день года можно вычислить с помощью функции ДАТА / DATE, она позволяет сформировать дату из трех составляющих — года, месяца и дня:

=ДАТА(год; месяц; день)

Например, следующая функция будет возвращать 01.01.2023:

=ДАТА(2023;1;1)

Если нам нужна первая дата текущего года, то второй и третий аргумент останутся единицами, а год можно вычислять как год от сегодняшней даты: