JERARQUIA de un valor dentro de un rango o matriz

Descargar Archivo del  Ejemplo

Hoy, con vuestro permiso, resolveremos una consulta que me habéis planteado. Partiendo de una tabla en la que tenemos información relativa a los empleados de la empresa y su sueldo anual bruto, queremos generar una Entrada de Datos que nos permita seleccionar, de una lista desplegable, a uno de dichos empleados y que me indique qué puesto ocupa por ganancias dentro de la empresa. La tabla de partida es la que se puede ver a continuación:

La solución, como veremos enseguida, es bastante sencilla. Para ello utilizaremos, entre otras, la función JERARQUIA, que paso a explicar brevemente:

=JERARQUIA(Número;Referencia;Orden)

* El argumento Número    hace referencia a la cifra concreta (en nuestro caso será el sueldo bruto) cuya jerarquía deseamos conocer.

* El argumento Referencia    es una matriz de una lista de números o una referencia a una lista de números (en nuestro caso será el rango de sueldos).

* Y finalmente el argumento Orden es un número que especifica cómo clasificar el argumento número. Los valores que puede tomar este argumento son básicamente dos: cero o cualquier valor distinto de cero. Si ponemos cero (o lo omitimos) la referencia de orden será descendente, mientras que en el caso contrario la referencia de orden será ascendente.

Vamos a utilizar dos soluciones distintas aunque muy similares.

Solución 1: En el rango G3:G17 resolvemos el orden para todos y cada uno de los empleados de la tabla. Seguimos estos pasos:

1. Nos situamos en G2 y escribimos el rótulo Orden.

2. Seleccionamos el rango E3:G17 y hacemos clic en el cuadro de nombres (a la izquierda de la barra de fórmulas) y escribimos el nombre tabla2 y pulsamos enter.

3. Seleccionamos el rango E2:F17 y vamos al menú Insertar/Nombre/Crear y dejamos marcado sólo nombres en la columna superior. De esta manera ya habremos dado nombre a los distintos rangos que luego utilizaremos en nuestras fórmulas.

4. Nos situamos en la celda B2 y preparamos los rótulos como se ve en la imagen. Vamos a C2 y abrimos el menú Datos/Validación de datos y seleccionamos Permitir/Lista y en Origen escribimos =Empleado. De esta manera ya tendremos nuestra lista desplegable en C2 para elegir el empleado del que queramos la información (evidentemente en C5 no debe escribir nada por ahora ya que es donde resolveremos la fórmula):

5. Nos situamos en G3 y escribimos la siguiente fórmula:

=JERARQUIA(F3;Sueldo)

Esta fórmula nos devolverá el orden que ocupa el sueldo que aparece en F3 dentro del rango Sueldo. Si quiere que aparezca como "6º" en vez de "6" sólo tiene que añadir a la fórmula la función CONCATENAR, o lo que es lo mismo, el operador &:

=JERARQUIA(F3;Sueldo)&"º"

6. Copiamos la fórmula de G3 en el resto de celdas del rango hasta G17. De esta manera ya tenemos el orden que ocupa cada uno según su sueldo:

7. Nos situamos en C5 y escribimos la siguiente fórmula:

=BUSCARV(C2;tabla2;3;FALSO)

De esta manera estaremos buscando el puesta que ocupa solamente el empleado elegido en C2 en nuestra lista desplegable.

Solución2: Resolvemos directamente en C8 el problema sin calcular el orden de todos y cada uno de los empleados. Hacemos lo siguiente:

1. Si no hemos desarrollado la primera solución ejecutamos los pasos 2, 3 y 4 de la misma.

2. Seleccionamos el rango E3:F17 y le damos el nombre tabla1.

3. Nos situamos en C8 y escribimos la siguiente fórmula:

=JERARQUIA(BUSCARV(C2;tabla;2;FALSO);Sueldo)&"º"

Fíjese que hemos anidado la función BUSCARV como primer argumento de la función JERARQUIA. Esto nos proporcionará la búsqueda del sueldo del empleado que introduzcamos en C2. Evidentemente, el resultado obtenido es el mismo que en la solución 1.

 Si desea añadir una Salida de Datos como la que se muestra en la imagen en B11, sólo tiene que añadir la siguiente fórmula:

="Es el "&C8&" que más gana"

Fíjese que de nuevo hemos utilizado la función CONCATENAR (&) para unir varios textos y una referencia al valor de la celda C8, además de decorar un poco el contorno. En este sentido (en el de la decoración) permitidme que os recomiende que jamás utilicéis la opción de formato combinar y centrar, que tenéis en vuestra barra de formato y que es el icono que podéis ver en la imagen:

este tipo de formato da muchísimos problemas, como sabréis. Es mucho mejor que seleccionéis, en este caso, B11 y C11 y vayáis al menú Formato/Celdas/Alineación y en la opción Horizontal seleccionéis Centrar en la selección. El resultado es el mismo pero esta opción no os dará ningún problema.

Como apunte final indicaros que la función JERARQUIA es como la "inversa" de K.ESIMO.MAYOR (o MENOR). Para que se comprenda correctamente con K.ESIMO.MAYOR estaríamos preguntando (en nuestro ejemplo) quién es el 6º que más gana, mientras que con JERARQUIA lo que calculamos es precisamente el orden que ocupa un determinado empleado.