Hướng dẫn how to calculate time overlap in excel - cách tính chồng chéo thời gian trong excel
Show
Giả sử có một số phạm vi ngày được ghi trong một tờ, và bây giờ bạn muốn tính toán số ngày hoặc giờ của các phạm vi ngày đó trùng với một phạm vi ngày cụ thể như bên dưới ảnh chụp màn hình hiển thị. Ngoại trừ để so sánh phạm vi ngày và tính toán từng cái một, ở đây trong bài viết này, tôi giới thiệu một công thức để nhanh chóng giải quyết nhiệm vụ này trong Excel. Tính toán ngày/thời gian chồng chéo với công thức Tính toán ngày/thời gian chồng chéo với công thứcChọn một ô trống mà bạn muốn đặt kết quả được tính toán, nhập công thức này = max (tối thiểu ($ g $ 2, c2) -max ($ g $ 1, b2) +1,0) và kéo xử lý tự động xuống để điền vào Công thức này cho các tế bào khác. Xem ảnh chụp màn hình:=MAX(MIN($G$2,C2)-MAX($G$1,B2)+1,0), and drag auto fill handle down to fill this formula to other cells. See
screenshot: Công thức này cũng có thể tính toán thời gian chồng chéo trong giờ. Mẹo. Nếu bạn muốn nhanh chóng đếm các ngày trong tuần/cuối tuần/ngày cụ thể giữa hai ngày, vui lòng cố gắng sử dụng Kutools cho người trợ giúp chính thức Excel, như trong ảnh chụp màn hình sau. Nó có chức năng đầy đủ mà không giới hạn trong 30 ngày, vui lòng tải xuống và dùng thử miễn phí ngay bây giờ.If you want to quickly count weekdays/weekends/specific day between two dates, please try to use the Kutools for Excel’s Formular Helper as shown in the following screenshot. It’s full function without limitation in 30 days, please download and have a free trial now. Các công cụ năng suất văn phòng tốt nhấtKutools cho Excel giải quyết hầu hết các vấn đề của bạn và tăng năng suất của bạn lên 80%
Tab Office mang giao diện được lập bảng đến văn phòng và làm cho công việc của bạn dễ dàng hơn nhiều
Comments(0) (0) Chưa có xếp hạng nào. Hãy là người đầu tiên xếp hạng! Tác giả: Bài viết của Oscar Cronquist Cập nhật lần cuối vào ngày 27 tháng 4 năm 2021 Tôi đã tìm thấy một bài viết cũ mà tôi nghĩ là thú vị để viết về ngày hôm nay. Hãy nghĩ về hai phạm vi chồng chéo, nó có thể là ngày, thời gian hoặc bất kỳ phạm vi số nào, công thức được thể hiện ở đây hoạt động với mọi thứ. Hình ảnh này cho thấy hai phạm vi thời gian, 06: 00-13: 00 (màu vàng) và 11: 00-18: 00 (màu xanh lá cây). Rõ ràng là có hai giờ chồng chéo nhưng làm thế nào để chúng ta tính toán mức độ trùng lặp của chúng trong Excel? Chức năng trung bình đến giải cứu, nhưng trước tiên, hãy để tôi giải thích chức năng. Nó trả về một giá trị phân tách nửa cao hơn của một tập dữ liệu từ nửa dưới. Ví dụ, trung bình (1,2,3) trả về 2. 1 là nửa dưới và 3 là nửa cao hơn.MEDIAN(1,2,3) returns 2. 1 is the lower half and 3 is the higher half. Trung bình (1,2,3,4,5,6) trả về 3,5 vì có hai giá trị (3, 4) tách biệt nửa cao hơn (5,6) với nửa dưới (1,2). Trung bình của hai giá trị này là 3,5.(1,2,3,4,5,6) returns 3.5 because there are two values (3, 4) separating the higher half (5,6) from the lower half (1,2). The average of these two values is 3.5. Có gì trên trang web này
1. Tính giờ chồng lên nhauChúng tôi có 4 lần ở đây để nhớ, bắt đầu và kết thúc khoảng thời gian 1 và 2. Hãy xem những gì xảy ra nếu chúng ta sử dụng hàm trung bình với giá trị bắt đầu và kết thúc của phạm vi thời gian 1 và chỉ có giá trị bắt đầu của phạm vi thời gian 2.MEDIAN function with the start and end value of time range 1 and only the start value of time range 2. Trung bình ("6:00 sáng", "01:00 PM", "11:00 AM") trở lại 11:00 sáng.("6:00 AM", "01:00 PM", "11:00 AM") returns 11:00 AM. Và sau đó kết thúc phạm vi thời gian 2. Trung bình ("6:00 sáng", "01:00 PM", "06:00 PM") trở lại 01:00 PM("6:00 AM", "01:00 PM", "06:00 PM") returns 01:00 PM 01:00 PM - 11:00 sáng là 02:00. Hai giờ là chồng chéo. Công thức trở thành Median (Start1, End1, End2) -& NBSP; Median (Start1, End1, Start2)(Start1,End1,End2) - MEDIAN(Start1,End1,Start2) và trả về 02:00. Trở lại đầu trang 2. Tính tổng chi phí dựa trên tỷ lệ khác nhau mỗi giờTỷ lệ là 8 từ 12:00 sáng đến 08:00 sáng, 08:00 AM - 6:00 tối Tỷ lệ là 5 và 6:00 tối đến 12:00 sáng, tỷ lệ là 10. Làm thế nào để chúng ta tính tổng chi phí nếu phạm vi thời gian là 06:00 AM - 8:00 tối?calculate total cost if the time range is 06:00 AM - 8:00 PM? Đếm số giờ chồng chéo cho phạm vi đầu tiên 00: 00-08: 00 và nhân với tỷ lệ 8. = Giờ (trung bình ("06:00 AM", "8:00 tối", "08:00 sáng") - Median ("06:00 PM", "8:00 PM", "12:00 sáng") )*số 8HOUR(MEDIAN("06:00 AM", "8:00 PM", "08:00 AM") - MEDIAN("06:00 PM","8:00 PM", "12:00 AM"))*8 trả lại 16. Đếm số giờ chồng chéo cho phạm vi thứ hai 08: 00-18: 00 và nhân với tỷ lệ 5. = Giờ (trung bình ("06:00 sáng", "8:00 tối", "06:00 pm") - trung bình ("06:00 AM", "8:00 tối", "08:00 AM") )*5HOUR(MEDIAN("06:00 AM", "8:00 PM", "06:00 PM") - MEDIAN("06:00 AM","8:00 PM", "08:00 AM"))*5 trả lại 50 Đếm số giờ chồng chéo cho phạm vi thứ hai 18: 00-24: 00 và nhân với tỷ lệ 10. = Giờ (trung bình ("06:00 sáng", "8:00 tối", "12:00 sáng") - trung bình ("06:00 AM", "8:00 tối", "06:00 PM") )*10HOUR(MEDIAN("06:00 AM", "8:00 PM", "12:00 AM") - MEDIAN("06:00 AM","8:00 PM", "06:00 PM"))*10 trả lại 20. Kết hợp tất cả các công thức cho = Giờ (trung bình ("06:00 sáng", "8:00 tối", "08:00 sáng") - Median ("06:00 AM", "8:00 tối", "12:00 sáng") )*8+giờ (trung bình ("06:00 AM", "8:00 tối", "06:00 AM") - Median ("06:00 AM", "8:00 PM", " Am "))*5+giờ (trung bình (" 06:00 sáng "," 8:00 tối "," 12:00 sáng ") - trung bình (" 06:00 sáng "," 8:00 tối "," 06:00 AM "))*10HOUR(MEDIAN("06:00 AM", "8:00 PM", "08:00 AM") - MEDIAN("06:00 AM","8:00 PM", "12:00 AM"))*8+HOUR(MEDIAN("06:00 AM", "8:00 PM", "06:00 AM") - MEDIAN("06:00 AM","8:00 PM", "08:00 AM"))*5+HOUR(MEDIAN("06:00 AM", "8:00 PM", "12:00 AM") - MEDIAN("06:00 AM","8:00 PM", "06:00 AM"))*10 trả lại 86. Trở lại đầu trang 2.1 Tôi có một câu hỏi cho bạnThật tuyệt khi xây dựng một công thức mảng để thu nhỏ công thức ở trên, như thế này: =SUM((MEDIAN(B2:B4,C2:C4,B7+{1;1;1})-MEDIAN(B2:B4,C2:C4,A7+{1;1;1}))*D2:D4)SUM((MEDIAN(B2:B4,C2:C4,B7+{1;1;1})-MEDIAN(B2:B4,C2:C4,A7+{1;1;1}))*D2:D4) Nhưng nó sẽ không hoạt động, bạn không thể sử dụng chức năng trung bình để làm điều đó. Bạn có biết một cách giải quyết? Xem phần tiếp theo.MEDIAN function to do that. Do you know a workaround? See next section. Trở lại đầu trang 3. Tính tổng chi phí dựa trên tỷ lệ khác nhau mỗi giờ (công thức nhỏ hơn)Hình ảnh trên cho thấy một công thức tính toán tổng chi phí dựa trên tỷ lệ mỗi giờ. Kiểm tra công thức Alex Grobermans trong phần bình luận bên dưới. = Sumproduct (tần số (hàng (gián tiếp ("a" & giờ (c7) +1 & ": a" & giờ (c8))), giờ (d3: d4)), e3: e5)SUMPRODUCT(FREQUENCY(ROW(INDIRECT("A"&HOUR(C7)+1&":A"&HOUR(C8))), HOUR(D3:D4)), E3:E5) Công thức ở trên sẽ không hoạt động nếu bạn bắt đầu và kết thúc kéo dài trong nhiều ngày, hãy xem phần tiếp theo bên dưới. Trở lại đầu trang 3. Tính tổng chi phí dựa trên tỷ lệ khác nhau mỗi giờ (công thức nhỏ hơn)Hình ảnh trên cho thấy một công thức tính toán tổng chi phí dựa trên tỷ lệ mỗi giờ. Kiểm tra công thức Alex Grobermans trong phần bình luận bên dưới.= Sumproduct (tần số (hàng (gián tiếp ("a" & giờ (c7) +1 & ": a" & giờ (c8))), giờ (d3: d4)), e3: e5) HOUR(C7)(C7) Công thức ở trên sẽ không hoạt động nếu bạn bắt đầu và kết thúc kéo dài trong nhiều ngày, hãy xem phần tiếp theo bên dưới. HOUR(42005.25)(42005.25) Giải thích tính toán trong tế bào C10 Bước 1 - tính toán giờHàm giờ trả về một số nguyên đại diện cho giờ của giá trị thời gian excel. INDIRECT("A"&HOUR(C7)+1&":A"&HOUR(C8))("A"&HOUR(C7)+1&":A"&HOUR(C8)) Công thức ở trên sẽ không hoạt động nếu bạn bắt đầu và kết thúc kéo dài trong nhiều ngày, hãy xem phần tiếp theo bên dưới. INDIRECT("A"&6+1&":A"&12)("A"&6+1&":A"&12) Công thức ở trên sẽ không hoạt động nếu bạn bắt đầu và kết thúc kéo dài trong nhiều ngày, hãy xem phần tiếp theo bên dưới. INDIRECT("A"&7&":A"&12)("A"&7&":A"&12) Công thức ở trên sẽ không hoạt động nếu bạn bắt đầu và kết thúc kéo dài trong nhiều ngày, hãy xem phần tiếp theo bên dưới. INDIRECT("A7:A12")("A7:A12") Giải thích tính toán trong tế bào C10 Bước 1 - tính toán giờHàm giờ trả về một số nguyên đại diện cho giờ của giá trị thời gian excel. ROW(INDIRECT("A"&HOUR(C7)+1&":A"&HOUR(C8)))(INDIRECT("A"&HOUR(C7)+1&":A"&HOUR(C8))) Công thức ở trên sẽ không hoạt động nếu bạn bắt đầu và kết thúc kéo dài trong nhiều ngày, hãy xem phần tiếp theo bên dưới. ROW(A7:A12)(A7:A12) Giải thích tính toán trong tế bào C10 Bước 1 - tính toán giờHàm giờ trả về một số nguyên đại diện cho giờ của giá trị thời gian excel. trở thành(data_array, bins_array) và trả lại 6.(ROW(INDIRECT("A"&HOUR(C7)+1&":A"&HOUR(C8))), HOUR(D3:D4)) Công thức ở trên sẽ không hoạt động nếu bạn bắt đầu và kết thúc kéo dài trong nhiều ngày, hãy xem phần tiếp theo bên dưới. Giải thích tính toán trong tế bào C10({7; 8; 9; 10; 11; 12}, {8;18}) Bước 1 - tính toán giờ Hàm giờ trả về một số nguyên đại diện cho giờ của giá trị thời gian excel.trở thành và trả lại 6.(array1, [array2], ...) Bước 2 - Tạo tham chiếu ô(FREQUENCY(ROW(INDIRECT("A"&HOUR(C7)+1&":A"&HOUR(C8))), HOUR(D3:D4)), E3:E5) Công thức ở trên sẽ không hoạt động nếu bạn bắt đầu và kết thúc kéo dài trong nhiều ngày, hãy xem phần tiếp theo bên dưới. Giải thích tính toán trong tế bào C10({2; 4; 0}, E3:E5) Công thức ở trên sẽ không hoạt động nếu bạn bắt đầu và kết thúc kéo dài trong nhiều ngày, hãy xem phần tiếp theo bên dưới. Giải thích tính toán trong tế bào C10({2; 4; 0}, {8;5;10}) Bước 1 - tính toán giờ Trở lại đầu trang Hàm giờ trả về một số nguyên đại diện cho giờ của giá trị thời gian excel.trở thành và trả lại 6. Bước 2 - Tạo tham chiếu ôSUMPRODUCT((MOD(C7+(ROW(INDIRECT("A1:A"&(C8-C7)/(1/24)))-1)/24, 1)<TRANSPOSE(D3:D5))*(MOD(C7+(ROW(INDIRECT("A1:A"&(C8-C7)/(1/24)))-1)/24, 1)>=TRANSPOSE(C3:C5))*TRANSPOSE(E3:E5)) Hàm gián tiếp tạo ra một tham chiếu ô dựa trên các giá trị văn bản. và trả về A7: A12.LET(x, MOD(C7+(SEQUENCE(, (C8-C7)/(1/24))-1)/24, 1), SUMPRODUCT((x Trở lại đầu trang 3. Tính tổng chi phí dựa trên tỷ lệ khác nhau mỗi giờ (công thức nhỏ hơn)Hình ảnh trên cho thấy một công thức tính toán tổng chi phí dựa trên tỷ lệ mỗi giờ. Kiểm tra công thức Alex Grobermans trong phần bình luận bên dưới. = Sumproduct (tần số (hàng (gián tiếp ("a" & giờ (c7) +1 & ": a" & giờ (c8))), giờ (d3: d4)), e3: e5) Trở lại đầu trang Làm thế nào để bạn tính toán khoảng thời gian chồng chéo trong Excel?Overlap = min (A2, B2) - Max (A1, B1) + 1. Nói cách khác, sự chồng chéo của hai khoảng số nguyên là sự khác biệt giữa giá trị tối thiểu của hai ranh giới trên và giá trị tối đa của hai ranh giới dưới, Cộng với 1.. In other words, the overlap of two integer intervals is a difference between the minimum value of the two upper boundaries and the maximum value of the two lower boundaries, plus 1.
Làm thế nào để bạn tính toán giờ chồng chéo?Đếm số giờ chồng chéo cho phạm vi đầu tiên 00: 00-08: 00 và nhân với tỷ lệ 8. Trả về 16. Đếm số giờ chồng chéo cho phạm vi thứ hai 08: 00-18: 00 và nhân với tốc độ 5. Đếm số giờ chồng chéo cho phạm vi thứ hai18: 00-24: 00 và nhân với tỷ lệ 10.. returns 16. Count overlapping hours for the second range 08:00-18:00 and multiply with rate 5. Count overlapping hours for the second range 18:00-24:00 and multiply with rate 10.
Làm thế nào để bạn làm phân tích chồng chéo trong excel?Mở không gian làm việc chứa các bộ dữ liệu mà bạn muốn xác định sự chồng chéo cho, chọn một hoặc nhiều bộ dữ liệu và nhấp vào phân tích mối quan hệ chạy.Chọn Phân tích chồng chéo.Sau đó, nhấp vào phân tích.Lưu ý: Bạn có thể chạy phân tích mối quan hệ quan trọng cùng lúc với phân tích chồng chéo.
Làm cách nào để tính toán khoảng thời gian trong Excel?Một kỹ thuật đơn giản khác để tính thời lượng giữa hai lần trong Excel là sử dụng hàm văn bản: Tính giờ giữa hai lần: = Text (B2-A2, "H") Giờ trả về và phút giữa 2 lần: = Text (B2-A2,"H: mm") Giờ trả về, phút và giây trong khoảng 2 lần: = Text (B2-A2, "H: MM: SS")Calculate hours between two times: =TEXT(B2-A2, "h") Return hours and minutes between 2 times: =TEXT(B2-A2, "h:mm") Return hours, minutes and seconds between 2 times: =TEXT(B2-A2, "h:mm:ss") |