Encontrar soluciones en Numbers
En lugar de intentar enseñar algún uso de la aplicación he pensado que seria mejor explicar como abordo los problemas hasta encontrar la solución, ya sabéis el dicho: Dale un pez y comerá un día, enséñale a pescar y comerá todos los días. Como no soy experto en Numbers ni lo uso cada día tal vez veáis que también lo podéis hacer vosotros.
Usaré un problema real por el que se pidió ayuda.
Como sumar el tiempo trabajado semanalmente, trasladado en una fila
En principio no parece difícil, es simplemente restar la hora de salida con la de entrada. Pero al mirar la tabla hay varios problemas.
- Las horas de entrada y salida están en la misma celda
- Si se restan determinadas horas saldrán negativas
- Hay espacios libres o con texto que nosotros ignoramos pero ¿que hará Numbers?
La fórmula final:
LET(horaris;B1:G1;
SUMA(SI.ERROR(SI(
TEXTO.DESPUES(horaris;”-"−TEXTO.ANTES(horaris;"-")<DURACION(0;0;0);
TEXTO.DESPUES(horaris;”-")−TEXTO.ANTES(horaris;"-"+DURACION(0;0;24);
TEXTO.DESPUES(horaris;”-")−TEXTO.ANTES(horaris;"-"));
DURACION(0;0;0))))
(Aunque se escriben seguido, he separado las fórmulas en líneas en toda la explicación para facilitar su comprensión)
Seguramente habrá quien después de mirarla un minuto la escriba de un tirón y pase a solucionar el hambre en el mundo. El resto necesitamos empezar desde cero e ir avanzando. Como todos habréis oido, los problemas hay que dividirlos para solucionarlos por partes.
Primer problema (casualmente es el punto 1 de la lista 😉)
La celda contiene tanto la hora de entrada como la de salida por lo que hay que separarlas. Yellowbox (un compañero del foro y habitual en crear sugerencias para Numbers) lo solucionó antes que yo entrara en escena.
Si me hubiera tocado a mi hacerlo habría rebuscado entre las funciones empezando por el apartado de Texto, por suerte están bien ordenadas. Entre las últimas de la lista aparecen TEXTO.ANTES y TEXTO.DESPUES. Como las horas están separadas por un guion (“-“) es fácil escribir la primera parte, siempre primero probándolo en una única celda:
TEXTO.DESPUES(B1;”-")−TEXTO.ANTES(B1;"-")
Resultado: 17:00 - 9:00 = 8h (aquí el guion es el símbolo de la resta)
Gracias a que Numbers detecta el tipo de valor en el momento de dividir el texto, no se le tiene que indicar que son horas.
Solucionado ✓
Segundo problema
Resultado de E1 al aplicar la fórmula anterior: 9:00 - 17:00 = -8h
Como está claro que el trabajador no devolvió 8h. a la empresa, tenemos el segundo problema. ✗
Fácil, le sumamos 24h. pero…
Resultado de E1: TEXTO.DESPUES(B1;”-“)−TEXTO.ANTES(B1;”-")+24 = 16
Funciona pero Numbers avisa que deja de considerarlo una duración y lo considera simplemente un número. Podría servirnos pero mejor que siga considerándolo horas. ✗
Resultado de E1: TEXTO.DESPUES(B1;”-“)−TEXTO.ANTES(B1;”-“)+24h = Error de sintaxis
No acepta poner 24h ya que interpreta la h final como parte de una nueva instrucción. ✗
Resultado de E1: TEXTO.DESPUES(B1;”-“)−TEXTO.ANTES(B1;”-“)+”24h” = 16:00
Así si funciona ✓
Empezamos con la formula
Con estas dos soluciones ya podemos empezar a crear la fórmula para toda la fila:
SUMA(SI(
TEXTO.DESPUES(B1:G1;”-“)−TEXTO.ANTES(B1:G1;”-“)<"0h";
TEXTO.DESPUES(B1:G1;”-")−TEXTO.ANTES(B1:G1;"-")+”24h”;
TEXTO.DESPUES(B1:G1;"-")−TEXTO.ANTES(B1:G1;"-")))
Empezamos con la función SUMA para sumar todos los resultados. Como ya sabréis, al escribir una función nos aparecen las diferentes funciones que empiezan por ese texto y, al escoger una la escribe con las partes requeridas. Practicad vosotros o será una explicación eterna.
Luego, hay un SI para separar los dos cálculos necesarios (las dos primeras fórmulas). Si se cumple la condición, el resultado es inferior a cero (<“0h”), se aplica la formula que le sigue, si no se cumple se aplica la otra.
Resultado: Error de sintaxis ✗
Parecía clara pero aquí Numbers nos la juega. Tanto “0h” como “24h” no sirven aquí aunque funcionasen en el segundo problema, dan error de sintaxis. Rebuscando otra vez entre las funciones encontré:
DURACION(semanas;días;horas;minutos;segundos;milisegundos)
Le quitamos los minutos, segundos y milisegundos, para hacerla más corta, y nos queda así.
SUMA(SI(
TEXTO.DESPUES(B1:G1;”-“)−TEXTO.ANTES(B1:G1;”-")<DURACION(0;0;0);
TEXTO.DESPUES(B1:G1;”-")−TEXTO.ANTES(B1:G1;"-")+DURACION(0;0;24);
TEXTO.DESPUES(B1:G1;"-")−TEXTO.ANTES(B1:G1;"-"))))
Resultado: Error: TEXTO.DESPUES no ha encontrado “-“ ✗
Cambiando la selección a B1:C1 funciona por lo que falla cuando la casilla no tiene el formato requerido, en este caso un guion. En las casillas D1 y F1. Al final Numbers no es tan inteligente como nosotros 😢.
Tercer problema
Para evitar los errores Numbers tiene la función SI.ERROR. Si no hay error en el primer cálculo ese es el resultado, si lo hay ejecuta el segundo cálculo. En este caso sumamos cero (DURACION(0;0;0)) si no hay nada o un texto sin “-“.
SUMA(SI.ERROR(SI(
TEXTO.DESPUES(B1:G1;”-“)−TEXTO.ANTES(B1:G1;”-")<DURACION(0;0;0);
TEXTO.DESPUES(B1:G1;”-")−TEXTO.ANTES(B1:G1;"-")+DURACION(0;0;24);
TEXTO.DESPUES(B1:G1;"-")−TEXTO.ANTES(B1:G1;"-"));DURACION(0;0;0)))
Resultado: 48:30h
Así funciona perfecto ✓
Extras
Como el peticionario de ayuda se describió como inútil con las hojas de cálculo preferí simplificarle el trabajo y que no tuviera que trastear demasiado con la formula, así se evitan problemas.
Para ello recurrí a la función LET. Simplemente sirve para definir las variables que luego puedes usar en la formula integrada. Como habréis visto, el conjunto de celdas (B1:G1) aparece seis veces y justamente es la parte que el usuario puede necesitar cambiar.
LET(horaris;B1:G1;SUMA(SI.ERROR(SI(
TEXTO.DESPUES(horaris;”-")−TEXTO.ANTES(horaris;"-")<DURACION(0;0;0);
TEXTO.DESPUES(horaris;”-")−TEXTO.ANTES(horaris;"-")+DURACION(0;0;24);
TEXTO.DESPUES(horaris;”-")−TEXTO.ANTES(horaris;"-"));
DURACION(0;0;0))))
Al principio defino la variable “horaris” como el conjunto B1:G1 y luego sustituyo sus seis apariciones en la anterior fórmula por “horaris”. Así solamente se necesita redefinir el conjunto una sola vez.
“horaris” (“horarios”) puede ser cualquier nombre que no coincida con una función, como soy Catalán siempre les pongo nombres catalanes.
Como decía al principio, la mayoría necesitamos ir probando y equivocarnos para encontrar las soluciones. Dedicadle unas horas antes de daros por vencidos.
Como extra, según mi limitada experiencia con Excel, la mayoría de las funciones en Numbers son las mismas, incluso el nombre, que las usadas en Excel por tanto, lo que aprendas en Numbers te servirá en Excel. (Las funciones en Español, con la versión Catalana no siempre coinciden)
One more thing 😜
Por si alguno se quedó con ganas de más y revisó el hilo, habrá encontrado una segunda fórmula que calcula las horas cuando los horarios están en celdas diferentes. No voy a explicar todo el proceso pero hay unas funciones que me gustaron encontrar:
ELEGIRCOLS(B1:G1;1;3;5)
Como su nombre deja ver, sirve para hacer un subconjunto, seleccionando algunas columnas de él. En este ejemplo serian la 1, 3 y 5.
Así, con la función gemela ELEGIRCOLS(B1:G1;2;4;5) se tienen los dos conjuntos que restar para encontrar la solución.
SECUENCIA(1;3;1;2)
Para hacerlo más sencillo para el forero encontré esta función para así evitar la lista de columnas anterior. Según el ejemplo, crea una secuencia de tres valores empezando por el 1 y sumándole 2 cada vez. El resultado es la lista de columnas requerida antes: 1, 3, 5.
Para dejarla “perfecta” le añadí:
COLUMNAS(B1:G1;0)
Con ella se cuenta las columnas que tiene la selección. En este caso lo divido por 2 para así tener el número de los subconjuntos anteriores (3). De este modo, con solo seleccionar las celdas que se necesita sumar, la formula se encarga de todo. Todo ello generando variables para simplificarla más.
La formula quedó así:
LET(horariSetmanal;B3:G3;numHoraris;COLUMNAS(horariSetmanal;0)÷2;
hora1;ELEGIRCOLS(horariSetmanal;SECUENCIA(1;numHoraris;1;2));
hora2;ELEGIRCOLS(horariSetmanal;SECUENCIA(1;numHoraris;2;2)); SUMA(SI.ERROR(SI(
hora2−hora1<DURACION(0;0;0;);
hora2−hora1+DURACION(0;0;24);
hora2−hora1);
DURACION(0;0;0))))
Aquí lo dejo, gracias por leerme.
Saludos desde Barcelona,
Albert