Jim đã mô tả một tình huống mà anh ta có một danh sách tên nhân viên và mức lương của họ. Anh ấy muốn xác định 5 nhân viên được trả lương cao nhất là ai. Anh ta sử dụng hàm LARGE để xác định năm mức lương lớn nhất và sau đó cố gắng sử dụng hàm VLOOKUP để trả về tên của các mức lương đó. Điều này hoạt động tốt trừ khi có sự trùng lặp trong năm mức lương cao nhất (mọi người được trả cùng một mức lương). Nếu có, thì hàm VLOOKUP chỉ trả về tên của nhân viên đầu tiên ở mức lương đó.

Để trả lại tất cả các tên riêng, bạn có thể làm một số điều sau.

Một phương pháp sẽ là bỏ qua hoàn toàn bằng cách sử dụng một công thức. Thay vào đó, bạn có thể sử dụng tính năng AutoFilter trong Excel:

  1. Chọn bất kỳ ô nào trong bảng dữ liệu của bạn.

  2. Chọn dữ liệu | Bộ lọc | Bộ lọc tự động. Excel thêm các mũi tên thả xuống ở bên phải của mỗi tiêu đề cột trong bảng.

  3. Sử dụng danh sách thả xuống ở đầu cột lương để chọn Top 10. Excel sẽ hiển thị hộp thoại Top 10 AutoFilter. (Xem Hình 1.)

  4. Điều chỉnh điều khiển trung tâm từ 10 thành 5.

  5. Nhấp vào OK. Excel hiển thị năm mức lương cao nhất trong danh sách.

Khi bạn làm theo các bước này, bạn thực sự có thể kết thúc với hơn năm bản ghi được hiển thị, đặc biệt nếu có mối ràng buộc trong tiền lương của nhân viên. Bộ lọc xác định năm mức lương cao nhất và sau đó hiển thị tất cả các bản ghi có mức lương phù hợp với những mức lương đó.

Nếu bạn không muốn sử dụng AutoFilter, một tùy chọn khác là chỉ cần đảm bảo rằng có một cái gì đó duy nhất về mỗi bản ghi trong danh sách nhân viên của bạn. Ví dụ: nếu tên nhân viên ở cột B và lương ở cột C, thì bạn có thể sử dụng công thức sau trong cột A để tạo mỗi bản ghi là duy nhất:

=C2+ROW()/100000000

Điều này sẽ cộng số hàng chia cho 100.000.000 và sẽ tạo ra một giá trị duy nhất. Nếu bạn có (ví dụ) mức lương giống hệt nhau là 98.765,43 ở hàng 2 và 49 trong cột A thì mức lương đó sẽ là:

98765.43000002 98765.43000049

Con số lớn (100.000.000) là do đó nếu bạn có một số trùng trong hàng 65536, bạn sẽ nhận được:

98765.43065536

Và ngay cả trong trường hợp này, giá trị được làm tròn đến 2 chữ số thập phân vẫn là số thực. Nếu LARGE và VLOOKUP được thực hiện với các giá trị “không phải duy nhất” trong cột A, thì bạn sẽ trả về mức lương lớn nhất (và những người có liên quan của họ), dựa trên vị trí của người đó trong danh sách.

Cách tiếp cận thứ ba là sử dụng các hàm RANK và COUNTIF để trả về “xếp hạng” duy nhất cho mỗi giá trị trong danh sách lương. Nếu mức lương nằm trong phạm vi B1: B50, hãy nhập thông tin sau vào ô C1 và sao chép nó xuống phạm vi:

=RANK(B1,$B$1:$B$50)+COUNTIF($B$1:B1,B1)-1

Bây giờ bạn có thể sử dụng INDEX trên các giá trị xếp hạng để trả về tên được liên kết với mỗi mức lương.

Cuối cùng, cách tiếp cận thứ tư là tạo một macro có thể trả về thông tin mong muốn. Có nhiều cách mà một macro có thể được thực hiện; sau đây chỉ là một trong số đó:

Function VLIndex(vValue, rngAll As Range, _   iCol As Integer, lIndex As Long)

Dim x As Long     Dim lCount As Long     Dim vArray() As Variant     Dim rng As Range     On Error GoTo errhandler

Set rng = Intersect(rngAll, rngAll.Columns(1))

ReDim vArray(1 To rng.Rows.Count)

lCount = 0     For x = 1 To rng.Rows.Count         If rng.Cells(x).Value = vValue Then             lCount = lCount + 1             vArray(lCount) = rng.Cells(x).Offset(0, iCol).Value         End If     Next x

ReDim Preserve vArray(1 To lCount)

If lCount = 0 Then         VLIndex = CVErr(xlErrNA)

ElseIf lIndex > lCount Then         VLIndex = CVErr(xlErrNum)

Else         VLIndex = vArray(lIndex)

End If errhandler:

If Err.Number <> 0 Then VLIndex = CVErr(xlErrValue)

End Function

Các tham số được truyền cho hàm do người dùng xác định này là giá trị, phạm vi ô cần tra cứu, “khoảng bù” từ phạm vi này để tra cứu (số cột bên phải là dương, bên trái là âm) và số lượng trùng lặp (1 là giá trị đầu tiên, 2 là giá trị thứ hai, v.v.).

Để sử dụng nó, chẳng hạn, giả sử A1: B1 chứa tiêu đề cột, A2: A100 chứa lương và B2: B100 chứa tên nhân viên.

Trong ô E2, bạn có thể nhập thông tin sau để xác định mức lương lớn nhất trong bảng:

=LARGE($A$2:$A$100,ROW()-1)

Trong ô F2, bạn có thể nhập công thức sau để xác định xem hàng có bất kỳ bản sao nào không và theo dõi “giá trị” hiện tại của bản sao đó:

=IF(E2=E1,1+F1,1)

Trong ô G2, bạn có thể sử dụng công thức sau, công thức này gọi hàm do người dùng định nghĩa:

=VLIndex(E2,$A$2:$A$100,1,F2)

Sao chép các ô E2: G2 sang E3: G6, và bạn sẽ có (trong cột G) tên của nhân viên có mức lương lớn nhất.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (3077) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003.