Toinen kysymys Atlanta Power Excel -seminaaristani:
Kuinka voit pyöristää laskutettavan ajan seuraavaan neljännekseen?
Oletetaan, että pidät lokia laskutusajastasi. Sinulla on aloitus- ja lopetusaika. Kaava, =E2-D2joka laskee kuluneen ajan.
Tulos F2: ssa on 55 minuuttia. Mutta oletusmuotoilu näyttää kello 00.55. Valitse koko sarake. Alusta solut painamalla Ctrl + 1. Valitse Aika-luokka. Mitä näistä sinun tulisi käyttää? Valitse toistaiseksi 13:30:55, mutta näet myöhemmin tämän valinnan ongelman.
Yrityksesi käytäntö on pyöristää kaikki laskutukset seuraavaan neljännekseen. Tee tämä laskelma: Päivässä on 24 tuntia. Tunnissa on 4 neljännes tuntia. Kerro 24 * 4 saadaksesi 96. Tämä tarkoittaa, että neljännes tunti vastaa 1/96 päivää.
Käytä kaavaa =CEILING(F2,1/96)pyöristää kulunut aika F: nä seuraavaan neljännekseen tuntiin. Excelin menetelmässä ajan varastoimiseksi Noon on 0,5. 6AM on 0,25. Klo 00.15 on 1/96 tai 0.010417. 1/96 on helpompi muistaa kuin 0,010417.
Tulokset on esitetty alla. Huomaa, kuinka 35 minuuttia kiertää 45 minuutiksi. Tämä näyttää olevan kaava, jota asianajajani käyttää … haha, se on vain vitsi Esquire Dewey! Älä haasta minua! Tiedän, että he käyttävät =CEILING(F2,4)…
Keskeytetään täällä … tämä artikkeli pyöristyy seuraavaan neljännekseen tuntiin, koska tätä menetelmää Larry seminaarissani käyttää. On hyvät mahdollisuudet, että yrityksesi pyöristää jotain muuta. Alla olevassa kuvassa näkyy, kuinka pyöristetään viiteen minuuttiin tai 1 minuuttiin. Voit käyttää vastaavaa logiikkaa pyöristääksesi lähimpään 6 minuuttiin (1/240) tai 12 minuuttiin (1/120).
Myös - lempeä ehdotus, joka osoittaa eron LATTIA, MROUND ja KATTO. B9: B22: n Lattiatoiminto pyöristyy aina alaspäin. CRO: C9: C22 -kerros kiertää lähimpään. KATTO D9: D22: ssä pyöristyy ylöspäin.
Huomautus
Excel-työkaluvinkki ehdottaa, että käytät FLOOR.MATH tai CEILING.MATH FLOOR- tai CEILING-tilan sijaan. Näiden toimintojen uudet ".Math" -versiot käsittelevät negatiivisia lukuja eri tavalla. Koska aika Excelissä ei voi koskaan olla negatiivinen, on hienoa käyttää tavallista Lattia- tai Katto.
Muuta jae arvosta 1/96 arvoon 1/288 pyöristämällä viiden minuutin välein.
Aikojen muuntaminen tunneiksi
Toinen ehdotus Larrylta: Muunna ajat todellisiksi tunneiksi. Tällä tavoin, kun kerrot tunnin laskutusprosentilla, matematiikka toimii. Ota pyöristetty kulunut aika ja kerro se 24: llä saadaksesi desimaalimäärän tunteja. Muista muotoilla tulos numerona kahdella desimaalilla eikä kerralla.
Mitä aikamuotoa käytetään?
On aika puhua noista ajan muotoiluvaihtoehdoista: Olisit voinut valita 13:30, 13:30:55 tai 37:30:55. Yllä olevissa kuvakaappauksissa olen käyttänyt 13:30:55. Ongelma tässä ilmenee viikon lopussa. Kun lasket desimaalitunnit sarakkeessa H, sinulla on 53 laskutettavaa tuntia. Kun lasket yhteen ajat G: ssä, saat viisi tuntia. Mitä tapahtuu?
Molemmat ovat oikein, jos ymmärrät valitsemasi numeromuodon. Sano, että kirjoitat =NOW()soluun. Voit muotoilla solun näyttämään päivämäärän ja kellonajan, vain päivämäärän tai vain ajan. Excelillä ei ole ongelmaa jättää 29. toukokuuta osa toukokuun 29. päivästä klo 08.15: 34, jos käsket sitä tekemään niin. Solun G57 vastaus on "2 päivää ja viisi tuntia". Mutta kun valitsit aikamuodoksi 13:37:55, käskit Exceliä jättämään pois päivien lukumäärä ja näyttämään sinulle vain tunnit, minuutit ja sekunnit.
Jos muunnat sarakkeen G muotoon 37:30:55, saat oikean tuntimäärän:
Kuvan yläpuolella oleva kuvateksti vangitsee pienen ongelman muodossa 37:30:55: se näyttää sekunteja, mikä tuntuu tarpeettomalta, koska pyöristämme lähimpään viidentoista minuuttiin. Kun olet valinnut 37:30:55, napsauta Mukautettu-luokkaa. Näet, että numeron muotoilukoodia (h):mm:ss;@käytetään. H: n ympärillä olevat hakasulkeet ovat tärkeä osa.
Jos haluat näyttää ajat tunteina ja minuutteina, muokkaa numeromuotoa poistaaksesi: ss.
Katso video
Tämän päivän video sisältää lopussa bonusvinkin desimaalien muotoilusta murto-osana.
Videon transkriptio
Opi Excel Podcastista, jakso 2210: Pyöristää ylöspäin seuraavaan vuosineljänneksen tuntiin.
Hei, tervetuloa takaisin netcastiin, olen Bill Jelen. Tänään toinen kysymys Atlanta Power Excel -seminaaristani. Joku teki laskutuslaskentataulukkoa ja halusi pyöristää ylöspäin seuraavaan neljännekseen tuntiin, eikö? Joten jokaisella täällä olevalla tehtävällä on asiakas, aloitusaika ja lopetusaika, ja kuluneen ajan laskeminen on yksinkertaista, jos ne ovat reaaliaikoja tai jos ne ovat vain = E2-D2. Joten se tarkoittaa 55 minuuttia, mutta se sanotaan todella oudolla tavalla - "00:55 AM". Haluamme mennä eteenpäin ja muotoilla tämä. Valitse Aika, ja hei, katso, tässä on muutama erilainen vaihtoehto: 13:30; 13:30:55, joka näyttäisi sekunnit; ja sitten 37:30:55. Nyt, tällä hetkellä, aion valita 13:30, mutta aiomme kiertää myöhemmin ja tutkia uudelleen valinnan. Joten valitsen OK ja kaksoisnapsautan kopioidaksesi sen alas,ja näet, että se vain tekee matematiikkaa - joten tässä, 25 minuuttia. Mutta tämän kysymyksen esittänyt henkilö - yritys, jossa he työskentelevät - haluavat pyöristyä seuraavaan neljännekseen.
Selvä, niin, neljännes tunti. Ajattelemme aikoja Excelissä. Keskipäivä on 0,5. Jos muotoilen sen ajankohtana - 12:00:00 PM. Ja jos teen 0,25, se on kello 6.00. Selvä, joten näin toimii, että yksi koko päivä on luku 1.0, ja jos haluaisimme selvittää 15 minuuttia, se olisi - siellä on 24 tuntia x 4 neljännes tuntia, joten meidän on laitettava = 1/96 siellä, ja se antaa meille 15 minuuttia. Nyt, 1/96 - Voin tavallaan tehdä matematiikan päähäni: 24x4; 1/96 - on varmasti helpompaa muistaa 1/96 kuin muistaa tämä desimaali: 0,01041666667. Selvä, joten 1/96: n käyttö siellä oli helpompaa, joten aiomme käyttää toimintoa nimeltä KATTO.
KATTO. Meillä on mahdollisuus valita CEILING ja CEILING.MATH positiivisten lukujen suhteen, jotka ovat identtisiä. Se on lyhyempi kirjoittaa KATTO, joten aion käyttää KATTOA. Ja pyöristettynä on 1/96 päivää - mikä on 15 minuuttia - ja se on oikea vastaus, väärässä muodossa. Kopioimme nämä muodot yli - Ctrl + C ja sitten Alt + E + S + T liittääksesi erikoismuodot - ja 55 minuuttia pyöristyy seuraavaan tuntiin. Kaksoisnapsauta kopioidaksesi sen alaspäin, ja katso, 35 minuuttia pyöristyy ylöspäin seuraavaan 45: een; 25 kierrosta seuraavaan 30. Se näyttää toimivan, vaikka henkilö, joka kysyi tämän kysymyksen, pyöristyy seuraavaan 15. Ehkä jopa yrityksesi käyttää jotain erilaista.
Selvä, joten pyöristämme lähimpään neljännes tuntiin käytämme arvoa 1/96. Mutta pyöristää lähimpään 5 minuuttiin - on 24 tuntia, tunnissa on 12 5 minuutin jaksoa, joten 12x4 = 288. Käytä 1/288 pyöristääksesi lähimpään 5 minuuttiin. Jos pyöristät lähimpään 6 minuuttiin - siellä on 10 6 minuutin jaksoa x 24 tuntia; pyöristät lähimpään 1/240. Voit pyöristää lähimpään minuuttiin valitsemalla lähinnä 1/1440.
Selvä, joten voit tehdä tämän, ja meillä on myös mahdollisuus valita. KATTO pyörii aina ylöspäin, joten 16 minuuttia - mennään sinne 1/96 - 16 minuuttia pyöristetään 30: een, ja vaikka olen varma, että asianajajani mielestä se on hieno tapa laskuttaa, ei oikeastaan oikeudenmukainen minulle - asiakkaalle. Selvä, joten tiedät, että KATTO pyörii ylöspäin. Asiamieheni ei koskaan käytä tätä: Tämä LATTIA pyörii aina alaspäin. Mutta luultavasti mikä on oikeudenmukinta tässä on käyttää MROUNDia. Joten, = AJAN PYÖRÄ 1/96: n tarkkuudella, ja se pyöristää kaiken alaspäin noin 22,5 minuuttiin; ja sitten sen jälkeen se pyöristyy ylöspäin. Selvä, joten se pyörii mitä tahansa lähinnä. Joten sinun on valittava, aiotko käyttää Lattiaa, MROUNDia tai Kattoa, ja sitten mikä sinun jakajasi tulee olemaan - onko se ''Tulemme lähimpään 5 minuuttiin, lähimpään minuuttiin tai tiedätkö mitä tahansa. Selvä, joten pyöristymme ylöspäin.
Nyt bonusvinkki täällä - tämä oli Larry tuossa seminaarissa - Larry sanoo: "Hei, katso, ei ole todella hyvä näyttää laskutettavaa aikaa sellaisina aikoina; haluamme todella muuntaa tunneiksi, koska olet todella menossa laskuttaa jossain määrin - tiedät, 35 dollaria tunnissa, 100 dollaria tunnissa, 500 dollaria tunnissa riippumatta hinnastasi - ja meidän on pystyttävä kertomaan tunnit x hinnat. Joten olisi mukavaa pystyt muuntamaan nuo tunnit desimaaliin. Joten otamme tuon ajan G2: ssä ja kerrotaan se 24: llä, ja se antaa meille desimaalimäärän tunteja, mutta se tulee olemaan väärässä muodossa. valitset aikamuodon tässä tapauksessa, kun sen ei pitäisi olla ollenkaan aikamuoto; sen pitäisi olla vain numero-muodossa. Varmista, että sisällytät vähintään kaksi desimaalia. Kopioi se alas kaksoisnapsauttamalla ja saamme aikamme. "
Selvä, palatakseni takaisin kysymykseemme "Mitä numeromuotoja meidän tulisi käyttää?", Tämä on todella hyvä paikka puhua "Miksi?" Jos valitsemme kaikki nämä numerot, näemme, että summa on 53 tuntia, ja jos tulen tänne ja laitan SUM-funktion, saamme 53 tuntia. Onpa täydellistä. Mutta jos käytän SUM-funktiota ajan sarakkeen alapuolella ja teen Alt + = tai AUTOSUM, se antaa minulle 5 tuntia … 5 tuntia. Mikä on oikein - 53 tai 5?
No, se osoittautuu jossain outossa mielessä, he molemmat ovat oikeassa. Mutta, tästä syystä heillä molemmilla on oikeus. Aion kirjoittaa funktion nimeltä = NYT. Näet nauhoittavan tätä takaisin 9. toukokuuta, joten 20 päivää sitten, ja kello on 7:08. Ja jos = NYT, joskus haluan näyttää päivämäärän ja kellonajan, mutta muina aikoina haluan vain näyttää päivämäärän. Joten voin antaa = NYT antaa minulle nykyisen päivämäärän tai, voin tehdä = NYT antaa minulle nykyisen ajan. Numerofunktiot joko mielellään lyhentävät vapaataikaa tai poistavat päivämäärän. Ja tämä, jonka valitsimme täällä, 13.30, sanoo: "Hei, en halua nähdä päivien lukumäärää; näytä vain aika." Kyllä, se on ongelma - koska tämä on todella 2 päivää ja 5 tuntia - 2 päivää on 48 tuntia - 2 päivää ja 5 tuntia. Ja kerroimme sen - sanoimme Excelille "Hei, älä vaivaudu näyttämään minulle päiviä. "No, emme tienneet, että kerroimme Excelille niin, mutta juuri sen kerroimme Excelille, kun valitsimme tämän tai tämän. Selvä, joten sinun on pakko tule tänne kello 37:30:55. Se näyttää meille täyden tuntimäärän, ja saamme oikean vastauksen 53 tuntiin.
Mutta näyttää todella typerältä näyttää nämä ylimääräiset sekunnit, kun pyöristämme 15 minuuttiin. Joten, palataan takaisin siihen. Kun olet valinnut 37:30:55 oppiaksesi sen takana olevan numeromuodon, napsauta Mukautettu. Näet, että asia, joka saa sen toimimaan, ovat hakasulkeita ympäri tuntia. Mutta emme tarvitse: ss. Joten nyt voimme muokata kyseistä mukautettua muotoa, joten meillä on tunteja ja minuutteja. Meidän ei tarvitse tänään ymmärtää, mitä; @ tarkoittaa. Sanotaan, että vain teksti näyttää tekstin; mutta jätä vain osa sinne, vaikka et ymmärrä sitä. Napsauta OK ja meillä on nyt aika tunteina ja minuutteina, jättämättä sekunteja. Mutta kokonaismäärät toimivat. Todella, todella hienovarainen, se 13:30 ja se 13:30:55. Tiedät, siinä ei sanota: "Hei, käsket Exceliä jättämään huomiotta päivämäärän … koko päivän." Mutta,Sinä olet.
Ajan matematiikan ottaminen on tässä kirjassa: LIVe: 54 kaikkien aikojen suurinta Excel-vinkkiä. Napsauta sitä "I" oikeassa yläkulmassa tarkistaaksesi sen.
Tämä kuulosti niin helpolta kysymykseltä; En voi uskoa, että olemme jo 7 minuuttia sisällä. Mutta yritys haluaa laskuttaa neljännes tunnissa, ja he haluavat aina pyöristää seuraavaan neljännekseen. Joten, 24 tuntia päivässä, 24x4 = 96; 15 minuuttia = 1/96. Joten, käytä KATTOA (A2,1 / 96) pyöristääksesi ylöspäin seuraavaan korkeampaan neljännes tuntiin. Käytä MROUND (A2,1 / 96) pyöristääksesi lähimpään neljännes tuntiin ja käytä sitten FLOOR (A2,1 / 96) pyöristääksesi alempaan neljännes tuntiin - palaa aina takaisin. Päivässä on 24 tuntia, joten voit tehdä 1/288 päästäksesi 5 minuutin jaksoihin, 1/240 päästäksesi 6 minuutin jaksoihin, 1/1440 pyöristää lähimpään minuuttiin. Ja sitten, bonusvinkki lopussa (mikä Numeromuoto) - haluat aina palata siihen 37:30:55, jos aiot näyttää yli 24 tunnin tunteja.
Voit ladata työkirjan tämän päivän videosta käymällä kyseisessä URL-osoitteessa Youtube-kuvauksessa.
Haluan kiittää sinua pysähtymisestä; Nähdään ensi kerralla toisesta netcastista.
Hei! Kiitos ripustamisesta. Mutta, vielä yksi bonusvinkki täällä (jota en edes aio viettää mainita). Aloitin täällä - tyhjennetään tämä koko asia - pieni kaava, joka tuli ja osoitti yhteen näistä desimaaleista - lähin neljännes tunti. Mutta halusin osoittaa, että se ei ole 0.010417. Se on mahtavaa - numeroiden muotoilussa on oikeastaan jotain, jota kutsutaan murtoluvuiksi, ja jos valitsen "Enintään kaksi numeroa", saan 1/96; mutta minun on käsiteltävä sitä 1/288, joten valitsen "Enintään kolme numeroa", ja se näyttää silti minulle 1/96; mutta sitten kun muunnan tähän - 1/288 -, se näyttää sen oikein. Luulen, että minun olisi pitänyt tehdä "neljä numeroa" käsittelemään 1/1440. Mitä se tekee? Se antaa minulle … Mutta,jos menen numeromuotoon ja menen Mukautettu-kohtaan ja vain lisätään ylimääräinen kysymysmerkki sinne, niin pääset lähimpään 1/1440 murto-osaan. Numeromuoto - siisti, siisti juttu.
Lataa Excel-tiedosto
Excel-tiedoston lataaminen: round-to-quarter-hour.xlsx
Päivän Excel-ajatus
Olen pyytänyt Excel Master -kaveriltani neuvoja Excelistä. Tämän päivän ajatus miettiä:
"Ystävät eivät anna ystävien käyttää epäsuoraa"
Jordan Goldmeier
