Problemas con intervalos numéricos, ayuda
Estoy necesitando resolver los siguientes problema:
A- Tengo 4 intervalos numéricos por fila ("L"): [A:B] [C:D] [E:F] [G:H], necesito saber si entre ellos se superponen para un mismo valor en la columna "L". La repuesta debe ser un texto ej: "si"/"no". Existe la posibilidad de agregar un condicionante más en columna "M", o sea, para todo M="texto"
ej: siendo los intervalos [1;3] [2;4] [4;5] [6;8], y para todos los "L"=3 (en el ej solo se superponen los 2 primeros). Rta: "SI"
B- Caso similar al anterior: si tengo 2 o más filas con "L"=3 y en cada fila hay intervalos, necesito saber si para intervalos equivalentes, la diferencia es <2 o 2>=. Rta en texto idema anterior para <2:"si" y para >=2:"no"
Ej: fila 1 (L1=4) [1,2][2,3][5,7][10,14]
fila 2 (L2=4) [2;3][5;6][0;0][8;9]
Los intervalos de la columnas [A;B] diferencia 0,[C;D] diferencia 2, [E;F]diferencia nula, [G;H]diferencia 1. Rta: "SI" (si un intervalo se compara con [0;0], la rta es"NO"
A- Tengo 4 intervalos numéricos por fila ("L"): [A:B] [C:D] [E:F] [G:H], necesito saber si entre ellos se superponen para un mismo valor en la columna "L". La repuesta debe ser un texto ej: "si"/"no". Existe la posibilidad de agregar un condicionante más en columna "M", o sea, para todo M="texto"
ej: siendo los intervalos [1;3] [2;4] [4;5] [6;8], y para todos los "L"=3 (en el ej solo se superponen los 2 primeros). Rta: "SI"
B- Caso similar al anterior: si tengo 2 o más filas con "L"=3 y en cada fila hay intervalos, necesito saber si para intervalos equivalentes, la diferencia es <2 o 2>=. Rta en texto idema anterior para <2:"si" y para >=2:"no"
Ej: fila 1 (L1=4) [1,2][2,3][5,7][10,14]
fila 2 (L2=4) [2;3][5;6][0;0][8;9]
Los intervalos de la columnas [A;B] diferencia 0,[C;D] diferencia 2, [E;F]diferencia nula, [G;H]diferencia 1. Rta: "SI" (si un intervalo se compara con [0;0], la rta es"NO"
1 Respuesta
Respuesta de santiagomf
1
A ver si lo entiendo bien y consigo explicarme.
Diremos que un valor de "L" (supongamos en L2) está en el primer rango cuando sea mayor o igual que el valor en "A" y menor o igual que "B". Estará en el segundo rango si cumple las mismas condiciones con "C" y "D" y así sucesivamente.
Por ello podemos hacer lo siguiente: si L2 es mayor o igual que A2 y menor o igual que B2, pondremos como resultado un 1 y si no un 0. La instrucción sería "=SI(Y(L2>=A2;L2<=B2);1;0)".
Haciendo lo mismo para los otros 3 rangos y sumando todo tendremos el número de rangos en los que está incluido L2.
Para lo que quieres, miraremos si esa suma es mayor o igual que 2 y devolveremos un "Sí". En caso contrario devolvemos "No".
La fórmula completa sería:
=SI(SI(Y(L2>=A2;L2<=B2);1;0)+SI(Y(L2>=C2;L2<=D2);1;0)+SI(Y(L2>=E2;L2<=F2);1;0)+SI(Y(L2>=G2;L2<=H2);1;0)>=2;"Sí";"No")
La pregunta B no acabo de entenderla. ¿Qué quieres decir con intervalos equivalentes?
Lo que pones como diferencia parece ser las restas A2-B1, C2-D1, ¿E2-F1? y G2-H1. La diferencia nula debe ser cuando el intervalo es el 0,0.
Pero lo que no acabo de entender qué es lo que quieres hacer en el segundo caso.
Cuéntamelo con más detalle y te lo intento resolver.
Diremos que un valor de "L" (supongamos en L2) está en el primer rango cuando sea mayor o igual que el valor en "A" y menor o igual que "B". Estará en el segundo rango si cumple las mismas condiciones con "C" y "D" y así sucesivamente.
Por ello podemos hacer lo siguiente: si L2 es mayor o igual que A2 y menor o igual que B2, pondremos como resultado un 1 y si no un 0. La instrucción sería "=SI(Y(L2>=A2;L2<=B2);1;0)".
Haciendo lo mismo para los otros 3 rangos y sumando todo tendremos el número de rangos en los que está incluido L2.
Para lo que quieres, miraremos si esa suma es mayor o igual que 2 y devolveremos un "Sí". En caso contrario devolvemos "No".
La fórmula completa sería:
=SI(SI(Y(L2>=A2;L2<=B2);1;0)+SI(Y(L2>=C2;L2<=D2);1;0)+SI(Y(L2>=E2;L2<=F2);1;0)+SI(Y(L2>=G2;L2<=H2);1;0)>=2;"Sí";"No")
La pregunta B no acabo de entenderla. ¿Qué quieres decir con intervalos equivalentes?
Lo que pones como diferencia parece ser las restas A2-B1, C2-D1, ¿E2-F1? y G2-H1. La diferencia nula debe ser cuando el intervalo es el 0,0.
Pero lo que no acabo de entender qué es lo que quieres hacer en el segundo caso.
Cuéntamelo con más detalle y te lo intento resolver.
Gracias por la pronta repuesta, paso a detallar el problema, desde un punto de vista más practico, es un calendario de trabajo en donde se discrimina el horario de comienzo y finalización de 4 tareas diferentes (los intervalos) y el nombre de la persona que realiza la tarea (para facilitar, uso números enteros o decimal 5 (1, 1.5, 2, 2.5, etc) en lugar de formato hora), ademas estas mismas tareas se realizan en 4 secciones diferentes. Esta planteado así:
Cada tarea 2 columnas entrada y salida
dia# nombre tarea1 tarea2 tarea3 tarea4
seccion1
seccion2
seccion3
seccion4
A- Un mismo día 1 persona puede realizar distintas tareas, si bien en la practica puede realizar 2 cosas o más al mismo tiempo, el registro no debe superponer hs. Si para tarea1 se realiza de 8 a 10 y la tarea3 se realiza de 9:30 a 11, hay una superposición de 9:30 a 10, la fórmula debe devolver "ok" o "error"
B- Un mismo día 2 o más personas pueden realizar la misma tarea pero debe haber 2 hs o más de espacio, si es menos la fórmula debe devolver "error" u "ok"
C- Un mismo día 1 persona puede realizar la misma tarea en varias secciones, caso semejante a "A", en la practica puede 2 o más al mismo tiempo, pero en el registro no se debe superponer. Si en seccion1 realiza tarea2 de 10 a 12 y en seccion3 realiza tarea2 de 11 a 13, la fórmula debe devolver "ok" o "error"
Desde ya gracias
Cada tarea 2 columnas entrada y salida
dia# nombre tarea1 tarea2 tarea3 tarea4
seccion1
seccion2
seccion3
seccion4
A- Un mismo día 1 persona puede realizar distintas tareas, si bien en la practica puede realizar 2 cosas o más al mismo tiempo, el registro no debe superponer hs. Si para tarea1 se realiza de 8 a 10 y la tarea3 se realiza de 9:30 a 11, hay una superposición de 9:30 a 10, la fórmula debe devolver "ok" o "error"
B- Un mismo día 2 o más personas pueden realizar la misma tarea pero debe haber 2 hs o más de espacio, si es menos la fórmula debe devolver "error" u "ok"
C- Un mismo día 1 persona puede realizar la misma tarea en varias secciones, caso semejante a "A", en la practica puede 2 o más al mismo tiempo, pero en el registro no se debe superponer. Si en seccion1 realiza tarea2 de 10 a 12 y en seccion3 realiza tarea2 de 11 a 13, la fórmula debe devolver "ok" o "error"
Desde ya gracias
Aclaración, se utiliza Office 2003
Entiendo que lo que buscas es que no se superpongan en el tiempo ni las tareas ni los trabajos de una sección.
Quizás lo más sencillo sea definir una consulta en la pases los valores de las 8 celdas y te devuelva si hay o no superposición de horarios.
Esta función podría ser así:
Option Explicit
Function snSuperpuestos(ByVal rangoHoras As Range) As String
ReDim mHoras(1 To 2, 1 To 4) As Double
Dim i As Integer
Dim j As Integer
Dim aux As String
' El rango que nos pasen tiene que ser de 1 fila por 8 columnas o
' bien de 4 filas y 2 columnas
If (rangoHoras.Rows.Count <> 1 Or rangoHoras.Columns.Count <> 8) And _
(rangoHoras.Rows.Count <> 4 Or rangoHoras.Columns.Column <> 2) Then
MsgBox "El rango que admite esta función tiene que ser de 1x8 o " & _
"4x2 filas-columnas. Proceso terminado"
snSuperpuestos = "ERROR filas/columnas"
Exit Function
End If
' Cargamos nuestra matriz para después hacer los cálculos
For i = 1 To 2
For j = 1 To 4
If rangoHoras.Rows.Count = 1 Then ' Es de 1x8
mHoras(i, j) = rangoHoras.Cells(1, 2 * (j - 1) + i)
Else
mHoras(i, j) = rangoHoras.Cells(i, j)
End If
Next j
Next i
' Comprobamos que la hora inicial sea anterior a la final
For i = 1 To 4
If mHoras(1, i) > mHoras(2, i) Then
MsgBox "Error. Hora inicial posterior a la hora final"
snSuperpuestos = "ERROR H.Inicial>H.Final"
Exit Function
End If
Next i
' Comprobamos los casos que se superponen usando la matriz
aux = "" ' Para ir guardando los superpuestos
For i = 1 To 3
For j = i + 1 To 4
If mHoras(1, i) <> 0 Or mHoras(2, i) <> 0 Or _
MHoras(1, j) <> 0 Or mHoras(1, j) <> 0 Then
' Ninguno de los dos horarios son nulos. Se pueden comparar
' Diremos que 2 tramos de horas están superpuestos si...
' - La hora de inicio 1 es menor que la hora fin 2 y
' La hora fin 1 es mayor que la de inicio 2
' O bien se da el caso contrario:
' - La hora de inicio 2 es menor que la hora fin 1 y
' La hora fin 2 es mayor que la de inicio 1
If ((mHoras(1, i) <= mHoras(2, j) And mHoras(2, i) > mHoras(1, j))) Or _
((mHoras(2, i) <= mHoras(1, j) And mHoras(1, i) > mHoras(2, j))) Then
' Están superpuestas las horas
If aux <> "" Then aux = aux & " "
aux = aux & Format$(i) & "-" & Format$(j)
End If
End If
Next j
Next i
If aux = "" Then
snSuperpuestos = "No"
Else
snSuperpuestos = "Error " & aux ' Damos el error y entre que horarios
End If
End Function
Siguiendo con el ejemplo de antes, en el que los horarios estaban entre A2 e I2, la llamada a la función sería: =snSuperpuestos(A2:H2)
Pero también admite que le pases un rango de 4 filas por 2 columnas (para ver que no se superponen los horarios en la misma tarea. Eso sería con una llamada del estilo: =snSuperpuestos(A2:B5)
(Espero estar entendiendo bien tu problema, sino menudo rollo que te estoy soltando)
Lo que no acabo de aclararme es lo de que: en un día no se puede realizar la misma tarea por 2 o más personas si no hay una diferencia de 2 horas, pero luego dices que una misma persona puede realizar la misma tarea en 2 secciones a la vez, pero que el registro no debe superponerlas. Glup, aquí me lio un poco.
Si me puedes contar algo más quizás lo pille. De momento te voy dejando esto para saber, sobre todo, si va por el buen camino.
Sigo pensando en lo que planteas.
Quizás lo más sencillo sea definir una consulta en la pases los valores de las 8 celdas y te devuelva si hay o no superposición de horarios.
Esta función podría ser así:
Option Explicit
Function snSuperpuestos(ByVal rangoHoras As Range) As String
ReDim mHoras(1 To 2, 1 To 4) As Double
Dim i As Integer
Dim j As Integer
Dim aux As String
' El rango que nos pasen tiene que ser de 1 fila por 8 columnas o
' bien de 4 filas y 2 columnas
If (rangoHoras.Rows.Count <> 1 Or rangoHoras.Columns.Count <> 8) And _
(rangoHoras.Rows.Count <> 4 Or rangoHoras.Columns.Column <> 2) Then
MsgBox "El rango que admite esta función tiene que ser de 1x8 o " & _
"4x2 filas-columnas. Proceso terminado"
snSuperpuestos = "ERROR filas/columnas"
Exit Function
End If
' Cargamos nuestra matriz para después hacer los cálculos
For i = 1 To 2
For j = 1 To 4
If rangoHoras.Rows.Count = 1 Then ' Es de 1x8
mHoras(i, j) = rangoHoras.Cells(1, 2 * (j - 1) + i)
Else
mHoras(i, j) = rangoHoras.Cells(i, j)
End If
Next j
Next i
' Comprobamos que la hora inicial sea anterior a la final
For i = 1 To 4
If mHoras(1, i) > mHoras(2, i) Then
MsgBox "Error. Hora inicial posterior a la hora final"
snSuperpuestos = "ERROR H.Inicial>H.Final"
Exit Function
End If
Next i
' Comprobamos los casos que se superponen usando la matriz
aux = "" ' Para ir guardando los superpuestos
For i = 1 To 3
For j = i + 1 To 4
If mHoras(1, i) <> 0 Or mHoras(2, i) <> 0 Or _
MHoras(1, j) <> 0 Or mHoras(1, j) <> 0 Then
' Ninguno de los dos horarios son nulos. Se pueden comparar
' Diremos que 2 tramos de horas están superpuestos si...
' - La hora de inicio 1 es menor que la hora fin 2 y
' La hora fin 1 es mayor que la de inicio 2
' O bien se da el caso contrario:
' - La hora de inicio 2 es menor que la hora fin 1 y
' La hora fin 2 es mayor que la de inicio 1
If ((mHoras(1, i) <= mHoras(2, j) And mHoras(2, i) > mHoras(1, j))) Or _
((mHoras(2, i) <= mHoras(1, j) And mHoras(1, i) > mHoras(2, j))) Then
' Están superpuestas las horas
If aux <> "" Then aux = aux & " "
aux = aux & Format$(i) & "-" & Format$(j)
End If
End If
Next j
Next i
If aux = "" Then
snSuperpuestos = "No"
Else
snSuperpuestos = "Error " & aux ' Damos el error y entre que horarios
End If
End Function
Siguiendo con el ejemplo de antes, en el que los horarios estaban entre A2 e I2, la llamada a la función sería: =snSuperpuestos(A2:H2)
Pero también admite que le pases un rango de 4 filas por 2 columnas (para ver que no se superponen los horarios en la misma tarea. Eso sería con una llamada del estilo: =snSuperpuestos(A2:B5)
(Espero estar entendiendo bien tu problema, sino menudo rollo que te estoy soltando)
Lo que no acabo de aclararme es lo de que: en un día no se puede realizar la misma tarea por 2 o más personas si no hay una diferencia de 2 horas, pero luego dices que una misma persona puede realizar la misma tarea en 2 secciones a la vez, pero que el registro no debe superponerlas. Glup, aquí me lio un poco.
Si me puedes contar algo más quizás lo pille. De momento te voy dejando esto para saber, sobre todo, si va por el buen camino.
Sigo pensando en lo que planteas.
WOWw... desde ya la mejor puntuación, bueno... ahora me fijo de acomodar un poco esa info... por lo que veo es una gran matriz y de alguna manera solucionaría todo de un saque, perdón si la info que pase es insuficiente, agrego: el problema "A" lo solucione con la siguiente fórmula al final de cada linea:
=SI(Y(O(A1>=N1;B1<=M1);O(A1>=Z1;B1<=Y1);O(A1>=AL1;B1<=AK1);O(M1>=Z1;N1<=Y1);O(M1>=AL1;N1<=AK1);O(Y1>=AL1;Z1<=AK1));"ok";"Error")
Aclaro el tema de las 2 hs:
El caso "B" es para cada sección por separado, para 2 o más personas realizando la misma tarea el mismo día, o sea: tengo a Juan y a Pedro realizando la tareaX en la seccionX el día 10, la tarea no debe ejecutarse al mismo tiempo y entre la ejecución de uno y del otro debe de haber 2 hs o más, si la diferencia entre los horarios es menor a 2 sera error. Cabe la posibilidad de que la misma persona repita la tarea, para lo cual se ingresa nuevamente en una nueva fila con la nueva hora de ejecución
el caso "C" es semejante al caso "A" que es para la misma persona sobre un mismo día la variable es: misma tarea pero cubriendo las 4 secciones, hay que detectar la superposición de horas.
Nuevamente gracias y espero haber aclarado algunos planteos
=SI(Y(O(A1>=N1;B1<=M1);O(A1>=Z1;B1<=Y1);O(A1>=AL1;B1<=AK1);O(M1>=Z1;N1<=Y1);O(M1>=AL1;N1<=AK1);O(Y1>=AL1;Z1<=AK1));"ok";"Error")
Aclaro el tema de las 2 hs:
El caso "B" es para cada sección por separado, para 2 o más personas realizando la misma tarea el mismo día, o sea: tengo a Juan y a Pedro realizando la tareaX en la seccionX el día 10, la tarea no debe ejecutarse al mismo tiempo y entre la ejecución de uno y del otro debe de haber 2 hs o más, si la diferencia entre los horarios es menor a 2 sera error. Cabe la posibilidad de que la misma persona repita la tarea, para lo cual se ingresa nuevamente en una nueva fila con la nueva hora de ejecución
el caso "C" es semejante al caso "A" que es para la misma persona sobre un mismo día la variable es: misma tarea pero cubriendo las 4 secciones, hay que detectar la superposición de horas.
Nuevamente gracias y espero haber aclarado algunos planteos
Por lo que estoy viendo, las comprobaciones que quieres hacer son 'casi' para hacerlas en base de datos más que en una hoja de cálculo.
Voy a darle vueltas para simular desde VB un proceso del estilo de una base de datos.
Mientras tanto, algún detalle. ¿Una persona puede estar en más de una sección o pertenece siempre a la misma?
Entiendo que si una persona aparece en 2 líneas, tendremos que controlar la superposición de hasta 8 horas.
Seguiré dándole vueltas, pero tiene su jaleillo.
Voy a darle vueltas para simular desde VB un proceso del estilo de una base de datos.
Mientras tanto, algún detalle. ¿Una persona puede estar en más de una sección o pertenece siempre a la misma?
Entiendo que si una persona aparece en 2 líneas, tendremos que controlar la superposición de hasta 8 horas.
Seguiré dándole vueltas, pero tiene su jaleillo.
si... es complicado.
En términos generales, cualquier persona puede realizar cualquier tarea en cualquier sección.
Con el problema "A" se verifica que: en una sola sección, el mismo día, para cualquier persona no superponga hs en tareas (solucionado con la formulita lógica al final de cada fila)
Con el problema "B" se verifica que: en una sola sección, el mismo día, para una misma tarea que se ejecute 2 o 3 veces por la misma persona o diferentes, verificar entre cada vez que se realiza la tarea haya 2 hs o más de diferencia.
Con el problema "C" se verificaque: para las 4 secciones, el mismo día, la misma tarea, la misma persona no superponga hs.
Notas varias:
office 2003
En vez de usar formato de hora (HH:mm) se usa números enteros o con decimal 0,5 (o sea, la fracción más chica de tiempo es de 30 min=0,5)
Las personas no son infinitas, serán unas 10 o 15 en total
En columna fecha son 2 columnas, una para "lunes/martes/etc" la otra para el numero 1,2,3,..., 30,31, para evitar desconcatenar y demás yerbas
Como veras busque simplificar a números enteros y evitar formatos complicados, pero de por si, las opciones para "B" y"C" las veo medias complicadas, si necesitas algo más o alguna variable que se me escape, decime.
Nuevamente gracias
En términos generales, cualquier persona puede realizar cualquier tarea en cualquier sección.
Con el problema "A" se verifica que: en una sola sección, el mismo día, para cualquier persona no superponga hs en tareas (solucionado con la formulita lógica al final de cada fila)
Con el problema "B" se verifica que: en una sola sección, el mismo día, para una misma tarea que se ejecute 2 o 3 veces por la misma persona o diferentes, verificar entre cada vez que se realiza la tarea haya 2 hs o más de diferencia.
Con el problema "C" se verificaque: para las 4 secciones, el mismo día, la misma tarea, la misma persona no superponga hs.
Notas varias:
office 2003
En vez de usar formato de hora (HH:mm) se usa números enteros o con decimal 0,5 (o sea, la fracción más chica de tiempo es de 30 min=0,5)
Las personas no son infinitas, serán unas 10 o 15 en total
En columna fecha son 2 columnas, una para "lunes/martes/etc" la otra para el numero 1,2,3,..., 30,31, para evitar desconcatenar y demás yerbas
Como veras busque simplificar a números enteros y evitar formatos complicados, pero de por si, las opciones para "B" y"C" las veo medias complicadas, si necesitas algo más o alguna variable que se me escape, decime.
Nuevamente gracias
Creo que más o menos tengo una idea clara de los controles que quieres hacer.
La solución más correcta pasaría por crear un procedimiento que realizase todos los chequeos y te indicase los errores.
Para ello, el tratamiento de los datos debería hacerse de forma similar a una base de datos. Se crearía un tipo de datos 'tipoTarea' en el cual tendríamos la fecha, persona, sección y hora de inicio y fin de la tares.
A continuación se crearía una matriz de datos del tipo 'tipoTarea' y se cargaría con los datos de tu hoja de cálculo.
Una vez que tuviéramos la matriz cargada, se podría hacer todo tipo de controles.
En rasgos generales creo que eso es lo que se debería hacer. Ahora mismo estoy colapsado de trabajo y otras consultas, dame unos días e intentaré programarte tanto la carga como el código para los diferentes controles.
La solución más correcta pasaría por crear un procedimiento que realizase todos los chequeos y te indicase los errores.
Para ello, el tratamiento de los datos debería hacerse de forma similar a una base de datos. Se crearía un tipo de datos 'tipoTarea' en el cual tendríamos la fecha, persona, sección y hora de inicio y fin de la tares.
A continuación se crearía una matriz de datos del tipo 'tipoTarea' y se cargaría con los datos de tu hoja de cálculo.
Una vez que tuviéramos la matriz cargada, se podría hacer todo tipo de controles.
En rasgos generales creo que eso es lo que se debería hacer. Ahora mismo estoy colapsado de trabajo y otras consultas, dame unos días e intentaré programarte tanto la carga como el código para los diferentes controles.
Voy a preparar una hoja de cálculo simulando tus datos.
Si me dejas una dirección de correo te la envío cuando la tenga más o menos hecha.
Si me dejas una dirección de correo te la envío cuando la tenga más o menos hecha.
Gracias, puede ser a [email protected] / [email protected]
Te envío una hoja de cálculo programada con los 2 primero chequeos.
El proceso está basado en una base de datos auxiliar que graba en la carpeta temporal y borra al terminar el chequeo.
El caso "C" no es necesario ya que estaría contemplado en el caso "A". Ten en cuenta que al estar en base de datos el solapamiento de horarios no se verifica por filas como en Excel.
Supongo que te surgirán dudas, pregunta lo que quieras.
Siento haber tardado, pero estoy liadísimo.
El proceso está basado en una base de datos auxiliar que graba en la carpeta temporal y borra al terminar el chequeo.
El caso "C" no es necesario ya que estaría contemplado en el caso "A". Ten en cuenta que al estar en base de datos el solapamiento de horarios no se verifica por filas como en Excel.
Supongo que te surgirán dudas, pregunta lo que quieras.
Siento haber tardado, pero estoy liadísimo.
