Quy tròn dữ liệu thời gian ?

[Chuotdong]

Mình muốn quy tròn time ví dụ 2h20'21" thì thành 3h còn nếu 2h20'19" thì thành 2h thì làm thế nào các bạn nhỉ -nghĩa là lớn hơn 20'20" thì làm tròn lên thành 1giờ. Mình thấy phải chuyển về dạng thập phân rồi round, có cách nào tiện hơn không ?

 

[BNTT]

Mình muốn quy tròn time ví dụ 2h20'21" thì thành 3h còn nếu 2h20'19" thì thành 2h thì làm thế nào các bạn nhỉ -nghĩa là lớn hơn 20'20" thì làm tròn lên thành 1giờ. Mình thấy phải chuyển về dạng thập phân rồi round, có cách nào tiện hơn không ?

Dùng ROUND thì tôi nghĩ chưa ra.
Nhưng làm theo cái này:

Lớn hơn 20'20" thì làm tròn lên thành 1 giờ​

Thì làm được.

Nghĩa là ta sẽ lấy phần phút và giây của giá trị thời gian đã cho, so sánh với 20'21", nếu nó lớn hơn hoặc bằng 20'21" thì làm tròn lên 1 giờ chẵn, còn nếu nhỏ hơn thì làm tròn xuống giờ hiện hành.

Trước hết, phải xác định 20'21" là bằng bao nhiêu (theo quy ước của Excel).
Excel xem tổng thời gian của 1 ngày = 1.
Mà một ngày thì có 24x60x60 = 86400 giây, do đó 1 giây (theo Excel) = 1/86400
Vậy, 20'21" = 20x60 + 21 = 1221 giây, hay thời gian này (20'21") theo Excel = 1221/86400

Tiếp theo, xác định xem 1 giờ bằng bao nhiêu (theo Excel). Như trên tôi đã nói, tổng thời gian trong 1 ngày là 1, do đó 1 giờ = 1/24

Bây giờ, trích lấy phần giờ trong giá trị thời gian đã cho. Giả sử bạn nhập giá trị thời gian trong ô B1. Thì phần giờ trong giá trị thời gian đó sẽ là:

= HOUR(B1)*1/24​

Suy ra phần phút và giây trong giá trị thời gian ở B1 là:

= B1 - HOUR(B1)*1/24​

Việc cuối cùng là đem so sánh giá trị phút và giây này với 20'21", và dùng hàm TIME(giờ, phút, giây) để ghép lại.

Công thức để "Lớn hơn 20'20" thì làm tròn lên thành 1 giờ" như sau:

= TIME(IF(B1 - HOUR(B1)*1/24 >= 1221/86400, HOUR(B1)+1, HOUR(B1)), 0, 0)​

Thử nhé:

B1 là 4:15:17, công thức trên sẽ ra kết quả là 4:00:00
B1 là 5:20:22, công thức trên sẽ ra kết quả là 6:00:00​

Đúng ý bạn không?

Nếu bạn không thích so sánh với 20'21", mà là một giá trị khác, thì bạn làm theo cách tôi đã nói ở trên, quy cái thời gian mấy phút mấy giây này thành giây rồi chia cho 86400.

Nhưng nói thiệt, công thức này tôi vẫn chưa vừa ý, vì hơi dài dòng, nhưng chưa nghĩ ra có cách nào cho nó ngắn hơn.

----------------
Bây giờ mới để ý bạn có gửi kèm cái File. Tôi đưa công thức ở trên vào file đó cho bạn xem nè:

 

[Chuotdong]

Dùng round thì mình làm như thế này, nhưng thấy chưa được. Mà cũng thấy phức tạp quá.

 

[BNTT]

Dùng round thì mình làm như thế này, nhưng thấy chưa được. Mà cũng thấy phức tạp quá.

Không hiểu ý bạn lắm.

Bạn muốn sau khi quy tròn, thì giá trị của kết quả là giá trị thời gian hay là giá trị số?

Tôi nói vậy, bởi số 2 và 2 tiếng đồng hồ, là khác nhau xa lắm lắm!!

Số 2 là bằng 2

Còn 2 giờ (2 tiếng đồng hồ), nó bằng 2/24 = 0.0833333333333333

 

[ndu96081631]

Dùng round thì mình làm như thế này, nhưng thấy chưa được. Mà cũng thấy phức tạp quá.

Thế sao bạn không dùng:

=HOUR(A2)+(MINUTE(A2)>20)

 

[Chuotdong]

Không hiểu ý bạn lắm.

Bạn muốn sau khi quy tròn, thì giá trị của kết quả là giá trị thời gian hay là giá trị số?

Tôi nói vậy, bởi số 2 và 2 tiếng đồng hồ, là khác nhau xa lắm lắm!!

Số 2 là bằng 2

Còn 2 giờ (2 tiếng đồng hồ), nó bằng 2/24 = 0.0833333333333333

Đúng rồi, đơn vị tính chỉ là giờ (< 24h) vì phần ngày trong công việc cụ thể của mình đã đựoc tách ra rồi bạn ạ.


Giá trị là thời gian cần thể hiện ở cột A nên mình để nguyên hh:mm:ss nhưng vì mục đích kết quả ở Cột B,C chỉ là tổng số giờ (phải quy tròn lên) nên ở đây chỉ là giá trị số - mà thậm trí là số nguyên nữa cơ đúng không bạn

 

[BNTT]

Đúng rồi, đơn vị tính chỉ là giờ (< 24h) vì phần ngày trong công việc cụ thể của mình đã đựoc tách ra rồi bạn ạ.


Giá trị là thời gian cần thể hiện ở cột A nên mình để nguyên hh:mm:ss nhưng vì mục đích kết quả ở Cột B,C chỉ là tổng số giờ (phải quy tròn lên) nên ở đây chỉ là giá trị số - mà thậm trí là số nguyên nữa cơ đúng không bạn

Nếu chỉ có vậy, bạn dùng công thức của anh Ndu96081631 gợi ý, nhưng thêm phần tính giây nữa thì mới chính xác:

= HOUR(A2) + AND(MINUTE(A2) >= 20, SECOND(A2) >= 21)​

Nếu phút + giây lớn hơn hoặc bằng 20'21" thì tăng số giờ lên 1, còn không thì giữ nguyên phần giờ. Thay cái 20 và 21 bằng cái bạn muốn.

Nhớ định dạng cho ô chứa công thức là Number

 

[hoangvuluan]

Hình như là:

= HOUR(A2) + IF(MINUTE(A2) > 20, 1, IF(AND(MINUTE(A2)=20, SECOND(A2) >= 21),1,0)
​

 

[Chuotdong]

Theo ý của BNTT mình sẽ xem lại tại sao lại đã tách phần ngày ra để làm gì, cái này mình giúp người khác thôi, nhưng khi hỏi lại thì thấy họ cũng muốn khái quát lên. Cần sẽ trao đổi với các bạn.

 

[BNTT]

Hình như là:

= HOUR(A2) + IF(MINUTE(A2) > 20, 1, IF(AND(MINUTE(A2)=20, SECOND(A2) >= 21),1,0)
​

1. Nếu chỉ để lấy ra 1, hoặc 0, thì không cần dùng đến IF đâu bạn ơi. Bản thân hàm AND() đã trả về 1 nếu đúng và 0 nếu sai rồi.

2. Cái công thức của bạn, cần gì đến 2 cái IF vậy?

Mình muốn quy tròn time ví dụ 2h20'21" thì thành 3h còn nếu 2h20'19" thì thành 2h thì làm thế nào các bạn nhỉ - nghĩa là lớn hơn 20'20" thì làm tròn lên thành 1giờ. Mình thấy phải chuyển về dạng thập phân rồi round, có cách nào tiện hơn không ?

Lớn hơn 20'20" và lớn hơn hoặc bằng 20'21" có gì khác nhau đâu?

 

[hoangvuluan]

1. Nếu chỉ để lấy ra 1, hoặc 0, thì không cần dùng đến IF đâu bạn ơi. Bản thân hàm AND() đã trả về 1 nếu đúng và 0 nếu sai rồi.

2. Cái công thức của bạn, cần gì đến 2 cái IF vậy?

Nếu làm tròn 4:21:00 thì thành mấy giờ? 4:00:00 hay 5:00:00 ?
vì khi đó hàm And(..,..) trả về giá trị sai (=0)

 

[BNTT]

Nếu làm tròn 4:21:00 thì thành mấy giờ? 4:00:00 hay 5:00:00 ?
vì khi đó hàm And(..,..) trả về giá trị sai (=0)

Cảm ơn bạn. Đúng là công thức ở trên của tôi quên xét trường hợp phút > 20 nhưng giây < 20.

Xin sửa lại như sau:

= HOUR(A2) + OR(MINUTE(A2) > 20, AND(MINUTE(A2) = 20, SECOND(A2) > 20))​

 

[hoangvuluan]

Chuyển kiểu tự động

Cảm ơn bạn. Đúng là công thức ở trên của tôi quên xét trường hợp phút > 20 nhưng giây < 20.

Xin sửa lại như sau:

= HOUR(A2) + OR(MINUTE(A2) > 20, AND(MINUTE(A2) = 20, SECOND(A2) > 20))
​

Dùng các hàm logic trong các phép toán số học như dùng con dao 2 lưỡi. Mặc dù nó thực hiện rất nhanh trong nhiều phép tính, nhưng luôn phải lưu ý: Excel thuộc nhóm ngôn ngữ có khả năng TypeCast (ép kiểu, chuyển kiểu) tự động; điều này chỉ xảy ra khi thực hiện logic với số học như ví dụ ở trên (phép cộng đã ép biểu thức lôgic trả về giá trị 0, 1 thay vì False, True)

Nhưng đừng bao giờ nói rằng False=0 và True=1. Bạn có thể kiểm chứng điều này trong Excel (vì dụ: công thức tại A1 = True=1 hoặc = False=0 sẽ cho giá trị False.

Dẫu sao việc dùng các hàm logic để đơn giản hóa vẫn được ưa chuộng, vì nó thực hiện nhanh hơn (chẳng hạn để kiểm tra điều kiện n số đều là số lẻ, chỉ cần dùng And(i1, i2,..., in).

Thân!

 

[Chuotdong]

Tiện đây gửi các bạn bài toán thực tế của bạn mình, mình cũng chưa sửa gì nhiều
Đang xem xét bỏ cột tách ngày đi ,.v.v.....
Các bạn có cách nào trông gọn hơn cứ chỉnh sửa giúp bạn mình luôn nhé

 

[BNTT]

Tiện đây gửi các bạn bài toán thực tế của bạn mình, mình cũng chưa sửa gì nhiều
Đang xem xét bỏ cột tách ngày đi ,.v.v.....
Các bạn có cách nào trông gọn hơn cứ chỉnh sửa giúp bạn mình luôn nhé

Cách trình bày dữ liệu trong file của bạn hơi khó hiểu. Bạn có thể giải thích cụ thể hơn cái này không:

Nội quy: Giá thuê xe bao gồm tính theo km và theo giờ đơn giá tùy theo đối tượng sử dụng, cụ thể:
- Giá lưu xe tính theo giờ, quá 20 phút tính tròn lên thành một giờ, quá 10 giờ/ ngày cũng chỉ tính là 10 giờ.
- Đối tượng loại A: giá lưu xe là 10.000 đ/giờ. Đối tượng loại B: giá lưu xe là 20.000 đ/giờ
- Giá thuê xe tính theo km: đối tượng loại A là 2.000đ/km; loại B là 3.000 đ/km.

Riêng về mặt tính thời gian, xin góp ý với bạn như sau:
Bạn lấy giờ trả xe trừ đi giờ thuê xe như trong file, vì việc tính giờ của bạn như vậy sẽ không chính xác.

Ví dụ, người ta thuê xe từ 17:00 ngày 1/12/2008, trả xe vào lúc 7:15 ngày 3/12/2008
Thí tính như thế nào? Nếu làm như bạn, ở phần ngày sẽ là 2, còn ở phần giờ, nếu bạn lấy 7:15 trừ 17:00 thì nó sẽ là một con số âm!

Nhưng nếu tính thủ công, thì từ 17:00 ngày 1/12/2008 đến 7:15 ngày 3/12/2008, chưa được 2 ngày! Bạn tin không?

Này nhé:
- Từ 17:00 ngày 1/12/2008 đến 17:00 ngày 2/12/2008 là 1 ngày tròn.
- Từ 17:00 ngày 2/12/2008 đến 7:15 ngày 3/12/2008, mới có 14 tiếng 15 phút thôi, chưa được 24 tiếng mà.

Nếu tính chính xác, thì thì từ 17:00 ngày 1/12/2008 đến 7:15 ngày 3/12/2008 = 1.59 ngày

Để cộng trừ thời gian (có cả ngày, giờ), bạn nên gộp ngày và giờ lại, rồi hẵng làm tính trừ.

Ví dụ, với bảng tính này:

|

A​

​

|

B​

​

|

C​

​

|

D​

​

|

E​

​

|

F​

​

|

1​

​

||

Nhận xe​

​

|

​

​

|

​

​

|

Giao xe​

​

|

​

​

|

​

​

|

2​

​

|

Người thuê​

|

Ngày tháng​

|

Giờ (h)​

|

Đồng hồ 4 số cuối​

|

Ngày tháng​

|

Giờ (h)​

|

Đồng hồ 4 số cuối​

|

3​

​

|

Ông A​

|

03/12/2008​

|

6:00​

|

63233​

|

05/12/2008​

|

20:00​

|

63783​

|

4​

​

|

Bà B​

|

06/12/2008​

|

13:00​

|

63783​

|

09/12/2008​

|

6:30​

|

64327​

|

Để tính số ngày và giờ đã thuê xe của ông A, bạn dùng công thức: = (E3+F3)-(B3+C3) = 2.58 (ngày)

Hay là:

• Để tính số ngày đã thuê xe: = DAY((E3+F3)-(B3+C3)) = 2 (ngày tròn)
• Để tính số giờ đã thuê xe (phần còn lại ít hơn 1 ngày): = HOUR((E3+F3)-(B3+C3)) = 14 (tiếng)
• Để tính số phút đã thuê xe (phần còn lại ít hơn 1 giờ): = MINUTE((E3+F3)-(B3+C3)) = 0 (phút)

Copy công thức xuống hàng 4, bạn sẽ có ngày giờ thuê xe của bà B là: (0 ngày +) 17 tiếng + 30 phút.

Đó là tôi chỉ nói tới chuyện tính thời gian chênh lệch (chính xác) giữa hai giá trị thời gian (có ngày và giờ cụ thể).
Còn chuyện muốn làm tròn sao đó, thì chưa nói đến. Đợi bạn cho ý kiến đã.

 

[Chuotdong]

Cám ơn BNTT, như mình đã nói cái này là của bạn mình làm, nói chung mình thấy không chặt chẽ và rất nhiều công đoạn phải làm bằng tay. Lúc đó bạn mình chỉ nhờ xem mỗi cái đoạn quy đổi nếu quá 20 phút lên thành 1 giờ thôi.

Nên khi rảnh mình sẽ đọc lại có gì chưa rõ sẽ trao đổi tiếp sau.

 

[Chuotdong]

Nhưng nếu tính thủ công, thì từ 17:00 ngày 1/12/2008 đến 7:15 ngày 3/12/2008, chưa được 2 ngày! Bạn tin không?

Này nhé:
- Từ 17:00 ngày 1/12/2008 đến 17:00 ngày 2/12/2008 là 1 ngày tròn.
- Từ 17:00 ngày 2/12/2008 đến 7:15 ngày 3/12/2008, mới có 14 tiếng 15 phút thôi, chưa được 24 tiếng mà.

Nếu tính chính xác, thì thì từ 17:00 ngày 1/12/2008 đến 7:15 ngày 3/12/2008 = 1.59 ngày

Chỗ này đúng là có hiểu khác nhau: 1 ngày ở đây không có nghĩa là 24 giờ mà là qua 0h đêm. Như vậy trong ví dụ của bạn:
- Từ 17:00 ngày 1/12/2008 đến 24:00 ngày 1/12/2008 là 7 gio tròn.
- Từ 00:00 ngày 2/12/2008 đến 24:00 ngày 2/12/2008 là 1 ngày tròn.
- Từ 00:00 ngày 3/12/2008 đến 7:15' ngày 3/12/2008 là 7 giờ.

Như vậy = riêng chi phí lưu xe , nếu người thuê là loại A sẽ là:
7 x 10.000 đ + 100.000 đ + 7 x 10.000 đ = 240.000 đ

 

[BNTT]

Chỗ này đúng là có hiểu khác nhau: 1 ngày ở đây không có nghĩa là 24 giờ mà là qua 0h đêm. Như vậy trong ví dụ của bạn:
- Từ 17:00 ngày 1/12/2008 đến 24:00 ngày 1/12/2008 là 7 gio tròn.
- Từ 00:00 ngày 2/12/2008 đến 24:00 ngày 2/12/2008 là 1 ngày tròn.
- Từ 00:00 ngày 3/12/2008 đến 7:15' ngày 3/12/2008 là 7 giờ.

Như vậy = riêng chi phí lưu xe , nếu người thuê là loại A sẽ là:
7 x 10.000 đ + 100.000 đ + 7 x 10.000 đ = 240.000 đ

Ủa, cũng giống nhau mà Chuotdong?
Cái của mình là 1 ngày + 14 giờ = 1*100.000 + 14*10.000 = 240.000 ?

 

[Chuotdong]

Không may nó lại giống nhau. Ví dụ khác dễ hiểu hơn như sau:

Một người thuê xe từ 17h ngày 1/12/08 đến 17h ngày 2/12/08 sẽ phải tính tiền như sau:

1) từ 17h ngày 1/12 đến 24h ngày 1/12: là 7 giờ (< 10 giờ) nên tiền lưu xe phải trả ngày này: là 10.000đ x 7 giờ = 70.000đ.
2) từ 0h ngày 2/12 đến 17h ngày 2/12: là 17 giờ , > 10 giờ (theo Nội quy đã nêu: "quá 10 giờ/ ngày cũng chỉ tính là 10 giờ.") nên chỉ tính như là 10 giờ -> chi phí lưu xe ngày hôm này là: 100.000 đ

Tổng cộng phải trả: 170.000 đ

Theo cách tính của bạn đi qua 2 ngày mà chỉ là 24h (vì > 10 giờ theo Nội quy), nên chỉ phải trả: 100.000đ. Đó là sự khác nhau