No sumar celdas ocultas

12 envíos / 0 nuevos
Último envío
pacomegia
Imagen de pacomegia
Offline
última acción: Hace 8 horas 19 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 27/12/2006 - 23:26
Puntos: 4220
No sumar celdas ocultas

Una de las lectoras de mi libro me ha planteado la siguiente cuestión:

"Hay algo que me resulta muy útil pero no consigo aplicarlo.
Es el caso de las celdas especiales, y concretamente para hacer una suma y
evitar casillas que están ocultas. Me podríais decir como hacerlo??? No
soy capaz ...

Gracias"

 

Pues bien, la función que debes emplear para sumar sólo las celdas visibles es SUBTOTALES
Esta función permite realizar diferentes operaciones: suma, producto, contar, etc. esto se indica con el primer argumento de esta función.
En el ejemplo de la suma, este argumento puede ser:
9 para que sume las celdas ocultas, o
109 para que sólo opere con las celdas visibles.

por ejemplo, la fórmula tendría un aspecto como este:
=SUBTOTALES(109;rango_a_sumar)
 

Espero que con esto quede resuelto tu problema.

ah, y gracias por haber comprado el libro 

visitante (no verificado)
Imagen de visitante
Re: No sumar celdas ocultas

qué bueno!!!

visitante (no verificado)
Imagen de visitante
Re: No sumar celdas ocultas

Sobre este mismo tema, hay otro punto muy interesante

1- Usando autofiltro seleccionas datos que pertenecen al criterio de filtrado

2-Tomando en cuenta que tengo una celda con un valor "guia", ¿como hacer para que solamente aplique la formula y solo aplique para los resultados que se ven en el autofiltro?

 

Ejemplo

Toda la columna "A" tiene datos que corresponden a "Carne" y "Lacteos",

La columna "B" tiene los datos de venta

La columna "D" tiene un dato que busco para ayuda con el filtro

Ahora cuando hago un filtrado y coloco unicamente "Carne" y en la celda "D" tambien coloco carne (se usa este dato en el final de la formula),  la formula tiene que filtrar y solamente tomar los datos visibles en ese momento (ignorar lo que el autofiltro oculto). Tengo una formula que ya lo hace, pero me parece muy compleja y estoy seguro que debe existir otra forma (la formula la encontre en internet)

=SUMPRODUCT(B3:B12,(SUBTOTAL(3,OFFSET(B3:B12,ROW(B3:B12)-MIN(ROW(B3:B12)),,1)))*((A3:A12)=G1))

Se ocupa esta formula porque tengo en otra hoja el listado de todos mis productos y al aplicar esta formula me da la suma por cada producto, pero en algunas ocasiones los usuarios quieren filtrar el origen de datos y colocar por ejemplo las ventas de un mes especifico, entonces la formula tiene que recalcular tomando en cuenta el filtrado que acaba de hacer el usuario y entonces para cada producto recalcular las ventas por cada mes, la formula solo debe sumar las celdas que en ese momento son visibles y lo que oculto el autofiltro ignorarlo.

¿Alguna idea de como simplificar la formula?

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 8 horas 19 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 27/12/2006 - 23:26
Puntos: 4220
Re: No sumar celdas ocultas

Supongo que en G1 tendrás carne o pescado, vamos el tipo de producto que quieres sumar.

supongo también que todo el lío ese de Subtotal(3,  el offset y las filas sirva para operar con las celdas visibles. Esto es lo que devuelve 0 o 1 según esté oculto o visible.En teoría subtotales 3 debería considerar también las celdas ocultas, aunque veo que no las cuenta (por si acaso yo emplearía 103.

Al final estás utilizando la función sumaproducto para multiplicar por cero o por uno y así tener en cuenta los datos que cumplen el criterio de G1.

No le veo mucho sentido a eso de MIN(FILA(B3:B12)), porque esto siempre será la fila de la celda B3, así que sería lo mismo que FILA(B3), en tu versión en inglés ROW(B3).

El tema de la función OFFSET, o DESREF en español, es para devolver un rango, en vez de un valor, porque la función SUBTOTAL sólo admite rangos y no listas de valores, así que como para operar con las visibles necesitamos SUBTOTALES, estamos obligados a generar un rango o más bien una matriz de rangos.

parece que no se puede simplificar mucho más esta fórmula ¿no?

------
Ya sé Excel, pero necesito más

envios gratis a todo el mundo

visitante (no verificado)
Imagen de visitante
Re: No sumar celdas ocultas

Hola amigos,

Esta consulta la he visto en diferentes sitios.

Este tema se soluciona trabajando con VBA desplazandose mediante el offset.

Al desplazarse utilicen la propiedad ActiveCell.Height

cuando tenemos celdas ocultas, la altura es cero, en consecuencia damos las indicaciones para cada caso; cuando esta oculta, que viene a ser cuando la altura es cero, y cuando está visible, que vendría a ser cuando la celda tiene altura.

Espero que esta referencia sea de utilidad.

Saludos,

Miguel Alarco

miguel_alarco@yahoo.com

Cron
Imagen de Cron
Offline
última acción: Hace 4 horas 6 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 4375
Re: No sumar celdas ocultas

Es interesante, pero las celdas ocultas tienen una manera más sencilla de detectarse: su propiedad .hidden es true.

visitante (no verificado)
Imagen de visitante
Re: No sumar celdas ocultas

Saludos.

 

Tengo un problema similar y en efecto esta podría ser la respuesta salvo por un detalle. Al eliminar el filtro o desplegar "todas" se modifica el valor calculado con subtotal y las celdas que estaban ocultas al estar visibles de nuevo intervienen en el calculo final. Y resulta que debe agrupar con el filtro varios datos y sumarlos, pero los datos ocultos deben ser sumados luego con un nuevo valor para el criterio del filtro.

 



 

Por ejemplo debo filtrar datos cuyo valor sea solo 50 y sumarlo pero luego debo filtrar de nuevo datos con valor 10 y sumarlos también.... Como dije al modificar el filtro pierdo la suma. y no puedo tomar solo el valor porque debe quedar la formula por si acaso se modifica un dato origen se actualice la suma. Alguna idea?

 



 

Hay alguna manera de extraer del filtro la referencia a cada una de las celdas sin tomar en cuenta las ocultas?

 

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 8 horas 19 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 27/12/2006 - 23:26
Puntos: 4220
Sumar.SI

en tu caso el criterio para la suma es independiente de lo que esté visto u oculto.

Utiliza la función SUMAR.SI  o  SUMAR.SI.CONJUNTO para sumar las celdas de aquellos datos que cumplen una condición.

Si tu condición es más complicada, prueba con el asistente para suma condicional, que es un complemento que viene con Excel.

envios gratis a todo el mundo

visitante (no verificado)
Imagen de visitante
Re: No sumar celdas ocultas

GRACIAS!!!!!

 

TRemendo dato de ayuda....

 

Muchas Gracias!

visitante (no verificado)
Imagen de visitante
Re: No sumar celdas ocultas

Muy buen a aportación

Jorge Esparza (no verificado)
Imagen de visitante
Formula por columna

Porque cuando haces esa formula en Filas si te descuenta las cantidades de Filas Ocultas, pero cuando es por columna no lo descuenta

Jorge Esparza (no verificado)
Imagen de visitante
Error en Suma de Subtotales

Buenos Dias,

       Porque cuando sumas por columnas no te descuenta las cantidades ocultas por columna solamente cuando lo haces en Fila, he utilizado la Funcion 9 y 109 y no descuenta el valor por columnas