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

Blue Softs Liên hệ QC

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
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

  • Bang cham cong - Copy.xlsx
    38.6 KB · Đọc: 8

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
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

  • Bang cham cong(1).xlsx
    33 KB · Đọc: 15

hocexcel_1991

Bắt đầu học Excel
Tham gia ngày
9 Tháng bảy 2018
Bài viết
588
Được thích
1,038
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
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
1,359
Được thích
1,271
Donate (Momo)
Donate
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
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
1,359
Được thích
1,271
Donate (Momo)
Donate
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 gắn bó
Tham gia ngày
11 Tháng mười một 2016
Bài viết
1,747
Được thích
1,778
Donate (Momo)
Donate
Đế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
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