Halberd Office Supply paga un bonus de 6.000 $ a sus tres mejores comerciales cada trimestre. En el T3 dos comerciales empataron en el segundo puesto, y la hoja de bonus repitió al mismo. Uno cobró 12.000 $ y el otro nada.
| Comercial | Ventas | Bonus pagado | Bonus debido |
|---|---|---|---|
| Okafor | 184.250 | 6.000 | 6.000 |
| Brennan | 171.900 | 12.000 | 6.000 |
| Lindqvist | 171.900 | 0 | 6.000 |
Alcance. Las fórmulas con
K.ESIMO.MAYOR,INDICE,COINCIDIR,CONTAR.SIySUMAPRODUCTOfuncionan en todas las versiones de Excel.JERARQUIA.EQVnecesita Excel 2010 o posterior; antes, usaJERARQUIA.
ORDENARyFILTRARnecesitan Excel 2021 o Microsoft 365, yTOMARnecesita Microsoft 365 o Excel 2024.
01La Lista de Bonus Nombró Dos Veces a Brennan
La hoja tiene diez comerciales en A2:B11, en el orden en que los exporta el sistema de ventas. La lista de bonus buscaba primero las ventas de cada puesto y después el nombre. En E2, copiada hasta E4:
=K.ESIMO.MAYOR($B$2:$B$11;D2)
Ventas del T3 y la lista de bonus de los tres primeros
Los diez comerciales de Halberd Office Supply en A1:G11, en el orden en que los exporta el sistema de ventas. La columna C es un puesto con desempate. D:G es la lista de bonus: E busca las ventas de cada puesto con K.ESIMO.MAYOR, F busca el nombre con INDICE/COINCIDIR y G lo busca por el puesto único.
fxLas celdas con fórmulas están resaltadas en verde
Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas
K.ESIMO.MAYOR acierta en todas las filas: 184.250 y luego 171.900 dos veces, porque dos comerciales vendieron de verdad 171.900 $. El nombre sale de F2, copiada hacia abajo:
=INDICE($A$2:$A$11;COINCIDIR(E2;$B$2:$B$11;0))
F3 y F4 dicen Brennan. Nóminas pagó la lista tal cual, así que Brennan cobró dos bonus y Lindqvist ninguno. Nadie se dio cuenta hasta que Lindqvist preguntó.
Escenario: Escribe los diez comerciales y sus ventas en A2:B11, del 1 al 3 en D2:D4, y las fórmulas con K.ESIMO.MAYOR e INDICE/COINCIDIR en E2:F4. Comprueba que F3 y F4 devuelven Brennan.
02Toda Búsqueda se Para en la Primera Coincidencia
COINCIDIR con 0 recorre la lista de arriba abajo y devuelve la posición de la primera celda que coincide. Las 171.900 de Brennan están en la fila 3 y las de Lindqvist en la 6, así que la búsqueda se para siempre en Brennan.
| Puesto | K.ESIMO.MAYOR | Posición | Nombre |
|---|---|---|---|
| 1 | 184.250 | 4 | Okafor |
| 2 | 171.900 | 2 | Brennan |
| 3 | 171.900 | 2 | Brennan |
Cambiar de función no sirve. BUSCARX(E4;$B$2:$B$11;$A$2:$A$11) y BUSCARV también devuelven la primera coincidencia. A la búsqueda se le hace dos veces la misma pregunta y da dos veces la misma respuesta.
Los valores de la columna E no pueden marcar la diferencia, porque unas ventas no dicen de qué fila vienen.
La solución tiene que dar a cada fila algo único que buscar, o dejar de buscar.
Escenario: Escribe =COINCIDIR(E4;$B$2:$B$11;0) en H4 y comprueba que devuelve 2. Después cambia B3 a 171899 y mira cómo F4 pasa a Lindqvist.
03Desempata con CONTAR.SI en Cualquier Versión
Da a cada comercial un puesto que no comparta nadie. Cuenta cuántas veces ha aparecido ya el valor más arriba y súmalo al puesto normal. En C2, copiada hasta C11:
=JERARQUIA.EQV(B2;$B$2:$B$11)+CONTAR.SI($B$2:B2;B2)-1
JERARQUIA.EQV da un 2 a los dos empatados. El rango de CONTAR.SI crece al copiarla hacia abajo, así que cuenta 1 para Brennan y 2 para Lindqvist. Brennan se queda en el 2 y Lindqvist pasa al 3.
| Comercial | Ventas | JERARQUIA.EQV | CONTAR.SI | Puesto único |
|---|---|---|---|---|
| Okafor | 184.250 | 1 | 1 | 1 |
| Brennan | 171.900 | 2 | 1 | 2 |
| Lindqvist | 171.900 | 2 | 2 | 3 |
| Marsh | 168.400 | 4 | 1 | 4 |
Ahora busca el puesto en la columna de puestos en lugar de las ventas. En G2, copiada hasta G4:
=INDICE($A$2:$A$11;COINCIDIR(D2;$C$2:$C$11;0))
Devuelve Okafor, Brennan y Lindqvist. La fila anterior gana el empate, y eso es una regla, no un accidente; la sección 5 explica cuándo conviene otra.
Escenario: Escribe la fórmula con JERARQUIA.EQV y CONTAR.SI en C2 y cópiala hasta C11. Comprueba que C3 da 2 y C6 da 3, y escribe la fórmula con INDICE/COINCIDIR en G2:G4.
04Ordena las Filas en Microsoft 365
Con matrices dinámicas puedes saltarte la búsqueda. Ordena toda la tabla por ventas y quédate con las tres primeras filas:
=TOMAR(ORDENAR(A2:B11;2;-1);3)
Derrama tres filas con nombre y ventas: Okafor, Brennan y Lindqvist. Cada fila del resultado es una fila real del origen, así que un nombre no puede salir dos veces salvo que esté dos veces en los datos.
ORDENAR mantiene las filas empatadas en el orden de origen, así que Brennan va antes que Lindqvist, la misma regla que el desempate con CONTAR.SI.
En Excel 2021, que tiene ORDENAR pero no TOMAR, escribe =ORDENAR(A2:B11;2;-1) y lee las tres primeras filas.
Para desempatar con un criterio tuyo, como menos descuentos, ordena por dos columnas con ORDENARPOR. Así la lista dice por qué un empatado va primero.
Escenario: Escribe =TOMAR(ORDENAR(A2:B11;2;-1);3) en I2. Comprueba que derrama Okafor, Brennan y Lindqvist con sus ventas, y cambia el 3 por un 4 para ver entrar a Marsh.
05Decide Qué Significa un Empate en el Corte
Un empate en el segundo puesto tiene respuesta fácil: los dos están entre los tres primeros. Un empate en el tercer puesto es una cuestión de política, y la fórmula debe seguir la política, no decidirla.
Imagina que Marsh también hubiera vendido 171.900 $. Tres comerciales comparten el segundo puesto y solo quedan dos plazas. Las dos soluciones ya no coinciden:
| Política | Fórmula | Nombres |
|---|---|---|
| Tres exactos, gana la fila anterior | =TOMAR(ORDENAR(A2:B11;2;-1);3) | 3 |
| Todos los que llegan al tercer puesto | =FILTRAR(A2:A11;B2:B11>=K.ESIMO.MAYOR(B2:B11;3)) | 4 |
La versión con FILTRAR paga a todos los que alcanzan la tercera venta más alta. Sus nombres salen en el orden de origen.
Para ordenarlos, usa =LET(s;ORDENAR(A2:B11;2;-1);FILTRAR(s;INDICE(s;;2)>=K.ESIMO.MAYOR(B2:B11;3))).
Ninguna lista está mal. Un bonus a cuatro cuesta 24.000 $ frente a un presupuesto de 18.000 $, y uno a tres deja sin cobrar a un comercial con las mismas ventas. Escribe la regla al lado de la lista.
Escenario: Escribe la fórmula con FILTRAR en K2 y cambia B8 a 171900. Comprueba que derrama cuatro nombres mientras la fórmula con TOMAR sigue mostrando tres.
06Busca Nombres Repetidos Antes de Enviarla
La lista rota parecía terminada. Una comprobación de una celda la habría detectado: cuenta cuántos nombres de la lista aparecen más de una vez.
=SUMAPRODUCTO(--(CONTAR.SI(F2:F4;F2:F4)>1))
En la lista rota devuelve 2, por los dos Brennan. En la lista corregida de G2:G4 devuelve 0. En Microsoft 365, =FILAS(UNICOS(F2:F4)) devuelve 2 cuando debería devolver 3.
Pon la comprobación junto a la lista y dale formato rojo cuando pase de 0. Cuesta una celda y funciona sea cual sea la forma de montar la lista.
Escenario: Escribe la comprobación con SUMAPRODUCTO debajo de la lista de bonus, apuntando a F2:F4. Comprueba que devuelve 2, apúntala a G2:G4 y comprueba que devuelve 0.
07Cinco Trampas
- Buscar un valor que comparten dos filas.
K.ESIMO.MAYORconINDICE/COINCIDIR,BUSCARXoBUSCARVdevuelve siempre la primera fila. Busca algo único. - Arreglarlo buscando desde abajo.
BUSCARXcon modo de búsqueda -1 encuentra a Lindqvist en el puesto 3, y otra vez a Lindqvist en el 2. - Sumar una cantidad mínima para desempatar.
=B2+FILA()/1000000cambia las ventas que informas y sigue fallando cuando las diferencias son pequeñas. Ordena por puesto, no toques el dinero. - Usar JERARQUIA.MEDIA para desempatar. Da 2,5 a cada empatado, y ningún número de puesto coincide. Usa
JERARQUIA.EQV. - Ordenar una columna con números guardados como texto.
K.ESIMO.MAYORyJERARQUIA.EQVse saltan las ventas en texto, y un comercial puede desaparecer de la lista. Compruébalo con=CONTAR(B2:B11).
08Cuatro Ejercicios
- Reprodúcelo. Monta la hoja y confirma que la lista con
INDICE/COINCIDIRdice Okafor, Brennan, Brennan. - Corrígelo en cualquier versión. Añade el puesto único en la columna C y confirma que G4 devuelve Lindqvist.
- Corrígelo en Microsoft 365. Confirma que
=TOMAR(ORDENAR(A2:B11;2;-1);3)devuelve tres comerciales distintos. - Mueve el empate. Pon a Marsh en 171.900 y compara las listas con
TOMARy conFILTRAR. Anota cuál pide tu regla de bonus.
Qué Llevarse
Todos los números de la hoja de bonus de Halberd eran correctos. El error fue pedir a una búsqueda que distinguiera dos números iguales, algo que no puede hacer.
Cuando los valores pueden empatar, da a cada fila un puesto único que buscar, u ordena las propias filas con ORDENAR y TOMAR. Decide qué significa un empate en el corte y revisa la lista final en busca de nombres repetidos.