Джим описал ситуацию, когда у него есть список имен сотрудников и их зарплат. Он хочет определить пятерку самых высокооплачиваемых сотрудников. Он использует функцию НАИБОЛЬШИЙ, чтобы определить пять самых крупных зарплат, а затем пытается использовать ВПР, чтобы получить имена, принадлежащие этим зарплатам. Это отлично работает, если в пяти самых высоких зарплатах нет дубликатов (люди получают одинаковую зарплату). Если да, то функция ВПР возвращает только имя первого сотрудника с этой зарплатой.

Чтобы вернуть все имена собственные, вы можете сделать несколько вещей.

Один из способов — вообще обойтись без формулы. Вместо этого вы можете использовать функцию автофильтра в Excel:

  1. Выберите любую ячейку в вашей таблице данных.

  2. Выберите данные | Фильтр | Автофильтр. Excel добавляет стрелки раскрывающегося списка справа от заголовка каждого столбца в таблице.

  3. Используйте раскрывающийся список в верхней части столбца зарплат, чтобы выбрать Top 10. Excel отображает диалоговое окно Top 10 AutoFilter. (См. Рис. 1.)

  4. Настройте центральный регулятор от 10 до 5.

  5. Щелкните ОК. Excel отображает пять первых зарплат в списке.

Выполнив эти шаги, вы можете увидеть более пяти записей, особенно если есть связи в заработной плате сотрудников. Фильтр определяет пять самых высоких зарплат, а затем отображает все записи с соответствующими зарплатами.

Если вы не хотите использовать автофильтр, другой вариант — просто убедиться, что в каждой записи в вашем списке сотрудников есть что-то уникальное. Например, если имена сотрудников указаны в столбце B, а зарплаты — в столбце C, вы можете использовать следующую формулу в столбце A, чтобы сделать каждую запись уникальной:

=C2+ROW()/100000000

Это добавит номер строки, разделенный на 100000000, и даст уникальное значение. Если у вас (например) одинаковая заработная плата 98 765,43 в строках 2 и 49 в столбце A, они будут:

98765.43000002 98765.43000049

Большое число (100000000) означает, что если бы у вас было идентичное число в строке 65536, вы бы получили:

98765.43065536

И даже в этом случае округленное значение до двух знаков после запятой все равно будет действительным числом. Если LARGE и VLOOKUP выполняются с «неуникальными» значениями в столбце A, тогда вы вернете самые большие зарплаты (и связанных с ними людей), в зависимости от положения человека в списке.

Третий подход заключается в использовании функций RANK и COUNTIF для возврата уникального «рейтинга» для каждого значения в списке зарплат. Если заработная плата находится в диапазоне B1: B50, введите в ячейку C1 следующее и скопируйте его в диапазон:

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

Теперь вы можете использовать INDEX для значений рейтинга, чтобы получить имя, связанное с каждой зарплатой.

Наконец, четвертый подход — создать макрос, который может возвращать желаемую информацию. Есть много способов реализации макроса; следующее — лишь одно из них:

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

Параметры, передаваемые этой пользовательской функции, — это значение, диапазон ячеек для поиска, «смещение» от этого диапазона для поиска (количество столбцов справа положительно, слева отрицательно) и номер дубликата (1 — первое значение, 2 — второе и т. д.).

Чтобы использовать его, например, ради, предположим, что A1: B1 содержит заголовки столбцов, A2: A100 содержит зарплаты, а B2: B100 содержит имена сотрудников.

В ячейку E2 вы можете ввести следующее, чтобы определить самую большую зарплату в таблице:

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

В ячейку F2 вы можете ввести следующую формулу, чтобы определить, есть ли в строке какие-либо дубликаты, и отслеживать текущее «значение» этого дубликата:

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

В ячейке G2 вы можете использовать следующую формулу, которая вызывает пользовательскую функцию:

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

Скопируйте ячейки E2: G2 в E3: G6, и вы получите (в столбце G) имена сотрудников с пятью наибольшими зарплатами.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (3077) применим к Microsoft Excel 97, 2000, 2002 и 2003.