01 / 11La Hoja Puso Primera la Oferta Más Cara

SUMAPRODUCTOPromedios PonderadosJERARQUIA

Matriz de puntuación ponderada en Excel con SUMAPRODUCTO

La Hoja de Puntuación del Consorcio Recomendó la Oferta de Limpieza Más Cara con un 9,60 y Dejó la Más Barata en Cuarto Lugar, Porque el Precio se Puntuó Como Si Más Fuera Mejor

3 nov 202610 min de lectura

UsaSUMAPRODUCTOMIN, MAXJERARQUIASI

La hoja de licitación del consorcio escolar Ashby Valley puso primera la oferta de limpieza de Northgate, con un 9,60. Debería haber quedado última. Una sola fórmula puntuó el precio y el plazo de arranque como si un número mayor fuera una oferta mejor.

Licitación de limpieza, otoño de 2026Cifra
Ofertas puntuadas5
Nota de Northgate en la hoja enviada9,60, primera
Nota de Northgate, corregida7,43, última
Nota de Cleanline en la hoja enviada6,93, cuarta
Nota de Cleanline, corregida9,10, primera
Sobrecoste de la oferta recomendada130.000 £ al año

Alcance. SUMAPRODUCTO, MIN, MAX, SUMA, SI, REDONDEAR, CONTAR.SI.CONJUNTO y JERARQUIA.EQV funcionan en todas las versiones de Excel desde 2010, en Windows, Mac y la web.

En todo el artículo las ofertas están en A1:E6: proveedor en A, oferta anual en libras en B, nota de calidad sobre 10 en C, plazo de arranque en semanas en D y nota de referencias sobre 10 en E. Las ponderaciones están en B7:E7: 40 %, 30 %, 20 % y 10 %.

Cada criterio se convierte en una nota sobre 10 en K2:N6, en el mismo orden que las ponderaciones: precio en K, calidad en L, arranque en M y referencias en N.

01La Hoja Puso Primera la Oferta Más Cara

El responsable de compras montó el bloque de notas una sola vez. Calidad y referencias llegaron como notas del tribunal sobre 10, así que L y N las copiaron tal cual. El precio y el arranque van en libras y en semanas, y K y M los convirtieron en una fracción del mayor:

=B2/MAX($B$2:$B$6)*10
=D2/MAX($D$2:$D$6)*10

La columna F ponderaba las cuatro notas con =SUMAPRODUCTO($B$7:$E$7;K2:N2) y la G las ordenaba con =JERARQUIA.EQV(F2;$F$2:$F$6). Northgate salió primera con 9,60.

Cinco Ofertas, Cuatro Ponderaciones y la Más Cara Primera

La hoja de la licitación de limpieza del consorcio escolar Ashby Valley, en A1:I7. La columna B guarda cada oferta anual en libras, C la nota de calidad sobre 10, D las semanas de arranque y E la nota de referencias sobre 10. La fila 7 guarda las ponderaciones. Las columnas F y G son la nota ponderada y el puesto de la hoja enviada, con precio y arranque puntuados como x/MAX; H e I son lo mismo con ambos puntuados como MIN entre el valor.

ABCDEFGHI
1
Supplier
Annual bid (£)
Quality /10
Mobilisation (wks)
References /10
Score as sent
Rank as sent
Score fixed
Rank fixed
2
Brightway
420000
7
6
8
7.66
3
7.66
4
3
Cleanline
360000
8
4
7
6.93
4
9.1
1
4
Northgate
490000
9
9
9
9.6
1
7.43
5
5
Sparkle
375000
6
5
6
6.57
5
7.84
2
6
Tidewell
405000
8
8
8
8.28
2
7.76
3
7
Weight
40%
30%
20%
10%

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

La oferta de Northgate era la más cara, 490.000 £ al año, y la más lenta en arrancar, nueve semanas. Cleanline costaba 130.000 £ menos y podía empezar en cuatro.

Quedó cuarta, pidió una reunión de explicación y descubrió que en precio había sacado un 7,35 sobre 10.

02x/MAX Da la Nota Máxima al Número Mayor

Dividir por el máximo da un 10 al valor más grande, sea cual sea. Eso es correcto para una nota de calidad, donde más es mejor. En un precio o un plazo, el valor más grande es la peor oferta, y se llevó la mejor nota.

ProveedorOferta anualNota de precio, x/MAXNota de precio, MIN/x
Cleanline360.000 £7,3510,00
Sparkle375.000 £7,659,60
Tidewell405.000 £8,278,89
Brightway420.000 £8,578,57
Northgate490.000 £10,007,35

El arranque fue igual. Las cuatro semanas de Cleanline sacaron 4,44 y las nueve de Northgate, 10,00. Entre las dos columnas suman el 60 % del peso, así que el orden de toda la tabla se dio la vuelta.

Escenario: Pon las cinco ofertas en A2:E6 y escribe =B2/MAX($B$2:$B$6)*10 en K2, copiada hacia abajo. Comprueba que K3, la de Cleanline, da 7,35 y que K4, la de Northgate, da 10,00.

Pruébalo en la cuadrícula

03Puntúa lo de Menor Es Mejor Como MIN Entre el Valor

Dale la vuelta a la fracción. El valor más bajo va arriba, así que la oferta más barata saca un 10 y las demás puntúan en proporción a lo cerca que quedan:

=MIN($B$2:$B$6)/B2*10
=MIN($D$2:$D$6)/D2*10

Una oferta que dobla el precio más bajo saca un 5. Las cuatro semanas de Cleanline pasan a 10,00 y las nueve de Northgate a 4,44. Calidad y referencias mantienen sus notas, y los totales cambian de sitio.

ProveedorNota enviadaPuesto enviadoNota corregidaPuesto corregido
Cleanline6,9349,101
Sparkle6,5757,842
Tidewell8,2827,763
Brightway7,6637,664
Northgate9,6017,435

Lee antes los pliegos. Algunas licitaciones fijan la fórmula del precio, y la publicada es la que hay que usar.

Otra versión habitual, =(MAX($B$2:$B$6)-B2)/(MAX($B$2:$B$6)-MIN($B$2:$B$6))*10, da un 0 a la oferta más cara y estira las diferencias pequeñas de precio, así que por sí sola puede cambiar la ganadora.

Escenario: Sustituye K2 por =MIN($B$2:$B$6)/B2*10 y M2 por =MIN($D$2:$D$6)/D2*10, copiadas hacia abajo. Comprueba que F3, la de Cleanline, da ahora 9,10 y que G3 da 1.

Pruébalo en la cuadrícula

04Comprueba Que el Mejor Valor Saca la Nota Más Alta

Una comprobación de una celda detecta el sentido antes de que nadie lea la clasificación. En una columna de menor es mejor, la fila con el valor más bajo debe tener la nota máxima. SUMAPRODUCTO puede traer esa nota sin una búsqueda:

=SUMAPRODUCTO(($B$2:$B$6=MIN($B$2:$B$6))*K2:K6)

En la hoja enviada devuelve 7,35, que no es la nota de precio más alta. Tras la corrección devuelve 10. Escribe la misma prueba para el arranque sobre la columna M, y para la calidad con MAX en lugar de MIN.

Escenario: Escribe =SUMAPRODUCTO(($B$2:$B$6=MIN($B$2:$B$6))*K2:K6)=MAX(K2:K6) en K8. Comprueba que da FALSO con la fórmula antigua y VERDADERO con la corregida.

Pruébalo en la cuadrícula

05La Única Fila Que Cuadraba

La responsable financiera sí revisó la hoja. Eligió Brightway, la oferta del medio, y cuadraba con las dos fórmulas. Fue mala suerte, pero con una causa matemática.

Las dos fórmulas dan la misma nota cuando el valor está en la media geométrica del más bajo y el más alto. Las 420.000 £ de Brightway son justo eso para 360.000 y 490.000, y sus seis semanas lo son para cuatro y nueve.

Brightwayx/MAXMIN/x
Nota de precio8,578,57
Nota de arranque6,676,67
Total7,667,66

Revisa los extremos. La oferta más barata y la más cara son las filas que un error de sentido mueve más, y por eso la prueba de la sección 4 mira el mínimo.

Escenario: Escribe =B2/MAX($B$2:$B$6)*10 en P2 y =MIN($B$2:$B$6)/B2*10 en Q2, copiadas hacia abajo. Comprueba que las dos columnas solo coinciden en la fila 2 y compara después las filas 3 y 4.

Pruébalo en la cuadrícula

06Comprueba Que las Ponderaciones Suman el 100 %

SUMAPRODUCTO multiplica por las ponderaciones que reciba. Si suman el 110 %, todas las notas salen infladas y ninguna da error. Alguien añade un criterio de sostenibilidad del 10 % y olvida quitárselo a otro.

Haz que el total se niegue a calcular cuando las ponderaciones están mal:

=SI(REDONDEAR(SUMA($B$7:$E$7);6)<>1;"Ponderaciones ≠ 100 %";SUMAPRODUCTO($B$7:$E$7;K2:N2))

El REDONDEAR importa. Los porcentajes se guardan como fracciones binarias, así que unas ponderaciones correctas pueden sumar una pizca menos de 1. Un simple <>1 podría entonces rechazar ponderaciones que están bien.

Escenario: Escribe la fórmula protegida en F2 y cópiala hacia abajo. Cambia C7 de 30 % a 40 % y comprueba que todas las notas se convierten en el aviso; luego deja el valor como estaba.

Pruébalo en la cuadrícula

07Mantén las Notas en el Orden de las Ponderaciones

SUMAPRODUCTO empareja la primera ponderación con la primera nota, la segunda con la segunda, y así sucesivamente. No lee los encabezados. Si el bloque de notas pone la calidad antes que el precio, la calidad se queda en silencio con el 40 %.

Fila de ponderacionesBloque de notas previstoBloque con columnas cambiadas
40 %PrecioCalidad
30 %CalidadPrecio
20 %ArranqueArranque
10 %ReferenciasReferencias

Coloca el bloque de notas exactamente en el orden de la fila de ponderaciones, con los mismos encabezados encima de ambos. Así, una columna insertada en un lado y no en el otro se ve de un vistazo.

Escenario: Intercambia el contenido de las columnas K y L, para que la calidad vaya primero. Comprueba que F4, la de Northgate, pasa de 7,43 a 7,59 sin un solo error, y vuelve a dejarlas como estaban.

Pruébalo en la cuadrícula

08Clasifica con la Nota Completa, No con la Redondeada

JERARQUIA.EQV da el mismo puesto a las notas empatadas y se salta el siguiente. Si redondeas antes, puedes crear un empate que no existía. Con un decimal, el 7,84 de Sparkle y el 7,76 de Tidewell se leen ambos 7,8.

ProveedorNota corregidaCon un decimalPuesto redondeado
Sparkle7,847,82
Tidewell7,767,82
Brightway7,667,74

Clasifica la nota sin redondear de la columna F y limítate a darle formato con dos decimales. Si dos ofertas empatan de verdad, desempata con una regla que fijen los pliegos, como el precio más bajo, y no con el orden de las filas.

Para un puesto único que recurre al orden de las filas sirve =JERARQUIA.EQV(F2;$F$2:$F$6)+CONTAR.SI.CONJUNTO($F$2:F2;F2)-1. Úsalo solo donde las reglas lo permitan.

09Cinco Trampas

  1. x/MAX en un coste. Da la nota máxima al precio más alto o al plazo más largo.
  2. Revisar la fila del medio. Un valor en la media geométrica puntúa igual con las dos fórmulas.
  3. Ponderaciones que no suman. SUMAPRODUCTO multiplica por un 110 % sin quejarse.
  4. Un bloque de notas en otro orden. SUMAPRODUCTO empareja por posición, no por encabezado.
  5. Clasificar notas redondeadas. Un decimal puede crear un empate que las notas completas no tienen.

10Cuatro Ejercicios

  1. Reprodúcelo. Puntúa las cinco ofertas con x/MAX y confirma que Northgate sale primera con 9,60.
  2. Corrígelo. Reescribe precio y arranque como MIN entre el valor y confirma que Cleanline encabeza con 9,10.
  3. Pruébalo. Añade la comprobación de la sección 4 a las dos columnas de menor es mejor.
  4. Protégelo. Añade el control de ponderaciones y el puesto sin redondear; después cambia una ponderación y mira cómo aparece el aviso.

Qué Llevarse

Todas las ofertas de la hoja del consorcio estaban bien escritas, y todas las ponderaciones también. Una fórmula copiada en horizontal dio por hecho que más siempre es mejor, y dos de los cuatro criterios funcionan al revés.

Antes de ponderar nada, anota en qué sentido va cada criterio. Después comprueba que la oferta más barata tiene la nota de precio más alta antes de que nadie lea la clasificación.

Comparte este artículo:
Volver al Blog

Practícalo

SUMAPRODUCTO

Un ejercicio en una cuadrícula real

Abrir el ejercicio

Las funciones que usa este artículo

Sintaxis, ejemplos resueltos y los errores que esperar: una página de referencia para cada una.

Sigue leyendo

  1. Cómo detectar valores fijos en fórmulas de ExcelLeer el artículo
  2. Sumar y contar celdas por color en ExcelLeer el artículo
  3. Promedio de promedios en Excel: por qué tu porcentaje fallaLeer el artículo
Reto diario · Día 107

Suma los kilómetros de un periodo de liquidación con SUMAR.SI.CONJUNTO

Un ejercicio nuevo cada día, resuelto en una cuadrícula real.

Resolver el reto de hoy