Ďalšia otázka z môjho seminára v Atlante Power Excel:
Ako môžete zaokrúhliť fakturovateľný čas na nasledujúcu štvrťhodinu?
Povedzte, že si vediete záznam o svojom fakturovateľnom čase. Máte začiatočný a konečný čas. Vzorec ako =E2-D2bude vypočítať uplynulý čas.
Výsledok vo F2 je 55 minút. Predvolené formátovanie je ale zobrazené o 0:55. Vyberte celý stĺpec. Stlačením Ctrl + 1 naformátujte bunky. Vyberte kategóriu Čas. Ktoré z nich by ste mali použiť? Zatiaľ zvoľte 13:30:55, ale problém s týmto výberom uvidíte neskôr.
Politikou vašej spoločnosti je zaokrúhliť všetky fakturácie na nasledujúcu štvrťhodinu. Vykonajte tento výpočet: Denne je 24 hodín. Za hodinu sú 4 štvrťhodiny. Vynásobte 24 * 4 a získate 96. To znamená, že štvrťhodina sa rovná 1/96 dňa.
Pomocou vzorca =CEILING(F2,1/96)zaokrúhlite uplynulý čas v F na ďalšiu štvrťhodinu. Pri spôsobe ukladania času v programe Excel je Poludnie 0,5. 6:00 je 0,25. 0:15 je 1/96 alebo 0,010417. Je ľahšie si zapamätať 1/96 ako 0,010417.
Výsledky sú uvedené nižšie. Všimnite si, ako sa 35 minút zaokrúhľuje na 45 minút. Zdá sa, že to je vzorec, ktorý používa môj právnik … haha, to je len vtip, Esquire Dewey! Nežalujte ma! Viem, že používajú =CEILING(F2,4)…
Tu pozastavené … tento článok sa zaokrúhľuje na ďalšiu štvrťhodinu, pretože to je metóda, ktorú Larry na mojom seminári používa. Existuje veľká šanca, že vaša spoločnosť zaokrúhli na niečo iné. Obrázok nižšie ukazuje, ako sa zaokrúhliť na päť minút alebo 1 minútu. Podobnú logiku môžete použiť na zaokrúhlenie na najbližších 6 minút (1/240) alebo 12 minút (1/120).
Tiež - jemný návrh ukazujúci rozdiel medzi PODLAHOU, KROVOM a STROPOM. Funkcia FLOOR v B9: B22 sa vždy zaokrúhľuje nadol. MROUND v C9: C22 sa zaokrúhľuje na najbližšie. STROP v D9: D22 sa zaokrúhľuje.
Poznámka
Popis v programe Excel navrhne, aby ste namiesto FLOOR alebo CEILING použili FLOOR.MATH alebo CEILING.MATH. Nové verzie týchto funkcií „.Math“ pracujú so zápornými číslami odlišne. Pretože čas v programe Excel nikdy nemôže byť negatívny, je v poriadku používať bežné PODLAHY alebo STROPY.
Zmeňte zlomok z 1/96 na 1/288 zaokrúhlený na päťminútové prírastky.
Prevod časov na hodiny
Ďalší návrh od Larryho: Prevod času na skutočné hodiny. Takto, keď sa vynásobíte fakturačnou sadzbou za hodinu, matematika bude fungovať. Vezmite zaokrúhlený uplynulý čas a vynásobte 24, aby ste získali desatinný počet hodín. Výsledok nezabudnite formátovať ako číslo s dvoma desatinnými miestami a nie ako čas.
Aký formát času použiť?
Je čas hovoriť o týchto možnostiach formátovania času: Mohli ste zvoliť 13:30, 13:30:55 alebo 37:30:55. Na snímkach obrazovky vyššie som použil 13:30:55. Problém s tým sa objaví na konci týždňa. Keď sčítate desatinné hodiny v stĺpci H, máte 53 fakturovateľných hodín. Keď spočítate časy v G, dostanete päť hodín. Čo sa deje?
Obidve sú správne, ak rozumiete formátu čísel, ktorý ste vybrali. Povedzme, že vstúpiš =NOW()do bunky. Bunku môžete naformátovať tak, aby zobrazovala dátum a čas, iba dátum alebo čas. Excel nemá problém opustiť 29. mája časť 29. mája 08:15:34, ak mu to poviete. Odpoveď v bunke G57 je „2 dni a päť hodín“. Ale keď ste si vybrali 13:37:55 ako formát času, povedali ste Excelu, aby vynechal počet dní a zobrazil vám iba hodiny, minúty a sekundy.
Ak prevediete stĺpec G na formát 37:30:55, získate správny počet hodín:
Titulok nad týmto obrázkom zachytáva malý problém s formátom 37:30:55: zobrazuje sekundy, čo sa zdá byť nevyhnutné, pretože sa zaokrúhľujeme na najbližších pätnásť minút. Po výbere 37:30:55 kliknite na kategóriu Vlastné. Vidíte, že (h):mm:ss;@sa používa formátovací kód čísla . Dôležitou súčasťou sú hranaté zátvorky okolo písmena H.
Ak chcete, aby sa vaše časy zobrazovali v hodinách a minútach, upravte formát čísla a odstráňte: ss.
Pozeraj video
Dnešné video obsahuje na konci bonusový tip na formátovanie desatinných miest ako zlomok.
Prepis videa
Naučte sa Excel z Podcastu, epizóda 2210: Zaokrúhlite na ďalšiu štvrťhodinu.
Ahoj, vitaj naspäť na netcast, som Bill Jelen. Dnes ďalšia otázka z môjho seminára Atlanta Power Excel. Niekto robil tabuľku fakturácie a chcel sa zaokrúhliť na nasledujúcu štvrťhodinu, však? Takže pre každú úlohu tu majú Klient, Počiatočný čas a Koncový čas a pre výpočet uplynulého času - to je jednoduché, či sú to skutočné časy, alebo či sú to len = E2-D2. Takže to hovorí 55 minút, ale hovorí to skutočne zvláštnym spôsobom - „12:55.“ Chceme pokračovať a formátovať to. Vyberte čas a hej, pozri, tu je niekoľko rôznych možností: 13:30; 13:30:55, ktorý by ukazoval sekundy; a potom 37:30:55. Teraz si momentálne vyberiem 13:30, ale neskôr zakrúžkujeme a znova preskúmame túto voľbu. Takže zvolím OK a dvojitým kliknutím to skopírujem,a vidíte, že sa to len robí matematiku - takže tu, 25 minút. Avšak ten, kto položil túto otázku - firme, pre ktorú pracujú -, chce sa zaokrúhliť na nasledujúcu štvrťhodinu.
Dobre, takže, štvrť hodiny. Zamyslime sa nad časmi v programe Excel. Poludnie je 0,5. Ak to naformátujem ako čas-- 12:00:00. A ak urobím 0,25, bude to o 6:00:00. Dobre, takže to funguje tak, že jeden celý deň je číslo 1,0, a teda, ak by sme chceli zistiť 15 minút, bolo by to - je tu 24 hodín x 4 štvrťhodiny, takže by sme museli uviesť = 1/96 tam, a to nám dáva 15 minút. Teraz, 1/96 - tú matematiku si môžem trochu urobiť v hlave: 24x4; 1/96 - je určite jednoduchšie zapamätať si 1/96, ako si pamätať toto desatinné miesto: 0,01041666667. Dobre, takže bolo jednoduchšie použiť tam 1/96, a tak použijeme funkciu zvanú CEILING.
STROP. Vidíte, máme na výber medzi CEILING a CEILING.MATH pre kladné čísla, ktoré sú identické. Je kratší na typ CEILING, takže použijem CEILING. A zaokrúhlené, je tu 1/96 dňa - čo je 15 minút - a to je správna odpoveď v nesprávnom formáte. Tieto formáty skopírujeme cez-- Ctrl + C a potom Alt + E + S + T na prilepenie špeciálnych formátov-- a 55 minút sa zaokrúhli na ďalšiu hodinu. Dvojitým kliknutím to skopírujete a 35 minút sa zaokrúhľuje na ďalších 45; 25 zaokrúhli na ďalších 30. Zdá sa, že to funguje, hoci osoba, ktorá položila túto otázku, sa zaokrúhľuje na ďalších 15. Možno aj vaša spoločnosť používa niečo iné.
Dobre, takže na zaokrúhlenie na najbližšiu štvrťhodinu použijeme 1/96. Ale na zaokrúhlenie na najbližších 5 minút - je tu 24 hodín, v každej hodine je 12 5-minútových období, takže 12x4 = 288. Na zaokrúhlenie na najbližších 5 minút použite 1/288. Ak zaokrúhlite na najbližších 6 minút - je tu 10 6-minútových období x 24 hodín; zaokrúhliš na najbližších 1/240. Ak chcete zaokrúhliť na najbližšiu minútu, vyberte na najbližšiu 1/1440.
Dobre, takže to môžete urobiť, a máme tiež na výber. STROP sa bude vždy zaokrúhľovať, takže 16 minút - poďme na 1/96 - 16 minút sa zaokrúhli na 30 a, hoci som si istý, že môj právnik si myslí, že je to skvelý spôsob fakturácie, je to ku mne nie je fér-- klient. Dobre, takže viete, že CEILING sa zaokrúhli nahor. Môj právnik to nikdy nepoužije: Táto PODLAHA sa vždy zaokrúhli nadol. Ale asi to, čo je najspravodlivejšie, je použiť OKOLIE. Takže, = KOLIESKA času na najbližšiu 1/96, a to bude so všetkým zaokrúhľovať nadol asi na 22,5 minúty; a potom sa to zaokrúhli nahor. Dobre, takže to zaokrúhľuje na všetko, čo má najbližšie. Musíte si teda vybrať, či použijete PODLAHU, MROUND alebo STROP a potom aký bude váš deliteľ - či už to bude 'to bude najbližších 5 minút, najbližšia minúta, alebo, viete, čokoľvek. Dobre, takže tam sa to zaokrúhľuje nahor.
Teraz tu máme bonusový tip - toto bol Larry na seminári - Larry hovorí: „Hej, pozri, nie je naozaj dobré ukazovať zúčtovateľný čas v takýchto časoch; naozaj chceme prepočítať hodiny, pretože naozaj ideš fakturovať za určitú sadzbu - viete, 35 dolárov za hodinu, 100 dolárov za hodinu, 500 dolárov za hodinu, bez ohľadu na to, aká je vaša sadzba - a my musíme byť schopní vynásobiť počet hodín x sadzby. Takže by bolo pekné dokážeme tieto hodiny prepočítať na desatinný čas. Takže tento čas vezmeme v G2 a vynásobíme ho 24, čím získame desatinný počet hodín, ale bude to v nesprávnom formáte. Sú to v tomto prípade vyberieme formát času, ktorý by nemal byť vôbec formátom času; mal by byť iba vo formáte Number. Nezabudnite uviesť minimálne 2 desatinné miesta. Dvojitým kliknutím to skopírujete dole a máme svoje časy. “
V poriadku, ak sa teraz vrátime k našej otázke „Aký formát čísla by sme mali použiť?“, Toto je naozaj dobré miesto pre rozhovor o „Prečo?“. Ak zvolíme všetky tieto čísla, tu uvidíme, že súčet je 53 hodín, a keď sem prídem a vložím funkciu SUMA, dostaneme 53 hodín. To je perfektné. Ale ak používam funkciu SUMA pod stĺpcom času a robím Alt + = alebo AUTOSUM, dá mi to 5 hodín … 5 hodín. Čo je správne-- 53 alebo 5?
Ukázalo sa to v niečom zvláštnom zmysle, obaja majú pravdu. Ale tu je dôvod, prečo majú obaja pravdu. Idem zadať funkciu nazvanú = TERAZ. Uvidíte, že to vlastne nahrávam 9. mája, takže pred 20 dňami a je 7:08 hod. A s = NOW, niekedy chcem ukázať dátum a čas, ale inokedy, len chcem zobraziť dátum. Takže môžem urobiť = TERAZ mi dať aktuálny dátum alebo, môžem urobiť = TERAZ mi dať aktuálny čas. Funkcie Number teda radi skrátia voľno alebo dátum odstránia. A tento, ktorý sme si vybrali tu dole, 13:30, hovorí: „Hej, nechcem vidieť počet dní; ukáž mi čas.“ Áno, to je problém - pretože to je skutočne 2 dni a 5 hodín - 2 dni sú 48 hodín - 2 dni a 5 hodín. A povedali sme to - povedali sme programu Excel: “Hej, neobťažuj sa mi ukazovať dni. “No, nevedeli sme, že to hovoríme Excelu, ale to sme hovorili Excelu, keď sme si vybrali tento alebo tento. Dobre, takže ste nútení zostúpte sem do 37:30:55. To je ten, ktorý nám ukáže plný počet hodín a my dostaneme správnu odpoveď 53 hodín.
Ale zdá sa byť naozaj hlúpe zobrazovať tieto sekundy navyše, keď sa zaokrúhľujeme na 15 minút. Vráťme sa teda k tomu. Keď vyberiete 37:30:55, aby ste sa dozvedeli formát čísla za týmto, kliknite na Vlastné. Uvidíte, že to, vďaka čomu to funguje, sú hranaté zátvorky okolo hodiny. Ale nepotrebujeme: ss. Teraz teda môžeme upraviť tento vlastný formát, takže máme hodiny a minúty. Dnes nemusíme rozumieť, čo znamená; @. Hovorí sa, že pre text stačí zobraziť text; ale, len nechaj tú časť tam, aj keď tomu nerozumieš. Kliknite na OK a teraz máme čas v hodinách a minútach, vynecháme sekundy. Ale súčty fungujú. Naozaj, skutočne jemné, tých 13:30 a tých 13:30:55. Viete, nehovorí sa: „Hej, hovoríš Excelu, aby ignoroval dátum … celé dni.“ Ale,ty si.
V tejto knihe nájdete veci, ako je napríklad Vyťažiť čas z matematiky: LIVe: 54 najlepších tipov na aplikáciu Excel všetkých čias. Kliknutím na toto „I“ v pravom hornom rohu to skontrolujete.
Znelo to ako taká ľahká otázka; Nemôžem uveriť, že už máme 7 minút. Spoločnosť však chce fakturovať za štvrť hodiny a vždy sa chce zaokrúhliť na nasledujúcu štvrťhodinu. Takže 24 hodín denne, 24x4 = 96; 15 minút = 1/96. Použite teda STROP (A2,1 / 96) na zaokrúhlenie na nasledujúcu vyššiu štvrťhodinu. Použite MROUND (A2,1 / 96) na zaokrúhlenie na najbližšiu štvrťhodinu a potom pomocou PODLAHY (A2,1 / 96) na zaokrúhlenie na dolnú štvrťhodinu - vždy sa vráťte späť. Je tu 24 hodín denne, takže môžete urobiť 1/288, aby ste sa dostali na 5-minútové obdobia, 1/240, aby ste sa dostali na 6-minútové obdobia, 1/1440 a zaokrúhliť na najbližšiu minútu. A potom bonusový tip na konci (ktorý formát čísla) - ak sa chystáte zobraziť hodiny presahujúce 24 hodín, vždy sa chcete vrátiť k týmto číslam 37:30:55.
Ak si chcete stiahnuť zošit z dnešného videa, navštívte túto adresu URL dole v popise Youtube.
Chcem sa vám poďakovať, že ste sa zastavili; uvidíme sa nabudúce pri ďalšom netcaste z.
Hej! Ďakujeme, že ste sa nezdržali. Ale tu ešte jeden bonusový tip (ktorý sa ani nebudem obťažovať spomenúť). Čím som tu začal - objasníme to celé - malý vzorec, ktorý prišiel a ukázal na jedno z týchto desatinných miest - teda najbližšiu štvrťhodinu. Chcel som však ukázať, že nie ako 0,010417. Je to úžasné - vo formáte čísla je vlastne niečo, čo sa volá Zlomky, a keď zvolím „Až dve číslice“, dostanem 1/96; ale musím zvládnuť tú 1/288, tak si vyberiem „Až tri číslice“ a stále mi to ukazuje 1/96; ale potom, keď prevediem na tento - 1/288 -, zobrazí sa to správne. Teraz som hádam mal urobiť „štyri číslice“, aby som zvládol 1/1440. Čo to bude robiť? To mi dá … Ale,ak prejdem do formátu čísel a prejdem na vlastné nastavenie a iba tam pridám otáznik, dostaneme sa k najbližšej 1/1440 zlomku. Formát čísla - super, super vec.
Stiahnite si súbor Excel
Stiahnutie súboru programu Excel: zaokrúhlovanie na štvrťhodinu.xlsx
Excel myslel dňa
Požiadal som svojich priateľov programu Excel Master o radu o programe Excel. Dnešná myšlienka na zamyslenie:
„Priatelia nedovolia priateľom používať NEPRIAME“
Jordan Goldmeier
