No sumar celdas ocultas

33 envíos / 0 nuevos
Último envío
pacomegia
Imagen de pacomegia
Offline
última acción: Hace 17 horas 55 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 11175
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 17 horas 55 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 11175
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

 

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

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 8 meses 1 día
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 12735
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 17 horas 55 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 11175
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.

 

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

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

Ascanio (no verificado)
Imagen de visitante
Filtro

Buenas tardes. Acabo de realizar una tabla con filtros. Mi problema es que una vez que filtro las columnas de datos la suma que tengo en algunas columnas no se actualizan y desaparecen. Quisiera una ayuda. Gracias.

 

Parvat (no verificado)
Imagen de visitante
Estimados, agradezco la ayuda

Estimados, agradezco la ayuda para resolver una situacion similar a la planteada, como obtener subtotales de una columna en la que hay celdas ocultas. La situacion es la siguiente: tengo una tabla en la que registro los movimientos de mercaderias con clientes: los envios (importe que va a una columna, debe), y los pagos (importe que va a otra columna, haber). La tabla me indica la sucesion de movimientos, fecha por fecha. Lo que necesito es ir obteniendo la evolucion del saldo de ese cliente, para lo cual es necesario sumar los egresos (importe del debe) a los ingresos (importe del haber, que es un numero negativo). ¿Como debo hacerlo? Se suma a esto el hecho de que la tabla tiene los registros de todos los clientes, por lo que al filtrar quedan ocultos los registros de los clientes no filtrados. Espero que se comprenda. Gracias!

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 17 horas 55 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 11175
crea un tema nuevo en el foro

Es mejor crear un tema nuevo en el foro para esta pregunta concreta que añadir un comentario en un tema antiguo (sólo hay que estar registrado en el sitio para poder crear un tema nuevo)

Mejor si se adjunta además un archivo de ejemplo.

 

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

HORACIO (no verificado)
Imagen de visitante
FILTRO

AGREGA UNA FILA MAS ARRIBA DE LOS FILTROS AL FINAL PONES LOS TOTALES EN LA PRIMERA FILA  EJ: F1 TOTALES DEL PRODUCTO Y ABAJO LA FORMULA QUE EXPLICA EN LA PRIMER RESPUESTA DE SUBTOTAL , O CUANDO TILDES EL FILTRO TILDA TAMBIEN LA QUE DICE TOTAL.

Flores (no verificado)
Imagen de visitante
Contar.si

Hola, tengo una tabla con una columna A de fechas (un año) y otra columna B con números aleatorios del 0 al 99. Necesito que al poner el filtro en la columna de fechas (ejemplo ultima semana, o últimos 3 meses) poder utilizar la fórmula CONTAR.SI para contar la solo las celdas visibles después de aplicar el filtro que cumplan con un criterio de la columna B (Ejemplo cantidad de veces que aparece el número 10 en las celdas visibles). Entiendo que la función subtotales omite valores ocultos pero esta solo me permite utilizarla con CONTAR, y no con CONTAR.SI... AYUDENME POR FAVOR!

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 17 horas 55 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 11175
se ha creado un nuevo tema

se ha creado un nuevo tema para esta nueva pregunta:

http://www.necesitomas.com/contarsi-celdas-visibles

 

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

Luisdom (no verificado)
Imagen de visitante
Genial!

Muchas gracias Paco Megia! Eres un fenómeno. Cordiales saludos.

Alan Perez (no verificado)
Imagen de visitante
excelente formula, tenia

excelente formula, tenia algunas semanas batallando para realizar un calculo similar.

gracias por el aporte!!!!

Johan Guerrero (no verificado)
Imagen de visitante
Muchas gracias

Hombre, muchas gracias por su explicación breve y concisa, me ha servido mucho!

Johan Wiebe (no verificado)
Imagen de visitante
Saldo acumulativo sin tomar en cuenta celdas filtradas u ocultas

Buenos días. Tengo un problema en una plantilla de excel donde por ejemplo en la celda C10 pongo la formula =C9+B10-A10 o sea un saldo acumulativo pero que no toma en cuenta las filas ocultas o filtradas. Quiere decir que si filtro un dato de la fila 9 y esta se oculta, que la formula automáticamente cambia a: =C8+B10-A10. Alguna sugerencia? Gracias.

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 17 horas 55 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 11175
En realidad dos sugerencias

En realidad dos sugerencias.

La primera es que crees un tema nuevo en el foro para plantear tu pregunta en vez de añadirlo como comentario en un tema antiguo.

La segunda es que adjuntes el archivo para poder verlo y tratar de entender lo que ocurre

 

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

jt (no verificado)
Imagen de visitante
buen dato

la botaste hermanito...

luna204 (no verificado)
Imagen de visitante
muy buena informacion me

muy buena informacion me sirvio bastante!!

gracias!

Risa

jorge alberto (no verificado)
Imagen de visitante
SOLUCION

Hola

Mira yo me encontré con el mismo problema y lo solucioné con la fórmula subtotales poniendo en el num_funcion el codigo 109 en vez de 9 que es la suma tradicional y al ocultar las celdas que no quería que me sumara, taran! me sumo correctamente las que estaban visibles. 

Espero les haya servido.

necolas
Imagen de necolas
Offline
última acción: Hace 6 años 2 meses
alta: 24/01/2018 - 15:49
Puntos: 10
AYUDA

Ya se el proceso de sumar las columnas cuando esten filtradas pero ¿como puedo hacer que cuando quite el filtro no se actualice el resultado de la suma hecha anteriormente? 

Gracias

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 17 horas 55 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 11175
Si es una fórmula, no se

Si es una fórmula, no se puede. Las fórmulas en Excel se están recalculando siempre.

Si quieres qeudarte con el valor, copialo y utiliza la opción de Pegar Valores para quedarte con el resultado de la fórmula

 

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

jlb (no verificado)
Imagen de visitante
Sumar solo columnas visibles en tabla dinámica

Es posible realizar un sumatorio de las columnas no ocultas en una tabla dinámica. He leido que en las respuestas anteriores hay opciones de sumar si, subtotales, pero entiendo que en todos los casos se hace referencia a tablas normales, y no dinámicas. Podeis ayudarme¿?

 

Gracias

Cron
Imagen de Cron
Offline
última acción: Hace 8 meses 1 día
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 12735
No entiendo a qué te refieres

No entiendo a qué te refieres con "columnas no ocultas" en una tabla dinámica, pues estos elementos no funcionan ocultando o mostrando valores, sino resumiendo los valores según las condiciones que les pongas.

La respuesta de subtotales sí que hace referencia a tablas normales o a hojas normales con filtro, donde se puedan ocultar o mostrar filas. Las tablas dinámicas tienen su propio ecosistema de funciones, configuraciones y características. Si pudieras subir un ejemplo sería más fácil entender exactamente qué es lo que necesitas.

Un saludo

Zeta (no verificado)
Imagen de visitante
Fómula SUBTOTALES

Gracias. Al principio no me funcionó. Cambié el sino de punto y coma y puse sólo una coma (,) y me funcionó. Apareció la suma sólo de las celdas visibles. Gracias!!!

WILLIAM (no verificado)
Imagen de visitante
QUE LA TABLA DINAMICA NO SUME CELDAS OCULTAS

QUE LA TABLA DINAMICA NO SUME CELDAS OCULTAS

WILLIAM (no verificado)
Imagen de visitante
QUE LA TABLA DINAMICA NO SUME CELDAS OCULTAS

--*** AMIGO SOLUCIONASTE , QUE LA TABLA DINAMICA NO SUME LAS CELDAS OCULTAS

 

 

wesksandoval@hotmail.com