Lớp học GPE tháng 10 - TPHCM: Conditional Formatting và Data Validation (tối 4, 6/10) | Excel cơ bản (tối 9, 11, 13/10) |
Thuần thục các hàm dò tìm (tối 10, 12/10) | Tất tần tật về PivotTable (tối 16, 18, 20/10) |
Tất tần tật về Filter và Advanced Filter (tối 23, 25/10) | Name động và biểu đồ (tối 24, 26, 28/10)

Đăng ký học Khởi đầu cùng Google Spreadsheet - 2 chủ nhật 1 và 8/10 - TPHCM

Đăng ký học Xây dựng ứng dụng Form bằng VBA - 2 chủ nhật 15 và 22/10 - TPHCM

Giúp code hoặc công thức tính thời gian theo buổi

Thảo luận trong 'Lập Trình với Excel' bắt đầu bởi minhtuan55, 28 Tháng tám 2017.

  1. minhtuan55

    minhtuan55 Thành viên thường trực

    Chào cả nhà GPE !
    Em có vấn đề tính thời gian theo từng buổi dựa vào giờ vào và giờ ra. Em làm gần 2 ngày mà chưa ra, nay nhờ các Cao thủ giúp em. Vấn đề em như sau:

    - Giờ buổi sáng : từ 6h đến 10h
    - Giờ buổi chiều: từ 10h đến 23h
    - Giờ buổi tối: từ 23 đến 6h hôm sau

    Ví dụ :
    Vào 29/8/2017 7:00 - ra 29/8/2017 9:00
    Thì kết quả mong muốn
    - giờ buổi sáng : 2 giờ ( giờ buổi chiều và buổi tối = 0 )
    -------------------------------------------------------------------------------------------
    Ví dụ :
    Vào 29/8/2017 10:00 - ra 29/8/2017 12:30
    Thì kết quả mong muốn
    - giờ buổi chiều: 2,5 giờ( giờ buổi sáng và buổi tối = 0 )

    -------------------------------------------------------------------------------------------
    Ví dụ :
    Vào 29/8/2017 9:00 - ra 29/8/2017 13:00
    Thì kết quả mong muốn
    - giờ buổi sáng : 1 giờ ( từ 9h đến 10h)
    - giờ buổi chiều: 3 giờ ( từ 10h đến 13h)
    - Giờ buổi tối : 0 giờ

    Em ví dụ 3 ví dụ minh họa cho Mọi người dể hiểu. Mong mọi người giúp đở. Em xin chân thành cảm ơn
     

    Các file đính kèm:

  2. meocon92

    meocon92 Thành viên mới

    Theo Phật phải từ bi hỷ xả, tâm ngôn khiêm cung từ tốn, tránh cao ngạo hỷ nộ ái ố, tứ đại giai không, diệt dục, diệt tâm ma. Tâm hư không mới chứa được vạn vật, không cao ngạo, hạ mình khiêm nhường thì mới có khả năng thụ nhận kiến thức mới, thùng đã đầy thì làm thế nào chứa thêm thứ khác vào! xả bớt đi lại chứa được thêm mới vào.Thế nhá cậu! nhẽ ra phải xứng với hình biểu tượng thì hay nhỉ!
    Bài trên với khả năng các bé phổ thông làm bằng các công thức thường thực hiện một phát xong ngay, cớ chi đánh đố để mang sầu khổ?
    Tớ giải đây nhé, có lần ra thì tốt, không thì tớ cũng chịu thôi:
    PHP:
    D10=IF($J$3=$J$4,N("Thỏa")+IF(($A$4-$A$3-0.0001)*24>LOOKUP($J$3,{1,2,3},{20,11,17}),($C10=$J$3)*SUM($K$3:$K$4)
    +
    LOOKUP($C10,{1,2,3},{4,13,7})*(LOOKUP($C10,{1;2;3},MMULT(N(IFERROR(MATCH(TRANSPOSE(ROW(INDIRECT(INT($A$3)&":"&INT($A$4)))+{6,10,23}/24),$A$3:$A$4,1)=1,)),ROW(INDIRECT("1:"&INT($A$4)-INT($A$3)+1))^0))
    -OR(
    ROUND(MOD($A$3,1)*24,0)={6,10,23})*($C10=$J$3)-($J$4=$C10)),IF($C10=$J$3,($A$4-$A$3-0.0001)*24,0)),
    N("Không thỏa")+OR($J$3:$J$4=$C10)*SUMIF($J$3:$J$4,$C10,$K$3:$K$4)+LOOKUP($C10,{1,2,3},{4,13,7})*(LOOKUP($C10,{1;2;3},
    MMULT(N(IFERROR(MATCH(TRANSPOSE(ROW(INDIRECT(INT($A$3)&":"&INT($A$4)))+{6,10,23}/24),$A$3:$A$4,1)=1,)),ROW(INDIRECT("1:"&INT($A$4)-INT($A$3)+1))^0))-OR(ROUND(MOD($A$3,1)*24,0)={6,10,23})*($C10=$J$3)-($J$4=$C10)))
     

    Các file đính kèm:

    Lần chỉnh sửa cuối: 12 Tháng chín 2017
  3. dazkangel

    dazkangel New Horizons

    Làm đại, bạn nói chuyện thách đố thì sẽ có ai giúp bạn đây ?
     

    Các file đính kèm:

  4. meocon92

    meocon92 Thành viên mới

    Nếu đơn giản thế này thì chả phải đem đánh đố với giọng điệu cao ngạo vậy. Công thức thấy cũng khá,
    nhưng sai be bét như tương khi thay đổi giờ giấc, như vào lúc 1h30 sớm ngày 28, hoặc cách ngày nhau hàng
    chục hàng trăm ngày thì sao nhỉ? Đã làm thì suy nghĩ cho lung, nhìn trước xem sau, dò trên xét dưới, chứ
    làm đại thì cũng chẳng chứng tỏ được điều gì và đi đến đâu cả...
     
  5. dazkangel

    dazkangel New Horizons

    sao vừa khá vừa lại be bét, dữ liệu vào không ăn nhập nguyên tắc thì sai, tôi cũng chả cần lường trước trường hợp. Chưa kể tính theo từng ngày thế có ai đi làm 1 phát hơn 24 tiếng à.
     
  6. Hoang2013

    Hoang2013 Thành viên tích cực

    [BÀI NÀY CHỈ GIÀNH CHO CỘNG ĐỒNG; KHÔNG GIÀNH CHO NGƯỜI ĐĂNG BÀI]​
    Với hàm tự tạo này:
    PHP:
    Function Ca(Vào As DateRa As Date)
     Const 
    G10 As Double #10:00:00 AM#
     
    Const G23 As Double #11:00:00 PM#
     
    Dim Arr(1 To 11 To 3)
     
    Dim Gv As DoubleGr As Double
     Gv 
    Vào Int(Vào):           Gr Ra Int(Ra)
     If 
    Day(Ra) = Day(VàoThen
        
    If Gv G10 And Gr G10 Then
            Arr
    (11) = 24 * (Gr Gv)
        ElseIf 
    Gv G10 And Gr <= G23 Then
            Arr
    (11) = 24 * (G10 Gv)
            
    Arr(12) = 24 * (Gr G10)
        ElseIf 
    Gv G10 And Gr <= TimeSerial(2400Then
            Arr
    (11) = 24 * (G10 Gv)
            
    Arr(12) = 24 * (Gr G10)
            
    Arr(13) = 24 * (Gr G23)
        
    End If
     ElseIf 
    Day(Ra) = Day(Vào) + 1 Then
        
    'Se Viét Tiếp Néu Có Ai  Đó Iêu Càu'
     
    End If
     
    Ca Arr()
    End Function
    Ta sẽ thu kết quả ở 3 cột cuối của bảng dữ liệu sau:
    PHP:
    '
       Cột B               Cột C                Cột E    Cột F   Cột G'
       
    Vào                   Ra                 Sáng     Chiều    Tối
    29
    /08/2017 7:00   29/08/2017 9:00           2,00     0         0
    29
    /08/2017 9:00   29/08/2017 12:30          1,00     2,5       0
    29
    /08/2017 9:00   29/08/2017  23:30:00 CH   1,00     13,5     0,5
    29
    /08/2017 8:30   29/08/2017 12:30          1,50     2,5       0
     
  7. meocon92

    meocon92 Thành viên mới

    Vậy trong ngày theo ca đi nhá! vào 23h30 ngày 28, ra sớm 5h30 ngày 29, thêm nữa này vào 13h30 trưa 28,
    ra sớm 5h30 ngày 29.... còn cả tá trường hợp không làm đến nơi đến chốn thì gọi là vừa khá vừa be bét, cậu nhá!
     
  8. Hoang2013

    Hoang2013 Thành viên tích cực

    Chú í: Bộ Luật LĐ chỉ cho fép làm thêm giờ trong qui định;
    Chúng ta tuân thủ luật fáp thì không thể viết công thức/hàm để tính cho trường hợp fi fáp.
     
  9. VetMini

    VetMini Thành viên gạo cội

    Nếu "nhìn trước xem sau" thì có lẽ đã thấy bài này bắt buộc phải giải trên Excel 2003.
    Đem ra "dò trên xét dưới" sẽ thấy file xlsx có nhiều khả năng được cảnh báo "có hiểu chưa?"
     
    Hoang2013 thích bài này.
  10. Hoang2013

    Hoang2013 Thành viên tích cực

    May quá; Mình giải trong VBA nên chắc vô hại với các loại Version khác nhau!
    Dịp này xin nhắc lại: Bài giải của mình chỉ dành cho người nào đó trong cộng đồng này, cần thì tham khảo; Bài mình viết không cho những người có bài đăng ở topic này!
     
    PacificPR thích bài này.
  11. dazkangel

    dazkangel New Horizons

    Ừ thì tôi sai, mà nói thật bài này chả có cái quái gì cao siêu cả. Kết hợp vài hàm min max là ra thôi bạn à, thế cho nên tôi nhận sai rồi bạn đừng trả lời tôi nhé thân.
     
  12. Hoang2013

    Hoang2013 Thành viên tích cực

    Với 3 mốc thời gian trong ngày gồm #6:00:00#; #10:00:00# & #23:00:00# ta có các trường hợp sau đây:

    (*) Trong ngày
    Sáng vô lúc 1h0; Ra lúc <=6h00 (Vô trễ ca đêm)
    Sáng vô lúc 2h0; Ra lúc <=10h00 (2 ca: Đêm & sáng)
    Sáng vô lúc 5h00; Ra lúc <=23h00 (3 ca luôn)
    Sáng vô lúc 5h30; Ra lúc <=23h30 (Sai luật?)

    Sáng vô lúc (>=6h00 & < =10h00); Ra lúc <=10h00
    . . . . . .
    (*) Ngoài ngày . . . . .

    Fải kiên trì xem xét thôi.
     
  13. meocon92

    meocon92 Thành viên mới

    Làm để cho ngưòi gửi bài thấy diễn đàn đều có cách giải mọi câu hỏi, vì thế không nên cao ngạo thách đố,
    cách giải có rồi nhưng nhìn vào quay như chong chóng rối mù như tơ vò thì cũng hỏng xôi rách việc, chả dùng vào đâu.
    Bác khỏi phải kiên trì thêm nhọc thân cho trường hợp này.
     
    SA_DQ thích bài này.
  14. VetMini

    VetMini Thành viên gạo cội

    Làm công thức thì chưa biết, chứ làm code thì dùng cách chuẩn hoá 3 ca thành cùng ngày là dễ hơn hết.
    Sau khi chuẩn hoá, bạn sẽ có 0 - 7 = tối; 7 - 11 = sáng; 11 - 24 = chiều
    Con toán chuẩn hoá giờ vào giờ ra lúc này chỉ là con toán dịch toạ độ x: + 1 giờ

    Sau khi có ca chuẩn, giờ chuẩn rồi thì ta xét vài cái if-else là ra số giờ mỗi ca

    Nếu giờ ra là ngày hôm sau thì tách giờ ra làm 2 khoảng, một khoảng từ giờ vào đến 24 và khoảng kia từ 0 đến giờ ra

    Nếu giờ ra là vài ngày sau thì cứ trừ dần cho đến ngày chuẩn, cứ mỗi ngày trừ đi thì được 7 giờ tối + 4 giờ sáng + 13 giờ chiều

    Chú: có những hợp đồng buộc bạn phải tính liên tục giờ như thế để trả cho bên gia công. Việc thay ngừoi cho đúng luật Lao Động là của bên gia công. Nếu bên chủ thấy họ sử dụng lao động không đúng thì có thể cảnh báo, cắt hợp đồng, hoặc báo cho cơ quan chức năng.
     
    SA_DQ thích bài này.
  15. dazkangel

    dazkangel New Horizons

    Các bác xem dùm em:
    PHP:
    C8=MAX(0,MIN(4,MIN(10,ROUND(24*(MOD(C5,1)+(DAY(C5)>DAY(C4))),1))-ROUND(24*MOD(C4,1),1)))
    PHP:
    C9=MAX(0,MIN(13,MIN(23,ROUND(24*(MOD(C5,1)+(DAY(C5)>DAY(C4))),1))-ROUND(24*MOD(C4,1),1)))
    PHP:
    C10=MAX(0,MIN(7,MIN(30,ROUND(24*(MOD(C5,1)+(DAY(C5)>DAY(C4))),1))-ROUND(24*MOD(C4,1),1)))
     
  16. HYen17

    HYen17 Thành viên cằn cỗi.

    Cách này rất hay & thú vị'
    Vậy có fải dịch chuyển tọa độ của giờ vô hay giờ ra không vậy bạn?
    Nếu có thì dịch chuyện 6h00 về 0h00 chứ nhỉ.
     
    Chỉnh sửa lần cuối bởi điều hành viên: 15 Tháng chín 2017
  17. batman1

    batman1 Thành viên hoạt động

    1. Bạn phải có Excel >= 2007. Nếu bạn chỉ có <= 2003 thì ngừng đọc tại đây, không tải tập tin đính kèm. Không đọc tiếp, không hỏi, không đề nghị gì.
    2. Tôi giả thiết là không ai làm việc > 24 tiếng. Nếu nhân viên của bạn làm việc > 24 tiếng thì xin mời gõ cửa nhà khác.
    3. Bạn tự test. Tôi không muốn bỏ công lập dữ liệu dùng cho mục đích test.
    4. Nếu bạn nhập vào cột A nhiều giờ vào, vào cột B nhiều giờ ra, thì tôi sẽ viết đúng công thức cho C2, D2 và E2, chỉ việc kéo xuống. Còn như tập tin bạn đính kèm thì bây giờ tự bạn sửa và áp dụng cho dữ liệu của mình.
    5. Tôi tin nhưng không đảm bảo là công thức luôn đúng. Tự bạn tạo ra dữ liệu đa dạng, test rồi tự kết luận. Thực ra nhiều lắm thì giờ vào có thể chọn các khả năng: 5, 6, 8, 10, 16, 23, 23:59. Tương tự chọn vài khả năng giờ ra rồi ghép với giờ vào. Không có quá nhiều khả năng để thử.
     

    Các file đính kèm:

    SA_DQ thích bài này.
  18. songhovl201

    songhovl201 Thành viên tiêu biểu

    Câu 1. Mình xin nói thêm rằng: Với tình huống này máy 2003 vẫn có thể đọc excel >=2007 bằng cách làm việc trực tuyến. Mình đã từng làm trên Oulook.com.vn rồi. Thân chào bạn!
     
  19. ChanhTQ@

    ChanhTQ@ Thành viên già cỗi.

    Mình cho rằng với E2003 thì chuyển qua hàm người dùng chắc cũng được.
    Mình xin thử liệt kê các trường hợp & nhờ các bạn bổ sung cho đủ giúp!
    PHP:
    Vô 23h10 ra 23h50;  Vô 23h30 về 4h30;   Vô 1h00 vể 5h30;  Vô 23h30 về  9h30;  vô 1h30 về 9h50, . . .  

     
  20. batman1

    batman1 Thành viên hoạt động

    Tôi giải thích cho bạn là tại sao tôi thêm câu 1.
    Tôi xem
    http://www.giaiphapexcel.com/diendan/threads/giúp-mình-sửa-code-lọc-bị-lỗi-khí-số-lượng-ký-tự-nhiều.129500/#post-812529
    thì thấy chỉ do vụ Excel 2003 mà lời qua tiếng lại nhiều quá. Tôi không muốn lại nổ ra tranh luận ở đây nên tốt nhất cắt luôn
    Vì công thức của tôi dùng 1 hàm chỉ có với Excel >= 2007 mà.
     
    ChanhTQ@ thích bài này.

Chia sẻ trang này