Foro Excel

Hola chicos: ¿Como puedo hacer para que aparezca el plazo medio mensual entre fechas y sectores?. Bueno en el archivo que adjunto, se hace uno más idea. Muchas gracias por adelantado. Un saludo Lima

Esta es mi versión.
la columna sectores la depuré.
podria haberse mantenido con la palabra sector..31..32...
pero requeria utilizar derecha( ,largo( )-6) lo cual enturbiaba toda la formula
Espero que te sirva.

GALI

Muchas gracias, funciona perfectamente. Un saludo

Hola chicos,

Sin quitarle el merito a la solucion de galileogali, creo que el caso claramente "clama" por una tabla dinamica. Pienso que la forma mas eficiente es la del anexo aunque se sacrifica el calculo en tiempo real. Pero si me apurais se puede solucionar con un procedimiento VBA simple asociado al evento Worksheet_Change.

Perdón amigos, pero dije que funcionaba demasiado pronto, pues cuando una celda no tiene fecha, sale una cifra que no es correcta ¿Tiene solución? porque la verdad es que sí­ funciona pero con fechas. Adjunto el xls. Muchas gracias por anticipado y un saludo. Lima

1) Partamos de la base conceptual de que calcular el promedio de Resolucion de algo que no tiene plazo, es poco menos que incongruente....
2) Para estos casos "atajamos errores"...y eso hice

3) con un poco mas de tiempo, que en este momento no tengo, se podria "Nombretear" la formula, es decir definir Rangos o Formulas Nombradas y reducir sensiblemente esa formula en espejo.

GALI

Tengo una SOLUCION CON SUBTOTALES UNIDA UNA MACRO DE EVENTO, PERO DAME UN RATO....
QUE POR ESTOS PAGOS TAMBIEN SE COME....

Hola chicos,

Si hay tanto deseo de hacerlo mediante formulas, aqui va mi version

Ahi te mando otras posibilidades: 1 con subtotales
Escribe respetando las columnas
Debajo de promedio general
Sector Fecha Entrada y fecha salida
de cada Registro nuevo y se incoporara

la otra es con formulas, pero con casi todo "Nombreteado"
Agregas culaquier registro abajo del ultimo y se modificran los Promedios.
GALI

Hola galileogali,

Unos comentarios sobre las formulas de rangos dinamicos que usas para los nombres definidos, y entre ellas:

=COINCIDIR(99999999;$A$6:$A$300)

1) Como seguramente sabes, el mayor numero posible en una celda de Excel es 9.99999999999999E+307 que es casi 1E308 pero no llega hasta ahi. La idea original de la formula era la de usar el mayor numero posible. Veo que en este caso has interpretado (cosa que yo tambien hubiera hecho) que el mayor numero posible para los datos de la columna [A] es mucho menor que el de Excel por lo que has puesto 99999999, me imagino, para ahorrar espacio. Pues curiosamente, a partir de ciertos numeros, cuanto mas alto el numero mas corta puede ser la notacion. El numero que yo utilizo en este tipo de formulas es 1E307 que es mas corto y mas proximo al numero imposible (de hecho el ultimo que se puede escribir de forma tan corta) a la vez.

2) Tambien veo que en los nombres entrad, extrem, salid y sect has acortado el rango de la columna [A] a $A$6:$A$300. Aqui solo comentarte que la funcion COINCIDIR con busqueda no exacta (es decir con el tercer parametro omitido o en 1 o -1) y la INDICE son tan eficientes que casi resulta innecesario reducir el rango. Piensa que la busqueda binaria de la funcion COINCIDIR, si es que no encuentra el valor buscado antes, solo necesitara hasta el maximo de 17 evaluaciones en el rango de 65536 celdas (si no recuerdo mal). Y la funcion INDICE es practicamente instantanea. Por si fuese poco ambas funciones no son volatiles lo que implica que solo recalcularan si algo cambia en el rango que usan como argumento o si recalcula la formula que usa los nombres que las contienen. Por tanto es mas corto y mas "universal" usar el rango de la columna entera:
=COINCIDIR(1E307;$A:$A)

3) Finalmente, los nombres definidos en este caso se podria optimizarlos de la siguiente manera:
entrad =INDICE(rng;;2)
rng =$A$6:INDICE($C:$C;COINCIDIR(1E307;$A:$A))
salid =INDICE(rng;;3)
Sect =INDICE(rng;;1)

KL, mucho te agradezco tus comentarios tan ilustrativos, pero hete aqui que en principio hube de definir los rangos sobre ENTIRECOLUMN, pero se me RALLENTIZA...bifurcacion: HASTA ANTES DE TU COMENTARIO>>POR LA EXTENSION DE LA COLUMNA, DESPUES DE TU COMENTARIO>>¡VAYA UNO A SABER POR QUí‰!!!.
Respecto a usar 999999, crei quera mas que sobrado, como tu dijiste, y como no recordaba la expresion exponencial exacta, dije: Gali no te pases con la Levadura que le deja gusto fuerte al Pan!!
Hata Pronto
GALI

Hola GALI,

No digo que al añadir columna entera la formula se ponga mas rapida, sera mas lenta obviamente, pero de ahi a que se note en 300 celdas... En mi maquina (P4 1.8MHz, RAM 512MB) no noto diferencia alguna entre tu version y la mia. Ahora si, si extiendes la tabla hasta tener miles de filas sera normal que se te rallentice, pero por culpa de la formula del nombre Formul:

=SUMAPRODUCTO((Sect=Hoja1!$J8)*(MES(salid)=MES(Hoja1!F$5))*(salid-entrad+1))/SI(SUMAPRODUCTO((Sect=Hoja1!$J8)*(MES(salid)=MES(Hoja1!F$5)))=0;1;SUMAPRODUCTO((Sect=Hoja1!$J8)*(MES(salid)=MES(Hoja1!F$5))))

Este tipo de construcciones con SUMPRODUCT son lentisimas.

?Comentas si has hecho pruebas con las formulas que te puse en mi mensaje anterior?

Hola GALI,

Creo que ya he dado con la respuesta. Aunque yo no lo note visualmente en mi PC, el tiempo de recalculo si que sube 3 veces: en mi caso de 0.09 seg a 0.30 seg. El tema es que los nombres definidos se recalculan cada vez que se recalcula la formula que los usa. Ahora, parece que entre tu y yo hemos construido un micromodelo perfectamente [aunque por fortuna localmente] volatil usando funciones no volatiles Es decir:

1) todas las formulas en la hoja de calculo (20) invocan el nombre Formul (2 veces)

2) el nombre Formul invoca los nombres
entrad (1 vez)
salid (4 veces)
Sect (3 veces)

3) cada vez al recalcular el nombre Formul realiza operaciones con cada una de las 18 celdas del rango devuelto por cada uno de los nombres entrad, salid y Sect

4) cada uno de los nombres entrad, salid y Sect invoca el nombre rng (1 vez)

5) finalmente, el nombre rng realiza sus busquedas (17 ya que siempre busca un valor inexistente) cada vez que es invocada.

Total que tenemos un conjunto de celdas y nombres cuyas formulas estan comunicados continuamente. Esto multiplicado por la ineficiencia de las operaciones matriciales de la formula que usa SUMAPRODUCTO lleva claramente a una rallentizacion importante (3 veces).

Gracias a Dios, o mas bien al sistema "smart calculation" de Excel, que esto no vaya a mas, porque haciendo los numeros con los datos de arriba se ve perfectamente la barbaridad del calculo que se hace (nada mas y nada menos que 293.760 operaciones con 18 registros).

Yo diria que me he pasado tres pueblos al ofrecer la extension del rango de busqueda a columna entera. Y si el rango de datos crece a miles de filas la velocidad del modelo se volvera desastrosa.

Pero aun estableciendo el rango de busqueda para COINCIDIR en 294 filas (9 busquedas) el numero de operaciones se queda en 155.520 que tambien parece demasiado para 18 registros.

Conclusion: vuelvo a recomendar mis primera y segunda soluciones. Ambas usan una columna intermedia adicional y una tabla dinamica o formula matricial respectivamente.

Hola chicos,
Perdonad que no os haya contestado antes, pero he tenido que salir de viaje.
Las fórmulas funcionan bien para lo que yo querí­a, os agradezco vuestro interés, si tengo alguna pega ya os lo haré saber.
Muchas gracias Gali y KL. Un saludo Lima