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.

4 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