01 / 09REDONDEAR Pide la Caja Más Cercana, No Bastante

RedondeoMULTIPLO.SUPERIOR y REDOND.MULTAuditoría de Fórmulas

Redondear a cajas completas en Excel con REDONDEAR.MAS

Cinco Líneas del Pedido de la Cafetería Llegaron Cortas, Porque REDONDEAR Compraba la Caja Más Cercana y No las Cajas Suficientes

Hollins Lane Kitchen hace su pedido al mayorista cada lunes, en cajas completas. La hoja dividía cada faltante entre el tamaño de la caja y redondeaba a la caja más cercana, así que cinco de diez líneas llegaron por debajo de lo que necesitaba la semana.

ArtículoFaltanCajaREDONDEAR pidióUnidades de menos
Leche de avena 1L2664 cajas2
Huevos camperos3403011 cajas10
Mantequilla 250g95402 cajas15
Servilletas1.1505002 cajas150
Nata para montar 1L1462 cajas2

Alcance. REDONDEAR, REDONDEAR.MAS, ENTERO, COCIENTE, MAX, REDOND.MULT, MULTIPLO.SUPERIOR, CONTAR.SI y SUMAPRODUCTO funcionan en todas las versiones actuales de Excel. MULTIPLO.SUPERIOR.MAT necesita Excel 2013 o posterior.

En Excel 2007 y anteriores, MULTIPLO.SUPERIOR devuelve #¡NUM! con un número negativo y una caja positiva. Las versiones posteriores lo redondean hacia cero.

01REDONDEAR Pide la Caja Más Cercana, No Bastante

La hoja lista diez artículos en A2:D11: lo que necesita la semana en B, lo que hay en la estantería en C y el tamaño de la caja en D. La fórmula antigua en E2, copiada hasta E11:

=REDONDEAR((B2-C2)/D2;0)

El pedido del lunes en cajas completas

El pedido de diez líneas de Hollins Lane Kitchen en A1:H11. E es la fórmula antigua con REDONDEAR y F las unidades que sobran o faltan: un hueco negativo es una línea que se agotó. G es REDONDEAR.MAS sobre el faltante limitado con MAX, y H las unidades que sobran al llegar.

ABCDEFGH
1
Item
Needed
In stock
Pack size
ROUND cases
Gap (units)
Cases needed
Spare (units)
2
Oat milk 1L
38
12
6
4
-2
5
4
3
Free-range eggs
420
80
30
11
-10
12
20
4
Butter 250g
120
25
40
2
-15
3
25
5
Coffee beans 1kg
30
8
6
4
2
4
2
6
Sourdough loaves
64
0
8
8
0
8
0
7
Paper napkins
1500
350
500
2
-150
3
350
8
Double cream 1L
20
6
6
2
-2
3
4
9
Takeaway cups
3000
400
1000
3
400
3
400
10
Lemons
50
5
12
4
3
4
3
11
Sugar sachets
800
1000
1000
0
200
0
200

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

Faltan 26 bricks de leche de avena y vienen de seis en seis, así que salen 4,33 cajas. REDONDEAR pide 4, que son 24 bricks. Cualquier fracción menor de media caja se redondea hacia abajo, y esa parte del faltante nunca se pide.

Los limones, con 3,75 cajas, y los vasos, con 2,6, se redondean hacia arriba, así que esas líneas parecían correctas.

Media hoja acertaba por suerte, y por eso nadie sospechó de la fórmula.

Escenario: Escribe 38 en B2, 12 en C2 y 6 en D2, y después =REDONDEAR((B2-C2)/D2;0) en E2. Comprueba que devuelve 4, y que =E2*D2 da 24 bricks frente a un faltante de 26.

Pruébalo en la cuadrícula

02Cada Forma de Cortar un Número Pide Distinto

Excel tiene varias formas de convertir 4,33 cajas en un número entero, y solo las que redondean hacia arriba sirven para un pedido. La fila del azúcar, donde el stock ya supera la necesidad, muestra una segunda trampa.

FórmulaAvena, 26 ÷ 6Café, 22 ÷ 6Azúcar, −200 ÷ 1000
REDONDEAR(x;0)440
ENTERO(x)43−1
COCIENTE(unid;caja)430
REDONDEAR.MAS(x;0)54−1
MULTIPLO.SUPERIOR(unid;caja)/caja540

ENTERO y COCIENTE cuentan solo cajas completas. Es la respuesta correcta a "cuántas cajas completas hay en la estantería" y la equivocada a "cuántas tengo que comprar". REDONDEAR acierta más o menos la mitad de las veces.

Escenario: Escribe =ENTERO(22/6), =COCIENTE(22;6) y =REDONDEAR.MAS(22/6;0) en tres celdas. Comprueba que las dos primeras devuelven 3 y la última 4, el pedido de café que la cafetería necesita de verdad.

Pruébalo en la cuadrícula

03REDONDEAR.MAS Siempre Cubre el Faltante

REDONDEAR.MAS con 0 decimales lleva cualquier fracción al siguiente número entero. 4,01 cajas pasan a 5, y 4,99 también. En G2, copiada hasta G11:

=REDONDEAR.MAS((B2-C2)/D2;0)

La leche de avena pasa a 5 cajas, los huevos a 12, la mantequilla a 3, las servilletas a 3 y la nata a 3. Las cinco líneas cortas llegan ahora con algunas unidades de sobra, y las demás no cambian.

ArtículoCajas REDONDEARCajas REDONDEAR.MASUnidades de sobra
Leche de avena 1L454
Huevos camperos111220
Mantequilla 250g2325
Servilletas23350
Nata para montar 1L234

Cinco cajas más es todo lo que cuesta no quedarse sin nada. Las unidades sobrantes van a la estantería y se descuentan del faltante del lunes siguiente.

Escenario: Escribe =REDONDEAR.MAS((B2-C2)/D2;0) en G2 y cópiala hasta G11. Comprueba que G2 devuelve 5 y G3 devuelve 12, y después escribe =SUMA(G2:G11) y comprueba que da 44, no las 45 que esperas.

Pruébalo en la cuadrícula

04Un Excedente Pidió Menos Una Caja

Los sobres de azúcar necesitan 800 y en la estantería hay 1.000, así que el faltante es −200. REDONDEAR.MAS redondea alejándose de cero, así que −0,2 cajas pasan a −1, no a 0.

El REDONDEAR antiguo lo escondía porque caía en 0. La corrección lo sacó a la luz: G11 mostraba −1, y el total del pedido bajó de 45 cajas a 44 sin que nadie borrara una línea.

Limita el faltante a cero antes de dividir. La fórmula corregida en G2, copiada hasta G11:

=REDONDEAR.MAS(MAX(0;B2-C2)/D2;0)

MAX(0;B2-C2) convierte cualquier excedente en 0, así que una línea con stock suficiente no pide nada. Con =REDONDEAR.MAS(MAX(0;B2-C2)/D2;0) en su sitio, G11 devuelve 0 y la columna suma 45.

Escenario: Escribe 800 en B11, 1000 en C11 y 1000 en D11. Comprueba que =REDONDEAR.MAS((B11-C11)/D11;0) devuelve −1, después envuelve el faltante en MAX(0;B11-C11) y comprueba que devuelve 0.

Pruébalo en la cuadrícula

05MULTIPLO.SUPERIOR Lleva las Unidades a la Caja

A veces el pedido se hace en unidades, no en cajas: 30 bricks en lugar de 5 cajas. MULTIPLO.SUPERIOR redondea un número hacia arriba hasta el siguiente múltiplo del tamaño de la caja, sin dividir:

=MULTIPLO.SUPERIOR(MAX(0;B2-C2);D2)

Devuelve 30 para la leche de avena, 360 para los huevos y 1.500 para las servilletas. Divide entre D2 para obtener cajas, y coinciden con REDONDEAR.MAS en todas las filas.

MULTIPLO.SUPERIOR.MAT funciona igual con números positivos y es el nombre moderno. En cualquier caso, mantén el MAX: deja la regla escrita en lugar de depender de cómo se redondee un número negativo.

REDOND.MULT redondea al múltiplo más cercano, así que tiene el mismo defecto que REDONDEAR. =REDOND.MULT(26;6) devuelve 24.

Escenario: Escribe =MULTIPLO.SUPERIOR(MAX(0;B2-C2);D2) en una columna libre y cópiala hacia abajo. Comprueba que devuelve 30 para la leche de avena y 360 para los huevos, y después divide cada uno entre su caja y compáralo con la columna G.

Pruébalo en la cuadrícula

06Comprueba Que Cada Línea Cubre la Necesidad

Un total no te dice que una línea se queda corta. Cuenta las líneas en las que el pedido no llega al faltante:

=SUMAPRODUCTO(--(G2:G11*D2:D11<B2:B11-C2:C11))

Devuelve 0 con la columna corregida. Apúntala a la columna antigua E, la de REDONDEAR, y devuelve 5, las cinco líneas que se agotaron.

Una segunda comprobación detecta el error contrario, un pedido negativo:

=CONTAR.SI(G2:G11;"<0")

Devuelve 0 cuando el MAX está puesto, y 1 con la primera corrección de REDONDEAR.MAS. Pon las dos comprobaciones encima del pedido, para que se vean antes de enviarlo.

Escenario: Escribe la comprobación con SUMAPRODUCTO en J2 y apúntala a la columna E. Comprueba que devuelve 5, después cambia E2:E11 por G2:G11 y comprueba que devuelve 0.

Pruébalo en la cuadrícula

07Cinco Trampas

  1. Redondear a la caja más cercana. REDONDEAR y REDOND.MULT descartan cualquier fracción menor de la mitad. Un pedido necesita REDONDEAR.MAS o MULTIPLO.SUPERIOR.
  2. Contar cajas completas con ENTERO o COCIENTE. Responden a lo que hay en la estantería, no a lo que hay que comprar, y dejan 3,67 cajas en 3.
  3. Dejar que un excedente sea negativo. REDONDEAR.MAS convierte −0,2 en −1. Envuelve antes el faltante en MAX(0;...).
  4. Redondear necesidad y stock por separado. =REDONDEAR.MAS(B5/D5;0)-REDONDEAR.MAS(C5/D5;0) devuelve 3 cajas de café para un faltante de 22 bolsas. Resta primero y redondea una sola vez.
  5. Mirar solo el total. 40 cajas parece una semana normal. Cuenta las líneas cortas con SUMAPRODUCTO, fila a fila.

08Cuatro Ejercicios

  1. Reprodúcelo. Monta la hoja con REDONDEAR y confirma que cinco líneas llegan cortas.
  2. Corrígelo. Cambia a REDONDEAR.MAS y confirma que el pedido sube de 40 cajas a 44, con el azúcar en −1.
  3. Limítalo. Añade MAX(0;...) y confirma que el azúcar devuelve 0 y el pedido suma 45.
  4. Hazlo por unidades. Reconstruye la columna G con MULTIPLO.SUPERIOR sobre las unidades y confirma que todas las filas coinciden.

Qué Llevarse

Cada necesidad, cada recuento de stock y cada tamaño de caja de la hoja de la cafetería era correcto. REDONDEAR pedía la caja más cercana, así que cualquier faltante menor de media caja nunca se compraba.

Pide con REDONDEAR.MAS o MULTIPLO.SUPERIOR, limita los excedentes con MAX(0;...) y cuenta las líneas cortas antes de enviar el pedido.

Comparte este artículo:
Volver al Blog

Practícalo

MULTIPLO.SUPERIOR, MULTIPLO.INFERIOR

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. Solver en Excel: optimizar con restriccionesLeer el artículo
  2. Sumar horas en Excel: más de 24 y horas extraLeer el artículo
  3. Cómo detectar valores fijos en fórmulas de ExcelLeer 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