Một câu hỏi khác từ hội thảo Atlanta Power Excel của tôi:
Làm thế nào bạn có thể làm tròn thời gian có thể thanh toán lên đến quý giờ tiếp theo?
Giả sử rằng bạn ghi lại thời gian có thể lập hóa đơn của mình. Bạn có thời gian bắt đầu và thời gian kết thúc. Một công thức như vậy =E2-D2sẽ tính toán thời gian đã trôi qua.
Kết quả ở F2 là 55 phút. Nhưng định dạng mặc định hiển thị 12:55 sáng. Chọn toàn bộ cột. Nhấn Ctrl + 1 để Định dạng Ô. Chọn danh mục Thời gian. Bạn nên sử dụng cái nào trong số này? Hiện tại, hãy chọn 13:30:55, nhưng sau này bạn sẽ thấy vấn đề với lựa chọn này.
Chính sách của công ty bạn là làm tròn tất cả các hóa đơn cho đến giờ của quý tiếp theo. Thực hiện phép tính này: Có 24 giờ trong một ngày. Có 4 phần tư giờ trong một giờ. Nhân 24 * 4 để được 96. Điều đó có nghĩa là một phần tư giờ tương đương với 1/96 của một ngày.
Sử dụng công thức =CEILING(F2,1/96)để làm tròn thời gian đã trôi qua tính bằng F đến một phần tư giờ tiếp theo. Trong phương pháp lưu trữ thời gian của Excel, Noon là 0,5. 6 giờ sáng là 0,25. 12:15 sáng là 1/96 hoặc 0,010417. 1/96 dễ nhớ hơn 0,010417.
Các kết quả được hiển thị dưới đây. Lưu ý rằng 35 phút làm tròn thành 45 phút. Đây dường như là công thức mà luật sư của tôi sử dụng… haha, đó chỉ là một trò đùa Esquire Dewey! Đừng kiện tôi! Tôi biết họ sử dụng =CEILING(F2,4)…
Tạm dừng ở đây… bài viết này sẽ kéo dài đến 1/4 giờ tới vì đó là phương pháp Larry trong buổi hội thảo của tôi đang sử dụng. Có một cơ hội tốt để bạn chuyển sang làm việc khác. Hình ảnh dưới đây cho thấy cách làm tròn thành năm phút hoặc 1 phút. Bạn có thể sử dụng logic tương tự để làm tròn thành 6 phút (1/240) hoặc 12 phút (1/120) gần nhất.
Ngoài ra - một gợi ý nhẹ nhàng cho thấy sự khác biệt giữa SÀN, MROUND và TRẦN. Hàm FLOOR trong B9: B22 luôn làm tròn xuống. MROUND trong C9: C22 làm tròn chính xác. TRẦN trong D9: D22 làm tròn lên.
Ghi chú
Chú giải công cụ Excel sẽ đề xuất bạn sử dụng FLOOR.MATH hoặc CEILING.MATH thay vì FLOOR hoặc CEILING. Các phiên bản ".Math" mới của các hàm này xử lý các số âm theo cách khác. Bởi vì thời gian trong Excel không bao giờ có thể là số âm, nên bạn có thể sử dụng SÀN thông thường hoặc TRẦN.
Thay đổi phân số từ 1/96 thành 1/288 để làm tròn thành năm phút.
Chuyển đổi thời gian thành giờ
Một gợi ý khác từ Larry: Chuyển đổi thời gian thành giờ thực. Bằng cách đó, khi bạn nhân với tỷ lệ thanh toán mỗi giờ, phép toán sẽ hoạt động. Lấy thời gian đã trôi qua làm tròn và nhân với 24 để được số giờ thập phân. Đảm bảo định dạng kết quả dưới dạng số có hai chữ số thập phân chứ không phải thời gian.
Định dạng thời gian nào để sử dụng?
Đã đến lúc nói về các lựa chọn định dạng thời gian đó: Bạn có thể chọn 13:30, 13:30:55 hoặc 37:30:55. Trong ảnh chụp màn hình ở trên, tôi đã sử dụng 13:30:55. Vấn đề này xuất hiện vào cuối tuần. Khi bạn tổng số giờ thập phân trong cột H, bạn có 53 giờ có thể lập hóa đơn. Khi bạn tổng số thời gian trong G, bạn có năm giờ. Điều gì đang xảy ra?
Cả hai đều đúng, nếu bạn hiểu định dạng số bạn đã chọn. Giả sử rằng bạn nhập =NOW()vào một ô. Bạn có thể định dạng ô để hiển thị ngày và giờ, chỉ ngày hoặc chỉ thời gian. Excel không có vấn đề gì khi rời khỏi phần ngày 29 tháng 5 của ngày 29 tháng 5 08:15:34 sáng nếu bạn yêu cầu Excel làm điều đó. Câu trả lời trong ô G57 là "2 ngày và năm giờ". Nhưng khi bạn chọn 13:37:55 làm định dạng thời gian, bạn đang yêu cầu Excel loại bỏ số ngày và chỉ hiển thị cho bạn giờ, phút và giây.
Nếu bạn chuyển đổi cột G sang sử dụng định dạng 37:30:55, thì bạn sẽ nhận được số giờ chính xác:
Chú thích phía trên hình ảnh đó ghi lại một vấn đề nhỏ với định dạng 37:30:55: nó đang hiển thị giây, điều này có vẻ không cần thiết vì chúng ta đang làm tròn đến mười lăm phút gần nhất. Sau khi chọn 37:30:55, hãy nhấp vào danh mục Tùy chỉnh. Bạn có thể thấy mã định dạng số (h):mm:ss;@được sử dụng. Các dấu ngoặc vuông xung quanh chữ H là phần quan trọng.
Để hiển thị thời gian của bạn theo giờ và phút, hãy chỉnh sửa định dạng số để xóa: ss.
Xem video
Video hôm nay có một mẹo bổ sung ở cuối để định dạng số thập phân dưới dạng phân số.
Bản ghi video
Học Excel từ Podcast, Tập 2210: Sắp tới Giờ Quý tiếp theo.
Chào mừng bạn trở lại netcast, tôi là Bill Jelen. Hôm nay, một câu hỏi khác từ hội thảo Atlanta Power Excel của tôi. Ai đó đang làm một bảng tính thanh toán và họ muốn làm tròn đến một phần tư giờ tiếp theo, phải không? Vì vậy, đối với mỗi tác vụ ở đây, chúng có Máy khách, Thời gian bắt đầu và Thời gian kết thúc, và để tính Thời gian đã trôi qua - điều đó thật đơn giản, nếu đó là thời gian thực hoặc nếu chúng chỉ = E2-D2. Vì vậy, đó là nói 55 phút, nhưng nó đang nói theo một cách thực sự kỳ lạ-- "12:55 sáng". Chúng tôi muốn tiếp tục và định dạng nó. Chọn Thời gian, và này, có một vài lựa chọn khác nhau ở đây: 13:30; 13:30:55, sẽ hiển thị giây; và sau đó là 37:30:55. Bây giờ, ngay bây giờ, tôi sẽ chọn 13:30, nhưng chúng tôi sẽ quay lại sau và kiểm tra lại lựa chọn đó. Vì vậy, tôi chọn OK và nhấp đúp để sao chép nó xuống,và bạn thấy rằng nó chỉ đang làm một phép toán - vì vậy ở đây, 25 phút. Tuy nhiên, người hỏi câu hỏi này - công ty mà họ làm việc - họ muốn làm tròn đến quý tiếp theo.
Được rồi, vậy, một giờ rưỡi. Hãy nghĩ về thời gian trong Excel. Buổi trưa là 0,5. Nếu tôi định dạng đó là thời gian-- 12:00:00 CH. Và nếu tôi làm 0,25, đó sẽ là 6:00 giờ sáng. Được rồi, cách hoạt động của nó là một ngày trọn vẹn là con số 1,0, và vì vậy, nếu chúng ta muốn tính ra 15 phút, thì nó sẽ là-- có 24 giờ x 4 phần tư giờ, vì vậy chúng ta phải đặt = 1/96 trong đó, và điều đó cho chúng tôi 15 phút. Bây giờ, 1/96 - Tôi có thể làm một phép toán trong đầu: 24x4; 1/96 - chắc chắn là dễ nhớ 1/96 hơn là nhớ số thập phân này: 0,01041666667. Được rồi, vì vậy việc sử dụng 1/96 ở đó dễ dàng hơn và vì vậy chúng tôi sẽ sử dụng một hàm có tên là CEILING.
TRẦN NHÀ. Hãy xem, chúng ta có một lựa chọn giữa CEILING và CEILING.MATH cho các số dương giống hệt nhau. Nó ngắn hơn để nhập CEILING, vì vậy tôi sẽ sử dụng CEILING. Và, làm tròn, có 1/96 ngày - tức là 15 phút - và đó là câu trả lời đúng, ở định dạng sai. Chúng tôi sẽ sao chép các định dạng này - Ctrl + C, và sau đó Alt + E + S + T để dán các định dạng đặc biệt-- và 55 phút làm tròn đến giờ tiếp theo. Nhấp đúp để sao chép nó xuống, và xem, 35 phút làm tròn đến 45 phút tiếp theo; 25 vòng cho đến 30 tiếp theo có vẻ như đang hoạt động, mặc dù người hỏi câu hỏi này - họ làm tròn đến 15 tiếp theo. Thậm chí có thể công ty của bạn sử dụng thứ gì đó khác.
Được rồi, để làm tròn đến một phần tư giờ gần nhất, chúng tôi sử dụng 1/96. Nhưng để làm tròn đến 5 phút gần nhất-- có 24 giờ, có 12 khoảng thời gian 5 phút trong mỗi giờ, do đó 12x4 = 288. Sử dụng 1/288 để làm tròn đến 5 phút gần nhất. Nếu bạn làm tròn đến 6 phút gần nhất - có 10 khoảng thời gian 6 phút x 24 giờ; bạn làm tròn đến 1/240 gần nhất. Để làm tròn đến phút gần nhất, hãy chọn chính xác đến 1/1440.
Được rồi, vì vậy bạn có thể làm điều này và chúng tôi cũng có một sự lựa chọn. TRẦN luôn kết thúc, vì vậy 16 phút-- hãy đến 1/96 ở đó-- 16 phút sẽ làm tròn thành 30 và, trong khi tôi chắc luật sư của tôi nghĩ rằng đó là một cách tuyệt vời để lập hóa đơn, đó là không thực sự công bằng với tôi-- khách hàng. Được rồi, vì vậy bạn biết CEILING sẽ làm tròn. Luật sư của tôi sẽ không bao giờ sử dụng cái này: SÀN này sẽ luôn làm tròn. Nhưng, có lẽ điều công bằng nhất ở đây là sử dụng MROUND. Vì vậy, = MROUND of Time chính xác đến 1/96 và điều đó sẽ làm tròn xuống cho mọi thứ trong khoảng 22,5 phút; và sau đó, sau đó, nó sẽ tròn lên. Được rồi, vì vậy nó làm tròn đến bất kỳ thứ gì nó gần nhất. Vì vậy, bạn phải chọn xem bạn sẽ sử dụng FLOOR, MROUND, hay CEILING, và sau đó ước của bạn sẽ là-- cho dù nó 'sẽ là 5 phút gần nhất, phút gần nhất, hoặc, bạn biết đấy, bất cứ điều gì. Được rồi, vậy là chúng ta đang làm tròn.
Bây giờ, một mẹo thưởng ở đây - đây là Larry trong buổi hội thảo đó - Larry nói, "Này, xem này, không thực sự tốt khi hiển thị thời gian có thể lập hóa đơn vào những thời điểm như vậy; chúng tôi thực sự muốn chuyển đổi sang giờ vì bạn thực sự sẽ thanh toán ở một mức nào đó-- bạn biết đấy, 35 đô la / giờ, 100 đô la / giờ, 500 đô la / giờ, bất kể mức phí của bạn là gì-- và chúng tôi cần có thể nhân số giờ x mức phí. Vì vậy, sẽ rất tuyệt nếu có thể chuyển đổi số giờ đó thành thời gian thập phân. Vì vậy, chúng tôi lấy thời gian đó trong G2 và nhân nó với 24 và điều đó sẽ cung cấp cho chúng tôi một số giờ thập phân, nhưng nó sẽ ở định dạng sai. Chúng sẽ chọn định dạng thời gian trong trường hợp này, khi nó hoàn toàn không phải là định dạng thời gian; nó chỉ nên ở định dạng Số. Và đảm bảo bao gồm ít nhất 2 chữ số thập phân. Nhấp đúp để sao chép nó xuống và chúng ta nhận được thời gian của chúng ta. "
Được rồi, bây giờ, quay trở lại câu hỏi của chúng ta ở đây về "Định dạng số nào chúng ta nên sử dụng?", Đây là một nơi thực sự tốt để nói về "Tại sao?". Nếu chúng ta chọn tất cả những con số này, ở đây, chúng ta sẽ thấy rằng tổng là 53 giờ, và nếu tôi xuống đây và đặt vào một hàm SUM, chúng ta sẽ có 53 giờ. Thật hoàn hảo. Nhưng, nếu tôi đang sử dụng hàm SUM bên dưới một cột thời gian và tôi thực hiện Alt + = hoặc AUTOSUM, thì nó sẽ cho tôi 5 giờ… 5 giờ. Câu nào đúng-- 53 hoặc 5?
Ồ, hóa ra theo một nghĩa kỳ lạ nào đó, cả hai đều đúng. Nhưng, đây là lý do tại sao cả hai đều đúng. Tôi sẽ nhập một hàm có tên = NOW. Bạn sẽ thấy tôi thực sự ghi lại đoạn này vào ngày 9 tháng 5, vì vậy, 20 ngày trước, và lúc đó là 7:08 sáng Và với = NOW, đôi khi tôi muốn hiển thị ngày và giờ, nhưng những lần khác, tôi chỉ muốn để hiển thị ngày tháng. Vì vậy, tôi có thể đặt = NOW cho tôi ngày hiện tại hoặc, tôi có thể = NOW cho tôi thời gian hiện tại. Và do đó, các hàm Số rất vui khi cắt bớt thời gian nghỉ hoặc loại bỏ ngày nghỉ. Và câu hỏi mà chúng tôi chọn ở đây, lúc 13:30, nói rằng, "Này, tôi không muốn xem Số ngày; chỉ cần cho tôi xem thời gian." Vâng, đó là vấn đề-- bởi vì đây thực sự là 2 ngày và 5 giờ - 2 ngày là 48 giờ - 2 ngày và 5 giờ. Và chúng tôi đã nói với nó-- chúng tôi đã nói với Excel, "Này, đừng bận tâm cho tôi xem ngày. "Chà, chúng tôi không biết mình đang nói với Excel điều đó, nhưng đó là những gì chúng tôi đã nói với Excel khi chúng tôi chọn cái này hoặc cái này. Được rồi, vì vậy bạn buộc phải xuống đây đến 37:30:55. Đó là số giờ sẽ hiển thị cho chúng ta đầy đủ số giờ và chúng ta nhận được câu trả lời chính xác là 53 giờ.
Tuy nhiên, có vẻ thực sự ngớ ngẩn khi hiển thị những giây thừa đó khi chúng ta làm tròn thành 15 phút. Vì vậy, hãy quay lại vấn đề đó. Sau khi bạn chọn 37:30:55 để tìm hiểu định dạng Số đằng sau điều này, hãy nhấp vào Tùy chỉnh. Bạn sẽ thấy rằng điều làm cho nó hoạt động là các dấu ngoặc vuông xung quanh giờ. Nhưng, chúng ta không cần: ss. Vì vậy, bây giờ chúng tôi có thể chỉnh sửa định dạng tùy chỉnh đó và vì vậy chúng tôi có giờ và phút. Ngày nay chúng ta không cần phải hiểu; @ có nghĩa là gì. Nó nói rằng, đối với văn bản, chỉ cần hiển thị văn bản; nhưng, chỉ để phần đó ở đó, ngay cả khi bạn không hiểu nó. Nhấp vào OK, và bây giờ chúng ta có thời gian tính theo giờ và phút, bỏ qua giây. Nhưng, tổng số hoạt động. Thực sự, rất tinh tế, 13:30 và 13:30:55 đó. Bạn biết đấy, nó không nói, "Này, bạn đang bảo Excel bỏ qua ngày … ngày đầy đủ." Nhưng,bạn là.
Những điều như Toán học dành thời gian có trong cuốn sách này: LIVe: 54 Mẹo Excel hay nhất mọi thời đại. Nhấp vào "Tôi" ở góc trên cùng bên phải để kiểm tra.
Điều này nghe có vẻ như một câu hỏi dễ dàng; Tôi không thể tin rằng chúng ta đã có 7 phút. Tuy nhiên, một công ty muốn lập hóa đơn theo giờ quý và họ luôn muốn làm tròn đến quý giờ tiếp theo. Vì vậy, 24 giờ trong ngày, 24x4 = 96; 15 phút = 1/96. Vì vậy, hãy sử dụng CEILING (A2,1 / 96) để làm tròn đến một phần tư giờ cao hơn tiếp theo. Sử dụng MROUND (A2,1 / 96) để làm tròn đến một phần tư giờ gần nhất và sau đó sử dụng FLOOR (A2,1 / 96) để làm tròn đến một phần tư giờ - luôn quay ngược lại. Có 24 giờ trong một ngày, vì vậy bạn có thể thực hiện 1/288 để chuyển đến khoảng thời gian 5 phút, 1/240 để chuyển đến khoảng thời gian 6 phút, 1/1440 để làm tròn đến phút gần nhất. Và sau đó, mẹo thưởng ở cuối (định dạng Số) - bạn luôn muốn quay lại 37:30:55 đó nếu bạn định hiển thị số giờ vượt quá 24 giờ.
Để tải xuống sổ làm việc từ video hôm nay, hãy truy cập URL đó ở dưới đó trong mô tả Youtube.
Tôi muốn cảm ơn bạn đã ghé qua; chúng tôi sẽ gặp bạn lần sau cho một netcast khác từ.
Chào! Cảm ơn vì đã theo dõi. Nhưng, chỉ có một mẹo thưởng nữa ở đây (mà tôi thậm chí sẽ không bận tâm đến). Những gì tôi đã bắt đầu ở đây-- hãy làm rõ toàn bộ vấn đề này - một công thức nhỏ xuất hiện và chỉ đến một trong những số thập phân này - vì vậy, quý giờ gần nhất. Nhưng, tôi muốn thể hiện điều đó không phải là 0,010417. Thật tuyệt vời-- trong Định dạng Số, thực sự có một thứ gọi là Phân số, và nếu tôi chọn "Có đến hai chữ số", tôi sẽ nhận được 1/96; nhưng, tôi cần xử lý 1/288 đó, vì vậy tôi chọn "Có đến ba chữ số" và nó vẫn hiển thị cho tôi 1/96; nhưng, sau đó khi tôi chuyển đổi sang cái này - 1/288 - nó sẽ hiển thị chính xác. Bây giờ, tôi đoán tôi nên thực hiện "bốn chữ số" để xử lý 1/1440. Điều đó sẽ làm gì? Điều đó sẽ cho tôi … Nhưng,nếu tôi đi vào Định dạng số và đi tới Tùy chỉnh, và chỉ cần thêm một dấu chấm hỏi bổ sung vào đó, thì chúng ta sẽ đi đến 1/1440 gần nhất của một phân số. Định dạng số-- điều tuyệt vời.
Tải xuống tệp Excel
Để tải xuống tệp excel: round-to-quý-giờ.xlsx
Suy nghĩ của Excel trong ngày
Tôi đã hỏi những người bạn Excel Master của mình để được tư vấn về Excel. Hôm nay cần suy ngẫm:
"Bạn bè không cho phép bạn bè sử dụng INDIRECT"
Jordan Goldmeier
