Excel - Programar calculo en tabla con condiciones

 
Vista:
sin imagen de perfil
Val: 8
Ha aumentado su posición en 3 puestos en Excel (en relación al último mes)
Gráfica de Excel

Programar calculo en tabla con condiciones

Publicado por CARLOS (3 intervenciones) el 05/11/2020 17:48:07
Hola a todos!

Es la primera vez que escribo en el foro y me gustaria pediros ayuda con un excel que estoy haciendo y estoy bloqueado :S... intentaré explicarlo lo mejor posible!

Tengo una tabla de esta forma:

Fila 1 --> cada columna es un valor de tiempo cada 5 minutos de un total de 24h (Ej: 00:05:00; 00:10:00, 00:15:00... hasta 00:00:00)
Columna 1 --> cada fila es una fecha (01/01/2000; 02/01/2000;...)

En cada celda de esa matriz, se rellenarà un valor automaticamente que puede ser "Abierto" o "Cerrado", de forma que al final tendré una matriz cuyo par de datos me indicarà que a fecha de 15/03/2000 a las 13:05:00 es "abierto" o "cerrado". Y asì cada 5 minutos de cada dìa.

Mi problema viene ahora: tengo que escribir una funcion en excel, que me devuelva el tiempo total que ha estado abierta en funcion de la fecha que yo escriba. Ejemplo:

En la celda A3 escribo "15/03/2000", y en la B3 me indica: "00:45:00" (o con otro formato), que seria que en esa fecha, ha estado abierta por un total de 45 minutos.

He probado con SUMIF y VLOOKUP pero no soy capaz de hacer que funcione en conjunto... mi otro problema es que tampoco soy capaz de hacerle entender a excel que me de el tiempo en duracion total y no me sume las horas tal cual (hasta ahora lo que obtengo son por ejemplo: 71:00:00 al sumar tres tiempos donde solo transcurren 10 minutos como 23:05:00 + 23:10:00 + 23:15:00) en lugar de 00:10:00 total).

Espero que me puedan ayudar! muchas gracias!!! :D
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
Imágen de perfil de wordexperto.com
Val: 6.373
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Programar calculo en tabla con condiciones

Publicado por wordexperto.com (2801 intervenciones) el 05/11/2020 19:15:54
Hola Carlos:
No me gusta la estructura de tabla de doble entrada que usas, es más eficiente una estructura de base de datos y los informes con Tablas dinámicas (https://www.youtube.com/playlist?list=PLF1OL9sNXkSyOJETR9ST8xjwjFj9WrkME). Aún así, lo que necesitas es contar.si.
En el ejemplo no debes sumar, sino restar la hora menor de la mayor, para conocer el tiempo transcurrido.
Si subes un ejemplo, te ayudaremos mejor.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil
Val: 8
Ha aumentado su posición en 3 puestos en Excel (en relación al último mes)
Gráfica de Excel

Programar calculo en tabla con condiciones

Publicado por CARLOS (3 intervenciones) el 06/11/2020 11:54:22
Gracias por responder!

El problema es que no puedo cambiar el formato de la tabla origen porque viene creada asì, se autorellena con informacion que copia una macro desde un servidor externo a excel.

La tabla es asi: se encuentra en la hoja "database".
1

La hoja del resultado seria asi: donde yo escribo la fecha y me pone el tiempo total de "abierto".
2

No he usado nunca la funcion contar.si pero voy a ver si puedo hacer algo!

(MODIFICO)

Con contar.si he podido calcular el tiempo perfectamente, ahora mi problema es que con esta funcion:

=COUNTIF('Database '!B2:KD2;"aperto")*5

usando LOOKUP o VLOOKUP me busque la fila para el calculo que corresponda a la fecha que yo le escribo, donde deberia anidar la formula? Gracias!
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Juanma
Val: 128
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Programar calculo en tabla con condiciones

Publicado por Juanma (38 intervenciones) el 07/11/2020 16:00:08
Te paso un ejemplo de tabla, parecida a lo que quieres.
en este ejemplo, en lugar de restar o sumar nada, cuenta las celdas que contienen "b" (en tu caso "abierto") y luego las multiplica por 5 minutos.

yo lo veo así mucho mas fácil. espero que te sirva
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil
Val: 8
Ha aumentado su posición en 3 puestos en Excel (en relación al último mes)
Gráfica de Excel

Programar calculo en tabla con condiciones

Publicado por CARLOS (3 intervenciones) el 09/11/2020 09:03:13
Hola!

Gracias! hice algo parecido con el countif pero tu funcion me parece mejor a la hora de usar el match. Por ahora lo tengo solucionado! Gracias a los dos :)
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar