Nhờ các Anh, Chị giúp trích lọc dữ liệu sinh viên thi lại

luuk

Thành viên mới
Tham gia ngày
23 Tháng ba 2015
Bài viết
7
Được thích
1
Điểm
165
Cháo các Anh, Chị.
Sau 2 ngày vật lộn với Excel, tìm nhiều cách khác nhau nhưng chưa thoả mãn điều kien mong muốn. Chính vì vậy em mong các Anh, Chị có kinh nghiệm giúp đỡ, hướng dẫn em làm cách nào để đạt được yêu cầu.
Em có file điểm của Sinh viên, giờ muốn lọc ra danh sách những sinh viên có điểm =0 (phải học lại) và điểm <5 (phải thi lại).
Em đính kèm file, từ cột R là danh sách em muốn lọc ra. Mong các Anh, Chị giúp đỡ ạ.
Trân trọng cảm ơn!
 

File đính kèm

HieuCD

Thành viên gạo cội
Tham gia ngày
14 Tháng chín 2010
Bài viết
6,270
Được thích
11,665
Điểm
1,560
Cháo các Anh, Chị.
Sau 2 ngày vật lộn với Excel, tìm nhiều cách khác nhau nhưng chưa thoả mãn điều kien mong muốn. Chính vì vậy em mong các Anh, Chị có kinh nghiệm giúp đỡ, hướng dẫn em làm cách nào để đạt được yêu cầu.
Em có file điểm của Sinh viên, giờ muốn lọc ra danh sách những sinh viên có điểm =0 (phải học lại) và điểm <5 (phải thi lại).
Em đính kèm file, từ cột R là danh sách em muốn lọc ra. Mong các Anh, Chị giúp đỡ ạ.
Trân trọng cảm ơn!
Mã:
R4 =IFERROR(INDEX($C$4:$C$20 &" " &$D$4:$D$20,SMALL(IF(OFFSET($D$4:$D$20,,MATCH(R$3,$E$2:$M$2,0))=0,ROW($1:$17),""),ROWS($1:1)),),"")
R14 =IFERROR(INDEX($C$4:$C$20 &" " &$D$4:$D$20,SMALL(IF((OFFSET($D$4:$D$20,,MATCH(R$13,$E$2:$M$2,0))>0)*(OFFSET($D$4:$D$20,,MATCH(R$13,$E$2:$M$2,0))<5),ROW($1:$17),""),ROWS($1:1)),),"")
Nhấn Ctrl+Shift+Enter
Copy cho các ô còn lại
 

luuk

Thành viên mới
Tham gia ngày
23 Tháng ba 2015
Bài viết
7
Được thích
1
Điểm
165
Mã:
R4 =IFERROR(INDEX($C$4:$C$20 &" " &$D$4:$D$20,SMALL(IF(OFFSET($D$4:$D$20,,MATCH(R$3,$E$2:$M$2,0))=0,ROW($1:$17),""),ROWS($1:1)),),"")
R14 =IFERROR(INDEX($C$4:$C$20 &" " &$D$4:$D$20,SMALL(IF((OFFSET($D$4:$D$20,,MATCH(R$13,$E$2:$M$2,0))>0)*(OFFSET($D$4:$D$20,,MATCH(R$13,$E$2:$M$2,0))<5),ROW($1:$17),""),ROWS($1:1)),),"")
Nhấn Ctrl+Shift+Enter
Copy cho các ô còn lại
Trân trọng cảm ơn Bạn. Mình có thể nghe giải thích công thức trên không ạ?
 

HieuCD

Thành viên gạo cội
Tham gia ngày
14 Tháng chín 2010
Bài viết
6,270
Được thích
11,665
Điểm
1,560
Trân trọng cảm ơn Bạn. Mình có thể nghe giải thích công thức trên không ạ?
2 công thức có cấu trúc giống nhau
$C$4:$C$20 &" " &$D$4:$D$20 : Ghép họ tên thành 1 cột
MATCH(R$3,$E$2:$M$2,0) : Tìm thứ tự cột môn học
OFFSET($D$4:$D$20,,MATCH(R$13,$E$2:$M$2,0)): Cột Điểm của môn học, từ cột $D$4:$D$20 qua phải MATCH(R$3,$E$2:$M$2,0) cột
ROW($1:$17): Thứ tự dòng dữ liệu từ 1 tới 17
IF(OFFSET($D$4:$D$20,,MATCH(R$3,$E$2:$M$2,0))=0,ROW($1:$17),""): nếu cột điểm môn hoc = 0 thì trả về số thứ tự nếu không thỏa trả về giá trị ""
ROWS($1:1)=1 là kết quả thứ 1, copy xuống sẽ là ROWS($1:2)=2 là kết quả thứ 2
SMALL(IF(OFFSET($D$4:$D$20,,MATCH(R$3,$E$2:$M$2,0))=0,ROW($1:$17),""),ROWS($1:1)): Số thứ tự dòng của kết quả thứ 1
copy xuống
SMALL(IF(OFFSET($D$4:$D$20,,MATCH(R$3,$E$2:$M$2,0))=0,ROW($1:$17),""),ROWS($1:2)): Số thứ tự dòng của kết quả thứ 2
Còn hàm Index chắc bạn biết
Trên thanh công thức, bạn dùng chuột "bôi đen" từng thành phần công thức, bấm phiếm chức năng F9 để xem kết quả của từng thành phần, sẽ dể hình dung cách vận hành của công thức
 

snow25

Thành viên gắn bó
Tham gia ngày
24 Tháng bảy 2018
Bài viết
2,462
Được thích
2,321
Điểm
360
2 công thức có cấu trúc giống nhau
$C$4:$C$20 &" " &$D$4:$D$20 : Ghép họ tên thành 1 cột
MATCH(R$3,$E$2:$M$2,0) : Tìm thứ tự cột môn học
OFFSET($D$4:$D$20,,MATCH(R$13,$E$2:$M$2,0)): Cột Điểm của môn học, từ cột $D$4:$D$20 qua phải MATCH(R$3,$E$2:$M$2,0) cột
ROW($1:$17): Thứ tự dòng dữ liệu từ 1 tới 17
IF(OFFSET($D$4:$D$20,,MATCH(R$3,$E$2:$M$2,0))=0,ROW($1:$17),""): nếu cột điểm môn hoc = 0 thì trả về số thứ tự nếu không thỏa trả về giá trị ""
ROWS($1:1)=1 là kết quả thứ 1, copy xuống sẽ là ROWS($1:2)=2 là kết quả thứ 2
SMALL(IF(OFFSET($D$4:$D$20,,MATCH(R$3,$E$2:$M$2,0))=0,ROW($1:$17),""),ROWS($1:1)): Số thứ tự dòng của kết quả thứ 1
copy xuống
SMALL(IF(OFFSET($D$4:$D$20,,MATCH(R$3,$E$2:$M$2,0))=0,ROW($1:$17),""),ROWS($1:2)): Số thứ tự dòng của kết quả thứ 2
Còn hàm Index chắc bạn biết
Trên thanh công thức, bạn dùng chuột "bôi đen" từng thành phần công thức, bấm phiếm chức năng F9 để xem kết quả của từng thành phần, sẽ dể hình dung cách vận hành của công thức
Công thức khó hơn viết code VBA anh à.
 

HieuCD

Thành viên gạo cội
Tham gia ngày
14 Tháng chín 2010
Bài viết
6,270
Được thích
11,665
Điểm
1,560

luuk

Thành viên mới
Tham gia ngày
23 Tháng ba 2015
Bài viết
7
Được thích
1
Điểm
165
Viết công thức rèn luyện tư duy phân tích tình huống và hướng xử lý, giúp ích rất nhiều cho viết code giải quyết các bài toán phức tạp
Em phân tích thì đươc nhưng hướng xử lý thì em tịt. Vất vả 2 ngày cứ IF, VLOOKUP, HLOOKUP, .... nhưng không ra được. Em phải thành thật với các Bác là em không biết gì về Excel. Một lần nữa trân trọng cảm ơn các Anh, Chị trên forum GPE. Em sợ một ngày nào đó em nghiền EX thì chết em. :D
 

HieuCD

Thành viên gạo cội
Tham gia ngày
14 Tháng chín 2010
Bài viết
6,270
Được thích
11,665
Điểm
1,560
Em phân tích thì đươc nhưng hướng xử lý thì em tịt. Vất vả 2 ngày cứ IF, VLOOKUP, HLOOKUP, .... nhưng không ra được. Em phải thành thật với các Bác là em không biết gì về Excel. Một lần nữa trân trọng cảm ơn các Anh, Chị trên forum GPE. Em sợ một ngày nào đó em nghiền EX thì chết em. :D
Bạn nên xem Excel là game trí tuệ, giành thời gian cho Excel tốt hơn đam mê lướt mạng xã hội hoặc bét nhè rượu bia với đám bạn
 

thukhon2014

Thành viên hoạt động
Tham gia ngày
6 Tháng hai 2017
Bài viết
154
Được thích
85
Điểm
180
Bạn nên xem Excel là game trí tuệ, giành thời gian cho Excel tốt hơn đam mê lướt mạng xã hội hoặc bét nhè rượu bia với đám bạn
Phải nói, từ lúc biết excel đam mê nó mãnh liệt thật!. Thấy mấy công việc mấy ông phòng ban khác xử lý số liệu mò mò. Chạy code phát ra ngay thấy ham lắm !
 
Top Bottom