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

[luvsmile]

Các bác giúp em bài này với, em muốn trả lại tên người đạt điểm cao nhất tương ứng với số điểm của họ thì dùng hàm gì ạ

 

[vu_tuan_manh_linh]

Các bác giúp em bài này với, em muốn trả lại tên người đạt điểm cao nhất tương ứng với số điểm của họ thì dùng hàm gì ạ

=INDEX(B3:B10;MATCH(MAX(H3:H10);H3:H10;0))
(chỉ đúng khi max là duy nhất)

 

[leonguyenz]

=INDEX(B3:B10;MATCH(MAX(H3:H10);H3:H10;0))
(chỉ đúng khi max là duy nhất)

Người có điểm trung bình cao nhất (cột F), không phải điểm Kết quả (cột H).

Các bác giúp em bài này với, em muốn trả lại tên người đạt điểm cao nhất tương ứng với số điểm của họ thì dùng hàm gì ạ

Số thí sinh có điểm trung bình > 8: chủ thớt làm sai nốt. COUNTIF cột F chứ không phải cột H.
Tiêu đề chẳng ăn khớp gì với câu hỏi, mong mod chuyển dùm.

 

[luvsmile]

=INDEX(B3:B10;MATCH(MAX(H3:H10);H3:H10;0))
(chỉ đúng khi max là duy nhất)

Bác ơi, e nhập vào thì nó báo #N/A

 

[A_Lang_9300]

bạn xem file này nha. bài này sài hàm offset lồng match cũng ra kết quả tương tự như index lồng match.
Chắc là bạn gõ nhầm thui, đây là bài tập đơn giản nên mình đoán là tìm điểm trung bình cao nhất chứ ko pải là tìm người có điểm trung bình cao nhất, bởi vì câu trên là tìm điểm trung bình thấp nhất nên câu dưới pải là tìm điểm trung bình cao nhất.

 

[diemhuyenanh]

bạn dùng hàm lookup và max la ok

 

[nguyen_dang]

Chào Các Bạn GPE.

Các Bạn vui lòng giúp mình Công thức trích lọc Tên có điểm cao nhất (có trùng điểm số), mình copy công thức của Các Bác ở trên nhưng không có trường hợp trùng điểm. Cám ơn Các Bạn.

 

[leonguyenz]

Chào Các Bạn GPE.

Các Bạn vui lòng giúp mình Công thức trích lọc Tên có điểm cao nhất (có trùng điểm số), mình copy công thức của Các Bác ở trên nhưng không có trường hợp trùng điểm. Cám ơn Các Bạn.

Tên người đạt thành tích cao nhất, AF3:

=IF(ROW(A1)<=COUNTIF($AB$3:$AB$57,MAX($AB$3:$AB$57)),INDEX($B$3:$AB$57,SMALL(IF($AB$3:$AB$57=MAX($AB$3:$AB$57),ROW($AB$3:$AB$57)-2,""),ROW(A1)),),"")

Ctrl+Shift+Enter và fill xuống.

 

[ndu96081631]

Tên người đạt thành tích cao nhất, AF3:

=IF(ROW(A1)<=COUNTIF($AB$3:$AB$57,MAX($AB$3:$AB$57)),INDEX($B$3:$AB$57,SMALL(IF($AB$3:$AB$57=MAX($AB$3:$AB$57),ROW($AB$3:$AB$57)-2,""),ROW(A1)),),"")

Ctrl+Shift+Enter và fill xuống.

Công thức tại AF3 sao không dựa vào cột AC (XẾP HẠNG) cho gọn nhỉ? Cứ em nào hạng 1 thì trích

 

[leonguyenz]

Công thức tại AF3 sao không dựa vào cột AC (XẾP HẠNG) cho gọn nhỉ? Cứ em nào hạng 1 thì trích

Em nghĩ bạn ấy hỏi công thức "trích lọc Tên", đây cũng là một phương án tự động trả ra kết quả.

 

[ndu96081631]

Em nghĩ bạn ấy hỏi công thức "trích lọc Tên", đây cũng là một phương án tự động trả ra kết quả.

Thì mình đang nói đến trích lọc tên đấy thôi nhưng thay vì tham chiếu vào cột AB (TỔNG CỘNG) sao không tham chiếu đến cột AC (XẾP HẠNG) cho nó gọn
Ý tôi là dùng cái này:

=IF(ROWS($1:1)>[COLOR=#ff0000]COUNTIF(AC$3:$AC$57,1)[/COLOR],"",INDEX($B$3:$B$57,SMALL([COLOR=#ff0000]IF($AC$3:$AC$57=1,ROW($AB$3:$AB$57)-2,"")[/COLOR],ROWS($1:1))))

 

[nguyen_dang]

Cám ơn sự giúp đỡ của Các Bạn. Mình xin lỗi vì không nói rõ ý định của mình để Các Bạn không mất thời gian giải thích của mình.

Bảng điểm đó dành cho HỘi THI, Mình chọn ra hạng 1, 2, 3

Công thức mình copy trong file đính không hiển thị đúng tên khi bị trùng điểm số. (nó chọn tên người đầu tiên cho người trùng điểm số thứ hai luôn). Ý của mình là khắc phục điều đó. Các bạn giúp lại mình ngen. Cám ơn các Bạn.

 

[ndu96081631]

Cám ơn sự giúp đỡ của Các Bạn. Mình xin lỗi vì không nói rõ ý định của mình để Các Bạn không mất thời gian giải thích của mình.

Bảng điểm đó dành cho HỘi THI, Mình chọn ra hạng 1, 2, 3

Công thức mình copy trong file đính không hiển thị đúng tên khi bị trùng điểm số. (nó chọn tên người đầu tiên cho người trùng điểm số thứ hai luôn). Ý của mình là khắc phục điều đó. Các bạn giúp lại mình ngen. Cám ơn các Bạn.

Công thức tại AF3:

=INDEX($B$3:$B$57,SMALL( IF($AC$3:$AC$57<=SMALL($AC$3:$AC$57,3),ROW($AC$3:$AC$57)-2,""),ROWS($1:1)))

Kéo fill đến AF5
Công thức mảng, gõ xong phải Ctrl + Shift + Enter để kết thúc nha

 

[supermanhehe]

cho mình hỏi, giả sử bài đầu tiên mình làm theo hàm lookup và max như thế này =lookup(max(F3:F10),F3:F10,B3:B10) thì nó lại trả kết quả sai, sao lại thế nhỉ?

 

[khuongvietphong]

cho mình hỏi, giả sử bài đầu tiên mình làm theo hàm lookup và max như thế này =lookup(max(F3:F10),F3:F10,B3:B10) thì nó lại trả kết quả sai, sao lại thế nhỉ?

Cùng chung thắc mắc với bạn. Không hiểu sao lại vậy nhỉ.

 

[giangleloi]

Cùng chung thắc mắc với bạn. Không hiểu sao lại vậy nhỉ.

Dò tìm không chính xác thì kết quả không chính xác là bình thường mà. Thử như thế này:

=LOOKUP(2,1/(MAX($F$3:$F$10)=$F$3:$F$10),$B$3:$B$10)

 

[khuongvietphong]

Dò tìm không chính xác thì kết quả không chính xác là bình thường mà...

Cho em hỏi tại sao lại là dò không chính xác, không chính xác ở chỗ nào ạ.

 

[supermanhehe]

Dò tìm không chính xác thì kết quả không chính xác là bình thường mà. Thử như thế này:

=LOOKUP(2,1/(MAX($F$3:$F$10)=$F$3:$F$10),$B$3:$B$10)

uk đúng rồi, vì lookup_vector phải sắp xếp theo thứ tự tăng dần =)) cảm ơn nhé, h mới biết

 

[supermanhehe]

nhưng mà mình không hiểu công thức của bạn, bạn có thể giải thích được không

 

[bebo021999]

cho mình hỏi, giả sử bài đầu tiên mình làm theo hàm lookup và max như thế này =lookup(max(F3:F10),F3:F10,B3:B10) thì nó lại trả kết quả sai, sao lại thế nhỉ?

Vấn đề ở chổ mảng F3:F10 không theo thứ tự tăng dần nên kết quả sai. VD: tìm vị trí số lớn nhất trong mảng A = {1,3,2,5,4,9,4,1} trả về số tại vị trí tương ứng trong mảng B.
Nếu dùng vầy là sai: =LOOKUP(MAX(mảng A),mảng A,mảng B)
Do đó tìm cách biến mảng A thành mảng tăng dần:
==> MAX(mảng A) = mảng A
==> 9={1,3,2,5,4,9,4,1}
tạo thành mảng {FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE}
Với TRUE=1, FALSE=0,
==>1/(MAX(mảng A) = mảng A) = {#DIV/0,#DIV/0,#DIV/0,#DIV/0,#DIV/0,1,#DIV/0,#DIV/0}
Mảng này chính là mảng có một phần tử số có nghĩa (số 1). Ta chỉ cần tìm số nào đó lớn hơn 1 (VD: số 2) trong mảng này là ra phần tử tương ứng trong mảng B.
LOOKUP(2,1/(MAX(mảng A) = mảng A),mảng B)
Đây là công thức mà bạn giangleloi đã áp dụng.

 

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