От нашей студентки проходящей курс "Excel бизнес-анализ и прогнозирование" поступил вопрос в чат с тренером поддержки, по организации работы с формулами даты и времени. Вопрос возник в процессе внедрения в работу знаий полученных на курсе. 

Суть «кейса»: имеется табличка с двумя полями, в которой содержится информация о дате создания и дате закрытия операции, на основании которых нужно вычислить количество и процент операций, которые были закрыты:

- в первый рабочий день (со времени начала выполнения операции до 18:00 следующего рабочего дня);

- в пределах двух следующих рабочих дней;

- в сроки более двух рабочих дней.

Для того чтобы всё правильно прописать, для начала нужно правильно проранжировать периоды.

Добавим «технический столбец» в котором пропишем с помощью логических функций ЕСЛИ следующие условия:

1. Если даты создания и закрытия совпадают, то это считается первым рабочим днем (в столбец подставится единичка)

В полях у нас формат дата-время, поэтому чтобы сравнить дни, нужно из этого формата извлечь дату. Сделать это можно несколькими способами:

• С помощью функции ДАТА, собрав даты из наших полей

=ЕСЛИ(ДАТА(ГОД(A2);МЕСЯЦ(A2);ДЕНЬ (A2))=ДАТА(ГОД(B2);МЕСЯЦ(B2);ДЕНЬ(B2));1

• Но если помнить, что дата и время для Excel являются числами (время – дробная часть даты), то можно пойти более коротким путём, используя функцию ЦЕЛОЕ.

=ЕСЛИ(ЦЕЛОЕ(A2)=ЦЕЛОЕ(B2);1

2.  На месте аргумента [значение_если_ложь] функции ЕСЛИ напишем ещё одну функцию ЕСЛИ, в которой проверим, является ли дата закрытия следующим рабочим днем (на всякий случай проверим чтобы дата была меньше двух рабочих дней от даты создания)

B2<РАБДЕНЬ(A2;2)

и чтобы одновременно проверялось условие, что время до 18:00

ЧАС(B2)<18

Данная проверка условий также входит в «операции, закрытые в первый рабочий день», поэтому в аргументе [значение_если_истина] должна подставляться единичка.

Формула на данном этапе выглядит примерно так:

=ЕСЛИ(ЦЕЛОЕ(A2)=ЦЕЛОЕ(B2);1;

                        ЕСЛИ(И(B2<РАБДЕНЬ(A2;2);ЧАС(B2)<18);1

3.  Теперь пришла очередь проверить принадлежит ли дата закрытия к группе «закрытые в пределах двух следующих рабочих дней». На месте аргумента [значение_если_ложь] пишем еще одну ЕСЛИ, которая будет проверять, является ли дата закрытия меньше третьего рабочего дня и тогда подставляться двоечка в наш «технический столбец».

ЕСЛИ(B2<РАБДЕНЬ(A2;3);2

• Почему не пишем проверку даты, является ли она больше первого рабочего дня? Потому что функция ЕСЛИ останавливается если её результатом является ИСТИНА и при правильном написании условий можно сократить длину формулы, а при неправильном – запутаться, и получить некорректный результат.
• Почему не проверяем час на <18? Потому что это не оговорено условием и по умолчанию считаем что в группу входят даты до конца дня

4. Прописывать условия для третьей группы не нужно, так как в неё попадают все даты, которые не соответствуют предыдущим условиям. На месте аргумента [значение_если_ложь] пишем троечку и закрываем скобки для всех функций ЕСЛИ.

Полностью формула выглядит так:

=ЕСЛИ(ЦЕЛОЕ(A2)=ЦЕЛОЕ(B2);1;

                        ЕСЛИ(И(B2<РАБДЕНЬ(A2;2);ЧАС(B2)<18);1;

                                            ЕСЛИ(B2<РАБДЕНЬ(A2;3);2;

                                                             3)))

 

 

Конкретно такие формулы мы учимся строить в 4 модуле курса "Excel бизнес-анализ и прогнозирование" 

РАБОТАЕМ РАСШИРЕННО С ДАТАМИ И ВРЕМЕНЕМ. МАТЕМАТИЧЕСКИЕ И ЛОГИЧЕСКИЕ ФУНКЦИИ

 

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

Для подсчёта количества используем функцию СЧЁТЕСЛИ.

Первая группа: =СЧЁТЕСЛИ($C$2:$C$17;1)

Вторая группа: =СЧЁТЕСЛИ($C$2:$C$17;2)

Третья группа: =СЧЁТЕСЛИ($C$2:$C$17;3)

С расчётом процента также никаких сложностей нет. Нужно просто разделить количество операций, принадлежащих к конкретной группе на общее количество операций (можно использовать как формулы которые написали выше, так и ссылки на ячейки с этими формулами:

Первая группа: =J2/СЧЁТ($C$2:$C$17)

Вторая группа: =J5/СЧЁТ($C$2:$C$17)

Третья группа: =J7/СЧЁТ($C$2:$C$17)

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

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

Автор статьи тренер DATAbi Михаил Беленчук

 

Дізнайтеся більше про повну програму "EXCEL: бізнес-аналіз і прогнозування"