Ще одне питання з мого семінару в Atlanta Power Excel:
Як можна округлити рахунок, що підлягає оплаті, до наступної чверті години?
Скажімо, ви ведете журнал свого часу, що підлягає оплаті. У вас є час початку та закінчення. Формула, така як =E2-D2буде обчислювати минулий час.
Результат у F2 - 55 хвилин. Але форматування за замовчуванням показує 00:55. Виберіть цілу колонку. Натисніть Ctrl + 1, щоб відформатувати комірки. Виберіть категорію Час. Які з них вам слід використовувати? Наразі виберіть 13:30:55, але пізніше ви побачите проблему з цим вибором.
Політика вашої компанії полягає в тому, щоб округлювати всі рахунки до наступної чверті години. Зробіть такий розрахунок: Є 24 години на добу. За годину є 4 чверті години. Помножте 24 * 4, щоб отримати 96. Це означає, що чверть години еквівалентно 1/96 дня.
Використовуйте формулу, =CEILING(F2,1/96)щоб округлити проміжок часу у F до наступної чверті години. У методі зберігання часу в Excel обід складає 0,5. 6 ранку - 0,25. 12:15 - 1/96 або 0,010417. Запам'ятати 1/96 простіше, ніж 0,010417.
Результати наведені нижче. Зверніть увагу, як 35 хвилин округлюються до 45 хвилин. Здається, це формула, яку використовує мій адвокат … ха-ха, це просто жарт Есквайр Дьюї! Не суди мене! Я знаю, що вони використовують =CEILING(F2,4)…
Зупинка тут … ця стаття округлюється до наступної чверті години, тому що саме цей метод використовує Ларрі на моєму семінарі. Існує велика ймовірність того, що ваша компанія обійде щось інше. На малюнку нижче показано, як округлити до п’яти хвилин або 1 хвилини. Ви можете використовувати подібну логіку для округлення з точністю до 6 хвилин (1/240) або 12 хвилин (1/120).
Також - лагідна пропозиція, що показує різницю між ПІДЛОГОЮ, ПЛОТОМ та СТЕЛЕЮ. Функція FLOOR у B9: B22 завжди округлюється. MROUND в C9: C22 округляється з точністю. СТОЛ в D9: D22 округлюється.
Примітка
Підказка Excel запропонує використовувати FLOOR.MATH або CEILING.MATH замість FLOOR або CEILING. Нові версії цих функцій ".Math" обробляють від'ємні числа по-різному. Оскільки час в Excel ніколи не може бути від’ємним, добре використовувати звичайний ПІДЛОГ або СТОЛ.
Змініть дріб з 1/96 на 1/288 на округлення до п’ятихвилинного кроку.
Перетворення часу в години
Ще одна порада Ларрі: перетворіть час на реальні години. Таким чином, коли ви множите на швидкість виставлення рахунків за годину, математика спрацює. Візьміть округлений минулий час і помножте на 24, щоб отримати десяткове число годин. Обов’язково форматуйте результат як число з двома знаками після коми, а не як час.
Який формат часу використовувати?
Настав час поговорити про вибір форматування часу: Ви могли вибрати 13:30, 13:30:55 або 37:30:55. На скріншотах вище я використав 13:30:55. Проблема з цим з’являється наприкінці тижня. Коли ви підсумовуєте десяткові години у стовпці H, у вас є 53 оплачувані години. Коли підсумовуєте час у G, ви отримуєте п’ять годин. Що відбувається?
Обидва правильні, якщо ви розумієте вибраний формат цифр. Скажімо, що ви входите =NOW()в клітинку. Ви можете відформатувати комірку, щоб показати дату та час, лише дату або просто час. Excel не має проблем, щоб зупинити частину від 29 травня від 29 травня 08:15:34, якщо ви скажете це зробити. Відповідь у комірці G57 - "2 дні та п’ять годин". Але коли ви вибрали 13:37:55 як формат часу, ви говорили Excel, щоб відключити кількість днів і показати вам лише години, хвилини та секунди.
Якщо перетворити стовпець G у формат 37:30:55, то ви отримаєте правильну кількість годин:
Заголовок над цим зображенням фіксує невелику проблему у форматі 37:30:55: він показує секунди, що здається непотрібним, оскільки ми округлюємо з точністю до п’ятнадцяти хвилин. Вибравши 37:30:55, натисніть на спеціальну категорію. Ви бачите, що використовується код форматування цифр (h):mm:ss;@. Квадратні дужки навколо Н - важлива частина.
Щоб відображати час у годинах та хвилинах, відредагуйте формат цифр, щоб видалити: ss.
Переглянути відео
Сьогоднішнє відео містить бонусну підказку в кінці для форматування десяткових знаків як дробу.
Стенограма відео
Дізнайтеся Excel з подкасту, епізод 2210: округлення до наступної чверті години.
Привіт, ласкаво просимо до мережі, я Білл Джелен. Сьогодні ще одне питання з мого семінару в Atlanta Power Excel. Хтось робив електронну таблицю виставлення рахунків, і вони хотіли округлити інформацію до наступної чверті години, так? Отже, для кожного завдання тут у них є Клієнт, Час початку та Час закінчення, і для обчислення минулого часу - це просто, якщо це реальні часи, або якщо вони просто = E2-D2. Отже, це говорить 55 хвилин, але це говорить по-справжньому дивним чином - "00:55". Ми хочемо продовжити і відформатувати це. Виберіть «Час», і привіт, дивіться, тут є кілька різних варіантів: 13:30; 13:30:55, що показувало б секунди; а потім 37:30:55. Зараз, зараз, я збираюся вибрати 13:30, але пізніше ми повернемося назад і переглянемо цей вибір. Отже, я вибираю OK і двічі клацну, щоб скопіювати його,і ви бачите, що це просто математика - отже, 25 хвилин. Але людина, яка задала це запитання - фірма, в якій вони працюють, - хоче охопити до наступної чверті години.
Гаразд, так, чверть години. Давайте подумаємо про час в Excel. Полудень - 0,5. Якщо я відформатую це як час - 12:00:00 PM. А якщо я зроблю 0,25, це буде 6:00:00 ранку. Добре, так це працює так, що один повний день - це число 1.0, і тому, якби ми хотіли зрозуміти 15 хвилин, це було б - там 24 години х 4 чверті години, тому нам довелося б поставити = 1/96 там, і це дає нам 15 хвилин. Зараз 1/96 - я можу якось робити цю математику в голові: 24x4; 1/96 - звичайно легше запам'ятати 1/96, ніж запам'ятати цей десятковий знак: 0,01041666667. Гаразд, так було простіше використовувати 1/96 там, і тому ми збираємось використовувати функцію, яка називається СТЕЛА.
СТОЛ. Дивіться, у нас є вибір між СТОЛОМ і СТОЛОМ. МАТЕМАТИЧНІ позитивні числа, що ідентичні. Ввести СТЕЛЬ коротше, тому я збираюся використовувати СТЕЛЬ. І округлено 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.
Гаразд, ви можете це зробити, і ми також маємо вибір. СТОЛО завжди збирається округлити, тож 16 хвилин - перейдемо до 1/96 там - 16 хвилин округлиться до 30, і, хоча я впевнений, мій адвокат вважає, що це чудовий спосіб виставлення рахунків, це не дуже чесно по відношенню до мене - клієнта. Гаразд, отже, ви знаєте, СТОЛ буде округлюватися Мій адвокат ніколи не збирається цим користуватися: Цей ПОВЕРХ завжди буде округлюватися. Але, мабуть, найсправедливіше тут - це використовувати MROUND. Отже, = MROUND Часу з точністю до 1/96, і це округлюється для всього приблизно до 22,5 хвилин; а потім, після цього, воно закінчиться. Гаразд, тому він округляється до будь-якого найближчого. Отже, вам слід вибрати, чи будете ви використовувати FLOOR, MROUND або СТОЛ, а потім яким буде ваш дільник - чи це буде 's буде з точністю до 5 хвилин, найближчої хвилини або, ви знаєте, чого завгодно. Гаразд, отже, ми збираємося.
Зараз, бонусна порада тут - це був Ларрі на цьому семінарі - Ларрі каже: "Гей, дивись, не дуже добре показувати оплачуваний час у такі часи; ми дійсно хочемо перевести в години, тому що ти справді збираєшся щоб виставляти рахунки за якоюсь ставкою - ви знаєте, 35 доларів на годину, 100 доларів на годину, 500 доларів на годину, незалежно від вашої ставки - і нам потрібно мати можливість помножити години х на тарифи. Отже, було б непогано перетворити ці години в десятковий час. Отже, ми беремо цей час у G2 і множимо його на 24, і це дасть нам десяткове число годин, але це буде в неправильному форматі. Вони збирається вибрати формат часу в цьому випадку, коли це взагалі не повинен бути форматом часу; він повинен бути просто у форматі Число. І обов’язково включити принаймні 2 знаки після коми. Двічі клацніть, щоб скопіювати це вниз, і ми отримуємо свої часи ".
Добре, тепер, повертаючись до нашого запитання "Який формат чисел нам слід використовувати?", Це справді гарне місце, щоб поговорити про "Чому?". Якщо ми виберемо всі ці числа, тут ми побачимо, що сума становить 53 години, а якщо я спускаюся сюди і ввожу функцію SUM, ми отримуємо 53 години. Це ідеально. Але, якщо я використовую функцію SUM нижче стовпця часу, і я роблю Alt + = або AUTOSUM, це дає мені 5 годин … 5 годин. Що правильно - 53 чи 5?
Ну, виявляється в якомусь дивному сенсі, вони обидва мають рацію. Але ось ось чому вони обидва мають рацію. Я збираюся ввести функцію з назвою = ЗАРАЗ. Ви побачите, що я насправді записую це 9 травня, отже, 20 днів тому, а зараз 7:08 ранку. І з = ЗАРАЗ, іноді я хочу показати дату та час, але інший раз, я просто хочу щоб показати дату. Отже, я можу зробити = ЗАРАЗ дайте мені поточну дату, або, я можу зробити = ЗАРАЗ, дайте мені поточний час. Отже, функції Number можуть із задоволенням або скоротити час вимкнення, або видалити дату. А цей, який ми вибрали тут, о 13:30, каже: "Гей, я не хочу бачити Кількість днів; просто покажи мені час". Так, це проблема - адже це насправді 2 дні і 5 годин - 2 дні - це 48 годин - 2 дні і 5 годин. І ми сказали це - ми сказали Excel, "Гей, не турбуйся, показуй мені дні. "Ну, ми не знали, що говоримо це Excel, але саме це ми говорили Excel, коли обирали цей чи цей. Добре, тож ти змушений спускаємось сюди до 37:30:55. Це та, яка покаже нам повну кількість годин, і ми отримаємо правильну відповідь 53 години.
Але, здається справді безглуздо показувати ті зайві секунди, коли ми округляємо до 15 хвилин. Отже, повернімось до цього. Після того, як ви вибрали 37:30:55, щоб дізнатись формат чисел, що стоїть за цим, натисніть Налаштування. Ви побачите, що все, що змушує це працювати, це квадратні дужки близько години. Але нам не потрібно: ss. Отже, тепер ми можемо редагувати цей спеціальний формат, і тому у нас є години та хвилини. Сьогодні нам не потрібно розуміти, що означає; @. Це говорить, що для тексту просто покажіть текст; але просто залиште цю частину там, навіть якщо ви її не розумієте. Натисніть кнопку ОК, і ми тепер маємо свій час у годинах і хвилинах, залишаючи секунди. Але загальні результати працюють. Дійсно, дуже тонко, що 13:30 та що 13:30:55. Знаєте, там не сказано: "Гей, ти кажеш Excel ігнорувати дату … цілі дні". Але,ти є.
Такі речі, як “Взяти математику за часом”, містяться в цій книзі: LIVe: 54 найкращі поради Excel усіх часів. Клацніть на "Я" у верхньому правому куті, щоб перевірити це.
Це звучало як таке легке запитання; Я не можу повірити, що ми вже 7 хвилин. Але компанія хоче виставити рахунок за чверть годин, і вони завжди хочуть округлити інформацію до наступної чверті години. Отже, 24 години на добу, 24х4 = 96; 15 хвилин = 1/96. Отже, використовуйте СТЕЛЬ (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-quarter-hour.xlsx
Думка дня в Excel
Я попросив своїх друзів Excel Master порадити їх щодо Excel. Сьогоднішня думка поміркувати:
"Друзі не дозволяють друзям використовувати INDIRECT"
Джордан Гольдмаєр
