Foro Excel

AL INTERROGANTE creo que una respueta podria ser usando BDCONTARA, aunque tambien me valgo de suma Producto
Donde estan los Criterios, selecciona la Provincia que desees contar.
La funcion es lenta, en 1200 registros, en 5000, mucho, mucho más.
Tal vez una macro pudiera hacerlo mas rapido....

Adrian:
¿Por qué no con =SUBTOTALES(3,$B$2:$B$1205)?, sin UDF, mas rapida y sin columna auxiliar

la siguiente formula que es relativamente rapida (dentro de lo que cabe obviamente) y no precisa de rangos auxiliares:

=SUMAPRODUCTO((A2:A1205=E2)*(COINCIDIR(B2:B1205&C2:C1205;B2:B1205&C2:C1205;0)=(FILA(A2:A1205)-FILA(A2)+1)))

una posible forma por macro (vease el anexo)

Hola, me parece que entendí­ todo mal, y nada qeu ver lo que hice.

Vos me dirás Gali, si estoy demasiado errado.
Igualmente no creo que se note mucha la diferencia de tiempo entre tu fórmula y mi (UDF + consulta SQL).

Saludos.

Ahh hoy me voy para Misiones!!!!!!!
Los voy a abandonar por una semana...

P@li...

por si interesa, aqui estan mis mediciones en Intel Core Duo 2.16 MHz, 3.4(4.0)Gb DDR2 SDRAM@667Mhz:

KL (formula)
12.05ms

KL (evento Worksheet_Change)
31.25ms

P@li (UDF)
45.12ms

GALI (formula con DDCONTARA)
293.34ms

Nota: no cuento la solucion de Adrian (formula con UDF + Autofiltro) porque, aparte de no resolver exactamente la tarea, tendria una duracion compuesta por 24.29ms del racalculo de la formula + el tiempo que se tarde en aplicar el Autofiltro.

Por cierto, para sumar valores filtrados con crirerios adicionales se puede usar una formula como la siguiente:

=SUMAPRODUCTO((A2:A1205=F1)*SUBTOTALES(3;DESREF(A2;FILA(A2:A1205)-FILA(A2);)))

la cual me tarda 1.9ms del racalculo de la formula + el tiempo que se tarde en aplicar el Autofiltro (vease el anexo)

KL, no se si estoy copiando, o soy yo que no comprendí­ los resultados que deben obtenerse, pero no me coinciden los que obtengo con tu fómula, con la macro y con los que expuso Galileo.

Puede que este haciendo algo mal?

Creo que de esta manera pude acelerar un poco mi UDF.

Pero me parece que pierde confiabilidad por el uso de:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Private Sub Workbook_Open()

Los uso para crear los Objetos y abrir la conección, y despues para eliminar los objetos y liberar la memoria.

Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=Yes;"""

KL, si no te molesta, podrí­as tomar el tiempo con esta modificación. Gracias!!

Saludos.

p@li";p="35153 escribió:KL, no se si estoy copiando, o soy yo que no comprendí­ los resultados que deben obtenerse, pero no me coinciden los que obtengo con tu fómula, con la macro y con los que expuso Galileo.

Puede que este haciendo algo mal?

Hola p@li,

el ultimo adjunto que he puesto no tiene nada que ver con la tarea de este hilo, sino forma parte de un comentario respecto a la sxolucion propuesta por Adrian

p@li";p="35154 escribió:Creo que de esta manera pude acelerar un poco mi UDF.

Hola P@li,

Has reducido el tiempo de recalculo a la mitad (a costa del uso de la memoria) - 20.42ms

Y tienes razon (no me habia fijado en ello antes) - nuestros codigos no resuelven la misma tarea. Yo entendi (e igual hubo una interferencia por parte del siguiente hilo: http://www.exceluciones.com/portal/viewtopic.php?t=7750 ) que la idea era contar las combinaciones UNICAS de cliente-servicio para una provincia seleccionada y creo que en esto la solucion de GALI y las mias coinciden.

Pues, creo que mi formula no hace lo esperado del todo correctamente. Al parecer, la version acertada era la que acabo de poner aqui: http://www.exceluciones.com/portal/view ... 5164#35161

Bueno por lo que veo creo que fue acertada mi decisoin de sacar el tema de la zona restringida, ya que sin manejo de FILES se entorpecí­a la exposición de propuestas.

P@li muy buenas tus intervenciones, trasladan a planteos en otra dimensión, pero ayudan a abrirnos la cabeza a todos....

En algun momento, tengo que reconocer, reste importancia a msegundos mas o menos, creo que hay razones de peso para tomarlos muy en serio, y paso a fundar mi opinión:
si bien no he tenido ocasion de probar el nuevo Excel, me encuentro leyendolas primeras bibliografias que caen en mis manos...
Lo más obvio: las nuevas dimensiones de la Hoja transforman a Excel en un posible FAGOCITADOR de Bases, Ahora bien la idea es sacarle el jugo al Maximo Posible y no despreciar esta caractersitica que MICROSOFT ha incorporado a riesgo de ensombrecer a ACCESS. Desde ya que el tema SPEED CALCULATION pasa a ser decisivo, como para que la decision por EXCEL sea rotunda

Sobre la formula de KL: sin palabras .
Si bien el recurso COINCIDIR-FILA, no es nuevo, francamente no se me habia ocurrido usarlo en este caso, tal vez porque todavia no internalizo la necesidad de la eficiencia en el calculo.
Comentario para los que quieren comprender un poco mejor la construccion:
=SUMAPRODUCTO((A2:A1205=E2)*(COINCIDIR(B2:B1205&C2:C1205;B2:B1205&C2:C1205;0)=(FILA(A2:A1205)-FILA(A2)+1)))

Doy por supuesto que se comprende el funcionamiento de SUMAPRODUCTO, para Imponer la condicion de que se busquen las duplas de cliente-servicio, se concatenan en el argumento ="elemento a buscar" y otro tanto en el argumento ="Rango a rastrear". Hasta ahi todo normal, pero al exigir que solo se consideren VERDADEROS aquellos registros en que COINCIDIR iguale al VALOR DE FILA (ajustado por el corrimiento mediante -FILA(A2)+1) solo se tomaran las PRIMERAS APARIOCIONES de CADA DUPLA, que es el unico caso en que COINCIDIR iguala a POSICION-FILA. No olvidemos que COINCIDIR frente a múltiples valores iguales en el Rango a BUSCAR SOLO DEVUELVE LA posicoin de la PRIMERA APARICION. No se si aclaro U OSCUREZCO...LA INTENCION ESTí

galileogali";p="35167 escribió:Sobre la formula de KL: sin palabras .
Si bien el recurso COINCIDIR-FILA, no es nuevo, francamente no se me habia ocurrido usarlo en este caso, tal vez porque todavia no internalizo la necesidad de la eficiencia en el calculo.
Comentario para los que quieren comprender un poco mejor la construccion:
=SUMAPRODUCTO((A2:A1205=E2)*(COINCIDIR(B2:B1205&C2:C1205;B2:B1205&C2:C1205;0)=(FILA(A2:A1205)-FILA(A2)+1)))

Hola GALI,

Gracias por tus observaciones (y por los elegios que creo que merezco), pero... como ya he dicho mas arriba creo que la formula es defectuosa. El problema esta en que las duplas se eliminan antes de comprobar el cumplimiento de la condicion (A2:A1205=E2) y por tanto es probable que no se cuenten todos los clientes que correspondan a la provincia seleccionada.

La ultima formula que hice es casi 4 veces mas lenta 43.12ms para 1200 filas en Excel2003 (aunque es increible la rapidez de la misma formula en el XL2007), pero, al parecer, funciona correctamente y esta en el archivo adjunto.

Desgraciadamente, no se como es la ultima solucion que ofreciste en el otro hilo y que te dijo Teresa que le valia, pero que necesitaba flexibilizar un poco los criterios