Formula

Hola, juanca !

... suma con un filtro ... formula matricial ... entenderla? [...]

==> tratare de explicar y espero que [aunque en saltos y 'revuelto'] el orden de los factores 'no altere el producto' ;)
1° para poder 'trabajar' la explicacion [sugiero] 'recortar' el rango a solo 'c2:c20' [o algo 'similar']
2° la parte de la formula '-Min(Fila(Rango))' [realmente] 'sale sobrando' puesto que 'resultara' SIEMPRE en...
el numero de fila 'mas bajo' del rango especificado [tambien sugiero] 'cambiarlo' a '-Fila(primer_celda_del_rango)'
3° la formula [modificada] 'queda' en: =Suma(1*(Si(SubTotales(2,DesRef(c2:c20,Fila(c2:c20)-Fila(c2),,1)),c2:c20,0)=8))

- ... subtotales devuelve solo ... falso o verdadero

==> aunque la funcion subtotales 'trabaja' con matrices, NO 'permite' que esas matrices puedan ser 'manipuladas' en formulas :(
si lo permitiera, seria 'suficiente' una formula [matricial tambien] como: =Suma(1*(SubTotales(2,C2:C20)=8))
o quizas un poco mas 'elaborada' como: =Suma(1*(Si(SubTotales(2,C2:C20)=8,SubTotales(2,C2:C20),0)))

- ... influenciado por el segundo argumento; la funcion desref ...

==> este es el 'secreto' ;) => para 'lograr' que subtotales 'devuelva' una matriz 'manipulable', el 2° argumento 'debe ser' DesRef
[es una tecnica que descubrio Laurent Longre alla por 1998, basado en un 'comportamiento no documentado' de DesRef]

- ... a su vez sobre el segundo argumento de esta (fila(C2 ...Min)
si analizo la formula siempre el valor de este argumento es cero (hice unas pruebas pero no se si siempre).

==> 'la parte' de la formula que siempre 'devuelve' cero es: '=Fila(C2:C20)-Fila(C2)' => formula 'modificada' < sin embargo, 'lo importante' NO ES ese cero que 'se ve' [como si fuera un 'valor final'] sino LA MATRIZ que 'resultá'
si 'usas' esa parte [o como formula 'unica' en una celda] y 'presionas' {F2}+{F9} 'veras' => LA MATRIZ 'resultante' <

Lo curioso del caso es que se cambio dicho valor por cero la formula no funciona [...]

==> se debe a que al 'sustituir' la formula [parcial] que 'devuelve' la matriz POR UN CERO, 'se pierde' el objetivo =>la matriz<:((

- ... cuando ... hace referencia a las celulas ... despues del filtro si todas ... apuntan para C2:C20.

==> con lo anterior debiera quedar claro que...
la 'manipulacion' de la matriz, 'combinada' con SubTotales [filtrada o no filtrada] y la 'condicionante' de 'cierto'
valor...
... es lo que 'hace que funcione' la formula ;)

[espero haber podido explicar de manera 'entendible'] ;)
_______
Saludos,
Héctor.
MS-MVP

Hector gracias por tus apreciaciones.
1- Aprendi y entendi la necesidad de utilizar como segundo
argumento la formula Desref. Lo que no entiendo es la
necesidad del segundo argumento de esta. Como analiza
Desref la matriz dada? sí el segundo argumento de
esta "deberia" ser la fila hacia arriba o hacia bajo
apartir de la referencia inicial(yo pienso en un numero
entero cualquiera, que es lo que medice la ayuda, no una
matriz).
2- Cual es la necesidad de restar el numero de la primera
fila (2), veo que sin esto la formula funciona pero no da
el valor correcto, pero no visualizo porque. Me parece que
el efecto es de llevar la matris de 2,3,4 ...30(FILA
(C2:C30)) para 0,1,2 ...28 pero no creo que sea este el
proposito. O sera un mero requisito por estar incluida en
una formula matricial?
3- La formula Subtotales devuelve una matriz de unos y
ceros, que me parece que se deberia de perder cuando la
formula SI le pasa el valor a su segundo argumento(el
rango C2:C30) y que seria lo normal. O mas bien es aqui
cuando la funcion subtotales le permite (a la funcion SI)
referse solo a los elementos que quedan despues del filtro
tomando como base la matriz de unos y ceros? Que creo no
es lo normal(hablo en el caso de funciones normales no
este caso). Porque creo no es normal? Porque yo veria la
necesidad de realizara una multiplicacion de la matriz de
la funcion subtotales(unos y ceros) con el rango C2:C30
para segir menteniendo los valores del rango despues del
filtro, que al igualarlos a 8 me daria una nueva matriz de
unos y ceros de lo elementos una vez hecho el filtro; los
cuales multiplico por 1 para sumarlos.

3-

Te envío un ejemplo con la formula subtotales.



"juanca" escribió en el mensaje
news:497901c37bdf$8bd19ec0$

El dia 12-09 Hector respondio una consulta de una suma

con

un filtro. La respuesta de Hector fue la siguiente

formula

matricial.
Alguien me puede ayudar a entenderla? De preferencia
Hector que es el papa de la criatura mas se que hay

otros

que me pueden ayudar :D
=Suma(1*(Si(SubTotales(2,DesRef(c2:c100,Fila(c2:c100)-

Min

(Fila(c2:c100)),,1)),c2:c100,0)=8))

Los puntos son:
-La formula sub totales devuelve solo un valor de falso

o

verdadero, ok, el cual esta dado influenciado por el
segundo argumento; la funsion desref, y a su ves sobre

el

segundo argumento de esta (fila(C2 ...Min), si analizo

la

formula siempre el valor de este argumento es cero(hice
unas pruebas pero no se si siempre). Lo curioso del caso
es que se cambio dicho valor por cero la formula no
funciona. Creo que aqui esta mi problema!!!.
-Mi terquedad y obstinacion me obligan a preguntar

cuando

la formula hace referencia a las celulas que quedan
despues del filtro si todas las referencias apuntan para
C2:C100.
-La igualdad a 8 o cualquier numero y la suma la

entiendo.

Es decir pensaba que entendia las formulas

matriciales...

Hector gracias por tus apreciaciones.
1- Aprendi y entendi la necesidad de utilizar como segundo
argumento la formula Desref. Lo que no entiendo es la
necesidad del segundo argumento de esta. Como analiza
Desref la matriz dada? sí el segundo argumento de
esta "deberia" ser la fila hacia arriba o hacia bajo
apartir de la referencia inicial(yo pienso en un numero
entero cualquiera, que es lo que medice la ayuda, no una
matriz).
2- Cual es la necesidad de restar el numero de la primera
fila (2), veo que sin esto la formula funciona pero no da
el valor correcto, pero no visualizo porque. Me parece que
el efecto es de llevar la matris de 2,3,4 ...30(FILA
(C2:C30)) para 0,1,2 ...28 pero no creo que sea este el
proposito. O sera un mero requisito por estar incluida en
una formula matricial?
3- La formula Subtotales devuelve una matriz de unos y
ceros, que me parece que se deberia de perder cuando la
formula SI le pasa el valor a su segundo argumento(el
rango C2:C30) y que seria lo normal. O mas bien es aqui
cuando la funcion subtotales le permite (a la funcion SI)
referse solo a los elementos que quedan despues del filtro
tomando como base la matriz de unos y ceros? Que creo no
es lo normal(hablo en el caso de funciones normales no
este caso). Porque creo no es normal? Porque yo veria la
necesidad de realizara una multiplicacion de la matriz de
la funcion subtotales(unos y ceros) con el rango C2:C30
para segir menteniendo los valores del rango despues del
filtro, que al igualarlos a 8 me daria una nueva matriz de
unos y ceros de lo elementos una vez hecho el filtro; los
cuales multiplico por 1 para sumarlos.

Hola, juanca !

... suma con un filtro ... formula matricial ...

entenderla? [...]

==> tratare de explicar y espero que [aunque en saltos

y 'revuelto'] el orden de los factores 'no altere el
producto' ;)

1° para poder 'trabajar' la explicacion

[sugiero] 'recortar' el rango a solo 'c2:c20' [o
algo 'similar']

2° la parte de la formula '-Min(Fila(Rango))'

[realmente] 'sale sobrando' puesto que 'resultara' SIEMPRE
en...

el numero de fila 'mas bajo' del rango

especificado [tambien sugiero] 'cambiarlo' a '-Fila
(primer_celda_del_rango)'

3° la formula [modificada] 'queda' en: =Suma(1*(Si

(SubTotales(2,DesRef(c2:c20,Fila(c2:c20)-Fila
(c2),,1)),c2:c20,0)=8))

- ... subtotales devuelve solo ... falso o verdadero

==> aunque la funcion subtotales 'trabaja' con matrices,

NO 'permite' que esas matrices puedan ser 'manipuladas' en
formulas :(

si lo permitiera, seria 'suficiente' una formula

[matricial tambien] como: =Suma(1*(SubTotales(2,C2:C20)=8))

o quizas un poco mas 'elaborada' como: =Suma(1*(Si

(SubTotales(2,C2:C20)=8,SubTotales(2,C2:C20),0)))

- ... influenciado por el segundo argumento; la funcion

desref ...

==> este es el 'secreto' ;) => para 'lograr' que

subtotales 'devuelva' una matriz 'manipulable', el 2°
argumento 'debe ser' DesRef

[es una tecnica que descubrio Laurent Longre alla

por 1998, basado en un 'comportamiento no documentado' de
DesRef]

- ... a su vez sobre el segundo argumento de esta (fila

(C2 ...Min)

si analizo la formula siempre el valor de este

argumento es cero (hice unas pruebas pero no se si
siempre).

==> 'la parte' de la formula que siempre 'devuelve' cero

es: '=Fila(C2:C20)-Fila(C2)' => formula 'modificada' <> sin embargo, 'lo importante' NO ES ese cero
que 'se ve' [como si fuera un 'valor final'] sino LA
MATRIZ que 'resultá'

si 'usas' esa parte [o como formula 'unica' en

una celda] y 'presionas' {F2}+{F9} 'veras' => LA
MATRIZ 'resultante' <>

Lo curioso del caso es que se cambio dicho valor por

cero la formula no funciona [...]

==> se debe a que al 'sustituir' la formula [parcial]

que 'devuelve' la matriz POR UN CERO, 'se pierde' el
objetivo =>la matriz<>:((

- ... cuando ... hace referencia a las celulas ...

despues del filtro si todas ... apuntan para C2:C20.

==> con lo anterior debiera quedar claro que...
la 'manipulacion' de la matriz, 'combinada' con

SubTotales [filtrada o no filtrada] y la 'condicionante'
de 'cierto'

valor...
... es lo que 'hace que funcione' la formula ;)

[espero haber podido explicar de

manera 'entendible'] ;)

_______
Saludos,
Héctor.
MS-MVP


.

Hola, JH !

==> tengo que 'salirme del esquema' de tu pregunta para [tratar de] explicar de manera 'directa' ;)
es necesario 'destripar' la formula para analizar los 'efectos' de los argumentos en cada parte de ella
estoy dando por 'entendido' el uso de la funcion DesRef [y sus argumentos]
y 'partimos' de los siguientes 'valores entendidos'

1-. la funcion SubTotales NO 'permite' la 'manipulacion' de la matriz con que 'trabaja' [a menos que se use]...
2.- la funcion DesRef [como su segundo argumento] 'devolvera' una matriz de celdas en el rango [siempre y cuando]...
3.- 'su' segundo argumento [filas_a_desplazar] debe ser [sea] una matriz, que 'significa' para DesRef [en este caso] que...
4.- 'su' primer argumento [la celda 'de partida'] 'ira bajando' por la matriz de celdas [del segundo agumento], y para que...
5.- el primer 'desplazamiento' de filas sea CERO =>que inicie en la misma celda 'de partida'<= [se debe restar 'ella misma']
6.- al segundo argumento [filas_a_desplazar] se 'complementa' con la resta de la fila de la '1er_celda_del_rango'.

==> dicho sea 'de paso', el primer argumento en DesRef NO 'necesita' ser una matriz, sino [solo] una referencia 'de partida'
la formula [nuevamente] modificada seria: =Suma(1*(Si(SubTotales(2,DesRef(c2,Fila(c2:c30)-Fila(c2),0)),c2:c30,0)Ã3))

==> 'destripando' la parte 'central' de la formula [la funcion DesRef], se obtienen los siguientes 'parciales'
1.- 'c2' es la referencia a la celda 'de partida' [primer argumento]
2.- 'Fila(c2:c30)-Fila(c2)' [el segundo argumento] 'devlelve' la matriz [de 'enteros'] por la que 'ira bajando' el argumento
anterior
Fila(c2:c30) 'devuelve' una matriz con los numeros de fila de las celdas 'pasadas' [como parte de] al segundo argumento
la parte 'final' [-Fila(c2)] se encarga de 'ajustar' para que el primer desplazamiento sea SIEMPRE CERO
3.- 0 [tercer argumento] 'hara' que la columna de 'desplazamiento' de la referencia sea LA MISMA

la matriz 'devuelta' por la funcion DesRef es ahora [por fin] 'manipulable' [como segundo argumento] por la funcion SubTotales
la matriz que 'devuelve' la funcion SubTotales 'muestra' unos o ceros [verdadero o falso] por cada fila [si esta filtrada o no]

==> 'el resto' es...
1.- 'ver' si cada fila [de las celdas del rango] esta filtrada o no.
2.- 'compararla' con la condicion 'deseada' y... ¡ FIN !!! [es decir, etc. etc. etc.] ;)
_______
Saludos,
Héctor.
MS-MVP