Kolejne pytanie z mojego seminarium Power Excel w Atlancie:
Jak zaokrąglić naliczany czas do następnego kwadransa?
Powiedz, że prowadzisz dziennik rozliczanego czasu. Masz godzinę rozpoczęcia i godzinę zakończenia. Formuła taka jak =E2-D2obliczy upływający czas.
Wynik w F2 to 55 minut. Ale domyślne formatowanie pokazuje 00:55. Wybierz całą kolumnę. Naciśnij Ctrl + 1, aby sformatować komórki. Wybierz kategorię Czas. Którego z poniższych należy użyć? Na razie wybierz 13:30:55, ale później zobaczysz problem z tym wyborem.
Polityka Twojej firmy polega na zaokrąglaniu wszystkich rachunków do następnego kwadransa. Zrób to obliczenie: doba ma 24 godziny. W ciągu godziny są 4 kwadrans. Pomnóż 24 * 4, aby otrzymać 96. Oznacza to, że kwadrans to 1/96 dnia.
Użyj wzoru, =CEILING(F2,1/96)aby zaokrąglić upływający czas w F do następnego kwadransa. W metodzie zapisywania czasu programu Excel południe wynosi 0,5. 6 rano to 0,25. 12:15 to 1/96 lub 0,010417. Łatwiej jest zapamiętać 1/96 niż 0,010417.
Wyniki przedstawiono poniżej. Zwróć uwagę, że 35 minut zaokrągla się do 45 minut. Wydaje się, że to jest formuła, której używa mój prawnik… haha, to tylko żart Esquire Dewey! Nie pozwól mnie! Wiem, że używają =CEILING(F2,4)…
Przerwa tutaj… ten artykuł zaokrągla do następnego kwadransa, ponieważ jest to metoda, której używa Larry na moim seminarium. Istnieje duża szansa, że Twoja firma zajmie się czymś innym. Poniższy obraz pokazuje, jak zaokrąglić do pięciu minut lub 1 minuty. Możesz użyć podobnej logiki, aby zaokrąglić do najbliższych 6 minut (1/240) lub 12 minut (1/120).
Również - delikatna sugestia pokazująca różnicę między PODŁOGĄ, PODŁOGĄ i SUFITEM. Funkcja PODŁOGA w B9: B22 zawsze zaokrągla w dół. MROUND w C9: C22 zaokrągla się do najbliższej. CEILING w D9: D22 zaokrągla się w górę.
Uwaga
Etykietka programu Excel zasugeruje użycie PODŁOGI.MATY lub SUFITU.MATY zamiast PODŁOGI lub SUFITU. Nowe wersje ".Math" tych funkcji inaczej obsługują liczby ujemne. Ponieważ czas w Excelu nigdy nie może być ujemny, dobrze jest użyć zwykłej PODŁOGI lub SUFITU.
Zmień ułamek z 1/96 na 1/288, aby zaokrąglić do pięciu minut.
Zamiana czasów na godziny
Kolejna sugestia Larry'ego: zamień godziny na rzeczywiste godziny. W ten sposób, gdy pomnożymy przez stawkę rozliczeniową za godzinę, matematyka zadziała. Weź zaokrąglony czas, który upłynął, i pomnóż go przez 24, aby uzyskać dziesiętną liczbę godzin. Pamiętaj, aby sformatować wynik jako liczbę z dwoma miejscami po przecinku, a nie godzinę.
Którego formatu czasu użyć?
Czas porozmawiać o tych wyborach formatowania czasu: mogłeś wybrać 13:30, 13:30:55 lub 37:30:55. Na powyższych zrzutach ekranu użyłem 13:30:55. Problem z tym pojawia się pod koniec tygodnia. Po zsumowaniu godzin dziesiętnych w kolumnie H masz 53 godziny podlegające rozliczeniu. Kiedy zsumujesz czasy w G, otrzymasz pięć godzin. Co się dzieje?
Oba są poprawne, jeśli rozumiesz wybrany format liczb. Powiedz, że wchodzisz =NOW()do celi. Możesz sformatować komórkę, aby wyświetlała datę i godzinę, tylko datę lub tylko godzinę. Program Excel nie ma problemu z opuszczeniem części 29 maja z dnia 29 maja 08:15:34, jeśli mu to powiesz. Odpowiedź w komórce G57 to „2 dni i pięć godzin”. Ale kiedy wybrałeś 13:37:55 jako format czasu, kazałeś programowi Excel pominąć liczbę dni i pokazać tylko godziny, minuty i sekundy.
Jeśli przekonwertujesz kolumnę G na format 37:30:55, otrzymasz poprawną liczbę godzin:
Podpis nad tym obrazem przedstawia mały problem z formatem 37:30:55: pokazuje sekundy, co wydaje się niepotrzebne, ponieważ zaokrąglamy do najbliższych piętnastu minut. Po wybraniu godziny 37:30:55 kliknij kategorię Niestandardowe. Możesz zobaczyć, że (h):mm:ss;@używany jest kod formatowania liczb . Nawiasy kwadratowe wokół litery H to ważna część.
Aby wyświetlać czasy w godzinach i minutach, edytuj format liczb i usuń: ss.
Obejrzyj wideo
Dzisiejszy film zawiera na końcu dodatkową wskazówkę dotyczącą formatowania ułamków dziesiętnych jako ułamków zwykłych.
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2210: Podsumowanie do następnego kwartału.
Hej, witaj z powrotem w netcastie, jestem Bill Jelen. Dzisiaj kolejne pytanie z mojego seminarium Power Excel w Atlancie. Ktoś robił rozliczeniowy arkusz kalkulacyjny i chciał zaokrąglić do następnego kwadransa, prawda? Tak więc dla każdego zadania tutaj mają klienta, czas rozpoczęcia i czas zakończenia oraz obliczenie czasu, który upłynął - to proste, jeśli są to czasy rzeczywiste lub po prostu = E2-D2. To znaczy 55 minut, ale mówi to w naprawdę dziwny sposób - „12:55”. Chcemy iść dalej i sformatować to. Wybierz czas i hej, spójrz, jest tu kilka różnych opcji: 13:30; 13:30:55, co wskazywało sekundy; a potem 37:30:55. Teraz, na razie, wybiorę 13:30, ale wrócimy później i ponownie przeanalizuję ten wybór. Więc wybieram OK i klikam dwukrotnie, aby skopiować plik w dół,i widzisz, że to tylko matematyka - więc tutaj, 25 minut. Ale osoba, która zadała to pytanie - firma, dla której pracują - chce zaokrąglić do następnego kwadransa.
W porządku, więc kwadrans. Pomyślmy o czasach w Excelu. Południe to 0,5. Jeśli sformatuję to jako godzinę - 12:00:00. A jeśli zrobię 0.25, to będzie 6:00:00. W porządku, więc działa to tak, że jeden pełny dzień to liczba 1.0, więc jeśli chcielibyśmy obliczyć 15 minut, byłoby to - 24 godziny x 4 kwadrans, więc musielibyśmy wpisać = 1/96, a to daje nam 15 minut. Teraz 1/96 - potrafię zrobić w głowie matematykę: 24x4; 1/96 - z pewnością łatwiej jest zapamiętać 1/96 niż ten dziesiętny: 0,01041666667. W porządku, więc łatwiej było tam użyć 1/96, więc użyjemy funkcji o nazwie CEILING.
SUFIT. Widzisz, mamy wybór pomiędzy CEILING i CEILING.MATH dla liczb dodatnich, które są identyczne. Jest krótszy do wpisania CEILING, więc użyję CEILING. W zaokrągleniu jest 1/96 dnia - czyli 15 minut - i to jest właściwa odpowiedź, w złym formacie. Skopiujemy te formaty - Ctrl + C, a następnie Alt + E + S + T, aby wkleić specjalne formaty - a 55 minut zaokrągla w górę do następnej godziny. Kliknij dwukrotnie, aby skopiować to w dół i zobacz, 35 minut zaokrągla w górę do następnych 45; 25 zaokrągla w górę do następnych 30. Wydaje się, że działa, chociaż osoba, która zadała to pytanie - zaokrągla do kolejnych 15. Może nawet Twoja firma używa czegoś innego.
W porządku, więc aby zaokrąglić do najbliższego kwadransa, używamy 1/96. Ale żeby zaokrąglić do najbliższych 5 minut - są 24 godziny, w każdej godzinie występuje 12 5-minutowych okresów, więc 12x4 = 288. Użyj 1/288, aby zaokrąglić do najbliższych 5 minut. Jeśli zaokrąglisz do najbliższych 6 minut - jest 10 okresów 6-minutowych x 24 godziny; zaokrąglasz do najbliższej 1/240. Aby zaokrąglić do najbliższej minuty, wybierz do najbliższej 1/1440.
W porządku, więc możesz to zrobić, a my też mamy wybór. CEILING zawsze będzie zaokrąglany w górę, więc 16 minut - przejdźmy tam do 1/96 - 16 minut to zaokrąglenie do 30, i chociaż jestem pewien, że mój prawnik uważa, że to świetny sposób nie jest to dla mnie sprawiedliwe - klient. W porządku, więc wiesz, że CEILING zaokrągli się w górę. Mój prawnik nigdy tego nie użyje: ta PODŁOGA zawsze będzie zaokrąglana w dół. Ale prawdopodobnie najlepszą rzeczą jest użycie MROUND. A więc = MROUND czasu do najbliższej 1/96, a to wszystko zaokrągli w dół do około 22,5 minuty; a potem zaokrągli się w górę. W porządku, więc zaokrągla to, co jest najbliżej. Musisz więc wybrać, czy użyjesz PODŁOGI, MROUND czy SUFITU, a następnie, jaki ma być twój dzielnik - czy to będzie.będzie to najbliższe 5 minut, najbliższa minuta, czy cokolwiek. W porządku, więc kończymy.
A teraz dodatkowa wskazówka - to był Larry na tym seminarium - Larry mówi: „Hej, słuchaj, nie jest dobrze pokazywać rozliczany czas w takich momentach; naprawdę chcemy przeliczyć godziny na godziny, ponieważ tak naprawdę żeby rozliczać się według jakiejś stawki - wiesz, 35 USD za godzinę, 100 USD za godzinę, 500 USD za godzinę, niezależnie od stawki - i musimy być w stanie pomnożyć godziny przez stawki. Byłoby więc dobrze być w stanie zamienić te godziny na czas dziesiętny. Tak więc bierzemy ten czas w G2 i mnożymy go przez 24, a to da nam dziesiętną liczbę godzin, ale będzie w złym formacie. mam zamiar wybrać format czasu w tym przypadku, kiedy to w ogóle nie powinien być format czasu; powinien być po prostu w formacie liczbowym. Pamiętaj, aby uwzględnić co najmniej 2 miejsca po przecinku. Kliknij dwukrotnie, aby go skopiować i mamy swoje czasy. "
W porządku, wracając do naszego pytania „Którego formatu liczb powinniśmy użyć?”, Jest to naprawdę dobre miejsce, aby porozmawiać o „Dlaczego?”. Jeśli wybierzemy wszystkie te liczby, zobaczymy, że suma to 53 godziny, a jeśli zejdę tutaj i wstawię funkcję SUMA, otrzymamy 53 godziny. To idealne. Ale jeśli używam funkcji SUMA poniżej kolumny czasu i wykonuję Alt + = lub AUTOSUM, daje mi to 5 godzin… 5 godzin. Która jest właściwa - 53 czy 5?
Cóż, okazuje się w jakimś dziwnym sensie, oboje mają rację. Ale oto dlaczego oboje mają rację. Wprowadzę funkcję o nazwie = TERAZ. Zobaczysz, że faktycznie nagrywam to 9 maja, więc 20 dni temu i jest 7:08. A przy = TERAZ czasami chcę pokazać datę i godzinę, ale innym razem po prostu chcę aby pokazać datę. Więc mogę zrobić = TERAZ podaj mi aktualną datę lub mogę zrobić = TERAZ podaj mi aktualny czas. Dlatego funkcje Number z przyjemnością albo skracają czas wolny, albo usuwają datę. A ten, który wybraliśmy tutaj, 13:30, mówi: „Hej, nie chcę widzieć liczby dni; po prostu pokaż mi godzinę”. Tak, w tym problem - bo to naprawdę 2 dni i 5 godzin - 2 dni to 48 godzin - 2 dni i 5 godzin. Powiedzieliśmy to - powiedzieliśmy Excelowi „Hej, nie kłopocz się pokazywaniem mi dni. ”Cóż, nie wiedzieliśmy, że mówimy o tym programowi Excel, ale właśnie o tym mówiliśmy, kiedy wybieraliśmy ten lub ten. W porządku, więc jesteś zmuszony zejdź tutaj do 37:30:55. To ta, która pokaże nam pełną liczbę godzin i otrzymamy poprawną odpowiedź 53 godziny.
Ale pokazanie tych dodatkowych sekund, kiedy zaokrąglamy do 15 minut, wydaje się naprawdę głupie. Więc wróćmy do tego. Po wybraniu 37:30:55, aby poznać format liczbowy, kliknij Niestandardowy. Zobaczysz, że to, co sprawia, że to działa, to nawiasy kwadratowe wokół godziny. Ale nie potrzebujemy: ss. Teraz możemy edytować ten niestandardowy format i mamy godziny i minuty. Nie musimy dziś rozumieć, co oznacza; @. Mówi się, że w przypadku tekstu po prostu pokaż tekst; ale zostaw tę część tam, nawet jeśli jej nie rozumiesz. Kliknij OK, a teraz mamy czas w godzinach i minutach, pomijając sekundy. Ale sumy działają. Naprawdę, bardzo subtelne, że 13:30 i 13:30:55. Wiesz, nie jest napisane: „Hej, mówisz programowi Excel, aby ignorował datę… pełne dni”. Ale,ty jesteś.
W tej książce znajdują się takie rzeczy, jak poświęcanie czasu matematyka: ŻYCIE: 54 najlepsze wskazówki dotyczące Excela wszechczasów. Kliknij „I” w prawym górnym rogu, aby to sprawdzić.
To brzmiało jak takie łatwe pytanie; Nie mogę uwierzyć, że mamy już 7 minut. Ale firma chce wystawiać rachunki w kwadrans i zawsze chce zaokrąglić w górę do następnego kwadransa. Zatem 24 godziny na dobę, 24x4 = 96; 15 minut = 1/96. Więc użyj CEILING (A2,1 / 96), aby zaokrąglić w górę do następnego wyższego kwadransa. Użyj MROUND (A2,1 / 96), aby zaokrąglić do najbliższego kwadransa, a następnie użyj opcji FLOOR (A2,1 / 96), aby zaokrąglić do niższego kwadransa - zawsze wraca. Dzień ma 24 godziny, więc możesz zrobić 1/288, aby uzyskać okresy 5-minutowe, 1/240, aby uzyskać okresy 6-minutowe, 1/1440, aby zaokrąglić do najbliższej minuty. A potem bonusowa wskazówka na końcu (jaki format liczb) - zawsze chcesz wrócić do tego 37:30:55, jeśli zamierzasz wyświetlać godziny przekraczające 24 godziny.
To download the workbook from today's video, visit that URL down there in the Youtube description.
I want to thank you for stopping by; we'll see you next time for another netcast from.
Hey! Thanks for hanging along. But, just one more bonus tip here (that I'm not even going to bother to mention). What I started out with here-- let's clear this whole thing-- a little formula that came over and pointed to one of these decimals-- so, the nearest quarter hour. But, I wanted to show that not as the 0.010417. It's awesome-- in the Number Formatting, there's actually something called Fractions, and if I choose "Up to two digits," I'm going to get 1/96; but, I need to handle that 1/288, so I choose "Up to three digits," and it still shows me 1/96; but, then when I convert over to this one -- the 1/288 -- it will show it correctly. Now, I guess I should have done "four digits" to handle the 1/1440. What's that going to do? That's going to give me… But, if I go into the Number Format, and go to Custom, and just add an extra question mark there, then we'll get to the nearest 1/1440 of a fraction. Number Format-- cool, cool thing.
Pobierz plik Excel
Aby pobrać plik Excela: round-to-quart-hour.xlsx
Myśl dnia Excela
Poprosiłem moich znajomych z programu Excel Master o radę dotyczącą programu Excel. Dzisiejsza myśl do rozważenia:
„Znajomi nie pozwalają znajomym używać POŚREDNIEGO”
Jordan Goldmeier
