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

Liên hệ QC

nms_qa

Con gà hơi già
Tham gia
1/4/13
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: 9
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: 20
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
 
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!
 
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. :)
 
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!
 
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:
Đế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:
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!
 
Web KT
Back
Top Bottom