Cách lấy người có điểm trung bình cao nhất

[keochi]

Mình đang dùng hàm index và match để tìm tên lỗi. nhưng khi giá trị ở cột tỷ lệ giống nhau thì toàn trả lại kết quả đầu tiên. mong ae giúp giải bài này để kết quả trả lại không giống nhau. trân trọng cám ơn

 

[Mộng Thường 2001]

Mình đang dùng hàm index và match để tìm tên lỗi. nhưng khi giá trị ở cột tỷ lệ giống nhau thì toàn trả lại kết quả đầu tiên. mong ae giúp giải bài này để kết quả trả lại không giống nhau. trân trọng cám ơn

Thử:

K23=IFERROR(OFFSET($C$4,0.1/ROUND(MOD(AGGREGATE(14,6,ROUND(L$5:L$19,4)*10^8+0.1/$B$5:$B$19,ROW($A1)),1),4),),"")

Enter, fill xuống. Rồi copy cả cột đó cho các cột tương thích khác phía sau.
Xem file kèm.
hihi ^o^

 

[keochi]

Thử:

K23=IFERROR(OFFSET($C$4,0.1/ROUND(MOD(AGGREGATE(14,6,ROUND(L$5:L$19,4)*10^8+0.1/$B$5:$B$19,ROW($A1)),1),4),),"")

Enter, fill xuống. Rồi copy cả cột đó cho các cột tương thích khác phía sau.
Xem file kèm.
hihi ^o^

Cám ơn anh, nhưng a có thể giải thích cho e về câu lệnh đó dc không ạ

 

[Mộng Thường 2001]

Cám ơn anh, nhưng a có thể giải thích cho e về câu lệnh đó dc không ạ

Cũng giống như hàm LARGE() bạn đã dùng, tôi cũng làm tương tự như bạn: truy lục các số đã sắp xếp từ lớn đến nhỏ bằng hàm Aggregate(14,6,....,"vị trí") (bạn có thể tham khảo hàm này tại: https://support.office.com/vi-vn/ar...f17-92b6-e19993fa26df?ui=vi-VN&rs=vi-VN&ad=VN)
Có khác một chút là lấy: Các số tỷ lệ (cột L nhân với 10^8) + số thứ tự dòng (do lấy 0.1/stt nên nếu có phát sinh cùng tỷ lệ thì dòng phát sinh trước sẽ có giá trị lớn hơn dòng phát sinh dưới).
Rồi dùng MOD(....,1) để lấy số lẻ, tức là lấy 0.1/stt ở trên.
Sau cùng dùng 0.1/MOD(...,1) đó để trả về số dòng cần lấy cho OFFSET(C4, 'dòng cần lấy',...)

Các hàm ROUND() dùng để làm tròn số tương thích với các phép chia tỷ lệ.

Chúc bạn học tập vui với anh em GPE.
hihi ^o^

 

[Phuocam]

nhưng khi giá trị ở cột tỷ lệ giống nhau thì toàn trả lại kết quả đầu tiên.

Dùng thử công thức này, xem có dễ hiểu hơn chút nào không?

=IF(L23="-","-",INDEX($C:$C,AGGREGATE(15,6,ROW($L$5:$L$19)/($L$5:$L$19=L23),COUNTIF($L$23:L23,L23))))

 

[keochi]

Cũng giống như hàm LARGE() bạn đã dùng, tôi cũng làm tương tự như bạn: truy lục các số đã sắp xếp từ lớn đến nhỏ bằng hàm Aggregate(14,6,....,"vị trí") (bạn có thể tham khảo hàm này tại: https://support.office.com/vi-vn/article/aggregate-hàm-aggregate-43b9278e-6aa7-4f17-92b6-e19993fa26df?ui=vi-VN&rs=vi-VN&ad=VN)
Có khác một chút là lấy: Các số tỷ lệ (cột L nhân với 10^8) + số thứ tự dòng (do lấy 0.1/stt nên nếu có phát sinh cùng tỷ lệ thì dòng phát sinh trước sẽ có giá trị lớn hơn dòng phát sinh dưới).
Rồi dùng MOD(....,1) để lấy số lẻ, tức là lấy 0.1/stt ở trên.
Sau cùng dùng 0.1/MOD(...,1) đó để trả về số dòng cần lấy cho OFFSET(C4, 'dòng cần lấy',...)

Các hàm ROUND() dùng để làm tròn số tương thích với các phép chia tỷ lệ.

Chúc bạn học tập vui với anh em GPE.
hihi ^o^

E cám ơn ạ ^o^

 

[keochi]

Dùng thử công thức này, xem có dễ hiểu hơn chút nào không?

=IF(L23="-","-",INDEX($C:$C,AGGREGATE(15,6,ROW($L$5:$L$19)/($L$5:$L$19=L23),COUNTIF($L$23:L23,L23))))

E cám ơn ạ ^o^ Nếu được a giải thích cho e dc ko ạ

 

[Mộng Thường 2001]

E cám ơn ạ ^o^ Nếu được a giải thích cho e dc ko ạ

Hầy dà! mấy ông "bạn hiền" của tôi chăm "làm", mà "biếng" giải thích lắm, gọi chung là "làm - biếng" khà khà khà , để tôi xử luôn cho bạn vậy.

Công thức =IF(L23="-","-",INDEX($C:$C,AGGREGATE(15,6,ROW($L$5:$L$19)/($L$5:$L$19=L23),COUNTIF($L$23:L23,L23))))
Cũng sử dụng hàm Aggregate() nhưng dùng đối số 15 tức tìm trị nhỏ tương tự như hàm Small().

1. Đoạn biểu thức: ROW($L$5:$L$19)/($L$5:$L$19=L23) gồm giá trị dòng 5 đến 19, chỉ lọc theo điều kiện tìm giá trị dòng nào thỏa L5:L19=L23 hay L5:L19=23.35%, bạn thấy chỉ có dòng số 5 (L5) là thỏa TRUE (hay 1), các giá trị khác là FALSE (hay 0). Cho nên đoạn biểu thức này cho ra 1 mảng là {5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}/{1;0;0;0;0;0;0;0;0;0;0;0;0;0;0} hay {5;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0}
2. COUNTIF($L$23:L23,L23) đếm xem từ L23 đến L23 có bao nhiêu giá trị bằng L23, kết quả là 1. Số 1 này cung cấp cho hàm Small(...,1) tức tìm giá trị nhỏ nhất trong mảng vừa tìm trên, kết quả = 5.
3. Hàm Index(C:C,5) nó sẽ tìm đến C5 lấy ra kết quả.
4. Nếu tồn tại 2 giá trị giống nhau thì Mảng tạo ra trong mục '1.' sẽ trả về 2 giá trị dòng, và hàm Countif() mục 2 khi đếm thì dòng phát sinh trước là 1, phát sinh sau là 2, tương ứng từng giá trị 1 & 2 đó là 2 số dòng đã cho trong Mảng mục '1.'

Chúc bạn học tập vui với anh em GPE.
hihi ^o^

 

[keochi]

Em cám ơn mọi người đã giải hộ e ạ, nhưng hàm AGGREGATE khó hiểu quá ạ. mọi ng có cách nào giải thích hộ e dc ko ạ

Em cám ơn ạ

 

[keochi]

Hầy dà! mấy ông "bạn hiền" của tôi chăm "làm", mà "biếng" giải thích lắm, gọi chung là "làm - biếng" khà khà khà , để tôi xử luôn cho bạn vậy.

Công thức =IF(L23="-","-",INDEX($C:$C,AGGREGATE(15,6,ROW($L$5:$L$19)/($L$5:$L$19=L23),COUNTIF($L$23:L23,L23))))
Cũng sử dụng hàm Aggregate() nhưng dùng đối số 15 tức tìm trị nhỏ tương tự như hàm Small().

1. Đoạn biểu thức: ROW($L$5:$L$19)/($L$5:$L$19=L23) gồm giá trị dòng 5 đến 19, chỉ lọc theo điều kiện tìm giá trị dòng nào thỏa L5:L19=L23 hay L5:L19=23.35%, bạn thấy chỉ có dòng số 5 (L5) là thỏa TRUE (hay 1), các giá trị khác là FALSE (hay 0). Cho nên đoạn biểu thức này cho ra 1 mảng là {5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}/{1;0;0;0;0;0;0;0;0;0;0;0;0;0;0} hay {5;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0}
2. COUNTIF($L$23:L23,L23) đếm xem từ L23 đến L23 có bao nhiêu giá trị bằng L23, kết quả là 1. Số 1 này cung cấp cho hàm Small(...,1) tức tìm giá trị nhỏ nhất trong mảng vừa tìm trên, kết quả = 5.
3. Hàm Index(C:C,5) nó sẽ tìm đến C5 lấy ra kết quả.
4. Nếu tồn tại 2 giá trị giống nhau thì Mảng tạo ra trong mục '1.' sẽ trả về 2 giá trị dòng, và hàm Countif() mục 2 khi đếm thì dòng phát sinh trước là 1, phát sinh sau là 2, tương ứng từng giá trị 1 & 2 đó là 2 số dòng đã cho trong Mảng mục '1.'

Chúc bạn học tập vui với anh em GPE.
hihi ^o^

Em cám ơn anh đã giúp ạ. hihi

 

[Mộng Thường 2001]

Em cám ơn mọi người đã giải hộ e ạ, nhưng hàm AGGREGATE khó hiểu quá ạ. mọi ng có cách nào giải thích hộ e dc ko ạ
Em cám ơn ạ

Cú pháp của Aggregate(): AGGREGATE('đối số Hàm tương ứng', 'tùy chọn giá trị phớt lờ', 'Mảng', 'đối số phụ cho 1 vài hàm quy định')

Thường hàm Aggregate() được sử dụng với 2 đối số: 14 (thay cho Large()) và 15 (thay cho Small()).

Riêng 'đối số phụ cho 1 vài hàm quy định' chính là giá trị lớn/nhỏ thứ mấy bạn cần truy lục ra, ví dụ: là 5, thì truy lục trong Mảng giá trị có giá trị lớn/nhỏ thứ 5. (giống Large/Small(.....,5) vậy).

Tuy là hàm sử dụng Mảng, nhưng do đã có khai báo trong đối số 'Mảng' nên không cần nhấn tổ hợp phím Ctrl+Shift+Enter (CSE) như hàm Large/Small(Mảng,..), ngoại trừ trường hợp có sử dụng lồng thêm các hàm Indirect(), Iferror(), If(), hay Transpose() trong đối số 'Mảng' của hàm Aggregate(), thì buộc lòng phải nhấn tổ hợp phím CSE này.

Cái đáng nói ở đây là tính năng ưu việt ở chỗ dùng đối số 6: "phớt lờ các giá trị lỗi" phát sinh trong Mảng phía sau, mà không cần dùng thêm hàm nào để bẫy lỗi. Như ví dụ tại bài #28, khi:
K27=AGGREGATE(15,6,{#DIV!0;#DIV!0;#DIV!0;#DIV!0;7;8;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0;#DIV!0}, 2 )
Tức yêu cầu tìm giá trị nhỏ nhất thứ 2 phát sinh trong Mảng, mà ta thấy chỉ có hai giá trị >0, tức dòng 7 và 8, còn lại các trị của các dòng khác báo lỗi, thì với đối số 6 này, Aggregate chỉ tính đến 2 giá trị >0, còn mấy trị lỗi nó không thèm tính đến, do vậy giá trị nhỏ thứ 2 là dòng 8, và nó tìm đến C8 để lấy ra nội dung của C8 làm kết quả.

Một số hàm khác có tính năng "phớt lờ" này nhưng không cần ghi ra đối số, như:

• COUNT(1/(đk1)/(đk2)/..../(đkn)) hay
• LOOKUP(2,1/(đk1)/(đk2)/..../(đkn), [Mảng/Vùng lấy giá tri] )

Chúc bạn học tập vui với anh em GPE.
hihi ^o^