Estamos muy habituados a obtener nuestros acumulados omitiendo filas ocultas de nuestras tablas o rango empleando las funciones AGREGAR o SUBTOTALES... pero y qué ocurre si queremos realizar una operación similar pero sobre columnas ocultas???.
La inquietud viene originada de la pregunta de un compañero de un grupo de LinkedIn al que pertenezco... y me picó el gusanillo de la curiosidad.
Después de buscar y buscar posibles soluciones encontré una solución, apoyándome en un rango auxiliar.
Veamos el planteamiento de partida:
La idea es obtener la suma de los diferentes conceptos pero omitiendo ciertas columnas (meses) que vamos a ocultar...
Por ejemplo, los meses pares (Febrero, Abril, Junio, etc...)
Al no poder emplear las funciones AGREGAR o SUBTOTALES que sólo trabajan sobre filas ocultas (NO columnas), en el rango C1:N1 incluiremos la siguiente fórmula con la función de Excel CELDA, empleando su propiedad 'ancho', la cual nos dirá el ancho de cada columna... y esta es la clave, ya que el ancho de una columna oculta es CERO!:
Esta es nuestra fórmula para cada celda de C1:N1
=SI(CELDA("ancho";C:C)<>0;1;0)
donde simplemente evaluamos, convirtiendo en 0 y 1, si la columna está oculta o no (oculta =0, visible=1).
Sobre este rango auxiliar bastará aplicar, para cada fila, una función SUMAPRODUCTO, multiplicando cada fila por el rango auxiliar, obteniendo entonces el valor sumado únicamente de las columnas visibles:
=SUMAPRODUCTO(C3:N3;$C$1:$N$1)
OJO!! MUY IMPORTANTE, esta función CELDA exige la actualización de la hoja.. suficiente con presionar la tecla de función F9.
Podemos comprobar y comparar la celda con la función SUMA, que nos devuelve el dato acumulado de los 12 meses, frente a nuestro cálculo que sólo retorna el dato 'visible'.
En post siguientes escribiré algo más de las funciones SUBTOTALES y AGREGAR.
La inquietud viene originada de la pregunta de un compañero de un grupo de LinkedIn al que pertenezco... y me picó el gusanillo de la curiosidad.
Después de buscar y buscar posibles soluciones encontré una solución, apoyándome en un rango auxiliar.
Veamos el planteamiento de partida:
La idea es obtener la suma de los diferentes conceptos pero omitiendo ciertas columnas (meses) que vamos a ocultar...
Por ejemplo, los meses pares (Febrero, Abril, Junio, etc...)
Al no poder emplear las funciones AGREGAR o SUBTOTALES que sólo trabajan sobre filas ocultas (NO columnas), en el rango C1:N1 incluiremos la siguiente fórmula con la función de Excel CELDA, empleando su propiedad 'ancho', la cual nos dirá el ancho de cada columna... y esta es la clave, ya que el ancho de una columna oculta es CERO!:
Esta es nuestra fórmula para cada celda de C1:N1
=SI(CELDA("ancho";C:C)<>0;1;0)
donde simplemente evaluamos, convirtiendo en 0 y 1, si la columna está oculta o no (oculta =0, visible=1).
Sobre este rango auxiliar bastará aplicar, para cada fila, una función SUMAPRODUCTO, multiplicando cada fila por el rango auxiliar, obteniendo entonces el valor sumado únicamente de las columnas visibles:
=SUMAPRODUCTO(C3:N3;$C$1:$N$1)
OJO!! MUY IMPORTANTE, esta función CELDA exige la actualización de la hoja.. suficiente con presionar la tecla de función F9.
Podemos comprobar y comparar la celda con la función SUMA, que nos devuelve el dato acumulado de los 12 meses, frente a nuestro cálculo que sólo retorna el dato 'visible'.
En post siguientes escribiré algo más de las funciones SUBTOTALES y AGREGAR.
SI OCULTAS LAS COLUMNAS Y HACES UNA SUMA DE UN RANGO, O UNA BUSQUEDA DE UNA HOJA A OTRA, HACE LA OMISION. PERO SI MUESTRO LO OCULTO, LO SIGUE OMITIENDO. PERO LA PREGUNTA: ¿SI PRESIONO F9 SE PERDERA LA OMISIÓN?
ResponderEliminarHola Jaime,
Eliminarque tal estás?, un placer saludarte igualmente.
Correcto, es lo que se explica en el post.
un cordial saludo
Increíble artículo, y muchísimas gracias!!!
ResponderEliminarUn placer ayudar.
EliminarSaludos
Excelente!!! Lo estoy utilizando ahora.
ResponderEliminarAhora quiero un botón que haga lo que hace F9.
Cómo lo hago?
Hola
Eliminartendrías que crear una macro
Sub actualiza()
Calculate
End Sub
y luego asignar la macro a un botón...
No se si te merezca la pena
Saludos
Monstruo, me ahorraste mucho trabajo!!
ResponderEliminarGracias :)
;-)
EliminarSlds
gracias! gran explicacion!
ResponderEliminarotro tema, sabes como se olcultan celdas y libros segun un criterio de usuario, tengo pensado un menu de inicio (un libro sencillo) que pregunte que se va a usar (meses y temas de la empresa) y luego, dar clic en boton y esa macro le oculte lo que no se va a usar. quedo atento!
Hola
Eliminargracias! ;-)
Puedes emplear la propiedad de hoja .Visible para mostrar u ocultar las que desees
O también, sin macros, algo más manual... empleando vistas personalizadas leer aquí
Saludos
Una pregunta sobre el tema de este hilo, al ocultar las columnas oficialmente no se hace mas, luego que excel no actualiza la formula de 1 o 0, por ende oculto las celdas y no pasa nada, debo escribir algo en cualquier lado y eliminarlo o usar el teclado para hacer una accion y excel diga: !Ha! actualicemos todo y ahi si registra la suma tras ocultar las columnas.
ResponderEliminar¿Como puedo solucionarlo Ismael?
hay que actualizar... presiona la tecla de función F9 (o recalcula el libro)...
EliminarTb con macros :'(
No hay mas
Saludos!!!
MUY BIEN, PERO SI QUIERO QUE UNA -- T A B L A D I N A M I C A --- NO NE SUME LAS COLUMNAS OCULTAS........COMO LO GAHO..???????
ResponderEliminarHola William
Eliminarqué tal estás?, un placer saludarte igualmente.
Si no quieres que sume columnas ocultas, bastara no incluir una columna(campo en realidad) en la tabla dinámica
Saludos
DISCULPA AMIGO ,,,, SON FILAS OCULTAS, COMO HAGO PARA QUE NO LAS SUME, YO LAS OCULTE EN LA TABLA DINAMICA PERO LA TABLA LAS SUMA
EliminarCOMO LE HAGO PARA QUE NO ME LAS SUME
Hola,
EliminarSi ocultas filas en la tabla dinámica y no quieres que 'sumen', aplica un filtro para que no aparezcan en la tabla dinámica en lugar de ocultar filas
Slds
************ES UNA TABLA DINAMICA***********
ResponderEliminar