Μια άλλη ερώτηση από το σεμινάριο Atlanta Power Excel:
Πώς μπορείτε να στρογγυλοποιήσετε τον χρεώσιμο χρόνο έως την επόμενη τρίμηνη ώρα;
Ας πούμε ότι διατηρείτε ένα αρχείο καταγραφής του χρεώσιμου χρόνου σας. Έχετε ώρα έναρξης και ώρα λήξης. Ένας τύπος όπως =E2-D2θα υπολογίσει το χρόνο που έχει παρέλθει.
Το αποτέλεσμα στο F2 είναι 55 λεπτά. Αλλά η προεπιλεγμένη μορφοποίηση δείχνει 12:55 π.μ. Επιλέξτε ολόκληρη τη στήλη. Πατήστε Ctrl + 1 για μορφοποίηση κελιών. Επιλέξτε την κατηγορία Ώρα. Ποιο από αυτά πρέπει να χρησιμοποιήσετε; Προς το παρόν, επιλέξτε 13:30:55, αλλά θα δείτε αργότερα το πρόβλημα με αυτήν την επιλογή.
Η πολιτική της εταιρείας σας είναι να ολοκληρώσετε όλες τις χρεώσεις έως την επόμενη τρίμηνη ώρα. Κάντε αυτόν τον υπολογισμό: Υπάρχουν 24 ώρες την ημέρα. Υπάρχουν 4 τέταρτες ώρες σε μια ώρα. Πολλαπλασιάστε 24 * 4 για να λάβετε 96. Αυτό σημαίνει ότι η τέταρτη ώρα ισοδυναμεί με 1/96 της ημέρας.
Χρησιμοποιήστε τον τύπο =CEILING(F2,1/96)για να στρογγυλοποιήσετε τον χρόνο που έχει παρέλθει σε F έως την επόμενη τέταρτη ώρα. Στη μέθοδο αποθήκευσης χρόνου του Excel, το μεσημέρι είναι 0,5. Το 6AM είναι 0,25. 12:15 π.μ. είναι 1/96 ή 0.010417. Είναι πιο εύκολο να θυμάστε το 1/96 από το 0,010417.
Τα αποτελέσματα φαίνονται παρακάτω. Παρατηρήστε πώς τα 35 λεπτά κυμαίνονται σε 45 λεπτά. Αυτή φαίνεται να είναι η φόρμουλα που χρησιμοποιεί ο δικηγόρος μου … χαχα, αυτό είναι απλώς ένα αστείο Esquire Dewey! Μην με μηνύσεις! Ξέρω ότι χρησιμοποιούν =CEILING(F2,4)…
Σε παύση εδώ… αυτό το άρθρο στρογγυλοποιείται μέχρι την επόμενη τρίμηνη ώρα, επειδή αυτή είναι η μέθοδος που χρησιμοποιεί ο Larry στο σεμινάριό μου. Υπάρχει μια καλή πιθανότητα να συνεργαστείτε σε κάτι άλλο. Η παρακάτω εικόνα δείχνει πώς να στρογγυλοποιήσετε σε πέντε λεπτά ή 1 λεπτό. Θα μπορούσατε να χρησιμοποιήσετε παρόμοια λογική για στρογγυλοποίηση στα πλησιέστερα 6 λεπτά (1/240) ή 12 λεπτά (1/120).
Επίσης - μια ήπια πρόταση που δείχνει τη διαφορά μεταξύ ΔΑΠΕΔΟΥ, ΜΑΡΤΥΡΙΟΥ και ΟΡΟΦΟΥ. Η συνάρτηση FLOOR στο B9: B22 στρογγυλοποιείται πάντα προς τα κάτω. Το MROUND στο C9: C22 στρογγυλοποιείται στον πλησιέστερο. Η ΟΡΟΦΗ σε D9: D22 στρογγυλοποιείται.
Σημείωση
Η επεξήγηση εργαλείου του Excel θα σας προτείνει να χρησιμοποιήσετε το FLOOR.MATH ή το CEILING.MATH αντί για το FLOOR ή το CEILING. Οι νέες εκδόσεις ".Math" αυτών των συναρτήσεων χειρίζονται αρνητικούς αριθμούς διαφορετικά. Επειδή ο χρόνος στο Excel δεν μπορεί ποτέ να είναι αρνητικός, είναι καλό να χρησιμοποιείτε το κανονικό δάπεδο ή το δάπεδο.
Αλλάξτε το κλάσμα από 1/96 σε 1/288 σε βήματα σε πέντε λεπτά.
Μετατροπή ωρών σε ώρες
Μια άλλη πρόταση από τον Larry: Μετατρέψτε τις ώρες σε πραγματικές ώρες. Με αυτόν τον τρόπο, όταν πολλαπλασιάζετε με το ποσοστό χρέωσης ανά ώρα, τα μαθηματικά θα λειτουργήσουν. Πάρτε τον στρογγυλεμένο χρόνο που πέρασε και πολλαπλασιάστε με 24 για να λάβετε έναν δεκαδικό αριθμό ωρών. Φροντίστε να μορφοποιήσετε το αποτέλεσμα ως αριθμό με δύο δεκαδικά ψηφία και όχι με μια ώρα.
Ποια μορφή ώρας να χρησιμοποιήσετε;
Είναι καιρός να μιλήσετε για αυτές τις επιλογές μορφοποίησης ώρας: Θα μπορούσατε να έχετε επιλέξει 13:30, 13:30:55 ή 37:30:55. Στα παραπάνω στιγμιότυπα οθόνης, έχω χρησιμοποιήσει 13:30:55. Το πρόβλημα με αυτό εμφανίζεται στο τέλος της εβδομάδας. Όταν αθροίζετε τις δεκαδικές ώρες στη στήλη H, έχετε 53 χρεώσιμες ώρες. Όταν αθροίζετε τις ώρες στο G, παίρνετε πέντε ώρες. Τι συμβαίνει?
Και τα δύο είναι σωστά, αν καταλάβετε τη μορφή αριθμών που επιλέξατε. Πείτε ότι εισάγετε =NOW()σε ένα κελί. Θα μπορούσατε να μορφοποιήσετε το κελί για να εμφανίσετε την ημερομηνία και την ώρα, την ημερομηνία ή την ώρα. Το Excel δεν έχει κανένα πρόβλημα να αφήσει το τμήμα της 29ης Μαΐου στις 29 Μαΐου 08:15:34 π.μ. εάν το πείτε να το κάνει. Η απάντηση στο κελί G57 είναι "2 ημέρες και πέντε ώρες". Αλλά όταν επιλέξατε 13:37:55 ως τη μορφή ώρας, λέγατε στο Excel να αφήσει τον αριθμό των ημερών και να σας δείξει μόνο τις ώρες, τα λεπτά και τα δευτερόλεπτα.
Εάν μετατρέψετε τη στήλη G σε μορφή 37:30:55, τότε λαμβάνετε τον σωστό αριθμό ωρών:
Η λεζάντα πάνω από αυτήν την εικόνα καταγράφει το μικρό πρόβλημα με τη μορφή 37:30:55: δείχνει δευτερόλεπτα, κάτι που φαίνεται περιττό αφού στρογγυλοποιούμε στα πλησιέστερα δεκαπέντε λεπτά. Αφού επιλέξετε 37:30:55, κάντε κλικ στην προσαρμοσμένη κατηγορία. Μπορείτε να δείτε τον κώδικα μορφοποίησης αριθμών που (h):mm:ss;@χρησιμοποιείται. Οι αγκύλες γύρω από το H είναι το σημαντικό μέρος.
Για να εμφανίσετε τις ώρες σας σε ώρες και λεπτά, επεξεργαστείτε τη μορφή αριθμού για να καταργήσετε το: ss.
Δες το βίντεο
Το σημερινό βίντεο περιέχει μια συμβουλή μπόνους στο τέλος για τη μορφοποίηση των δεκαδικών ως κλάσμα.
Μεταγραφή βίντεο
Μάθετε το Excel από το Podcast, Episode 2210: Round Up to the Next Quarter Hour.
Γεια σας, καλώς ήλθατε πίσω στο netcast, είμαι ο Bill Jelen. Σήμερα, μια άλλη ερώτηση από το σεμινάριο Atlanta Power Excel. Κάποιος έκανε ένα υπολογιστικό φύλλο χρέωσης και ήθελε να στρογγυλοποιήσει την επόμενη τέταρτη ώρα, σωστά; Έτσι, για κάθε εργασία εδώ, έχουν έναν Πελάτη, Ώρα Έναρξης και Ώρα Λήξης, και για να υπολογίσουν τον Χρόνο που έχει παρέλθει - αυτό είναι απλό, εάν είναι πραγματικοί χρόνοι, ή εάν είναι απλώς = E2-D2. Αυτό σημαίνει 55 λεπτά, αλλά το λέει με πολύ περίεργο τρόπο - "12:55 π.μ." Θέλουμε να προχωρήσουμε και να το μορφοποιήσουμε. Επιλέξτε Ώρα, και, κοίτα, υπάρχουν μερικές διαφορετικές επιλογές εδώ: 13:30. 13:30:55, που θα έδειχνε τα δευτερόλεπτα. και μετά 37:30:55. Τώρα, προς το παρόν, θα επιλέξω τις 13:30, αλλά θα επιστρέψουμε αργότερα και θα επανεξετάσουμε αυτήν την επιλογή. Έτσι, επιλέγω OK και κάνω διπλό κλικ για να το αντιγράψω,και βλέπετε ότι κάνει απλά τα μαθηματικά - οπότε εδώ, 25 λεπτά. Όμως, το άτομο που έθεσε αυτήν την ερώτηση - την εταιρεία στην οποία εργάζονται - θέλει να ολοκληρώσει την επόμενη τρίμηνη ώρα.
Εντάξει, λοιπόν, τέταρτη ώρα. Ας σκεφτούμε τις ώρες στο Excel. Το μεσημέρι είναι 0,5. Αν το διαμορφώσω ως ώρα - 12:00:00 μ.μ. Και αν κάνω 0,25, αυτό θα είναι 6:00:00 π.μ. Εντάξει, ο τρόπος με τον οποίο λειτουργεί αυτό είναι ότι μια ολόκληρη μέρα είναι ο αριθμός 1.0, και έτσι, αν θέλαμε να υπολογίσουμε 15 λεπτά, θα ήταν - υπάρχουν 24 ώρες x 4 τρίμηνο ώρες, οπότε θα έπρεπε να βάλουμε = 1/96 εκεί, και αυτό μας δίνει 15 λεπτά. Τώρα, 1/96 - Μπορώ να κάνω αυτά τα μαθηματικά στο μυαλό μου: 24x4. 1/96 - είναι σίγουρα πιο εύκολο να θυμάστε το 1/96 από το να θυμάστε αυτό το δεκαδικό: 0,01041666667. Εντάξει, οπότε ήταν πιο εύκολο να χρησιμοποιήσετε το 1/96 εκεί, και έτσι θα χρησιμοποιήσουμε μια λειτουργία που ονομάζεται CEILING.
ΟΡΟΦΗ. Βλέπουμε, έχουμε μια επιλογή μεταξύ CEILING και CEILING.MATH για θετικούς αριθμούς που είναι πανομοιότυποι. Είναι πιο σύντομο να πληκτρολογείτε CEILING, επομένως θα χρησιμοποιήσω το CEILING Και, στρογγυλεμένο, υπάρχει 1/96 της ημέρας - που είναι 15 λεπτά - και αυτή είναι η σωστή απάντηση, σε λάθος μορφή. Θα αντιγράψουμε αυτές τις μορφές πάνω - Ctrl + C και, στη συνέχεια, Alt + E + S + T για να επικολλήσουμε ειδικές μορφές - και τους γύρους των 55 λεπτών μέχρι την επόμενη ώρα. Κάντε διπλό κλικ για να το αντιγράψετε προς τα κάτω και, δείτε, τα 35 λεπτά περνούν μέχρι τα επόμενα 45. 25 γύρους έως τους επόμενους 30. Φαίνεται να λειτουργεί, αν και το άτομο που έκανε αυτήν την ερώτηση - στρογγυλοποιεί μέχρι τα επόμενα 15. Ίσως ακόμη και η εταιρεία σας χρησιμοποιεί κάτι διαφορετικό.
Εντάξει, έτσι ώστε να φτάσουμε στην πλησιέστερη τέταρτη ώρα, χρησιμοποιούμε το 1/96. Αλλά για στρογγυλοποίηση στα πλησιέστερα 5 λεπτά - υπάρχουν 24 ώρες, υπάρχουν 12 περίοδοι 5 λεπτών σε κάθε ώρα, έτσι 12x4 = 288. Χρησιμοποιήστε το 1/288 για στρογγυλοποίηση στα κοντινότερα 5 λεπτά. Εάν στρίψετε στα πλησιέστερα 6 λεπτά - υπάρχουν 10 περίοδοι 6 λεπτών x 24 ώρες. στρίβετε στο πλησιέστερο 1/240. Για γύρο στο πλησιέστερο λεπτό, επιλέξτε στο πλησιέστερο 1/1440.
Εντάξει, έτσι μπορείτε να το κάνετε αυτό, και έχουμε επίσης μια επιλογή. Το ανώτατο όριο πάντοτε στρογγυλοποιείται, οπότε 16 λεπτά - ας πάμε στο 1/96 εκεί - 16 λεπτά πρόκειται να στρογγυλοποιηθούν σε 30 και, ενώ είμαι βέβαιος ότι ο δικηγόρος μου πιστεύει ότι είναι ένας πολύ καλός τρόπος για να χρεώσετε δεν είναι πραγματικά δίκαιο για μένα - τον πελάτη. Εντάξει, οπότε γνωρίζετε ότι το CEILING θα στρογγυλοποιηθεί. Ο δικηγόρος μου δεν πρόκειται να το χρησιμοποιήσει ποτέ: Αυτό το ΔΑΠΕΔΟ θα είναι πάντα στρογγυλό. Αλλά, πιθανώς αυτό που είναι το πιο δίκαιο εδώ είναι να χρησιμοποιήσετε το MROUND. Λοιπόν, = MROUND της ώρας στο πλησιέστερο 1/96, και αυτό θα στρογγυλοποιηθεί προς τα κάτω για περίπου 22,5 λεπτά. και μετά, μετά από αυτό, θα στρογγυλοποιηθεί. Εντάξει, οπότε στρογγυλοποιείται σε ό, τι είναι πιο κοντά. Οπότε, πρέπει να επιλέξετε αν πρόκειται να χρησιμοποιήσετε το ΔΑΠΕΔΟ, το MROUND ή το CEILING και, στη συνέχεια, ποιος θα είναι ο διαιρέτης σας - είτε όχι "πρόκειται να είναι στα πλησιέστερα 5 λεπτά, το πλησιέστερο λεπτό ή, ξέρετε, οτιδήποτε άλλο. Εντάξει, οπότε εκεί συγκεντρώνουμε.
Τώρα, μια συμβουλή μπόνους εδώ - αυτό ήταν ο Larry σε αυτό το σεμινάριο - Ο Larry λέει, "Γεια, δεν είναι πολύ καλό να δείχνετε χρεώσιμο χρόνο σε τέτοιες στιγμές. Θέλουμε πραγματικά να μετατρέψουμε σε ώρες επειδή πραγματικά θα πάτε για χρέωση με κάποιο ρυθμό - ξέρετε, 35 $ / ώρα, 100 $ / ώρα, 500 $ / ώρα, όποια και αν είναι η τιμή σας - και πρέπει να είμαστε σε θέση να πολλαπλασιάσουμε τις ώρες x τις τιμές. Έτσι, θα ήταν ωραίο να μπορέσουμε να μετατρέψουμε αυτές τις ώρες σε δεκαδικό χρόνο. Έτσι, παίρνουμε αυτόν τον χρόνο σε G2 και πολλαπλασιάζουμε τον με 24, και αυτό θα μας δώσει έναν δεκαδικό αριθμό ωρών, αλλά θα είναι σε λάθος μορφή. πρόκειται να επιλέξει μια μορφή ώρας σε αυτήν την περίπτωση, όταν δεν πρέπει να είναι καθόλου μορφή ώρας · θα πρέπει απλώς να είναι σε μορφή αριθμού. Και βεβαιωθείτε ότι έχετε συμπεριλάβει τουλάχιστον 2 δεκαδικά ψηφία. Κάντε διπλό κλικ για να το αντιγράψετε και παίρνουμε την εποχή μας. "
Εντάξει, τώρα, επιστρέφοντας στην ερώτησή μας εδώ "Ποια μορφή αριθμού πρέπει να χρησιμοποιήσουμε;", αυτό είναι ένα πολύ καλό μέρος για να μιλήσουμε για το "Γιατί;". Εάν επιλέξουμε όλους αυτούς τους αριθμούς, εδώ, θα δούμε ότι το άθροισμα είναι 53 ώρες και αν έρθω εδώ και βάλω μια συνάρτηση SUM, έχουμε 53 ώρες. Αυτό είναι τέλειο. Αλλά, αν χρησιμοποιώ τη συνάρτηση SUM κάτω από μια χρονική στήλη και κάνω Alt + = ή το AUTOSUM, μου δίνει 5 ώρες… 5 ώρες. Ποιο είναι σωστό - 53 ή 5;
Λοιπόν, αποδεικνύεται με κάποια περίεργη έννοια, και οι δύο έχουν δίκιο. Αλλά, γιατί είναι και οι δύο σωστοί. Θα εισαγάγω μια συνάρτηση που ονομάζεται = ΤΩΡΑ. Θα δείτε ότι το ηχογραφώ πραγματικά στις 9 Μαΐου, έτσι, πριν από 20 ημέρες, και είναι 7:08 π.μ. Και με = ΤΩΡΑ, μερικές φορές θέλω να δείξω την ημερομηνία και την ώρα, αλλά άλλες φορές, απλά θέλω για να εμφανιστεί η ημερομηνία. Έτσι, μπορώ να κάνω = ΤΩΡΑ να μου δώσετε την τρέχουσα ημερομηνία, ή, μπορώ να κάνω = ΤΩΡΑ να μου δώσετε την τρέχουσα ώρα. Και έτσι, οι συναρτήσεις αριθμού είναι στην ευχάριστη θέση είτε να περικόψουν το χρονικό όριο είτε να καταργήσουν την ημερομηνία εκτός. Και αυτό που επιλέξαμε εδώ, στις 13:30, λέει, "Γεια, δεν θέλω να δω τον αριθμό των ημερών, απλώς δείξε μου την ώρα." Ναι, αυτό είναι το πρόβλημα - γιατί αυτό είναι πραγματικά 2 ημέρες και 5 ώρες - 2 ημέρες είναι 48 ώρες - 2 ημέρες και 5 ώρες. Και το είπαμε - είπαμε στο Excel, "Γεια, μην ενοχλείς να μου δείξεις τις μέρες. "Λοιπόν, δεν ξέραμε ότι το λέγαμε στο Excel, αλλά αυτό λέγαμε στο Excel όταν επιλέξαμε αυτό ή αυτό. Εντάξει, οπότε είσαι αναγκασμένος να ελάτε εδώ στις 37:30:55. Αυτό θα μας δείξει τον πλήρη αριθμό ωρών και θα έχουμε τη σωστή απάντηση 53 ωρών.
Όμως, φαίνεται πολύ ανόητο να δείξουμε αυτά τα επιπλέον δευτερόλεπτα όταν κάνουμε γύρο σε 15 λεπτά. Ας επιστρέψουμε λοιπόν σε αυτό. Αφού επιλέξετε 37:30:55 για να μάθετε τη μορφή αριθμού πίσω από αυτό, κάντε κλικ στο Custom. Θα δείτε ότι αυτό που το κάνει να λειτουργεί είναι τα τετράγωνα αγκύλια περίπου την ώρα. Όμως, δεν χρειαζόμαστε το: ss. Έτσι, τώρα μπορούμε να επεξεργαστούμε αυτήν την προσαρμοσμένη μορφή και έτσι έχουμε ώρες και λεπτά. Δεν χρειάζεται να καταλάβουμε σήμερα τι σημαίνει; @. Λέει ότι, για κείμενο, απλώς δείξτε το κείμενο. αλλά, αφήστε το μέρος εκεί, ακόμα κι αν δεν το καταλαβαίνετε. Κάντε κλικ στο OK και τώρα έχουμε τον χρόνο μας σε ώρες και λεπτά, αφήνοντας τα δευτερόλεπτα. Όμως, τα σύνολα λειτουργούν. Πραγματικά, πολύ λεπτή, ότι 13:30 και 13:30:55. Ξέρετε, δεν λέει, "Γεια, λέτε στο Excel να αγνοήσει την ημερομηνία … τις πλήρεις ημέρες." Αλλά,είσαι.
Πράγματα όπως το Takeing Time Math βρίσκονται σε αυτό το βιβλίο: LIVe: The 54 Greatest Excel Tips of All Time. Κάντε κλικ στο "I" στην επάνω δεξιά γωνία για να το δείτε.
Αυτό ακούγεται σαν μια τόσο εύκολη ερώτηση. Δεν μπορώ να πιστέψω ότι έχουμε ήδη 7 λεπτά. Όμως, μια εταιρεία θέλει να χρεώσει σε τέταρτο ώρες, και πάντα θέλει να στρογγυλοποιηθεί στην επόμενη τρίμηνη ώρα. Έτσι, 24 ώρες την ημέρα, 24x4 = 96; 15 λεπτά = 1/96. Χρησιμοποιήστε λοιπόν το CEILING (A2,1 / 96) για να φτάσετε στην επόμενη υψηλότερη τρίμηνη ώρα. Χρησιμοποιήστε το MROUND (A2,1 / 96) για στρογγυλοποίηση στην πλησιέστερη τρίμηνη ώρα και, στη συνέχεια, χρησιμοποιήστε το ΔΑΠΕΔΟ (A2,1 / 96) για στρογγυλοποίηση στην χαμηλότερη τρίμηνη ώρα - πάντα επιστρέφει Υπάρχουν 24 ώρες την ημέρα, οπότε μπορείτε να κάνετε 1/288 για να φτάσετε σε περιόδους 5 λεπτών, 1/240 για να φτάσετε σε περιόδους 6 λεπτών, 1/1440 για να γυρίσετε στο πλησιέστερο λεπτό. Και στη συνέχεια, η συμβουλή μπόνους εκεί στο τέλος (ποια μορφή αριθμού) - θέλετε πάντα να επιστρέψετε σε αυτήν την ώρα 37:30:55 εάν πρόκειται να εμφανίσετε ώρες άνω των 24 ωρών.
Για να κατεβάσετε το βιβλίο εργασίας από το σημερινό βίντεο, επισκεφθείτε τη διεύθυνση URL εκεί κάτω στην περιγραφή του Youtube.
Θέλω να σας ευχαριστήσω που σταματήσατε. θα σας δούμε την επόμενη φορά για ένα άλλο netcast από.
Γεια! Ευχαριστώ που μείνατε. Όμως, μόνο μία ακόμη συμβουλή μπόνους εδώ (που δεν πρόκειται να αναφερθώ στον κόπο). Αυτό που ξεκίνησα εδώ - ας καθαρίσουμε όλο αυτό - μια μικρή φόρμουλα που ήρθε και έδειξε ένα από αυτά τα δεκαδικά - έτσι, την πλησιέστερη τέταρτη ώρα. Όμως, ήθελα να το δείξω όχι ως το 0,010417. Είναι φοβερό - στη μορφοποίηση αριθμών, υπάρχει πράγματι κάτι που ονομάζεται Fractions, και αν επιλέξω "Μέχρι δύο ψηφία", θα λάβω 1/96. αλλά, πρέπει να το χειριστώ αυτό το 1/288, οπότε επιλέγω "Έως τρία ψηφία" και μου δείχνει ακόμα 1/96. αλλά, όταν μετατρέψω σε αυτό - το 1/288 - θα το δείξει σωστά. Τώρα, υποθέτω ότι έπρεπε να έχω κάνει "τέσσερα ψηφία" για να χειριστώ το 1/1440. Τι θα κάνει αυτό; Αυτό θα μου δώσει … Αλλά,αν πάω στη Μορφή Αριθμών και πάω στο Προσαρμοσμένο και απλά προσθέσω ένα επιπλέον ερωτηματικό εκεί, τότε θα φτάσουμε στο πλησιέστερο 1/1440 ενός κλάσματος. Αριθμητική μορφή - δροσερό, ωραίο πράγμα.
Λήψη αρχείου Excel
Για να κατεβάσετε το αρχείο excel: round-to-quarter-hour.xlsx
Excel Thought Of the Day
Ζήτησα από τους φίλους μου στο Excel Master τις συμβουλές τους σχετικά με το Excel. Η σημερινή σκέψη να σκεφτούμε:
"Οι φίλοι δεν επιτρέπουν στους φίλους να χρησιμοποιούν ΑΜΕΣΑ"
Jordan Goldmeier
