Serie Ejercicios de Excel Avanzado
|
Ejercicio #3 Nivel de dificultad: 6/10
Obtener cajas completas y latas sobrantes de un proceso de empacado |
 Índice de contenido |
PLANTEAMIENTO DEL PROBLEMA
Figura 1.
En un determinado proceso se utilizan cajas en las que se empacan 48 unidades de productos para llenar completamente una caja.
Usaremos el siguiente ejemplo para ilustrar la convención utilizada por los empleados para registrar la cantidad de cajas que han sido completadas, con sus respectivos productos sobrantes:
60,01 cajas
Esto significa 60 cajas completas y 1 producto sobrante.
60,1 cajas
Esto significa 60 cajas completas y 10 productos sobrantes.
60,10 cajas
Esto significa 60 cajas completas y 10 productos sobrantes.
Es decir, la parte entera representa la cantidad de cajas completas, mientras que la parte decimal representa los productos sobrantes.
Se requiere desarrollar fórmulas que permitan calcular la cantidad total de cajas que están completamente llenas y la cantidad total de productos sobrantes.
Puedes DESCARGAR AQUÍ el libro de Excel con los datos.
SOLUCIÓN A ESTE PROBLEMA DE EXCEL
Funciones a utilizar:
- ENTERO
- SI.ERROR
- ENCONTRAR
- EXTRAE
- VALOR
- RESIDUO
Paso 1. Cálculo de cajas completas en cada valor reportado
En una columna vamos a crear una fórmula que utiliza a la función ENTERO para obtener la parte entera de cada valor reportado de cantidad de cajas (columna D en la Figura 1). Esta es la fórmula:
=ENTERO(D3)
Figura 2.
Paso 2. Cálculo de productos sobrantes en cada valor reportado
De acuerdo con el planteamiento del problema, para obtener los productos sobrantes en cada valor de cantidad de cajas reportado, necesitamos separar completamente la parte decimal, sin el símbolo separador de decimales, en cada valor.
Este cálculo puede ser hecho de dos maneras diferentes:
Método 1: Mediante una combinación de varias funciones de Excel
Método 2: Sin ninguna función de Excel
¡Veamos cada una!
Método 1: Mediante una combinación de varias funciones de Excel
Aquí vamos a hacer uso de las siguientes funciones de Excel: VALOR, ENCONTRAR, EXTRAE, SI.ERROR
El método 1 se lleva a cabo mediante dos pasos:
(a) Encontrar la posición del separador decimal
En nuestro ejemplo, el separador decimal es la coma (“,”). Vamos a utilizar la función ENCONTRAR para determinar la posición de la coma en cada valor de cantidad de cajas reportado, de esta forma:
=ENCONTRAR(“,“;D3)
Figura 3.
Como podrás observar, la fórmula anterior presenta un detalle. Y es que cuando la cantidad de cajas no tiene parte “decimal”, la fórmula arrojará un error puesto que se produce un error en la función ENCONTRAR al no hallar la coma. Eso debemos tenerlo presente en la fórmula final para el cálculo de productos sobrantes.
(b) Obtener la parte decimal de cada cantidad de cajas registrada
Ahora, como ya sabemos dónde se halla la coma en una cantidad de cajas reportada con parte decimal, podemos separar la parte decimal, mediante esta fórmula:
=EXTRAE(D3;G3+1;2)
Figura 4.
Nuevamente, el detalle con esta fórmula es que si el valor reportado de cantidad de cajas no posee parte decimal, entonces también se producirá un error. Procedemos entonces a solventar el error utilizando la función SI.ERROR, reescribiendo la fórmula anterior, de esta forma:
=SI.ERROR(EXTRAE(D3;G3+1;2);0)
El valor cero que ves al final es el valor que devolverá la función SI.ERROR cuando se produzca un error en la función EXTRAE.
Pero, aquí no termina todo en cuanto a obtener la parte decimal. Debes tener presente que el valor devuelto por la función EXTRAE no es necesariamente un valor numérico, sino un texto, aunque lo veamos como número.
Para asegurarnos de obtener un valor numérico para la parte decimal, haremos uso de la función VALOR, como se muestra a continuación:
=SI.ERROR(VALOR(EXTRAE(D3;G3+1;2));0)
Ahora solo nos resta sustituir el valor de la celda G3 en la fórmula anterior, con lo cual la fórmula para separar la parte decimal nos queda finalmente así:
=SI.ERROR(VALOR(EXTRAE(D3;ENCONTRAR(“,“;D3)+1;2));0)
Figura 5.
Método 2: Sin ninguna función de Excel
Mediante este método vamos a obtener la parte decimal de cada cantidad de cajas registrada, de la siguiente manera:
Restarle a cada cantidad de cajas reportada la parte entera obtenida en el Paso 1, de esta forma:
Figura 6.
Y finalmente, para deshacernos de la coma y convertir en entero esta parte decimal, la multiplicamos por 100, de esta forma:
Figura 7.
Paso 3. Cálculo de la cantidad total de cajas llenas
El total de cajas llenas viene dado por la suma de los valores de la columna E más la cantidad de cajas que pueden ser llenadas con la suma de los productos sobrantes de la columna F.
Para determinar la cantidad de cajas que pueden llenarse con el total de productos sobrantes de la columna F si empleamos el método 1 en el paso 2 (o columna G si empleamos el método 2. Ver Figura 7), emplearemos la siguiente fórmula:
=ENTERO(SUMA(F:F)/48)
La fórmula anterior suma todos los valores de la columna F y divide dicho resultado entre 48, puesto que en cada caja caben máximo 48 productos. Finalmente, de dicha división tomamos la parte entera mediante el uso de la función ENTERO.
Entonces, la cantidad total de cajas llenas viene dada por esta fórmula:
=SUMA(E:E) + ENTERO(SUMA(F:F)/48)
Figura 8.
Paso 4. Cálculo de la cantidad final de productos sobrantes
Para calcular la cantidad final de productos sobrantes haremos uso de la función RESIDUO para obtener el residuo de dividir el total de productos sobrantes en la columna F (o columna G si empleamos el método 2. Ver Figura 7) entre 48, como se muestra a continuación:
=RESIDUO(SUMA(F:F);48)
Figura 9.
