Jim describió una situación en la que tiene una lista de nombres de empleados y sus salarios. Quiere determinar quiénes son los cinco empleados mejor pagados. Utiliza la función LARGE para identificar los cinco salarios más grandes y luego intenta usar BUSCARV para devolver los nombres que pertenecen a esos salarios. Esto funciona bien a menos que haya duplicados en los cinco salarios principales (a las personas se les paga el mismo salario). Si los hay, BUSCARV solo devuelve el nombre del primer empleado con ese salario.

Para devolver todos los nombres propios, hay un par de cosas que puede hacer.

Un método sería omitir el uso de una fórmula por completo. En su lugar, puede utilizar la función Autofiltro en Excel:

  1. Seleccione cualquier celda en su tabla de datos.

  2. Elija datos | Filtro | Autofiltro. Excel agrega flechas desplegables a la derecha del encabezado de cada columna en la tabla.

  3. Utilice la lista desplegable en la parte superior de la columna de salarios para elegir los 10 principales. Excel muestra el cuadro de diálogo Autofiltro de los 10 principales. (Ver figura 1)

  4. Ajuste el control central de 10 a 5.

  5. Haga clic en Aceptar. Excel muestra los cinco salarios principales de la lista.

Cuando sigue estos pasos, en realidad puede terminar con más de cinco registros visibles, especialmente si hay vínculos en los salarios de los empleados. El filtro identifica los cinco salarios principales y luego muestra todos los registros con salarios que coinciden con ellos.

Si no desea utilizar el Autofiltro, otra opción es simplemente asegurarse de que haya algo único en cada uno de los registros de su lista de empleados. Por ejemplo, si los nombres de los empleados están en la columna B y los salarios están en la columna C, entonces puede usar la siguiente fórmula en la columna A para hacer que cada registro sea único:

=C2+ROW()/100000000

Esto agregará el número de fila dividido por 100,000,000 y creará un valor único. Si tiene (por ejemplo) salarios idénticos de 98.765,43 en las filas 2 y 49 en la columna A, serán:

98765.43000002 98765.43000049

El número grande (100,000,000) es para que si tuviera un número idéntico en la fila 65536, obtendría:

98765.43065536

E incluso en este caso, el valor redondeado a 2 decimales seguiría siendo el número real. Si LARGE y VLOOKUP se realizan con los valores «no únicos» en la columna A, devolverá los salarios más altos (y sus personas asociadas), según la posición de la persona dentro de la lista.

Un tercer enfoque es utilizar las funciones RANK y COUNTIF para devolver una «clasificación» única para cada valor en la lista de salarios. Si los salarios están en el rango B1: B50, ingrese lo siguiente en la celda C1 y cópielo en el rango:

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

Ahora puede usar ÍNDICE en los valores de clasificación para devolver el nombre asociado con cada salario.

Finalmente, un cuarto enfoque es crear una macro que pueda devolver la información deseada. Hay muchas formas en que se puede implementar una macro; el siguiente es solo uno de ellos:

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

Los parámetros pasados ​​a esta función definida por el usuario son el valor, el rango de celdas para buscar, el «desplazamiento» de este rango para la búsqueda (el número de columnas a la derecha es positivo, a la izquierda es negativo) y el número del duplicado (1 es el primer valor, 2 el segundo, y así sucesivamente).

Para usarlo, por ejemplo, suponga que A1: B1 contiene encabezados de columna, A2: A100 contiene los salarios y B2: B100 contiene los nombres de los empleados.

En la celda E2 puede ingresar lo siguiente para determinar el salario más alto en la tabla:

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

En la celda F2 puede ingresar la siguiente fórmula para determinar si la fila tiene duplicados y realizar un seguimiento del «valor» actual de ese duplicado:

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

En la celda G2 puede usar la siguiente fórmula, que invoca la función definida por el usuario:

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

Copie las celdas E2: G2 a E3: G6, y tendrá (en la columna G) los nombres de los empleados con los cinco salarios más altos.

_Nota: _

Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios ExcelTips), he preparado una página especial que incluye información útil.

link: / excelribbon-ExcelTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador].

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (3077) se aplica a Microsoft Excel 97, 2000, 2002 y 2003.