Vēl viens jautājums no mana Atlanta Power Excel semināra:
Kā jūs varat noapaļot rēķina laiku līdz nākamajai ceturtdaļas stundai?
Pieņemsim, ka jūs glabājat sava rēķina apmaksas laika žurnālu. Jums ir sākuma un beigu laiks. Tāda formula kā =E2-D2aprēķinās pagājušo laiku.
Rezultāts F2 ir 55 minūtes. Bet noklusējuma formatējums rāda 00:55. Izvēlieties visu kolonnu. Nospiediet Ctrl + 1, lai formatētu šūnas. Atlasiet kategoriju Laiks. Kurus no šiem jums vajadzētu izmantot? Pagaidām izvēlieties 13:30:55, bet vēlāk redzēsiet problēmu ar šo izvēli.
Jūsu uzņēmuma politika ir visu rēķinu noapaļošana līdz nākamajai ceturtdaļas stundai. Veiciet šo aprēķinu: dienā ir 24 stundas. Stundā ir 4 ceturtdaļas stundas. Reiziniet 24 * 4, lai iegūtu 96. Tas nozīmē, ka ceturtdaļa stunda ir līdzvērtīga 1/96 dienai.
Izmantojiet formulu, =CEILING(F2,1/96)lai noapaļotu pagājušo laiku F līdz nākamajai ceturtdaļas stundai. Izmantojot Excel metodi laika uzkrāšanai, Noon ir 0,5. 6:00 ir 0,25. 00:15 ir 1/96 vai 0.010417. Vieglāk ir atcerēties 1/96 nekā 0,010417.
Rezultāti ir parādīti zemāk. Ievērojiet, kā 35 minūtes noapaļo līdz 45 minūtēm. Šķiet, ka šī ir mana advokāta izmantotā formula … haha, tas ir tikai joks Esquire Dewey! Nesūdzi mani tiesā! Es zinu, ka viņi izmanto =CEILING(F2,4)…
Pārtraukšana šeit … šis raksts tiek noapaļots uz nākamo ceturtdaļu stundu, jo tā ir metode, kuru Larijs manā seminārā izmanto. Pastāv labas izredzes, ka jūs kompānija iegūst kaut ko citu. Zemāk redzamajā attēlā parādīts, kā noapaļot līdz piecām minūtēm vai 1 minūtei. Jūs varētu izmantot līdzīgu loģiku, lai noapaļotu līdz tuvākajām 6 minūtēm (1/240) vai 12 minūtēm (1/120).
Arī - maigs ieteikums, kas parāda atšķirību starp GRĪDU, MROUND un CEILING. B9: B22 funkcija FLOOR vienmēr noapaļo uz leju. CRO: C22: C22 noapaļo līdz tuvākajam. D9: D22 GRIEZUMS noapaļojas uz augšu.
Piezīme
Excel rīka padoms ieteiks FLOOR.MATH vai CEILING.MATH izmantot grīdas FLOOR vai CEILING vietā. Šo funkciju jaunās ".Math" versijas atšķirīgi apstrādā negatīvos skaitļus. Tā kā laiks programmā Excel nekad nevar būt negatīvs, ir labi izmantot parasto GRĪDU vai GRIETUMU.
Mainiet daļu no 1/96 uz 1/288 uz soli noapaļojot līdz piecām minūtēm.
Laiku konvertēšana stundās
Vēl viens Lerija ieteikums: Konvertējiet laikus uz reālām stundām. Tādā veidā, reizinot ar norēķinu likmi stundā, matemātika darbosies. Paņemiet noapaļoto pagājušo laiku un reiziniet ar 24, lai iegūtu stundu decimāldaļu. Noteikti formatējiet rezultātu kā skaitli ar divām zīmēm aiz komata, nevis laiku.
Kuru laika formātu lietot?
Ir pienācis laiks runāt par šīm laika formatēšanas iespējām: Jūs varētu izvēlēties 13:30, 13:30:55 vai 37:30:55. Iepriekš redzamajos ekrānuzņēmumos esmu izmantojis 13:30:55. Problēma ar to parādās nedēļas beigās. Saskaitot decimālās stundas H slejā, jums ir 53 apmaksājamas stundas. Saskaitot laikus G, jūs saņemat piecas stundas. Kas notiek?
Abi ir pareizi, ja saprotat izvēlēto ciparu formātu. Sakiet, ka ievadāt =NOW()šūnā. Jūs varētu formatēt šūnu, lai parādītu datumu un laiku, tikai datumu vai tikai laiku. Programmai Excel nav problēmu pārtraukt 29. maija daļu no 29. maija pulksten 08:15:34, ja jūs to liekat darīt. Atbilde šūnā G57 ir "2 dienas un piecas stundas". Bet, kad kā laika formātu izvēlējāties 13:37:55, jūs teicāt programmai Excel atstāt dienu skaitu un rādīt tikai stundas, minūtes un sekundes.
Ja konvertējat G sleju formātā 37:30:55, tiek iegūts pareizais stundu skaits:
Paraksts virs šī attēla atspoguļo nelielu problēmu 37:30:55 formātā: tajā tiek rādītas sekundes, kas šķiet lieki, jo mēs noapaļojamies līdz tuvākajām piecpadsmit minūtēm. Pēc 37:30:55 izvēles noklikšķiniet uz kategorijas Pielāgots. Var redzēt, kā (h):mm:ss;@tiek izmantots ciparu formatēšanas kods . Kvadrātiekavās ap H ir svarīga daļa.
Lai rādītu laiku stundās un minūtēs, rediģējiet ciparu formātu, lai noņemtu: ss.
Skatīties video
Šodienas video beigās ir bonusa padoms par decimāldaļu formatēšanu kā daļu.
Video atšifrējums
Uzziniet programmu Excel no Podcast, 2210. sērija: noapaļojiet līdz nākamajai ceturkšņa stundai.
Sveiki, laipni lūgti atgriezties netcast ēterā, es esmu Bils Jelens. Šodien vēl viens jautājums no mana Atlanta Power Excel semināra. Kāds veica rēķinu izklājlapu, un viņi gribēja noapaļot uz augšu līdz nākamajai ceturtdaļas stundai, vai ne? Tātad katram uzdevumam šeit ir klients, sākuma laiks un beigu laiks, un, lai aprēķinātu pagājušo laiku, tas ir vienkārši, ja tie ir reālie laiki vai ja tie ir tikai = E2-D2. Tātad, tas saka 55 minūtes, bet saka to patiešām dīvainā veidā - "00:55 AM". Mēs vēlamies turpināt un formatēt to. Izvēlies Laiks, un, re, skaties, šeit ir dažas dažādas izvēles iespējas: 13:30; 13:30:55, kas parādītu sekundes; un pēc tam 37:30:55. Pašlaik es izvēlos 13:30, bet mēs vēlāk riņķosim atpakaļ un izskatīsim šo izvēli vēlreiz. Tātad, es izvēlos Labi un veicu dubultklikšķi, lai to nokopētu,un jūs redzat, ka tā ir tikai matemātika - tātad, 25 minūtes. Bet persona, kas uzdeva šo jautājumu - firma, kurā viņi strādā, - vēlas noapaļot uz nākamo ceturtdaļu stundu.
Labi, tātad, ceturtdaļa stundas. Padomāsim par laikiem programmā Excel. Pusdienlaikā ir 0,5. Ja es to formatēju kā laiku - plkst. 12:00:00. Un, ja es darīšu 0,25, tas būs pulksten 6:00:00. Labi, tāpēc tas darbojas tā, ka viena pilna diena ir skaitlis 1.0, un, ja mēs vēlētos noskaidrot 15 minūtes, tas būtu - tur ir 24 stundas x 4 ceturtdaļas stundas, tāpēc mums vajadzētu likt = 1/96 tur, un tas mums dod 15 minūtes. Tagad, 1/96 - es varu savā ziņā veikt šo matemātiku: 24x4; 1/96 - noteikti ir vieglāk atcerēties 1/96, nekā atcerēties šo decimāldaļu: 0.01041666667. Labi, tāpēc tur bija vieglāk izmantot 1/96, un tāpēc mēs izmantosim funkciju ar nosaukumu CEILING.
GRIETUMI. Skatiet, mums ir izvēle starp CEILING un CEILING.MATH pozitīviem skaitļiem, kas ir identiski. Īsāk ir ievadīt CEILING, tāpēc es izmantoju CEILING. Noapaļotajā dienā ir 1/96 dienas - tas ir 15 minūtes - un tā ir pareizā atbilde, nepareizā formātā. Šos formātus kopēsim pāri - Ctrl + C un pēc tam Alt + E + S + T, lai ielīmētu īpašus formātus, un 55 minūtes noapaļo uz nākamo stundu. Veiciet dubultklikšķi, lai nokopētu to uz leju, un, redziet, 35 minūtes noapaļo uz augšu līdz nākamajām 45; Šķiet, ka tas darbojas, lai gan persona, kas uzdeva šo jautājumu, tiek noapaļota uz nākamo 15. Varbūt pat jūsu uzņēmums izmanto kaut ko citu.
Labi, tāpēc, lai noapaļotu līdz tuvākajai ceturtdaļas stundai, mēs izmantojam 1/96. Bet, lai noapaļotu līdz tuvākajām 5 minūtēm - ir 24 stundas, katrā stundā ir 12 5 minūšu periodi, tātad 12x4 = 288. Izmantojiet 1/288, lai noapaļotu līdz tuvākajām 5 minūtēm. Ja noapaļojat līdz tuvākajām 6 minūtēm - ir 10 6 minūšu periodi x 24 stundas; jūs noapaļojat līdz tuvākajam 1/240. Lai noapaļotu līdz tuvākajai minūtei, izvēlieties tuvāko 1/1440.
Labi, lai jūs to varētu izdarīt, un mums ir arī izvēle. GRIETUMI vienmēr tiks noapaļoti uz augšu, tāpēc 16 minūtes - ejam uz 1/96 tur - 16 minūtes noapaļo līdz 30, un, kaut arī esmu pārliecināts, ka mans advokāts domā, ka tas ir lielisks veids, kā izrakstīt rēķinu, tas ir nav īsti taisnīgi pret mani - klients. Labi, tāpēc jūs zināt, ka CEILING noapaļosies uz augšu. Mans advokāts nekad to neizmantos: Šī GRĪDA vienmēr būs noapaļota uz leju. Bet, iespējams, šeit ir taisnīgākais izmantot MROUND. Tātad = Laika MROUND līdz tuvākajam 1/96, un tas visu noapaļo līdz aptuveni 22,5 minūtēm; un pēc tam tas noapaļosies uz augšu. Labi, tāpēc tas noapaļo visu, kas ir vistuvāk. Tātad jums ir jāizvēlas, vai izmantosiet GRĪDU, MROUND vai CEILING, un pēc tam kāds būs jūsu dalītājs - vai tas būss būs tuvāk 5 minūtēm, tuvākajai minūtei vai, ziniet, vienalga. Labi, tāpēc tur mēs noapaļojam uz augšu.
Tagad šeit ir bonusa padoms - tas bija Lerijs tajā seminārā - Lerijs saka: "Hei, izskatās, nav īsti labi šādā laikā parādīt rēķina apmaksas laiku; mēs patiešām vēlamies pārvērst stundas, jo jūs patiešām dodaties norēķināties ar zināmu likmi - jūs zināt, 35 USD stundā, 100 USD stundā, 500 USD stundā neatkarīgi no jūsu likmes, un mums jāspēj reizināt stundas x likmes. Tātad, būtu jauki jāspēj šīs stundas pārrēķināt līdz decimāldaļai. Tātad, mēs ņemam to laiku G2 un reizinām ar 24, un tas dos mums decimālu stundu skaitu, bet tas būs nepareizā formātā. Viņi ir izvēlēsies laika formātu šajā gadījumā, kad tam nevajadzētu būt laika formātam; tam jābūt tikai ciparu formātā. Un noteikti iekļaujiet vismaz 2 zīmes aiz komata. Veiciet dubultklikšķi, lai to nokopētu, un mēs saņemam savus laikus. "
Labi, tagad, atgriežoties pie mūsu jautājuma “Kurš ciparu formāts mums jāizmanto?”, Šī ir patiešām laba vieta, kur runāt par “Kāpēc?”. Ja mēs izvēlēsimies visus šos skaitļus, šeit mēs redzēsim, ka summa ir 53 stundas, un, ja es nokāpšu šeit un ievietoju SUM funkciju, mēs saņemam 53 stundas. Tas ir ideāli. Bet, ja es izmantoju funkciju SUM zem laika kolonnas un es daru Alt + = vai AUTOSUM, tas man dod 5 stundas … 5 stundas. Kas ir pareizi - 53 vai 5?
Nu, tas izrādās kaut kādā dīvainā nozīmē, viņiem abiem ir taisnība. Bet, lūk, kāpēc viņiem abiem ir taisnība. Es ievadīšu funkciju ar nosaukumu TAGAD. Jūs redzēsiet, ka es to tiešām ierakstīju 9. maijā, tātad, pirms 20 dienām, un ir pulksten 7:08. Un ar = NOW dažreiz es vēlos parādīt datumu un laiku, bet citreiz es vienkārši vēlos lai parādītu datumu. Tātad, es varu dot = NOW dot man pašreizējo datumu, vai arī es varu izdarīt = NOW dot man pašreizējo laiku. Tādējādi funkcijas Numurs labprāt saīsina brīvo laiku vai noņem datumu. Un šis, kuru mēs izvēlējāmies šeit, 13:30, saka: "Hei, es negribu redzēt dienu skaitu; vienkārši parādiet man laiku." Jā, tā ir problēma - jo tas tiešām ir 2 dienas un 5 stundas - 2 dienas ir 48 stundas - 2 dienas un 5 stundas. Un mēs to teicām - mēs teicām programmai Excel "Hei, neuztraucieties man parādīt dienas. "Nu, mēs nezinājām, ka to teicām Excel, bet tieši to mēs teicām Excel, kad izvēlējāmies šo vai šo. Labi, tāpēc jūs esat spiesti nāc šeit līdz 37:30:55. Tas parādīs mums visu stundu skaitu, un mēs saņemsim pareizo atbildi - 53 stundas.
Bet šķiet ļoti dumjš parādīt šīs papildu sekundes, kad mēs noapaļojamies līdz 15 minūtēm. Tātad, atgriezīsimies tajā. Kad esat izvēlējies 37:30:55, lai uzzinātu ciparu formātu, noklikšķiniet uz Pielāgots. Jūs redzēsiet, ka lieta, kas liek tai darboties, ir kvadrātiekavas ap stundu. Bet mums nav vajadzīgi: ss. Tātad, tagad mēs varam rediģēt šo pielāgoto formātu, un tāpēc mums ir stundas un minūtes. Mums šodien nav jāsaprot, ko nozīmē; @. Tas saka, ka teksta gadījumā vienkārši parādiet tekstu; bet, vienkārši atstājiet šo daļu tur, pat ja to nesaprotat. Noklikšķiniet uz Labi, un tagad mums ir laiks stundās un minūtēs, atstājot sekundes. Bet kopsummas darbojas. Tiešām, ļoti smalks, tas 13:30 un tas 13:30:55. Jūs zināt, ka nav teikts: "Hei, jūs liekat Excel ignorēt datumu … pilnas dienas." Bet,tu esi.
Lietas, piemēram, Laika matemātika, ir šajā grāmatā: LIVe: 54 visu laiku lieliskākie Excel padomi. Lai to pārbaudītu, augšējā labajā stūrī noklikšķiniet uz “I”.
Tas izklausījās tik viegls jautājums; Es nespēju noticēt, ka esam jau 7 minūtes garumā. Bet uzņēmums vēlas izrakstīt rēķinu ceturkšņa stundās, un viņi vienmēr vēlas noapaļot uz nākamo ceturtdaļas stundu. Tātad, 24 stundas dienā, 24x4 = 96; 15 minūtes = 1/96. Tātad, izmantojiet CEILING (A2,1 / 96), lai noapaļotu uz nākamo augstāko ceturtdaļas stundu. Izmantojiet MROUND (A2,1 / 96), lai noapaļotu līdz tuvākajai ceturtdaļas stundai, un pēc tam izmantojiet FLOOR (A2,1 / 96), lai noapaļotu līdz apakšējai ceturtdaļas stundai - vienmēr atgriežas. Dienā ir 24 stundas, tāpēc jūs varat veikt 1/288, lai nokļūtu 5 minūšu periodos, 1/240, lai nokļūtu 6 minūšu periodos, 1/1440, lai noapaļotu līdz tuvākajai minūtei. Un tad, bonusa padoms tur beigās (kurš ciparu formāts) - jūs vienmēr vēlaties atgriezties pie šī 37:30:55, ja jūs rādīsit stundas, kas pārsniedz 24 stundas.
Lai lejupielādētu darbgrāmatu no šodienas videoklipa, apmeklējiet šo vietni URL, kas atrodas Youtube aprakstā.
Es gribu pateikties, ka apstājāties; tiksimies nākamreiz, lai skatītu citu netcast no.
Hei! Paldies, ka karājāties. Bet, vēl tikai viens bonusa padoms šeit (ko es pat negrasos pieminēt). Ar ko es šeit sāku - iztīrīsim visu šo lietu - nelielu formulu, kas parādījās un norādīja uz vienu no šiem cipariem aiz komata - tuvāko ceturtdaļu stundu. Bet es gribēju parādīt, ka ne kā 0.010417. Tas ir lieliski - skaitļu formatējumā faktiski ir kaut kas tāds, ko sauc par frakcijām, un, ja es izvēlos "Līdz diviem cipariem", es saņemšu 1/96; bet man ir jārīkojas ar šo 1/288, tāpēc es izvēlos "Līdz trim cipariem", un tas man joprojām parāda 1/96; bet tad, kad es pārvēršos par šo - 1/288 -, tas to pareizi parādīs. Tagad es domāju, ka man vajadzēja izdarīt "četrus ciparus", lai rīkotos ar 1/1440. Ko tas darīs? Tas man dos … Bet,ja es iešu ciparu formātā un dodos uz Custom, un tur vienkārši pievienoju papildu jautājuma zīmi, tad mēs nonāksim līdz tuvākajai 1/1440 daļai. Numura formāts - forša, forša lieta.
Lejupielādējiet Excel failu
Lai lejupielādētu Excel failu: noapaļojiet līdz ceturtdaļai-hour.xlsx
Excel dienas doma
Esmu lūdzis saviem Excel Master draugiem padomu par Excel. Šodienas doma apdomāt:
"Draugi neļauj draugiem izmantot INDIRECT"
Džordans Goldmeiers
