Další otázka z mého semináře Atlanta Power Excel:
Jak můžete zaúčtovat fakturovatelný čas až na další čtvrt hodinu?
Řekněme, že si vedete záznam o svém fakturovatelném čase. Máte počáteční a koncový čas. Vzorec jako například =E2-D2vypočítá uplynulý čas.
Výsledek v F2 je 55 minut. Výchozí formátování se ale zobrazuje o 12:55. Vyberte celý sloupec. Stisknutím Ctrl + 1 formátujte buňky. Vyberte kategorii Čas. Které z nich byste měli použít? Prozatím zvolte 13:30:55, ale problém s tímto výběrem uvidíte později.
Zásadou vaší společnosti je zaokrouhlovat všechny fakturace do další čtvrt hodiny. Proveďte tento výpočet: Denně je 24 hodin. Za hodinu jsou 4 čtvrtiny. Vynásobte 24 * 4 a získejte 96. To znamená, že čtvrt hodiny odpovídá 1/96 dne.
Pomocí vzorce =CEILING(F2,1/96)zaokrouhlete uplynulý čas v F na další čtvrt hodinu. V aplikaci Excel pro ukládání času je Noon 0,5. 6:00 je 0,25. 0:15 je 1/96 nebo 0,010417. Je snazší zapamatovat si 1/96 než 0,010417.
Výsledky jsou uvedeny níže. Všimněte si, jak 35 minut zaokrouhlí až na 45 minut. Zdá se, že to je vzorec, který můj právník používá … haha, to je jen vtip Esquire Dewey! Nežalovej mě! Vím, že používají =CEILING(F2,4)…
Pauza zde … tento článek se zaokrouhluje na další čtvrt hodiny, protože to je metoda, kterou Larry na mém semináři používá. Existuje velká šance, že se vaše společnost zaokrouhlí na něco jiného. Obrázek níže ukazuje, jak zaokrouhlit na pět minut nebo 1 minutu. Podobnou logiku můžete použít k zaokrouhlování na nejbližší 6 minut (1/240) nebo 12 minut (1/120).
Také - jemný návrh ukazující rozdíl mezi PODLAHOU, MROUNDEM a STROPEM. Funkce FLOOR v B9: B22 se vždy zaokrouhluje dolů. MROUND v C9: C22 se zaokrouhlí na nejbližší. STROP v D9: D22 zaokrouhluje nahoru.
Poznámka
Popisek aplikace Excel navrhne, abyste místo FLOOR nebo CEILING použili FLOOR.MATH nebo CEILING.MATH. Nové verze „.Math“ těchto funkcí zpracovávají záporná čísla odlišně. Protože čas v aplikaci Excel nikdy nemůže být záporný, je dobré použít běžnou PODLAHU nebo STROP.
Změňte zlomek z 1/96 na 1/288 zaokrouhlením na pětiminutové kroky.
Převod časů na hodiny
Další návrh od Larryho: Převést časy na skutečné hodiny. Tímto způsobem, když vynásobíte fakturační sazbou za hodinu, bude matematika fungovat. Vezměte zaokrouhlený uplynulý čas a vynásobte 24, abyste získali desetinný počet hodin. Výsledek naformátujte jako číslo se dvěma desetinnými místy a ne jako čas.
Jaký formát času použít?
Je čas promluvit si o těchto možnostech formátování času: Mohli jste si vybrat 13:30, 13:30:55 nebo 37:30:55. Na výše uvedených screenshotech jsem použil 13:30:55. Problém s tím se objeví na konci týdne. Když sečtete desetinné hodiny ve sloupci H, máte 53 fakturovatelných hodin. Když sečtete časy v G, dostanete pět hodin. Co se děje?
Oba jsou správné, pokud rozumíte formátu čísel, který jste vybrali. Řekněme, že vstoupíte =NOW()do buňky. Buňku můžete naformátovat tak, aby zobrazovala datum a čas, pouze datum nebo čas. Excel nemá problém vynechat 29. května část 29. května 08:15:34, pokud mu to řeknete. Odpověď v buňce G57 je „2 dny a pět hodin“. Ale když jste si vybrali 13:37:55 jako formát času, řekli jste aplikaci Excel, aby vynechala počet dní a zobrazila vám pouze hodiny, minuty a sekundy.
Pokud převedete sloupec G na formát 37:30:55, získáte správný počet hodin:
Titulek nad tímto obrázkem zachycuje malý problém s formátem 37:30:55: zobrazuje sekundy, což se zdá být zbytečné, protože se zaokrouhlujeme na nejbližších patnáct minut. Po výběru 37:30:55 klikněte na kategorii Vlastní. Můžete vidět, že (h):mm:ss;@je použit formátovací kód čísla . Hranaté závorky kolem H jsou důležitou součástí.
Chcete-li zobrazit své hodiny v hodinách a minutách, upravte formát čísla a odeberte: ss.
Sledovat video
Dnešní video obsahuje na konci bonusový tip pro formátování desetinných míst jako zlomek.
Přepis videa
Learn Excel from Podcast, Episode 2210: Round Up to the Next Quarter Hour.
Hej, vítej zpět na netcastu, jsem Bill Jelen. Dnes další otázka z mého semináře Atlanta Power Excel. Někdo dělal tabulku fakturace a chtěl se zaokrouhlit na další čtvrt hodinu, že? Takže pro každý úkol zde mají Klienta, Počáteční čas a Koncový čas a pro výpočet Uplynulého času - to je jednoduché, pokud jsou skutečné časy, nebo pokud jsou jen = E2-D2. Říká to tedy 55 minut, ale říká to opravdu divným způsobem - „12:55.“ Chceme pokračovat a formátovat to. Vyberte čas a hej, podívejte se, tady je několik různých možností: 13:30; 13:30:55, což by ukazovalo sekundy; a pak 37:30:55. Teď si právě teď vyberu 13:30, ale později zakroužkujeme a tuto volbu znovu přezkoumáme. Takže jsem zvolil OK a dvojitým kliknutím to zkopíroval,a vidíte, že to dělá jen matematiku - tak tady, 25 minut. Ale osoba, která položila tuto otázku - firma, pro kterou pracují - chtějí zaokrouhlit na další čtvrt hodiny.
Dobře, takže, čtvrt hodiny. Zamysleme se nad časy v aplikaci Excel. Poledne je 0,5. Pokud to naformátuji jako čas - 12:00:00 PM. A když udělám 0,25, bude to 6:00:00. Dobře, tak to funguje tak, že jeden celý den je číslo 1,0, a tak, kdybychom chtěli zjistit 15 minut, bylo by to - je tu 24 hodin x 4 čtvrt hodiny, takže bychom museli dát = 1/96 tam, a to nám dává 15 minut. Teď 1/96 - můžu si tu matematiku v hlavě udělat: 24x4; 1/96 - je určitě snazší zapamatovat si 1/96, než si pamatovat toto desetinné místo: 0,01041666667. Dobře, takže tam bylo snazší použít 1/96, a tak použijeme funkci zvanou CEILING.
STROP. Podívejte se, máme na výběr mezi CEILING a CEILING.MATH pro kladná čísla, která jsou identická. Typ CEILING je kratší, takže použiji CEILING. Zaokrouhleno je 1/96 dne - což je 15 minut - a to je správná odpověď ve špatném formátu. Zkopírujeme tyto formáty přes-- Ctrl + C a poté Alt + E + S + T pro vložení speciálních formátů - a 55 minut zaokrouhlí na další hodinu. Poklepáním to zkopírujte a 35 minut se zaokrouhlí na dalších 45; 25 zaokrouhlení na dalších 30. Zdá se, že to funguje, ačkoli osoba, která položila tuto otázku, se zaokrouhluje na dalších 15. Možná i vaše společnost používá něco jiného.
Dobře, takže zaokrouhleno na nejbližší čtvrt hodinu použijeme 1/96. Ale zaokrouhlit na nejbližších 5 minut - je tu 24 hodin, v každé hodině je 12 5minutových období, takže 12x4 = 288. Pomocí 1/288 zaokrouhlete na nejbližších 5 minut. Pokud zaokrouhlíte na nejbližších 6 minut - je tu 10 6minutových období x 24 hodin; zaokrouhlíte na nejbližší 1/240. Chcete-li zaokrouhlit na nejbližší minutu, vyberte na nejbližší 1/1440.
Dobře, takže to můžete udělat, a máme také na výběr. CEILING se vždy zaokrouhlí nahoru, takže 16 minut - pojďme na 1/96 tam - 16 minut se zaokrouhlí nahoru na 30, a zatímco jsem si jistý, že můj právník si myslí, že je to skvělý způsob fakturace, je ke mně to není fér-- klient. Dobře, takže víte, že se CEILING zaokrouhluje nahoru. Můj právník to nikdy nepoužije: Tato PODLAHA se vždy zaokrouhlí dolů. Pravděpodobně je ale nejspravedlivější použít MROUND. Takže = MROUND of the Time na nejbližší 1/96, a to se zaokrouhlí dolů na všechno až na asi 22,5 minut; a poté se to zaokrouhlí nahoru. Dobře, takže to zaokrouhlí na cokoli nejblíže. Musíte si tedy vybrat, zda použijete PODLAHU, MROUND nebo STROP, a jaký bude váš dělitel - ať už to 'Bude to na nejbližších 5 minut, nejbližší minutu, nebo, víš, na cokoli. Dobře, tak jsme zaokrouhlovali nahoru.
Bonusový tip zde - to byl Larry na semináři - Larry říká: „Hele, podívej, není opravdu dobré ukazovat fakturovatelný čas v takových dobách; opravdu chceme převést hodiny, protože opravdu jdeš účtovat za určitou sazbu - víte, 35 $ / hod., 100 $ / hod., 500 $ / hod., bez ohledu na to, jaká je vaše sazba - a my musíme být schopni vynásobit počet hodin x sazby. Takže by bylo hezké být schopni převést tyto hodiny na desetinnou dobu. Takže vezmeme ten čas v G2 a vynásobíme ho 24, což nám dá desetinný počet hodin, ale bude to ve špatném formátu. Jsou v tomto případě zvolíte formát času, kdy by to neměl být vůbec formát času; měl by být pouze ve formátu Number. A nezapomeňte uvést alespoň 2 desetinná místa. Poklepáním to zkopírujte dolů a máme své časy. “
Dobře, vraťme se nyní k naší otázce „Který formát čísel bychom měli použít?“, Toto je opravdu dobré místo pro rozhovor o „Proč?“. Pokud zvolíme všechna tato čísla, zde uvidíme, že součet je 53 hodin, a když sem sestoupím a vložím funkci SUMA, dostaneme 53 hodin. To je skvělé. Pokud ale používám funkci SUM pod časovým sloupcem a dělám Alt + = nebo AUTOSUM, dává mi to 5 hodin … 5 hodin. Který má pravdu-- 53 nebo 5?
Ukázalo se to v nějakém zvláštním smyslu, oba mají pravdu. Ale tady je důvod, proč mají oba pravdu. Chystám se zadat funkci nazvanou = NYNÍ. Uvidíte, že to vlastně nahrávám 9. května, takže před 20 dny a je 7:08 hodin A s = NOW, někdy chci ukázat datum a čas, ale jindy jen chci zobrazit datum. Takže můžu = TEĎ mi dát aktuální datum, nebo můžu = NYNÍ mi dát aktuální čas. Funkce Number jsou tedy rádi, že buď zkrátí volno, nebo datum odstraní. A ten, který jsme si vybrali tady dole, 13:30, říká: „Hele, nechci vidět počet dní; ukaž mi čas.“ Ano, to je problém - protože to je opravdu 2 dny a 5 hodin-- 2 dny jsou 48 hodin-- 2 dny a 5 hodin. A řekli jsme to - řekli jsme Excelu, “Hele, neobtěžuj se mi ukazovat dny. “No, nevěděli jsme, že to říkáme Excelu, ale to jsme říkali Excelu, když jsme si vybrali tento nebo tento. Dobře, takže jsi nucen pojď sem dolů do 37:30:55. To je ten, který nám ukáže plný počet hodin, a dostaneme správnou odpověď 53 hodin.
Ale zdá se být opravdu hloupé ukázat ty sekundy navíc, když se zaokrouhlujeme na 15 minut. Vraťme se tedy k tomu. Poté, co zvolíte 37:30:55, abyste se za tím naučili formát čísla, klikněte na Vlastní. Uvidíte, že to, díky čemu to funguje, jsou hranaté závorky kolem hodiny. Ale nepotřebujeme: ss. Nyní tedy můžeme upravit tento vlastní formát, takže máme hodiny a minuty. Dnes nemusíme rozumět tomu, co znamená @. Říká se, že pro text stačí zobrazit text; ale prostě tu část nechte, i když tomu nerozumíte. Klikněte na OK a nyní máme čas v hodinách a minutách, vynecháme sekundy. Ale součty fungují. Opravdu, opravdu subtilní, 13:30 a 13:30:55. Víte, neříká se: „Hej, říkáš Excelu, aby ignoroval datum… celé dny.“ Ale,ty jsi.
V této knize najdete věci jako Využití času z matematiky: LIVe: 54 největších tipů na Excel všech dob. Kliknutím na toto „I“ v pravém horním rohu to zkontrolujete.
Znělo to jako tak snadná otázka; Nemůžu uvěřit, že už jsme za 7 minut. Společnost však chce fakturovat za čtvrt hodiny a vždy se chce zaokrouhlit na další čtvrt hodinu. Takže 24 hodin denně, 24x4 = 96; 15 minut = 1/96. Použijte tedy STROP (A2,1 / 96) k zaokrouhlení nahoru na další vyšší čtvrthodinu. Použijte MROUND (A2,1 / 96) k zaokrouhlení na nejbližší čtvrthodinu a poté použijte FLOOR (A2,1 / 96) k zaokrouhlení na spodní čtvrthodinu - vždy se vrátí zpět. Je tu 24 hodin denně, takže můžete udělat 1/288, abyste se dostali na 5minutové období, 1/240, abyste se dostali na 6minutové období, 1/1440 zaokrouhlit na nejbližší minutu. A pak bonusový tip na konci (který formát čísla) - vždy se budete chtít vrátit k tomu 37:30:55, pokud budete chtít zobrazit hodiny přesahující 24 hodin.
Chcete-li sešit stáhnout z dnešního videa, přejděte na tuto adresu URL dole v popisu Youtube.
Chci vám poděkovat, že jste se zastavili; uvidíme se příště na dalším netcastu z.
Ahoj! Děkujeme, že jste se drželi. Ale tady ještě jeden bonusový tip (který se ani nebudu obtěžovat zmínit). S čím jsem zde začal - pojďme to celé vyjasnit - malý vzorec, který přišel a ukázal na jedno z těchto desetinných míst - tedy nejbližší čtvrt hodinu. Chtěl jsem ale ukázat, že ne jako 0,010417. Je to úžasné - ve Formátování čísel je vlastně něco, co se nazývá Zlomky, a pokud zvolím „Až dvě číslice“, dostanu 1/96; ale musím to zvládnout 1/288, tak jsem zvolil "Až tři číslice" a stále mi to ukazuje 1/96; ale pak, když převedu na tento - 1/288 - zobrazí se to správně. Myslím, že jsem měl udělat „čtyři číslice“, abych zvládl 1/1440. Co to bude dělat? To mi dá … Ale,pokud přejdu do formátu čísel a přejdu na vlastní, a přidám tam další otazník, dostaneme se na nejbližší 1/1440 zlomku. Formát čísel - skvělá, skvělá věc.
Stáhněte si soubor Excel
Chcete-li stáhnout soubor aplikace Excel: zaokrouhlen na čtvrt hodiny.xlsx
Excel myšlenka dne
Požádal jsem své přátele Excel Master o radu ohledně Excelu. Dnešní myšlenka k zamyšlení:
„Přátelé nedovolte přátelům používat NEPŘÍMÉ“
Jordan Goldmeier
