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.
