miércoles, 27 de mayo de 2015

Sumar omitiendo las columnas ocultas en Excel.

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:

Sumar omitiendo las columnas ocultas en Excel.



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!:

Sumar omitiendo las columnas ocultas en Excel.


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.

Sumar omitiendo las columnas ocultas en Excel.



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.

17 comentarios:

  1. 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?

    ResponderEliminar
    Respuestas
    1. Hola Jaime,
      que tal estás?, un placer saludarte igualmente.
      Correcto, es lo que se explica en el post.

      un cordial saludo

      Eliminar
  2. Increíble artículo, y muchísimas gracias!!!

    ResponderEliminar
  3. Excelente!!! Lo estoy utilizando ahora.

    Ahora quiero un botón que haga lo que hace F9.
    Cómo lo hago?

    ResponderEliminar
    Respuestas
    1. Hola
      tendrí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

      Eliminar
  4. Monstruo, me ahorraste mucho trabajo!!
    Gracias :)

    ResponderEliminar
  5. gracias! gran explicacion!
    otro 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!

    ResponderEliminar
    Respuestas
    1. Hola
      gracias! ;-)

      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

      Eliminar
  6. 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.

    ¿Como puedo solucionarlo Ismael?

    ResponderEliminar
    Respuestas
    1. hay que actualizar... presiona la tecla de función F9 (o recalcula el libro)...
      Tb con macros :'(

      No hay mas

      Saludos!!!

      Eliminar
  7. 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..???????

    ResponderEliminar
    Respuestas
    1. Hola William
      qué 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

      Eliminar
    2. DISCULPA AMIGO ,,,, SON FILAS OCULTAS, COMO HAGO PARA QUE NO LAS SUME, YO LAS OCULTE EN LA TABLA DINAMICA PERO LA TABLA LAS SUMA

      COMO LE HAGO PARA QUE NO ME LAS SUME

      Eliminar
    3. Hola,
      Si 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

      Eliminar
  8. ************ES UNA TABLA DINAMICA***********

    ResponderEliminar

Nota: solo los miembros de este blog pueden publicar comentarios.