Друг въпрос от моя семинар в Атланта 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 за съхраняване на времето, Noon е 0,5. 6AM е 0,25. 12:15 ч. Е 1/96 или 0,010417. По-лесно е да запомните 1/96, отколкото 0,010417.
Резултатите са показани по-долу. Забележете как 35-те минути завършват до 45 минути. Изглежда, че това е формулата, която адвокатът ми използва … ха-ха, това е просто шега Esquire Dewey! Не ме съдете! Знам, че използват =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. Проблемът с това се появява в края на седмицата. Когато съберете десетичните часове в колона Н, имате 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: Закръглете до следващия четвърт час.
Хей, добре дошли отново в netcast, аз съм Бил Джелен. Днес още един въпрос от моя семинар в Атланта 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 AM. Добре, така че начинът, по който това работи, е, че един пълен ден е числото 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 или таван, а след това какъв ще бъде вашият делител - далище бъде с точност до 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, за да научите числовия формат зад това, щракнете върху Custom. Ще видите, че това, което го кара да работи, е квадратните скоби около час. Но ние не се нуждаем от: ss. И така, сега можем да редактираме този персонализиран формат и така имаме часове и минути. Днес не е нужно да разбираме какво означава; @. Казва се, че за текст просто покажете текста; но просто оставете тази част там, дори и да не я разбирате. Щракнете върху OK и вече разполагаме с времето в часове и минути, като оставим секундите. Но общите резултати работят. Наистина, наистина фино, това 13:30 и това 13:30:55. Знаете ли, не се казва: "Хей, казваш на Excel да игнорира датата … пълните дни." Но,ти си.
Неща като Taking Time Math са в тази книга: LIVe: 54-те най-добри съвета на Excel за всички времена. Щракнете върху това „I“ в горния десен ъгъл, за да го проверите.
Това прозвуча като толкова лесен въпрос; Не мога да повярвам, че вече имаме 7 минути. Но една компания иска да таксува за четвърт час и те винаги искат да закръглят до следващия четвърт час. И така, 24 часа в денонощието, 24x4 = 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. Какво ще направи това? Това ще ми даде … Но,ако вляза в числовия формат и отида в Custom и просто добавя допълнителен въпросителен знак там, тогава ще стигнем до най-близката 1/1440 от дроб. Формат на номера - готино, страхотно нещо.
Изтеглете Excel файла
За да изтеглите файла на Excel: round-to-quarter-hour.xlsx
Мисъл на деня в Excel
Помолих приятелите си в Excel Master за съвети относно Excel. Днешната мисъл за размисъл:
„Приятелите не позволяват на приятелите да използват INDIRECT“
Джордан Голдмайер
