José Francisco Martínez Jiménez.
Caso práctico nº 11: Función SUMA.PRODUCTO (continuación del caso práctico nº 9)
Puedes descargarte este caso práctico AQUI.
Vamos a trabajar con la tabla siguiente para ver cómo se pueden resolver más preguntas utilizando otras funciones dentro de SUMA.PRODUCTO:
(1)¿De cuántos productos se han vendido una cantidad mayor o igual a 60? (Resp.: 4)
=SUMA.PRODUCTO(B2:B11>=60)
(2)¿De cuántos productos se han vendido una cantidad menor de 30? (Resp.: 4)
=SUMA.PRODUCTO(B2:B11<30)
(3)¿Cuál es el importe total de los productos de los que se han vendido una cantidad mayor o igual a 60? (Resp.: 1.846,89 €)
=SUMA.PRODUCTO(F2:F11;B2:B11>=60)
(4)¿Cuál es el importe total de los productos de los que se han vendido una cantidad mayor o igual a 60 siempre que el código del producto sea un número par? (Resp.:884,83 €)
=SUMA.PRODUCTO(F2:F11;B2:B11>=60;ESPAR(A2:A11))
(5)¿Qué importe total suma los productos cuyo código sea múltiplo de 3? (Resp.: 1.352,49 €)
=SUMA.PRODUCTO(F2:F11;RESIDUO(A2:A11;3)=0)
(6)¿Qué importe suma lo vendido en el primer trimestre de 2022? (Resp.: 1.922,96 €)
=SUMA.PRODUCTO(F2:F11;G2:G11<FECHA(2022;4;1))
(7)¿Qué importe suma lo vendido en el segundo trimestre de 2022? (Resp.: 2.044,90 €)
=SUMA.PRODUCTO(F2:F11;G2:G11<FECHA(2022;7;1);G2:G11>=FECHA(2022;4;1))
También podemos usar la función TEXTO con el formato de fecha “Q” que devuelve el trimestre de una fecha como “T1” – “T2” – “T3” – “T4”:
=SUMA.PRODUCTO(F2:F11;TEXTO(G2:G11;”Q”)=”T2”)
(8)¿Cuántos productos son de color Verde? (Resp.: 5)
=SUMA.PRODUCTO(H2:H11=”Verde”)
(9)¿Qué importe total suma lo vendido en los meses impares de color Verde? (Resp.: 827,69 €)
=SUMA.PRODUCTO(F2:F11;H2:H11=”Verde”;ESIMPAR(MES(G2:G11)))
(10)¿Qué importe total suma lo vendido siempre que los céntimos sean una cifra par? (Resp.: 2.125,14 €)
=SUMA.PRODUCTO(F2:F11;ESPAR(F2:F11*100))
(11)¿Qué importe de IVA suma el IVA del 21%? (Resp.: 318,24 €)
=SUMA.PRODUCTO(E2:E11;C2:C11=21)
(12)¿Qué importe de IVA suma el IVA distinto del 21%? (Resp.: 208,10 €)
=SUMA.PRODUCTO(E2:E11;C2:C11<>21)
Practicad y probad… Y como decía SÉNECA: “Alguna cosa sucede bien al que muchas prueba”.
Caso práctico nº 12: Turnos de trabajo
Puedes descargarte este caso práctico AQUI.
Hace ya unos cuantos años un familiar que trabajaba en un departamento de un gran hospital de Sevilla me comentó que todos los años tenían que hacer un cuadrante de los turnos de trabajo de todo el personal médico, de enfermería, auxiliares, etc, del departamento en cuestión. Cuadrante que confeccionaban a mano con el consiguiente gasto de tiempo y nunca libre de errores.
Una vez que me explicó cómo lo hacían me puse manos a la obra y este es el resultado.
Lo primero era conocer el patrón o modelo que seguían:
Lo primero era conocer el patrón o modelo que seguían:
Existían cinco turnos, nombrados del 1 al 4, más un turno de noche. Cada turno incluía a todo el personal necesario de las distintas categorías.
El ciclo de trabajo se repetía cada 16 días (no existen sábados, domingos ni días de fiesta; te corresponde trabajar o descansar independientemente del día de la semana).
Para los turnos 1 a 4 el ciclo era: dos mañanas (“M”), dos tardes (“T”), dos noches (“N”), cuatro descansos (“D”), dos mañanas, dos tardes, dos descansos y vuelta a empezar. Total 16 días.
Para el turno de noche el ciclo era: dos descansos (“D”) y dos noches (“N”) repetidos cuatro veces y vuelta a empezar. Total 16 días.
Por último era necesario conocer la fecha en que comenzaban los ciclos de todos los turnos.
Como puede observarse en la imagen siguiente todos los días, de lunes a domingo, están cubiertas las mañanas, las tardes y las noches de ese departamento en concreto.
Esta tabla es la que vamos a utilizar para todos los cálculos necesarios para crear el cuadrante anual de todos los turnos para el año que indiquemos en la hoja de cálculo.
Parte del resultado final se muestra en la imagen siguiente (solo el primer trimestre). Veamos los pasos necesarios para crearla.
Paso 1: Hojas necesarias
Paso 2: Hoja “TABLAS”
Introducimos los datos de la Imagen 2. A la celda A1 la llamamos “FECHAINICIO” (Cuadro de nombre) para que en las fórmulas empleadas, en lugar de “$TABLAS.$A$1”, figure ese nombre que es mucho más intuitivo.
Al rango B2:Q6 le aplicamos un formato condicional para ver las jornadas de trabajo más claramente (Imagen 6).
En esta hoja no hay fórmulas. Todas las celdas contienen datos simples: fecha, textos y números.
Paso 3: Hoja “TURNOS_ANUALES”
Veamos ahora los datos y fórmulas empleados para el mes de enero. Para el resto de meses es igual teniendo en cuenta las referencias absolutas, relativas o mixtas empleadas (ver en la Ayuda de LibreOffice Calc el apartado Direcciones y referencias, absolutas y relativas):
Celda A2: Contiene el año a mostrar (número sin decimales).
Celdas A4 – A5: Celdas combinadas y centradas para una visualización óptima (es como si fuera una sola celda). Contiene el nombre el mes.
Celdas A6 – A10: Contienen los nombres de los turnos exactamente del mismo modo que están escritos en la hoja TABLAS.
Celda B4: Contiene la fórmula =FECHA($A$2;1;1). El resultado es la fecha del 1 de enero de 2023. Ver función FECHA. ¿Por qué veo 01 en lugar de 01/01/2023 si es una fecha? Porque se usa el formato “DD” para visualizar sólo el día (sin el mes ni el año). Hay que tener en cuenta que la celda contiene una fecha, no un número (ver Imagen 8).
Celda C4: Contiene la fórmula =B4+1. Al sumar 1 a una fecha da como resultado la fecha del día siguiente.
Celdas D4 – AF4: Copiamos la celda C4 y la pegamos en todo ese rango. Cada celda contendrá la fecha del día siguiente a la celda anterior.
Celda B5: Contiene la fórmula =B4 (formato “NN” para visualizar las tres primeras letras del día de la semana). Ver Imagen 9.
Celdas C5 – AF5: Copiamos la celda B5 y la pegamos en todo ese rango. El rango B5 – AF5 tiene un formato condicional para resaltar los domingos. Ver Imagen 10.
Celda B6: Contiene la fórmula (observad la colocación de $)
=BUSCARV($A6;T_TURNOS;RESIDUO(B$4-FECHAINICIO;16)+2;0)
Donde
Función BUSCARV( ) ya explicada en el número 2 de esta revista (LibreOffice Calc: búsqueda en una tabla con dos variables). También podéis acudir a la Ayuda de Calc.
$A6 identifica al turno en cuestión. La columna es una referencia absoluta para que no se mueva en horizontal al copiar, sin embargo, la fila es relativa para que sí se actualice verticalmente.
T_TURNOS es $TABLAS.$A$2:$Q$6. Repetimos, observad el signo de $ (indica una referencia absoluta de columna y/o fila).
RESIDUO(B$4-FECHAINICIO;16)+2 es la columna a devolver del rango anterior. La función RESIDUO(Dividendo; Divisor) nos permite calcular el resto de una división. Como B$4 y FECHAINICIO son fechas, su resta es el número de días que las separa. Como 16 (días) es la duración del ciclo de los turnos, el resto de esta división entera es la posición en el ciclo. Si el resto es 0 (cero) es el primer día del ciclo, si es 1 es el segundo día del ciclo …, y así sucesivamente. Se le suma 2 para que devuelva la columna correcta la función BUSCARV( ).
0 es un parámetro que indica que la búsqueda debe ser exacta.
Celdas C6 – AF6: Copiamos la celda B6 y la pegamos en todo ese rango.
Celdas B7 – AF10: Volvemos a copiar la celda B6 y la pegamos en todo ese rango.
Celdas B6 – AF10: A este rango le aplicamos un formato condicional para ver las jornadas de trabajo más claramente (Imagen 6).
Los cambios para el mes de febrero son (ver Imagen 11):
Celda B12: Contiene la fórmula =FECHA($A$2;2;1). Cambia el número del mes.
Celda B14: =BUSCARV($A14;T_TURNOS;RESIDUO(B$12-FECHAINICIO;16)+2;0). Observad las celdas implicadas.
Celda AD12: Si el año es bisiesto entonces debería aparecer aquí “29” o “en blanco” si no lo es. Contiene la fórmula =SI(DIA(AC12+1)=1;””;AC12+1). Si al sumar 1 al día anterior es el día 1 (mes siguiente) quiere decir que el año no es bisiesto, en caso contrario nos daría 29. Podríamos haber usado también la fórmula =SI(ESAÑOBISIESTO(AC12);AC12+1;””) con idéntico resultado. Ver la Ayuda de Calc para ESAÑOBISIESTO( ).
Celda AD14: Contiene la fórmula
=SI($AD$12=””;””;BUSCARV($A14;T_TURNOS;RESIDUO(AD$12-FECHAINICIO;16)+2;0)). Se mostrará en blanco o con la jornada correspondiente de acuerdo con el valor de AD12.
El resto de meses se confecciona como lo explicado anteriormente, teniendo en cuenta, obviamente, el número de días de cada mes. Y, repetimos de nuevo, observad y comprobad las celdas que forman parte de las fórmulas.
Este artículo se ha escrito utilizando LibreOffice Writer, Calc e Impress versión 7.4.5 para entornos corporativos.
Saludos cordiales.
Si te ha gustado este artículo, encontrarás más contenidos interesantes en nuestra sección “Tecnología práctica”.
Y no dejes de ver qué tenemos publicado en los distintos números de EnRed@2.0.
