Еще один вопрос с моего семинара по Power Excel в Атланте:
Как округлить оплачиваемое время до следующей четверти часа?
Допустим, вы ведете журнал оплачиваемого времени. У вас есть время начала и время окончания. Такая формула, как =E2-D2рассчитывает прошедшее время.
Результат по F2 - 55 минут. Но форматирование по умолчанию показывает 12:55. Выберите весь столбец. Нажмите Ctrl + 1, чтобы отформатировать ячейки. Выберите категорию «Время». Что из этого следует использовать? На данный момент выберите 13:30:55, но проблема с этим выбором вы увидите позже.
Политика вашей компании заключается в округлении всех счетов до следующей четверти часа. Сделайте такой расчет: в сутках 24 часа. В часе 4 четверти часа. Умножьте 24 * 4, чтобы получить 96. Это означает, что четверть часа эквивалентна 1/96 дня.
Используйте формулу =CEILING(F2,1/96)для округления прошедшего времени в F до следующей четверти часа. В методе хранения времени в Excel полдень равен 0,5. 6 утра - 0,25. 00:15 - это 1/96 или 0,010417. Легче запомнить 1/96, чем 0,010417.
Результаты показаны ниже. Обратите внимание, как 35 минут округляются до 45 минут. Похоже, это формула, которую использует мой поверенный… ха-ха, это просто шутка, эсквайр Дьюи! Не подавай на меня в суд! Я знаю, что они используют =CEILING(F2,4)…
Пауза здесь … эта статья округляется до следующей четверти часа, потому что это метод, который использует Ларри на моем семинаре. Есть большая вероятность, что ваша компания перейдет к чему-то другому. На изображении ниже показано, как округлить до пяти или 1 минуты. Вы можете использовать аналогичную логику для округления до ближайших 6 минут (1/240) или 12 минут (1/120).
Также - нежное предложение, показывающее разницу между FLOOR, MROUND и CEILING. Функция FLOOR в B9: B22 всегда округляется в меньшую сторону. ОКРУГЛЕНИЕ в C9: C22 округляется до ближайшего. ПОТОЛОК в D9: D22 округляется в большую сторону.
Заметка
Во всплывающей подсказке Excel будет предложено использовать FLOOR.MATH или CEILING.MATH вместо FLOOR или CEILING. Новые версии этих функций ".Math" иначе обрабатывают отрицательные числа. Поскольку время в Excel никогда не может быть отрицательным, можно использовать обычный FLOOR или CEILING.
Измените дробь с 1/96 на 1/288 с округлением до пяти минут.
Перевод времени в часы
Еще одно предложение от Ларри: переводите время в реальные часы. Таким образом, когда вы умножите на тариф за час, математика будет работать. Возьмите затраченное время с округлением и умножьте на 24, чтобы получить десятичное число часов. Обязательно форматируйте результат как число с двумя десятичными знаками, а не время.
Какой формат времени использовать?
Пришло время поговорить о вариантах форматирования времени: вы могли выбрать 13:30, 13:30:55 или 37:30:55. На скриншотах выше я использовал 13:30:55. Проблема с этим появляется в конце недели. Когда вы суммируете десятичные часы в столбце H, у вас есть 53 оплачиваемых часа. Если сложить время в G, получится пять часов. Что происходит?
Оба верны, если вы понимаете выбранный числовой формат. Скажите, что вы входите =NOW()в камеру. Вы можете отформатировать ячейку, чтобы отображать дату и время, только дату или просто время. У Excel не возникнет проблем с выходом из части 29 мая 08:15:34 от 29 мая, если вы скажете ему это сделать. Ответ в ячейке G57: «2 дня и пять часов». Но когда вы выбрали 13:37:55 в качестве формата времени, вы приказывали Excel не указывать количество дней и показывать вам только часы, минуты и секунды.
Если преобразовать столбец G в формат 37:30:55, вы получите правильное количество часов:
Подпись над этим изображением отражает небольшую проблему с форматом 37:30:55: он показывает секунды, что кажется ненужным, поскольку мы округляем до ближайших пятнадцати минут. Выбрав 37:30:55, щелкните категорию Custom. Вы можете видеть, что используется код форматирования чисел (h):mm:ss;@. Квадратные скобки вокруг буквы H - важная часть.
Чтобы время отображалось в часах и минутах, отредактируйте числовой формат, удалив: ss.
Смотреть видео
В конце сегодняшнего видео есть бонусный совет по форматированию десятичных знаков как дроби.
Стенограмма видео
Учитесь Excel из подкаста, серия 2210: округление до следующего четверти часа.
Привет, добро пожаловать обратно в netcast, я Билл Джелен. Сегодня еще один вопрос с моего семинара по Power Excel в Атланте. Кто-то делал электронную таблицу счетов, и они хотели округлить до следующей четверти часа, верно? Итак, для каждой задачи здесь у них есть клиент, время начала и время окончания, и для расчета прошедшего времени - это просто, если это реальное время, или если они просто = E2-D2. Итак, это говорит о 55 минутах, но он говорит это очень странно - «00:55». Мы хотим пойти дальше и отформатировать это. Выберите «Время», и смотрите, здесь есть несколько вариантов: 13:30; 13:30:55, который покажет секунды; а затем 37:30:55. А сейчас я выберу 13:30, но позже мы вернемся к нему и еще раз рассмотрим этот выбор. Итак, я выбираю ОК и дважды щелкаю, чтобы скопировать его,и вы видите, что он просто выполняет вычисления - так вот, 25 минут. Но человек, задавший этот вопрос - фирма, в которой они работают - они хотят округлить до следующей четверти часа.
Хорошо, четверть часа. Давайте подумаем о времени в Excel. Полдень - 0,5. Если я отформатирую это как время - 12:00:00 PM. А если я сделаю 0,25, это будет 6:00:00. Хорошо, так это работает так: один полный день - это число 1.0, и поэтому, если бы мы хотели вычислить 15 минут, это было бы … 24 часа x 4 четверти часа, поэтому нам пришлось бы поставить = 1/96, и это дает нам 15 минут. Теперь, 1/96 - я могу вычислить в уме: 24x4; 1/96 - конечно, легче запомнить 1/96, чем запомнить эту десятичную дробь: 0,01041666667. Хорошо, там было проще использовать 1/96, поэтому мы собираемся использовать функцию под названием CEILING.
ПОТОЛОК. Видите ли, у нас есть выбор между CEILING и CEILING.MATH для положительных чисел, которые идентичны. Это короче типа CEILING, поэтому я собираюсь использовать CEILING. В итоге получается 1/96 дня, то есть 15 минут, и это правильный ответ в неправильном формате. Мы собираемся скопировать эти форматы - Ctrl + C, а затем Alt + E + S + T, чтобы вставить специальные форматы - и округление 55 минут до следующего часа. Дважды щелкните, чтобы скопировать это вниз, и увидите, что 35 минут округляются до следующих 45; 25 округлений до следующих 30. Кажется, это работает, хотя человек, задавший этот вопрос, округляет до следующих 15. Может быть, даже ваша компания использует что-то другое.
Хорошо, поэтому для округления до ближайшей четверти часа мы используем 1/96. Но если округлить до ближайших 5 минут - 24 часа, 12 5-минутных периодов в каждом часе, поэтому 12x4 = 288. Используйте 1/288, чтобы округлить до ближайших 5 минут. Если округлить до ближайших 6 минут - получается 10 6-минутных периодов x 24 часа; округлите до ближайшего 1/240. Чтобы округлить до ближайшей минуты, выберите значение до 1/1440.
Хорошо, ты можешь это сделать, и у нас тоже есть выбор. CEILING всегда округляется, поэтому 16 минут - давайте перейдем к 1/96 - 16 минут округлим до 30, и, хотя я уверен, что мой адвокат считает, что это отличный способ выставить счет, это не совсем честно по отношению ко мне - клиенту. Хорошо, вы знаете, что значение CEILING будет округлено в большую сторону. Мой адвокат никогда не воспользуется этим: этот ЭТАЖ всегда будет округляться в меньшую сторону. Но, наверное, лучше всего использовать MROUND. Итак, = ОКРУГЛЕНИЕ времени до ближайшей 1/96, и это будет округляться до 22,5 минут; а затем, после этого, оно будет округлено. Хорошо, он округляется до ближайшего к нему значения. Итак, вам нужно выбрать, собираетесь ли вы использовать FLOOR, MROUND или CEILING, а затем, каким будет ваш делитель - будь то?будет с точностью до ближайших 5 минут, ближайшей минуты или, знаете, чего угодно. Хорошо, вот мы и собираемся.
А теперь бонусный совет - это был Ларри на том семинаре - Ларри говорит: «Эй, послушайте, не очень хорошо показывать оплачиваемое время в такие моменты; мы действительно хотим преобразовать в часы, потому что вы действительно собираетесь выставлять счета по определенной ставке - вы знаете, 35 долларов в час, 100 долларов в час, 500 долларов в час, независимо от вашей ставки - и нам нужно иметь возможность умножать количество часов на ставки. Итак, было бы неплохо быть в состоянии преобразовать эти часы в десятичное время. Итак, мы берем это время в G2 и умножаем его на 24, и это дает нам десятичное число часов, но оно будет в неправильном формате. Они собираюсь выбрать формат времени в этом случае, когда он вообще не должен быть форматом времени; он должен быть только в числовом формате. И убедитесь, что включены как минимум 2 десятичных знака. Дважды щелкните, чтобы скопировать это вниз, и мы получаем свое время ".
Хорошо, теперь, возвращаясь к нашему вопросу «Какой числовой формат нам следует использовать?», Это действительно хорошее место, чтобы поговорить о «Почему?». Если мы выберем все эти числа, здесь мы увидим, что сумма составляет 53 часа, а если я спущусь сюда и введу функцию SUM, мы получим 53 часа. Это идеально. Но если я использую функцию СУММ под столбцом времени и нажимаю Alt + = или AUTOSUM, это дает мне 5 часов… 5 часов. Что правильно - 53 или 5?
Что ж, оказывается, в каком-то странном смысле они оба правы. Но вот почему они оба правы. Я собираюсь ввести функцию под названием = СЕЙЧАС. Вы увидите, что я на самом деле записываю это еще 9 мая, так что 20 дней назад, это 7:08 утра. И с = СЕЙЧАС иногда я хочу показать дату и время, но в других случаях я просто хочу чтобы показать дату. Итак, я могу сделать = СЕЙЧАС дать мне текущую дату или я могу сделать = СЕЙЧАС дать мне текущее время. Итак, числовые функции с удовольствием сокращают время отключения или убирают дату. И тот, который мы выбрали здесь, 13:30, говорит: «Эй, я не хочу видеть Количество дней; просто покажите мне время». Да, в этом проблема - потому что на самом деле это 2 дня 5 часов, 2 дня это 48 часов, 2 дня и 5 часов. И мы сказали это … мы сказали Excel: "Эй, не пытайся показывать мне дни ». Ну, мы не знали, что говорим об этом Excel, но это то, что мы говорили Excel, когда выбирали тот или этот. Хорошо, так что ты вынужден спуститесь сюда к 37:30:55.Это тот, который покажет нам полное количество часов, и мы получим правильный ответ - 53 часа.
Но кажется действительно глупым показывать эти дополнительные секунды, когда мы округляем до 15 минут. Итак, вернемся к этому. После того, как вы выберете 37:30:55, чтобы узнать числовой формат, стоящий за этим, нажмите Пользовательский. Вы увидите, что все, что заставляет его работать, - это квадратные скобки вокруг часа. Но нам не нужен: ss. Итак, теперь мы можем редактировать этот пользовательский формат, и теперь у нас есть часы и минуты. Сегодня нам не нужно понимать, что означает; @. Он говорит, что для текста просто покажите текст; но просто оставьте эту часть там, даже если вы этого не понимаете. Нажмите OK, и теперь у нас есть время в часах и минутах, не считая секунд. Но итоги работают. Действительно, очень тонко, это 13:30 и 13:30:55. Знаете, здесь не говорится: «Эй, вы говорите Excel игнорировать дату … полные дни». Но,ты.
Такие вещи, как «Занятие математикой времени», находятся в этой книге: ЖИЗНЬ: 54 величайших совета по работе с Excel за все время Щелкните "I" в правом верхнем углу, чтобы проверить это.
Это звучало как такой простой вопрос; Не могу поверить, что прошло уже 7 минут. Но компания хочет выставлять счет за четверть часа и всегда хочет округлить до следующей четверти часа. Итак, 24 часа в сутки, 24х4 = 96; 15 минут = 1/96. Итак, используйте CEILING (A2,1 / 96) для округления до следующей более высокой четверти часа. Используйте MROUND (A2,1 / 96) для округления до ближайшей четверти часа, а затем используйте FLOOR (A2,1 / 96) для округления до нижней четверти часа - всегда возвращается назад. В сутках 24 часа, поэтому вы можете сделать 1/288, чтобы перейти к 5-минутным периодам, 1/240, чтобы перейти к 6-минутным периодам, 1/1440, чтобы округлить до ближайшей минуты. И затем, бонусный совет в конце (какой числовой формат) - вы всегда хотите вернуться к 37:30:55, если вы собираетесь отображать часы, превышающие 24 часа.
Чтобы загрузить книгу из сегодняшнего видео, перейдите по этому URL-адресу в описании Youtube.
Я хочу поблагодарить вас за то, что заглянули; увидимся в следующий раз для другого сетевого вещания от.
Привет! Спасибо, что согласились. Но вот еще один бонусный совет (о котором я даже не буду говорить). С чего я начал здесь - давайте проясним все это - небольшая формула, которая подошла и указала на одно из этих десятичных знаков - итак, ближайшая четверть часа. Но я хотел показать это не как 0,010417. Это потрясающе - в форматировании чисел есть что-то, называемое дробями, и если я выберу «До двух цифр», я получу 1/96; но мне нужно обработать это 1/288, поэтому я выбираю «До трех цифр», и он все равно показывает мне 1/96; но затем, когда я перейду на этот - 1/288 - он покажет это правильно. Думаю, мне следовало набрать «четыре цифры» для работы с 1/1440. Что это будет делать? Это даст мне … Но,если я перейду в Числовой формат, перейду к Пользовательскому и просто добавлю туда дополнительный вопросительный знак, тогда мы перейдем к ближайшей 1/1440 дроби. Числовой формат - крутая штука.
Скачать файл Excel
Чтобы загрузить файл Excel: round-to-четверть часа.xlsx
Идея дня в Excel
Я попросил совета у моих друзей-мастеров Excel по поводу Excel. Сегодняшняя мысль задуматься:
«Друзья не позволяют друзьям использовать КОСВЕННО»
Джордан Голдмайер
