Še eno vprašanje z mojega seminarja v Atlanti Power Excel:
Kako lahko čas za plačilo zaokrožite na naslednjo četrt uro?
Recimo, da vodite dnevnik svojega plačljivega časa. Imate začetni in končni čas. Formula, na primer za =E2-D2izračun pretečenega časa.
Rezultat v F2 je 55 minut. Toda privzeto oblikovanje prikazuje 00:55. Izberite celoten stolpec. Pritisnite Ctrl + 1 za oblikovanje celic. Izberite kategorijo Čas. Katero od teh bi morali uporabiti? Za zdaj izberite 13:30:55, vendar boste kasneje videli težavo s to izbiro.
Politika vašega podjetja je, da vse račune zaokrožite do naslednje četrt ure. Naredite ta izračun: Na dan je 24 ur. Na uro so 4 četrt ure. Pomnožite 24 * 4, da dobite 96. To pomeni, da je četrt ure enaka 1/96 dneva.
Uporabite formulo, =CEILING(F2,1/96)da pretečeni čas v F zaokrožite na naslednjo četrt uro. V Excelovi metodi shranjevanja časa je opoldne 0,5. 6 AM je 0,25. 00:15 je 1/96 ali 0,010417. Lažje si je zapomniti 1/96 kot 0,010417.
Rezultati so prikazani spodaj. Upoštevajte, kako se 35 minut zaokroži na 45 minut. Zdi se, da je to formula, ki jo uporablja moj odvetnik … haha, to je samo šala Esquire Dewey! Ne toži me! Vem, da uporabljajo =CEILING(F2,4)…
Zaustavitev tukaj … ta članek zaokroža na naslednjo četrt uro, ker to metodo uporablja Larry na mojem seminarju. Obstaja velika verjetnost, da družba zaokroži na kaj drugega. Spodnja slika prikazuje, kako zaokrožiti na pet minut ali 1 minuto. Podobno logiko lahko uporabite za zaokrožitev na najbližjih 6 minut (1/240) ali 12 minut (1/120).
Prav tako - nežen predlog, ki prikazuje razliko med TLOM, TLOM in STROPOM. Funkcija FLOOR v B9: B22 se vedno zaokroži. MROUND v C9: C22 zaokroži na najbližje. STROP v D9: D22 se zaokroži.
Opomba
V Excelovem namigu boste predlagali uporabo FLOOR.MATH ali CEILING.MATH namesto FLOOR ali CEILING. Nove različice teh funkcij ".Math" drugače obravnavajo negativne številke. Ker čas v Excelu nikoli ne more biti negativen, je v redu, če uporabljate običajni TL ali STROP.
Spremenite ulomek z 1/96 na 1/288 na krožne do petminutne korake.
Pretvorba časov v ure
Še en Larryjev predlog: Pretvorite čase v realne ure. Na ta način matematika deluje, ko pomnožite s hitrostjo zaračunavanja na uro. Vzemite zaokroženi pretečeni čas in pomnožite s 24, da dobite decimalno število ur. Rezultat formatirajte kot število z dvema decimalnima mestoma in ne kot čas.
Kateri časovni format uporabiti?
Čas je, da se pogovorimo o možnostih oblikovanja časa: lahko bi izbrali 13:30, 13:30:55 ali 37:30:55. V zgornjih posnetkih zaslona sem uporabil 13:30:55. Težava s tem se pojavi konec tedna. Ko v stolpcu H seštejete decimalne ure, imate 53 obračunanih ur. Ko seštejete čase v G, dobite pet ur. Kaj se dogaja?
Oba sta pravilna, če razumete izbrano obliko številke. Recimo, da vstopite =NOW()v celico. Celico lahko formatirate tako, da prikazuje datum in uro, samo datum ali samo čas. Excel ne bo imel težav s tem, da bo 29. maja 29. maja ob 08:15:34 končal, če mu boste to naročili. Odgovor v celici G57 je "2 dni in pet ur". Ko pa ste kot časovno obliko izbrali 13:37:55, ste Excelu rekli, naj pusti število dni in vam prikaže samo ure, minute in sekunde.
Če pretvorite stolpec G v format 37:30:55, dobite pravilno število ur:
Napis nad sliko zajema majhno težavo v formatu 37:30:55: prikazuje sekunde, kar se zdi nepotrebno, saj zaokrožujemo na najbližjih petnajst minut. Po izbiri 37:30:55 kliknite na kategorijo Po meri. Vidite, da (h):mm:ss;@se uporablja koda za oblikovanje števil . Pomemben del so oglati oklepaji okoli H.
Če želite prikazati svoje ure v urah in minutah, uredite številčno obliko in odstranite: ss.
Oglejte si video
Današnji videoposnetek vsebuje na koncu še bonus namig za formatiranje decimalnih mest kot delček.
Video zapis
Naučite se Excel iz podcasta, epizoda 2210: zaokrožite do naslednje četrtletne ure.
Hej, dobrodošli nazaj na netcastu, jaz sem Bill Jelen. Danes še eno vprašanje z mojega seminarja v Atlanti Power Excel. Nekdo je delal preglednico za obračun in hotel je zaokrožiti na naslednjo četrt uro, kajne? Torej, za vsako nalogo tukaj imajo odjemalca, čas začetka in čas konca in za izračun pretečenega časa - to je preprosto, če so to dejanski časi ali če so samo = E2-D2. Torej, to pomeni 55 minut, vendar je res čudno - "00:55". Želimo nadaljevati in to formatirati. Izberite Čas in glej, tukaj je na voljo nekaj različnih možnosti: 13:30; 13:30:55, kar bi kazalo sekunde; in nato 37:30:55. Zdaj, za zdaj bom izbral 13:30, kasneje pa bomo krožili nazaj in ponovno preučili to izbiro. Torej, izberem V redu in dvokliknem, da ga kopiram,in vidite, da gre samo za matematiko - torej tukaj 25 minut. Toda oseba, ki je postavila to vprašanje - podjetje, v katerem delajo - želi zaokrožiti na naslednjo četrt uro.
Torej, četrt ure. Pomislimo na čase v Excelu. Opoldne je 0,5. Če to oblikujem kot čas-- 12:00:00 PM. In če naredim 0,25, bo to 6:00:00. V redu, tako to deluje tako, da je en cel dan številka 1.0, in če bi želeli ugotoviti 15 minut, bi bilo - tam je 24 ur x 4 četrt ure, zato bi morali postaviti = 1/96 notri, to pa nam daje 15 minut. Zdaj, 1/96 - nekako si lahko računam v glavi: 24x4; 1/96 - zagotovo si je lažje zapomniti 1/96 kot pa to decimalko: 0,01041666667. V redu, tam je bilo lažje uporabljati 1/96, zato bomo uporabili funkcijo, imenovano STROP.
STROP. Glejte, pri pozitivnih številkah, ki so enake, lahko izbiramo med STROPOM in STROPOM. Krajši je tip STROP, zato bom uporabil STROP. In zaokroženo je 1/96 dneva - kar je 15 minut - in to je pravi odgovor v napačni obliki. Te formate bomo prekopirali čez - Ctrl + C, nato pa Alt + E + S + T, da prilepite posebne formate - in 55 minut se zaokroži do naslednje ure. Dvokliknite, da to kopirate, in glejte, 35 minut se zaokroži na naslednjih 45; 25 zaokroži do naslednjih 30. Zdi se, da deluje, čeprav oseba, ki je postavila to vprašanje, zaokroži na naslednjih 15. Mogoče celo vaše podjetje uporablja nekaj drugega.
V redu, torej za zaokrožitev na najbližjo četrt uro uporabimo 1/96. Toda zaokrožiti na najbližjih 5 minut - 24 ur je, v vsaki uri je 12 petminutnih obdobij, torej 12x4 = 288. Z 1/288 zaokrožite na najbližjih 5 minut. Če zaokrožite na najbližjih 6 minut - obstaja 10 6-minutnih obdobij x 24 ur; zaokrožite na najbližjo 1/240. Za zaokrožitev na najbližjo minuto izberite na najbližjo 1/1440.
V redu, torej lahko to storite in tudi mi imamo izbiro. STROP se bo vedno zaokrožil, torej 16 minut - pojdimo na 1/96 tam - 16 minut se zaokroži na 30, in čeprav sem prepričan, da moj odvetnik meni, da je to odličen način za obračun, je ni resnično pošteno do mene - stranka. V redu, torej veste, da se bo STROP zaokrožil. Moj odvetnik tega ne bo nikoli uporabil: TLA se bodo vedno zaokrožila navzdol. Toda verjetno je najbolj pošteno pri tem uporabiti MROUND. Torej, = MROUND of Time na najbližjo 1/96, in to se bo zaokrožilo za vse do približno 22,5 minut; in potem se bo po tem zaokrožilo. V redu, torej zaokroži na tisto, kar je najbližje. Torej, izbrati morate, ali boste uporabljali FLOOR, MROUND ali STROP, nato pa kakšen bo vaš delitelj - alibo na najbližjih 5 minut, najbližjo minuto ali, veste, karkoli že. Torej, tam smo zaokrožili.
Zdaj pa tu še bonus namig - to je bil Larry na tem seminarju - Larry pravi: "Hej, glej, v takšnih časih ni prav dobro prikazovati obračunanega časa; resnično želimo pretvoriti v ure, ker resnično greš da zaračunavamo po neki stopnji - veste, 35 USD / uro, 100 USD / uro, 500 USD / uro, ne glede na vašo stopnjo - in moramo biti sposobni pomnožiti ure x cene. Torej, lepo bi bilo lahko te ure pretvorimo v decimalni čas. Torej vzamemo ta čas v G2 in ga pomnožimo s 24, kar nam bo dalo decimalno število ur, vendar bo v napačni obliki. v tem primeru izbrala časovno obliko, ko to sploh ne bi smela biti časovna oblika; morala bi biti samo v obliki številke. Pazite, da vključite vsaj dve decimalni mesti. Dvakrat kliknite, da jo kopirate, in dobimo svoje čase. "
V redu, zdaj, ko se vrnemo k našemu vprašanju "Kateri format številk naj uporabimo?", Je to resnično dobro mesto za pogovor o "Zakaj?". Če izberemo vse te številke, bomo tukaj videli, da je vsota 53 ur, in če pridem sem in vstavim funkcijo SUM, dobimo 53 ur. To je popolno. Če pa uporabljam funkcijo SUM pod stolpcem časa in naredim Alt + = ali AUTOSUM, mi da 5 ur … 5 ur. Kateri je pravi - 53 ali 5?
No, izkaže se v nekem čudnem smislu, oba imata prav. Ampak, evo, zakaj imata oba prav. Vnesel bom funkcijo, imenovano = ZDAJ. Videli boste, da to dejansko posnamem 9. maja, torej pred 20 dnevi in ura je 7:08 in z = ZDAJ včasih želim prikazati datum in uro, drugič pa samo želim za prikaz datuma. Torej, lahko naredim = ZDAJ mi da trenutni datum ali, lahko naredim = ZDAJ mi da trenutni čas. Funkcije številk tako z veseljem bodisi skrajšajo čas, bodisi odstranijo datum. In ta, ki smo jo izbrali tukaj, ob 13.30, pravi: "Hej, nočem videti števila dni; samo pokaži mi čas." Ja, v tem je težava - ker je to res 2 dni in 5 ur - 2 dneva je 48 ur - 2 dneva in 5 ur. In povedali smo - rekli smo Excelu, "Hej, ne trudi se, da mi pokažeš dni. "No, nismo vedeli, da to govorimo Excelu, toda to smo govorili Excelu, ko smo izbrali tega ali tega. V redu, torej ste prisiljeni pridite sem do 37:30:55. Ta nam bo pokazal celotno število ur in dobili bomo pravi odgovor 53 ur.
Ampak, zdi se res neumno pokazati tiste dodatne sekunde, ko zaokrožujemo na 15 minut. Torej, vrnimo se k temu. Ko izberete 37:30:55, če želite izvedeti obliko števil, ki stoji za tem, kliknite Po meri. Videli boste, da je stvar, zaradi katere deluje, okrogle oklepaje okrog ene ure. Vendar ne potrebujemo: ss. Torej, zdaj lahko uredimo to obliko po meri in tako imamo ure in minute. Danes nam ni treba razumeti, kaj pomeni @; Pravi, da za besedilo samo pokažite besedilo; ampak ta del pustite tam, četudi ga ne razumete. Kliknite V redu in zdaj imamo svoj čas v urah in minutah, pri čemer izpustimo sekunde. Toda vsote delajo. Res, res subtilno, tisti 13:30 in tisti 13:30:55. Veste, ne piše: "Hej, praviš Excelu, naj prezre datum… celotne dni." Ampak,ti si.
Stvari, kot je Taking Time Math, so v tej knjigi: LIVe: 54 največjih nasvetov za Excel vseh časov. Kliknite "I" v zgornjem desnem kotu, da ga preverite.
To je zvenelo kot tako enostavno vprašanje; Ne morem verjeti, da smo že čez 7 minut. Podjetje pa želi račun zaračunati v četrt urah in vedno želi zaokrožiti na naslednjih četrt ur. Torej, 24 ur na dan, 24x4 = 96; 15 minut = 1/96. Torej, uporabite STROP (A2,1 / 96), da zaokrožite na naslednjo višjo četrtinsko uro. Uporabite MROUND (A2,1 / 96), da zaokrožite na najbližjo četrt uro, nato pa FLOOR (A2,1 / 96), da zaokrožite na spodnjo četrt uro - vedno gre nazaj. Na dan je 24 ur, tako da lahko naredite 1/288, da pridete do 5-minutnih obdobij, 1/240, da pridete do 6-minutnih obdobij, 1/1440, da zaokrožite na najbližjo minuto. In potem, na koncu še bonus namig (kateri format številke) - vedno se želite vrniti na tisto 37:30:55, če boste prikazovali ure, ki presegajo 24 ur.
Če želite prenesti delovni zvezek iz današnjega videoposnetka, obiščite ta URL spodaj v opisu Youtube.
Zahvaljujem se vam, da ste se ustavili; se vidimo naslednjič za še eno oddajo od.
Zdravo! Hvala, ker ste se družili. Ampak, tu je še en bonusni namig (ki se ga niti ne bom trudil omenjati). S čim sem začel tukaj - razčistimo celotno stvar - malo formule, ki je prišla in pokazala na eno od teh decimalnih mest - torej najbližja četrt ura. Ampak, hotel sem pokazati, da ne kot 0,010417. To je super - pri oblikovanju številk se dejansko imenuje Fractions in če izberem "Do dve števki", dobim 1/96; vendar moram obdelati to 1/288, zato izberem "Do tri števke" in še vedno mi kaže 1/96; ko pa jo pretvorim v to - 1/288 - jo bo pravilno prikazal. Zdaj bi moral narediti "štiri številke" za obdelavo 1/1440. Kaj bo to storilo? To mi bo dalo … Ampak,če grem v obliko zapisa števil in grem po meri in tam dodam dodaten vprašaj, bomo prišli do najbližje 1/1440 ulomka. Format številke - kul, kul stvar.
Prenesite datoteko Excel
Če želite prenesti datoteko excel: round-to-quarter-hour.xlsx
Excel Misel dneva
Za nasvet glede Excela sem prosil prijatelje Excel Excel. Današnja misel o premišljevanju:
"Prijatelji ne dovolijo prijateljem, da uporabljajo INDIRECT"
Jordan Goldmeier
