Nhờ anh chị em kiểm tra giúp mình công thức trong file

[huongmai71]

E Chào anh chị,
Khi có dữ liệu chấm công thì CT nhảy đúng số liệu, khi ko có thì hiển thị chế độ #DIV/0!. Vì vậy khi tính tổng e ko tính được. Mong anh chị xem giúp e xem sai ở đâu với ạ

E cảm ơn

 

[Tran K]

Đặt điều kiện cho ô như sau (thí dụ): =if(A1=0,0,cong/A1), như vậy khi A1=0 thì ô đích =0, nếu không sẽ bị chia cho số 0 nên lỗi #DIV/0.

 

[tieuthubuongbinh]

Bạn cài iferror(CT của bạn,0) là được
Vd: =IFERROR(($AY9*($D9+$E9))+(($AY9*($F9+$G9+$H9)/$AS9))+((COUNTIF($I9:$AL9,"CT0.25A")*0.25+(COUNTIF($I9:$AL9,"CT0.5A")*0.5+(COUNTIF($I9:$AL9,"CT0.75A")*0.75+(COUNTIF($I9:$AL9,"CT1A")*1)+(COUNTIF($I9:$AL9,"CT1.5A")*1.5+(COUNTIF($I9:$AL9,"CT2A")*2))))))*50000,0)

 

[Phan Thế Hiệp]

E Chào anh chị,
Khi có dữ liệu chấm công thì CT nhảy đúng số liệu, khi ko có thì hiển thị chế độ #DIV/0!. Vì vậy khi tính tổng e ko tính được. Mong anh chị xem giúp e xem sai ở đâu với ạ

E cảm ơn

Thay vì bạn phải kiên nhẫn tạo công thức như:

AT8=+(COUNTIF($I8:$AL8,"CT0.25A")+COUNTIF($I8:$AL8,"CT0.25B")+COUNTIF($I8:$AL8,"CT0.25C")+COUNTIF($I8:$AL8,"CT0.25D")+COUNTIF($I8:$AL8,"CT0.25E")+COUNTIF($I8:$AL8,"CT0.25F")+COUNTIF($I8:$AL8,"CT0.25G")+COUNTIF($I8:$AL8,"CT0.25H")+COUNTIF($I8:$AL8,"CT0.25I")+COUNTIF($I8:$AL8,"CT0.25K")+COUNTIF($I8:$AL8,"CT0.25N")+COUNTIF($I8:$AL8,"CT0.25M")+COUNTIF($I8:$AL8,"CT0.25L")+COUNTIF($I8:$AL8,"CT0.25P")+COUNTIF($I8:$AL8,"CT0.25q")+COUNTIF($I8:$AL8,"CT0.25T")+COUNTIF($I8:$AL8,"CT0.25V")+COUNTIF($I8:$AL8,"CT0.25S")+COUNTIF($I8:$AL8,"CT0.25R")+COUNTIF($I8:$AL8,"CT0.25Z"))*0.25+(COUNTIF($I8:$AL8,"CT0.5A")+COUNTIF($I8:$AL8,"CT0.5B")+COUNTIF($I8:$AL8,"CT0.5C")+COUNTIF($I8:$AL8,"CT0.5D")+COUNTIF($I8:$AL8,"CT0.5E")+COUNTIF($I8:$AL8,"CT0.5F")+COUNTIF($I8:$AL8,"CT0.5G")+COUNTIF($I8:$AL8,"CT0.5H")+COUNTIF($I8:$AL8,"CT0.5I")+COUNTIF($I8:$AL8,"CT0.5K")+COUNTIF($I8:$AL8,"CT0.5N")+COUNTIF($I8:$AL8,"CT0.5M")+COUNTIF($I8:$AL8,"CT0.5L")+COUNTIF($I8:$AL8,"CT0.5P")+COUNTIF($I8:$AL8,"CT0.5Q")+COUNTIF($I8:$AL8,"CT0.5T")+COUNTIF($I8:$AL8,"CT0.5V")+COUNTIF($I8:$AL8,"CT0.5S")+COUNTIF($I8:$AL8,"CT0.5R")+COUNTIF($I8:$AL8,"CT0.5Z"))*0.5+(COUNTIF($I8:$AL8,"CT0.75A")+COUNTIF($I8:$AL8,"CT0.75B")+COUNTIF($I8:$AL8,"CT0.75C")+COUNTIF($I8:$AL8,"CT0.75D")+COUNTIF($I8:$AL8,"CT0.75E")+COUNTIF($I8:$AL8,"CT0.75F")+COUNTIF($I8:$AL8,"CT0.75G")+COUNTIF($I8:$AL8,"CT0.75H")+COUNTIF($I8:$AL8,"CT0.75I")+COUNTIF($I8:$AL8,"CT0.75K")+COUNTIF($I8:$AL8,"CT0.75N")+COUNTIF($I8:$AL8,"CT0.75M")+COUNTIF($I8:$AL8,"CT0.75L")+COUNTIF($I8:$AL8,"CT0.75P")+COUNTIF($I8:$AL8,"CT0.75q")+COUNTIF($I8:$AL8,"CT0.75T")+COUNTIF($I8:$AL8,"CT0.75V")+COUNTIF($I8:$AL8,"CT0.27S")+COUNTIF($I8:$AL8,"CT0.75R")+COUNTIF($I8:$AL8,"CT0.75Z"))*0.75+(COUNTIF($I8:$AL8,"CT1A")+COUNTIF($I8:$AL8,"CT1B")+COUNTIF($I8:$AL8,"CT1C")+COUNTIF($I8:$AL8,"CT1D")+COUNTIF($I8:$AL8,"CT1E")+COUNTIF($I8:$AL8,"CT1F")+COUNTIF($I8:$AL8,"CT1G")+COUNTIF($I8:$AL8,"CT1H")+COUNTIF($I8:$AL8,"CT1I")+COUNTIF($I8:$AL8,"CT1K")+COUNTIF($I8:$AL8,"CT1N")+COUNTIF($I8:$AL8,"CT1M")+COUNTIF($I8:$AL8,"CT1L")+COUNTIF($I8:$AL8,"CT1P")+COUNTIF($I8:$AL8,"CT1q")+COUNTIF($I8:$AL8,"CT1T")+COUNTIF($I8:$AL8,"CT1V")+COUNTIF($I8:$AL8,"CT1S")+COUNTIF($I8:$AL8,"CT1R")+COUNTIF($I8:$AL8,"CT1Z"))*1+(COUNTIF($I8:$AL8,"CT1.5A")+COUNTIF($I8:$AL8,"CT1.5B")+COUNTIF($I8:$AL8,"CT1.5C")+COUNTIF($I8:$AL8,"CT1.5D")+COUNTIF($I8:$AL8,"CT1.5E")+COUNTIF($I8:$AL8,"CT1.5F")+COUNTIF($I8:$AL8,"CT1.5G")+COUNTIF($I8:$AL8,"CT1.5H")+COUNTIF($I8:$AL8,"CT1.5I")+COUNTIF($I8:$AL8,"CT1.5K")+COUNTIF($I8:$AL8,"CT1.5N")+COUNTIF($I8:$AL8,"CT1.5M")+COUNTIF($I8:$AL8,"CT1.5L")+COUNTIF($I8:$AL8,"CT1.5P")+COUNTIF($I8:$AL8,"CT1.5Q")+COUNTIF($I8:$AL8,"CT1.5T")+COUNTIF($I8:$AL8,"CT1.5V")+COUNTIF($I8:$AL8,"C1.5S")+COUNTIF($I8:$AL8,"CT1.5R")+COUNTIF($I8:$AL8,"CT1.5Z"))*1.5+(COUNTIF($I8:$AL8,"CT2A")+COUNTIF($I8:$AL8,"CT2B")+COUNTIF($I8:$AL8,"CT2C")+COUNTIF($I8:$AL8,"CT2D")+COUNTIF($I8:$AL8,"CT2E")+COUNTIF($I8:$AL8,"CT2F")+COUNTIF($I8:$AL8,"CT2G")+COUNTIF($I8:$AL8,"CT2H")+COUNTIF($I8:$AL8,"CT2I")+COUNTIF($I8:$AL8,"CT2K")+COUNTIF($I8:$AL8,"CT2N")+COUNTIF($I8:$AL8,"CT2M")+COUNTIF($I8:$AL8,"CT2L")+COUNTIF($I8:$AL8,"CT2P")+COUNTIF($I8:$AL8,"CT2q")+COUNTIF($I8:$AL8,"CT2T")+COUNTIF($I8:$AL8,"CT2V")+COUNTIF($I8:$AL8,"CT2S")+COUNTIF($I8:$AL8,"CT2R")+COUNTIF($I8:$AL8,"CT2Z"))*2

Bạn có thể rút gọn như sau:

AT8=SUMPRODUCT(COUNTIF($I8:$AL8,"CT"&0.25*{1,2,3,4,6,8}&CHAR(ROW($65:$90)))*0.25*{1,2,3,4,6,8})

Enter, fill xuống.
Giải thích:

1. 0.25*{1,2,3,4,6,8}=> sẽ tạo ra 1 mảng {0.25, 0.5, 0.75, 1.0, 1.5, 2} là các hệ số nhân trong công thức của bạn
2. CHAR(ROW($65:$90)=> sẽ tạo ra 1 mảng chứa các ký tự "A"; "B"; "C";.....; "Z"
3. "CT"&0.25*{1,2,3,4,6,8}&CHAR(ROW($65:$90)=> sẽ tạo ra 1 mảng như sheet "MangKyTu" (xem file kèm)
4. COUNTIF($I8:$AL8, "MangKyTu" )=> sẽ đếm theo từng ký tự có trong "MangKyTu"
5. Dùng COUNTIF() nhân với hệ số tương ứng {0.25, 0.5, 0.75, 1.0, 1.5, 2}
6. Cuối cùng tổng cộng các kết quả trên bằng Sumproduct()

Bạn có thể tham khảo các công thức tôi đã điều chỉnh cho gọn tại các cột có tô màu xanh.

Thân

 

[HieuCD]

Thay vì bạn phải kiên nhẫn tạo công thức như:

AT8=+(COUNTIF($I8:$AL8,"CT0.25A")+COUNTIF($I8:$AL8,"CT0.25B")+COUNTIF($I8:$AL8,"CT0.25C")+COUNTIF($I8:$AL8,"CT0.25D")+COUNTIF($I8:$AL8,"CT0.25E")+COUNTIF($I8:$AL8,"CT0.25F")+COUNTIF($I8:$AL8,"CT0.25G")+COUNTIF($I8:$AL8,"CT0.25H")+COUNTIF($I8:$AL8,"CT0.25I")+COUNTIF($I8:$AL8,"CT0.25K")+COUNTIF($I8:$AL8,"CT0.25N")+COUNTIF($I8:$AL8,"CT0.25M")+COUNTIF($I8:$AL8,"CT0.25L")+COUNTIF($I8:$AL8,"CT0.25P")+COUNTIF($I8:$AL8,"CT0.25q")+COUNTIF($I8:$AL8,"CT0.25T")+COUNTIF($I8:$AL8,"CT0.25V")+COUNTIF($I8:$AL8,"CT0.25S")+COUNTIF($I8:$AL8,"CT0.25R")+COUNTIF($I8:$AL8,"CT0.25Z"))*0.25+(COUNTIF($I8:$AL8,"CT0.5A")+COUNTIF($I8:$AL8,"CT0.5B")+COUNTIF($I8:$AL8,"CT0.5C")+COUNTIF($I8:$AL8,"CT0.5D")+COUNTIF($I8:$AL8,"CT0.5E")+COUNTIF($I8:$AL8,"CT0.5F")+COUNTIF($I8:$AL8,"CT0.5G")+COUNTIF($I8:$AL8,"CT0.5H")+COUNTIF($I8:$AL8,"CT0.5I")+COUNTIF($I8:$AL8,"CT0.5K")+COUNTIF($I8:$AL8,"CT0.5N")+COUNTIF($I8:$AL8,"CT0.5M")+COUNTIF($I8:$AL8,"CT0.5L")+COUNTIF($I8:$AL8,"CT0.5P")+COUNTIF($I8:$AL8,"CT0.5Q")+COUNTIF($I8:$AL8,"CT0.5T")+COUNTIF($I8:$AL8,"CT0.5V")+COUNTIF($I8:$AL8,"CT0.5S")+COUNTIF($I8:$AL8,"CT0.5R")+COUNTIF($I8:$AL8,"CT0.5Z"))*0.5+(COUNTIF($I8:$AL8,"CT0.75A")+COUNTIF($I8:$AL8,"CT0.75B")+COUNTIF($I8:$AL8,"CT0.75C")+COUNTIF($I8:$AL8,"CT0.75D")+COUNTIF($I8:$AL8,"CT0.75E")+COUNTIF($I8:$AL8,"CT0.75F")+COUNTIF($I8:$AL8,"CT0.75G")+COUNTIF($I8:$AL8,"CT0.75H")+COUNTIF($I8:$AL8,"CT0.75I")+COUNTIF($I8:$AL8,"CT0.75K")+COUNTIF($I8:$AL8,"CT0.75N")+COUNTIF($I8:$AL8,"CT0.75M")+COUNTIF($I8:$AL8,"CT0.75L")+COUNTIF($I8:$AL8,"CT0.75P")+COUNTIF($I8:$AL8,"CT0.75q")+COUNTIF($I8:$AL8,"CT0.75T")+COUNTIF($I8:$AL8,"CT0.75V")+COUNTIF($I8:$AL8,"CT0.27S")+COUNTIF($I8:$AL8,"CT0.75R")+COUNTIF($I8:$AL8,"CT0.75Z"))*0.75+(COUNTIF($I8:$AL8,"CT1A")+COUNTIF($I8:$AL8,"CT1B")+COUNTIF($I8:$AL8,"CT1C")+COUNTIF($I8:$AL8,"CT1D")+COUNTIF($I8:$AL8,"CT1E")+COUNTIF($I8:$AL8,"CT1F")+COUNTIF($I8:$AL8,"CT1G")+COUNTIF($I8:$AL8,"CT1H")+COUNTIF($I8:$AL8,"CT1I")+COUNTIF($I8:$AL8,"CT1K")+COUNTIF($I8:$AL8,"CT1N")+COUNTIF($I8:$AL8,"CT1M")+COUNTIF($I8:$AL8,"CT1L")+COUNTIF($I8:$AL8,"CT1P")+COUNTIF($I8:$AL8,"CT1q")+COUNTIF($I8:$AL8,"CT1T")+COUNTIF($I8:$AL8,"CT1V")+COUNTIF($I8:$AL8,"CT1S")+COUNTIF($I8:$AL8,"CT1R")+COUNTIF($I8:$AL8,"CT1Z"))*1+(COUNTIF($I8:$AL8,"CT1.5A")+COUNTIF($I8:$AL8,"CT1.5B")+COUNTIF($I8:$AL8,"CT1.5C")+COUNTIF($I8:$AL8,"CT1.5D")+COUNTIF($I8:$AL8,"CT1.5E")+COUNTIF($I8:$AL8,"CT1.5F")+COUNTIF($I8:$AL8,"CT1.5G")+COUNTIF($I8:$AL8,"CT1.5H")+COUNTIF($I8:$AL8,"CT1.5I")+COUNTIF($I8:$AL8,"CT1.5K")+COUNTIF($I8:$AL8,"CT1.5N")+COUNTIF($I8:$AL8,"CT1.5M")+COUNTIF($I8:$AL8,"CT1.5L")+COUNTIF($I8:$AL8,"CT1.5P")+COUNTIF($I8:$AL8,"CT1.5Q")+COUNTIF($I8:$AL8,"CT1.5T")+COUNTIF($I8:$AL8,"CT1.5V")+COUNTIF($I8:$AL8,"C1.5S")+COUNTIF($I8:$AL8,"CT1.5R")+COUNTIF($I8:$AL8,"CT1.5Z"))*1.5+(COUNTIF($I8:$AL8,"CT2A")+COUNTIF($I8:$AL8,"CT2B")+COUNTIF($I8:$AL8,"CT2C")+COUNTIF($I8:$AL8,"CT2D")+COUNTIF($I8:$AL8,"CT2E")+COUNTIF($I8:$AL8,"CT2F")+COUNTIF($I8:$AL8,"CT2G")+COUNTIF($I8:$AL8,"CT2H")+COUNTIF($I8:$AL8,"CT2I")+COUNTIF($I8:$AL8,"CT2K")+COUNTIF($I8:$AL8,"CT2N")+COUNTIF($I8:$AL8,"CT2M")+COUNTIF($I8:$AL8,"CT2L")+COUNTIF($I8:$AL8,"CT2P")+COUNTIF($I8:$AL8,"CT2q")+COUNTIF($I8:$AL8,"CT2T")+COUNTIF($I8:$AL8,"CT2V")+COUNTIF($I8:$AL8,"CT2S")+COUNTIF($I8:$AL8,"CT2R")+COUNTIF($I8:$AL8,"CT2Z"))*2

Bạn có thể rút gọn như sau:

AT8=SUMPRODUCT(COUNTIF($I8:$AL8,"CT"&0.25*{1,2,3,4,6,8}&CHAR(ROW($65:$90)))*0.25*{1,2,3,4,6,8})

Enter, fill xuống.
Giải thích:

1. 0.25*{1,2,3,4,6,8}=> sẽ tạo ra 1 mảng {0.25, 0.5, 0.75, 1.0, 1.5, 2} là các hệ số nhân trong công thức của bạn
2. CHAR(ROW($65:$90)=> sẽ tạo ra 1 mảng chứa các ký tự "A"; "B"; "C";.....; "Z"
3. "CT"&0.25*{1,2,3,4,6,8}&CHAR(ROW($65:$90)=> sẽ tạo ra 1 mảng như sheet "MangKyTu" (xem file kèm)
4. COUNTIF($I8:$AL8, "MangKyTu" )=> sẽ đếm theo từng ký tự có trong "MangKyTu"
5. Dùng COUNTIF() nhân với hệ số tương ứng {0.25, 0.5, 0.75, 1.0, 1.5, 2}
6. Cuối cùng tổng cộng các kết quả trên bằng Sumproduct()

Bạn có thể tham khảo các công thức tôi đã điều chỉnh cho gọn tại các cột có tô màu xanh.

Thân

Sao không dùng {...}&"*"

 

[Phan Thế Hiệp]

Sao không dùng {...}&"*"

Do bám theo chủ đề, lòi ra cái dại!
Cảm ơn anh.

Chúc anh ngày vui.

Mong anh chị xem giúp e xem sai ở đâu với ạ

Điều chỉnh lại:

AT8=SUM(COUNTIF($I8:$AL8,"CT"&0.25*{1,2,3,4,6,8}&"*")*0.25*{1,2,3,4,6,8})

Thân