Frecuencias en un rango con filtro

Problema Resuelto, me tarde un poco pero ya lo resolví yo mismo

Gracias de todos modos



"Jaime Javier Hernández M." <jaimejavier(nospam)@hotmail.com> escribió en el
mensaje news:

Hola Grupo:
Tengo una hoja de calculo con las respuestas a unas encuestas asi:

Pregu1 Pregunta2 Pregunta 3 ...
Sujeto1 10 6 3
Sujeto2 8 1 7
Sujeto3 5 2 6
Sujeto4 7 6 9
...

Quisiera obtener una tabla que me dijera la frecuencia con la que se
respondio a cada calificacion Asi
Calif Frecuencia
1 1
2 1
3 1
4 0
5 1
6 3
7 2
8 1
9 1
10 1

Se que para esto existe la funcion Frecuencia pero quisiera que al filtrar
la lista de calificaciones con un filtro Automático Aparecieran las
frecuencias de los datos filtrados (solo los que se ven)

SE PUEDE? .. ALGUIEN SABE COMO SE HACE?

Cualquier comentario sera de gran utilidad
Gracias de antemano

Jaime J

hola, Jaime Javier !

1) si 'atendemos' a tu siguiente consulta [funcion definida por el usuario]...
'debieramos asumir' que la 'solucion' que encontraste ha sido una funcion personalizada -?-

2) si deseas que 'otros' tengan acceso a dicha funcion... [probablemente]...
encontraras uno que otro 'detalle' como... compartir el archivo [o el codigo] con los que la 'necesiten' :))

3) si te llegara a interesar otro 'tipo' de alternativas -?-
puedes usar funciones 'integradas' de excel y evitar el uso [o la necesidad] de codigos 'transportables' ;)

4) si suponemos que el arreglo [de tu ejemplo exclusivamente] estuviera en el rango 'A2:D5' [sin los puntos suspensivos]...
a) la funcion frecuencia 'debiste' aplicarla a un rango con 'arreglo matricial' [es decir]...
si pones las calificaciones [1 a 10] en el rango -p.e.- 'A8:A17'...
seleccionas el rango contiguo [B8:B17] e introduces la siguiente 'formula'...
-> =frecuencia($b$2:$d$5,$a$8:$a$17) [en una sola celda y terminando con crl+mayus+enter]
b) puedes sustituir el procedimiento anterior si pones -p.e.- en 'B8' la siguiente formula...
-> =contar.si($b$2:$d$5,a8) [y la copias hasta la celda 'B17'] <= NO es de entrada matricial ;)

5) para hacer 'conteos' a rangos con autofiltros de solo las celdas/filas 'visibles' [mismo rango de ejemplo]
introduces -p.e.- en 'B8' alguna de las siguientes formulas [y las copias hasta la celda 'B17']...
a) =suma(--(si(subtotales(2,desref($b$2,fila($b$2:$b$5)-fila($b$2),columna($b$2:$d$2)-columna($b$2))),$b$2:$d$5,0)¨))
b) =sumaproducto(--($b$2:$d$5¨),subtotales(2,desref($b$2,fila($b$2:$b$5)-fila($b$2),columna($b$2:$d$2)-columna($b$2))))
-> OJO: la primera SI es 'matricial'... la introduces con ctrl+mayus+enter EN la celda inicial [B8] y luego la copias al 'resto' :))

solo toma en cuenta que mi sistema usa como separador de argumentos a la coma ',' NO al punto y coma ';' :D
y habria que ajustar los rangos de ejemplo en las formulas a los rangos 'reales' de tus hojas ;)

si cualquier duda [o informacion adicional]... comentas ?
saludos,
hector.

__ consulta 2 __

Problema Resuelto, me tarde un poco pero ya lo resolvi yo mismo
Gracias de todos modos

__ consulta 1 __

Tengo una hoja de calculo con las respuestas a unas encuestas asi:
Pregu1 Pregunta2 Pregunta 3 ...
Sujeto1 10 6 3
Sujeto2 8 1 7
Sujeto3 5 2 6
Sujeto4 7 6 9
...
... obtener una tabla que me dijera la frecuencia con la que se respondio a cada calificacion
Calif Frecuencia
1 1
2 1
3 1
4 0
5 1
6 3
7 2
8 1
9 1
10 1
Se que... existe la funcion Frecuencia pero quisiera que al filtrar la lista... Aparecieran las frecuencias de los datos filtrados [...]
Cualquier comentario sera de gran utilidad

Hector

Gracias por responder.

En efecto la solucion a este problema la encontre desarrollando una pequeña
macro que cuenta sobre los datos solo en las celdas visibles , sin embargo
tenia el problema de que necesitaba crear mas hojas con esta solucion y me
la pasé pegando la funcion a cada libro que la necesita.

Probé la fórmula A) del inciso 5 de tu respuesta

[a)
=suma(--(si(subtotales(2,desref($b$2,fila($b$2:$b$5)-fila($b$2),columna($b$2:$d$2)-columna($b$2))),$b$2:$d$5,0)¨))]

¡y funciona Bien , Aunque no sé por que! . Debiste pasar un buen rato
ideando la solución y te lo agradezco, Perdona que te moleste pero Ojalá
pudieras comentar un poco sobre cómo funciona la solucion, o si pudieras
orientarme sobre donde aprender el uso de funciones matriciales Pues te lo
agradecería igual, Ya veo que la solución solo sirve si la introduces con el
Ctrl mayusc Enter

Nuevamente Gracias por tomarte el tiempo

Jaime
"Héctor Miguel" escribió en el mensaje
news:%

hola, Jaime Javier !

1) si 'atendemos' a tu siguiente consulta [funcion definida por el
usuario]...
'debieramos asumir' que la 'solucion' que encontraste ha sido una
funcion personalizada -?-

2) si deseas que 'otros' tengan acceso a dicha funcion...
[probablemente]...
encontraras uno que otro 'detalle' como... compartir el archivo [o el
codigo] con los que la 'necesiten' :))

3) si te llegara a interesar otro 'tipo' de alternativas -?-
puedes usar funciones 'integradas' de excel y evitar el uso [o la
necesidad] de codigos 'transportables' ;)

4) si suponemos que el arreglo [de tu ejemplo exclusivamente] estuviera en
el rango 'A2:D5' [sin los puntos suspensivos]...
a) la funcion frecuencia 'debiste' aplicarla a un rango con 'arreglo
matricial' [es decir]...
si pones las calificaciones [1 a 10] en el rango -p.e.- 'A8:A17'...
seleccionas el rango contiguo [B8:B17] e introduces la siguiente
'formula'...
-> =frecuencia($b$2:$d$5,$a$8:$a$17) [en una sola celda y
terminando con crl+mayus+enter]
b) puedes sustituir el procedimiento anterior si pones -p.e.- en 'B8'
la siguiente formula...
-> =contar.si($b$2:$d$5,a8) [y la copias hasta la celda 'B17']
<= NO es de entrada matricial ;)

5) para hacer 'conteos' a rangos con autofiltros de solo las celdas/filas
'visibles' [mismo rango de ejemplo]
introduces -p.e.- en 'B8' alguna de las siguientes formulas [y las
copias hasta la celda 'B17']...
a)
=suma(--(si(subtotales(2,desref($b$2,fila($b$2:$b$5)-fila($b$2),columna($b$2:$d$2)-columna($b$2))),$b$2:$d$5,0)¨))
b)
=sumaproducto(--($b$2:$d$5¨),subtotales(2,desref($b$2,fila($b$2:$b$5)-fila($b$2),columna($b$2:$d$2)-columna($b$2))))
-> OJO: la primera SI es 'matricial'... la introduces con ctrl+mayus+enter
EN la celda inicial [B8] y luego la copias al 'resto' :))

solo toma en cuenta que mi sistema usa como separador de argumentos a la
coma ',' NO al punto y coma ';' :D
y habria que ajustar los rangos de ejemplo en las formulas a los rangos
'reales' de tus hojas ;)

si cualquier duda [o informacion adicional]... comentas ?
saludos,
hector.

__ consulta 2 __

Problema Resuelto, me tarde un poco pero ya lo resolvi yo mismo
Gracias de todos modos

__ consulta 1 __

Tengo una hoja de calculo con las respuestas a unas encuestas asi:
Pregu1 Pregunta2 Pregunta 3 ...
Sujeto1 10 6 3
Sujeto2 8 1 7
Sujeto3 5 2 6
Sujeto4 7 6 9
...
... obtener una tabla que me dijera la frecuencia con la que se respondio
a cada calificacion
Calif Frecuencia
1 1
2 1
3 1
4 0
5 1
6 3
7 2
8 1
9 1
10 1
Se que... existe la funcion Frecuencia pero quisiera que al filtrar la
lista... Aparecieran las frecuencias de los datos filtrados [...]
Cualquier comentario sera de gran utilidad

hola, Jaime Javier !

... la formula A) del inciso 5 de tu respuesta
[a) =suma(--(si(subtotales(2,desref($b$2,fila($b$2:$b$5)-fila($b$2),columna($b$2:$d$2)-columna($b$2))),$b$2:$d$5,0)¨))]
... funciona Bien, Aunque no se por que!...
... comentar un poco sobre como funciona la solucion, o... sobre donde aprender el uso de funciones matriciales
... veo que la solucion solo sirve si la introduces con el Ctrl mayusc Enter [...]

1) la formula de la opcion 'b)'... te da el mismo resultado y... NO es de entrada matricial ;)
=sumaproducto(--($b$2:$d$5¨),subtotales(2,desref($b$2,fila($b$2:$b$5)-fila($b$2),columna($b$2:$d$2)-columna($b$2))))

2) [algunos] sitios que ofrecen explicaciones y ejemplos para trabajar con matrices y formulas matriciales [cortesia de KL]...
Bob Umlas: http://www.emailoffice.com/excel/ar...umlas.html (uno de los mas reconocidos)
Bob Phillips: http://www.xldynamic.com/source/xld...ODUCT.html (uso de SUMPRODUCT)
Chip Pearson: http://www.cpearson.com/excel/array.htm (bastante completo)
Laurent Longre: http://tinyurl.com/8o56f (un ejemplo interesante)
ExcelTip: http://www.exceltip.com/exceltips.p...ory&ID6
Ozgrid: http://www.ozgrid.com/Excel/arrays.htm
Alan Beban http://home.pacbell.net/beban/

3) hace como tres a#os 'tuve que' [intentar] 'explicar' una formula que propuse [muy] similar a la de ahora
la respuesta es 'la misma' [claro que 'adaptada' a la 'nueva' formula] ;)
espero no dejarte 'pior' en cuanto a matrices y formulas matriciales :))

si cualquier duda... comentas?
saludos,
hector.
==sumaproducto(--($b$2:$d$5¨),subtotales(2,desref($b$2,fila($b$2:$b$5)-fila($b$2),columna($b$2:$d$2)-columna($b$2))))

la parte central de la formula propuesta es la funcion desref como segundo argumento de la funcion subtotales
aunque la funcion subtotales 'trabaja' con matrices, NO 'permite' que esas matrices puedan ser 'manipuladas' en formulas
para lograr que subtotales devuelva una matriz manipulable, su segundo argumento ->debe ser<- desref
[es una tecnica que descubrio Laurent Longre alla por 1998, basado en un 'comportamiento no documentado' de desref]

dando por 'entendido' el uso de la funcion desref [y sus argumentos], '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...
a) 'su' primer argumento [la celda 'de partida'] 'ira bajando' por la matriz de celdas [del segundo agumento], y para que...
b) el primer 'desplazamiento' de filas sea CERO =>que inicie en la misma celda 'de partida'<= [se debe restar 'ella misma']
c) por lo tanto, al segundo argumento [filas_a_desplazar] se 'complementa' con la resta de la fila de la '1er_celda_del_rango'.
4) su tercer argumento [columnas_a_desplazar]... realiza un comportamiento similar al segundo argumento...
[solo que 'transportado' a las columnas en lugar de a las filas] :))

'destripando' la parte 'central' de la formula [la funcion desref], se obtienen los siguientes 'parciales'
1) '$b$2' es la referencia a la celda 'de partida' [primer argumento]
2) 'fila($b$2:$b$5)-fila($b$2)' [segundo argumento] 'devuelve' la matriz [de 'enteros'] por la que 'ira bajando' la celda 'de partida'
'fila($b$2:$b$5)' devuelve una matriz con los numeros de fila de las celdas 'pasadas' [como parte de] al segundo argumento
la parte 'final' [-fila($b$2)] se encarga de 'ajustar' para que el primer desplazamiento sea SIEMPRE CERO
3) [tercer argumento] igual que con las filas... pero ahora por las columnas :D

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) 'sumar' una unidad por cada celda 'visible' cuyo dato coincida con la calificacion 'buscada' y... FIN !!! ;)

Muy Bien Hector
Y excelentemente bien explicado
El día que vengas a Puebla avisame para invitarte una cerveza :)

Jaime J.




"Héctor Miguel" escribió en el mensaje
news:e20P$

hola, Jaime Javier !

... la formula A) del inciso 5 de tu respuesta
[a)
=suma(--(si(subtotales(2,desref($b$2,fila($b$2:$b$5)-fila($b$2),columna($b$2:$d$2)-columna($b$2))),$b$2:$d$5,0)¨))]
... funciona Bien, Aunque no se por que!...
... comentar un poco sobre como funciona la solucion, o... sobre donde
aprender el uso de funciones matriciales
... veo que la solucion solo sirve si la introduces con el Ctrl mayusc
Enter [...]

1) la formula de la opcion 'b)'... te da el mismo resultado y... NO es de
entrada matricial ;)

=sumaproducto(--($b$2:$d$5¨),subtotales(2,desref($b$2,fila($b$2:$b$5)-fila($b$2),columna($b$2:$d$2)-columna($b$2))))

2) [algunos] sitios que ofrecen explicaciones y ejemplos para trabajar con
matrices y formulas matriciales [cortesia de KL]...
Bob Umlas: http://www.emailoffice.com/excel/ar...umlas.html (uno
de los mas reconocidos)
Bob Phillips: http://www.xldynamic.com/source/xld...ODUCT.html (uso
de SUMPRODUCT)
Chip Pearson: http://www.cpearson.com/excel/array.htm (bastante
completo)
Laurent Longre: http://tinyurl.com/8o56f (un ejemplo interesante)
ExcelTip: http://www.exceltip.com/exceltips.p...ory&ID6
Ozgrid: http://www.ozgrid.com/Excel/arrays.htm
Alan Beban http://home.pacbell.net/beban/

3) hace como tres a#os 'tuve que' [intentar] 'explicar' una formula que
propuse [muy] similar a la de ahora
la respuesta es 'la misma' [claro que 'adaptada' a la 'nueva' formula]
;)
espero no dejarte 'pior' en cuanto a matrices y formulas matriciales
:))

si cualquier duda... comentas?
saludos,
hector.
=> =sumaproducto(--($b$2:$d$5¨),subtotales(2,desref($b$2,fila($b$2:$b$5)-fila($b$2),columna($b$2:$d$2)-columna($b$2))))

la parte central de la formula propuesta es la funcion desref como segundo
argumento de la funcion subtotales
aunque la funcion subtotales 'trabaja' con matrices, NO 'permite' que esas
matrices puedan ser 'manipuladas' en formulas
para lograr que subtotales devuelva una matriz manipulable, su segundo
argumento ->debe ser<- desref
[es una tecnica que descubrio Laurent Longre alla por 1998, basado en un
'comportamiento no documentado' de desref]

dando por 'entendido' el uso de la funcion desref [y sus argumentos],
'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...
a) 'su' primer argumento [la celda 'de partida'] 'ira bajando' por la
matriz de celdas [del segundo agumento], y para que...
b) el primer 'desplazamiento' de filas sea CERO =>que inicie en la
misma celda 'de partida'<= [se debe restar 'ella misma']
c) por lo tanto, al segundo argumento [filas_a_desplazar] se
'complementa' con la resta de la fila de la '1er_celda_del_rango'.
4) su tercer argumento [columnas_a_desplazar]... realiza un comportamiento
similar al segundo argumento...
[solo que 'transportado' a las columnas en lugar de a las filas] :))

'destripando' la parte 'central' de la formula [la funcion desref], se
obtienen los siguientes 'parciales'
1) '$b$2' es la referencia a la celda 'de partida' [primer argumento]
2) 'fila($b$2:$b$5)-fila($b$2)' [segundo argumento] 'devuelve' la matriz
[de 'enteros'] por la que 'ira bajando' la celda 'de partida'
'fila($b$2:$b$5)' devuelve una matriz con los numeros de fila de las
celdas 'pasadas' [como parte de] al segundo argumento
la parte 'final' [-fila($b$2)] se encarga de 'ajustar' para que el
primer desplazamiento sea SIEMPRE CERO
3) [tercer argumento] igual que con las filas... pero ahora por las
columnas :D

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) 'sumar' una unidad por cada celda 'visible' cuyo dato coincida con la
calificacion 'buscada' y... FIN !!! ;)