Giúp hoàn thiện bảng BCC (2 người xem)

Liên hệ QC

Người dùng đang xem chủ đề này

Đang trực tuyến: 1 (Thành viên: 0, Khách: 1)
T

tueyennhi

Thành viên tích cực
Tham gia
18/10/10
Bài viết
1,192
Được thích
105
Chào anh chị!

Em gửi lên diễn đàn bản chấm công em đang xây dựng theo sự hướng dẫn của rất nhiều anh chị em trên này (nhưng chưa hoàn thiện hết). Hiện tại BCC còn sử dụng cả công thức nên chậm. Mong anh chị đưa ra phương án xử lý cải thiện BCC này tốt hơn. (theo hướng VBA cũng được hoặc dùng hàm nào đó tối ưu hơn cũng được).
Để tổng hợp công từ tất cả các sheet khác bấm vào biểu tượng bàn tay.

Hiện tại các công thức em đang sử dụng trong sheet BCC như sau:
+ Thử việc ngày: =+SUMIFS(K8:ED8,$K$6:$ED$6,"<="&G8,$K$7:$ED$7,"WD")
Căn cứ vào thời gian hết HĐ thử việc ở cột ED ta sẽ tính tổng các cột có tiêu đề là WD

+ Thử việc đêm: =SUMPRODUCT(($K$6:$ED$6<=G8)*IFERROR(IF(K8:ED8="D",1,--SUBSTITUTE($K$8:$ED$8,"D","")),0)*($K$7:$ED$7="WD")*ISTEXT(K8:ED8))
Căn cứ vào thời gian hết HĐ thử việc ở cột ED ta sẽ tính tổng các giá trị D (có thể là 0.1D hoặc 0.5D hoặc D) ở các cột có tiêu đề là WD.

Tương tự với Công ngày chính thức và công đêm chính thức căn cứ vào thời gian hết thử việc.

+Đối với các cột nghỉ từ EK đến EV và:
Ví dụ nghỉ Holiday: =+COUNTIFS($K8:$ED8,"LT",$K$7:$ED$7,"WD")+SUMIF(N!$I$5:$I$1000,BCC!$B8,N!$N$5:$N$1000)
Đếm các ô có ký hiệu LT ở các cột có tiêu đề WD + tổng có điều kiện trong sheet N
Các ngày nghỉ khác tương tự.

+ Tính Day OVT và Night OVT:
+) OVT ngày thường =+SUMIFS($K8:$ED8,$K$7:$ED$7,"ON",$K$5:$ED$5,"<>1",$K$5:$ED$5,"<>LT")
Tính tổng từ K8 đến ED8 đối với các cột có tiêu đề ON và xét $K$5:$ED$5 giá trị khác 1 (loại bỏ ON ngày chủ nhật) và xét $K$5:$ED$5 giá trị khác LT (loại bỏ ON ngày lễ tết)

Tương tự với các OVT khác.

Công việc khá là rối răm phức tạp em mong anh chị giúp đỡ.

Em cảm ơn ạ!
 

File đính kèm

Sắp xếp theo thời gian sắp xếp theo bầu chọn
Thêm 2 yêu cầu 3, 4
PHP: 
Public Sub Cong()
Dim sArr(),  tArr(), I As Long, J As Long, R As Long, Vao As Double, Ra As Double, Tem
tArr = Sheets("Form").Range("AZ1:BB6").Value
sArr = Range("F9", Range("F1000000").End(xlUp)).Resize(, 40).Value
R = UBound(sArr): ReDim dArr(1 To R, 1 To 3)
For I = 1 To R
    If sArr(I, 3) > 0 Then
        '----------------------------------------1 Loai cong'
        For J = 1 To 6
            If tArr(J, 1) = sArr(I, 1) Then
                Vao = Application.Max(sArr(I, 3), tArr(J, 2))
                Ra = Application.Min(sArr(I, 4), tArr(J, 3))
                Exit For
            End If
        Next J
        sArr(I, 10) = IIf(sArr(I, 6) = "", Vao, sArr(I, 6))
        sArr(I, 11) = IIf(sArr(I, 7) = "", Ra, sArr(I, 7))
        sArr(I, 12) = (sArr(I, 11) - sArr(I, 10) + IIf(sArr(I, 11) < sArr(I, 10), 1, 0)) * 24
        '---------------------------------- An 1'
        Tem = Application.WorksheetFunction.Round((sArr(I, 14) - sArr(I, 13)) * 24, 2)
        sArr(I, 15) = IIf(Tem = 0.5, 1, Tem)
        '----------------------------------An 2'
        sArr(I, 18) = (sArr(I, 17) - sArr(I, 16)) * 24
        '----------------------------------Tg còn lai'
        sArr(I, 19) = sArr(I, 12) - sArr(I, 15) - sArr(I, 18)
        '----------------------------------Che do'
        sArr(I, 20) = IIf(sArr(I, 8) = "S", 1, 0)
    End If
Next I
Range("F9").Resize(R, 40) = sArr
End Sub
 
Upvote 0
Ba Tê đã viết:
Thêm 2 yêu cầu 3, 4
PHP: 
Public Sub Cong()
Dim sArr(),  tArr(), I As Long, J As Long, R As Long, Vao As Double, Ra As Double, Tem
tArr = Sheets("Form").Range("AZ1:BB6").Value
sArr = Range("F9", Range("F1000000").End(xlUp)).Resize(, 40).Value
R = UBound(sArr): ReDim dArr(1 To R, 1 To 3)
For I = 1 To R
    If sArr(I, 3) > 0 Then
        '----------------------------------------1 Loai cong'
        For J = 1 To 6
            If tArr(J, 1) = sArr(I, 1) Then
                Vao = Application.Max(sArr(I, 3), tArr(J, 2))
                Ra = Application.Min(sArr(I, 4), tArr(J, 3))
                Exit For
            End If
        Next J
        sArr(I, 10) = IIf(sArr(I, 6) = "", Vao, sArr(I, 6))
        sArr(I, 11) = IIf(sArr(I, 7) = "", Ra, sArr(I, 7))
        sArr(I, 12) = (sArr(I, 11) - sArr(I, 10) + IIf(sArr(I, 11) < sArr(I, 10), 1, 0)) * 24
        '---------------------------------- An 1'
        Tem = Application.WorksheetFunction.Round((sArr(I, 14) - sArr(I, 13)) * 24, 2)
        sArr(I, 15) = IIf(Tem = 0.5, 1, Tem)
        '----------------------------------An 2'
        sArr(I, 18) = (sArr(I, 17) - sArr(I, 16)) * 24
        '----------------------------------Tg còn lai'
        sArr(I, 19) = sArr(I, 12) - sArr(I, 15) - sArr(I, 18)
        '----------------------------------Che do'
        sArr(I, 20) = IIf(sArr(I, 8) = "S", 1, 0)
    End If
Next I
Range("F9").Resize(R, 40) = sArr
End Sub
Nhấp chuột vào đây để mở rộng...

Cách viết này hay quá, sArr nào không bị biến đổi thì vẫn giữ nguyên. Nếu chuyển thành mảng dArr thì không linh động như vậy được :)
 
Upvote 0
tueyennhi đã viết:
Anh ơi em giải thích lại ở bài #37 rồi anh nhé. Anh xem lại giúp em.
Nhấp chuột vào đây để mở rộng...
Hiểu thêm được số 5:
PHP: 
Public Sub Cong()
Dim sArr(), tArr(), I As Long, J As Long, R As Long, Vao As Double, Ra As Double, Tem
tArr = Sheets("Form").Range("AZ1:BB6").Value
sArr = Range("F9", Range("F1000000").End(xlUp)).Resize(, 40).Value
R = UBound(sArr): ReDim dArr(1 To R, 1 To 3)
For I = 1 To R
    If sArr(I, 3) > 0 Then
        '----------------------------------------1 Loai cong'
        For J = 1 To 6
            If tArr(J, 1) = sArr(I, 1) Then
                Vao = Application.Max(sArr(I, 3), tArr(J, 2))
                Ra = Application.Min(sArr(I, 4), tArr(J, 3))
                Exit For
            End If
        Next J
        sArr(I, 10) = IIf(sArr(I, 6) = "", Vao, sArr(I, 6))
        sArr(I, 11) = IIf(sArr(I, 7) = "", Ra, sArr(I, 7))
        sArr(I, 12) = (sArr(I, 11) - sArr(I, 10) + IIf(sArr(I, 11) < sArr(I, 10), 1, 0)) * 24
        '---------------------------------- An 1'
        Tem = Application.WorksheetFunction.Round((sArr(I, 14) - sArr(I, 13)) * 24, 2)
        sArr(I, 15) = IIf(Tem = 0.5, 1, Tem)
        '----------------------------------An 2'
        sArr(I, 18) = (sArr(I, 17) - sArr(I, 16)) * 24
        '----------------------------------Tg còn lai'
        sArr(I, 19) = sArr(I, 12) - sArr(I, 15) - sArr(I, 18)
        '----------------------------------Che do'
        sArr(I, 20) = IIf(sArr(I, 8) = "S", 1, 0)
        '----------------------------------Tong TG'
        If sArr(I, 1) = "N" Or sArr(I, 1) = "H" Then
            sArr(I, 21) = sArr(I, 12) - sar(I, 15)
        Else
            sArr(I, 21) = sArr(I, 12)
        End If
        '?????????????????????????'
       
    End If
Next I
Range("F9").Resize(R, 40) = sArr
End Sub
Mã: 
6. Tính công ngày (cột AA) và công đêm (Cột AB)
- Tùy từng ca làm việc mà công được tính ở khoảng thời gian khác nhau:
+ Ca ngày, hành chính giờ công được tính trong khoảng từ 8h đến 17h trừ đi giờ ăn 1. Khung giờ làm phát sinh mà ngoài khoảng trên thì không phải là công mà sẽ quy thành giờ làm thêm.
+ Ca 1, giờ công được tính trong khoảng từ 6h đến 14h. Ngoài khoảng này sẽ là giờ làm thêm.
+ Ca 2, giờ công được tính trong khoảng từ 14h đến 22h. Ngoài khoảng này sẽ là giờ làm thêm.
+ Ca 1, giờ công được tính trong khoảng từ 22h đến 30h (6h sáng hôm sau). Ngoài khoảng này sẽ là giờ làm thêm.
Ca nào ghi vào cột nào? 2 dòng ca 1 làm theo dòng nào?
 
Upvote 0
Ba Tê đã viết:
Hiểu thêm được số 5:
PHP: 
Public Sub Cong()
Dim sArr(), tArr(), I As Long, J As Long, R As Long, Vao As Double, Ra As Double, Tem
tArr = Sheets("Form").Range("AZ1:BB6").Value
sArr = Range("F9", Range("F1000000").End(xlUp)).Resize(, 40).Value
R = UBound(sArr): ReDim dArr(1 To R, 1 To 3)
For I = 1 To R
    If sArr(I, 3) > 0 Then
        '----------------------------------------1 Loai cong'
        For J = 1 To 6
            If tArr(J, 1) = sArr(I, 1) Then
                Vao = Application.Max(sArr(I, 3), tArr(J, 2))
                Ra = Application.Min(sArr(I, 4), tArr(J, 3))
                Exit For
            End If
        Next J
        sArr(I, 10) = IIf(sArr(I, 6) = "", Vao, sArr(I, 6))
        sArr(I, 11) = IIf(sArr(I, 7) = "", Ra, sArr(I, 7))
        sArr(I, 12) = (sArr(I, 11) - sArr(I, 10) + IIf(sArr(I, 11) < sArr(I, 10), 1, 0)) * 24
        '---------------------------------- An 1'
        Tem = Application.WorksheetFunction.Round((sArr(I, 14) - sArr(I, 13)) * 24, 2)
        sArr(I, 15) = IIf(Tem = 0.5, 1, Tem)
        '----------------------------------An 2'
        sArr(I, 18) = (sArr(I, 17) - sArr(I, 16)) * 24
        '----------------------------------Tg còn lai'
        sArr(I, 19) = sArr(I, 12) - sArr(I, 15) - sArr(I, 18)
        '----------------------------------Che do'
        sArr(I, 20) = IIf(sArr(I, 8) = "S", 1, 0)
        '----------------------------------Tong TG'
        If sArr(I, 1) = "N" Or sArr(I, 1) = "H" Then
            sArr(I, 21) = sArr(I, 12) - sar(I, 15)
        Else
            sArr(I, 21) = sArr(I, 12)
        End If
        '?????????????????????????'
    
    End If
Next I
Range("F9").Resize(R, 40) = sArr
End Sub

Ca nào ghi vào cột nào? 2 dòng ca 1 làm theo dòng nào?
Đừng nói tui "ngu", vì tôi không phải cùng ngành với bạn mà hiểu cái cột nào để ghi cái chuyện gì.

Mã: 
6. Tính công ngày (cột AA) và công đêm (Cột AB)
- Tùy từng ca làm việc mà công được tính ở khoảng thời gian khác nhau:
+ Ca ngày, hành chính giờ công được tính trong khoảng từ 8h đến 17h trừ đi giờ ăn 1. Khung giờ làm phát sinh mà ngoài khoảng trên thì không phải là công mà sẽ quy thành giờ làm thêm.
+ Ca 1, giờ công được tính trong khoảng từ 6h đến 14h. Ngoài khoảng này sẽ là giờ làm thêm.
+ Ca 2, giờ công được tính trong khoảng từ 14h đến 22h. Ngoài khoảng này sẽ là giờ làm thêm.
+ Ca 1, giờ công được tính trong khoảng từ 22h đến 30h (6h sáng hôm sau). Ngoài khoảng này sẽ là giờ làm thêm.
Nhấp chuột vào đây để mở rộng...
 
Upvote 0
6. Tính công ngày (cột AA) và công đêm (Cột AB) căn cứ vào giờ ra vào ở cột O và P, ca ở cột F
- Tùy từng ca làm việc mà công được tính ở khoảng thời gian khác nhau:
+ Ca ngày, hành chính giờ công được tính trong khoảng từ 8h đến 17h trừ đi giờ ăn 1. Khung giờ làm phát sinh mà ngoài khoảng trên thì không phải là công mà sẽ quy thành giờ làm thêm.
+ Ca 1, giờ công được tính trong khoảng từ 6h đến 14h. Ngoài khoảng này sẽ là giờ làm thêm.
+ Ca 2, giờ công được tính trong khoảng từ 14h đến 22h. Ngoài khoảng này sẽ là giờ làm thêm.
+ Ca 1, giờ công được tính trong khoảng từ 22h đến 30h (6h sáng hôm sau). Ngoài khoảng này sẽ là giờ làm thêm.

Hì sao anh nói vậy :). Vì viết nhiều quá em không để ý tên cột tính và dữ liệu căn cứ để tính. Chưa kịp sửa thì anh đã nhắc rồi :)
 
Lần chỉnh sửa cuối:
Upvote 0
6. Tính công ngày (cột AA) và công đêm (Cột AB) căn cứ vào giờ ra vào ở cột O và P, ca ở cột F
- Tùy từng ca làm việc mà công được tính ở khoảng thời gian khác nhau:
+ Ca ngày, hành chính giờ công được tính trong khoảng từ 8h đến 17h trừ đi giờ ăn 1. Khung giờ làm phát sinh mà ngoài khoảng trên thì không phải là công mà sẽ quy thành giờ làm thêm. (Cái này hiểu là ghi vào cột AA)
+ Ca 1, giờ công được tính trong khoảng từ 6h đến 14h. Ngoài khoảng này sẽ là giờ làm thêm. (Ghi vào cột nào)
+ Ca 2, giờ công được tính trong khoảng từ 14h đến 22h. Ngoài khoảng này sẽ là giờ làm thêm.(Ghi vào cột nào)
+ Ca 1, giờ công được tính trong khoảng từ 22h đến 30h (6h sáng hôm sau). Ngoài khoảng này sẽ là giờ làm thêm. (Ca 1 đã có bên trên, bây giờ ca 1 nữa là sao?)
Từ đầu và trong code đều dùng ca N,D,X,Z.... ghi ngày đêm, ca 1, ca2 làm gì cho rắc rối?
 
Upvote 0
Ba Tê đã viết:
6. Tính công ngày (cột AA) và công đêm (Cột AB) căn cứ vào giờ ra vào ở cột O và P, ca ở cột F
- Tùy từng ca làm việc mà công được tính ở khoảng thời gian khác nh...
Từ đầu và trong code đều dùng ca N,D,X,Z.... ghi ngày đêm, ca 1, ca2 làm gì cho rắc rối?
Nhấp chuột vào đây để mở rộng...

- Tính công ngày (cho các ca N, H, X, Y) rồi ghi vào cột AA
- Tính công đêm (cho các ca D, Z) rồi ghi vào cột AB

+ Ca N, ca H giờ công được tính trong khoảng từ 8h đến 17h trừ đi giờ ăn 1. Khung giờ làm phát sinh mà ngoài khoảng trên thì không phải là công mà sẽ quy thành giờ làm thêm.
+ Ca X, giờ công được tính trong khoảng từ 6h đến 14h. Ngoài khoảng này sẽ là giờ làm thêm.
+ Ca Y, giờ công được tính trong khoảng từ 14h đến 22h. Ngoài khoảng này sẽ là giờ làm thêm.

+ Ca Z, ca D giờ công được tính trong khoảng từ 22h đến 30h (6h sáng hôm sau). Ngoài khoảng này sẽ là giờ làm thêm.

(Em nhầm ca 1 ở dưới là ca 3)
 
Upvote 0
tueyennhi đã viết:
- Tính công ngày (cho các ca N, H, X, Y) rồi ghi vào cột AA
- Tính công đêm (cho các ca D, Z) rồi ghi vào cột AB

+ Ca N, ca H giờ công được tính trong khoảng từ 8h đến 17h trừ đi giờ ăn 1. Khung giờ làm phát sinh mà ngoài khoảng trên thì không phải là công mà sẽ quy thành giờ làm thêm.
+ Ca X, giờ công được tính trong khoảng từ 6h đến 14h. Ngoài khoảng này sẽ là giờ làm thêm.
+ Ca Y, giờ công được tính trong khoảng từ 14h đến 22h. Ngoài khoảng này sẽ là giờ làm thêm.

+ Ca Z, ca D giờ công được tính trong khoảng từ 22h đến 30h (6h sáng hôm sau). Ngoài khoảng này sẽ là giờ làm thêm.

(Em nhầm ca 1 ở dưới là ca 3)
Nhấp chuột vào đây để mở rộng...

(Em nhầm ca 1 ở dưới là ca 3)
Bài #46 tôi đã thắc mắc 2 dòng ca 1, bài #48 bạn vẫn copy "y chang" bài #46 để hỏi, không chịu đọc hiểu thắc mắc của người khác.
Khi thấy điều kiện rõ ràng, có thể làm được thì tính, gặp bài "lu bu" quá thì "chạy"
Tôi không tham gia Topic này nữa, bạn nhờ người khác đi.
 
Upvote 0
Anh Bate
Ba Tê đã viết:
(Em nhầm ca 1 ở dưới là ca 3)
Bài #46 tôi đã thắc mắc 2 dòng ca 1, bài #48 bạn vẫn copy "y chang" bài #46 để hỏi, không chịu đọc hiểu thắc mắc của người khác.
Khi thấy điều kiện rõ ràng, có thể làm được thì tính, gặp bài "lu bu" quá thì "chạy"
Tôi không tham gia Topic này nữa, bạn nhờ người khác đi.
Nhấp chuột vào đây để mở rộng...

Vâng, em xin lỗi. Sáng dậy mắt nhắm mắt mở trông con nên em không đọc kĩ. Cảm ơn anh!
 
Upvote 0
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

B
Xin hướng dẫn cách để hàm Xlookup tự hiển thị theo số dòng có chứa điều kiện tìm kiếm ko dùng VBA (4 người xem)
Trả lời
4
Đọc
54
thuytrangbp28
T
S
NHỜ ANH EM GIÚP MÌNH TÌM KIẾM DỮ LIỆU (4 người xem)
Trả lời
12
Đọc
122
sangdennh
S
H
chuyển dữ liệu từ 1 dòng thành nhiều dòng, (3 người xem)
Trả lời
9
Đọc
58
hanhnguyen12022021
H
DinhManh1307
Cách thống kê công việc sản xuất từng người để tính lương sản phẩm (3 người xem)
Trả lời
14
Đọc
212
DinhManh1307
DinhManh1307
T
tìm ra số tháng mà lượng hàng nhập ban đầu sử dụng hết
Trả lời
1
Đọc
49
bebo021999
B
V
Help: Hàm đếm lọc giá trị trùng
Trả lời
12
Đọc
75
DeTong
D
C
Filter có điều kiện đang ở dạng Cột (1 người xem)
Trả lời
2
Đọc
55
thuytrangbp28
T
L
Có ai biết fix lỗi này không ạ
    • Buồn
Trả lời
2
Đọc
48
lekimhuy0812
L
tonny jj
Lập hàm thể hiện số công lệnh của ngày đi công tác
Trả lời
5
Đọc
170
Gà Công Nghệ
Gà Công Nghệ
GPE-Trợ Lý
[GPE] TALKSHOW KHÁM PHÁ SỨC MẠNH HÀM FILTER TRONG EXCEL 365
    • Thích
Trả lời
1
Đọc
3K
PhanTuHuong
PhanTuHuong
K
Lỗi cấu trúc hình ảnh khi mở file bằng điện thoại
Trả lời
0
Đọc
37
Kecodonlalala
K
N
  • Question Question
Nhờ mọi người góp ý cho file Quản lý bán hàng viết bằng VBA đầu tay của mình
    • Cảm ơn
    • Thích
Trả lời
7
Đọc
163
newbiexo
N
H
Xin được trợ giúp lỗi máy không mở được cửa sổ VBE.
Trả lời
3
Đọc
67
Nguyễn Duy Tuân
Nguyễn Duy Tuân
HeSanbi
  • Question Question
Sự khác nhau giữa book.Worksheets và book.Sheets trong Application
    • Thích
Trả lời
2
Đọc
145
Nguyễn Duy Tuân
Nguyễn Duy Tuân
PhanTuHuong
Hà Nội hẹn hò gặp gỡ đầu năm 2025 thôi :)
    • Thích
    • Yêu thích
Trả lời
7
Đọc
333
PhanTuHuong
PhanTuHuong
yeudoi
Cà phê giao lưu cùng thành viên kỳ cựu Cao Mạnh Sơn 19h 23/2/2025 (1 người xem)
    • Thích
    • Yêu thích
Trả lời
1
Đọc
75
PhanTuHuong
PhanTuHuong
K
Làm sao để ô excel hiển thị tên nhưng khi nhấp vào thì đó là công thức
Trả lời
1
Đọc
53
pycckuu410
P
Nguyễn Duy Tuân
Phiên bản mới Add-in A-Tools v10 (2024)
    • Thích
2 3
Trả lời
41
Đọc
3K
Nguyễn Duy Tuân
Nguyễn Duy Tuân
ongke0711
  • Question Question
Tải hóa đơn điện tử (https://hoadondientu.gdt.gov.vn/) Excel Vba (1 người xem)
    • Thích
    • Yêu thích
    • Cảm ơn
15 16 17
Trả lời
326
Đọc
25K
pycckuu410
P
V
  • Question Question
Đếm xếp loại của đơn hàng
Trả lời
8
Đọc
240
vanlinh_2904
V
Xem thêm…

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Đếm ngược thời gian

000
Ngày
00
Giờ
00
phút
00
giây
TALKSHOW KHÁM PHÁ SỨC MẠNH HÀM FILTER TRONG EXCEL 365
Back
Top Bottom