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
qué bueno!!!
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?
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.
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
Es interesante, pero las celdas ocultas tienen una manera más sencilla de detectarse: su propiedad .hidden es true.
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?
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.
GRACIAS!!!!!
TRemendo dato de ayuda....
Muchas Gracias!
Muy buen a aportación
Porque cuando haces esa formula en Filas si te descuenta las cantidades de Filas Ocultas, pero cuando es por columna no lo descuenta
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
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.
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!
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.
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.
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!
se ha creado un nuevo tema para esta nueva pregunta:
http://www.necesitomas.com/contarsi-celdas-visibles
------
Ya sé Excel, pero necesito más.
Muchas gracias Paco Megia! Eres un fenómeno. Cordiales saludos.
excelente formula, tenia algunas semanas batallando para realizar un calculo similar.
gracias por el aporte!!!!
Hombre, muchas gracias por su explicación breve y concisa, me ha servido mucho!
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.
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.
la botaste hermanito...
muy buena informacion me sirvio bastante!!
gracias!
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.
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
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.
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
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
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!!!
QUE LA TABLA DINAMICA NO SUME CELDAS OCULTAS
--*** AMIGO SOLUCIONASTE , QUE LA TABLA DINAMICA NO SUME LAS CELDAS OCULTAS
wesksandoval@hotmail.com