Tìm hàm/ công thức phù hợp với bảng tính

nms_qa

Con gà hơi già
Tham gia ngày
1 Tháng tư 2013
Bài viết
13
Được thích
1
Điểm
365
Dear các anh chị,

Em có bảng chấm công với dữ liệu đầu vào là các sheet 1,2,3... tương ứng với các ngày trong tháng. (file đính kèm)

+ FT: làm việc 8h/ công

+ PT: làm việc 5.5h/ công

+ 1 mã nhân viên có thể làm nhiều hơn 1 công/ ngày

+ Giờ làm việc sẽ =8 hoặc 5.5 - Đi muộn - Về sớm

Yêu cầu:

+ Lấy ra ký hiệu chấm công dạng S+0.25, C+ 0.5, D1, S + C... (Đủ công thì thể hiện ký hiệu chấm công, thiếu thì thể hiện số giờ)

+ Đếm số công đủ (Count S,C,D...) của mỗi nhân viên

+ Tổng số công thiếu (Sum 0.25, 0,75,...) của mỗi nhân viên

+ Đếm số công thiếu nhưng >0.5 công (Count 0.55,0.6, 0.7...) của mỗi nhân viên

Bạn cũ nghỉ ngang, em mới nhận bàn giao và nghiên cứu mãi nhưng chưa ra giải pháp, rất mong được các anh chị ra tay tương trợ chứ em làm thủ công oải quá.

Em cảm ơn nhiều ạ.
 

File đính kèm

nms_qa

Con gà hơi già
Tham gia ngày
1 Tháng tư 2013
Bài viết
13
Được thích
1
Điểm
365
Chào anh chị,

Như yêu cầu ở post #1 em đã giải quyết được 1 phần, hiện giờ em đang vướng ở công thức tính tổng tổng/đếm các giá trị là số trong chuỗi "văn bản + số" như file đính kèm ở ô AI & AJ (Sheet BCC)

Rất mong được các anh chị hướng dẫn,

Em cảm ơn.
 

File đính kèm

hocexcel_1991

Bắt đầu học Excel
Tham gia ngày
9 Tháng bảy 2018
Bài viết
467
Được thích
856
Điểm
335
Nơi ở
Từ Sơn, Bắc Ninh
Chào anh chị,

Như yêu cầu ở post #1 em đã giải quyết được 1 phần, hiện giờ em đang vướng ở công thức tính tổng tổng/đếm các giá trị là số trong chuỗi "văn bản + số" như file đính kèm ở ô AI & AJ (Sheet BCC)

Rất mong được các anh chị hướng dẫn,

Em cảm ơn.
Dùng công thức này xem đúng kết quả không
Mã:
AI4=SUM(IFERROR(--TRIM(MID(SUBSTITUTE(C4:AG4,"+",REPT(" ",10)),{1;2}*10-9,10)),))
AJ4=COUNT(1/TEXT(IFERROR(--TRIM(MID(SUBSTITUTE(C4:AG4,"+",REPT(" ",10)),{1;2}*10-9,10)),),"[<0.5];\0"))
nhấn Ctrl+Shirt+Enter
 

nms_qa

Con gà hơi già
Tham gia ngày
1 Tháng tư 2013
Bài viết
13
Được thích
1
Điểm
365
Dùng công thức này xem đúng kết quả không
Mã:
AI4=SUM(IFERROR(--TRIM(MID(SUBSTITUTE(C4:AG4,"+",REPT(" ",10)),{1;2}*10-9,10)),))
AJ4=COUNT(1/TEXT(IFERROR(--TRIM(MID(SUBSTITUTE(C4:AG4,"+",REPT(" ",10)),{1;2}*10-9,10)),),"[<0.5];\0"))
nhấn Ctrl+Shirt+Enter
Cảm ơn bạn nhé, mình thử với công thức của bạn thấy đúng vơi yêu cầu và chưa phát sinh gì. Chỉ nhầm xíu ở phần Count công >0.5 chứ không phải < 0.5 :p

Nếu có thời gian phiền bạn giúp mình diễn giải công thức trên để mình có thể học hỏi và áp dụng trong những trường hợp tương tự.

Mình cảm ơn nhiều!
 

xuongrongdat

Có bao giờ bạn tự hỏi "Tôi là ai?"
Tham gia ngày
30 Tháng sáu 2008
Bài viết
356
Được thích
248
Điểm
710
Nơi ở
Mỹ Tho- Tiền Giang
Mã:
AI4=SUM(IFERROR(--TRIM(MID(SUBSTITUTE(C4:AG4,"+",REPT(" ",10)),{1;2}*10-9,10)),))
AJ4=COUNT(1/TEXT(IFERROR(--TRIM(MID(SUBSTITUTE(C4:AG4,"+",REPT(" ",10)),{1;2}*10-9,10)),),"[<0.5];\0"))
nhấn Ctrl+Shirt+Enter
Nếu có thời gian phiền bạn giúp mình diễn giải công thức trên để mình có thể học hỏi và áp dụng trong những trường hợp tương tự.
Mong muốn của bạn hơi khó đó nhen! Thường thì tác giả của công thức chỉ có thể giải thích ý nghĩa tổng quát của toàn công thức thui, chứ diễn giải cặn kẽ ra thì "ba chấm" lắm. :D
Mình hỏi tế nhị trước thế này: bạn đã hiểu hết cú pháp cũng như ý nghĩa của các hàm trong công thức trên chưa (iferror, trim, substitute, rept) và hiểu về công thức mảng chưa? Nếu hiểu hết rồi thì giờ ngồi nghiệm từng đoạn từng đoạn công thức, từ từ sẽ hiểu. Còn nếu chưa hiểu thì buộc lòng phải google tự tìm hiểu đó bạn. :)
 

nms_qa

Con gà hơi già
Tham gia ngày
1 Tháng tư 2013
Bài viết
13
Được thích
1
Điểm
365
Mình hỏi tế nhị trước thế này: bạn đã hiểu hết cú pháp cũng như ý nghĩa của các hàm trong công thức trên chưa (iferror, trim, substitute, rept) và hiểu về công thức mảng chưa? Nếu hiểu hết rồi thì giờ ngồi nghiệm từng đoạn từng đoạn công thức, từ từ sẽ hiểu. Còn nếu chưa hiểu thì buộc lòng phải google tự tìm hiểu đó bạn. :)
Mình chưa bạn ạ, đặc biệt là khúc này ",REPT(" ",10)),{1;2}*10-9,10)),))"

Mã:
=SUM(IFERROR(--TRIM(MID(SUBSTITUTE(C4:AG4,"+",REPT(" ",10)),{1;2}*10-9,10)),))
Mình chép công thức trên xuống ô C54 và thay C4:AG4 (dòng) thành C4:C53 (cột) nhưng không được, rất mong được hướng dẫn thêm ạ

Mình cảm ơn nhiều!
 

xuongrongdat

Có bao giờ bạn tự hỏi "Tôi là ai?"
Tham gia ngày
30 Tháng sáu 2008
Bài viết
356
Được thích
248
Điểm
710
Nơi ở
Mỹ Tho- Tiền Giang
Mình chưa bạn ạ, đặc biệt là khúc này ",REPT(" ",10)),{1;2}*10-9,10)),))"

Mã:
=SUM(IFERROR(--TRIM(MID(SUBSTITUTE(C4:AG4,"+",REPT(" ",10)),{1;2}*10-9,10)),))
Mình chép công thức trên xuống ô C54 và thay C4:AG4 (dòng) thành C4:C53 (cột) nhưng không được, rất mong được hướng dẫn thêm ạ

Mình cảm ơn nhiều!
đặc biệt là khúc này ",REPT(" ",10)),{1;2}*10-9,10)),))" => ôi, đoạn này là hàm Rept lồng trong hàm Substitute í, bạn tách ra vậy không phải đâu. Bạn cóp công thức vào Excel rồi vào xem công thức, bạn xem màu của các cặp dấu ngoặc đơn í, hễ cùng màu là nó chung trong 1 hàm í. Bạn xem vậy rồi suy nghĩ hiểu mới đúng, chứ không ngắt ra tùm lum là sai chỗ sai hàm.

Thân !
 
Lần chỉnh sửa cuối:

Nhattanktnn

Thành viên tiêu biểu
Tham gia ngày
11 Tháng mười một 2016
Bài viết
433
Được thích
381
Điểm
210
Đếm công đủ mình nghĩ bạn nên sửa lại công thức cho gọn:
Mã:
AH4=SUMPRODUCT(COUNTIF($C4:$AG4,{"*S*","*H*","*C*","*D1*","*D2*"}))
Bài đã được tự động gộp:

Mình chép công thức trên xuống ô C54 và thay C4:AG4 (dòng) thành C4:C53 (cột) nhưng không được, rất mong được hướng dẫn thêm ạ
Thử sửa {1;2} thành {1,2} xem sao
 
Lần chỉnh sửa cuối:

nms_qa

Con gà hơi già
Tham gia ngày
1 Tháng tư 2013
Bài viết
13
Được thích
1
Điểm
365
Thử sửa {1;2} thành {1,2} xem sao
Vừa xinh :p Cảm ơn bạn nhiều!
Bài đã được tự động gộp:

đặc biệt là khúc này ",REPT(" ",10)),{1;2}*10-9,10)),))" => ôi, đoạn này là hàm Rept lồng trong hàm Substitute í, bạn tách ra vậy không phải đâu. Bạn cóp công thức vào Excel rồi vào xem công thức, bạn xem màu của các cặp dấu ngoặc đơn í, hễ cùng màu là nó chung trong 1 hàm í. Bạn xem vậy rồi suy nghĩ hiểu mới đúng, chứ không ngắt ra tùm lum là sai chỗ sai hàm.

Thân !
Cảm ơn bạn, mình sẽ google tìm hiểu thêm xem sao!
 
Top Bottom