martes, 2 de marzo de 2010

Aclaraciones de la Función SUBTOTALES


Me habéis realizado varias preguntas de las que deduzco que no queda suficientemente claro algún aspecto de la función SUBTOTALES (en concreto el primer argumento de esta función, Num_Funcion).

Tal y como vimos en el anterior post, la función SUBTOTALES tiene dos argumentos, a saber:

Num_funcion: Es un número del 1 al 11 o del 101 al 111 que indica que función debe ser aplicada a la lista. Las correspondencias de dichos números de función son las que se muestran a continuación:


Ref1: Es la referencia o rango de los que queremos calcular el subtotal (podemos incluir hasta 29 referencias o rangos).

Aclaraciones:
1. La función SUBTOTALES pasa por alto las filas que no se hayan incluido en el resultado de un filtro, independientemente del valor de núm_función que utilice.

2. Para las constantes núm_función de 1 a 11, la función SUBTOTALES incluye los valores de filas ocultos por el comando Ocultar en el submenú Fila del menú Formato. Puedes utilizar estas constantes cuando desees calcular subtotales ocultos y números visibles de una lista. Para las constantes núm_función de 101 a 111, la función SUBTOTALES pasa por alto los valores de filas ocultos por el comando Ocultar en el submenú Fila del menú Formato. Puedes utilizar estas constantes cuando sólo desees calcular subtotales de números visibles de una lista.

Para que no quede ninguna duda veámoslo con un ejemplo. Recuperando la tabla utilizada en mi anterior artículo, he añadido otro cálculo adicional de subtotales en el rango C10:D14 que se corresponde con los códigos del argumento Num_Funcion 109, 101, 104, 105 y 102, es decir para que la función SUBTOTALES pase por alto los valores ocultos por el comando Ocultar en el submenú Fila del menú Formato:


Si filtramos la tabla los resultados son idénticos independientemente del num_funcion que utilicemos (da igual poner 9 que 109 para la SUMA, por ejemplo):


Volvemos a nuestra tabla original y procedemos ahora a ocultar, por ejemplo, las filas 21 a 34. Entonces es cuando comprobamos la diferencia del uso de uno u otro código. En el primer caso realiza el cálculo con todos los valores de la tabla, incluyendo los no visibles, mientras que en el segundo caso realiza los cálculos sólo para los valores visibles:


3. La función SUBTOTALES está diseñada para columnas de datos o rangos verticales. No está diseñada para filas de datos ni para rangos horizontales.

4. La función SUBTOTALES no está diseñada para trabajar con referencias tridimensionales. En caso de existir este tipo de referencia devolverá valor de error #¡VALOR!

5. Si hay otros subtotales dentro del argumento ref1 (o subtotales anidados), estos subtotales anidados se pasarán por alto para no repetir los cálculos.

8 comentarios:

  1. Hola,
    Ya me quedo más claro; pero aprovecho para preguntarte lo siguiente: tengo una tabla con sus respectivos campos y oculto unas filas, luego aplico filtro y luego, para volver al estado inicial de la tabla saco el filtro; sin embargo, una vez saco el filtro las filas que estaban ocultas vuelven a aparecer, por lo que si quiero segiuir tenéndolas ocultas tengo que volver a ocultar manualmente; no sé si sabrá sies así o fallo mío.

    Un saudo y gracias

    ResponderEliminar
  2. Hola Miguel. No es fallo tuyo. Es como trabaja excel con los filtros. Al trabajar con filtros no diferencia entre filas ocultas o no ocultas que pertenezcan a la tabla. Aunque un registro esté oculto lo incluirá en el resultado del filtro si cumple las condiciones pedidas. Lo mismo ocurre cuando pides que muestre todos los valores.

    ResponderEliminar
  3. Hola de nuevo y perdona por la insistencia: entonces si lo he entendido bien, la diferencia por ejemplo entre 9 y 109 se ve únicamente cuando NO aplicas filtro, ya que si por ejemplo, oculto una fila "Departamento Financiero", pero luego en el filtro le doy a incluir todos los que son "Departamento Financiero", me van a salir las que haya más la fila que oculté y me lo va a computar en la suma; es decir, si oculto y filtro y la que oculto "resulta seleccionada", me lo va acoger y si no la oculto, obviamente también...por tanto sólo se ve cambio cuando aplico la suma a toda la tabla...no sé si es así la cosa.

    Un saludo y gracias

    ResponderEliminar
  4. Efectivamente Miguel. Si en la tabla original ocultas 5 filas de, por ejemplo, jefe de producto, los códigos 9 y 109 te devolverán distintas sumas. Si entonces activas filtros y en el campo "Puesto" seleccionas "jefe de producto" excel no respeta las filas que hayas ocultado e incluye todas aquellos registros que en el campo "Puesto" cumplan con "jefe de producto". A partir de ese momento no hay diferencia de cálculos con los códigos 1 a 11 y 101 a 111. El comando Filtros trabaja ocultando y dejando visibles celdas que cumplan unos requisitos y no considera las filas que el usuario haya ocultado manualmente previamente.

    ResponderEliminar
  5. Por favor ayuda como sumo subtotales que me salga el valor en texto, tomando de una base de texto.

    ResponderEliminar
    Respuestas
    1. Por favor mándame un ejemplo concreto para que te pueda ayudar. Un saludo.

      Eliminar
  6. Estimado, he visto este tema en varios foros,y todos coinciden en practicamente la misma utilidad de esa función pero le he dado un montón de vueltas y formas, y no hay caso, la 9 y 109 ocultandola o no suman siempre lo mismo, forme todas las filas y despues las formulas, despues oculte filas, y siempre suma lo no visible en ambos casos, ojala me puedas ayudar para saber que me falla. Gracias.

    ResponderEliminar
    Respuestas
    1. Por favor mándame tu hoja de excel a kiko.rial@gmail.com a ver si encuentro el problema. Un saludo

      Eliminar