Дата и время в Excel

Excel изображает даты с помощью натуральных чисел от 1 до 65380. Летосчисление начинается с 1 января 1900 года, которое соответствует 1 (при этом есть удивительная дата 0 января 1900г), и кончается 31 декабря 2078 года (эта дата соответствует числу 65380). Каждую полночь прибавляется 1 день. Например, 12 декабря 2018 г обозначается числом 43446.
Время меньшее суток измеряется долями суток. Например, 17:30 = 0,72916. Можно указать сразу и дату и время, например, 12 декабря 2018 г 17:30 = 43446,73.

1. Задача учёта продолжительности рабочих смен. Продолжительность определяется как разница между Временем окончания и Временем начала. В первых двух строках дневных смен это происходит без проблем, а вот в двух последних строках ночных смен при простой разнице возникает ошибка отрицательного времени помечаемая решётками #######. Для преодоления этой проблемы нужно вспомнить о том, что ночная смена проходит через полночь, а в полночь добавляется еще 1  день, что и нужно учесть в формулах этих ячеек. Также эту задачу нужно решать с использованием логической функции «Если», что позволит написать для всех ячеек столбца E одинаковую формулу. Посмотреть задачи по логическим функциям можно здесь.
В идеале же в ячейках со временем нужно прописать полную дату и время одновременно. То есть, вместо «22:00» написать «14/10 22:00». И, конечно, учесть, что заканчивается эта смена уже в следующие сутки «15/10 06:00». И тогда необходимости усложнять формулу, способами описанными выше, не возникнет.
Также необходимо рассчитать сумму всех рабочих смен с помощью функции «Сумма». Но тут тоже возникает сложность связанная с тем, что формат времени по умолчанию не показывает значения большие чем 24 часа. Для того, чтобы это получилось, необходимо в группе «Число» выбрать специальный формат времени (см рис).
2016-05-17_192406

2. Задача на расчёт скорости, темпа и накопительных итогов.
Всем известно, что скорость рассчитывается делением дистанции на время. Но если мы просто напишем формулу деления числовых данных из ячейки Дистанции на данные из ячейки времени, то мы получим скорость невозможно большую. Это происходит потому, что время в Excel измеряется в сутках. Следовательно и посчитанная скорость представлена не в привычных км/ч, а в км/сут. И нам требуется добавить в формулу расчета скорости коэффициент пересчитывающий время из суток в часы = 24.
При расчете Темпа нужно делить Время на Дистанцию и пересчитывать Время уже не в часы, а в минуты, как этого требуют единицы измерения соответствующего столбца «Темп».
Дистанция за год рассчитывается нарастающим итогом следующим образом. В формуле верхней ячейке F2 указываем только ссылку на соответствующую ячейку в столбце B (Дистанция). А в следующей ячейке с адресом F3 складываем предыдущую дистанцию и текущую (=F2+B3). Далее просто протягиваем вниз за уголок ячейку F3.
Аналогичным образом рассчитываем и накопительный итог для Времени, только вместо Дистанции используем Время и формат времени позволяющий время >24ч.
2016-05-17_193328

Comments are closed.