Sumas condicionales con SUMAPRODUCTO

La función SUMAR.SI nos permite realizar sumas de acuerdo a una condición.
Ya hemos mostrado en este blog cómo hacer sumas condicionales con más de una condición
.
En esta nota veremos como enfrentarnos con situaciones especiales del tipo, por ejemplo, cómo sumar los cinco mayores números de una lista.

Por ejemplo, supongamos esta lista de números

Los cinco números mayores de la lista ( 94, 98, 93, 88, 87) suman 454. No hay forma en la cual podamos usar SUMAR.SI para sumar los números mayores de la lista. Pero si podemos hacerlo con SUMAPRODUCTO (o con fórmulas matriciales). EN nuestro caso

=SUMAPRODUCTO(lista*(JERARQUIA(lista;lista)<=5)) donde lista es un nombre que define el rango A2:A21 SUMAPRODUCTO crea dos matrices. Una contiene todos los números del rango "lista" (A2:A21); la otra matriz contiene valores VERDADERO y FALSO producidos por la función JERARQUIA. Cuando el valor calculado es menor o igual a 5, es VERDADERO; en caso contrario, FALSO. Como ya hemos visto, Excel interpreta VERDADERO como 1 al emplearlo en una operación y FALSO como 0.

Para calcular los 5 menores podemos recurrir a la función CONTAR, para determinar cuantos miembros hay en el rango y así poder determinar cuales son los últimos 5 en orden decreciente

=SUMAPRODUCTO(lista*(JERARQUIA(lista;lista)>CONTAR(lista)-5))

o esta mas sencilla, sin CONTAR

=SUMAPRODUCTO(lista*(JERARQUIA(lista;lista;1)<=5))

Si queremos sumar todos los números pares usamos la fórmula

=SUMAPRODUCTO(lista*(RESIDUO(lista;2)=0))

o

=SUMAPRODUCTO(lista*(RESIDUO(lista;2)<>1))

Para los impares usamos

=SUMAPRODUCTO(lista*RESIDUO(lista;2))

que es el equivalente a

=SUMAPRODUCTO(lista*(RESIDUO(lista;2)=1))

pero nos ahorra dos paréntesis.

La función RESIDUO(número;2) da 0 si el número es par. Por eso la expresión RESIDUO(lista;2)=0 es VERDADERO si el número en la "lista" es par.

Para sumar los múltiplos de un determinado número, también podemos usarla función RESIDUO. Por ejemplo, para sumar todos los números que son múltiplos de 3 en nuestra lista, usamos la fórmula

=SUMAPRODUCTO(lista*(RESIDUO(lista;3)=0))

Otro uso de RESIDUO es sumar todos los valores que de las filas pares o impares. Por ejemplo, para sumar todos los valores del rango "lista" que están en filas pares, usamos

=SUMAPRODUCTO(lista*(RESIDUO(FILA(lista);2)=0))

De la misma manera, para sumar los valores en filas impares podemos usar:

=SUMAPRODUCTO(lista*(RESIDUO(FILA(lista);2)<>0))

Si queremos sumar cada tres filas, a partir de la fila 3 podemos usar esta fórmula

=SUMAPRODUCTO(lista*(RESIDUO(FILA(lista);3)=0))

Si queremos sumar cada 3 filas, pero empezando de la primer fila del rango, usamos esta fórmula

=SUMAPRODUCTO((RESIDUO(FILA(lista);3)=2)*lista)

donde "2" es el número de la primer fila del rango.

La fórmula general es =SUMAPRODUCTO((RESIDUO(FILA(lista);n)=m)*lista)

Donde n es el "escalón" (número de filas que queremos saltear en la cuenta) y m es la fila de donde comenzamos a sumar.

El archivo con las fórmulas se puede descargar aquí.

Technorati Tags: MS Excel

Crear un índice de hojas en un cuaderno de Excel.

Una de las consultas que recibo con cierta frecuencia es cómo crear un índice de las hojas de un cuaderno Excel.

Cuando queremos navegar de una a otra hoja del cuaderno podemos usar varios métodos, por ejemplo usar las flechas de navegación en la esquina inferior izquierda de la hoja o hacer un clic con el botón derecho del mouse sobre ellas para abrir el menú de navegación



Pero cuando tenemos muchas hojas en un cuaderno, por ejemplo una por semana del año, estos métodos resultan incómodos.
La solución es crear una hoja que haga de índice. Las entradas del índice son los nombres de las hojas, a las cuales le hemos incorporado un enlace (hyperlink), de manera que con un solo clic podamos navegar a la hoja deseada.

Cuando se trata de pocas hojas, podemos hacer esto en forma manual. Pero cuando tenemos un cuaderno con un gran número de hojas, lo mejor es utilizar una macro.
Esta macro, que pueden descargar aquí, nos ofrece la opción de crear una nueva hoja que contenga el índice




Si aceptamos la opción, la macro crea la nueva hoja, en la celda A1 pone el título "Índice" y a partir de la fila 3, pondrá los nombres de las hojas con un enlace a la celda A1 de cada una de ellas.



Si no aceptamos la opción de crear una nueva hoja, el índice es creado a partir de la celda activa del cuaderno.




Technorati Tags: MS Excel

Búsqueda aproximada en Excel – tercera nota

En las últimas dos notas vimos cómo encontrar en una lista el valor más cercano al valor buscado, o extraer el valor asociado a él.
La fórmula matricial que expusimos, y que he mejorado gracias al aporte de Natxo (ver comentario en la nota anterior), nos da el valor más cercano al valor buscado. A veces buscamos el mayor valor más cercano y a veces el menor. Por ejemplo, si observamos la tabla del ejemplo de la nota anterior



vemos que Pedro, con 76, es el más cercano al promedio, 76,2. Pero si buscamos quién tiene la calificación más cercana por encima del promedio, la respuesta es María con 79.

Esta fórmula matricial nos permite encontrar el valor más cercano por encima del valor buscado

={=INDICE($A$2:$A7;COINCIDIR(MIN(SI(B2:B7-B9>=0;B2:B7;FALSO));SI(B2:B7-B9>=0;B2:B7;FALSO);FALSO))}

Esta fórmula crea una matriz con las diferencias entre los valores de la lista y el valor buscado; luego encuentra el menor que sea mayor a cero, es decir, el más cercano que sea mayor al valor buscado.

De la misma manera, esta fórmula no da el valor más cercano que es menor que el buscado

={INDICE($A$2:$A7;COINCIDIR(MIN(SI(B2:B7-C9>=0;B2:B7;FALSO));SI(B2:B7-C9>=0;B2:B7;FALSO);FALSO))}

El archivo con las fórmulas se puede descargar aquí.

Technorati Tags: MS Excel

Búsqueda aproximada en Excel - segunda nota

Ayer vimos como superar el problema de búsqueda aproximada al valor más cercano en Excel.
Podemos ampliar esta técnica para extraer el valor asociado al valor más cercano al valor buscado. Veamos un ejemplo. Supongamos esta lista de alumnos con notas en distintas asignaturas

En la fila 9 hemos calculado el promedio de cada asignatura. Si queremos averiguar quién es el alumno más cercano al promedio, podemos usar la fórmula que expusimos ayer



La fórmula

={INDICE($A$2:B7,COINCIDIR(MIN(ABS(B2:B7-B9)),B2:B7-B9),1)}

que hemos puesto en la celda B11 nos da como resultado Pedro, que es el alumno con la nota más cercana al promedio.

La única diferencia con la fórmula matricial que presentamos ayer es que hemos agregado el argumento "número de columna" a la función INDICE, que al argumento "matriz" comprende dos columnas y el nombre de los alumnos se encuentra en la primera.

Esta fórmula tiene un serio inconveniente. Si la lista no está ordenada en orden ascendiente, el resultado puede ser erróneo. Si copiamos la fórmula a las celdas contiguas

vemos que el resultado para Literatura es "María", cuando tendría que se "Mercedes". Lo mismo con Matemáticas, donde el resultado es "Pedro" siendo el resultado correcto "Mercedes".

Una solución posible es reordenar las listas antes de aplicar la fórmula. Pero en casos como el nuestro esto nos obligaría a dividir nuestra lista en tres listas separadas.

Otra solución es emplear una fórmula que no sea dependiente del orden de los resultados. La fórmula que aplicaremos es la siguiente:

={ INDIRECTO((DIRECCION(COINCIDIR(MIN(ABS(C9-C2:C7)),ABS(C9-C2:C7),0)+1,1)))}


Para hacer la fórmula más legible, la he dividido en la barra de fórmulas usando Alt+Enter



Esta fórmula emplea la función INDIRECTO que devuelve la referencia especificada por una cadena de texto, como ya hemos explicado.

La función DIRECCION crea una referencia a una celda, en forma de texto, cuyo valor es interpretado por INDIRECTO.

La función COINCIDIR calcula cuál es la posición del valor más cercano al promedio, usando los valores absolutos de las diferencias (como vimos en la nota de ayer).

El valor 1 se refiere a la columna A.

La combinación de estas funciones en la fórmula da como resultado $A$6. IINDIRECTO convierte este resultado al contenido de la celda, "Pedro".

Esta fórmula también tiene un serio inconveniente. Si movemos la lista hacia abajo, o agregamos líneas por encima de ella, obtenemos resultados erróneos. Esto se debe a que COINCIDIR sigue dando la posición correcta, pero ahora esta no esta coincide con los números de fila.

Para evitar esto corregimos nuestra fórmula de la siguiente manera:

={INDIRECTO((DIRECCION(FILA(D2:D7)+COINCIDIR(MIN(ABS(D9-D2:D7)),ABS(D9-D2:D7),0)-1,1)))}



El cuaderno se puede descargar aquí

Technorati Tags: MS Excel

Buscar el valor más cercano en una lista de Excel

Excel cuenta con varias funciones de búsqueda, como BUSCARV, BUSCARH y COINCIDIR.
Estas funciones permiten realizar búsquedas exactas o aproximadas. Supongamos esta situación

Para encontrar la posición del valor buscado (8.5) en la tabla usamos la función COINCIDIR, omitiendo el tercer argumento de la función. En esta situación la función da como resultado 1. Si hubiéramos hecho una búsqueda exacta, el resultado sería #N/A.

El problema con este resultado es que si buscamos la posición del valor más cercano al valor buscado, el resultado tendría que haber sido 2, ya que el valor buscado (8.5) está más cerca del segundo valor en la lista (10) que del primero (5).

Para obtener la posición del valor más cercano al valor buscado tenemos que usar esta fórmula matricial:

={COINCIDIR(MIN(ABS(B2:B7-B9)),B2:B7-B9)}



Para entender esta fórmula tendremos que analizarla de "adentro hacia fuera".

La expresión ABS(B2:B7-B9), al estar dentro de una fórmula matricial, de cómo resultado el menor de los valores del rango B2-B9, B3-B9,…,B7-B9.
El mínimo de esta serie de valores es 1.5, que ocupa el segundo lugar en la serie



El resultado de la fórmula COINCIDIR puede usarse como argumento de la función INDICE para encontrar el valor más cercano al buscado en la lista de valores. En nuestro caso:

={INDICE(B2:B7,COINCIDIR(MIN(ABS(B2:B7-B9)),B2:B7-B9))}

Technorati Tags: MS Excel

Tabla de tasas de cambio con la función TRANSPONER

Un compañero de trabajo me consultaba hoy sobre el uso de la función TRANSPONER sobre la cual ya he escrito en el pasado.
Mi explicación fue interrumpida con un "y para qué sirve en la vida real?". El ejemplo de la nota (ver enlace) le pareció complicado y, aceptémoslo, un tanto rebuscado.
No me quedó más remedio que crear un ejemplo más ilustrativo, que es el siguiente: una tabla de tasas de cambios cruzados con la función TRANSPONER. Una tabla de este tipo aparece en mi nota sobre cómo construir un presupuesto de viaje, pero allí no explico cómo construirla.
Empezamos por una tabla sencilla con el nombre de las monedas y su tasa de cambio, por ejemplo, respecto al Euro



Para trabajar con más comodidad, definimos un nombre que contendrá el rango B3:B6, que llamaremos "tasas"



Ahora construimos el marco para nuestra tabla



Seleccionamos el rango B11:E11 y ponemos la fórmula matricial =$B$3/TRANSPONER(tasas)



Los paréntesis "{" y "}" aparecen al introducir la fórmula pulsando simultáneamente Ctrl+Mayúsculas+Enter, como con toda función matricial.

Copiamos la fórmula al resto de las filas, cambiando la celda de referencia (B4 para el franco suizo, B5 para la libra esterlina y B6 para el yen japonés).



Si queremos orientar la tabla en otro sentido, podemos usar la fórmula matricial ={=$B$3/tasas}



O transponer la primer tabla con TRANSPONER



El cuaderno del ejemplo se puede descargar aquí.

Technorati Tags: MS Excel

Crear un botón flotante para macros en Excel.

Crear macros en Excel es fácil. En particular si usamos la grabadora de macros. Esta herramienta, si bien no crea macros eficientes, si nos permite automatizar tareas sencillas.
Uno de los métodos más comunes para correr macros es ligarlas a un botón. Esto, como ya hemos visto, lo hacemos creando el botón con la barra de herramientas de Formularios o de Cuadro de Controles y ligando la macro a él.
Supongamos esta macro

Sub hola()
    Dim Mensaje

    Mensaje = "La hora es: " & Time & vbCrLf

    Select Case Time
      Case Is <= 0.5
        Mensaje = Mensaje & "Buenos días!"
      Case 0.5 To 0.75
        Mensaje = Mensaje & "Buenas tardes!"
      Case Else
        Mensaje = Mensaje & "Buenas noches!"
      End Select

    MsgBox Mensaje

End Sub

Esta macro produce un mensaje que muestra la hora y un de acuerdo a ésta, un saludo. Para correrla la ligamos a un botón



Al apretar el botón veremos



Uno de mis lectores me consultaba cómo hacer que este botón "flote" sobre la hoja. Esto le era necesario ya que se trataba de una hoja con muchas columnas y al navegar en la hoja, el botón quedaba oculto.

Apresurémonos a decir que hay otras soluciones fuera de ligar una macro a un botón. Podemos ligar la macro a un menú o crear un atajo de teclado, como ya hemos mostrado. De esta manera, podemos activar la macro sin importar donde estemos en la hoja.

Pero, si por algún misterioso motivo, no tenemos más remedio que ligar la macro a un botón y queremos que éste esté siempre visible, las posibilidades son:
1 – Crea una barra de herramientas y ligarle la macro
2 – Crear un UserForm y definirlo como "modeless"

Personalmente prefiero la primer opción. La ventaja de la segunda, es que nos permite determinar el tamaño de la forma.

Para crear una barra de herramientas personalizada con la macro hacemos lo siguiente:

# abrimos el menú Ver-Barras de Herramientas-Personalizar y apretamos el botón Nueva



# Le damos un nombre a la nueva barra y apretamos Aceptar



# Abrimos la pestaña Comandos y buscamos Macros en la ventana Categorías



# Ahora arrastramos la opción Personalizar el botón a la nueva barra de herramientas



# Con un clic del botón derecho abrimos el menú de la barra y seleccionamos la opción Asignar macro, lo que hacemos tal como con el botón.



A partir de esta momento, la barra flotará sobre la hoja, no importa adonde nos desplacemos.
Esta barra estará presente en todas las hojas del cuaderno, hasta que la cerremos. Esto puede ser una ventaja o una desventaja, dependiendo de la macro. Si la macro debe correr sólo cuando determinada hoja sea la activa, esto puede ser un inconveniente,

Crear un UserForm flotante requiere un poco más de conocimiento y práctica de Visual Basic. Los pasos son los siguientes:

# Abrimos el editor de Vba (Alt+F11), agregamos un Userform y sobre el pegamos un botón



# Cambiamos el valor de Caption en la ventana de Propiedades del botón a "Saludar"



# Hacemos un doble clic sobre el botón para abrir el módulo y ponemos este código



# Volvemos al UserForm (Ctrl+Tab) y en la ventana Propiedades del Userform cambiamos la propiedad ShowModal a False



Esto permitirá seguir trabajando en la hoja a pesar de no haber cerrado el formulario.

# Programamos el evento Workbook_Open, si queremos que el botón aparezca en todas las hojas, cuando abrimos el cuaderno

Private Sub Workbook_Open()
    UserForm1 Show
End Sub

Si queremos que el botón aparezca sólo en una hoja determinada, programamos también el evento en el módulo de la hoja

Private Sub Worksheet_Activate()
    UserForm1.Show
End Sub

En las restantes hojas tendremos que cerrar el formulario, o programar el evento Activate de esas hojas así:

Private Sub Worksheet_Activate()
    Unload UserForm1
End Sub

# Cambiamos el tamaño del UserForm y/o del botón de acuerdo a nuestros deseos.

Technorati Tags: MS Excel

Redondeo de horas en Excel

Cuando queremos redondear tiempos o fechas en Excel debemos tomar en cuenta que, en lo que tiempo y fechas se refiere, Excel no es WYSIWYG(what you see it's what you get, queriendo decir, lo que ves es lo que es).
Como ya hemos explicado, para Excel la fecha 11 de febrero de 2008 es el número 39489(los días transcurridos desde el 1ro. de enero de 1900). La hora 18:00 es el número 0.75 (que resulta de dividir 18 por 24).
En resumen, existe una diferencia entre como Excel muestra las fechas y las horas en pantalla y como las considera para realizar operaciones.
También cuando queremos redondear fechas y horas debemos tomar en cuenta esta cuestión.
Supongamos, como pedía uno de mis lectores, que queremos redondear la hora 2:20 a la hora más cercana. Si usamos la fórmula =REDONDEAR(A1;0), obtenemos un resultado erróneo




Este resultado se debe a que para Excel la hora 2:20 es el número 0.097222 y por lo tanto, al redondear a un número entero obtenemos 0. Esto podemos verlo si aplicamos el formato General a las celdas



La fórmula correcta para redondear horas es =REDONDEAR(A1*24,0)/24



Es decir: 0.09722 X 24 = 2.3333 ---> REDONDEAR(2.3333;0)= 2 ---> 2/24= 0.8333
Y finalmente, cuando ponemos formato de hora a 0.8333 vemos en la celda 02:00

Si queremos redondear por medias horas, usamos como factor 48 en lugar de 24



Si queremos redondear por múltiplos de 15 minutos (cuarto de hora) usamos 96 (24X4), =REDONDEAR(A1*96,0)/96; para múltiplo de 20 minutos usamos 72, etc.

La regla general es =REDONDEAR(A1*(60/m*24),0)/(60/m*24), donde m es la cantidad de minutos en el múltiplo.

Una variante interesante a esta técnica es la propuesta en Daily Dose of Excel. Ésta consiste en crear un nombre con la constante "=24*60"



De esta manera podemos usar el nombre en nuestra fórmula de esta manera

Technorati Tags: MS Excel

Cargo de horas de trabajo con Excel.

Otras de las tareas usuales con Excel es el cálculo de horas a cargar por un proyecto. Sencillamente, calculamos el total de horas y lo multiplicamos por la tarifa. A pesar de lo obvio que parece, debemos tomar en cuenta cómo Excel considera las horas en los cálculos.
Por ejemplo, si hemos invertido 10 horas de trabajo y la tarifa acordada con nuestro cliente es 50 euros, estaríamos tentado ha hacer este cálculo:"10:00 X € 50.00 = € 500". Pongámoslo ahora en una hoja de Excel



El resultado en la celda C2 es "incorrecto". Como explicamos en la nota anterior, esto se debe a que el valor de la celda A2 es en realidad 0.416666666666667 (10 horas dividido por 24 horas).
Para corregir este "error" (que en realidad no lo es) multiplicamos nuestra fórmula original por 24



En la "vida real" las cosas son más complicadas. Por lo general trabajamos con tarifas horarias diferenciales. Supongamos esta tarifa diferencial para cargar un proyecto



La idea es cargar a nuestro cliente con una tarifa decreciente en función de la cantidad de horas.

Siguiendo las indicaciones de la nota anterior sobre el cálculo con horas, construimos esta tabla con las horas trabajadas en el proyecto



Para cargar a nuestro cliente tenemos que dividir las horas de trabajos en porciones de 8 horas. El modelo propuesto es el siguiente



Todas las celdas en el rango A11:B13 tienen el formato [hh]:mm. La celda A14 tiene el formato [h]:mm +, para que el sino + aparezca, sin convertir el contenido de la celda en texto.
Los intervalos los fijamos en forma dinámica, donde la duración del intervalo está definida por las celdas B11 – A11 (8 horas en nuestro caso). Esta diferencia la ponemos en un nombre (escala):



En la celda A12 ponemos la fórmula =B11, en la celda A13, =B12. En la celda B12 ponemos la fórmula =A12+escala, en la celda B13 =A13+escala. De esta manera nuestro modelo será dinámico y se ajustará automáticamente a los cambios en el tamaño del intervalo.
En la columna Horas, en la celda D11 ponemos la fórmula

=MIN(escala,$D$6)

Usamos esta fórmula en lugar de la función SI para determinar si el total de horas trabajadas es menor que el primer intervalo de nuestra tarifa diferencial.
En las celdas del rango D12:D14 usamos la fórmula

=MIN(escala,$D$6-SUMA($D$11:D11))

La expresión SUMA($D$11:D11) va resumiendo le total de horas que ya hemos cargado. Hay que prestar atención a las direcciones absolutas y relativas en la función SUMA.

Para ver cómo el modelo ajusta automáticamente los intervalos, cambiamos el primer intervalo a, por ejemplo, seis horas

Technorati Tags: MS Excel

Administración de horas de trabajo en Excel.

Supongo que si se hiciera una estadística sobre los usos de Excel, las herramientas para la administración de horas de trabajo figurarían entre los primeros lugares.
Excel está "hecho a medida" para estas tareas, pero para construir este tipo de herramientas tenemos que comprender primero como maneja Excel el tema de las fechas y las horas.
En esta nota haremos una revisión integral del tema y daremos algunos ejemplos prácticos.

Excel considera las fechas y las horas como números sucesivos de una serie. Ésta comienza el 1ro. de enero de 1900, fecha a la Excel le asigna el número 1. El 2 de enero está representado por el número 2 y así sucesivamente.
Las horas son la parte decimal del número. Un día completo son 24 horas, de manera que las 12 del mediodía está representado por el número 0.5; las seis de la mañana por el 0.25, las 6 de la tarde por el 0.75, etc. En este momento es el 9 de febrero a las 9:50 de la mañana. El resultado de la función AHORA() es 09/02/2008 09:50:36



Si cambiamos el formato de la celda a "General" veremos el número 39487.4101446759



donde 39487 es el número de días transcurrido desde el 01-01-1900 hasta hoy y 0.4101446759 resulta de dividir 35346 segundos (el equivalente de 9 horas, 50 minutos y 36 segundos) por 86400 (la cantidad de segundos que hay en un día).

Otro aspecto importante a tomar en cuenta, antes de abocarnos a la tarea de construir herramientas para administrar horas, es el formato de números en Excel y el de las fechas y horas en particular. Cambios de formato no alteran el número sino como es representado por Excel en la pantalla. Supongamos una tabla donde ponemos el comienzo de una tarea, las horas a trabajar y el resultado será la hora de finalización



Para ver el resultado, debemos cambiar el formato de la celda, que ha sido "heredado" de la celda A2, por el formato hh:mm



Extraño, ¿no? Para entender este resultado volvemos a cambiar el formato de las celdas a "General"



Sucede que cuando ingresamos 15:30 en la celda A2, Excel la interpreta como el 1ro. de enero de 1900 a las 15:30. Al poner 8 en la celda B2, por ser un número entero, es interpretado como 8 días. El resultado en la celda C2 será el 8 de enero de 1900 a las 15:30. Esto lo podemos ver cambiando el formato de las celdas a dd/mm/yyyy hh:mm



Para evitar resultados erróneos debemos ingresar los datos de horas con formato horario.

En este ejemplo hemos ingresado el dato en la celda B2 como 08:00, lo que Excel interpreta como 8 horas, y por lo tanto el resultado es el esperado



Hay otras cuestiones a considerar cuando trabajamos con horas y fechas en Excel, como el manejo de resultados negativos, suma de horas por encima de 24 horas, distintos sistemas de fechas y más. Todo esto ha sido tratado en distintas notas del blog. Todas las notas sobre fechas y horas pueden verse pulsando el enlace de las etiquetas en la columna izquierda del blog



Consideremos ahora este ejemplo



Todas las celdas de la tabla tienen el formato hh:mm. El Total Bruto es la cantidad de horas transcurridas desde el ingreso hasta la salida, sin tomar en cuenta el descanso. La fórmula de Total bruto es =(E5-B5)+(E5<B5)*24. El objetivo de la expresión (E5<B5)*24 es corregir el resultado cuando la hora del final es menor que la del principio. La explicación completa de esta fórmula la encontrarán en la nota sobre diferencia de horas en Excel. Más adelante veremos que esta fórmula tiene un serio inconveniente.

La fórmula de horas trabajadas es =F5-((D5-C5)+(D5<C5)*24), donde nuevamente usamos la expresión (D5<C5)*24 para corregir el error que se genera cuando la salida sucede al día siguiente de la entrada.
La columna Horas Regulares la calculamos con esta fórmula: =MIN(G5,$E$1) donde $E$1 es la celda que contiene la cantidad de horas regulares (en nuestro caso 8). La función MIN nos da el mínimo entre la jornada regular y las horas trabajadas.
Para calcular las horas extras usamos la fórmula =MAX(0,G5-H5). Es decir, el máximo entre 0 y la diferencia entre las horas trabajadas y las regulares.

Supongamos ahora que queremos totalizar el total de horas del día. Como ya hemos explicado, las celdas del total tendrán el formato [h]:mm.



Como ven, los resultados parecen incorrectos! Esto se debe a que nuestra fórmula de corrección no nos sirve en este caso. En lugar de la fórmula =(E5-B5)+(E5<B5)*24 en Total Bruto, usaremos la fórmula =RESIDUO(E5-B5,1) en la columna de Total bruto y la fórmula =F5-(RESIDUO(D5-C5,1)) en la Horas trabajadas.
Ahora los totales son correctos




Technorati Tags: MS Excel